MySQL常用指令

数据库 SQL

Created At : 2019-09-07 21:10

Views 👀 :

Comment:

一、MySQL

启动

services start mysqlXXXX

启动MySQL
XXX -> 版本号(如：@5.7)

services stop mysqlXXXX

停止MySQL
XXX同上

mysql -u root(默认用户名) -p

登陆MySQL

mysql -h 主机名 -u root -p

exit

退出MySQL用户

管理

use 数据库名;

选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

show databases;

列出 MySQL 数据库管理系统的数据库列表。

show tables;

显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

show columns from 表名;

显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

show index from 数据库;

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

show table status [from 数据库名] [like ‘表名’] \G;

该命令将输出Mysql数据库管理系统的性能及统计信息。
from 数据库名：显示数据库中的所有表信息
like ‘表名’：数据库中该表名的所有信息
\G：格式化打印

创建数据库

基本操作数据库

DDL：操作数据库和表
DML：增删改数据库表中的数据
DQL：查询数据库表中的数据
DCL：权限管理

CRUD：
create(创建),retrieve(查询),update(修改),delete(删除)

DDL 操作数据库和表

操作数据库

* 创建数据库

create
```
  * create database 数据库名称;
```
- 创建数据库，判断在不在
  - create database if not exists 数据库名称;
- 创建数据库指定字符集
  - create database if not exists 数据库名称 character set 字符集;
retrieve
- 查询所有数据库名称
  - show databases;
- 查询某个数据库字符集
  - show create database 数据库名称;
update
- 修改字符集
  - alter database 数据库名称 character set 字符集名称;
delete
- 删除数据库
  - drop database 数据库名称;
- 判断是否存在并删除
  - drop database if not exists 数据库名称;*

使用数据库

查询数据库名称
- select database()
使用数据库
- use 数据库名称*

操作表

create
- 创建表
  - create table 表名称(列名数据类型, 列名数据类型);
  - create table if not exists 表名称(列名数据类型, 列名数据类型);
- 复制表
  - create table 表名 like 被复制的表名;
retrieve
- 查询所有表名称
  - show tables;
- 查询表结构
  - desc 表名;;
update
- 修改表名
  - alter table 表名 rename to 新的表名;
- 修改表的字符集
  - alter table 表名 character set 字符集名称;
- 添加一列
  - alter table 表名 add 列名数据类型;
- 需改列名称类型
  - alter table 表名 change 列名新列名新数据类型;
  - alter table 表名 modify 列名新数据类型;
- 删除列
  - alter table 表名 drop 列名;
delete
- 删除表
  - drop table 表名称;
- 判断是否存在并删除
  - drop table if not exists 表名称;

DML 增删改数据库表中的数据

添加数据

语法
- insert into 表名(列名, 列名, …) values(数据, 数据, …);

删除数据

语法
- delete from 表名 [ where 条件 ];
- truncate table 表名; — 删除名，然后在创建一个一模一样的空表

修改数据

语法
- update 表名 set 列名=值, 列名=值,…;

DQL 查询数据库表中的数据

基本语法

  `
select
      字段名列表
  from
      表名
  where
      条件列表
  group by
      分组字段
  having
      分组之后的条件
  order by
      排序
  limit
      分页条件;
      `

查询语句

查询条件
- 去除重复
  - distinct
- ifnull(列名, 默认值)
- 修改显示名称：列名 as 别名 或者 列名别名
- 条件
  - between…and…：在两者之间
  - in(..,.)：同时满足括号内条件
  - like：模糊查询
    - 占位符
      - _：单个占位字符
      - %：多个占位字符
  - is null && not is null：为null或不为null
  - and &&
  - or ||
  - not !
排序查询
- 语法：order by 子句
  - order by 排序字段1 排序方式1, 排序字段2 排序方式2,…;
- 排序方式：
  - ASC：升序，默认
  - DESC：降序
聚合函数：将一列数据作为一个整体，进行纵向计算，可以添加别名对其表达
- count：计算个数
- max：计算最大值
- min：计算最小值
- sum：计算和
- avg：计算平均值
- 注意，聚合函数的计算会排除null
  - 选择非空列
  - ifnull函数
  - * 但并不推荐
分组查询
- 语法：group by 分组字段;
  - 分组后查询的字段：分组字段，聚合函数，否则没有意义
  - where 和 having 的区别：
    - where 在分组之前进行限定，如果不满足，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
    - where 后不可以跟聚合函数，having 可以进行聚合函数的判断。
分页查询
- 语法：limit 开始的下表，查询的数量
- 公式：开始的索引 = (当前的页码 - 1) * 每页查询的条数;

约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类
- 主键约束：primary key
  - 非空且唯一，一张表只能有一个字段为主键，表中记录的唯一标识
  - 创建
    - create table stu(id int primary key,…);
  - 创建表后添加主键
    - alter table stu modify id int primary key;
  - 删除
    - alter table sut drop primary key;
  - 自动增长
    - 如果某一列是数值类型，使用 auto_increment 可以自动完成值的增加
    - 添加自动增长
      - alter table stu modify id int auto_increment;
    - 删除自动增长
      - alter table stu modify id int;
- 非空约束：not null
  - 创建表时添加的约束
    - create table stu(name varchar(20) not null);
  - 创建表后添加非空约束
    - alter table stu modify name int not null;
  - 删除name的非空约束
    - alter table stu modify name int;
- 唯一约束(值不能重复)：unique
  - 注意：在mysql中，唯一约束限定的值可以有多个null
  - 创建表时添加的唯一约束
    - create table stu(name varchar(20) unique);
  - 创建表后添加唯一约束
    - alter table stu modify name int unique;
  - 删除唯一约束
    - alter table stu drop index 列名;
- 外键约束：foreign key
  - 在创建表时添加外键
    - create table 表名(…, constraint 外键名称 foreign key (外键列的名称) references 主表名称(主表列名称))
  - 删除外键
    - alter table 表名 drop foreign key 外键名称
  - 创建表后创建外键
    - alter table 表名 add constraint 外键名称 foreign key (外键列的名称) references 主表名称(主表列名称)
  - 级联操作：一系列联动操作
    - 添加级联更新：on update cascade
      - alter table 表名 add constraint 外键名称 foreign key (外键列的名称) references 主表名称(主表列名称) on update cascade;
    - 级联删除：on delete cascade
      - alter table 表名 add constraint 外键名称 foreign key (外键列的名称) references 主表名称(主表列名称) on update cascade on delete cascade;

多表之间的关系

一对一
- 在一方表中添加唯一外键指向另一张表的主键
一对多(多对一)
- 在多的一方建立外键，指向一的一方的主键
多对多
- 借助中间表，中间表中至少包含两张表中的主键以及联合主键

范式

设计数据库时使用的一些规范。成递次规范

第一范式：每一列都是不可分割的原子数据项
- 能建立数据库关系
第二范式：在1NF基础上，非码属性必须完全依赖于候选码(消除非主属性对主码的部分函数依赖)
- 函数依赖：A—>B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称为B依赖于A。如学号—>姓名，(学号，课程名称)—>分数
- 完全函数依赖：A—>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有属性值。如(学号，课程名称)—>分数
- 部分函数依赖：A—>B，如果A是一个属性组，则B属性值的确定只需要A属性组中某一些值即可。如(学号，课程名称)—>姓名
- 传递函数依赖：A—>B，B—>C，如果通过A属性(属性组)的值，可以确定唯一B属性的值；再通过B属性(属性组)的值可以确定唯一C属性的值，则称C传递函数依赖与A。如学号—>系名，系名—>系主任
- 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码。如(学号，课程名称)
  - 主属性：码属性组中的所有属性
  - 非主属性：除过码属性组的属性
第三范式：在2NF基础上，任何非主属性不依赖于其他非主属性(消除传递依赖)
巴斯克德范式
第四范式
第五范式

数据库的备份和还原

命令行方式
- 备份： mysqldump -u用户名 -p密码数据库名称 > 保存路径
- 还原：
  - 创建数据库
  - 执行文件 source 文件路径
图形化工具

多表查询

笛卡尔积： A 和 B 的集合。但是需要消除无用数据。
分类：

内连接查询：
- 隐式内连接：使用where条件消除无用数据
  - select 表1.列名, 表2.列名 from 表1 别名, 表2 别名 where 表1id = 表2id;
- 显示内连接：
  - select 字段 from 表名1 inner join 表名2 on 条件;// inner可以省略
- 注意
  - 从哪些表插数据
  - 条件是什么
  - 查询哪些字段
外连接查询：
- 左外连接：查询的是左表所有记录及交集部分
  - select 字段 from 表1 left outer join 表2 on 条件;// outer可以省略
- 右外连接：查询的是右表所有记录及交集部分
  - select 字段 from 表1 right outer join 表2 on 条件;// outer可以省略
子查询：查询中嵌套查询，称嵌套查询为子查询
- 子查询的结果是单行单列：子查询可以作为条件，使用运算符判断。
  - select * from 表 where 列 = (子查询);
- 子查询的结果是多行单列
  - 使用运算符 in 判断：select 列 from 表 where 条件 in (子查询);
- 子查询的结果是多行多列
  - 子查询可以作为一张虚拟表：select 列 from 表1, (子查询);

事务

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：
- 开启事务: start transaction;
- 回滚: rollback;
- 提交: commit;

MySQL数据库中事务默认自动提交

事务提交的两种方式：
- 自动提交
  - mysql就是自动提交的，oracle是手动提交的
  - 一条DML语句就会自动提交一次事务
- 手动提交
  - 需要先开启事务，在提交
- 修改事务的默认提交方式
  - 查看事务的默认提交方式：select @@autocommit; — 1代表自动提交，0代表手动提交
  - 修改默认提交方式：set @@autocommit=0; — 修改后如果不commit，语句不会生效
四大特征
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
- 持久性：当事务提交或回滚后，数据库会发生持久化保存
- 隔离性：多个事务之间，相互独立
- 一致性：数据操作前后，数据总量不变
隔离级别
多个事务之间是隔离的，相互杜立德。但是多个事务操作同一批数据，就会发生一些问题，设置不同的隔离级别，就可以解决这些问题。
问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据，
- 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作(DML)数据表中所有数据，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
  
  隔离级别
- read uncommitted：读未提交
  - 产生的问题：脏读、不可重复读、幻读
- read committed：读已提交(oracle默认)
  - 产生的问题：不可重复读、幻读
- repeatable read：可重复读(mysql默认)
  - 产生的问题：幻读
- serializable：串行化
  - 可以解决所有的问题
- 注意：隔离级别从上到下，安全越来越高，效率越来越低
- 查询隔离级别
  - select @@tx_isolation
- 设置隔离级别
  - set global transaction isolation level 级别字符串;

DCL 权限管理

管理用户
- 添加用户：
  - create user ‘用户名’@’主机名’ identified by ‘密码’;
- 删除用户：
  - drop user ‘用户名’@’主机名’;
- 修改用户：
  - update user set password = password(‘新密码’) where user = ‘用户名;
  - set password for ‘用户名’@’主机名’ = password(‘新密码’);
  - 修改root密码
    - 1.停止mysql服务
    - 2.mysqld —skip-grant-tables;— 使用无验证方式启动mysql服务
    - 3.mysql 直接登陆进入修改root密码
    - 4.手动接受这个mysql进程，后重启即可
- 查询用户：切换到mysql数据库中的user表中，
  - select * from user;
授权
- 查询权限
  - show grants for ‘用户名’@’主机名’;
- 授予权限
  - grant 权限列表 on 数据库.表名 to ‘用户名’@’主机名’;
  - grant all on *.* to ‘用户名’@’主机名’;
- 撤销权限
  - revoke 权限列表数据库.表名 from ‘用户名’@’表名’;