SQL语句

1、建表及插入数据

Student表：

create table Student(Sno char(9) primary key, Sname char(20) unique, Ssex char(2),Ssage smallint , Sdept char(20));

​

Course表：

create table Course (Cno char(4) primary key , Cname char(40) not null , Cpno char(4), Ccredit smallint );

​

 

SC表：

create table SC (Sno char(9), Cno char(4), Grade smallint , primary key(Sno,Cno));

​

2、SQL语句

2.1 、 模式的定义与删除

2.1.1、定义模式

create  schema <模式名> authorization <用户名>;

【例 1】为用户WANG定义一个学生-课程模式

create  schema "S-T" authorization WANG;

2.1.2、删除模式

drop schema <模式名> <cascade| restrict>;

cascade表示级联删除 ，在删除模式时同时把该模式中的所有数据库对象全部删除。选择restrict ，表示如果该模式中已经定义了下属数据库对象，则拒绝删除。

【例 2】drop schema ZHANG cascade;

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

2.2.1 定义基本表

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

[表级完整性约束条件]);

【例3】建立一个学生表：student

create table student (Sno char(9) primary key, Sname char(20) unique , Ssex cahr(2));

2.2.2、修改基本表

alter table <表名>
[add [column]<新列名><数据类型>[完整性约束]」
[add<表级完整性约束>]
[DROP [COLUMN] <列名>[CASCADE |RESTRICT]]
[drop constraint<完整性约束名>[CASCADE |RESTRICT]]
[alter column<列名><数据类型>]

其中<表名>是要修改的基本表,ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。drop column子句

用于删除表中的列,如果指定了cascade短语,则自动删除引用了该列的其他对象,比如视图:如果指定了RESTRCT短语,则如果该列

被其他对象引用,RDBMS将拒绝删除该列。DROP CONSTRAINT子句用于删除指定的完整性约束条件。ALTER COLUMN子句用

于修改原有的列定义,包括修改列名和数据类型。

【例4】向Student表中增加 入学时间 列，起数据类型为日期型

alter table Student add S_entrance date;

[例5] 将年龄由数据类型由字符型改为整型

alter table Student alter Sage int;

2.2.3、删除基本表

一般格式：DROP TABLE <表名> [RESTRICT|CASCADE];

restrict：限制删除，如果定义在该基本表上有视图，触发器，引用……此表不能被删除

cascade：级联删除，删除表的同时，删除定义在该表上的所有依赖关系。

【例6】删除Student表

drop table Student cascade;

基本表一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、触发器等对象也将被删除。

2.2.4、索引的建立与删除

1当表的数据量比较大时，查询操作比较耗时。建立索引是加快查询速度的有效手段。

一般格式：CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

ON <表名> (<次序>[<列名>][,<列名>[<次序>]]…)

 

UNIQUE:  每一个索引值只对应唯一的数据记录

CLUSTER:  为了提高某个属性的查询速度，把这些属性上具有相同值的元组集中存放在连续的物理块中称为聚簇。

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

CREATE UNIQUE INDEX Stuno ON Student(Sno);

2修改索引

对于已经建立的索引，如果需要重命名，一般格式：

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

【例8 】 将Student表中的Stusno索引名改为Stuno

ALTER INDEX Scsno REANME TO Stuno;

3、删除索引

一般格式：

DROP INDEX <索引名>;

 

2.3、数据查询（*）

一般格式：

SELECT [ ALL | DISTINCT ] <目标列表达式> [,<目标列表达式>]…

FROM <表名或视图名> [,<表名或视图名>…] | (<SELECT 语句>)[AS] <别名>

[WHERE<条件表达式> ]

[GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ORDER BY <列名2> ] [ ASC | DESC ]

根据where语句的条件表达式从from 子句指定的基本表。视图或派生表找出满足条件的元组，再按select子句中的目标列表达式选出元组中的属性值形成结果。

2.3.1、单表查询

1、选择表中的若干列

（1）查询指定列

【例9】查询全体学生的学号和姓名

Select Sno , Sname  from Student;

（2）查询全部列

Select * from Student;

（3）查询经过计算的值

【例10】查询全体学生的姓名和出生年份

Select  Sname, 2018 -Sage  from Student;

<目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。

【例11】查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

Select Sname , 2018-Sage , lower(Sdept) from Student;

2、选择表中的若干元组

（1）消除取值重复的行

【例12】查询选修了课程的学生学号：

Select distinct Sno from SC;       (distinct去掉重复行，默认为all)

 

（2）查询满足条件的元组，通过where 子句实现

查询条件	谓词
比较	= > <  >=  <=  !=  …
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN , NOT IN
字符匹配	LIKE  ,NOT LIKE
空值	IS NULL , IS NOT NULL
多重条件	AND OR NOT

【例13】查询计算机科学系全体学生的名单

Select Sname from Student where Sdept=’CS’;

 

【例14】查询考试成绩不及格的学生的学号

Select distinct Sno from SC where Grade <60;

【例15】查询年龄在20~30岁之间的学生的姓名、系别和年龄。

Select Sname,Sdept,Ssage from Student where Ssage between 20 and 30;

【例16】查询计算机科学系，、数学系、也不是信息系的学生的姓名和性别。

Select Sname ,Ssex from Student where  Sdept  in ('CS','MA','IS');

(3)字符匹配

一般格式：[NOT] LIKE ‘<匹配串>’ [ ESCAPE‘<换码字符>’]

%便是任意长度的字符串                _代表任意单个字符

【例17】查询所有姓刘的学生的姓名、学号和性别。

Select Sname,Ssex,Sno from Student where Sname like ‘刘%’;

【例18】查询第二个字为‘阳’的学生的姓名和学号。

Select Sname ,Sno from Student where Sname like ‘_阳%’;

（4）、涉及控制的查询

【例19】查询所有有成绩的学生学号和课程号。

Select Sno,Cno from SC where Grade is not null;

（5）、多重条件查询

【例20】查询计算机科学系年龄在20岁以下的学生姓名。

Select Sname from Student where Sdept=’CS’ and Ssage<20;

3、ORDER BY 子句

【例21】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

Select * from Student order by Sdept,Ssage desc;

4、聚集函数

COUNT (*)                                  统计元组个数

COUNT([DISTINCT | ALL]<列名>)               统计一列中值的个数

SUM([DISTINCT | ALL]<列名>)                  计算一列值的总和

AVG([DISTINCT | ALL]<列名>)                  计算一列值的平均值 

MAX([DISTINCT | ALL]<列名>)                  求一列中的最大值

MIN([DISTINCT | ALL]<列名>)                   求最小值

【例22】查询学生总人数。

Select count(*) num from Student;

【例23】查询选修了课程的学生人数。

Select count(distinct Sno) num from SC;

【例24】计算选修了二号课程学生的平均成绩。

Select avg(Grade) from SC where Cno=’2’;

【例25】查询学生201215122选修课程的总学分数。

Select sum(Ccredit) from SC , Course where Sno=’201215122’ and Sc.Cno=Course.Cno;

5、GROUP BY 子句

Group  by 子句将查询结果按某一列或多列的值分组，值相等的为一组。

【例26】求各个课程号及相应的选课人数。

Select Cno,count(Sno) from SC group by Cno;

【例27】查询选修了两门以上课程的学生学号。

Select Sno from SC group by  Sno having count(*) >2;

这里先用group by子句按Sno 进行分组，再用聚集函数count对每一组计数；having短语给出了选择组的天剑，只有满足的组才会被选出来。

【例28】查询平均成绩大于等于80的学生学号和平均成绩。

Select Sno,avg(Grade) from SC group by Sno having avg(Grade)>80;;

注意：where子句中是不能跟聚集函数作为条件表达式的。聚集函数只能跟select子句和group by子句子句中的having子句。

2.3.2、连接查询

前面的表都是针对一个表进行的。若一个查询同时设计两个以上的表，则称为连接查询。连接查询包括等值连接，自然连接，非等值连接，自身连接，外连接，复合条件连接。

1等值和非等值连接

一般格式：[<表名1>]<列名1><比较运算符>[<表名2>]<列名2>

比较运算符有=、>  <  >=  <=  !=

运算符为=时，称为等值连接，其他为非等值连接。

【例29】查询每个学生及其选修课程的情况。

Select Student.* , SC.* from Student , SC where Student.Sno=SC.Sno;

用自然连接完成：会去掉目标重复的属性列。

Select Student.Sno,Sname,Ssex,Ssage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;

【例30】查询选修了二号课程且成绩在80分以上的而所有同学的学号和姓名。

Select Student.Sno , Sname from Student , SC where Student.Sno=Sc.Sno and Sc.Cno=’2’ and SC.Grade>80;

2、自身连接

自己与自己进行连接，称为自身连接。

【例31】查询每门课的间接先修课。

Select FIRST.Cno , SECOND.Cpno from Course FIRST , Course SECOND where FIRST.Cpno= SECOND.Cno;

3、多表连接

【例32】查询每个学生的学号，姓名，选修课的课程名及成绩。

Select Student.Sno , Sname, Cname , Grade from Student, SC , Course where Student.Sno = SC.Sno and SC.Cno=Course.Cno;

2.3.3、嵌套查询

一个select-from-where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语中称为嵌套查询。子查询的select语句中不能使用order by 子句，只能对最终查询结果排序。

1. 带有in谓词的子查询

【例33】查询与“刘晨”在同一个系学习的学生。

Select Sno, Sname , Sdept from Student where Sdept in (select Sdept from Student where Sname =’刘晨’);

【例34】查询选修了课程名为‘信息系统’的学生学号和姓名。

Select Sno , Sname from Student where Sno in(select Sno from SC where Cno In(select Cno from Course where Cname =’信息系统’));

【例35】找出每个学生超出他自己选修课程平均成绩的课程号。

Select Sno ,Cno from SC x where Grade >=(select avg(Grade) from SC y where y.sno=x.sno);

1. 带有any或all谓词的子查询

>any                      大于子查询中的某个值

>all                       大于子查询中的所有值

      Any（任意）       all（全部）

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

Select Sname, Sage from Student where Sage<any(select Sage from Student where Sdept=’CS’) and Sdept<>’CS’;

【例37】查询非计算机科学系中比计算机科学系 所有 学生年龄小的学生姓名和年龄。

Select Sname, Sage from Student where Sage<all(select Sage from Student where Sdept=’CS’) and Sdept<>’CS’;

1. 带有EXISTS谓词的子查询

EXISTS代表存在量词。不返回任何数据，只产生逻辑真值或假值。若内层查询结果非空，则外层的where子句返回真值，否则返回假值。

【例38】查询所有选修了1号课程的学生姓名。

Select Sname from Student where EXISTS  (select * from SC where Sno=Student.Sno  and Cno=’1’);

2.4、数据更新

2.4.1、插入数据

1、插入元组

一般格式：insert into <表名>[(<属性列1>[,<属性列2>]…)] values (<常量1>[,<常量2>]…);

【例39】将一个学生元组（学号：201215128，姓名：陈东，性别：男，所在系：IS， 年龄：18）插入到Student表中。

Insert into Student values(‘201215128’, ‘陈东’,’男’,’IS’,18);

2.4.2、修改数据

一般格式：update <表名> set <列名>=<表达式>[,<列名>=<表达式>]…[where <条件>];

【例40】将所有学生的年龄加1岁

Update Student set Sage=Sage+1;

【例41】将计算机科学系全体同学的成绩置0。

Update SC set Grade=0 where Sno in (select Sno from Student where Sdept=’CS’);

2.4.3、删除数据

一般格式:  DELETE FROM <表名> [WHERE<条件>];

【例42】删除所有学生的选课记录

Delete from SC;

2.5、视图

视图是从一个或几个基本表导出的表，与基本表不同，是一个虚表。

2.5.1、定义视图

CREATE VIEW<视图名>[<列名>[,<列名>]…] AS <子查询> [WITH CHECK OPTION];

子查询可以是任意的select语句。

WITH CHECK OPTION 表示对视图进行update，insert和delete操作时要保证更新、插入或删除的行满足视图定义的谓词条件。

【例43】建立信息系学生的视图

Create view IS_Studnet  as select SNO,Sname,Ssage from Student where Sdept=’IS’;

只是把视图的定义存入数据字典，并不执行其中的select语句。

【例44】建立信息系选修了1号课程的学生的视图（包括学号，姓名，成绩）

Create view IS_S1(Sno,Sname,Grade) as Select Student.Sno,Sname,Grade from Student , SC where Sdept=’IS’ and Student.Sno=SC.Sno and SC.Cno=’1’;

 

视图也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

【例45】建立信息系选修了1号课程且成绩在90分以上的同学的视图。

Create view IS_S2 as select Sno,Sname, Grade from IS_S1 where Grade>=90;

【例46】定义一个反映学生出生年份的视图

Create view BT_S(Sno,Sname,Sbirth) as select Sno , Sname , 2018-Ssage from Student;

【例47】将学生的学号及平均成绩定义为一个视图

Create view S_G (Sno, Gavg) as select Sno,avg(Grade) from SC group by Sno;

2.5.2、删除视图

一般格式：DROP VIEW<视图名>[CASCADE];

视图删除后的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用cascade级联删除语句把该视图和由它导出的视图一起删除。

2.5.3、查询视图

视图定义后，用户可以像对基本表一样对视图进行查询。

【例48】在信息系学生中找出年龄小于20的学生。

Select Sno,Ssage from IS_Student where Ssage<20;

注意：where子句不能用积极函数作为条件表达式。Where是对元组过滤，having是对分组过滤。

2.5.4、更新视图

由于视图是不实际存储的虚表，所以对视图的更新最终转换为对基本表的更新。

2.5.5、视图的作用

1．简化用户的操作

2、使用户能以多种角度看待同一数据

3、对重构数据库提供了一定程度的逻辑独立性

4、视图能对机密数据提供安全保护

5、适当利用视图可以更清晰的表达查询