MySQL（DQL部分）

MySQL（DQL）

基础查询

SELECT
	字段列表(字段1，字段2，字段3)
FROM 
	表明列表(表1，表2，表3)
WHERE
	条件列表
GROUP BY
	分组列表
HAVING
	分组之后的条件
ORDER BY
	排序
LIMIT
	分页限定

一、条件查询

运算符

• 常用于WHERE子句后面的条件

• < , > ,<=,>=,=,<>

• BETWEEN...AND...

• IN(集合)

• LIKE

  • 占位符
    • _：单个任意字符
    • %：多个任意字符

• IS NULL

• AND 或 &&

• OR 或 ||

• NOT 或 !

二、排序查询

• 语法：ORDER BY 子句

  • ORDER BY 排序字段1 排序方式1，排序字段2 排序方式2
  • 排序方式：
    • ASC：升序，默认的
    • DESC：降序
  • 注意：如果有多个排序条件，则当前边的条件一样时，才会判断第二个条件

• 聚合函数

  • COUNT
    • 一般选择非空的列：主键
    • COUNT(*)
  • MAX、MIN、SUM、AVG
  • 注意：聚合函数的计算排除NULL值
    • 可选择不包含非空的列进行计算
    • 可使用IFNULL函数

三、分组查询

• 语法：GROUP BY 分组字段
• 注意
  • 分组之后查询的字段最好只包括分组字段、聚合函数
    • WHERE和HAVING的区别
    • WHERE在分组之前限定，如果不满足条件，则不参与分组及查询；HAVING在分组之后限定，如果不满足条件，则不查询
    • WHERE子句不能跟聚合函数，HAVING可以进行聚合函数的判断

四、分页查询

• 语法：LIMIT开始的索引，每页查询的条数

• 公式：开始的索引 = (当前页码 - 1) * 每页显示的条数

  • 每页显示3条记录

    • SELECT * FROM table_name LIMIT 0, 3  -- 第一页
      SELECT * FROM table_name LIMIT 3, 3  -- 第二页
      SELECT * FROM table_name LIMIT 6, 3  -- 第三页
      

五、多表查询

• 笛卡尔积

  • 有两个集合A、B，取这两个集合组成的所有情况
  • 要完成多表查询，需要消除无用的数据查询

• 多表查询

  • 目的：消除无用的数据查询

  • 分类：

    • 内连接查询（查询交集部分）

      • 隐式内连接：使用WHEWE消除无用数据

        • SELECT
          	t1.*,
          	t2.department
          FROM
          	t_user t1,  -- 从表
          	t_department t2 -- 主表
          WHERE 
          	t1.dpt_id = t2.id;  
          	-- t1.dpt_id为从表的外键，t2.id为主表的主键
          

      • 显式内连接

        • SELECT
          	t1.*,
          	t2.department
          FROM
          	t_user t1
          INNER JOIN 
          	t_department t2
          ON
          	t1.dpt_id = t2.id
          
          

    • 外连接查询（查询交集部分及LEFT左表/RIGHT右表NULL部分）

      • 左连接：LEFT JOIN 查询左表t1所有内容以及t1和t2的交集内容

        • SELECT
          	t1.*,
          	t2.department
          FROM
          	t_user t1
          LEFT JOIN 
          	t_department t2
          ON
          	t1.dpt_id = t2.id
          

      • 右连接：将上面的LEFT JOIN改为RIGHT JOIN ，查询右表t2所有内容以及t1和t2的交集内容

    • 子查询

      • 概念：查询中嵌套查询，将嵌套的查询结果作为条件或虚拟表。

      • 子查询结果是单行单列：可作为条件，使用<、>、=等运算符判断

        • SELECT
          	* 
          FROM
          	t_uesr 
          WHERE
          	t_uesr.money > ( SELECT AVG( USER.money ) FROM t_uesr );
          

      • 子查询结果是单行多列：可作为条件，使用IN运算符判断

        • SELECT
          	* 
          FROM
              t_uesr 
          WHERE
          	t_uesr.dpt_id 
          IN 
          	(SELECT t_department.id 
               FROM t_department 
               WHERE t_department.department 
               IN ( ‘技术部‘, ‘销售部‘ ));
          

      • 子查询结果是多行多列：可作为一张虚表参与查询

        • SELECT
          	* 
          FROM
          	t_uesr t1,
          	( SELECT * FROM t_department WHERE department.id <= 2 ) t2 
          WHERE
          	t1.dpt_id = t2.id;
          

约束

• 概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性

• 分类：

  • 主键约束：primary key
  • 非空约束：not null
  • 唯一约束：unique
  • 外键约束：foreign key

• 唯一约束

  • 注意：唯一约束可以有null值，但是只能有一条记录为null

  • 在创建表时，添加唯一约束

    • CREATE TABLE test ( id INT, phone VARCHAR ( 11 ) UNIQUE );
      

  • 删除唯一约束

    • ALTER TABLE test DROP INDEX phone;
      

  • 创建表后，添加唯一约束

    • ALTER TABLE test  MODIFY phone VARCHAR(11) UNIQUE;
      

• 主键约束

  • 注意：

    • 含义：非空且唯一
    • 一张表只能有一个字段为主键
    • 主键即表中记录的唯一标识

  • 建表时，添加主键

    • CREATE TABLE test ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR ( 11 );
      

  • 删除主键

    • ALTER TABLE test DROP PRIMARY KEY;
      

  • 建表后，添加主键

    • ALTER TABLE test MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
      

• 外键约束

  • 注意：

  • 建表时，添加外键

    • CREATE TABLE 表名(...
      			外键列，
      			constraint 外键名称 foreign key （外键列名称）references 主表名称(主表列名称))；
      

  • 删除外键

    • ALTER TABLE 表名 DROP foreign key 外键列名称；
      

  • 建表后添加外键

    • ALTER TABLE 表名 ADD constraint 外键名称 foreign key （外键列名称）references 主表名称(主表列名称);
      

  • 

  • 级联操作

    • 主表的更新引起子表的更新

    • 语法

      • ALTER TABLE 
        	表名 ADDconstraint 外键名称 foreign key （外键列名称）references 主表名称(主表列名称)  
        ON UPDATE CASCADE ON DELETE CASCADE
        

    • 分类

      • 级联更新：ON UPDATE CASCADE
      • 级联删除(危险)：ON DELETE CASCADE

数据库设计

多表之间的关系

• 分类

  • 一对一：人和身份证
  • 一对多（多对一）：部门和员工
  • 多对多：学生和课程，一个学生可以选择多门课程，一门课程可被多个学生选择

• 实现关系：

  • 一对一：

    • 可以在任意一方添加唯一外键指向另一方的主键，一般来说，一对一直接放在一张表处理就可以了

  • 一对多（多对一）：

    • 如果部门和员工
    • 实现方式：在多的一方创建外键，指向一的一方的主键

  • 多对多：

    • 学生和课程

      多对多关系的实现需要借助第三张表，该表至少包含两个字段，这两个字段最为第三张表的外键，分别指向两张表的主键

• 数据库设计的范式

  • 概念：设计数据库时，遵循的一些规范。目前关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF)、第三范式（3NF)、巴斯-科德范式（BCNF）、第四范式（4NF)、第五范式（5NF)，越高的范式数据库冗余越小
  • 第一范式：每一列都是不可分割的原子数据项
  • 第二范式：在第一范式的基础上，消除非主属性对主码的部分函数依赖
  • 第三范式：在第二范式的基础上，消除传递依赖
  • 依赖的概念
    • 函数依赖：通过A属性/属性组的值，可以确定唯一B的属性值，则称B依赖于A
    • 完全函数依赖：如果A是一个属性组，则B属性值的确定需要完全依赖A属性组中所有的值
    • 部分函数依赖：果A是一个属性组，则B属性值的确定只需要完全依赖A属性组中某些的值
    • 传递函数依赖：B依赖于A，C依赖于B，则称C传递函数依赖于A
    • 码：在一张表中，一个属性/属性组，被其他所有属性完全依赖，则称这个属性/属性组为码
      • 主属性：码属性组中所有的属性
      • 非主属性：除去码属性组的中属性的所有属性

MySQL（DQL部分）

标签：join   计算   多行   级联删除   not   排序   char   查询   upd