MySQL的查询语句练习

单表查询部分：

素材：

表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等CREATE TABLE `worker` (`部门号` int(11) NOT NULL,`职工号` int(11) NOT NULL,`工作时间` date NOT NULL,`工资` float(8,2) NOT NULL,`政治面貌` varchar(10) NOT NULL DEFAULT '群众',`姓名` varchar(20) NOT NULL,`出生日期` date NOT NULL,PRIMARY KEY (`职工号`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

题一：

显示所有职工的基本信息

mysql">select * from worker;

在这里插入图片描述

题二：

查询所有职工所属部门的部门号，不显示重复的部门号

mysql">select distinct `部门号` from `worker`;        -- distinct 是去重的

在这里插入图片描述

题三：

求所有职工的人数

select count(*) as `职工总人数` from worker;select count(`姓名`) from worker; 	-- 两种方式均可

在这里插入图片描述

题四：

查询最高和最低工资

mysql"> select max(`工资`) from worker;select min(`工资`) from worker;

在这里插入图片描述

题五：

查询职工的平均工资和总工资

mysql">select sum(`工资`) from worker;
select avg(`工资`) from worker;

在这里插入图片描述

题六：

创建一个只有职工号，姓名和参加工作的新表，名为工作日期表

create table `工作日期表` select `职工号`, `姓名`, `工作时间` from worker;

在这里插入图片描述

select * from 工作日期表;

在这里插入图片描述

题七：

显示所有职工的年龄

 select 姓名,出生日期 from worker;

在这里插入图片描述

题八：

列出所有姓李的职工号、姓名和出生日期

mysql">select 职工号,姓名,出生日期 from worker where 姓名 like '李%';

在这里插入图片描述

题九：

列出1960年后出生的职工的姓名参加工作日期

 select 姓名,工作时间 from worker where year(出生日期) > 1960;

在这里插入图片描述

题十：

列出工资在 3000-4000 之间的所有职工姓名

select 姓名 from worker where 工资 >=3000 and 工资 <= 4000;

在这里插入图片描述

题十一：

列出所有姓陈和姓李的员工姓名

mysql">select 姓名 from worker where 姓名 like '陈%' or 姓名 like '李%' ;

在这里插入图片描述

题十二：

列出所有部门号为2和3的职工号、姓名、是否党员

 select 职工号,姓名,政治面貌 from worker where 部门号 = 102 or 部门号 = 103;

在这里插入图片描述

题十三：

将职工表worker中的职工按出生先后顺序排序

 select * from worker order by 出生日期 ;

在这里插入图片描述

题十四：

显示工资最高的前三名职工的职工号和姓名

 select 职工号,姓名 from worker order by 工资 desc limit 3;

在这里插入图片描述

题十五：

求各部门党员人数

select 部门号 ,count(*) as 人数 from worker where 政治面貌 = '党员' group by 部门号;

在这里插入图片描述

题十六：

统计各部门的总工资和平均工资

mysql">select 部门号 , sum(工资)as 总工资,avg(工资)as 平均工资 from worker group by 部门号;

在这里插入图片描述

题十七：

列出总人数大于等于4的部门号和总人数

 select 部门号,count(*)as 总人数 from worker group by 部门号 having count(*) >=4;

在这里插入图片描述

二、多表查询

1、创建student和score表

mysql">-- student表
CREATE TABLE student (id INT(10) NOT NULL UNIQUE PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(4),birth YEAR,department VARCHAR(20),address VARCHAR(50)
);
-- score表CREATE TABLE `score` (`id` INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,`stu_id` INT(10) NOT NULL,`c_name` VARCHAR(20),`grade` INT(10)
);

2、添加student和score表添加记录

-- student表
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
-- score表
INSERT INTO score VALUES(NULL,901, '计算机',98);INSERT INTO score VALUES(NULL,901, '英语', 80);INSERT INTO score VALUES(NULL,902, '计算机',65);INSERT INTO score VALUES(NULL,902, '中文',88);INSERT INTO score VALUES(NULL,903, '中文',95);INSERT INTO score VALUES(NULL,904, '计算机',70);INSERT INTO score VALUES(NULL,904, '英语',92);INSERT INTO score VALUES(NULL,905, '英语',94);INSERT INTO score VALUES(NULL,906, '计算机',90);INSERT INTO score VALUES(NULL,906, '英语',85);

3、查询student表的所有记录

select * from student;

在这里插入图片描述

4、查询student表第二条到第四条记录

mysql">select * from student limit 1, 3;

在这里插入图片描述

5、从student表查询所有学生的学号（id）、姓名(name)、院系（department）：

select `id`, `name`, `department` from student;

在这里插入图片描述

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

mysql">select * from student where department like '计算机系' or department like '英语系';

在这里插入图片描述

7、从student表中查询年龄18-40岁的信息

mysql">select * from student where 2025 - birth between 18 and 40;

在这里插入图片描述

8、从student表中查询每个院系有多少人

mysql">select department ,count(*)as 总人数 from student group by department;

在这里插入图片描述

9、从score 表中查询每个科目的最高分

mysql">select c_name ,max(grade) from score group by c_name;

在这里插入图片描述

10、查询李四的考试科目（c_name）和考试成绩（grade）：

select c_name,grade from score where stu_id =(select id from student where name = '李四');

在这里插入图片描述

11、用连接的方式查询所有学生的信息和考试信息:

select * from student ,score where student.id = score.stu_id;

在这里插入图片描述

12、计算每个学生的总成绩

mysql">select stu_id ,sum(grade)as 总成绩 from score group by stu_id ;

在这里插入图片描述

13、计算每个考试科目的平均成绩

select c_name, avg(grade) as `平均成绩` from score group by c_name;

在这里插入图片描述

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

select * from student join score on student.id=score.stu_id where c_name='计算机' and grade<95;

在这里插入图片描述

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

select * from student where id in (select stu_id from score where c_name='计算机') and id in (select stu_id from score where c_name='英语');

在这里插入图片描述

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

mysql">select * from score where c_name = '计算机' order by grade desc;

在这里插入图片描述

17、从student表和score表中查询出学生的学号，然后合并查询结果

mysql">select id from student union select stu_id from score;

在这里插入图片描述

18、查询姓张或姓王的同学的姓名，院系和考试课目及成绩

select name ,department ,c_name ,grade from student ,score where student.id =score.stu_id and name like '张%' or name like '王%';

在这里插入图片描述

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

select name ,2025 - birth as age,department ,c_name,grade from student ,score where student.id = score.stu_id and address like '湖南%';

在这里插入图片描述