第1题
案例：
1、创建数据库test02_library
2、创建表格books
字段名 | 字段说明 | 数据类型 |
b_id | 书编号 | int(11) |
b_name | 书名 | varchar（50） |
authors | 作者 | varchar(100) |
price | 价格 | float |
pubdate | 出版日期 | year |
note | 说明 | varchar(100) |
num | 库存 | int(11) |
3、使用alter语句给books按如下要求增加相应的约束
字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
b_id | 书编号 | int(11) | 是 | 否 | 是 | 是 | 是 |
b_name | 书名 | varchar（50） | 否 | 否 | 是 | 否 | 否 |
authors | 作者 | varchar(100) | 否 | 否 | 是 | 否 | 否 |
price | 价格 | float | 否 | 否 | 是 | 否 | 否 |
pubdate | 出版日期 | year | 否 | 否 | 是 | 否 | 否 |
note | 说明 | varchar(100) | 否 | 否 | 否 | 否 | 否 |
num | 库存 | int(11) | 否 | 否 | 是 | 否 | 否 |
4、向books表中插入记录
1） 指定所有字段名称插入第一条记录
2）不指定字段名称插入第二记录
3）同时插入多条记录（剩下的所有记录）
b_id | b_name | authors | price | pubdate | note | num |
1 | Tal of AAA | Dickes | 23 | 1995 | novel | 11 |
2 | EmmaT | Jane lura | 35 | 1993 | joke | 22 |
3 | Story of Jane | Jane Tim | 40 | 2001 | novel | 0 |
4 | Lovey Day | George Byron | 20 | 2005 | novel | 30 |
5 | Old land | Honore Blade | 30 | 2010 | law | 0 |
6 | The Battle | Upton Sara | 30 | 1999 | medicine | 40 |
7 | Rose Hood | Richard haggard | 28 | 2008 | cartoon | 28 |
5、统计书名中包含a字母的书
6、统计书名中包含a字母的书的数量和库存总量
7、找出“novel”类型的书，按照价格降序排列
8、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
9、按照note分类统计书的数量
10、按照note分类统计书的库存量，显示库存量超过30本的
11、查询所有图书，每页显示5本，显示第二页
12、按照note分类统计书的库存量，现在库存量最多的
13、查询书名达到10个字符的书，不包括里面的空格
14、查询书名和类型，其中
note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
15、查询书名、库存，其中
num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
16、统计每一种note的库存量，并合计总量
17、统计每一种note的数量，并合计总量
18、统计库存量前三名的图书
19、找出最早出版的一本书
20、找出novel中价格最高的一本书
21、找出书名中字数最多的一本书，不含空格
//1.创建数据库test02_library
create database test02_library;
//2.创建表格books
use test02_library;
create table books (
b_id int(11),
b_name varchar(50),
authors varchar(100),
price float,
pubdate year,
note varchar(100),
num int(11)
);
desc books;
运行后的结果为：
//3.使用alter语句给books按如下要求添加相应的约束
alter table books add primary key (b_id);
alter table books add unique key (b_id);
alter table books modify b_id int(11) auto_increment;
alter table books modify b_name varchar(50) not null;
alter table books modify authors varchar(100) not null;
alter table books modify price float not null;
alter table books modify pubdate year not null;
alter table books modify num int(11) not null;
desc books;
//4.向books 表中插入记录
insert into books (b_id,b_name,authors,price,pubdate,note,num) values(1,'Tal of AAA','Dickes',23,'1995','novel',11);
insert into books values(2,'EmmaT','Jane lura',35,'1993','joke',22);
insert into books values (3,'Story of Jane','Jane Tim',40,'2001','novel',0),
(4,'Lovey Day','George Byron',20,'2005','novel',30),
(5,'Old land','Honore Blade',30,'2010','law',0),
(6,'The Battle','Upton Sara',30,'1999','medicine',40),
(7,'Rose Hood','Richard haggard',28,'2008','cartoon',28);
select * from books;运行后的结果为：
//5.统计书名中包含a字母的书
select count(*) as 数量
from books
where b_name like '%a%';运行后的结果为:
//6.统计书名中包含a字母和库存总量
select count(*) as 数量,sum(num) as 库存总量
from books
where b_name like '%a%';运行后的结果为：
//7.找出"novel"类型的书，并按照价格降序进行排序
select * from books
where note='novel'
order by price desc;运行后的结果为：
//8.查询图书信息，按照库存量降序排序，如果库存量相同的按照note升序进行排序
select * from books
order by num desc,note asc;运行后的结果为：
//9、按照note分类统计书的数量
select count(*)as 书的数量,note
from books
group by note;运行后的结果为：
//10.按照note分类统计书的库存量，显示库存量超过30本的
select note,sum(num) as 书的库存量
from books
group by note
having sum(num)>30;运行后的结果为：
//11、查询所有图书，每页显示5本，显示第二页
select * from books
limit 5,5;运行后的结果为：
//12、按照note分类统计书的库存量，现在库存量最多的
select num as 库存量,note from books
group by note
order by num desc
limit 0,1;运行后的结果为：
// 13、查询书名达到10个字符的书，不包括里面的空格
select * from books
where CHAR_LENGTH(replace(b_name,' ',''))>=10;运行后的结果为：
//14.查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
select b_name as '书名',note,case note
when 'novel' then '小说'
when 'law' then '医药'
when 'medicine' then '医药'
when 'cartoon' then '卡通'
when 'joke' then '笑话'
end as '类型'
from books;运行后的结果为：
/**15.查询书名，库存，其中
num值超过30本的，显示滞销，大于0并低于10的，显示畅销，
为0的显示需要无货，其他的显示正常**/
select b_name as '书名',num,case
when num>30 then '滞销'
when num>0 and num<10 then '畅销'
when num=0 then '无货'
else '正常'
end as '库存'
from books;
//16.统计每一种note的库存量，并合计总量
select ifnull(note,'合计总量') as note,sum(num)
from books
group by note with ROLLUP;
//17.统计每一种note的数量，并统计总量
SELECT ifnull(note,'合计总量') as note,count(*) as '数量'
from books
group by note with ROLLUP;运行后的结果为：
//18.统计库存量前三名的图书
SELECT * from books
order by num DESC
limit 0,3;运行后的结果为：
//19.找出最早出版的一本书
select * from books
order by pubdate ASC
limit 0,1;运行后的结果为：
// 20、找出novel中最高的一本书
select * from books
where note='novel'
order by price DESC
limit 0,1;运行后的结果为：
// 21、找出书名中字数最多的一本书，不含空格
21.找出书名中字数最多的一本书，不含空格
select * from books
order by CHAR_LENGTH(replace(b_name,' ','')) desc
limit 0,1;运行后的结果为：
第2题
案例：
1、创建数据库：day02_test02_company
2、在此数据库下创建如下3表，数据类型，宽度，是否为空根据实际情况自己定义。
A． 部门表（department）：部门编号（depid），部门名称（depname），部门简介（deinfo）；其中部门编号为主键。
B． 雇员表（emoloyee）：雇员编号（empid），姓名（name），性别（sex），职称（title），出生日期（birthday），所在部门编号（depid）；其中
- 雇员编号为主键；
- 部门编号为外键，外键约束等级为（on update cascade 和on delete set null）；
- 性别默认为男；
C． 工资表（salary）：雇员编号（empid），基本工资（basesalary），职务工资（titlesalary），扣除（deduction）。其中雇员编号为主键。
3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
4、添加数据如下：
部门表：
部门编号 | 部门名称 | 部门简介 |
111 | 生产部 | Null |
222 | 销售部 | Null |
333 | 人事部 | 人力资源管理 |
雇员表：
雇员编号 | 姓名 | 性别 | 职称 | 出生日期 | 所在部门编号 |
1001 | 张三 | 男 | 高级工程师 | 1975-1-1 | 111 |
1002 | 李四 | 女 | 助工 | 1985-1-1 | 111 |
1003 | 王五 | 男 | 工程师 | 1978-11-11 | 222 |
1004 | 张六 | 男 | 工程师 | 1999-1-1 | 222 |
工资表：
雇员编号 | 基本工资 | 职务工资 | 扣除 |
1001 | 2200 | 1100 | 200 |
1002 | 1200 | 200 | NULL |
1003 | 2900 | 700 | 200 |
1004 | 1950 | 700 | 150 |
5、查询出每个雇员的雇员编号，姓名，职称，所在部门名称，应发工资（基本工资+职务工资），实发工资（基本工资+职务工资-扣除）。
6、查询销售部门的雇员姓名及其基本工资
7、查询姓“张”且年龄小于40的员工的全部信息和年龄
8、查询所有男员工的基本工资和职务工资
9、查询基本工资低于2000的员工姓名和职称、所在部门名称
10、查询员工总数
11、查询部门总数
12、查询应发工资的平均工资和最高工资、最低工资
13、按照部门统计应发工资的平均工资
14、找出部门基本工资的平均工资低于2000的
15、按照员工编号、姓名、基本工资、职务工资、扣除，并按照职务工资升序排列，如果职务工资相同，再按照基本工资升序排列
16、查询员工编号、姓名，出生日期，及年龄段，其中
如果80年之前出生的，定为”老年“；80后定为”中年“，90后定为”青壮年“
17、查询所有的员工信息，和他所在的部门名称
18、查询所有部门信息，和该部门的员工信息
19、查询所有职位中含“工程师”的男员工的人数
20、查询每个部门的男生和女生的人数和平均基本工资
//1.创建数据库：day02_test02_company
create database day02_test02_company;
use day02_test02_company;
//2.A.部门表（department）：部门编号（depid），部门名称（depname），部门简介（deinfo）；其中部门编号为主键。
create table department(
depid int(11) primary key,
depname varchar(50) not null,
deinfo varchar(50)
);
desc department;
//2.B． 雇员表（emoloyee）：雇员编号（empid），姓名（name），性别（sex），职称（title），出生日期（birthday），所在部门编号（depid）；其中
- 雇员编号为主键；
- 部门编号为外键，外键约束等级为（on update cascade 和on delete set null）；
- 性别默认为男；
create table employee(
empid int(11) primary key,
ename varchar(50) not null,
sex char(1) not null default '男',
title varchar(25),
brithday date,
depid int (11),
foreign key (depid) references department(depid) on update CASCADE on DELETE set null
);
desc employee;
//2.C 工资表（salary）：雇员编号（empid），基本工资（basesalary），职务工资（titlesalary），扣除（deduction）。其中雇员编号为主键。
create table salary(
empid int(11) primary key,
basesalary float,
titlesalary float,
deduction float
);
desc salary;运行后的结果为：
部门表
雇员表
工资表
// 3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
alter table salary
add FOREIGN KEY (empid)
REFERENCES employee (empid)
on update cascade on delete cascade;//4.添加数据（1）
insert into department values(111,'生产部',Null),
(222,'销售部',Null),
(333,'人事部','人力资源管理');
select * from department;运行后的结果为：
// 4.添加数据（2）
insert into employee values(1001,'张三','男','高级工程师','1975-1-1',111),
(1002,'李四','女','助工','1985-1-1',111),
(1003,'王五','男','工程师','1978-11-11',222),
(1004,'张六','男','工程师','1999-1-1',222);
select * from employee;运行后的结果为：
// 4.添加数据（3）
insert into salary values(1001,2200,1100,200),
(1002,1200,200,NULL),
(1003,2900,700,200),
(1004,1950,700,150);
select * from salary;运行后的结果为：
5、查询出每个雇员的雇员编号，姓名，职称，所在部门名称，应发工资（基本工资+职务工资），实发工资（基本工资+职务工资-扣除）。
select e.empid,ename,title,depname,
basesalary+titlesalary as '应发工资',basesalary+titlesalary-deduction as '实发工资'
from employee e
inner join department d
on e.depid=d.depid
inner join salary s
on e.empid =s.empid;运行后的结果为：
//6、查询销售部门的雇员姓名及其基本工资
select ename as '雇员姓名',basesalary as '基本工资'
from employee
inner join salary
on employee.empid = salary.empid
inner join department
on department.depid=employee.depid
where department.depid = 222;运行后的结果为：
//7、查询姓“张”且年龄小于40的员工的全部信息和年龄
select *,year(now())-year(brithday) as '年龄'
from employee
where ename like '张%' and year(now())-year(brithday)<40;运行后的结果为：
//8.查询所有男员工的基本工资和职务工资
select basesalary,titlesalary
from employee e
inner join salary s
on e.empid=s.empid
where sex='男';运行后的结果为：
//9.查询基本工资低于2000的员工姓名和职称、所在部门名称
select ename,title,depname
from employee e
inner join department d
on e.depid=d.depid
inner join salary s
on s.empid =e.empid
where basesalary<2000;运行后的结果为：
//10.查询员工总数
select count(*) as '员工的总人数'
from employee;运行后的结果为：
//11、查询部门总数
select count(*) as '部门的总数'
from department;运行后的结果为：
//12.查询应发工资的平均工资和最高工资、最低工资
select avg(basesalary+titlesalary) as '平均工资',max(basesalary+titlesalary) as '最高工资',min(basesalary+titlesalary) as '最低工资'
from salary;运行后的结果为：
13.按照部门统计应发工资的平均工资
select avg(basesalary+titlesalary) as '平均工资',depid
from salary s
inner join employee e
on s.empid=e.empid
group by depid;运行后的结果为：
//14、找出部门基本工资的平均工资低于2000的
select avg(basesalary) as '平均工资',depid
from salary s
inner join employee e
on s.empid=e.empid
group by depid
having avg(basesalary)<2000;运行后的结果为：
//15.按照员工编号、姓名、基本工资、职务工资、扣除，并按照职务升序排列，如果职务工资相同，再按照基本工资升序排列
select e.empid,ename,basesalary,titlesalary,deduction
from employee e
inner join salary s
on e.empid=s.empid
order by titlesalary ASC,basesalary asc;运行后的结果为：
//16.查询员工编号、姓名，出生日期，及年龄段，其中如果80年之前出生的，定为”老年“；80后定为”中年“，90后定为”青壮年“
select empid,ename,brithday,
CASE when year(brithday)<1980 then '老年'
when year(brithday)<1990 then '中年'
else '青壮年' end as '年龄段'
from employee;运行后的结果为：
//17.查询所有的员工信息，和他所在的部门名称
select e.*,depname
from employee e
inner join department d
on e.depid=d.depid;运行后的结果为：
//18.查询所有部门信息，和该部门的员工信息
select DISTINCT d.*,e.*
from department d
inner join employee e
on d.depid=e.depid;运行后的结果为：
//19、查询所有职位中含“工程师”的男员工的人数
select count(*) as ' 人数'
from employee
where title like '%工程师%' and sex='男'运行后的结果为：
//20、查询每个部门的男生和女生的人数和平均基本工资
select count(*),d.depid,avg(basesalary),sex
from department d
inner join employee e
on d.depid = e.depid
inner join salary s
on s.empid =e.empid
group by d.depid,sex;运行后的结果为：
总结
本节主要对select六大字句进行实战练习，数据库的查询在数据库操作中是很重要的，需要重点熟练掌握。
