数据库技术——数据定义与查询

以下是数据库查询的例表，sno为学号，cno为课程号，sdept为系名，cpno为先修课程，ccredit为学分。

数据定义

注：格式文中出现的[…]代表可省略

1、模式的定义与删除

a、定义模式

create schema <模式名> authorization <用户名>;
要创建模式，调用该命令的用户须拥有数据库管理员的权限，或者取得了管理员授予的create schema权限。
【例】为用户wang定义一个学生-课程模式S-T。

create schema "S-T" authorization wang;

【例】create schema authorization wang;
该语句未指定模式名，默认为wang
定义模式实际上是定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，比如基本表、视图、索引等。

b、删除模式

删除语句：drop schema <模式名> <cascade|restrict>
其中cascade（级联）、restrict（限制）必须二选一，级联表示删除模式及其所有数据库对象（如表、视图等），限制表示只有在模式没有下属对象时才能删除。

2、基本表的定义、删除与修改

a、定义基本表

	create table <表名>  
		(<列名><数据类型>[<列级完整性约束条件>]
		[,<列名><数据类型>[<列级完整性约束条件>]]...
		[,<表级完整性约束条件>]);

【例】创建一个学生表student，学号为主码，姓名取唯一值

create table student
	(sno char(9) primary key,   //sno为主码
	sname char(20) unique,		//sname不取重复值
	ssex char(2),
	sdept char(20)
);

b、数据类型

数据类型	含义
char(n）,character(n)	长度为n的定长字符串
varchar(n),charactervarying(n)	最大长度为n的字符串
clob	字符串大对象
blob	二进制大对象
int,integer	长整数（4字节）
smallint	短整数（2字节）
bigint	大整数（8字节）
numeric(p,d)	定点数，由p位数字（不包括小数点、符号）组成，小数点后面有d位数字
decimal(p,d),dec(p,d)	同numeric(p,d)
real	取决于机器精度的单精度浮点数
double precision	取决于机器精度的双精度浮点数
float(n)	可选精度的浮点数，精度至少为n位数字
boolean	逻辑布尔量
date	日期，包含年、月、日，格式如2019-09-23
time	时间，包含一日的时、分、秒，格式如23：59：03
timestamp	时间戳类型（时间戳解释）
interval	时间间隔类型

c、模式与表

每个基本表都属于一个模式，一个模式有0个或多个基本表

d、修改基本表

alter table <表名> [ add [column] <新列名> <数据类型> [ 完整性约束 ] ]   这里column可省略，alter column表示修改原有的列定义
[ add < 表级完整性约束 >] [ drop [column] <列名> [cascade|restrict]]   这里cascade、restrict同删除模式类似

	[ drop <完整性约束名> ]
		[ modify <列名> <数据类型> ]；

【例】student表中添加入学时间列

alter table student add Sentrance date;

【例】将年龄的数据类型由字符型修改为整型

alter table student alter column sage int;

e、删除基本表

drop table <表名> [cascade|restrict];这里cascade、restrict同删除模式类似

3、索引的定义与删除

a、建立索引

在SQL语言中建立索引用create index语句，格式如下

create [unique] [column] index <索引名>
	on <表名> (<列名> [<次序>] [,<列名> [<次序>] ]...);

【例】给student表按学号升序建立唯一索引

create unique index Stusno on student(sno);

b、修改索引

alter index <旧索引名> rename to <新索引名>;

【例】将student索引改为hahaha

alter index Stusno rename to hahaha;

c、删除索引

drop index <索引名>；

【例】删除student索引

drop index hahaha;

4、数据字典

数据字典是个指南，它为数据库提供了“路线图”，数据字典通常是指数据库中数据定义的一种记录，类似一个
数据库的数据结构，实为数据库管理系统内部的一组系统表，记录了数据库中所有定义的信息。

二、数据查询

一般格式如下
select [ all | distinct ] <目标表达式> [ , [ 目标表达式 ] ] 此处[…] 代表可省略
from <表名或视图名> [ , <表名或视图名>... ] | (<select语句>) as <别名> 此处 | 代表 或
[where <条件表达式>]
[group by <列名1>[ having <条件表达式> ] ]group by 代表按列名1进行分组，值相等的分为一个组
order by <列名2>[ asc | desc ] asc代表升序，desc为降序

1、单表查询

a、选择表中若干列

【例】查询学号姓名select sno,sname from student;

【例】查询出生年份select sname,2019-sage from student;

【例】查询系名且用小写字母表示select lower(sdept) from student;

b、选择表中若干元组

(1)消除取值重复的行

【例】查询课程表中的学生学号select distinst sno from sc;

(2)查询满足条件的元组

常用查询条件

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

【例】查询CS系学生名单select sname from student where sdept='CS';
【例】查询名字中第二个字为阳的学生select sname from student where sname='_阳%';

（注意这里的 _ 和 % 分别表示单个字符和任意长度的字符串）

c、order by子句

用户可以利用order by子句对查询结果按照一个或多个属性列的升序（asc）或降序（desc）排列，默认值为升序。
【例】查询选修了3号课程的学生学号及其成绩，查询结果按成绩降序排列
select sno,grade from sc where cno='3' and order by grade desc;

d、聚集函数

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

【例】查询学生总人数select count(*) from student;
【例】查询选修了一号课程的学生的平均成绩select avg(grade) from sc where cno='1';
注：当聚集函数遇到空值是时，除了count( * )外其余都会跳过空值而只处理非空值。

e、group by子句

group by 代表按列名1进行分组，值相等的分为一个组,分组目的是细化聚集函数的作用对象。如果未对查询结果进行分组，聚集函数会作用于整个查询结果
【例】求各个课程号及其选课人数select cno,count(sno) from sc group by cno; 将cno相同的分为一组，再对其使用聚集函数
【例】查询选修了三门课以上的学生学号select sno from sc group by sno having count(*)>3;
   (having称为分组滤过条件,也就是说是分组需要的条件,所以必须与group by联用,也就是说，聚合函数计算的结果可以当条件来使用，因为它无法放在where里，只能通过having这种方式来解决。如果需要对组函数的结果作为条件，那么不能使用where子句，必须使用having子句。)

2、连接查询

若一个查询设计两个以上的表，则称之为连接查询。

a、等值与非等值连接查询

连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词 ，使用’ = '比较运算符时为等值连接，用到其他比较运算符为非等值连接。
【例】查询每个人的选课情况select student.*,sc.* from where student.sno=sc.sno;

b、自身连接

一个表与自身进行连接
【例】查询一门课的间接先修课
select x.cno,y.cpno from course x,course y where x.cpno=y.cno;

注意这里要为表起两个别名，一个是x，一个是y。

c、外连接

有时候会遇到这种情况：输出所有学生的选课成绩，如果只从选课表SC中输出你会发现一部分学生没有信息，因为他们根本没有选课，对于没有选课的学生也应该进行输出，那这怎么解决呢？这便是外连接。
在一般的连接中，只有满足条件的元组才会输出，但在外连接中，以其中一个表作为主体，并且把悬浮元组保存在输出结果中
【例】以student表为主体，输出所有学生的基本情况和选课情况，若学生没有选课仍然把student的悬浮元组保留，而在sc表的属性上填null；

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

结果如图，没有选课的同学sc表属性列输出为null；还需注意上式为左外连接，输出左边关系中的所有元组，对应的右外连接输出右边关系中的所有元组。

d、多表连接

除了两表连接、自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。
【例】查询每个学生的学号、姓名、选修的课程号以及成绩。

select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;

结果如下

3、嵌套查询

SQL中select-from-where为一个查询块，将一个查询快嵌套在另一个查询块的where子句或者having短语的条件中的查询成为嵌套查询。
【例】查询选修了2号课程的学生的名字

select sname from student where sno in 
(select sno from sc where cno='2');

注意：这里的下层查询块select sno from sc where cno='2'嵌套在上层查询块select sname from student where sno in的where条件中，上层查询块称为父查询或外层查询，下层查询块称为内层查询或子查询。

a、带有in谓词的子查询

子查询的结果往往是一个集合，谓词IN是嵌套查询中常使用的一个谓词。
【例】查询与刘晨在一个系的同学
select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨');这里子查询的查询条件不依赖于父查询，称为不相关子查询
对应的相关子查询，例如以下在带有比较运算符中的子查询即是相关子查询。

实现一个查询一般有多种方法，有些嵌套查询能用其他连接运算代替，有些是不能的，不同的方法效率也不一样，这是数据库编程人员应该掌握的数据库性能调优技术。

b、带有比较运算符的子查询

指的是父查询与子查询之间用比较运算符{>、<、=、>=、<=、!=}连接的嵌套查询
【例】找出每个学生超过他自己选修课程平均成绩的课程号

select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno);

同时这是个相关子查询，因为对于求哪一个学生的平均成绩取决于下x.sno的值，这是和父查询相关的。

c、带有any(some)或all谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用any（有的系统用some）或all谓词修饰符。使用any或all必须同时使用比较运算符，语义如下

表达式	语义
>any	大于子查询中某个值
>all	大于子查询中所有值
<any	小于子查询中某个值
<all	小于子查询中所有值
>=any	大于等于子查询中某个值
>=all	大于等于子查询中所有值
<=any	小于等于子查询中某个值
<=all	小于等于子查询中所有值
=any	等于子查询某个值
=all	等于子查询所有值（通常无意义）
!=(或<>)any	不等于子查询中的某个值
!=(或<>)all	不等于子查询中的任何值

【例】查询非计算机科学系中比计算机科学系任意一个学生年龄都小的学生姓名和年龄。

select sname,sage from student where sage<any
(select sage from student where sdept='CS') and sdept<>'CS';

d、带有exists谓词的子查询

exists即代表存在，带有exists的子查询不返回任何数据，只产生逻辑真值true或逻辑假值false。
【例】查询所有选修了一号课程的学生姓名。

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

注意本例中的子查询sno依赖于父查询的某个属性值，因此是相关子查询。
子查询中select并没有列出实际的列名，因为子查询只返回true或false，列名实际上时没有意义的，若子查询结果为 ==非空==则where返回真值，否则返回假值
与exists对应的not exists，当子查询返回值为真时where返回false，否则为true。
【例】查询选修了全部课程的学生学号和姓名
注意这里用到了全部，但SQL中并没有这样一个此能表达这个意思，我们可以用“没有一门课是他不选修的”来表达语义。

select sno,sname from student where not exists
 (select * from course where not exists
  (select * from sc where sno=student.sno and cno=course.cno));

这里的顺序容易混乱，同时注意这是个多层嵌套相关子查询，从student的第一个元组开始访问，将sno对应到sc的sno，再从sc的第一个元组开始继续向下访问，将第一个元组的cno连接到course的cno，这样便把第一个学生的第一门可连了起来，当第一门课访问结束时（这里说访问不是很恰当），假设查询结果为真，最内层子查询会返回真，到了第二层子查询继续从sc表同一个学号的下一门课程开始访问，直到这个学号的课程访问结束，如果这个学生选修了所有的课程，那么第二层查询会返回false，到了最外层查询会输出这个学生的学号、姓名，接着从第二个学生的学号开始进行下一次访问，直到所有的学生访问结束输出结果。

4、集合查询

集合操作包括并操作union、交操作intersect和差操作except。

注: 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

【例】查询计算机的学生及年龄不大于19的学生（求计算机的学生与所有sage<19的学生的并集）

select * from student where sdept='cs' union select * from 
student where sage<=19;

结果如下：输出了计算机的学生和年龄小于19的学生，union的感觉就像时把两个集合连接了起来

注意到查询结果中去掉了重复元组，如果要保留的话可使用union all操作符。

【例】查询选修了1和2号课程的学生

select student.sno,sname from student,sc where cno='1' 
and sc.sno=student.sno union all select student.sno,sname 
from student,sc where cno='2' and sc.sno=student.sno;

对于intersect和except，用法其实和union是一样的，只需注意它的含义即可。
并且我的mysql不支持intersect和except，所以不讲了。

5、基于派生词的查询

当子查询出现在from语句后，系统会生成一个临时派生表，成为主查询的查询对象
【例】查询选修了1号课程的学生姓名

select sname from student,(select sno from sc where cno='1') 
as temp where student.sno=temp.sno;

注意from后面有两个表，并且派生表需要起了个别名temp，（as可省略）

6、select语句的一般格式

select [ all | distinct ] < 目标表达式 >[ 别名 ] [ ，< 目标表达式 >[ 别名 ] ]…
from < 表明或视图名 > [ 别名 ] [, < 表明或视图名 > [ 别名 ] ]…| ( < select语句 > )
[ as ] < 别名 >
[ where < 条件表达式 > ]
[ group by < 列名 1 > [ having < 条件表达式 > ] ]
[ order by < 列名 2 > [ asc | desc] ]；

注:==‘ [ ] ’ 代表内容可省略，‘ | ’代表逻辑或

OK，常用SQL代码就这些。