mysql常用命令查询手册

2020-04-16  本文已影响0人  黑夜的眸

启动MySQL服务

$ sudo service mysql start
或者
/etc/init.d/mysql start

使用root用户登陆

$ mysql -u 用户名 -p 密码

查看命令帮助信息

mysql> HELP 命令名;

查看授予用户的安全权限

mysql> SHOW GRANTS;

注释

-- 单行注释
 /* 多行注释 */

取消正在输入的命令

\c

查看当前含有哪些数据库

mysql> SHOW DATABASES;

连接数据库

mysql> USE 数据库名

查看当前数据库中含有哪些表

mysql> SHOW TABLES;

显示表属性

mysql> DESCRIBE 表名;
或
mysql> SHOW COLUMNS FROM 表名;

后者将会输出指定字段的字段名、数据类型、非空约束、是否是主键和默认值等信息。

显示服务器警告或错误信息

mysql> SHOW ERRORS;
或
mysql> SHOW WARNINGS;

退出数据库

mysql> EXIT/QUIT;

新建数据库

mysql> CREATE DATABASE 数据库名;

通常SQL语句不区分大小写，但建议将关键字大写，变量和数据小写。

新建数据表

mysql> CREATE TABLE 数据表名
    -> (
    -> 列名1 数据类型(数据长度) PRIMARY KEY,        --主键
    -> 列名2 数据类型(数据长度) NOT NULL,        --非空约束
    -> 列名3 数据类型(数据长度) DEFAULT '默认值',        --默认值约束
    -> UNIQUE(列名a),        --唯一约束
    -> CONSTRAINT 主键名 PRIMARY KEY (列名a,列名b,...)，        --复合主键
    -> CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 表名(主键名)        --外键
    -> );

注意最后一个列名后面不加逗号”,”。

数据类型

数据类型 大小(字节) 说明 格式
INT 4 整数 -
FLOAT 4 单精度浮点数 -
DOUBLE 4 双精度浮点数 -
ENUM - 单选 ENUM(‘a’,’b’,’c’)
SET - 多选 SET(‘1’,’2’,’3’)
DATE 3 日期 YYYY-MM-DD
TIME 3 时间点或持续时间 HH:MM:SS
YEAR 1 年份值 YYYY
CHAR 0~255 定长字符串 -
VARCHAR 0~255 变长字符串 -
TEXT 0~65535 长文本数据 -

CHAR和VARCHAR的区别:
CHAR的长度是固定的，而VARCHAR的长度是可以变化的。
比如，存储字符串"abc"，对于CHAR (10)，表示存储的字符将占10个字节(包括7个空字符)，
而同样的VARCHAR(12)则只占用3个字节的长度，12只是最大值，
当你存储的字符小于12时，按实际长度存储。

ENUM和SET的区别:
ENUM类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而SET类型的值则可以多选。

插入数据

INSERT INTO 数据表名(列名1,列名2,列名3) VALUES(值1,值2,值3);

VALUES中的值为对应列属性的值,其中CHAR,VARCHAR,TEXT,DATE,TIME,ENUM等类型的数据需要单引号修饰。

SQL约束
主键

PRIMARYKEY KEY
或
CONSTRAINT 主键名 PRIMARY KEY (列名a,列名b,...)

一个表中可以有多个主键。

外键

CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 数据表名 (被参考的主键名)

每个表可以由多个外键，每个外键必须参考另一个表中的主键，被外键约束的列的取值必须在它参考的主键的列中有对应的值。

非空约束

NOT NULL

MySQL中违反非空约束只会警告不会报错。

默认值约束

DEFAULT '默认值'

字符类型默认值使用单引号括起。

唯一约束

UNIQUE(列名)

该表中该列值不可重复。

MySQL通配符

_   ### 只能匹配单个任意字符
%   ### 匹配0或多个任意字符

必须通过LIKE使用通配符。

通配符不能用于检索NULL。

使用通配符搜索相对于其他搜索方式通常要花费更长的时间。

将通配符放在搜索模式的开头处，搜索起来是最慢的，尽量避免这样做。

MySQL查询语句关键字顺序

mysql> SELECT ...
       FROM ...
       WHERE ...
       ORDER BY ...
       LIMIT ...;

SELECT操作
查看表中所有的内容

mysql> SELECT * FROM 数据表名;

SELECT基本格式

mysql> SELECT 列名a,列名b,...
       -> FROM 数据表名
       -> WHERE 限制条件1 AND/OR 限制条件2 ...
       -> 其他命令;
WHERE子句操作符
操作符 说明
= 等于
!= 不等于
<> 不等于
< 小于
> 大于
<= 小于等于
<= 大于等于
BETWEEN … AND 在两个值之间
AND 需要同时满足的条件
OR 满足其一即可的条件
IN 指定条件范围
NOT 否定其后的任何条件
IS NULL 空值检查
LIKE 使用通配符

AND的优先级大于OR，当同时使用AND和OR时，使用括号来避免因筛选顺序不同造成错误。

IN操作符的范围参数放在圆括号中，以逗号分隔。

使用IN通常比使用OR执行速度更快，而且语义更清楚，能够更动态的建立WHERE子句。

在进行匹配过滤和不匹配过滤时都不会对NULL值进行匹配。

NULL表示不含值，它不同于0、空字符串和空格。

使用单引号限定字符串。

匹配字符串时默认不区分大小写。

例如：

SELECT 列名a,列名b,... FROM 表名 WHERE 列名a>x1 AND/OR 列名a<x2 AND/OR 列名b='x3';
SELECT 列名a,列名b,... FROM 表名 WHERE 列名a IN/NOT IN ('范围列名a','范围列名b',...);
SELECT 列名a,列名b,... FROM 表名 WHERE 列名a LIKE 'xx_xx%';

对查询结果排序

mysql> SELECT 列名... FROM 表名 ORDER BY 首选排序列名 
DESC/ASC, 第二排序列名 DESC/ASC, ... DESC/ASC;

ORDER BY 默认升序排列，使用ASC指定升序排序，使用DESC指定降序排序。

DESC/ASC只能直接作用于直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

去除重复值

mysql> SELECT DISTINCT 列名... FROM 表名;

DISTINCT关键字作用于所有列而不是仅作用于前置它的列。

限制返回结果数

mysql> SELECT 列名... FROM 表名 LIMIT 检索起始行,最大输出行数;
或
mysql> SELECT 列名... FROM 表名 LIMIT

最大输出行数 OFFSET 检索起始行; # MySQL 5
若不指定起始行，则从第0行开始检索，注意MySQL中表行从0开始。
若语句中含有ORDER BY，则LIMIT应该位于ORDER BY之后。

例句：

SELECT 列名,... FROM 表名 ORDER BY 列名 ASC/DESC;

内置函数和计算

函数 功能 作用类型
COUNT() 计数 任意类型
SUM() 求和 数字类数据
AVG() 平均值 数字类数据
MAX() 最大值 数字类数据
MIN() 最小值 数字类数据
AS 重命名 不适用

例如：

SELECT COUNT/SUM/AVG/MAX/MIN(列名) AS 新的列名,函数2,函数3,... FROM 表名;

会将函数的结果作为新的列的值。
子查询

mysql> SELECT 所查列名,函数()
    -> FROM 表名 
    -> WHERE 所查列名 IN 
    -> (SELECT 相关列名 FROM 相关表名 WHERE 限制条件);

处理多个表且处理结果来自一个表时使用子查询，子查询可以扩展多层。

连接查询

mysql> SELECT 列名1,列名2,...
    -> FROM 表名1,表名2
    -> WHERE 表名1.列名1 = 表名2.列名2;
或
mysql> SELECT 列名1,列名2,...
    -> FROM 表名1 JOIN 表名2
    -> ON 表名1.列名1 = 表名2.列名2;

使用连接查询显示多个表中的数据。

删除数据库

mysql-> DROP DATABASE 数据库名;

重命名数据表

mysql-> RENAME TABLE 原数据表名 TO 新数据表名;
或

mysql-> ALTER TABLE 原数据表名 RENAME 新数据表名;
或

mysql-> ALTER TABLE 原数据表名 RENAME TO 新数据表名;

删除数据表

mysql-> DROP TABLE 数据表名;

在表中增加列

mysql-> ALTER TABLE 数据表名
     -> ADD COLUMN 新增列名 数据类型(数据长度) 约束
     -> 插入的位置;
或

mysql-> ALTER TABLE 数据表名
     -> ADD 新增列名 数据类型(数据长度) 约束
     -> 插入的位置;

插入的位置：
新增列默认放在表的最右边，使用 FIRST 将新列插入到第一列，使用 AFTER 指定列名 将新列插入到指定列后面。
删除列

mysql-> ALTER TABLE 表名
     -> DROP COLUMN 列名;
或

mysql-> ALTER TABLE 表名
     -> DROP 列名;

修改列

mysql-> ALTER TABLE 表名
     -> CHANGE 原列名 新列名 数据类型(数据长度) 约束;

数据类型不可省略。修改数据类型可能导致数据丢失，慎用！

修改数据类型

mysql-> ALTER TABLE 表名
     -> MODIFY 列名 新数据类型;

修改表中某个值

mysql-> UPDATE 表名
     -> SET 列名a=新值a,列名b=新值b,...
     -> WHERE 条件;

一定要加限制条件！

删除某行记录

mysql-> DELETE FROM 表名
     -> WHERE 条件;

一定要加限制条件！

为某列建立索引

mysql-> ALTER TABLE 表名
     -> ADD INDEX 索引名 (列名);

或

mysql-> CREATE INDEX 索引名
     -> ON 表名 (列名);

显示某表的索引

mysql-> SHOW INDEX FROM 表名;

创建视图（一种虚拟存在的表）

mysql-> CREATE VIEW 视图名(列名1,列名2,...)
     -> AS SELECT 目标列名a,目标列名b,...
     -> FROM 表名;

在SELECT中使用子查询或连接查询可以将视图建立在多张表上。

导入（将文件中的数据保存进表）

mysql-> LOAD DATA INFILE '文件路径'
     -> INTO TABLE 表名;

导出（将表中数据保存到文件中）

mysql-> SELECT 列名1,列名2,...
     -> INTO OUTFILE '文件路径'
     -> FROM 表名;

备份整个数据库

$ mysqldump -u root 数据库名 > 备份文件名

备份整个表

$ mysqldump -u root 数据库名 数据表名 > 备份文件名

恢复数据库

mysql-> source 文件路径/备份文件名;
# 注意：需要先使用USE命令选择数据库之后才能使用SOURCE命令
或

$ mysql -u root 新建的数据库名 < 备份文件名;
或者
$ mysqldump -u root -p 新建的数据库名 < 备份文件名;

复制整个数据表到一张新表中

create table <新表名> (
      select * from <数据表>
)

正则表达式

空白元字符

元字符 说明
\f 换页
\n 换行
\r 回车
\t 水平制表
\v 垂直制表

字符类（预定义字符集）

字符类 说明
[:alnum:] 任意字符和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和水平制表（同[\t]）
[:cntrl:] ASCII控制字符（ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包含空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数（同[a-fA-F0-9]）

匹配多个实例

重复元字符 说明
* 0个或多个匹配
+ 1个或多个匹配（同{1,}）
? 0个或1个匹配（同{0,1）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（m不超过255）

重复元字符对它前边的字符或表达式生效

定位元字符

定位元字符 说明
^ 只匹配文本的开始处
$ 只匹配文本的结尾处
[[:<:]] 只匹配词的开始处
[[:>:]] 只匹配词的结尾处

定位元字符对它后边的字符或表达式生效

拼接字段

别名（alias）

MySQL算数操作符

操作符 说明
+ 加
- 减
* 乘
/ 除

可以使用圆括号区分运算优先顺序

SELECT测试

SQL函数

常用的文本处理函数

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Rigit() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

Soundex是将文本串转换为描述其语音表示的字母数字模式的算法，使得能够对串进行发音比较而不是字母比较

例如搜索发音类似 Lie 的子串：

mysql> SELECT 列名
       FROM 表名
       WHERE Soundex(列名)=Soundex('Lie');

该搜索可以匹配到发音与 Lie 类似的 Lee 、 Li 等

日期和时间处理函数

函数 说明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分 v4.1.1
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期计算函数？
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 返回一个日期对应的星期
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Now() 返回当前日期时间
Second() 返回一个时间的秒部分
Time() 返回一个日期的时间部分 v4.1.1
Year() 返回一个日期的年份部分

MySQL日期格式使用 yyyy-mm-dd 格式

检索日期时应该使用Date()函数，直接比较可能检测不到结果

MySQL会将00-69处理为2000-2069，将70-99处理为1970-1999，为避免歧义，使用标准格式

数值处理函数

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

SQL聚集函数

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某行的平均值
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列之和

GROUP BY 和 ORDER BY

ORDER BY GROUP BY
排序产生的输出 分组行，但输出可能不是分组的数据
任意列都可以使用（甚至非选择的列） 只能使用选择的列或表达式，而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句，这是保证数据正确排序的唯一方法。

SELECT子句顺序

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 尽在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

原文链接：https://www.jianshu.com/p/73a50d4e89ab

上一篇下一篇

猜你喜欢

热点阅读