超基础MySQL常用知识,总有一个知识点你不常想起

客户端操作

查看mysql字符编码的设置情况

方式一：

status

方式二：

show variables like 'char%';

修改mysql的用户密码

set password for root@localhost=password('1234');

创建用户与设置用户权限

## 创建用户：
## 		用户名格式： 用户名@主机名
CREATE USER 新用户名 IDENTIFIED BY '密码';
## 为用户赋予相应的权限：
## 		所有权限：ALL；如果仅允许SELECT和UPDATE，那么就设置为："SELECT,UPDATE"
##		表示所有数据库：*.*
GRANT 赋予的权限 ON 数据库名.表名 TO 用户名;

删除用户

如果想删除某个用户，或者在创建用户的时候报这样的错误：

ERROR 1396 (HY000): Operation CREATE USER failed for 'wjtest'@'%'

那么就可以执行下面这样的操作：

# 查看mysql的用户
## 或者 SELECT user FROM mysql.user;
use mysql;
select user from user;

# 删除test用户
drop user 'test'@'%';

# 刷新mysql权限
flush privileges;

创建数据库

创建数据库

CREATE DATABASE 数据库名;

数据库名和表名，在windows、macOS和Linux上的处理方法并不相同。在windows和macOS的环境中不区分字符的大小写，但是在Linux环境中却区分大小写。

举个例子：tb1和TB1在windows和macOS环境中会作为相同的表名处理；但在Linux中不会。

显示数据库信息

SHOW DATABASES;

指定使用的数据库

指定数据库

USE 数据库名;

显示当前使用的数据库

SELECT DATABASE();

创建表

CREATE TABLE 表名 (列名1 数据类型, 列名2 数据类型, ...);

对于数据库名、表名和列名可以使用``(反括号)括起来使用。

显示所有表

SHOW TABLES;

指定字符编码创建表

当输入字符到表中出现字符乱码的情况时，可以在命令行后面指定字符编码；如：

CREATE TABLE tb1 (empid varchar(10), name varchar(10), age int) CHARSET=utf8;

确认表的列结构

DESC 表名;

数据类型

数据库的表中只能输入各个列指定格式的数据。这种数据的格式称为数据类型。数据类型具有降低输入错误数据的可能性、保证数据库整体可信赖性的特点。

常用的数据类型

数值类型

数据类型	含义
INT	整型：整数类型(占32位二进制)
TINYINT	极小整型：-128~127
SMALLINT	小整型：-32768~32767
MEDIUMINT	中整型：-8388608~8388607
BIGINT	大整型：超级大了
FLOAT	单精度浮点数，占4字节
DOUBLE	双精度浮点数，占8字节
DECIMAL	精确小数，DECIMAL(最大位数,小数点之后的位数)的格式中的“最大位数”可以指定不大于65的值，“小数点之后的位数”可以指定不大于30的值。不会产生误差

字符串类型

数据类型	含义
CHAR	固定长度字符串，长度不超过255个字符
VARCHAR	可变长度字符串，1~65532字节，长度取决于使用的字符编码
TEXT	长文本字符串，长度不超过65535个字节
LONGTEXT	极长文本字符串，长度不超过4294 967 295个字节

日期与时间类型

数据类型	含义
TIME	表示时间类型
DATE	表示日期类型
DATETIME	同时可以表示日期和时间类型

修改提示符的字符串

# 提示符还可以 通过"\d"设置数据库名，"\h"设置主机名，"\u"设置用户名
promp 作为提示符显示的文本

修改表

修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 数据类型;

修改数据类型要慎重，如果列中存在数据，原则上就不应该再修改列的数据类型了。

添加列

ALTER TABLE 表名 ADD 列名 数据类型;

修改列的位置

将列添加到最前面

ALTER TABLE 表名 ADD 列名 数据类型 FIRST;

把列添加到指定位置

ALTER TABLE 表名 ADD 列名 数据类型 AFTER 指定列名;

修改列名和数据类型

ALTER TABLE 表名 CHANGE 修改前的列名 修改后的列名 修改后的数据类型;

删除列

ALTER TABLE 表名 DROP 列名;

设置主键

创建主键

主键用于在多条记录中确定一条记录时使用的标识符。具备以下特征：

• 没有重复的值；
• 不允许输入空值(NULL)；

在创建表的时候设置主键

CREATE TABLE 表明 (列名 数据类型 PRIMARY KEY, ...);

设置唯一键

唯一键虽然不允许列中有重复值，但允许输入NULL。

CREATE TABLE 表明 (列名 数据类型 UNIQUE, ...);

使用具有自动连续编号功能

要使列具有自动连续编号功能，就得在定义列的时候进行以下三项设置：

1. 数据类型为INT等整数类型；
2. 加上AUTO_INCREMENT；
3. 设置PRIMARY KEY，使列具有唯一性;

CREATE TABLE 表明 (列名 数据类型 AUTO_INCREMENT PRIMARY KEY , ...);

连续编号的初始化

如果把表中的数据都删除，然后重新输入记录，自动编号不会从1开始分配，而是从最大值+1的值开始分配。如果过想要从1开始分配，则可以：初始化AUTO_INCREMENT的值：

ALTER TABLE 表名 AUTO_INCREMENT=1;

注意：如果表中还有数据，这时设置AUTO_INCREMENT的值比表中已经存在的编号最大值小，那么下一次编号是最大值+1，而不是设置的AUTO_INCREMENT。

设置列的默认值

CREATE TABLE 表名 (列名 数据类型 DEFAULT 默认值, ...);

创建索引

当查找表中的数据时，如果过数据量过于庞大，查找操作就会花费很多时间。在这种情况下，最好在表上创建索引。如果事先创建了索引，那么查找时就不会对全表进行扫描，而是利用索引进行扫描。

创建索引

CREATE INDEX 索引名 ON 表名 (列名);

查看索引

## 横向显示
SHOW INDEX FROM 表名;
## 纵向显示
SHOW INDEX FROM \G

删除索引

DROP INDEX 索引名 ON 表名;

索引和处理速度的关系：创建了索引并不一定会缩短查找时间；在相同值较多的情况下最好不好创建索引。

• 当对创建了索引的表进行更新时，也需要对已经存在的索引信息进行维护。检索速度快，更新速度慢；
• 在使用索引的情况下，即使索引在创建过程中出现了错误，查找结果也不会受到任何影响。创建索引只会影响数据库整体的处理速度。

复制、删除表和记录

复制表的三种方法

复制表的列结构和记录

使用SELECT的结果复制列结构和记录，然后创建新的表。该方法不能复制AUTO_INCREMENT等属性。需要在复制后重新设置。

CREATE TABLE 新表名 SELECT * FROM 元表名;

复制完后，需要先使用DESC 表名确认表的结构之后再使用。

仅复制表的列结构

在创建表名后面加上LIKE指定复制的元表，这种复制方法会复制AUTO_INCREMENT等属性。

CREATE TABLE 新表名 LIKE 元表名;

仅复制记录

INSERT INTO 表名 SELECT * FROM 元表名;

删除表、数据库和记录

删除表

DROP TABLE 表名;  ## 如果表不存在的情况下会发生错误，建议用下面一条命令删除
## 当目标表存在时将其删除
DROP TABLE IF EXISTS 表名;

删除数据库

DROP DATABASE 数据库名;

删除表中所有记录

DELETE FROM 表名;

在不登录mysql的情况下操作mysql

使用mysqladmin创建数据库

mysqladmin -u 用户名 -p密码 CREATE 数据库名
## 删除该数据库
mysqladmin -u 用户名 -p密码 DROP 数据库名

使用mysql命令执行查询

如果在不启动mysql的情况下想要操作mysql，可以加上 -e选项，并且用""双引号将后面的命令括起来。

mysql 数据库名 -u 用户名 -p密码 -e "命令"

举个例子：

mysql db1 -u root -proot -e "SELECT * FROM tb1";

参考资料

西泽梦路(著) , 卢克贵 (译) . MySQL基础教程 . 人民邮电出版社 . 2020.1