- limit X OFFSET Y 从Y开始取X个值；limit X,Y 从X开始取Y个值
- 先JOIN后Where:最常用inner join,left join
- t1 inner join t2 on t1.a=t2.a 求交集t1.a=t2.a记录数比t1,t2两张表都少
- t1 full join t2 on t1.a=t2.a 求笛卡尔结果记录数比t1,t2两张表都要多，关联不上的字段两张表里的值都可以为null
- t1 left join t2 on t1.a=t2.a 求左外链接结果记录数=t1表的记录数，t2表关联不上的记录字段为null
- t1 right join t2 on t1.a=t2.a 求右外链接，结果记录数以t2表为准，t1表关联不上的字段为null.
- 分组及组内计数:group by col having aggCol>num
select count(salary) cnt from salaries group by emp_no having cnt>15;
- 子查询用X() over(Partition by col1 order by col2) as rk排序，外查询通过where rk=1取记录:其中X常用
- Rank() 结果1，1，1，4....
- dense_rank() 结果1，1，1，2...
- row_number() 结果1，2，3，4...
- avg（colx）
- sum(col)
-- 按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和
select emp_no,salary,sum(salary) over(order by emp_no) as running_total from salaries where to_date="9999-01-01"
- 子查询用max(col1) 外查询通过where条件比较
- 子查询是多列活着多行记录时外查询通过where (col1,col2) not in( select col1,col2)过滤。
SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)
-- 先执行子查询得到的记录与外查询匹配如果关联条件满足则返回true,记录存在即外层where条件满足。如果反返回false即不存在。
- order by后面可以是同级select查询的任何字段包括函数；group by后面不可以有同级select的聚合函数。having中可以有聚合函数
- 拼接两列转一行:第一个参数是拼接分割符
select concat_ws(" ",last_name,first_name) Name
-- group_concat（X，Y） y是链接的符号，x是链接的字段；
-- 属于同一个部门的emp_no按照逗号进行连接
select dept_no,group_concat(emp_no) from dept_emp group by dept_no;
- 创建表
-- 对于主键列的写法关键字：primary key,外键约束 constraint foreign key(外键字段) references 外表（主键）
actor_id smallint(5) not null primary key comment "主键id",
alter table audit add CONSTRAINT FOREIGN KEY(emp_no)
references employees_test(id);
-- 通过子查询创建表,视图关键字table,view as select
create table tableName as select col1,col2 as colx from table1
-- 增加唯一索引UNIQUE和普通索引 index
Alter table actor ADD UNIQUE uniq_idx_firstname(first_name);
Alter table actor add index idx_lastname(last_name);
-- 使用强制索引查询关键字force index(indexName)
select *
from salaries
force index(idx_emp_no)
where emp_no=10005;
-- default 关键字default "2020-10-01 00:00:00"
-- 创建触发器关键字：trigger for each row begin ..end
create trigger audit_log
After insert on employees_test
for each row
Begin
insert into audit values(new.id,new.name);
end
- Ignore 关键字实现忽略插入
- insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
- replace into表示插入替换数据，需求表中有PrimaryKey 或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
- insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
- 替换某行某列的值 replace(col1,sourceValue,distValue)
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5 and emp_no=10001;
- round(sum(type="no_completed")/count(1),3) 取小数点后三位四舍五入
- 请你统计一下牛客每个日期新用户的次日留存率。
select c.date,round(count(d.user_id)/count(1),3) as p
from
(select min(date) date,user_id
from login
group by user_id)
as c left join
(select l3.user_id
from login l3,login l4
where l3.user_id=l4.user_id
and DATE_ADD(l3.date,INTERVAL 1 DAY)
=l4.date)as d
on c.user_id=d.user_id
group by c.date
union
select date,0.000 as p
from login
where date not in(select min(date) from login group by user_id)
order by date