mysql三大范式及数据约束及查询及存储过程

三大范式

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

第二范式：在第一范式的基础上，要求一个表只表达一个意思。

第三范式：在第二范式的基础伤，要求表中的除主键以外的的其他字段都和主键发生直接关联。

数据约束

默认值

create table student(
    id INT,
    name VARCHAR(20),
    gender varchar(20) default '男'
)

非空

name varchar(20) not null,

唯一

id INT UNIQUE,

主键

id int primary key,

自增长(auto_increment)(zerofill不满位数自动填充)

id int(4) zerofill primary key auto_increment,

外键

create table dept(
    id int primary key auto_increment,
    deptName varchar(29)
)

create table employee(
    id int primary key auto_increment,
    name varchar(20),
    deptId int,
    constraint employee_dept_fk FOREIGN KEY(deptId) references dept(id)
)

级联操作

连接查询（多表查询）

交叉查询

select employee.name,dept.deptName from employee,dept;

内连接查询

select e.Name,d.deptName
    from employee e,dept d
    where e.deptId=d.id;

内链接的第二种方法

select e.Name,d.deptName
    FROM employee e
    inner join dept d
    on e.deptId=d.id;

左连接(把左边的表的内容填满，右边没内容为null)

select d.deptName,e.name
    from dept d
    left outer join employee e
    on e.deptId=d.id;

右外连接

select d.deptName,e.name
    from employee e
    right outer join dept d
    on e.deptId=d.id;

存储过程

DELIMIT $
CREATE PROCEDURE pro_test()
BEGIN 
    select * from employee;
END$

-- 调用
CALL pro_test();

参数类型

IN:输入参数
OUT:输出参数
INOUT:输入输出参数

Delimiter $
create procedure pro_findById(IN eid INT)
BEGIN
    select * from employee where id=eid;
end$

-- 调用
call pro_findById(3);

带有输入参数的存储过程

delimiter $
create procedure pro_findById(IN eid INT)
BEGIN
    select * from employee where id=eid;
end$

-- 调用
Call pro_findById(3);

带有输出参数的存储过程(SET n=‘输出参数’)

show varables:查询全部变量

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

set 变量名=值:设置全局变量

character_set_client:设置mysql数据库接受数据的默认字符串

character_set_results:mysql数据库输出数据的默认字符集

---

会话变量（变量只在当前连接会中有效）

set @变量名=value：折这会话变量

select @变量:查看会话变量

局部变量

set 变量=value:设置局部变量

select 变量:查看局部变量

-- 声明一个会话变量去接受输出参数
CALL pro_testByOut(@n);
-- 查看会话变量
select @n;

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

delimiter $
create procedure pro_testByInOut(INOUT n VARCHAR(10))
begin
    select n;
    set n='500';
end $

-- 创建会话变量
    set @n='10';
-- 调用存储过程
Call pro_testByInOut(@n);
-- 查看
select @n;

带有判断条件的存储过程

delimiter $
create procedure pro_testByIf(In num int,out str varchar(20))
begin
    if num=1 then
        set str='monday';
    elseif num=2 then
        set str='wendesday';
    elseif num=3 then
        set str='星期三';
    else
        set str='错误参数';
    end if;
end $

Call pro_testByIf(4,@n);

SELECT @n;

带有循环条件的存储过程

delimiter $
create procedure pro_testByWhile(In num int, out vsum int)
begin
    -- 声明局部变量
    declare i int default 1;
    declare asum int defalt 0;
    while i<num do
        set asum=asum+i;
        set i=i+1;
    end while;
    set vsum=asum;
end$

Call pro_testByWhile(200,@vsum);

select @vsum;

接收表中的数据的存储过程

delimiter $
create procedure pro_findById2(IN eid INT,OUT aname varchar(20))
begin
    select name into aname from employee where id =eid;
end$

call pro_findById2(3,@aname);
select @aname;

触发器

插入的表的与触发的表要不同

create trigger test_insert
after insert on employee 
for each row
insert into body(contendt)values('往员工插入了一条数据');

删除数据触发

create trigger test_delete 
after delete on student
for each row
insert into body(content)values('往员工表删除了一条数据');