SQL数据定义

  • char(n) :n个长度的固定字符串
  • varchar(n) :最大n个长度的可变长字符串
  • int :整数
  • smallint :小整数
  • numeric(p,d) :这个数有p位数字，小数点后d位数字。如numeric(3,1)可以存储33.3 ,44.4
  • float(n) :精度至少为n的单精度浮点数
  • double :双精度浮点数
  • date :日期，格式YYYY-MM-DD
  • time :时间，格式HH:MM:SS
  • timestamp :时间戳，格式YYYY-MM-DD HH:MM:SS

DDL

DDL是数据库定义语言，用于创建修改删除数据库的结构

创建数据库

1
2
3
4
5
6
7
8
9
10
11
create table table_name(
    id int,
    name char(10) not null,
    age int,
    gender char(1) default 'M',
    address varchar(50),
    primary key(id),
    unique(name),
    check(age>10),
    foreign key(address) references other_table (address),
);

有几点需要注意：

  1. 主键用primary key($A_1$,$A_2$…)定义
  2. 候选键用unique关键字定义
  3. 非空约束用not null关键字定义
  4. 检查约束用check()定义
  5. 外键用foreign key($A_1$,$A_2$…) references other_table定义
  6. 默认值用default关键字定义

删除关系

1
2
drop table table_name; # 删除表
delete from table_name; # 删除表中所有数据，表还在

修改关系

1
2
3
4
5
6
7
8
alter table table_name
add  age int;// 添加新列

alter table table_name
drop  address;// 删除列

alter table table_name
modify  name varchar(20);// 修改列的数据类型

DML

增删改查CRUD

查

1
2
3
4
insert into table_name (id,name,address) values (1,'Alice','Beijing');
update table_name set address='Shanghai' where id=1;
delete from table_name where id=1;
select * from table_name where address='Beijing';

select

select distinct name from table;
这样查询结果中name列不会有重复值。如果不加distinct，默认name可能会有重复

select查询对象还可以进行算术运算

1
select id, name, age + 1 as age_next_year from table_name;

as 是起别名

where

where子句中可以使用逻辑运算符 and , or , not 来组合多个条件

1
select * from table_name where address='Beijing' and age>20;

where子句谓词：

  • between … and … : 在某个范围内（包含边界值）
1
select * from table_name where age between 20 and 30;

连表查询

查询结果涉及多个表

1
2
3
select instructor.name, teaches.course_id
from instructor,teaches
where instructor.id = teaches.instructor_id;

现在可以给出SQL的一般形式

  • select子句用于列出查询结果中所需要的属性

  • from子句是在查询求值中需要访问的关系列表

  • where子句是作用在from子句中的关系的属性上的谓词
    一个典型的SQL查询具有如下形式：

    select $A_1$ ,$A_2$ ,…,$A_n$
    from $r_1$,$r_2$,$r_3$…$r_m$
    where P;

执行顺序是先from，再where，最后select

order by

对查询结果排序

1
select * from table_name order by age desc;

默认是升序asc，降序用desc

可以按多个列排序

1
select * from table_name order by age desc, name asc;

这样先按age降序排序，age相同的再按name升序排序

集合运算

SQL支持并，交，差三种集合运算

1
2
3
4
5
6
7
8
9
10
11
select * from table1
union
select * from table2;

select * from table1
intersect
select * from table2;

select * from table1
except
select * from table2;

union自动去除重复项。要想保留重复项，可以使用 union all。intersect和except也会自动去除重复项，同样后面加all解决

聚集函数

SQL提供了多种聚集函数来对一组值进行计算，常用的有：

  • count() : 计数
  • sum() : 求和
  • avg() : 求平均值
  • min() : 求最小值
  • max() : 求最大值

除了count(*)之外，其他聚集函数都忽略null值

1
select count(*), avg(age), max(salary) from table_name;

有时count()会和distinct一起使用，计算某列不同值的个数

1
select count(distinct name) from table_name;

分组查询

SQL提供了group by子句来对查询结果进行分组

1
2
select department, count(*) from employees
group by department;

上面查询结果会显示每个部门的员工数量

在每个分组上执行相应的操作，
要确保select子句中的非聚集函数列都出现在group by子句中

分组过滤 having

SQL提供了having子句来对分组后的结果进行过滤

1
2
3
select department, count(*) from employees
group by department
having count(*) > 5;

包含聚集、group by或having子句的查询的含义可通过下述运算序列来定义：

  1. 与不带聚集的查询情况类似，首先根据from子句来计算出一个关系。
  2. 如果出现了where子句，where子句中的谓词将应用到from子句的结果关系上。
  3. 如果出现了group by子句，满足where谓词的元组通过group by子句被放入分组中。如果没有group by子句，满足where谓词的整个元组集被当成一个分组。
  4. 如果出现了having子句，它将应用到每个分组上；不满足having子句谓词的分组将被去掉。
  5. select子句利用剩下的分组产生查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

嵌套子查询

集合成员资格

连接词 in 用于测试某个值是否在子查询的结果集中，这个结果集就是select子句产生的一组值

1
2
3
select name from students
where department in
    (select department from instructors where salary > 80000);

集合比较

至少比某一个大用 some>表示

1
2
3
select name from students
where age > some
    (select age from instructors where department='CS');

这句SQL表示查询学生中年龄大于计算机系任意一位教师年龄的学生

全部比某一个大用 all>表示

1
2
3
select name from students
where age > all
    (select age from instructors where department='CS');

这句SQL表示查询学生中年龄大于计算机系所有教师年龄的学生

空关系测试

存在用 exists表示

1
2
3
select name from students
where exists
    (select * from instructors where department='CS' and age < students.age);

这句SQL表示查询学生中存在计算机系教师年龄小于该学生年龄的学生

重复元组存在性测试

unique用来测试子查询结果中是否有重复元组

1
2
3
select name from students
where unique
    (select department from instructors where department=students.department);

这句SQL表示查询学生中其所在系只有一位教师的学生

在不使用unique的情况下，可以用count()来实现类似功能

1
2
3
select name from students
where 1>=
    (select count(department) from instructors where department=students.department);

from子查询

from子查询用于在from子句中嵌套一个查询，作为一个临时表使用

1
2
3
select name, age from
    (select name, age from students where age > 20) as temp
where temp.age < 30;

这里的temp是给子查询结果起的别名

with子句

with子句提供了一种定义临时关系的方式。这个定义只对with子句的查询有效

1
2
3
with temp(name, age) as
    (select name, age from students where age > 20)
select * from temp where age < 30;

标量子查询

标量子查询是返回单个值的子查询，可以在select子句或where子句中使用

1
2
select name, age from students
where age > (select avg(age) from students);

不带from子句的标量

1
select (select count(*) from students) /(select count(*) from instructors) as student_instructor_ratio from dual;

这里的dual是Oracle数据库中的一个特殊表，包含一行一列，用于执行不依赖于任何表的查询。在其他数据库中，可以省略from子句，直接使用select语句。

改

删除

1
delete from instructor where salary < 50000;

插入

1
2
insert into instructor (name, age, salary) values ('John Doe', 35, 60000);
insert into instructor values (null, 'Jane Smith', 29, 55000);

如果我们想插入查询出来的一些元组

1
2
insert into high_salary_instructors 
          select id, name, salary from instructor where salary > 80000;

更新

1
update instructor set salary = salary * 1.1 where department = 'CS';

SQL提供case结构，可以根据不同条件设置不同的值

1
2
3
4
5
update instructor set salary = case
    when department = 'CS' then salary * 1.1
    when department = 'EE' then salary * 1.05
    else salary
end;

连接表达式

自然连接

自然连接是最常用的连接操作，表示两个关系中所有同名属性相等的元组进行连接

1
select * from table1 natural join table2;

在此之前我们是这样写的

1
2
select * from table1, table2
where table1.common_attr = table2.common_attr;

连接条件

on子句用于指定连接条件

1
select * from table1 join table2 on table1.common_attr = table2.common_attr;

外连接

外连接用于保留某个关系中的所有元组，即使它们在连接中没有匹配的元组

左外连接

左外连接保留左侧关系中的所有元组，并将右侧关系中匹配的元组连接上去

1
select * from table1 left join table2 on table1.common_attr = table2.common_attr;

右外连接

右外连接保留右侧关系中的所有元组，并将左侧关系中匹配的元组连接上去

1
select * from table1 right join table2 on table1.common_attr = table2.common_attr;

全外连接

全外连接保留两个关系中的所有元组，并将匹配的元组连接上去

1
select * from table1 full join table2 on table1.common_attr = table2.common_attr;

视图

视图定义

使用 create view语句定义视图

1
2
create view view_name as
select column1, column2 from table_name where condition;