第四章 关系数据库标准语言SQL

SQL的基本知识和特点

SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体。

SQL功能	动词
数据查询	select
数据定义	create，drop，alter
数据操纵	insert，update，delete
数据控制	grant（授权），revoke（回收权）

SQL的数据定义功能

数据类型

1. 整型数据类型

整型数据类型	存储大小	取值范围
bigint	8个字节	-263~263-1
int	4个字节	-231~231-1
smallint	2个字节	-215~215-1
tinyint	1个字节	0~255

整数型数据可以在较少的字节力存储较大的精确数字，尽量选用。

2. 定点数据类型
   numeric ≈ decimal，只是numeric型数据的列可以带有identity关键字。
   格式：decimal[(p[,s])]或numeric[(p[,s])]
   p精度指定小数点左右两边的总位数，s刻度指定小数点右边的位数

精度范围	分配字节数
1~9	5
10~19	9
20~28	13
29~38	17

3. 浮点数据类型
   1）real 型数据存储时使用4个字节，精度可达7位。相当于float(24)
   2）float 格式float(n)表示科学计数法尾数的位数

n	p	字节
1~24	7	4
25~53	15	8

4. 字符数据类型
   三种Char、Varchar和Text。最常用的是前两个。
   两者的主要区别是，如果实际数据的字符长度短于给定的最大长度，前者会用空格填充，后者不会。

create table Chars_example
(Char_1 Char(5),
Varchar_1 Varchar(5),
Text_1 Text)
go

插入一行数据

insert into Chars_example
values ('abcd','abc','ddddddddddddddddddddddddddddd')
go

5. 日期/时间数据类型
6. 图形数据类型
7. 货币数据类型
8. 二进制数据类型
   等等

数据定义语句介绍

1. 建立数据库

create database lizi#create database <数据库名>

2. 创建数据表

use lizi
create table student
	(Sno Char(5) Primary Key,#主键不能为空，且为主码
	Sname Char(20) Not Null,#不能为空
	Ssex Char(2) Check (Ssex = '男' Or Ssex = '女'),#约束条件
	Sage Tinyint Check(Sage>=15 And Sage <= 45),
	Sdept Char(20) Default'计算机系')#自动插入

create table course
	(cno char(10) not null,
	cname char(20) not null,
	ccredit tinyint check(ccredit>0),
	pcno char(10),
	primary key(cno))

create table sc
	(sno char(5) not null,
	cno char(10) not null,
	grade tinyint check(grade >=10 and grade<=100),
	primary key(sno,cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno))

3. 删除表

drop table student

4. 修改表结构

alter table student add scome datetime
#向student表增加“入学时间”列，其数据类型为日期型

alter table student alter column sname char(22)
#修改姓名字段的长度

alter table student drop column scome
#删除“入学时间”这个字段

SQL的数据查询功能

查询语句的基本结构

select [all|distinct]<目标列表达式>[,<目标列表达式>]...
from <表名或视图名>[,<表名或视图名>]...
[where<条件表达式>]
[group by <分组依据列>]
[having<条件表达式>]
[order by<排序依据列>[asc|desc]]

简单查询

1. 选择表中的若干列
   1）查询指定列

select sname,sno from student

2）查询全部列

select * from student

3）查询经过计算的值

select sname,2008-sage from student#得到出生年份
select sname,'year of birth:',2004-sage'出生年份' from student
#实际上得到两列，属性名为sname和出生年份，出生年份列的格式为'year of birth:1998'

2. 选择表中的若干元组
   1）消除取值重复的行

select distinct sno from sc

2）查询满足条件的元组
可以通过where来实现

查询条件谓词	符号
比较	=、>、>=、<=、<、<>、(!=)、not
确定范围	between and ，not between and
确定集合	in，not in
字符匹配	like，not like
空值	is null，is not null
多重条件	and，or

①比较大小

select sname from student where sdept='计算机系'
select sname ,sage from student where sage<20
select sname,sage from student where not sage>=20
select distinct sno from course where grade<60

②确定范围

select sname,sdept,sage from student where sage between 20 and 23
select sname,sdept,sage from student where sage not between 20 and 23

③确定集合

select sname,ssex from student where sdept in ('信息系','数学系','计算机系')
select sname,ssex from student where sdept not in ('信息系','数学系','计算机系')

④字符匹配

[not] like '<匹配串>'

其含义是查找制定的属性列值与匹配相匹配的元组。匹配串可以是一个完整的字符串，也可以含有以下通配符：
%：代表任意长度（包括0）的字符串
_：代表任意单个字符
[]：匹配[]中的任意一个字符
[ ^ ]：不匹配[]中的任意一个字符

select sname,sno,ssex from student where sname like'刘%'
select sname,sno from student where sname like '_[大小]%'
select sname,sno,ssex from student where sname not like '刘%'
select * from student where sno like '%[ ^235 ]'
#查询学号中最后一位不是2、3、5的学生情况

⑤涉及空值的查询

select sno,cno from sc where grade is null
select sno,cno from sc where grade is not null

⑥多重条件查询

select sname from student where sdept = '计算机系' and sage<20
select sname,ssex from student where sdept='信息系' or sdept = '数学系' or sdept = '计算机系'

3. 对查询结果排序
   asc为缺省值

select sno,grade from sc where cno='3' order by grade desc
#若按照升序排，成绩为空值的元组将在最后显示，若按照降序排，成绩为空值的元组将在最先显示
select * from student order by sdept ,sage desc

4. 使用集函数

count ([distinct|all] * )#统计元祖个数
count([distinct|all]<列名>)#统计一列中值的个数
sum([distinct|all]<列名>)#计算一列值的总和（此列必须是数值型）
avg([distinct|all]<列名>)#计算一列值的平均值（此列必须是数值型）
max([distinct|all]<列名>)#求一列值中的最大值
min([distinct|all]<列名>)#求一列值中的最小值

select count(*) from student 
select count(distinct sno) from sc
select avg(grade) from sc where cno = '1'
select max(grade) from sc where cno = '1'
select max(grade) , min(grade) ,avg(grade) from sc where cno='1'

5. 对查询结果分组
   原则：如果查询语句中有group by子句，那么select后面的目标列，要么为集函数要么同时出现在group by后面。

select cno,count(sno) from sc group by cno
select sno from sc where sdept = '信息系' group by sno having count(*)>3
#选修课程超过3门的信息系学生的学号

where和having的根本区别在于：作用对象不同，where子句作用于基本表或视图，从中选择满足条件的元组。having短语作用于组，从中选择满足条件的组。

多表连接查询

1. 内连接
   内连接是最常用的连接类型，使用内连接指的是，如果两个表的相关字段满足连接条件，则从这两个表中提取数据并组合成新的记录。
   当连接运算符为“=”时，称为等值连接。使用其他运算符称为非等值连接。
   连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但是不必是相同的。
   如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。

select * from student,sc where student.sno=sc.sno#自然连接
select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where sc.sno=student.sno#查询结果不再出现sc.sno

select student.sno,sname from student,sc where student.sno = sc.sno where sc.cno = '2' and sc.grade>90

2. 自身连接

select c1.pcno,c2.cno
from course c1,course c2#取两个别名c1,c2
where c1.pcno=c2.cno

3. 外连接
   （没连接上的空值）

select student.sno,sname,ssex,sage,sdept,cno,grade 
from student 
left outer join on
sc

子查询

select sname from student where sno in (select sno from sc where cno='2')
#上层查询块/主查询（下层查询块/子查询）

子查询的select语句中不能使用order by子句，order by子句永远只能对最终查询结果排序。

1. 带有in谓词的子查询

①select sdept from student where sname='刘晨'
②select sno,sname,sdept from student where sdept='IS'

合并为

select sno,sname,sdept from student where sdept in 
(select sdept from student where sname = '刘晨')

select sno,sname from student where sno in 
(select sno from sc where cno in
(select cno from course where cname='数据库基础'))

2. 带有比较运算符的子查询
   如1的第一个合并可以为

select sno,sname,sdept from student where sdept = 
(select sdept from student where sname = '刘晨')

注意子查询一定要在比较符之后，下面是错误的：

select sno,sname,sdept from student where
(select sdept from student where sname = '刘晨') = sdept 

3. 带有exists谓词的子查询
   exists代表存在。带有exists的子查询不返回任何实际数据，它只产生逻辑真值或者逻辑假值。

select sname from student
where exists 
(select * from sc where sno=student.sno and cno= '1')

使用exists后，由内层查询结果非空，则外层的where子句返回真值，否则返回假值。由exists引出的子查询，其目标列表达式通常都用“*”，因为带exists的子查询值返回真值或假值，给出列名无实际意义。

4. 相关子查询
   相关子查询指的是，要依赖主查询进行计算，并将其结果再影响到主查询的子查询。通常非相查询中，子查询可以独立完成，之后将结果返回主查询。而对于相关子查询，二者是同步进行的。

select sname from student s1 where sage<(select avg(sage) from student where s1.sdept = sdept

SQL的数据操纵功能

SQL语言的数据操纵语句主要包括插入数据、修改数据和删除数据三种语句。

插入数据记录

1. 插入一行新记录

insert into <表名> [(<列名1>[,<列名2>...])] values (<值>)
#其中<表名>是指要插入新记录的表，<列名>是可选项，指定待添加数据的列

列名排列顺序不一定要和表定义时的顺序一致，但当指定列名表时values子句值的排列顺序必须和列明表中的列名排列顺序一致，个数相等，数据类型一一对应。

insert into student
values('08006','王凡','女',21,'计算机')
#into子句中没有指定列名，则新插入的记录必须在每个属性列上均有值

2. 插入一行的部分数据值

insert into sc (sno,cno)
values ('08006','5')

3. 插入多行记录

insert into <表名> [(<列名1>[,<列名2>...])] <子查询>

create table jsbiao
	(sno char(5),
	sname char(20),
	sdept char(20),
	bnum int)
insert into jsbiao (sno,sname,sdept)
	select sno,sname,sdept from student

修改数据记录

update <表名>
set <列名>>=表达式>[,<列名>>=表达式>]...
[where <条件>]

1. 修改一行

update student
set dept = '信息'
where tn = '王敏'

2. 修改多行

update student
set age = age +1

update jsbiao
set bnum =10

3. 用子查询选择要修改的行

update jsbiao
set bnum = bnum +5
where dept = '计算机'

4. 用子查询提供要修改的值

update jsbiao
set bnum = (select avg(bnum) from jsbiao)

删除数据记录

delete
from <表名>
[where <条件>]

1. 删除一行记录

delete 
from student
where sname='王凡'

2. 删除多行记录

delete 
from jsbiao#jsbiao变成空表

SQL的数据控制功能

权限与角色

1. 权限
   在SQL中有两个安全机制：一种是视图机制，当用户通过视图访问数据库时，不访问词视图外的数据。另一种，也是主要的安全机制，是权限机制。权限机制的基本思想是给用户授予不同类型的权限，在必要时，可以收回授权。
   权限可以分为系统权限和对象权限。
   系统权限由数据库管理员授予其他用户，是指数据库用户能够对数据库系统进行某种特定的操作的限制，如create table
   对象权限由创建基本表、视图等数据库对象的用户授权其他用户，是指数据库用户在制定的数据库对象上进行某种特定的操作的限制，如：select、insert、update、delete
2. 角色
   角色是多种权限的集合，可以把角色授予用户或其他角色。

系统权限与角色的授予与收回

1. 授予

grant <系统权限>|<角色>[,<系统权限>|<角色>]...
to <用户名>|<角色>|public [,<用户名>|<角色>]...
[with grant option]
#public代表数据库中的全部用户，with grant option为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。

grant create table#系统权限
to zhangsan#用户

2. 收回

revoke <系统权限>|<角色>[,<系统权限>|<角色>]...
from <用户名>|<角色>|public [,<用户名>|<角色>]...

revoke create table#系统权限
from zhangsan#用户

对象权限与角色的授予与收回

1. 授予

grant all|<对象权限>[(列名[,列名]...)][,<对象权限>]...on <对象名>
to <用户名>|<角色>|public [,<用户名>|<角色>]...
[with grant option]
#列名用于指定要授权的数据库对象的一列或多列。实际上，只有当授权insert、update权限时才需指定列名；on子句用于指定要授予对象权限的数据库对象名

grant insert on student to zhangsan

2. 收回

revoke <对象权限>|<角色>[,<对象权限>|<角色>]...on <对象名>
from <用户名>|<角色>|public [,<用户名>|<角色>]...

revoke insert
on student
from zhangsan