1 数据约束
1.1 简介
给表添加数据约束,从而约束用户操作表数据的行为。
1.2 默认值约束
注意: 当前没有插入默认值字段的时候,默认值才会起作用
默认值(default)
CREATE TABLE test(
NAME VARCHAR(20),
gender VARCHAR(2) DEFAULT '男'
)
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME,gender) VALUES('张三',NULL);
SELECT * FROM test;
注意:1.当不插入gender的时候,分配一个‘男’的默认值
2.当gender字段为null时,不分配默认值
1.3 非空约束
非空(not null)
CREATE TABLE test(
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(2)
)
需求: name字段一定要有值(不能不插入数据,不能是null),这是给name添加非空约束
1)非空约束,不能不插入值
INSERT INTO test(gender) VALUES('男'); -- ERROR 1364 (HY000): Field 'NAME' doesn't have a default value
2)非空约束,不能插入null
1.4 唯一约束(unique)
CREATE TABLE test(
id INT UNIQUE,
NAME VARCHAR(20)
)
需求: id的值不能出现重复值。这时就要给id添加一个唯一约束。
INSERT INTO test(id,NAME) VALUES(1,'张三');
1)不能插入重复的值
INSERT INTO test(id,NAME) VALUES(1,'李四'); -- Duplicate entry '1' for key 'id'
INSERT INTO test(id,NAME) VALUES(2,’李四’);
2)唯一约束,可以插入多个null。所以唯一约束不能约束null
INSERT INTO test(id,NAME) VALUES(NULL,'王五');
INSERT INTO test(id,NAME) VALUES(NULL,'陈六');
1.5 主键约束
主键约束(primary key)(唯一+非空)
注意:
1)通常情况下,我们会给每张表都会设置一个主键字段,用来标记记录的唯一性
2)但是不建议把业务含义字段作为主键,因为随着业务的变化,业务字段可能会出现重复。
3)建议给每张张独立添加一个叫id的字段,把这个id字段设置成主键,用来作为记录的唯一性。
创建表给id字段施加主键
CREATE TABLE test(
id INT PRIMARY KEY,
NAME VARCHAR(20)
)
1)唯一性
INSERT INTO test(id,NAME) VALUES(1,'张三');
INSERT INTO test(id,NAME) VALUES(1,'张三'); -- Duplicate entry '1' for key 'PRIMARY'
2)非空性
INSERT INTO test(id,NAME) VALUES(NULL,'张三'); -- ERROR 1048 (23000): Column 'id' cannot be null
1.6 自增长约束(auto_increment)
自增长约束(auto_increment)
创建表给id字段添加主键自增长约束
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
自增长约束:初始值为0,每次递增1
需求:id的值不需要我们开发者管理,交给数据库维护,这时给id添加自增长约束。
INSERT INTO test(NAME) VALUES('张三');
INSERT INTO test(NAME) VALUES('李四');
INSERT INTO test(NAME) VALUES('王五');
删除数据
使用truncate table删除数据的时候,可以把自增长的初始值置为0
TRUNCATE TABLE test;
1.7 外键约束
外键作用: 约束两种表的数据
问题: 约束哪种情况下的两种表呢?
员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptName VARCHAR(20)
)
INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');
INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');
INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');
问题:每次插入员工数据时,部门名称数据会出现重复(冗余),如果数据出现冗余,那么会浪费数据库存储空间。
如何解决部门名称数据冗余的问题? 这时就可以设计一张独立的 部门表,把部门名称放到部门表中。
员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT
)
部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
SELECT * FROM employee;
SELECT * FROM dept;
INSERT INTO dept(NAME) VALUES('软件开发部');
INSERT INTO dept(NAME) VALUES('软件维护部');
INSERT INTO employee(NAME,deptId) VALUES('陈六',1);
INSERT INTO employee(NAME,deptId) VALUES('王五',1);
INSERT INTO employee(NAME,deptId) VALUES('张三',3);
问题:在插入员工表的部门id的时候,插入了不存在的部门id,如何防止这种非法数据的插入,这时就添加外键约束了。
添加外键约束(foreign key)
需求:deptId字段值来自于dept表的id字段的值,这时可以给deptId字段添加外键约束。
员工表(副表: 被别的表约束。外键设置在副表)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT,
CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
)
部门表(主表:约束别人的表)
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
SELECT * FROM employee;
SELECT * FROM dept;
INSERT INTO employee(NAME,deptId) VALUES('陈六',1);
INSERT INTO employee(NAME,deptId) VALUES('王五',1);
插入数据
INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 违反外键约束:a foreign key constraint fails
修改数据
UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails
删除数据
DELETE FROM dept WHERE id=1; -- a foreign key constraint fails
外键约束在什么情况下,会其作用?
插入数据:当往副表插入了主表不存在的数据时,外键起作用
修改数据:当往副表修改为主表不存在的数据时,外键起作用
删除数据: 副表中有关联主表的数据的时候,当删除主表数据,外键其作用
当有了外键之后,应该如何管理数据呢?
插入数据: 先插入主表的数据,再插入副表数据
修改数据: 先修改主表数据,再修改副表数据
UPDATE employee SET deptId=3 WHERE id=5;
UPDATE dept SET id=3 WHERE id=2;
删除数据:先删除副表数据,再删除主表数据
DELETE FROM dept WHERE id=3;
DELETE FROM employee WHERE deptId=3;
1.8 级联技术
级联: 当有了外键的时候,我们希望修改或删除数据的时候,修改或删除了主表的数据,同时能够影响副表的数据,这时就可以使用级联。
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
deptId INT,
添加级联修改: ON UPDATE CASCADE
添加级联删除: ON DELETE CASCADE
CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
2 数据库设计
2.1 引入
需求分析: 原始需求 -> 业务需求
客户 业务模型:客户(姓名 登记时间 邮箱 微信号 )
需求设计:
业务模型(客户业务模型): -》 抽取实体模型: class Customer{name email weixin}
业务模型(客户业务模型) -> 数据模型:设计数据库表(customer表: 字段)
2.2 数据库设计的三大范式
第一范式: 要求表的每个字段必须独立的不可分割的单元
第二范式: 在第一范式的基础上,要求表的除主键以外的字段都和主键有依赖关系的。一张表应该只表达一个意思
第三范式: 在第二范式基础上,要求表的除主键外的字段都只能和主键有直接决定的依赖关系。
3 多表查询
1.1 交叉连接查询(笛卡尔乘积: 4*3=12,产生笛卡尔积的原因是没有足够的连接条件)
需求: 员工及其部门
SELECT employee.name,dept.name FROM employee,dept;
多表查询的步骤: 1)确定查询哪些表 2)确定查询哪些字段 3)确定连接条件(规则: 表数量-1)
内连接效果:只有满足连接条件的数据才会显示出来
1.2 内连接查询
SELECT e.name,d.name
FROM employee e,dept d
WHERE e.deptId=d.id;
另一种语法
SELECT e.name,d.name
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;
1.3 左外连接查询
需求: 查询部门及其部门的员工(部门全部显示)
左外连接查询效果: 左表(部门表)的数据全部显示,右表(员工)的数据当满足连接条件的时候,就显示满足条件的的数据,但是如果不满足连接条件,则显示null
预期结果:
软件开发部 张三
软件开发部 王五
软件维护部 李四
软件维护部 陈六
秘书部 null
SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;
1.4 右外连接查询
右外连接查询效果: 右表(部门表)的数据全部显示,左表(员工)的数据当满足连接条件的时候,就显示满足条件的数据,但是如果不满足连接条件,则显示null
SELECT d.name,e.name
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;
1.5 自连接查询
需求: 查询员工姓名及其上司姓名(没有上司的员工也显示)
预期结果:
员工 上司
张三 null
李四 张三
王五 李四
陈六 王五
SELECT e.name AS '员工',b.name AS '上司'
FROM employee e
LEFT OUTER JOIN employee b
ON e.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
SELECT * FROM employee WHERE id=4;
END$
调用
CALL pro_test();
需求: 传入员工id,查询对应的员工,输出员工姓名
DELIMITER $
CREATE PROCEDURE pro_QueryNameById(IN eId INT,OUT eName VARCHAR(20))
BEGIN
SELECT NAME INTO eName FROM employee WHERE id=eId;
END$
调用这个存储过程
CALL pro_QueryNameById(2,@eName);
SELECT @eName;
需求: 输入一个num,计算从1到num的总和
DELIMITER $
CREATE PROCEDURE pro_sun(IN num INT,OUT summ INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE tem INT DEFAULT 0;
WHILE(i<=num) DO
SET tem=i+tem;
SET i=i+1;
END WHILE;
SET summ=tem;
END$
调用这个存储过程
CALL pro_sun(5,@zh);
SELECT @zh;
5 触发器
5.1 简介
当往员工表插入/修改/删除一条数据的时候,同时往日志表记录下来,这时就要使用触发器完成。
5.2 触发器语法
员工日志表
CREATE TABLE emp_log(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
)
需求: 当往员工表插入一条数据时,往员工日志插入一条记录。
创建增加触发器
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('员工被插入了一条记录');
创建修改触发器
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('员工被修改了一条记录');
创建删除触发器
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
INSERT INTO emp_log(content) VALUES('员工被删除了一条记录');
删除触发器
DROP TRIGGER tri_empAdd;
5.3 数据库的权限问题
mysql权限问题
root用户: 超级管理员。 权限: 增删改查(数据库,表,数据)
给mysql数据库分配不同权限的不同用户
mysql数据库:存放mysql配置信息,包括用户信息
USE mysql;
用户表
SELECT * FROM USER;
加密函数(md5算法–单向加密)
SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
修改用户密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
分配权限给不同的用户
需求: 分配查询day15数据库的employee表的权限给eric用户,密码为‘123456’
GRANT SELECT ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';