MySQL基础练习---牛客网的数据以及典型题目
1 部门表departments
部门no和部门名称
2 部门员工表 dept_emp
每个部门对应的员工信息
3 部门经理表 dept_manager
每个部门的经理信息
4 员工表 employees
员工的具体信息
5 薪水表 salaries
所有员工的薪水
6 职称表 titles
所有员工的职称
MySQL代码
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sqlpractice_nk` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `sqlpractice_nk`; /*Table structure for table `departments` */ DROP TABLE IF EXISTS `departments`; CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `departments` */ insert into `departments`(`dept_no`,`dept_name`) values (\'d001\',\'Marketing\'),(\'d002\',\'Finance\'),(\'d003\',\'Human Resources\'),(\'d004\',\'Production\'),(\'d005\',\'Development\'),(\'d006\',\'Quality Management\'); /*Table structure for table `dept_emp` */ DROP TABLE IF EXISTS `dept_emp`; 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `dept_emp` */ insert into `dept_emp`(`emp_no`,`dept_no`,`from_date`,`to_date`) values (10001,\'d001\',\'1986-06-26\',\'9999-01-01\'),(10002,\'d001\',\'1996-08-03\',\'9999-01-01\'),(10003,\'d004\',\'1995-12-03\',\'9999-01-01\'),(10004,\'d004\',\'1986-12-01\',\'9999-01-01\'),(10005,\'d003\',\'1989-09-12\',\'9999-01-01\'),(10006,\'d002\',\'1990-08-05\',\'9999-01-01\'),(10007,\'d005\',\'1989-02-10\',\'9999-01-01\'),(10008,\'d005\',\'1998-03-11\',\'2000-07-31\'),(10009,\'d006\',\'1985-02-18\',\'9999-01-01\'),(10010,\'d005\',\'1996-11-24\',\'2000-06-26\'),(10010,\'d006\',\'2000-06-26\',\'9999-01-01\'); /*Table structure for table `dept_manager` */ DROP TABLE IF EXISTS `dept_manager`; 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `dept_manager` */ insert into `dept_manager`(`dept_no`,`emp_no`,`from_date`,`to_date`) values (\'d001\',10002,\'1996-08-03\',\'9999-01-01\'),(\'d004\',10004,\'1986-12-01\',\'9999-01-01\'),(\'d003\',10005,\'1989-09-12\',\'9999-01-01\'),(\'d002\',10006,\'1990-08-05\',\'9999-01-01\'),(\'d005\',10010,\'1996-11-24\',\'2000-06-26\'),(\'d006\',10010,\'2000-06-26\',\'9999-01-01\'); /*Table structure for table `employees` */ DROP TABLE IF EXISTS `employees`; 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `employees` */ insert into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10001,\'1953-09-02\',\'Georgi\',\'Facello\',\'M\',\'1986-06-26\'),(10002,\'1964-06-02\',\'Bezalel\',\'Simmel\',\'F\',\'1985-11-21\'),(10003,\'1959-12-03\',\'Parto\',\'Bamford\',\'M\',\'1986-08-28\'),(10004,\'1954-05-01\',\'Chirstian\',\'Koblick\',\'M\',\'1986-12-01\'),(10005,\'1955-01-21\',\'Kyoichi\',\'Maliniak\',\'M\',\'1989-09-12\'),(10006,\'1953-04-20\',\'Anneke\',\'Preusig\',\'F\',\'1989-06-02\'),(10007,\'1957-05-23\',\'Tzvetan\',\'Zielinski\',\'F\',\'1989-02-10\'),(10008,\'1958-02-19\',\'Saniya\',\'Kalloufi\',\'M\',\'1994-09-15\'),(10009,\'1952-04-19\',\'Sumant\',\'Peac\',\'F\',\'1985-02-18\'),(10010,\'1963-06-01\',\'Duangkaew\',\'Piveteau\',\'F\',\'1989-08-24\'),(10011,\'1953-11-07\',\'Mary\',\'Sluis\',\'F\',\'1990-01-22\'); /*Table structure for table `salaries` */ DROP TABLE IF EXISTS `salaries`; 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `salaries` */ insert into `salaries`(`emp_no`,`salary`,`from_date`,`to_date`) values (10001,60117,\'1986-06-26\',\'1987-06-26\'),(10001,62102,\'1987-06-26\',\'1988-06-25\'),(10001,66074,\'1988-06-25\',\'1989-06-25\'),(10001,66596,\'1989-06-25\',\'1990-06-25\'),(10001,66961,\'1990-06-25\',\'1991-06-25\'),(10001,71046,\'1991-06-25\',\'1992-06-24\'),(10001,74333,\'1992-06-24\',\'1993-06-24\'),(10001,75286,\'1993-06-24\',\'1994-06-24\'),(10001,75994,\'1994-06-24\',\'1995-06-24\'),(10001,76884,\'1995-06-24\',\'1996-06-23\'),(10001,80013,\'1996-06-23\',\'1997-06-23\'),(10001,81025,\'1997-06-23\',\'1998-06-23\'),(10001,81097,\'1998-06-23\',\'1999-06-23\'),(10001,84917,\'1999-06-23\',\'2000-06-22\'),(10001,85112,\'2000-06-22\',\'2001-06-22\'),(10001,85097,\'2001-06-22\',\'2002-06-22\'),(10001,88958,\'2002-06-22\',\'9999-01-01\'),(10002,72527,\'1996-08-03\',\'1997-08-03\'),(10002,72527,\'1997-08-03\',\'1998-08-03\'),(10002,72527,\'1998-08-03\',\'1999-08-03\'),(10002,72527,\'1999-08-03\',\'2000-08-02\'),(10002,72527,\'2000-08-02\',\'2001-08-02\'),(10002,72527,\'2001-08-02\',\'9999-01-01\'),(10003,40006,\'1995-12-03\',\'1996-12-02\'),(10003,43616,\'1996-12-02\',\'1997-12-02\'),(10003,43466,\'1997-12-02\',\'1998-12-02\'),(10003,43636,\'1998-12-02\',\'1999-12-02\'),(10003,43478,\'1999-12-02\',\'2000-12-01\'),(10003,43699,\'2000-12-01\',\'2001-12-01\'),(10003,43311,\'2001-12-01\',\'9999-01-01\'),(10004,40054,\'1986-12-01\',\'1987-12-01\'),(10004,42283,\'1987-12-01\',\'1988-11-30\'),(10004,42542,\'1988-11-30\',\'1989-11-30\'),(10004,46065,\'1989-11-30\',\'1990-11-30\'),(10004,48271,\'1990-11-30\',\'1991-11-30\'),(10004,50594,\'1991-11-30\',\'1992-11-29\'),(10004,52119,\'1992-11-29\',\'1993-11-29\'),(10004,58326,\'1994-11-29\',\'1995-11-29\'),(10004,60770,\'1995-11-29\',\'1996-11-28\'),(10004,62566,\'1996-11-28\',\'1997-11-28\'),(10004,64340,\'1997-11-28\',\'1998-11-28\'),(10004,67096,\'1998-11-28\',\'1999-11-28\'),(10004,69722,\'1999-11-28\',\'2000-11-27\'),(10004,70698,\'2000-11-27\',\'2001-11-27\'),(10004,74057,\'2001-11-27\',\'9999-01-01\'),(10005,78228,\'1989-09-12\',\'1990-09-12\'),(10005,82621,\'1990-09-12\',\'1991-09-12\'),(10005,83735,\'1991-09-12\',\'1992-09-11\'),(10005,85572,\'1992-09-11\',\'1993-09-11\'),(10005,85076,\'1993-09-11\',\'1994-09-11\'),(10005,86050,\'1994-09-11\',\'1995-09-11\'),(10005,88448,\'1995-09-11\',\'1996-09-10\'),(10005,89724,\'1997-09-10\',\'1998-09-10\'),(10005,90392,\'1998-09-10\',\'1999-09-10\'),(10005,90531,\'1999-09-10\',\'2000-09-09\'),(10005,91453,\'2000-09-09\',\'2001-09-09\'),(10005,94692,\'2001-09-09\',\'9999-01-01\'),(10006,43311,\'1990-08-05\',\'1991-08-05\'),(10006,43311,\'1991-08-05\',\'1992-08-04\'),(10006,43311,\'1992-08-04\',\'1993-08-04\'),(10006,43311,\'1993-08-04\',\'1994-08-04\'),(10006,43311,\'1994-08-04\',\'1995-08-04\'),(10006,43311,\'1995-08-04\',\'1996-08-03\'),(10006,43311,\'1996-08-03\',\'1997-08-03\'),(10006,43311,\'1997-08-03\',\'1998-08-03\'),(10006,43311,\'1998-08-03\',\'1999-08-03\'),(10006,43311,\'1999-08-03\',\'2000-08-02\'),(10006,43311,\'2000-08-02\',\'2001-08-02\'),(10006,43311,\'2001-08-02\',\'9999-01-01\'),(10007,56724,\'1989-02-10\',\'1990-02-10\'),(10007,60740,\'1990-02-10\',\'1991-02-10\'),(10007,62745,\'1991-02-10\',\'1992-02-10\'),(10007,63475,\'1992-02-10\',\'1993-02-09\'),(10007,63208,\'1993-02-09\',\'1994-02-09\'),(10007,64563,\'1994-02-09\',\'1995-02-09\'),(10007,68833,\'1995-02-09\',\'1996-02-09\'),(10007,70220,\'1996-02-09\',\'1997-02-08\'),(10007,73362,\'1997-02-08\',\'1998-02-08\'),(10007,75582,\'1998-02-08\',\'1999-02-08\'),(10007,79513,\'1999-02-08\',\'2000-02-08\'),(10007,80083,\'2000-02-08\',\'2001-02-07\'),(10007,84456,\'2001-02-07\',\'2002-02-07\'),(10007,88070,\'2002-02-07\',\'9999-01-01\'),(10008,46671,\'1998-03-11\',\'1999-03-11\'),(10008,48584,\'1999-03-11\',\'2000-03-10\'),(10008,52668,\'2000-03-10\',\'2000-07-31\'),(10009,60929,\'1985-02-18\',\'1986-02-18\'),(10009,64604,\'1986-02-18\',\'1987-02-18\'),(10009,64780,\'1987-02-18\',\'1988-02-18\'),(10009,66302,\'1988-02-18\',\'1989-02-17\'),(10009,69042,\'1989-02-17\',\'1990-02-17\'),(10009,70889,\'1990-02-17\',\'1991-02-17\'),(10009,71434,\'1991-02-17\',\'1992-02-17\'),(10009,74612,\'1992-02-17\',\'1993-02-16\'),(10009,76518,\'1993-02-16\',\'1994-02-16\'),(10009,78335,\'1994-02-16\',\'1995-02-16\'),(10009,80944,\'1995-02-16\',\'1996-02-16\'); /*Table structure for table `titles` */ DROP TABLE IF EXISTS `titles`; CREATE TABLE `titles` ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `titles` */ insert into `titles`(`emp_no`,`title`,`from_date`,`to_date`) values (10001,\'Senior Engineer\',\'1986-06-26\',\'9999-01-01\'),(10002,\'Staff\',\'1996-08-03\',\'9999-01-01\'),(10003,\'Senior Engineer\',\'1995-12-03\',\'9999-01-01\'),(10004,\'Engineer\',\'1986-12-01\',\'1995-12-01\'),(10004,\'Senior Engineer\',\'1995-12-01\',\'9999-01-01\'),(10005,\'Senior Staff\',\'1996-09-12\',\'9999-01-01\'),(10005,\'Staff\',\'1989-09-12\',\'1996-09-12\'),(10006,\'Senior Engineer\',\'1990-08-05\',\'9999-01-01\'),(10007,\'Senior Staff\',\'1996-02-11\',\'9999-01-01\'),(10007,\'Staff\',\'1989-02-10\',\'1996-02-11\'),(10008,\'Assistant Engineer\',\'1998-03-11\',\'2000-07-31\'),(10009,\'Assistant Engineer\',\'1985-02-18\',\'1990-02-18\'),(10009,\'Engineer\',\'1990-02-18\',\'1995-02-18\'),(10009,\'Senior Engineer\',\'1995-02-18\',\'9999-01-01\'),(10010,\'Engineer\',\'1996-11-24\',\'9999-01-01\'),(10010,\'Engineer\',\'1996-11-24\',\'9999-01-01\');
典型题目汇总
以下题目是在第二遍做时遇到困难的：
（1）从titles表获取按照title进行分组，注意对于重复的title进行忽略
（2）查找所有员工自入职以来的薪水涨幅情况
（3）对所有员工的薪水按照salary进行按照1-N的排名
（4）获取员工其当前的薪水比其manager当前薪水还高的相关信息
（5）汇总各个部门当前员工的title类型的分配数目
（6）
0
版权声明：本文为youngao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。