SQL学习笔记二

一、约束

1. 约束就是对表中的数据进行限定，保证数据的正确性、有效性和完整性

2. 分类：

   1. 主键约束：primary key
   2. 非空约束：not null
   3. 唯一约束：unique
   4. 外键约束：foreign key

3. 非空约束：not null，值不能为null
   举栗说明

   //创建表时添加约束
   CREATE TABLE stu(
   		id INT,
   		NAME VARCHAR(20) NOT NULL //name为非空
   	);
   
   //创建表完后，添加非空约束
   ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
   
   //删除name的非空约束
   ALTER TABLE stu MODIFY NAME VARCHAR(20);
   

4. 唯一约束：unique，值不能重复
   举栗说明

   //创建表时，添加唯一约束
   CREATE TABLE stu(
   		id INT,
   		telephone_number VARCHAR(20) UNIQUE //添加了唯一约束
   	);
   
   //删除唯一约束
   ALTER TABLE stu DROP INDEX telephone_number;
   
   //在创建表后，添加唯一约束
   ALTER TABLE stu MODIFY telephone_number VARCHAR(20) UNIQUE;
   

   mysql中，被唯一约束限定的属性，它的列的值可以有多个null

5. 主键约束：primary key
   举栗说明

   //在创建表时，添加主键约束
   create table stu(
   		sid int primary key, //给sid添加主键约束
   		name varchar(20)
   	);
   	
   //删除主键
   ALTER TABLE stu DROP PRIMARY KEY;
   
   //创建完表后，添加主键
   ALTER TABLE stu MODIFY sid INT PRIMARY KEY;
   

   一张表只能有一个字段为主键,它非空且唯一

6. 自动增长
   也就是说，如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
   举栗说明

   //在创建表时，添加主键约束，并且完成主键自增长
   create table stu(
   		sid int primary key auto_increment,// 给sid添加主键约束
   		name varchar(20)
   		);
   
   //删除自动增长
   ALTER TABLE stu MODIFY sid INT;
   
   //添加自动增长
   ALTER TABLE stu MODIFY sid INT AUTO_INCREMENT;
   

7. 外键约束
   foreign key,让表于表产生关系，从而保证数据的正确性
   举栗说明

   //在创建表时，可以添加外键
   create table 表名(
   			sid INT PRIMARY KEY,
   			//这里可以把‘constraint 外键名称’ 去掉
   			constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
   		);
   
   //删除外键
   ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
   
   //创建表之后，添加外键
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
   

8. 级联操作
   级联操作用于关联外键的表中的操作，存在级联更新和级联删除两种。
   举栗说明
   若存在一张员工表和一张部门表，对应员工表设置一个外键关联部门表的主键，若要对部门表主键进行修改，一般是不能的，因为此时员工表与之关联，若要进行修改可设置级联更新，修改后两张表相应的数据都会修改，级联删除同上

   1. 级联更新：ON UPDATE CASCADE
   2. 级联删除：ON DELETE CASCADE

   //添加级联操作
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
   				FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
   

二、数据库的设计

1. 多表之间的关系
   1. 一对一：如学生和校园卡
   2. 一对多(多对一)：部门和员工，个部门有多个员工，一个员工只能对应一个部门
   3. 多对多：学生和课程，一个课程也可以被很多学生选择，一个学生可以选择很多门课程
2. 实现关系
   1. 一对一：在任意一方添加唯一外键指向另一方的主键，不过一般情况下会把他们合并成一张表
   2. 一对多(多对一)：在多的一方建立外键，指向一的一方的主键
   3. 多对多：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。第三张表的可以采用联合主键的方式时之唯一，举栗：PRIMARY KEY(rid,uid), // 联合主键

三、范式

概念：设计数据库时，需要遵循的一些规范。
遵从不同的规范要求，设计出合理的关系型数据库各种范式呈递次规范，越高的范式数据库冗余越小。
目前有六种范式，这里只说明前三种，因为一般我们设计数据库表遵从前三种就可以了

1. 分类
   1. 第一范式（1NF）：每一列都是不可分割的原子数据项
   2. 第二范式（2NF）：在1NF基础上消除非主属性对主码的部分函数依赖
   3. 第三范式（3NF）：在2NF基础上消除传递依赖
2. 几个概念
   1. 先给出一张表
   2. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以唯一确定B属性的值。则称B依赖于A。
      1. 例如：学号–>姓名。 （学号，课程名称） --> 分数
      2. 说明：由表可知通过学号可以唯一确定学生姓名，所以称姓名依赖于学号，同上。
   3. 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
      1. 例如：（学号，课程名称） --> 分数
      2. 说明：只有学号加课程名称才可以唯一确定分数，而不是二者中的单独一个，则称分数完全函数依赖于属性组A
   4. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
      1. 例如：（学号，课程名称） – > 姓名
      2. 学号就可以唯一确定姓名了，而A是学号和课程名称构成的属性组，则称姓名部分依赖于属性组A
   5. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A。例如：学号–>系名，系名–>系主任
   6. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码。
      1. 例如：该表中码为：（学号，课程名称）
      2. 说明：通过学号与课程名称组成的属性组可以唯一确定表中另外的几个属性
   7. 主属性：码属性组中的所有属性
   8. 非主属性：除了码属性组的属性
      举栗说明
3. 1NF举例
   表1中"系"这一列包含 两列，不满足第一范式的原子项，可更改为表2。一般情况下我们创建的表都会满足第一范式。

表1 表2

4. 2NF举例
   首先我们来看表2存在的问题。第一，存在严重数据冗余(重复)，比如姓名、系主任。第二，数据添加也存在问题，想要添加新增设的系和系主任时，数据不完整和不合法。第三，删除也存在问题，系和系主任是个学生的数据挂钩的，倘若学生毕业把他的数据删除，相应这个系内容也没了。
   其次，2NF解决的是消除非主属性对主码的部分函数依赖。
   解决：在表2中，我们可知学号与课程名称组成的属性组可以唯一确定表中另外的几个属性，所以学号与课程名称组成的属性组构成表2的主码，也就是主属性。想要消除2NF范式定义的部分函数依赖，只需要把除主属性外的属性单独拿出来再结合主键构成另外一张表即可，如下图3

表3

5. 3NF举例
   3NF消除的是传递依赖。我们观察表3中的学生表可知，学号被系名所依赖，系主任依赖于系名，所以系主任传递依赖于学号。想要消除它，我们需要再构建一张系表，这里系名作为主键，把传递依赖的系主任属性拿出来，如下图4中

表4

四、数据库的备份和还原

1. 命令行方式
   1. 备份：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
   2. 还原：登录数据库–>创建数据库–>使用数据库–>执行命令：source 保存的路径

      保存的路径应包括要保存的文件名，如：d://a.sql

2. 图形化工具

五、多表查询

1. 一个概念：笛卡尔积：有两个集合A,B .取这两个集合的所有组成情况

2. 多表查询分为内连接查询、外连接查询、子查询

3. 内连接查询

   1. 隐式内连接：使用where条件消除无用数据
      举个栗子

   	//查询员工表的名称，性别和部门表的名称
   		    SELECT 
   				d1.name, -- 员工表的姓名
   				d1.gender,-- 员工表的性别
   				d2.name -- 部门表的名称
   			FROM
   				emp d1,
   				dept d2
   			WHERE 
   				d1.`dept_id` = d2.`id`;
   

   2. 显式内连接：
      1. 语法： * 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
         举个栗子

      SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	
      

      应该值得注意的是内连接查询的条件是什么

4. 外链接查询

   1. 左外连接
      1. 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
      2. 查询的是左表所有数据以及其交集部分
         举个栗子

      //询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
      SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
      

   2. 右外连接
      1. 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
      2. 询的是右表所有数据以及其交集部分
         举个栗子

      SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
      

5. 子查询

   1. 概念：查询中嵌套查询，称嵌套查询为子查询
   2. 子查询三种不同情况
      1. 子查询的结果是单行单列的，这种情况下结果做条件，使用运算符去判断
      2. 子查询的结果是多行单列的，子查询可以作为条件，使用运算符in来判断
      3. 子查询的结果是多行多列的，子查询可以作为一张虚拟表参与查询

六、事务

1. 事务的基本介绍
   1.概念： 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
   2. 操作:开启事务： start transaction、回滚：rollback、 提交：commit
   3. MySQL数据库中事务默认自动提交
   4. 事务提交的两种方式
      1. 自动提交：mysql就是自动提交的，一条DML(增删改)语句会自动提交一次事务
      2. 手动提交：Oracle 数据库默认是手动提交事务，需要先开启事务，再提交
   5. 修改事务的默认提交方式：
      1. 查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
      2. 修改默认提交方式： set @@autocommit = 0;
2. 事务的四大特征
   1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
   2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
   3. 隔离性：多个事务之间。相互独立
   4. 一致性：事务操作前后，数据总量不变
3. 事务的隔离级别
   1. 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
   2. 隔离级别
      1. read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读
      2. read committed：读已提交 （Oracle）。产生的问题：不可重复读、幻读
      3. repeatable read：可重复读 （MySQL默认）。 产生的问题：幻读
      4. serializable：串行化。可以解决所有的问题

      隔离级别从小到大安全性越来越高，但是效率越来越低

      5. 数据库设置隔离级别
         set global transaction isolation level 级别字符串;
      6. 数据库查询隔离级别
         select @@tx_isolation;

七、DCL

1. DBA：数据库管理员
2. DCL：管理用户，授权
   1. 管理用户
      1. 添加用户
         语法：CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
      2. 删除用户
         语法：DROP USER ‘用户名’@‘主机名’;
      3. 修改用户密码
         语法：UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
         或者：SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
         举栗：SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘123’);
      4. 查询用户
         1. 切换到mysql数据库:USE myql;
         2. 查询user表:SELECT * FROM USER;
3. mysql中忘记了root用户的密码？
   1. cmd – > net stop mysql 停止mysql服务(管理员运行cmd)
   2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
   3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
   4. use mysql;
   5. update user set password = password(‘你的新密码’) where user = ‘root’;
   6. 关闭两个窗口,打开任务管理器，手动结束mysqld.exe 的进程
   7. 启动mysql服务,使用新密码登录
4. 权限管理
   1. 查询权限
      语法：SHOW GRANTS FOR ‘用户名’@‘主机名’;
      举栗：SHOW GRANTS FOR ‘lisi’@’%’;
   2. 授予权限
      语法：grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;

      //授予lisi 对数据库db1数据库的account表的DML操作权限
      GRANT INSERT,DELETE,UPDATE ON db1.account TO 'lisi'@'%';
      
      //给张三用户授予所有权限，在任意数据库任意表上
      GRANT ALL ON *.* TO 'zhangsan'@'localhost';
      

   3. 撤销权限
      语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
      栗子：REVOKE UPDATE ON db3.account FROM ‘lisi’@’%’;