首页 > 代码库 > MySql学习2

MySql学习2

 

 

1 数据约束

1.1 简介

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

 

1.2 默认值约束

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

 

-- 1.1 默认值(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;

 

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

-- 注意: 1)当没有插入gender字段的时候,分配一个默认值

 

1.3 非空约束

-- 1.2 非空(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

INSERT INTO test(NAME,gender) VALUES(NULL,‘‘); -- ERROR 1048 (23000): Column ‘NAME‘ cannot be null

 

1.4 唯一约束

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 主键约束

-- 1.4 主键约束(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

-- 1.5 自增长约束(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(‘王五‘);

 

 

-- 删除数据

-- delete from test where id=2;

-- 使用truncate table删除数据的时候,可以把自增长的初始值置为0

TRUNCATE TABLE test;

 

SELECT * FROM test;

 

 

1.7 外键约束

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

问题:什么情况下会出现两种表?

答案:员工表中出现部门名称的冗余字段!!!!

如何解决部门名称冗余问题?

答案:独立设计一张部门表,把部门名称放到部门表中,这是员工表只需要关联部门的id即可!!

问题: 当插入员工表的部门id的时候,可能会出现不存在的部门id!!这是非法数据!!!如何防止非法数据的插入?

答案: 这是可以把员工表的部门id设置为外键约束。

 

当有了外键约束之后,操作数据的顺序如下:

插入数据: 先插入主表的数据,再插入副表数据

修改数据: 先修改主表数据,再修改副表数据

  删除数据: 先删除副表数据,再删除主表数据

 

-- 1.6 外键约束

-- 外键作用: 约束两种表的数据

-- 问题: 约束哪种情况下的两种表呢?

-- 员工表

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 数据库设计的三大范式

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

 

学生表: 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 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: 输入输出参数。

 

 

-- 3.1 带有输入参数的存储过程

-- 需求: 传入员工id查询对应的员工

DELIMITER $

CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型(IN) 参数名称 数据类型(int)

BEGIN

SELECT * FROM employee WHERE id=eid;

END $

 

-- 调用

CALL pro_testByIn(2);

 

-- 3.2 带有输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))

BEGIN

-- 修改变量n

SET n = ‘输出参数‘;

END $

 

-- 问题: 如何接收存储过程的输出参数???

-- 定义变量去接收输出参数数据。

 

-- mysql数据库三种变量:

-- 1)全局变量。mysql内置的变量,mysql程序关闭的时候全局变量才会失效!!

            -- show variables:产看全局变量

-- character_set_client: mysql接收的客户端的数据编码

-- character_set_resultsmysql使用什么编码输出给客户端数据

        -- 查看某个全局变量: select @@变量名

        -- 修改某个全局变量: set @@变量名=

            -- 案例:使用黑dos窗口客户端测试插入和查询表数据因为编码问题产生的乱码现象

-- 2) 会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效!!   

    -- 查看某个会话变量: select @变量名

        -- 修改/定义某个会话变量: set @变量名=

            -- 案例:演示查询和更改会话变量

-- 3) 局部变量:在存储过程中定义的变量。存储过程结束局部变量失效!!

    -- 查看某个局部变量: select 变量名

        -- 修改某个局部变量: set 变量名=

        -- 定义某个局部变量: declare 变量名 数据类型;

        

-- 定义会话变量去接收输出参数数据         

-- set @n=‘eric‘;

CALL pro_testByOut(@n);

-- 查看会话变量n

SELECT @n;

 

-- 3.3 带有输入输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))

BEGIN

-- 查看n变量

SELECT n;

-- 修改n变量

SET n = ‘500‘;

END $

 

-- 定义会话变量调用存储过程

SET @n=‘100‘;

CALL pro_testByInOut(@n);

-- 查看n

SELECT @n;

 

-- 3.4 带有判断条件的存储过程

-- 需求: 输入一个num整数,num=1 ,输出‘星期一’,num=2,输出‘星期二’,num=3,输出‘星期三’,否则,输出‘错误参数’

DELIMITER $

CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20))

BEGIN

IF num=1 THEN

SET str = ‘星期一‘;

ELSEIF num= 2 THEN

SET str =‘星期二‘;

ELSEIF num=3 THEN

SET str = ‘星期三‘;

ELSE

SET str = ‘错误参数‘;

END IF;

END $

 

CALL pro_testByIf(5,@str);

SELECT @str;

 

-- 3.5 带有循环条件的存储过程

-- 需求: 输入一个num,计算从1num的总和。

DELIMITER $

CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)

BEGIN

-- int result =0;

-- for(int i=1;i<=100;i++){

-- result += i;

-- }

 

-- 定义局部变量

DECLARE i INT DEFAULT 1;

DECLARE result INT DEFAULT 0;

WHILE i<=num DO

SET result = result + i;

SET i = i+1;

END WHILE;

SET score = result;

END $

 

 

CALL pro_testByWhile(200,@score);

SELECT @score;

 

-- 3.6 携带数据库的数据给输出参数(INTO)

-- 需求: 传入员工id,查询对应的员工,输出员工姓名

DELIMITER $

CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))

BEGIN

SELECT NAME INTO sname FROM employee WHERE id=eid;

END $

 

CALL pro_testByData(2,@sname);

SELECT @sname;ss

 

-- 练习

存储过程练习

student表的数据基础上,设计一个存储过程:

 

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

2

如果平均分,大于80分,且小于等于100分,输出优秀

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

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

USE day14;

 

SELECT * FROM student2;

 

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;

 

 

CALL pro_testByAvg(@str,@tavg);

SELECT  @str;

SELECT @tavg;

 

 

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;

 

SELECT * FROM employee;

SELECT * FROM emp_log;

 

 

INSERT INTO employee(NAME,deptId,bossId) VALUES(‘李七‘,5,1);

UPDATE employee SET NAME=‘张三‘ WHERE id=10;

DELETE FROM employee WHERE id=10;

 

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‘;

GRANT DELETE ON day15.employee TO ‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

 

SHOW TABLES;

 

MySql学习2