Hi,大家好，今天我给大家带来的是“MariaDB(MySQL)常用语句的使用”
说明
1、MySQL语句不区分字符大小写，在Unix或类Unix(Linux)系统下“参数”区分字符大小写
2、多数MySQL语句以";"结尾；如果结尾处不写";"MySQL会等待输入下一条语句，直到结尾有";"为止
3、一行需要执行多条语句用";"进行分隔
本篇内容以Linux系统为例；本文中的[ 符号里边的内容为可选内容，既可有可无的 ]、{ 符号里的内容为必须给出的内容 }、| 符号为或者之意；语句之前的符号# 为Linux系统的命令提示符（需在系统下执行的）、符号> 为MySQL数据库提示符（需在MySQL数据库中执行的）；字体颜色为红色的是需要注意的地方（可能会出现误操作的地方）
温馨提示：使用电脑阅读效果更佳
MariaDB(MySQL)语句分类
服务端命令：通过mysql连接发往服务器执行并取回结果（SQL语句）
DDL(Data Defined Language)：要用于管理数据库组件，例如表、索引、视图、用户、存储过程
CREATE DATABASE, ALTER DATABASE, DROP DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, CREATE USER, DROP USER, SELECT
DML(Data Manipulation Language)：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查
INSERT, DELETE, UPDATE, SELECT
DCL：权限管理
GRANT, REVOKE
客户端命令：本地执行
以下语句有两种表示方式(clear,\c)，语句结尾处不用加; 但加了也不会出错 | |
语句 | 说明 |
? (\?) | 获取命令帮助 |
clear (\c) | 清除当前的输入语句 |
connect (\r) | 重新连接MySQL |
delimiter (\d) | 设置语句分隔符，默认为分号; |
ego (\G) | 语句结束标记，结果以垂直方式显示 |
exit (\q) | 退出MySQL |
go (\g) | 语句结束标记 |
help (\h) | 显示此帮助 |
prompt (\R) | 更改MySQL提示符 |
quit (\q) | 退出MySQL |
source (\.) | 装载并运行SQL脚本，脚本存放的位置"MySQL"用户需要有权限读取 |
status (\s) | 获取MySQL状态信息 |
system (\!) | 执行shell命令 |
use (\u) | 设定哪个库为默认数据库 |
一、登录/退出MySQL
登录
登录本地数据库
匿名登录
# mysql [数据库名称]
# mysql -p [数据库名称]
指定用户登录
# mysql -u 用户名 -p [数据库名称]
# mysql -u 用户名 –p用户的密码 [数据库名称]
远程登录数据库
# mysql -h 主机 -u 用户名 -p用户的密码 [数据库名称]
注意：在登录时可指定数据库"[DATABASE_NAME]"
退出
> exit
> quit
> \q
Ctrl+C
二、选择/切换数据库：在数据库中执行的操作都要先选择数据库
> USE 数据库名称;
三、数据库管理
创建
创建一个新的数据库
> CREATE {DATABASE | SCHEMA} 数据库名称;
创建一个新的数据库，如果数据库不存在，则创建
> CREATE DATABASE IF NOT EXISTS 数据库名称;
注意：数据库名称在创建时定义，后期不可更改
修改
修改指定数据库的默认字符集
> ALTER DATABASE 数据库名称 CHARACTER SET '字符集名称';
查询
查看当前设备上已有的数据库
> SHOW DATABASES;
查看以指定字符开头的数据库
> SHOW DATABASES LIKE 'name%';
查看以指定字符结尾的数据库
> SHOW DATABASES LIKE '%name';
查看数据库所支持的引擎
> SHOW ENGINES;
查看所支持的字符集
> SHOW CHARACTER SET;
常见字符集
big5：繁体中文
gb2312：简体中文
utf8：统一的字符编码标准
查看字符集的排序方式
> SHOW COLLATION;
查看当前数据库的版本号和当前日期
> SELECT VERSION(), CURRENT_DATE;
删除
删除数据库：删库需小心（MySQL没有回收站）
> DROP {DATABASE | SCHEMA} 数据库名称;
> DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名称;
建议把不用的数据库移动到其他位置，而不是删除
# mv /var/lib/mysql/DATABASE_NAME /PATH/TO/DIRECTORY_NAME
四、数据表管理
创建
在数据库内创建数据表
> USE 数据库名称;
> CREATE TABLE 数据表名称 (字段1 数据类型 修饰符,字段2 数据类型 修饰符,字段3 数据类型 修饰符 ...) [table_option];
MySQL数据类型参考：点我查看
在创建数据表时还可以定义以下内容：
定义键
主键：PRIMARY KEY (字段1, 字段2, ...)
唯一键：UNIQUE KEY (字段1, 字段2,...)
外键：FOREIGN KEY (column)
定义索引
KEY|INDEX [索引名称] (字段1, 字段2,...)
> CREATE TABLE server ( IDC_ROOM VARCHAR(3) NOT NULL,RACK_SLOT VARCHAR(10)NOT NULL ,SN VARCHAR(30) PRIMARY KEY,HOSTNAME VARCHAR(16) NOT NULL,MACHINE_TYPE VARCHAR(10) NOT NULL ) ;
table_option:
| ENGINE [=] engine_name： 【定义存储引擎】
| AUTO_INCREMENT [=] value： 【设置默认自动增长数据从数字几开始算，默认为1】
| [DEFAULT] CHARACTER SET [=] charset_name： 【定义字符集，如不定义则从数据库继承】
| [DEFAULT] COLLATE [=] collation_name： 【定义排序规则】
| COMMENT [=] 'string'： 【定义数据表的注释信息】
| MAX_ROWS [=] value 【定义最大行数】
| MIN_ROWS [=] value 【定义最小行数】
| PASSWORD [=] 'string' 【加密】
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 【定义行格式】
DEFAULT：默认格式
DYNAMIC：动态格式
FIXED：固定长度
COMPRESSED：压缩存储
REDUNDANT：冗余存储
COMPACT：紧致存储
键类型可以在字段中指明
> CREATE TABLE 数据表名称 (ID TINYINT NOT NULL AUTO_INCREMENT UNIQUE KEY,NAME VARCHAR(20) NOT NULL UNIQUE KEY);
也可以单独指明：把多个字段联合起来定义（多字段索引）
> CREATE TABLE 数据表名称 (ID TINYINT NOT NULL AUTO_INCREMENT ,NAME VARCHAR(20) NOT NULL,UNIQUE KEY(ID,NAME));
注意：创建数据表的时候，表名不可使用"table"作为其名称
在指定数据库中创建数据表
> CREATE TABLE 数据库名称.数据表名称 (字段1 数据类型 修饰符,字段2 数据类型 修饰符,字段3 数据类型 修饰符 ...) [table_option];
插入
向数据表中插入数据
一次插入一行
> INSERT INTO [数据库名称.]数据表名称(字段1,字段2,字段3 ...)
一次插入多行
> INSERT INTO [数据库名称.]数据表名称(字段1,字段2,字段3 ...) {VALUES | VALUE} (DATE1: '字段1的值','字段2的值',字段3的值' ...),(DATE2: '字段1的值','字段2的值','字段3的值' ...) ...;
注意
(1) 使用INSERT语句，可以一次插入一行，也可以一次插入多行"(line1_data),(line2_data)..."
(2) 如果想用INSERT语句插入空值时，可以直接用NULL
替换表中已有数据：如果没有数据，则插入；如果已有数据，则替换（支持单行插入、多行插入、给定字段插入或全字段插入）
> REPLACE [INTO] 数据表名称(字段1,字段2,字段3 ...) VALUES ('字段1的值','字段2的值','字段3的值','字段4的值','字段5的值','字段6的值')[,(line2_data)...];
从文件中读取数据并插入到表中：一个空表，填充它的一个简易方法是创建一个文本文件，每条信息各一行，然后用一个语句将文件的内容装载到表中
> LOAD DATA LOCAL INFILE '/PATH/TO/ FILE_NAME' INTO TABLE [数据库名称.]数据表名称;
注意：如果需要填充空值时，使用\N（反斜线+字母N）表示
我演示的是从文件中导入到数据库，表中的敏感数据我已修改
从EXCEL表格中插入数据：点我查看
修改
修改数据表的名称
> RENAME TABLE [数据库名称.]数据表名称 TO 数据库名称.新表名称 ;
> ALTER TABLE [数据库名称.]数据表名称 RENAME TO 数据库名称.新表名称;
修改注释信息
> ALTER TABLE [数据库名称.]数据表名称 COMMENT = '注释信息';
给表中添加一个新字段
> ALTER TABLE 数据表名称 ADD 新字段名 数据类型 修饰符 [FIRST | AFTER 字段名];
指明字段位置
FIRST：添加到第一字段
AFTER：添加到哪个字段后面
如果不指定字段位置，则放在最后
修改字段名称(CHANGE)
> ALTER TABLE 数据表名称 CHANGE 原名称 新名称 原有属性信息（数据类型、修饰符...）;
修改/更新指定字段
> UPDATE [LOW_PRIORITY] [IGNORE] [数据库名称.]数据表名称 SET 字段1=字段1的值[,字段2=字段2的值] ... WHERE where_condition [ORDER BY ...] [LIMIT row_count]
注意：用UPDATE修改行时必须使用WHERE子句指明条件（尽量指主键），否则将修改所有
查询
查询当前数据库中的数据表
> USE 数据库名称;
> SHOW TABLES;
查询指定库中的数据表
> SHOW TABLES IN 数据库名称;
> SHOW TABLES FROM 数据库名称;
查看数据表的详细信息
> DESCRIBE [数据库名称.]数据表名称;
> DESC [数据库名称.]数据表名称;
查看数据表的状态信息
> SHOW TABLE STATUS [LIKE '数据表名称'];
查看数据表的状态信息（垂直显示）
> SHOW TABLE STATUS [LIKE '数据表名称']\G;
输出内容说明
查看创建命令
> SHOW CREATE TABLE [数据库名称.]数据表名称;
显示以指定字符开头的表的状态
> SHOW TABLE STATUS WHERE Name LIKE '数据表名称%'\G;
显示指定时间创建的表
> SHOW TABLE STATUS WHERE Create_time LIKE '时间%'\G;
SELECT语句：用来从数据表中检索信息
> SELECT SELECT_EXPR FROM [数据库名称.]数据表名称1,[数据表名称2 ...] [WHERE where_condition];
SELECT语句的子句的执行过程
SELECT_EXPR
*：所有字段（所有行，所有列）
ALL
字段1,字段2,字段3：投影；只显示指定字段
DISTINCT：相同的数据，只显示一次
field AS field_alias：给field取个别名field_alias
只查看表中不同的数据
> SELECT DISTINCT FROM [数据库名称.] 数据表1,[数据表2 ...];
查看指定表中所有字段（慎用）
> SELECT * FROM [数据库名称.]数据表名称1,[数据表2 ...];
查看指定表中的指定列（如果表太大则不适合执行）
> SELECT 字段1,字段2,字段3 ... FROM [数据库名称.]数据表名称;
定义显示的字段别名
> SELECT 字段 AS 字段别名 FROM [数据库名称.]数据表名称;
FROM子句：指定要查询的关系
单表查询（简单查询）：后面跟一个表名
多表查询：后面跟多个表名
子查询：后面跟WHERE子句
WHERE子句：选择、指明条件；只输出符合条件的行（重要）
> SELECT 字段1,字段2,字段3 ... FROM [数据库名称.]数据表名称 WHERE where_condition;
where_condition：
操作符
BETWEEN x AND y | 在x和y范围之间的值 |
IS NULL | 空值 |
IS NOT NULL | 非空值 |
IN (LIST) | 使用列表给出的值；用于离散取值 |
算数运算
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
% | 取模 |
比较表达式
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
= | 等于 |
!= 或 <> | 不等于 |
<=> | 空值比较 |
组合条件（逻辑运算）
AND (&&) | 与；并且关系，既两个条件同时满足 |
OR (||) | 或；或者关系，既两个条件满足一个 |
NOT (!) | 非；条件不满足即为真 |
注意：AND和OR可以混用，但AND比OR具有更高的优先级。如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意( NOT xxx AND NOT xxx AND NOT xxx 等于NOT ( xxx OR xxx OR xxx ) )
字符串比较
通配符
LIKE '模式'：支持使用“通配符”做模糊匹配
%：任意长度的任意字符
_：任意单个字符
正则表达式
{RLIKE | REGEXP} '模式'：基基于正则表达式做模式匹配；不用索引、效率低
正则表达式元字符：点我查看
排序(ORDER BY)：将查询到的结果排序显示；如果表太大，排序操作极其消耗资源
> ORDER BY 字段名称 {ASC | DESC}
升序：默认(ASC)
> SELECT 字段1,字段2,字段3 ... FROM [数据库名称.]数据表名称 ORDER BY 字段名称 ASC;
降序(DESC)
> SELECT 字段1,字段2,字段3 ... FROM [数据库名称.]数据表名称 ORDER BY 字段名称 DESC;
注意：默认是区分字符大小写的，如不想区分字符大小写则执行“ORDER BY BINARY 字段名称”
LIMIT子句
LIMIT [offset,]count
LIMIT #：只显示前#行的内容
LIMIT x,y：忽略x行，显示y行后的行
x：偏移量
y：偏移后取几个值予以显示
注意：在MySQL中字符串必须加引号，而数值一定不能加引号
聚合计算
> SELECT 聚合计算函数 FROM [数据库名称.]数据表名称;
聚合计算函数
AVG(字段名称)：求平均值
MAX(字段名称)：求最大值
MIN(字段名称)：求最小值
SUM(字段名称)：求合
COUNT(字段名称)：求个数之和
分组(GROUP BY)：分组主要目的是为了做聚合计算
> SELECT * FROM [数据库名称.]数据表名称 GROUP BY '字段名';
对分组进行过滤
> HAVING conditions;
复制
复制表结构
> CREATE TABLE 数据表名称 LIKE 原数据库.原数据表;
复制表中的几个字段并将其创建成一个新表（不会复制其属性）
> CREATE TABLE 数据表名称 SELECT 字段1,字段2,字段3 ... FROM 原数据库.原数据表;
删除
删除表：删表需小心（MySQL没有回收站）
> DROP TABLE [IF EXISTS] [数据库名称.]数据表名称;
删除表中的行(DELETE)
> DELETE FROM [数据库名称.]数据表名称 [WHERE where_condition];
> DELETE FROM 数据表名称 [ORDER BY ...] [LIMIT row_count]
注意：用DELETE删除行时必须使用WHERE子句，否则将清空整张表(DELETE FROM table_name)
先排序后删除
> DELETE FROM [数据库名称.]数据表名称 [WHERE where_condition] [ORDER BY '字段名' [DESC]]
删除字段（列）
> ALTER TABLE 数据表名称 DROP 字段名称;
清空整张数据表
> TRUNCATE [数据库名称.]数据表名称;
五、索引管理
创建
创建索引
> CREATE {INDEX | INDEXES} 索引名称 ON [数据库名称.]数据表名称(字段1,字段2,字段3 ...);
创建多列索引
> CREATE UNIQUE {INDEX | INDEXES} 索引名称 ON [数据库名称.]数据表名称;
给表中指定的字段加入索引
> ALTER TABLE [数据库名称.]数据表名称 ADD INDEX(‘字段名称’);
查询
查看表中索引
> SHOW {INDEX | INDEXES} FROM [数据库名称.]数据表名称;
、 删除：索引不用时，要立即删除
删除索引：这可能会导致查询操作降低
> DROP {INDEX | INDEXES} 索引名称 ON [数据库名称.]数据表名称;
注意：INDEX为一个索引，INDEXES为多个索引
六、键管理
创建
(1) 在数据表创建的时候定义主键
(2) 给已有的表中指定的字段加入主键
ALTER TABLE [数据库名称.]数据表名称 ADD {PRIMARY KEY | UNIQUE KEY}(‘字段名称’);
修改
先删除键，再添加键
查询
查询主键
> SHOW KEYS FROM [数据库名称.]数据表名称 WHERE key_name='PRIMARY';
删除
删除键：不是自增长的才可以删除，如果是自增长（有auto_increment属性）必须先将表中其他属性删除
> ALTER TABLE [数据库名称.]数据表名称 DROP {PRIMARY KEY | UNIQUE KEY};
七、用户管理
MySQL用户和操作系统中的用户不同；不能用MySQL用户登录操作系统，同样也不能用操作系统用户访问MySQL
用户是公开的，大家都可以看到；而密码只有某个授权使用用户才能获取
MySQL使用自己独有的加密机制password()函数加密用户密码
MySQL默认管理员是root，和操作系统管理员名称一致
MySQL用户由'用户名'@'主机'共同组成
MySQL用户名：不能超过16个字符
主机
主机名：zhangdaifu
IP地址：10.0.0.1
网络地址：10.0.0.1/255.0.0.0
支持使用通配符表示：10.%.%._/255.0.0.0
如需表示通配符本身的含义则需要进行转义\
创建
创建用户帐号
> CREATE USER '用户名'@'主机';
创建用户账号并设置其密码
> CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
修改
修改用户名
> UPDATE mysql.user SET USER='新用户名称' WHERE User='用户名';
查询：MySQL用户在mysql.user表中保存
查看所有用户信息
> SELECT User,Host[, ...] FROM mysql.user;
查看指定用户信息
> SELECT User,Host[, ...] FROM mysql.user WHERE User='用户名';
删除
删除用户帐号
> DROP USER '用户名'[@'主机' [, 用户名@主机] ...] ;
八、密码管理
创建
给用户配置密码
为当前登录用户配置密码
> SET PASSWORD = PASSWORD("用户的密码");
修改
通过命令行修改
# mysqladmin -u'用户名' -p'老密码' password '新密码'
使用UPDATE直接编辑user表
> UPDATE mysql.user SET PASSWORD=用户的密码 WHERE User='用户名';
刷新权限：flush privileges;
通过SET PASSWORD语句修改
> SET PASSWORD FOR 用户名=PASSWORD('用户的密码');
参考：https://blog.csdn.net/qq_33285112/article/details/78982766
查询
> SELECT User,Password FROM mysql.user WHERE User='用户名';
删除
清除指定用户密码
> UPDATE mysql.user SET PASSWORD='' WHERE User='用户名'
九、权限管理
MySQL使用帐号的目的是为了“认证”
MySQL用户信息存储在以下表中
user | 存储了用户的账号、全局权限及其他非权限字段 |
db | 数据库级别的权限、库级别的权限 |
host | 已被废弃，已被整合进user表 |
tables_priv | 表级别的权限 |
columns_priv | 列（字段）级别的权限 |
procs_priv | 存储过程和存储函数相关的权限 |
proxies_priv | 代理用户权限 |
MySQL服务器在启动时会读取这6张表，并在内存中生成“授权表”
授权
权限级别：管理权限、数据库、数据表、字段、存储例程
> GRANT permissions_type,... ON [object_type] { [数据库名称.]数据表名称 | function_name | procedure_name } TO 'user_name'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
> flush privileges
权限类型
ALL：所有权限；不安全
database_name.table_name
*.*：所有库的所有表
数据库.*：指定库的所有表
数据库.数据表：指定库的指定表
数据库.routine_name：指定库上的存储过程或存储函数
[object_type]
TABLE：给数据表授权；默认
FUNCTION：给存储函数授权
PROCEDURE：给存储过程授权
[WITH GRANT OPTION]：将自己所拥有的权限转权一个副本给其他用户
查询
查看当前用户所拥有的权限
> SHOW GRANTS;
查看指定用户所拥有的权限
> SHOW GRANTS FOR '用户名'@'权限类型';
删除
回收授权
> REVOKE 权限类型[, ...] ON [数据库名称.]数据表名称 FROM '用户名'@'主机';
注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中
(1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效
(2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可
检查上一条语句是否正确执行（类似于Shell变量"$?"）
> SHOW WARNINGS;
常见错误
(1) ERROR 1046 (3D000): No database selected
未选择数据库
(2) ERROR 1050 (42S01): Table 'table1' already exists
表'table1'已存在
(3) ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
登录数据库没有输入密码
(4) ERROR 1317 (70100): Query execution was interrupted
查询执行被中断
更多参考: http://www.bluestep.cc/demos/mysql/manual/index.htm
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
