DML
数据操纵语言DML(Data Manipulation Language),用户通过它可以实现对数据库的基本操作。
SELECT完整语法
先给一下完整的语法,后面将逐一来讲解。
-
基础语法
SELECT 字段列表 FROM 数据源; -
完整语法
select 去重选项 字段列表 [as 字段别名]
from 数据源
[where子句]
[group by 子句]
[having子句]
[order by 子句]
[limit子句];
创建User 表
- 创建数据库
create database test;
- 创建User表
use test;
CREATE TABLE `test`.`user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`pwd` VARCHAR(32) NOT NULL,
`age` INT NOT NULL,
`sex` VARCHAR(8) NOT NULL,
PRIMARY KEY (`id`));
- 插入数据
如:
use test;
insert into user(name,pwd,age,sex)
values
('小灰灰','xiaohuihui',6,'男'),
('黑太郎','heitailang',20,'男');
- 查询数据
./mysql -h 127.0.0.1 -u root -P 3306 -p
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select id,name from user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 喜羊羊 |
| 2 | 懒羊羊 |
| 3 | 慢羊羊 |
| 4 | 慢羊羊 |
| 7 | 沸羊羊 |
| 8 | 花羊羊 |
| 9 | 奔羊羊 |
| 10 | 瘦羊羊 |
| 11 | 红太狼 |
| 12 | 蕉太狼 |
| 13 | 小灰灰 |
| 14 | 黑太郎 |
+----+-----------+
12 rows in set (0.00 sec)
单表查询
- 在SELECT 语句中使用 “*” 通配符查询所有字段
mysql> SELECT * FROM user;
+----+-----------+-------------+-----+-----+
| id | name | pwd | age | sex |
+----+-----------+-------------+-----+-----+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 |
| 4 | 慢羊羊 | manyangyang | 14 | 男 |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 |
| 8 | 花羊羊 | huayangyang | 18 | 女 |
| 9 | 奔羊羊 | benyangyang | 20 | 男 |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 |
| 11 | 红太狼 | hongtailang | 20 | 女 |
| 12 | 蕉太狼 | jiaotailang | 24 | 男 |
| 13 | 小灰灰 | xiaohuihui | 6 | 男 |
| 14 | 黑太郎 | heitailang | 20 | 男 |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)
- 在SELECT 语句中指定所有字段
mysql> SELECT id,name,pwd,age,sex FROM user;
+----+-----------+-------------+-----+-----+
| id | name | pwd | age | sex |
+----+-----------+-------------+-----+-----+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 |
| 4 | 慢羊羊 | manyangyang | 14 | 男 |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 |
| 8 | 花羊羊 | huayangyang | 18 | 女 |
| 9 | 奔羊羊 | benyangyang | 20 | 男 |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 |
| 11 | 红太狼 | hongtailang | 20 | 女 |
| 12 | 蕉太狼 | jiaotailang | 24 | 男 |
| 13 | 小灰灰 | xiaohuihui | 6 | 男 |
| 14 | 黑太郎 | heitailang | 20 | 男 |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)
- 查询指定字段:SELECT 列名 FROM 表名;
mysql> SELECT name from user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懒羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 红太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
+-----------+
12 rows in set (0.00 sec)
- 查询多个字段:SELECT 字段名1,字段名2, … 字段名n FROM 表名;
mysql> SELECT name,pwd,sex from user;
+-----------+-----+
| pwd | sex |
+-----------+-----+
| 喜羊羊 | 男 |
| 懒羊羊 | 男 |
| 慢羊羊 | 女 |
| 慢羊羊 | 男 |
| 沸羊羊 | 女 |
| 花羊羊 | 女 |
| 奔羊羊 | 男 |
| 瘦羊羊 | 女 |
| 红太狼 | 女 |
| 蕉太狼 | 男 |
| 小灰灰 | 男 |
| 黑太郎 | 男 |
+-----------+-----+
12 rows in set (0.00 sec)
- 查询指定记录 SELECT 字段名1,字段名2, … 字段名n FROM 表名 WHERE 查询条件
mysql> SELECT name,pwd,sex from user where sex='女';
+-----------+-------------+-----+
| name | pwd | sex |
+-----------+-------------+-----+
| 慢羊羊 | meiyangyang | 女 |
| 沸羊羊 | feiyangyang | 女 |
| 花羊羊 | huayangyang | 女 |
| 瘦羊羊 | souyangyang | 女 |
| 红太狼 | hongtailang | 女 |
+-----------+-------------+-----+
5 rows in set (0.01 sec)
- 查询年龄小于20的记录
mysql> SELECT name,age FROM user where age <20;
+-----------+-----+
| name | age |
+-----------+-----+
| 喜羊羊 | 12 |
| 懒羊羊 | 10 |
| 慢羊羊 | 10 |
| 慢羊羊 | 14 |
| 沸羊羊 | 16 |
| 花羊羊 | 18 |
| 小灰灰 | 6 |
+-----------+-----+
7 rows in set (0.01 sec)
- IN 关键字查询:查询满足指定范围内的条件的记录
mysql> SELECT id,name,sex FROM user where id in (1,2,3,4);
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 喜羊羊 | 男 |
| 2 | 懒羊羊 | 男 |
| 3 | 慢羊羊 | 女 |
| 4 | 慢羊羊 | 男 |
+----+-----------+-----+
4 rows in set (0.00 sec)
- NOT IN 关键字查询: 查询不在指定范围内的条件的记录
mysql> SELECT id,name,sex FROM user where id not in (1,2,3,4);
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 7 | 沸羊羊 | 女 |
| 8 | 花羊羊 | 女 |
| 9 | 奔羊羊 | 男 |
| 10 | 瘦羊羊 | 女 |
| 11 | 红太狼 | 女 |
| 12 | 蕉太狼 | 男 |
| 13 | 小灰灰 | 男 |
| 14 | 黑太郎 | 男 |
+----+-----------+-----+
8 rows in set (0.01 sec)
- BETWEEN AND 关键字:查询某个范围内的记录
mysql> SELECT id,name,sex,age FROM user where age between 20 and 24;
+----+-----------+-----+-----+
| id | name | sex | age |
+----+-----------+-----+-----+
| 9 | 奔羊羊 | 男 | 20 |
| 10 | 瘦羊羊 | 女 | 24 |
| 11 | 红太狼 | 女 | 20 |
| 12 | 蕉太狼 | 男 | 24 |
| 14 | 黑太郎 | 男 | 20 |
+----+-----------+-----+-----+
5 rows in set (0.00 sec)
- NOT BETWEEN AND 关键字:查询不在某个范围内的记录
mysql> SELECT id,name,sex,age FROM user where age not between 20 and 24;
+----+-----------+-----+-----+
| id | name | sex | age |
+----+-----------+-----+-----+
| 1 | 喜羊羊 | 男 | 12 |
| 2 | 懒羊羊 | 男 | 10 |
| 3 | 慢羊羊 | 女 | 10 |
| 4 | 慢羊羊 | 男 | 14 |
| 7 | 沸羊羊 | 女 | 16 |
| 8 | 花羊羊 | 女 | 18 |
| 13 | 小灰灰 | 男 | 6 |
+----+-----------+-----+-----+
7 rows in set (0.00 sec)
- 查询以“小”开头的数据
mysql> SELECT id,name,age,sex FROM user where name like '小%';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 13 | 小灰灰 | 6 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
- 查询以“喜”开头 和 “ 羊”结尾的数据
mysql> SELECT id,name,age,sex FROM user where name like '喜%羊';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 1 | 喜羊羊 | 12 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
- 下划线通配符查询
mysql> SELECT id,name,age,sex FROM user where name like '__灰';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 13 | 小灰灰 | 6 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
保留关键字
下面我们看看如果在表的定义中使用了mysql的保留关键字,如何查询数据,首先创建表config
CREATE TABLE `test`.`config` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARCHAR(45) NULL,
`value` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
使用普通的查询语句查询数据
mysql> SELECT key,value FROM config;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key,value FROM config' at line 1
mysql>
如果表中字段使用了mysql的保留关键字,则需添加单引号查询,如:
mysql> SELECT 'key','value' FROM config;
+-----+-------+
| key | value |
+-----+-------+
| key | value |
| key | value |
+-----+-------+
2 rows in set (0.00 sec)
mysql>
查询空值
- 添加字段email
ALTER TABLE `test`.`user`
ADD COLUMN `email` VARCHAR(64) NULL AFTER `sex`;
UPDATE `test`.`user` SET `email` = 'xiyangyang@163.com' WHERE (`id` = '1');
UPDATE `test`.`user` SET `email` = 'lanyangyang@163.com' WHERE (`id` = '2');
UPDATE `test`.`user` SET `email` = 'meiyangyang@163.com' WHERE (`id` = '3');
UPDATE `test`.`user` SET `email` = 'manyangyang@163.com' WHERE (`id` = '4');
UPDATE `test`.`user` SET `email` = 'feiyangyang@163.com' WHERE (`id` = '7');
- IS NULL 查询
mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NULL;
+----+-----------+-----+-----+-------+
| id | name | age | sex | email |
+----+-----------+-----+-----+-------+
| 8 | 花羊羊 | 18 | 女 | NULL |
| 9 | 奔羊羊 | 20 | 男 | NULL |
| 10 | 瘦羊羊 | 24 | 女 | NULL |
| 11 | 红太狼 | 20 | 女 | NULL |
| 12 | 蕉太狼 | 24 | 男 | NULL |
| 13 | 小灰灰 | 6 | 男 | NULL |
| 14 | 黑太郎 | 20 | 男 | NULL |
+----+-----------+-----+-----+-------+
7 rows in set (0.00 sec)
- IS NOT NULL 查询
mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NOT NULL;
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 1 | 喜羊羊 | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
5 rows in set (0.00 sec)
AND多条件查询
mysql> SELECT id,name,age,sex,email FROM user where id IN (4,7) AND age = 16 and name=' 沸羊羊';
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
1 row in set (0.00 sec)
OR 多条件查询
mysql> SELECT id,name,age,sex,email FROM user where id=4 OR id=7;
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 4 | 慢羊羊 | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
2 rows in set (0.00 sec)
DISTINCT
去重选项就是是否对结果中完全相同的记录(所有字段数据都相同)进行去重:
- all:不去重
- distinct:去重
语法:select 去重选项 字段列表 from 表名; - 插入一条数据
INSERT INTO `test`.`user` (`name`, `pwd`, `age`, `sex`, `email`)
VALUES ('喜羊羊', 'xiyangyang', '12', '男', 'xiyangyang@163.com');
- 不使用 distinct 关键字
mysql> SELECT name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懒羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 红太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
- 使用 distinct 关键字 : 消除重复的记录值
mysql> SELECT distinct name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懒羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 红太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
+-----------+
11 rows in set (0.00 sec)
查询结果排序
- 不使用 ORDER BY : 根据数据插入到数据表的顺序来显示
mysql> SELECT name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懒羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 红太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
- 使用 ORDER BY
mysql> SELECT name FROM user ORDER BY name;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 喜羊羊 |
| 奔羊羊 |
| 小灰灰 |
| 慢羊羊 |
| 慢羊羊 |
| 懒羊羊 |
| 沸羊羊 |
| 瘦羊羊 |
| 红太狼 |
| 花羊羊 |
| 蕉太狼 |
| 黑太郎 |
+-----------+
13 rows in set (0.00 sec)
- 使用DESC 关键字
mysql> SELECT name FROM user ORDER BY name DESC;
+-----------+
| name |
+-----------+
| 黑太郎 |
| 蕉太狼 |
| 花羊羊 |
| 红太狼 |
| 瘦羊羊 |
| 沸羊羊 |
| 懒羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 小灰灰 |
| 奔羊羊 |
| 喜羊羊 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
分组查询
- 使用 GROUP BY 关键
mysql> SELECT sex,COUNT(*) FROM user GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| 男 | 8 |
| 女 | 5 |
+-----+----------+
2 rows in set (0.01 sec)
- 使用GROUP_CONCAT函数
mysql> SELECT sex,GROUP_CONCAT(name) FROM user GROUP BY sex;
+-----+---------------------------------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+-----+---------------------------------------------------------------------------------+
| 女 | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,红太狼 |
| 男 | 喜羊羊,懒羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+-----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
- 使用HAVING
mysql> SELECT sex,GROUP_CONCAT(name) as names FROM user GROUP BY sex HAVING COUNT(name) > 5;
+-----+---------------------------------------------------------------------------------+
| sex | names |
+-----+---------------------------------------------------------------------------------+
| 男 | 喜羊羊,懒羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+-----+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 使用WITH ROLLUP
mysql> SELECT sex,GROUP_CONCAT(name) FROM user GROUP BY sex WITH ROLLUP;
+------+-----------------------------------------------------------------------------------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 女 | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,红太狼 |
| 男 | 喜羊羊,懒羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
| NULL | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,红太狼,喜羊羊,懒羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> SELECT sex,COUNT(name) FROM user GROUP BY sex WITH ROLLUP;
+------+-------------+
| sex | COUNT(name) |
+------+-------------+
| 女 | 5 |
| 男 | 8 |
| NULL | 13 |
+------+-------------+
3 rows in set (0.00 sec)
LIMIT 关键字
基本语法: LIMIT [位置偏移量,] 行数
- 查询前4条记录
mysql> SELECT * FROM user LIMIT 4;
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | manyangyang | 14 | 男 | manyangyang@163.com |
+----+-----------+-------------+-----+-----+---------------------+
4 rows in set (0.02 sec)
mysql> SELECT * FROM user LIMIT 0,4;
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | manyangyang | 14 | 男 | manyangyang@163.com |
+----+-----------+-------------+-----+-----+---------------------+
4 rows in set (0.00 sec)
- 查询 从第5条记录开始,共3条记录
mysql> SELECT * FROM user LIMIT 4,3;
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 7 | 沸羊羊 | feiyangyang | 16 | 女 | feiyangyang@163.com |
| 8 | 花羊羊 | huayangyang | 18 | 女 | NULL |
| 9 | 奔羊羊 | benyangyang | 20 | 男 | NULL |
+----+-----------+-------------+-----+-----+---------------------+
3 rows in set (0.00 sec)
连接查询
- 创建订单表
CREATE TABLE `test`.`orders` (
`order_id` INT UNSIGNED NOT NULL,
`user_id` INT NULL,
`price` DECIMAL(8) NULL,
`create_time` DATETIME NULL,
PRIMARY KEY (`order_id`));
- 插入测试数据
use test;
INSERT INTO `test`.`orders`
(`order_id`,
`user_id`,
`price`,
`create_time`)
VALUES
(101,1,100,'2021-01-01'),
(102,2,200,'2021-01-02'),
(103,3,300,'2021-01-03'),
(104,4,400,'2021-01-04');
- 内连接查询
mysql> SELECT name,user_id,price,create_time FROM user,orders where user.id=orders.user_id;
+-----------+---------+-------+---------------------+
| name | user_id | price | create_time |
+-----------+---------+-------+---------------------+
| 喜羊羊 | 1 | 100 | 2021-01-01 00:00:00 |
| 懒羊羊 | 2 | 200 | 2021-01-02 00:00:00 |
| 慢羊羊 | 3 | 300 | 2021-01-03 00:00:00 |
| 慢羊羊 | 4 | 400 | 2021-01-04 00:00:00 |
+-----------+---------+-------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT name,user_id,price,create_time FROM user INNER JOIN orders ON user.id=orders.user_id;
+-----------+---------+-------+---------------------+
| name | user_id | price | create_time |
+-----------+---------+-------+---------------------+
| 喜羊羊 | 1 | 100 | 2021-01-01 00:00:00 |
| 懒羊羊 | 2 | 200 | 2021-01-02 00:00:00 |
| 慢羊羊 | 3 | 300 | 2021-01-03 00:00:00 |
| 慢羊羊 | 4 | 400 | 2021-01-04 00:00:00 |
+-----------+---------+-------+---------------------+
4 rows in set (0.00 sec)
- LEFT JOIN 左连接
mysql> SELECT name,user_id,price,create_time FROM user LEFT JOIN orders ON user.id=orders.user_id;
+-----------+---------+-------+---------------------+
| name | user_id | price | create_time |
+-----------+---------+-------+---------------------+
| 喜羊羊 | 1 | 100 | 2021-01-01 00:00:00 |
| 懒羊羊 | 2 | 200 | 2021-01-02 00:00:00 |
| 慢羊羊 | 3 | 300 | 2021-01-03 00:00:00 |
| 慢羊羊 | 4 | 400 | 2021-01-04 00:00:00 |
| 沸羊羊 | NULL | NULL | NULL |
| 花羊羊 | NULL | NULL | NULL |
| 奔羊羊 | NULL | NULL | NULL |
| 瘦羊羊 | NULL | NULL | NULL |
| 红太狼 | NULL | NULL | NULL |
| 蕉太狼 | NULL | NULL | NULL |
| 小灰灰 | NULL | NULL | NULL |
| 黑太郎 | NULL | NULL | NULL |
| 喜羊羊 | NULL | NULL | NULL |
+-----------+---------+-------+---------------------+
13 rows in set (0.01 sec)
- RIGHT JOIN 右连接
mysql> SELECT name,user_id,price,create_time FROM user RIGHT JOIN orders ON user.id=orders.user_id;
+-----------+---------+-------+---------------------+
| name | user_id | price | create_time |
+-----------+---------+-------+---------------------+
| 喜羊羊 | 1 | 100 | 2021-01-01 00:00:00 |
| 懒羊羊 | 2 | 200 | 2021-01-02 00:00:00 |
| 慢羊羊 | 3 | 300 | 2021-01-03 00:00:00 |
| 慢羊羊 | 4 | 400 | 2021-01-04 00:00:00 |
+-----------+---------+-------+---------------------+
4 rows in set (0.00 sec)
- 带IN关键字子查询
mysql> SELECT * FROM user where id in (SELECT user_id FROM orders);
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | manyangyang | 14 | 男 | manyangyang@163.com |
+----+-----------+-------------+-----+-----+---------------------+
4 rows in set (0.01 sec)
- 合并查询结果
- UNION 从查询结果集中自动去除重复行
mysql> SELECT * FROM user WHERE id IN (1,2,3,4,7,8) UNION SELECT * FROM user where id IN (7,8,9,10);
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | manyangyang | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 | feiyangyang@163.com |
| 8 | 花羊羊 | huayangyang | 18 | 女 | NULL |
| 9 | 奔羊羊 | benyangyang | 20 | 男 | NULL |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 | NULL |
+----+-----------+-------------+-----+-----+---------------------+
8 rows in set (0.01 sec)
- UNION ALL 包含重复行
mysql> SELECT * FROM user WHERE id IN (1,2,3,4,7,8) UNION ALL SELECT * FROM user where id IN (7,8,9,10);
+----+-----------+-------------+-----+-----+---------------------+
| id | name | pwd | age | sex | email |
+----+-----------+-------------+-----+-----+---------------------+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 | xiyangyang@163.com |
| 2 | 懒羊羊 | lanyangyang | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | manyangyang | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 | feiyangyang@163.com |
| 8 | 花羊羊 | huayangyang | 18 | 女 | NULL |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 | feiyangyang@163.com |
| 8 | 花羊羊 | huayangyang | 18 | 女 | NULL |
| 9 | 奔羊羊 | benyangyang | 20 | 男 | NULL |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 | NULL |
+----+-----------+-------------+-----+-----+---------------------+
10 rows in set (0.00 sec)
- INSERT INTO SELECT语句
语句形式为:Insert into Table2(field1,field2,…) select value1,value2,… from Table1 [where column =value][]为可选内容要求目标表Table2必须在。示例如下:
use user_db;
Insert into user_bak select * from user;
- SELECT INTO FROM语句
语句形式为:SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。