查询案例一
-- 创建数据库
create database yy1 default charset utf8;
use yy1;
-- 创建表结构
create table person(
`id` int not null auto_increment primary key comment '编号',
`name` varchar(20) default '' comment '姓名',
`age` int(3) default '0' not null comment '年龄',
`team` varchar(20) default '' comment '门派',
`leader` int not null comment '掌门人',
`salary` int default '0' not null comment '工资'
)engine=innodb
default charset=utf8
comment='人员表';
create table location(
`id` int not null auto_increment primary key comment '编号',
`name` varchar(20) default '' not null comment '门派名',
`address` varchar(20) default '' not null comment '门派地址'
)engine=innodb
default charset=utf8
comment='地址表';
-- 添加数据
insert into person values
(null,'张三','100','武当','1','20000'),
(null,'张无忌','20','明教','0','8000'),
(null,'岳不群','40','华山','0','6500'),
(null,'东方不败','35','日月神教','0','12000'),
(null,'令狐冲','21','华山','3','4000'),
(null,'林平之','18','华山','3','2000'),
(null,'左冷禅','50','嵩山','0','10000'),
(null,'张翠山','46','武当','1','10000'),
(null,'宋远桥','55','武当','1','6500');
insert into location values
(null,'嵩山','河南'),
(null,'华山','陕西'),
(null,'日月神教','黑木崖')
-- 0.将武当派张三修改为张三丰
update person set name = '张三丰' where team = '武当' and name = '张三';
-- 1.查询所有人员?
select * from person;
-- 2.只查询人员的姓名和年龄?
select name,age from person;
-- 3.查询年龄为20岁的人有哪些?
select * from person where age = 20;
-- 4.查询60岁以下的人员有哪些?
select * from person where age > 60;
-- 5.查询50岁以上并且工资大于8000的人员有哪些?
select * from person where age > 50 and salary > 8000;
-- 6.查询姓[张]的人员有哪些?
select * from person where name like '张%';
-- 7.查询哪些人员属于 武当/华山/嵩山?
select * from person where team in ('武当','华山','嵩山');
-- 8.查询工资在 5000-8900的人员有哪些?
select * from person where salary between 5000 and 8900;
-- 9.查询所有人员,要求按工资倒序排列?
select * from person order by salary desc;
-- 10.查询年龄为21岁人员的领导人是谁?
select * from person where id = (select leader from person where age = 21);
-- 11.查询当前人员中最高工资是多少?
select name from person where salary = (select max(salary) from person);
-- 12.查询当前人员中最低工资是多少?
select name from person where salary = (select min(salary) from person);
-- 13.查询所有人员的平均工资是多少?
select avg(salary) from person;
-- 14.查询所有人员的工资总和是多少?
select sum(salary) from person;
-- 15.查询目前有多少个人员?
select count(1) from person;
-- 16.查询当前武林中有哪些门派?
select team from person group by team;
-- 17.查询 华山派 最高工资是谁?
select name from person where team = '华山' and salary = (select max(salary) from person where team = '华山');
-- 18.查询各门派的平均工资是多少?
select team,avg(salary) from person group by team;
-- 19.查询当前武林中有哪些门派的平均工资大于8000?
select team from person group by team having avg(salary) > 8000;
-- 20.查询当前人员表的中的第3条数据到第7条数据?
select * from person limit 2,5;
-- 21.查询没有门派的人员有哪些?
select name from person where team is null;
-- 22.查询武当派下有哪些小弟?
select name from person where team = '武当' and leader != (select id from person where team = '武当' and leader = '0');
-- 23.查询各门派的工资总和按倒序/正序排列
select team,sum(salary) from person group by team order by sum(salary) asc;
-- 24.查询人员并显示门派所在位置
select p.team,l.address from person as p inner join location as l on p.team = l.name;
-- 25.查询人员表,如果人员门派存在位置则显示位置信息,不存在则不显示
select p.team,l.address from person as p right join location as l on p.team = l.name;
-- 26.查询位置表,如果人员的门派有位置信息则显示人员,没有则不显示.
select l.address,p.team from location as l left join person as p on l.name = p.team;
-- 27.查询登记了地理位置的门派人员信息
select p.team from person as p,location as l where p.team = l.name group by l.name;
-- 28.删除工资重复的人员
delete from person where age not in
(select * from (select max(age) from person group by team having count(1) > 1) as a)
and team in
(select * from (select team from person group by team having count(1) > 1) as b);
-- 29.删除工资同一个门派的人员,请保留年龄最大的一个人
select * from person group by team having count(1) > 1
-- 30.删除不满意的数据
truncate table person;
truncate location;
查询案例二
-- 创建数据库
create database yy2 default charset utf8;
use yy2;
-- 创建表结构
create table liuyan(
`id` int not null auto_increment primary key comment '编号',
`title` varchar(32) not null comment '标题',
`author` varchar(16) comment '作者',
`addtime` datetime not null comment '留言时间',
`content` text not null comment '留言内容',
`isdelete` char(1) default '0' not null comment '是否删除'
)engine=innodb
default charset=utf8
comment='留言信息表';
-- 1. 在留言表最后添加一列状态(status char(1) 默认值为0)
alter table liuyan add `status` char(1) default '' comment '状态';
-- 2. 修改留言表author的默认值为’youku’,设为非空
alter table liuyan modify `author` varchar(16) default 'youku' not null comment '作者';
-- 3. 删除liuyan表中的isdelete字段
alter table liuyan drop isdelete;
-- 4. 为留言表添加>5条测试数据
insert into liuyan values
(null,'python','李岩',now(),'starting python','0'),
(null,'linux','岩哥',now(),'ok linux','1'),
(null,'HR','丫丫',now(),'this is `aaa`','0'),
(null,'php','李岩',now(),'html/css/javascript','0'),
(null,'html','丫丫',now(),'"pink"','1'),
(null,'windows','岩哥',now(),'services.msc','1');
-- 5.要求将id值大于3的信息中author字段值改为admin
update liuyan set author = 'admin' where id > 3;
-- 6.删除id号为4的数据
delete from liuyan where id = 4;
-- 7.为留言表添加>15条测试数据，要求分三个用户添加
insert into liuyan values
(null,'t1','admin',now(),'info1','1'),
(null,'t2','yy',now(),'info2','1'),
(null,'t3','yan',now(),'info3','0'),
(null,'t4','王',now(),'info4','0'),
(null,'t5','admin',now(),'info5','0'),
(null,'t6','王',now(),'info6','1'),
(null,'t7','yan',now(),'info7','0'),
(null,'t8','王',now(),'info8','1'),
(null,'t9','王',now(),'info9','1'),
(null,'t10','admin',now(),'info10','0'),
(null,'t11','yy',now(),'info11','0'),
(null,'t12','yan',now(),'info12','1'),
(null,'t13','yan',now(),'info13','1'),
(null,'t14','王',now(),'info14','0'),
(null,'t15','admin',now(),'info15','1'),
(null,'t16','yy',now(),'info16','1');
-- 8.查询所有留言信息
select * from liuyan;
-- 9.查询某一用户的留言信息
select * from liuyan where author = 'yy';
-- 10.查询所有数据，按时间降序排序
select * from liuyan order by addtime desc;
-- 11.获取id在2到6之间的留言信息，并按时间降序排序
select * from liuyan where id between 2 and 6 order by id desc;
-- 12.统计每个用户留了多少条留言，并对数量按从小到大排序
select author,count(1) from liuyan group by author order by count(1) asc;
-- 13.将id为8、9的两条数据的作者改为’doudou’
update liuyan set author = 'doudou' where id in (8,9);
-- 14.使用limit取出最新的三条留言
select * from liuyan order by addtime desc limit 0,3;
-- 15.查询留言者中包含”a”字母的留言信息，并按留言时间从小到大排序
select * from liuyan where content like '%a%' order by addtime asc;
-- 16.删除”作者”重复的数据,并保留id最大的一个作者
delete from liuyan where author in
(select * from (select author from liuyan group by author having count(1) > 1) as a)
and id not in
(select * from (select max(id) from liuyan group by author having count(1) > 1) as b);
查询案例三
-- 创建数据库
create database yy3 default charset utf8;
use yy3;
-- 创建表结构
create table user(
`id` int not null auto_increment primary key comment '卡号',
`name` varchar(20) default '' not null comment '姓名',
`class` int(2) not null comment '班级'
)engine=innodb
default charset=utf8
comment='用户表';
create table book(
`id` int not null auto_increment primary key comment '书号',
`name` varchar(20) default '' not null comment '书名',
`author` varchar(10) default '' not null comment '作者',
`price` decimal(4,2) default '0.00' not null comment '单价',
`number` int(5) default '0' not null comment '库存'
)engine=innodb
default charset=utf8
comment='图书表';
create table recard(
`id` int not null auto_increment primary key comment '编号',
`u_id` int not null comment '卡号',
`b_id` int not null comment '书号',
`redate` datetime not null comment '归还日期',
constraint `fu_id` foreign key (`u_id`) references `user` (`id`),
constraint `fb_id` foreign key (`b_id`) references `book` (`id`),
key `u_id` (`u_id`),
key `b_id` (`b_id`),
unique key `user_book_id` (`u_id`,`b_id`) using btree
)engine=innodb
default charset=utf8
comment='归还记录表';
-- 添加数据
insert into user values
(null,'zhangsan','1'),
(null,'lisi','2'),
(null,'wanger','3'),
(null,'mazi','1'),
(null,'chenwu','2'),
(null,'fengliu','3');
insert into book values
(null,'linux','zhao','35.8','10'),
(null,'python','qian','55.4','20'),
(null,'mysql','sun','22.6','30'),
(null,'php','li','78.8','40'),
(null,'java','zhou','25.3','50'),
(null,'html','wu','40.8','45'),
(null,'css','zheng','46.6','35'),
(null,'js','wang','33.9','25'),
(null,'go','feng','23','15'),
(null,'ruby','chen','36.2','60');
insert into recard values
(null,1,1,'2017-12-13 19:30:01'),
(null,2,1,'2017-12-13 19:30:02'),
(null,3,3,'2017-12-13 19:30:03'),
(null,4,8,'2017-12-13 19:30:04'),
(null,5,4,'2017-12-13 19:30:05'),
(null,6,10,'2017-12-13 19:30:06'),
(null,1,9,'2017-12-13 19:30:07'),
(null,2,3,'2017-12-13 19:30:08'),
(null,3,5,'2017-12-13 19:30:09'),
(null,4,6,'2017-12-13 19:30:10'),
(null,5,9,'2017-12-13 19:30:11'),
(null,6,1,'2017-12-13 19:30:12'),
(null,1,3,'2017-12-13 19:30:13'),
(null,2,4,'2017-12-13 19:30:14'),
(null,3,8,'2017-12-13 19:30:15'),
(null,4,10,'2017-12-13 19:30:16'),
(null,5,7,'2017-12-13 19:30:17'),
(null,6,6,'2017-12-13 19:30:18'),
(null,1,8,'2017-12-13 19:30:19'),
(null,2,9,'2017-12-13 19:30:20'),
(null,3,6,'2017-12-13 19:30:21'),
(null,4,2,'2017-12-13 19:30:22'),
(null,5,2,'2017-12-13 19:30:23');
-- 1. 找出借书超过3本的读者,输出借书卡号及所借图书册数
select r.u_id as '卡号',count(1) as '借书数量' from recard as r group by r.u_id having count(1) > 3;
-- 2. 查询借阅了"python"一书的读者，输出姓名及班级
select name as '姓名',class as '班级' from user where id in (
select u_id from recard where b_id = (
select id from book where name = 'python'
)
)
-- 3. 查询书名包括"n"关键字的图书，输出书号、书名、作者
select id as '书号',name as '书名',author as '作者' from book where name like '%n%'
-- 4. 查询现有图书中价格最高的图书，输出书名及作者
select name as '书名',author as '作者' from book where price = (select max(price) from book);
-- 5. 查询当前借了"python"但没有借"linux"的读者，输出其借书卡号，并按卡号降序排序输出
select u_id from recard where b_id in
(select id from book where name = 'python')
and b_id not in
(select id from book where name = 'linux')
order by u_id desc;
-- 6. 如果经常按书名查询图书信息，请建立合适的索引
alter table book add index `name`(`name`) using btree;
-- 7. 查询"1"班学生的借书信息，显示出姓名和书名
select u.name as '姓名',b.name as '书名' from recard as r,user as u,book as b
where r.u_id = u.id and r.b_id = b.id and u.class = 1;
-- 8. 查询当前同时借有"python"和"go"两本书的读者，输出其借书卡号，并按卡号升序排序输出
select r.u_id from recard as r inner join book as b on r.b_id = b.id
where b.name in ('python','go') group by u_id having count(1) = 2 order by r.u_id asc;
-- 9. 将USER表的NAME最大列宽增加到50个字符
alter table user modify `name` varchar(50) default '' not null comment '姓名';
-- 10. 为USER表增加1列(系名)，可变长，最大20个字符
alter table user add `department` varchar(20) comment '系名';
浙公网安备 33010602011771号