导图社区 mysql
这是一篇关于mysql的思维导图,知识点系统且全面,希望对大家有用。感兴趣的小伙伴可以收藏一下!
编辑于2024-05-09 19:45:30mysql
DDL 数据定义语言
库
create database if not exists db2
select database()
use 库名
show create database XD;
表
常见数据类型
整数
TINYINT 1 字节
SMALLINT 2 字节
MEDIUMINT 3 字节
INT 4 字节
BIGINT 8 字节((0,2的64次方减1))
浮点型
FLOAT(m,d) 4 字节 单精度浮点型
DOUBLE(m,d) 8 字节 双精度浮点型
定点型
DECIMAL(m,d)
字符串类型
CHAR 0-255字节 定长字符串
VARCHAR 0-65535字节 变长字符串
TINYTEXT 0-255字节 短文本字符串
TEXT 0-65535字节 长文本数据
MEDIUMTEXT 0-16777215字节 中等长度文本数据
LONGTEXT 0-4294967295字节 极大文本数据
备注:char的优缺点:存取速度比varchar更快,但是比varchar更占用空间
时间型
date 3 yyyy-MM-dd
time 3 HH:mm:ss
year 1 yyyy
datetime 8 yyyy-MM-dd HH:mm:ss
timestamp 4 yyyy-MM-dd HH:mm:ss 存储日期+时间,可作时间戳
创建表
CREATE TABLE 表名 ( 字段名 类型 约束条件);
create table 新表名 like 旧表名;
查看数据库中的所有表:show tables;
查看表结构:desc 表名;
查看创建表的sql语句:show create table 表名;
\G :有结束sql语句的作用,还有把显示的数据纵向旋转90度
\g :有结束sql语句的作用
约束条件
comment ----说明解释
not null ----不为空
default ----默认值
unsigned ----无符号(即正数)
auto_increment ----自增
zerofill ----自动填充
unique key ----唯一值
表结构维护
修改表名
rename table 旧表名 to 新表名
添加列
alter table add 列名 类型 comment '说明'
给表最前面添加一列:alter table 表名 add 列名 类型 first;
给表某个字段后添加一列:alter table 表名 add 列名 类型 after 字段名;
修改列类型
alter table 表名 change 旧列名 新列名 类型;
删除列
alter table 表名 drop 列名;
修改字符集
alter table 表名 character set 字符集;
删除
drop table (if exists) 表名
DML 数据操纵语言
新增
insert into 表名(字段名) values(字段对应值)
蠕虫复制(将一张表的数据复制到另一张表中)
insert into 表名1(字段名1,字段名2) select 字段名1,字段名2 from 表名2;
insert into 表名1(字段名1,字段名2) select 字段名1,字段名2 from 表名2;
建表复制
create table 表名1 as select 字段名1,字段名2 from 表名2
插入多个数据
insert into 表名 (字段名) values (对应值1),(对应值2),(对应值3)
修改
update 表名 set 字段名1=值1 where 字段名=值
删除
delete from 表名 where 字段名=值
备注:delele 会把删除的操作记录给记录起来,以便数据回退,不会释放空间,而且不会删除定义。 truncate不会记录删除操作,会把表占用的空间恢复到最初,不会删除定义 drop会删除整张表,释放表占用的空间。速度: drop > truncate > delete
DQL 数据查询语言
where
简单查询
select * from employee
精确查询
select * from employee where sal != 50000
模糊查询
select * from employee where ename like '林%'
范围查询
select * from employee where sal between 10000 and 30000
离散查询
select * from employee where ename in ('猴子','林俊杰','小红','小胡'
清除重复值
select distinct(job) from employee
统计查询(聚合函数)
count(code)或者count(*)
sum(sal) 计算总和
max() 计算最大值
avg() 计算平均值
min() 计算最低值
concat函数: 起到连接作用
分组查询
group by 列1,列2....列N
having(筛选)
group by job having job ='文员'
排序查询
select * from employee order by sal
where ---- group by ----- having ------ order by
limit限制查询
limit 4,5 4开始 ,取出5条
exists
exists跟 not exists
返回boolean
外连接查询
左连接
left join 表名 on 条件 / left outer 表名 join on 条件
左表记录全部, 右表 符合条件记录
右连接
right join 表名 on 条件/ right outer 表名 join on 条件
右表记录全部, 左表 符合条件记录
内连接
INNER JOIN 表名 ON 条件
联合查询
... UNION ... (去除重复)
... UNION ALL ...(不去重复)
例:(select * from employee a where a.job = '销售员' order by a.sal limit 999999 ) union (select * from employee b where b.job = '文员' order by b.sal desc limit 999999)
DCL 数据控制语言
权限
select user,host from mysql.user
刷新权限:flush privileges
修改密码
set password for root@localhost = password('mima')
mysqladmin -u用户 -p旧密码 password 新密码
update mysql.user set authentication_string=password('密码') where user='用户' and host='ip'
忘记密码
① my.cnf (默认在/etc/my.cnf),在[mysqld]下面加上 skip-grant-tables
②重启mysql服务
③mysql -uroot -p 无需密码登录进入
④修改密码
创建用户
create user 'username'@'host' identified by 'password'
权限设置
授权
已有用户pig用户所有库表权限:grant all privileges on *.* to 'pig'
新用户:grant select,update on XD.employee to 'cat'@'%' identified by '123456' 创建新用户,授予xd.exployee表的select,update权限
all privileges 所有权限
. 所有库表
回收权限
revoke all privileges on *.* from 'pig' @ '%'
备份
分类
完全备份
部分备份
增量备份:
是以上一次备份为基础来备份变更数据
差异备份:
是以第一次完全备份的基础来备份
备份的方式
逻辑备份
直接生成sql语句,执行备份的sql恢复数据的时候
物理备份
直接拷贝相关的物理数据
备份的场景
热备份
备份时,读写都可
温备份
读可,写不能
冷备份
不能进行任何操作
mysqldump备份(跨机器)
备份
mysqldump -u 用户 -h host -p 密码 dbname table > 路径
单库
mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix | gzip > /mysql_data_back/zabbix_users.sql.gz
多库
mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix XD | gzip > /mysql_data_back/zabbix_XD.sql.gz
全库
mysqldump -uroot -pabc123456 -h120.25.93.69 --all-databases | gzip > /mysql_data_back/all.sql.gz
单库例子并保留创建库语句
mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix | gzip > /mysql_data_back/zabbix_bak.sql.gz
单表
mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix users | gzip > /mysql_data_back/zabbix_users.sql.gz
恢复
mysql -uroot -pabc123456 -h120.25.93.69
没有创建库的语句
mysql -uroot -pabc123456 -h120.25.93.69 zabbix
有创建库的语句
mysql -uroot -pabc123456 -h120.25.93.69 zabbix
mysqldump -uroot -p XD user -F > user_bak.sql
加上-F选项可以重新生成一个新的二进制日志文件
二进制日志mysqlbinlog备份
二进制日志
二进制日志
mysql数据库中的一些写入性操作,增删改
是否开启二进制
show variables like 'log_bin%'
开启二进制日志 : vi /etc/my.cnf
[mysqld] log-bin=/data/mydata/log_bin/mysql-bin server-id=1
查看所有的binlog日志列表
show master logs
刷新二进制日志
flush logs
重置(清空)二进制日志文件
reset master
备份
第一步:日志文件导出成普通文件
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql
第二步:找出要恢复的位置
mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | grep -iw 'drop'
mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="59674" /data/mydata/log_bin/mysql-bin.000002 | mysql -uroot -p
优化
避免使用select *from
避免条件使用or
加上limit 限制行数
引擎
show engines
create table yingqin (id int,name varchar(20)) engine='InnoDB'
alter table 表名 engine='MyiSAm'
MyISAM与InnoDB
MyISAM
支持全文索引(full text);不支持事务;表级锁;保存表的具体行数;奔溃恢复不好
InnoDB
支持事务;5.6之前的版本是不支持全文索引;行级锁;不保存表 的具体行数;奔溃恢复好
索引
index:普通索引
允许空(null)
添加索引
alter table 表名 add index 索引名称 (字段名称)
create index 索引 on 表名 (字段名)
查看索引
show index from 表名\G
删除索引
drop index 索引名称 on 表名
alter table 表名 drop index 索引名
unique:唯一索引
允许空(null);唯一值
add unique 索引名称 (字段名称)
primary key:主键索引主题
drop primary key
foreign key:外键索引
add foreign key (字段名) references 关联的表名
(1)俩个表,主键跟外键的字段类型一定要相同 (2)要使用外键约束表的引擎一定得是InnoDB引擎,MyISAM是不起作用的 (3)在干掉外键索引之前必须先把外键约束删除,才能删除索引
fulltext: 全文索引
alter table command add fulltext(字段名)
select * from 表名 where match (字段名) against ('检索内容')
alter table command drop index instruction
• 1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以 • 2、全文索引不针对非常频繁的词做索引。比如is,no,not,you,me,yes这些,我们称之为停止词 • 3、对英文检索时忽略大小写
联合索引
alter table test add index(username,servnumber,password)
alter table test drop index username
多列数据进行查询时,可以考虑建立联合索引
事务
特性
原子性(Atomicity)
事务必须是原子工作单元,一个事务中的所有语句,应该做到:要么全做,要么一个都不 做
一致性(Consistency)
让数据保持逻辑上的“合理性”
隔离性(Isolation)
如果多个事务同时并发执行,但每个事务就像各自独立执行一样
持久性(Durability)
一个事务执行成功,则对数据来说应该是一个明确的硬盘数据更改(而不仅仅是内存中的 变化)
备注:表的引擎要为innodb引擎
操作命令
开始:begin; start transaction;
提交:commit
回滚:rollback
命令
启动关闭
启动命令
service mysql start
关闭命令
service mysql stop
重新启动命令
service mysql restart
查看状态命令
service mysql status
登录
mysql -uroot -p
默认端口号
3306
配置文件
/etc/my.cnf
默认引擎、默认提交、跳过权限、二进制日志开启、慢查询开启...