单表操作

 

查询语法：

SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

　　

简单查询：

#简单查询
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;

三、where约束：

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是80或90或100
4. like 'eg%'
    可以是%或_，
    %表示任意多字符
    _表示一个字符

　like 'e__n' :
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not 

示例：

　

#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';
        
#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

　四、过滤：

　　

having和where语法上是一样的。
select * from employee where id>15;    
select * from employee having id>15;  

having和where的区别

#！！！执行优先级从高到低：where > group by > 聚合函数 > having >order by
1.where和having的区别                                                                                
     1. Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的                                        
     （先找到表，按照where的约束条件，从表(文件)中取出数据），Where中不能使用聚合函数                                              
     2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作                                                     
     （先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组，                                                
      如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），                                     
      在Having中可以使用聚合函数。                                                                          
     3.where的优先级比having的优先级高                                                                     
     4.having可以放到group by之后，而where只能放到group by 之前。   

 

验证不同之处示例：

1.查看员工的id>15的有多少个
select count(id) from employee where id>15;#正确，分析：where先执行，后执行聚合count(id),
                                            然后select出结果
select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤，
                                            #无法对id进行id>15的过滤
#以上两条sql的顺序是
1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目
2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了）
进行id>15的过滤，很明显，根本无法获取到id字段

　　

1 ------having-----------
2 select depart_id,count(id) from employee group by depart_id;
3 select depart_id,count(id) from employee group by depart_id having depart_id = 3;
4 select depart_id,count(id) from employee group by depart_id having count(id)>7;
5 select max(salary) 最大工资 from employee where id>2 group by depart_id having count(id)>3;
6 select * from employee where id>7; #查看所有id>7的员工信息

　练习：

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) between 10000 and 20000;

五、分组查询 group by

　要分组必须满足：可以按照任意字段分组,但分完组后，只能查看分组的那个字段，要想取的组内的其他字段信息，需要借助函数

单独使用GROUP BY关键字分组
    select post from employee group by post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和group_concat()函数一起使用
      select post,group_concat(name) from  employee group by post;#按照岗位分组，并查看组内成员名
      select  post,group_concat(name) as emp_members FROM employee group by post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

注意：一般相同的多的话就可以分成一组（一定是有重复的字段）

练习：

 

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,max(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;
复制代码

　　

六、关键字的执行优先级(重点)

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

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

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

4.如果有聚合函数，则将组进行聚合

5.将4的结果过滤：having

6.查出结果：select

7.去重

8.将6的结果按条件排序：order by

9.将7的结果限制显示条数

七、查询排序order by 

　

按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;


===========order by==========
1.select * from employee order by salary;#如果不指定，默认就是升序
2.select * from employee order by salary asc;
3.select * from employee order by salary desc;

#先按照年龄升序，当年龄相同的太多，分不清大小时，在按照工资降序
4.select * from employee order by age asc, salary desc;

　例子：

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * form employee order by age,hire_date desc;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post ,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post ,avg(salary) from employee group by post having avg(salary)>10000 desc;

八、使用聚合函数查询

　　　　先from找到表

　　　　再用where的条件约束去表中取出记录

　　　　然后进行分组group by，没有分组则默认一组

　　　　然后进行聚合

　　　　最后select出结果

示例：
    select count(*) from employee;
    select count(*) from employee where depart_id=1;
    select max(salary) from employee;
    select min(salary) from employee;
    select avg(salary) from employee;
    select sum(salary) from  employee;
    select sum(salary) form employee WHERE depart_id=3;

九、where的补充（使用正则表达式查询）　　

1.select * from employee where name regexp '^ale';  #匹配以ale开头的员工信息
2.select * from employee where name regexp 'on$'; #匹配以on结尾的员工信息
3.select * from employee where name regexp 'n{1,2}'; #匹配name里面包含1到2个n的员工信息
小结：对字符串匹配的方式
where name = 'egon';
where name like 'yua%';
where name regexp 'on$';

练习：

查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp '^jin.*[ng]$';

十、限制查询的记录数：LIMIT

=========limit：限制打印几条=========
1.select * from employee limit 3;#打印前三条
2.像这样表示的：指的是从哪开始，往后取几条 (这样的操作一般用来分页)
select * from employee limit 0,3;
select * from employee limit 3,4;
select * from employee limit 6,3;
select * from employee limit 9,3;
3.select * from employee order by id desc limit 3; #查看后三条

　小练习：

1. 分页显示，每页5条
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;