1 数据约束

              1.1 简介

                   给表添加数据约束，从而约束用户操作表数据的行为。

 

              1.2 默认值约束

注意： 当前没有插入默认值字段的时候，默认值才会起作用

 1.1 默认值(default)

1）当没有插入gender字段的时候，分配一个默认值

需求：当不插入gender的时候，分配一个‘男’的默认值

CREATE TABLE test( NAME VARCHAR(20), gender VARCHAR(2) DEFAULT '男' )

              1.3 非空约束

1.2 非空（not null） 1)非空约束，不能不插入值

2)非空约束，不能插入null

需求： name字段一定要有值（不能不插入数据，不能是null），这是给name添加非空约束

CREATE TABLE test( NAME VARCHAR(20) NOT NULL, gender VARCHAR(2) )

              1.4 唯一约束

1.3唯一(unique)

1)不能插入重复的值

2)唯一约束，可以插入多个null。所以唯一约束不能约束null

需求： id的值不能出现重复值。这时就要给id添加一个唯一约束。

CREATE TABLE test( id INT UNIQUE, NAME VARCHAR(20) )

              1.5 主键约束

1.4 主键约束(primary key)（唯一+非空）

 1）唯一性

 2）非空性

 注意：

 1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性

 2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复。

 3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性。

创建表给id字段施加主键

CREATE TABLE test( idINT PRIMARY KEY, NAMEVARCHAR(20) )

  1.6 自增长约束（auto_increment）

 1.5 自增长约束（auto_increment）

自增长约束：初始值为0，每次递增1

需求：id的值不需要我们开发者管理，交给数据库维护，这时给id添加自增长约束。

创建表给id字段添加主键自增长约束

CREATE TABLE test( idINT PRIMARY KEY AUTO_INCREMENT, NAMEVARCHAR(20) ) 使用truncate table删除数据的时候，可以把自增长的初始值置为0。

              1.7 外键约束

               外键约束：约束两种表的情况

案例：创建一个员工表，表中含有员工ID、姓名、部门；

CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), deptName VARCHAR(20) ) 当我们在表中加入数据时，可能会出现重复的部门（冗余），如果数据出现冗余，那么会浪费数据库存储空间。

因此我们创建一个部门表，利用部门表中部门的ID来代表该部门。

CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) )

CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), deptId INT ) 问题：现在我们在员工表中添加数据时，当部门ID不存在时，我们在员工表中仍可以输入，如何防止这种非法数据的插入，这时就添加外键约束了。

添加外键约束（foreign key）

需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。

 员工表（副表： 被别的表约束。外键设置在副表）

CREATE TABLE employee( idINT PRIMARY KEY AUTO_INCREMENT, NAMEVARCHAR(20), deptIdINT, CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCESdept(id) -- 外键名称 外键字段 参考 )

部门表（主表：约束别人的表）

CREATE TABLE dept( idINT PRIMARY KEY AUTO_INCREMENT, NAMEVARCHAR(20) )

外键约束在什么情况下，会其作用？

插入数据：当往副表插入了主表不存在的数据时，外键起作用

修改数据：当往副表修改为主表不存在的数据时，外键起作用

 删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用

 

 当有了外键之后，应该如何管理数据呢？

 插入数据： 先插入主表的数据，再插入副表数据

 修改数据： 先修改主表数据，再修改副表数据

 删除数据：先删除副表数据，再删除主表数据

              1.8 级联技术

                   级联：当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。

CREATE TABLE employee( idINT PRIMARY KEY AUTO_INCREMENT, NAMEVARCHAR(20), deptIdINT, --添加级联修改： ON UPDATE CASCADE --添加级联删除： ON DELETE CASCADE CONSTRAINTemployee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE -- 外键名称 外键字段 参考 )

2 数据库设计

              2.1 引入

                   需求分析：原始需求 -> 业务需求

                                     客户                                     业务模型：客户（姓名   登记时间邮箱  微信号）

                   需求设计：

                                     业务模型（客户业务模型）： -》 抽取实体模型： classCustomer{name email weixin} 

                                     业务模型（客户业务模型） ->  数据模型：设计数据库表（customer表：字段）

 

              2.2 数据库设计的三大范式

                            第一范式： 要求表的每个字段必须独立的不可分割的单元

 

                                                        学生表： student    name             --违反第一范式

                                                                                              张三|狗娃

                                                                                              王含|张小含

 

                                               查询：现用名中包含‘张’的学生

                                                                 select* from student where name like '%张%';

 

 

                                                        学生表： student  name   old_name             --符合第一范式

                                                                                              张三      狗娃

                                                                                              王含      张小含

 

                   第二范式：在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。

                                                                 一张表应该只表达一个意思！！！！

 

                                               员工表：employee

                                                        员工编号  员工姓名  部门名称   订单名称     --违反第二范式

 

                                               员工表：employee

                                                        员工编号  员工姓名  部门名称        --符合第二范式

 

                                               订单表：

                                                        订单编号  订单名称

 

                   第三范式：在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。

 

                                              

                                               员工表：employee                                  --不符合第三范式（出现数据冗余）

                                                        员工编号  员工姓名  部门编号部门名称

                                                        1      张三        1    软件开发部

                                                        2      李四                   1     软件开发部        

 

                                               员工表：employee                                  --符合第三范式（降低数据冗余）

                                                        员工编号  员工姓名  部门编号

                                                        1      张三        1   

                                                        2      李四                   1   

                                               部门表： dept

                                                        部门编号  部门名称

                                                                 1    软件开发部

 

                                     数据库存储空间  和数据查询效率

3 多表查询

1.1 交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）

需求： 员工及其部门

SELECT employee.name,dept.name FROMemployee,dept;

这里会输出所有员工*所有部门项；

多表查询的步骤： 1）确定查询哪些表  2）确定查询哪些字段  3）确定连接条件(规则： 表数量-1)

内连接效果：只有满足连接条件的数据才会显示出来

SELECT e.name,d.name FROMemployee e INNERJOIN dept d ONe.deptId=d.id;

 1.2内连接查询（使用最多）

SELECT e.name,d.name FROMemployee e,dept d WHEREe.deptId=d.id;

 另一种语法

1.3 左外连接查询（其次）

需求： 查询部门及其部门的员工（部门全部显示）

 左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，

         就显示满足条件的的数据，但是如果不满足连接条件，则显示null

 预期结果：

         软件开发部  张三

         软件开发部  王五

         软件维护部  李四

         软件维护部  陈六

         秘书部     null

SELECT d.name,e.name FROMdept d LEFTOUTER JOIN employee e ONd.id=e.deptId;

-- 1.4 右外连接查询

-- 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，

        -- 就显示满足条件的数据，但是如果不满足连接条件，则显示null

SELECT d.name,e.name FROMemployee e RIGHTOUTER JOIN dept d ONd.id=e.deptId;        

1.5 自连接查询

需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)

 预期结果：

         员工    上司

         张三  null

         李四   张三

         王五   李四

         陈六   王五

SELECT e.name AS '员工',b.name AS '上司' FROMemployee e LEFTOUTER JOIN employee b ONe.bossId=b.id;

4 mysql存储过程

              4.1 引入

                   存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程。

              4.2 存储过程的特点

                   1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程

                   2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。

                   3）存储过程的移植性非常差的！

              4.3 存储过程语法

创建存储过程

定义结束符号

DELIMITER 结束符号

CREATE PROCEDURE 存储过程名称 (形式参数列表)

BEGIN

         多个sql语句

END 结束符号 

 调用存储过程

CALL 存储过程名称(实际参数列表);

 

参数类型：

IN： 输入参数，可以携带数据到存储过程中

OUT: 输出参数，可以携带数据到存储过程外面。

INOUT: 输入输出参数 存储过程的语法  delimiter $  -- 声明一个结束符  create procedure pro_Test(输入或者输出参数)  begin  带有逻辑的sql语句  end$ 调用存储过程：  call pro_Test(实参); 存储过程的输入输出参数如何表示呢？  in 输入参数的变量名称 类型 out 输出参数的名称 类型  inout 输入输出参数名称 类型  需求：根据上面的语法叙述，创建一个带参数的存储过程，并调用 需求： 传入员工id查询对应的员工 DELIMITER $ CREATE PROCEDURE pro_testByIn(IN eid INT) -- 参数类型（IN） 参数名称 数据类型(int) BEGIN SELECT * FROM employee WHERE id=eid; END $ -- 调用 CALL pro_testByIn(2); 思考一个问题？我们在调用带有输出参数的存储过程的时候，肯定是会返回一个值的，  那么我们该使用什么变量去接受存储过程给我返回来的这个值呢  在我们的数据库中有哪些变量可以充当这个变量使用呢？  1.全局变量(mysql数据库的系统变量)：随着mysql数据库的启动而存在，随着mysql数据库的关闭二消失  查看mysql数据库的全局变量 SHOW VARIABLES; 查看mysql数据库中全部变量和字符相关的 SHOW VARIABLES LIKE 'character%'; 如何去查看mysql数据库的全局变量  select @@+全局变量名  如何去改变数据库的全局变量  set @@+全局变量=值 SELECT @@character_set_client; SET @@character_set_client='utf8';  2.会话变量：存在于某一次会话中，随着会话的结束而消失  如何去查看一个会话变量  select @+变量名   给会话变量设置值  set @变量名=值 SET @n='hello'; SELECT @n; 3.局部变量：位于存储过程中，随着存储过程而存在，随着存储过程的调用完毕二消失 给局部变量设置值  set 局部变量=值 查看局部变量 select 局部变量 创建一个带有输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_TestOut(OUT str VARCHAR(20)) BEGIN 给输出参数赋值 SET str='我是输出参数'; END$ 根据我们刚才的分析，我们，目前必须使用会话变量去接收一个带有输出参数的带出的值 SET @nn='world'; SELECT @nn; CALL pro_TestOut(@nn) SELECT @nn;  3.4 带有判断条件的存储过程  需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，  num=3,输出‘星期三’，否则，输出‘错误参数’ DELIMITER $ CREATE PROCEDURE pro_TestDay(IN num INT,OUT d VARCHAR(20)) BEGIN IF num=1 THEN SET d='星期一'; ELSEIF num=2 THEN SET d='星期二'; ELSEIF num=3 THEN SET d='星期三'; ELSE SET d='参数错误'; END IF; END$调用上面的这个带有输入和输出参数的存储过程 CALL pro_TestDay(4,@d); SELECT @d;SQL语句中，IF 后面要加 THEN ，而且要END IF；

需求：

         1）计算出所有学生的英语平均分

         2）如果平均分，大于80分，且小于等于100分，输出'优秀'

                   如果平均分，大于等于60分，且小于等于80分，输出'良好'

                   如果平均分，小于60分，输出'不及格'

DELIMITER $ CREATE PROCEDURE pro_testByAvg(OUT str VARCHAR(20),OUT tavg FLOAT) BEGIN DECLARE savg FLOAT DEFAULT 0.0; SELECT AVG(english) INTO savg FROM student2; IF savg>80 AND savg<=100 THEN SET str = '优秀'; ELSEIF savg>=60 AND savg<=80 THEN SET str = '良好'; ELSE SET str = '不及格'; END IF; SET tavg = savg; END $

删除存储过程 DROP PROCEDURE pro_testByAvg;

5 触发器

              5.1 简介

                   当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。

5.2 触发器语法

先创建一个员工日志表

CREATE TABLE emp_log( idINT PRIMARY KEY AUTO_INCREMENT, contentVARCHAR(20) ) 需求： 当往员工表 插入一条数据时，往员工日志插入一条记录。

创建增加触发器：

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW INSERT INTO emp_log(content) VALUES('员工被插入了一条记录');

 创建修改触发器

CREATE TRIGGER tri_empUpd AFTER UPDATE ONemployee FOR EACH ROW INSERTINTO emp_log(content) VALUES('员工被修改了一条记录');

创建删除触发器

CREATE TRIGGER tri_empDel AFTER DELETE ONemployee FOR EACH ROW INSERTINTO emp_log(content) VALUES('员工被删除了一条记录'); 删除触发器：

DROP TRIGGER 触发器名称;

5.3 数据库的权限问题

五、mysql权限问题

root用户： 超级管理员。    权限：增删改查（数据库，表，数据）

给mysql数据库分配不同权限的不同用户

mysql数据库：存放mysql配置信息，包括用户信息

用户表

USE mysql; SELECT * FROM USER; password(密码)这个函数会对密码进行加密 SELECT PASSWORD('root'); 更改root这个用户名的密码‘123456’ UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';创建一个新的用户，并且给新的用户一些特有的权限 GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';