MySQL 常用命令一览（万字好文）

一、使用 mysql

下载链接：
链接：https://pan.xunlei.com/s/VMNHMWaZ-bLa5HltrBnjRPdVA1
提取码：v8rh

1.1 登录登出 mysql

默认登录：mysql -uroot -p ,然后输入密码登录即可

查看版本信息：mysql -V

1.2 mysql 退出

有三种退出方法

sql>exit;
sql>quit;
sql>\q

1.3 mysql 登录常用参数

1.4 mysql 命令提示符修改

当我们登录进 mysql 的时候，可以看到如下信息 mysql> ....

但是如何修改提示符呢？有两种方式

1. 在链接客户端的时候通过参数指定

mysql -uroot -proot --prompt 提示符 

2. 连接上客户端后，通过 prompt 改变

mysql> propmt 提示符

提示符的参数

接下来把提示符修改为：用户名 + @ + 主机名 + 数据库的形式

prompt '\u@\h \d>'

补充：这里显示的 none 是因为我们刚登陆，没有指定是使用哪个数据库，所以会显示 none

因此我们可以使用如下命令选择指定数据库

show databases; --这句话的意思是 展示当前系统有哪些数据库可以用

use bank; -- 使用 bank 数据库

1.6 mysql 常用命令

数据类型	代表
mysql -uroot -proot -P3306 -h127.0.0.1 （mysql -uroot -p [常用命令]）	数据库连接命令 （可以连接指定服务器的数据库）
select version()	显示当前服务器版本
select now()	显示当前日期时间
select user()	显示当前用户
show databases;	显示当前的数据库
show tables [from db_name] [like ‘pattern’	where expr]
show columns from tb1_name	查看表结构
use tb_name	使用数据库
select database()	显示当前使用的数据库
create database tb_name	创建一个数据库
drop database tb_name	删除数据库
set names gbk(utf-8)	解决编码格式，解决现实乱码问题

1.7 数据库用户及权限

1.7.1 用户

1. 创建用户

create user username@'host' identified by "password"

eg:
create user happy@"localhost" identified by "happy"

2. 用户授权 与 收回权限
   权限分为系统权限 和 数据对象权限

系统权限：

• connect
• resource
• DBA

数据对象权限

• all
• select
• update
• alter
• insert
• delete

收回权限

• revoke

grant permission on database tables to user@'host'

grant connect,resource to 用户名

grant select on 数据库 表 to 用户

# 收回权限
revoke connect,xxx from 用户名

revoke select on 表名 from 用户名

1.8 mysql8 修改为原始登录密码

mysql8密码修改
alter user 'team01'@localhost IDENTIFIED WITH mysql_native_password by '123456';

二、数据库表操作

2.1 数据类型整理

整形

浮点数：

日期和时间型

字符类型：

2.2 常见数据表操作

2.2.1 DDL 数据库定义语言

1. create 数据库（表）创建语言
2. alter 数据库（表）修改语言
3. drop、delete 数据库（表）删除语言

创建数据表 create

基本语法：

create table [if not exists] table_name(
	column_name data_type,
	.....
);

-- eg  创建一个用户表
create table user(
	username varchar(20),
	age tinyint(3) unsigned, -- 短整型 unsigned 表示无符号，也就是默认大于 0
	salary float(8,2) unsigned
);

表约束系列 —— 和 oracle 的一致

修改数据表 alter

【1】 修改表结构

1. 增加数据表的列

alter table tb_name add [column] (col_name col_data_tyoe + 约束条件) 

2. 删除数据表的列

alter table tb_name drop [column] col_name;

3. 修改数据表

alter table tb_name modify [column] col_name col_definition[约束类型] [first] ;

-- 修改列名称
alter table tb_name change old_name new_name col_definition;

4. 数据表更名

不建议随意修改数据列和数据表的名称

--一、方式一
alter table rename [to|as] new_tb_name;

-- 二、方式二
rename table tb_name to new_tb_name [, tb_name1 to tb_new_name1]

【2】 修改表约束

3. 增加约束

alter table tb_name add 关键字 data_type 约束条件;

-- 给余数增加名字，以主键约束为例
alter table tb_name add [constraint] pk_users2_id primary key (id);

alter table tb_name add unique key (username); -- 简写

4. 删除约束

-- 删除主键约束
alter table tb_name drop primary key;

-- 删除唯一约束
alter table tb_name drop {index | key } index_name;

-- 删除外键约束
alter table tb_name drop foreign key 外键约束名;

【3】其他实用技巧

重新设置编号初始值

alter table 表名 auto_increment=1;

删除表，数据库和记录

1. 删除表 （表和数据都会删除，一定要谨慎）

drop table 表名;

drop table if exists 表名;-- 存在即删除

2. 删除所有记录 (不删除表自身，只删除表中记录的方法）删除所有数据

delete from 表名;

DDL 语句会隐式提交，不能 rollback

2.2.2 DML 数据库操作语言

1. 插入数据 insert
2. 更新数据 update
3. 删除数据 delete

插入数据

-- col_name 和 val 的数量要一一对应
insert [into] tb_name (col_name,...) values (val1,---);

-- 插入方式 一 （tb_name 里面的字段有几个，值就对应几个）
insert into tb_name(字段一，字段二,....) values （值1，值2..）

-- 简化版 （不写字段，直接加 values，这里你必须插入表中所有的字段）
insert into tb_name values(字段1，字段2，字段3); -- 这样写的话，必须包含所有的字段，不然数据库会不匹配

-- eg Student 有 name 和 age
insert into student(name,age) values("xxx",18);
insert into student values("xxx",10);

更新数据

update tb_name set 字段名 = 'xxx'; -- 修改这列的所有数据

update tb_name set 字段名 = 'xxxx' where xxx; -- 修改指定列的数据

删除数据

delete from tb_name where xxxx; -- 删除指定数据

2.2.3 DQL 数据库查询语言

查询语法：

简单查询

select * from tb_name; --查询某表的所有数据

select col_name1,col_name2 .... from tb_name; -- 查询字段指定列数据

-- 给查询字段取别名
select col_name1 as xxx from tb_name; -- 给查询的字段的列使用别名替代

字段过滤查询

-- where
select * from tb_name where id%2 = 0; -- 查询偶数列的数据

-- group by 分组查询 （分组查询一般在多表中查询使用的比较多） 
-- 分组条件 having

字段排序

order by 字段名 asc升序，desc 降序

分页查询

select * from tb_name limit 2; -- 得到两条记录

select * from tb_name limit 3,2; -- 从第三条记录开始，查询两条记录

2.3 高级用法

2.3.1 表复制（结构 | 数据）

复制表结构和数据

create table tb1A select * from tb1;
create table tb1B select * from tb2;

复制表的列结构

create table 新表名 like 元素名;

复制其他表的记录

insert into 表名 select * from 元素名;

选择一列进行复制

insert into tb1_emp(name) select emp_name from tb_emp; -- 注意参数类型，长度问题

2.3.4 创建索引

当数据库中的数据的数据量过大时，查询效率就会变得非常非常的低，在这种情况下，我们可以在表上建立索引（index）

索引类型：

1. 主键索引 （primary key）
2. 唯一索引 （unique）
3. 普通索引
4. 普通组合索引

如果事先建立了索引，查询就不会对全表进行扫描，而是利用索引扫描，这样可以减少查询时间。

我们常见创建的主键就是一种索引，它会随着主键的创建而创建

-- 创建普通索引
create index 索引名 on 表名 (列名);

-- 创建普通组合索引
create index 索引名 on 表名 (列名1,列明2,列名3...);

-- 格式化显示索引
show index from 表名 \G;

-- 删除索引
drop index 索引名 from 表名;

索引的创建是影响整个数据库处理效率的重要问题，我们把这种提高效率的策略称为调优（tuning）

索引的使用原则：

1. 索引的创建为 where 子句 或连接子句构成的列
2. 基数小的列，索引效果较差，没必要为此创建索引
3. 使用短索引，字符串列需要截取前面一部分的长度
4. 不要为了使用索引而使用索引

三、mysql 内置函数使用

3.1 字符函数

1. concat 字符连接函数
   

2. 格式化 format
   

3. 字符小写 lower()
   

4. 字符全部大写
   

5. 字符串截取 left right
   

6. 获取字符串长度
   

7. 删除字符串中的空格 trim
   
   删除指定字符
   

8. 替换 replace （还可以一换多）
   

9. 字符串截取
   mysql 截取的下标从 1开始截取所有字符串
   
   

10. 模糊查询 like
    % 代表任意字符
    _ 代表一个字符
    

3.2 数值运算符和函数

3.3 比较运算与函数

between xxx and xxx 在 范围内
[not] in() 一系列数值之内
is [not] null 是否为空

3.4 日期和和函数

3.5 信息函数

3.6 聚合函数

3.7 加密函数

四、mysql 自定义函数

自定义函数须有两个必要条件

1. 参数
2. 返回值

使用 select fi() 就可以看到结果了

如果是这样的，那就是 unicode 编码问题，我们需要改变数据库的编码方式

五、mysql 优化

5.1 优化查询的方式

1. SQL 中 FROM 子句中的表名，执行顺序上最先处理的表叫基表
2. 在 FROM 子句中包含多个表，选择记录条数最少的表作为基础表
3. 3个以上的表连接查询，选择交叉表作为基础表

使用 exists 代替 in

exists 只有 true 或 false，而 in 会做全扫描

5.2 其他

1. 建表和索引
2. sql优化
3. 做模块开发使用存储过程