MySQL常用操作语句

Posted on 2016/10/23 14:45:52
数据库
By 王佳亮

说明：文中[]以及括号内的内容为可选参数，根据实际需要来决定写还是不写。

MySQL数据库默认端口是 3306

基本操作

1、查看MySQL数据库服务器和数据库字符集 show variables like '%char%';

2、查看MySQL数据表的字符集 show table status from 要查询的库 like '%要查询的表%';

3、查看MySQL数据列的字符集 show full columns from 要查询的表;

4、查看当前安装的MySQL所支持的字符集 show charset;

5、创建数据库时，设定字符集为utf8 create database 数据库名 character set utf8;

6、查看MySQL数据库支持的存储引擎 show engines; 或 show variables like 'have%';

7、查看默认存储引擎 show variables like 'storage_engine';

8、数据库登录 mysql -h localhost -u root -p;

9、删除数据库 drop database 要删除的数据库名;

10、创建表 CREATE TABLE 表名(字段属性，字段属性，字段属性);

11、查看表结构 describe 表名;

12、查看表详细结构语句 SHOW CREATE TABLE 表名 \G;

13、修改表名 ALTER TABLE 旧表名 RENAME 新表名;

14、修改字段的数据类型 ALTER TABLE 表名 MODIFY 属性名数据类型;

15、修改字段名 ALTER TABLE 表名 CHANGE 旧属性名新属性名新数据类型;

16、增加字段 ALTER TABLE 表名 ADD 属性名1 数据类型 [ 完整性约束条件 [FIRST] AFTER 属性名2 ];

17、删除字段 ALTER TBALE 表名 DROP 属性名;

18、修改字段的排列位置 ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2;

19、更改表的存储引擎 ALTER TABLE 表名 ENGINE=存储引擎名;

20、删除表的外键约束 ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

21、删除没有被关联的普通表 DROP TABLE 表名;

22、删除被其他表关联的父表

(1)先删除有关联表的外键 ALTER TABLE 有关联的表名 DROP FOREIGN KEY 外键名;

（2）再删除要删除的表 DROP TABLE 表名;

23、创建表时，创建普通索引，在建表的时候，语句最后加入 INDEX(要建立索引的字段);

24、创建表时，创建唯一性索引，在建表的时候，语句最后加入 UNIQUE INDEX 索引名（索引字段 ASC）;

25、创建表时，创建全文索引，在建表的时候，语句最后加入 FULLTEXT INDEX 索引名 (要建立索引的字段);

26、创建表时，创建单列索引，在建表的时候，语句最后加入 INDEX 索引名(要建立索引的字段);

27、创建表时，创建多列索引，在建表的时候，语句最后加入 INDEX 索引名(要建立索引的字段1,要建立索引的字段2);

28、创建表时，创建空间索引，在建表的时候，语句最后加入 SPATIAL INDEX 索引名(要建立索引的字段);

29、在已经存在的表上创建索引 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC]);

（1）、在已经存在的表上创建唯一性索引 CREATE UNIQUE INDEX 索引名 ON 表名(要建立索引的字段);

（2）、在已经存在的表上创建全文索引 CREATE FULLTEXT INDEX 索引名 ON 表名(要建立索引的字段);

（3）、在已经存在的表上创建单列索引 CREATE INDEX 索引名 ON 表名(要建立索引的字段);

（4）、在已经存在的表上创建多列索引 CREATE INDEX 索引名 ON 表名(要建立索引的字段1,要建立索引的字段2);

（5）、在已经存在的表上创建空间索引 CREATE SPATIAL 索引名 ON 表名(要建立索引的字段);

30、在已经存在的表上修改索引 ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名 [(长度)] [ASC|DESC]);

（1）、普通索引 ALTER TABEL 表名 ADD INDEX 索引名(要建立索引的字段);

（2）、唯一性索引 ALTER TABEL 表名 ADD UNIQUE INDEX 索引名(要建立索引的字段);

（3）、全文索引 ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(要建立索引的字段);

（4）、单列索引 ALTER TABLE 表名 ADD INDEX 索引名(要建立索引的字段);

（5）、多列索引 ALTER TABLE 表名 ADD INDEX 索引名(要建立索引的字段1,要建立索引的字段2);

（6）、空间索引 ALTER TABLE 表名 ADD SPACIAL INDEX 索引名(要建立索引的字段);

31、删除索引 DROP INDEX 索引名 ON 表名;

32、创建视图 CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW AS SELECT 语句[WITH [CASCADED|LOCAL] CHECK OPTION];

备注：

UNDEFINED表示MYSQL将自动选择所要的使用的算法；

MERGE选项表示将使用视图的语句与视图定义合并起来；

TEMPTABLE表示将视图的结果存入临时表，用临时表执行语句。

CASCADED表示更新视图时要满足所有相关视图表和表的条件，

LOCAL表示更新视图时，要满足该视图本身的定义的条件即可。

33、在VIEW表中查看视图详细信息 SELECT * FROM INFORMATION_SCHEMA.VIEWS;

34、修改视图方法一

CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句[WITH [CASCADED|LOCAL] CHECK OPTION];

35、修改视图方法二

ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句[WITH [CASCADED|LOCAL] CHECK OPTION];

36、删除视图 DROP VIEW [IF EXISTS] 视图名列表 [RESTRICT|CASCADE];

初级操作

1、创建触发器 CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

2、创建有多个执行语句的触发器

DELIMITER &&

CREATE TRIGGER 触发器名 BEFOR|AFTER 触发事件 ON 表名 FRO EACH ROW

BEGIN

执行语句1;

执行语句2;

END

DELIMITER;

3、查看触发器信息 SHOW TRIGGERS \G

4、查询指定触发器信息 SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME ='触发器名';

5、删除触发器 DROP TRIGGER 触发器名;

6、查询 SELECT 字段列表 FROM 表名和视图列表 WHERE 条件表达式;

7、排序 SELECT 字段列表 FROM 表名和视图列表 WHERE 条件表达式 ORDER BY 要排序的字段 ASC|DESC;

8、分组查询 SELECT 字段列表 FROM 表名和视图列表 GROUP BY 要分组的字段;

9、分组查询显示分组的所有元素 SELECT 字段列表,GROUP_CONCAT(要全部显示的字段) FROM 表名和视图列表 GROUP BY 要分组的字段;

10、分组字段中元素的个数 SELECT 字段列表,COUNT(要全部显示的字段) FROM 表名和视图列表 GROUP BY 要分组的字段;

11、分组限制输出结果 SELECT 字段列表 FROM 表名和视图列表 GROUP BY 要分组的字段 HAVING 条件表达式;

12、分组记录分组个数总和 SELECT 字段列表 FROM 表名和视图列表 GROUP BY 要分组的字段 WITH ROLLUP;

13、限制查询结果数量 SELECT * FROM 表名和视图列表 LIMIT 数字;

14、限制查询结果数量，指定初始位置 SELECT * FROM 表名和视图列表 LIMIT 起始数字，显示的数量;

15、统计记录条数 SELECT COUNT(*) FROM 表名和视图列表;

16、统计某个字段数据的和 SELECT SUM(要统计的字段) FROM 表名和视图列表;

17、统计某个字段的平均值 SELECT AVG(要统计的字段) FROM 表名和视图列表;

18、某个字段的最大值 SELECT MAX(要统计的字段) FROM 表名和视图列表;

19、某个字段的最小值 SELECT MIN(要统计的字段) FROM 表名和视图列表;

20、内连接查询 SLECT 属性名列表表名1,表名2 WHERE 表名1.字段名1=表名2.字段名2;

21、外接接查询 SLECT 属性名列表表名1 LEFT|RIGHT|JOIN 表名2 ON 表名1.字段名1=表名2.字段名2;

22、子查询 SELECT COUNT(*) FROM 表名和视图列表 WHERE 字段 IN （子查询语句）;

23、合并查询 SELECT 语句1 UNION|UNION ALL SELECT 语句2;

24、为表起别名 SELECT 字段列表 FROM 表名和视图列表别名;

25、为字段起别名 SELECT 字段列表 AS 别名 FROM 表名和视图列表

26、使用正则表达式 SELECT 字段列表 FROM 表名和视图列表 WHERE 字段名 REGEXP '正则表达式';

27、插入数据 INSERT INTO 表名(属性1,属性2,...,属性n) VALUES(值1,值2,...,值n);

28、更新数据 UPDATE 表名属性名1=取值1,属性名2=取值2,...属性名n=取值n WHERE 条件表达式;

29、删除数据 DELETE FROM 表名 WHERE 条件表达式;

30、创建存储过程 CREATE PROCEDURE 存储过程名称 (参数列表) 存储过程特性 SQL代码;

例如：创建一个名为num_from_employee 的存储过程

CREAT PROCEDURE num_from_employee(IN emp_id int,OUT count_num INT)

READS SQL DATA

BEGIN

SELECT COUNT(*) INTO count_num

FROM employee

WHERE d_id=emp_id;

END

备注：

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL 表示子程序中不包含SQL语句；

READS SQL DATA 表示子程序中包含读数据的语句；

MODIFIES SQL DATA 表示子程序中包含写数据的语句。

31、创建存储函数 CREATE FUNCTION 函数名 (参数列表) 存储过程特性 RETURNS Type 运行的SQL代码;

例如：创建一个名为name_from_employee 的存储过程

CREAT FUNCTION name_from_employee(emp_id INT)

RETURNS VARCHAR(20)

BEGIN

RETURN (SELECT name

FROM employee

WHERE d_id=emp_id);

END

32、查看存储过程和函数的状态 SHOW PROCEDURE|FUNCTION STATUS LKE '存储过程名称' \G;

33、查看存储过程和函数的定义 SHOW CREATE PROCEDURE|FUNCTION 存储过程名称;

34、从INFORMATION_SCHEMA.ROUTINES 表中查看存储过程和函数的信息

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='存储过程名称';

35、修改存储过程和函数 ALTER PROCEDURE|FUNCTION 存储过程名称执行语句;

36、删除存储过程和函数 DROP PROCEDURE|FUNCTION 存储过程名称;

37、新建普通用户

方法一 CREATE USER 用户名 IDENTIFIED BY '密码';

方法二 GRANT priv_type ON database.table TO 用户名 IDENTIFIED BY '密码';

38、删除普通用户 DROP USER 用户名;

39、ROOT用户修改普通用户密码

方法一 SET PASSWORD FOR '用户名'@'主机名'=PASSWORD("新密码");

方法二 UPDATE mysql.user SET Password=PASSWORD("新密码") WHERE User="用户名" AND Host="主机名";

40、普通用户修改密码 SET PASSWORD=PASSWORD('新密码');

41、数据备份 mysqldump -u username -p dbname table1 table2 ...>Backupname.sql

42、备份多个数据库 mysqldump -u username -p --database dbname1 dbname2 ...>BackupName.sql

43、数据还原 mysql -u root -p [dbname]<backup.sql

44、查看MySQL数据库的性能 SHOW STATUS LIKE 'value';

45、分析查询，查看SQL语句执行情况 EXPLAIN SELECT 语句;

[参考文献] 黄缙华.MySQL入门很简单[M].北京：清华大学出版社,2011.

MySQL常用操作语句

微信公众号

抖音视频号

著作成果