Mysql练习题

Mysql练习题

创建dept部门表

mysql> create table dept( deptno int(2) primary key,
dname varchar(14), 
loc varchar(13));

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | NO   | PRI | NULL    |       |
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK'),
    -> (20,'RESEARCH','DALLAS'),
    -> (30,'SALES','CHICAGO'),
    -> (40,'OPERATIONS','BOSTON');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

创建emp员工表（注意MUL是外键，参照dept表的主键）

mysql> create table emp( empno int(4) primary key, 
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal float(7,2),
comm float(7,2),
deptno int(2),
constraint emp_dept_deptno foreign key(deptno) references dept(deptno));

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno    | int(4)      | NO   | PRI | NULL    |       |
| ename    | varchar(10) | YES  |     | NULL    |       |
| job      | varchar(9)  | YES  |     | NULL    |       |
| mgr      | int(4)      | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| sal      | float(7,2)  | YES  |     | NULL    |       |
| comm     | float(7,2)  | YES  |     | NULL    |       |
| deptno   | int(2)      | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(
    -> 7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),
    -> (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
    -> (7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
    -> (7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),
    -> (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
    -> (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),
    -> (7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),
    -> (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000.00,NULL,20),
    -> (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000.00,NULL,10),
    -> (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),
    -> (7876,'ADAMS','CLERK',7788,'1987-07-13',1100.00,NULL,20),
    -> (7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),
    -> (7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),
    -> (7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

创建salgrade工资等级表

mysql> create table salgrade( grade int(11), 
losal int(11),
hisal int(11));

mysql> insert into salgrade(grade,losal,hisal) values(
    -> 1,700,1200),
    -> (2,1201,1400),
    -> (3,1401,2000),
    -> (4,2001,3000),
    -> (5,3001,9999);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

1、在emp表中，求员工的姓名，工资 奖金，工资+奖金（使用ifnull函数

mysql> select ename,sal,comm,sal+ifnull(comm,0) from emp;
+--------+---------+---------+--------------------+
| ename  | sal     | comm    | sal+ifnull(comm,0) |
+--------+---------+---------+--------------------+
| SMITH  |  800.00 |    NULL |             800.00 |
| ALLEN  | 1600.00 |  300.00 |            1900.00 |
| WARD   | 1250.00 |  500.00 |            1750.00 |
| JONES  | 2975.00 |    NULL |            2975.00 |
| MARTIN | 1250.00 | 1400.00 |            2650.00 |
| BLAKE  | 2850.00 |    NULL |            2850.00 |
| CLARK  | 2450.00 |    NULL |            2450.00 |
| SCOTT  | 3000.00 |    NULL |            3000.00 |
| KING   | 5000.00 |    NULL |            5000.00 |
| TURNER | 1500.00 |    0.00 |            1500.00 |
| ADAMS  | 1100.00 |    NULL |            1100.00 |
| JAMES  |  950.00 |    NULL |             950.00 |
| FORD   | 3000.00 |    NULL |            3000.00 |
| MILLER | 1300.00 |    NULL |            1300.00 |
+--------+---------+---------+--------------------+
14 rows in set (0.00 sec)

2、在emp表中，将员工名字和工号连接起来显示，例如：SMITH_7369

mysql> select concat(ename,'_',empno) from emp;
+-------------------------+
| concat(ename,'_',empno) |
+-------------------------+
| SMITH_7369              |
| ALLEN_7499              |
| WARD_7521               |
| JONES_7566              |
| MARTIN_7654             |
| BLAKE_7698              |
| CLARK_7782              |
| SCOTT_7788              |
| KING_7839               |
| TURNER_7844             |
| ADAMS_7876              |
| JAMES_7900              |
| FORD_7902               |
| MILLER_7934             |
+-------------------------+
14 rows in set (0.01 sec)

3、求员工的编号，姓名，所在的部门名字（emp和dept）

mysql> select e.empno,e.ename,d.dname from emp e,dept d
    -> where e.deptno=d.deptno;
+-------+--------+------------+
| empno | ename  | dname      |
+-------+--------+------------+
|  7782 | CLARK  | ACCOUNTING |
|  7839 | KING   | ACCOUNTING |
|  7934 | MILLER | ACCOUNTING |
|  7369 | SMITH  | RESEARCH   |
|  7566 | JONES  | RESEARCH   |
|  7788 | SCOTT  | RESEARCH   |
|  7876 | ADAMS  | RESEARCH   |
|  7902 | FORD   | RESEARCH   |
|  7499 | ALLEN  | SALES      |
|  7521 | WARD   | SALES      |
|  7654 | MARTIN | SALES      |
|  7698 | BLAKE  | SALES      |
|  7844 | TURNER | SALES      |
|  7900 | JAMES  | SALES      |
+-------+--------+------------+
14 rows in set (0.00 sec

4、emp表中，求哪些人的工资比scott的工资高，显示用户姓名和工资

mysql> select ename,sal from emp where sal > (select sal from emp where ename='scott');
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

5、求sales部门有几个人？

mysql> select count(deptno) from emp where deptno=(selectt deptno from dept where dname='SALES');
+---------------+
| count(deptno) |
+---------------+
|             6 |
+---------------+
1 row in set (0.00 sec)

6、求工资最低的那个人的姓名，工资，部门

mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno order by e.sal limit 1;
+-------+--------+----------+
| ename | sal    | dname    |
+-------+--------+----------+
| SMITH | 800.00 | RESEARCH |
+-------+--------+----------+
1 row in set (0.00 sec)

7、emp表中，求哪些人是普通员工，不是领导

mysql> select ename from emp where empno not in (select mgr from emp where mgr is not null);
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| MARTIN |
| TURNER |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+
8 rows in set (0.00 sec)

8、公司老板给员工某福利，岗位为salesman销售的，涨工资1000，岗位为analyst分析师的，涨工资2000，其他人维持不变，列出员工名字，旧的薪资，岗位，新的薪资。（使用case语句

mysql> select ename,job,sal,case when job='SALESMAN' then sal+1000 when job='ANALYST' then sal+2000 else sal end as sal from emp;
+--------+-----------+---------+---------+
| ename  | job       | sal     | sal     |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2600.00 |
| WARD   | SALESMAN  | 1250.00 | 2250.00 |
| JONES  | MANAGER   | 2975.00 | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 | 2250.00 |
| BLAKE  | MANAGER   | 2850.00 | 2850.00 |
| CLARK  | MANAGER   | 2450.00 | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 | 5000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2500.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 5000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)