day23_mysql

今日内容

零、 复习昨日
一、函数[了解,会用]
二、事务[重点,理解,面试]
三、数据库范式

零、 复习昨日

见晨考

一、函数

• 字符串函数
• 数学函数
• 日期函数
• 日期-字符串转换函数
• 流程函数

1.1 字符串函数

-- 今天讲的这些函数,可以应用在CRUD中都行
-- =========== 字符串函数 ============
-- concat(str1,....) 连接字符串 【重要】
select concat('a','1','b','2') from dual;
select concat('a','1','b','2'),sid,sname from stu;
select concat(sid,sname),sid,sname from stu;
select concat('',sname),sid,sname from stu;
select concat('我叫',sname,',今年',age,'明年',age+1,'岁') from stu;

-- left(string2,length) 从 string2 中的左边起取 length 个字符
select left('java',2)
select left(sname,1) from stu; -- 取出姓氏

-- length 获得长度 , utf8中,一个中文三个字节
select length(sname),sname from stu;
select length('abc');

-- 替换
-- REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str
select replace('java','av','AV');
select replace(sname,'三','叁') from stu;

-- SUBSTRING (str , position [,length ] 截取
select substring('java',2); -- 从第2位,取到末尾
select substring('java',2,2); -- 从第2位,取2个
-- 取出stu表中姓名,姓,名
select sname 姓名 ,left(sname,1) 姓,substring(sname,2) 名 from stu;
-- 插入时截取部分数据插入
insert into stu (sname) value('java');
insert into stu (sname) value(substring('java',2,2));
-- 更新时取部分数据更新
update stu set sname = left('史密斯',1) where sid = 1011

-- ltrim rtrim trim 虑空
select ltrim(' java '),rtrim(' java '),trim(' java ');
select length(' java '),length(ltrim(' java ')),length(rtrim(' java ')),length(trim(' java '));
-- 插入时使用虑空
insert into pet(id,nick,weight)
values(64,trim(' jerry '),1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

2.2 数学函数

-- =========== 数学函数 ============
select abs(-1);
select ceiling(1.1),floor(1.1);
-- 数字格式化 fromat(x,d) x是原始数据,d是保留的小数位数(会四舍五入)
select format(100,1);
select format(100.15,1);
select format(100.14,1);

select avg(score) from stu;
select format(avg(score),1) from stu;

-- 数字格式化truncate(x,d) x是原始数据,d是保留的小数位数(不会四舍五入)
select truncate(100.9,0);
select truncate(100.19,1);
select truncate(100.11,1);

-- 四舍五入 round(x[,d]) x是必填,原始数据
select round(100.91); -- 默认是将小数四舍五入后变整数
select round(100.91,1); -- 参数2是保留小数位数
select round(100.99,1); 
select round(100.999,2); 

-- 随机数 rand() 0-1之间的小数
select rand();
select ceiling(rand() * 30);
insert into tb_1 (age) values (ceiling(rand() * 30))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.3 日期函数【重要】

-- =========== 日期函数 ============
-- 获得当前日期时间
select now(); -- now() 当前日期和时间
select sysdate(); -- sysdate()当前系统日期和时间
select current_date(); -- 获得当前日期
select current_time();-- 获得时分秒
select current_timestamp();-- 获得当前时间戳
update tb_order set order_time = now() where oid = 1;

-- 单独获得年,月,日
select year(now());
select year('1970-01-01');
select year(order_time) from tb_order
select month('1970-01-01');
select day('1970-01-01');
-- 查询2023年的订单
select * from tb_order where year(order_time) = 2000
-- 当月生日人数
select count(*) from t10 where month(birthday) = month(now())
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.4 日期字符串转换函数【重要】

常见的模板:

• %Y-%m-%d 2000-01-02
• %Y年%m月%d日 2000年02月02日

%Y：代表4位的年份

%y：代表2为的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)

%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)

%k：代表 小时,格式为(0……23)

%h： 代表小时,格式为(01……12)

%I： 代表小时,格式为(01……12)

%l ：代表小时,格式为(1……12)

%i： 代表分钟, 格式为(00……59)

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表 时间,格式为24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59)

%s：代表 秒,格式为(00……59)

-- =========== 日期/字符串转换函数 ============
/*
 日期 --> 字符串  date_format(date,'%Y-%m-%d')
 字符串 --> 日期 str_to_date('datestr','%Y-%m-%d') 
 ---------------------
 日期模板
 %Y年 %m月 %d日
 %H时 %i分钟 %S秒
*/
select date_format(now(),'%Y年%m月%d日')
select str_to_date('2022年11月18日','%Y年%m月%d日')

insert into t10 (id,birthday) value (1,str_to_date('2020-01-01','%Y-%m-%d'))
1
2
3
4
5
6
7
8
9
10
11
12
13

2.5 流程函数【重要！！】

-- inst(string,substring) 查找substring在string中的位置,找不到返回0
select if(instr('java','big') > 0,'存在','不存在');
-- 查询学生姓名,已经是否名字含三
select 
	sname,
	if(instr(sname,'三') > 0,'是','否') 
		as 是否含三
from stu;
-- ================== 流程函数 ================
select if(1>0,'真','false') from dual;

-- 获得所有人的平均分
select sum(score) / count(sid) from stu;
select avg(if(score is null,0,score)) from stu;
-- 查询学生学号,成绩,以及是否及格(>=60)
select sid , score , if(score >= 60,'及格','不及格') 是否及格 from stu;
-- 查询学生学号,成绩,假如没有成绩显示缺考
select sid , if(score is null,'缺考',score) from stu;

-- 计算年龄大于50的人数
select count(sid) from stu where age > 50
select count(if(age < 50,null,sid)) from stu

-- 查询学生学号,成绩,假如没有成绩显示缺考
select sid , ifnull(score,'缺考') from stu;
-- 范围判断
-- CASE WHEN [expr1] THEN [result1]… ELSE [default] END	如果expr是真, 返回result1,否则返回default
-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,score,case
when score < 60 then '不及格'
when score <= 70 then '及格'
when score <= 80 then '中等'
when score <= 90 then '良好'
else '优秀'
end as 等级
from stu
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

二、事务[面试]

事务 transaction tx

2.1 介绍

什么是事务?

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。(一组操作同时成功,或同时失败)

---

场景:

银行转账操作,A账号要给B账户转钱. A原有1000块,B原有1000块.A转账给B 100元

A的钱要减少,B的钱要增多

update A set money = money - 100 where id = a

-- 服务器出现异常,导致后面的sql没有执行

update B set money = money + 100 where id = b
1
2
3
4
5

2.2 mysql中事务

• 目前使用的mysql支持事务操作
• mysql中的事务是每个sql语句都是单独事务,且自动提交事务的
  • 通过命令查询当前事务的提交方式 SHOW VARIABLES like ‘autocommit’
  • 通过命令设置自动提交关闭 set autocommit = off / 或者= 0 关
  • set autocommit = on / 或者= 1 开
• 事务的操作
  • 开启事务 start transaction 或者 begin
  • 提交事务 commit
  • 回滚事务 rollback

2.3 演示事务

准备数据

CREATE TABLE `account` (
 `id` int(50) NOT NULL,
 `name` varchar(50) NOT NULL,
 `money` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO account VALUES(1,'张三',1000);
INSERT INTO account VALUES(2,'李四',1000);
1
2
3
4
5
6
7

-- =============================================
-- 开启事务
start transaction;
-- 开始转账
update account set money = money - 100 where id = 1
-- 出大事了,后面执行不了
update account set money = money + 100 where id = 2
-- 如果一切正常,提交事务
commit;
-- 服务器出现异常,要回滚
rollback;
-- 查询当前事务提交方式
SHOW VARIABLES  like 'autocommit';
-- 手动控制事务,自动提交关闭
set autocommit = off;

-- ============= java 伪代码 ==================
try{
 conn.setAutocommit(false); -- 自动提交,开启手动事务
 conn.execute("update ....")
 System.out.print(1/0)
 conn.execute("update ....")
 conn.commit();   -- 提交
}catch(Exception e) { -- 如果有异常
 conn.rollback();  -- 回滚
}

-- 后面学框架,只需要配置一下就ok
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2.4 事务特性

事务的特性(ACID)

• 原子性（Atomicity）：指事务的整个操作是一个整体，要么都成功,要么都失败
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致 性状态。转账前和转账后的总金额不变。
• 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一 个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永 久性的，接下来即使数据库发生故障也不应该对其有任何影响。

2.5 事务的隔离机制

数据库有不同的隔离机制/隔离级别

1. 读未提交-READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。

2. 读已提交-READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。

   （oracle 默认的）

3. **可重复读-**REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。

   （mysql 默认）,行锁

4. **串行化-**SERIALIZABLE: 避免赃读、不可重复读、虚读。

   串行化,其实是表锁

查看当前数据库的隔离级别: SELECT @@TX_ISOLATION;

更改当前的事务隔离级别：

SET [glogal | session] TRANSACTION ISOLATION LEVEL 四个级别之一。

赃读：指一个事务读取了另一个事务未提交的数据。

对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.  之后, 若 T2 回滚, T1读取的内容就是临时且无效的.  
1

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）

  对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.  之后, T1再次读取同一个字段, 值就不同了.  
1

虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 (insert)

对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中  插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行
1

总结

1 什么是事务
2 mysql事务怎么操作(开启,提交,回滚)
3 事务什么特点(ACID)
4 什么是事务隔离(能解释)
5 有哪些级别,简单解释
6 mysql默认什么级别
1
2
3
4
5
6

三、数据库设计三范式

第一范式：要求数据表中的字段（列）不可再分(原子性)

第二范式：不存在非关键字段对关键字段的部分依赖

ps: 主要是针对联合主键,非主键不能只依赖联合主键的一部分

• 联合主键,即多个列组成的主键

第三范式：不存在非关键字段之间的传递依赖

四、E-R图

ER图 Entry-relationship 实体关系图

• 属性是椭圆
• 实体是矩形
• 关联是实线
• 关联关系是菱形

数据库还有索引,视图、触发器、存储过程、存储函数、权限控制,用户管理等知识

还有很重要的SQL优化