一、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));
-
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)
5.2 交运算
intersect 进行交运算,并自动去重。如果想要保留所有重复项,使用intersect all
(select course_id
from section
where year = 2017)
intersect
(select course_id
from section
where year = 2020)
5.3 差运算
找出在2017开设但不在2020开设的课程,如果想要保留重复项,使用except all:
(select course_id
from section
where year = 2017)
except
(select course_id
from section
where year = 2020)
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;
7.2 分组聚合
找出每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;
**注意:**任何没有出现在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
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)
8.2 集合比较
找出工资至少比Biology系某位教师的工资要高的所有教师的名字:
select name
from instructor
where salary > some(select salary
from instructor
where dept_name = 'Biology')
找出工资比Biology系每个教师的工资要高的所有教师的名字:
select name
from instructor
where salary > all(select salary
from instructor
where dept_name = 'Biology')
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)
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)
等价于:
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)
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
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;
8.7 标量子查询
标量子查询:只返回一个包含单个属性的元组
SQL允许标量子查询出现在返回单个值的表达式能够出现的任何地方
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
9.数据库的修改
9.1 删除
delect from r where P
P代表一个谓词,在关系r中所有满足P的元组都将被删除
9.2 插入
insert into course(course_id,title,dept_name,credits)
values('CS-437','Database Systems','Comp.Sci.',4);
在查询结果的基础上插入元组:
insert into instructor
select ID,name,dept_name,18000
from student
where dept_name = 'Music' and tot_cred > 144
大部分关系数据库产品都有特殊的“bulk loader”工具,它可以向关系中插入一个非常大的元组集合。这些工具允许从格式化的文本文件中读出数据,并且它们的执行速度比等价的插入语句序列要快得多。
9.3 更新
update instructor
set salary = salary * 1.05
where salary > 15000;
9.4 case结构
SQL提供case结构:
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
case 结构:
case
when pred1 then result1
when pred2 then result2
...
when predn then resultn
else result0
end