select

一.課上練習(xí)代碼

1 查詢所有學(xué)生信息

select * from tb_student;
select * from tb_teacher;

2 查詢所有課程名稱及學(xué)分(投影和別名)

select couname, coucredit from tb_course;
select couname as 課程名稱, coucredit 學(xué)分 from tb_course;
select stuname 姓名, case stusex when 1 then '男' else '女' end 性別 from tb_student;
select stuname , if(stusex, '男', '女') 性別 from tb_student;

3 查詢所有女學(xué)生的姓名和出生日期(篩選)

select stuname, stubirth from tb_student where stusex=1;

4 查詢所有80后學(xué)生的姓名、性別和出生日期(篩選)

select stuname, stusex, stubirth from tb_student where
stubirth>='1980-1-1' and stubirth<='1989-12-31';
select stuname, stusex, stubirth from tb_student where
stubirth between '1980-1-1' and '1989-12-31';

5 查詢姓”楊“的學(xué)生姓名和性別(模糊)

select stuname, stusex, stubirth from tb_student where stuname like '楊%';

6 查詢姓”楊“名字兩個字的學(xué)生姓名和性別(模糊)

select stuname, stusex, stubirth from tb_student where stuname like '楊_';

7 查詢姓”楊“名字三個字的學(xué)生姓名和性別(模糊)

select stuname, stusex, stubirth from tb_student where stuname like '楊__';

8 查詢名字中有”不“字或“嫣”字的學(xué)生的姓名(模糊)

select stuname, stusex, stubirth from tb_student where stuname like '%不%'
or stuname like '%嫣%';

9 查詢沒有錄入家庭住址的學(xué)生姓名(空值)

select stuname, stuaddr from tb_student where stuaddr is null;

10 查詢錄入了家庭住址的學(xué)生姓名(空值)

select stuname, stuaddr from tb_student where stuaddr is not null;

11 查詢學(xué)生選課的所有日期(去重)

select distinct scdate from tb_score ;

12 查詢學(xué)生的家庭住址(去重)

select distinct stuaddr from tb_student where stuaddr is not null;

13 查詢男學(xué)生的姓名和生日按年齡從大到小排列(排序)

-- asc ascending -升序
-- desc  descending -降序
select stuname,stubirth from tb_student where stusex=1 order by stubirth;
select stuname,stubirth from tb_student where stusex=1 order by stubirth desc;
select stuname 姓名,year(now())-year(stubirth) 年齡 from tb_student where stusex=1 order by 年齡;

14 查詢年齡最大的學(xué)生的出生日期(聚合函數(shù))

select min(stubirth) from tb_student;

15 查詢年齡最小的學(xué)生的出生日期(聚合函數(shù))

select max(stubirth) from tb_student;

16 查詢男女學(xué)生的人數(shù)(分組和聚合函數(shù))

17 常用聚合函數(shù) min/max/count/avg

select count(stuid) from tb_student;
select stusex, count(*) from tb_student group by stusex;
select stusex, min(stubirth) from tb_student group by stusex;

18 查詢課程編號為1111的課程的平均成績(篩選和聚合函數(shù))

select avg(scmark) from tb_score where couid=1111;
select min(scmark) from tb_score where couid=1111;
select count(stuid) from tb_score where couid=1111;
select sum(scmark) from tb_score where couid=1111;

####19 查詢學(xué)號為1001的學(xué)生所有課程的平均分(篩選和聚合函數(shù))
select avg(scmark) from tb_score where stuid=1001;

20 查詢每個學(xué)生的學(xué)號和平均成績(分組和聚合函數(shù))

select stuid,avg(scmark) from tb_score 
group by stuid;

21 查詢平均成績大于等于90分的學(xué)生的學(xué)號和平均成績

-- 分組以前的篩選用where,分組以后的篩選用having
select stuid,avg(scmark) 平均分 from tb_score 
group by stuid having 平均分 >=90;

22 查詢年齡最大的學(xué)生的姓名(子查詢)

select stuname from tb_student where stubirth = (
select min(stubirth) from tb_student

23 查詢年齡最大的學(xué)生姓名和年齡(子查詢+運算)

select stuname,year(now())-year(stubirth) from tb_student 
where stubirth = (select max(stubirth) from tb_student);

select stuname,year(now())-year(stubirth) from tb_student 
where stubirth = (select min(stubirth) from tb_student);

24 查詢選了兩門以上的課程的學(xué)生姓名(子查詢/分組條件/集合運算)

select stuname from tb_student where stuid in (
select stuid from tb_score group by stuid having count(stuid)>2);

25 查詢學(xué)生姓名、課程名稱以及成績(連接查詢)

select stuname, couname, scmark from tb_student t1, tb_course t2, tb_score t3
where t1.stuid=t3.stuid and t2.couid=t3.couid and scmark is not null;
-- 
select stuname, couname, ifnull(scmark,0) from 
tb_student t1 inner join tb_score t3 on t1.stuid=t3.stuid inner join
tb_course t2 on t2.couid=t3.couid order by scmark desc;

26 查詢選課學(xué)生的姓名和平均成績(子查詢和連接查詢)

select stuname, avgmark from tb_student t1,
(select stuid, avg(scmark) avgmark from tb_score group by stuid) t2
where t1.stuid=t2.stuid order by avgmark desc;

select stuname, avgmark from tb_student t1 inner join
(select stuid, avg(scmark) avgmark from tb_score group by stuid) t2
on t1.stuid=t2.stuid order by avgmark desc;

-- 內(nèi)連接（inner join）：只有滿足連接條件的記錄才會被查出來
-- 外連接（outer join）：左外連接 / 右外連接 / 全外連接
-- left outer join / right outer join / full outer join

27 查詢每個學(xué)生的姓名和選課數(shù)量(左外連接和子查詢)

select stuname, ifnull(total,0) from tb_student t1 left join
(select stuid ,count(stuid) total from tb_score group by stuid) t2
on t1.stuid = t2.stuid limit 5 offset 5;

二.作業(yè)代碼

• 切換上下文
  use hrs;

1.查詢薪資最高的員工姓名和工資

select ename ,sal from tb_emp where sal=(select max(sal) from tb_emp); 
select ename ,sal,comm from tb_emp where sal+ ifnull(comm,0)=
(select max(sal+ ifnull(comm,0)) from tb_emp); 

2.查詢員工的姓名和年薪((月薪+補貼)*12)

select ename ,(sal+ ifnull(comm,0))*12 from tb_emp;

3.查詢有員工的部門的編號和人數(shù)

select dno , count(dno) from tb_emp group by dno;

4.查詢所有部門的名稱和人數(shù)

select dname,ifnull(pno,0) from tb_dept t1 left join
(select dno , count(dno) as pno from tb_emp group by dno) t2 on t1.dno=t2.dno;

5.查詢薪資最高的員工(Boss除外)的姓名和工資

select ename ,sal from tb_emp where sal=(select max(sal) from tb_emp where mgr is not null); 

6. 查詢薪水超過平均薪水的員工的姓名和工資

select ename , sal from tb_emp where sal > (select avg(sal) from tb_emp) and mgr is not null;

7.查詢薪水超過其所在部門平均薪水的員工的姓名、部門編號和工資

select ename , dno, sal from tb_emp t1 where sal > 
(select avgsal from (select dno, count(dno), avg(sal) avgsal  from tb_emp group by dno) t3
where t1.dno = t3.dno and mgr is not null);
###8.查詢部門中薪水最高的人姓名、工資和所在部門名稱
select ename ,sal, dname from tb_emp t1,tb_dept t2 where t1.dno = t2.dno and
sal = (select maxsal from (select dno,max(sal) maxsal from tb_emp where mgr is not null group by dno ) t3
where t1.dno = t3.dno
);

9.查詢主管的姓名和職位

select ename ,job from tb_emp where mgr = 7800;

10.詢薪資排名4~6名的員工姓名和工資

select ename ,sal from tb_emp t1,
(select sal as sals from tb_emp where mgr is not null group by sal order by sal desc limit 3,3) t2
where t1.sal in (t2.sals);

課上練習(xí)數(shù)據(jù)庫創(chuàng)建代碼

-- 如果存在名為school的數(shù)據(jù)庫就刪除它
drop database if exists school;

-- 創(chuàng)建名為school的數(shù)據(jù)庫并設(shè)置默認的字符集和排序方式
create database school default charset utf8 collate utf8_bin;

-- 切換到school數(shù)據(jù)庫上下文環(huán)境
use school;

-- 創(chuàng)建學(xué)院表
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 '網(wǎng)站',
primary key (collid)
);

-- 創(chuàng)建學(xué)生表
create table tb_student
(
stuid int not null comment '學(xué)號',
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 '所屬學(xué)院',
primary key (stuid),
foreign key (collid) references tb_college (collid)
);

-- alter table tb_student add constraint fk_student_collid foreign key (collid) references tb_college (collid);

-- 創(chuàng)建教師表
create table tb_teacher
(
teaid int not null comment '工號',
teaname varchar(20) not null comment '姓名',
teatitle varchar(10) default '助教' comment '職稱',
collid int not null comment '所屬學(xué)院',
primary key (teaid),
foreign key (collid) references tb_college (collid)
);

-- 創(chuàng)建課程表
create table tb_course
(
couid int not null comment '編號',
couname varchar(50) not null comment '名稱',
coucredit int not null comment '學(xué)分',
teaid int not null comment '授課老師',
primary key (couid),
foreign key (teaid) references tb_teacher (teaid)
);

-- 創(chuàng)建選課記錄表
create table tb_score
(
scid int auto_increment comment '選課記錄編號',
stuid int not null comment '選課學(xué)生',
couid int not null comment '所選課程',
scdate datetime comment '選課時間日期',
scmark decimal(4,1) comment '考試成績',
primary key (scid),
foreign key (stuid) references tb_student (stuid),
foreign key (couid) references tb_course (couid)
);

-- 添加唯一性約束（一個學(xué)生選某個課程只能選一次）
alter table tb_score add constraint uni_score_stuid_couid unique (stuid, couid);

-- 插入學(xué)院數(shù)據(jù)
insert into tb_college (collname, collmaster, collweb) values 
('計算機學(xué)院', '左冷禪', 'http://www.abc.com'),
('外國語學(xué)院', '岳不群', 'http://www.xyz.com'),
('經(jīng)濟管理學(xué)院', '風(fēng)清揚', 'http://www.foo.com');

-- 插入學(xué)生數(shù)據(jù)
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', null, 2),
(3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
(3755, '項少龍', 1, '1993-1-25', null, 3),
(3923, '楊不悔', 0, '1985-4-17', '四川成都', 3);

-- 插入老師數(shù)據(jù)
insert into tb_teacher (teaid, teaname, teatitle, collid) values 
(1122, '張三豐', '教授', 1),
(1133, '宋遠橋', '副教授', 1),
(1144, '楊逍', '副教授', 1),
(2255, '范遙', '副教授', 2),
(3366, '韋一笑', '講師', 3);

-- 插入課程數(shù)據(jù)
insert into tb_course (couid, couname, coucredit, teaid) values 
(1111, 'Python程序設(shè)計', 3, 1122),
(2222, 'Web前端開發(fā)', 2, 1122),
(3333, '操作系統(tǒng)', 4, 1122),
(4444, '計算機網(wǎng)絡(luò)', 2, 1133),
(5555, '編譯原理', 4, 1144),
(6666, '算法和數(shù)據(jù)結(jié)構(gòu)', 3, 1144),
(7777, '經(jīng)貿(mào)法語', 3, 2255),
(8888, '成本會計', 2, 3366),
(9999, '審計學(xué)', 3, 3366);

-- 插入選課數(shù)據(jù)
insert into tb_score (stuid, couid, scdate, scmark) values 
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, curdate(), null),
(3755, 1111, date(now()), null),
(3755, 8888, date(now()), null),
(3755, 9999, '2017-09-01', 92);

作業(yè)數(shù)據(jù)庫創(chuàng)建代碼

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

use hrs;

drop table if exists tb_emp;
drop table if exists tb_dept;

create table tb_dept
(
dno int not null comment '編號',
dname varchar(10) not null comment '名稱',
dloc varchar(20) not null comment '所在地',
primary key (dno)
);

insert into tb_dept values 
    (10, '會計部', '北京'),
    (20, '研發(fā)部', '成都'),
    (30, '銷售部', '重慶'),
    (40, '運維部', '深圳');

create table tb_emp
(
eno int not null comment '員工編號',
ename varchar(20) not null comment '員工姓名',
job varchar(20) not null comment '員工職位',
mgr int comment '主管編號',
sal int not null comment '員工月薪',
comm int comment '每月補貼',
dno int comment '所在部門編號',
primary key (eno)
);

alter table tb_emp add constraint fk_emp_dno foreign key (dno) references tb_dept (dno);

insert into tb_emp values 
    (7800, '張三豐', '總裁', null, 9000, 1200, 20),
    (2056, '喬峰', '分析師', 7800, 5000, 1500, 20),
    (3088, '李莫愁', '設(shè)計師', 2056, 3500, 800, 20),
    (3211, '張無忌', '程序員', 2056, 3200, null, 20),
    (3233, '丘處機', '程序員', 2056, 3400, null, 20),
    (3251, '張翠山', '程序員', 2056, 4000, null, 20),
    (5566, '宋遠橋', '會計師', 7800, 4000, 1000, 10),
    (5234, '郭靖', '出納', 5566, 2000, null, 10),
    (3344, '黃蓉', '銷售主管', 7800, 3000, 800, 30),
    (1359, '胡一刀', '銷售員', 3344, 1800, 200, 30),
    (4466, '苗人鳳', '銷售員', 3344, 2500, null, 30),
    (3244, '歐陽鋒', '程序員', 3088, 3200, null, 20),
    (3577, '楊過', '會計', 5566, 2200, null, 10),
    (3588, '朱九真', '會計', 5566, 2500, null, 10);