MySQL基础知识（一）

#### 心得
### 提高INSERT的性能
1. 因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
2. 如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之 间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：
INSERT  LOW_PRIORITY INTO
顺便说一下，这也适用于下一章介绍的UPDATE和DELETE语句。


### mySQL的内部引擎
迄今为止使用的CREATE TABLE语句全都以ENGINE=InnoDB语句结束。
MySQL有一个具体管理和处理数据的内部引擎但MySQL与其他DBMS不一样，它具有 多种引擎。
1. InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
2. MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度 很快（特别适合于临时表）；
3. MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章）但不支持 事务处理


## 更快的删除
* 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快


###表的修改更新
1. 小心使用ALTER TABLE ！！！！！
2. 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和 数据的备份）。
3. 数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。
4. 如果删除了不应该删除的列，可能会丢失该列中的所有数据。
5. 如增加外键可能导致表结构的变化，而引起表无法使用





### 删除已有的主键：alter table table_name drop primary key;
### 添加主键：alter table table_name add primary key(key_name)，也可以用change和modify添加;
### 删除已有的外键：alter table drop foreign key key_id;(注意这里的key_id是系统自动生成的，不是我们定义的外键字段名)
### 添加外键：alter table table_name foreign key (key_name) references table_name(key_name);
## 限制结果（LIMIT）
* 行0 检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行。
* 在行数不够时 LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行（例如，给出LIMIT 10, 5，但只有13
行），MySQL将只返回它能返回的那么多行。
* ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。


## 排序（ORDER BY）
* 为了按多个列排序，只要指定列名，列名之间用逗号分开即可，select id,name,price from product order by name,price;
* DESC与ASC关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字


#### 聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
###聚集函数的特点
1. 只用于单个列 AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
2. NULL值 AVG()函数忽略列值为NULL的行
3. 如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。
4. 对非数值数据使用MAX() 虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值
5. 在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。
6. NULL值 MAX()函数忽略列值为NULL的行。NULL值 SUM()函数忽略列值为NULL的行
7. 


###其他函数


如果要的是日期，请使用Date() 如果你想要的仅是日期，
则使用Date()是一个良好的习惯，即使你知道相应的列只包
含日期也是如此。这样，如果由于某种原因表中以后有日期和
时间值，你的SQL代码也不用改变。当然，也存在一个Time()
函数，在你只想要时间时应该使用它。还有year()month()等类似的函数
之后。使用子句的次序不对将产生错误消息。


###分组（group by）
* 注意事项：
1. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
2. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。


###having与where的区别
HAVING和WHERE的差别 这里有另一种理解方法，WHERE在数据
分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重
要的区别，WHERE排除的行不包括在分组中。这可能会改变计
算值，从而影响HAVING子句中基于这些值过滤掉的分组。


###关系表
什么是外键：外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。


### DCL语句的使用
* DCL概述
* 数据控制语言：用来定义数据库的访问权限和安全级别，及创建用户。


* 常用操作
* 创建用户
* 语句格式：create user 用户名@xxx identified by '密码';
* 其中xxx表示创建的用户使用的IP地址，可以设置为localhost(代表本机)或者'%'（代表允许所有IP地址登录）


* 用户授权
* 创建用户之后，可以使用新用户进行登录，查看数据库只有系统自带的数据库，
* 想要操作自己创建的数据库还需要root用户对新用户进行授权
* 语句格式：
* grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP;
* 将数据库的n个权限授予用户。
* grant all on 数据库名.* to 用户名@IP;
* 将操作数据库的所有的权限授予用户。


* 撤销权限
* 当需要限制新用户操作数据库的权限时,root用户可以撤销已授予用户的某些权限
* 语句格式
* revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;
* 比如：revoke delete on day21.* from pkxing;
### MySQL单表查询之排序查询
* order by 的作用
* 根据指定的列进行排序，默认是升序。
* asc：默认值，升序
* desc：降序


* order by 注意事项
* 后面跟字段名或字段的别名。


### MySQL单表查询之聚合查询
* 常用的聚合函数以下几个
* SUM(列名) 求指定列的数值之和，列必须是数值列。否则计算结果为0
* AVG(列名) 求指定列的数值平均值
* MIN(列名) 求指定列的最小值
* MAX(列名) 求指定列的最大值
* COUNT(列名) 求数量


* 注意事项
* 如果列的值为null，则不会统计该列。 


### MySQL单表查询之分页查询
* LIMIT分页查询格式
* limit 开始行号,数量;
* 行号是从0开始，
* limit 0,2;


* 注意事项
* 分页同时要排序，分页的语句必须在排序语句后面。


### MySQL单表查询之分组查询
* group by 语句
* 对信息进行分组


* 示例演示
* 查询男女的数量各多少
* 查询人数大于等于2的地区


* having的作用
* 对组信息进行过滤的

* having与where的区别
* where语句是对行信息进行过滤，where语句后面不能跟聚合函数！！！！！！！
* having语句是对象组信息进行过滤，having语句后面可以跟聚合函数。


### 数据库的备份和还原
* 参考word文档
* 备份数据库（导出数据库中所有的表和数据）
* 语法格式：mysqldump -u 用户名 -p 密码 数据库>文件的路径（这种方式不需要登录数据库）
* 还原数据库（导入）
方式一：在 Windows 命令行中使用 mysql 命令
Ø 语法格式：mysql -u 用户名 -p 密码 数据库<文件的路径（这种方式不需要登录数据库）
方式二：在 MySQL 命令行中使用 source 命令把 sql 文件导入到当前数据库中
Ø 语法格式：source 导入文件的路径;注：这种方式要先选择数据库


### 数据的约束
* 什么是数据约束
* 用来约束数据表中的数据。


* 数据约束的作用
* 保证数据表中的数据的有效性，完整性。


* 约束的分类
* 默认约束
* 非空约束
* 唯一约束
* 主键约束
* 外键约束
* 检查约束(MySQL不支持)
* 用户输入完数据时立即检查数据的合法性，如果不合法则直接报错。


* 什么时候添加约束
* 在创建表结构时添加约束
* 创建完表结构之后在修改约束
* 可能会修改失败。


### 数据约束之默认约束
* 什么是默认约束
* 用来约束某一字段的值如果不给定，则使用默认值。
* 关键字：default 默认值;


### 数据约束之非空约束
* 什么是非空约束
* 约束某一列的值不能是空值。
* 关键字：not null


### 数据约束之唯一约束
* 什么是唯一约束
* 用来约束某一列的值不能出现重复的值。
* 关键字：unique


* 两个问题
* 可以出现多个null吗？
* 可以，因为null没有值，所以没有重复的概念
* 可以出现多个''吗？ 
* 不可以，因为’‘也是有值的，所以也不能重复。


### 数据约束之主键约束
* 什么是主键
* 用来唯一标识一条记录
* 每一个表都应该有一个主键，用来唯一标识一条记录
* 关键字：primary key 

* 主键的特点
* 非空且唯一


* 主键的设计原则
* 主键应该使用对用户没有任何意义的字段 
* 主键的值一般有数据库或计算机自动生成。


* 零填充
* 作用：让指定整数列的值在不满足指定位数的情况下前面用零补齐。
* 关键字：ZEROFILL
* 关键字位置：在数据类型后面
* 示例演示
* 创建学生表s6，id为整数，长4位，零填充，主键，自动增长。名字varchar(20)，唯一约束。

* delete和truncate有什么区别
* delete
* 用来删除表中的所有数据，但不影响自增长的值。
* delete删除数据可以通过事务进行回滚。
* truncate
* 用来删除表中的所有数据,重新建表。但会将自增长的值设置会默认值
* truncate删除数据，无法通过事务进行回滚。


### 数据约束之外键约束
* 为什么要使用外键约束
* 示例演示：引出没有使用外键时存在的问题。
* 创建一个员工表包含如下列
* (id主键自增长,emp_name,dept_name,dept_loc) 
* 插入5条记录，观察部门字段的数据。

* 上述员工表存在的问题
* 部门的数据出现大量相同的值，造成数据冗余。


* 如何解决数据大量冗余的问题
* 再设计另一张表，把存在冗余的字段放到新表中。
* 设计一个部门表
* 包含字段有：id 主键自增长,dept_name,dept_loc 

* 修改员工表
* 先删除员工表，重写设计员工表
* 包含字段有：id 主键自增长，员工名emp_name, dept_id(部门id)，部门id就是外键

* 插入部门数据
* 插入员工数据
* 插入正常数据(部门存在)
* 插入不正常的数据(部门不存在的)

* 数据冗余的通过多设计一张就解决问题了，但是可能会出现错数据问题，如何解决数据出错的问题呢
* 解决方法：外键约束
* 主表：约束其他表的表，表结构不需要改变。
* 副本/从表：被其他表约束的表。
* 删除员工表，重写设计表结构
* 在员工表上把部门id设置为外键。

create table employee(
id int PRIMARY key AUTO_INCREMENT,
emp_name varchar(20),
dept_id int,
constraint foreign key (dept_id) references 
dept(id)
-- 员工表的部门id值引用部门表的主键值，
此时员工表的dept_id就是外键
-- 员工表就是从表，部门就是主表
);

* 定义外键格式
* constraint foreign key(外键) references 主表(主键)
* 使用了外键约束之后插入数据
* 插入2条正常数据。
* 插入1条数据指定一个不存在的部门id    
* a foreign key constraint fails(违反外键约束)

* 外键约束下对表记录的操作
* 插入数据
* 向部门表中插入一个新部门：行政部，插入一个新员工，分配到行政部。


* 插入数据的顺序
* 先插入主表(部门表)
* 再插入从表(员工表)


* 修改数据表
* 把部门表中的id值4改为5，能不能直接修改？
* 如果从表(员工表)中有引用的数据，不能直接修改主表（部门表)
* 修改步骤
* 先修改从表，把员工表中dept_id=4的员工先改到其它部门。
* 再修改主表，部门表


* 删除数据
* 需求：删除部门表中部门id=2的某个部门。 
* 如果从表(员工表)中有引用的数据，不能直接删除主表（部门表) 
* 删除步骤
* 先删除从表(员工表的数据)，或修改员工的部门ID
* 再删除主表(部门表)中的数据。 


### 级联操作
* 问题分析
* 如果有外键的前提下，必须先修改或删除从表中的数据，才能修改或删除主表中的数据，
可不可以在修改或删除主表的数据时直接影响从表的数据呢？

* 解决方法
* 使用级联操作


* 什么是级联操作
* 在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作。


* 级联更新和级联删除演示
* 先删除员工表
* 重新创建从表(员工表)，主表不变(部门表)

-- 级联操作
-- on update cascade：级联更新
-- on delete cascade：级联删除
create table employee(
    id int primary key auto_increment,
  emp_name varchar(20),
    dept_id int , -- 部门id
    foreign key(dept_id) references dept(id) on update cascade on delete cascade 
) 
 
* 修改和删除操作的变化
* 需求：把部门表中的id等于1的部门改为id等于10
* 添加级联更新后，可以直接修改部门，员工表同时进行了更新。
* 需求：删除部门id为2的部门
* 添加级联删除后，可以直接删除部门，员工表同时进行了删除


### 多表操作之表与表之间的关系
* 表与表之间的关系有三种
* 一对一 (了解)
* 一对多 (重点)
* 多对多 




* 一对多关系(重点：最常见的关系)
* 常见案例：部门与员工，客户与订单，商品与分类
* 建表原则：一方不动，多方增加一个字段，该字段(外键)值引用一方的主键值


* 多对多关系
* 常见案例：学生和课程
* 建表原则：通过中间表来维护两张表的关系，中间表至少有两个字段，一个字段引用学生表的主键，
* 一个字段引用课表表的主键。
* 一对一关系(了解)
* 在实际开发中应用不多，因为一对一可以创建成一张表。
* 建表原则有两个，实际使用择其一即可
* 外键唯一：主表的主键和从表的外键，形成主外键关系，外键唯一。
* 外键是主键：主表的主键和从表的主键形成主外键关系。


### 多对多关系示例演示
* 需求
* 一个学生可以有多门课程，一门课程可以被多个学生选择，如何实现多对多关系。
* 关键：单独设计一张关系表， 


* 数据准备：有一张学生表(学号,姓名)和一张课程表(课程号,课程名称)
* 向学生表中添加3个学生，向课程表中添加3个课程


* 在关系表中操作实现下面的功能：
* 添加
    * 1号学生选择课程1,3
    * 2号学生选择课程1,2,3
    * 3号学生选择2,3
* 删除
* 2号学生不想选2号课程了，将选课删除
* 修改
    * 3号学生选择的2号课程想改成1号课程


* 小结
* 多对多关系中，如果要建立两个主表之间的关系，需要向关系表插入数据。