1 数据约束
1.1 简介
给表添加数据约束，从而约束用户操作表数据的行为。
1.2 默认值约束
注意： 当前没有插入默认值字段的时候，默认值才会起作用
1.1 默认值(default)
1）当没有插入gender字段的时候，分配一个默认值
需求：当不插入gender的时候，分配一个‘男’的默认值
CREATE TABLE test(
NAME VARCHAR(20),
gender VARCHAR(2) DEFAULT '男'
)
1.3 非空约束
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;SHOW VARIABLES LIKE 'character%';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;需求：
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;
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';
扫一扫
8562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
