关系数据库标准语言SQL

关系数据库标准语言SQL

1.SQL概述

结构化查询语言(Structured Query Language，SQL)是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。

2.学生-课程数据库

本教程以学生-课程数据库为例来讲解SQL数据的定义、数据操纵、数据查询和数据控制语句。

为此，要定义一个学生–课程模式S-T。学生–课程数据库中包含以下三个表：

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)（学号，姓名，性别，年龄，所在系）

课程表：Course(Cno,Cname,Cpno,Ccredit)（课程号，课程名，先行课，学分）

学生选课表：SC(Sno,Cno,Grade)（学号，课程号，成绩）

3.数据定义

(1.模式的定义与删除

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

（(1.定义基本表

CREATE TABLE `student` (
  `Sno` char(10) NOT NULL default '' COMMENT '学号',
  `Sname` char(10) default NULL COMMENT '姓名',
  `Ssex` char(2) default NULL COMMENT '性别',
  `Sage` smallint(10) default NULL COMMENT '年龄',
  `Sdept` char(20) default NULL COMMENT '所在系',
  `S_enrance` date default NULL,
  PRIMARY KEY  (`Sno`),
  UNIQUE KEY `Stusno` (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（(2.数据类型

关系模型中一个很重要的概念是域，每一个属性来自一个域，它的取值必须是域中的值。在SQL中域的概念用数据类型来实现。

（(3.模式与表

（(4.修改基本表

ALTER TABLE student ADD S_enrance DATE;//增加列
ALTER TABLE student ALTER COLUMN Sage INT;//修改数据类型
ALTER TABLE course ADD UNIQUE(Cname);//增加唯一性约束条件

（(5.删除基本表

DROP TABLE student CASCADE;//删除表

(3.索引的建立与删除

当表的数据量比较大时，查询操作会比较耗时，建立索引是加快查询速度的有效手段。数据库索引类似于图书会面的索引，能快速定位到需要查询的内容。

（(1.建立索引

CREATE UNIQUE INDEX Stusno ON student(Sno);//建立升序索引 ON表示索引范围列
CREATE UNIQUE INDEX SCno ON sc(Sno ASC,Con DESC);//建立升序和降序索引

ASC是建立升序索引	DESC是建立降序索引

（(2.修改索引

ALTER INDEX SCno RENAME TO SCSno;//修改索引名

（(3.删除索引

DROP INDEX Scno;//删除索引

(4.数据字典

4.数据查询

数据查询是数据库的核心操作，SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的额功能，其一般格式为：

SELECT <目标列的表达式> FROM <表或视图> AS <别名>
WHERE <条件表达式>
GROUP BY<列名1> [HAVING<条件表达式>]
ORDER BY<列名2>[ASC|DESC]

(1.单表查询

单表查询是指仅仅涉及一个表的查询

((1.选择表中的若干列

1.查询指定列
SELECT Sno,Sname FROM student;
2.查询全部列
SELECT * FROM student;
3.查询经过计算的值，SELECT子句的目标表达式可以是表中的列，也可以是表达式，包括算数表达式、字符串常量、函数等。
SELECT Sname,2021-Sage FROM student;
SELECT Sname,'Year of Birth：',2021-Sage，LOWER(Sdept) FROM student;

((2.选择表中的若干元组(行)

(((1.消除取值重复的行

SELECT DISTINCT Sno FROM sc;// DISTINCT为去重约束

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

查询满足条件的元组可以通过WHERE子句实现。

（1）比较大小

用于比较的运算符一般包括：=,>,<,!=

SELECT Sname FROM student WHERE Sdept='CS';//查询表中Sdept='CS'的目标列的所有元组

SELECT Sname,Sage FROM student WHERE Sage>18;

（2）确定范围

谓词BETWEEN AND和NOT BETWEEN AND可以用来查找属性值是否在指定范围内的元组。

其中BETWEEN后是范围的低值，AND后是范围的高值。

//查询表中年龄在15到25之间目标列的所有元组
SELECT Sname,Sdept,Sage FROM student WHERE Sage BETWEEN 15 AND 25;

（3）确定集合

谓词IN可以用来查找属性值属于指定集合的元组。

//查询表中Sdept 在('CS','MA','IS')内的目标列的所有元组
SELECT Sname,Ssex FROM student WHERE Sdept IN('CS','MA','IS');

（4）字符匹配

谓词LIKE可以用来进行字符串的匹配，语法格式为LIKE’<匹配串>’

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。

其中%(百分号)代表任意长度的字符串，_(下横线)代表单个任意字符。

SELECT * FROM student WHERE Sno like'201215121';此时like可用=代替
SELECT * FROM student WHERE Sname LIKE '刘%';
SELECT * FROM student WHERE Sname LIKE '欧阳_';
SELECT * FROM student WHERE Sname LIKE '_阳%';

（5）涉及空值的查询

SELECT Sno,Cno FROM SC WHERE Grade IS NULL;查询表中Grade是0的目标列的所有元组

（6）多重条件查询

逻辑运算符AND和OR可用来连接多个查询条件，AND的优先级高于OR。

//查询表中Sdept='CS'且 Sage<20的目标列的所有元组
SELECT Sname FROM student WHERE Sdept='CS'AND Sage<20;

(((3.ORDER BY子句（排序）

用户可以用ORDER BY子句对查询结果按照一个或者多个属性列的升序(ASC)或降序(DESC)排列，默认值为升序。

//查询表中Cno='3'的目标列的所有元组，并且按照Grade的降序排列
SELECT Sno,Grade FROM sc WHERE Cno='3' ORDER BY Grade DESC;
//查询表中所有列的每个元组，并且按照Sdept升序,Sage降序排列
SELECT * FROM student ORDER BY Sdept,Sage DESC;

(((4.聚集函数（求和、求平均值、求最大值最小值）

为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有如下几个：

注意：聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。

COUNT(*)	统计元组(行)个数
COUNT([DISTINCT|ALL]<列名>)    统计一列中值的个数
SUM([DISTINCT|ALL]<列名>)		计算一列值的总和(此列必须是数值类型)
AVG([DISTINCT|ALL]<列名>)		计算一列值的平均值(此列必须是数值类型)
MAX([DISTINCT|ALL]<列名>)		求一列中的最大值
MIN([DISTINCT|ALL]<列名>)		求一列中的最小值
如果指定Distinct短语，则表示在计算时要取消指定列中的重复项，如果不指定Distinct短语，默认为All，则表示不取消重复值。

SELECT COUNT(*) FROM student;//查询学生总人数
SELECT COUNT(DISTINCT Sno)FROM sc;//查询选修了课程的人数
SELECT AVG(Grade) FROM sc WHERE Cno=1;//查询选修1号课程的学生的平均成绩
SELECT MAX(Grade)FROM SC WHERE Cno=1;//查询选修1号课程的学生最高分数
SELECT SUM(Ccredit)FROM sc,course WHERE Sno='201215012'AND sc.Cno=course.Cno;
//查询学生201215012选修课程的总分数

(((5.GROUP BY子句（分组）

Group By子句查询是将查询结果按某一列或多列的值分组，值相等的为一组，对查询结果分组的的目的是为了细化聚集函数的作用对象，如果未对查询结果进行分组，聚集函数将作用于整个查询结果。

分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

SELECT Cno,COUNT(Sno) FROM sc GROUP BY Cno;//查询各个课程号对应的选课人数
//查询选修了三门以上课程的学生学号
SELECT Sno FROM sc GROUP BY Sno HAVING COUNT(*)>3;
//查询平均成绩大于60分的学生学号和平均成绩
SELECT Sno,AVG(Grade)FROM sc GROUP BY Sno  HAVING AVG(Grade)>60;

GROUP BY 子句先进行分组，再用聚集函数COUNT(*)对每一组计数， HAVING短语给出了选择组的条件。

WHERE子句与HAVING短语的区别在于多用对象不同。WHERE子句多用于基本表或视图，从中选择满足条件的的元组(行)，HAVING短语作用于组，从中选择满足条件的组。

(2.连接查询（两个表）

(1).等值与非等值连接查询

连接查询的WHERE子句用来连接两个表的条件，此称为连接条件

//查询每个学生及其选课情况
SELECT student.*,sc,*FROM student,sc WHERE student.Sno=sc.Sno;
//查询选修2号课程且成绩在60分以上的所有学生的学号和姓名
SELECT student.Sno,Sname 
FROM student,sc 
WHERE student.Sno=sc.Sno AND sc.Cno='2' AND sc.Grade>60;

(2).自身连接

一个表与其自身进行连接称为表的自身连接。

(3).外连接

外连接、内连接、左连接、右链接

(4).多表连接

(3.嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例如：

SELECT Sname FROM student //外层查询
WHERE Sno IN (SELECT Sno FROM sc WHERE Cno='2');//内层查询

嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力，以层层嵌套的方式来构造程序正是SQK中“结构化”的含义所在。

((1.带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。

//查询与刘星在同一个系学习的学生
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='信息系统'
		)
);

//本查询也可以用连接查询来实现
SELECT student.Sno,Sname 
FROM student 
WHERE student.Sno=SC.Sno AND
			sc.Cno=course.Cno AND
			course.Cname='信息系统';

((2.带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。

当用户能确切知道内层查询返回的是单个值时，可用>,<,=,!=等比较运算符。

//找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno,Cno 
FROM sc x	//x是sc表的别名
WHERE Grade>=(SELECT AVG(Grade)
			  FROM sc y		//y是sc表的另一个别名
			  WHERE y.Sno=x.Sno
			  );

((3.带有ANY或ALL谓词的子查询

子查询返回单个值时可以使用比较运算符，但返回多值是时要用ANY(有的系统用SOME)或ALL谓词修饰符。

而使用ANY或ALL谓词同时则必须使用比较运算符。

例如 >ANY 表示大于子查询结果中的某个值，>ALL 表示大于子查询中的所有值。

//查询非计算机科学系(CS)中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage 
FROM student
WHERE Sage<ANY(SELECT Sage   //表示比任意一个学生年龄小
			   FROM student 
			   WHERE Sdept='CS')
AND Sdept!='CS';

((4.带有EXISTS谓词的子查询

EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值"true"或逻辑假值"false"。

使用EXISTS存在量词后，若内层查询结果为非空，则外层的WHERE子句返回真值，否则返回假值。

//查询选修了1号课程的学生姓名
SELECT Sname 
FROM student 
WHERE EXISTS(
SELECT * 
FROM sc
WHERE Sno=student.Sno AND Cno='1');

//也可以用如下嵌套查询语句
SELECT Sname 
FROM student 
WHERE  Sno IN(
SELECT Sno FROM sc 
WHERE  sc.Cno='1'
);

(4.集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并集操作UNION，交集操作INTERSECT，和差集操作EXCEPT。

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

//查询计算机系的学生以及年龄不大于19岁的学生(所查询的是两个查询结果的并集，重复的结果会自动去掉)
SELECT * FROM student WHERE Sdept='CS'
UNION
SELECT * FROM student WHERE Sage<19;

(5.基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。

//查询所有选修了1号课程的学生姓名：	其中SELECT Sno FROM sc WHERE Cno='1'为临时表，别名为sci
SELECT Sname FROM student,(SELECT Sno FROM sc WHERE Cno='1') AS sci
WHERE student.Sno=sci.Sno;

(6.Select语句的一般格式

Select语句的一般格式：

SELECT <目标列的表达式> 
FROM <表或视图> AS <别名>
WHERE <条件表达式>
GROUP BY<列名1> [HAVING<条件表达式>]
ORDER BY<列名2>[ASC|DESC]

5.数据更新

数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。在SQL中有相应的三类语句。

(1.插入数据

(1).插入元组

//将一个新的学生元组插入到Student表中
INSERT INTO student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈东','男','IS',18);

(2).插入子查询结果

//对于每一个系，求学生的平均年龄，并把结果存入新表中
INSERT INTO Dept_age(Sdept,Avg_age) 
SELECT Dept,AVG(Grade)
FROM student 
GROUP BY Sdept;//同一个系在同一个组

(2.修改数据

(1).修改一个元组的值

//将学生201215121的年龄改为22岁
UPDATE student SET Sage=22
WHERE Sno='201215121';

(2).修改多个元组的值

//将所有学生的年龄增加1岁
UPDATE student SET Sage=Sage+1;

(3).带子查询的修改语句

//将计算机系的全体学生的成绩置零
UPDATE sc SET Grade=0
WHERE Sno IN(
SELECT Sno FROM student WHERE Sdept='CS');

(3.删除数据

(1).删除某一元组的值

//删除学号为201215128的学生记录
DELETE FROM student WHERE Sno='201215128';

(2).删除多个元组的值

//删除所有的学生选课记录
DELETE FROM sc;

(3).带子查询的删除语句

//删除计算机科学系所有学生的选课记录
DELETE FROM sc
WHERE Sno IN
(SELECT Sno FROM student WHERE Sdept='CS');

6.空值处理

(1).空值的产生

(2).空值的判断

(3).空值的约束条件

(4).空值的算数运算、比较运算和逻辑运算

7.视图

(1.定义视图

(1).建立视图

(2).删除视图

(2.查询视图

(3.更新视图

(4.视图的作用