#第十三章、练习题

#基础练习
#练习1
#已经存在数据库test04_emp，两张表emp2和dept2
CREATE DATABASE test04_emp;

USE test04_emp;

CREATE TABLE emp2(
id INT,
emp_name VARCHAR(15)
);

CREATE TABLE dept2(
id INT,
dept_name VARCHAR(15)
);

#题目：
#1、向表emp2的id列中添加PRIMARY KEY约束
ALTER TABLE emp2
ADD PRIMARY KEY(id);

#2、向表dept2的id列中添加PRIMARY KEY约束
ALTER TABLE dept2
ADD PRIMARY KEY(id);

#3、向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之向关联的列是dept2表中的id列
ALTER TABLE emp2
ADD dept_id INT;

DESC emp2;

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY (dept_id) REFERENCES dept2(id);

######################################################################

#练习2
#承接《第11章_数据处理之增删改》的综合案例
#1、创建数据库test01_library

#2、创建表books，表结构如下：
/*
		字段名					字段说明				数据类型
		id 							书编号					INT
		name 						书名						VARCHAR(50)
		authors 				作者						VARCHAR(100)
		price 					价格						FLOAT
		pubdate					出版日期				YEAR
		note 						说明						VARCHAR(100)
		num 						库存						INT
*/
#数据库在第11章时已经创建，第1题和第2题可直接使用
USE test01_library;

DESC books;

#3、使用ALTER语句给books按如下要求增加相应的约束
/*
		字段名			字段说明		数据类型			主键	外键	非空	唯一	自增
		id 					书编号			INT(11)				是		否		是		是		是
		name 				书名				VARCHAR(50)		否		否		是		否		否
		authors 		作者				VARCHAR(100)	否		否		是		否		否
		price 			价格				FLOAT					否		否		是		否		否
		pubdate			出版日期		YEAR					否		否		是		否		否
		note 				说明				VARCHAR(100)	否		否		否		否		否
		num 				库存				INT(11)				否		否		是		否		否
*/
#为id字段添加约束
#方式一（我做的）
ALTER TABLE books
ADD PRIMARY KEY(id);
ALTER TABLE books
MODIFY id INT AUTO_INCREMENT;

#方式二（老师讲的另一种方法）
ALTER TABLE books
MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

#为name字段添加约束
ALTER TABLE books
MODIFY name VARCHAR(50) NOT NULL;

#为authors字段添加约束
ALTER TABLE books
MODIFY authors VARCHAR(100) NOT NULL;

#为price字段添加约束
ALTER TABLE books
MODIFY price FLOAT NOT NULL;

#为pubdate字段添加约束
ALTER TABLE books
MODIFY pubdate YEAR	NOT NULL;

#为num字段添加约束
ALTER TABLE books
MODIFY num INT NOT NULL;

DESC books;

######################################################################

#练习3
#题目
#1、创建数据库test04_company
CREATE DATABASE IF NOT EXISTS test04_company;

USE test04_company;

#2、按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees
#offices表：
/*
		字段名			数据类型			主键	外键	非空	唯一	自增
		officeCode	INT(10)				是		否		是		是		否
		city				VARCHAR(50)		否		否		是		否		否
		address			VARCHAR(50)		否		否		否		否		否
		country 		VARCHAR(50)		否		否		是		否		否
		postalCode 	VARCHAR(15)		否		否		否		是		否
*/
#employees表：
/*
		字段名						数据类型			主键	外键	非空	唯一	自增
		employeeNumber		INT(11)				是		否		是		是		是
		lastName 					VARCHAR(50)		否		否		是		否		否
		firstName 				VARCHAR(50)		否		否		是		否		否
		mobile						VARCHAR(25)		否		否		否		是		否
		officeCode 				INT(10)				否		是		是		否		否
		jobTitle 					VARCHAR(50)		否		否		是		否		否
		birth							DATETIME 			否		否		是		否		否
		note 							VARCHAR(255)	否		否		否		否		否
		sex 							VARCHAR(5)		否		否		否		否		否
*/
CREATE TABLE IF NOT EXISTS offices(
officeCode INT PRIMARY KEY,		#老师写的：officeCode INT(10) PRIMARY KEY,
city VARCHAR(50)	NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
);

DESC offices;

CREATE TABLE IF NOT EXISTS employees(
employeeNumber INT PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT fk_emp_offcode FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);

DESC employees;

#3、将表employees的mobile字段修改到officeCode字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER officeCode;

#4、将表employees的birth字段名改为employee_birth
ALTER TABLE employees
CHANGE birth employee_birth DATETIME;

#5、修改sex字段，数据类型为CHAR(1)，非空约束
ALTER TABLE employees
MODIFY sex CHAR(1) NOT NULL;

#6、删除字段note
ALTER TABLE employees
DROP COLUMN note;

#7、增加字段名favoriate_activity，数据类型为VARCHAR(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

#8、将表employees名称修改为employees_info
RENAME TABLE employees
TO employees_info;

DESC employees_info;

######################################################################

#拓展练习
#练习1
#创建数据库test04_Market，在test04_Market中创建数据表customers。
#customers表结构如下所示，按以下要求进行操作。
/*
		字段名			数据类型			主键	外键	非空	唯一	自增
		c_num 			INT(11)				是		否		是		是		是
		c_name 			VARCHAR(50)		否		否		否		否		否
		c_contact 	VARCHAR(50)		否		否		否		否		否
		c_city 			VARCHAR(50)		否		否		否		否		否
		c_birth 		DATETIME 			否		否		是		否		否
*/
#1、创建数据库test04_Market。
CREATE DATABASE test04_Market;

USE test04_Market;

#2、创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束。
CREATE TABLE customers(
c_num INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATETIME NOT NULL
);

DESC customers;

#3、将c_contact字段插入c_birth字段后面。
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

#4、将c_name字段数据类型改为VARCHAR(70)。
ALTER TABLE customers
MODIFY c_name VARCHAR(70);

#5、将c_contact字段改名为c_phone。
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

#6、增加c_gender字段，数据类型为CHAR(1)。
ALTER TABLE customers
ADD COLUMN c_gender CHAR(1);

#7、将表名修改为customers_info。
RENAME TABLE customers
TO customers_info;

DESC customers_info;

#8、删除字段c_city。
ALTER TABLE customers_info
DROP c_city;


#在test04_Market中创建数据表orders。orders表结构如下所示，按以下要求进行操作。
/*
		字段名		数据类型		主键	外键	非空	唯一	自增
		o_num			INT(11)			是		否		是		是		是
		o_date 		DATE 				否		否		否		否		否
		c_id 			INT(11)			否		是		否		否		否
*/
#1、创建数据表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customers表中的主键c_num。
CREATE TABLE IF NOT EXISTS orders(
o_num INT PRIMARY KEY AUTO_INCREMENT,
o_date	DATE,
c_id INT,
CONSTRAINT fk_orders_c_id FOREIGN KEY(c_id) REFERENCES customers_info(c_num)
);

DESC orders;

#2、删除orders表的外键约束，然后删除表customers。
#查看约束信息，主要是查看外键约束名
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'orders';
#删除外键约束
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_c_id;
#查看索引名
SHOW INDEX FROM orders;
#删除索引
ALTER TABLE orders
DROP INDEX fk_orders_c_id;

######################################################################

#练习2
#创建数据表pet，并对表进行插入、更新与删除操作。pet表结构如下所示。
#pet表结构
/*
		字段名		字段说明		数据类型			主键	外键	非空	唯一	自增
		name 			宠物名称		ARCHAR(20)		否		否		是		否		否
		owner 		宠物主人		ARCHAR(20)		否		否		否		否		否
		species		种类				ARCHAR(20)		否		否		是		否		否
		sex				性别				CHAR(1)				否		否		是		否		否
		birth			出生日期		YEAR 					否		否		是		否		否
		death			死亡日期		TEAR					否		否		否		否		否
*/
#pet表中记录
/*
		name 			owner 		species		sex 	birth		death
		Fluffiy		Harold		cat 			f 		2003 		2010
		Claws			Gwen			cat 			m  		2004 		NULL
		Buffiy		NULL			dog				f 		2009		NULL
		Fang			Benny			dog				m 		2000		NULL		
		Bowser		Diane			dog				m 		2003		2009
		Chirpy		NULL 			bird			f 		2008		NULL
*/
#1、首先创建数据表pet，使用不同的方法将表记录插入到pet表中。
#创建表
CREATE TABLE pet(
`name` VARCHAR(20) NOT NULL,
`owner` VARCHAR(20),
species VARCHAR(20) NOT NULL,
sex CHAR(1) NOT NULL,
birth YEAR NOT NULL,
death YEAR
);

DESC pet;

#向表中插入数据
INSERT INTO pet(name,owner,species,sex,birth,death)
VALUES('Fluffiy','Harold','cat','f','2003','2010');

INSERT INTO pet VALUES
('Claws','Gwen','cat','m','2004',NULL),
('Buffiy',NULL,'dog','f','2009',NULL),
('Fang','Benny','dog','m','2000',NULL),
('Bowser','Diane','dog','m','2003',2009),
('Chirpy',NULL,'bird','f','2008',NULL);

SELECT * FROM pet;

#2、使用UPDATE语句将名称为Fang的狗的主人改为Kevin。
UPDATE pet
SET owner = 'Kevin'
WHERE name = 'Fang';

#3、将没有主人的宠物owner字段值都改为Duck。
UPDATE pet
SET owner = 'Duck'
WHERE owner IS NULL;

#4、删除已死亡的宠物记录。
DELETE FROM pet
WHERE death IS NOT NULL;

#5、删除所有表中的记录。
DELETE FROM pet;

SELECT * FROM pet;

######################################################################

#练习3
#1、创建数据库：test_company
CREATE DATABASE IF NOT EXISTS test_company;

USE test_company;

#2、在此数据库下创建如下3表，数据类型，宽度，是否为空根据实际情况自己定义。
/*
	A、部门表（department）：部门编号（depid）。部门名称（depname）。
	部门简介（depinfo）；其中部门编号为主键。
	B、雇员表（employee）：雇员编号（empid），姓名（name），性别（sex），
	职称（title），出生日期（birthday），所在部门编号（depid）；其中：
	雇员编号为主键；
	部门编号为外键，外键约束等级为（on update cascade和on delete set null）；
	性别默认为男；
	C、工资表（salary）：雇员编号（empid），基本工资（basesalary），职务
	工资（titlesalary），扣除（deduction）。其中雇员编号为主键。
*/
#部门表
CREATE TABLE department(
depid INT,
depname VARCHAR(20) NOT NULL,
depinfo VARCHAR(50),
PRIMARY KEY(depid)
);

DESC department;

#雇员表
CREATE TABLE employee(
empid INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex CHAR(1) NOT NULL DEFAULT '男',
title VARCHAR(15),
birthday DATE NOT NULL,
depid INT,
CONSTRAINT fk_emp_depid FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET NULL
);

DESC employee;

#工资表
CREATE TABLE salary(
empid INT PRIMARY KEY,
basesalary DECIMAL(10,2),
titlesalary DECIMAL(10,2),
deduction DECIMAL(10,2)
);

DESC salary;

#3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为
#（on update cascade和on delete cascade）
ALTER TABLE salary
ADD CONSTRAINT fk_sal_empid FOREIGN KEY(empid) REFERENCES employee(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
*/
#添加部门表数据
INSERT INTO department VALUES
(111,'生产部',NULL),
(222,'销售部',NULL),
(333,'人事部','人力资源管理');

SELECT * FROM department;

#添加雇员表数据
INSERT INTO employee VALUES
(1001,'张三',DEFAULT,'高级工程师','1975-1-1',111),
(1002,'李四','女','助工','1985-1-1',111),
(1003,'王五','男','工程师','1978-11-11',222),
(1004,'张六',DEFAULT,'工程师','1999-1-1',222);

SELECT * FROM employee;

#添加工资表数据
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 emp.empid,emp.name,emp.title,dep.depname,sal.basesalary+sal.titlesalary AS '应发工资',sal.basesalary+sal.titlesalary-IFNULL(sal.deduction,0) AS '实发工资'
FROM employee AS emp JOIN department dep JOIN salary AS sal
ON emp.depid = dep.depid AND emp.empid = sal.empid;

#6、查询销售部门的雇员姓名及其基本工资
SELECT emp.name,sal.basesalary
FROM department dep JOIN employee emp JOIN salary sal
ON dep.depid = emp.depid AND emp.empid = sal.empid
WHERE dep.depname = '销售部';

#7、查询姓“张”且年龄小于40的员工的全部信息和年龄
SELECT *,YEAR(NOW())-YEAR(birthday) AS '年龄'
FROM employee
WHERE name LIKE '张%' AND (YEAR(NOW())-YEAR(birthday)) < 40;

#8、查询所有男员工的基本工资和职务工资
SELECT * FROM employee;

SELECT emp.empid,emp.name,sal.basesalary,sal.titlesalary
FROM employee emp JOIN salary sal
ON emp.empid = sal.empid
WHERE emp.sex = '男';

#9、查询基本工资低于2000的员工姓名和职称、所在部门名称
SELECT emp.name,emp.title,dep.depname
FROM department dep JOIN employee emp JOIN salary sal
ON dep.depid = emp.depid AND emp.empid = sal.empid
WHERE sal.basesalary < 2000;

#10、查询员工总数
SELECT COUNT(*)
FROM employee;

#11、查询部门总数
SELECT COUNT(*)
FROM department;

#12、查询应发工资的平均工资和最高工资、最低工资
SELECT AVG(basesalary+titlesalary) AS '平均工资',MAX(basesalary+titlesalary) AS '最高工资',MIN(basesalary+titlesalary) AS '最低工资'
FROM salary;

#13、按照部门统计应发工资的平均工资
SELECT depid,AVG(basesalary+titlesalary)
FROM employee JOIN salary
ON employee.empid = salary.empid
GROUP BY employee.depid

#14、找出部门基本工资的平均工资低于2000的
SELECT depid,AVG(basesalary) AS avgsal
FROM employee JOIN salary
ON employee.empid = salary.empid
GROUP BY employee.depid
HAVING avgsal < 2000;

#15、按照员工编号、姓名、基本工资、职务工资、扣除，并按照职务升序排列，如果职务工资相同，再按照基本工资升序排列。
SELECT emp.empid,emp.name,emp.title,basesalary,titlesalary,deduction
FROM employee emp JOIN salary sal
ON emp.empid = sal.empid
ORDER BY emp.title ASC,basesalary ASC;

#16、查询员工编号、姓名，出生日期，及年龄段。其中，如果80年之前出生的，定为“老年”；80后定为“中年”，90后定为“青壮年”
SELECT empid,name,birthday,
CASE WHEN YEAR(birthday) < 1980 THEN '老年'
		 WHEN YEAR(birthday) < 1990 THEN '中年'
		 ELSE '青壮年' END AS '年龄段'
FROM employee;

#17、查询所有的员工信息，和他所在的部门名称
SELECT emp.*,dep.depname
FROM employee emp LEFT JOIN department dep
ON emp.depid = dep.depid

#18、查询所有部门信息，和该部门的员工信息
SELECT dep.*,emp.*
FROM department dep LEFT JOIN employee emp
ON dep.depid = emp.depid

#19、查询所有职位中含“工程师”的男员工的人数
SELECT COUNT(*)
FROM employee
WHERE sex = '男' AND title LIKE '%工程师%';

#20、查询每个部门的男生和女生的人数和平均基本工资
SELECT dep.depid,emp.sex,COUNT(*),AVG(sal.basesalary)
FROM department dep JOIN employee emp JOIN salary sal
ON dep.depid = emp.depid AND emp.empid = sal.empid
GROUP BY dep.depid,emp.sex

######################################################################

#练习4：
#1、创建一个数据库：test_school
CREATE DATABASE IF NOT EXISTS test_school;

USE test_school;

#2、创建如下表格：
#表1	Department表的定义
/*
	字段名			字段描述		数据类型			主键	外键	非空	唯一
	DepNo				部门号			INT(10)				是		否		是		是
	DepName 		部门名称		VARCHAR(20)		否		否		是		否
	DepNote			部门备注		VARCHAR(50)		否		否		否		否
*/
#表2 Teacher表的定义
/*
	字段名		字段描述		数据类型			主键	外键	非空	唯一
	Number		教工号			INT						是		否		是		是
	Name 			姓名				VARCHAR(30)		否		否		是		否
	Sex 			性别				VARCHAR(4)		否		否		否		否
	Birth			出生日期		DATE 					否		否		否		否
	DepNo			部门号			INT						否		是		否		否
	Salary 		工资				FLOAT					否		否		否		否
	Address 	家庭住址		VARCHAR(100)	否		否		否		否
*/
#创建表1
CREATE TABLE Department(
DepNo INT(10) PRIMARY KEY,
DepName VARCHAR(20) NOT NULL,
DepNote VARCHAR(50)
);

DESC Department;

#创建表2
CREATE TABLE Teacher(
Number INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Sex VARCHAR(4),
Birth DATE,
DepNo INT,
Salary FLOAT,
Address VARCHAR(100),
CONSTRAINT fk_teach_depno FOREIGN KEY(DepNo) REFERENCES Department(Depno)
);

DESC Teacher;

#3、添加记录
/*
	DepNo		DepName 			DepNote
	601			软件技术系		软件技术等专业
	602			网络技术系		多媒体技术等专业
	603			艺术设计系		广告艺术设计等专业
	604			管理工程系		连锁经营管理等专业
*/
/*
	Number 	Name 		Sex 	Birth				DepNo		Salary 		Address
	2001		Tom 		女		1970-01-10	602			4500			四川省绵阳市
	2002		Lucy		男		1983-12-18	601			2500			北京市昌平区
	2003		Mike		男		1990-06-01	604			1500			重庆市渝中区
	2004		James		女		1980-10-20	602			3500			四川省成都市
	2005		Jack		男		1975-05-30	603			1200			重庆市南岸区
*/
#向部门表添加记录
INSERT INTO Department VALUES
(601,'软件技术系','软件技术等专业'),
(602,'网络技术系','多媒体技术等专业'),
(603,'艺术设计系','广告艺术设计等专业'),
(604,'管理工程系','连锁经营管理等专业');

SELECT * FROM Department;

#向教师表中添加记录
INSERT INTO Teacher VALUES
(2001,'Tom','女','1970-01-10',602,4500,'四川省绵阳市'),
(2002,'Lucy','男','1983-12-18',601,2500,'北京市昌平区'),
(2003,'Mike','男','1990-06-01',604,1500,'重庆市渝中区'),
(2004,'James','女','1980-10-20',602,3500,'四川省成都阳市'),
(2005,'Jack','男','1975-05-30',603,1200,'重庆市南岸区');

SELECT * FROM Teacher;

#4、用SELECT语句查询Teacher表的所有记录
SELECT * FROM Teacher;

#5、找出所有其家庭地址中含有“北京”的教师的教工号及部门名称，要求显示结果中各列标题用中文别名表示。
SELECT teach.Number AS '教工号',dep.DepName AS '部门名称',teach.Address
FROM Teacher teach LEFT JOIN Department dep
ON teach.DepNo = dep.DepNo
WHERE teach.Address LIKE '%北京%';

#6、获得Teacher表中工资最高的教工号和姓名。
#我做的
SELECT Number,Name,MAX(Salary)
FROM Teacher

#老师给的
#方法一
SELECT Number,Name
FROM Teacher
WHERE salary =(SELECT MAX(Salary) FROM Teacher);

#方法二
SELECT Number,Name
FROM Teacher
ORDER BY Salary DESC LIMIT 0,1;

#7、找出所有收入在2500~4000之间的教工号。
SELECT Number
FROM Teacher
WHERE Salary BETWEEN 2500 AND 4000;

#8、查找在网络技术系工作的教师的姓名、性别和工资。
SELECT Name,Sex,Salary
FROM Teacher teach JOIN Department dep
ON teach.DepNo = dep.DepNo
WHERE dep.DepName = '网络技术系'

######################################################################

#练习5
#1、建立数据库test_student
CREATE DATABASE IF NOT EXISTS test_student;

USE test_student;

#2、建立以下三张表，并插入记录
#Table：Classes
/*
	专业					班级		姓名		性别		座位
	计算机网络		1班			张三		男			8
	软件工程			2班			李四		男			12
	计算机维护		1班			王五		男			9
	计算机网络		2班			LILY 		女			15
	软件工程			1班			小强		男			20
	计算机维护		1班			CoCo 		女			18
*/
#Table：Score
/*
	姓名		英语		数据		语文
	张三		65			75			98
	李四		87			45			86
	王五		98			86			65
	LILY 		75			86			87
	小强		85			60			58
	CoCo		96			87			70
*/
#Table：Records
/*
	姓名		记录
	小强		迟到
	小强		事假
	李四		旷课
	李四		旷课
	李四		迟到
	CoCo 		病假
	LILY 		事假
*/
#创建表一：Classes
CREATE TABLE classes(
major VARCHAR(20) NOT NULL,
class VARCHAR(10) NOT NULL,
sname VARCHAR(10) NOT NULL,
sex CHAR(1) DEFAULT '男',
seat INT UNIQUE NOT NULL
);

#创建表二：score
CREATE TABLE score(
sname VARCHAR(10) NOT NULL,
english INT NOT NULL,
math INT NOT NULL,
chinese INT	NOT NULL
);

#创建表三：Records
CREATE TABLE records(
sname VARCHAR(10) NOT NULL,
record VARCHAR(10)
);

#向classes表插入数据
INSERT INTO classes VALUES
('计算机网络','1班','张三',DEFAULT,8),
('软件工程','2班','李四',DEFAULT,12),
('计算机维护','1班','王五','男',9),
('计算机网络','2班','LILY','女',15),
('软件工程','1班','小强',DEFAULT,20),
('计算机维护','1班','CoCo','女',18);

SELECT * FROM classes;

#向score表插入数据
INSERT INTO score VALUES
('张三',65,75,98),
('李四',87,45,86),
('王五',98,86,65),
('LILY',75,86,87),
('小强',85,60,58),
('Coco',96,87,70);

SELECT *FROM score;

#向records表插入数据
INSERT INTO records VALUES
('小强','迟到'),
('小强','事假'),
('李四','旷课'),
('李四','旷课'),
('李四','迟到'),
('Coco','病假'),
('LILY','事假');

SELECT * FROM records;

#3、写出将张三的语文成绩修改为88的SQL语句
UPDATE score
SET chinese = 88
WHERE sname = '张三';

#4、搜索出计算机维护1班各门课程的平均成绩
#我做的，不对
SELECT major,class,AVG(english),AVG(math),AVG(chinese)
FROM score JOIN classes
GROUP BY major,class
HAVING major = '计算机维护' AND class = '1班'

#老师给的参考方法
SELECT AVG(english),AVG(math),AVG(chinese)
FROM score
WHERE sname IN (SELECT sname
								FROM classes
								WHERE major = '计算机维护' AND class = '1班');

#5、搜索科目有不及格的人的名单
SELECT sname
FROM score
WHERE english < 60 OR math < 60 OR chinese <60;

#6、查询记录2次以上的学生的姓名和各科成绩
SELECT score.*
FROM score JOIN (SELECT sname,COUNT(*)
									FROM records
									GROUP BY sname
									HAVING COUNT(*) >= 2) temp
ON score.sname = temp.sname;

######################################################################

#练习6
#1、建立数据库：test_xuankedb
CREATE DATABASE IF NOT EXISTS test_xuankedb;

USE test_xuankedb;

#2、建立如下三张表：
/*
		学生表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、
		年龄（Sage）、所在系（Sdept）五个字段，Sno为关键字。
		
		课程表（Course）由课程号（Cno）、课程名（Cname）、选修课号（Cpno）、
		学分（Ccredit）四个字段，Cno为关键字。
		
		成绩表SG由学号（Sno）、课程号（Cno）、成绩（Grade）三个字段，
		（Sno，Cno）为关键字。
*/

#创建学生表
CREATE TABLE IF NOT EXISTS student(
sno INT PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
ssex CHAR(1) NOT NULL,
sage INT NOT NULL,
sdept VARCHAR(40) NOT NULL
);

DESC student;

#创建课程表
CREATE TABLE IF NOT EXISTS course(
cno INT PRIMARY KEY,
cname VARCHAR(20) NOT NULL,
cpno VARCHAR(40),
ccredit INT
);

DESC course;

#创建成绩表
CREATE TABLE IF NOT EXISTS sg(
sno INT,
cno INT,
grade INT,
PRIMARY KEY(sno,cno),
CONSTRAINT fk_s_stu_sno FOREIGN KEY(sno) REFERENCES student(sno),
CONSTRAINT fk_s_cou_sno FOREIGN KEY(cno) REFERENCES course(cno)
);

DESC sg;

#3、向Student表增加“入学时间（Scome）”列，其数据类型为日期型。
ALTER TABLE student
ADD scome DATE;

#4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
SELECT sno,grade
FROM sg
WHERE cno = 3
ORDER BY grade DESC;

#5、查询学习1号课程的学生最高分数、平均成绩。
SELECT MAX(grade),AVG(grade)
FROM sg
WHERE cno = 1;

#6、查询与“李洋”在同一个系学习的学生。
SELECT *
FROM student
WHERE sdept = (SELECT dept
							 FROM student
							 WHERE sname = '李洋'
							 );

#7、将计算机系全体学生的成绩置零。
UPDATE sg
set grade = 0
WHERE sno IN (SELECT sno
							FROM student
							WHERE sdept = '计算机系');

#8、删除学生表中学号为05019的学生记录。
DELETE FROM student
WHERE sno = 05019

#9、删除计算机系所有学生的成绩记录。
DELETE FROM sg
WHERE sno IN (SELECT sno
							FROM student
							WHERE dept = '计算机系');

######################################################################

#练习7
#1、建立数据库：test_library
CREATE DATABASE IF NOT EXISTS test_library;

USE test_library;

#2、建立如下三个表：
/*
	表一：press出版社	属性：编号pressid（int）、名称pressname（varchar）、
	地址address（varchar）
	
	表二：sort种类 属性：编号sortno（int）、数量scount（int）
	
	表三：book图书 属性：编号bid（int）、名称bname（varchar）、
	种类bsortno（int）、出版社编号pressid（int）
*/

#创建表一
CREATE TABLE press(
pressid INT PRIMARY KEY,
pressname VARCHAR(30),
address VARCHAR(50)
);

#创建表二
CREATE TABLE sort(
sortno INT PRIMARY KEY,
scount INT
);

#创建表三
CREATE TABLE book(
bid INT PRIMARY KEY,
bname VARCHAR(20),
bsortno INT,
pressid INT,
CONSTRAINT fk_p_b_pid FOREIGN KEY(pressid) REFERENCES press(pressid),
CONSTRAINT fk_s_b_sno FOREIGN KEY(bsortno) REFERENCES sort(sortno)
);

#3、给sort表中添加一列属性：描述describes（varchar）
ALTER TABLE sort
ADD describes VARCHAR(30);

DESC sort;

#4、向三个表中各插入几条数据
#表一：press
/*
	pressid 		pressname 				address
	100					外研社						上海
	101					北大出版社				北京
	102					教育出版社				北京
*/
#表二：sort
/*
	sortno 		scount		describes
	11				50				小说
	12				300				科幻
	13				100				神话
*/
#表三：book
/*
	bid 	bname					bsortno		pressid
	1			红与黑				11				100
	2			幻城					12				102
	3			希腊神话			13				102
	4			一千零一夜		13				102
*/

#向表一插入数据
INSERT INTO press VALUES
(100,'外研社','上海'),
(101,'北大出版社','北京'),
(102,'教育出版社','北京');

#向表二插入数据
INSERT INTO sort VALUES
(11,50,'小说'),
(12,300,'科幻'),
(13,100,'神话');

#向表三插入数据
INSERT INTO book VALUES
(1,'红与黑',11,100),
(2,'幻城',12,102),
(3,'希腊神话',13,102),
(4,'一千零一夜',13,102);

#5、查询出版社id为100的书的全部信息
SELECT * FROM press;
SELECT * FROM sort;
SELECT * FROM book;

SELECT *
FROM book
WHERE pressid = 100;

#6、查询出版社为外研社的书的全部信息
SELECT *
FROM book
WHERE pressid = (
									SELECT pressid
									FROM press
									WHERE pressname = '外研社'
									);

#7、查询图书数量（scount）大于100的种类
SELECT *
FROM sort
WHERE scount > 100;

#8、查询图书种类最多的出版社信息
#我做的
SELECT *
FROM press
WHERE pressid = (
									SELECT pressid
									FROM book
									GROUP BY bsortno
									ORDER BY count(*) DESC
									LIMIT 0,1
									);

#老师给的参考答案
#方式一(报错，无法运行）
SELECT * FROM press WHERE pressid=(
		SELECT temp.pressid FROM 
		(SELECT pressid,MAX(t,c) FROM (SELECT pressid,COUNT(*) AS c FROM book GROUP BY pressid ORDER BY c DESC) AS t) AS temp);
		
#方式二（可以运行）
SELECT * FROM press WHERE pressid = (
SELECT pressid
FROM (SELECT pressid,bsortno FROM book GROUP BY pressid,bsortno) temp
GROUP BY pressid
ORDER BY COUNT(*) DESC
LIMIT 0,1);

######################################################################

#练习8
#1、建立数据库：test_tour
CREATE DATABASE IF NOT EXISTS test_tour;

USE test_tour;

#2、建立如下两个表
#agency旅行社表：
/*
	列名（英文名）	列名（中文名）	数据类型		允许空值		说明
	Id 							旅行社编号			INT					NO 					主键
	Name 						旅行社名				VARCHAR			NO				
	Address 				旅行社地址			VARCHAR 		NO
	Areaid 					所属区域id			INT					YES
*/
#travel旅行线路表
/*
	列名（英文名）	列名（中文名）	数据类型		允许空值		说明
	Tid 						旅行线路编号		INT					NO					主键
	Time 						所需时间				VARCHAR			NO
	Position 				目的地					VARCHAR			NO
	Money						花费						FLOAT				YES
	Aid 						所属旅行社id		INT					NO					外键
	Count 					报名人数				INT					YES
*/

#创建旅行社表
CREATE TABLE agency(
id INT PRIMARY KEY,
`name` VARCHAR(20) NOT NULL,
address VARCHAR(100) NOT NULL,
areaid INT
);

DESC agency;

#创建旅行线路表
CREATE TABLE travel(
tid INT PRIMARY KEY,
time VARCHAR(50) NOT NULL,
`position` VARCHAR(100) NOT NULL,
money FLOAT,
aid INT NOT NULL,
count INT,
CONSTRAINT fk_tr_aid FOREIGN KEY travel(aid) REFERENCES agency(id)
);

DESC travel;

#3、添加记录
#agency表数据
/*
	id 			name					address
	101			青年旅行社		北京海淀
	102			天天旅行社		天津海院
*/
#travel表数据
/*
	tid 		time		position 		money		aid		rcount
	1				5天			八达岭			3000		101		10
	2				7天			水长城			5000		101		14
	3				8天			水长城			6000		102		11
*/

#向表agency添加记录
INSERT INTO agency(id,`name`,address) VALUES
(101,'青年旅行社','北京海淀'),
(102,'天天旅行社','天津海院');

SELECT * FROM agency;

#向表travel添加记录
INSERT INTO travel VALUES
(1,'5天','八达岭',3000,101,10),
(2,'7天','水长城',5000,101,14),
(3,'8天','水长城',6000,102,11);

SELECT * FROM travel;

#4、查出旅行线路最多的旅社
#我做的，分了四步，后一步的代码都包含了前一步的代码
#第一步：对旅行线路表中的旅行社进行分组
SELECT aid,COUNT(*) AS c
FROM travel
GROUP BY aid

#第二步：在第一步的分组结果中，取旅行社统计数量最大值的数量和id
SELECT MAX(new.c),new.aid
FROM (
			SELECT aid,COUNT(*) AS c
			FROM travel
			GROUP BY aid
			) AS new
			
#第三步：从第二步的表中取出旅行社的id
SELECT maxsheet.aid
FROM (SELECT MAX(new.c),new.aid
			FROM (
						SELECT aid,COUNT(*) AS c
						FROM travel
						GROUP BY aid
						) AS new
			) as maxsheet;

#第四步：根据第三步取出的旅行社id，在旅行社表中查出旅行社信息
SELECT *
FROM agency
WHERE id = (SELECT maxsheet.aid
						FROM (SELECT MAX(new.c),new.aid
									FROM (
												SELECT aid,COUNT(*) AS c
												FROM travel
												GROUP BY aid
												) AS new
									) as maxsheet
						);

#老师给的参考答案
SELECT *
FROM agency INNER JOIN (SELECT t.aid,MAX(t.c) FROM (SELECT aid,COUNT(*) AS c FROM travel GROUP BY aid) AS t) temp
ON agency.id = temp.aid;

#5、查出最热门的旅行线路（也就是查询出报名人数最多的线路）
#我做的
SELECT *
FROM travel
ORDER BY count DESC
LIMIT 0,1;

#老师提供的答案
SELECT *
FROM travel
WHERE count = (
								SELECT MAX(count)
								FROM travel
							);

#6、查询花费少于5000的旅行线路
SELECT *
FROM travel
WHERE money < 5000;

#7、找到一次旅行花费最昂贵的旅行社名
#先找到最贵的费用是多少
SELECT MAX(money)
FROM travel;

#再查找最贵费用的旅行社aid							
SELECT aid
FROM travel
WHERE money = (
								SELECT MAX(money)
								FROM travel
								);

#然后根据旅行社aid查找旅行社名称
SELECT name
FROM agency
WHERE id = (
						SELECT aid
						FROM travel
						WHERE money = (
														SELECT MAX(money)
														FROM travel
														)
						);

#老师给的答案
SELECT name
FROM agency
WHERE id = (
							SELECT aid
							FROM travel
							WHERE money = (
															SELECT MAX(money)
															FROM travel
														)
						);

#8、查出青年旅社所有的旅行线路都玩一遍需要多少时间
#先查出青年旅社的id
SELECT id
FROM agency
WHERE name = '青年旅行社';

#根据旅行社id在线路表进行查找求和
SELECT SUM(time)
FROM travel
WHERE aid = (
							SELECT id
							FROM agency
							WHERE name = '青年旅行社'
							);

#老师给的答案
SELECT SUM(time)
FROM travel
WHERE aid = (
							SELECT id
							FROM agency
							WHERE name = '青年旅行社'
						);