Day 70

_分组查询

1. 分组查询语法

   • SELECT 字段名称 FROM [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
     

   • 注意：分组之后，查询的字段为聚合函数的分组字段，查询其他字段无任何意义

2. WHERE 和 HAVING 区别：

   • 执行时机不一样：where 是分组之前进行限定，不满足WHERE条件，不参与分组，而HAVING 是分组之后对结果进行过滤。
   • 可判断的条件不一样：WHERE 不能对聚合函数进行判断，HAVING可以
   • 执行顺序：WHERE > 聚合函数 > HAVING

3. /*
   1. 分组查询语法
   
      - ```SQL
        SELECT 字段名称 FROM [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
      - 注意：分组之后，查询的字段为聚合函数的分组字段，查询其他字段无任何意义
   
   2. WHERE 和 HAVING 区别：
   
      - 执行时机不一样：where 是分组之前进行限定，不满足WHERE条件，不参与分组，而HAVING 是分组之后对结果进行过滤。
      - 可判断的条件不一样：WHERE 不能对聚合函数进行判断，HAVING可以
      - **执行顺序**：WHERE > 聚合函数 > having
        */
   
   -- 需求：查询表employees中不同部门员工的平均工资
   select department_id  ,round( avg(salary)  , 1)'平均工资'
   from employees e 
   group by department_id ;
   
   -- 需求：查询表employees中不同部门员工的平均工资，并按照部门id降序排列
   select department_id , round( avg(salary))'平均工资' 
   from employees e 
   group by department_id 
   order by department_id desc ;
   
   -- 需求：需求：查询表employees中不同部门员工的平均工资，并按照部门id降序排列，并且保留平均工资大于7000的部门
   /*
   
    * 2. WHERE 和 HAVING 区别：
   
      - 执行时机不一样：where 是分组之前进行限定，不满足WHERE条件，不参与分组，而HAVING 是分组之后对结果进行过滤。
      - 可判断的条件不一样：WHERE 不能对聚合函数进行判断，HAVING可以
      - **执行顺序**：WHERE > 聚合函数 > having
        */
        select  department_id ,round (avg(salary))'平均工资'
        from employees e 
        group by department_id  
        having avg(salary)>7000 
        order by department_id desc ;
   

_DQL 分页查询

1. 分页查询语法：

   • SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数
     

   • 注意：起始索引---->从零开始

   • *计算公式：起始索引 = （当前页码 - 1）每页显示的条数

   • tips：

     • 分页查询limit 是MySQL数据库的方言
     • Oracle 分页查询使用的是rownumber
     • SQL Server 分页查询使用的是top

2. /*
   分页查询语法：
   
     SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数
   - 注意：起始索引---->从零开始
   
   - **计算公式：起始索引 = （当前页码 - 1）*每页显示的条数**
   
   - tips：
   
     - 分页查询limit 是MySQL数据库的方言
     - Oracle 分页查询使用的是rownumber
     - SQL Server 分页查询使用的是top
       */
       select *
       from employees e limit 0,20; 
   
   -- 需求：分页查询，每页20条数据，查询第三页，查询到的页面按工资从高到低排序
   select *
   from employees e 
   order by salary desc
   limit 40,20;
   

_约束、约束案例

1. 约束名称	描述	关键字
   非空约束	保证列中所有数据不能有null值	NOT NULL
   唯一约束	保证列中所有数据各不相同	UNIQUE
   主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
   检查约束	保证列中的值满足某一条件	CHECK
   默认约束	保存数据时，未指定值则采用默认值	DEFAULT
   外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY

2. /*
   | 约束名称 | 描述                                                         | 关键字      |
   | -------- | --------------------------------------------------------- | --------- |
   | 非空约束 | 保证列中所有数据不能有null值                                       | NOT NULL    |
   | 唯一约束 | 保证列中所有数据各不相同                                          | UNIQUE      |
   | 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一                               | PRIMARY KEY |
   | 检查约束 | 保证列中的值满足某一条件                                           | CHECK       |
   | 默认约束 | 保存数据时，未指定值则采用默认值                                     | DEFAULT     |
   | 外键约束 | 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性                 | FOREIGN KEY |
    */
   
   drop table if exists student;
   -- 学生表
   create table student(
   -- id为主键约束
   	id int primary key,
   
   -- 姓名为非空约束
   	name varchar(20) not null,
   
   -- 实习薪资salary，要求为非空
   	salary int not null ,
   
   -- 奖金 bouns ，要求为默认约束
   	bouns double(8,2) default 0
   
   );
   show tables;
   
   insert into student (id , name , salary , bouns)  values(001,'Alex',7000,500) ;
   insert into student (id , name , salary , bouns)  values(002,'Bob',7000,500) ;
   
   select * from student s ;
   
   
   

_约束、外键约束

1. 外键约束：

   • 概念：外键用来让两个表的数据之间建立连接，保证数的一致性和完整性

2. 语法：

   • 添加约束

     • -- 创建表时添加外键约束
       CREATE TABLE 表名(
       	列名 数据类型
           ...
           CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
       );
       

     • -- 建完表后添加外键约束
       ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES (主表名称);
       

   • 删除约束

     • ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
       

3. /*
   添加约束：
   	-- 创建表时添加外键约束
   CREATE TABLE 表名(
   	列名 数据类型
       ...
       CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
   );
   	-- 建完表后添加外键约束
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES (主表名称);
   
    删除外键
   	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
   */
   
   drop table if exists student;
   drop table if exists student_class;
   
   create table student (
   	id int primary key ,
   	name varchar(12) not null,
   	age int not null,
   	class int not null
   );
   
   create table student_class(
   	id int primary key,
   	address varchar(20) not null
   );
   -- 添加外键
   alter table student add constraint fk_class foreign key (class) references student_class(id);
   
   -- 删除外键
   alter table student drop foreign key fk_class;