MySQL基础（二）

DQL查询语句
排序查询
语法格式

ORDER BY 子句;
ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2...

排序方式
ASC 升序排列 如果不书写排序方式，默认按照ASC的方式进行排序。
DESC 降序排列

#按照MATH列对数据进行排序(升序)
SELECT * FROM 表名 ORDER BY math（列名） ASC;

#按照MATH列对数据进行排序(降序)
SELECT * FROM 表名 ORDER BY math（列名） DESC;

#按照MATH列队数据进行排序,如果MATH列数据相同,按照ENGLISH列队数据进行排序(升序)
SELECT * FROM 表名 ORDER BY math（列名） ASC ,english ASC;

注意事项：如果有多个排序条件,需要当第一条件排序的结果相同,才会按照第二条件进行排序.

聚合函数
count 查询数量
语法

SELECT COUNT(条件) FROM 表名;

#查询student表中的NAME（列名）数据非NULL的条数.
SELECT COUNT(name) FROM student;

#查询student表中的ENGLISH（列名）数据非NULL的条数.
SELECT COUNT(english) FROM student;

MySQL是忽略NULL值的，不会把NULL记录为有效数据。

#查询student表中的ENGLISH的条数,如果为NULL,按照0计算.
SELECT COUNT(IFNULL(english,0)) FROM student;

max 查询最大值
语法

SELECT MAX(条件) FROM 表名;

#查询MATH列的最大值
SELECT MAX(math) FROM student;

min 查询最小值
语法

SELECT MIN(条件) FROM 表名;

#查询MATH列的最小值
SELECT MIN(math) FROM student;

sum 计算总和
语法

SELECT SUM(条件) FROM 表名;

#查询MATH列的总和
SELECT SUM(math) FROM student;

avg 计算平均值
语法

SELECT AVG(条件) FROM 表名;

#查询MATH列的平均值
SELECT AVG(math) FROM student3;

总结:
聚合函数是将一列作为一个整体去进行纵向的计算的.
COUNT计算个数一般是选择非NULL的列进行计算的

分组查询
语法格式

GROUP BY 分组字段;

注意事项：
使用分组查询的时候，查询的字段应该是聚合函数或者是分组字段。

#查询MATH列的平均分(按照SEX进行分组)
SELECT AVG(math),sex FROM student GROUP BY sex;

#查询ENGLISH列的平均分,COUNT信息(按照SEX进行分组)
SELECT COUNT(IFNULL(english,0)),AVG(english) FROM student GROUP BY sex;

#查询ENGLISH列的平均分,人数(分数低于70分不参与运算,按照SEX进行分组)
SELECT COUNT(IFNULL(english,0)),AVG(english) FROM student3 WHERE english>70 GROUP BY sex;

#查询ENGLISH列的平均分,人数(分数低于70分不参与运算,按照SEX进行分组,分组之后只显示人数大于2人的数据)
SELECT COUNT(IFNULL(english,0)),AVG(english) FROM student3 WHERE english>70 GROUP BY sex HAVING COUNT(id) > 2;

HAVING和WHERE的区别?
WHERE用于在分组前对条件进行限定,如果不满足条件,则不参与分组,HAVING在分组之后进行限定,如果不满足条件,则不会被查询出来.
WHERE后不可以跟聚合函数,HAVING可以进行聚合函数的判断.

分页查询
语法

LIMIT 开始的索引,每页查询的条数.

#分页查询,从索引为0的数据开始查询,查询3条数据.(第一页)
SELECT * FROM student LIMIT 0,3;

#分页查询,从索引为4的数据开始查询,查询3条数据(第二页)
SELECT * FROM student LIMIT 4,3;

开始索引的计算方法
开始索引 = （当前页码-1）*每页显示的条数
因为数据库的第一条数据的索引为0.

MySQl 约束
约束的概述
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性/唯一性。
约束的分类
非空约束 NOT NULL
概述
非空约束用于确保当前列的值不为空值.
使用方式

#创建一张表person,INT类型的ID,VARCHAR类型的NAME(不允许为NULL)
CREATE TABLE person
(
  id INT,
  name VARCHAR(5) NOT NULL -- NAME列不允许为NULL值.
);

当一张表的某一列设置为非空约束之后,添加NULL值到表中的时候,会报错.

删除非空约束

#通过改变列的数据类型可以删除列的非空约束(不添加NOT NULL)
ALTER TABLE person MODIFY name VARCHAR(5);

添加非空约束到已有的列
#添加非空约束到PERSON表中的ID列.
ALTER TABLE person MODIFY id INT NOT NULL;

唯一约束 UNIQUE
概述： 唯一约束是指定表中的列或列组合不能重复，保证数据的唯一性。
使用方式：

#创建一张表person,INT类型的ID(不允许重复),VARCHAR类型的NAME(不允许为NULL).
CREATE TABLE person(
  id INT UNIQUE,
  name VARCHAR(5) NOT NULL
);

删除唯一约束：
#通过DROP INDEX可以删除列的唯一约束.
ALTER TABLE person DROP INDEX id;

添加唯一约束到已有的列
#添加唯一约束到PERSON表中的ID列.
ALTER TABLE person MODIFY id INT UNIQUE;

添加唯一约束到列中的时候，需要确认现有的数据唯一，如果已经存在重复数据，则唯一约束无法添加到列。
注意事项：唯一约束允许由NULL值，但是只允许有一条数据为NULL。

主键约束： PRIMARY KEY
概述：
主键约束在表中定义一个主键来唯一确定表中每一行数据的唯一性。
有主键约束的列中数据，非空且唯一。

使用方式：

#创建一张表PERSON,INT类型的ID(主键约束),VARCHAR类型的NAME(不允许为NULL).
CREATE TABLE PERSON(
  ID INT PRIMARY KEY,
  NAME VARCHAR(5) NOT NULL
);
#删除PERSON表中的主键约束
ALTER TABLE PERSON DROP PRIMARY KEY;
由于PRIMARY KEY 在一张表中只能有一个，所以无需指定列。

添加主键约束到已有的列
#添加主键约束到PERSON表中的ID列.
ALTER TABLE PERSON MODIFY ID INT PRIMARY KEY ;

注意事项：
主键约束一个表中只能有一列。
主键就是表中的每行数据的唯一标识。

主键约束自动增长
概述：如果某一个列是数值类型的，使用AUTO_INCREMENT可以来完成值的自动增长。

使用方式：

#创建一张表PERSON,INT类型的ID(主键约束自动增长),VARCHAR类型的NAME(不允许为NULL).
CREATE TABLE PERSON(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(5) NOT NULL
);

删除自动增长：
#删除PERSON表的ID列的自动增长.
ALTER TABLE PERSON MODIFY ID INT;
删除了自动增长之后，主键约束依然存在。
添加自动增长到已有的列
#添加自动增长到PERSON表的ID列.
ALTER TABLE PERSON MODIFY ID INT AUTO_INCREMENT;

注意事项：
如果列是自动增长的，那么可以赋值为NULL,MYSQL会自动增长。
赋值为NULL的时候，生成的数据是根据表中的最后一条数据进行判断的，不具备连续性。

外键约束： FOREIGN KEY

外键的创建
语法格式：

CONSTRAINT 外键名称 FOREIGN KEY (外键列) REFERENCES 主表名(主键列)

/*
创建DEPARTMENT表
INT类型的ID(主键,自动增长),
VARCHAR类型的DEP_NAME(非空,唯一),
VARCHAR类型的DEP_LOCATION(非空)
*/
CREATE TABLE DEPARTMENT(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  DEP_NAME VARCHAR(20) NOT NULL UNIQUE,
  DEP_LOCATION VARCHAR(20) NOT NULL
);

/*
创建EMPLOYEE表
INT类型的ID(主键,增长),
VARCHAR类型的NAME(非空),
INT类型的AGE(非空),
INT类型的DEP_IP(外键对应主表的主键)
 */
CREATE TABLE EMPLOYEE(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  AGE INT NOT NULL,
  DEP_ID INT,
  CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID)
);

注意事项：
当主表和从表存在外键关系时，主表删除的时候，从表内有数据引用，是无法直接删除的。
当从表添加数据的时候，如果外键列的数据主表中不存在的话，是无法直接添加的。
外键的名称不可以重复。
外键的好处：可以让表与表之间产生关系，来保证数据的正确性。
删除外键的操作：

#删除EMPLOYEE表中的外键约束
ALTER TABLE EMPLOYEE DROP FOREIGN KEY EMP_DEFT_FK;

添加外键到已有列的操作
#添加外键约束到EMPLOYEE表中的DEP_ID列.
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID);

级联更新/级联删除

#添加外键约束到EMPLOYEE表中的DEP_ID列并设计级联更新
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID) ON UPDATE CASCADE ;

#添加外键约束到EMPLOYEE表中的DEP_ID列并设计级联更新/级联删除
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID) ON UPDATE CASCADE ON DELETE CASCADE;

多表之间的关系
多表之间具有那列关系？
一对一关系：
例:人和身份证，一个人只能有一张身份证,一张身份证只能对应一个人.
一对多/多对一关系：
例:教室和学生，一个学生只能对应一个教室,一个教室可以对应多个学生.
多对多关系：
例:学生与课程，一门课程可以被多个学生选择,一个学生可以选择多门课程.
实现多表之间的关系
一对多关系
在一的一方建立外键,指向多的一方的主键.
多对多关系
多对多的关系的实现需要借助第三张中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键.
一对一关系
一对一关系的实现,可以在任意一方添加唯一外键指向另一方的主键.

范式：
概念：范式就是设计数据库时需要遵循的一些规范。
设计关系型数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范被称为不同的范式,各种范式呈递归规范,越高的范式数据冗余越小.

范式的分类：
目前关系数据库有六种范式,第一范式(INF),第二范式(2NF),第三范式(3NF),巴斯-科德(BCNF),第四范式(4NF),第五范式(5NF[完美范式])

总结：一般数据库的设计负责第三范式即可,数据库符合的范式越高,代表表越多,表越多代表表之间的关系越复杂,虽然符合的范式高,但是不利于SQL语句的书写.