MariaDB SQL语句基础

 MariaDB SQL语句基础

SQL语句基础：


数据库：database
DDL ：data Defination Language，数据定义语言
创建，删除，修改 ==> CREATE,DROP,ALTER

DML ：data Manipulatation Language，数据操纵语言
插入，删除，修改，查询 ==> INSERT,DELETE,UPDATE,SELECT,

DCL ：
授权，收回权限 ==> GRANT,REVOKE


（一）DDL语言应用：

数据库应用：
创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';

查看数据库：
SHOW DATABASES;

查看所有有权限的
SHOW GRANTS FOR user

指明字符集：
CHARACTER SET 'character set name'
查看所有支持的字符集：
SHOW CHARACTER SET;

指明排序规则：
COLLATE 'collate name'
查看所有支持的排序规则：
SHOW COLLATION;

删除数据库：
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
【注意】数据库中没有回收站，数据删除后无法恢复
修改数据库：
ALTER dataBASE|SCHEMA 'DB_NAME';

获取命令帮助：
mysql> HELP KEYWORK;
如：mysql> HELP CREATE DATABASE;

【示例】：创建查看并删除测试数据库和表
创建库：
mysql> CREATE DATABASE testdb;
查看库：
mysql> SHOW DATABASES;
切换库：
mysql> use testdb;


*** 表：table
* 【注意】一个表可以没有行(如空表)，但至少要有一个字段

字段组成 ：字段名、数据类型、字段修饰、数据类型修饰、约束
【注意】字段修饰符在类型修饰符前面

数据类型：
字符型
定长字符型 ：占据空间不随字符实际长度变化
CHAR(#) ：不区分字符大小写
BINARY(#) ：区分字符大小写
变长字符型 ：占据空间随字符实际长度而变化，按需占用空间
VARCHAR(#) ：不区分字符大小写
VARBINARY ：区分字符大小写
对象存储：
TEXT ：字符， 不区分字符大小写
BLOB ：二进制，区分大小写
mysql内建类型：
ENUM ：枚举，如gender(ENUM('m','f'))
SET ：集合

数值型
精确数值型
整形 ：int
tinyint ：1 byte
smallint ：2 bytes
mediumint ：3 bytes
int ：4 bytes
bigint ：8 bytes
十进制 ：decimal

近似数值型 
单精度浮点型
float
双精度浮点型
double

日期时间型：
DATE ：日期
TIME ：时间
DATETIME ：日期时间
TIMESTAMP ：时间戳
YEAR(2) ：2位年份
YEAR(4) ：4位年份


约束关系、修饰符使用范围：
所有数据类型使用：
NOT NULL ：非空约束
DEFAULT VALUE ：设定默认值
PRIMARY KEY ：定义主键
UNIQUE KEY ：定义唯一键
数值型适用：
UNSIGNED ：无符号，即非负数
AUTO_INCREMENT ：自增长

修饰符分类：
字段修饰符：
PRIMARY KEY ：定义主键
UNIQUE KEY ：定义唯一键

类型修饰符：
NOT NULL ：非空约束
DEFAULT VALUE ：设定默认值


*** 表的应用：
创建表：字段 ：字段名、数据类型、约束
CREAT TABLE [IF NOT EXISTS] tb_name (col_name data_type DEFINITION, col_name data_type DEFINITION, ...) [TABLE OPTIONS]
定义字段和类型 col_name type
定义主键 PRIMARY KEY(col_name, ...)
定义索引 INDEX(col_name, ...)
定义唯一键 UNIQUE(col_name, ...)
定义表选项 ENGINE='engine_name'
** 【注意】一个表中只能有一个主键，定义主键的方法：
1、定义某一单独字段作为主键
如：定义id字段为主键：
mysql> CREAT TABLE IF NOT EXISTS test_table (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL, age tinyint UNSIGNED) ;
2、定义某几个字段联合起来作为主键
如：定义id和name两个字段联合为主键：
mysql> CREAT TABLE IF NOT EXISTS test_table2 (id int UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, age tinyint UNSIGNED，PRIMARY KEY(id,name)); 

表选项：
ENGINE='engine_name'
查看所有支持的引擎：SHOW ENGINES;
*建议使用InnoDB引擎
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

查看表内容：
** DESC [db_name.]tb_name;类似于绝对路径和相对路径
查看当前库中已有的表：
SHOW TABLES;
查看表信息：
SHOW CREATE TABLE tb_name;
查看表状态：
SHOW TABLE STATUS LIKE 'tb_name'\G
\G 是将信息用竖排显示代替横排


修改表：
ALTER TABLE 'tb_name';
字段修改：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
删除地段：drop
修改字段：
alter  ：删除字段的默认值
change ：修改字段名称
modify ：修改字段属性定义
索引：
添加索引：add
删除索引：drop
表选项：
修改表选项

获取帮助：
mysql> HELP CREATE TABLE;

删除表：
DROP TABLE [IF EXISTS] tb_name;
【注意】删除操作不可逆，慎用

* 【示例】
创建表1和表2，并设置表2中id字段作为主键：
mysql> CREATE TABLE tbl1 (id int NOT NULL, name VARCHAR(100) NOT NULL, age tinyint);
mysql> CREATE TABLE tbl2 (class int NOT NULL, id tinyint NOT NULL, name VARCHAR(100) NOT NULL，PRIMARY KEY(id)) ENGINE='InnoDB'
查看某数据库内的所有表：
mysql> SHOW TABLES;
查看表2：
mysql> DESC tbl2;
向表中增加字段gender ：
mysql> ALTER TABLE tbl2 ADD gender ENUM('m','f') AFTER name;
将id字段修改为sid并重新定义约束 ：
mysql> ALTER TABLE tbl2 CHANGE id sid tinyint(4) UNSIGNED NOT NULL;
将name和class设为唯一键：
mysql> ALTER TABLE tbl2 ADD UNIQUE KEY(name,class);
删除class的唯一键：
mysql> ALTER TABLE tbl2 DROP KEY class;
添加索引：
mysql> ALTER TABLE tbl2 ADD INDEX(gender);
竖向查看索引：
mysql> SHOW INDEXES FROM tbl2\G;
删除字段sid ：
mysql> ALTER TABLE tbl2 DROP sid;
删除表1：
mysql> DROP TABLE tbl1;
Query OK, 0 rows affected (0.06 sec)
mysql> DROP TABLE IF EXISTS  tbl1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
最终结果：
mysql> DESC tbl2;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| class  | int(11)             | NO   |     | NULL    |       |
| name   | varchar(100)        | NO   | UNI | NULL    |       |
| gender | enum('m','f')       | YES  | MUL | NULL    |       |
| id     | tinyint(4) unsigned | NO   | PRI | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)



索引：
索引式特殊数据结构：一般定义在查找时作为查找条件的字段

索引：要有索引名称，某索引如果不使用，为了不影响速度，应该删掉索引

索引应用：
创建索引：
CREATE INDEX index_name ON tbl_name (index_col_name,...)
查看索引：
SHOW INDEXES FROM tbl_name[\G];
删除索引：此命令也可以删除键
DROP INDEX index_name ON tbl_name;



（二）DML语言应用
DML ：data Manipulatation Language，数据操纵语言
插入，删除，修改，查询 ==> INSERT,DELETE,UPDATE,SELECT

插入：INCERT
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (value1,...),(...),...
如：mysql> INSERT INTO tbl2 VALUES (23,'Zhang San','m',01),(23,'Li Si','f',02);
或：mysql> INSERT INTO tbl2 (id,name,class) VALUES (3,'Yang Guo','23'),(4,'Guo Xiang','23');


查询表的内容：SELECT
SELECT col_name,col_name,... FROM tbl_name [WHERE clause] [ORDER BY 'com_name' [DESC]] [LIMIT [m,]n];
字段表示法：
* ：所有字段
as ：字段别名，col1 AS alias1
如：显示id为student_id,name为student_name
mysql> SELECT id AS stuedent_id,name AS student_name FROM tbl2;

WHERE clause ：筛选条件
操作符：
=/== , > , < , >= , <= , !=
BETWEEN a AND b  ：在a与b之间
LIKE ：模糊匹配
% ：任意长度任意字符
_ ：任意单个字符

如：查找 name字段 以 G 开头的
mysql> SELECT * FROM tbl2 WHERE name LIKE 'G%';

RLIKE ：基于正则表达式匹配，需要启用正则表达式引擎，效率差
如：mysql> SELECT * FROM tbl2 WHERE name RLIKE '.*Guo.*';

IS NULL ：值为空
IS NOT NULL ：值不为空



条件逻辑操作：
and ，or ， not 
&& ， ||  ， ！

ORDER BY 'col_name' ：按某一字段排序

LIMIT [m,]n ：偏移m个，仅显示n个



示例：查找显示表中id字段数值不等于3的行：
mysql> SELECT * FROM tbl2 WHERE id!=3;
组合筛选条件查找：
mysql> SELECT * FROM tbl2 WHERE id!=3 && gender IS NOT NULL && class='23';
【注意】查询某字段为空的语法，如：gender IS NULL 或 gender IS NOT NULL 。
查找后按 name字段 升序排序：
mysql> SELECT * FROM tbl2 WHERE id>=2 ORDER BY name;
按 name字段 降序排序：
mysql> SELECT * FROM tbl2 WHERE id>=2 ORDER BY name DESC;
限制只显示前两个：
mysql> SELECT * FROM tbl2 WHERE id>=2 ORDER BY name LIMIT 2;
偏移1个，取两个
mysql> SELECT * FROM tbl2 WHERE id>=2 ORDER BY name DESC LIMIT 1,2;


删除：DELETE
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
如：mysql> DELETE FROM tbl2 WHERE id=2;

【注意】要注意加WHERE condition这个选项，如果不加代表删除所有行
DELETE FROM tbl_name;  代表清空整个table，危险！

修改：UPDATE
UPDATE tbl_name SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
如：mysql> UPDATE tbl2 SET gender='f' WHERE name='Guo Xiang';

【注意】要注意加WHERE condition这个选项，如果不加代表修改所有行


（三）用户账号及权限管理

用户账号：'user'@'host'
user ：用户名
host ：此用户访问mysqld服务时允许通过哪些主机远程创建连接
IP、网络地址、主机名、通配符( " % " 和 " _ " )

修改配置文件，启用略过解析主机名选项：
vim /etc/my.cnf
[mysqld]
增加一行：
skip-name-resolve=ON



用户登录：
~]# mysql -uusername -hhostname -ppassword

查看当前登录用户：
mysql> SELECT user();

查看所有已创建的用户：
mysql> use mysql;
mysql> SELECT user,host,password FROM user;

创建用户：
mysql> CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; 

删除用户：
mysql> DROP USER 'username'@'hostname';
【注意】单引号为必需

修改已有用户密码:
mysql> SET PASSWORD FOR 'username'@'hostname'=PASSWORD('password');
【注意】密码可以为空，代表删除密码

授权：
权限分类：管理权限、数据库、表、字段、存储例程

GRANT priv_type,... ON [object_type] db_name.tb_name TO 'username'@'hostname' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

priv_type ：权限类型
ALL ：所有权限，【慎用】
增删改查等等权限
db_name.tb_name ：
*.* ：所有库的所有表
db_name.* ：指定库的所有表
db_name.tb_name ：指定库的指定表
db_name.routine_name ：指定库的存储例程

如：mysql> GRANT ALL ON wpdb.* TO 'wpuser'@'127.0.0.1' IDENTIFIED BY 'zmkm';

查看指定用户获得的授权：
mysql> SHOW GRANTS;
mysql> SHOW GRANTS CURRENT_USER;
mysql> SHOW GRANTS FOR 'username'@'hostname';

回收权限：
REVOKE priv_tyep,... ON db_name.tb_name FROM 'username'@'hostname';


【注意】MariaDB服务进程启动时，会读取mysql库中的所有授权表至内存中
(1) GRANT或REVOKE等执行权限操作时会保存于表中，MariaDB的服务进程会自动重读授权表
(2)对于不能或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表
mysql> FLUSH PRIVILEGES;