MySQL学习笔记_02

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


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值