文章目录
DCL
更多数据库MySQL系统内容就在以下专栏:
专栏链接:数据库MySQL
1. 基本介绍
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
2. 语法
2.1 基础查询
基本语法:
- 查询多个字段
SELECT 字段1, 字段2,...FROM 表名;
SELECT * FROM 表名;
- 设置别名
SELECT 字段1[AS 别名1], 字段2[AS 别名2],...FROM 表名;
注意:
as 可以省略
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
创建一个emp表
mysql>
mysql> create table emp(
-> id int comment '编号',
-> workno varchar(10) comment '工号',
-> name varchar(10) comment '姓名',
-> gender char(1) comment '性别',
-> age tinyint unsigned comment '年龄',
-> idcard char(18) comment '身份证号',
-> workaddress varchar(50) comment '工作地址',
-> entrydate date comment '入职时间'
-> ) comment '员工表';
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| emp |
| student |
+------------------+
2 rows in set (0.04 sec)
mysql>
添加16组数据
mysql>
mysql> insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate) values(1,'1', '柳岩', '女', 20, '123456789012345678', '北京', '2001-01-01'), (2,'2', '张无忌', '男', 18, '123456789012345671', '北京', '2002-02-02'),(3,'3', '韦一笑', '男', 38, '123456789012345672', '上海', '2003-03-03'),(4,'4', '赵敏', '女', 18, '123456789012345673', '北京', '2004-04-04'),(5,'5', '小昭', '女', 16, '123456789012345674', '上海', '2005-05-05'),(6,'6', '杨逍', '男', 28, '123456789012345675', '北京', '2006-01-01'), (7,'7', '范瑶', '男', 40, '123456789012345676', '北京', '2007-01-01'),(8,'8', '黛绮丝', '女', 38, '123456789012345678', '天津', '2008-01-01'), (9,'9', '范冰冰', '女', 45, '123456789012345679', '北京', '2009-01-01'), (10,'10', '陈友谅', '男', 53, '223456789012345676', '上海', '2017-01-01'),
-> (11,'11', '张士诚', '男', 55, '323456789012345676', '江苏', '2027-01-01'),
-> (12,'12', '常遇春', '男', 32, '423456789012345676', '北京', '2037-01-01'),
-> (13,'13', '张三丰', '男', 88, '523456789012345676', '江苏', '2047-01-01'),
-> (14,'14', '灭绝', '女', 65, '623456789012345676', '西安', '2057-01-01'),
-> (15,'15', '胡青牛', '男', 70, '723456789012345676', '西安', '2067-01-01'),
-> (16,'16', '周芷若', '女', 18, null, '北京', '2077-01-01');
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from emp;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | null | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.10 sec)
mysql>
案例1:
查询指定字段 name, workno, age 返回
示例代码:
mysql>
mysql> select name, workno, age from emp;
+--------+--------+-----+
| name | workno | age |
+--------+--------+-----+
| 柳岩 | 1 | 20 |
| 张无忌 | 2 | 18 |
| 韦一笑 | 3 | 38 |
| 赵敏 | 4 | 18 |
| 小昭 | 5 | 16 |
| 杨逍 | 6 | 28 |
| 范瑶 | 7 | 40 |
| 黛绮丝 | 8 | 38 |
| 范冰冰 | 9 | 45 |
| 陈友谅 | 10 | 53 |
| 张士诚 | 11 | 55 |
| 常遇春 | 12 | 32 |
| 张三丰 | 13 | 88 |
| 灭绝 | 14 | 65 |
| 胡青牛 | 15 | 70 |
| 周芷若 | 16 | 18 |
+--------+--------+-----+
16 rows in set (0.10 sec)
mysql>
案例2:
查询所有字段返回
示例代码:
mysql>
mysql> select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | null | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.11 sec)
mysql>
或者
mysql>
mysql> select * from emp;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | null | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.12 sec)
mysql>
案例3:
查询所有员工的工作地址,起别名
示例代码:
mysql>
mysql> select workaddress as '工作地址' from emp;
+----------+
| 工作地址 |
+----------+
| 北京 |
| 北京 |
| 上海 |
| 北京 |
| 上海 |
| 北京 |
| 北京 |
| 天津 |
| 北京 |
| 上海 |
| 江苏 |
| 北京 |
| 江苏 |
| 西安 |
| 西安 |
| 北京 |
+----------+
16 rows in set (0.12 sec)
mysql>
或者
mysql>
mysql> select workaddress '工作地址' from emp;
+----------+
| 工作地址 |
+----------+
| 北京 |
| 北京 |
| 上海 |
| 北京 |
| 上海 |
| 北京 |
| 北京 |
| 天津 |
| 北京 |
| 上海 |
| 江苏 |
| 北京 |
| 江苏 |
| 西安 |
| 西安 |
| 北京 |
+----------+
16 rows in set (0.11 sec)
mysql>
案例4:
查询公司员工的上班地址(不要重复)
示例代码:
mysql>
mysql> select distinct workaddress '工作地址' from emp;
+----------+
| 工作地址 |
+----------+
| 北京 |
| 上海 |
| 天津 |
| 江苏 |
| 西安 |
+----------+
5 rows in set (0.09 sec)
mysql>
2.2 条件查询
基本语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
案例1:
查询年龄等于88的员工。
mysql>
mysql> select * from emp where age=88;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
1 row in set (0.04 sec)
mysql>
案例2:
查询年龄小于20的员工信息
mysql>
mysql> select * from emp where age<20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | null | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
4 rows in set (0.04 sec)
mysql>
案例3:
查询年龄小于等于20的员工信息
mysql>
mysql> select * from emp where age<=20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | null | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.04 sec)
mysql>
案例4:
查询没有身份证号的员工信息
mysql>
mysql> select * from emp where idcard is null;
+----+--------+--------+--------+-----+--------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------+-------------+------------+
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------+-------------+------------+
1 row in set (0.07 sec)
mysql>
案例5:
查询有身份证号的员工信息
mysql>
mysql> select * from emp where idcard is not null;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
15 rows in set (0.08 sec)
mysql>
案例6:
查询年龄不等于88的员工信息
mysql>
mysql> select * from emp where age!=88;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
15 rows in set (0.08 sec)
mysql>
或者
mysql>
mysql> select * from emp where age <> 88;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
15 rows in set (0.05 sec)
mysql>
案例7:
查询年龄在15岁(包含)到20岁(包含)之间的员工信息
mysql>
mysql> select * from emp where age >=15 && age <=20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.05 sec)
mysql>
或者
mysql>
mysql> select * from emp where age>=12 and age<=20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.05 sec)
mysql>
或者
mysql>
mysql> select * from emp where age between 15 and 20;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.05 sec)
mysql>
案例8:
查询性别为 女 且年龄小于25岁的员工信息
mysql>
mysql> select * from emp where gender = '女' and age <25;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
4 rows in set (0.05 sec)
mysql>
案例9:
查询年龄等于18 或 20 或40 的员工信息
mysql>
mysql> select * from emp where age = 18 || age =20 || age = 40;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.04 sec)
mysql>
或者:
mysql>
mysql> select * from emp where age = 18 or age = 20 or age = 40;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.05 sec)
mysql>
或者:
mysql>
mysql> select * from emp where age in(18, 20,40);
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
5 rows in set (0.05 sec)
mysql>
案例10:
查询姓名为两个字的员工信息
mysql>
mysql> select * from emp where name like '__';
+----+--------+------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
+----+--------+------+--------+-----+--------------------+-------------+------------+
6 rows in set (0.04 sec)
mysql>
案例11:
mysql>
mysql> select * from emp where idcard like '%x';
Empty set
mysql>
或者:
mysql> select * from emp where idcard like '_________________x';
Empty set
mysql>
2.3 聚合函数
基本介绍:
将一列数据作为一个整体,进行纵向计算。
常见聚合函数:
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
2.4 聚合查询
基本语法:
SELECT 聚合函数(字段列表)FROM 表名;
注意:
使用聚合函数进行计算时,所有的null值不参与计算。
案例1:
统计该企业员工数量
mysql>
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.03 sec)
mysql>
案例2:
统计该企业员工的平均年龄
mysql>
mysql> select avg(age) from emp;
+----------+
| avg(age) |
+----------+
| 40.1250 |
+----------+
1 row in set (0.03 sec)
mysql>
案例3:
统计该企业员工的最大年龄
mysql> select max(age) from emp;
+----------+
| max(age) |
+----------+
| 88 |
+----------+
1 row in set (0.03 sec)
mysql>
案例4:
统计该企业员工的最小年龄
mysql>
mysql> select min(age) from emp;
+----------+
| min(age) |
+----------+
| 16 |
+----------+
1 row in set (0.03 sec)
mysql>
案例5:
统计西安地区员工的年龄之和
mysql>
mysql> select sum(age) from emp where workaddress = '西安';
+----------+
| sum(age) |
+----------+
| 135 |
+----------+
1 row in set (0.04 sec)
mysql>
2.5 分组查询
基本语法:
SELECT 字段列表FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
where与having的区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件的,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意:
- 执行顺序:where> 聚合函数> having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段并无任何意义。
案例1:
根据性别分组,统计 男性员工 和 女性员工的数量。
mysql>
mysql> select gender, count(*) from emp group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 7 |
| 男 | 9 |
+--------+----------+
2 rows in set (0.05 sec)
mysql>
案例2:
根据性别分组,统计男性员工 和 女性员工的平均年龄
mysql>
mysql> select gender, avg(age) from emp group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 女 | 31.4286 |
| 男 | 46.8889 |
+--------+----------+
2 rows in set (0.05 sec)
mysql>
案例3:
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址。
mysql>
mysql> select workaddress, count(*) from emp where age<45 group by workaddress having count(*) >= 3;
+-------------+----------+
| workaddress | count(*) |
+-------------+----------+
| 北京 | 7 |
+-------------+----------+
1 row in set (0.04 sec)
mysql>
2.6 排序查询
** 基本语法:**
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
ASC : 升序(默认值)
DESC:降序
注意:
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
案例1:
根据年龄对公司的员工进行升序排序
mysql>
mysql> select name, age from emp order by age asc;
+--------+-----+
| name | age |
+--------+-----+
| 小昭 | 16 |
| 张无忌 | 18 |
| 赵敏 | 18 |
| 周芷若 | 18 |
| 柳岩 | 20 |
| 杨逍 | 28 |
| 常遇春 | 32 |
| 韦一笑 | 38 |
| 黛绮丝 | 38 |
| 范瑶 | 40 |
| 范冰冰 | 45 |
| 陈友谅 | 53 |
| 张士诚 | 55 |
| 灭绝 | 65 |
| 胡青牛 | 70 |
| 张三丰 | 88 |
+--------+-----+
16 rows in set (0.04 sec)
mysql>
案例2:
根据入职时间,对员工进行降序排序
mysql>
mysql> select * from emp order by entrydate desc;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.05 sec)
mysql>
案例3:
根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
mysql>
mysql> select * from emp order by age asc, entrydate desc;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
16 rows in set (0.07 sec)
mysql>
2.7 分页查询
基本语法:
SELECT 字段列表 FROM 表名LIMIT 起始索引, 查询记录数;
注意:
- 起始索引从0开始,起始索引 = (查询页码-1) * 每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
- 如果查询的是第一页数据,其实索引可以忽略,直接简写为limit 10
案例1:
查询第一页员工数据,每页展示10条记录
mysql>
mysql> select * from emp limit 0, 10;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
10 rows in set (0.05 sec)
mysql>
或者:
mysql>
mysql> select * from emp limit 10;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
| 2 | 2 | 张无忌 | 男 | 18 | 123456789012345671 | 北京 | 2002-02-02 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 4 | 4 | 赵敏 | 女 | 18 | 123456789012345673 | 北京 | 2004-04-04 |
| 5 | 5 | 小昭 | 女 | 16 | 123456789012345674 | 上海 | 2005-05-05 |
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 7 | 7 | 范瑶 | 男 | 40 | 123456789012345676 | 北京 | 2007-01-01 |
| 8 | 8 | 黛绮丝 | 女 | 38 | 123456789012345678 | 天津 | 2008-01-01 |
| 9 | 9 | 范冰冰 | 女 | 45 | 123456789012345679 | 北京 | 2009-01-01 |
| 10 | 10 | 陈友谅 | 男 | 53 | 223456789012345676 | 上海 | 2017-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
10 rows in set (0.05 sec)
mysql>
案例2:
查询第二页员工数据,每页展示10条记录
mysql>
mysql> select * from emp limit 10, 10;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 11 | 11 | 张士诚 | 男 | 55 | 323456789012345676 | 江苏 | 2027-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 13 | 13 | 张三丰 | 男 | 88 | 523456789012345676 | 江苏 | 2047-01-01 |
| 14 | 14 | 灭绝 | 女 | 65 | 623456789012345676 | 西安 | 2057-01-01 |
| 15 | 15 | 胡青牛 | 男 | 70 | 723456789012345676 | 西安 | 2067-01-01 |
| 16 | 16 | 周芷若 | 女 | 18 | NULL | 北京 | 2077-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
6 rows in set (0.07 sec)
mysql>
2.8 综合案例练习
案例1:
查询年龄为20, 21, 22, 23的员工信息。
mysql>
mysql> select * from emp where gender = '女' and age = 20 or age = 20 or age = 21 or age = 22 or age = 23;
+----+--------+------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
+----+--------+------+--------+-----+--------------------+-------------+------------+
1 row in set (0.05 sec)
mysql>
或者:
mysql>
mysql> select * from emp where gender = '女' and age in(20, 21, 22, 23);
+----+--------+------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+------+--------+-----+--------------------+-------------+------------+
| 1 | 1 | 柳岩 | 女 | 20 | 123456789012345678 | 北京 | 2001-01-01 |
+----+--------+------+--------+-----+--------------------+-------------+------------+
1 row in set (0.04 sec)
mysql>
案例2:
查询性别为男,并且年龄在 20-40 (含)以内的,姓名为三个字的员工
mysql>
mysql> select * from emp where gender = '男' and age >= 20 and age <= 40 and name like '___';
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
2 rows in set (0.05 sec)
mysql>
或者:
mysql>
mysql> select * from emp where gender = '男' and age between 20 and 40 and name like '___';
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
2 rows in set (0.05 sec)
mysql>
案例3:
统计员工表中,年龄小于60岁的,男性员工和女性员工的人数
mysql>
mysql> select gender, count(*) from emp where age < 60 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 6 |
| 男 | 7 |
+--------+----------+
2 rows in set (0.05 sec)
mysql>
案例4:
查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按照年龄升序排序,如果年龄相同按入职时间降序排序
mysql>
mysql> select name, age from emp where age <= 35 order by age asc, entrydate desc;
+--------+-----+
| name | age |
+--------+-----+
| 小昭 | 16 |
| 周芷若 | 18 |
| 赵敏 | 18 |
| 张无忌 | 18 |
| 柳岩 | 20 |
| 杨逍 | 28 |
| 常遇春 | 32 |
+--------+-----+
7 rows in set (0.04 sec)
mysql>
案例5:
查询性别为男,且年龄在20-40(含)以内的前3个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
mysql>
mysql> select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate asc limit 0, 3;
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| id | workno | name | gender | age | idcard | workaddress | entrydate |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
| 6 | 6 | 杨逍 | 男 | 28 | 123456789012345675 | 北京 | 2006-01-01 |
| 12 | 12 | 常遇春 | 男 | 32 | 423456789012345676 | 北京 | 2037-01-01 |
| 3 | 3 | 韦一笑 | 男 | 38 | 123456789012345672 | 上海 | 2003-03-03 |
+----+--------+--------+--------+-----+--------------------+-------------+------------+
3 rows in set (0.05 sec)
mysql>
2.9 执行顺序
执行顺序:
上面我们介绍是编写顺序 , 并不是执行顺序。
执行顺序:
验证执行顺序:
mysql> select name, age from emp e where age > 15 order by age asc;
+--------+-----+
| name | age |
+--------+-----+
| 小昭 | 16 |
| 张无忌 | 18 |
| 赵敏 | 18 |
| 周芷若 | 18 |
| 柳岩 | 20 |
| 杨逍 | 28 |
| 常遇春 | 32 |
| 韦一笑 | 38 |
| 黛绮丝 | 38 |
| 范瑶 | 40 |
| 范冰冰 | 45 |
| 陈友谅 | 53 |
| 张士诚 | 55 |
| 灭绝 | 65 |
| 胡青牛 | 70 |
| 张三丰 | 88 |
+--------+-----+
16 rows in set (0.10 sec)
mysql>
思路:
我们给emp这个表起了一个别名e ,如果执行顺序是from—> where—>group by—> having —> select,那么我们在用e来访问name,age 就不会报错。
mysql>
mysql> select e.name, e.age from emp e where e.age > 15 order by age asc;
+--------+-----+
| name | age |
+--------+-----+
| 小昭 | 16 |
| 张无忌 | 18 |
| 赵敏 | 18 |
| 周芷若 | 18 |
| 柳岩 | 20 |
| 杨逍 | 28 |
| 常遇春 | 32 |
| 韦一笑 | 38 |
| 黛绮丝 | 38 |
| 范瑶 | 40 |
| 范冰冰 | 45 |
| 陈友谅 | 53 |
| 张士诚 | 55 |
| 灭绝 | 65 |
| 胡青牛 | 70 |
| 张三丰 | 88 |
+--------+-----+
16 rows in set (0.10 sec)
mysql>
那反过来,如果给select中的name 和 age 起一个别名,用这个别名去访问order by 中的age 或者区访问where中的age均会报错。
mysql>
mysql> select e.name ename, e.age eage from emp e where eage > 15 order by eage asc;
1054 - Unknown column 'eage' in 'where clause'
mysql>