数据库基础练习（复习）

指挥官乄
137 阅读4分钟

标题： 数据库基础练习（复习）

1、表

  • 员工表、部门表、薪水表
CREATE DATABASE `mysqlpractice`
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );



INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

2、习题

地址: 习题地址

1、第一题

1、取得每个部门最高薪水的人员名称

-- 思路，根据部门分组，查询最高
-- 通过emp进行多表联查，条件是两张表的部门编号相同，且最高薪水相等
-- order by 指定结果集进行排序

select e.ename,e.deptno,e.sal 

from 

(select deptno , max(sal) as s  from emp group by deptno) t 

join 
emp e 

on e.deptno = t.deptno and e.sal = t.s

order by  e.deptno

2.第二题

2、哪些人的薪水在部门的平均薪水之上


select e.ename , e.deptno,e.sal,t.s from emp e 
join 
(select deptno,avg(sal) as s from emp group by deptno) t
on e.deptno = t.deptno and e.sal > t.s 
order by deptno

3.第三题

3. 取得平均薪水最高的部门的部门编号


select deptno, max(sal) from emp GROUP BY DEPTNO 

-- 将结果集，当作一张临时表使用
select max(t.sal)
from
(select e.deptno d, max(sal) sal from emp e GROUP BY deptno  ) t

3、牛客练习

地址： mysql地址

第四题

地址： 查询表中前两个用户 使用limit 方法，可别忘记了

limit 可以进行分页，可以进行查找

  • limit N 查询数据库中前n条数据
  • limit M，N，从第M条数据开始，查询N条数据，注意没有括号
select device_id from user_profile limit 2

emp表中的limit使用.png

第五题

地址 where or 的条件判断

第六题

地址 in 的使用

select device_id, gender,age,university,gpa from user_profile where university 
in
(select university from user_profile where  university='山东大学' or university='北京大学' or university='复旦大学')

第七题

地址模糊查询 （别忘记加where）

select device_id,age,university from user_profile where  university like "%北京%"

4、盲点

  1. 书库中有!=的使用，之前好像没怎么用过比较模糊

    select device_id, gender,age,university from user_profile where university!='复旦大学'

  2. 判断字段不为空 （ is not null） 注意要加is

    select device_id, gender,age,university from user_profile where age is not NULL

  3. where 不经可以加条件判断，而且还可以在后面添加and 进行多条件判断，还可以加or 进行判断

    select device_id, gender,age,university,gpa from user_profile where university='北京大学'
or  gpa>3.7

  4. 模糊查询 要添加where进行条件判断

avatar
文章
阅读
粉丝