MySQL的基础知识

  • 数据库常见概念
    • DB：数据库，存储数据的容器。
    • DBMS：数据库管理系统，又称为数据库软件或数据库产品，⽤于创建或管理DB。
    • SQL：结构化查询语⾔，⽤于和数据库通信的语⾔，不是某个数据库软件持有的，⽽是⼏乎所有的主流数据库软件通⽤的语⾔。
  • MySQL常用的一些命令
    • mysql登录命令 mysql -h ip -P 端口 -u ⽤户名 -p
      • -P ⼤写的P后⾯跟上端口
      • 如果是登录本机，ip和端口可以省略 mysql -u ⽤户名 -p
    • 查看数据库版本
      • 在未登录情况下，查看本机mysql版本 mysql --version 或 mysql -V
      • 登录情况下，查看链接的库版本 select version();
    • 显示所有数据库 show databases;
    • 进⼊指定的库 use 库名;
    • 显示当前库中所有的表 show tables;
    • 查看其他库中所有的表 show tables from 库名;
    • 查看表的创建语句 show create table 表名;
    • 查看表结构 desc 表名;
    • 查看当前所在库 select database();
    • 查看当前mysql⽀持的存储引擎 SHOW ENGINES;
    • 查看系统变量及其值 SHOW VARIABLES;
    • 查看某个系统变量 SHOW VARIABLES like '变量名'; 或 SHOW VARIABLES like '%wait_timeou%t';
  • MySQL语法规范
    • 不区分⼤⼩写，但建议关键字⼤写，表名、列名⼩写
    • 每条命令最好⽤英⽂分号结尾
    • 每条命令根据需要，可以进⾏缩进或换⾏
    • 注释
      • 单⾏注释：#注释⽂字
      • 单⾏注释：— 注释⽂字 ，注意， 这⾥需要加空格
      • 多⾏注释：/ 注释⽂字 /
  • SQL的语⾔分类
    • DQL（Data Query Language）：数据查询语⾔ select 相关语句
    • DML（Data Manipulate Language）：数据操作语⾔ insert 、update、delete 语句
    • DDL（Data Define Languge）：数据定义语⾔ create、drop、alter 语句
    • TCL（Transaction Control Language）：事务控制语⾔ set autocommit=0、start
      transaction、savepoint、commit、rollback

MySQL的数据类型

  • 整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
    • 有符号类型：默认
    • ⽆符号类型：unsigned
    • 类型(n)说明：觉在开发过程中没有什么⽤途。⽆论n等于多少，int永远占4个字节；n表⽰的是显⽰宽度，不⾜的⽤0补⾜，超过的⽆视长度⽽直接显⽰整个数字，但这要整型设置了unsigned zerofill才有效
  • 浮点数类型：float、double、decimal
    • float数值类型⽤于表⽰单精度浮点数值，⽽double数值类型⽤于表⽰双精度浮点数值
    • float和double都是浮点型，⽽decimal是定点型
    • 浮点型和定点型可以⽤类型名称后加（M，D）来表⽰，M表⽰该值的总共长度，D表⽰⼩数点后⾯的长度，M和D又称为精度和标度
    • float和double在不指定精度时，默认会按照实际的精度来显⽰，⽽DECIMAL在不指定精度时，默认整数为10，⼩数为0
    • decimal采⽤的是四舍五⼊，float和double采⽤的是四舍六⼊五成双（就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后⾯是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前⾯的数字，若是奇数则进位，若是偶数则将5舍掉）
    • 浮点数float、double如果不写精度和标度，则会按照实际显示；decimal不写精度和标度，⼩数点后⾯的会进⾏四舍五⼊，并且插⼊时会有警告!
    • 从上⾯sum的结果可以看出float、double会存在精度问题，decimal精度正常的，⽐如银⾏对统计结果要求⽐较精准的建议使⽤decimal
  • 字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
    • char类型占⽤固定长度，如果存放的数据为固定长度的建议使⽤char类型，如：⼿机号码、⾝份证等固定长度的信息
  • ⽇期类型：Date、DateTime、TimeStamp、Time、Year
  • 其他数据类型：暂不介绍，⽤的⽐较少
  • 数据类型选择的⼀些建议
    • 选⼩不选⼤：⼀般情况下选择可以正确存储数据的最⼩数据类型，越⼩的数据类型通常更快，占⽤磁盘，内存和CPU缓存更⼩
    • 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型⽐字符操作代价要⼩得多，因为字符集和校对规则(排序规则)使字符⽐整型⽐较更加复杂
    • 尽量避免NULL：尽量制定列为NOT NULL，除⾮真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值⽐较更加复杂
    • 浮点类型的建议统⼀选择decimal
    • 记录时间的建议使⽤int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进⾏存储，⽅便⾛索引

MySQL用户管理

  • mysql是如何来识别⼀个⽤户的呢？
  1. mysql为了安全性考虑，采⽤主机名+⽤户名来判断⼀个⽤户的⾝份，因为在互联⽹中很难
  2. 通过⽤户名来判断⼀个⽤户的⾝份，但是我们可以通过ip或者主机名判断⼀台机器，某个
  3. ⽤户通过这个机器过来的，我们可以识别为⼀个⽤户，所以mysql中采⽤⽤户名+主机名
  4. 来识别⽤户的⾝份。当⼀个⽤户对mysql发送指令的时候，mysql就是通过⽤户名和来源
  5. （主机）来断定⽤户的权限。
  • Mysql权限验证分为2个阶段
    • 阶段1：连接数据库，此时mysql会根据你的⽤户名及你的来源（ip或者主机名称）判断是否有权限连接
    • 阶段2：对mysql服务器发起请求操作，如create table、select、delete、update、create index等操作，此时mysql会判断你是否有权限操作这些指令
  • 权限⽣效时间
  1. ⽤户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此
  2. 时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启mysql或者执
  3. ⾏flush privileges;才可以⽣效。
  4. ⽤户登录之后，mysql会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存
  5. 在这个连接中，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的
  6. 权限会在下⼀次登录时才会⽣效。
  • 查看mysql中所有⽤户 use mysql; select user,host from user;
  • 创建⽤户 create user ⽤户名[@主机名] [identified by '密码'];
    • 主机名默认值为%，表⽰这个⽤户可以从任何主机连接mysql服务器
    • 密码可以省略，表⽰⽆密码登录
  1. create user 'test2'@'localhost' identified by '123';
  2. -- 说明：test2的主机为localhost表⽰本机，此⽤户只能登陆本机的mysql
  3. create user 'test3'@% identified by '123';
  4. -- 说明：test3可以从任何机器连接到mysql服务器
  5. create user 'test4'@'192.168.11.%' identified by '123';
  6. -- 说明：test4可以从192.168.11段的机器连接mysql
  • 修改密码
    • 使用SET PASSWORD语句 SET PASSWORD FOR '⽤户名'@'主机' = PASSWORD('密码');
    • 使用ALTER USER语句 ALTER USER '⽤户名'@'主机' IDENTIFIED BY PASSWORD('密码');
    • 通过修改mysql.user表修改密码use mysql; update user set authentication_string = password('321') where user = 'test1' and host = '%'; flush privileges;(5.7中user表中的authentication_string字段表⽰密码，⽼的⼀些版本中密码字段是password)
  • 给⽤户授权 grant privileges ON database.table TO 'username'[@'host'] [with grant option]
    • priveleges (权限列表)，可以是all，表⽰所有权限，也可以是select、update等权限，多个权限之间⽤逗号分开
    • ON ⽤来指定权限针对哪些库和表，格式为数据库.表名 ，点号前⾯⽤来指定数据库名，点号后⾯⽤来指定表名，. 表⽰所有数据库所有表
    • TO 表⽰将权限赋予某个⽤户, 格式为username@host，@前⾯为⽤户名，@后⾯接限制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅
    • WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。注意：经常有⼈在创建操作⽤户的时候不指定WITH GRANT OPTION选项导致后来该⽤户不能使⽤GRANT命令创建⽤户或者给其它⽤户授权。 备注：可以使⽤GRANT重复给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个select权限，然后又给⽤户添加⼀个insert权限，那么该⽤户就同时拥有了select和insert权限
  1. grant all on *.* to 'test1'@'%';
  2. -- 说明：给test1授权可以操作所有库所有权限，相当于dba
  3. grant select on seata.* to 'test1'@'%';
  4. -- 说明：test1可以对seata库中所有的表执⾏select
  5. grant select,update on seata.* to 'test1'@'%';
  6. -- 说明：test1可以对seata库中所有的表执⾏select、update
  7. grant select(user,host) on mysql.user to 'test1'@'localhost';
  8. -- 说明：test1⽤户只能查询mysql.user表的user,host字段
  • 查看⽤户有哪些权限 show grants for '⽤户名'[@'主机'] （主机可以省略，默认值为%）
  • 撤销⽤户的权限 revoke privileges ON database.table FROM '⽤户名'[@'主机'];
  • 删除⽤户
    • drop user '⽤户名'[@'主机'] drop的⽅式删除⽤户之后，⽤户下次登录就会起效
    • 通过删除mysql.user表数据的⽅式删除 delete from user where user='⽤户名' and host='主机'; flush privileges;
  • 授权原则说明
    • 只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给select权限就可以了，不要给⽤户赋予update、insert或者delete权限
    • 创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定IP或者内⽹IP段
    • 初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤户，这些⽤户默认没有密码
    • 为每个⽤户设置满⾜密码复杂度的密码
    • 定期清理不需要的⽤户，回收权限或者删除⽤户
  • 总结
    • 通过命令的⽅式操作⽤户和权限不需要刷新，下次登录⾃动⽣效
    • 通过操作mysql库中表的⽅式修改、⽤户信息，需要调⽤flush privileges;刷新⼀下，下次登录⾃动⽣效
    • mysql识别⽤户⾝份的⽅式是：⽤户名+主机
    • 本节中讲到的⼀些指令中带主机的，主机都可以省略，默认值为%，表⽰所有机器
    • mysql中⽤户和权限的信息在库名为mysql的库中

DDL常⻅操作汇总

  • DDL：Data Define Language数据定义语⾔，主要⽤来对数据库、表进⾏⼀些管理操作。如：建库、删库、建表、修改表、删除表、对列的增删改等等。
  • 创建库 create database [if not exists] 库名;
  • 删除库 drop databases [if exists] 库名;
  • 建库通⽤的写法 drop database if exists 旧库名; create database 新库名;
  1. show databases like 'javacode2018';
  2. drop database if exists javacode2018;
  3. create database javacode2018;
  • 创建表
    • 在同⼀张表中，字段名不能相同
    • 宽度和约束条件为可选参数，字段名和类型是必须的
    • 最后⼀个字段后不能加逗号
    • 类型是⽤来限制 字段 必须以何种数据类型来存储记录
    • 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
    • 类型后写的 约束条件 是在类型之外的 额外添加的约束
  1. create table 表名(
  2. 字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  3. 字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  4. 字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
  5. )[表的⼀些设置];
  • 约束说明
    • not null：标识该字段不能为空
    • default value：为该字段设置默认值，默认值为value
    • primary key：标识该字段为该表的主键，可以唯⼀的标识记录，插⼊重复的会报错
      • ⽅式1：跟在列后 a int not null comment '字段a' primary key
      • ⽅式2：在所有列定义之后定义，⽀持多字段作为主键 PRIMARY KEY (a,b)
    • foreign key：为表中的字段设置外键 foreign key(当前表的列名) references 引⽤的外键表(外键表中字段名称)
      • 两张表中需要建⽴外键关系的字段类型需要⼀致
      • 要设置外键的字段不能为主键
      • 被引⽤的字段需要为主键
      • 被插⼊的值在外键表必须存在，如上⾯向test6中插⼊ts5_a为2的时候报错了，原因ts5_a：2的值在test5表中不存在
    • unique key(uq)：标识该字段的值是唯⼀的；⽀持⼀个到多个字段，插⼊重复的值会报违反唯⼀约束，会插⼊失败
      • ⽅式1：跟在字段后 a int not null comment '字段a' unique key
      • ⽅式2：所有列定义之后定义，⽀持多字段 unique key(a,b)
    • auto_increment：标识该字段的值⾃动增长（整数类型，⽽且为主键）
      • 默认值从1开始，每次+1
      • 关于⾃动增长字段的初始值、步长可以在mysql中进⾏设置，⽐如设置初始值为1万，每次增长10
      • ⾃增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中⾃增列的最⼤值作为当前值，如果表数据被清空之后，数据库重启了，⾃增列的值将从初始值开始
  • 删除表 drop table [if exists] 表名;
  • 修改表名 alter table 表名 rename [to] 新表名;
  • 表设置备注 alter table 表名 comment '备注信息';
  • 复制表（只复制表结构） create table 表名 like 被复制的表名;
  • 复制表结构+数据 create table 表名 [as] select 字段,... from 被复制的表 [where 条件];，如 create table test13 as select * from test11;
  • 添加列 alter table 表名 add column 列名 类型 [列约束];，如 alter table test14 add column b int not null default 0 comment '字段b';
  • 修改列 alter table 表名 modify column 列名 新类型 [约束]; 或 alter table 表名 change column 列名 新列名 新类型 [约束];，2种⽅式区别：modify不能修改列名，change可以修改列名
  • 删除列 alter table 表名 drop column 列名;

DML常⻅操作

  • DML(Data Manipulation Language)数据操作语⾔，以INSERT、UPDATE、DELETE三种指令为核⼼，分别代表插⼊、更新与删除，是必须要掌握的指令，DML和SQL中的select熟称CRUD（增删改查）
  • 插⼊单⾏
    • insert into 表名[(字段,字段)] values (值,值);
      • 值和字段需要⼀⼀对应
      • 如果是字符型或⽇期类型，值需要⽤单引号引起来；如果是数值类型，不需要⽤单引号
      • 字段和值的个数必须⼀致，位置对应
      • 字段如果不能为空，则必须插⼊值
      • 可以为空的字段可以不⽤插⼊值，但需要注意：字段和值都不写；或字段写上，值⽤null代替
      • 表名后⾯的字段可以省略不写，此时表⽰所有字段，顺序和表中字段顺序⼀致。
    • insert into 表名 set 字段 = 值,字段 = 值; 该方式不常见，不建议使⽤
  • 批量插⼊
    • insert into 表名 [(字段,字段)] values (值,值),(值,值),(值,值);
    • insert into 表 [(字段,字段)] 数据来源select语句;
      • 数据来源select语句可以有很多种写法，需要注意：select返回的结果和插⼊数据的字段数量、顺序、类型需要⼀致
  • insert IGNORE关键字会让INSERT只插入数据库不存在的记录 INSERT [IGNORE] INTO 表名 ...;
  1. -- 创建test1
  2. create table test1(a int,b int);
  3. -- 创建test2
  4. create table test2(c1 int,c2 int,c3 int);
  5. -- 向test2中插⼊数据
  6. insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
  7. -- 向test1中插⼊数据
  8. insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
  9. -- 向test1插⼊数据，数据来源于test2表
  10. insert into test1 (a,b) select c2,c3 from test2 where c1>=200;
  • 单表更新 update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];
    • 有些表名可能名称⽐较长，为了⽅便操作，可以给这个表名起个简单的别名，更⽅便操作⼀些
    • 如果⽆别名的时候，表名就是别名
  • 多表更新 update 表1 [[as] 别名1],表名2 [[as] 别名2] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件]（建议采⽤单表⽅式更新，⽅便维护）
  • UPDATE语句用于修改表的记录
    • IGNORE：修改记录时忽略冲突的记录
    • ORDER BY：比如给所有员工号加1，员工号是主键，就会引起冲突；需要给员工号降序排列，然后依次加1
    • LIMIT：用于修改前几条数据，只有一个参数
    • 执行顺序：UPDATE -> WHERE -> ORDER BY -> LIMIT -> SET
  1. UPDAET [IGNORE] 表名
  2. SET 字段1=值1, 字段2=值2, ...
  3. [WHERE 条件1 ...]
  4. [ORDER BY ...]
  5. [LIMIT ...];
  • delete单表删除 delete [别名] from 表名 [[as] 别名] [where条件];
    • 如果⽆别名的时候，表名就是别名
    • 如果有别名，delete后⾯必须写别名
    • 如果没有别名，delete后⾯的别名可以省略不写
  1. -- 删除test1表所有记录
  2. delete from test1;
  3. -- 删除test1表所有记录
  4. delete test1 from test1;
  5. -- 有别名的⽅式，删除test1表所有记录
  6. delete t1 from test1 t1;
  7. -- 有别名的⽅式删除满⾜条件的记录
  8. delete t1 from test1 t1 where t1.a>100;
  • 多表删除，同时删除多个表中的记录 delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];，别名可以省略不写，但是需要在delete后⾯跟上表名，多个表名之间⽤逗号隔开
  1. - 删除test1表中的记录，条件是这些记录的字段a在test.c2中存在的记录
  2. delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
  3. - 同时对2个表进⾏删除，条件是test.a=test.c2的记录
  4. delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
  • DELETE语句用于删除表的记录
    • IGNORE：删除记录时忽略冲突的记录
    • ORDER BY：排序
    • LIMIT：用于删除前几条数据，只有一个参数
    • 执行顺序：FROM -> WHERE -> ORDER BY -> LIMIT -> DELETE
  1. DELETE [IGNORE] FROM 表名
  2. [WHERE 条件1, 条件2, ...]
  3. [ORDER BY ...]
  4. [LIMIT ...];
  • 使⽤truncate删除 truncate 表名;
  • drop，truncate，delete区别
    • drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉，以后要新增数据是不可能的，除⾮新增⼀个表；drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid；如果要删除表定义及其数据，请使⽤ drop table 语句
    • truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据⽽已。注意：truncate不能删除具体⾏数据，要删就要把整个表清空了。
    • delete (删除表中的数据)：delete 语句⽤于删除表中的⾏。delete语句执⾏删除的过程是每次从表中删除⼀⾏，并且同时将该⾏的删除操作作为事务记录在⽇志中保存，以便进⾏进⾏回滚操作。
    • 对于由foreign key约束引⽤的表，不能使⽤truncate table ，⽽应使⽤不带where⼦句的delete语句。由于truncate table 记录在⽇志中，所以它不能激活触发器。
    • delete语句是数据库操作语⾔(dml)，这个操作会放到 rollback segement 中，事务提交之后才⽣效；如果有相应的 trigger，执⾏的时候将被触发
    • truncate、drop 是数据库定义语⾔(ddl)，操作⽴即⽣效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger
    • 如果有⾃增列，truncate⽅式删除之后，⾃增列的值会被初始化，delete⽅式要分情况（如果数据库被重启了，⾃增列值也会被初始化，数据库未被重启，则不变）
    • 如果要删除表定义及其数据，请使⽤ drop table 语句
    • 安全性：⼩⼼使⽤ drop 和 truncate，尤其没有备份的时候，否则哭都来不及
    • 删除速度，⼀般来说: drop> truncate > delete