1. 更改表名：

alter table titles_test rename to titles_2017

2.replace()函数

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。

replace into titles_test VALUES (5,10005,'Senior Engineer', '1986-06-26', '9999-01-01')

3.update 语句

update titles_test set to_date=NULL, from_date='2001-01-01' where to_date='9999-01-01'

4.select 去重排序

select DISTINCT salary from salaries order by salary DESC

5.出现三次以及三次以上的积分

select number
from grade
group by number
having count(number)>=3

6.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees where emp_no%2!=0 and last_name != "Mary" order by hire_date DESC

7. 薪水第二多的员工-嵌套表- as在外面

select * from
(select emp_no, salary from salaries order by salary DESC limit 2 ) as c
order by salary ASC
limit 1

8. 查询每个用户最近一天登录的日子，并且按照user_id升序排序 - Group

select user_id, max(date) from login group by user_id order by user_id ASC

9.批量插入

insert into actor values(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),(2,"NICK","WAHLBERG","2006-02-15 12:34:33")

10.查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t - having 语句

select emp_no,count(emp_no) from salaries group by emp_no having count(emp_no)>15

11. 保留三位小数，四舍五入 round()

select job , round(avg(score),3) from grade group by job order by avg(score) desc

12.请你找出所有非部门领导的员工emp_no in语句
select emp_no from employees where emp_no not in (select emp_no from dept_manager)

13. 字符串拼接 concat()

select concat(last_name," ",first_name) from employees

14.删除emp_no重复的记录，只保留最小的id对应的记录。(不能直接删除查询的结果，要有个中间select)

delete from titles_test where id not in

(select * from
(select min(id) from titles_test group by emp_no) as C)

15. left join

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

select person.id, person.name,task.content from person left join task on person.id=task.person_id order by person.id ASC

 16. 分页 limit X,Y   X是第几个开始，Y是几条数据

select * from employees limit 5,5

 17. replace和length

select length("10,A,B") - length(replace("10,A,B",",","")) as cnt