MySql作业练习题

Post author:xfxia
Post published:2023年8月26日
Post category:mysql

第一题

在这里插入图片描述

删除classno列
修改tel的列名为phone
刘六学员信息不存在，删除相关信息
删除语文成绩在85到90之间的学生信息
删除家庭住址在通州区并且学生成绩不及格的学生信息
删除学号为1，3，6, 9的学生。
修改学号为5的地址为郑州第八大街和学分88分

第二题

2. 把下面的sql写出来

```sql
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;		//检测是否存在表`course`，存在删掉，不存在不执行

CREATE TABLE `course` (
  `cs_id` int(11) NOT NULL COMMENT '课程编号',
  `cs_name` varchar(50) NOT NULL COMMENT '课程名称',
  `cs_credit` tinyint(255) unsigned DEFAULT NULL COMMENT '课程学分',
  `cs_type` char(12) DEFAULT NULL COMMENT '课程类别',
  `cs_depart` char(6) DEFAULT NULL COMMENT '院系名称',
  PRIMARY KEY (`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */
insert into `course`(`cs_id`,`cs_name`,`cs_credit`,`cs_type`,`cs_depart`) values (5200313,'数据库原理及应用',4,'核心专业','信工'),(5203314,'计算机导论',4,'通识教育','信工'),(5219314,'数据结构',5,'专业核心','信工'),(5223013,'大学物理',4,'专业基础','信工'),(5227614,'毕业实习',4,'集中实践','信工'),(5230912,'云计算',2,'共同选修','信工'),(5236212,'机器学习',2,'共同选修','信工'),(5237514,'c语言',4,'专业基础','信工'),(5245112,'区块链',2,'任意选修','信工'),(7200422,'知识产权法',2,'任意选修','文法'),(20201833,'概率论',3,'专业基础','基础'),(20202336,'高等数学',6,'专业基础','基础'),(29299131,'劳动教育',1,'集中实践','学务');



1. 查询全部课程的信息

2. 查询信工学院开设的课程名、课程号及学分

3.查询学分超过3学分的课程代码、课程名和开课单位


4.查询学分在2到4之间课程的信息


5.查询课程名称中带“数据”的课程名、课程号及开课单位

6.查询不是信工学院开设的集中实践课的开课单位和课程名称

7.查询信工学院开设的课程的类型有哪些

-- 1. 查询全部课程的信息
select * from course;

-- 2. 查询信工学院开设的课程名、课程号及学分
select cs_name,cs_id,cs_credit from course;

-- 3.查询学分超过3学分的课程代码、课程名和开课单位
select cs_id,cs_name,cs_depart from course where cs_credit>3;

-- 4.查询学分在2到4之间课程的信息
select * from course where cs_credit between 2 and 4;

-- 5.查询课程名称中带“数据”的课程名、课程号及开课单位
select cs_name,cs_id,cs_depart from course where cs_name like '%数据%';

-- 6.查询不是信工学院开设的集中实践课的开课单位和课程名称
select cs_depart , cs_name from course where cs_depart != '信工' and cs_type='集中实践';

-- 7.查询信工学院开设的课程的类型有哪些
select cs_type from course where cs_depart='信工';

第三题

/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;		//检测是否存在表`student`，存在删掉，不存在不执行

CREATE TABLE `student` (
  `stu_id` bigint(11) unsigned NOT NULL COMMENT '学号',
  `stu_name` char(12) NOT NULL COMMENT '姓名',
  `stu_sex` enum('男','女') DEFAULT NULL COMMENT '性别',
  `stu_age` tinyint(255) unsigned DEFAULT NULL COMMENT '年龄',
  `stu_major` char(9) DEFAULT NULL COMMENT '专业',
  `stu_college` char(12) DEFAULT NULL COMMENT '学院',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */
insert into `student`(`stu_id`,`stu_name`,`stu_sex`,`stu_age`,`stu_major`,`stu_college`) values (201804550101,'郭奎','男',22,'计科','信工学院'),(201804550102,'吕宇航','男',18,'计科','信工学院'),(201804550103,'张豪辉','女',19,'计科','信工学院'),(201804550107,'丁志杰','男',17,'金融学','金贸学院'),(201804550109,'范伟','男',19,'金融学','金贸学院'),(201804550116,'张依婷','女',17,'大数据','信工学院'),(201804550120,'张维','男',19,'计科','信工学院'),(201804550121,'朱柳阳','女',20,'计科','信工学院'),(201804550144,'谭兵炎','男',20,'大数据','信工学院'),(201804550153,'杨志强','男',17,'大数据','信工学院');

1.查询计科专业和大数据专业的学生信息


2.查询不是信工学院的学生姓名和学号

3.查询年龄是17,18,19的学生姓名和专业

4.查询信工学院的的专业有哪些

5.查询年龄为空的学生信息

6.查询学生的信息，查询结果按姓名升序排序

-- 1.查询计科专业和大数据专业的学生信息
select * from stu where stu_major='计科' or stu_major='大数据';

-- 2.查询不是信工学院的学生姓名和学号
select stu_name,stu_id from stu where stu_college != '信工学院';

-- 3.查询年龄是17,18,19的学生姓名和专业
select stu_name,stu_major from stu where stu_age in (17,18,19);

-- 4.查询信工学院的的专业有哪些
select  distinct stu_major  from stu where stu_college='信工学院'; 

-- 5.查询年龄为空的学生信息
select * from stu where stu_age is null;

-- 6.查询学生的信息，查询结果按姓名升序排序
select * from stu order by stu_name asc;

第四题

create table student
(
    id int(10) primary key,
    name varchar(20) not null,
    sex varchar(4),
    birth year,
    department varchar(20) not null,
    address varchar(50)
);

create table score
(
    id int(10) primary key,
    stu_id int(10) not null,
    c_name varchar(20),
    grade int(10)
);
insert into student(id,name,sex,birth,department,address)
    values(901,'张老大','男',1985,'计算机系','北京市海淀区'),
        (902,'张老二','男',1986,'中文系','北京市昌平区'),
        (903,'张三','女',1990,'中文系','湖南省永州市'),
        (904,'李四','男',1990,'英语系','辽宁省阜新市'),
        (905,'王五','女',1991,'英语系','福建省厦门市'),
        (906,'王六','男',1988,'计算机系','湖南省衡阳市');
insert into score(id,stu_id,c_name,grade)
    values(1,901,'英语',80),
        (2,902,'计算机',65),
        (3,902,'中文',88),
        (4,903,'中文',95),
        (5,904,'计算机',70),
        (6,904,'英语',92),
        (7,905,'英语',94),
        (8,906,'计算机',90),
        (9,901,'计算机',null),
        (10,901,'中文',null),
        (11,902,'英语',null);
        
a) 查询student表的所有记录
b) 查询student表的第2条到4条记录
c) 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
d) 从student表中查询计算机系和英语系的学生的信息（写出两种答案）
f) 从student表中查询每个院系有多少人
g) 从score表中查询每个科目的最高分
h) 计算每个考试科目的平均成绩
i) 将计算机考试成绩按从高到低进行排序

-- a) 查询student表的所有记录
select * from student;

-- b) 查询student表的第2条到4条记录
select * from student limit 2,3;

-- c) 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
select id,name,department from student;

-- d) 从student表中查询计算机系和英语系的学生的信息（写出两种答案）
select * from student where department='计算机系' or department='英语系';
select id,name,sex,birth,department,address from student where department='计算机系' || department='英语系';

-- f) 从student表中查询每个院系有多少人
select department , count(*) from student group by department;

-- g) 从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;

-- h) 计算每个考试科目的平均成绩
select c_name,avg(grade) from score group by c_name;

-- i) 将计算机考试成绩按从高到低进行排序 
select * from score where c_name='计算机' order by grade desc;

第五题

create table tb_emp(
    id int primary key auto_increment COMMENT '员工编号',
		name varchar(20) COMMENT '员工姓名',
		age int COMMENT '员工年龄',
		job varchar(20) COMMENT '岗位',
		salary int COMMENT '薪水',
		entrydate date COMMENT '员工入职时间',
		managerid int COMMENT '员工领导编号',
		dept_id int COMMENT '员工所在部门编号'
);
create table tb_dept(
    id int primary key auto_increment,
		name varchar(20)
);
insert into tb_dept(name) values('研发部'),('市场部'),('财务部'),('销售部'),('总经办');

insert into tb_emp values 
(null,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(null,'张无忌',20,'项目经理',12500,'2005-12-01',1,1),
(null,'杨逍',33,'开发',8400,'2000-11-03',2,1),
(null,'韦一笑',48,'开发',11000,'2002-03-05',2,1),
(null,'常豫川',43,'开发',10500,'2004-09-01',2,1),
(null,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
(null,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
(null,'周芷若',60,'会计',48000,'2006-06-01',7,3),
(null,'丁敏君',19,'出纳',4000,'2009-06-01',7,3),
(null,'赵敏',23,'市场总监',14000,'2009-06-01',1,2),
(null,'鹿仗客',56,'职员',3750,'2009-06-01',10,2),
(null,'鹤比翁',19,'职员',3750,'2009-06-01',10,2),
(null,'方东白',19,'职员',3750,'2009-06-01',10,2),
(null,'张三丰',88,'销售总监',14000,'2004-06-01',1,4),
(null,'玉莲舟',38,'销售',4600,'2009-06-01',14,4),
(null,'宋远桥',40,'销售',4600,'2009-06-01',14,4),
(null,'陈友谅',42,null,2000,'2011-06-01',1,null);
create table salglrade(
grade int,
losal int,
hisal int
) comment '薪资等级表;
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);


1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。


2：查询每个雇员和其所在的部门名

3：查询每个雇员姓名及其工资所在的等级

4：查询雇员名第2个字母不是敏的雇员的姓名、所在的部门名、工资所在的等级。

5：查询每个雇员和其经理的姓名

6：查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））

7：查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）

8：查询每个部门中工资最高的人的姓名、薪水和部门编号

9：查询每个部门平均工资所在的等级

10．查询员工的姓名、年龄、职位、部门信息―（隐式内连接)

11．查询年龄小于30岁的员工的姓名、年龄、职位、部门信息〈显式内连接）

12．查询拥有员工的部门ID、部门名称

13．查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来


14.查询所有员工的工资等级

15．查询“研发部”所有员工的信息及工资等级

16．查询“研发部”员工的平均工资


17．查询工资比“灭绝”高的员工信息。

18．查询比平均薪资高的员工信息

19．查询所有的部门信息，并统计部门的员工人数

-- 1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
select id,name,salary,dept_id from tb_emp where salary=(select max(salary) from tb_emp);

-- 2：查询每个雇员和其所在的部门名
select tb_emp.name,tb_dept.`name` from tb_emp join tb_dept where tb_emp.dept_id = tb_dept.id;

-- 3：查询每个雇员姓名及其工资所在的等级
select e.`name`,s.grade from tb_emp e, salgrade s where e.salary between s.losal and s.hisal;

-- 4：查询雇员名第2个字母不是敏的雇员的姓名、所在的部门名、工资所在的等级。

select e.id,e.`name`,s.grade,d.`name` from tb_emp e, salgrade s ,tb_dept d where e.salary between s.losal and s.hisal and e.dept_id=d.id and substring(e.name,2,1)!='敏' order by e.id;

-- 5：查询每个雇员和其经理的姓名
select a.name ,b.name from tb_emp a join tb_emp b on a.managerid = b.id;

-- 6：查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid = b.id;

-- 7：查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）
select tb_emp.`name`,tb_dept.`name` from tb_emp right join tb_dept on tb_emp.dept_id = tb_dept.id; 

-- 8：查询每个部门中工资最高的人的姓名、薪水和部门编号
-- a)求每个部门中工资最高的薪资
select max(tb_emp.salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id group by tb_dept.name;

-- b)从员工表中找到属于这些薪资的人
select e.name,e.salary,e.dept_id from tb_emp e where e.salary in(select max(tb_emp.salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id group by tb_dept.name);


-- 9：查询每个部门平均工资所在的等级
-- a)求每个部门的平均薪水
select avg(tb_emp.salary),tb_emp.dept_id from tb_emp group by tb_emp.dept_id;

-- b)从结果集变成一个临时表 在这张表中和工资表进行连接 筛选平均工资 之后的等级
select emp.*,g.grade from(
select avg(tb_emp.salary) ag,tb_emp.dept_id from tb_emp group by tb_emp.dept_id)emp 
join salgrade g on emp.ag between g.losal and g.hisal;



-- 10．查询员工的姓名、年龄、职位、部门信息―（隐式内连接)
select tb_emp.`name`,tb_emp.age,tb_emp.job,tb_dept.`name` from tb_emp ,tb_dept where tb_emp.dept_id=tb_dept.id;

-- 11．查询年龄小于30岁的员工的姓名、年龄、职位、部门信息〈显式内连接）
select tb_emp.`name`,tb_emp.age,tb_emp.job,tb_dept.`name` from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_emp.age < 30;

-- 12．查询拥有员工的部门ID、部门名称
select e.name,e.dept_id,a.name from tb_emp e join tb_dept a on e.dept_id = a.id; 

-- 13．查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来
select e.name,e.age,a.name from tb_emp e left join tb_dept a on e.dept_id = a.id where e.age>40; 

-- 14.查询所有员工的工资等级
select tb_emp.name,tb_emp.salary,salgrade.grade from tb_emp,salgrade where tb_emp.salary between salgrade.losal and salgrade.hisal;

-- 15．查询“研发部”所有员工的信息及工资等级
-- a)查询研发部的姓名
select tb_emp.name from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部';

select e.*,s.grade from tb_emp e,salgrade s where e.salary between s.losal and s.hisal and e.name in(select tb_emp.name from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部') order by e.id ;

-- 第二种写法
select e.*,s.grade from tb_emp e 
left join salgrade s on e.salary BETWEEN s.losal and s.hisal 
left join tb_dept d on e.dept_id = d.id 
where d.name='研发部'
;

-- 16．查询“研发部”员工的平均工资
select avg(salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部';

-- 17．查询工资比“灭绝”高的员工信息。
-- a)先查灭绝的工资
select max(salary) from tb_emp where name='灭绝';

select * from tb_emp where salary>(select max(salary) from tb_emp where name='灭绝');

-- 18．查询比平均薪资高的员工信息
-- a)先查平均薪资
select avg(salary) from tb_emp;
-- b)再查比平均薪资高的人
select tb_emp.* from tb_emp where tb_emp.salary>(select avg(salary) from tb_emp);
-- 19．查询所有的部门信息，并统计部门的员工人数
	select tb_dept.name,count(*),tb_emp.dept_id from tb_emp right join tb_dept  on tb_emp.dept_id=tb_dept.id group by tb_emp.dept_id;

第六题

create table readers(
  reader_id int(7) primary key,-- 读者id
  name varchar(8),-- 读者姓名
  sex varchar(2) default '男' check(sex in('男','女')),
  dept varchar(16),-- 读者所在部门
  status varchar(8),-- 学历
  address varchar(30)-- 地址
);
-- 图书信息表
create table books(
 book_id int(6) primary key,-- 图书编号
 type_id varchar(3),-- 类型编号
 book_name varchar(50),-- 图书名
 author varchar(50),-- 作者
 publisher varchar(50),-- 出版社
 price int(3)-- 价格
);
-- 借阅信息表
create table borrow_info(
  reader_id int(7),-- 读者id
  book_id int(6),-- 图书Id
  borrow_time date,-- 借书时间
  notes varchar(200),-- 标志
 primary key(reader_id,book_id)
);

-- 添加数据readers
insert into readers values('0034103','范元帅','男','艺术学院','本科生','师大南院');
insert into readers values('0034301','杨凡','男','电信学院','教师','八里台禄小区');
insert into readers values('0034429','许丹丹','女','艺术学院','本科生','师大南院');
insert into readers values('0134101','陈超','男','计算机学院','本科生','师大南院');
insert into readers values('0134102','范金良','男','计算机学院','本科生','师大南院');
insert into readers values('0134103','国皓','男','计算机学院','本科生','师大南院');
insert into readers values('0134104','贺云龙','男','计算机学院','本科生','师大南院');
insert into readers values('0134105','刘德文','男','计算机学院','本科生','师大南院');
insert into readers values('0134106','彭俊','男','计算机学院','本科生','师大南院');
insert into readers values('0134107','彭志成','男','计算机学院','本科生','师大南院');
insert into readers values('0134108','沈举','男','计算机学院','本科生','师大南院');
insert into readers values('0134109','田强','男','计算机学院','本科生','师大南院');
insert into readers values('0134110','王旭','男','计算机学院','本科生','师大南院');
insert into readers values('0134111','吴卫','男','计算机学院','本科生','师大南院');
insert into readers values('0134112','熊敏','男','计算机学院','本科生','师大南院');
insert into readers values('0134113','颜东','男','计算机学院','本科生','师大南院');
insert into readers values('0134114','殷建鹏','男','计算机学院','本科生','师大南院');
insert into readers values('0134115','周春林','男','计算机学院','本科生','师大南院');
insert into readers values('0134116','陈洁','女','计算机学院','本科生','师大南院');
insert into readers values('0134117','陈琪','女','计算机学院','研究生','师大南院');
insert into readers values('0134118','丁璐','女','计算机学院','研究生','师大南院');
insert into readers values('0134119','丁岩','女','计算机学院','本科生','师大南院');
insert into readers values('0134120','董蕾','女','计算机学院','本科生','师大南院');
insert into readers values('0134121','何芳','女','计算机学院','本科生','师大南院');
insert into readers values('0134122','蒋莎','女','计算机学院','本科生','师大南院');
insert into readers values('0134123','李颖','女','计算机学院','研究生','师大南院');
insert into readers values('0134124','李芬','女','计算机学院','研究生','师大南院');
insert into readers values('0134125','李锦萍','女','计算机学院','教授','师大南院');
insert into readers values('0134126','李小汐','女','计算机学院','本科生','师大南院');
insert into readers values('0134127','李玥玖','女','计算机学院','本科生','师大南院');
insert into readers values('0134128','郦莎','女','计算机学院','本科生','师大南院');
insert into readers values('0134129','廖英','女','计算机学院','本科生','师大南院');
insert into readers values('0134130','林琳','女','计算机学院','教师','师大南院');
insert into readers values('0134131','林敏','女','计算机学院','本科生','师大南院');

insert into books values('109101','BAS','高等数学','李方健','清华大学出版社','20');
insert into books values('109104','BAS','离散数学','孙德风','天津大学出版社','22');
insert into books values('109107','ELC','数字电路','刘国庆','高等教育出版社','13');
insert into books values('109109','CMP','C语言程序设计','谭浩强','清华大学出版社','15');
insert into books values('109110','CMP','数据结构','王志国','高等教育出版社','32');
insert into books values('109111','CMP','操作系统','王志国','高等教育出版社','25');
insert into books values('109112','CMP','计算机组成原理','张小敏','南大在学出版社','27');
insert into books values('109113','CMP','微机原理与接口技术','刘国庆','人民大学出版社','34');
insert into books values('109114','CMP','数据库原理','彭来德','高等教育出版社','16');
insert into books values('109115','CMP','计算机网络','马国露','人民大学出版社','11');
insert into books values('109116','CMP','计算机网络','谭浩强','南大在学出版社','13');
insert into books values('109117','CMP','编译原理','方刚','清华大学出版社','38');
insert into books values('209101','CMP','VB与WINDOWS程序设计','谭浩强','清华大学出版社','30');
insert into books values('209102','CMP','C++与面向对象技术','谭浩强','人民大学出版社','19');
insert into books values('209103','CMP','Java与网络程序设计','付勇','高等教育出版社','20');
insert into books values('209106','CMP','单片机原理及应用','刘国庆','人民大学出版社','22');
insert into books values('209107','CMP','PLC原理及其应用开发','刘国庆','南大在学出版社','16');
insert into books values('209111','CMP','人工智能导论','丁宝康','高等教育出版社','18');
insert into books values('209116','CMP','信息论与编码学概论','丁宝康','高等教育出版社','21');
insert into books values('209117','CMP','密码学基础','张顺志','人民大学出版社','25');
insert into books values('209123','CMP','Internet应用及网页设计','李朋','高等教育出版社','16');
insert into books values('209124','CMP','多媒体技术及应用','谭浩强','高等教育出版社','15');
insert into books values('209130','CMP','PB与数据库应用开发','张华强','清华大学出版社','42');
insert into books values('209132','CMP','计算机图形学','徐志超','人民大学出版社','27');
insert into books values('309102','CMP','计算机学科教学论','丁宝康','高等教育出版社','12');
insert into books values('309103','CMP','中学计算机教材研究与分析','徐志超','人民大学出版社','14');
insert into books values('309104','CMP','信息技术概论','付勇','天津大学出版社','24');
insert into books values('309105','CMP','数据库分析与设计','丁宝康','人民大学出版社','30');
insert into books values('309106','CMP','数据库导论','付勇','清华大学出版社','29');


insert into borrow_info values('0034103','109101','2005-6-2 00:00:00','NULL');
insert into borrow_info values('0034301','209130','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0034429','109107','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134101','109109','2005-6-3 00:00:00','NULL');
insert into borrow_info values('0134102','109110','2005-6-30 00:00:00','NULL');
insert into borrow_info values('0134103','109111','2005-6-13 00:00:00','NULL');
insert into borrow_info values('0134104','109112','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134105','109113','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0134106','109114','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134107','109115','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134108','109116','2005-6-12 00:00:00','NULL');
insert into borrow_info values('0134109','109117','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134110','209101','2005-6-22 00:00:00','NULL');
insert into borrow_info values('0134111','209102','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134112','209103','2005-6-16 00:00:00','NULL');
insert into borrow_info values('0134113','209106','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134114','209107','2005-6-11 00:00:00','NULL');
insert into borrow_info values('0134115','209111','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134116','209106','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0034301','209132','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0034301','309102','2005-6-9 00:00:00','NULL');
insert into borrow_info values('0034301','309103','2005-6-21 00:00:00','NULL');
insert into borrow_info values('0034301','309104','2005-6-14 00:00:00','NULL');
insert into borrow_info values('134101','109101','2005-6-1 00:00:00','NULL');
insert into borrow_info values('134102','109104','2005-6-8 00:00:00','NULL');
insert into borrow_info values('134104','109107','2005-6-1 00:00:00','NULL');
insert into borrow_info values('134105','109109','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134426','109110','2005-6-15 00:00:00','NULL');
insert into borrow_info values('0134427','109111','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134428','109112','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134429','109113','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','109114','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134431','109115','2005-6-1','NULL');
insert into borrow_info values('0134432','109116','2005-6-14','NULL');
insert into borrow_info values('0134433','109117','2005-6-1','NULL');
insert into borrow_info values('0134434','209101','2005-6-2','NULL');
insert into borrow_info values('0134435','209102','2005-6-1','NULL');
insert into borrow_info values('0134430','209103','2005-6-5','NULL');
insert into borrow_info values('0134430','209106','2005-6-5','NULL');
insert into borrow_info values('0134430','209107','2005-6-21','NULL');
insert into borrow_info values('0134430','209111','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','209116','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','209124','2005-6-5 00:00:00','NULL');

-- 1、检索读者“杨凡”所在单位
select dept from readers where name='杨凡';

-- 2、检索所有读者的全部信息
select * from readers;

-- 3、检索图书馆中所有藏书的书名和出版单位
select book_name,publisher from books;

-- 4、检索“人民大学出版社”所有的书名和单价，结果按照单价降序排列
select book_name,price from books where publisher='人民大学出版社' order by price desc;

-- 5、检索价格在10元至15元之间的图书的名称、作者、单价和分类号，结果按分类号和单价升序排列
select book_name,author,price,type_id from books order by type_id,price;

-- 6、检索“人民大学出版社”和“清华大学出版社”的所有图书的名称和作者
select book_name,author from books where publisher in('人民大学出版社','清华大学出版社');

-- 7、检索书名以“数据库”开头的所有图书的书名和作者
select book_name,author from books where book_name like '数据库%';

-- 8、检索同时借了总编号为209116和209124两本图书的借书证号
select reader_id from borrow_info where book_id=209116;

select * from borrow_info where  book_id=209124 and reader_id=(select reader_id from borrow_info where book_id=209116);

-- 9、检索所有借阅了图书的读者姓名和所在单位
select name,dept from readers;

-- 10、检索“扬凡”所借的所有图书的书名和借阅日期
select reader_id from readers where name='杨凡'; 

select   bk.book_name,br.borrow_time from borrow_info br join books bk on br.book_id=bk.book_id
where br.reader_id =  (select reader_id from readers where name='杨凡');

-- 11、检索价格在20元以上且已经借出的图书，结果按单价降序排列
select distinct bk.book_name,bk.price from books bk join borrow_info  br on bk.book_id = br.book_id where bk.price>20
order by bk.price desc;


-- 12、检索借阅了“C语言程序设计”一书的读者姓名和所在单位

select book_id from books where book_name='C语言程序设计';

select r.name,r.address from readers r join borrow_info br on r.reader_id = br.reader_id where br.book_id=(select book_id from books where book_name='C语言程序设计');

-- 13、检索与“杨凡”在同一天借阅了图书的读者的姓名和所在单位
-- a)杨凡借阅的时间
select br.borrow_time from borrow_info br join readers r on br.reader_id = r.reader_id where r.name='杨凡';

select distinct r.name,r.dept from readers r join borrow_info br on br.reader_id = r.reader_id where br.borrow_time in
(select br.borrow_time from borrow_info br join readers r on br.reader_id = r.reader_id where r.name='杨凡') and r.name!='杨凡';

-- 14、检索藏书中比“高等教育出版社”的所有图书的单价更高的图书
-- a)求高等教育出版社的图书最高价格
select max(price) from books where publisher = '高等教育出版社';

select book_name from books where price >(select max(price) from books where publisher = '高等教育出版社');

-- 15、检索藏书中所有与“数据库导论”或“数据库原理”在同一出版社出版的图书

select publisher from books where book_name in('数据库导论','数据库原理');

select book_name from books where publisher in(select publisher from books where book_name in('数据库导论','数据库原理'));


-- 16、求该图书馆藏书的总册数

select count(*) from books;

-- 17、求“高等教育出版社”的图书中最高的价格、最低的价格以及平均价格
select max(price),min(price),avg(price) from books where publisher='高等教育出版社';

-- 18、求“计算机学院”当前借阅了图书的读者人数
select dept,count(*) from readers r join borrow_info br on r.reader_id=br.reader_id where dept='计算机学院';

-- 19、求各个出版社的最高价格、最低价格、平均价格
select max(bk.price),min(bk.price),avg(bk.price),bk.publisher from books bk
group by bk.publisher;

-- 20、分别求出各个单位当前借阅图书的读者人数
select dept,count(*) from readers join borrow_info on readers.reader_id=borrow_info.reader_id  group by dept;

-- 21、求各个出版单位的册书、价格总额，并按总价降序排列，如有总价相同者按出版社名称降序排列
select publisher,count(*),sum(price) from books group by publisher order by sum(price) desc,publisher desc;

-- 22、检索当前至少借阅了5本图书的读者姓名和所在单位

-- a)求超过5人的人名
select r.name from readers r join borrow_info br on r.reader_id=br.reader_id group by r.name having count(br.book_id)>=5;

-- b)
select name,dept from readers where name in(select r.name from readers r join borrow_info br on r.reader_id=br.reader_id group by r.name having count(br.book_id)>=5);


-- 23、分别找出借书人数超过10个人的单位和人数
select r.dept,count(distinct r.reader_id) from readers r join borrow_info br on r.reader_id=br.reader_id
group by r.dept having count(r.reader_id)>10;

-- 24、检索没有借阅任何图书的读者姓名和所在单位
select distinct r.name,r.dept from readers r join borrow_info br on r.reader_id = br.reader_id;

select r.name,r.dept from readers r where r.name not in(select distinct r.name from readers r join borrow_info br on r.reader_id = br.reader_id);

版权声明：本文为weixin_44720982原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_44720982/article/details/124468696

Tags: mysql