2021-11-9mysql数据库

DESC 表名 显示表的结构
字符串使用单引号
所有语句后加,最后一句不用加
IF NOT EXISTS 不存在就创建
自增 AUTO_INCREMENT
注释 COMMENT
主键PRIMARY KEY (id`)
引擎ENGINE=INNODB DEFAULT CHARSET=UTF8

数值

类型 字节数
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8
float 4
double 8
decimal 金融问题

字符串

char 字符串固定大小。 0～255
varchar 可变字符串 0～65535
tinytext. 微型文本。 2^8-1
text. 文本串。 保存大文本

时间日期

date. YYYY-MM-DD

time. HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss. 最常用的时间格式
timestamp. 时间戳 1970.1.1到现在的毫秒数
year 年份

null

没有值，未知
不要用null进行运算

字段属性

1.unsigned 无符号的整数
2.zerofill. 不足位0填充
3.自增 唯一的主键
4.null / not null
5.default 设置默认值

数据库表的类型 引擎

INNODB mysql 默认 节约空间，速度较快。 在数据库表中只有*.frm
MYISAM 早些年使用 安全性高，事务的处理，多表多用户操作. *.frm 表结构文件
.MYD 数据文件。.myn 索引文件
INNODB MYISAM
事务支持 支持 不支持
数据行锁定 支持 不支持
外间约束 支持 不支持
全文索引 不支持 支持
表空间的大小 较大 较小

修改表
alter table 旧表名 rname as 新表名
添加表字段
alter table teacher add age int(11)
alter table teacher add constraint id foreign key(id) reference ‘表名’ 列名
修改表字段
alter table teacher modify age varchar(11) 字段类型和属性
alter table teacher change age age1 int(11)重命名
删除表字段
alter table teacher drop age1

删除表. 如果表存在
drop table if exists teacher
所有的创建和删除操作尽量加上判断，以免报错

MYSQL数据管理

外键
数据库级别外键 不建议使用，避免数据库过多造成困扰

DML

insert
insert into 表名（字段，，，，） values (值，，，，) 多条数据（），（）…

update
update 表名 set 原来的值 = 新值 where 条件
delete
删除指定数据
delete from 表名 where 条件
清空数据库表的数据，表的结构和索引约束不会变
truncate 表名。
truncate 会重新设置自增列 计数器会归零 不会影响事务

函数

ABS() 绝对值
ceiling 向上取整
floor 向下取整
rand 返回一个0～1之间的随机数
sign. 判断一个数的符号 0-0 负数返回 -1 整数返回 1

CHAE_LENGTH()返回一个字符串的长度
Concat(a,b)拼接字符串
insert() 向一个字符串插入一个字符串
upper()转大写
lower()转小写
instr 返回字符串的索引
replace() 替换指定的字符串
substring/substr() 截图从指定位置截取指定长度的字符串
reverse() 反转字符串

时间日期
CURRENT_DATE()/curdate() 返回当前日期
now 获取当前时间
localtime 获取本地时间
sysdate 获取系统时间
system_user /user 当前用户

聚合函数
count (指定列/*/1) 指定列会忽略null值 */1 不会忽略null值 本质 计算行数
sum
avg
max
min

DQL

指定查询字段
select * from 表名
select 字段 as 别名 from 表名 别名where 条件
去重
select distinct 列名 from 表名 where 条件
select version 查询系统版本
select 100*3-1 as 计算结果 用来计算
select @@auto_increment 查询自增步长

where 等值查询
and &&
or ||
not !
模糊查询
is null
is not null
between and
like。% 匹配人意字符 _一个字符
in 在其中某一个

联表查询

join on(条件)

join
left join
inner join
right join
确定使用哪种连接查询
确定交叉点 两个表中哪个数据是相同的

多表查询思路

查询哪些数据
从哪几个表中查 先查询两个表

图1:select. 列名 from 表1 left join表2 on 表1.列名 = 表2.列名 会返回左表所有值，即使右表不匹配
图3:select. 列名 from 表1 inner join表2 on 表1.列名 = 表2.列名 表中至少有一个匹配，就返回行
图4:select. 列名 from 表1 right join表2 on 表1.列名 = 表2.列名 会返回右表所有值，即使左表不匹配
图2:select. 列名 from 表1 left join表2 on 表1.列名 = 表2.列名 where 表2.列名 is null
图5:select. 列名 from 表1 right join表2 on 表1.列名 = 表2.列名 where 表2.列名 is null

自连接

一张表拆成两张表
select 列名 form 表名1 as a,表名1 as b where

分页limit、排序order by

分页 缓解数据库压力 提高用户体验
limit 起始值，页面大小
查询一到五
select 列名 from 表名 where order by 列名 asc limit 0,5
网页应用： 当前页 n 总页数 count/pageSize ，页面大小pagesize
limit （当前页-1）*5。
（n-1)*pageSize
排序
升序 ASC 降序DESC
select 列名 from 表名 where order by 列名 asc

子查询

where (内嵌一个子查询)

MD5

加密
update testmd5 set pwd=MD5(pwd) where id = 1
插入时加密
insert into testmd5 values(4,‘xiaoming’,‘MD5(‘123456’)’)
如何校验 将用户传进来密码，进行md5加密 然后对比
select * from testmd5 where name = ‘xiaoming’ and pwd = md5(‘123456’)

事务

要么都成功要么都失败
ACID。
原子性
要么都成功要么都失败
一致性
最终一致性 过程一致性
持久性
事务结束后的数据不随着外界原因导致数据丢失
事务没有提交 恢复到原状 事务已经提交持久化数据库 不可逆
隔离性
问题：
脏读 ：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变。
虚读 换读 在一个事务内，读取到了别人插入的数据，导致前后读出来的数据不一致
多个用户并发操作 不会有影响
mysql 默认时开启事务自动提交的‘
set autocommit = 0 关闭。set autocommit = 1 开启
手动 处理事务
start transaction

commit 提交‘ rollback 回滚

savepoint 保存点
rollback to savepoint 回滚到保存点
release savepoint 撤销保存点

关闭自动提交
set autocommit = 0
开启一个事务
start transaction
update account set money = money-500 where id = ‘a’;
update account set money = money=+500 where id = ‘b’;
提交/回滚
commit;
rollback;
开启自动提交
set autocommit = 1
java中
try{ 连接数据库
connection = 数据源.getConnection()
关闭数据库的自动提交，自动会开启事务
connection.setAutoCommit(false);
sql1
执行sql1
sql2
执行sql2
connection.commit();
执行成功提交

}catch(Exception e){
try{
conn.rollback();
执行失败 回滚
}catch(Exception e){
e.printstacktrace();
}
e.printstacktrace();
}finally{
数据源.release(connection,st,s);
}

索引

索引名（列名）
主键索引primary key. 不可重复 只能有一个列作为主键
唯一索引 uniqe key 避免重复的列出现 唯一索引可以重复，多个列都可以标识
常规索引（key index）
全文索引 fulltext
show index from 表名 显示索引的信息
explain 分析sql执行的情况
explain select * from 表名
索引在大数据时区别十分明显
索引一般加在常用来查询的字段上

索引的数据结构 mysql索引背后
hash
btree

权限管理和备份

用户管理
用户表 mysql.user
本质：对这张表增删改查
添加用户
create user zxw identified by ‘密码’
修改密码
当前用户
set password =PASSWORD(‘123456’)
指定用户
set password FOR ZXW = PASSWORD(‘123456’)
重命名
rename user zxw to zxw2
用户授权
grant all privileges on . to zxw2. 全部权限 除了给别人授权
查看权限
show grant for zxw2
show grant for root@localhost
撤销权限
revoke all privileges on . from zxw2
删除用户
drop user zxw2

备份方式
直接拷贝物理文件
可视化工具手动导出
mysqldump 命令行使用
mysqldump -h主机-u用户 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D：/a.sql
导入
登陆情况下 切换到指定的数据库
source 物理磁盘位置/文件名
数据库三大范式
第一
原子性 保证每一列不可再分
第二
前提 满足第一范式
每张表只描述一件事
第三
前提 满足第一第二范式
确保数据表中的每一列数据都和主键直接相关 而不能间接相关
关联查询的表不能超过三张表
规范性和性能的问题
性能更加重要