sql join简单总结和leetcode题目简单测试
参考连接：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html
两天多写的这么多sql 语句
#create database lib;
use lib;
/*
create table if not exists Person
(
PersonId int auto_increment,
FirstName varchar(100),
LastName varchar(100),
primary key(PersonId)
) engine = innodb default charset = utf8;
*/
#show tables;
#insert into Person values(2,"bbb","BBB");
#insert into Person values(3,"ccc","CCC");
#select * from Person;
/*
create table if not exists Address
(
AddressId int auto_increment,
PersonId int,
City varchar(100),
State varchar(100),
primary key (AddressId)
) engine = innodb default charset = utf8;
*/
#show tables;
/*
insert into Address values (1,1,"HangZhou","nice");
insert into Address values (2,2,"NanJing","good");
insert into Address values (3,3,"GuangZhou","wonderful");
*/
#select * from Address;
#select * from Person;
#select Person.FirstName, Person.LastName, Address.City, Address.State from Person left join Address on Person.PersonId = Address.PersonId;
/*
create table if not exists Employee
(
id int auto_increment,
Salary int,
primary key (id)
) engine = innodb default charset = utf8;
*/
/*
insert into Employee values (1,100);
insert into Employee values (2,200);
insert into Employee values (3,400);
insert into Employee values (4,500);
*/
#select * from Employee;
#select max(Salary) from Employee;
#select max(Salary) from (select Salary from Employee where (Salary < max(Salary)));
#select max(Salary) as secondBiggest from Employee where (Salary < (select max(Salary) from Employee));
#select max(Salary) as third from Employee where (Salary < (select max(Salary) as second from Employee where (Salary < (select max(Salary) from Employee))));
/*
create table if not exists A
(
id int auto_increment,
name varchar(100),
primary key(id)
)engine = innodb default charset = utf8;
*/
/*
create table if not exists B
(
id int auto_increment,
address varchar(100),
primary key(id)
)engine = innodb default charset = utf8;
*/
/*
insert into A values (1,"google");
insert into A values (2,"taobao");
insert into A values (3,"weibo");
insert into A values (4,"facebook");
*/
/*
insert into B values (1,"USA");
insert into B values (5,"China");
insert into B values (3,"China");
insert into B values (6,"USA");
*/
#select A.*,B.address from A join B on A.id = B.id;
#select * from A right join B on A.id = B.id;
#select A.*,B.address from A left join B on A.id= B.id;
#select * from Employee;
#alter table Employee add column name varchar(100);
#alter table Employee add column ManagerId int;
#alter table Employee add column test int;
#alter table Employee drop column test;
#alter table Employee rename as Employeee;
#select * from Employeee;
#alter table Employeee rename as Employee;
#show columns from Employee;
#alter table Employee change id Id int auto_increment;
#show columns from Employee;
#alter table Employee modify name varchar(80);
#alter table Employee modify name varchar(100);
#alter table Employee change name Name varchar(100);
#select * from Employee;
#update Employee set Name = 'A', ManagerId = 3 where Id = 1;
#update Employee set Name = 'B',ManagerId = 4 where Id = 2;
/*
update Employee set Name = 'C' where Id = 3;
update Employee set Name = 'D' where Id = 4;
update Employee set Salary = 450 where Id = 1;
*/
/*
select e.Id,e.Name from Employee e left join Employee m on e.ManagerId = m.Id
where e.Salary > m.Salary;
*/
#select e.* from Employee e join Employee m on e.ManagerId = m.Id;
#alter table Person change PersonId Id int auto_increment;
#alter table Person add column Email varchar(100);
#show columns from Person;
/*
alter table Person drop column FirstName;
alter table Person drop column LastName;
show columns from Person;
*/
/*
insert into Person values (1,"aaa");
insert into Person values (2,"bbb");
insert into Person values (3,"bbb");
*/
#delete from Person;
#truncate table Person;
#select m.Email from Person m left join Person n on m.Email = n.Email;
#select * from Person group by Email;
#update Person set Id = 5 where Id = 3;
/*
insert into Person values (2,"ccc");
update Person set Id = 3 where Id = 4;
insert into Person values (4,"ccc");
*/
#select * from Person;
#select Email from Person group by Email having count(Email) > 1;
/*
create table if not exists Customers
(
Id int auto_increment,
Name varchar(100),
primary key (Id)
)engine = innodb default charset = utf8;
*/
/*
create table if not exists Orders
(
Id int auto_increment,
CustomerId int,
primary key (Id)
)engine = innodb default charset = utf8;
*/
/*
insert into Customers values (1,"A");
insert into Customers values (2,"B");
insert into Customers values (3,"C");
insert into Customers values (4,"D");
*/
/*
insert into Orders values (1,3);
insert into Orders values (2,1);
*/
#select * from Orders;
#select * from Customers;
#select Id from Customers where Id not in (select CustomerId from Orders);
#select Customers.Id from Customers left join Orders on Customers.Id = Orders.CustomerId where Orders.CustomerId is null;
#select c.*,o.CustomerId from Customers c left join Orders o on c.Id = o.CustomerId;
/*
create table if not exists weather
(
Id int auto_increment,
RecordDate date default null,
Temperture int default null,
primary key (Id)
)engine = innodb default charset = utf8;
*/
/*
INSERT INTO `weather` VALUES ('1','2015-01-01', '10');
INSERT INTO `weather` VALUES ('2','2015-01-02', '25');
INSERT INTO `weather` VALUES ('3','2015-01-03', '20');
INSERT INTO `weather` VALUES ('4','2015-01-04', '30');
*/
#show columns from weather;
#select datediff('2020-06-14','2020-06-15') as test1;
#select w1.Id,w1.RecordDate from weather w1 inner join weather w2 on w1.Temperture > w2.Temperture
# and datediff(w1.RecordDate, w2.RecordDate) = 1;
#select w1.* from weather w1 join weather w2 on w1.Temperture > w2.Temperture and
# datediff(w1.RecordDate, w2.RecordDate) = 1;
/*
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
`student` varchar(255) DEFAULT NULL,
`class`varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('A', 'Math');
INSERT INTO `courses` VALUES ('B', 'English');
INSERT INTO `courses` VALUES ('C', 'Math');
INSERT INTO `courses` VALUES ('D', 'Biology');
INSERT INTO `courses` VALUES ('E', 'Math');
INSERT INTO `courses` VALUES ('F', 'Computer');
INSERT INTO `courses` VALUES ('G', 'Math');
INSERT INTO `courses` VALUES ('H', 'Math');
*/
#select * from courses;
#select class from courses group by class having count(student) >= 5;
/*
DROP TABLE IF EXISTS `world`;
CREATE TABLE `world` (
`name`varchar(255) DEFAULT NULL,
`continent` varchar(255) DEFAULT NULL,
`area`int(11) DEFAULT NULL,
`population` int(11) DEFAULT NULL,
`gdp`varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of world
-- ----------------------------
INSERT INTO `world` VALUES ('Afghanistan','Asia', '652230', '25500100', '20343000000');
INSERT INTO `world` VALUES ('Albania','Europe', '28748', '2831741', '12960000000');
INSERT INTO `world` VALUES ('Algeria','Africa', '2381741', '37100000', '188681000000');
INSERT INTO `world` VALUES ('Andorra','Europe', '468', '78115', '3712000000');
INSERT INTO `world` VALUES ('Angola', 'Africa','1246700', '20609294', '100990000000');
*/
#select * from world where area > 3000000 &nbs***bsp;population > 25000000;
#show columns from courses;
#select * from courses;
#select ifnull((select * from Employee order by Salary limit 3,1),null) as test;
#select ifnull((select * from Employee order by Salary limit 2,1),null) as getNthHighestSalary(2)
#select * from Employee order by Salary limit 0,3;
#select * from Employee order by Salary;
#select ifnull((select Salary from Employee order by Salary limit 4,1),null) as test;
#show columns from Employee;
/*
create function f2() returns int
begin
return 555;
end;
select f2();
*/
#select version();
/*
create function f3() returns int
begin
declare c int;
select Salary from Employee order by Salary limit 0,1 into c;
return c;
end;
*/
/*
create function getNthSalaty(N int) returns int
begin
declare M int;
set M = N-1;
select ifnull((select Salary from Employee order by Salary desc limit M,1),null) into M;
return M;
end;
select getNthSalaty(3);
*/
/*
create table if not exists scores
(
Id int,
Socre double,
primary key (Id)
)engine = innodb default charset = utf8;
*/
/*
truncate table scores;
insert into scores values (1,3.50);
insert into scores values (2,3.65);
insert into scores values (3,4.00);
insert into scores values (4,3.85);
insert into scores values (5,4.00);
insert into scores values (6,3.65);
*/
#select * from Scores;
#show columns from scores;
#alter table Score rename as Scores;
#alter table scores modify Score double;
/*
SELECT S1.Score,
(
SELECT COUNT(DISTINCT S2.Score)
FROM Scores S2
WHERE S2.Score>=S1.Score
)AS Rank
FROM Scores S1;
*/
/*
select S1.Score,
(
select count(distinct S2.Score)
from Scores S2
where S2.Score >= S1.Score
) as rank from Scores S1;
*/
/*
select S1.Score,
(
select count(distinct S2.Score)
from Scores S2
where S2.Score >= S1.Score
) as Rank from Scores S1 order by S1.Score desc;
*/
/*
Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1','1');
insert into Logs (Id, Num) values ('2','1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4','2');
insert into Logs (Id, Num) values ('5','1');
insert into Logs (Id, Num) values ('6','2');
insert into Logs (Id, Num) values ('7','2');
*/
#select Num from Logs group by Num having count(Num) >= 3;
/*
select distinct l1.Num as ConsecutiveNums from Logs l1
left join Logs l2 on l1.Id = l2.Id - 1
left join Logs l3 on l1.Id = l3.Id - 2
where l1.Num = l2.Num and l2.Num = l3.Num;
*/
/*
select distinct l1.Num as ConsecutiveNumRes from Logs l1
left join Logs l2 on l1.Id = l2.Id -1 left join Logs l3 on l2.Id = l3.Id -1
where l1.Num = l2.Num and l2.Num = l3.Num;
*/
#show columns from Employee;
#alter table Employee drop column ManagerId;
#alter table Employee add column DepartmentId int;
#select * from Employee;
#update Employee set Name = 'A', Salary = 7000, DepartmentId = 1 where Id = 1;
#update Employee set Salary = 8000, DepartmentId =2 where Id = 2;
#update Employee set Salary = 6000, DepartmentId = 2 where Id = 3;
#update Employee set Salary = 9000, DepartmentId = 1 where Id = 4;
/*
create table if not exists Department
(
Id int default null,
Name varchar(100),
primary key (Id)
)engine = innodb default charset = utf8;
insert into Department values (1,'IT');
insert into Department values (2,'Sales');
select * from Department;
*/
/*
select d.Name as Department, e.Name as Employee, e.Salary from Employee e left join Department d
on e.DepartmentId = d.Id order by Department desc;
*/
/*
select d.Name as Department, e.Name as employee, e.Salary, e.Id, e.DepartmentId from
Employee e left join Department d on e.DepartmentId = d.Id where e.Salary
in (select max(Salary) from employee group by DepartmentId);
*/
#select * from Employee;
#select max(Salary) from Employee group by DepartmentId;
#select Salary, DepartmentId from Employee order by DepartmentId;
#select sum(Salary), DepartmentId from Employee group by DepartmentId order by sum(Salary) desc;
/*
Create table If Not Exists seat(id int, student varchar(255));
Truncate table seat;
insert into seat (id, student) values ('1','Abbot');
insert into seat (id, student) values ('2','Doris');
insert into seat (id, student) values ('3','Emerson');
insert into seat (id, student) values ('4','Green');
insert into seat (id, student) values ('5','Jeames');
*/
/*
select newSeat.id as id, newSeat.student as newStudent from
(
select id-1 ,student from seat where mod(id,2) = 0
union
select id+1, student from seat where mod(id,2) = 1 and id != (select count(*) from seat)
union
select id,student from seat where mod(id,2) =1 and id = (select count(*) from seat)
) newSeat;
*/
select id-1 as id,student from seat where mod(id,2) = 0
union
select id+1 as id, student from seat where mod(id,2) = 1 and id != (select count(*) from seat)
union
select id as id,student from seat where mod(id,2) =1 and id = (select count(*) from seat)
order by id asc; 
