数据库基础知识

数据库范式

第一范式（1NF）

属性不可分

即数据库表的每一列都是不可分割的基本数据项

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式（2NF）

不存在组合关键字的某些字段决定非关键字段（组合关键字不可拆）

属性完全依赖于主键 （消除部分子函数依赖）

例：假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，存在如下决定关系：

(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)

这个数据库表不满足第二范式，因为存在如下决定关系：

(课程名称) → (学分)

(学号) → (姓名, 年龄)

即存在组合关键字中的字段决定非关键字的情况。

正确建表：

学生：Student(学号, 姓名, 年龄)；

课程：Course(课程名称, 学分)；

选课关系：SelectCourse(学号, 课程名称, 成绩)。

这样的数据库表是符合第二范式的， 消除了数据冗余、更新异常、插入异常和删除异常。

第三范式（3NF）

属性不依赖于其它非主属性（消除传递依赖）

例：假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，关键字为单一关键字"学号"，存在如下决定关系：

(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)

这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：

(学号) → (所在学院) → (学院地点, 学院电话)

即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。

正确建表：

学生：(学号, 姓名, 年龄, 所在学院)；

学院：(学院, 地点, 电话)。

BCNF

不存在关键字段决定关键字段

例：假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID, 存储物品ID) →(管理员ID, 数量)

(管理员ID, 存储物品ID) → (仓库ID, 数量)

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID) → (管理员ID)

(管理员ID) → (仓库ID)

即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。

正确建表：

仓库管理：StorehouseManage(仓库ID, 管理员ID)；

仓库：Storehouse(仓库ID, 存储物品ID, 数量)。

数据库基础

基本操作

建表

#创建表：create table 表名(列名 数据类型 约束，列名 数据类型 约束);
#建表约束：
#主键：primary key 每个表中只能有一列是主键，数据唯一不能重复，不能为空。
#唯一：unique数据不能重复，可以为空，每个表中可以有多列是唯一列
#默认值：default
#非空：not null
#自增：auto_increment
#外键约束：foreign key

创建一个简单的学生表：

create table studentInfo (
    number int primary key auto_increment,
    name varchar(45) unique not null,
    sex enum('男', '女'),
    age int default 18
);

#char(45): 字符串长度是45，长度固定，例如存“hello”，实际存"hel1o889998889..."，长度固定占45
#varchar(45): 字符串最大长度是45，例如存“hello”，实际存"hello"，实际存多少就占多少长度，不能超过45，一个字符占一个字节，最多存8千个字符
#nvarchar(45): 字符串最大长度是45，例如存“hello”，实际存"hello"，实际存多少就占多少长度，不能超过45。使用宽字符集，一个字符占两个字节，数据库最多存8千个字节，就是最多存4千个字符

插入

insert into 表名 values(...);
insert into 表名(...) values(...);

修改

#修改表：alter table 表名...
#1.增加列：alter table 表名 add column 列名 数据类型 约束;
alter table studentinfo add column school int;

#2.修改列的属性:alter table 表名 modify 列名 数据类型 约束;
alter table studentinfo modify school varchar(100);

#3.删除列：alter table 表名 drop 列名;
alter table studentinfo drop school;

#删除表:drop table 表名
drop table studentinfo;

#更新数据：update 表名 set 列名=新的值 where 条件;
update studentinfo set age=19;

update studentinfo set age=20 where name = '小明';

#删除数据：delete from 表名 where 条件;
delete from studentinfo where sex='女';

查询

#查询数据库：select 列名 from 行名;

#模糊查询：where 列名 like 模糊表达式;
#%：匹配任意0到n个字符，例如“小%”，可以查询小王，小青蛙。。。
#_：匹配任意一个字符，例如“张_”，可以匹配张三，张四。。。
select * from studentinfo where name like "%小%";
select * from studentinfo where sex like "%_";
select * from studentinfo;
select * from studentinfo where sex is not null;

#分页查询：limit a，b;(a是起始位置，b是偏移量，也就是多少行) （第几页-1）* 每一页显示几行
select * from studentinfo limit 4,2;
 
#聚合函数
#count():查询个数
select count(*) from studentinfo;
select count(name) from studentinfo;
select count(sex) from studentinfo;

#sum():求和
select sum(score) from sc;
select sum(score) from sc where C='01';
select sum(score) from sc where C='02';

#max():最大值
select max(score) from sc where C='01';

#min():最小值
select min(score) from sc where C='02';

#avg():平均值
select avg(score) from sc where C='01';

#分组：group by 分组条件;
#每门课程的总成绩
select C,sum(score) from sc group by C;

#每个学生的总成绩
select S,sum(score) from sc group by S;

#查询总成绩超过200分的学号和总成绩
select S,sum(score) from sc group by S having sum(score)>=200;

#条件: where 条件规则;
#相等:列名 = 条件;
#大于、小于、大于等于、小于等于:列名 > 条件;列名 < 条件;列名 = 条件，列名 <= 条件;
#不相等:列名 != 条件;只有MYSQL支持 其他: 列名<>值;
#或者: 条件1 or 条件2;
#并且: 条件1 and 条件2;
#介于两者之间: between 条件1 and 条件2;
#在范围内: in (范围);
#不在范围内: not in (范围);

#条件where和having
#当作为条件的列是表中原来就有的，用where加
#当作为条件的列是表中原来没有的，用having加

 多表联查：

#多表联查:
#1、内联: 表1 inner join  2 on 连接条件 inner join 表3 on 连接条件);
select * from student inner join sc on sc.S = student.S;
#2、左联:表1 left ioin 表2 on 连接条件;
select * from student left join sc on sc.S = student.S;
#3、右联:表1 right join 表2 on 连接条件;
select * from student right join sc on sc.S = student.S;
#4、笛卡尔积:select 表1，表2 where 连接条件;
select * from student, sc where sc.S = student.S;

#区别：
#内联: 查询结果取两个表的交集，不包含空的结果
#左联:查询结果以左边表为基准，匹配行，如果没有就显示空
#右联:查询结果以右边表为基准，匹配行，如果没有就显示空
#笛卡尔积:查询结果和内联一致，但是查询效率低，不推荐使用

视图

#视图：view
#视图是为了简化复杂的sql语句提出的概念。视图是一个表或者多个表导出的虚拟表，不是真实存在的，不需要满足范式的要求。
#视图创建好以后长久保留在数据库库中，可以通过视图名使用，跟查询表一样使用。
#创建视图：create view 视图名 as (sql语句);
create view myview as(
select student.*,C,score from student inner join sc on sc.s=student.s
);

函数

#创建函数的语法
#delimiter //  #声明//为新的结束标志 
#create function 函数名(变量名 类型，变量名 类型)
#returns 返回值类型 
#begin
#函数语句;
#函数语句;
#end //
#delimiter ;  #重新声明;为结束标志

#int add(int a,int b)
#{
#    int c=0;
#    c=a+b;
#    return c;
#}

#例子:加法函数
drop function if exists myadd;
delimiter //
create function myadd(a int,b int)
returns int
begin 
	declare c int default 0;
    set c = a + b;
    return c;
end //
delimiter ;
    
#调用函数：select 函数名(参数列表);
select myadd(3,5);

#删除函数：drop function 函数名;
drop function myadd;

#判断是否存在再删除：drop function if exists 函数名;
drop function if exists myadd;

全局变量、局部变量

#局部变量：
#会话变量：@变量名 
set @a = 100;
select @a;

#系统变量(全局变量):
show global variables;
select @@basedir;

 if 语法

#if语法：
#if 表达式1 then 执行语句;执行语句;执行语句;
#elseif 表达式2 then 执行语句;执行语句;执行语句;
#else 执行语句;
#end if;

#例子：判断输入的值是正数、负数和零 
drop function if exists myfun;
delimiter //
create function myfun(a int)
returns varchar(100)
begin
	declare res varchar(100) default '';
    if a>0 then set res='正数';
    elseif a<0 then set res='负数';
    else set res='零';
    end if;
    return res;
end //
delimiter ;
select myfun(-10);

case 语法

#case:
#case 变量 when 值1 then 执行语句;
#          when 值2 then 执行语句;
#          when 值3 then 执行语句;
#end case;
drop function if exists myfun;
delimiter //
create function myfun(a int)
returns varchar(100)
begin
	declare res varchar(100) default '';
	case a when 10 then set res='十';
		   when 0 then set res='零';
           when -10 then set res='负十';
    end case;
    return res;
end //
delimiter ;
select myfun(0);

#case when 表达式1 then 执行语句;
#     when 表达式2 then 执行语句;
#     when 表达式3 then 执行语句;
#end case;
drop function if exists myfun;
delimiter //
create function myfun(a int)
returns varchar(100)
begin
	declare res varchar(100) default '';
	case when (a=0) then set res='零';
		 when (a>0) then set res='正数';
		 when (a<0) then set res='负数';
    end case;
    return res;
end //
delimiter ;
select myfun(10);

 循环

#1、while循环
#while 循环条件
#do
#执行语句;
#end while;

#例子：累加，计算1+2+...+n
drop function if exists mysum;
delimiter //
create function mysum(n int)
returns int
begin
	declare nSum int default 0;
	declare i int default 1;
	while i <= n
    do
		set nSum = nSum + i;
		set i = i + 1;
	end while;
	return nSum;
end //
delimiter ;
select mysum(100);

#2、其他循环: mysql没有for循环，有repeat和loop循环

存储过程

#存储过程:procedure，大型数据库中一组为了完成特定功能的sql语句的集合。存储在数据库中，一次编译永久有效。
#优点:
#1、减少网络流量
#2、增强代码的重用性和共享性
#3、加快系统运行速度
#4、使用灵活

#创建存储过程语法:
#delimiter //
#create procedure 过程名(变量名 类型，变量名 类型)
#begin
#sql语句;
#sql语句;
#end //
#delimiter ;
#存储过程没有返回值，但是可以通过定义参数的类型，IN|OUT|INOUT三种参数类型，不定义默认是输入类型参数。

#调用存储过程: ca11 存储过程名(参数列表);
#删除: drop procedure 过程名;
#drop procedure if exists 过程名:

#函数和存储过程的区别:
#1、函数中不能写sql语句，存储过程中可以写sql语句，存储过程中可以调用函数。
#2、函数有返回值，存储过程没有返回值，但是可以声明参数为OUT|INOUT类型来返回数据。

#写一个分页的存储过程。参数：当前是第几页(nPage int)，每页显示多少行(nCount int)，查询哪个表(tableName varchar(20))。
#要求做参数校验：如果页数小于等于0，就显示第一页数据；如果页数超过最大页数，就显示最后一页的数据。
#先写固定查student表
#再写拼表名
drop procedure if exists limitpro;
delimiter //
create procedure limitpro(nPage int, nCount int)
begin
	declare nOffset int default 0;
    declare nMaxPage int default 0;
    declare nSumCount int default 0;
    #查询数据行数
    select count(*) into nSumCount from student;
    #计算最大页数
    set nMaxPage = ceil(nSumCount / nCount);
    #判断页数合法性
    if (nPage <= 0) then set nPage = 1;
    end if;
    if (nPage > nMaxPage) then set nPage = nMaxPage;
    end if;
    
    set nOffset=(nPage-1)*nCount;
    select * from student limit nOffset,nCount;
end //
delimiter ;
call limitpro(2,6);

触发器

#创建触发器语法:
#delimiter //
#create trigger 触发器名 #触发器没有参数，也没有返回值
#after/before 操作       #在。。。操作之前或者之后
#on 表名				 #产生触发的表(对哪个表进行操作)
#for each row			 #影响每一行
#begin
#sql语句;
#end //
#delimiter ;

#例子: 删除student表的时候，同时删除sc表中这个同学的选课信息
delimiter //
create trigger mydel
after delete
on student
for each row
begin
	#同时删除sc表中这个同学的选课信息
    delete from sc where S = old.S;
    #old表: 删除的数据或者修改之前的数据
    #new表: 新增加的数据或者修改之后的数据
end //
delimiter ;
delete from student where s = '01';

#增加
delimiter //
create trigger myinsert
after insert
on student
for each row
begin
	#同时帮这个同学自动选择01课程
    insert into sc(S,C) values (new.S,'01'); 
end //
delimiter ;
insert into student(S,Sname,Ssex) values (1,'李雪','女'); 

#修改数据例子：修改学号的时候，同时修改sc表中的学号
delimiter //
create trigger myupdate
after update
on student
for each row
begin
	#同时修改sc表中的学号
    update sc set S=new.S where S=old.S;
end //
delimiter ;
update student set s='20' where s='1';

事务

#事务:作为单个逻辑单元执行的一系列sql语句的集合，操作要么都执行，要么都不执行。
#4个特性:ACID
#原子性:事务是最小的工作单元，不可再分，要么都执行，要么都不执行。
#一致性:数据库的完整性约束不能被破坏。
#隔离性:并行执行的多个事务，多个事务互相不影响。
#持久性:事务数据提交以后，持久的保存在数据库中。

#语法: transaction
#开启事务: start transaction;
#一系列sql语句;
#提交: commit;
#回滚: rollback;
start transaction;
update bank set money = money + 50 where name ='小李';
update bank set money = money - 50 where name ='小赵';
select * from bank;
commit;