好久没用Mysql 有些东西都忘了 在这记录一下 方便以后查看
查看所有数据库
show databases
选中数据库
use [数据库名字]
删除数据库
drop [数据库名字]
创建数据库
create database [数据库名字]
表:
查看所有的表
show tables
查询
select * from [table] [条件]
删除表
drop table [table]
truncate table [table]
delete from [table]
区别：
drop 删除表结构和数据、被依赖的约束、索引、触发器、与该表有关的存储过程、函数等会保留 但状态会变成invalid 释放空间 彻底删除
truncate 清空表的数据 表的结构还在 释放空间
delete 清空表的数据 结构还在 不释放空间
delete 是数据操作语言 dml 可以激活触发器 数据是一行一行删除 会在事务日志中记录 所以可以回滚
truncate 是数据定义语言 ddl 不可以激活触发器 数据是以页为单位删除 事务日志只会记录页的删除
创建表
create table [table] ([列名][列的数据类型][列的约束],[列名2]...) [引擎编码等 可以不写]
举例：创建一个表名叫test的表 有3个列 分别是存储整数 文本和文本
create table test (id int,name varchar(30),message varchar(100))
列的数据类型
整数型
tinyint 占用一个字节 无符号最大存储 0到255 有符号 -128到127
small 占用两个字节 无符号 0-65535 有符号 -32768-32767
mediumint 占用3个字节 无符号0-16777215 有符号 -8388608-8388607
int 占用4个字节 无符号 0-4294967295 有符号 -2147483648-2147483647
bigint 占用8个字节 无符号 0-18446744073709551615 有符号 -9223372036854775808--9223372036854775807
浮点型
单精度浮点型
float 占用4字节 无符号 0，(1.175 494 351 E-38，3.402 823 466 E+38) 有符号 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
双精度浮点型
double 占用8字节 无符号 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 有符号 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
小数
decimal(M,D) 定点型 占用M+2个字节 M是总位数 D是小数位数 范围与双精度浮点型相同
声明方式
float(M,D)
double(M,D)
decimal(M,D)
M是总位数 D是小数点后的位数
举例：float(10,2) 一共10位数 小数点后两位 例如12345678.30
一般float和double不指定位数 直接写float、double 浮点型在数据库中存储是近似值 不是准确值 定点型是准确值 好像是文本格式记录的 如果要保存需要非常精确的数值比如余额、科学计算等一般使用定点型
mysql存储浮点型会四舍五入 比如限制了存储8位数 小数部分5位数 如果存储123.00 00 09 会被存储为123. 00 00 1
字符型
char(M) 定长字符串 比如 char(10) 直接申请10字符的空间 新增的数据不足10字符也占10字符的空间
varchar(M) 变长字符串 新增的数据不足M个字符 则只占新增的字符长度
以下每个单词首字母需要大写
tinyblob 最大存储255字节
blob 最大存储65535字节
mediumblob 最大存储16M数据
longblob 最大存储4G数据
binary(M) 定长 M<=255 插入数据 不足255 补0
VarBinary(M) 定长 M<=65536 插入数据 不足65536 不补0
tinytext 0-255字节
text 0-65535字节
mediumtext 0-16772150字节
longtext 0-4294967295字节
binary和Varbinary的M指定的是字节的长度 char和varchar是字符
日期时间
Date 日期 格式 YYYY-MM-DD 范围 1000-01-01到9999-12-31 占3bytes
Time 时间 格式HH:MM:SS 范围 -838:59:59到838:59:59 占3bytes
DateTime 日期时间 格式 YYYY-MM-DD HH:MM:SS 范围 1000-01-01 00:00:00-9999-12-31 23:59:59 占8bytes
Year 年 格式 YYYY 范围 1000-9999 占1bytes
TimeStamp 格式和DateTime相同 范围 1970-01-01 00:00:00 UTC-2038-01-19 03:14:07UTC 北京时间 2038-01-14 11:14:07 占4bytes
增删改查
增
insert into [表名] ([列名],[列名]...) values ([值],[值]...)
例
insert into test (id,name) values (1,'test1')
删
delete from [表名] [条件]
例
delete from test where id = 1
改
update [表名] set [列名] = [值] [条件]
例
update test set name = 'test2' where id = 1
查
select [列名] from [表名] [条件] 条件可以不写 asc/desc 升序/降序
例
select id from test
where 查询
运算符
=
<>,!= 不等于
<
>
>=
<=
between ... and 区间
and
or
not
in
例
select * from test where id = 0
select * from test where id != 0
其他同理
select * from test where id between 10 and 1000 取出id在10和1000之间的数据 左小右大
取出id等于1同时name等于name的数据
select * from test where id = 1 and name = ‘name’
取出id等于1 和 name 等于name的数据
select * from test where id = 1 or name = ‘name’
取出id不等于1的数据
select * from test where id not 1 结果:取出id除了1的所有数据
取出指定集合里的数据
select * from test where id in (0,1) 结果 取出id 0 和 1的数据
where后加if判断
select * from test where if(id>10,id = 1,id = 7) 结果 取出id = 1的数据
解读
把if后面看成编程里的if语句 意思是where id字段里有大于10的 那么就取出id = 1 的内容 否则取出id = 7的数据
可以多运算符同时使用
group by
分组 根据聚合函数运算后的结果 进行分组
select * from test group by id
根据id分组 id相同的分成同一组 只显示一行
聚合函数
sum
avg
max
min
...
group by 一般与运算函数一起使用
select count(*) from test group by id
统计根据id分组后的行数
having 子句
增加having子句是因为where不能与聚合函数一起使用 having可以对where和分组后的数据再筛选一遍
select * from test where id >10 having < 20
结果
取出id>10小于20的集
order by
排序
把结果排序
select * from test order by id asc
把结果根据 id 升序排列
desc 降序
limit
指定取出多少行
select * from test limit 10
取出10行
select * from test limit 5,3
跳过5行后取出3行
where子查询
select [列] from [表名] where id = ([新的查询语句])
select * from test where id = (select max(id) from test)
from子查询
把一个查询结果当成一个表查询
select * from (select* from test) as ceshi where id = 1
查询出id = 1 的结果
exists子查询
EXISTS 运算符用于测试子查询中是否存在任何记录 如果子查询返回一条或多条记录，EXISTS 运算符返回 TRUE 返回true后执行外层查询
select * from test where exists (select * from test id = 1)
返回了test表所有数据
select * from test where exists (select * from where id = 0)
返回0条数据
union
联合查询
把两个查询语句的结果联合成一个结果
select name from test union select name from ceshi
查询两表的列必须是相同的数据类型 名字可以不一样
联合后的结果会把重复的数据删除 保留1个
可以用union all 来显示所有数据
连接查询
连接两个表查询 通俗来说 把两个表连接成一张大表来查询
left join
左连接
结果根据左表的列为准 如果左表的数据多于第二张表 则左表的数据都显示 右表没有的用NULL填充 反之 右表多的数据不显示
例
select * from test left join test7 on test.id = test7.id
right join
右连接
与左连接相反
例
select * from test right join test2 on test.id = test2.id
inner join
内连接
留下两个表相同的数据 其他的都不显示
select * from test inner join test2 on test.id = test2.id
只会留下两张表共有id的数据
连接之后还可以添加其他筛选语句
列的增删改
增
aler table [表名] add column [列名] [数据类型] [not null ...] 在最后增加一列
alter table [表名] add column [列名] [数据类型] [not null ...] after name 在name后增加一列
aler table [表名] add column [列名] [数据类型] [not null ...] first 增加到第一列
删
alter table [表名] drop column [列名]
改
alter table [表名] modify [列名] [数据类型] [列的约束] 修改列的类型
alter table [表名] change [列名] [新列名] [数据类型] [约束] 修改列名和数据类型
视图
把查询出来的结果保存成视图
视图是虚拟的表
只有表结构 没有数据 数据还在原表
要查询时数据取自原表
原表更新 视图也会更新
创建视图
create view [视图名字] as 查询语句
举例
create view test7 as select * from test
查询
跟表查询一致
删除
drop view [名字],[名字]..
视图不可以用 from子查询、系统变量和用户变量、预处理、触发器
触发器
4要点
触发地点(table)
触发操作(增删改)
触发时机(after/before)
触发事件(增删改)
create trigger [触发器名字]
after/before [触发时间]
insert on test [触发地点和操作]
for each row [行触发器] 固定的 颗粒度 行 影响一行触发一次 不写默认执行一个语句触发一次 mysql之前了解只有行触发器 现在不知道有没有其他级别触发器
begin
[执行语句] 可以用if等语句
end
例
先更改语句结束符
delimiter @
create trigger test
after insert on test
for each row
begin
insert into test3 (id,name) values (1,'n'); [在test表插入后执行此语句] 可以用变量等
end @
之后可以改回原来的结束符
delimiter ;
5.7之前不能同时存在两种一样的触发器
例
[创建等语句] after insert on test [执行一些语句]
[创建等语句] after insert on test [执行另一些语句]
不能同时监视相同操作
想要实现可以在执行语句里加if判断来执行不同操作
5.7之后可以 不过需要注意：
监视同一个操作 触发增加一行数据的操作 如果字段有唯一索引 或者自增 两个触发器增加相同数据会不成功类似问题
查看所有触发器
show trigger
删除触发器
drop trigger name
需要用到影响之前行的数据使用old.列名
新行用new.列名
例
订单表num列数值改变 就把库存表的number字段减去相对数值
create trigger test
after update on price
for each row
begin
update inventory set number = old.number (修改之前的数值) + new.num (修改之后订单表num列的数值) - old.num (修改之前订单表num列的数值) where id = old.number ;
end@
before 在操作前触发 可以用来检测违法操作等
存储引擎
| 特点/引擎 | MyISAM | InnoDB | memory | merge 点击查看简介 | NDB |
| 存储限制 | 256TB(好像可以修改) | 64TB |
RAM大小 max_heap_table_size = xxM tmp_table_size = xxM 可以设置 最大存储大小 默认16M |
没有 | 有 |
| 事务 | 支持 | 仅支持READ COMMMITTED (不支持MVCC) | |||
| 锁 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 |
支持 (innoDB 1.2.x开始支持 很多文章还没更新) |
|||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据压缩 | 支持 |
支持 (1.2.x后支持MyISAM全部特性) |
支持 | ||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中等 | 低 | 高 |
| 批量插入速度 | 高 | 低 | 高 | 高 | 高 |
| 外键 | 支持 | 支持 |
| 存储引擎 | 使用场景 |
| MyISAM | 默认MySQL插件式存储引擎(5.5版本之前)，如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不是很高，那么选择这个存储引擎是非常适合的，MyISAM是在Web，数据仓库和其他应用环填下最常使用的存储引擎之一 |
| innoDB | 5.5版本之后的默认存储引擎 用于事务处理应用程序，支持外键，如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据库操作除了插入和查询之外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择，InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交（commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择 |
| memory | 将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问，MEMORY的缺陷是对表的大小有限制，太大的表无法承存在内存中，其次是要确保表的数据可以恢复，数据库异常终止之后表中的数据是可以恢复的，MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果 |
| merge | Merge存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同（即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同）的数据表合并为一个表，方便了数据的查询 |
附录：MySQL引擎之间的区别和优劣之分 - 知乎 (zhihu.com) 简单介绍了十种存储引擎
面试 | MySQL 中 InnoDB 和 MyISAM 的联系与区别 - 知乎 (zhihu.com) 不是很全 不过也有比较详细的地方
深入理解InnoDB -- 架构篇 - 知乎 (zhihu.com) 详细
事务
事务4特点
原子性 事务中的语句要么都成功执行 要么都失败 体现在最终是commit还是rollback 不可再分割 有的成功有的失败是不可能的 commit就都执行 rollback就都失败
一致性 事务使库从一个状态转到另一个状态 比如A给B转账 A减少 B增加 从最终结果看是 合理的 一致的 如果A减少 B没增加 就不一致了
隔离性 事务允许并发 多个事务有相应的隔离机制
持久性 事务结束后 结果持久化在硬盘上
MySQL事务【详解-最新的总结】_mysql的事务_悬浮海的博客-CSDN博客
隐式事务
即insert delete update 等都会在语句结束自动提交
查看是否开启自动提交
show variables like ‘autocommit’
设置是否自动提交
set autocommit = 0/1
显式事务
即手动控制commit rollback
开启事务
start transaction 或 begin
start transaction read only开启只读事务 事务中只能执行读操作 可能对读有优化 执行其他操作会报错
commit 提交事务
rollback 回滚事务
savepoint save1 加保存点 跟游戏里保存点一样 不想回滚所有操作 可以提前加保存点 再执行 rollback to save1 可以回滚保存点到现在的操作
事务的隔离级别
在并发的时候需要
想象一个场景 两个客户端同时连接MySQL 同时开启了事务 事务A修改了一个数据 事务B读取了该数据 事务A因为一些原因回滚了 事务B读的数据跟数据库的实际数据不一样
上面这个问题的名字就叫 脏读
还有一些因为隔离级别不同差不多的情况
读已提交 顾名思义 可以读到其他事务提交了的数据
不可重复读 还是脏读的场景 有两个事务同时操作数据库 不同的是 这次事务B先提交了 把数据库的数据更新完了 A去读取数值就是新数值 跟之前查询的不一样了 就是不可重复读
可重复读 顾名思义 可以重复读 每次结果都一样
幻读 事务A按照条件进行查询 事务B插入了符合事务A条件的数据 事务A之前查询没有这条数据 插入了一条数据 此列有唯一索引 发现插入失败 再查一次 又发现有这条数据 就像幻觉一样
根据需求不同 有不同的隔离级别解决相应的问题 一共有4个级别
读未提交 read uncommitted 最宽松的 顾名思义 可以读没有提交的数据 会出现脏读的现象
读已提交 READ COMMITTED 顾名思义 这个级别可以读已提交的数据 读不到没提交的数据
可重复读 repeatable read 顾名思义 可以重复读 解决了不可重复读问题 但会出现幻读
串行化 SERIALIZABLE 事务都是互斥的 事务不能同时 读写 写读 写写 可以同时读
隔离级别越高 并发的能力就越低
查看
查看隔离级别
show variables like 'transaction_isolation'
设置隔离级别
全局
set global transaction isolation level = REPEATABLE READ
会话
set session transaction isolation level = [...]
数据库备份
mysqldump
原理：连接到数据库 把需要备份的数据查询出来 转成insert 语句 恢复直接执行insert 语句
常用参数
--host /-h 主机地址
--port / -p 端口
--user / -u 用户名
--password / -p 密码
--databases 要备份的数据库
--all-databases 备份所有数据库
--compack 压缩
--lock-tables 备份前锁定所有表
--no-create-db 禁止创建 创建数据库语句
--force 当备份出错时继续
--default-character-set 指定字符集
--add-locks 备份前锁定要备份的表
例子
mysqldump -u root -p [] --all-databases > ../xxx/all.db
还原
use [数据库]
source 路径
未完
标签：事务,数据,基础,MySQL,test,where,id,select From： https://www.cnblogs.com/mltooo/p/17534105.html