Java面试题 —数据库

原创 于 2019-08-23 18:16:13 发布 · 272 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

  1. 查找最晚入职员工的所有信息

     CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));

    （1）不考虑重复：

     SELECT  * FROM employees ORDER BY hire_date DESC LIMIT 0,1

    （2）考虑重复：

     SELECT *  FROM employees WHERE hire_date = (select MAX(hire_date)  FROM employees)

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

     CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));

    （1）不考虑重复：

      `SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1`

    （2）考虑重复：

     SELECT * FROM employees 
 WHERE hire_date = (
 SELECT DISTINCT hire_date FROM employees 
 ORDER BY hire_date DESC limit 2,1
 );

  3. 查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

     CREATE TABLE `dept_manager` (
 `dept_no` char(4) NOT NULL,
 `emp_no` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`dept_no`));
 CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));

    (1)

     SELECT   s.*,d.dept_no  FROM salaries s , dept_manager d
 WHERE s.to_date='9999-01-01' 
 AND d.to_date='9999-01-01'
 AND s.emp_no=d.emp_no

    解析：关于为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？

    因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前，而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导。

  4. 查找所有已经分配部门的员工的last_name和first_name

     CREATE TABLE `dept_emp` (
 `emp_no` int(11) NOT NULL,
 `dept_no` char(4) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`dept_no`));
 CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));

    (1) 个人答案：

     SELECT e.last_name,e.first_name,d.dept_no FROM employees e,dept_emp d
 WHERE  d.emp_no=e.emp_no

    （2）参考答案：

     select employees.last_name, first_name, dept_emp.dept_no
 from dept_emp inner join employees
 on dept_emp.emp_no = employees.emp_no;

  5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

     CREATE TABLE `dept_emp` (
 `emp_no` int(11) NOT NULL,
 `dept_no` char(4) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`dept_no`));
 CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));

    (1)个人答案：

     SELECT e.last_name,e.first_name,d.dept_no
 FROM employees AS e
 LEFT JOIN dept_emp AS d
 on e.emp_no=d.emp_no

    解析：left join 就是说左边表的内容全都要，并把右边表的信息匹配进来，没有的显示NULL。
    注意：on与where有什么区别，两个表连接时用on，在使用left jion时，on和where条件的区别如下：

    1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
    2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

  6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

      CREATE TABLE  `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));
 CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));

    解析：此题应注意以下四个知识点：

    1. 由于测试数据中，salaries.emp_no 不唯一（因为号码为 emp_no 的员工会有多次涨薪的可能，所以在 salaries 中对应的记录不止一条），employees.emp_no 唯一，即 salaries 的数据会多于 employees，因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no，则有限制条件 e.emp_no = s.emp_no
    2. 根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等，因此有限制条件 e.hire_date = s.from_date
    3. 根据题意要按照 emp_no 值逆序排列，因此最后要加上 ORDER BY e.emp_no DESC
    4. 为了代码良好的可读性，运用了 Alias 别名语句，将 employees 简化为 e，salaries 简化为s，employees AS e 与 salaries AS s，其中 AS 可以省略

    （1）方法一：并列查询

     SELECT e.emp_no,s.salary 
 FROM employees AS e,salaries AS s
 WHERE e.emp_no=s.emp_no AND e.hire_date=s.from_date
 ORDER BY e.emp_no DESC

    （2）方法二：内联查询

     SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS s
 ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
 ORDER BY e.emp_no DESC

  7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

     CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));

    解析：

    1. 用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
    2. 根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
    3. 由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

    答案：

         SELECT emp_no ,COUNT(emp_no) AS t
     FROM salaries
     GROUP BY emp_no
     HAVING t>15

  8. 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

     CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));

    （1）个人答案：

     SELECT DISTINCT salary
 FROM salaries 
 WHERE salaries.to_date='9999-01-01' 
 ORDER BY salary DESC

    （2）参考答案：

     SELECT salary FROM salaries
 WHERE to_date='9999-01-01'
 GROUP BY salary
 ORDER BY salary DESC;

    解析：对于distinct与group by的使用:

    1. 当对系统的性能高并数据量大时使用group by
    2. 当对系统的性能不高时使用数据量少时两者皆可
    3. 尽量使用group by

  9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’

     CREATE TABLE `dept_manager` (
 `dept_no` char(4) NOT NULL,
 `emp_no` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`dept_no`));
 CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));

    （1）个人答案：

     SELECT d.dept_no,d.emp_no,s.salary
 FROM dept_manager AS d, salaries AS s
 WHERE d.emp_no=s.emp_no
 AND d.to_date='9999-01-01'
 AND s.to_date='9999-01-01'

    （2）参考答案：

     SELECT d.dept_no, d.emp_no, s.salary 
 FROM salaries AS s INNER JOIN dept_manager AS d 
 ON d.emp_no = s.emp_no
 AND d.to_date = '9999-01-01'
 AND s.to_date = '9999-01-01'

  10. 获取所有非manager的员工emp_no

    CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

    （1）

    SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

  11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。
    结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no

    CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

    (1)个人答案：

    SELECT e.emp_no AS emp_no,m.emp_no AS manager_no
FROM dept_emp e,dept_manager m
WHERE e.emp_no!=m.emp_no
AND e.dept_no=m.dept_no
AND e.to_date='9999-01-01'
AND m.to_date='9999-01-01'

    （2）参考答案：

    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
AND e.to_date = '9999-01-01'
AND m.to_date = '9999-01-01';

  12. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

    CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）参考：

    SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no

    解析：

    1. 先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
    2. 选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = '9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
    3. 用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
    4. 将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

  13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t

    CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

    （1）

    SELECT title,COUNT(title) AS t
FROM titles
GROUP BY title HAVING t>=2

  14. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
    注意对于重复的title进行忽略。

    CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

    （1）解析：重复的title及相同的emp_no

    SELECT DISTINCT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title HAVING t>=2

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

    CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

    （1）奇数即 %2 =1

  16. 统计出当前各个title类型对应的员工当前（to_date=‘9999-01-01’）薪水对应的平均工资。结果给出title以及平均工资avg。

    CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

    （1）

    SELECT t.title ,AVG(s.salary) AS 'avg'
FROM salaries s INNER JOIN titles t
ON t.emp_no=s.emp_no
WHERE t.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY title

  17. 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

    CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）不考虑多人：

    SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC 
LIMIT 1,1

    （2）考虑多人（添加GROUP BY 或者DISTINCT）：

    SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER BY salary DESC
LIMIT 1,1;v

  18. 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

    CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）方法一：

       SELECT e.emp_no, s1.salary, e.last_name, e.first_name
    FROM employees e, salaries s1, salaries s2
    WHERE e.emp_no = s1.emp_no
    AND s1.to_date = '9999-01-01'
    AND s2.to_date = '9999-01-01'
    AND s1.salary <= s2.salary
    GROUP BY s1.emp_no
    HAVING COUNT(DISTINCT s2.salary)=2

    （2）方法二：

    SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

  19. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

    CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

    （1）方法一：

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

    解析：两次LFET JOIN 连接嵌套

    1. 第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
    2. 第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

    （2）方法二：

    SELECT em.last_name, em.first_name, dp.dept_name
FROM  (employees em LEFT JOIN dept_emp de ON em.emp_no = de.emp_no) AS t
LEFT JOIN departments dp ON dp.dept_no = t.dept_no

  20. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

    CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）

    select (max(salary)-min(salary)) as growth from salaries
where emp_no = '10001'

  21. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

    CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）参考答案：

    select a.emp_no, (b.salary - c.salary) as growth
from employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

  22. 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

    CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）参考答案（本题题设次数总和当做薪水出现次数）：

    SELECT de.dept_no,d.dept_name,COUNT(s.salary) AS 'sum'
FROM dept_emp de 
INNER JOIN departments d
ON de.dept_no=d.dept_no
INNER JOIN salaries s
ON de.emp_no=s.emp_no
GROUP BY de.dept_no

  23. 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照

     emp_no升序排列
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）解析：本题的主要思想是复用salaries表进行比较排名，具体思路如下：

    1. 从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
    2. 本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
    3. 千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
    4. 最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

  24. 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

    CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

    （1）

    SELECT de.dept_no, s.emp_no, s.salary 
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')

    解析：

    1. 先用INNER JOIN连接employees和salaries，找出当前所有员工的工资情况
    2. 再用INNER JOIN连接dept_emp表，找到所有员工所在的部门
    3. 最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = ‘9999-01-01’)选出当前所有非manager员工，再依次输出dept_no、emp_no、salary

  25. 获取select * from employees对应的执行计划
    （1）EXPLAIN SELECT * FROM employees
    解析：

    explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
    作用

    1. 表的读取顺序
    2. 数据读取操作的操作类型
    3. 哪些索引可以使用
    4. 哪些索引被实际使用
    5. 表之间的引用
    6. 每张表有多少行被优化器查询

  26. 创建一个actor表，包含如下列信息

    （1）

    CREATE TABLE actor(
    actor_id      smallint(5) not null  primary key,
    first_name    varchar(45) not null,
    last_name     varchar(45) not null,
    last_update   timestamp  not null DEFAULT (datetime('now','localtime'))
)

    注意：ast_update末尾加上DEFAULT是为该字段设置默认值，且默认值为(datetime(‘now’,‘localtime’))，即获得系统时间，注意最外层的括号不可省略

  27. 对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

    CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

    （1）

    insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

    另外：如果不存在则插入，如果存在则替换

    INSERT  REPLACE INTO tablename VALUES(...);
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值