补充:concat
# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法并用 select concat(\'Name: \', name) as \'名字\', concat(\'Sal: \', salary) as \'薪资\' from emp; mysql> select concat(\'name: \', name) as \'名字\',concat(\'sal: \',salary) as \'薪资\' from emp; +-----------------+-----------------+ | 名字 | 薪资 | +-----------------+-----------------+ | name: tank | sal: 7300.33 | | name: egon | sal: 1000000.31 | | name: kevin | sal: 8300.00 | | name: jason | sal: 3500.00 | | name: owen | sal: 2100.00 | | name: jerry | sal: 9000.00 | | name: 大饼 | sal: 30000.00 | | name: sean | sal: 10000.00 | | name: 歪歪 | sal: 3000.13 | | name: 丫丫 | sal: 2000.35 | | name: 丁丁 | sal: 1000.37 | | name: 星星 | sal: 3000.29 | | name: 格格 | sal: 4000.33 | | name: 张野 | sal: 10000.13 | | name: 程咬金 | sal: 20000.00 | | name: 程咬银 | sal: 19000.00 | | name: 程咬铜 | sal: 18000.00 | | name: 程咬铁 | sal: 17000.00 | | name: o | NULL | +-----------------+-----------------+ 19 rows in set (0.00 sec) 3.having(过滤) 1.having与where语法一样，只不过having必需要在group by后使用; 2.where 不能使用聚合函数，但having可以; - 书写顺序: - select - from - where - group by - having - 执行顺序: - from - where - group by - having - select 1、统计各 部门 年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门; select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000; mysql> select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000; +---------+---------------+ | post | avg(salary) | +---------+---------------+ | teacher | 255450.077500 | +---------+---------------+ 1 row in set (0.00 sec) 4.distinct(去重) # distinct: 去重 - 书写顺序: - select - distinct - from - where - group by - having 执行顺序: - from - where - group by - having - select - distinct # 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。 # 若所查字段内有不重复的字段记录就不会去重 select distinct id, post from emp; mysql> select distinct id, post from emp; +----+-----------------------------------+ | id | post | +----+-----------------------------------+ | 1 | 张江第一帅形象代言部门 | | 2 | teacher | | 3 | teacher | | 4 | teacher | | 5 | teacher | | 6 | teacher | | 7 | teacher | | 8 | teacher | | 9 | sale | | 10 | sale | | 11 | sale | | 12 | sale | | 13 | sale | | 14 | operation | | 15 | operation | | 16 | operation | | 17 | operation | | 18 | operation | | 19 | NULL | +----+-----------------------------------+ 19 rows in set (0.00 sec) select distinct post from emp; mysql> select distinct post from emp; +-----------------------------------+ | post | +-----------------------------------+ | 张江第一帅形象代言部门 | | teacher | | sale | | operation | | NULL | +-----------------------------------+ 5 rows in set (0.00 sec) 5.order by(排序) - 书写顺序: - select - from - where - group by - having - order by 执行顺序: - from - where - group by - having - select - order by # 通过select 查出来的数据再进行排序 # order by 默认升序 # asc升序 # desc降序 # 1、根据薪资进行升序 select name, salary from emp order by salary; # 默认升序 mysql> select name, salary from emp order by salary; +-----------+------------+ | name | salary | +-----------+------------+ | o | NULL | | 丁丁 | 1000.37 | | 丫丫 | 2000.35 | | owen | 2100.00 | | 歪歪 | 3000.13 | | 星星 | 3000.29 | | jason | 3500.00 | | 格格 | 4000.33 | | tank | 7300.33 | | kevin | 8300.00 | | jerry | 9000.00 | | sean | 10000.00 | | 张野 | 10000.13 | | 程咬铁 | 17000.00 | | 程咬铜 | 18000.00 | | 程咬银 | 19000.00 | | 程咬金 | 20000.00 | | 大饼 | 30000.00 | | egon | 1000000.31 | +-----------+------------+ 19 rows in set (0.00 sec) 2.select * from emp order by salary asc; # 指定升序 3.select name, salary from emp order by salary desc; # 指定降序 mysql> select name, salary from emp order by salary desc; +-----------+------------+ | name | salary | +-----------+------------+ | egon | 1000000.31 | | 大饼 | 30000.00 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | | 张野 | 10000.13 | | sean | 10000.00 | | jerry | 9000.00 | | kevin | 8300.00 | | tank | 7300.33 | | 格格 | 4000.33 | | jason | 3500.00 | | 星星 | 3000.29 | | 歪歪 | 3000.13 | | owen | 2100.00 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | o | NULL | +-----------+------------+ 19 rows in set (0.00 sec) # 先按照age升序，再按照salary降序 select age, salary from emp order by age asc, salary desc; # 统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序 select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary); 9.limit(限制查询记录的数量) 书写顺序: - select - from - order by - limit 执行顺序: - from - select - order by - limit # 1、从第一条开始，获取4条记录; select * from emp limit 4; mysql> select * from emp limit 4; +----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ | 1 | tank | male | 17 | 2017-03-01 | 张江第一帅形象代言部门 | NULL | 7300.33 | 401 | 1 | | 2 | egon | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | +----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) # 2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数; select * from emp limit 0, 4; select * from emp limit 4, 4; mysql> select * from emp limit 4, 4; +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | 大饼 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | sean | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 4 rows in set (0.00 sec) # 3、查询工资最高的人的详细信息 select * from emp order by salary limit 1; mysql> select * from emp order by salary limit 1; +----+------+------+-----+------------+------+--------------+--------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+------+--------------+--------+--------+-----------+ | 19 | o | male | 28 | 0000-00-00 | NULL | NULL | NULL | NULL | NULL | +----+------+------+-----+------------+------+--------------+--------+--------+-----------+ 1 row in set (0.00 sec) 10.正则 # 在编程中，凡是看到reg开头的，基本上都是跟正则有关 # *: 代表 0 或 多个 select * from emp where name regexp \'^程.*(金|银|铜|铁)$\'; mysql> select * from emp where name regexp \'^程.*(金|银|铜|铁)$\'; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 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 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 4 rows in set (0.00 sec) 三.多表查询多表查询
-关联查询
-子查询