JAVA全栈开发 day18MySql03

2023-12-13 05:01:22

一、复习

为什么要用数据库

数据库好处

数据库的发展史

?	层次模型

?    网状模型

?    关系模型（二维表专门存储数据， 表与表的关联）

?         表与表的关系： 1对1   ，1对多，多对多

?     非关系模型

  关系模型与非关系的区别

  关系数据库有哪些？mysql , oracle , sql server , sybase , db2 等

?     mysql :几十M 开源**免费**得的数据库，体积非常小

?    oracle : 性能高，收费非常贵

关系型数据库的优缺点

优点：1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；

2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；

3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；

4、支持SQL，可用于复杂的查询。
缺点：

1、为了维护一致性所付出的巨大代价就是其读写性能比较差；

2、固定的表结构；

3、高并发读写需求；

4、海量数据的高效率读写

DBMS, RDBMS

mysql 数据库
mysql 8.x

mysql 5.x

SQL：结构化查询语言

ＤＤＬ

ＤＭＬ

ＤＱＬ

ＤＣＬ

ＤＤＬ：

创建数据库

使用数据库

删除数据库

创建表

修改表

删除表
ＤＭＬ

插入数据 insert into

? 删除数据 delete from xxx , TRUNCATE TABLE 表名

? 修改数据 update
ＤＱＬ

简单查询

条件查询

模糊查询

范围：between xx and xxx , in , not in

聚合函数

分组函数

子查询

表联接（重点）

存储引擎（有哪些，各有什么特点）

事务（多个ｓｑｌ操作看成一个不可分割整体，称为事务）

ＡＣＩＤ四大特性

数据库共定义了四种隔离级别： 
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化） 
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）(mysql默认) 
Read committed：可避免脏读情况发生（读已提交）。 
Read uncommitted：最低级别，以上情况均无法保证。(读未提交) 

－－－－－－－－－－－－－－－－ 

数据库的数据类型

int, float, double, decimal , char , varchar , text , nchar, nvarchar, ntext 



数据完整性（完整性 ＝ 准备性 ＋ 可靠性）

实体完整性（保证行的有效性）： 主键约束，唯一约束，标识列

域完整性（保证列的有效性）：default , not null , check约束

引用完整性（保证引用有效性）： 外键约束

?    外键

?    外键约束

   （注：互联网一般不加外键，在代码中保证它的有效性）

自定义完整性



补充： 主键约束，唯一约束

 主键约束： 加了主键约束，值不能重复（不能为null）, 主键可以组合列

 唯一约束：值不能重复(可以null)

二、编码

字符集：是一套符号和编码的规则
校验规则：是对该套符号和编码的校验，定义符号的排序和比较规则，其中区分大小写，跟校验规则有关。

show character set 查询mysql支持的字符集

gbk_chinese_ci 不区分大小写
gbk_bin 区分大小写

//避免创建数据库及表出现中文乱码和查看编码方法
//1、创建数据库的时候：
CREATE DATABASE `test`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
//2、建表的时候
CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

三、mysql 函数的使用

1 字符串函数

charset(str) //返回字符串字符集 

concat (string2 [,… ]) //连接字串 

instr (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 

lcase (string2 ) //转换成小写 

left (string2 ,length ) //从string2中的左边起取length个字符 

length (string ) //string长度 

load_file (file_name ) //从文件读取内容 

locate (substring , string [,start_position ] ) 同instr,但可指定开始位置 

lpad (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length 

ltrim (string2 ) //去除前端空格 

repeat (string2 ,count ) //重复count次 

replace (str ,search_str ,replace_str ) //在str中用replace_str替换search_str 

rpad (string2 ,length ,pad) //在str后用pad补充,直到长度为length 

rtrim (string2 ) //去除后端空格 

strcmp (string1 ,string2 ) //逐字符比较两字串大小, 

substring (str , position [,length ]) //从str的position开始,取length个字符, 

注：mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1

2日期函数

addtime (date2 ,time_interval ) //将time_interval加到date2 

convert_tz (datetime2 ,fromtz ,totz ) //转换时区 

current_date ( ) //当前日期 

current_time ( ) //当前时间 

current_timestamp ( ) //当前时间戳 

date (datetime ) //返回datetime的日期部分 

**date_add (date2 , interval d_value d_type )** //在date2中加上日期或时间 

date_format (datetime ,formatcodes ) //使用formatcodes格式显示datetime 

date_sub (date2 , interval d_value d_type ) //在date2上减去一个时间 

**datediff (date1 ,date2 ) //两个日期差** 

day (date ) //返回日期的天 

dayname (date ) //英文星期 

dayofweek (date ) //星期(1-7) ,1为星期天 

dayofyear (date ) //一年中的第几天 

extract (interval_name from date ) //从date中提取日期的指定部分 

makedate (year ,day ) //给出年及年中的第几天,生成日期串 

maketime (hour ,minute ,second ) //生成时间串 

monthname (date ) //英文月份名 

now ( ) //当前时间 

sec_to_time (seconds ) //秒数转成时间 

str_to_date (string ,format ) //字串转成时间,以format格式显示 

timediff (datetime1 ,datetime2 ) //两个时间差 

time_to_sec (time ) //时间转秒数] 

week (date_time [,start_of_week ]) //第几周 

year (datetime ) //年份 

dayofmonth(datetime) //月的第几天 

hour(datetime) //小时 

last_day(date) //date的月的最后日期 

microsecond(datetime) //微秒 

month(datetime) //月

3 数字函数

abs (number2 ) //绝对值 

bin (decimal_number ) //十进制转二进制 

ceiling (number2 ) //向上取整 

conv(number2,from_base,to_base) //进制转换 

floor (number2 ) //向下取整 

format (number,decimal_places ) //保留小数位数 

hex (decimalnumber ) //转十六进制 

注：hex()中可传入字符串,则返回其asc-11码,如hex(‘def’)返回4142143 

也可以传入十进制整数,返回其十六进制编码,如hex(25)返回19 

least (number , number2 [,..]) //求最小值 

mod (numerator ,denominator ) //求余 

power (number ,power ) //求指数 

rand([seed]) //随机数 

round (number [,decimals ]) //四舍五入,decimals为小数位数]

sqrt(number2) //开平方

minute(datetime) //分返回符号,正负或0

四、事务

事务指的是逻辑上的一组操作(多条sql语句),组成这组操作的各个单元要么全都成功,要么全都失败.

事务作用：保证在一个事务中多次操作要么全都成功,要么全都失败.

例如转账：

update account set money=money-100 where name=‘tom’;//tom转出100块

update account set money=money+100 where name=‘jerry’;//jerry收到100块

以上两条sql语句,很明显是一组语句,因为转账要么都成功要么都失败,不应该出现一方成功另一方失败的情况.那么我

1.ACID

原子性：强调事务的不可分割.多条语句要么都成功，要么都失败。
一致性：强调的是事务的执行的前后，数据要保持一致.
隔离性：一个事务的执行不应该受到其他事务的干扰.
持久性：事务一旦结束(提交/回滚)数据就持久保持到了数据库.

多个事务可现的情况：脏读，幻读，不可以重复读。

上述情况的解决方法：设置事务的隔离级别

2.事务的隔离级别

数据库共定义了四种隔离级别：
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）(mysql默认)
Read committed：可避免脏读情况发生（读已提交）。
Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

– 查询隔离级别
select @@tx_isolation;

– 设置隔离级别
set transaction isolation level Read uncommitted;

3.事务的操作

1、START TRANSACTION（或BEGIN）：开始一个事务。所有在该语句之后执行的语句都将视为该事务的一部分。

2、COMMIT：提交事务。如果事务成功，则所有修改将成为永久性的。如果提交失败，则事务将回滚到其开始状态。

3、ROLLBACK：撤消事务中进行的所有修改，并将数据库恢复到事务开始时的状态。

BEGIN; – 开始事务

INSERT INTO clazz VALUES(NULL,336);
INSERT INTO clazz VALUES(NULL,388);

COMMIT;

直接用 set 来改变 mysql 的自动提交模式:

set autocommit=0 禁止自动提交

set autocommit=1 开启自动提交

五、索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

在这里插入图片描述

1.索引优势劣势

优势

1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势

1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2）虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.索引分类

(1).普通索引

index :加速查找

(2).唯一索引

? 主键索引：primary key ：加速查找+约束(不为空且唯一)

? 唯一索引：unique：加速查找+约束 (唯一)

(3).联合索引

-primary key(id,name):联合主键索引

-unique(id,name):联合唯一索引

-index(id,name):联合普通索引

(4).全文索引

fulltext :用于搜索很长一篇文章的时候,效果最好。

(5).空间索引

spatial :了解就好,几乎不用

注意:不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引；

MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引；

Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引；

NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引；

Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引；

3.如何创建索引

https://www.cnblogs.com/wwqkrt/p/17420804.html

【什么聚集，什么非聚集】

六、数据库设计

在这里插入图片描述

E-R 图：

表里的一行数据：实体

? 表：实体的集合

关系型数据库：表，表的关系（即实体与实体之间的关系）

在这里插入图片描述

ATM 机：转账，存钱，取钱，查询

实体(表)：名词：

? ATM

属性：

需要存储的信息

用户( 账号，密码，金额，用户基本 )
软件实现中需要的数据

用户( 账号，密码，金额，用户基本, 账号分类，创建日期，状态，登录ip )

关系：

1.数据库设计步骤：

收集信息
找实体，名词，对象，实体
找属性（存储的数据，开发的需要添加的属性）
找实体与属性之间关系
E-R 图（实体：矩形，属性：椭圆，关系：菱形，连线：没有箭头）
编写数据字典（word文档，表，字段都进行记录）
画逻辑结构图
画物理结构图
生成相关的sql

数据字典

逻辑结构图（power designer）

2.数据库设计规范（3NF）

第一范式：列不可再分,行不可重复 (拆列,创建主键)

第二范式：满足1NF, 表中的所有非主键列必须依赖于整个主键列。

第三范式：满足2NF,每一列数据都和主键直接相关，而不能间接相关

总结：

范式一列不可再分(值必须唯一),行不可重复(定义主键)

范式二非主键列必须依赖于主键列(非主依主)

范式三非主键列之间必须相互独立(非主独立)

3.常见数据库设计

外键设计

如果有外键这一列，但是可以插入非法数据，针对外键插入的数据进行约束

约束有两种方式，一种是我们可以通过java代码在业务上进行限定，另外一种是通过数据库的外键约束

外键约束不等于外键

传统项目一般会使用外键约束

互联网项目不会使用数据库的外键约束,外键约束性能太低（业务上进行限定）

多对多的表设计（商品与订单的关系）

多对多的表设计:首先新建一张中间表，中间表有两个外键，分别来至于对方表的主键，并且这两个外键要建立一个联合主键（或者使用自增列做为主键）。

在这里插入图片描述

自连接

在这里插入图片描述

自关联查询

– 查询一级分类

select id,name from category where parent_id is null

– 查询二级分类

select id,name from category where parent_id = 1

– 查询三级分类

select id,name from category where parent_id = 5

（员工表中，即有员工，又有上级编号，怎么查询出上级的姓名？）

– 创建员工表

CREATE TABLE emp (

? id INT PRIMARY KEY AUTO_INCREMENT,

? NAME VARCHAR(10),

? gender CHAR(1), – 性别

? salary DOUBLE, – 工资

? join_date DATE, – 入职日期

? dept_id INT,

? FOREIGN KEY (dept_id) REFERENCES dept(id) – 外键，关联部门表(部门表的主键)

);

在这里插入图片描述

4.字典表设计

在这里插入图片描述

5.权限管理设计

在这里插入图片描述

文章来源:https://blog.csdn.net/weixin_64646820/article/details/134824569
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！

JAVA全栈开发 day18MySql03

一、复习

二、编码

三、mysql 函数的使用

1 字符串函数

2日期函数

3 数字函数

四、事务

1.ACID

2.事务的隔离级别

3.事务的操作

五、索引

1.索引优势劣势

2.索引分类

3.如何创建索引

六、数据库设计

1.数据库设计步骤：

数据字典

逻辑结构图（power designer）

2.数据库设计规范（3NF）

3.常见数据库设计

外键设计

多对多的表设计（商品与订单的关系）

自连接

自关联查询

4.字典表设计

5.权限管理设计