Skip to content

REVIEW 3 | SQL PART1

:material-circle-edit-outline: 约 380 个字 :fontawesome-solid-code: 77 行代码 :material-clock-time-two-outline: 预计阅读时间 2 分钟

sql标准非强制，主要是指导

DDL

data-definition language，用来定义数据的都是DDL

Domain Type

  • char(n)
    • fixed length n, invariable
    • 无论输入的字符串长度是多少，它都会占用 n 个字节的存储空间
    • 用空格填充
  • varchar(n)
    • variable length, n is the maximun length
    • 占的空间会变化
  • int
    • smallint
    • 这两个的范围不同，与机器有关
  • numeric(p,d)
    • p is the total digits
    • d is the digits to the right of decimal point
  • real, double precision
    • 都是用来表示实数的数据类型
    • real 是一个通用的术语，指的是任何可以表示实数的数据类型。在不同的编程语言和数据库中，real 的具体含义可能有所不同。
      • 例如，在 C 语言中，real 是一个浮点数类型。在 SQL 中，real 是一种浮点数类型。
    • Double precision 也是一种特定的浮点数类型，它通常占用 64 位内存空间。
    • Real 和 double precision 的主要区别在于它们的精度。Double precision 的精度是 real 的两倍。
      • 在大多数情况下，double precision 是表示实数的最佳选择。
  • float(n)
    • 浮点数，最少有 n digits

Built-in Data Tupes

  • data '2005-7-27'
  • time '09:00:30(.75)'
  • timestamp 'data time'
  • interval '1' day

Table Construct

CREATE TABLE X(
    number int, 
    name varchar(10) not NULL, -- 不能有 NULL
    primary key(number, name),
    foreign key (ID,student) references Y
);

CREATE TABLE Y(
    ID int primary key, 
    grade numeric(3,0) default 0, -- 没设置值的都默认设置为 0
    student varchar(10) not NULL, 
    foreign key (number) references X
        on delete cascade |set null |restrict |set default
        on update cascade |set null |restrict |set default
    -- 这四种动作保证外键的约束完整性
    -- cascade 外键删了主键也删，外键更新主键也得更新
    -- set null 外键删了被设置为 NULL
    -- restrict 如果被引用了就不能执行本次 delete/update
    -- set default 删了就设置为 default
);

insert into X values (12345, 'xiaoming');

drop table X;   -- delete table
delete from Y;  -- delete content, but retains table

alter table X add grade varchar(3);
alter table X drop grade;   -- many database do not support this

not NULL, primary key, foreign key 属于 Intergrity constraints

SELECT

data-manipulation language，对数据进行操作的都是

select NaMe; -- = select NAME = select name, case insensitive
select distinct name; -- 将重复的去掉
select all name; -- 将重复的保留
select *;
select salary/12;

where X and Y or Y not Z;
where salary between 1 and 1000;
where (instructor.ID,dept_name) = (teacher.ID,'math');
where student.cid <> teacher.cid; -- <> == ≠

from A, B; -- 做的笛卡尔乘积

from student natural join teacher on student.cid = teacher.cid;
from student natural join teacher using(cid);

select ID, salary as ID_salary
from instructor (as) i -- as may be omitted
where i.salary < 10
;

String Operations

where name like '%dar%'; -- '% %' match any substring
where name like 'dar%'; -- ' %' match any string beginning with 'dar'
where name like '100\%'; -- search '100%'
where name like '___'; -- match any string of exactly 3 characters
where name like '___&'; -- match any string of at least 3 characters

Order the Display of Tuples

select name
order by name;
order by name desc; -- or asc
order by name,depy_name;

limit 3;

Duplicates

Set Operations

(select name) 
union
(select id);

union;
intersect;
except all; -- retain duplicates

NULL

where salary is null;

Aggregate Functions

avg;
min;
max;
sum;
count;
group by;
having; -- used after the group by, while where used before

Attributes in selectclause outside of aggregate functions must appear in group bylist

image-20240402202318156

ID没有group by

Set Membership

where (name,id) (not) in (select ...)

Set Comparison

where salary > some/all(select salary ...);

exists;
not exists;
unique;
with;

image-20240311152750543

some的强度类似or

image-20240311152813979

all的强度类似and