一、学生管理系统
1.创建表
- 创建学院表
create table tb_college
(
collid int auto_increment comment '学院编号',
collname varchar(50) not null comment '学院名称',
website varchar(1024) default '' comment '学院网站',
primary key(collid)
);
2.创建老师表
create table tb_teacher
(
tid int not null,
tname varchar(20) not null,
title enum('教授', '副教授', '讲师', '助教') not null,
cid int not null
-- foreign key (cid) references tb_college (collid),
-- primary key(tid)
);
2.修改表
注：一对多关系是在多的一边添加外键来维护表之间的关系
1.修改老师表添加主键约束
alter table tb_teacher add constraint pk_teacher_tid
primary key (tid);
2.修改老师表添加外键约束(参照完整性约束)
alter table tb_teacher add constraint fk_teacher_cid
foreign key (cid) references tb_college (collid)
on delete restrict on update restrict;
3.修改老师表删除约束
alter table tb_teacher drop foreign key fk_teacher_cid;
4.建立学生跟学院的多对一关系(在多的一方添加外键列)
alter table tb_student add column cid int;
alter table tb_student add constraint fk_student_cid
foreign key (cid) references tb_college (collid);
3.插入数据
1.给学院表录入3条记录
insert into tb_college(collname, website)
values
('计算机学院', 'http://www.abc.com'),
('经济管理学院', default),
('外国语学院', 'http://www.xyz.com');
2.尝试删除和更新计算机学院
update tb_college set collid=100 where collid=1;
delete from tb_college where collid=100;
4.建立表与表之间的关系
1.建立老师和课程的多对多关系 (通过中间表来维护)
create table tb_teacher_course
(
tcid int auto_increment,
tid int not null,
cid int not null,
opendate date not null,
primary key (tcid),
foreign key (tid) references tb_teacher (tid),
foreign key (cid) references tb_course (cid)
);
- 建立学生和课程多的多对多关系 (通过中间表来维护)
create table tb_student_course
(
scid int auto_increment,
sid int not null,
cid int not null,
score decimal(4,1),
primary key(scid)
);
alter table tb_student_course add constraint fk_sc_sid
foreign key (sid) references tb_student (stuid);
alter table tb_student_course add constraint fk_sc_cid
foreign key (cid) references tb_course (cid);
二、查询操作
1.查询所有学生的信息
select * from tb_student;
2.查询所有课程的名称及学分(投影和别名)
select cname as 课程名称, credit as 学分 from tb_course;
3.查询所有女学生的姓名和出生日期(筛选)
select sname as 姓名, birth as 出生日期 from tb_student where gender=0;
4.查询所有80后学生的姓名、性别和出生日期(筛选)
select sname as 姓名, if(gender, '男', '女') as 性别, birth as 出生日期
from tb_student where birth between '1980-1-1' and '1989-12-31';
5.通用写法
select sname as 姓名, case gender when 1 then '男' else '女' end as 性别, birth as 出生日期
from tb_student where birth between '1980-1-1' and '1989-12-31';
6.查询姓”杨“的学生姓名和性别(模糊)
select sname as 姓名, if(gender, '男', '女') as 性别 from tb_student where sname like '杨%';
7.查询姓”杨“名字两个字的学生姓名和性别(模糊)
select sname as 姓名, if(gender, '男','女') as 性别 from
tb_student where sname like '杨_';
8.查询姓”杨“名字三个字的学生姓名和性别(模糊)
select sname as 姓名, if(gender, '男','女') as 性别 from
tb_student where sname like '杨__';
9.查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
select sname as 姓名 from tb_student
where sname like '%不%' or sname like '%嫣%';
10.查询没有录入家庭住址的学生姓名(空值)
select sname as 姓名 from tb_student where addr is null;
11.查询录入了家庭住址的学生姓名(空值)
select sname as 姓名 from tb_student where addr is not null;
12.查询学生选课的所有日期(去重)
select distinct seldate as 选课日期 from tb_score;
13.查询学生的家庭住址(去重)
select distinct addr as 家庭住址 from tb_student
where addr is not null;
14.查询男学生的姓名和年龄按年龄从大到小排列(排序)
select sname as 姓名, year(now()) - year(birth) as 年龄
from tb_student where gender=1 order by birth;
15.查询年龄最大的学生的出生日期(聚合函数)
集合函数：max() / min() / sum() / avg() / count()
select min(birth) as 出生日期 from tb_student;
16.查询年龄最小的学生的出生日期(聚合函数)
select max(birth) as 出生日期 from tb_student;
17.查询男女学生的人数(分组和聚合函数)
select if(gender, '男', '女') as 性别, count(stuid) as 人数
from tb_student group by gender order by 人数 desc;
18.查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(mark) as 平均成绩 from tb_score where cid=1111;
19.查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(mark) as 平均分 from tb_score where sid=1001;
20.查询每个学生的学号和平均成绩(分组和聚合函数)
select sid as 学号, avg(mark) as 平均成绩 from tb_score
group by sid;
21.查询平均成绩大于等于90分的学生的学号和平均成绩
注意：分组之前的筛选用where，分组之后的筛选用having
select sid as 学号, avg(mark) as 平均成绩 from tb_score
group by sid having 平均成绩 >= 90;
22.查询年龄最大的学生的姓名(子查询)
子查询 - 在一个查询中又使用到了另外一个查询的结果
select sname as 姓名 from tb_student
where birth = (select min(birth) from tb_student);
23.查询年龄最大的学生姓名和年龄(子查询+运算)
select sname as 姓名, year(now())-year(birth) as 年龄
from tb_student
where birth = (select min(birth) from tb_student);
24.查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select sname as 姓名 from tb_student
where stuid in (
select sid from tb_score group by sid having count(sid) > 2
);
25.查询学生的姓名和所在学院的名称
连接查询（联结查询/联接查询）
如果查询多表没有任何限制条件那么就产生笛卡尔积。
如果查询的多张表中有同名的列那么需要使用完全限定名。
select sname as 姓名, collname as 学院名称
from tb_student t1, tb_college t2
where t1.collid = t2.collid;
select sname as 姓名, collname as 学院名称 from tb_student t1
inner join tb_college t2
on t1.collid=t2.collid;
26. 查询学生姓名、课程名称以及成绩
select sname as 姓名, cname as 课程名称, mark as 成绩
from tb_student t1, tb_score t2, tb_course t3
where t1.stuid = t2.sid and t2.cid = t3.couid;
select sname as 姓名, cname as 课程名称, mark as 成绩 from tb_student t1
inner join tb_score t2 on t1.stuid=t2.sid
inner join tb_course t3 on t2.cid=t3.couid;
27.查询选课学生的姓名和平均成绩(子查询和连接查询)
select sname as 姓名, 平均成绩
from tb_student t1 inner join
(select sid, avg(mark) as 平均成绩 from tb_score group by sid) t2
on stuid=sid;
select sname as 姓名, 平均成绩
from tb_student t1 left outer join
(select sid, avg(mark) as 平均成绩 from tb_score group by sid) t2
on stuid=sid;
28.查询每个学生的姓名和选课数量(左外连接和子查询)
内连接只能查询满足条件的记录，外链接可以保留所有记录
full outer join -> 全外链接
left outer join -> 左外链接
right outer join -> 右外链接
注意:MySQL不支持全外链接
select sname as 姓名, 选课数量
from tb_student t1 left outer join
(select sid, count(sid) as 选课数量 from tb_score group by sid) t2
on stuid=sid
三、事务(transaction)
事务(transaction):多个操作不可分割，要么全成功要么全失败。
1.事务的(ACID)特性:
1.原子性(Atomicity):事务中的操作不可分割。
2.一致性(Consistency):事务前后数据状态要一致。
3.隔离性(Isolation):多个并发事务不能看到彼此的中间状态。
4.持久性(Duration):事务完成后数据要持久化。
2.事务操作
1.开启事务
begin / start transaction;
2.提交事务
commit
3.回滚事务
rollback;
四、作业
1.查询薪资最高的员工姓名和工资
select ename as 姓名, sal+comm as 工资 from tbemp
where sal+comm = (select max(sal+comm) as 工资 from tbemp);
2.查询员工的姓名和年薪((月薪+补贴)*12)
select ename as 姓名, if(comm is not null, (sal+comm)*12, (sal)*12) as 年薪 from tbemp
group by eno;
3.查询有员工的部门的编号和人数
select dno as 部门号, count(dno) as 人数 from tbemp
group by dno;
4.查询所有部门的名称和人数
select dname as 部门名称, count(t1.dno) as 人数
from tbemp t1 right outer join tbdept t2 on t1.dno=t2.dno
group by t1.dno
order by 人数;
5.查询薪资最高的员工(Boss除外)的姓名和工资
select ename as 姓名, sal+comm as 工资 from tbemp
where sal+comm=(
select max(if(comm is not null,sal+comm,sal))
from tbemp where eno <> (select eno from tbemp where job='总裁')
);
6.查询薪水超过平均薪水的员工的姓名和工资
select ename as 姓名, if(comm is not null, sal+comm, sal) as 工资 from tbemp
where sal > (select avg(sal) from tbemp);
7.查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
select ename as 姓名, t1.dno as 部门编号, if(comm is not null, sal+comm, sal) as 工资 from tbemp t1
inner join (select dno,avg(sal) as avg from tbemp group by dno) t2 on t1.dno=t2.dno
where sal>avg;
8.查询部门中薪水最高的人姓名、工资和所在部门名称
select ename as 姓名, if(comm is not null, sal+comm, sal) as 工资, dname as 部门名称 from tbemp t1
inner join (select dno, max(sal) as max from tbemp group by dno) t2 on t1.dno=t2.dno, tbdept
where sal=max and t1.dno=tbdept.dno;
9.查询主管的姓名和职位
select ename as 姓名, job as 职位 from tbemp
where job like '%主管';
10.查询薪资排名4~6名的员工姓名和工资
select ename as 姓名, if(comm is not null, sal+comm, sal) as 工资 from tbemp
order by sal desc limit 4,3;
网友评论