MySQL习题 求数据库高手解答

create table stu1 (

Id int auto_increment primary KEY,

Name VARCHAR(20) not null ,

Sex varchar(4) ,

Brith Date ,

department VARCHAR(20)not null,

address VARCHAR(50)

)

create table score(

Id INT auto_increment PRIMARY KEY,

stu_id int,

FOREIGN KEY(stu_id) REFERENCES stu1(Id) on DELETE CASCADE,

C_name VARCHAR(20),

grade int

)

SELECT *from stu1

INSERT INTO stu1 VALUES( 901,'张老大', '男','1985-06-05','计算机系', '北京市海淀区')

INSERT INTO stu1 VALUES( 902,'张老二', '男','1986-06-05','中文系', '北京市昌平区')

INSERT INTO stu1 VALUES( 903,'张三', '女','1990-06-05','中文系', '湖南省永州市')

INSERT INTO stu1 VALUES( 904,'李四', '男','1990-06-05','英语系', '辽宁省阜新市')

INSERT INTO stu1 VALUES( 905,'王五', '女','1991-06-05','英语系', '福建省厦门市')

INSERT INTO stu1 VALUES( 906,'王六', '男','1988-06-05','计算机系', '湖南省衡阳市')

SELECT *from Score

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '计算机',98)

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '英语', 80)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '计算机',65)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '中文',88)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '中文',95)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '计算机',70)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '英语',92)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '英语',94)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '计算机',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '英语',85)

/*查询 student 表的第 2 条到 4 条记录*/

SELECT * from stu1 LIMIT 2,2

/*从 student 表中查询计算机系和英语系的学生的信息*/

SELECT *from stu1 WHERE department in('计算机系','英语系')

/*从 student 表中查询年龄 18~22 岁的学生信息*/

SELECT YEAR('1990-09-09')

SELECT YEAR(NOW())-YEAR('1990-09-09')

SELECT s.Name,s.age,s.Brith from (SELECT *,YEAR(NOW())-YEAR(Brith)as age FROM stu1)as s WHERE s.age>23 and s.age<28

/*8.从 student 表中查询每个院系有多少人*/

SELECT department,COUNT(id) from stu1 GROUP BY(department)

/*从 score 表中查询每个科目的最高分*/

(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

SELECT id,h.C_name,h.scote_m FROM score,(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

WHERE h.C_name=Score.C_name and h.scote_m=Score.grade

/*10.查询李四的考试科目（c_name）和考试成绩（grade）*/

SELECT name,C_name,grade from stu1,score where name='李四'and stu1.id=score.stu_id

SELECT stu_id,C_name,grade FROM score WHERE stu_id in(SELECT id FROM stu1 where name='李四')

/*12.计算每个学生的总成绩*/

SELECT grade_sum_t.grade_sum,grade_sum_t.stu_id,NAME,department FROM stu1,

(SELECT sum(grade)as grade_sum,stu_id from score GROUP BY stu_id )as grade_sum_t

where grade_sum_t.stu_id=stu1.id

/*13.计算每个考试科目的平均成绩*/

SELECT C_name,AVG(grade) from score GROUP BY C_name

/*14.查询计算机成绩低于 95 的学生信息*/

SELECT * FROM stu1 WHERE id IN(SELECT stu_id from score WHERE C_name='计算机' AND grade<95)

/*15.查询同时参加计算机和英语考试的学生的信息*/

SELECT * FROM stu1 WHERE id IN(

SELECT stu_id FROM score WHERE C_name='英语' AND stu_id in(

SELECT stu_id FROM score WHERE C_name='计算机'))

/*16.将计算机考试成绩按从高到低进行排序*/

/*18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩*/

SELECT Name,department,C_name,grade FROM stu1,score WHERE stu1.id=score.stu_id AND (Name LIKE '王%' OR Name LIKE '张%')

SELECT *from Score

/*19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩*/

SELECT YEAR(NOW())-YEAR(Brith) FROM stu1

SELECT Name,YEAR(NOW())-YEAR(Brith) as age,department,C_name,grade FROM stu1,score WHERE stu1.Id=score.stu_id AND address LIKE '湖南%'

/*20-*/

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '物理',80)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '化学',53)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '生物',59)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '物理',55)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '化学',56)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '生物',50)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '物理',100)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '化学',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '生物',54)

SELECT stu_id,AVG(grade) FROM score WHERE stu_id IN(SELECT stu_id FROM score1 WHERE grade<60 GROUP BY stu_id HAVING COUNT(stu_id)>=2)GROUP BY stu_id

SELECT *FROM score1

Hello，写的语言格式有些丑

练习题目：

3、多表连接（等值连接）

①案例1 ：查询员工名、部门名

②为表起别名

# ③添加筛选条件

# 案例：查询 工资>5000 的工种名和员工名、工资

④添加分组和筛选

#01 案例：查询每个部门的员工个数和部门名

⑤排序

#01 案例：查询每个部门的员工个数和部门名

⑥ 三表连接

# 案例：查询员工名、部门名和所在城市

4、多表连接（等值连接）练习

传统模式的多表连接

1. 显示所有员工的姓名，部门号和部门名称。

2. 查询90 号部门员工的job_id 和90 号部门的location_id

3. 选择所有有奖金的员工的last_name  , department_name , location_id , city 

----------- 三表连查

4. 选择city 在Toronto 工作的员工的

 last_name  , job_id , department_id , department_name     ----------- 三表连查

5. 查询每个工种、每个部门的部门名、工种名和最低工资  ----------- 三表连查

6. 查询每个国家下的部门个数大于2 的国家编号

5、非等值查询

2.非等值连接

#案例1：查询员工的工资以及对应的工资级别

#案例2：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别

6、内连接

#案例1 ：查询员工名、部门名

案例2：查询有奖金的员工名、部门名

案例3：查询城市名、员工名和部门名

9、练习

一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

#二、查询哪个城市没有部门

三、查询部门名为SAL或IT的员工信息

#四、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

/*

employees       Emp#       manager    Mgr#

kochhar            101       king         

  100

*/

10、单行子查询

案例1：谁的工资比Abel高

①查询Abel的工资

②查询员工的信息满足工资>①的结果

案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资

 ①查询141的job_id

②查询143的salary

③查询  姓名，job_id 和工资，满足job_id=①并且salary>②

案例3：返回公司工资最少的员工的last_name,job_id和salary

①查询最低工资

②查询员工的last_name,job_id和salary满足 salary=①

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

①查询50号部门的最低工资

②查询每个部门的最低工资

③筛选最低工资>①

11、多行子查询

二、多行子查询

案例1：返回location_id是1400或1700的部门中的所有员工姓名

①查询location_id是1400或1700的部门编号

②查询department_id满足①结果的员工姓名

案例2：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员

             工号、姓名、job_id 以及salary

①查询job_id为‘IT_PROG’部门工资

②返回其它部门中，工资<any ①的结果

题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工

#的员工号、姓名、job_id 以及salary

12、子查询练习题

#1. 查询和Zlotkey 相同部门的员工姓名和工资

#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。

#①查询公司平均工资

② 查询工资>①的员工的员工号，姓名和工资。

#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

①查询各部门的平均工资

②查询员工的员工号, 姓名和工资，满足本部门并且工资>①

4. 查询姓名中包含字母u 的员工在相同部门的员工的员工号和姓名

①查询姓名中包含字母u的员工的部门

② 部门=①的员工的员工号和姓名

5. 查询在部门的location_id 为1700 的部门工作的员工的员工号

①查询loaction_id =1700的部门编号

② 查询员工号，满足部门号=①

#6. 查询管理者是King 的员工姓名和工资

①查询员工名是king的编号

#② 查询员工姓名和工资，领导的编号=①

#7. 查询工资最高的员工的姓名，要求first_name 和last_name 显示为一列，列名为 姓. 名

①查询最高工资

②查询姓名，工资=①

14、子查询巩固练习

# 1 、查询工资最低的员工信息

#①查询公司的最低工资

②查询员工信息，满足 salary=①

2. 查询平均工资最低的部门信息

①查询每个部门的平均工资

②查询①结果中avg(salary) 字段中的最低值

# ③查询部门编号，满足平均工资= ②结果

④查询部门信息，满足  department_id= ③

3*. 查询平均工资最低的部门信息和该部门的平均工资

 4. 查询平均工资最高的 job 信息

①查询每个job的平均工资

②查询①结果中的 avg(salary)的最高值

③查询每个工种的平均工资，满足 平均工资=②

④工种表和③连接  ， 查询平均工资最高的 job 信息  

# 5. 查询平均工资高于公司平均工资的部门有哪些?

#①查询公司的平均工资

②查询每个部门的平均工资，并且平均工资>①

6. 查询平均工资最高的部门的  manager 的详细信息:

①查询平均工资最高的部门编号

②查询部门编号=①的manager的详细信息

---