回顾MySQL的CRUD操作

TtuoSYy7

于 2022-10-24 22:40:56 发布

阅读量186

点赞数

分类专栏：数据库文章标签： 1024程序员节

本文链接：https://blog.csdn.net/weixin_46273248/article/details/106965611

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在这之前先回顾一些简单概念：

DB:
DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）

DBMS:
DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…）

SQL:
结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成）
----
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
DBMS -(执行)-> SQL -(操作)-> DB

SQL语句分类

SQL语句	作用
DQL（数据查询语言）	查询语句，凡是select语句都是DQL
DML（数据操作语言）	insert delete update，对表当中的数据进行增删改
DDL（数据定义语言）	create drop alter，对表结构的增删改
TCL（事务控制语言）	commit提交事务，rollback回滚事务(TCL中的T是Transaction)
DCL（数据控制语言）	grant授权、revoke撤销权限等

常用命令：

select database(); 查看当前使用的是哪个数据库
select version(); 查看mysql的版本号
\c 命令，结束一条语句
exit 命令，退出mysql
查看创建表的语句：
show create table emp;

提示：
1、任何一条sql语句以 “;” 结尾
2、sql语句不区分大小写

先来看看本次例子所用表格数据及结构：

分别有：dept部门表、emp员工表、salgrade薪资等级表

它们的表结构：
dept部门表结构
emp员工表结构
salgrade薪资等级表结构
表中数据：
在这里插入图片描述

在这里插入图片描述

一、查询语句（DQL）

1. 简单的查询语句：

语法格式：
select 字段名1,字段名2,字段名3,… from 表名;

查询员工的年薪？（字段可以参与数学运算。）

select ename,sal * 12 from emp;

在这里插入图片描述
给查询结果的列重命名？

select ename,sal * 12 as yearsal from emp;

（as关键字可以省略）

别名起中文？

select ename,sal * 12 as '年薪' from emp;

查询所有字段？

select * from emp;

去重查询？

select distinct job from emp; // distinct关键字去除重复记录

记住：distinct只能出现在所有字段的最前面。

union

select ename from emp
union
select dname from dept;

limit
语法机制：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个

取出工资前5名的员工（思路：降序取前5个）

select ename,sal from emp order by sal desc limit 0, 5;

select ename,sal from emp order by sal desc limit 5;

2. 条件查询:

语法格式：

    select
        字段,字段...
    from
        表名
    where
        条件;

执行顺序：先from，然后where，最后select

查询工资等于5000的员工姓名？

select ename from emp where sal = 5000;

查询SMITH的工资？（字符串的使用）

select sal from emp where ename = 'SMITH'; // 字符串使用单引号括起来

找出工资在1100和3000之间的员工，包括1100和3000？（between…and…的使用）

select ename,sal from emp where sal >= 1100 and sal <= 3000;
// or
select ename,sal from emp where sal between 1100 and 3000; // between...and...是闭区间 [1100 ~ 3000]

找出工作岗位是MANAGER或SALESMAN的员工？（or的使用）

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

in等同于or：

select ename,job from emp where job in('SALESMAN', 'MANAGER');

not in: 不在这几个值当中

select ename,job from emp where sal not in(800, 5000);

in后面的值不是区间，是具体的值

**找出薪资大于1000的并且部门编号是20或30部门的员工 **and和or联合起来用

select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30); // 正确的。

3. 模糊查询like：

在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_

符号	含义
%	任意多个字符
_	任意1个字符

找出名字当中含有O的？

select ename from emp where ename like '%O%';

找出名字中第二个字母是A的？

select ename from emp where ename like '_A%';

找出名字中有下划线的？

select name from t_user where name like '%\_%';

找出名字中最后一个字母是T的？

select ename from emp where ename like '%T';

4. 排序（升序、降序）order by：

（order by是最后执行的）

按照工资升序，找出员工名和薪资？

select
      ename,sal
from
      emp
order by
      sal;

注意：默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。

select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序

按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc , ename asc;

注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列

select
      ename,job,sal
from
      emp
where
      job = 'SALESMAN'
order by
      sal desc;

5. 分组函数：

函数	作用
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

找出工资总和？

select sum(sal) from emp;

找出最高工资？

select max(sal) from emp;

找出最低工资？

select min(sal) from emp;

找出平均工资？

select avg(sal) from emp;

找出总人数？

select count(*) from emp;
select count(ename) from emp;

找出工资高于平均工资的员工？

select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function

ERROR 1111 (HY000): Invalid use of group function
无效的使用了分组函数

原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。group by是在where执行之后才会执行的。

count()和count(具体的某个字段)，他们有什么区别？
count():不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。

6. group by 和 having：

group by ：按照某个字段或者某些字段进行分组
having : having是对分组之后的数据进行再次过滤

找出每个工作岗位的最高薪资？

select max(sal),job from emp group by job;

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。

记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

每个工作岗位的平均薪资？

select job,avg(sal) from emp group by job;

找出每个部门不同工作岗位的最高薪资？

 select
       deptno,job,max(sal)
from
       emp
group by
        deptno,job;

找出每个部门的最高薪资，要求显示薪资大于2900的数据？
第一步：找出每个部门的最高薪资

select max(sal),deptno from emp group by deptno;

第二步：找出薪资大于2900

select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。

select max(sal),deptno from emp where sal > 2900 group by deptno;  // 效率较高，建议能够使用where过滤的尽量使用where。

找出每个部门的平均薪资，要求显示薪资大于2000的数据?
第一步：找出每个部门的平均薪资

select deptno,avg(sal) from emp group by deptno;

第二步：要求显示薪资大于2000的数据

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

7. 连接查询

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

内连接之等值连接：最大特点是：条件是等量关系
查询每个员工的部门名称，要求显示员工名和部门名？

select
        e.ename,d.dname
from
        emp e
inner join  // inner可以省略的，带着inner目的是可读性好一些
        dept d
on
        e.deptno = d.deptno;

内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系
找出每个员工的工资等级，要求显示员工名、工资、工资等级？

select
    e.ename,e.sal,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal;

自连接：最大的特点是：一张表看做两张表。自己连接自己
找出每个员工的上级领导，要求显示员工名和对应的领导名？

select
    a.ename as '员工名',b.ename as '领导名'
from
    emp a
inner join
    emp b
on
    a.mgr = b.empno;

外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
找出每个员工的上级领导？

select
    a.ename '员工', b.ename '领导'
from
    emp a
left join  //左外连接
    emp b
on
    a.mgr = b.empno;

select
    a.ename '员工', b.ename '领导'
from
    emp b
right join  // 右外连接
    emp a
on
    a.mgr = b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来

8. 子查询

where子句中使用子查询
找出高于平均薪资的员工信息？

select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询
找出每个部门平均薪水的等级？

select
    t.*,s.grade
from
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;

在select后面嵌套子查询
找出每个员工所在的部门名称，要求显示员工名和部门名？

select
    e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
    emp e;

总结一个完整的DQL语句的执行步骤：

select        5
        ...
from            1
        ...        
where            2
        ...    
group by        3
        ...
having        4
        ...
order by        6
        ...
limit            7
        ...;