表查询

1，数据类型

• 查询语法：

  select 字段1，字段2….from 表名

  ​ where 条件

  ​ group by 字段1

  ​ having 筛选

  ​ order by 默认升序

  ​ limit 限制条数

• 关键字的执行优先级

  • 重点中的重点：关键字的执行优先级
    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的结果限制显示条数
    

• 简单查询

  • select * from 表名

  • distinct 去重，必须放在要去重的字段前面

  • 四则运算 字段的四则运算

  • concat 定义显示格式

    • concat() 函数用于连接字符串
    • concat_ws() 括号中的第一个参数为分隔符

  • #简单查询
        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 约束

  • 比较运算符：大于小于不等于

  • between 10 and 20 值在10 到20之间

  • in（10,20,30）值是或

  • like ‘da%’，%表示任意多字符

  • like ‘da_’，表示一个字符，要几个字符就加几个_

  • 逻辑运算符：在多个条件直接可以使用逻辑运算符 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__';
    

• group up:分组查询

  • 可以按照任意字段分组，但分完组后，只能查看分组的那个字段，要想获取其他字段信息，需要借助函数

  • 单独使用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;#按照岗位分组，并查看每个组有多少人
    

• having：筛选

  • having和where语法上是一样的

    • select * from employee where id>15;    
      select * from employee having id>15;
      

  • 不同点：

    • 执行优先级：where--group by —聚合函数---having----order by
    • where 是一个约束条件，使用where约束来自数据库的数据，where是在返回结果之前起作用的，where中不能使用聚合函数
    • having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，having中可以使用聚合函数
    • where的优先级比having的优先级高
    • having可以放到group by之后，而where只能放到group by之前

• ​ order by：查询排序

  • 单列排序

    •     SELECT * FROM employee ORDER BY salary;
          SELECT * FROM employee ORDER BY salary ASC;	#升序
          SELECT * FROM employee ORDER BY salary DESC;	#降序
      

  • 多列排序

    •     SELECT * from employee
              ORDER BY age,
              salary DESC;
      

• 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;
    

• 聚合函数

  • 示例：
        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;
    

2，多表连接查询

• #建表
  create table department(
  id int,
  name varchar(20) 
  );
  
  create table employee1(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
  );
  
  #插入数据
  insert into department values
  (200,'技术'),
  (201,'人力资源'),
  (202,'销售'),
  (203,'运营');
  
  insert into employee1(name,sex,age,dep_id) values
  ('egon','male',18,200),
  ('alex','female',48,201),
  ('wupeiqi','male',38,201),
  ('yuanhao','female',28,202),
  ('liwenzhou','male',18,200),
  ('jingliyang','female',18,204)
  ;
  

• 交叉连接：不适用任何匹配条件，生成笛卡尔积

  • select * from employee1,department where employee1.dep_id=department.id;
    

• 内连接：找到两张表中共有的部分，只连接匹配的行

  • #上面用where表示的可以用下面的内连接表示，建议使用下面的那种方法
    select * from employee1 inner join department on employee1.dep_id=department.id;
    

• 左连接：优先显示左表全部记录

  • #左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录
    select * from employee1 left join department on department.id=employee1.dep_id;
    
    select * from department left join  employee1 on department.id=employee1.dep_id;
    

• 右连接：优先显示右表全部记录

  • #右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录
    select * from employee1 right join department on department.id=employee1.dep_id;
    
    select * from department right join employee1 on department.id=employee1.dep_id;
    

• 全外连接：显示两个表的全部记录

  • mysql不支持full join，可以使用union间接实现全外连接

  • select * from employee1 left join department on department.id=employee1.dep_id
    union
    select * from employee1 right join department on department.id=employee1.dep_id;
    

• 左连接，右连接，没有匹配的用null填充

• 符合条件连接查询

  • 示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出公司所有部门中年龄大于25岁的员工
    select * from employee1 inner join department on employee1.dep_id=department.id and age>25;
    

    示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
    select * from employee1 inner join department on employee1.dep_id=department.id =and age>25 and age>25 order by age asc;
    

• 子查询

  • #1：子查询是将一个查询语句嵌套在另一个查询语句中。
    #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
    #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
    #4：还可以包含比较运算符：= 、 !=、> 、<等
    

• select语句关键字的定义顺序

  • select

  • distinct

  • from

  • join

  • on

  • where

  • group by

  • having

  • order by

  • limit

  • SELECT DISTINCT <select_list>
    FROM <left_table>
    <join_type> JOIN <right_table>
    ON <join_condition>
    WHERE <where_condition>
    GROUP BY <group_by_list>
    HAVING <having_condition>
    ORDER BY <order_by_condition>
    LIMIT <limit_number>
    
    SELECT语句关键字的定义顺序