数据库（三）超详细SQL语句入门 | SQL增删改查，重命名，字符操作，联合操作，聚合函数，嵌套子查询

1 SQL表内类型

char(n) 固定长度n的字符

varchar（n） 最大长度n的字符

int 整形

smallint 小整形

numeric（p,d ） 固定小数点位数，特定精度 如 numeric(3,1) 支持 44.5

float

double

2 SQL增删改语句

2.1 创建表

语法

注意不要丢掉分号

示例：

create table department(
	dept_name varchar(20),
	building varchar(15),
	budget numeric (12,2),
	primary key(dept_name)
);

指定主键

primary key(dept_name)

指定非空属性

dept_name varchar(20) not null

制定外键

foreign key (name) references instructor

2.2 删除表

drop table r;

2.3 表中添加属性

alter table r add A D;

为表添加A D属性

2.4 添加新的元组信息

insert into instructors values("10211","Simith","Biology")

2.5 删除表所有元组

delete from instructor

2.6 元组

示例：给收入低于 70000 的教师加薪 5%

update instructor
set salary = salary * 1.05
where salary < 70000;

3 查询语句

查询结构

select 属性

from 表

where 谓词条件

多个表的时候，需要加表名.属性区分

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name= department.dept_name;

注意

1 SQL语句大小写不敏感 name=NAME

2 选择结果可能会包含重复的结果

使用distinct去除重复

select distinct dept_name
from instructor;

3 使用*查询全部

select * from instructor;

4 选择中可以包括运算表达式

select ID, name, salary/12 as monthly_salary  
from instructor;

表示选择工资除以12后选择出来

5 where谓词

select name
from instructor
where dept_name = 'Comp. Sci.'  and salary > 70000;

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

4 重命名

4.1 为什么用

as

1 在相同的关系中比较元组

示例：查找 Comp.Sci 部门中薪水高于至少一名讲师的所有讲师的姓名。

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci';

2 将长的名字改短

select T.name, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

3 结果属性修改名称

select T.name as instructor_name, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

5 字符操作

5.1 寻找

like

% 子字符串匹配

__ 匹配任何字符串

举例

'Intro%' matches any string beginning with “Intro”.
'%Comp%' matches any string containing “Comp” as a substring.
'_ _ _' matches any string of exactly three characters.
'_ _ _ %' matches any string of at least three characters.

注意

1 大小写敏感

2 转义字符区分%

Match the string “100%”
like ‘100 %’

6 生序降序

order关键字

desc 降序

asc升序

示例1：按字母顺序列出物理系的所有教师

select name
from instructor
where dept_name = 'Physics'
order by name;

示例2：按薪水和姓名列出讲师

select *
from instructor
order by salary desc, name asc;

between

select name 
from instructor
where salary between 90000 and 100000

等价于

select name
from instructor
where salary <= 100000 and salary >= 90000;

7 联合操作

7.1 并集Union

可以直接自动消除重复，不像选择子句。

例子1 ：查找 2017 年秋季或 2018 年春季开设的课程

select course_id  
 from section
 where semester = 'Fall' and year = 2017
 union
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

如果我们想要保留所有重复值

用union all

select course_id  
 from section
 where semester = 'Fall' and year = 2017
 union all 
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.2 交集 INTERSECT

select course_id  
 from section
 where semester = 'Fall' and year = 2017
intersect
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.3 差集 EXCEPT

select course_id  
 from section
 where semester = 'Fall' and year = 2017
except
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.4 对于空值补充

1 任何包含Null的表达式返回为Null

例：

5 + null  returns null

2 任何包含Null的比较返回的值是未知的

例

 5 < null  or  null <> null   or   null = null  returns unknown

3 对于布尔运算

and : (true and unknown)  = unknown,  
	  (false and unknown) = false,
      (unknown and unknown) = unknown
or: (unknown or true)   = true,
	 unknown or false)  = unknown
    (unknown or unknown) = unknown

空值，未知检测

示例：查找工资为空的所有教师。

select name
from instructor
where salary is null/ unknown

都是用is的，注意不能用=来替代is 在null的情况下

8 聚合函数

Average: avg
Minimum: min
Maximum: max
Total: sum
Count: count

示例：查找计算机科学系教师的平均工资

select avg (salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';

8.1 group by

group by是一个限定函数，意味着在这种情况下，限定在哪里的一个范围内进行比较寻找

比如下面的例子，要找到每一个部门的平均值

示例:查找每个部门讲师的平均工资

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

8.2 having

示例：查找平均工资大于42000的所有部门的名称和平均工资

having相当于就是给Group加一个条件，比如在这个范围内大于42000的

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

9 Nested Subqueries 嵌套子查询

9.1 Set Membership

in 和not in

示例： 查找所有姓名为“莫扎特”或“爱因斯坦”的教师

select distinct name
from instructor
where name in("Mozart","Einstein")

示例：查找已参加 ID 为 10101 的教师教授的课程部分的（不同）学生总数

select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in                                 (select course_id, sec_id, semester, year
                                 from teaches
                                 where teaches.ID= 10101);

9.2 Set Comparison

SQL also allows < some, <= some, >= some, = some, and <> some
定义： y < some (X) : y小于X中任一元素，则结果为true

比较

some 只要一个满足即可

all 所有满足

9.3 exist or not exists

示例: 查找学过生物系所有课程的学生（ID、姓名）。

select S.ID, S.name
from student as S
where not exists ( (select course_id
                                 from course
                                 where dept_name = 'Biology')
                               except
                                 (select T.course_id
                                   from takes as T
                                   where S.ID = T.ID));

9.4 with as

临时的关系，一会儿的查询用到

示例: 查找预算最高的所有部门

with max_budget (value) as       
	(select max(budget)
    from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;