MySQL 备忘单为您提供一页包含最常用的 MySQL 命令和语句,帮助您更有效地使用 MySQL。
MySQL 命令行客户端命令
使用 mysql 命令行客户端并输入用户名和密码连接到 MySQL 服务器(MySQL 将提示输入密码):
mysql -u [username] -p;
Code language: SQL (Structured Query Language) (sql)
使用用户名和密码连接到指定数据库的 MySQL 服务器:
mysql -u [username] -p [database];
Code language: SQL (Structured Query Language) (sql)
退出mysql命令行客户端:
exit;
Code language: SQL (Structured Query Language) (sql)
mysqldump -u [username] -p [database] > data_backup.sql;
Code language: SQL (Structured Query Language) (sql)
要清除 Linux 上的 MySQL 屏幕控制台窗口,请使用以下命令:
mysql> system clear;
Code language: SQL (Structured Query Language) (sql)
目前,Windows 操作系统上没有可用于清除 MySQL 屏幕控制台窗口的命令。
使用数据库
如果数据库服务器中不存在,则创建指定名称的数据库
CREATE DATABASE [IF NOT EXISTS] database_name;
Code language: SQL (Structured Query Language) (sql)
使用数据库或将当前数据库更改为您正在使用的另一个数据库:
USE database_name;
Code language: SQL (Structured Query Language) (sql)
永久删除具有指定名称的数据库。所有与数据库关联的物理文件都将被删除。
DROP DATABASE [IF EXISTS] database_name;
Code language: SQL (Structured Query Language) (sql)
显示当前MySQL数据库服务器中所有可用的数据库
SHOW DATABASE;
Code language: SQL (Structured Query Language) (sql)
使用表格
显示当前数据库中的所有表。
SHOW TABLES;
Code language: SQL (Structured Query Language) (sql)
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
);
Code language: SQL (Structured Query Language) (sql)
将新列添加到表中:
ALTER TABLE table
ADD [COLUMN] column_name;
Code language: SQL (Structured Query Language) (sql)
从表中删除一列:
ALTER TABLE table_name
DROP [COLUMN] column_name;
Code language: SQL (Structured Query Language) (sql)
将具有特定名称的索引添加到表的列上:
ALTER TABLE table
ADD INDEX [name](column, ...);
Code language: SQL (Structured Query Language) (sql)
将主键添加到表中:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name,...);
删除表的主键:
ALTER TABLE table_name
DROP PRIMARY KEY;
DROP TABLE [IF EXISTS] table_name;
Code language: SQL (Structured Query Language) (sql)
DESCRIBE table_name;
Code language: SQL (Structured Query Language) (sql)
显示表中某列的信息:
DESCRIBE table_name column_name;
Code language: SQL (Structured Query Language) (sql)
使用索引
在表上创建指定名称的索引:
CREATE INDEX index_name
ON table_name (column,...);
Code language: SQL (Structured Query Language) (sql)
删除索引:
DROP INDEX index_name;
Code language: SQL (Structured Query Language) (sql)
CREATE UNIQUE INDEX index_name
ON table_name (column,...);
Code language: SQL (Structured Query Language) (sql)
使用视图
创建一个新视图:
CREATE VIEW [IF NOT EXISTS] view_name
AS
select_statement;
Code language: SQL (Structured Query Language) (sql)
使用WITH CHECK OPTION
创建一个新视图:
CREATE VIEW [IF NOT EXISTS] view_name
AS select_statement
WITH CHECK OPTION;
Code language: SQL (Structured Query Language) (sql)
创建或替换视图:
CREATE OR REPLACE view_name
AS
select_statement;
Code language: SQL (Structured Query Language) (sql)
删除视图:
DROP VIEW [IF EXISTS] view_name;
Code language: SQL (Structured Query Language) (sql)
删除多个视图:
DROP VIEW [IF EXISTS] view1, view2, ...;
Code language: SQL (Structured Query Language) (sql)
重命名视图:
RENAME TABLE view_name
TO new_view_name;
Code language: SQL (Structured Query Language) (sql)
显示数据库中的视图:
SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';
Code language: SQL (Structured Query Language) (sql)
使用触发器
创建一个新的触发器:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;
Code language: SQL (Structured Query Language) (sql)
放下触发器:
DROP TRIGGER [IF EXISTS] trigger_name;
Code language: SQL (Structured Query Language) (sql)
显示数据库中的触发器:
SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];
Code language: SQL (Structured Query Language) (sql)
使用存储过程
创建存储过程:
DELIMITER $$
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
body;
END $$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
删除存储过程:
DROP PROCEDURE [IF EXISTS] procedure_name;
Code language: SQL (Structured Query Language) (sql)
显示存储过程:
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE search_condition];
Code language: SQL (Structured Query Language) (sql)
使用存储的函数
创建一个新的存储函数:
DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
-- statements
END $$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
删除存储的函数:
DROP FUNCTION [IF EXISTS] function_name;
Code language: SQL (Structured Query Language) (sql)
显示存储的函数:
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE search_condition];
Code language: SQL (Structured Query Language) (sql)
从表中查询数据
SELECT * FROM table_name;
Code language: SQL (Structured Query Language) (sql)
从表的一列或多列查询数据:
SELECT
column1, column2, ...
FROM
table_name;
Code language: SQL (Structured Query Language) (sql)
从查询结果中删除重复行:
SELECT
DISTINCT (column)
FROM
table_name;
Code language: SQL (Structured Query Language) (sql)
使用WHERE
子句通过过滤器查询数据:
SELECT select_list
FROM table_name
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
使用列别名更改列名称的输出:
SELECT
column1 AS alias_name,
expression AS alias,
...
FROM
table_name;
Code language: SQL (Structured Query Language) (sql)
使用内连接从多个表中查询数据:
SELECT select_list
FROM table1
INNER JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)
使用左连接从多个表中查询数据:
SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)
使用右连接从多个表中查询数据:
SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)
计算行的笛卡尔积:
SELECT select_list
FROM table1
CROSS JOIN table2;
Code language: SQL (Structured Query Language) (sql)
SELECT COUNT(*)
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
对结果集进行排序:
SELECT
select_list
FROM
table_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];
Code language: SQL (Structured Query Language) (sql)
使用GROUP BY
子句对行进行分组。
SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;
Code language: SQL (Structured Query Language) (sql)
使用HAVING
子句过滤组:
SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;
Code language: SQL (Structured Query Language) (sql)
修改表中的数据
将新行插入表中:
INSERT INTO table_name(column_list)
VALUES(value_list);
Code language: SQL (Structured Query Language) (sql)
将多行插入表中:
INSERT INTO table_name(column_list)
VALUES(value_list1),
(value_list2),
(value_list3),
...;
Code language: SQL (Structured Query Language) (sql)
更新表中的所有行:
UPDATE table_name
SET column1 = value1,
...;
Code language: SQL (Structured Query Language) (sql)
更新WHERE
子句中的条件指定的一组行的数据。
UPDATE table_name
SET column_1 = value_1,
...
WHERE condition
Code language: SQL (Structured Query Language) (sql)
UPDATE
table1,
table2
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
DELETE FROM table_name;
Code language: SQL (Structured Query Language) (sql)
删除由条件指定的行:
DELETE FROM table_name
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
DELETE table1, table2
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
搜寻中
使用LIKE
运算符搜索数据:
SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';
Code language: SQL (Structured Query Language) (sql)
使用带有RLIKE
运算符的正则表达式进行文本搜索。
SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';
Code language: SQL (Structured Query Language) (sql)