mysql DML语句基本操作
访问量:1206
创建时间:2021-04-19
插入数据
insert into 表名称 (字段名称，字段名1，字段名2，...字段名n) values (值1，值2，...值n)
向t_user 插入数据
mysql> insert into t_user (user_name,user_birthday,user_gender,user_state,user_height,user_desc) values ('张三','1995-10-01','男',1,171.1, 'this is a desc.');
#简写：插入的值与 表中所有的字段 都对应
mysql> insert into t_user value (2, 'lisi', '1995-10-01', '男', 1, 171.1, 'this is a desc.');
###指定列，省略其他列，被省略的列可以为空或者有默认值
mysql> insert into t_user (user_name,user_state,user_height,user_desc) values ('wangwu',1,172.1, 'th
is is a desc.');
修改数据
update 表名 set 字段1=修改的值1,字段2=修改的值2,...字段n=修改的值n where 修改条件
mysql> update t_user set user_birthday='2000-01-01' where user_id=1;
mysql> update t_user set user_height=180.1,user_state=2 where user_id=2;
mysql> select * from t_user;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 1 | 张三 | 2000-01-01 | 男 | 1 | 171.1 | this is a desc. |
| 2 | lisi | 1995-10-01 | 男 | 2 | 180.1 | this is a desc. |
| 3 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 4 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
mysql> update t_user set user_state=7 where user_id>1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from t_user;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 1 | 张三 | 2000-01-01 | 男 | 1 | 171.1 | this is a desc. |
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
| 3 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
| 4 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
4 rows in set (0.00 sec)
### 修改时，不加条件，所有的行数据都会被改变。
查询语句
select * from tb_name; 不带条件，查询全部数据
select * from tb_name where ; 带条件查询（比较运算 > ,< ,>= ,<= ,!= ,<>, =）
mysql> select * from t_user where user_gender='男';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 1 | 张三 | 2000-01-01 | 男 | 1 | 171.1 | this is a desc. |
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
范围查询
mysql> select * from t_user where user_id>1;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
| 3 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
| 4 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from t_user where user_id>1 and user_id<3;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
1 row in set (0.00 sec)
不等于
mysql> select * from t_user where user_name!='张三';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
| 3 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
| 4 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
逻辑运算符 and 同时满足多个条件 ,or 满足一个条件即可
mysql> insert into t_user (user_name,user_height,user_desc) values ('wangwu',172.1, 'this is a desc.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user (user_name,user_height,user_desc) values ('lisi',180.1, 'this is a desc.');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user where user_state=1 and user_height>180;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
1 row in set (0.04 sec)
mysql> select * from t_user where user_state=1 or user_height>180;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.05 sec)
模糊匹配查询,like 关键字， '_'标识占位符， %表示通配符
mysql> select * from t_user where user_name like 'lisi';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 9 | Lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
###查询lisi 开始的
mysql> select * from t_user where user_name like 'lisi%';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
| 9 | Lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.00 sec)
###查询lisi结尾的
mysql> select * from t_user where user_name like '%lisi';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 9 | Lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
###占位符演示
mysql> select * from t_user where user_name like 'lisi__';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
1 row in set (0.00 sec)
##包含si
mysql> select * from t_user where user_name like '%si%';
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
| 9 | Lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.00 sec)
指定范围集合 IN
mysql> insert into t_user (user_name,user_state,user_height,user_desc) values ('Lisi',2,180.1, 'this
is a desc.');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_user (user_name,user_state,user_height,user_desc) values ('Lisi',3,180.1, 'this is a desc.');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user where user_state in (1,2);
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 9 | Lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 10 | Lisi | NULL | NULL | 2 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
5 rows in set (0.00 sec)
mysql> select * from t_user where user_id in (5,6,7);
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.05 sec)
等同于：UNION ALL是将查询结果合并
select * from t_user where user_id =5
UNION ALL
select * from t_user where user_id =6
UNION ALL
select * from t_user where user_id =7;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.34 sec)
范围查询 between and
mysql> select * from t_user where user_id between 6 and 8;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from t_user where user_id >= 6 and user_id<= 8;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 6 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.01 sec)
删除表中数据的语法
delete from 表名 where 删除条件;
#根据单个条件
mysql> delete from t_user where user_name='张三';
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
| 3 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
| 4 | wangwu | NULL | NULL | 7 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.00 sec)
#同时满足多个条件的删除
mysql> delete from t_user where user_name='wangwu' and user_state=7;
Query OK, 2 rows affected (0.32 sec)
mysql> select * from t_user;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
1 row in set (0.00 sec)
#多个条件满足一个就删除
mysql> insert into t_user (user_name,user_height,user_desc) values ('wangwu',172.1, 'this is a desc.');
Query OK, 1 row affected (0.34 sec)
mysql> select * from t_user;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 2 | lisi | 1995-10-01 | 男 | 7 | 180.1 | this is a desc. |
| 5 | wangwu | NULL | NULL | 1 | 172.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
mysql> delete from t_user where user_state=1 or user_gender='男';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t_user;
Empty set (0.01 sec)
group by 分组查询
配合函数 count(字段) --获取符合条件出现的非null值得次数 ，SUM(字段)获取所有符合条件的数据的总和，AVG(字段)或者平均值
group by 会创建临时表。 使用group by 查询的字段一定是group by后面的字段（可以跟多个字段）
mysql> insert into t_user (user_name,user_gender,user_state,user_height,user_desc) values ('hanmei',
'女',3,180.1, 'this is a desc.');
mysql> insert into t_user (user_name,user_gender,user_state,user_height,user_desc) values ('laoliu', '男',3,180.1, 'this is a desc.');
mysql> select user_gender,count(*) as 数量 from t_user group by user_gender;
+-------------+--------+
| user_gender | 数量 |
+-------------+--------+
| NULL | 6 |
| 女 | 1 |
| 男 | 2 |
+-------------+--------+
3 rows in set (0.00 sec)
#按性别分组的总身高
mysql> select sum(user_height) from t_user group by user_gender;
#按性别分组的平均身高
mysql> select user_gender,avg(user_height) from t_user group by user_gender;
+-------------+------------------+
| user_gender | avg(user_height) |
+-------------+------------------+
| NULL | 178.76667 |
| 女 | 180.10000 |
| 男 | 180.10000 |
+-------------+------------------+
3 rows in set (0.00 sec)
排序 order by
order by asc 升序排列，从小到大
mysql> select * from t_user order by user_id ;
降序排列,从大到小,要放在where条件之后，group by之后，group by在where之后
mysql> select * from t_user where user_id >10 order by user_id desc ;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 15 | laoliu | NULL | 男 | 3 | 184.1 | this is a desc. |
| 14 | laoliu | NULL | 男 | 3 | 180.1 | this is a desc. |
| 13 | hanmei | NULL | 男 | 3 | 180.1 | this is a desc. |
| 12 | hanmei | NULL | 女 | 3 | 180.1 | this is a desc. |
| 11 | Lisi | NULL | NULL | 3 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
5 rows in set (0.00 sec)
mysql> select user_gender, avg(user_height) 平均身高 from t_user where user_id >10 group by user_g
ender order by 平均身高 desc ;
+-------------+--------------+
| user_gender | 平均身高 |
+-------------+--------------+
| 男 | 181.43333 |
| NULL | 180.10000 |
| 女 | 180.10000 |
+-------------+--------------+
3 rows in set (0.05 sec)
limit查询
LIMIT 后边可以跟两个参数，如果只写一个表示从零开始查询指定长度, 如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，两个参数必须是整数。
mysql> select * from t_user limit 2;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 7 | lisi | NULL | NULL | 1 | 180.1 | this is a desc. |
| 8 | lisi11 | NULL | NULL | 1 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from t_user limit 5,3;
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| user_id | user_name | user_birthday | user_gender | user_state | user_height | user_desc |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
| 11 | Lisi | NULL | NULL | 3 | 180.1 | this is a desc. |
| 12 | hanmei | NULL | 女 | 3 | 180.1 | this is a desc. |
| 13 | hanmei | NULL | 男 | 3 | 180.1 | this is a desc. |
+---------+-----------+---------------+-------------+------------+-------------+-----------------+
3 rows in set (0.01 sec)
关联查询
创建测试表
create table t_dept
(
dept_id int primary key,
dept_name varchar(30) not null
);
create table t_emp
(
emp_id int primary key,
emp_name varchar(20) not null,
emp_salary decimal(5,1) not null ,
dept_id int not null
);
insert into t_dept values(40,'教学部'),(10,'研发部'),(20,'市场部'),(30,'销售部');
insert into t_emp values(1,'zs',5550,10);
insert into t_emp values(2,'zs1',4550,10);
insert into t_emp values(3,'zs2',5550,10);
insert into t_emp values(4,'wangwu1',3550,20);
insert into t_emp values(5,'wangwu2',3000,20);
insert into t_emp values(6,'lisi',3500,30);
左连接,前面表为主表，后面表为从表，主表数据不省略(查询的数据根据主表来限制)
mysql> select * from t_dept d left join t_emp e on e.dept_id = d.dept_id;
+---------+-----------+--------+----------+------------+---------+
| dept_id | dept_name | emp_id | emp_name | emp_salary | dept_id |
+---------+-----------+--------+----------+------------+---------+
| 10 | 研发部 | 3 | zs2 | 5550.0 | 10 |
| 10 | 研发部 | 2 | zs1 | 4550.0 | 10 |
| 10 | 研发部 | 1 | zs | 5550.0 | 10 |
| 20 | 市场部 | 5 | wangwu2 | 3000.0 | 20 |
| 20 | 市场部 | 4 | wangwu1 | 3550.0 | 20 |
| 30 | 销售部 | 6 | lisi | 3500.0 | 30 |
| 40 | 教学部 | NULL | NULL | NULL | NULL |
+---------+-----------+--------+----------+------------+---------+
7 rows in set (0.06 sec)
中关联，（查询存在关联的数据，不存在关联信息自动去掉）
mysql> select * from t_dept d join t_emp e on e.dept_id = d.dept_id;
+---------+-----------+--------+----------+------------+---------+
| dept_id | dept_name | emp_id | emp_name | emp_salary | dept_id |
+---------+-----------+--------+----------+------------+---------+
| 10 | 研发部 | 1 | zs | 5550.0 | 10 |
| 10 | 研发部 | 2 | zs1 | 4550.0 | 10 |
| 10 | 研发部 | 3 | zs2 | 5550.0 | 10 |
| 20 | 市场部 | 4 | wangwu1 | 3550.0 | 20 |
| 20 | 市场部 | 5 | wangwu2 | 3000.0 | 20 |
| 30 | 销售部 | 6 | lisi | 3500.0 | 30 |
+---------+-----------+--------+----------+------------+---------+
右连接，join后面的表是主表
mysql> select * from t_dept d right join t_emp e on d.dept_id = e.dept_id;
+---------+-----------+--------+----------+------------+---------+
| dept_id | dept_name | emp_id | emp_name | emp_salary | dept_id |
+---------+-----------+--------+----------+------------+---------+
| 10 | 研发部 | 1 | zs | 5550.0 | 10 |
| 10 | 研发部 | 2 | zs1 | 4550.0 | 10 |
| 10 | 研发部 | 3 | zs2 | 5550.0 | 10 |
| 20 | 市场部 | 4 | wangwu1 | 3550.0 | 20 |
| 20 | 市场部 | 5 | wangwu2 | 3000.0 | 20 |
| 30 | 销售部 | 6 | lisi | 3500.0 | 30 |
+---------+-----------+--------+----------+------------+---------+
6 rows in set (0.00 sec)
内关联,会把空数据去除
mysql> select * from t_dept d , t_emp e where d.dept_id = e.dept_id;
+---------+-----------+--------+----------+------------+---------+
| dept_id | dept_name | emp_id | emp_name | emp_salary | dept_id |
+---------+-----------+--------+----------+------------+---------+
| 10 | 研发部 | 1 | zs | 5550.0 | 10 |
| 10 | 研发部 | 2 | zs1 | 4550.0 | 10 |
| 10 | 研发部 | 3 | zs2 | 5550.0 | 10 |
| 20 | 市场部 | 4 | wangwu1 | 3550.0 | 20 |
| 20 | 市场部 | 5 | wangwu2 | 3000.0 | 20 |
| 30 | 销售部 | 6 | lisi | 3500.0 | 30 |
+---------+-----------+--------+----------+------------+---------+
6 rows in set (0.00 sec)
横连接UNION
mysql> select * from t_emp where dept_id in(20,30);
+--------+----------+------------+---------+
| emp_id | emp_name | emp_salary | dept_id |
+--------+----------+------------+---------+
| 4 | wangwu1 | 3550.0 | 20 |
| 5 | wangwu2 | 3000.0 | 20 |
| 6 | lisi | 3500.0 | 30 |
+--------+----------+------------+---------+
3 rows in set (0.00 sec)
mysql> select * from t_emp where dept_id in(10,20);
+--------+----------+------------+---------+
| emp_id | emp_name | emp_salary | dept_id |
+--------+----------+------------+---------+
| 1 | zs | 5550.0 | 10 |
| 2 | zs1 | 4550.0 | 10 |
| 3 | zs2 | 5550.0 | 10 |
| 4 | wangwu1 | 3550.0 | 20 |
| 5 | wangwu2 | 3000.0 | 20 |
+--------+----------+------------+---------+
5 rows in set (0.00 sec)
####UNION 合并数据去除重复
select * from t_emp where dept_id in(20,30)
UNION
select * from t_emp where dept_id in(10,30);
+--------+----------+------------+---------+
| emp_id | emp_name | emp_salary | dept_id |
+--------+----------+------------+---------+
| 4 | wangwu1 | 3550.0 | 20 |
| 5 | wangwu2 | 3000.0 | 20 |
| 6 | lisi | 3500.0 | 30 |
| 1 | zs | 5550.0 | 10 |
| 2 | zs1 | 4550.0 | 10 |
| 3 | zs2 | 5550.0 | 10 |
+--------+----------+------------+---------+
6 rows in set (0.00 sec)
####UNION ALL合并数据 不去除重复数据
select * from t_emp where dept_id in(20,30)
UNION ALL
select * from t_emp where dept_id in(10,30);
+--------+----------+------------+---------+
| emp_id | emp_name | emp_salary | dept_id |
+--------+----------+------------+---------+
| 4 | wangwu1 | 3550.0 | 20 |
| 5 | wangwu2 | 3000.0 | 20 |
| 6 | lisi | 3500.0 | 30 |
| 1 | zs | 5550.0 | 10 |
| 2 | zs1 | 4550.0 | 10 |
| 3 | zs2 | 5550.0 | 10 |
| 6 | lisi | 3500.0 | 30 |
+--------+----------+------------+---------+
7 rows in set (0.05 sec)
登陆评论:
使用GITHUB登陆