05. SQL演练
2024年10月28日大约 22 分钟
05. SQL演练
1.演练一
1. 数据准备
-- 数据库准备
drop table if exists students;
-- 创建表
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
);
-- 插入数据
insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');2. 练习
-- 查询基本语法：数据库操作
-- 1. 查询某些字段（姓名、性别、年龄）
select name, sex,age from students;
-- 2. 表起别名: 表 as 表别名 (这个语法为后面关联查询做准备，现在只需要知道这个语法即可)
select s.name, s.sex, s.age from students as s;
-- 3. 字段取别名: 原来的名字 as 新的名字
select name as 姓名, sex as 性别, age as 年龄 from students;
-- 4. 字段内容去重: distinct
select sex from students; -- 查所有
select distinct sex from students; -- 去重
-- 5. 条件查询: where
-- 5.1 条件查询-比较运算符 >, <, <=, >=, !=、<>
-- 查询小乔的年龄
select age from students where name='小乔';
-- 查询20岁以下的学生
select * from students where age < 20;
-- 查询家乡不在北京的学生 != 之间不能有空格
select * from students where hometown != '北京';
-- 查询学号是'007'的学生的身份证号
select card from students where studentNo='007';
-- 查询'1班'以外的学生信息
select * from students where class <> '1班';
-- 查询年龄大于20的学生的姓名和性别
select name, sex from students where age > 20;
-- 5.2 条件查询-逻辑运算符: and, or, not
-- 查询年龄等于18的女生的记录(and)
select * from students where age=18 and sex='女';
-- 查询出女学生或者是1班的学生
select * from students where sex='女' or class='1班';
-- 查询非天津学生的记录
select * from students where hometown != '天津';
select * from students where not hometown='天津';
-- 查询河南或河北的学生
select * from students where hometown='河南' or hometown='河北';
-- 查询'1班'的'上海'的学生
select * from students where class='1班' and hometown='上海';
-- 查询非20岁的学生
select * from students where not age=20;
select * from students where age!=20;
-- 5.3 条件查询-模糊查询: like, %, _
-- 查询姓孙的学生
select * from students where name like '孙%';
-- 查询姓孙且名字是一个字的学生
select * from students where name like '孙_';
-- 查询姓名以‘ 乔’ 结尾的学生
select * from students where name like '%乔';
-- 查询姓名中包含‘白’ 的学生
select * from students where name like '%白%';
-- 查询姓名为两个字的学生
select * from students where name like '__';
-- 查询姓‘百’且年龄大于20的学生
select * from students where name like '百%' and age > 20;
-- 查询学号以1结尾的学生
select * from students where studentNo like '%1';
-- 5.4 条件查询-范围查询: in, between ... and ...
-- in 表示在一个非连续的范围内
-- 查询家乡是北京或上海或广东的学生
select * from students where hometown='北京' or hometown='上海' or hometown='广东';
select * from students where hometown in ('北京', '上海', '广东');
-- between ... and ...表示在一个连续的范围内
-- 查询年龄为18至20的学生
select * from students where age >= 18 and age <= 20;
select * from students where age between 18 and 20;
-- 查询年龄为18或19或22的女生
select * from students where age in(18, 19, 22) and sex='女';
-- 查询年龄在20到25以外的学生
select * from students where age not between 20 and 25;
-- 5.5 条件查询-空判断: is null, is not null
-- 判断为空: is null
-- 查询没有填写身份证的学生
select * from students where card is null;
-- 判断非空: is not null
-- 查询填写了身份证的学生
select * from students where card is not null;
-- 5.6 排序: order by ... asc(默认)、desc
-- 查询所有学生信息， 按年龄从小到大排序
select * from students order by age;
-- 查询所有学生信息， 按年龄从大到小排序， 年龄相同时， 再按学号从小到大排序
select * from students order by age desc, studentNo;
-- 查询所有学生信息， 按班级从小到大排序， 班级相同时， 再按学号从小到大排序
select * from students order by class, studentNo;
-- 5.7 聚合函数: count, max, min, avg, sum
-- 查询学生总数
select name from students;
-- 统计name下有几个记录，个数
select COUNT(name) from students;
-- 不管统计哪个字段，这里例子都有12个
-- 统计总数，count(*)
select COUNT(*) from students;
-- 查询女生的最大年龄
select age from students where sex='女';
select MAX(age) from students where sex='女';
-- 查询1班的最小年龄
select class, age from students where class='1班';
select MIN(age) from students where class='1班';
-- 查询北京学生的年龄总和
select hometown, age from students where hometown='北京';
select SUM(age) from students where hometown='北京';
-- 查询女生的平均年龄
select sex, age from students where sex='女';
select AVG(age) from students where sex='女';
-- 查询年龄为最大值的学生的信息, 子查询可以解决
-- 聚合函数不能在 where 中使用
-- select * from students where age=max(age); -- err
-- 查询所有学生的最大年龄、 最小年龄、 平均年龄
select MAX(age), MIN(age), AVG(age) from students;
-- 一班共有多少个学生
select COUNT(*) from students where class='1班';
-- 查询3班年龄小于18岁的同学有几个
select COUNT(*) from students where class='3班' and age<18;
-- 5.8 分组查询
-- 查询各种性别的人数
select * from students;
select sex, MAX(age), COUNT(*), AVG(age) from students group by sex;
-- 统计各班级的人数，最小年龄
select class, COUNT(*), MIN(age) from students group by class;
-- 先以班级，再以性别分组
select class, sex, COUNT(*), MIN(age) from students group by class, sex;
-- 查询男生总人数， 通过分组实现
-- 分组后，再过滤
select sex, COUNT(*) from students group by sex having sex='男';
-- 查询每个班级男生的总记录数
select class, sex, COUNT(*) from students group by class, sex having sex='男';
-- 查询所有班级中不同性别的记录数大于1的信息
select class, sex, COUNT(*) from students group by class, sex having COUNT(*) > 1;
-- 查询各个班级学生的平均年龄、 最大年龄、 最小年龄
select class, COUNT(*), AVG(age), MAX(age), MIN(age) from students group by class;
-- 查询1班除外其他班级学生的平均年龄、 最大年龄、 最小年龄
select class, COUNT(*), AVG(age), MAX(age), MIN(age) from students group by class having class != '1班';
-- 5.9 分页-获取部分数据
-- select * from 表名 limit 起点(从0开始，代表第几页), 每一页显示的个数
-- 人的习惯，起点加1，因为它从0开始
select * from students limit 0, 5; -- 取从第1到第5条数据
select * from students limit 1, 5; -- 取从2~6条数据
select * from students limit 10, 5; -- 取从11~12条数据
-- select * from students limit (n-1)*m, m
-- 说明:
-- n表示显示第几页的数据
-- 页数 12/3 = 4
-- n = 1, 2, 3, 4
-- m表示每页显示多少条数据
-- m = 3, 每页显示3条数据
-- n = 1, m = 3
-- (1-1)*3 = 0
select * from students limit 0, 3;
-- n = 2, m = 3
-- (2-1)*3 = 3
select * from students limit 3, 3;
-- n = 3, m = 3
-- (3-1)*3 = 6
select * from students limit 6, 3;
-- n = 4, m = 3
-- (4-1)*3 = 9
select * from students limit 9, 3;
-- 查询第4到第6行学生信息
select * from students limit 3, 3;
-- 每页显示5条数据， 显示每一页的数据
select * from students limit 0, 5;
select * from students limit 5, 5;
select * from students limit 10, 5;
-- 已知总记录数和每页显示条数， 求总页数？
-- 总数为12， 每页显示3个数据，12/3=4页
-- 总数为12， 每页显示5个数据，如果除不尽，12/5取商 + 12. 演练二
1. 数据准备
-- 学科表
drop table if exists courses;
create table courses (
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
);
-- 插入学科数据
insert into courses values
('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');
-- 分数表
drop table if exists scores;
create table scores (
id int(10) unsigned primary key auto_increment,
courseNo int(10),
studentno varchar(10),
score tinyint(4)
);
-- 插入分数数据
insert into scores values
('1', '1', '001', '90'),
('2', '1', '002', '75'),
('3', '2', '002', '98'),
('4', '3', '001', '86'),
('5', '3', '003', '80'),
('6', '4', '004', '79'),
('7', '5', '005', '96'),
('8', '6', '006', '80');
-- 地区表
drop table if exists areas;
create table areas(aid int primary key, atitle varchar(20),pid int);
insert into areas values
('130000', '河北省', NULL),
('130100', '石家庄市','130000'),
('130400','邯郸市','130000'),
('130600', '保定市', '130000'),
('130700','张家口市', '130000'),
('130800', '承德市', '130000'),
('410000', '河南省', NULL),
('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),
('410500', '安阳市', '410000'),
('410700', '新乡市', '410000'),
('410800', '焦作市', '410000'),
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410301', '洛龙区', '410300');2. 练习
-- 1. 连接查询-内连接
-- 查询学生信息及学生的成绩(连接查询，不添加条件会进行笛卡尔积查询)
SELECT * FROM students INNER JOIN scores ON students.studentNo=scores.studentno;
-- students改名为stu, scores 改名为 sc
SELECT * FROM students AS stu INNER JOIN scores AS sc ON stu.studentNo=sc.studentno;
-- 可以省略as
SELECT * FROM students stu INNER JOIN scores sc ON stu.studentNo=sc.studentno;
-- 内连接另外的写法
SELECT * FROM students,scores; -- 笛卡尔积查询(多表查询，不添加条件)
SELECT * FROM students, scores WHERE students.studentNo=scores.studentno;
-- 查询课程信息及课程的成绩
SELECT * FROM courses INNER JOIN scores ON courses.courseNo=scores.courseNo;
-- 查询王昭君的成绩， 要求显示姓名、 课程号、 成绩
SELECT * FROM students INNER JOIN scores ON students.studentNo=scores.studentno
-- name和系统某些名字重名了，建议加反引号（不是单引号）
WHERE students.`name`='王昭君';
SELECT students.`name`, scores.courseNo, scores.score FROM students
INNER JOIN scores ON students.studentNo=scores.studentno
-- name和系统某些名字重名了，建议加反引号（不是单引号）
WHERE students.`name`='王昭君';
-- 查询学生信息及学生的课程对应的成绩
SELECT * FROM students INNER JOIN scores ON students.studentNo=scores.studentno
INNER JOIN courses ON scores.courseNo=courses.courseNo;
-- 查询王昭君的数据库成绩， 要求显示姓名、 课程名、 成绩
SELECT * FROM students INNER JOIN scores ON students.studentNo=scores.studentno
INNER JOIN courses ON scores.courseNo=courses.courseNo
WHERE students.name='王昭君';
SELECT students.name, courses.name, scores.score FROM students
INNER JOIN scores ON students.studentNo=scores.studentno
INNER JOIN courses ON scores.courseNo=courses.courseNo
WHERE students.name='王昭君' AND courses.name='数据库';
-- 查询所有学生的数据库成绩， 要求显示姓名、 课程名、 成绩
SELECT students.name, courses.name, scores.score FROM students
INNER JOIN scores ON students.studentNo=scores.studentno
INNER JOIN courses ON scores.courseNo=courses.courseNo
WHERE courses.name='数据库';
-- 查询男生中最高成绩， 要求显示姓名、 课程名、 成绩
SELECT students.name, courses.name, scores.score FROM students
INNER JOIN scores ON students.studentNo=scores.studentno
INNER JOIN courses ON scores.courseNo=courses.courseNo
WHERE students.sex='男'
ORDER BY scores.score DESC -- 大--> 小
LIMIT 0, 1; -- 分页取出第1个
-- 2. 连接查询-左连接
-- 查询所有学生的成绩， 包括没有成绩的学生
SELECT * FROM students LEFT JOIN scores ON students.studentNo=scores.studentNo;
-- 查询所有学生的成绩， 包括没有成绩的学生， 需要显示课程名
SELECT * FROM students LEFT JOIN scores ON students.studentNo=scores.studentNo
LEFT JOIN courses ON courses.courseNo=scores.courseNo;
-- 2. 连接查询-右连接
-- 查询所有学生的成绩， 包括没有成绩的学生
SELECT * FROM scores RIGHT JOIN students ON students.studentNo=scores.studentNo;
-- 查询所有学生的成绩， 包括没有成绩的学生， 需要显示课程名
-- 1. 先成绩和课程右连接
SELECT * FROM scores RIGHT JOIN courses ON scores.courseNo=courses.courseNo
-- 2. 上一步继续和学生右连接
RIGHT JOIN students ON students.studentNo=scores.studentNo
-- 3. 自关联数据
SELECT * FROM areas AS a1
INNER JOIN areas AS a2
ON a1.aid=a2.pid;
-- 查询河南省所有的市
SELECT * FROM areas AS a1
INNER JOIN areas AS a2
ON a1.aid=a2.pid
WHERE a1.atitle='河南省';
-- 查询郑州市的所有的区
SELECT * FROM areas AS a1
INNER JOIN areas AS a2
ON a1.aid=a2.pid
WHERE a1.atitle='郑州市';
-- 查询河南省的所有的市区
SELECT * FROM areas AS a1
INNER JOIN areas AS a2 ON a1.aid=a2.pid
INNER JOIN areas AS a3 ON a2.aid=a3.pid
WHERE a1.atitle='河南省';
-- 查询河南省的所有的市区，包活没有区的
SELECT * FROM areas AS a1
INNER JOIN areas AS a2 ON a1.aid=a2.pid
LEFT JOIN areas AS a3 ON a2.aid=a3.pid
WHERE a1.atitle='河南省';
-- 查询河北省所有的市的信息
SELECT * FROM areas AS a1
INNER JOIN areas AS a2
ON a1.aid=a2.pid
WHERE a1.atitle='河北省';
-- 查询出洛阳市所有的区的信息
SELECT * FROM areas AS a1
INNER JOIN areas AS a2 ON a1.aid=a2.pid
LEFT JOIN areas AS a3 ON a2.aid=a3.pid
WHERE a1.atitle='洛阳市';
SELECT * FROM areas AS a1
INNER JOIN areas AS a2
ON a1.aid=a2.pid
WHERE a1.atitle='洛阳市';
-- 4. 子查询
-- 例1：查询王昭君的成绩，要求显示成绩（标量子查询）
SELECT studentNo FROM students WHERE NAME='王昭君';
SELECT * FROM scores WHERE studentno='001';
-- select studentNo from students where name='王昭君'为子查询
-- 子查询的结果作为主查询的条件
-- select studentNo from students where name='王昭君'结果为001
-- 等价于 select * from scores where studentno='001';
SELECT score FROM scores WHERE studentno=(SELECT studentNo FROM students WHERE NAME='王昭君');
-- 例2：查询18岁的学生的成绩，要求显示成绩（列子查询）
-- 先找18岁学生的学号， 结果为列结果
-- 002
-- 006
SELECT studentNo FROM students WHERE age=18;
SELECT score FROM scores WHERE studentno IN (SELECT studentNo FROM students WHERE age=18);
-- 例3：查询和王昭君同班、同龄的学生信息（行子查询）
-- 查王昭君的班级和年龄, 结果为行结果 （'1班', 20）
SELECT class, age FROM students WHERE NAME='王昭君';
SELECT * FROM students WHERE (class, age)=(SELECT class, age FROM students WHERE NAME='王昭君');
-- 子查询充当数据源
-- 例1：查询数据库和系统测试的课程成绩
SELECT * FROM courses WHERE NAME IN ('数据库', '系统测试');
-- 内连接
SELECT * FROM scores AS sc INNER JOIN (
SELECT * FROM courses WHERE NAME IN ('数据库', '系统测试')
) AS co ON co.courseNo=sc.courseNo;
SELECT co.name, sc.score FROM scores AS sc INNER JOIN (
SELECT * FROM courses WHERE NAME IN ('数据库', '系统测试')
) AS co ON co.courseNo=sc.courseNo;
-- 子查询特定的关键字
-- in 和 =any等价， some是any的别名
-- 例2：查询18岁的学生的成绩，要求显示成绩（列子查询）
SELECT score FROM scores WHERE studentno IN (SELECT studentNo FROM students WHERE age=18);
SELECT score FROM scores WHERE studentno =ANY(SELECT studentNo FROM students WHERE age=18);
-- not in 和 !=all等价
-- 查询不是18岁的成绩
SELECT score FROM scores WHERE studentno NOT IN (SELECT studentNo FROM students WHERE age=18);
SELECT score FROM scores WHERE studentno != ALL(SELECT studentNo FROM students WHERE age=18);
-- 1、 查询大于平均年龄的学生
-- 先查平均年龄
SELECT AVG(age) FROM students;
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
-- 2、 查询年龄在18-20之间的学生的成绩
-- 先查18-20之间的学生的学号
SELECT studentNo FROM students WHERE age BETWEEN 18 AND 20;
-- 子查询充当条件
SELECT * FROM scores WHERE studentno IN (SELECT studentNo FROM students WHERE age BETWEEN 18 AND 20);
-- 子查询充当数据源
SELECT * FROM scores AS sc INNER JOIN (SELECT studentNo FROM students WHERE age BETWEEN 18 AND 20) AS st ON st.studentNo=sc.studentNo;3. 演练三
1. 数据准备
/*
**创建部门表
*/
drop table if exists departments;
create table departments (
deptid int(10) primary key,
deptname varchar(20) not null -- 部门名称
);
insert into departments values ('1001', '市场部');
insert into departments values ('1002', '测试部');
insert into departments values ('1003', '开发部');
/*
**创建员工表
*/
drop table if exists employees;
create table employees (
empid int(10) primary key,
empname varchar(20) not null, -- 姓名
sex varchar(4) default null, -- 性别
deptid int(20) default null, -- 部门编号
jobs varchar(20) default null, -- 岗位
politicalstatus varchar(20) default null, -- 政治面貌
leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');
insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众', null);
insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');
insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);
insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');
insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');
insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');
insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');
insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');
insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');
insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);
/*
**创建工资表
*/
drop table if exists salary;
create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- 工资
);
insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');2. 练习
-- 1、 列出总人数大于4的部门号和总人数。
-- 部门人数 > 4
-- 分组，以 部门号 分组，分完组，需要过滤， > 4
-- 显示哪些字段 部门号， 总人数
select deptid, count(*) from employees group by deptid having count(*) > 4;
-- 2、 列出开发部和和测试部的职工号、 姓名。
-- 有2个表，部门表有开发部和和测试部， 员工表有职工号、 姓名
-- 部门表departments 和 员工表employees 如何关联 ===》 部门号 deptid
-- a) 先查 部门表departments中 开发部和和测试部 对应的部门id
-- b) 员工表再和上面关联
select deptid from departments where deptname in ('开发部', '测试部');
-- 员工表再和子查询的结果关联
select * from employees as emp inner join (
select deptid from departments where deptname in ('开发部', '测试部')
) as dept on dept.deptid=emp.deptid;
select emp.empid, emp.empname from employees as emp inner join (
select deptid from departments where deptname in ('开发部', '测试部')
) as dept on dept.deptid=emp.deptid;
-- 3、 求出各部门党员的人数， 要求显示部门名称。
-- 各部门， 分组 group by 部门号
-- 先 过滤 (员工表employees)， 再分组
-- 要求显示部门名称， 部门名称在部门表departments
-- a. 员工表employees和部门表departments关联
select emp.deptid, dept.deptname, count(*) from employees as emp
inner join departments as dept on dept.deptid=emp.deptid
where emp.politicalstatus='党员' -- 内连接时，只连接党员
group by emp.deptid -- 再来分组， having 过滤的内容一定是分组后的字段内容
-- 4、 列出市场部的所有女职工的姓名和政治面貌。
select * from employees as emp
inner join departments as dept on dept.deptid=emp.deptid;
select * from employees as emp
inner join departments as dept on dept.deptid=emp.deptid
where dept.deptname='市场部' and emp.sex='女';
-- 5、 显示所有职工的姓名、 部门名和工资数。
select * from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
select emp.empname, dept.deptname, sa.salary from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
-- 6、 显示各部门名和该部门的职工平均工资。
-- 分组后，显示的是分组的字段，或聚合函数
select dept.deptid, dept.deptname, avg(sa.salary) from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
-- 7、 显示工资最高的前3名职工的职工号和姓名。
select * from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc -- 降序
limit 3
select emp.empid, emp.empname, sa.salary from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc -- 降序
limit 3
-- 8、 列出工资在1000－2000之间的所有职工姓名。
select emp.empid, emp.empname, sa.salary from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
where sa.salary between 1000 and 2000
-- 9、 列出工资比王昭君高的员工。
-- 找王昭君工资，这个是子查询
select sa.salary from employees as emp inner join salary as sa
on sa.empid=emp.empid where emp.empname='王昭君'
select sa.salary from employees as emp
inner join departments as dept on emp.deptid=dept.deptid
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
-- 子查询作为条件
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary > 3000
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary > (
select sa.salary from employees as emp inner join salary as sa
on sa.empid=emp.empid where emp.empname='王昭君'
)
-- 10、 列出每个部门中工资小于本部门平均工资的员工信息。
-- 本部门平均工资是多少
-- 分组后，显示分组的字段，或聚合函数
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
select * from employees as emp
inner join salary as sa on sa.empid=emp.empid
inner join (
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
) as temp on emp.deptid=temp.deptid
where sa.salary < temp.avg_salary
select emp.empname, emp.deptid, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
inner join (
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
) as temp on emp.deptid=temp.deptid
where sa.salary < temp.avg_salary4. 演练四
1. 数据准备
-- 建学生信息表student
create table student(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
-- 建立教师表
create table teacher (
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
-- 建立课程表course
create table course (
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
-- 建立成绩表
CREATE TABLE score (
sno VARCHAR (20) NOT NULL ,
FOREIGN KEY (sno) REFERENCES student (sno),
cno VARCHAR (20) NOT NULL,
FOREIGN KEY (cno) REFERENCES course (cno),
degree DECIMAL
);
-- 添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','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','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
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. 练习
-- 1. 查询Student表中的所有记录的Sname、 Ssex和Class列。
select sname,ssex,class from student;
-- 2. 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
-- 3. 查询Student表的所有记录。
select * from student;
-- 4. 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree>=60 and degree<=80;
select * from score where degree between 60 and 80;
-- 5. 查询Score表中成绩为85， 86或88的记录。
select * from score where degree in(85,86,88);
-- 6. 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class='95031' or ssex='女';
-- 7. 以Class降序查询Student表的所有记录。
select * from student order by class desc;
-- 8. 以Cno升序、 Degree降序查询Score表的所有记录。
select * from score order by cno ,degree desc;
-- 9. 查询“95031”班的学生人数。
select count(*) from student where class='95031';
-- 10. 查询Score表中的最高分的学生学号和课程号。 （子查询或者排序）
select * from score order by degree desc -- 降序
limit 1 -- 取第1个
select sno, cno from score order by degree desc -- 降序
limit 1 -- 取第1个
-- 找最高的分数
select max(degree) from score
select sno, cno from score where degree = 92
select sno, cno from score where degree = (select max(degree) from score)
-- 12. 查询每门课的平均成绩。
select cno, avg(degree) from score group by cno
-- 13. 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
-- 分组，找那个组名字3开头的课程， 同时，这个组>=5人, 算出这个组的平均分
select cno, avg(degree) from score group by cno
having count(*)>=5 and cno like '3%'
-- 14. 查询分数大于70， 小于90的Sno列。
select sno, degree from score where degree>70 and degree<90
-- 15. 查询所有学生的Sname、 Cno和Degree列。
select * from student as stu
inner join score as sc on stu.sno=sc.sno
select stu.sname, sc.cno, sc.degree from student as stu
inner join score as sc on stu.sno=sc.sno
-- 16. 查询所有学生的Sno、 Cname和Degree列。
select * from score as sc
inner join course as co on sc.cno=co.cno
-- 17. 查询“95031”班学生的平均分。
select avg(sc.degree) from student as stu
inner join score as sc on stu.sno=sc.sno
where class='95031'
-- 18. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
-- 1. 先查“109”号同学成绩
-- 2. 选修“3-105”课程所有成绩只要 > 上一步
-- 1. 先查“109”号同学成绩
-- 这个有4个，取最大那个, 只要>这个最大值，比所有都大
select max(degree) from score where sno='109'
-- 2. 选修“3-105”课程所有成绩只要 > 上一步
select * from student as stu
inner join score as sc on stu.sno=sc.sno
where sc.degree > 81
select * from student as stu
inner join score as sc on stu.sno=sc.sno
where sc.degree > (
select max(degree) from score where sno='109'
) and sc.cno='3-105'
-- 19. 查询成绩高于学号为“109”、 课程号为“3-105”的成绩的所有记录。
-- 1. 先查“109”号同时课程号为“3-105”， 同学成绩
select max(degree) from score where sno='109' and cno='3-105'
-- 2. 学生成绩 > 上一步
select * from student as stu
inner join score as sc on stu.sno=sc.sno
where sc.degree > (
select max(degree) from score where sno='109' and cno='3-105'
)
-- =any 等价于 in
-- !=all 等价于 not in
-- >any >=any <any
-- >all <all5. TPShop数据库演练
-- 在数据库中查询出商品分类类目
-- 找所有的
select mobile_name from tp_goods_category
-- 显示的 is_show为1， 不显示为0
select mobile_name, is_show from tp_goods_category where parent_id=0
select mobile_name from tp_goods_category where parent_id=0 and is_show=1
-- 查询出手机数码的分类类目
-- 如何找出31
select name from tp_goods_category where parent_id=31
-- 手机数码的id就是31
select id from tp_goods_category where mobile_name='手机数码'
-- 上一步的子查询充当条件
select name from tp_goods_category where parent_id=(
select id from tp_goods_category where mobile_name='手机数码'
)
-- 查询出手机通讯的分类类目
-- 如何找出32
select name from tp_goods_category where parent_id=32
-- 手机通讯的id就是32
select id from tp_goods_category where name='手机通讯'
-- 上一步的子查询充当条件
select name from tp_goods_category where parent_id=(
select id from tp_goods_category where name='手机通讯'
)
-- 查询出Tpshop中当前用户的订单信息
-- 13800138006
-- 1. 找到登陆用户的user_id, 通过用户手机号过滤
select user_id from tp_users where mobile='13800138006'
-- 2. 找订单，通过user_id过滤
select * from tp_order where user_id = (
select user_id from tp_users where mobile='13800138006'
)
-- 3. 查询订单的order_id
select order_id from tp_order where user_id = (
select user_id from tp_users where mobile='13800138006'
)
-- 4. 查商品信息，通过order_id找
select * from tp_order_goods where order_id in (
select order_id from tp_order where user_id = (
select user_id from tp_users where mobile='13800138006'
)
)
-- 查询出Tpshop当前用户中的购物车信息
-- 13800138006
-- 1. 找到登陆用户的user_id, 通过用户手机号过滤
select user_id from tp_users where mobile='13800138006'
select * from tp_cart where user_id = 8
select * from tp_cart where user_id = (
select user_id from tp_users where mobile='13800138006'
)
select nickname, pay_points from tp_users where mobile='13800138006'
-- 通过SQL语句修改Tpshop当前用户的昵称为 test001
-- 通过SQL语句将用户的积分值修改为20000
update tp_users set nickname='test001', pay_points=20000 where mobile='13800138006'