2021-10-06 全面，详细，MySQL命令语句，值得收藏

<!--DOS窗口登录数据库-->

mysql -h 服务器主机地址 -u 用户名 -p 密码

• 如果在本机操作，可省略 -h 参数

• -p 后面可以不写密码，按Enter键后可输入密码。如果写密码，-p和密码间没有空格。

<!--创建数据库-->

CREATE DATABASE 数据库名;

<!--查看数据库列表-->

SHOW DATABASES;

<!--选择数据库-->

USE 数据库名;

<!--删除数据库-->

DROP DATABASE 数据库名;

<!--创建表-->

CREATE TABLE [ IF NOT EXISTS ] 表名（

字段1 数据类型 [ 字段属性 | 约束 ] [索引] [注释],

................

字段n 数据类型 [ 字段属性 | 约束 ] [索引] [注释]

）[表类型] [表字符集] [注释];

<!--单字段主键-->

CREATE TABLE [ IF NOT EXISTS ] 表名(

字段1 数据类型 PRIMARY KEY,

..........

)

<!--在定义完所有字段之后指定主键-->

CREATE TABLE [ IF NOT EXISTS ] 表名(

字段1 数据类型,

..........

[CONSTRAINT<约束名>] PRIMARY KEY [ 列名 ]

)

<!--多字段联合主键-->

CREATE TABLE [ IF NOT EXISTS ] 表名(

PRIMARY KEY [字段1,字段2.......]

)

<!--注释使用COMMENT关键字来添加，下面是举例-->

CREATE TABLE test（

'id' int(11) UNSIGNED COMMENT ' 编号 '

）COMMENT=' 测试表 '

<!--编码格式设置使用CHARSET关键字来添加，下面是举例（默认是utf-8）-->

CREATE TABLE test（

'id' int(11) UNSIGNED COMMENT ' 编号 '

）CHATSET= 字符集名

<!--查看表(必须要先选择数据库)-->

SHOW tables;

<!--查看表定义-->

DESCRIBE 表名; 或 DESC 表名;

<!--删除表-->

DROP TABLE [ IF EXISTS ] 表名;

<!--修改表-->

ALTER TABLE <旧表名> RENAME [TO] <新表名>

<!--添加字段-->

ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

<!--修改字段-->

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

<!--删除字段-->

ALTER TABLE 表名 DROP 字段名;

<!--查看所有修改表结构的SQL语句-->

HELP ALTER TABLE;

<!--添加主键约束-->

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名 (主键字段)

<!--添加外键约束-->

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名 (关联字段)

<!--查看帮助文档目录列表-->

HELP contents;

<!--查看所支持的数据类型-->

HELP Data Types;

<!--进一步查看某一数据类型-->

HELP INT;

<!--查询某命令，以下示例查询CREATE TABLE命令-->

HELP CREATE TABLE;

<!--查看默认存储引擎-->

SHOW VATIABLES LIKE 'storage_engine%';

<!--指定表的存储引擎使用ENGINE关键字-->

CREATE TABLE 表名(

#省略代码

)ENGINE=存储引擎;

<!--插入单行数据-->

INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

<!--插入多行数据-->

INSERT INTO 新表 (字段名列表) VALUES (值列表1),(值列表2),.......,(值列表n);

<!--将查询结果插入到新表中-->

CREATE TABLE 新表 （SELECT 字段1,字段2,.......FROM原表);

<!--更新表中某行数据-->

UPDATA 表名 SET 列名=更新值 [ WHERE 更新条件 ]

<!--删除表中的数据-->

DELETE [FROM] 表名 [WHERE <删除条件>]

<!--删除表中所有信息（不建议使用，删除的数据不能恢复）-->

TRUNCATE TABLE 表名

<!--查询数据(ASC是升序DESC是降序,默认升序，排序列名可以多个，用“，”隔开，可以分开指定)（ * 表示所有）-->

SELECT <列名 | 表达式 | 函数 | 常量>

FROM <表名>

[WHERE <查询条件表达式>]

[ORDER BY <排序的列名>[ASC 或 DESC]]

<!--查询总成绩-->

SELECT SUM (列名) FROM 表名;

<!--查询平均成绩-->

SELECT AVG (列名) FROM 表名;

<!--首字母大写-->

SELECT UPPER (列名) FROM 表名;

<!--出生年份统计-->

SELECT YEAR (列名) FROM 表名;

<!--分数取整-->

SELECT CEIL (列名) FROM 表名;

<!--查询指定位置和指定行数(ASC是升序DESC是降序,默认升序，排序列名可以多个，用“，”隔开，可以分开指定)（ * 表示所有）（位置偏移量指从结果集中第几条数据开始显示（第一条记录的位置偏移量是“0”，第二条是“1”。。。。。）此参数可选，当省略时默认从第一条记录开始）（行数指显示记录的条数）-->

SELECT <字段名列表>

FROM <表名或视图>

[WHERE <查询条件表达式>]

[GROUP BY <分组的字段名>]

[ORDER BY <排序的列名>[ASC 或 DESC]]

[ LIMIT [位置偏移量],行数 ]

<!--子查询在where语句中的一般用法（外层查询称为父查询，圆括号中嵌入的查询称为子查询，先执行子查询，求出子查询的值，在执行整个父查询，返回最后结果）-->

SELECT.....FROM 表名 WHERE 字段 比较运算符 (子查询)

<!--IN子查询（圆括号里的子查询可以嵌套多层）-->

SELECT.....FROM 表名 WHERE 条件字段 IN (子查询)

<!--NOT IN子查询（圆括号里的子查询可以嵌套多层）-->

SELECT.....FROM 表名 WHERE 条件字段 NOT IN (子查询)

<!--EXISTS子查询（确认后边的查询是否继续，返回值是true或false）（以下语句的意思是：如果存在数据表，则先删除它，在重新创建）-->

DROP 表名 IF EXISTS 数据表;

<!--EXISTS作为WHERE语句的子查询（以下语句的意思是：EXISTS 关键字后面的参数是一个任意的子查询，如果该子查询有返回行，则结果为true，此时再执行外层查询语句。反之，没有返回行，则结果为false，此时不再执行外层查询语句）-->

SELECT......FROM 表名 WHERE EXISTS (子查询)

<!--NOT EXISTS子查询（实现反操作，表示不存在对应查询条件的记录）-->

SELECT......FROM 表名 WHERE NOT EXISTS (子查询)

<!--设置自动提交关闭或开启事务（值为：0 关闭自动提交，值为：1 开启自动提交）-->

SET autocommit = 0 | 1;

<!--以下是默认设置下流程（默认下，每条SQL语句就是一个事务，即执行SQL语句后自动提交）-->

//选择数据库

USE mybank;

//设置结果集以GBK编码格式显示

SET NAMES gbk;

//开启事务，指定事务从此处开始，后续的SQL语句是一个整体

BEGIN;

//转账：张三的账户减少500元，李四的账户增加500元

UPDATE 'bank' SET 'currentMoney' = 'currentMoney' - 500 WHERE 'customerName' = '张三';

UPDATE 'bank' SET 'currentMoney' = 'currentMoney' + 500 WHERE 'customerName' = '李四';

//提交事务，写入硬盘，永久的保存

COMMIT;

<!--以下是设置手动自动提交关闭或开启事务流程-->

//关闭事务自动提交，该语句之后为事务开始

SET autocommit = 0 ;

//转账：张三的账户减少500元，李四的账户增加500元

UPDATE 'bank' SET 'currentMoney' = 'currentMoney' - 500 WHERE 'customerName' = '张三';

UPDATE 'bank' SET 'currentMoney' = 'currentMoney' + 500 WHERE 'customerName' = '李四';

//提交事务，写入硬盘，永久的保存

COMMIT;

UPDATE 'bank' SET 'currentMoney' = 'currentMoney' - 1000 WHERE 'customerName' = '张三';

//回滚事务

ROLLBACK;

//恢复自动提交

SET autocommit = 1 ;

<!--创建视图-->

CREATE VIEW 视图名 AS <SELECT 语句>;

<!--删除指定视图-->

DROP VIEW [ IF EXISTS ] 视图名;

<!--查看视图数据-->

SELECT 字段1,字段2,........FROM view_name;

<!--通过views来查看所有视图(“\G，表示将结果集按列显示”)-->

USE information_schema;

SELECT * FROM views\G;

<!--创建索引（使用CREATE INDEX语句在已经存在的表上添加索引）（UNIQUE|FULLTEXT| SPATIAL:分别表示唯一索引，全文索引，空间索引，都为可选参数）（index_name：指定索引名）（table_name：指定创建索引的表名）（column_name：指定需要创建索引的列）（length：指定索引长度，可选参数，只有字符串类型才能指定索引长度）-->

CRERTE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name ON table_name(column_name[length].......);

<!--删除索引-->

DROP INDEX table_name.index_name;

<!--查看索引（Table：表示创建索引的表）（Non_unique：表示索引是唯一，1 代表是非唯一索引，0 代表唯一索引）（Key_name：表示索引名称）（Seq_in_index：表示该列在索引中的位置，如果索引是单列的，该值为1，组合索引为每列在索引定义中的顺序）（Column_name：表示定义索引的列字段）（Sub_part：表示索引的长度）（Null：表示该列是否能为空值）（Index_type：表示索引类型））-->

SHOW INDEX FROM table_name;

<!--使用mysqldump命令备份数据库（mysqldump是DOS系统下的命令，无需进入mysql命令行，否则将无法执行）（username：表示用户名）（host：表示登录用户的主机名称，如本机为主机可省略）（password：表示登录密码）（dbname：表示需要备份的数据库）（tbname：表示需要备份的数据表，可指定多张表，为可选项，如备份整个数据库则此项省略）（filename.sql：表示备份文件的名称）-->

mysqldump -u username -h host -p password dbname[ tbname1[,tbname2......]] > filename.sql;

<!--使用mysqldump命令恢复数据库（username：表示用户名）（dbname：表示数据库名）（filename.sql：为执行数据库备份后生成的文件）-->

mysql -u username -p [ dbname ] < filename.sql;

<!--表数据导出到文本文件（filename：用来指定文本文件的路径和文件名）-->

SELECT [ field_name ] FROM tablename [ WHERE condition ] INTO OUTFILE 'filename' [ OPTION ];

<!--表数据导入到文本文件（filename：用来指定文本文件的路径和文件名）（tablename：用来指定导入表的名称）-->

LOAD DATA INFILE filename INTO TABLE tablename [OPTION];

<!--创建普通用户 （IDENTIFIED BY：用来设置密码，默认时密码为空）（username：表示创建的用户名）（host：表示指定用户登录的主机名，如果只是本地用户可使用“localhost”，如果该用户可登录任何远程主机，可使用通配符“%”）（PASSWORD：表示使用哈希值设置密码，为可选项）（password：表示用户登录时使用的密码）-->

CREATE USER 'username'@'host' [ IDENTIFIED BY [PASSWORD] 'password' ];

<!--执行GRANT语句创建用户并授权 （priv_type：表示设置的用户操作权限，如果授予所有权限可使用ALL。MySQL中的权限很多，以下列出了常用的数据库或表操作权限（CREATE和DROP权限：可以创建数据库和表，或删除已有数据库和表）（INSERT、DELETE、SELECT和UPDATE权限：允许在一个数据库现有的表上实施增，删，查，改操作）（ALTER权限：可以用ALTER TABLE 来更改表的结构和重新命名表））（databasename,tablename：表示所创建用户账号的权限范围，即只能在指定数据库和表上使用此权限，如果给所有数据库和表授权，则可以使用 *.*）（WITH GRANT OPTION：表示对新建立的用户赋予GRANT权限，可选）（其它部分与CREATE USER 语句一致）-->

GRANT priv_type ON databasename,tablename TO username @ host [IDENTIFIED BY [PASSWORD] 'password' ] [WITH GRANT OPTION]

<!--使用mysqladmin命令修改root账户密码 （使用修改密码的用户应为超级管理员，如：root用户）（newpassword：指新密码，使用""括起来）-->

mysqladmin -u username -p password "newpassword";

<!--使用SET命令修改用户密码 （ PASSWORD()函数：用于对密码加密）（newpassword：指新密码，使用""括起来）（如果是普通用户修改密码，可省略FOR子句）-->

SET PASSWORD [FOR username@ host] = PASSWORD ("newpassword");

<!--删除普通用户 （以下语句可以用于删除一个或多个MySQL账户。）（要使用必须拥有数据库的全局CREATE USER权限或DELETE权限，一般由数据库管理员（DBA）来执行此操作）-->

DROP USER username1@host [,username2@host.....];