基本操作
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
表连接
笛卡尔积连接
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