数据库：DQL+DDL+DML

Lens Bit

已于 2025-04-13 00:14:25 修改

阅读量481

点赞数 5

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql

于 2025-04-12 23:56:29 首次发布

本文链接：https://blog.csdn.net/qq_41073619/article/details/147183491

MySQL 专栏收录该内容

5 篇文章

订阅专栏

DISTINCT：去除重复记录

SELECT DISTINCT job
FROM emp
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

#distinct只能出现在所有字段的最前方
#表明job和deptno联合地去除重复
select distinct job, deptno 
from emp
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

select count(job) 
from emp;
+------------+
| count(job) |
+------------+
|         14 |
+------------+

select count(distinct job) 
from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

一、连接查询

1、表连接方式：

1.1 内连接：

（1）等值连接

#92语法
select e.ename, d.dname
from emp e , dept d
where e.deptno = d.deptno

#99语法
#连接条件与WHERE筛选条件做到了分离
select e.ename, d.dname
from emp e
     join 
     dept d
on e.deptno = d.deptno
#还能添加 where 条件

（2）非等值连接

#找出员工的姓名、工资、工资等级

select e.ename, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| EMOK   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

（3）自连接

#找出员工的名字，和其对应的领导名字

select e1.ename 员工, e2.ename 领导
from emp e1 join emp e2
where e1.mgr = e2.empno
+--------+-------+
| 员工   | 领导  |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| EMOK   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
#共13条记录，但有14个员工，因为其中一位员工没有领导

1.2 外连接：

在外连接中，两张表产生了主次关系。

外连接的查询结果条数>=内连接的结果条数

（1）左外连接（左边的表为主表）

select e1.ename em ,e2.ename leader 
from emp e1 left join emp e2 
on e1.mgr = e2.empno
+--------+--------+
| em     | leader |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| EMOK   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

（2）右外连接（右边的表为主表）

1.3 全连接(不常用)

1.4 三张表、四张表连接

#找出每个员工的姓名、部门、薪资、薪资等级
#需要三张表连接emp、dept、salgrade

select e.ename, d.dname, e.sal, s.grade
from emp e 
left join 
dept d
on e.deptno = d.deptno

left join 
salgrade s
on e.sal between s.losal and s.hisal

+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| EMOK   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+

2、现象：两张表连接查询时，没有任何条件的限制，会有什么情况？

笛卡尔积之后，记录数量为M*N（第一张表M个记录，第二张表N个记录），过于冗余。

解决方法：

连接之前加条件，防止过多的记录被连接，但是匹配次数并没有减少。

二、子查询

1、from子句中的子查询

将子查询的结果作为一张临时表

#找出每个岗位的平均工资
select job, avg(sal)
from emp
group by job
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

#找出每个岗位的平均工资的薪资等级
select 
  t.job, s.grade
from
  (
    select job, avg(sal) as avgsal 
    from emp 
    group by job
  ) t
join 
  salgrade s
on 
  t.avgsal between s.losal and s.hisal
+-----------+-------+
| job       | grade |
+-----------+-------+
| CLERK     |     1 |
| SALESMAN  |     2 |
| MANAGER   |     4 |
| ANALYST   |     4 |
| PRESIDENT |     5 |
+-----------+-------+

2、select子句中的子查询

对于select后面的子查询，这个子查询一次只能返回唯一一条结构。

#查出员工名字和其部门名
select e.ename, 
      (select d.dname from dept d where d.deptno=e.deptno) as dname
from emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| EMOK   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
+--------+------------+

三、union合并查询结果集

对于表连接来说，每次匹配的次数满足笛卡尔积

但是union可以不用做匹配，只是完成两个结果集的拼接

union性能的优异之处：

匹配次数会减少

a:x条记录；

b:y条记录；

c:z条记录。

a连接b再连接c，匹配x*y*z次；

a连接b，a连接c，再union，匹配xy+xz次。

select ename, job  from emp  where job ='manager' 
union
select ename, job  from emp  where job ='salesman'
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| EMOK   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

四、limit：将查询结果的一部分取出

将查询结果的一部分取出，通常用于分页查询当中。

分页的作用是提高观看体验，可以一页一页地看，比如百度的查询结果就是分页的。

#显示前5条
select ename,sal
from emp
order by sal desc
limit 5;

#从第0个开始，共显示6条
select ename,sal
from emp
order by sal desc
limit 0, 6 ;

limit在order by之后执行！

limit 起始页码-1, 记录条数

limit (pageNo-1)*pageSize , pageSize;

五、DDL：定义语言

1、表的创建

create table 表名(
  字段名1 数据类型,
  字段名2 数据类型,
  字段名3 数据类型,
  ...
);

create table t_student(
  id varchar(255),
  name varchar(255),
  birth date,
  ...
);

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(255) | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| birth | date         | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

#数据类型
char:定长字符串
varchar:变长字符串
int:java int
bigint:java long
double:双精度浮点型
float:单精度浮点型
date:短日期类型
datetime:长日期类型
blob：二进制大对象，视频音频图片等等（Binary Large Object）
clob:字符大对象，比如一篇文章，超过255个字符的（Character Large Object）

2、表的删除

//表存在则删除，不存在则报错
drop table t_student;

//表存在则删除，不存在也不报错
drop table if exists t_student;

六、DML：操纵语言

1、插入数据

insert into t_student(字段1, 字段2, 字段3)
values(数据1, 数据2, 数据3);

#可以插入多条记录
insert into t_student(字段1, 字段2, 字段3)
values(数据1, 数据2, 数据3),
(数据1, 数据2, 数据3),
(数据1, 数据2, 数据3);

使用数据处理函数

Mysql中的日期格式

%Y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

//str_to_date(): varchar转换date类型
insert into
t_student(id, name, birth)
values(101, 'zhangsan', str_to_date('01-10-1999','%d-%m-%Y'))//1999年10月1日
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
| 101  | zhangsan | 1999-10-01 |
+------+----------+------------+


//如果写的时间字符串的格式是%Y-%m-%d，str_to_date()函数可以省略
insert into
t_student(id, name, birth)
values(102, 'vsn', '1996-11-02')
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
| 101  | zhangsan | 1999-10-01 |
| 102  | vsn      | 1996-11-02 |
+------+----------+------------+

//now()函数获取系统现在时间
//为datetime类型：长时间类型
insert into t_user(id,name,birth,create_time) 
values(2,'lili','1999-01-25',now());

+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    2 | lili | 1999-01-25 | 2024-06-05 00:45:50 |
+------+------+------------+---------------------+

2、修改数据

update t_user 
set name='jack', birth='2000-01-02' 
where id=1;

+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    2 | lili | 1999-01-25 | 2024-06-05 00:45:50 |
|    1 | jack | 2000-01-02 | 2024-06-05 00:55:56 |
+------+------+------------+---------------------+

#不加where条件
update t_user 
set name='jack', birth='2000-01-02';

#将作用于全部记录
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    2 | jack | 2000-01-02 | 2024-06-05 00:45:50 |
|    1 | jack | 2000-01-02 | 2024-06-05 00:55:56 |
+------+------+------------+---------------------+

3、删除数据

delete from t_user 
where id=2;

+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | jack | 2000-01-02 | 2024-06-05 00:55:56 |
+------+------+------------+---------------------+

#不加where条件，将作用于全部记录，表为空