数据库期末复习---SQL语言

一、SQL语言主要组成部分

 

二、DDL

说明：后续的所有操作针对create table 中建的三张表

①create table

student表：

1	Create Table Student
2	(
3	sno char(10) primary key(sno) ,
4	sname varchar(20)  not null,
5	sage smallint,
6	ssex char(2),
7	sdept char(10)
8	)

course表：

1	Create Table Course
2	(
3	cno char(10) primary key (cno) ,
4	cname varchar(20) ,
5	credit smallint
6	)

sc表：

1	Create Table SC
2	(
3	sno char(10) not null ,
4	cno char(10) not null ,
5	grade smallint,
6	primary key (sno, cno)
7	)

②drop table

1	drop table student cascade

cascade:表示强制删除
restrict:有外码、视图时就不能删了

③alter table

例1：向student表增加“入学时间”列，其数据类型为日期型。

1	alter table student add s_extrance datetime

例2：将年龄的数据类型有字符型改为整型

1	alter table student alter column sage int

例3：增加课程名称必须取唯一值的约束条件 

1	alter table course add unique(cname)

例4：删除一列

1	alter table Student drop  column  guofeng

 

④create index

说明:在最经常查询的列上建立，并且一个表最多只能建立一个聚簇索引。
例1：unique表明此索引的每一个索引值只对应唯一的数据记录。

1	create unique index x on student(sno)

例2：cluster表明要建立的索引时聚簇索引。

1	create cluster index x on student(sno)

 

⑤drop index 后直接加索引名

 

三、DML

①select

           例1：查询第一个是‘刘’开头的学生学号

1	select Sname from Student where Sname like '刘%'

说明：%是匹配任意字符串，_是匹配一个字符

例2：查询选了课的学生学号

1	select sno from sc group by sno

另外一种方法：

1	select distinct sno from sc

例3：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。

1	Select  sno，count(sno) From SC
2	Where grade < 60
3	Group By sno
4	Having count(sno) >= 2

例4：查询全体学生的姓名及其出生年份

1	select sname,2009-Sage from Student

例5：查询考试成绩有不及格的学生的学号

1	select distinct sno from sc where grade < 60

说明：这里用了distinct，若一个学生有多门课不及格则只列出一次。

例6：查询年龄在20-30(包括20、30)岁的人

1	select sno from student where sage between 20 and 30

例7：查询CS、MA、IS系的学生姓名

1	select sname from student where sdept in ('CS','MA','IS')

例8：查询CS系年龄在20以下的学生姓名

1	select sname from student where sdept='CS' and sage < 20

例9：查询学生110119选修课程的总学分数

1	select sum(ccredit) from sc,course where sno='110119' and sc.cno=course.cno

例10：查询与郭峰所在系相同的学生姓名

1	select sno,sname from student
2	where sdept in
3	(select sdept from student where sname='郭峰')

例11：查询选修了“算法”的学生学号
方法1：

1	select sno from student
2	where sno in
3	(
4	select sno from sc where cno in
5	(select cno from course where cname='算法算法')
6	)

方法二：

1	select sno from sc,student,course
2	where student.sno=sc,sno and sc.cno=course.cno and course.cname='算法算法'

例12：查询每个学生超过他选修课程平均成绩的课程号

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

例13：查询选修了3门以上课程的学生学号

1	select sno from sc group by sno having count(*) > 3

例14：查询其他系中比CS系所有学生年龄都小的学生学号、年龄
方法一：

1	select sname,sage from student
2	where sage < all(select sage from student where sdept='CS')
3	and sdept <> 'CS'

方法二：

1	select sname,sage from student
2	where sage < (select min(sage) from student where sdept='CS')
3	and sdept <> 'CS'

②insert

举例：insert into student(…) values('001','朱佳','21','f','C');

③update

举例：将学生110119的年龄改为22岁
update student set sage=22 where sno=’110119’

④delete

举例：删除学号为110119的记录
delete from student where sno=’110119’

四、视图

①概述

建立一个视图：

1	create view as
2	select sno,sname,sage
3	from student where sdept='CS' with check option

 with check option:保证你只能对CS系操作，否则出错

之后的操作和对表的操作都一样

②视图的作用总结

使用视图，可以简化数据操作。
使用视图，基表中的数据就有了一定的安全性。
可以更清晰的表达查询。

五、DCL

①把查询student表和修改学生学号的权限授权给用户guofeng

1	grant update(sno),select
2	on table student
3	to guofeng(public是所有人)

②把用户对SC表的INSERT权限收回

1	revoke insert
2	on table sc
3	from guofeng cascade(把开始给别人的也要回来)