MySQL刷题记录

1.

 

select* from employees order by hire_date desc limit 1;

 

笔记：

 limit 0,1;

使用limit关键字 从第0条记录 向后读取一个，也就是第一条记录 

 

2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)

当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 

 

使用子查询

select* from employees where hire_date=(select max(hire_date) from employees);

 

 

查找入职员工时间排名倒数第三的员工所有信息

select * from employees where hire_date=(

    select distinct hire_date

    from employees

    order by hire_date desc

    limit 1 offset 2

);

可能有相同时间入职的员工，所以下面这种只考虑到了一个员工

select * from employees order by hire_date desc limit 1,2;

不行

 

 SQL语句：

select x.emp_no,x.salary,x.from_date,x.to_date,y.dept_no

from salaries as x,dept_manager as y

where x.emp_no=y.emp_no order by x.emp_no asc;

注意：属性名那里要用表明区分一下

 

 SQL语句：

select x.last_name,x.first_name,y.dept_no from employees x,dept_emp y

where x.emp_no=y.emp_no and dept_no is not null;

 

 

 

左外连接：

select

    x.last_name,

    x.first_name,

    y.dept_no

from

    employees x

left outer join

    dept_emp y

    on x.emp_no=y.emp_no;

 

 

SQL:

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

 

SQL:

select x.emp_no from employees x

where x.emp_no not in (

    select emp_no from dept_manager

)

 SQL:

select x.emp_no,y.emp_no as manager from dept_emp x,dept_manager y

where x.dept_no=y.dept_no and x.emp_no !=y.emp_no;

 

 

 把两个表连接起来，把各个部门的最高薪资查询出来，再把符合条件这个最高薪资的员工编号求出来

select x.dept_no,x.emp_no,y.salary as maxSalary

from dept_emp x,salaries y

where x.emp_no=y.emp_no and (x.dept_no,y.salary) in (

       

select

    h.dept_no,

    max(z.salary)

from

    dept_emp h,

    salaries z

where

    h.emp_no = z.emp_no

group by

    h.dept_no

)

order by x.dept_no asc

 

 判断数字是否为奇数，用mod(字段，2)=1,为奇数，=0为偶数

 

 

select emp_no,birth_date,first_name,last_name,gender,hire_date from employees

where mod(emp_no,2)=1 and last_name not like "Mary" order by hire_date desc;

 

 

 

select title,avg(s.salary)

from titles x,salaries s

where x.emp_no=s.emp_no

group by x.title

 order by avg(s.salary) asc;

 

 

SQL:

select emp_no,salary

from salaries

where salary=(select salary from salaries order by salary desc limit 1 offset 1 )

order by emp_no

 

 

 先把第二大求出来

select max(salary) from salaries

    where salary !=(select max(salary) from salaries)

再求该第二多薪资的全部信息查询出来

SQL:

select x.emp_no,y.salary,x.last_name,x.first_name

from employees x,salaries y

where x.emp_no=y.emp_no and y.salary=(

    select max(salary) from salaries

    where salary !=(select max(salary) from salaries)

)

 

 

 先左连接员工和雇佣关系模式，再左连接部门关系模式

SQL:

select last_name,first_name,dept_name

from (

    select last_name,first_name,dept_no

    from employees

    left join dept_emp

    on employees.emp_no=dept_emp.emp_no

) as a

left join departments

on a.dept_no=departments.dept_no

 

 

 

 

SQL:

select y.dept_no ,y.dept_name,count(x.salary) as sum

from dept_emp z,salaries x,departments y

where x.emp_no=z.emp_no and y.dept_no=z.dept_no

group by z.dept_no order by z.dept_no

 

 

SQL:

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

 

批量插入数据：

insert into actor(actor_id,first_name,last_name,last_update)

values(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),

      (2,"NICK","WAHLBERG","2006-02-15 12:34:33");

 

更新列表两列内容：

update titles_test set to_date=null ,from_date="2001-01-01" where to_date="9999-01-01"

 

利用replace更新数据

10005替换10001

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

 

select a.id from Weather a,Weather b where 

DATEDIFF(a.recordDate,b.recordDate)=1

and a.Temperature>b.Temperature;

使用函数判断两者差一天

 

 

select x,y,z,

case when x+y>z and x+z>y and y+z>x then "Yes"

ELSE "No"

END as "triangle"

from Triangle;

 

 

 

 sum相当于遍历的作用。