#返回顶部按钮 #生成一个目录导航
xingchenck
mysql 数据库 IV（单表查询）

1.今日内容

  1. 单表查询语法

    select distinct 字段1，字段2...  from 表名
						     where 条件
					             group by 字段
						     having 筛选
					             order by 字段
						     limit 限制条数

  2. 关键字执行的优先级

    from       # 找到表
where      # 拿着where指定的约束条件，去文件/表中取出一条条记录
group by   # 将取出来的数据进行group by，如果没有group by，则整体做为一组
select     
distinct   #执行select-去重
having     #将分组的结果进行having过滤
order by   #将结果按条件排序，order by
limit      #限制结果的显示条数

2.具体内容

  1. 数据准备

    单表查询准备工作 
       #创建表
	mysql> create table employee(                                                       		 	
      -> id int not null unique auto_increment,
    	-> emp_name varchar(20) not null,
      -> sex enum('male','female') not null default 'female',
      -> age int(3) unsigned not null default 28,
      -> hire_date date not null,
      -> post varchar(50),
      -> post_comment varchar(100),
      -> salary double(15,2),
      -> office int,
      -> depart_id int
      -> );
	Query OK, 0 rows affected (0.04 sec)


  #查看表结构

	mysql> desc employee;
	+--------------+-----------------------+------+-----+---------+---	-------------+
	| Field        | Type                  | Null | Key | Default | 		Extra          |
	+--------------+-----------------------+------+-----+---------+---	-------------+
	| id           | int(11)               | NO   | PRI | NULL    | 		auto_increment |
	| emp_name     | varchar(20)           | NO   |     | NULL    |                	 |
	| sex          | enum('male','female') | NO   |     | female  |                	 |
	| age          | int(3) unsigned       | NO   |     | 28      |                	 |
	| hire_date    | date                  | NO   |     | NULL    |                 	|
	| post         | varchar(50)           | YES  |     | NULL    |                 	|
	| post_comment | varchar(100)          | YES  |     | NULL    |                 	|
	| salary       | double(15,2)          | YES  |     | NULL    |                 	|
	| office       | int(11)               | YES  |     | NULL    |                	 |
	| depart_id    | int(11)               | YES  |     | NULL    |                	 |
	+--------------+-----------------------+------+-----+---------+---	-------------+
	10 rows in set (0.02 sec)
	

   #插入数据

	mysql> insert into 			 employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) 	values
		-> ('egon','male',18,'20170301','老男孩驻沙河办事处外交大	使',7300.33,401,1), 
             -> ('alex','male',78,'20150302','teacher',1000000.31,401,1),
    	        -> ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
   	        -> ('yuanhao','male',73,'20140701','teacher',3500,401,1),
    	        -> ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
    	        -> ('jingliyang','female',18,'20110211','teacher',9000,401,1),
    	        -> ('jinxin','male',18,'19000301','teacher',30000,401,1),
    	        -> ('成龙','male',48,'20101111','teacher',10000,401,1),
        	-> ('歪歪','female',48,'20150311','sale',3000.13,402,2),
   		-> ('丫丫','female',38,'20101101','sale',2000.35,402,2),
    	        -> ('丁丁','female',18,'20110312','sale',1000.37,402,2),
    	        -> ('星星','female',18,'20160513','sale',3000.29,402,2),
    	        -> ('格格','female',28,'20170127','sale',4000.33,402,2),
    	        -> ('张野','male',28,'20160311','operation',10000.13,403,3),
    	        -> ('程咬金','male',18,'19970312','operation',20000,403,3),
    	        -> ('程咬银','female',18,'20130311','operation',19000,403,3),
    	        -> ('程咬铜','male',18,'20150411','operation',18000,403,3),
    	        -> ('程咬铁','female',18,'20140512','operation',17000,403,3)
    	        -> ;
		Query OK, 18 rows affected (0.01 sec)
		Records: 18  Duplicates: 0  Warnings: 0


	mysql> select * from employee;
	+----+------------+--------+-----+------------+-------------------	----------------------+--------------+------------+--------+------	-----+
	| id | emp_name   | sex    | age | hire_date  | post                                    	| post_comment | salary     | office | depart_id |
	+----+------------+--------+-----+------------+-------------------	----------------------+--------------+------------+--------+------	-----+
	|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外	交大使              | NULL         |    7300.33 |    401 |         	1 |
	|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 	| NULL         | 1000000.31 |    401 |         1 |
	|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 	| NULL         |    8300.00 |    401 |         1 |
	|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 	| NULL         |    3500.00 |    401 |         1 |
	|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 	| NULL         |    2100.00 |    401 |         1 |
	|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 	| NULL         |    9000.00 |    401 |         1 |
	|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 	| NULL         |   30000.00 |    401 |         1 |
	|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 	| NULL         |   10000.00 |    401 |         1 |
	|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    	| NULL         |    3000.13 |    402 |         2 |
	| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    	| NULL         |    2000.35 |    402 |         2 |
	| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    	| NULL         |    1000.37 |    402 |         2 |
	| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    	| NULL         |    3000.29 |    402 |         2 |
	| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    	| NULL         |    4000.33 |    402 |         2 |
	| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               	| NULL         |   10000.13 |    403 |         3 |
	| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               	 | NULL         |   20000.00 |    403 |         3 |
	| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                                	| NULL         |   19000.00 |    403 |         3 |
	| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               	 | NULL         |   18000.00 |    403 |         3 |
	| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               	 | NULL         |   17000.00 |    403 |         3 |
	+----+------------+--------+-----+------------+-------------------	----------------------+--------------+------------+--------+------	-----+
	18 rows in set (0.00 sec)

  • 简单查询

# 简单查询
	select * from 表名;
	select 字段名 from 表名;
	select 字段名1,字段名2... from 表名;

#去重 distinct
	select distinct 字段名 from 表面;
	
	#	示例
mysql> select distinct post from employee;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使                  |
| teacher                                 |
| sale                                    |
| operation                               |
+-----------------------------------------+
4 rows in set (0.00 sec)

#进行四则运算查询
	mysql> select emp_name,salary*12 from employee;

#定义显示格式 concat（）

	#concat() 用于连接字符串
	mysql> select concat('姓名：',emp_name,'、年薪：',salary*12) as  	Annual_salary from employee;
+------------------------------------------+
| Annual_salary                            |
+------------------------------------------+
| 姓名：egon、年薪：87603.96                 |
| 姓名：alex、年薪：12000003.72              |
| 姓名：wupeiqi、年薪：99600.00              |
|  ....                                    |
+------------------------------------------+
18 rows in set (0.00 sec)

	#concat_ws() 第一个参数作为分隔符
mysql> select concat_ws(':',emp_name,salary*12) as Annual_salary from employee;
+----------------------+
| Annual_salary        |
+----------------------+
| egon:87603.96        |
| alex:12000003.72     |
| wupeiqi:99600.00     |
|  ....                |
+----------------------+
18 rows in set (0.00 sec)

	#结合case语句
mysql> select (
    -> case
    -> when emp_name = '程咬金' then emp_name
    -> when emp_name = '成龙' then concat(emp_name,'最帅')
    -> else concat(emp_name,'---')
    -> end
    -> )new_name from employee;
+---------------+
| new_name      |
+---------------+
| egon---       |
|  ...          |
| jinxin---     |
| 成龙最帅      |
|  ....        |
| 程咬金        |
| 程咬银---     |
|  ...         |
+---------------+
18 rows in set (0.00 sec)

mysql> select concat('<',emp_name,':',salary,'>') from employee;
+-------------------------------------+
| concat('<',emp_name,':',salary,'>') |
+-------------------------------------+
| <egon:7300.33>                      |
| <alex:1000000.31>                   |
| <wupeiqi:8300.00>                   |
|  ...                                |
+-------------------------------------+
18 rows in set (0.00 sec)

  • where约束

where 字句中可以使用：
	1.比较运算符：
		< > >= <= <> !=
	2.between 80 and 100  -->(值在80到100之间)
	3.in(80,90,100)  -->(值是80或90或100)
	4.like ‘q%_’
		通配符可以是%或_,
		% 表示任意多个字符,
		_ 表示一个字符
	5.逻辑运算符：
		and  or  not

# 单条件查询
mysql> select emp_name from employee where post = 'sale';

# 多条件查询
mysql> select emp_name from employee where post = 'sale' and salary > 10000;

# between and
mysql> select emp_name,salary from employee where salary between 10000 and 20000;

mysql> select emp_name,salary from employee where salary not between 10000 and 20000;

# is null (判断某个字段是否为null不能用‘=’，需要用is)
mysql> select emp_name ,salary from employee where post_comment is NULL;

mysql> select emp_name ,salary from employee where post_comment is not null;

mysql> select emp_name ,salary from employee where post_comment = '';
Empty set (0.00 sec)

# in（集合查询）
mysql> select emp_name, salary from employee where salary = 3000 or salary =3500 or salary = 4500;

mysql> select emp_name ,salary from employee where salary in(3000,3500,4500);

mysql> select emp_name ,salary from employee where salary not in(3000,3500,4500);

# like（模糊查询）
mysql> select * from employee where emp_name like 'eg%';

mysql> select * from employee where emp_name like 'eg__';

  • group by

    • select * from 表 group by 字段名;
      • 分组 group by
      • 根据某个重复率比较高的字段进行
      • 去重
      • 一旦分组就不能对具体某一个数据进行操作了
      • group_concat ：只用来做最终的结果，不能做中间结果操作其他数据
# 使用group by 分组
mysql> select post from employee group by post;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| operation                               |
| sale                                    |
| teacher                                 |
| 老男孩驻沙河办事处外交大使                  |
+-----------------------------------------+
4 rows in set (0.01 sec)

# group by与group_concat()函数一起使用
mysql> select post ,group_concat(emp_name) from employee group by post;

# group by与聚合函数一起使用
mysql> select post,count(id) as count from employee group by post;
+-----------------------------------------+-------+
| post                                    | count |
+-----------------------------------------+-------+
| operation                               |     5 |
| sale                                    |     5 |
| teacher                                 |     7 |
| 老男孩驻沙河办事处外交大使                  |     1 |
+-----------------------------------------+-------+
4 rows in set (0.00 sec)

  • 聚合函数

    • 99.99%的情况都与分组group by一起使用
    • 如果没有和分组一起使用，默认一张表是一组
    • count(id) / count(*) 计数：每个组对应几条数据
    • max 求最大值，这个组中某字段的最大值
    • min 求最小值，这个组中某字段的最小值
    • avg 求平均值
    • sum 求和值
#示例

mysql> select min(hire_date) from employee;

mysql> select min(hire_date) from employee group by post;

mysql> select count(*) from employee;

mysql> select count(*) from employee where depart_id = 1;

mysql> select max(salary) from employee;

mysql> select min(salary) from employee;

mysql> select avg(salary) from employee;

mysql> select sum(salary) from employee;
练习 
   mysql> select post, group_concat(emp_name) from employee group by post
    -> ;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | group_concat(emp_name)                                  |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)


mysql> select post, count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select count(id) from employee group by sex;
+-----------+
| count(id) |
+-----------+
|        10 |
|         8 |
+-----------+
2 rows in set (0.00 sec)

mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select post,max(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | max(salary) |
+-----------------------------------------+-------------+
| operation                               |    20000.00 |
| sale                                    |     4000.33 |
| teacher                                 |  1000000.31 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+
4 rows in set (0.00 sec)

mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post                                    | avg(salary)   |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)

mysql> select post,min(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | min(salary) |
+-----------------------------------------+-------------+
| operation                               |    10000.13 |
| sale                                    |     1000.37 |
| teacher                                 |     2100.00 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+
4 rows in set (0.01 sec)

mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)

  • having（过滤）

    • 就是一个对组进行筛选的条件
    • having与where不一样的地方
      • 执行优先级不一样：where > group by > having
      • where发生在分组group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数
      • having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数
#示例
mysql> select * from employee where salary >10000;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | alex      | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin    | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
7 rows in set (0.00 sec)

#当根据post进行分组后，having就拿不到salary字段，可以使用聚合函数
mysql> select post,group_concat(emp_name) from employee group by post having salary >10000;
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

#使用聚合函数
mysql> select post,group_concat(emp_name) from employee group by post having sum(salary) >10000;
+-----------+---------------------------------------------------------+
| post      | group_concat(emp_name)                                  |
+-----------+---------------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale      | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher   | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
+-----------+---------------------------------------------------------+
3 rows in set (0.00 sec)

<details>
练习 
   mysql> select post ,group_concat(emp_name),count(id) from employee group by post having count(id)>2;
   +-----------+---------------------------------------------------------+-----------+
   | post      | group_concat(emp_name)                                  | count(id) |
   +-----------+---------------------------------------------------------+-----------+
   | operation | 张野,程咬金,程咬银,程咬铜,程咬铁                        |         5 |
   | sale      | 歪歪,丫丫,丁丁,星星,格格                                |         5 |
   | teacher   | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |         7 |
   +-----------+---------------------------------------------------------+-----------+
   3 rows in set (0.00 sec)

   mysql> select post,avg(salary) from employee group by post having avg(salary)>10000;
   +-----------+---------------+
   | post      | avg(salary)   |
   +-----------+---------------+
   | operation |  16800.026000 |
   | teacher   | 151842.901429 |
   +-----------+---------------+
   2 rows in set (0.00 sec)

   mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;
   +-----------+--------------+
   | post      | avg(salary)  |
   +-----------+--------------+
   | operation | 16800.026000 |
   +-----------+--------------+
   1 row in set (0.00 sec)

   mysql> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
   +-----------+--------------+
   | post      | avg(salary)  |
   +-----------+--------------+
   | operation | 16800.026000 |
   +-----------+--------------+
   1 row in set (0.01 sec)
 ```

 </details>


+ ### order by (查询排序)

```mysql
# 单列排序
order by 字段
order by 字段 asc
order by 字段 desc

#多列排序：先按照字段1排序，如果字段1相同，则按照字段2排序
order by 字段1,字段2
order by 字段1 asc,字段2 desc
order by 字段1 desc,字段2 asc
order by 字段1 desc,字段2 desc

#示例
mysql> select * from employee order by age,salary desc;

mysql> select * from employee order by age,hire_date;

mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary);
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)

mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
+-----------+---------------+
2 rows in set (0.00 sec)

  • limit (限制查询)

    • 显示分页
      • limit m,n
        • 表示从m+1开始,取n条
        • limit 0,6 表示从1开始取6条
        • limit 6,6 表示从7开始取6条
        • limit 12,6 表示从13开始取6条
        • limit 18,6 表示从19开始取6条
    • 取前n名
      • limit n （m默认为0）
    • limit n offset m :从m+1开始,取n条
# 示例 分页显示，每页5条
mysql> select * from employee limit 0,5;

mysql> select * from employee limit 5,5;

mysql> select * from employee limit 10,5;
posted on 2020-01-10 12:02  xingchenck  阅读(196)  评论(0)  编辑  收藏  举报

Copyright © 2024 xingchenck
Powered by .NET 9.0 on Kubernetes Powered By博客园