LIMIT [参数1]--m,参数2--n;
#表示从跳过m条数据开始取n行数据 #参数1为可选参数,表示跳过m条数据(默认为0)
eg:1表示从第二行开始 #参数2为必选参数,表示取几行数据
SELECT *FROM employees ORDER BY hire_date DESC LIMIT 1;
SELECT *FROM employees ORDER BY hire_date DESC LIMIT 2,1;
SELECT * FROM employees
eg1: SELECT * FROM table LIMIT 5; //检索前 5 个记录行 等价于
SELECT * FROM table LIMIT 0,5; //检索前 5 个记录行
eg2: SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
#为了检索某行开始到最后的所有数据,可以设置第二个参数为-1
eg3: SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last
WHERE hire_date = (
SELECT DISTINCT hire_date
FROM employees ORDER BY hire_date DESC -- 倒序
LIMIT 1 OFFSET 2 -- 去掉排名倒数第一第二的时间,取倒数第三 );
LIMIT 参数1--m OFFSET 参数2--n // offset 偏移量
#表示跳过n个数据,取m个数据 #参数1表示读取m条数据 #参数2表示跳过n个数据
eg4: SELECT * FROM table LIMIT 2 OFFSET 1; //跳过1条数据读取2条数据,即读取2-3条数据
1.m代表从m+1条记录行开始检索,n代表取出n条数据。(m可设为0)
如:SELECT * FROM 表名 limit 6,5; 表示:从第7条记录行开始算,取出5条数据
2.值得注意的是,n可以被设置为-1,当n为-1时,表示从m+1行开始检索,直到最后一条数据。
如:SELECT * FROM 表名 limit 6,-1; 表示:取出第6条记录行以后的所有数据。
3.若只给出m,则表示从第1条记录行开始算一共取出m条
如:SELECT * FROM 表名 limit 6; 表示:取出前6条记录行。
select s.*,d.dept_no
from salaries s INNER JOIN dept_manager d ON s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01'
1.left join 顾名思义,就是“左连接”,表1左连接表2,以左为主,表示以表1为主,关联上表2的数据,查出来的结果显示左边的所有数据,然后右边显示的是和左边有交集部分的数据。如下: select * from kem...(左表是全的,右表有null)
2.right join “右连接”,表1右连接表2,以右为主,表示以表2为主,关联查询表1的数据,查出表2所有数据以及表1和表2有交集的数据,如下: select * from kemu right join score ...(右表是全的,左表有null)
3.inner join,其实就是“inner join”,为了简写才写成join,两个是表示一个的,内连接,表示以两个表的交集为主,查出来是两个表有交集的部分,其余没有关联就不额外显示出来,(左,右表的共同字段)
SELECT e.last_name, e.first_name, d.dept_no FROM employees AS e, dept_emp AS d WHERE e.emp_no = d.emp_no;
SELECT e.last_name, e.first_name, d.dept_no FROM employees AS e JOIN dept_emp AS d ON e.emp_no=d.emp_no;
SELECT e.last_name, e.first_name, d.dept_no FROM employees AS e INNER JOIN dept_emp AS d ON e.emp_no=d.emp_no;
SELECT e.last_name, e.first_name,d.dept_no FROM employees e left join dept_emp d ON e.emp_no=d.emp_no;
SELECT EMP_NO, t FROM( select EMP_NO, COUNT(*) AS t FROM salaries GROUP BY emp_no ) q where q.t>15
SELECT emp_no,COUNT(*) as t FROM salaries GROUP BY emp_no HAVING t>15
SELECT ( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门, count( de.emp_no ) AS 人数 FROM dept_emp de WHERE de.to_date = '9999-01-01' GROUP BY de.dept_no HAVING count( de.emp_no ) > 30000
GROUP BY必须得配合聚合函数来用,分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。
补充:WHERE语句在GROUP BY语句之前,SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后,SQL会在分组之后计算HAVING语句
select salary from salaries where to_date='9999-01-01' group by salary order by salary desc;
select DISTINCT salary from salaries WHERE to_date="9999-01-01" ORDER by salary DESC
select emp_no from employees where emp_no not in (select emp_no from dept_manager);
SELECT e.emp_no FROM employees e LEFT JOIN dept_manager d on e.emp_no=d.emp_no WHERE dept_no is NULL
SELECT e.emp_no,m.emp_no
FROM dept_emp AS e INNER JOIN dept_manager AS m
ON e.dept_no=m.dept_no
where e.emp_no!=m.emp_no
SELECT * FROM employees
WHERE MOD(emp_no,2)!=0 and last_name!= 'Mary'
ORDER BY hire_date DESC
在 MySQL 中求余函数 MOD(x,y) 返回 x 被 y 除后的余数,MOD() 对于带有小数部分的数值也起作用,它返回除法运算后的余数。(java取余用%)
你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg,并且以avg升序排序 SELECT t.title ,avg(s.salary)
from salaries s
INNER JOIN titles t ON t.emp_no=s.emp_no
GROUP by t.title
ORDER BY avg(s.salary) ASC
SELECT emp_no, salary
FROM salaries
ORDER BY salary DESC LIMIT 1 OFFSET 1 ;
等价于
SELECT emp_no, salary
FROM salaries
ORDER BY salary DESC LIMIT 1 ,1
考虑到工资第一多第二多的员工都有可能有多个,所以需要将其按照工资分组再排序
SELECT emp_no, salary
FROM salaries
where salary=(
select salary
from salaries
order by salary desc limit 1 offset 1)
请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不能使用order by完成
SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e
INNER JOIN salaries s ON e.emp_no=s.emp_no
WHERE s.salary=(
SELECT MAX(salary)
FROM salaries
WHERE salary <
(
SELECT max(salary)
FROM salaries
WHERE to_date='9999-01-01'
)
and to_date='9999-01-01'
)
and s.to_date='9999-01-01'
请你查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工,
SELECT e.last_name, e.first_name,s.dept_name
FROM employees e
LEFT JOIN dept_emp d on e.emp_no=d.emp_no
LEFT JOIN departments s ON s.dept_no=d.dept_no
查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
SELECT s.emp_no, (b.salary-s.salary) growth
FROM salaries s
INNER JOIN employees e ON s.emp_no=e.emp_no
INNER JOIN (
SELECT s.emp_no, s.salary
FROM salaries s
WHERE s.to_date='9999-01-01'
) b ON b.emp_no=e.emp_no
WHERE s.from_date=e.hire_date
ORDER BY growth ASC
SELECT s.emp_no, (b.salary-s.salary) growth
FROM
(
SELECT s.emp_no, s.salary
FROM salaries s
INNER JOIN employees e ON s.emp_no=e.emp_no
WHERE s.from_date=e.hire_date
)s
INNER JOIN (
SELECT s.emp_no, s.salary
FROM salaries s
WHERE s.to_date='9999-01-01'
) b ON b.emp_no=s.emp_no
select d.dept_no,d.dept_name,COUNT(dept_name) as sum
FROM departments d
INNER JOIN dept_emp e ON d.dept_no = e.dept_no
INNER JOIN salaries s on e.emp_no =s.emp_no
GROUP BY d.dept_name,d.dept_no
=
SELECT f.dept_no,f.dept_name,COUNT(f.dept_name) AS sum
from(
SELECT d.dept_no , e.emp_no,d.dept_name
FROM departments d
INNER JOIN dept_emp e ON d.dept_no=e.dept_no
) f
inner join salaries s ON f.emp_no=s.emp_no
GROUP BY f.dept_no,f.dept_name
ORDER BY f.dept_no
SELECT a.dept_no,a.emp_no,s.salary
FROM(
SELECT e.emp_no, e.dept_no
FROM dept_emp e
LEFT JOIN dept_manager m ON m.emp_no=e.emp_no
where m.dept_no is null
) a
INNER JOIN salaries s ON a.emp_no=s.emp_no