/*案例1*/
create table students(
id int auto_increment not null primary key comment'ID',
name varchar(30) not null comment'名字',
email varchar(50) not null comment'邮箱',
address varchar(100) null default'北京' comment'地址'
);
--插入数据
insert into students(name,email,address) values('张飞','123@qq.com',default),('关羽','234@qq.com','地狱');
insert into students(name,email,address,age,birth) values('张三','123@qq.com',default,56,'1986-12-5'),('李四','234@qq.com','地狱',26,'1888-9-2');
insert into students(name,email,address,age,birth) values('小明','xiaoming@qq.com','大同',45,'1986-12-5'),('大龙','自己看@qq.com','南京',26,'1988-9-2');
--给表添加列
alter table students add column phone varchar(11) not null comment '电话号码';
--添加字段内的值
update students set phone=12338383388 where name='张飞';
update students set age=66 where name = '张飞';
update students set birth='1997-2-9' where name = '张飞';
--添加多个字段
ALTER TABLE students
ADD COLUMN age int(3) comment '年龄',
ADD COLUMN birth VARCHAR(255) comment '生日';
--添加多个字段值
update students set age=85,birth='1993-9-9' where name='关羽';
--修改字段中的类型
alter table students modify phone varchar(25) null ;
--删除表里的字段
alter table students drop column phone;
--检索字段名并升序排列
select name,age from students order by age;
--检索案例表里条件age字段为21到60的所有内容按age升序排列:
select * from students where age<=60 and age>=21 order by age;
--检索案例表里条件address字段为北京或地狱并age字段大于等于23的所有信息并按升序排列
select * from students where(address='北京' or address='地狱') and age >= 25 order by age;
--检索案例表里条件age字段不是21和50的name,age,address信息并绛序排列:
select name,age,address from students where age not in (21,50) order by age desc;
--检索案例表里搜索条件name字段为张…的所有内容:
select * from students where name like '%张%';
--检索案例表里总人数的数量,最小年龄,最大年龄,年龄总和,平均值并起别名:
select count(*) as 总人数,min(age) as 最小年龄,max(age) as 最大年龄,sum(age) as 总和,avg(age) as 平均值 from students;
/*案例2*/
--创建学生表student，插入6条记录
CREATE TABLE student (
stu_id INT(10) PRIMARY KEY,
stu_name VARCHAR(20)NOT NULL,
sex VARCHAR(2),
birth YEAR,
department VARCHAR(20),
addr VARCHAR(50)
);
--插入数据
INSERT INTO student VALUES
( 901,'张飞', '男',1985,'计算机系', '河北省涿州市'),
( 902,'关羽', '男',1986,'中文系', '山西省运城市'),
( 903,'貂蝉', '女',1990,'中文系', '山西省忻州县'),
( 904,'刘备', '男',1990,'英语系', '河北省涿州市'),
( 905,'小乔', '女',1991,'英语系', '安徽省潜山市'),
( 906,'赵云', '男',1988,'计算机系', '河北省正定市');
--创建分数表score，插入10条记录
CREATE TABLE score (
score_id INT(10) PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
);
INSERT INTO score(stu_id,c_name,grade) VALUES(901, '计算机',98),
(901, '英语', 80),(902, '计算机',65),(902, '中文',88),
(903, '中文',95),(904, '计算机',70),(904, '英语',92),
(905, '英语',94),(906, '计算机',90),(906, '英语',85);
--从student表中查询计算机系和英语系学生的信息
select * from student where department in ('计算机系','英语系');
select * from student where department='计算机系' or department='英语系';
--从student表中查询年龄25~28岁的学生信息。
select stu_id,stu_name,sex, 2016-birth AS age,department,addr from student WHERE 2016-birth BETWEEN 25 AND 28;
SELECT stu_id,stu_name,sex,2016-birth AS age,department,addr FROM student WHERE 2016-birth>=25 AND 2016-birth<=28;
--在student表中统计每个院系各有几个学生
select department as 院系 , count(stu_id) as 人数 from student group by department;
--查询每个院系学生中的最高分
select max(grade),department from score left join student on score.stu_id=student.stu_id group by department;
--查询学生貂蝉的考试科目（c_name）和考试成绩（grade）
select stu_name,c_name,grade from score left join student on score.stu_id=student.stu_id where stu_name='貂蝉';
--查询计算机成绩低于95的学生信息。
select stu_name,sex,birth,addr,department,c_name,grade from student left join score on score.stu_id=student.stu_id where c_name='计算机' and grade<=95;
--查询同时参加计算机和英语考试的学生的信息。
select * from student where stu_id=any(
select stu_id from score where stu_id in
(select stu_id from score where c_name='计算机') and c_name='英语'
);
--从student表和score表中分别查询出学生的学号，然后合并查询结果。
select stu_id from student union select stu_id from score;
--查询姓张或者姓赵的同学的个人信息、院系和考试科目及成绩。
select student.stu_id, stu_name,sex,birth, department,addr, c_name,grade from student,score
where (stu_name like "张%" or stu_name like "赵%") and (student.stu_id=score.stu_id);
--查询山西省的学生的姓名、出生年份、院系、考试科目及成绩
SELECT stu_name,birth,department, addr,c_name,grade from student,score where addr like "山西%" and student.stu_id=score.stu_id;
/*案例3 各部门工资最高的员工*/
--创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
CREATE TABLE Employee(
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Salary INT,
DepartmentId INT
);
--导入数据
INSERT INTO Employee (Id,Name,Salary,DepartmentId)
VALUES (1,'Joe',70000,1),(2,'Henry',80000,2),(3,'Sam',60000,2),(4,'Max',90000,2);
--创建Department 表，包含公司所有部门的信息
CREATE TABLE Department(
Id INT NOT NULL,
Name VARCHAR(255)
);
--导入数据
INSERT INTO Department (Id,Name)
VALUES (1,'IT'),(2,'Sales');
--编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
SELECT Department.Name As Department, Employee.Name As Employee, Employee.Salary As Salary
FROM Department JOIN Employee
ON Department.Id = Employee.DepartmentId
WHERE Salary IN (SELECT MAX(Salary) FROM Employee GROUP BY DepartmentId);
SELECT MAX(Salary),DepartmentId FROM Employee GROUP BY DepartmentId;
/*案例4 小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？*/
/*注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
请创建如下所示seat表：
示例：
±--------±--------+
| id | student |
±--------±--------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
±--------±--------+
假如数据输入的是上表，则输出结果如下：
±--------±--------+
| id | student |
±--------±--------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
±--------±--------+
*/
--创建seat表
CREATE TABLE seat(
id INT NOT NULL PRIMARY KEY,
student VARCHAR(255)
);
--导入数据
INSERT INTO seat
VALUES
(1,'Abbot'),
(2,'Doris'),
(3,'Emerson'),
(4,'Green'),
(5,'Jeames');
--处理语句
select (case when mod(id,2)=1 and id!=(select max(id) from seat ) then id+1
when mod(id,2)=0 then id-1 else id end ) as id,student
from seat order by id;
/*案例5 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。
请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。*/
/*
创建以下score表：
±—±------+
| Id | Score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+
*/
--创建表
CREATE TABLE score(
Id INT,
Score DECIMAL(3,2)
);
--导入数据
INSERT INTO score
VALUES (1,3.50),(2,3.65),(3,4.00),(4,3.85),(5,4.00),(6,3.65);
--处理语句
select Score,(select count(distinct Score) from score where Score>=s.Score) as Rank from score as s order by Score desc;
--实现排名功能，但是排名是非连续的，如下：
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
±------±-----
--处理语句
select Score,((select count(Score) from score where Score > s.Score)+1) as Rank from score as s order by Score desc;
/*案例6 各部门前3高工资的员工*/
/*
插入以下数据（其实是多插入5,6两行）：
±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
±—±------±-------±-------------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+
此外，请考虑实现各部门前N高工资的员工功能。
*/
--创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
CREATE TABLE Employee(
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Salary INT,
DepartmentId INT
);
--创建Department 表，包含公司所有部门的信息
CREATE TABLE Department(
Id INT NOT NULL,
Name VARCHAR(255)
);
--清空表数据
truncate table Employee;
--导入数据
INSERT INTO Employee (Id,Name,Salary,DepartmentId)
VALUES (1,'Joe',70000,1),(2,'Henry',80000,2),(3,'Sam',60000,2),(4,'Max',90000,2),(5,'Janet',69000,1),(6,'Randy',85000,1);
--导入数据
INSERT INTO Department (Id,Name)
VALUES (1,'IT'),(2,'Sales');
--处理语句
select d.name as Department,e.Name as Employee,e.Salary as Salary from Employee e join Department d on e.DepartmentId=d.id
where Salary in (select Salary from Employee
where ( select count(distinct Salary) from Employee where Salary>=e.Salary and DepartmentId=d.id)<=3
) order by DepartmentId,Salary desc;
