常用SQL语句使用记录

常用SQL语句整理，示例如下：

1.给表增加字段（第二个add容易忘记）

alter table xx_template_field add `relative_field_key` varchar(255) NULL DEFAULT NULL COMMENT '关联字段的key',add `relative_field_val` varchar(255) NULL DEFAULT NULL COMMENT '关联字段的值';

2.更新字段数据

update xx_template set create_time = "2099-01-01 00:00:00" where busi_class="default";

3.删除记录

delete from xx_area where area_code="2131100037";

4.concat/upper/lower/left/substr函数的使用

select user_id,concat(upper(left(name,1)),lower(substr(name,2))) as name from Users order by user_id;

5.group by和sum的组合使用

select event_day as day,emp_id,sum(out_time - in_time) as total_time from Employees group by emp_id,event_day;

6.与用and表示

select id,movie,description,rating from cinema where id % 2 = 1 and description != 'boring' order by rating desc;

7.用group by分组后用having计算每个组的项目数目进行判断

select Email from Person group by email having count(*) > 1

8.在MySQL中不能在同一条SQL中查询出一张表的某些值，再直接用这些值更新这张表。

解决方案：将查询出的结果用SELECT再查询一次，得到一张额外的临时表，再用临时表的值做更新。

delete from Person where id not in (select id from (select min(id) id from Person group by email) t)

9.内连接的使用（每个衍生的表t必须起别名）

select Employee from (select a.name Employee,a.salary aSalary,b.name bName,b.salary bSalary from Employee a inner join Employee b on a.managerId = b.id)t where aSalary > bSalary

10.to_days函数的使用

select b.id id from Weather a,Weather b where a.Temperature < b.Temperature and to_days(a.recordDate) + 1 = to_days(b.recordDate)

11.左外连接的使用（需要左表所有数据）

select firstName,lastName,city,state from Person p left join Address a on p.personId = a.personId

12.使用函数（函数名自动作为列名）

limit从0开始计数，且无法直接计算，所以通过set操作。

distinct可以去重

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
    select distinct salary from Employee order by salary desc limit N,1
  );
END

13.括号可以表示结果集

select distinct num ConsecutiveNums from Logs where
(id + 1,num) in (select * from Logs) and
(id + 2,num) in (select * from Logs)

14.清空表数据

truncate group_info

15.if函数实现三元运算符

update Salary set sex = if(sex='m','f','m')

16.ascii和char函数的使用

update Salary set sex = char(ascii('m') + ascii('f') - ascii(sex))

17.order by+count+limit的配合使用

select customer_number from Orders group by customer_number order by count(*) desc limit 1

18.distinct+列名对该列去重

select date_id,make_name,count(distinct lead_id) unique_leads,count(distinct partner_id) unique_partners from DailySales group by date_id,make_name

19.case-when-then-else-end的使用

select(
    case
        when mod(id,2) = 1 and id = (select count(*) from Seat) then id
        when mod(id,2) = 1 then id + 1
        else id - 1
    end
) id,student from Seat order by id

update salary set sex = (
    case
        when sex = 'm' then 'f'
        else 'm'
    end
)

20.用逗号和where实现表的连接

select p.product_id,product_name from Sales s,Product p where p.product_id = s.product_id group by product_id having sum(sale_date < '2019-01-01') = 0 and sum(sale_date > '2019-03-31') = 0

21.year和if函数的使用

select user_id buyer_id,join_date,(
    if(sum(year(order_date) = '2019'),sum(year(order_date) = '2019'),0)
) orders_in_2019 from Users u left join Orders o
on o.buyer_id = u.user_id group by user_id

22.用like实现模糊匹配(fuzzyQuery)

select patient_id,patient_name,conditions from Patients where conditions like '% DIAB1%' or conditions like 'DIAB1%'

23.MySQL没有全外连接的语法，可通过左连接 union 右连接的方法实现

select employee_id from (select e.employee_id employee_id,name,salary from Employees e left outer join Salaries s on e.employee_id = s.employee_id union
select s1.employee_id employee_id,name,salary from Employees e1 right outer join Salaries s1 on e1.employee_id = s1.employee_id)t 
where name is null or salary is null order by employee_id

24.dense_rank函数的使用

select score,dense_rank() over(order by score desc) 'rank' from Scores