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 100in (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$";

 

 

 

转载于:https://www.cnblogs.com/augustyang/p/11079174.html