select count(salary) cnt from salaries group by emp_no having cnt>15;
-- 按照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"
SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)
-- 先执行子查询得到的记录与外查询匹配如果关联条件满足则返回true,记录存在即外层where条件满足。如果反返回false即不存在。
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
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5 and emp_no=10001;
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
原文:https://www.cnblogs.com/bchjazh/p/14661892.html