Mysaql练习题

举例一:

– 如果存在名为school的数据库就删除它

drop database if exists school;

– 创建名为school的数据库并设置默认字符集为utf8
create database school default charset utf8;
– 切换到school数据库
use school;

– 创建学生表（tb_student)
create table tb_student

(
– not null 非空约束
stuid int not null,
– varchar 长度可变的字符串
stuname varchar(4) not null,
– bit 要么是1要么是0,default 1 默认值约束
stusex bit default 1,
– 最多输入50个字符
stuaddr varchar(50),
– 主键语句
primary key (stuid)

);
– 如果表存在就删除它
– drop table if exists tb_student;
– char固定长度，修改名为tb_student的列
alter table tb_student add column stutel char(11)
– 删除添加列
– alter table tb_student drop column stutel;
– 修改设置stutel列不允许重复 (唯一约束)
alter table tb_student add CONSTRAINT uni_tel unique(stutel);

– 插入数据
insert into tb_student values(1001,‘小红’,1,‘四川成都’,‘13550159164’);
insert into tb_student(stuid, stuname, stusex) values(1002,‘元芳’,1);
insert into tb_student values
(1003,‘小白’,1,‘四川绵阳’,‘13980834477’);
(1004,‘白洁’,1,‘四川广元’,‘13980834447’);
(1005,‘小黑’,1,‘湖南长沙’,‘13980834377’);
(1006,‘冷面’,1,‘四川巴中’,‘null’);
– 删除tb_student表中指定的数据
– delete from tb_student where stuid=1002;
– 同时删除多个学生
– delete from tb_student where stuid in (1001,1003);
– delete from tb_student where stuid=1001 or stuid=1003;
– 删除1004到1010;
– delete from tb_student where stuid between 1004 and 1010;
– 清楚所有内容
– truncate tabel tb_student;

– 更新tb——stdudent表中指定的数据
update tb_student set stuaddr=‘湖南长沙’,stutel=‘13980834379’
where stuid=1001, stuid=1003;

– 查询所有行所有列
select * from tb_student;

– 设计表与表之间的关系(实体关系ER图)
– 如果两张表中有多对多的情况要多写一张表

举例二:

drop database if exists SRS;
create database SRS default charset utf8;

– 切换到SRS
use SRS;

– 创建学院表
create table tb_college
(
collid int not null auto_increment comment ‘学院编号’,
collname varchar(50) not null comment ‘学院名称’,
collmaster varchar(20) not null comment ‘院长姓名’,
collweb varchar(511) default ‘’ comment ‘学院网站’,
primary key (collid)
);

– 添加唯一性约束
alter table tb_college add constraint uni_college_collname unique (collname);
– alter table tb_college drop index uni_college_collname;

– 创建学生表
create table tb_student
(
stuid int not null comment ‘学号’,
stuname varchar(20) not null comment ‘学生姓名’,
stusex bit default 1 comment ‘性别’,
stubirth date not null comment ‘出生日期’,
stuaddr varchar(255) default ‘’ comment ‘籍贯’,
collid int not null comment ‘所属学院编号’,
primary key (stuid)
);

– 添加外键约束
alter table tb_student add constraint fk_student_collid
foreign key (collid) references tb_college (collid);

– 创建教师表
create table tb_teacher
(
teacherid int not null comment ‘教师工号’,
tname varchar(20) not null comment ‘教师姓名’,
ttitle varchar(10) default ‘’ comment ‘职称’,
collid int not null comment ‘所属学院编号’
);

– 添加主键约束
alter table tb_teacher add constraint pk_teacher
primary key (teacherid);

– 添加外键约束
alter table tb_teacher add constraint fk_teacher_collid
foreign key (collid) references tb_college (collid);

– 创建课程表
create table tb_course
(
courseid int not null comment ‘课程编号’,
cname varchar(50) not null comment ‘课程名称’,
ccredit tinyint not null comment ‘学分’,
tid int not null comment ‘教师工号’,
primary key (courseid)
);

– 添加外键约束
alter table tb_course add constraint fk_course_tid
foreign key (tid) references tb_teacher (teacherid);

– 创建学生选课表
create table tb_score
(
scid int not null auto_increment comment ‘选课编号’,
sid int not null comment ‘学号’,
cid int not null comment ‘课程编号’,
selectdate datetime comment ‘选课时间日期’,
score decimal(4,1) comment ‘考试成绩’,
primary key (scid)
);

– 添加检查约束(MySQL中检查约束不生效)
alter table tb_score add constraint ck_score_score
check (score between 0 and 100);

– 添加外键约束
alter table tb_score add constraint fk_score_sid
foreign key (sid) references tb_student (stuid);
alter table tb_score add constraint fk_score_cid
foreign key (cid) references tb_course (courseid);

– 插入学院数据
insert into tb_college
(collname, collmaster, collweb) values
(‘计算机学院’, ‘左冷禅’, ‘http://www.abc.com’),
(‘外国语学院’, ‘岳不群’, ‘http://www.xyz.com’),
(‘经济管理学院’, ‘风清扬’, ‘http://www.foo.com’);

– 插入学生数据
insert into tb_student
(stuid, stuname, stusex, stubirth, stuaddr, collid) values
(1001, ‘向问天’, 1, ‘1990-3-4’, ‘四川成都’, 1),
(1002, ‘任我行’, 1, ‘1992-2-2’, ‘湖南长沙’, 1),
(1033, ‘任盈盈’, 0, ‘1989-12-3’, ‘湖南长沙’, 1),
(1572, ‘余沧海’, 1, ‘1993-7-19’, ‘四川成都’, 1),
(1378, ‘岳灵珊’, 0, ‘1995-8-12’, ‘四川绵阳’, 1),
(1954, ‘林平之’, 1, ‘1994-9-20’, ‘福建莆田’, 1),
(2035, ‘令狐冲’, 1, ‘1988-6-30’, ‘陕西咸阳’, 2),
(3011, ‘林震南’, 1, ‘1985-12-12’, ‘福建莆田’, 3),
(3755, ‘龙傲天’, 1, ‘1993-1-25’, ‘广东东莞’, 3),
(3923, ‘向天问’, 0, ‘1985-4-17’, ‘四川成都’, 3),
(2177, ‘隔壁老王’, 1, ‘1989-11-27’, ‘四川成都’, 2);

– 插入老师数据
insert into tb_teacher
(teacherid, tname, ttitle, collid) values
(1122, ‘张三丰’, ‘教授’, 1),
(1133, ‘宋远桥’, ‘副教授’, 1),
(1144, ‘杨逍’, ‘副教授’, 1),
(2255, ‘范遥’, ‘副教授’, 2),
(3366, ‘韦一笑’, ‘讲师’, 3);

– 插入课程数据
insert into tb_course
(courseid, cname, ccredit, tid) values
(1111, ‘Python程序设计’, 3, 1122),
(2222, ‘Web前端开发’, 2, 1122),
(3333, ‘操作系统’, 4, 1122),
(4444, ‘计算机网络’, 2, 1133),
(5555, ‘编译原理’, 4, 1144),
(6666, ‘算法和数据结构’, 3, 1144),
(7777, ‘经贸法语’, 3, 2255),
(8888, ‘成本会计’, 2, 3366),
(9999, ‘审计’, 3, 3366);

– 插入选课数据
insert into tb_score
(sid, cid, selectdate, score) values
(1001, 1111, now(), 95),
(1001, 2222, now(), 87.5),
(1001, 3333, now(), 100),
(1001, 4444, now(), null),
(1001, 6666, now(), 100),
(1002, 1111, now(), 65),
(1002, 5555, now(), 42),
(1033, 1111, now(), 92.5),
(1033, 4444, now(), 78),
(1033, 5555, now(), 82.5),
(1572, 1111, now(), 78),
(1378, 1111, now(), 82),
(1378, 7777, now(), 65.5),
(2035, 7777, now(), 88),
(2035, 9999, now(), 70),
(3755, 1111, now(), 72.5),
(3755, 8888, now(), 93),
(3755, 9999, now(), null);

– 删除数据
delete from tb_student where stuid=2177;

– 更新数据
update tb_score set score=null where sid=1002 and cid=1111;

– 查询(DQL)
select * from tb_student;
select * from tb_teacher;
– 投影和别名
select stuid, stuname, stusex from tb_student;
select stuid as 学号, stuname as 姓名, stusex as 性别 from tb_student;
select stuid as 学号, stuname as 姓名, case stusex when 1 then ‘男’ else ‘女’ end as 性别 from tb_student;
select stuid as 学号, stuname as 姓名, if(stusex, ‘男’, ‘女’) as 性别 from tb_student;
select stuid+500 as 学号, stuname as 姓名, if(stusex, ‘男’, ‘女’) as 性别 from tb_student;
– 添加组合
select concat(tname,ttitle) as 全称 from tb_teacher;

– 筛选 <>–>不等于
select * from tb_student where stuid=1001;
select stuid as 学号, stuname as 姓名, stuaddr as 籍贯 from tb_student where stuid>1001 and stuid<2000;

– 模糊
select stuid, stuname from tb_student where stuname like ‘林%’ or stuname like’龙%’;
– 查姓林名为两个字的名字
select stuid, stuname from tb_student where stuname like ‘林_’;
– 查询姓林名为两个字的名字
select stuid, stuname from tb_student where stuname like ‘林__’;
– 查询为天字的名字
select stuid, stuname from tb_student where stuname like ‘%天%’;

– 排序 默认是升序 desc降序
select * from tb_student order by stubirth desc;
select * from tb_student order by stusex asc, stubirth desc;
select * from tb_student where stubirth between ‘1990-1-1’ and ‘1999-12-31’ order by stusex asc, stubirth desc;

– 聚合函数(max/min/sum/avg/count)（数据库通用）
select count(stuid)from tb_student;
select avg(score) from tb_score where cid=1111;
select sum(score) from tb_score where cid=1111;
select max(score) from tb_score where cid=1111;
select min(score) from tb_score where cid=1111;
select max(stubirth) from tb_student;

– 分组查询
– case 列名 when 1 then 新名 else 新名 end as 新名
select case stusex when 0 then ‘女’ else ‘男’ end as 性别, count(stusex) as 人数 from tb_student group by stusex;
select case stusex when 0 then ‘女’ else ‘男’ end as 性别, min(stubirth) as 出生日期 from tb_student group by stusex;
select cid, avg(score) from tb_score group by cid;
– 查询每个学生的学号和平均成绩
select sid, avg(score) from tb_score group by sid;
– 分组后做删选加having,where分组前删选,order by 排序
select sid, avg(score) as avg from tb_score where sid between 1000 and 1999 group by sid having avg>=80 order by avg desc;
– 查询年龄最大的3名学生的信息,limit 3限制前3个
select * from tb_student order by stubirth limit 3;
– offset 跳过前4行，看后3个5,6,7
select * from tb_student order by stubirth limit 3 offset 4;
– 跳过前3行，看后4个5,6,7, 8(简写)
select * from tb_student order by stubirth limit 3,4;
– 查年龄最大的学生的姓名—>子查询(把一个查询结果作为另一个查询的一部分来使用)
select stuname from tb_student where stubirth=(select min(stubirth) from tb_student);

– 查询每个学生的学号和平均成绩
select sid, avg(score) as avg from tb_score group by sid order by avg desc;

– 查询平均成绩大于等于80分的学生的学号和平均成绩
select sid, avg(score) as avg from tb_score where sid between 1000 and 1999 group by sid having avg>=80 order by avg desc;

– 分页查询
– 查询年龄最大的3名学生的信息
select * from tb_student order by stubirth limit 3;
select * from tb_student order by stubirth limit 3 offset 3;
select * from tb_student order by stubirth limit 3,3
select * from tb_student order by stubirth limit 6,4;

– 子查询(把一个查询的结果作为另外一个查询的一部分来使用)
– 查询年龄最大的学生的姓名
select stuname from tb_student where stubirth=(select min(stubirth) from tb_student);

select stuname from tb_student where stubirth=(select max(stubirth) from tb_student);

select stuname from tb_student where stuid in (select sid from tb_score where score=(select max(score) from tb_score where cid=(select courseid from tb_course where cname=‘Python程序设计’)) and cid=(select courseid from tb_course where cname=‘Python程序设计’));
– 查询选了三门及以上的课程的学生姓名
select stuname from tb_student where stuid in (select sid from tb_score group by sid having count(sid)>=3);
– 查询课程名称、学分、授课老师的名字和职称
– 笛卡尔积
– 连接查询
select cname, ccredit, tname, ttitle from tb_course, tb_teacher where tid=teacherid;

– 内连接
select cname, ccredit, tname, ttitle from tb_course inner join tb_teacher on tid=teacherid;
– 查询学生姓名和所在学院名称
select stuname, collname from tb_student t1, tb_college t2 where t1.collid=t2.collid;
select stuname, collname from tb_student t1 inner join tb_college t2 on t1.collid=t2.collid;
– 查询学生姓名、课程名称以及考试成绩
– 和null作比较时不能使用=和<>
– 要使用is null或者is not null来进行判断
select stuname, cname, score from tb_student, tb_course, tb_score where stuid=sid and courseid=cid and score is not null;
select stuname, cname, score from tb_student inner join tb_score on stuid=sid inner join tb_course on courseid=cid where score is not null;
– 查询选课学生的姓名和平均成绩

– 查询每个学生的姓名和选课数量
– 左外连接 左表不满足连表条件的记录也要查询出来
– 对于不满足条件的列补上null
– MySQL不支持全外连接(full outer join)
select stuname as 姓名, ifnull(total, 0) as 选课数量 from tb_student left outer join (select sid, count(sid) as total from tb_score group by sid) tb_temp on stuid=sid;

列举三:

– 创建人力资源管理系统数据库
drop database if exists HRS;
create database HRS default charset utf8;
– 切换数据库上下文环境
use HRS;
– 删除表
drop table if exists TbEmp;
drop table if exists TbDept;
– 创建部门表
create table TbDept
(
deptno tinyint primary key, – 部门编号
dname varchar(10) not null, – 部门名称
dloc varchar(20) not null – 部门所在地
);
– 添加部门记录
insert into TbDept values (10, ‘会计部’, ‘北京’);
insert into TbDept values (20, ‘研发部’, ‘成都’);
insert into TbDept values (30, ‘销售部’, ‘重庆’);
insert into TbDept values (40, ‘运维部’, ‘深圳’);
– 创建员工表
create table TbEmp
(
empno int primary key, – 员工编号
ename varchar(20) not null, – 员工姓名
job varchar(20) not null, – 员工职位
mgr int, – 主管编号
sal int not null, – 员工月薪
comm int, – 每月补贴
dno tinyint – 所在部门编号
);
– 添加外键约束
alter table TbEmp add constraint fk_dno foreign key (dno) references TbDept(deptno);
– 添加员工记录
insert into TbEmp values (7800, ‘张三丰’, ‘总裁’, null, 9000, 1200, 20);
insert into TbEmp values (2056, ‘乔峰’, ‘分析师’, 7800, 5000, 1500, 20);
insert into TbEmp values (3088, ‘李莫愁’, ‘设计师’, 2056, 3500, 800, 20);
insert into TbEmp values (3211, ‘张无忌’, ‘程序员’, 2056, 3200, null, 20);
insert into TbEmp values (3233, ‘丘处机’, ‘程序员’, 2056, 3400, null, 20);
insert into TbEmp values (3251, ‘张翠山’, ‘程序员’, 2056, 4000, null, 20);
insert into TbEmp values (5566, ‘宋远桥’, ‘会计师’, 7800, 4000, 1000, 10);
insert into TbEmp values (5234, ‘郭靖’, ‘出纳’,5566, 2000, null, 10);
insert into TbEmp values (3344, ‘黄蓉’, ‘销售主管’, 7800, 3000, 800, 30);
insert into TbEmp values (1359, ‘胡一刀’, ‘销售员’, 3344, 1800, 200, 30);
insert into TbEmp values (4466, ‘苗人凤’, ‘销售员’, 3344, 2500, null, 30);
insert into TbEmp values (3244, ‘欧阳锋’, ‘程序员’, 3088, 3200, null, 20);
insert into TbEmp values (3577, ‘杨过’, ‘会计’, 5566, 2200, null, 10);
insert into TbEmp values (3588, ‘朱九真’, ‘会计’, 5566, 2500, null, 10);

– 查询薪资最高的员工姓名和工资
select ename, sal from tbemp order by sal desc limit 1;
select ename, sal from tbemp where sal=(select max(sal) from tbemp);
– 查询员工的姓名和年薪((月薪+补贴)*12)
select ename, (sal+ifnull(comm, 0))*12 as total from tbemp order by total desc;
– 查询有员工的部门的编号和人数
select dno, count(dno) from tbemp group by dno;

– 查询所有部门的名称和人数
select dname, ifnull(total, 0) as total from tbdept, (select dno, count(dno) as total from tbemp group by dno) tb_emp where deptno=dno;

select dname, ifnull(total, 0) as total from tbdept left outer join (select dno, count(dno) as total from tbemp group by dno) tb_temp on deptno=dno;

– 查询薪资最高的员工(Boss除外)的姓名和工资
select ename, sal from tbemp where sal=(select max(sal) from tbemp where mgr is not null);
– 查询薪水超过平均薪水的员工的姓名和工资
create view v_av as select ename, sal from tbemp where sal>(select avg(sal) from tbemp);
select * from v_av;

– 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
select ename, t1.dno, sal, sal-avgsal from tbemp t1 inner join (select dno, avg(sal) as avgsal from tbemp group by dno) t2 on t1.dno=t2.dno and sal>avgsal;

– 查询部门中薪水最高的人姓名、工资和所在部门名称
create view v_a as select ename, sal, dname from tbdept inner join (select ename, sal, t1.dno from tbemp t1 inner join (select dno, max(sal) as maxsal from tbemp group by dno) t2 on t1.dno=t2.dno and sal=maxsal) tb_temp on deptno=dno;
select * from v_a;

select ename, sal, dname from tbemp t1 inner join tbdept t2 on deptno=t1.dno inner join (select dno, max(sal) as maxsal from tbemp group by dno) t3 on deptno=t3.dno and sal=maxsal;
– 查询主管的姓名和职位
select ename, job from tbemp where empno in (select distinct mgr from tbemp where mgr is not null);

– 推荐使用exists/not exists取代in/not in和distinct
select ename, job from tbemp t1 where exists (select ‘x’ from tbemp t2 where t1.empno=t2.mgr);

– 查询薪资排名4~6名的员工姓名和工资
select ename, sal from tbemp order by sal desc limit 3,3;

– 删除用户
drop user zq;

– 创建一个用户
create user ‘zq’@’%’ identified by ‘123123’;

– 给 用户授权
grant all privileges on . to ‘zq’@’%’ with grant option;

– 从hellokitty召回权限
revoke insert, delete,update on hrs.tbemp from [email protected]’%’;

– 索引相当于是书的目录 ，它可以用来加速查询（先查询目录定位搜素范围）
– 这是典型的用空间换时间策略，所以索引要创建在用作查询的筛选条件的列上
– 这是普通索引，可以有多个名字
create index idx_emp_name on tbemp (ename);
– 唯一索引，名字不能重复
create unique index idx_emp_name on tbemp (ename);
– 删除索引
drop index idx_emp_name on tbemp;