导图社区 MYSQL
- 65
- 3
- 0
- 举报
MYSQL
本图整理了MySQL的增删改查、密码的修改和对应权限的更改，希望这份脑图会对你有所帮助。
编辑于2023-05-18 14:42:33 江西- 相似推荐
- 大纲
Mysql(所有语句都必须用分号结尾)
数据库 DATABASE
创建
CREATE
database+数据库名称
+character+set+字符集
设置字符集
+collate+校对规则
设置字符集的基础上设置效验规则
删除
DROP
删除数据库 datebase
修改
ALTER
database+数据库名称
character+set+字符集、校对集和存储引擎
修改字符集、校对规则
查看
SHOW
datebases
查看所有的数据库
CREATE DATEBASE (查看数据库创建语句) +数据库名称
查看数据库的创建语句 最后加 \G可以纵向显示
选择数据库
USE database
更改数据库名字
2.如果所有表都是MyISAM类型的话，可以改文件夹的名字 3.重命名所有的表(新建一个数据库然后把所有的表改为) CREATE DATABASE new_db_name; RENAME TABLE db_name.table1 TO new_db_name.table1, db_name.table2 TO new_db_name.table2; DROP DATABASE db_name;
查看当前选择数据库
select datebase()
数据表 TABLE
创建
create
table
+相对应的数据库名称 . 表名称
先进到相对应的数据库(use+相对应的数据库)+表名称+(数据字段+数据类型）
可以在设置字段后面添加注释 格式：comment '注释'；
删除
drop
table
修改
alter
TABLE+表名称
charset = 修改字符集、校对集和存储引擎
修改字符集、校对集和存储引擎
add+字段名+数据类型+
添加字段 位置： first（把要添加的字段放到第一位） after+A字段（把要添加的字段放在A字段的后面） 无符号字段： unsigned（新增一个无符号字段） 零填充： zerofill（把数值前面的零显示出来）
change 重命名字段+Old_Field+New_Field+数据类型
drop（删除字段）+Field_Name
格式：RENAME TABLE+旧表名+to+新表名
修改表名（不需要alter）
查找
show
tables
(所有的表)前提要先进入数据库
格式：CREATE TABLE+表名称
查看数据表的创建语句
格式：DESC+表
查看表内容（不需要添加show）
复制表结构
格式：create table 新表名 like 数据库.表名;
数据字段
插入
1、 insert into+数据表+values(数据内容),(数据内容),(......),(......);
给表插入数据(需要注意表中内容的格式，凡是非数值数据都需要引号包裹)
2、 insert into+数据表(数据表中的字段) +values(数据内容)
给表中字段插入局部数据（同样要注意它的格式）
查看
格式：select +想看的字段+from+表名
数据表中的全部数据可以用 * 号(不需要show) mysql的关系运算符：=,>,>=,<,<=,!= mysql的逻辑运算符：与(and)，或(or)，非(not)
高级操作
格式：select 字段名 as 字段别名
修改
格式：update+表名+set+字段 = 新的数据内容+where+条件
不需要alter
高级操作
格式：update 表名 set 字段 = 值[where 条件][limit+更新数量];
删除
格式：delete from+表名+where+条件
高级操作
如果表中存在自增长，删除后，自增不会还原 数据删除不会改变表的样式，只能删除后再新建truncate 表名;
格式：select [select 选项] 字段列表[字段别名]/* from 数据源 [where子句][group by子句][having子句][limit子句];
all：（默认值），保留所有的结果 distinct：去重，对查询出来的结果，将重复的数据去除。
格式：delete from 表名 [where条件][limit+数量];
复制表中字段
格式：insert into 表名[(字段列表)] select 字段列表/* from 数据表名；
约束
主键
主键：primary key，主要的键，一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复。一张表只能最多一个主键。
格式：primary key
基本操作
第一种方法：创建字段时在后面添加primary key
优点：直接，快速 缺点：只能使用一个字段作为主键（无法创建复合主键）
第二种方法：创建表时，再最后加primary key (字段名)
在创建表的时候，在所有的字段之后，使用primary key（主键字段列表）来创建主键（如果有多个字段作为主键，可以是复合主键）
第三种方: 修改表中字段时加primary key
格式：alter table 表名 add primary key(数据字段);
删除主键
格式：alter table 表名 drop primary key;
主键是无法更新的，只能先删除，再新增。
主键冲突
不该主键字段，改在其他字段
更新
格式：insert into 表名（包含主键的字段）values（值列表）on duplicate key update 字段 = 新值
范例：insert into my_pri1 values ( 'czn',18001) on duplicate key update name = 'mqy';
替换
格式：replace into 表名 values(主键字段，被改的字段);
范例： replace into my_pri1 values('anwy',18003);
唯一键
一张表往往有很多字段需要具有唯一性，数据不能重复，但是一张表中又只能有一个主键。 唯一键（unique key）就可以解决表中有多个字段需要唯一性约束的问题。 唯一键的本质与主键差不多：唯一键默认的允许自动为空。而且可以多个为空（空字段不参与唯一性比较） 如果唯一键也不允许为空：与主键的约束作用是一致的。
格式：unique key
方案一：在创建表的时候，字段之后直接跟 unique / unique key
方案二：在所有的字段之后增加unique key（字段列表）;
-复合唯一键
方案三：在创建表之后增加唯一键
格式：alter table 表名 add unique key(数据字段);
删除唯一键
格式：alter table 表名 drop index 索引名字；
-- 唯一键默认使用字段名作为索引名字
自增
自增长：当对应的字段，不给值，或者给默认值或者给null的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1的操作，得到一个新的值。 自增长通常是跟主键搭配使用。 自增长的格式：auto_increment 自增在创建字段的后面添加 1、任何一个字段要做自增长必须前提是一个索引（key一列有值） 2、自增长字段必须是数字（整型） 3、一张表最多只能有一个自增长
格式：auto_increment
给主键添加自增
格式：ALTER TABLE 表名 MODIFY 字段+数据类型 auto_increment;
修改自增长
格式：alter table 表名 auto_increment = 值
修改当前自增长已经存在的值：修改只能比当前已有的自增长的最大值更大，不能改小（小不生效）
修改自增的变量
格式：set auto_increment_increment = 5; -- 一次自增5
查看自增长对应的变量：show variables like ‘auto_increment%’；
删除自增
格式：alter table 表名 modify 字段 类型；
外键
外键：foreign key, 外面的键（键不在自己表中）。 如果一张表中有一个字段（非主键）指向另外一张表的主键，那么将该字段称之为外键。 外键的条件： 1、要保证表的存储引擎是innodb，如果不是，那么外键创建成功也没有约束效果 2、外键字段的类型（列类型）必须与父表的主键类型完全一致 3、一张表中的外键名字不能重复 4、增加外键的字段（数据已经存在），必须保证数据与父表主键要对应
格式：foreign key
增加外键
创建表时增加
在所有的表字段之后，使用foreign key（外键字段）references 外部表（主键字段）
修改表结构
格式：alter table 表名 add[constraint 外键名字] foreign key(外键字段) references 表名（主键字段）
修改外键和删除外键
外键不能直接修改，只能先删除后新增
修改&删除
格式：alter table 表名 drop foreign key 外键名；
外键的作用
父表
父表数据进行写操作时，对应的主键在子表中已经被数据所引用，那么不允许操作
子表
子表进行写操作时，如果对应的外键字段在父表中找不到对应的匹配数据，那么操作失败
外键约束
格式：在所有的表字段之后，使用foreign key（外键字段）references 父表（主键字段）on delete set null on update cascade;
三种约束模式（都是对父表的约束）
district：严格模式（默认），父表不能删除或者更新一个已经被子表数据引用的纪录
cascade：级联模式，父表操作，子表的数据也会跟着修改
set null：置空模式，父表数据删除，子表对应的数据也会被置空
通常采用的做法是：删除时子表置空，更新时子表级联
重组集合（group by）
对于分组查询（group by），select中出现的信息，通常只有两种情况。 1、分组本身的字段信息 2、一组综合统计信息 统计信息一般要结合统计函数（聚合函数）使用
聚合函数5
（1） 计数值：count(字段)，表示求出一组中原始数据的行数；
（2） 最大值：max(字段)，表示求出一组中该字段的最大值；
（3） 最小值：min(字段)，表示求出一组中该字段的最小值；
（4） 平均值：avg(字段)，表示求出一组中该字段的平均值；
（5） 总和值：sum(字段)，表示求出一组中该字段的累加和；
格式：select 字段，max（字段）from 数据表 group by 字段；
having
having的含义跟where的含义是一样，也是用来筛选数据的，但是having是只用于对group by分组的结果进行的条件筛选。
语法格式：having 筛选条件
升降序（order by）
排序，根据某个字段进行升序或者降序排序，依赖校对集
格式：order by 字段名 [asc/desc]; asc：升序（默认值），desc：降序
联合查询
概述：将多次查询(多条select语句)，在记录上进行拼接(字段数不会增加)
格式：select 语句1 union[all、distinct] select 语句2
union的两个选项 all :保存所有值（不管是否重复） distinct ：去重复（默认值）
联合查询跟字段的数据类型无关，但要求字段数一致。
意义：查询同一张表但是需求不同，2、多表查询，要求表数据结构一致
连接查询
交叉连接
实际开发中很少使用
格式: select * from 表一 cross join 表二
内连接（自然连接）
内连接使用比较运算符对两个表的数据进行比较，并列出与连接条件匹配的数据化，组成新的数据表，也就是说只有满足条件的记录才能出现在查询结果中
满足条件的记录才能出现在查询结果中
格式：select 查询字段 from 表一 join 表二 on 表二 .关系字段 = 表二.关系字段
外连接
返回的结果自包含符合查询条件和连接条件的数据
左连接
返回包括左表中的所有记录和右表中符合连接条件的纪录
格式：select 所查字段 from 表一 left join 表二 on 表一.关系字段 = 表二.关系字段 where 条件
右连接
返回包括右表中的所有记录和左表中符合连接条件的纪录
格式：select 所查字段 from 表一 right join 表二 on 表一.关系字段 = 表二.关系字段 where 条件
复合条件连接查询
连接查询中，通过添加过滤条件来限制查询结果，使查询结果更加精确
子查询
子查询是指一个查询语句嵌套在另一个查询语句中内部的查询
in关键字
还有一个not in就是和in的查询结果相反
内层查询语句中仅返回一个数据列，这个数据列中的值将外层查询语句进行比较操作
exists关键字
关键字后面的参数可以是任意一个子查询，这个子查询相当于测试不会产生数据，只返回true和false,当结果为true时，外层查询才会执行
any关键字
它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询的任意条件，就返回一个结果作为外层查询条件
all
返回的结果需同时满足所有内层查询条件
视图
视图是为了方便检索数据，简化流程用的
创建视图格式：CREATE VIEW 视图名 AS 要查询的语句；
删除视图格式：DROP VIEW 视图名；
数据类型
数值型
整数型
tinyint
1字节，范围：-128/0~127/255
smallint
2字节，范围：-32768/0~32768/65535
mediumint
3字节，-8388608/0~8388607/16777215
int/intege
4字节，-2147483648/0~2147483648/4294967295
bigint
8字节， -9223372036854775808/0~9223372036854775808/18440711073709551615
浮点型
浮点型插入：整数不能超出长度，小数可以超出 超出精度范围的四舍五入 float(M,D):M代表总长度，D代表小数部分的长度，整数部分的长度为M-D
float
float占四个字节
double
定点型
绝对的保证整数部分不会被四舍五入（不会丢失精度） 小数部分有可能（理论上小数部分也不会丢失精度）
decimal
日期时间型
year
年份, 两种形式，year（2）和year（4）：1901-2156
timestamp
时间戳，格式不是时间戳，但从1970年开始和datetime格式一致 会自动更新目前时间
time
time：时间，表示某一个时间 00:00:00
date
日期 格式：为date部分 0000-00-00
datetime
时间日期 格式:YYYY-MM-DD HH:ii:ss 表示的范围是1000到9999年，有0值：0000-00-00 00:00:00
字符串
set
集合字符串 跟枚举相识，但他是集合 可以使用元素列表中的一个或多个，逗号隔开 格式：set（元素列表） 集合中每个元素都对应一个二进制， 如果选中为1，没有选中为0，最后把二进制反过来 ps：设置一个集合字符串 create table my_set（ hobby set（'水球','网球','羽毛球','排球','乒乓球','篮球'); 如果我添加数值 insert into my_set values('水球，排球，网球'); 二进制位1010100反过来就是0010101 十进制为21
enum
枚举字符串 实现将所有可能出现的结果都设计好，实际上存储数据必须是事先规定好的数据的一个 格式：enum（需要设计的元素列表）； 使用：存储数据，只能存储上面定义好的数据 插入数据：数据只能是设计元素列表的个数 枚举实际存储的数据不是字符串而是数值 枚举元素的规律：按照元素出现的顺序，从1开始编号 枚举原理：枚举在进行数据规范的时候（定义的时候），系统会自动建立一个数字与枚举元素相对应的关系（关系放在日志中），然后在进行数据插入的时候，系统会自动将字符转换成对应数字存储，在数据提取的时候，系统自动将数值转换成对应的字符串显示
blob
存储二进制文件（通常不用）
text
存储文字（超过255个字符会用到它）
varchar
理论最大为65535个，但会多出一到两个去记入长度 但实际255个超过就会使用text类型
不重复的数据使用变长
char
可以储存的最大为255 utf8环境为12个字节，一个字符占3个长度 gbk环境为8字节，一个字符占2个长度
重复的
MySQL的常用通配符
%：任意多个字符
_：任意单个字符
escape：转义特定字符
字符集
set names 你要的字符集
show character set；
查看所有字符集
show variables like 'character_set%';
查看服务器默认处理的的字符集
索引
描述：系统通过某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件，文件能够实现快速的匹配数据，并且能够快速的找到对应表中的记录。 意义 提高查询数据的效率、约束数据的有效性（唯一性） 增加索引的同时，索引会产生索引文件，会消耗磁盘空间 如果某个字段需要作为查询条件经常使用时，可以添加索引
primary key
主键索引
uniquekey
唯一索引
fulltex index
全文索引
index
普通索引
关系
所有的关系都是指的表与表之间的关系
多对多
一张表（A）中的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能对应A表中的多条记录。方法：增加一个中间表，让其他两个表和中间表形成一对多关系
一对多
一张表中有一条记录可以对应另外一张表中的多条记录，但是反过来，另外一张表的一条记录只能对应第一张表中的一条记录。
一对一
一张表的一条记录一定只能与另外一张表的一条记录进行对应；反之亦然。
where
where原理：where是唯一一个从磁盘获取数据的时候就开始判断的条件，从磁盘取出一条记录，开始进行where判断，判断的结果如果成立就保存到内存，如果失败直接放弃
判断条件：比较运算符：>,<,>=,<=,!=,<>,=,like,between and, in/not in 逻辑运算符：&&(and),||(or),!(not)
注解
别名
给字段添加别名，更好的阅读这个字段
as
程序员可见
给字段添加相关的注解（程序员可见）
COMMENT
单行注释
# 注释
--空格 注释
多行注释
/* 注释 */
限制数量（limit）
方案一：只查询数量
格式：select * form 数据表 limit 长度；
方案二：限制起始位置，限制数量
limit方案二目的：主要用来实现数据的分页，为用户节省时间，提高服务器响应效率，减少资源的浪费。
格式：select * from 数据表 limit 2,5；
limit offset,length length：每页显示的数据量：基本不变（企业中一般不用我们去定义大下） offset：=（页码-1）* 每页显示量
蠕虫复制
从已有表创建新表（复制表结构）
格式：create table 表名 like 数据库.表名;
先查出数据，然后将查出的数据新增一遍（复制数据）
格式：insert into 表名[(字段列表)] select 字段列表/* from 数据表名；
备份和还原
备份数据库
不需要进入mysql数据库，外面使用
单个
mysqldump -uusemame -ppassword dbname[数据库] > 路径和名称
多个
mysqldump -uusemame - ppassword --databases 数据库名称[数据库1，数据库2] > 路径和名称
全部
mysqldump -uusemame -ppasswprd --all-databases > 路径和名称
还原数据库
先创建数据库名称，然后使用mysqldump -uusemame -ppassword dbname[数据库] > 路径和名称
用户
创建
创建用户：create user 'user2'@'localhost' identified by '123'; 设置权限：grant select on *.* to 'user2'@'localhost' with grant option;
mysql 8.0版本后的使用的方法
create user '用户名'@'hostname'[identified by [password]'password'][ '用户名'@'hostname'[identified by [password]'password']]
insert into mysql.user(host,user,password,ssl_cipher、x509_issuer、x509_subject)values('hostname','username',sha('password'),'','','');
查询
select host，user，authentication_string from user;
刷新权限表
flush privileges;
删除角色
delete from user where user = '用户名'and host = 'localhost';
drop user 'username'@'localhost';
修改用户密码
修改root用户密码
mysqladmin -u username -p password new_password
也是在外面执行语句
不推荐：updata mysql.user set password = password('new_password') where user = 'username' and host = 'hostname';
不推荐：set password = password('new_password');
推荐：alter mysql.user 'username'@'hostname' identified by 'new_password';
这个语句也可以用来修改普通用户的权限
root用户修改普通用户的密码
updata mysql.user set password = password('new_password') where user = 'username' and host = 'hostname';
grant usage on *.* to 'username'@'localhost' identified by 'new_password'
usage 表示无权限
set password for 'username'@'hostname'= password('new_password')
普通用户修改密码
格式：set password = password('new_password');
权限设置
授予权限
格式：grant privileges on database.table to 'username'@'hostname'[,'username'@'hostname',........][with[参数]]
设置权限的关键字：grant 权限设置：privileges 设置在哪个表：on database.table 部分权限： create和drop可以创建数据库、表、索引和删除数据库、表、索引 insert、delete、update、select对数据库中的已有的表进行增删改查操作 index可以删除或者创建索引，适用于所有表 alter可以修改表的结构或者重命名表 grant可以为其他用户授权，可用于数据库和表 file被赋予该权限的用户可以读写mysql服务器上的任何文件 with的参数： grant option 将自己的权限赋予其他用户 max_queries_per_hour count 设置用户每小时最多可以执行多少次（count）查询 max_updates_pre_hour count 设置每小时最多可以执行多少次更新 max_connections_pre_hour count 设置每小时最大的连接数量 max_user_connections 设置每个用户最大可以同时建立的连接数量
收回权限
所有权限：revoke all, grant option from 'username'@'hostname'[,'username'@'hostname',.......]
查看权限
格式：show grants for 'username'@'hostname';
浮动主题
浮动主题
进阶操作
最终格式