XQL刷题

---

title: XQL刷题
tags: XQL 刷题
renderNumberedHeading: true
grammar_cjkRuby: true
slug：XQL 刷题

1. limit X OFFSET Y 从Y开始取X个值；limit X,Y 从X开始取Y个值
2. 先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.
3. 分组及组内计数:group by col having aggCol>num

select count(salary) cnt from salaries group by emp_no having cnt>15;

4. 子查询用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"

5. 子查询用max(col1) 外查询通过where条件比较
6. 子查询是多列活着多行记录时外查询通过where (col1,col2) not in( select col1,col2)过滤。

   SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)
   -- 先执行子查询得到的记录与外查询匹配如果关联条件满足则返回true,记录存在即外层where条件满足。如果反返回false即不存在。
   

7. order by后面可以是同级select查询的任何字段包括函数；group by后面不可以有同级select的聚合函数。having中可以有聚合函数
8. 拼接两列转一行:第一个参数是拼接分割符

   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;
   

9. 创建表

-- 对于主键列的写法关键字：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

10. Ignore 关键字实现忽略插入

• insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
• replace into表示插入替换数据，需求表中有PrimaryKey 或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
• insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

11. 替换某行某列的值 replace(col1,sourceValue,distValue)

update titles_test set emp_no=replace(emp_no,10001,10005) where id=5 and emp_no=10001;

12. round(sum(type="no_completed")/count(1),3) 取小数点后三位四舍五入
13. 请你统计一下牛客每个日期新用户的次日留存率。

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