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);