数据库基本操作及leetcode

基本操作

create database db_school;
use db_school;
create table student1(
	id smallint unsigned primary key auto_increment,
	name char(20) not null unique key,
	age tinyint unsigned
);

//关联外键

create table users(
	id smallint unsigned primary key auto_increment,
	username varchar(10) not null,
	pid smallint unsigned,
	foreign key(pid) references student1(id) on delete cascade
	//cascade 保证在父表删除或更新行时，会自动删除或更新子表中匹配的行
);

//显示表结构

show columns from student1;

//显示创建表时的代码

show create table student1;

//显示某个表中的索引

show indexes from student1;

///添加列

alter table student1 add sex enum('1','2','3') default 3;

//删除列

alter table student1 drop sex(,drop age);

//插入

insert into student1 (id,name,age,s) values (null,'zhaojingru',24,1)
insert student1 values(null,'zhaojingru',24,1);//null可写成default
 insert student1 values(null,'lishanshan',26,1),(null,'luhonglin',25,1);

//更新

update student1 set age=age-5;//无where子句，每一行都会更新
update student1 set age=age+5，sex=2;//无where子句，每一行都会更新
update student1 set age=age-5 where id%2=0;

//删除 drop delete truncate区别

delete from student1 where id=1;

//查询
// * 表示所有列
//distinct
//having +分组条件
//order by:对结果进行排序 ASC、DESC降序

select * from student1 order by age,id DESC;

//group by 对查询进行分组
//limit x：返回从上开始的x条记录
//limit x,y:返回从第x+1条开始的Y条记录 【记录从0开始编号】
子查询

select * from student1 where id=(select id from student1 where sex=1);
select * from student1 where age<ALL(select age from student1 where id>4);

ANY 大于最小 <ANY 小于最大
ALL 大于最大 < ALL 小于最小
表级约束：对多个数据列建立的约束
列级约束：对一个数据列建立的约束

explain

explain解释

表连接

笛卡尔积连接

join

外连接

左外连接 left join
左表全部保留，右表关联不上用null表示
右外连接 right join
两表关联，右表全部保留，左表关联不上用null表示
全连接 union/union all
union
两表关联，两表的内容均保留，没有关联的字段用null表示。
会把结果集中重复的记录删掉

select * from t1 left join t2 on t1.id=t2.id 
union select * from t1 right join t2 on t1.id=t2.id

union all
mysql 会把所有的记录返回，且效率高于union

select id as BID from weather as w1 
union all select id as QID from weather as w2; 

{“headers”: [“BID”], “values”: [[1], [2], [3], [4], [1], [2], [3], [4]]}

内连接 inner join
保留交集的记录

select * from t1 inner join t2 on t1.id=t2.id

leetcode

交换工资

使用case when then
update 表名 set 列名=‘case when 条件满足 then case赋值 else case赋值其他值 end

--标准case when then结构
update salary set sex=case when sex='m' then 'f'
when sex='f' then 'm'
else null 
end

update salary set sex=case 
when sex='m' then 'f' else ‘m’ end

---

查找重复的电子邮箱

select Email from Person group by Email having count(email)>1
select Email from (select Person.Email,count(email) as num from Person group by Email)as 起个临时表名 where num>1

---

大的国家

select name,population,area from World where area>3000000 or population>25000000
select name,population,area from World where area>3000000 union select name,population,area from World where population>25000000

union效率比较高
在使用union时两个select的列数/属性名必须一致
在使用union时要区别union和union all的区别
union会自动去重，但是union all 不会去重，会将两个表的重复数值也放进去

---

有趣的电影

条件同时满足使用and
影片描述非boring 使用!=
奇数使用mod(id,2)=1
排序 order by rating DESC（降序排列,默认升序）

select id,movie,description,rating from cinema where description != 'boring' 
and mod(id,2)=1 order by rating DESC

关于not like
%代表任意个字符
_代表一个字符
名字中含有晓 where name like “%晓%”
名字有两个字，且第二个字为红：where name like “_红”

---

组合两个表

select Person.FirstName,Person.LastName,Address.City,Address.State 
from Person left join Address
on Person.PersonId=Address.PersonId

---

超过经理收入的员工

分析：
1、确定使用连接操作。首先返回的是满足条件的员工的name
2、使用内连接。若使用左连接，会返回全部的姓名，因为左连接包括全部左表
右连接包含全部右表 ,若使用左连接，需要使用where字句筛选b.salary is not null;

select a.name as Employee from Employee as a ,Employee as b 
where a.salary>b.salary and a.managerId=b.id

select a.name as Employee from Employee as a join Employee as b
where a.managerId=b.id and a.salary>b.salary

select a.name as employee from employee as a left join employee as b 
on a.managerid =b.id and a.salary>b.salary where b.salary is not null;

---

第二高的薪水

分析：
1、通过排序使得该表按照salary有序
2、第二高应该是降序，默认为asc升序，因此需要使用desc降序排列
3、取出第二高的元素需要使用limit显示一行即可
limit用法
SELECT * FROM table LIMIT limit 1；//查询第一条语句
SELECT * FROM table LIMIT 5,20;//检索记录行6-25
SELECT* FROM table LIMIT 5,-1;//检索记录行6到结尾数据
SELECT* FROM table LIMIT 0,-1;//检索全部记录
SELECT * FROM table LIMIT 5; //检索前 5 个记录行
4、如果表数据不足两行时，该sql无法满足，因此还需要处理结果为null的情况

select 
ifnull((select distinct salary  
from Employee  order by salary DESC limit 1,1),null) as SecondHighestSalary 

---

从不订购的客户

select name as Customers from Customers as a left join orders as b
on a.id =b.customerId where b.customerId is null

select name as Customers from Customers 
where id not in (select customerId from orders)

---

组合两个表

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State

分析：
1、上面四个字段分别来自两张表，person表和address表，因此需要表连接
2、题意分析：person是否有地址都要基于person提供四个字段的信息
3、按照left join/right join/inner join的规则，需要使用left join
4、连接条件分析，两个表重复字段为personid

select p.firstname,p.lastname,a.city,a.state from person as p 
left join address as a on p.personid=a.personid

关键点：
字段查询不使用*的情况下需要使用，
表使用别名需要 person as p
使用join时条件需要使用on

---

上升的温度

• 计算日期的函数datediff(date1,date2)
  得到的结果是日期1与日期2相差的天数。
  如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。
• timestampdiff(时间类型, 日期1, 日期2)
  这个函数和上面diffdate的正、负号规则刚好相反。
  日期1大于日期2，结果为负，日期1小于日期2，结果为正。

select w1.id from weather as w1 join weather as w2 
on datediff(w1.recorddate,w2.recorddate)=1 
where w1.temperature>w2.temperature;

1、join表示笛卡尔积 weather as w1 join weather as w2
2、左表比右表的日期多一天 datediff(w1.recorddate,w2.recorddate)=1
3、左表的温度比右表高 where w1.temperature>w2.temperature;
4、select左表的id select w1.id

超过5名学生的课

select class from courses group by class having count(distinct student)>=5;

select class from 
(select class,count(distinct student) as counts from courses group by class) as abc where counts>=5;

删除重复的电子邮箱

删除的格式：delete from 表 where 列值=”条件“

delete p1 from person as p1,person as p2 
where p1.email=p2.email and p1.id>p2.id;

笛卡尔积 person as p1,person as p2

重新格式化部门表

case结构

case 列名
when 列值 then 想要设置的值
when 列值 then 想要设置的值
when 列值 then 想要设置的值
end as 想要设置的列名

select id,
sum(case when s='true' then 1 else 0 end) as 'true',
sum(case when s='false' then 1 else 0 end) as 'false'
from course group by id;

+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | null        | 7000        | null       | ... | null        |

| 3    | null        | 10000       | null        | ... | null        |
| 1    | 8000        | null        | null        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 1    | null        | null        | 6000        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

SELECT id,
CASE `month` WHEN 'Jan' THEN revenue END Jan_Revenue,
CASE `month` WHEN 'Feb' THEN revenue END Feb_Revenue,
CASE `month` WHEN 'Mar' THEN revenue END Mar_Revenue,
CASE `month` WHEN 'Apr' THEN revenue END Apr_Revenue,
CASE `month` WHEN 'May' THEN revenue END May_Revenue,
CASE `month` WHEN 'Jun' THEN revenue END Jun_Revenue,
CASE `month` WHEN 'Jul' THEN revenue END Jul_Revenue,
CASE `month` WHEN 'Aug' THEN revenue END Aug_Revenue,
CASE `month` WHEN 'Sep' THEN revenue END Sep_Revenue,
CASE `month` WHEN 'Oct' THEN revenue END Oct_Revenue,
CASE `month` WHEN 'Nov' THEN revenue END Nov_Revenue,
CASE `month` WHEN 'Dec' THEN revenue END Dec_Revenue
FROM Department;

对上述结果使用id进行分组，然后sum这一组的值

+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

select id,
sum (case when month = 'Jan' then revenue end) 'Jan_revenue',
sum (case when month = 'Feb' then revenue end) 'Feb_revenue',
sum (case when month = 'Mar' then revenue end) 'Mar_revenue',
sum (case when month = 'Apr' then revenue end) 'Apr_revenue',
sum (case when month = 'May' then revenue end) 'May_revenue',
sum (case when month = 'Jun' then revenue end) 'Jun_revenue',
sum (case when month = 'Jul' then revenue end) 'Jul_revenue',
sum (case when month = 'Aug' then revenue end) 'Aug_revenue',
sum (case when month = 'Sep' then revenue end) 'Sep_revenue',
sum (case when month = 'Oct' then revenue end) 'Oct_revenue',
sum (case when month = 'Nov' then revenue end) 'Nov_revenue',
sum (case when month = 'Dec' then revenue end) 'Dec_revenue'
from Department
group by id