【数据库】关系数据库标准语言SQL

create table Student
(  Sno CHAR(10) primary key,
   Sname CHAR(20) UNIQUE,
   Sage smallint,
   Ssex CHAR(2),
   Sdept CHAR(20),
)

create table Course
(  Cno CHAR(4) primary key,
   Cname CHAR(40) UNIQUE,
   Ccredit smallint,
   Cpno CHAR(4),
  )

create table SC
(  Cno CHAR(4) ,
   Sno CHAR(10),
   Grade smallint,
   primary key(Sno,Cno),
   FOREIGN KEY (Cno) references Course(Cno),
   FOREIGN KEY (Sno) references Student(Sno)
)

alter table修改基本表

增新列：alter table 表名 add 新列名数据类型完整性约束

删完整性约束：alter table 表名 drop 完整性约束

修改原有列类型：alter table 表名 alter column 列名数据类型

/*向Student表中增Scome属性，其数据类型为日期型*/
alter table Student add Scome DATE;
/*将年龄的数据类型改为SMALLINT*/
alter table Student alter column Sage SMALLINT;
/*删除关于学生姓名必须取唯一值的约束*/
alter table Student drop unique(Sname);

drop table删除基本表

drop table 表名

drop table SC;
drop table Course;
drop table Student;

创建、删除索引

创建：create 约束 index 索引名 on 表名（列名）；

删除：drop index 索引名 on 表名；

/*创建唯一索引Stusno在Student表（Sno列）*/
create unique index Stusno on Student(Sno);
/*删除Student表的Stusname索引*/
drop index Stusno on Student;

单表查询

select 属性 from 表名；

常用的符号：

as 查出来的表头

* 所有信息

lower(属性) 查出来的内容大写改小写显示

distinct 删除重复行

字符：所有%，占位符_ (汉字用两个占位)

转义字符escape'\,表示\为转码字符

例：

消除重复行

select Sno from SC;/*默认*/
select distinct Sno from SC;
select all Sno from SC;/*保留所有重复行*/

where子句指定条件：

查询条件	谓词
比大小	=，>,<,>=,<=,!=,<>,!>,!<,NOT+比较运算符
定范围	between...and, not between...and
定集合	in, not in
字符匹配	like, not like
空	null， not null
多重条件	and， or

例：

查IS系、MA系和CS系的学生姓名、性别

select Sname,Ssex from Student
where Sdept in('IS','MA','CS');
select Sname,Ssex from Student
where Sdept = 'IS'or Sdept = 'MA'or Sdept = 'CS';

is,is not不能用 = ，!= 代替；like可以用=代替（模糊查询%时不能代替）

/*查所有有成绩的记录的学生学号和课程*/
select Sno,Cno from SC where Grade is not null;/*is,is not不能用 = ，!= 代替*/

/*and查CS系年龄在20岁以下的学生姓名*/
select Sname from Student where Sage<20 and Sdept like 'CS'/*like可以用=代替*/

转义字符escape

escape' '

/*查DB_Design课程的课程号和学分*/
select Cno,Ccredit from Course where Cname 
like 'DB\_Design'escape'\';/*转义字符escape'\,表示\为转码字符*/

/*查DB_开头且倒数第三个字符为i的课程的详细情况*/
select * from Course where Cname like 'DB\_%i__'escape'\';

对查询结果排序order by

order by默认升序

order by 属性 DESC；//降序

order by 属性 ASC；//升序

升序null在前，降序null在后

例

/*查选修2号课程的学生学号和成绩，按成绩降序排序*/
select Sno,Grade from SC where Cno = '2' order by Grade desc;
/*查选修2号课程的学生学号和成绩，按成绩升序排序*/
select Sno,Grade from SC where Cno = '2' order by Grade asc;

集函数

count（distinc[ALL] *）	统计元组个数
count（distinc[ALL] 列名）	统计一列中值个数
sum（distinc[ALL] 列名）	计算一列值总和（数值型）
avg（distinc[ALL] 列名）	计算平均值
max（distinc[ALL]列名）	求一列值中的最大值
min(distinct[ALL]列名)	求一列值中的最小值

/*查询选修了课程的学生人数*/
select count(distinct Sno) from SC;

/*计算选修了2号课程的学生平均成绩*/
select avg(Grade) from SC where Cno='2';

group by子句

group by子句将查询结果表的各行按一列或多列取值相等的原则进行分组

/*查询各个课程号与相应的选课人数*/
select Cno,count(Sno) as '选课人数' from SC 
group by Cno;

having短语

指定组后 having短语指定筛选条件

/*查询IS系选修了两门及以上课程的学生学号*/
select Sno from SC where Dept = 'IS' 
group by Sno having count(*)>=2;

where子句作用于基本表或视图，选择满足条件的元组；

having短语作用于组，选择满足条件的组；

多表查询

连接查询

等值连接

选择属性值相等的元组

表名1.列名1 比较运算符表名2.列名2

比较运算符有=，>,<,>=,<=,!=

表名1.列名1 between 表名2.列名2 and 表名2.列名3

/*查询每个学生及其选修课情况*/
select Student.* ,SC.* from Student,SC where Student.Sno = SC.Sno;

自然连接

属性值和属性名都相等，删除重复的属性

属性列唯一可以去掉表名前缀，属性都出现了引用时必须加上表名前缀

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC 
where Student.Sno=SC.Sno;

自身连接

一个表与其自己进行连接，可以为这个表取两个别名

select 1.Cno,second.Cpno from Course first,Course second 
where first.Cpno = second.Cno;

嵌套查询

一个表in（一个表）

嵌套查询可以改成连接查询

例：查和刘晨在一个系的学生

分两步查

select Sdept from Student where Sname = '刘晨';
select Sno,Sname,Sdept from Student where Sdept = 'IS';

连接查询

select S1.Sno,S1.Sname,S1.Sdept 
from Student S1,Student S2 
where S1.Sdept = S2.Sdept and S2.Sname = '刘晨';

嵌套查询

select Sno,Sname,Sdept 
from Student S1 
where S1.Sdept in(select Sdept from Student S2 where S2.Sname = '刘晨');

all和any谓词

all表示所有，any表示任一（某一）

/*查其他系中比IS系任一学生年龄小的学生的姓名和年龄，并按年龄的降序排列*/
select Sname,Sage from Student
where Sage<any(select Sage from Student where Sdept ='IS')
and Sdept!='IS' order by Sage desc;

select Sname,Sage from Student 
where Sage<(select max(Sage) from Student where Sdept ='IS')
and Sdept!='IS' order by Sage desc;

/*查询其他系中比IS系所有学生年龄都小的学生的姓名和年龄*/
select Sname,Sage from Student
where Sage<all(select Sage from Student where Sdept = 'IS')
and Sdept<>'IS' order by Sage desc;

存在量词exists

exists返回逻辑真或假，不存在返回假

not exists不存在返回真，存在返回假

/*至少选修了学生201215121选修的全部课程的学生的学号*/
select distinct Sno from SC SCX where not exists
(select* from SC SCY where SCY.Sno = '201215121'and not exists
(select* from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno));

集合查询：并union、交intersect、差minus

/*查询选修了1号课程或者选修了2号课程的学生*/
select Sno from SC where Cno = 1 union select Sno from SC where Cno = 2;

or求并集，and求交集

union==or

intersrct==and

/*3.59查询选修1号课程的学生集合与选修2号课程的学生集合的交集*/
select Sno from SC where Cno = '1' and Sno in
(select Sno from SC where Cno = '2');
/*3.60查询CS系的学生与年龄大于19岁的学生的差集*/
select * from Student where Sdept='CS'and Sage>19;

查询总结

select 目标表达式别名

from 表名/视图名别名

where 条件

group by 列名1 having 条件

order by 列名2 asc/desc

数据更新

insert插入数据

insert into 表名属性列 values 常量

update修改数据

update 表名

set 列名 = 表达式

where 条件

update语句一次只能操作一个表，不能多表连接，只能用嵌套。

delete删除数据

delete from 表名 where 条件

视图

create view创建视图

create view 视图名列名

as 子查询

with check option

with check option：对视图进行更新，插入，删除操作保证所操作的行满足视图定义中的谓词条件。

组成视图的属性列名必须全部省略或者全部指定。

需要指定的情况：

某个目标列不是单纯的属性名，而是集函数或列表达式
多表连接时选出了几个同名列作为视图字段
需要在视图中为某个列启用新的更适合的名字

若使用子查询select*，则当基本表修改，视图就无法正常使用了

drop view删除视图

drop view IS_S1;

查询视图

查询方法与基本表相同

/*查询信息系选修了1号课程的学生*/
select IS_Student.Sno,Sname
from IS_Student,SC
where SC.Cno='1' and SC.Sno = IS_Student.Sno;

更新视图

更新：插入、删除、修改

视图是虚表，对视图更新，最后会转换成对基本表的更新。

更新方法与基本表相同。

如果定义视图时加上with check option子句，若更新不满足条件，则会拒绝执行更新操作

例：

/*将信息系学生视图中学号为201215125的学生姓名改为刘辰*/
update IS_Student
set Sname = '刘辰'
where Sno = '201215125';
/*向信息系学生视图中插入一个新的学生记录，学号201215126，姓名赵新，年龄20岁*/
insert into IS_Student
values('201215126','赵新',20);
/*删除信息系学生视图中学号为201215125的记录*/
delete 
from IS_Student
where Sno='201215125';