SQL面试题

SELECT  *
FROM employees 
order by hire_date desc limit 1;

 

 

 

SELECT *
FROM employees
WHERE hire_date=
(SELECT hire_date
FROM employees
GROUP BY hire_date
ORDER BY hire_date desc limit 2,1
)

 

group by 结合语句 对列表的某一项进行结合

order by语句默认按照升序对记录进行排序

如果希望按照降序进行排序，可以使用DESC

limit子句用于限制查询结果返回数量，常用于分页查询

limit i,n

i:为查询结果的索引值（默认从0开始），当i=0时可省略i

n：为查询结果返回的数量

 

 

 

SELECT s.emp_no,salary,from_date,s.to_date,d.dept_no 
FROM salaries AS s 
INNER JOIN dept_manager as d 
on s.emp_no=d.emp_no
ORDER BY s.emp_no

 

 

 

SELECT last_name,first_name,dept_no
FROM employees AS s
INNER JOIN dept_emp as d
on s.emp_no=d.emp_no

 

 

 

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

 

 

SELECT salary
FROM salaries
group by salary
order by salary desc

 

 

SELECT emp_no
FROM employees
where emp_no not in
(SELECT emp_no 
 FROM dept_manager );

select e.emp_no,m.emp_no as manager 
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 emp_no%2=1 and last_name<>'Mary' 
ORDER BY hire_date desc

 

 

select title,avg(s.salary) 
from titles as t 
INNER JOIN salaries as s 
on t.emp_no=s.emp_no 
group by title

 

 

SELECT emp_no,salary
FROM salaries
where salary=(
SELECT salary
WHERE salaries
GROUP BY salary 
order by salary desc LIMIT 1,1)

 

 

 

 

SELECT e.last_name,e.first_name,s.dept_name 
FROM employees AS e 
LEFT JOIN dept_emp AS d 
ON e.emp_no=d.emp_no 
LEFT JOIN departments AS s 
ON s.dept_no = d.dept_no

 

 

 

 

SELECT d.dept_no,d.dept_name,count(s.salary) AS sum 
FROM departments AS d 
INNER JOIN dept_emp AS e 
ON d.dept_no= e.dept_no 
INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no 
GROUP BY d.dept_no,d.dept_name 
ORDER BY d.dept_no

  

SELECT film_id,title 
FROM film
WHERE film_id not in
(
SELECT f.film_id 
FROM film as f 
INNER JOIN film_category as c
on f.film_id = c.film_id 
INNER JOIN category as ca
on ca.category_id=c.category_id
)

 

 

SELECT title,description
FROM film
WHERE film_id in
(
SELECT film_id
FROM film_category
WHERE category_id=
(SELECT category_id
FROM category
WHERE name='Action'
)
)

 

 特殊用法：

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')

 

 （删除加嵌套子查询）

DELETE FROM titles_test
WHERE id not in
(SELECT *
FROM(
SELECT min(id)
FROM titles_test
GROUP BY emp_no
as ss
)
)

 

 replace特殊用法

UPDATE titles_test
SET emp_no = REPLACE(emp_no,10001,10005)
WHERE id=5;

 

 字段计算

UPDATE salaries as s
JOIN emp_bonus as e
ON s.emp_no=e.emp_no
SET salary = salary*1.1
WHERE to_date='9999-01-01'

 

 

 

截取字符串

select first_name
from employees
order by right(first_name,2)

 

 

SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp 
GROUP BY dept_no

 

 

SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary 
FROM salaries 
WHERE to_date = '9999-01-01';

 

 

SELECT distinct number
FROM (
SELECT *,count(id) over(partition by number) as o from grade ) as v
WHERE v.o>2 order by v.number
)

 

 

SELECT id,number,dense_rank() 
OVER (order by number desc) as t_rank
FROM passing_number
ORDER BY number desc,id asc;