该笔记学习B站up主: 狂神说java 的mysql视频所得
链接 : https://www.bilibili.com/video/BV1NJ411J79W?from=search&seid=869715549617273125
数据库
概念 : 数据仓库,软件,安装在操作系统之上
作用 : 存储数据,管理数据
数据库分类
关系型数据库 (SQL)
通过表和表之间,列与行之间的关系进行数据存储
非关系型数据库 (NoSQL)
对象存储,通过对象的自身的属性决定
基本命令
-- 命令行连接
mysql -u用户名 -p密码
-- 修改密码
update mysql.user set authentication_string=password('新密码') where user='root' and HOST = 'localhost';
-- 刷新权限
flush privileges;
-- 查看所有的数据库
show databases;
--切换数据库
use 数据库名;
-- 查看所有的表
show tables;
-- 显示数据库中所有表的信息
describe 表名;
-- 创建数据库
create database 数据库名;
--退出连接
exit
DDL 定义
DML 操作
DQL 查询
DCL 控制
操作数据库
操作数据库>操作数据库中的表>操作数据库中的表中的数据
如果表名或者字段名是特殊字符的话,需要加上``符号
这个符号在tab键上面
-- 创建数据库(如果不存在则创建,判断条件可以加可不加)
create database if not exists 数据库名;
-- 移除数据库(如果存在则删除)
drop database if exists 数据库名;
-- 使用数据库
use 数据库名;
数据库列类型
数值
- tinyint 十分小 1个字节
- smalint 较小 2个字节
- mediumint 中等 3个字节
- int 标准的存储 4个字节
- bigint 较大的 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候一般使用这个
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65535
- tinytext 微型文本 一般用于博客
- text 文本串 保存大文本
时间日期
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year 年份表示
null
- 没有值或者未知的值
注意:不要使用NULL进行运算,使用NULL运算的结果为NULL
数据库的字段属性
Unsigned :
- 无符号整数
- 声明了该列不能声明为负数
zerofill
- 0填充的
- 不足的位数使用0来填充
自增
- 通常理解为自增,自动在上一条记录中的基础上+1
- 通常用来设计唯一的主键,必须是整数类型
- 可以自定义设置主键自增的起始值和步长
NULL 和 not NULL
- 设置为非空的话,如果不填写值得话会报错
- 设置为空的话,默认就是NULL
默认
- 设置默认的值
- 如果不指定该列的值,则会填充默认的值
拓展:一张表常用的字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
MySQL中数据类型的长度问题总结
- int类型
INT(1),你可以插数字1，也可以插100,1000。
int(M)
M最大显示宽度，最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关
INT（10）、INT（11）是指定显示宽度，不是存储大小或者值的范围，再看一个例子：
INT（3）的情况下 插入数字20，和数字2以及数字200，显示结果为：
020；
002；
200；
这样的话就是当你设置了宽度的时候,如果存的数值小于这个宽度的话.就会帮你默认用0填充. - varchar(M)
- 括号里面的值就是可变长度,在这个范围内,可以随意写入范围内的字符,在mysql5.0后英文和中午都是同样的字符,但是低版本中汉字是占3个字符
- char(M)
char中的M是规定的固定长度
创建数据库表格式
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
....
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集类型][注释]
数据库引擎
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持(表锁) | 支持(行锁) |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大(约为MYISAM的2倍) |
常规使用操作
- MYISAM 节约空间,速度较快
- INNODB 安全性高,支持事务的处理,(因为是行级锁)多表用户操作
扩展 : 两大索引在物理空间的位置
所有数据文件都存放在数据库中的data目录下,一个文件夹对应一个数据库,
数据库的本质还是文件的存储
- InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的 ibdata1 文件
- MYSIAM 对应的文件:
- *.frm 表结构文件
- *.MYD 数据文件
- *.MYI 索引文件
设置数据库表的字符集编码
CHARSET=utf8
不设置的话会采用mysql的默认字符集编码(不支持中文)
方式一:
在创建表的时候,加上 CHARSET=utf8
方式二:
直接在mysql的my.ini文件中加上 character-set-server=utf8
建议使用方式一,因为通用性较高
修改表
-- 修改表名
alter table 旧表名 rename 新表名
-- 增加表的字段
alter table 表名 add 字段名 列属性
-- 修改表的字段(修改约束)
alter table 表名 modify 字段名 列属性
-- 修改表的字段(重命名)
alter table 表名 change 旧字段名 新字段名 列属性
-- 删除表的字段
alter table 表名 drop 字段名
删除表
-- 如果该表存在就删除
drop table if exists 表名
MySQL数据管理
物理外键
方式一:在创建表的时候添加外键约束
方拾二:在创建表的时候没有添加外键约束,后期可以在修改表的时候进行添加约束
-- 由于用的不多,只记录方式二的方法
alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 要关联的哪个表(哪个字段)
不建议使用物理外键,:
1.数据库需要维护外键的内部管理；
2.外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
3.有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
4.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；
操作符
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| > | 大于 |
| > | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN 数值1 and 数值2 | 在数值1和数值2之间,包含数值1和数值2 |
| AND | 和 |
| OR | 或 |
insert
插入字段
-- 插入字段(添加)
insert into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3);
-- 简写insert (如果不写表字段的话,他就会一一对应)
insert into 表名 values(值1,值2,值3)
-- 对字段插入多个多个值
insert into 表名(字段名) values(属性值1),(属性值2),(属性值3) --这样的话就会对和这个字段插入了三条属性值
注意事项:
- 字段和字段之间使用 英文逗号 隔开
- 字段是可以省略的,但是后面的数据必须是一一对应的,不能少
- 可以同时插入多条数据,values后面的值,需要使用 , 隔开
update
修改
-- 不带where条件的修改
update 表名 set 列名 = 属性值 -- 这样的话该表中的该列中的值都会发生改变
-- 带where条件的修改
update 表名 set 列名 = 属性值 where 列名 = 条件值
-- 多个属性修改 逗号隔开
update 表名 set 列名1 = 属性值1 , 列名2 = 属性值2 where 列名 = 条件值
注意:
- 写列名时候尽量加上``符号
- 如果没有加筛选的条件的话,就是修改该列下的全部的值
- value 是一个具体的值也可以是一个变量
- 多个设置的属性之间,使用英文逗号隔开
delete
删除
-- 删除语句不带条件就是把整个表中的数据全部删除
delete from 表名
-- 带条件的删除语句
delete from 表名 where 列名 = 条件值
TRUNCATE 清空表数据
-- 清空表数据
truncate 表名
delete truncate 和drop区别
delete和truncate都可以删除数据,但是都不会删除表结构,truncate不会影响事务,但是会重新设置自增列计数器归零
drop则删除整个表（结构和数据）
拓展: delete 删除的问题
- InnoDB 自增列会从1开始(存在内存中,断电即失)
- MySAIM 继续从上一个自增亮开始(存在文件中,不会丢失)
select
查询
-- 查询所有字段
select * from 表名
-- 查询某些字段
select 字段1,字段2 from 表名
-- 使用别名 可以给字段和表起别名
select 字段1 as 别名1,字段2 as 别名2 from 表名 as 别名
distinct
去重
-- 去重查询 去除查询出结果中重复的数据,重复的数据只显示一条
select distinct 列名 from 表名
where 条件字句
检索数据中符合条件的值
联表查询
join
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配的,就会返回行 |
| left join | 会从左表中返回所有的值,即使右表中没有匹配的数据 |
| right join | 会从右表中返回所有的值,即使左表中没有匹配的数据 |
自连接
一张表的自连接核心:一张表拆分成两张一样的表即可
分页和排序
排序 order by 条件字段
-- desc 降序
select * from 表名 order by 条件字段 desc
-- asc 升序
select * from 表名 order by 条件字段 asc
分页
limit 起始值 每页的大小
n : 当前页
pagesize : 页面大小
起始页 : (n-1)*pagesize
总页数 :
- 数据总数%页面大小==0?数据总数/页面大小:数据总数/页面大小+1
- totalPage= (size+ maxsize-1) / maxsize; (推荐)
- totalPage= (int) Math.ceil(size/ maxsize);
MySql函数
常用函数
数学运算
select abs(-2) -- 绝对值
select ceiling(9.1) -- 向上取整
select floor(9.2) -- 向下取整
select rand() -- 返回一个0~1之间的随机数
select sign(19) -- 判断一个数的符号 负数返回-1 正数返回1 0的话返回0
字符串
select char_length('dadsaaaa') -- 返回字符串长度
select concat('ada','dada') -- 将字符串拼接起来返回
select insert('ada',1,2,'qeq') -- 查询 替换 从某个位置替换字符 qeqa
select lower('qeq') -- 转为小写字母
select upper('wew') -- 转为大写字母
select instr('dads','d') -- 返回d第一次出现的字符串的索引
select substr('qeq')
count
count(字段) -- 会忽略所有的null值
count(*) -- 不会忽略所有的null值
count(1) -- 不会忽略所有的null值
-- 都可以统计表中的数据
-- count(*)和count(1)本质都是计算行数
分组
group by
-- 语法
select 查询列表 from 表 [where 筛选条件] group by 分组的字段 [order by 排序的字段];
数据库级别的MD5加密
MD5 : 主要增强算法复杂度和不可逆性
MD5()是一个函数,可以在增删改查时候进行使用
例如:
insert into usertable(password) values(md5('123'));
-- 这样插入的就是一个MD5加密后的密文
select 完整语法
select [all|distinct]
{*|table.*|[table.field1[as alias1][,table.field2[as alias2]][,...]]}
from table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[where ...] 指定结果查询
[group by] -- 指定结果按照哪几个字段进行分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by ...] -- 指定查询结果按照一个或多个条件排序
[limit {[offset,]row_count | row_countOFFSET offset}];
--指定查询的记录从哪条至哪条
事务
要么都成功要么都失败
事务原则: ACID
原子性 一致性 隔离性 持久性
原子性
针对同一个事务
原子性表示这两个步骤要么都成功,要么都失败
一致性
针对一个事务操作前后的状态一致
一致性表示事务完成后,符合逻辑运算
事务前后的数据完整性要保持一致
隔离性
针对多个事务操作,主要排除其他事务对本次事务影响,不能被其他事务操作数据所干扰
隔离失败导致的问题
脏读
指的是一个事务读到另外一个事务未提交的数据
不可重复读
在一个事务读取表中的一行数据,多次读取结果不同
幻读
值得是一个事务内读取到了别的事务插入的数据,导致前后读取的结果不一致
持久性
表示事务结束后的数据不会随着外界原因导致数据丢失
事务没有提交,数据恢复到原装
事务提交后,数据持久化到数据库
事务一旦提交就不可逆了
-- mysql默认开启事务自动提交
set autocommit = 0 -- 关闭事务自动提交
set autocommit = 1 -- 开启事务自动提交
-- 标记事务的开始,从这个之后的sql都在一个事务中
start transaction
-- 提交事务
commit
-- 回滚事务
rollback
事务路程
索引
索引是帮助mysql高效获取数据的数据结构
索引的分类
单列索引
指的是一个索引中只包含一个列,一个表中可以有多个单列索引
- 主键索引 (primary key)
唯一标识,主键不可以重复,不能为空,只有一个列可以作为主键索引 - 唯一索引 (unique key)
避免重复出现的值,唯一索引可以创建多个 - 常规索引 (key/index)
默认的,可以用index或者key关键字设置 - 全文索引 (fulltext)
在特定的数据库引擎下使用
组合索引
值得是一个索引中包含两个或两个以上的列
sql格式是:
create 索引类型 索引名 on 表名(
字段名(length),字段名(length),…);
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
注意点:
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则
Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .
打破最左原则的话会导致组合索引失效
索引的使用
- 在创建表的时候给字段增加索引
- 创建完毕后,增加索引
-- 显示所有的索引
show index from 表名
-- 增加索引
alter table 表名 add 索引类型 `索引名`(字段名)
-- 分析sql执行的状况
explain sql语句
索引原则
- 索引不是越多越好
- 要不要对经常变动的表加索引
- 小数据量的表不需要加索引
- 索引一般加在经常用来查询的字段上
数据库规范设计
- 分析业务需求
- 概要设计:设计关系图 E-R图
三范式
第一范式 : 要求数据库表的每一列都是不可分割的原子数据项
第二范式 : 满足第一范式前提下,每张表只描述一件事
第三范式 : 满足第一和第二范式的前提下,确保数据表中的每一列数据都与主键直接相关,而不是间接相关
反三范式
使用反三大范式的目的是为了，实现效率的提高，在字段类型中使用json的方式存贮多条数据，实现对数据的处理。
三范式的目的是为了以时间换空间,反三范式是以空间换时间
数据库连接池
池化技术 : 准备一些预先的资源,过来就连接预先准备好的资源,连接完毕后重新放回去
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
