SQL模糊查询及相关练习

SQL

存储过程

- 存储过程详解

简单应用

• 用下面的方法可以将某个字段变成索引，
• alter table TbEmp add constraint fk_dno foreign key(dno) references TbDept(deptno)
• decimal(a,b) 数字位数为a,小数位数为b
• tinyint 整数位数最大为4
• distinct 去重
• limit(a,b) 从索引为a开始取，取b个值

模糊查询

• 转自星朝的博客园
• SQL模糊查询，使用like比较关键字，加上SQL里的通配符，请参考以下：
• 1、LIKE’Mc%’ 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
• 2、LIKE’%inger’ 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
• 3、LIKE’%en%’ 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
• 4、LIKE’_heryl’ 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
• 5、LIKE’[CK]ars[eo]n’ 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
• 6、LIKE’[M-Z]inger’ 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
• 7、LIKE’M[^c]%’ 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

练习题：

• 

create database HR charset utf8;

use HR;

create table TbDept(
deptno int not null,
dname varchar(30) not null,
dloc varchar(40),
primary key(deptno)
);

create table TbEmp(
empno int not null,
ename varchar(20) not null,
job varchar(30),
mgr int,
sal float,
dno int,
primary key(empno),
foreign key(dno) references TbDept(deptno)
);

insert into TbDept values (10, '会计部', '北京');
insert into TbDept values (20, '研发部', '成都');
insert into TbDept values (30, '销售部', '重庆');
insert into TbDept values (40, '运维部', '深圳');

insert into TbEmp values (7800, '张三丰', '总裁', null, 9000, 20);
insert into TbEmp values (2056, '乔峰', '分析师', 7800, 5000, 20);
insert into TbEmp values (3088, '李莫愁', '设计师', 2056, 3500, 20);
insert into TbEmp values (3211, '张无忌', '程序员', 2056, 3200, 20);
insert into TbEmp values (3233, '丘处机', '程序员', 2056, 3400, 20);
insert into TbEmp values (3251, '张翠山', '程序员', 2056, 4000, 20);
insert into TbEmp values (5566, '宋明月', '会计师', 7800, 4000, 10);
insert into TbEmp values (5234, '郭靖', '出纳', 5566, 2000, 10);
insert into TbEmp values (3344, '黄蓉', '销售主管', 7800, 3000, 30);
insert into TbEmp values (1359, '胡一刀', '销售员', 3344, 1800, 30);
insert into TbEmp values (4466, '苗人凤', '销售员', 3344, 2500, 30);
insert into TbEmp values (3244, '欧阳锋', '程序员', 3088, 3200, 20);
insert into TbEmp values (3577, '杨过', '会计师', 5566, 2200, 10);
insert into TbEmp values (3588, '朱九真', '会计师', 5566, 2200, 10);



-- 1.查询薪水最高的员工姓名和工资
select ename, sal from TbEmp t join 
(select max(sal) as m from TbEmp) as t1 on t1.m=t.sal;

-- 2.查询员工的姓名和年薪(月薪*12)
select ename, sal * 12 as '年薪' from TbEmp;

-- 3.查询有员工的部门的编号和人数
select dno as '部门编号' , count(*) as '人数' 
from TbEmp t1  group by(dno);

-- 4.查询所有部门的名称和人数
-- ifnull表示如果前面的变量如果为空，就用后面的变量代替
select t1.dname, ifnull(t2.c,0) from tbdept t1
-- left join 表示以左边的表格为准来查询数据
left join
(select dno, count(*) as c from tbemp group by dno) t2
on t1.deptno = t2.dno;

-- 5.查询薪水最高的员工（boss除外）的姓名和工资
select ename, sal from TbEmp t  join 
(select max(sal) as m from TbEmp where not job='总裁') as t1 
on t1.m=t.sal;

select ename, sal from tbemp t1
where t1.sal =
(select max(t.sal) as maxsal from tbemp t
where t.mgr is not null);

-- 6.查询薪水超过平均薪水的员工的姓名和工资
select ename as '员工姓名', sal as '员工工资' from TbEmp where sal>
(select avg(sal) from TbEmp);

-- 7.查询薪水超过其所在部门的平均薪水的员工和姓名，部门编号和工资
select ename as '员工姓名', dno as '部门编号', sal as '员工工资' from
 TbEmp t where sal>(select avg(sal) from TbEmp t2 
 where t2.dno=t.dno) order by(-sal);

select * from tbdept t3
join(
select t1.dno, ename, sal from tbemp t1
join (
select avg(sal) as avgsal, dno from tbemp group by dno) t2
on t1.dno = t2.dno and t1.sal > avgsal) t4
on t3.deptno=t4.dno;

-- 8.查询部门中薪水最高的人的姓名，工资和所在部门名称
select ename as '员工姓名', dname as '部门名称', sal as '员工工资' 
from TbEmp t join TbDept t1 on t1.deptno=t.dno 
where sal=(select max(sal) from TbEmp t2 
where t2.dno=t.dno);

-- 9.查询主管的姓名和职位
select ename as '主管姓名', job as '职位' from TbEmp t 
join (select mgr from TbEmp) t1 
on t1.mgr=t.empno group by(job);

select * from tbemp
-- 用in的方法比较好
where empno in (
#distinct去重
select distinct mgr from tbemp where mgr is not null);

-- 10.查询薪水排名的前三的员工姓名和工资
select ename as '员工姓名', sal as '员工工资' 
from TbEmp order by(-sal) limit 3 ;

-- 11.求薪水排在第4-8名的员工
select ename as '员工姓名', sal as '员工工资' 
from TbEmp order by(-sal) limit 3,5 ;
select ename as '员工姓名', sal as '员工工资' 
from TbEmp order by(-sal) limit 5 offset 3;

"""学生选课系统

1. 创建学生选课系统
2. 切换数据库
3. 创建学生表 TbStudent
主键stuid ，姓名stuname，
性别stusex，生日stubirth，电话stutel，住址stuaddr
照片stuphoto（以二进制存）
4. 创建课程表TbCourse
主键cosid， 课程名称cosname，学分coscredit，课程描述cosintro
5. 学生选课记录表TbSC
主键scid，学生外键sid ，课程外键cid，创建日期scdate， 分数score

"""

-- 创建数据库
create database select_cl charset utf8;
-- 使用数据库
use select_cl;
-- 如果表存在删除表
drop table if exists TbStudent;
-- 创建学生表
create table TbStudent(
stuid int not null,
stuname varchar(20) not null,
stusex  bit default 1,
stubirth datetime not null,
stutel varchar(11),
stuaddr varchar(255),
stuphoto longblob,
primary key(stuid)
);
-- 删除字段
alter table tbstudent drop column stutel;

drop table if exists Tbcourse;
-- 创建课程表
create table TbCourse(
cosid int not null,
cosname varchar(20) not null,
coscredit tinyint not null,
cosintro varchar(255) ,
primary key(cosid)
);
-- 创建选课表
drop table if exists tbsc;
create table TbSC(
scid int primary key auto_increment,
sid int,
cid int,
scdate datetime not null,
score float
);
-- 关联外键，on delete cascade on update cascade
表示在删除学生表数据时，选课表对应数据也删除
alter table tbsc add constraint fk_sid foreign key(sid) 
references tbstudent(stuid) 
on delete cascade on update cascade;
-- on delete set null on update cascade
表示在删除课程表数据时，选课表对应数据变成空
alter table tbsc add constraint fk_cid foreign key(cid) 
references tbcourse(cosid) 
on delete set null on update cascade;
-- 插入学生表数据
insert into tbstudent values (1001, '张三丰',
default, '1978-1-1', '成都市一环路西二段17号', null);
insert into tbstudent (stuid, stuname, stubirth) values(1002, '郭靖', '1980-2-2');
insert into tbstudent (stuid, stuname, stusex, stubirth, stuaddr)
 values (1003, '黄蓉', 0, '1982-3-3', '成都市二环路南四段123号');
insert into tbstudent values(1004, '张无忌', 1, '1990-4-4', null, null);
insert into tbstudent values(1005, '丘处机', 1, '1983-5-5', 
'北京市海淀区宝盛北里西区28号', null);
insert into tbstudent values (1006, '王处一', 1, '1985-6-6',
 '深圳市宝安区宝安大道5010号', null);
insert into tbstudent values (1007, '刘处玄', 1, '1987-7-7',
 '郑州市金水区纬五路21号', null);
insert into tbstudent values (1008,
'孙不二', 0, '1989-8-8', '武汉市光谷大道61号', null),
(1009, '平一指', 1, '1992-9-9', '西安市雁塔区高新六路52号', null),
(1010, '老不死', 1, '1993-10-10', '广州市天河区元岗路310号', null),
(1011, '王大锤', 0, '1994-11-11', null, null),
(1012, '隔壁老王', 1, '1995-12-12', null, null),
(1013, '郭啸天', 1, '1977-10-25', null, null);

-- 删除学生数据
delete from TbStudent where stuid=1004;

-- 更新学生数据
update tbstudent set stubirth='1980-12-12', stuaddr=
'上海市宝山区同济支路199号' where stuid=1002;

-- 添加课程记录
insert into tbcourse values(1111, 'C语言程序设计', 3, 
'大神级讲师授课需要抢座'), (2222,
'Java程序设计', 3,null),
(3333, '数据库概论', 2, null),
(4444, '操作系统原理', 4, null);

-- 添加学生选课记录
insert into tbsc values
(default,1001,1111, '2016-9-1', 95),
(default,1002,1111, '2016-9-1', 94),
(default,1001,2222, now(), null),
(default,1001,3333, '2017-3-1', 85),
(default,1001,4444, now(), null),
(default,1002,4444, now(), null),
(default,1003,2222, now(), null),
(default,1003,3333, now(), null),
(default,1005,2222, now(), null),
(default,1006,1111, now(), null),
(default,1006,2222, '2017-3-1', 80),
(default,1006,3333, now(), null),
(default,1006,4444, now(), null),
(default,1007,1111, '2016-9-1', null),
(default,1007,3333, now(), null),
(default,1007,4444, now(), null),
(default,1008,2222, now(), null),
(default,1010,1111, now(), null);

-- 1.查询所有学生信息
select * from tbstudent;

-- 2.查询所有课程名称及学分
select cosname, coscredit from tbcourse;

-- 3.查询所有女学生的姓名和出生日期
select stuname, stubirth from tbstudent where stusex=0;

-- 4.查询所有80后学生的姓名，性别和出生日期
select stuname, stusex, stubirth from tbstudent where stubirth between '1980-1-1' and '1989-12-31';

-- 5.查询姓王的学生的姓名和性别(模糊)
select stuname, stusex from tbstudent where stuname like'王%';

-- 6.查询姓郭名字总共两个字的学生的姓名（模糊）
select stuname from tbstudent where stuname like'郭_';

-- 7.查询姓郭名字总共三个字的学生的姓名（模糊）
select stuname from tbstudent where stuname like'郭__'; 

-- 8.查询名字中有王字的学生的姓名（模糊）
select stuname from tbstudent where stuname like'%王%';

-- 9.查询没有录入家庭地址和照片的学生姓名（多条件筛选和空值处理）
select stuname from tbstudent where stuaddr is
 null and stuphoto is null;

-- 10.查询学生选课的所有日期（去重）
select distinct scdate from tbsc;

-- 11.查询学生的姓名和生日按年龄从大到小排列(排序)
select stuname, stubirth from tbstudent 
order by (year(stubirth));

-- 12.查询所有录入了家庭地址的男学生的姓名、
出生日期和家庭住址按年龄从小到大排列（多条件筛选和排序）
select stuname, stubirth, stuaddr from tbstudent
where stusex=1 and 
stuaddr is not null order by (-year(stubirth));

-- 13.查询年龄最大的学生的出生日期（聚合函数）
select min(stubirth) from tbstudent;

-- 14.查询年龄最小的学生的出生日期（聚合函数）
select max(stubirth) from tbstudent;

-- 15.查询男女学生的人数（分组和聚合函数）
select count(*) as '学生人数', stusex as '性别' from tbstudent group by(stusex);

-- 16.查询课程编号为1111的课程的平均成绩（筛选和聚合函数）
select cid, avg(score)from tbsc where cid=1111;

-- 17.查询学号为1001的学生所有课程的总成绩（筛选和聚合函数）
select sid, sum(score) from tbsc where sid=1001;

-- 18.查询每个学生的学号和平均成绩，null值处理成0（分组和聚合函数）
select stuid as '学号', ifnull(avg(score), 0) as '平均成绩' 
from tbsc t
right join tbstudent s on s.stuid=t.sid
group by(stuid);

-- 19.查询平均成绩大于等于90分的学生的学号和平均成绩
select sid as '学号', avg(score) as a  from tbsc group by(sid) having a >= 90;

-- 20.查询年龄最大的学生的姓名
select stuname from tbstudent where stubirth=

(select min(stubirth) from tbstudent);

-- 21.查询选了两门以上的课程的学生姓名
select count(cid), stuname from tbsc t join tbstudent s on t.sid=s.stuid group by(sid) having count(cid) >=2;

-- 22.查询选课学生的姓名和平均成绩
select stuname, ifnull(avg(score), 0) from tbsc t
join tbstudent s on s.stuid=t.sid
group by(sid);

-- 23.查询学生姓名、所选课程名称和成绩
select stuname, cosname, ifnull(score, 0) from tbsc t
join tbstudent s on s.stuid=t.sid
join tbcourse c on c.cosid=t.cid;
-- 24.查询每个学生的姓名和选课数量
select stuname as '学生姓名', ifnull(count(cid), 0) as '选课数量'
from tbsc t
right join tbstudent s on s.stuid=t.sid
group by(stuname);