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;