UPDATE 数据更新
DELETE 数据删除
SELECT 数据查询
单表查询
多表查询
- 在 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 |
-------- -----------