mysql高级练习题

1 简答题

1.1 简答题目一

约束有哪些分类？各自的作用是什么？

答：非空约束： 关键字是 NOT NULL
保证列中所有的数据不能有null值。

唯一约束：关键字是 UNIQUE
保证列中所有数据各不相同。

主键约束： 关键字是 PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。

检查约束： 关键字是 CHECK
保证列中的值满足某一条件。
注意：MySQL不支持检查约束。

默认约束： 关键字是 DEFAULT
保存数据时，未指定值则采用默认值。

外键约束： 关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

1.2 简答题目二

多表关系有哪些？每种关系的建表原则是什么？

答：一对一：
如：用户 和 用户详情
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
一对多(多对一)：
如：部门 和 员工
一个部门对应多个员工，一个员工对应一个部门

多对多：
如：商品 和 订单
一个商品对应多个订单，一个订单包含多个商品

1.3 简答题目三

内连接查询的SQL格式是？

答：内连接查询语法
 -- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

1.4 简答题目四

外连接查询分类有哪些？SQL格式是？

答：  外连接查询语法
-- 左外连接
    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

1.5 简答题目五

什么是子查询？一般有哪些子查询情况？

答：子查询概念：
    查询中嵌套查询，称嵌套查询为子查询
    
   子查询根据查询结果不同，作用不同：
单行单列：作为条件值，使用 = != > <等进行条件判断
    SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

多行单列：作为条件值，使用 in 等关键字进行条件判断
    SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

多行多列：作为虚拟表
    SELECT 字段列表 FROM (子查询) WHERE 条件; 

1.6 简答题目六

说说你对事务的理解？

答：
1.数据库的事务（Transaction）是一种机制、一个操作序列，
包含了一组数据库操作命令
2.事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，
即这一组数据库命令要么同时成功，要么同时失败
3.事务是一个不可分割的工作逻辑单元

1.7 简答题目七

事务的四大特征有哪些？

答： 
1.原子性（Atomicity）: 事务是不可分割的最小操作单位，
要么同时成功，要么同时失败
2.一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
3.隔离性（Isolation） :多个事务之间，操作的可见性
4.持久性（Durability） :事务一旦提交或回滚，
它对数据库中的数据的改变就是永久的

2 编码题

2.1 编码题目一

训练目标：

能够根据要求创建具有关联的多表

需求描述：

现需要开发一个旅游网站，需要对涉及到的表进行设计并创建。以下是已经设计好的表及表结构，请创建出对应的表及其表关系

图中表说明：

• tab_user ：用户表
• tab_route ： 旅游线路表。如桂林5天4晚双飞游
• tab_category ：旅游线路分类表。如国内游，国际游，亲子游等
• tab_seller ：商家表
• tab_favorite ： 收藏表。也是用户表和线路表的中间表

-- tab_user ：用户表
-- tab_route ： 旅游线路表。如桂林5天4晚双飞游
-- tab_category ：旅游线路分类表。如国内游，国际游，亲子游等
-- tab_seller ：商家表
-- tab_favorite ： 收藏表。也是用户表和线路表的中间表

DROP TABLE IF EXISTS tab_user -- 用户表
DROP TABLE IF EXISTS tab_route-- 旅游线路表
DROP TABLE IF EXISTS tab_category -- 旅游线路分类表
DROP TABLE IF EXISTS tab_seller -- 商家表
DROP TABLE IF EXISTS tab_favorite -- 收藏表

-- 分析：
-- 1.一个用户可以看多个旅游线路表，一个旅游线路表可以被多个用户看，多对多的关系
-- 2. 一个商家可以有多条旅游路线，一条旅游路线只能被一个商家开发，一对多
-- 3.一条旅游线路可以被收藏一次，收藏的旅游路线可以有多条 一对多
-- 4.一个类别有多条旅游路线，一条路线只能有一个分类


-- 创建用户表
CREATE TABLE tab_user(
uid INT PRIMARY KEY auto_increment, -- 用户列表id
username VARCHAR(100) NOT NULL , -- 用户账号
password VARCHAR(32) NOT NULL , -- 用户密码
name VARCHAR (100) NOT NULL , -- 用户姓名
birthday DATE NOT NULL , -- 用户生日
sex CHAR(1) NOT NULL , -- 用户性别
telephone VARCHAR(11) NOT NULL, -- 用户电话
email VARCHAR(100) NOT NULL , -- 用户邮箱
status CHAR(1) NOT NULL , -- 用户状态
code VARCHAR(50) NOT NULL  -- 用户签名
)
-- 查看用户表信息
SELECT * FROM tab_user

-- 创建旅游路线表
CREATE TABLE tab_route(
rid INT PRIMARY KEY auto_increment,
rname VARCHAR(500),
price DOUBLE,
routelntroduce VARCHAR(1000),
rflag CHAR(1),
rdate VARCHAR(19),
isThemeTour CHAR(1),
count INT,
cid INT,
rimage VARCHAR(200),
sid INT,
sourceld VARCHAR(50)
);

-- 创建收藏表。也是用户表和线路表的中间表 tab_favorite 多对对关系

CREATE TABLE tab_favorite(
rid INT,
date DATE,
uid INT,
CONSTRAINT fk_r_id FOREIGN KEY(rid) REFERENCES tab_route(rid), -- 关联线路表
CONSTRAINT fk_u_id FOREIGN KEY(uid) REFERENCES tab_user(uid) -- 关联用户表

)

-- 创建商家表 tab_seller
CREATE TABLE tab_seller(
sid INT PRIMARY KEY auto_increment,
sname VARCHAR(200),
consphone VARCHAR(20),
address VARCHAR(200)
);

-- 创建旅游线路分类表 tab_category

CREATE TABLE tab_category(
cid INT PRIMARY KEY auto_increment,
cnam VARCHAR(100)
)

-- 关联商家表
ALTER TABLE tab_route ADD  CONSTRAINT fk_r_f FOREIGN KEY(sid) REFERENCES tab_seller(sid)

-- 关联旅游线路分类表
ALTER TABLE tab_route ADD  CONSTRAINT fk_r_c FOREIGN KEY(cid) REFERENCES tab_category(cid)

1. 2.2 编码题目二

   训练目标：

   能够根据原型图设计出表及表关系

   需求描述：

   现有如下图所示两个页面原型图，请设计出对应的表结构及多表之间关系

   店铺分类 原型图如下

商家信息 原型图如下：

DROP TABLE Store_classification
DROP TABLE business

CREATE TABLE Store_classification (
Classification_name VARCHAR(200) PRIMARY KEY, -- 分类名称
sort INT , -- 排序
Top_or_not CHAR(1), -- 是否置顶 是、否只需要一个字符 也可以用0和1判断
status TINYINT(1) -- 用0和1标识状态
);

CREATE TABLE business(
name VARCHAR(100), -- 店铺名称
classification VARCHAR(100), -- 店铺分类
addr VARCHAR(200), -- 地址
tel VARCHAR(11), -- 联系方式
sort INT  -- 排序
);

-- 商家信息原型表中的店铺分类数据，关联 店铺分类信息原型中的分类名称，是多对一的关系

ALTER TABLE business 
 ADD 
CONSTRAINT fk_b_s 
FOREIGN KEY(classification) 
 REFERENCES
  Store_classification(Classification_name)

提示：

1. 店铺分类 原型中 是否置顶 在进行字段设计时可以用 1 表示是，0 表示否；状态可以使用 1 表示有效，0 表示禁用
2. 商家信息 原型表中的 店铺分类 数据，关联 店铺分类信息 原型中的 分类名称，是多对一的关系

2.3 编码题目三

训练目标：

能够根据具体的需求完成多表查询语句的编写

需求描述：

现有如下图所示表结构，根据以下要求写出对应的SQL语句

1. 查询 教研部 部门的总人数

2. 列出最低薪金大于15000的各种工作以及从事此工作的员工人数

3. 列出所有员工的姓名及其直接上级的名字

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

5. 列出部门人数大于等于5人的部门名称和人数

素材：

表的创建语句及数据添加语句在 素材\题目一.sql 中

提示：

1. 需求二需要根据员工表中的 job 字段进行分组，分组后判断最低薪金大于15000
2. 需求三是 emp 表的自关联，可以将 emp 表当做员工表和领导表，然后进行外连接查询
3. 需求四需要用到外连接查询
4. 需求五需要分组部门进行分组，然后分组后判断count(*) >= 5

SELECT * FROM emp , dept
 WHERE emp.deptno = dept.deptno 
 ORDER BY empno

-- 1. 查询 `教研部` 部门的总人数
SELECT COUNT(ename) FROM  emp
 WHERE deptno = (SELECT deptno FROM dept WHERE dname = '教研部')

-- 2. 列出最低薪金大于15000的各种工作以及从事此工作的员工人数
SELECT  dname , COUNT(ename) 
FROM
 (SELECT * FROM emp WHERE emp.sal > 15000 ) emp , dept
  WHERE  emp.deptno = dept.deptno GROUP BY dname

-- 3. 列出所有员工的姓名及其直接上级的名字
-- 法一
SELECT t1.ename ,
 (SELECT ename FROM emp t2 WHERE t2.empno = t1.mgr)
   FROM  emp t1  

-- 法二
SELECT t1.ename , t2.ename 
FROM emp  t1 
LEFT JOIN emp  t2 on t2.empno = t1.mgr


-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dname , ename,job,mgr,hiredate,sal,COMM 
FROM dept 
left JOIN emp on dept.deptno = emp.deptno

-- 5. 列出部门人数大于等于5人的部门名称和人数
SELECT  dname ,COUNT(ename) 
FROM emp , dept 
WHERE  emp.deptno = dept.deptno 
GROUP BY dept.dname 
HAVING COUNT(ename)>5

2.4 编码题目四

训练目标：

能够根据具体的需求完成多表查询语句的编写

需求描述：

现有如下图所示表结构，根据以下要求写出对应的SQL语句

1. 查询所有学生的学号，姓名，选课数，总成绩
2. 查询平均成绩大于八十分的同学的姓名和平均成绩
3. 查询没有报东方不败老师课的学生姓名
4. 查询挂科超过两门(包括两门)的学生姓名和班级
5. 查询选修了所有课程的学生姓名

素材：

表的创建语句及数据添加语句在 素材\题目二.sql 中

提示：

1. 需求一需要根据sname或者学生的id进行分组，然后在进行组内聚合运算

2. 需求二需要分组，并且在分组完后进行过滤【使用having】，having后可以跟聚合函数

3. 需求三可以先统计出报了 “东方不败老师” 课的学生，然后排除掉这些学生，其他的就是满足需求的

4. 需求四在分组之前需要过滤掉及格的，然后分组后再过滤过滤掉挂课数小于2门的

-- 1. 查询所有学生的学号 sid，姓名 sname，选课数 ，总成绩 num
SELECT student.sid , sname ,COUNT(course_id), sum(num) 
FROM student, score 
WHERE student.sid = score.student_id 
GROUP BY sname
-- 2. 查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname ,  AVG(num) 
FROM student , score 
WHERE student.sid = score.student_id 
GROUP BY sname 
HAVING AVG(num) > 80

-- 3. 查询没有报东方不败老师课的学生姓名
SELECT sname 
FROM	student 
WHERE student.sname NOT IN (SELECT DISTINCT  sname    
FROM student ,  teacher , course , score 
WHERE teacher.tid = course.teacher_id 
AND student.sid = score.student_id 
AND score.course_id = course.cid  
AND tname = '东方不败老师')


-- 4. 查询挂科超过两门(包括两门)的学生姓名和班级
SELECT student.sname, class.caption 
FROM class, student 
WHERE student.sid 
NOT IN 
(SELECT hg.id FROM 
( SELECT score.student_id id ,
COUNT(score.num) 
count FROM score 
WHERE score.num > 60 
GROUP BY score.student_id ) hg 
WHERE (( SELECT COUNT(*) FROM course )- hg.count) < 2 ) 
AND class.cid = student.class_id


-- 5. 查询选修了所有课程的学生姓名
SELECT student.sname 
FROM student, score 
WHERE student.sid = score.student_id 
GROUP BY
score.studentid 
HAVING COUNT(score.course_id) = (SELECT COUNT(*) FROM course)