mysql 单表查询

mysql  单表查询

 

 

select distinct 字段1,字段2,字段3 from 库.表 
    where 条件
    group by 分组条件
    having 过滤
    order by 排序字段
    limit n;


1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数

 

一 简单查询

 

1 distinct 去重 

select distinct post from employee;

 

2 定义显示格式

CONCAT()  函数用于连接字符串
mysql> select concat('姓名:' ,name,' 年薪:',salary*12) as annual_salary from employee;
+------------------------------------+
| annual_salary                      |
+------------------------------------+
| 姓名:egon 年薪:87603.96            |
| 姓名:alex 年薪:12000003.72         |
| 姓名:wupeiqi 年薪:99600.00         |
| 姓名:yuanhao 年薪:42000.00         |

 

 

CONCAT_WS() 第一个参数为分隔符

mysql> select concat_ws(':',name,salary*12) as Annual_salary  from employee;
+----------------------+
| Annual_salary        |
+----------------------+
| egon:87603.96        |
| alex:12000003.72     |
| wupeiqi:99600.00     |

 

 

mysql> select name,salary*12 annual_salary from employee;
+------------+---------------+
| name       | annual_salary |
+------------+---------------+
| egon       |      87603.96 |
| alex       |   12000003.72 |



mysql> select concat("姓名:",name," 性别:" , sex)as info, concat( salary*12) as annual_salary  from employee;
+---------------------------------+---------------+
| info                            | annual_salary |
+---------------------------------+---------------+
| 姓名:egon 性别:male             | 87603.96      |
| 姓名:alex 性别:male             | 12000003.72   |
| 姓名:wupeiqi 性别:male          | 99600.00      |

 

二 where 约束

 

比较运算符 ： > <> = <= >= !=
between 80 and 100
in (10,20,30)
like "tes%"
逻辑运算符 and or not

 

in 

select * from employee where age in (73,78,28);
select * from employee where post_comment is NULL;
select * from employee where post_comment is  NOT NULL;
select * from employee where age  between 20 and 30;

 

不在20 到30之间

select * from employee where age  not  between 20 and 30;

 

like

select * from employee where  name like "jin%";

 

1. 查看岗位是teacher的员工姓名、年龄
select name,age from employee where post="teacher";

2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from employee where post="teacher" and age >30;

3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select name,age,salary from employee where salary between 9000 and 10000;

4. 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is NOT NULL;

5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post="teacher" and salary not in (10000,3000,30000);


6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post="teacher" and salary  in (10000,3000,30000);

7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,concat(salary *12)as annual_salary  from employee where name like "jin%";

 

 

三 分组查询：group by

 

 5.6 设置 ONLY_FULL_GROUP_BY

set global sql_mode='ONLY_FULL_GROUP_BY';

 

 退出mysql 重新登录

 

group_concat

1. 查询岗位名以及岗位包含的所有员工名字
select post ,group_concat(name) from employee group by post;

2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from employee group by post;

3. 查询公司内男员工和女员工的个数
select sex,count(id) from employee group by sex;

4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;

5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;

6. 查询岗位名以及各岗位的最低薪资
 select post,min(salary) from employee group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;

 

 

四 having过滤 

 

where 执行优先级

执行优先级从高到低：where > group by > having 

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee  group by post having count(id) <2;


2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;


3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary)< 20000;

 

五 查询排序：order by 

 

asc 升序
select * from employee order by age asc;

desc 降序
select * from employee order by age desc;

 

六 限制查询记录数limit

从第0位开始 5位
select * from employee limit 0,5;

 

 

七 使用正则表达式查询

select * from employee where name like "jin%";
select * from employee where name regexp "^jin";

jin 开头 g结尾
select * from employee where name regexp "^jin.*g$";