MySQL尚让_mysql基础

from

where

group by

having

select

distinct

order by

limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

(1)where约束

比较运算符：>、=、<=、<>、!=

select id, name,age from employee where id>5;

+----+------------+-----+

| id | name | age |

+----+------------+-----+

| 6 | jingliyang | 18 |

| 7 | jinxin | 18 |

| 8 | xiaomage | 48 |

| 9 | 歪歪 | 48 |

| 10 | 丫丫 | 38 |

| 11 | 丁丁 | 18 |

| 12 | 星星 | 18 |

| 13 | 格格 | 28 |

| 14 | 张野 | 28 |

| 15 | 程咬金 | 18 |

| 16 | 程咬银 | 18 |

| 17 | 程咬铜 | 18 |

| 18 | 程咬铁 | 18 |

+----+------------+-----+

关键字between and

select id, name,age from employee where salary between 10000 and 50000;

+----+-----------+-----+

| id | name | age |

+----+-----------+-----+

| 7 | jinxin | 18 |

| 8 | xiaomage | 48 |

| 14 | 张野 | 28 |

| 15 | 程咬金 | 18 |

| 16 | 程咬银 | 18 |

| 17 | 程咬铜 | 18 |

| 18 | 程咬铁 | 18 |

+----+-----------+-----+

关键字in集合查询

select id, name,age from employee where age in(18,28);

+----+------------+-----+

| id | name | age |

+----+------------+-----+

| 1 | egon | 18 |

| 5 | liwenzhou | 28 |

| 6 | jingliyang | 18 |

| 7 | jinxin | 18 |

| 11 | 丁丁 | 18 |

| 12 | 星星 | 18 |

| 13 | 格格 | 28 |

| 14 | 张野 | 28 |

| 15 | 程咬金 | 18 |

| 16 | 程咬银 | 18 |

| 17 | 程咬铜 | 18 |

| 18 | 程咬铁 | 18 |

+----+------------+-----+

关键字like模糊查询

通配符%表示人任意多字符

通配符_表示一个字符

select id, name,age from employee where name like 'j%';

+----+------------+-----+

| id | name | age |

+----+------------+-----+

| 6 | jingliyang | 18 |

| 7 | jinxin | 18 |

+----+------------+-----+

逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

select id, name,age from employee where name like 'j%' and sex='female';

+----+------------+-----+

| id | name | age |

+----+------------+-----+

| 6 | jingliyang | 18 |

+----+------------+-----+

(2)group by分组查询

为什么要分组?

取每个部门的最高工资

取每个部门的员工数

取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

select * from employee group by post;

这句代码得到的是每组中的第一条数据,没有任何意义,因为我们现在想查的是每组的多条数据

由于没有设置ONLY_FULL_GROUP_BY,默认都是组内的第一条记录

如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY

set global sql_mode='ONLY_FULL_GROUP_BY';

或者将ONLY_FULL_GROUP_BY添加到my,ini中

设置成功后，一定要退出，然后重新登录方可生效

如果想查看组内信息，需要借助于聚合函数

max()求最大值

min()求最小值

avg()求平均值

sum() 求和

count() 求总个数

select post,count(id),max(salary) from employee group by post;

+-----------------------------------------+-----------+-------------+

| post | count(id) | max(salary) |

+-----------------------------------------+-----------+-------------+

| operation | 5 | 20000.00 |

| sale | 5 | 4000.33 |

| teacher | 7 | 1000000.31 |

| 老男孩驻沙河办事处外交大使 | 1 | 7300.33 |

+-----------------------------------------+-----------+-------------+

(3)having过滤

Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

group_concat()函数

必须使用group by才能使用此函数

练习:

查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

mysql> select post,group_concat(name),count(1) from employee group by post having count(1)<2;

+-----------------------------------------+--------------------+----------+

| post | group_concat(name) | count(1) |

+-----------------------------------------+--------------------+----------+

| 老男孩驻沙河办事处外交大使 | egon | 1 |

+-----------------------------------------+--------------------+----------+

查询各岗位平均薪资大于10000的岗位名、平均工资

select post,avg(salary) from employee group by post having avg(salary)>10000;

+-----------+---------------+

| post | avg(salary) |

+-----------+---------------+

| operation | 16800.026000 |

| teacher | 151842.901429 |

+-----------+---------------+

查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;

+-----------+--------------+

| post | avg(salary) |

+-----------+--------------+

| operation | 16800.026000 |

+-----------+--------------+

(4)order by查询排序

asc 升序

desc 降序

例:先按照age升序排序，如果年纪相同，则按照id降序

select * from employee order by age asc,id desc;

查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

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 |

+-----------+---------------+

(5)限制查询的记录数

第一个参数起始位置，第二个参数显示的个数

select * from employee limit 3;如果只有一个参数,默认第一个参数是0

select * from employee limit 3,4;