数据库内部理论

数据库设计

E-R模型
E-R模型的基本元素是:实体、联系和属性,
E表示entry,实体:一个数据对象,描述具有相同特征的事物
R表示relationship,联系:表示一个或多个实体之间的关联关系,关系的类型包括包括一对一、一对多、多对多
属性:实体的某一特性称为属性
关系也是一种数据,需要通过一个字段存储在表中
1、实体A对实体B为1对1,则在表A或表B中创建一个字段,存储另一个表的主键值

2、实体A对实体B为1对多:在表B中创建一个字段,存储表A的主键值

3、实体A对实体B为多对多:新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值

函数

字符串函数
拼接字符串concat(str1,str2...)
select concat(12, 34, 'ab');
包含字符个数length(str)，中文是3，其他字符是1
select length('c');
截取字符串
left(str,len)返回字符串str的左端len个字符
right(strlen)返回字符串str的右端len个字符
substring(str.pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3);

去除空格
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回制除了右空格的字符串str
select Ltrim(' bar ')
大小写转换,函数如下
lower(str)
upper(str)
select lower('aBcD')

数学函数
求四舍五入值round(n,d), n表示原数, d表示小数位置,默认为0
select round(1.6);
求x的y次幂pow(x.y)
select pow(2,3);
获取圆周率PI()
select PI();
随机数rand(),值为0-1.0的浮点数
select rand();

流程控制
case语法:等值判断
说明:当值等于某个比较值的时候,对应的结果会被返回;如果所有的比较值都不相等则返回else的结果;如果没有else并且所有比较值都不相等则返回null

case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 ... else 结果 end
例：
select case 1 when 1 then 'one' when 2 then 'two' else 'zero' end as result;

自定义函数

创建
语法如下
delimiter $s
create function 函数名称(想数列表) returns 返回类型
begin
sql语句
end
$s
delimiter;
说明: delimiter用于设置分割符,默认为分号

示例
要求:创建函数my trim,用于删除字符串左右两侧的空格
step1:设置分割符
delimiter $s
step2:创建函数
create function my_trim(str varchar(100)) returns varchar(100）
begin
return Itrimfrtrim(str));
end
$$
step3:还原分割符
delimiter ;
使用自定义函数
select 'abc',my_trim('abc')

视图
对于复杂的查询,在多个地方被使用,如果需求发生了改变,需要更改sql语句,则需要在多个地方进行
修改，维护起来非常麻烦
解决：定义视图
视图本质就是对查询的封装
定义视图,建议以v开头

create view 视图名称 as select语句;
例:创建视图,查询学生对应的成绩信息
create view v_stu_score_course as
select
stu. *,cs.courseNo,cs.name courseName,sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo

事务
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如: A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:
1.检查A的账户余额>500元;
2.A账户中扣除500元;
3.B账户中增加500元;
正常的流程走下来, A账户扣了500, B账户加了500,皆大欢喜。那如果A账户扣了钱之后,系统出故障了呢? A白白损失了500,而B也没有收到本该属于他的500,以上的案例中,隐藏着一个前提条件:A扣
钱和B加钱,要么同时成功,要么同时失败。事务的需求就在于此
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。所
以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性
事务命令
要求:表的引擎类型必须是innodb类型才可以使用事务,这是mysql表的默认引擎
查看表的创建语句,可以看到engine=innodb

事务中的两部操作

begin;
所有操作都成功
commit;

begin;
任何一步失败
rollback;