Mysql增删改查(DML、DQL)

INSERT 数据插入
UPDATE 数据更新
DELETE 数据删除
SELECT 数据查询
单表查询
多表查询toc

  • Mysql 管理软件中, 可以通过 SQL 语句中的 DML 语言来实现数据的操作:
    • INSERT 数据插入
    • UPDATE 数据更新
    • DELETE 数据删除
  • 同时可以通过 SQL 语句中的 DQL 语言来实现数据的查询:
    • SELECT 数据查询

准备操作环境数据表

mysql> create database test;
mysql> use test;
mysql> create table t1(id int,
        name varchar(10),
        sex enum('man','gril'),
        age int
    );
## 查看表结构
mysql> desc t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            | YES  |     | NULL    |       |
| name  | varchar(10)        | YES  |     | NULL    |       |
| sex   | enum('man','gril') | YES  |     | NULL    |       |
| age   | int(11)            | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+

数据插入或更改的内容必须在字段对应的数据类型范围内

INSERT 数据插入

语法: INSERT INTO 表名 ( 字段1, 字段2......) VALUES ( 值1, 值2.....);;

## 插入一条数据,INSERT INTO 表名 ( 字段1, 字段2......) VALUES ( 值1, 值2.....);
mysql> insert into t1(id,name,sex,age) values (1,"zhangsan","man",18);
## 完整插入可以省略字段,INSERT INTO 表名 VALUES ( 值1, 值2....);
mysql> insert into t1 values(2,"lisi","man",10);
## INTO 也可以省略掉, INSERT 表名 VALUES ( 值1, 值2....);
mysql> insert t1 values(2,"wangwu","gril",22);
## 指定字段插入 , INSERT INTO 表名 ( 字段2, 字段3…) VALUES ( 值2, 值3…);
mysql> insert into t1(name,sex,age) values ("zhaoliu","man",20);
## 插入多条记录 , INSERT INTO 表名 VALUES ( 值1, 值2....),( 值1, 值2....);
mysql> insert into t1 values
        (3,"xiaoming","man",16),
        (4,"xiaohong","gril",19),
        (5,"dasha","man",25);
## 查询 t1 表的所有数据
mysql> select * from t1;
+------+----------+------+------+
| id   | name     | sex  | age  |
+------+----------+------+------+
|    1 | zhangsan | man  |   18 |
|    2 | lisi     | man  |   10 |
|    2 | wangwu   | gril |   22 |
| NULL | zhaoliu  | man  |   20 |
|    3 | xiaoming | man  |   16 |
|    4 | xiaohong | gril |   19 |
|    5 | dasha    | man  |   25 |
+------+----------+------+------+

UPDATE 数据更新

语法: UPDATE 表名 SET 字段=值 WHERE 条件;
添加对应的 WHERE 条件,精准修改,不添加默认修改全部的

## 将 t1 表中,名字叫 zhangsan 的改成 ersha
mysql> update t1 set name="ersha" where name="zhangsan";
## 将 t1 表中,性别为 gril 的年龄改为 18
mysql> update t1 set age=18 where sex="gril";
## 查询 t1 表的所有数据
mysql> select * from t1;
+------+----------+------+------+
| id   | name     | sex  | age  |
+------+----------+------+------+
|    1 | ersha    | man  |   18 |
|    2 | lisi     | man  |   10 |
|    2 | wangwu   | gril |   18 |
| NULL | zhaoliu  | man  |   20 |
|    3 | xiaoming | man  |   16 |
|    4 | xiaohong | gril |   18 |
|    5 | dasha    | man  |   25 |
+------+----------+------+------+

DELETE 数据删除

语法: DELETE FROM 表名 WHERE 条件;
添加对应的 WHERE 条件,精准修改,不添加默认删除全部的

## 将 t1 表中,删除名字叫 dasha 的数据
mysql> delete from t1 where name="dasha";
## 将 t1 表中,删除 sex 为 gril 的数据
mysql> delete from t1 where sex="gril";
## 查询 t1 表的所有数据
mysql> select * from t1;
+------+----------+------+------+
| id   | name     | sex  | age  |
+------+----------+------+------+
|    1 | ersha    | man  |   18 |
|    2 | lisi     | man  |   10 |
| NULL | zhaoliu  | man  |   20 |
|    3 | xiaoming | man  |   16 |
+------+----------+------+------+
## 清空表数据(游戏里删档就是用的这个)
mysql> truncate t1;

SELECT 数据查询

语法: SELECT 字段1,字段2,..... FROM 表名.....;

单表查询

在学习查询前, 需要定义好对应数据进行查询

编号 id int
姓名 name varchar(30)
性别 sex enum('man','gril')
日期 time date
职位 post varchar(50)
职责 job varchar(100)
薪水 salary double(15,2)
部门编号 dep_id int

## 创建表
mysql> create table t2(
        id int primary key AUTO_INCREMENT not null,
        name varchar(30) not null,
        sex enum('man','gril') default 'man' not null,
        time date not null,
        post varchar(50) not null,
        job varchar(100),
        salary double(15,2) not null,
        dep_id int 
    );
## 插入数据
mysql> insert into t2(name,sex,time,post,job,salary,dep_id) values
        ('jack','man','20180202','it','teach',5000,100),
        ('tom','man','20180203','it','teach',5500,100),
        ('robin','man','20180202','it','teach',8000,100),
        ('alice','gril','20180202','it','teach',7200,100),
        ('lisi','man','20180202','hr','hrcc',600,101),
        ('harry','man','20180202','hr', NULL,6000,101),
        ('trf','gril','20180206','sale','salecc',20000,102),
        ('test','gril','20180205','sale','salecc',2200,102),
        ('dog','man','20180205','sale', NULL,2200,102),
        ('alex','man','20180205','sale','',2200,102);
## 查看表结构
mysql> desc t2;
+--------+--------------------+------+-----+---------+----------------+
| Field  | Type               | Null | Key | Default | Extra          |
+--------+--------------------+------+-----+---------+----------------+
| id     | int(11)            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)        | NO   |     | NULL    |                |
| sex    | enum('man','gril') | NO   |     | man     |                |
| time   | date               | NO   |     | NULL    |                |
| post   | varchar(50)        | NO   |     | NULL    |                |
| job    | varchar(100)       | YES  |     | NULL    |                |
| salary | double(15,2)       | NO   |     | NULL    |                |
| dep_id | int(11)            | YES  |     | NULL    |                |
+--------+--------------------+------+-----+---------+----------------+

简单数据查询

## 查询所有数据
mysql> select * from t2;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  1 | jack  | man  | 2018-02-02 | it   | teach  |  5000.00 |    100 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  5 | lisi  | man  | 2018-02-02 | hr   | hrcc   |   600.00 |    101 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
|  8 | test  | gril | 2018-02-05 | sale | salecc |  2200.00 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale | NULL   |  2200.00 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale |        |  2200.00 |    102 |
+----+-------+------+------------+------+--------+----------+--------+
##  指定字段查询
mysql> select name,salary,dep_id from t2;
+-------+----------+--------+
| name  | salary   | dep_id |
+-------+----------+--------+
| jack  |  5000.00 |    100 |
| tom   |  5500.00 |    100 |
| robin |  8000.00 |    100 |
| alice |  7200.00 |    100 |
| lisi  |   600.00 |    101 |
| harry |  6000.00 |    101 |
| trf   | 20000.00 |    102 |
| test  |  2200.00 |    102 |
| dog   |  2200.00 |    102 |
| alex  |  2200.00 |    102 |
+-------+----------+--------+
## 去重查询字段 distinct
mysql> select post from t2;
+------+
| post |
+------+
| it   |
| it   |
| it   |
| it   |
| hr   |
| hr   |
| sale |
| sale |
| sale |
| sale |
+------+

mysql> select distinct post from t2;
+------+
| post |
+------+
| it   |
| hr   |
| sale |
+------+
## 通过运算查询 , 计算每个人的年薪
mysql> select name,salary,salary*12 from t2;
+-------+----------+-----------+
| name  | salary   | salary*12 |
+-------+----------+-----------+
| jack  |  5000.00 |  60000.00 |
| tom   |  5500.00 |  66000.00 |
| robin |  8000.00 |  96000.00 |
| alice |  7200.00 |  86400.00 |
| lisi  |   600.00 |   7200.00 |
| harry |  6000.00 |  72000.00 |
| trf   | 20000.00 | 240000.00 |
| test  |  2200.00 |  26400.00 |
| dog   |  2200.00 |  26400.00 |
| alex  |  2200.00 |  26400.00 |
+-------+----------+-----------+
## 计算年薪并定义输出字段信息别名 , AS 可去掉
select name,salary,salary*12 as Annual_salary from t2;
+-------+----------+---------------+
| name  | salary   | Annual_salary |
+-------+----------+---------------+
| jack  |  5000.00 |      60000.00 |
| tom   |  5500.00 |      66000.00 |
| robin |  8000.00 |      96000.00 |
| alice |  7200.00 |      86400.00 |
| lisi  |   600.00 |       7200.00 |
| harry |  6000.00 |      72000.00 |
| trf   | 20000.00 |     240000.00 |
| test  |  2200.00 |      26400.00 |
| dog   |  2200.00 |      26400.00 |
| alex  |  2200.00 |      26400.00 |
+-------+----------+---------------+

条件查询

  • 单条件查询 SELECT 字段1,字段2,..... FROM 表名 WHERE 字段=值;
  • 多条件查询 SELECT 字段1,字段2,..... FROM 表名 WHERE 字段1=值1 (and|or) 字段2=值2;
    • 关键字 BETWEEN AND
    • 关键字 IS NULL
    • 关键字 IN 集合查询
    • 关键字 LIKE 模糊查询
## 单条件查询
mysql> select name,post from t2 where post='hr';
+-------+------+
| name  | post |
+-------+------+
| lisi  | hr   |
| harry | hr   |
+-------+------+
## 多条件查询
mysql> select name,post,salary from t2 where post='hr' and salary >5000;
+-------+------+---------+
| name  | post | salary  |
+-------+------+---------+
| harry | hr   | 6000.00 |
+-------+------+---------+
## 查找薪资范围在8000-20000,使用BETWEEN区间
mysql> select name,salary from t2 where salary between 8000 and 20000;
+-------+----------+
| name  | salary   |
+-------+----------+
| robin |  8000.00 |
| trf   | 20000.00 |
+-------+----------+
## 查找部门为Null或者没有职责的员工
mysql> select name,job from t2 where job is null or job='';
+-------+------+
| name  | job  |
+-------+------+
| harry | NULL |
| dog   | NULL |
| alex  |      |
+-------+------+
## 查找有职责的员工
mysql> select name,job from t2 where job is not null and not job='';
+-------+--------+
| name  | job    |
+-------+--------+
| jack  | teach  |
| tom   | teach  |
| robin | teach  |
| alice | teach  |
| lisi  | hrcc   |
| trf   | salecc |
| test  | salecc |
+-------+--------+
## 模糊查询like, 通配符%(零到多个字符)
mysql> select * from t2 where name like 'al%';
+----+-------+------+------------+------+-------+---------+--------+
| id | name  | sex  | time       | post | job   | salary  | dep_id |
+----+-------+------+------------+------+-------+---------+--------+
|  4 | alice | gril | 2018-02-02 | it   | teach | 7200.00 |    100 |
| 10 | alex  | man  | 2018-02-05 | sale |       | 2200.00 |    102 |
+----+-------+------+------------+------+-------+---------+--------+
## 通配符_(一个字符)
mysql> select * from t2 where name like 'al__';
+----+------+-----+------------+------+------+---------+--------+
| id | name | sex | time       | post | job  | salary  | dep_id |
+----+------+-----+------------+------+------+---------+--------+
| 10 | alex | man | 2018-02-05 | sale |      | 2200.00 |    102 |
+----+------+-----+------------+------+------+---------+--------+

查询排序

低到高排序 ASC (默认)
高到低排序 DESC

##  按单列排序 , 按薪水从低往高排序 , 默认 ASC
mysql> select * from t2 order by salary asc;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  5 | lisi  | man  | 2018-02-02 | hr   | hrcc   |   600.00 |    101 |
|  8 | test  | gril | 2018-02-05 | sale | salecc |  2200.00 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale | NULL   |  2200.00 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale |        |  2200.00 |    102 |
|  1 | jack  | man  | 2018-02-02 | it   | teach  |  5000.00 |    100 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
+----+-------+------+------------+------+--------+----------+--------+
## 按单列排序 , 薪水从高往低排序 , DESC 倒序
mysql> select * from t2 order by salary desc;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
|  1 | jack  | man  | 2018-02-02 | it   | teach  |  5000.00 |    100 |
|  8 | test  | gril | 2018-02-05 | sale | salecc |  2200.00 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale | NULL   |  2200.00 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale |        |  2200.00 |    102 |
|  5 | lisi  | man  | 2018-02-02 | hr   | hrcc   |   600.00 |    101 |
+----+-------+------+------------+------+--------+----------+--------+
## 多列排序 , 先按入职时间,再按薪水排序
mysql> select * from t2 order by time asc, salary asc;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  5 | lisi  | man  | 2018-02-02 | hr   | hrcc   |   600.00 |    101 |
|  1 | jack  | man  | 2018-02-02 | it   | teach  |  5000.00 |    100 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
|  8 | test  | gril | 2018-02-05 | sale | salecc |  2200.00 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale | NULL   |  2200.00 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale |        |  2200.00 |    102 |
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
+----+-------+------+------------+------+--------+----------+--------+
## 多列排序 , 先按职位, 再按薪水排序
mysql> select * from t2 order by post, salary;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  5 | lisi  | man  | 2018-02-02 | hr   | hrcc   |   600.00 |    101 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  1 | jack  | man  | 2018-02-02 | it   | teach  |  5000.00 |    100 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  8 | test  | gril | 2018-02-05 | sale | salecc |  2200.00 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale | NULL   |  2200.00 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale |        |  2200.00 |    102 |
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
+----+-------+------+------------+------+--------+----------+--------+

限制查询的记录数

##  查询薪资最高前 5 名同事
mysql> select * from t2 order by salary desc limit 5;
+----+-------+------+------------+------+--------+----------+--------+
| id | name  | sex  | time       | post | job    | salary   | dep_id |
+----+-------+------+------------+------+--------+----------+--------+
|  7 | trf   | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
|  3 | robin | man  | 2018-02-02 | it   | teach  |  8000.00 |    100 |
|  4 | alice | gril | 2018-02-02 | it   | teach  |  7200.00 |    100 |
|  6 | harry | man  | 2018-02-02 | hr   | NULL   |  6000.00 |    101 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  |  5500.00 |    100 |
+----+-------+------+------------+------+--------+----------+--------+
##  从第 4 条开始 , 并显示 5 条数据
mysql> select * from t2 order by salary desc limit 3,5;
+----+-------+------+------------+------+--------+---------+--------+
| id | name  | sex  | time       | post | job    | salary  | dep_id |
+----+-------+------+------------+------+--------+---------+--------+
|  6 | harry | man  | 2018-02-02 | hr   | NULL   | 6000.00 |    101 |
|  2 | tom   | man  | 2018-02-03 | it   | teach  | 5500.00 |    100 |
|  1 | jack  | man  | 2018-02-02 | it   | teach  | 5000.00 |    100 |
| 10 | alex  | man  | 2018-02-05 | sale |        | 2200.00 |    102 |
|  8 | test  | gril | 2018-02-05 | sale | salecc | 2200.00 |    102 |
+----+-------+------+------------+------+--------+---------+--------+

使用聚合函数查询

COUNT 个数
SUM 求和
AVG 求平均值
MAX 最大值
MIN 最小值

## 统计当前表总共多少条数据
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
## 统计dep_id为101有多少条数据
mysql> select count(*) from t2 where dep_id=101;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
## 薪水最高
mysql> select max(salary) from t2;
+-------------+
| max(salary) |
+-------------+
|    20000.00 |
+-------------+
## 薪水最低
mysql> select min(salary) from t2;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
## 平均薪水
mysql> select avg(salary) from t2;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
## 总共发放多少薪水
mysql> select sum(salary) from t2;
+-------------+
| sum(salary) |
+-------------+
|    58900.00 |
+-------------+
## hr部门发放多少薪水
mysql> select sum(salary) from t2 where post='hr';
+-------------+
| sum(salary) |
+-------------+
|     6600.00 |
+-------------+
## 哪个部门哪个人薪水最高
mysql> select * from t2 where salary=(select max(salary) from t2);
+----+------+------+------------+------+--------+----------+--------+
| id | name | sex  | time       | post | job    | salary   | dep_id |
+----+------+------+------------+------+--------+----------+--------+
|  7 | trf  | gril | 2018-02-06 | sale | salecc | 20000.00 |    102 |
+----+------+------+------------+------+--------+----------+--------+

分组查询

## ROUP BY 和 GROUP_CONCAT()函数一起使用
mysql> select post,group_concat(name) from t2 group by post;
+------+----------------------+
| post | group_concat(name)   |
+------+----------------------+
| hr   | lisi,harry           |
| it   | jack,tom,robin,alice |
| sale | trf,test,dog,alex    |
+------+----------------------+
## GROUP BY 和集合函数一起使用
mysql> select post,sum(salary) from t2 group by post;
+------+-------------+
| post | sum(salary) |
+------+-------------+
| it   |    25700.00 |
| hr   |     6600.00 |
| sale |    26600.00 |
+------+-------------+

使用正则表达式查询

## 查询名字以 ali 开头
mysql> select * from t2 where name regexp '^ali';
+----+-------+------+------------+------+-------+---------+--------+
| id | name  | sex  | time       | post | job   | salary  | dep_id |
+----+-------+------+------------+------+-------+---------+--------+
|  4 | alice | gril | 2018-02-02 | it   | teach | 7200.00 |    100 |
+----+-------+------+------------+------+-------+---------+--------+
## 查询名字以 bin 结尾
mysql> select * from t2 where name regexp 'bin$';
+----+-------+-----+------------+------+-------+---------+--------+
| id | name  | sex | time       | post | job   | salary  | dep_id |
+----+-------+-----+------------+------+-------+---------+--------+
|  3 | robin | man | 2018-02-02 | it   | teach | 8000.00 |    100 |
+----+-------+-----+------------+------+-------+---------+--------+

多表查询

多表连接查询
复合条件连接查询
子查询

准备3张数据表

## 准备表 1
mysql> create table t3(
        id int auto_increment primary key not null,
        name varchar(50),
        age int,
        dep_id int
    );
## 为表 1 插入数据
mysql> insert into t3(name,age,dep_id) values
        ('lisi',18,200),
        ('tom',26,201),
        ('jack',30,201),
        ('alice',24,202),
        ('robin',40,'200'),
        ('natasha',28,204);
## 查看表 1 数据
mysql> select * from t3;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | lisi    |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
## 准备表 2
mysql> create table t4(
        dep_id int,
        dept_name varchar(100)
    );
## 为表 2 插入数据
mysql> insert into t4 values
        (200,'hr'),
        (201,'it'),
        (202,'xs'),
        (203,'cw');
## 查看表 2 数据
mysql> select * from t4;
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    200 | hr        |
|    201 | it        |
|    202 | xs        |
|    203 | cw        |
+--------+-----------+
## 准备表 3
mysql> create table t5(
        id int primary key not null,
        salary double(15,2) not null
    );
## 为表 3 插入数据
mysql> insert into t5 values 
        (1,6000),
        (2,5500),
        (3,5000),
        (4,7200),
        (5,8000);
## 查看表 3 数据
mysql> select * from t5;
+----+---------+
| id | salary  |
+----+---------+
|  1 | 6000.00 |
|  2 | 5500.00 |
|  3 | 5000.00 |
|  4 | 7200.00 |
|  5 | 8000.00 |
+----+---------+

交叉连接, 不使用任何匹配条件

mysql> select t3.name,t3.age,t3.dep_id,t4.dept_name from t3,t4;
+---------+------+--------+-----------+
| name    | age  | dep_id | dept_name |
+---------+------+--------+-----------+
| lisi    |   18 |    200 | hr        |
| lisi    |   18 |    200 | it        |
| lisi    |   18 |    200 | xs        |
| lisi    |   18 |    200 | cw        |
| tom     |   26 |    201 | hr        |
| tom     |   26 |    201 | it        |
| tom     |   26 |    201 | xs        |
| tom     |   26 |    201 | cw        |
| jack    |   30 |    201 | hr        |
| jack    |   30 |    201 | it        |
| jack    |   30 |    201 | xs        |
| jack    |   30 |    201 | cw        |
| alice   |   24 |    202 | hr        |
| alice   |   24 |    202 | it        |
| alice   |   24 |    202 | xs        |
| alice   |   24 |    202 | cw        |
| robin   |   40 |    200 | hr        |
| robin   |   40 |    200 | it        |
| robin   |   40 |    200 | xs        |
| robin   |   40 |    200 | cw        |
| natasha |   28 |    204 | hr        |
| natasha |   28 |    204 | it        |
| natasha |   28 |    204 | xs        |
| natasha |   28 |    204 | cw        |
+---------+------+--------+-----------+

内连接, 只连接匹配的行

## 只找出有部门的员工 , ( 部门表中没有 natasha 所在的部门 )
mysql> select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3,t4 where t3.dep_id=t4.dep_id;
+----+-------+------+--------+-----------+
| id | name  | age  | dep_id | dept_name |
+----+-------+------+--------+-----------+
|  1 | lisi  |   18 |    200 | hr        |
|  2 | tom   |   26 |    201 | it        |
|  3 | jack  |   30 |    201 | it        |
|  4 | alice |   24 |    202 | xs        |
|  5 | robin |   40 |    200 | hr        |
+----+-------+------+--------+-----------+
## 第二种方式
mysql> select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3 inner join t4 on t3.dep_id=t4.dep_id;
+----+-------+------+--------+-----------+
| id | name  | age  | dep_id | dept_name |
+----+-------+------+--------+-----------+
|  1 | lisi  |   18 |    200 | hr        |
|  2 | tom   |   26 |    201 | it        |
|  3 | jack  |   30 |    201 | it        |
|  4 | alice |   24 |    202 | xs        |
|  5 | robin |   40 |    200 | hr        |
+----+-------+------+--------+-----------+

外连接

## 左连接
mysql> select id,name,t4.dept_name from t3 left join t4 on t3.dep_id = t4.dep_id;
+----+---------+-----------+
| id | name    | dept_name |
+----+---------+-----------+
|  1 | lisi    | hr        |
|  5 | robin   | hr        |
|  2 | tom     | it        |
|  3 | jack    | it        |
|  4 | alice   | xs        |
|  6 | natasha | NULL      |
+----+---------+-----------+
## 右连接
mysql> select id,name,t4.dept_name from t3 right join t4 on t3.dep_id = t4.dep_id;
+------+-------+-----------+
| id   | name  | dept_name |
+------+-------+-----------+
|    1 | lisi  | hr        |
|    2 | tom   | it        |
|    3 | jack  | it        |
|    4 | alice | xs        |
|    5 | robin | hr        |
| NULL | NULL  | cw        |
+------+-------+-----------+

符合条件连接查询

## 以内连接的方式查询 t3 和 t4 表 , 找出公司所有部门中年年龄大于 25 岁的员工
mysql> select t3.id,t3.name,t3.age,t4.dept_name from t3,t4 where t3.dep_id = t4.dep_id and age >25;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  2 | tom   |   26 | it        |
|  3 | jack  |   30 | it        |
+----+-------+------+-----------+
## 以内连接的方式查询 t3 和 t4 表,并且以 age 字段降序显示
mysql> select t3.id,t3.name,t3.age,t4.dept_name from t3,t4 where t3.dep_id = t4.dep_id order by age desc;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  3 | jack  |   30 | it        |
|  2 | tom   |   26 | it        |
|  4 | alice |   24 | xs        |
|  1 | lisi  |   18 | hr        |
+----+-------+------+-----------+

三表连接

三表连接不是极限,只要把表之间的关系表达清楚,是可以无限表连接的

## 三表查询所有名字、部门、薪水数据
mysql> select t3.name,t4.dept_name,t5.salary from t3,t4,t5 where t3.id=t5.id and t3.dep_id=t4.dep_id;
+-------+-----------+---------+
| name  | dept_name | salary  |
+-------+-----------+---------+
| lisi  | hr        | 6000.00 |
| tom   | it        | 5500.00 |
| jack  | it        | 5000.00 |
| alice | xs        | 7200.00 |
| robin | hr        | 8000.00 |
+-------+-----------+---------+
## 第二种方式
mysql> select t3.name,t4.dept_name,t5.salary from t4 inner join t3 on t3.dep_id=t4.dep_id inner join t5 on t3.id=t5.id;
+-------+-----------+---------+
| name  | dept_name | salary  |
+-------+-----------+---------+
| lisi  | hr        | 6000.00 |
| tom   | it        | 5500.00 |
| jack  | it        | 5000.00 |
| alice | xs        | 7200.00 |
| robin | hr        | 8000.00 |
+-------+-----------+---------+

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果,可以为外层查询语句提供查询条件。
子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含比较运算符:= 、 !=、> 、<等

## 带 IN 关键字的子查询 查询 t3 表,但 dept_id 必须在 t4 表中出现过
mysql> select * from t3 where dep_id in (select dep_id from t4);
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | lisi  |   18 |    200 |
|  2 | tom   |   26 |    201 |
|  3 | jack  |   30 |    201 |
|  4 | alice |   24 |    202 |
|  5 | robin |   40 |    200 |
+----+-------+------+--------+
# 代表运算符子查询 , 查询年年龄大于等于 25 岁员工所在部门(查询老龄化的部门)
mysql> select dep_id,dept_name from t4 where dep_id in (select distinct dep_id from t3 where age >=25);
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    201 | it        |
|    200 | hr        |
+--------+-----------+
# 子查询 EXISTS 关键字表示存在。在使用 EXISTS 关键字时,内层查询语句不返回查询的记录,而是返回一个真假值。
#Ture 或 False ,当返回 Ture 时,外层查询语句将进行查询;当返回值为 False 时,外层查询语句不进行查询,实际生产当中不常用,反而是 SQL 注入中常用
## t4 表中存在 dep_id=203 , Ture
mysql> select * from t3 where exists (select * from t4 where dep_id=203);
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | lisi    |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
## t4 表中不存在 dep_id=300 , False
mysql> select * from t3 where exists (select * from t4 where dep_id=300);

联合查询(常用于 SQL 注入)

语法:SQL1 UNION SQL2(去重)
SQL1 UNION ALL SQL2(不去重)
表字段最后显示必须相同

mysql> select * from t4 union select * from t5;
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    200 | hr        |
|    201 | it        |
|    202 | xs        |
|    203 | cw        |
|      1 | 6000      |
|      2 | 5500      |
|      3 | 5000      |
|      4 | 7200      |
|      5 | 8000      |
|      6 | 4000      |
+--------+-----------+
上一篇:SQL四种语言:DDL,DML,DCL,TCL


下一篇:DML,DDL,DCL的区别