基础MySQL的语法练习

布满荆棘的人生 2024-03-27 18:33 99阅读 0赞

![在这里插入图片描述][5b4938e77ff34b988de08370dc1c1c6b.png_pic_center]

### 基础MySQL的语法练习 ###

create table DEPT(
      DEPTNO int(2) not null,
      DNAME  VARCHAR(14),
      LOC    VARCHAR(13)
    );
    
    alter table DEPT
      add constraint PK_DEPT primary key (DEPTNO);
    
    create table EMP
    (
      EMPNO    int(4) primary key,
      ENAME    VARCHAR(10),
      JOB      VARCHAR(9),
      MGR      int(4),
      HIREDATE DATE,
      SAL      double(7,2),
      COMM     double(7,2),
      DEPTNO   int(2)
    );
    
    alter table EMP
      add constraint FK_DEPTNO foreign key (DEPTNO)
      references DEPT (DEPTNO);
    
    create table SALGRADE
    (
      GRADE int primary key,
      LOSAL double(7,2),
      HISAL double(7,2)
    );
    
    create table BONUS
    (
      ENAME VARCHAR(10),
      JOB   VARCHAR(9),
      SAL   double(7,2),
      COMM  double(7,2)
    );
    
    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');
    
    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);
    
    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);
    
    select * from dept;
    EXPLAIN select * from dept;
    EXPLAIN select DEPTNO from dept;
    
    select * from emp;
    EXPLAIN select * from emp;
    EXPLAIN select EMPNO from emp;
    
    select * from salgrade;
    EXPLAIN select * from salgrade;
    EXPLAIN select GRADE from salgrade;
    
    
    select * from bonus;
    EXPLAIN select * from bonus;
    EXPLAIN select ENAME from bonus;
    
    -- 对emp表查询
    select * from emp;
    
    EXPLAIN select empno,ename,sal from emp;
    
    EXPLAIN select * from emp where sal > 2000;
    
    select empno,ename,job,mgr from emp where sal > 2000;
    
    -- 起别名：
    select empno 员工编号,ename 姓名,sal 工资 from emp;
    
    select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
    
    select empno as '员工编号',ename as "姓名",sal as 工资 from emp;
    
    -- 算术运算符：
    select empno,ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;
    
    select empno,ename,sal,comm,sal+comm from emp;
    
    -- 去重操作
    select job from emp;
    
    select distinct job from emp;
    
    select job,deptno from emp;
    
    select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重
    
    -- 排序：
    select * from emp order by sal; -- 默认情况下是按照升序排列的
    
    select * from emp order by sal asc; -- asc 升序，可以默认不写
    
    select * from emp order by sal desc; -- desc 降序
    
    select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列
    
    -- 查看emp表：
    select * from emp;
    
    -- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：
    -- where 子句 + 关系运算符
    select * from emp where deptno = 10;
    
    select * from emp where deptno > 10;
    
    select * from emp where deptno >= 10;
    
    select * from emp where deptno < 10;
    
    select * from emp where deptno <= 10;
    
    select * from emp where deptno <> 10;
    
    select * from emp where deptno != 10;
    
    select * from emp where job = 'CLERK';
    
    select * from emp where job = 'clerk';
    
    select * from emp where binary job = 'clerk'; -- binary区分大小写
    
    select * from emp where hiredate < '1981-12-25';
    
    -- where 子句 + 逻辑运算符：and
    select * from emp where sal > 1500 and sal < 3000;  -- (1500,3000)
    
    select * from emp where sal > 1500 && sal < 3000;
    
    select * from emp where sal > 1500 and sal < 3000 order by sal;
    
    select * from emp where sal between 1500 and 3000; -- [1500,3000]
    
    -- where 子句 + 逻辑运算符：or
    select * from emp where deptno = 10 or deptno = 20;
    select * from emp where deptno = 10 || deptno = 20;
    select * from emp where deptno in (10,20);
    select * from emp where job in ('MANAGER','CLERK','ANALYST');
    
    -- where子句 + 模糊查询：
    -- 查询名字中带A的员工  -- %代表任意多个字符 0,1,2，.....
    select * from emp where ename like '%A%' ;
    
    -- -任意一个字符
    select * from emp where ename like '__A%';
    
    -- 关于null的判断：
    select * from emp where comm is null;
    select * from emp where comm is not null;
    
    -- 小括号的使用  ：因为不同的运算符的优先级别不同，加括号为了可读性
    select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or
    
    select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);
    
    select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;
    
    -- 函数举例：
    select empno,ename,lower(ename),upper(ename),sal from emp;
    
    -- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
    -- 函数作用：为了提高select的能力
    -- 注意：函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。
    select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
    -- 函数的分类：
    -- lower(ename),upper(ename) ：改变每一条结果，每一条数据对应一条结果  -- 单行函数
    -- max(sal),min(sal),count(sal),sum(sal),avg(sal):多条数据，最终展示一个结果  -- 多行函数
    
    -- 单行函数包含：
    -- 1.字符串函数
    -- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
    select ename,length(ename),substring(ename,2,3) from emp;
    
    -- 2.数值函数
    select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表
    
    select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
    
    select ceil(sal) from emp;
    
    select 10/3,10%3,mod(10,3);
    
    -- 3.日期与时间函数
    select * from emp;
    
    select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
    
    select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
    
    insert into emp values (999,'lili','SALASMAN',7698,now(),1000,null,30);
    
    -- 4.流程函数
    -- if相关
    select empno,ename,sal,if(sal>=2500,'高薪','低薪') as '薪资等级' from emp; -- if-else 双分支结构
    
    select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支
    
    select nullif(1,1),nullif(1,2) from dual; --  如果value1等于value2，则返回null，否则返回value1
    
    -- case相关：
    -- case等值判断
    select empno,ename,job,
    case job
     when 'CLERK' then '店员'
     when 'SALESMAN'  then '销售'
     when 'MANAGER' then '经理'
     else '其他'
    end '岗位',
    sal from emp;
    
    -- case区间判断:
    select empno,ename,sal,
    case
     when sal<=1000 then 'A'
     when sal<=2000 then 'B'
     when sal<=3000 then 'C'
     else 'D'
    end '工资等级',
    deptno from emp;
    
    -- 6.其他函数
    select database(),user(),version() from dual;
    
    -- 多行函数：
    select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
    
    select * from emp;
    
    -- 多行函数自动忽略null值
    select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
    
    -- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效
    select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
    
    -- count --计数
    -- 统计表的记录数：方式1：
    select * from emp;
    select count(ename) from emp;
    select count(*) from emp;
    
    -- 统计表的记录数：方式2
    select 1 from dual;
    select 1 from emp;
    select count(1) from emp;
    
    select * from emp;
    
    -- 统计各个部门的平均工资
    select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用
    
    select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
    
    select deptno,avg(sal) from emp group by deptno order by deptno desc;
    
    -- 统计各个岗位的平均工资
    select job,avg(sal) from emp group by job;
    select job,lower(job),avg(sal) from emp group by job;
    
    
    -- 统计各个部门的平均工资 ,只显示平均工资2000以上的  - 分组以后进行二次筛选 having
    select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
    
    select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
    
    select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
    
    -- 统计各个岗位的平均工资,除了MANAGER
    -- 方法1：
    select job,avg(sal) from emp where job != 'MANAGER' group by job;
    
    -- 方法2：
    select job,avg(sal) from emp group by job having job != 'MANAGER'
    
    # 单表查询总结
    # 【1】select语句总结
    # select column, group_function(column)
    # from table
    # [where condition]
    # [group by group_by_expression]
    # [having group_condition]
    # [order by column];
    # 注意：顺序固定，不可以改变顺序
    
    # 【2】select语句的执行顺序
    # from--where -- group by– select-having-order by
    
    -- 单表查询练习：
    -- 列出工资最小值小于2000的职位
    select job,min(sal)
    from emp
    group by job
    having min(sal) < 2000 ;
    
    
    -- 列出平均工资大于1200元的部门和工作搭配组合
    select deptno,job,avg(sal)
    from emp
    group by deptno,job
    having avg(sal) > 1200
    order by deptno;
    
    -- 统计[人数小于4的]部门的平均工资。
    select deptno,count(1),avg(sal)
    from emp
    group by deptno
    having count(1) < 4;
    
    -- 统计各部门的最高工资，排除最高工资小于3000的部门。
    select deptno,max(sal)
    from emp
    group by deptno
    having max(sal) < 3000;
    
    -- 查询员工的编号，姓名，部门编号：
    select * from emp;
    select empno,ename,deptno from emp;
    
    -- 查询员工的编号，姓名，部门编号,部门名称：
    select * from emp; -- 14条记录
    select * from dept; -- 4条记录
    
    -- 多表查询 ：
    -- 交叉连接：cross join
    select *
    from emp
    cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义
    
    select *
    from emp
    join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以
    
    -- 自然连接：natural join
    -- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
    select *
    from emp
    natural join dept;
    
    select empno,ename,sal,dname,loc
    from emp
    natural join dept;
    
    -- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低
    -- 解决： 指定表名：
    select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
    from emp
    natural join dept;
    
    -- 缺点：表名太长
    -- 解决：表起别名
    select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
    from emp e
    natural join dept d;
    
    -- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
    -- 解决： 内连接 - using子句：
    select *
    from emp e
    inner join dept d -- inner可以不写
    using(deptno) -- 这里不能写natural join了 ,这里是内连接
    
    -- using缺点：关联的字段，必须是同名的
    -- 解决： 内连接 - on子句：
    select *
    from emp e
    inner join dept d
    on (e.deptno = d.deptno);
    
    -- 多表连接查询的类型： 1.交叉连接  cross join  2. 自然连接  natural join
    -- 3. 内连接 - using子句   4.内连接 - on子句
    -- 综合看：内连接 - on子句
    
    select *
    from emp e
    inner join dept d
    on (e.deptno = d.deptno)
    where sal > 3500;
    
    -- 条件：
    -- 1.筛选条件  where  having
    -- 2.连接条件 on,using,natural
    -- SQL99语法 ：筛选条件和连接条件是分开的
    
    -- 外连接查询
    
    
    -- inner join - on子句： 显示的是所有匹配的信息
    select *
    from emp e
    inner join dept d
    on e.deptno = d.deptno;
    
    select * from emp;
    select * from dept;
    -- 问题：
    -- 1.40号部分没有员工，没有显示在查询结果中
    -- 2.员工scott没有部门，没有显示在查询结果中
    
    -- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据
    
    -- 左外连接： left outer join   -- 左面的那个表的信息，即使不匹配也可以查看出效果
    select *
    from emp e
    left outer join dept d
    on e.deptno = d.deptno;
    
    -- 右外连接： right outer join   -- 右面的那个表的信息，即使不匹配也可以查看出效果
    select *
    from emp e
    right outer join dept d
    on e.deptno = d.deptno;
    
    -- 全外连接  full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据
    -- scott ，40号部门都可以看到
    # select *
    # from emp e
    # full outer join dept d
    # on e.deptno = d.deptno;
    
    
    -- 解决mysql中不支持全外连接的问题：
    
    select *
    from emp e
    left outer join dept d
    on e.deptno = d.deptno
    union -- 并集 去重 效率低
    select *
    from emp e
    right outer join dept d
    on e.deptno = d.deptno;
    
    select *
    from emp e
    left outer join dept d
    on e.deptno = d.deptno
    union all-- 并集 不去重 效率高
    select *
    from emp e
    right outer join dept d
    on e.deptno = d.deptno;
    
    -- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）
    -- outer可以省略不写
    
    -- 三表连接查询
    
    -- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
    select * from emp;
    select * from dept;
    select * from salgrade;
    
    select e.ename,e.sal,e.empno,e.deptno,d.dname,s.*
    from emp e
    right outer join dept d
    on e.deptno = d.deptno
    inner join salgrade s
    on e.sal between s.losal and s.hisal
    
    -- 自连接查询
    
    -- 查询员工的编号、姓名、上级编号,上级的姓名
    select * from emp;
    
    select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
    from emp e1
    inner join emp e2
    on e1.mgr = e2.empno;
    
    -- 左外连接：
    select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
    from emp e1
    left outer join emp e2
    on e1.mgr = e2.empno;
    
    -- 92语法：多表查询
    -- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称：
    select e.empno,e.ename,e.sal,e.deptno,d.dname
    from emp e,dept d;
    -- 相当于99语法中的cross join ,出现笛卡尔积，没有意义
    select e.empno,e.ename,e.sal,e.deptno,d.dname
    from emp e,dept d
    where e.deptno = d.deptno;
    -- 相当于99语法中的natural join
    
    
    -- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，查询出工资大于2000的员工
    select e.empno,e.ename,e.sal,e.deptno,d.dname
    from emp e,dept d
    where e.deptno = d.deptno and e.sal > 2000;
    
    -- 查询员工的名字，岗位，上级编号，上级名称（自连接）：
    select e1.ename,e1.job,e1.mgr ,e2.ename
    from emp e1,emp e2
    where e1.mgr = e2.empno;
    
    -- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
    select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade
    from emp e,dept d,salgrade s
    where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal;
    
    -- 总结：
    -- 1.92语法麻烦
    -- 2.92语法中 表的连接条件 和  筛选条件  是放在一起的没有分开
    -- 3.99语法中提供了更多的查询连接类型：cross,natural,inner,outer
    
    
    
    -- 引入子查询：
    -- 查询所有比“CLARK”工资高的员工的信息
    -- 步骤1：“CLARK”工资
    select sal from emp where ename = 'CLARK'; -- 2450
    -- 步骤2：查询所有工资比2450高的员工的信息
    select * from emp where sal > 2450;
    -- 两次命令解决问题 --》效率低 ，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是
    -- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改
    
    -- 将步骤1和步骤2合并 --》子查询：
    select * from emp where sal > (select sal from emp where ename = 'CLARK');
    -- 一个命令解决问题 --》效率高
    
    # 【2】执行顺序：
    # 先执行子查询，再执行外查询；
    # 【3】不相关子查询：
    # 子查询可以独立运行，称为不相关子查询。
    # 【4】不相关子查询分类：
    # 根据子查询的结果行数，可以分为单行子查询和多行子查询。
    
    -- 单行子查询：
    -- 查询工资高于平均工资的雇员名字和工资。
    select ename,sal
    from emp
    where sal > (select avg(sal) from emp);
    
    -- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
    select ename,sal
    from emp
    where deptno = (select deptno from emp where ename = 'CLARK')
          and
          sal < (select sal from emp where ename = 'CLARK');
    
    -- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
    select * 
    from emp
    where job = (select job from emp where ename = 'SCOTT')
          and
          hiredate < (select hiredate from emp where ename = 'SCOTT')

[5b4938e77ff34b988de08370dc1c1c6b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/5be2d4aa0d784b73b36434016c87c48c.png