sql学习笔记5—查询练习

参考资料来源于Bilibili《一天学会MYSQL数据库》

• 条件查询
• 排序
• 子查询
• 其它（按所选字段查询、查询数目）

1、准备数据

创建四个表：学生表、教师表、课程表和成绩表。
学生表(学号 姓名 性别 出生日期 所在班级)

CREATE TABLE student(s_no VARCHAR(10) PRIMARY KEY,
                   s_name VARCHAR(20) NOT NULL,
                   s_sex CHAR(2) NOT NULL,
                   s_birth DATETIME NOT NULL,
                   s_class VARCHAR(10) NOT NULL);

教师表(教师编号、教师名字、教师性别、出生日期、职称、所在部门)

CREATE TABLE teacher(t_no VARCHAR(10) PRIMARY KEY,
                  t_name VARCHAR(20) NOT NULL,
                  t_sex CHAR(2) NOT NULL,
                  t_birth DATETIME NOT NULL,
                  t_prof VARCHAR(20) NOT NULL,
                  t_depart VARCHAR(20) NOT NULL);

课程表(课程号,课程课程名称,教师编号)

CREATE TABLE course(c_no VARCHAR(10) PRIMARY KEY,
                  c_name VARCHAR(20) NOT NULL,
                  t_no VARCHAR(10),
                 FOREIGN KEY(t_no) REFERENCES teacher(t_no));

成绩表(学号,课程号,成绩)

CREATE TABLE score(s_no VARCHAR(10) ,
                  c_no VARCHAR(10),
                  grade DECIMAL,
                  FOREIGN KEY(s_no) REFERENCES  student(s_no),
                  FOREIGN KEY(c_no) REFERENCES  course(c_no),
                  PRIMARY KEY(s_no,c_no));

向表中添加数据

--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

2、查询练习

a.查询表中所有或者部分字段的数据
SELECT * FROM 表名；（所有字段）
SELECT 字段1，字段2，字段3... FROM 表名

SELECT s_no,s_name,s_sex from student;
--返回结果
+------+--------+-------+
| s_no | s_name | s_sex |
+------+--------+-------+
| 101  | 曾华   | 男    |
| 102  | 匡明   | 男    |
| 103  | 王丽   | 女    |
| 104  | 李军   | 男    |
| 105  | 王芳   | 女    |
| 106  | 陆军   | 男    |
| 107  | 王尼玛 | 男    |
| 108  | 张全蛋 | 男    |
| 109  | 赵铁柱 | 男    |
+------+--------+-------+

b.查询表中不重复的字段
SELECT DISTINCT 字段名 FROM 表名;(使用关键字DISTINCT)

SELECT DISTINCT  t_depart FROM  teacher;
--返回结果
+------------+
| t_depart   |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+

c.查询值在某一范围内的记录
SELECT 字段名 FROM 表名 WHERE 值的条件；
值的条件：

• 逻辑运算符
• BETWEEN AND
• IN 值的取值集合

SELECT * FROM score WHERE grade BETWEEN 60 AND 80;
--between and 包含边界
--result
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 105  | 3-245 |    75 |
| 105  | 6-166 |    79 |
| 109  | 3-105 |    76 |
| 109  | 3-245 |    68 |
+------+-------+-------+

也可以

SELECT * FROM score WHERE grade < 60 AND grade >80;
SELECT * FROM score WHERE  grade in (76,79);

d.按所选字段值升序或者降序排列
SELECT 字段名 FROM 表名 ORDER BY 选中的字段 DESC(ASC);

SELECT * FROM student ORDER BY s_class DESC;
--result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth             | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+

以字段1升序，字段2降序
SELECT 字段名 FROM 表名 ORDER BY 字段1 ASC, 字段2 DESC;

SELECT * FROM score ORDER BY c_no ASC, grade DESC;
--result
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 103  | 3-105 |    92 |
| 105  | 3-105 |    88 |
| 109  | 3-105 |    76 |
| 103  | 3-245 |    86 |
| 105  | 3-245 |    75 |
| 109  | 3-245 |    68 |
| 103  | 6-166 |    85 |
| 109  | 6-166 |    81 |
| 105  | 6-166 |    79 |
+------+-------+-------+

e.查询符合某个字段的记录数目
SELECT COUNT(*) FROM 表名 WHRER 条件；
查询班级为”95031”的学生人数；

SELECT COUNT(*) FROM student WHERE s_class='95031';
--result
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

f.子查询
查询score表中成绩最高分的学生学号

SELECT s_no FROM score WHERE grade=(SELECT MAX(grade) FROM score);
--result
+------+
| s_no |
+------+
| 103  |
+------+

上述语句select max(grade) from score 可能会出现一个问题，即最大值有两个及以上，为了避免这种现象，我们可以用limit x,y限制查询记录的个数。
如：

SELECT s_no, c-no FROM score ORDER BY grade LIMIT 0,1;
--result
+------+-------+
| s_no | c_no  |
+------+-------+
| 109  | 3-245 |
+------+-------+