先分别创建三个表
mysql> create table S(
-> sno varchar(10) not null, sname varchar(20),dept varchar(20),age int,sex char(4)
-> );
Query OK, 0 rows affected
mysql> create table C(
-> cno varchar(10) not null, cname varchar(20), cpno varchar(10),credit int
-> );
Query OK, 0 rows affected
mysql> create table SC(
-> sno varchar(10) not null, cno varchar(10) not null, score decimal
-> );
Query OK, 0 rows affected
给三个表输入如下信息
学生表-S
学号SNO | 姓名SName | 系部Dept | 年龄Age | 性别Sex |
---|---|---|---|---|
200512 | 李勇 | 计算机系 | 20 | 男 |
200518 | 刘晨 | 计算机系 | 19 | 女 |
200018 | 王敏 | 数学系 | 18 | 女 |
200511 | 杨扬 | 物理系 | 20 | 男 |
200510 | 张立 | 信息系 | 19 | 男 |
200513 | 张立 | 物理系 | 19 | 男 |
200514 | 王点点 | 信息系 | 19 | 女 |
200012 | 欧阳雨 | 数学系 | 20 | 男 |
200515 | 刘依依 | 数学系 | 23 | 女 |
课程表-C
课程号CNo | 课程名CName | 前修课程Cpno | 学分Credit |
---|---|---|---|
c01 | 数据库原理 | c03 | 4 |
c02 | 信息系统 | c01 | 4 |
c03 | 数据结构 | null | 6 |
c04 | DB_设计 | co1 | 3 |
成绩表-SC
学号SNo | 课程号CNo | 成绩Score |
---|---|---|
200512 | c02 | 70 |
200512 | c03 | null |
200512 | c04 | null |
200515 | c01 | 80 |
200518 | c03 | 95 |
200018 | c01 | 80 |
200518 | c02 | 45 |
200511 | c02 | 78 |
200511 | c01 | 45 |
200511 | c03 | 89 |
200514 | c02 | 78 |
200514 | c01 | 45 |
200514 | c03 | 89 |
200514 | c04 | 78 |
数据检索的语句格式
SELECT [ALL|DISTINCT] <目标列表达式列表>
FROM <表名或视图名列表>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]
其中,SELECT后的目标列表达式可以是列名、表达式或函数。
GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING短语:筛选出只有满足指定条件的组
ORDER BY子句:对查询结果表按指定列值的升序或降序排序
注意:T-SQL语句中关键字不区分大小写
- 无条件查询全部数据
select * from S;
2) 在SELECT关键字后指明要检索的列名
① 查询S表的学生学号和姓名
select sno,sname from S;
② 查询S表中的系部名
SELECT DEPT FROM S
③ 查询S表中不重复的系部名
SELECT DISTINCT DEPT FROM S
3) 改变列标题的检索
① 使用 空格 形式: 列名 新标题
SELECT SNO 学号 , SNAME 姓名 FROM S
② 使用“AS”形式,列名 AS 新标题
SELECT SNO AS 学号, SNAME AS 姓名 FROM S
4) 有条件选择的查询
① 在S表检索“信息系”的学生信息
SELECT * FROM S WHERE DEPT=’信息系’
② 在S表中检索姓“王”的学生信息
SELECT * FROM S WHERE sname like ‘王%’
③ 在SC表检索’C01’选修课成绩为空的选课记录
SELECT SNO, CNO FROM SC WHERE cno=’C01’ and SCORE is null
④ 检索年龄为21,18,22的学生学号、姓名
SELECT SNO,SNAME FROM S WHERE age in (21,18,22)
Age IN {21,18,22}
表示某条记录的AGE字段值是否是集合{21,18,22}中的元素,如是,则选择。它等价于下面语句:
SELECT SNO, SNAME FROM S WHERE age=18 or age=21 or age=22
- 使用聚集函数
① 查询选课表中最高分、平均分、最低分
SELECT MAX(SCORE), AVG(SCORE),MIN(SCORE) FROM SC
② 查询“C01”课程的最高分、平均分和最小成绩。
SELECT MAX(SCORE), AVG(SCORE),MIN(SCORE)
FROM SC
WHERE CNO=’C01’
6) 对检索结果进行排序
SELECT * FROM SC
WHERE SCORE IS NULL
ORDER BY SNO,CNO DESC
7) 进行分组统计:
① 查询各学生的选课数
SELECT SNO,COUNT(*)
FROM SC
GROUP BY SNO
② 使用HAVING :“选课表”中查询选修了3 门以上课程的学生学号。
SELECT SNO ,COUNT (*) FROM SC
GROUP BY SNO
HAVING COUNT(*)>=3
③ “选课表”中按学号分组汇总学生的平均分,并按平均分降序排列。
SELECT SNO 学号, AVG(成绩) 平均分 FROM SC
GROUP BY SNO
ORDER BY 平均分 DESC