《数据库系统概念》-01 初级SQL，基础SQL语句

一、SQL介绍

1.SQL查询语言概览

最为广泛的数据库查询语言：SQL

最初叫做Sequel，现SQL(Structured Query Language,结构化查询语言)

2.SQL数据定义

2.1 基本类型

SQL标准支持多种固有类型：

• char(n)：具有用户指定长度n的固定长度的字符串 。全称 character
• varchar(n)：具有用户指定的最大长度n的可变长度的字符串。全称 character varying
• int：整数（依赖于机器的整数的有限子集）。全称 integer
• smallint：小整数（依赖于机器的整数类型的子集）
• numeric（p，d）：具有用户指定精度的定点数。这个数有p位数字（加上一个符号位），并且小数点右边有p位中的d位数字
• real，double precision：浮点数与双精度浮点数，精度依赖于机器
• float(n)：精度至少为n位数字的浮点数

在char（10）类型的属性A中存入字符串"Abc"，会在该字符串后追加七个空格；但varchar（10）不会

比较两个不同长度的char 类型的值时，会在短值后面附加恰当的空格使它们长度一致

比较 char 和 varchar 属性时，再不同的数据库中有不同的情况，有时会在短值后附加额外的空格，但有时即使两者长度相同，返回也可能为false。因此，推荐无论何时都是以 varchar 类型而不是 char 类型

2.2 基本模式定义

create table定义SQL关系：

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

• primary key(A，B，…，C)：主码，主码属性必须是非空且唯一的。可以使用多个属性作为联合主码

• foreign key(A，B，…，C) references s：外码声明表示关系中任意元组在属性上的取值必须对应于关系s中某元组在主码属性上的取值

• not null：一个属性的非空约束，在该属性上不允许出现空值

• drop tbale r：删除r中的所有元组，还删除r 的模式

• delete from r：保留关系r，但删除r中的所有元组

• alter table r add A D：为已存在关系r添加属性A，A是属性名，D是属性类型

• alter table r drop A：从已存在关系r去掉属性A。很多数据库不支持

3.SQL查询的基本结构

SQL查询的基本结构由三个子句构成：select、from、where

3.1 单关系查询

select A1,A2,A3 from r where A1 = "hello" and A2 > 2000

where 子句中允许使用and、or、not，运算对象可以包括<、<=、=等表达式。

SQL允许我们使用比较运算符比较字符串、算术表达式、以及特殊类型，比如日期类型

3.2 多关系查询

select A1,a.A2,A3 from a,b where a.A2 == b.A2

4.附加的基本运算

4.1 更名运算

select A1,S.A2,A3 from student as S,teacher as T where S.A2 == T.A2

4.2 字符串运算

在SQL使用一对单引号来标识字符串，如果单引号是字符串的组成部分，那就用两个单引号字符来标识，如“I’m so sorry”表示为’I’‘m so sorry’

在一些数据库，如MySql 、SQL Server 中，匹配字符串时并不区分大小

SQL 还支持多种函数：

• 连接字符串用“||”
• 大小写转换，upper（s）转换s为大写，lower（s）转换s为小写
• trim（s）去掉s后面的空格

在字符串上可以使用like运算符来实现运算模式匹配：

• %：%字符匹配任何子串
• _: _匹配任何一个字符

“ \ ”转为转义字符

4.3 排列元组的显示次序

select * from student order by name 默认升序 隐藏了最后的 asc

select * from student order by name desc 降序

4.4 where 子句谓语

between…and… select * from student where age between 1 adn 18

a >= b 且 c >= d 等价于(a,b) >= (c,d)

5.集合运算

5.1 并运算

union 进行并运算，并自动去重。如果想要保留所有重复项，使用union all

(select course_id 
from section
where year = 2017)
union
(select course_id
from section
where year = 2020)
1
2
3
4
5
6
7

5.2 交运算

intersect 进行交运算，并自动去重。如果想要保留所有重复项，使用intersect all

(select course_id 
from section
where year = 2017)
intersect
(select course_id
from section
where year = 2020)
1
2
3
4
5
6
7

5.3 差运算

找出在2017开设但不在2020开设的课程,如果想要保留重复项，使用except all：

(select course_id 
from section
where year = 2017)
except
(select course_id
from section
where year = 2020)
1
2
3
4
5
6
7

6.空值

SQL在涉及空值的任何比较运算的结果是为 unknown（既不是谓词 is null，也不是 is not null），这创建了在 true和false 之外的第三种逻辑值。（一些数据库不支持 unknown）

• true and unknown 结果是 unknown ，false and unknown 结果是 false，unknown and unknown 结果是 unknown
• true or unknown 结果是 true ，false or unknown 结果是 unknown，unknown or unknown 结果是 unknown
• not unknown 结果是 unknown
• null = null 结果是 unknown

where 对于任何一个计算出 false 和unknown 的元组都不能加入结果

7.聚合函数

聚合函数是以值集（集合或多重集合）为输入并返回单个值的函数

SQL提供了五个标准的固有聚集函数：

• 平均数：avg
• 最小\大值：min\max
• 总和：sum
• 计数：count

7.1 基本聚合

select avg(salary) as avg_salary
from teachers
where teachers.age = 28;
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2017;
1
2
3
4
5
6

7.2 分组聚合

找出每个系的平均工资

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

**注意：**任何没有出现在group by 子句中的属性如果出现在select子句中，它只能作为聚合函数中的参数，否则这样的查询就是错误的。

7.3 having子句

having 针对 group by 子句构成的每个分组。

select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000
1
2
3
4

7.4 对空值和布尔值的聚集

在使用 avg 时，如果输入的数据有空值怎么办？

除了count(*)之外的所有的聚集函数都忽略其输入集合中的空值。如此聚集函数的输入值可能为空值，规定空集的count运算值为0，并且当作用在空值上时，其他所有聚集运算返回一个空值。

8.嵌套子查询

以下代码中，括号中的查询就是子查询

8.1 集合成员资格

select name,age 
from teachers 
where age > 25 and name in (select name,age
						from teachers
						where salary > 10000)
1
2
3
4
5

8.2 集合比较

找出工资至少比Biology系某位教师的工资要高的所有教师的名字：

select name
from instructor
where salary > some(select salary
                   from instructor
                   where dept_name = 'Biology')
1
2
3
4
5

找出工资比Biology系每个教师的工资要高的所有教师的名字：

select name
from instructor
where salary > all(select salary
                   from instructor
                   where dept_name = 'Biology')
1
2
3
4
5

8.3 空关系测试

exists结构在作为参数的子查询非空时返回true值

not exists

select course_id
from section S
where semester = 'Fall' and year = 2017 and
	exists(select * 
           from section as T
          where semester = 'Spring' and year = 2018 and
          	S.course_id = T.course_id)
1
2
3
4
5
6
7

8.4 重复元组存在性测试

如果在作为参数的子查询结果中没有重复的元组，则 unique 结构返回 true 值

not unique

select T.course_id
from course as T
where unique(select R.course_id
            from section as R
            where T.course_id = R.course_id and
            	R.year = 2017)
1
2
3
4
5
6

等价于：

select T.course_id
from course as T
where 1>=(select R.course_id
            from section as R
            where T.course_id = R.course_id and
            	R.year = 2017)
1
2
3
4
5
6

8.5 from子句中的子查询

SQL允许在from子句中使用子查询表达式。

select dept_name,avg_salary
from(select dept_name,avg(salary) as avg_salary
    from instructor
    group by dept_name)
where avg_salary>=4200
1
2
3
4
5

8.6 with子句

with子句提供了一种定义临时关系的方式。(大多数数据库支持)

with max_budget(value) as
	(select max(budget)
    from department)
select budget
from department,max_budget
where department.budget = max_budget.value;
1
2
3
4
5
6

8.7 标量子查询

标量子查询：只返回一个包含单个属性的元组

SQL允许标量子查询出现在返回单个值的表达式能够出现的任何地方

select dept_name,
		(select count(*)
        from instructor
        where department.dept_name = instructor.dept_name)
        as num_instructors
from department;
1
2
3
4
5
6

9.数据库的修改

9.1 删除

delect from r where PP代表一个谓词，在关系r中所有满足P的元组都将被删除

9.2 插入

insert into course(course_id,title,dept_name,credits)
	values('CS-437','Database Systems','Comp.Sci.',4);
1
2

在查询结果的基础上插入元组：

insert into instructor
	select ID,name,dept_name,18000
	from student
	where dept_name = 'Music' and tot_cred > 144
1
2
3
4

大部分关系数据库产品都有特殊的“bulk loader”工具，它可以向关系中插入一个非常大的元组集合。这些工具允许从格式化的文本文件中读出数据，并且它们的执行速度比等价的插入语句序列要快得多。

9.3 更新

update instructor
set salary = salary * 1.05
where salary > 15000;
1
2
3

9.4 case结构

SQL提供case结构：

update instructor
set salary = case
	when salary <= 100000 then salary * 1.05
	else salary * 1.03
end
1
2
3
4
5

case 结构：

case
	when pred1 then result1
	when pred2 then result2
	...
	when predn then resultn
	else result0
end
1
2
3
4
5
6
7