Linux服务 MariaDB SQL语句基础
MariaDB SQL语句基础
数据库：保存由组织的数据的容器，通常是一个文件或一组文件；
人们通常用数据库这个术语来代表他们所使用的数据库软件，这是不正确的；确切地说，数据库软件应该成为DBMS(数据库管理系统)；数据库是通过DBMS来创建和被操纵的容器，你实际使用的是DBMS，它替你访问数据库；
SQL；
数据库组件：数据库，表，索引，视图，用户，权限等；
表：某种特定类型数据的结构化清单；表是一种结构化的文件，可用来存储某种特定类型的数据，例如：表可以保存顾客清单、产品目录，或者其他信息清单；
列(column)：表是由列组成的，列存储着表中的某部分信息；
数据类型(datatype)：所容许的数据的类型，每个表列都用相应的数据类型，它限制该列中存储的数据；
行(row)：表中的一个记录；例如：顾客表中每一行存储一个顾客的信息；
主键(primary key)：一列或一组列，其值能唯一区分表中的每一行；
表中的任何列都可以作为主键，只要它满足以下条件：
任意两行都不具有相同的主键值；
每行都必须具有一个主键值(主键列不许由NULL值)；
主键值通常定义在表的一列上，也可以一起使用多个列最为主键；在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的(单个列的值可以不唯一)；
主键的建议使用规则：
不更新主键列中的值；
不重用主键列的值；
不在主键列中使用可能会更改的值；例如：使用一个名字作为主键以标识一个用户，当该用户更名时就必须要修改这个主键；
存储过程、存储函数、触发器、事件调度器
创建数据库、设计数据表
字段：字段名、数据类型、数量居类型的修饰符、约束；
数据类型包括字符和数值；
字符：
变长字符：VARCHAR(#),VARBINARY(#);按需分配，用多少分配多少；
定长字符：CHAR(#),BINARY(3);只要设置了值，不管用不用都全部被占用；
内建类型：
对象：TEXT(类似于CHAR不区分大小写)，BLOB(二进制存储的，区分大小写);都是属于变长的；
数值：
精确数值
整形：int
十进制：decimal
近似数值
单精度：float
双精度：double
日期时间型：
日期：DATE
时间：TIME
日期时间：DATETIME
时间戳：TIMESTAMP
年份：YEAR(2),YEAR(4)
修饰符：
所有类型适用：
NOT NULL
DEFAULT
PRIMARY KEY
UNIQUE KEY
数值型适用：
UNSIGNED
AUTO_INCREMENT
DDL、DML、DCL
DDL：CREATE(创建)、DROP(删除)、ALTER(修改)等；
DML：INSERT(插入)(REPLACE(替换))、DELETE(删除)、UPDATE(更新)、SELECT(查询)等；
DCL：GRANT(授权)，REVOKE(撤权)等
数据库相关：DDL
CREATE DATABASE [IF NOT EXISTS] ‘db_name’;
CHARACTER SET [=] charset
COLLATE [=] collate
查看数据库支持的字符集：SHOW CHARACTER SET;
查看数据库支持的排序规则：SHOW COLLATE;
DROP DATABASE [IF EXISTS] ‘db_name’;
表相关：
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1, col2 type2, …);
clo type1
PRIMARY KEY(col1 ,…)
INDEX(col1,…)
UNIQUE KEY(col1,...)
例子：
CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR (20) NOT NULL, age tinyint UNSIGNED);以id为主键；
CREATE TABLE tbl2 (id int UNSIGNED NOT NULL, name VARCHAR (20) NOT NULL, age tinyint UNSIGNED,PRIMARY KEY(id,name));以id和name为主键；
表选项：
ENGINE [=] engine_name→ 存储引擎
查看支持的存储引擎：
SHOW ENGINES;
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} → 行格式
获取帮助：HELP CREATE TABLE;
查看创建表时使用的命令：SHOW CREATE TABLE tbl_name;
查看表状态：SHOW TABLE STATUS LIKE ‘tbl_name’;
竖排显示：SHOW TABLE STATUS LIKE ‘tbl_name’\G;
例子：
*************************** 1. row ***************************
Name: students→ 表名
Engine: InnoDB→ 存储引擎
Version: 10 → 版本
Row_format: Compact → 行格式：紧致
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2018-12-23 03:53:42
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci→ 使用的字符集
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
DROP TABLE [IF EXISTS] ‘tbl_name’;→ 删除表；
ALTER TABLE ‘tbl_name’;→ 修改表；
字段
添加字段：ADD
ADD col1 data_type [FIRST|AFTER col_name]
例子：ALTER TABLE students ADD gender ENUM('m','f');→ 添加字段；
ALTER TABLE students ADD UNIQUE KEY (name);→添加唯一键；
删除字段：DROP
修改字段：ALTER，CHANGE，MODIFY
例子： ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;
索引
添加索引：ADD
例子：ALTER TABLE students ADD INDEX(age);
删除索引：DROP
例子：ALTER TABLE students DROP age;
表选项
修改
查看帮助：HELP ALTER TABLE;
查看表上的索引：SHOW INDEX FROM [db_name.]tbl_name;
索引：索引是一种特殊的数据结构；可以加速查询操作，一般定义在查找时作为查找条件的字段上；
索引虽然会带来多余的开销(比如：额外占用空间、插入数据时要插入两次：[原表中一次，索引中一次]、插入到索引中时，索引是排序的，这就需要检索这一顺序然后将数据插到相应位置)，但是相对于没有索引时将表全部放进内存进行全表扫描来说已经节约了很大的开销了，尤其是大表；对于mysql来说一般不会将一个字段中的所有内容都拿来当作索引，一般是一个字段上的前多少个字节当作索引(默认为70个)；
单独创建索引时要有索引名称；
创建索引：CREATE INDEX index_name ON tbl_name (index_col_name,…);
删除索引：DROP INDEX index_name ON tbl_name;
例子：DROP INDEX name ON students;
DML；
插入行：
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...
例子：INSERT INTO students VALUES (1,'Yang Guo','m'),(2,'Guo Xiang','f');
INSERT INTO students (sid,name) VALUES (3,'Zhang Wuji'),(4,'Zhao Min');
删除行：
DELETE FROM tbl_name [WHERE clause] [ORDER BY ‘col_name’] [LIMIT [m,]n]; → 红字为：删除整张表；
例子：DELETE FROM students WHERE sid=3;
更新表中的数据：
UPDATE tbl_name SET col1=new_val1,col2=new_val2,… [WHERE clause] [ORDER BY ‘col_name’] [LIMIT [m,]n];
例子：UPDATE students SET gender=’f’ WHERE sid=4;
查询行：
SELECT col1,col2,… FROM tbl_name [WHERE clause] [ORDER BY ‘col_name’] [LIMIT [m,]n];
字段表示方法：
*：表示所有字段；
as：字段别名，col1 AS alias1，查询显示的结果为alias1；
例子：SELECT sid AS stuid ,name AS stuname FROM students;
例子：SELECT * FROM students;
SELECT * FROM students WHERE sid<3;
SELECT * FROM students WHERE gender=’m’;
SELECT * FROM students WHERE IS NULL;
SELECT * FROM students ORDER BY name;
WHERE clause:
操作符：
>,<,>=,<=,==,!=
BETWEEN …. AND …
LIKE：
%：任意长度的任意字符；
例子：SELECT * FROM students WHERE name LIKE ‘Z%’;
_：任意单个字符；
RLIKE：基于正则表达式作搜索，速度相对较慢；
IS NULL
IS NOT NULL
条件的逻辑操作：
and，or，not
例子：SELECT * FROM students WHERE sid<=4 and sid>=2;
SELECT * FROM students WHERE sid BETWEEN 2 AND 4;
DCL：
用户帐号及权限管理：
用户帐号：’user’@’host’
user：登陆名
mysqd在验证客户端身份时，它会尝试着在每一次客户端创建连接时，反解客户端的IP地址为其对应的主机名，然后根据主机名来检查权限；所以有时候我们命名做了授权却无法访问，是因为虽然根据主机名做了授权但是主机却无法将IP地址反解为对应的主机名(没有作DNS解析)；所以我们在使用此功能时建议禁止检查主机名；
在mysql服务的配置文件(/etc/my.cnf | /etc/mysql/my.cnf)中添加一行skip_name_resolve = yes
host：此用户访问mysql服务时允许通过哪些主机远程创建连接；
IP、网络地址、主机名、通配符(%和_)
创建用户帐号：
CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’];
删除用户：
DROP USER ‘username’@’host’;
授权：包括管理权限、{创建 | 删除 | 修改}{数据库 | 表 | 字段}、存储历程
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] db_name.tbl_name TO ‘user’@’host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];→ 将绿数据库中的绿表中的红权限授予给蓝用户；
[WITH GRANT OPTION]：将权限转赠给他人；
priv_type：ALL [PRIVILEGES]，授予所有权限；
db_name.tbl_name：
*.*：所有库的所有表；
db_name.*：指定库的所有表；
db_name.tbl_name：指定库的指定表；
db_name.routine_name：指定库的存储例程；
……
查看指定用户获得的授权：
SHOW GRANTS FOR ‘user’@’host’;
例子： SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR CURRENT_USER;→ 查看当前用户授权；
撤销权限：
REVOKE priv_type ,… ON db_name.tbl_name FROM ‘user’@’host’;
MariaDB服务进程启动时会读取mysql库中的所有授权表至内存中，每当用户来登陆时，都会在内存中检查权限是否符合；所以在服务器启动之后新建用户或者更改了某个用户的权限之后可能会发现没有生效，这是因为内存中的数据还没有被更新所导致的，只要键入FLUSH PRIVILEGES;
GRANT或REVOKE等执行的权限操作会保存到表中，MariaDB的服务进程会自动重读授权表；
对于不能够或者不能及时重读授权表的命令，可以手动键入上面的命令让MariaDB的服务重读授权表；
注：根据马哥视频做的学习笔记，如有错误，欢迎指正；侵删；
借鉴资料：MYSQL必知必会；
