数据库定义与SQL查询（单表，多表查询）

语法风格

• SQL中的数据项（包括列项、表等）分隔符为“,”
• 语句的结束符为“;”
• 语句一般采用格式化的书写方式
• SQL 对大小写不敏感！

SELECT [ ALL|DISTINCT ] [ 表名. ]{ *| 列名 | 表达式[ AS 新列名 ] }
FROM 表名[表别名], …
[ WHERE 条件表达式 | 子查询 ]

数据库的定义和维护

• 建立数据库 : CREATE DATABASE 数据库名
• 删除数据库 : DROP DATABASE 数据库名
• 显示所有数据库的名称 : SHOW DATABASES
• 切换到指定数据库 : USE 数据库名

数据表的定义和维护

• 建立数据表 : CREAT TABLE 表名 （列名 数据类型，列名 数据类型…）
• 删除数据表 : DROP DATABASE 数据库名
• 显示所有数据表的名称 : SHOW DATABASES 数据库名

修改数据表

• 添加新的列 : ALTER TABLE 表名 ADD COLUMN 列名 数据类型
• 删除表中的列 : ALTER TABLE 表名 DROP 列名
• 修改表中列的名称 : ALTER TABLE 表名 CHANGE 列名 新列名 数据类型
• 修改表中列的数据类型 : ALTER TABLE 表名 ALTER COLUMN 列名 数据类型

数据的定义和维护

• 插入数据 : INSERT INTO 表名 (列1,列2...) VALUES (值1,值2...)
• 更新数据 : UPDATE 表名 set 列=修改值 WHERE 条件
• 删除数据 : DELETE FROM 表名 WHERE 条件
• 查询数据 : SELECT * FROM 表名 WHERE 条件

复杂的单表查询

1.条件查询

比较：<、<=、>、>=、=、<>、not
确定范围：Between A and B、Not Between A and B
多重条件：AND、OR、NOT

SELECT name, age FROM student WHERE age BETWEEN 18 AND 22；
( WHERE age>=18 AND age<=22 )；
(WHERE age NOT BETWEEN 18 AND 22 )；
( WHERE age<18 OR age>22)；

2.来自集合的查询

字符匹配：IN，NOT IN

: 求信息工程系、计算机系、数学系的系名、学生名 (或不是这些系的学生)
SELECT dept , name FROM student WHERE dept IN ('IE', ‘CS’, ‘MA’)；
(WHERE dept=‘IE’ OR dept=‘CS’ OR dept=‘MA’)
(WHERE dept NOT IN (‘IE’, ‘CS’, ‘MA’)； 都不是
(WHERE dept!=‘IE’ AND dept!=‘CS’ AND dept!=‘MA’)

3.模糊查询

字符匹配：LIKE，NOT LIKE
字符匹配：%匹配任意字符串，_匹配一个英文字符
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE 模糊替代条件

例：求最后两个汉字为‘原理’的课程名
SELECT name FROM course WHERE Cname LIKE ‘%原理’；

4.判空查询

空值：IS NULL、IS NOT NULL
SELECT 字段名 FROM 表名 WHERE 字段名 判空条件

查缺少成绩的学生的学号和相应的课程号
SELECT student_number , couser_number FROM course_student WHERE grade IS NULL;

5.分页查询

SELECT 字段名 FROM 表名 LIMIT 起始行数，查询的行数(起始行数从0开始)

查询SC表的学号，显示3条
SELECT student_number FROM SC LIMIT 3;

6.分组查询

将查询结果集按某一列或多列的值分组，值相等的为一组。
SELECT 字段名 FROM 表名 GROUP BY 字段名

查询各个课程号与相应的选课人数
Select course_number , count(Sno) From course Group By course_number;

7.组函数查询

Max()取最大值，min()取最小值 ，avg()取平均值，sum()求和，count()统计记录数量

求在SC表中 选修各门课的人数及平均成绩
SELECT COUNT(student ), AVG( grade） FROM SC；

8.筛选查询

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT 字段名 FROM 表名 GROUP BY 字段名 HAVING 条件

求在SC表中选修课程在5门以上且都及格的学生号及总平均分
SELECT student_number, AVG( all grade) FROM SC GROUP BY student_number HAVING COUNT(course_number)>5 AND MIN(grade)≥60；

9.排序查询
排序：降序DESC、升序ASC
SELECT 字段名 FROM 表名 ORDER BY 字段名 ASC /DESC

对选修’C5’课程的学生按成绩降序排列，同分数者按学号升序排列
SELECT student_number, grade FROM SC WHERE course_number=’C5’ ORDER BY grade DESC，student_number ASC；

连接查询（多表查询）

1.多表连接

连接条件一 ： [表名1.] 列名1 比较运算符 [表名2.]列名2
连接条件二 ：[表名1.]列名1 BETWEEN [表名2.]列名2 AND [表名2.]列名3

求选课情况，要求输出学号、姓名、课程名与成绩
（S为学生表，C为课程表，SC为课程成绩表，sno为学号，cno为课程号）
SELECT S.Sno，S.Sname，S.Cname，SC.Grade FROM S，C，SC WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno；

2.复合连接查询

WHERE子句中除了连接条件，还有其它限制条件。

求选修’C6’课程且成绩超过90分的学生名与成绩
SELECT S.Sname, SC.Grade FROM S, SC WHERE S.Sno=SC.Sno AND SC.Cno='C6' AND SC.Grade>90；

3.自连接

用表别名把一个表定义为多个不同的表进行连接（将同一张表视为多张表）。

求每门课的间接先修课名（即先修课的先修课）
Select first.cname,third.cname From c as first,c as second,c as third Where first.pcno=second.cno and second.pcno=third.cno;

4.左外连接

返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值
语法 ：SELECT 字段名 FROM 左表 LEFT JOIN 右表 on 连接条件

将s表与sc表连接显示且不显示重复行
select * FROM sc LEFT JOIN s on s.sno=sc.sno;

5.右外连接(与左外连接相似)

返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值
语法：SELECT 字段名 FROM 右表 RIGHT JOIN 左表 on 连接条件

将s表与sc表连接显示且不显示重复行
select * FROM sc LEFT JOIN s on s.sno=sc.sno;

6.全外连接

返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空
语法：SELECT 字段名 FROM 表1 FULL JOIN 表2 on 连接条件

查询成绩为80的学生在s表和sc表的所有信息
SELECT * FROM c,sc FULL JOIN sc ON sc.grade='80'

嵌套查询

在SELECT … FROM … WHERE语句结构的 WHERE子句中可嵌入一个SELECT语句块。

其上层查询称为外层查询或父查询，其下层查询称为内层查询或子查询
SQL语言允许使用多重嵌套查询。

在子查询中不允许使用ORDER BY子句。

嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件。

子查询返回单个值的查询

求与‘刘力’同一个系的学生名，年龄
SELECT Sname, Sage FROM S
WHERE Sdept =
( SELECT Sdept FROM S WHERE Sname = ‘刘力’）；

子查询返回一组值的查询

求选修‘C6‘课程且成绩超过90分的学生 sno学号，con课程号
SELECT * FROM S WHERE Sno IN
( SELECT Sno
FROM SC
WHERE Cno=‘C6’
AND Grade＞90 )；