执行顺序
(1)FROM [left_table]
(2)ON <join_condition>
(3)<join_type> JOIN <right_table>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(8)SELECT
(9)DISTINCT
(10)ORDER BY <order_by_list>
(11)Top
文章目录
- 查看版本
- 查看那些线程正在运行
- 找到数据库文件的保存位置
- 慢查询
- 二进制日志文件
- 显示用户信息
- 显示服务器错误或警告消息
- 限定返回的结果行数---LIMIT
- 查询支持的全部存储引擎
- 查询默认的存储引擎
- 创建数据库（指定字符集）
- 后续修该表的字符集
- 数据类型（列类型）
- 创建表（指定引擎，字符集）
- 后续修改表字段，条件约束
- 后续添加列
- 后续删除列
- 删除表
- 删除表所有数据(只能删除所以数据行)
- 删除表的数据行
- 变更表名
- 修改表的存储引擎
- 修改表的自增类型字段的初始值
- 查看支持的字符集
- 查看当前字符集
- 查看编码格式
- 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
- 在建立数据表时创建索引
- 在已建立的数据表中创建索引
- 删除索引
- 插入数据到数据表
- 复制表Product的数据到表ProductMargin，并运算添加多一列
- 更新表数据
- 查看表的字段详细信息，如主键-----DESC(DESCRIBE或EXPLAIN)
- 注释
- SELECT书写常数
- SELECT时过滤指定重复行------DISTINCT
- 算数运算符
- 比较运算符
- 逻辑运算符
- 聚合函数，GROUP BY，HAVING和ORDER BY（所谓聚合，就是将多行汇总为一行。）
- 其他函数
- 视图（类似批处理脚本）
- 子查询
- 谓词
- 集合运算，表和表之间的操作
- 联结（以列为单位对表进行联结），表和表之间的操作
- 窗口函数
- GROUPING运算符
- 存储过程
- 游标
- 触发器
- 事务
- 事务的4种隔离级别和并发事务带来哪些问题?
- 用户账号管理
- 数据库维护
- 数据库备份，恢复和数据库迁移
- 用SELECT ...INTO OUTFILE导出文本文件
- 用mysqldump命令导出文本文件
- 用mysql命令导出文本文件
- 优化
- 锁
查看版本
SELECT VERSION();
查看那些线程正在运行
SHOW PROCESSLIST
找到数据库文件的保存位置
show variables like '%datadir%';
慢查询
查看是否开启慢查询功能：
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录
mysql> show variables like 'slow_query%';
+---------------------+------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/instance-1-slow.log |
+---------------------+------------------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
临时配置
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> set global slow_query_log_file='/var/lib/mysql/instance-1-slow.log';
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
永久配置
/etc/my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/instance-1-slow.log
long_query_time = 2
配置好后，重新启动 MySQL 即可。
二进制日志文件
--配置文件设置如下;
server-id = 1 --指定一个集群内的 MySQL 服务器 ID，如果做数据库集群那么必须全局唯一，一般来说不推荐 指定 server_id 等于 1。
log_bin_index = /var/lib/mysql/mysql-bin.index --指定索引文件的位置
log_bin = /var/log/mysql/mysql-bin.log --开启 Binlog 并写明存放日志的位置
expire_logs_days = 10 --删除超出这个变量保留期之前的全部日志被删除
max_binlog_size = 200M --binary log 最大的大小
--查看是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)
--查看数据库binlog列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 531 |
+------------------+-----------+
1 row in set (0.00 sec)
--查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 531 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
显示用户信息
MariaDB [TEST]> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.000 sec)
显示服务器错误或警告消息
MariaDB [TEST]> SHOW ERRORS;
Empty set (0.000 sec)
MariaDB [TEST]> SHOW WARNINGS;
Empty set (0.000 sec)
限定返回的结果行数—LIMIT
返回前三行
MariaDB [TEST]> SELECT prod_name FROM products LIMIT 3;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.000 sec)
从第二行开始，返回五行
MariaDB [TEST]> SELECT prod_name FROM products LIMIT 2,5;
+-------------+
| prod_name |
+-------------+
| 2 ton anvil |
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
+-------------+
5 rows in set (0.000 sec)
查询支持的全部存储引擎
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| OQGRAPH | YES | Open Query Graph Computation Engine (http://openquery.com/graph) | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
12 rows in set (0.001 sec)
查询默认的存储引擎
MariaDB [(none)]> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | |
| enforce_storage_engine | |
| storage_engine | InnoDB |
+----------------------------+--------+
4 rows in set (0.001 sec)
default_storage_engine 表示永久表（permanent tables）的默认存储引擎。
default_tmp_storage_engine 表示临时表的默认存储引擎。
storage_engine这个系统变量不推荐使用，它已经被系统变量default_storage_engine替代了。
创建数据库（指定字符集）
数据库下创建的表的字符集也是utf8
MariaDB [TEST]> CREATE DATABASE Love CHARACTER SET = 'utf8';
Query OK, 1 row affected (0.001 sec)
GBK: create database 数据库名 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci；
查看数据库字符集：show variables like '%char%';
后续修该表的字符集
ALTER {DATABASE | SCHEMA} [数据库名] [DEFAULT] CHARACTER SET [=] 字符集 | [DEFAULT] COLLATER [=] 较对规则名称（”|“为两者可选项）
MariaDB [TEST]> ALTER DATABASE Love DEFAULT CHARACTER SET 'gbk' DEFAULT COLLATE 'gbk_chinese_ci';
Query OK, 1 row affected (0.001 sec)
MariaDB [TEST]> SHOW CREATE DATABASE Love;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| Love | CREATE DATABASE `Love` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.000 sec)
数据类型（列类型）
1. 数值类型
-- a. 整型 ----------
类型 字节 范围（有符号位）
tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表示总位数
- 默认存在符号位，unsigned 属性修改
- 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
例：int(5) 插入一个数'123'，补填后为'00123'
- 在满足要求的情况下，越小越好。
- 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。
-- b. 浮点型 ----------
类型 字节 范围
float(单精度) 4字节
double(双精度) 8字节
浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
不同于整型，前后均会补填0.
定义浮点型时，需指定总位数和小数位数。
float(M, D) double(M, D)
M表示总位数，D表示小数位数。
M和D的大小会决定浮点数的范围。不同于整型的固定范围。
M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
支持科学计数法表示。
浮点数表示近似值。
-- c. 定点数 ----------
decimal -- 可变长度
decimal(M, D) M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。
2. 字符串类型
-- a. char, varchar ----------
char 定长字符串，速度快，但浪费空间
varchar 变长字符串，速度慢，但节省空间
M表示能存储的最大长度，此长度是字符数，非字节数。
不同的编码，所占用的空间不同。
char,最多255个字符，与编码无关。
varchar,最多65535字符，与编码有关。
一条有效记录最大不能超过65535个字节。
utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符
varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。
例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3
-- b. blob, text ----------
blob 二进制字符串（字节字符串）
tinyblob, blob, mediumblob, longblob
text 非二进制字符串（字符字符串）
tinytext, text, mediumtext, longtext
text 在定义时，不需要定义长度，也不会计算总长度。
text 类型在定义时，不可给default值
-- c. binary, varbinary ----------
类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.
3. 日期时间类型
一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节 日期 1000-01-01 到 9999-12-31
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
YYYYMMDDhhmmss
YYMMDDhhmmss
YYYYMMDDhhmmss
YYMMDDhhmmss
date YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD
YYYYMMDD
YYMMDD
time hh:mm:ss
hhmmss
hhmmss
year YYYY
YY
YYYY
YY
4. 枚举和集合
-- 枚举(enum) ----------
enum(val1, val2, val3...)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
表现为字符串类型，存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。
-- 集合（set） ----------
set(val1, val2, val3...)
create table tab ( gender set('男', '女', '无') );
insert into tab values ('男, 女');
最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
当创建表时，SET成员值的尾部空格将自动被删除。
创建表（指定引擎，字符集）
AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。
CREATE TABLE Addressbook
(
regist_no INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) DEFAULT 0,
tel_no CHAR(10) ,
mail_address CHAR(20) ,
PRIMARY KEY (regist_no)) ENGINE=InnoD,BDEFAULT CHARSET=GBK;;
后续修改表字段，条件约束
Alter[IGNORE] TABLE 数据表名 alter_spec[,alter_spec]…
例子；alter table tb_bookinfobak add translator varchar(30) not null ,modify inTime DATETIME(6);
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ] --添加新字段
ADD INDEX [index_name] (index_col_name,...) --添加索引名称
ADD PRIMARY KEY (index_col_name,...) --添加主键名称
ADD UNIQUE [index_name] (index_col_name,...) --添加唯一索引
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} --修改字段名称
CHANGE [COLUMN] old_col_name create_definition --修改字段类型
MODIFY [COLUMN] create_definition --修改子句定义字段
DROP [COLUMN] col_name --删除字段名称
DROP PRIMARY KEY --删除主键名称
DROP INDEX index_name --删除索引名称
RENAME [AS] new_tbl_name --更改表名
添加约束条件
Alter TABLE 数据表名 ADD CONSTRAINT 约束名 约束类型 (字段名)
Alter TABLE tb_bookinfo ADD CONSTRAINT mrprimary PRIMARY KEY (id);
删除约束条件
ALTER TABLE 表名 DROP PRIMARY KEY
ALTER TABLE tb_bookinfo DROP PRIMARY KEY;
后续添加列
-- [PostgreSQL] [MySQL]
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
-- [Oracle]
ALTER TABLE Addressbook ADD (postal_code CHAR(8)) NOT NULL;
-- [SQL Server]
ALTER TABLE Addressbook ADD postal_code CHAR(8) NOT NULL;
/*
[DB2] 无法添加。
在DB2中，如果要为添加的列设定NOT NULL约束，
需要像下面这样指定默认值，或者删除NOT NULL约束，
否则就无法添加新列。
*/
-- [DB2 修正版]
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL DEFAULT '0000-000';
后续删除列
ALTER TABLE Addressbook DROP COLUMN postal_code;
删除表
DROP TALBE Addressbook;
删除表所有数据(只能删除所以数据行)
TRUNCATE TABLE <表名>;
DELETE语句和TRUNCATE TABLE语句的区别：
使用TRUNCATE TABLE语句后，表中的AUTO_INCREMENT计数器将被重新设置为该列的初始值。
对于参与了索引和视图的表，不能使用TRUNCATE TABLE语句来删除数据，而应用使用DELETE语句。
TRUNCATE TABLE操作比DELETE操作使用的系统和事务日志资源少。DELETE语句每删除一行，都会在事务日志中添加一行记录，而TRUNCATE TABLE语句是通过释放存储表数据所用的数据页来删除数据的，因此只在事务日志中记录页的释放。
删除表的数据行
删除所有数据行
DELETE FROM Product;
删除所选范围内的数据行
DELETE FROM Product WHERE sale_price >= 666;
变更表名
-- [MySQL]
RENAME TABLE Addressbook TO abc;
-- [Oracle][PostgreSQL]
ALTER TABLE Addressbook RENAME TO abc;
-- [SQL Server]
sp_rename 'Addressbook', 'abc';
-- [DB2 修正版]
RENAME TABLE Addressbook TO abc;
修改表的存储引擎
ALTER TABLE tb_bookinfo ENGINE=MyISAM;
修改表的自增类型字段的初始值
ALTER TABLE tb_bookinfo AUTO_INCREMENT=100;
查看支持的字符集
MariaDB [TEST]> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.000 sec)
查看支持校对的完整列表
SHOW COLLATION
查看当前字符集
1,SHOW FULL COLUMNS FROM <表名>;
2,SHOW CREATE TABLE <表名>;
查看编码格式
SHOW VARIABLES LIKE ‘%char%’;
把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
ALTER TABLE <表名> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER DATABASE <数据库> DEFAULT CHARACTER SET character_name [COLLATE ...];
//utf8_general_ci 不区分大小写
//utf8_general_cs 区分大小写
在建立数据表时创建索引
CREATE TABLE table_name(
属性名 数据类型[约束条件],
属性名 数据类型[约束条件]
……
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL ] INDEX }KEY
[别名]( 属性名1 [(长度)] [ASC | DESC])
);
其中，属性名后的属性值，其含义如下：
UNIQUE：可选参数，表明索引为唯一性索引；
FULLTEXT：可选参数；表明索引为全文搜索。
SPATIAL：可选参数，表明索引为空间索引。
INDEX和KEY参数用于指定字段索引，用户在选择时，只需要选择其中的一种即可；另外别名为可选参数，其作用是给创建的索引取新名称；别名的参数如下：
属性名1：指索引对应的字段名称，该字段必须被预先定义。
长度：可选参数，其指索引的长度，必须是字符串类型才可以使用。
ASC/DESC：可选参数，ASC表示升序排列，DESC参数表示降序排列。
例子；CREATE TABLE tb_score(
id int(11) auto_increment primary key not null,
name varchar(50) not null,
math int(5) not null,
english int(5) not null,
chinese int(5) not null,
index(id));
在已建立的数据表中创建索引
CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name
ON table_name(属性 [(length)] [ ASC | DESC]);
index_name为索引名称，该参数作用是给用户创建的索引赋予新的名称；
table_name为表名，即指定创建索引的表名称。
可选参数，指定索引类型，包括UNIQUE（唯一索引）、FULLTEXT（全文索引）、SPATIAL（空间索引）。
属性参数，指定索引对应的字段名称。该字段必须已经预存在于用户想要操作的数据表中，如果该数据表中不存在用户指定的字段，则系统会提示异常；
length为可选参数，用于指定索引长度。
ASC和DESC参数，指定数据表的排序顺序。
与建立数据表时创建索引相同，在已建立的数据表中创建索引同样包含6种索引方式。
例子；CREATE INDEX idx_name ON tb_bookinfo (bookname);
删除索引
DROP INDEX index_name ON table_name;
例子；DROP INDEX idx_name ON tb_bookinfo;
插入数据到数据表
1，省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。
2，INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何 SQL语法（但使用ORDER BY子句并不会产生任何效果）。
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
在实现数据插入时，还可以使用REPLACE插入新记录。REPLACE语句与INSERT INTO语句类似。所不同的是：如果一个要插入数据的表中存在主键约束（PRIMARY KEY）或者唯一约束（UNIQUE KEY），而且要插入的数据中又包含与要插入数据的表中相同的主键约束或唯一约束列的值，那么使用INSERT INTO语句则不能插入这条记录，而使用REPLACE语句则可以插入，只不过它会先将原数据表中起冲突的记录删除，然后再插入新的记录。
REPLACE语句有以下3种语法格式。
语法一：
REPLACE INTO 数据表名[(字段列表)] VALUES(值列表)
语法二：
REPLACE INTO 目标数据表名[(字段列表1)] SELECT (字段列表2) FROM 源表 [WHERE 条件表达式]
语法三：
REPLACE INTO 数据表名 SET 字段1=值1,字段2=值2,字段3=值3……
复制表Product的数据到表ProductMargin，并运算添加多一列
数据库经常被多个客户访问，对处理什么请
求以及用什么次序处理进行管理是MySQL的任务。INSERT操
作可能很耗时（特别是有很多索引需要更新时），而且它可能
降低等待处理的SELECT语句的性能。
如果数据检索是最重要的（通常是这样），则你可以通过在
INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL
降低INSERT语句的优先级，如下所示：
INSERT LOW_PRIORITY INTO
这也适用于下一章介绍的UPDATE和DELETE语句。
MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快
INSERT INTO ProductMargin (product_id, product_name, sale_price, purchase_price, margin)
SELECT product_id, product_name, sale_price, purchase_price, sale_price - purchase_price
FROM Product;
MariaDB [Shop]> SELECT * FROM ProductMargin;
+------------+--------------+------------+----------------+--------+
| product_id | product_name | sale_price | purchase_price | margin |
+------------+--------------+------------+----------------+--------+
| 0001 | T恤 | 1000 | 500 | 500 |
| 0002 | 打孔器 | 500 | 320 | 180 |
| 0003 | 运动T恤 | 4000 | 2800 | 1200 |
| 0004 | 菜刀 | 3000 | 2800 | 200 |
| 0005 | 高压锅 | 6800 | 5000 | 1800 |
| 0006 | 叉子 | 500 | NULL | NULL |
| 0007 | 擦菜板 | 880 | 790 | 90 |
| 0008 | 圆珠笔 | 100 | NULL | NULL |
+------------+--------------+------------+----------------+--------+
更新表数据
IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些
行中的一行或多行时出一个现错误，则整个UPDATE操作被取消
（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发
生错误，也继续进行更新，可使用IGNORE关键字，如下所示：
UPDATE IGNORE customers…
1，使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
UPDATE ProductMargin
SET sale_price = NULL
WHERE product_id = '0003';
查看表的字段详细信息，如主键-----DESC(DESCRIBE或EXPLAIN)
MariaDB [Shop]> DESC Product;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int(11) | YES | | NULL | |
| purchase_price | int(11) | YES | | NULL | |
| regist_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.001 sec)
注释
● 1行注释
书写在“--”之后，只能写在同一行。
● 多行注释
书写在“/*”和“*/”之间，可以跨多行。
-- 本SELECT语句会从结果中删除重复行。
SELECT DISTINCT product_id, purchase_price
FROM Product;
/* 本SELECT语句，
会从结果中删除重复行。*/
SELECT DISTINCT product_id, purchase_price
FROM Product;
SELECT书写常数
MariaDB [Shop]> SELECT '商品' AS string,product_name FROM Product;
+--------+--------------+
| string | product_name |
+--------+--------------+
| 商品 | T恤 |
| 商品 | xxxx |
+--------+--------------+
2 rows in set (0.001 sec)
SELECT时过滤指定重复行------DISTINCT
/*如果多条行有NULL时，会将多条NULL合并成一条。DISTINCT 关键字只能用在第一个列名之前。因此，请大家注意不能写成 regist_date, DISTINCT product_type。*/
MariaDB [Shop]> SELECT * FROM Product2;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 2000-05-00 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 2000-03-20 | 2009-09-11 |
| 0009 | 手套 | 衣服 | 800 | 2000-05-00 | NULL |
+------------+--------------+--------------+------------+----------------+-------------+
3 rows in set (0.000 sec)
MariaDB [Shop]> SELECT DISTINCT product_type FROM Product2;
+--------------+
| product_type |
+--------------+
| 衣服 |
| 办公用品 |
+--------------+
2 rows in set (0.001 sec)
算数运算符
四则运算所使用的运算符（+、-、*、/）称为算术运算符。运算符就
是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，
并返回结果的符号。加法运算符（+）前后如果是数字或者数字类型的列
名的话，就会返回加法运算后的结果。
● NULL
5 + NULL
10 - NULL
1 * NULL
4 / NULL
NULL / 9
NULL / 0
正确答案全部都是 NULL。所有包含 NULL 的计算，结果肯定是 NULL。即使用 NULL
除以 0 时这一原则也适用。通常情况下，类似 5/0 这样除数为 0 的话会发
生错误，只有 NULL 除以 0 时不会发生错误，并且结果还是 NULL
SELECT product_name, sale_price,sale_price * 2 AS "sale_price_x2" FROM Product;
比较运算符
● 运算符 含义
= 和~相等
<> 和~不相等
>= 大于等于~
> 大于~
<= 小于等于~
< 小于~
这些比较运算符可以对字符、数字和日期等几乎所有数据类型的列和值进行比较。
SELECT product_name, product_type, regist_date
FROM Product
WHERE regist_date < '2009-09-27';
● 对字符串使用不等号时的注意事项:
现在，chr 列被定为字符串类型，并且在对字符串类型的数据进行
大小比较时，使用的是和数字比较不同的规则。典型的规则就是按照字典
顺序进行比较，该规则最重要的一点就是，以相同字符开头的单词比不同字符开头
的单词更相近。
SELECT chr FROM Chars WHERE chr > '2';
结果:
chr
-----
3
222
666
888
● 不能对NULL使用比较运算符
判断是否为NULL使用 IS NULL(为空)或IS NOT NULL(不为空)
逻辑运算符
● NOT运算符，不能单独使用，必须和其他查询条件结合使用
SELECT product_name, product_type, sale_price FROM Product WHERE NOT sale_price >= 1000;
● AND运算符和OR运算符
AND 运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。
OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立。
AND 运算符优先于 OR 运算符，通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20');
聚合函数，GROUP BY，HAVING和ORDER BY（所谓聚合，就是将多行汇总为一行。）
● 5 个常用的聚合函数。
COUNT：计算表中的记录数（行数）。其他函数不能将星号作为参数（如果使用星号会出错），这是COUNT 函数所特有的。
COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据
行数，而COUNT(<列名>)会得到NULL之外的数据行数（不包含NULL）。
SUM： 计算表中数值列中数据的合计值，忽略NULL
AVG： 计算表中数值列中数据的平均值，忽略NULL
MAX： 求出表中任意列中数据的最大值，忽略NULL
MIN： 求出表中任意列中数据的最小值，忽略NULL
● 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type) FROM Product;
● SQL的分组菜刀GROUP BY
1，使用GROUP BY包含NULL时，在结果中会以空行（“不确定”行）的形式表现出来，并不会忽略。
执行优先级FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，所以使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
2，在GROUP BY子句中不能使用SELECT子句中定义的别名
● 按照商品种类统计数据行数
SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
● 使用聚合函数和GROUP BY子句时需要注意以下 5点。
1，只能写在SELECT子句之中
2，GROUP BY子句中不能使用SELECT子句中列的别名
3，GROUP BY子句的聚合结果是无序的
4，WHERE子句中不能使用聚合函数
5，只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数
6，HAVING子句要写在GROUP BY子句之后
● HAVING（使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。）
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件
WHERE 比HAVING执行速度快
例，SELECT product_type, AVG(sale_price) FROM Product GROUP BY product_type HAVING AVG(sale_price) >= 2500;
● ORDER BY
1,ORDER BY子句通常写在SELECT语句的末尾
2,在ORDER BY子句中可以使用SELECT子句中定义的别名
3，包含NULL时，会在开头或末尾进行汇总，不会忽略NULL
4，在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。
按照销售单价和商品编号的降序进行排序
SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price, product_id DESC;
其他函数
● 数值
ABS函数-----绝对值（绝大部分函数对NULL返回NULL）
SELECT m,
ABS(m) AS abs_col
FROM SampleMath;
MOD(被除数，除数)-----求余（SQL Server不支持该函数，而是用“%”）
SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;
ROUND(对象数值,保留小数的位数)-----四舍五入
SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;
● 字符串
拼接函数-----字符串1||字符串2
SELECT str1, str2,
str1 | | str2 AS str_concat
FROM SampleStr;
LENGTH函数------LENGTH(字符串)
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;
小写转换------LOWER
大写转换------UPPER
SELECT str1,LOWER(str) AS low_str FROM SampleStr WHERE str1 IN ('ABC','aBc')
字符串替换------REPLACE
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
字符串的截取------SUBSTRING
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
截取出字符串中第3位和第4位的字符
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;
● 日期函数
CURRENT_DATE——当前日期
SELECT CURRENT_DATE;
CURRENT_TIME——当前时间
SELECT CURRENTTIME;
CURRENT_TIMESTAMP——当前日期和时间
SELECT CURRENT_TIMESTAMP;
EXTRACT——截取日期元素
EXTRACT(日期元素 FROM 日期)
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
CAST——类型转换
CAST（转换前的值 AS 想要转换的数据类型）
将字符串类型转换为数值类型
SQL Server PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
Oracle
SELECT CAST('0001' AS INTEGER) AS int_col
FROM DUAL;
DB2
SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;
COALESCE——将NULL转换为其他值
使用SampleStr表中的列作为例子
SELECT COALESCE(str2, 'NULL')
FROM SampleStr;
视图（类似批处理脚本）
● 从 SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
● 使用视图，可以轻松完成跨多表查询数据等复杂操作。
● 可以将常用的SELECT语句做成视图来使用。
● 创建视图需要使用CREATE VIEW语句。
● 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”（视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。）两项限制。
● 删除视图需要使用DROP VIEW语句。
● AS和定义别名的AS并不相同，而且省略会报错。
● 对多数 DBMS 来说，多重视图会降低 SQL 的性能（虽然语法没有错），但应该避免在视图的基础上创建视图。
● 创建视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
● 使用视图
SELECT product_type, cnt_product
FROM ProductSum;
● 查看视图
方法一；
mysql> DESC v_book;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode | varchar(30) | YES | | NULL | |
| bookname | varchar(70) | YES | | NULL | |
| author | varchar(30) | YES | | NULL | |
| price | float(8,2) | YES | | NULL | |
| page | int(10) unsigned | YES | | NULL | |
| booktype | varchar(30) | YES | | NULL | |
+----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
方法二；
SHOW TABLE STATUS LIKE '视图名';
方法三；
SHOW CREATE VIEW 视图名
● 修改视图
方法一；
CREATE OR REPLACE [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图[(属性清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];
方法二；
ALTER VIEW [algorithm={merge | temptable | undefined} ]VIEW view_name [(column_list)] AS select_statement[WITH [cascaded | local] CHECK OPTION]
algorithm：该参数已经在创建视图中作了介绍，这里不再赘述。
view_name：视图的名称。
select_statement：SQL语句用于限定视图。
注意：在创建视图时，在使用了WITH CHECK OPTION，WITH ENCRYPTION,WITH SCHEMABING或VIEW_METADATA选项时，如果想保留这些选项提供的功能，必须在ALTER VIEW语句中将它们包括进去。
● 更新视图
UPDATE v_book SET bookname='Java Web程序设计（慕课版）' WHERE barcode='9787115418425';
更新视图的限制；
（1）视图中包含COUNT()、SUM()、MAX()和MIN()等函数
（2）视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVIG等关键字。
（3）常量视图。
（4）视图中的SELECT中包含子查询。
（5）由不可更新的视图导出的视图。
（6）创建视图时，ALGORITHM为TEMPTABLE类型。
（7）视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。
● 删除视图
DROP VIEW ProductSum;
查询MySQL中root用户是否具有创建视图的权限
MariaDB [TEST]> SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y | Y |
| Y | Y |
| Y | Y |
| Y | Y |
+-------------+------------------+
4 rows in set (0.042 sec)
创建视图时需要注意以下几点：
（1）运行创建视图的语句需要用户具有创建视图（CREATE VIEW）的权限，若加了[or replace]时，还需要用户具有删除视图（DROP VIEW）的权限；
（2）SELECT语句不能包含FROM子句中的子查询；
（3）SELECT语句不能引用系统或用户变量；
（4）SELECT语句不能引用预处理语句参数；
（5）在存储子程序内，定义不能引用子程序参数或局部变量；
（6）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句；
（7）在定义中不能引用temporary表，不能创建temporary视图；
（8）在视图定义中命名的表必须已存在；
（9）不能将触发程序与视图关联在一起；
（10）在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。
子查询
● 子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
● 由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
● 子查询优先执行。
● 子查询嵌套越多性能越差，所以尽量避免多层嵌套。
● 标量子查询就是只能返回“一行一列”的子查询。标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用“单一值”的位置都可以使用。
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;
在Oracle的FROM子句中，不能使用AS（会发生错误），因此，在Oracle中执行时，需要AS ProductSum;”变为 ProductSum;。
标量子查询；
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
关联子查询；
● 关联子查询会在细分的组内进行比较时使用（实现跟GROUP BY类似的功能）。
● 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
● 关联子查询只能出现在子查询之中，否则就会发生错误。
例子；关键在子查询的 “WHRER”
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);
谓词
对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/
FALSE/UNKNOWN）。这也是谓词和函数的最大区别。
● 谓词就是返回值为真值的函数。
● 掌握LIKE的三种使用方法（前方一致、中间一致、后方一致）。
● 需要注意BETWEEN包含三个参数。
● 想要取得NULL数据时必须使用IS NULL。
● 可以将子查询作为IN和EXISTS的参数。
LIKE
使用LIKE进行前方一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';
% 是代表“0 字符以上的任意字符串”,此外，我们还可以使用 _（下划线）来代替 %，与 % 不同的是，它代
表了“任意 1 个字符”。
查询“abc+任意2个字符”的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc_ _';
BETWEEN谓词——范围查询
会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值，那就必须使用 < 和 >
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
IS NULL、IS NOT NULL——判断是否为NULL
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL
选取出进货单价（purchase_price）为NULL的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;
IN谓词——OR的简便用法
IN比OR好用
OR;
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
IN;
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);
NOT IN;
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);
CASE;
● CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索
CASE表达式包含简单CASE表达式的全部功能。
● 虽然CASE表达式中的ELSE子句可以省略，但为了让 SQL语句更加容易
理解，还是希望大家不要省略。
● CASE表达式中的END不能省略。
● 使用CASE表达式能够将SELECT语句的结果进行组合。
● 虽然有些 DBMS提供了各自特有的CASE表达式的简化函数，例如Oracle中的DECODE和MySQL中的IF，等等，但由于它们并非通用的函数，功
能上也有些限制，因此有些场合无法使用
搜索CASE表达式;
SELECT product_name,
CASE WHEN product_type = '衣服'
THEN 'A ：' || product_type
WHEN product_type = '办公用品'
THEN 'B ：' || product_type
WHEN product_type = '厨房用具'
THEN 'C ：' || product_type
ELSE NULL
END AS abc_product_type
FROM Product;
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具'
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品'
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;
集合运算，表和表之间的操作
● 集合运算就是对满足同一规则的记录进行的加减等四则运算。
● 使用UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运
算符来进行集合运算。
● 集合运算符可以去除重复行。
● 如果希望集合运算符保留重复行，就需要使用ALL选项。
● 作为运算对象的记录的列数必须相同
● 作为运算对象的记录中列的类型必须一致
● 可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次
使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
使用INTERSECT选取出表中公共部分
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
使用EXCEPT对记录进行减法运算
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
联结（以列为单位对表进行联结），表和表之间的操作
● 联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。
UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位
进行的。
● 联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的
使用方法。
● 请大家一定要使用标准 SQL的语法格式来写联结运算，对于那些过时的
或者特定 SQL中的写法，了解一下即可，不建议使用。
● 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
● 进行联结时需要在FROM子句中使用多张表。
● 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
● WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用。
● 只在 SELECT 语句执行期间存在，SELECT 语句执行之后就会消失。如果希望继续使用这张“表”，还是将它创成视图
● 内联结和外联结差不多，区别在于外联结会包含NULL。
内联结——INNER JOIN
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id;
内联结和WHERE子句结合使用
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P ①
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';
外联结——OUTER JOIN
将两张表进行外联结(选取出单张表中全部的信息,包含NULL)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P ①
ON SP.product_id = P.product_id;
外联结中使用LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id;
对3张表进行内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
交叉联结——CROSS JOIN（笛卡儿积）（比较少用）
因为 ShopProduct表存在 13 条记录，Product 表存在 8 条记录，所以结果中就包含了13 × 8 = 104 条记录
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
窗口函数
● 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
● 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。
● 窗口函数也称为 OLAP 函数，OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据
进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。
● 能够作为窗口函数使用的函数
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
● 专用窗口函数 RANK，RANK 是用来计算记录排序的函数。
● PARTITION BY 能够设定排序的对象范围。
● ORDER BY 能够指定按照哪一列、何种顺序进行排序。可以通过关键字ASC/DESC 来指定升序和降序，默认ASC。
● PARTITION BY 在横向上对表进行分组，而 ORDER BY决定了纵向排序的规则。
● 窗口函数兼具分组和排序两种功能。
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;
● 使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
●RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
●DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
●ROW_NUMBER函数
赋予唯一的连续位次。
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……
● 原则上窗口函数只能在SELECT子句中使用。反过来说，就是这类函数不能在WHERE 子句或者 GROUP BY 子句中使用。
将SUM函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
将当前记录的前后行作为汇总对象（PRECEDING（“之前”）和 FOLLOWING（“之后”））
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND
1 FOLLOWING) AS moving_avg
FROM Product;
GROUPING运算符
● 只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想
要同时得到，可以使用GROUPING运算符。
● 理解GROUPING运算符中CUBE的关键在于形成“积木搭建出的立方体”的印象。
● 虽然GROUPING运算符是标准 SQL的功能，但还是有些 DBMS尚未支持这一功能。
GROUPING 运算符包含以下 3 种
1，ROLLUP
2，CUBE
3，GROUPING SETS
使用ROLLUP同时得出合计和小计
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);
使用CUBE取得全部组合的结果
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY CUBE(product_type, regist_date);
GROUPING SETS——取得期望的积木
SELECT CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,
CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY GROUPING SETS (product_type, regist_date);
存储过程
创建存储过程；
MariaDB [TEST]> DELIMITER //
MariaDB [TEST]> CREATE PROCEDURE productpricing()
-> BEGIN
-> SELECT AVG(prod_price) AS priceaverage FROM products;
-> END //
Query OK, 0 rows affected (0.002 sec)
MariaDB [TEST]> DELIMITER ;
使用存储过程；
MariaDB [TEST]> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
删除存储过程；
MariaDB [TEST]> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.003 sec)
检查存储过程
MariaDB [TEST]> SHOW CREATE PROCEDURE productpric;
+-------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| productpric | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `productpric`()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)
游标
Mysql中游标只适用于存储过程以及函数。
游标的使用一般分为5个步骤，主要是：定义游标->打开游标->使用游标->关闭游标->释放游标。
游标只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢，同时会造成内存不足的现象，给服务器带来严重的性能问题。而且数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，这样非常不可取。
create procedure p1()
begin
declare id int;
declare name varchar(15);
-- 声明游标
declare mc cursor for select * from class;
-- 打开游标
open mc;
-- 获取结果
fetch mc into id,name;
-- 这里是为了显示获取结果
select id,name;
-- 关闭游标
close mc;
end;
触发器
只有表才支持触发器，视图不支持（临时表也不支持）
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许
一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE
和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所
以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义
两个触发器
在MySQL中，触发器按以下顺序执行：BEFORE触发器、表操作、AFTER触发器操作，其中表操作包括常用的数据库操作命令如INSERT、UPDATE、DELETE。
触发器中不能包含START TRANSCATION、COMMIT或ROLLBACK等关键词，也不能包含CALL语句。触发器执行非常严密，每一环都息息相关，任何错误都可能导致程序无法向下执行。已经更新过的数据表是不能回滚的。故在设计过程中一定要注意触发器的逻辑严密性。
CREATE TRIGGER 触发器名称 BEFORE | AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END
创建触发器
MariaDB [TEST]> CREATE TRIGGER newproduct
-> AFTER INSERT ON products
-> FOR EACH ROW SELECT 'Success' INTO @a;
DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER BEFORE DELETE
ON tb_bookinfo1 FOR EACH ROW
BEGIN
INSERT INTO tb_booklog (event,logtime) values('删除了一条图书信息',now());
INSERT INTO tb_bookinfobak SELECT * FROM tb_bookinfo1 where id=OLD.id;
END
//
查看触发器
SHOW TRIGGERS;
或者
SHOW TRIGGERS\G
查看 triggers表中触发器信息
在MySQL中，所有触发器的定义都存在该数据库的triggers表中。读者可以通过查询triggers表来查看数据库中所有触发器的详细信息。
SELECT * FROM information_schema.triggers;
查询指定名称的触发器
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= '触发器名称';
查询指定数据库对应的触发器
SELECT * FROM information_schema.triggers WHERE TRIGGER_SCHEMA= '数据库名称';
删除触发器
在应用完触发器后，切记一定要将触发器删除，否则在执行某些数据库操作时，会造成数据的变化。
MariaDB [TEST]> DROP TRIGGER newproduct;
Query OK, 0 rows affected (0.001 sec)
事务
事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完
整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们
或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发
生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤
销）以恢复数据库到某个已知且安全的状态。
MySQL默认采用自动提交（AUTOCOMMIT）模式。也就是说，如果不显式地开启一个事务，则每个SQL语句都被当作一个事务执行提交操作。
查看MySQL的自动提交功能是否关闭，可以使用MySQL的SHOW VARIABLES命令查询AUTOCOMMIT变量的值，如果该变量的值为1或者ON时表示启用，为0或者OFF时表示禁用。
显式关闭自动提交功能；
SHOW VARIABLES LIKE 'autocommit';
隐式关闭自动提交功能
使用START TRANSACTION;命令时，可以隐式地关闭自动提交功能。该方法不会修改AUTOCOMMIT变量的值。
MySQL使用下面的语句来标识事务的开始：
START TRANSACTION
MySQL的ROLLBACK命令用来回退（撤销）MySQL语句
COMMIT即提交（写或保存）
使用保留点,为了支持回退部分事务处理，必须能在事务处理块中合适的位置放
置占位符。这样，如果需要回退，可以回退到某个占位符。保留点越多越好 可以在MySQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能按自己的意愿灵活地进行回退。
创建占位符
SAVEPOINT delete1；
回退到该占位符
ROLLBACK TO delete1；
事务的4种隔离级别和并发事务带来哪些问题?
事务的隔离级别有4种：
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复度和幻读区别：
不可重复读的重点是修改，幻读的重点在于新增或者删除。
例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。
例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。
用户账号管理
MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。
创建用户账号
MariaDB [mysql]> CREATE USER love IDENTIFIED BY '123';
Query OK, 0 rows affected (0.001 sec)
MariaDB [mysql]> SELECT user FROM user;
+------+
| user |
+------+
| love |
| root |
| root |
| root |
| root |
+------+
5 rows in set (0.000 sec)
指定散列口令 IDENTIFIED BY指定的口令为纯文本，MySQL
将在保存到user表之前对其进行加密。为了作为散列值指定口
令，使用IDENTIFIED BY PASSWORD。
使用GRANT或INSERT GRANT语句（稍后介绍）也可以创建用
户账号，但一般来说CREATE USER是最清楚和最简单的句子。
此外，也可以通过直接插入行到user表来增加用户，不过为安
全起见，一般不建议这样做。MySQL用来存储用户账号信息
的表（以及表模式等）极为重要，对它们的任何毁坏都
可能严重地伤害到MySQL服务器。因此，相对于直接处理来
说，最好是用标记和函数来处理这些表。
更改口类
MariaDB [mysql]> SET PASSWORD FOR love = PASSWORD('456');
Query OK, 0 rows affected (0.000 sec)
SET PASSWORD更新用户口令。新口令必须传递到Password()函
数进行加密。在不指定用户名时，SET PASSWORD更新当前登录用户的口令。
重命名一个用户账号
MariaDB [mysql]> RENAME USER love TO LOVE;
Query OK, 0 rows affected (0.001 sec)
MariaDB [mysql]> SELECT user FROM user;
+------+
| user |
+------+
| LOVE |
| root |
| root |
| root |
| root |
+------+
5 rows in set (0.000 sec)
删除用户账号
MariaDB [mysql]> DROP USER LOVE;
Query OK, 0 rows affected (0.000 sec)
查看用户的权限
MariaDB [mysql]> SHOW GRANTS FOR love;
+-----------------------------------------------------------------------------------------------------+
| Grants for love@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'love'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限
设置用户权限
MariaDB [mysql]> GRANT SELECT ON xiaomi.* TO love;
Query OK, 0 rows affected (0.001 sec)
此GRANT允许用户在xiaomi.*（xiaomi数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户love
对xiaomi数据库中的所有数据具有只读访问权限。
# 授权账号命令：
grant 权限(create, update等) on 库.表 to '账号'@'host' identified by '密码'
# all privileges表示授予所有权限， %代表允许所有域的连接
grant all privileges on luffy.* to 'luffy'@'%' identified by 'luffy';
# 要是本机连mysql连不上，再添加localhost访问权限，本机就可以登录了
>: grant all privileges on luffy.* to 'luffy'@'localhost' identified by 'luffy';
# 设置完有权限限制的账号后一定要刷新权限，如果没刷新权限，该终端无法被通知，当然也可以直接重启cmd
>: flush privileges;
GRANT和REVOKE可在几个层次上控制访问权限：
整个服务器，使用GRANT ALL和REVOKE ALL；
整个数据库，使用ON database.*；
特定的表，使用ON database.table；
特定的列；
特定的存储过程
取消用户权限
MariaDB [mysql]> REvoke SELECT ON xiaomi.* FROM love;
Query OK, 0 rows affected (0.000 sec)
-- root密码重置
1. 停止MySQL服务
2. [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
[Windows] mysqld --skip-grant-tables
3. use mysql;
4. UPDATE `user` SET PASSWORD=PASSWORD("密码") WHERE `user` = "root";
5. FLUSH PRIVILEGES;
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES;
-- 增加用户
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
-- 重命名用户
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';
-- 查看权限
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
-- 权限层级
-- 要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。
全局层级：全局权限适用于一个给定服务器中的所有数据库，mysql.user
GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级：数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.host
GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级：表权限适用于一个给定表中的所有列，mysql.talbes_priv
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级：列权限适用于一个给定表中的单一列，mysql.columns_priv
当使用REVOKE时，您必须指定与被授权列相同的列。
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
数据库维护
首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引
数据），可能需要在进行备份前使用FLUSH TABLES语句。
下面列出这个问题的可能解决方案。
使用命令行实用程序mysqldump转储所有数据库内容到某个外部
文件。在进行常规备份前这个实用程序应该正常运行，以便能正
确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
（并非所有数据库引擎都支持这个实用程序）。
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所
有数据到某个外部文件。这两条语句都接受将要创建的系统文件
名，此系统文件必须不存在，否则会出错。数据可以用RESTORE
TABLE来复原
ANALYZE TABLE，用来检查表键是否正确。
MariaDB [TEST]> ANALYZE TABLE products;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| TEST.products | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.016 sec)
Table：表示表的名称；
Op：表示执行的操作。analyze表示进行分析操作。check表示进行检查查找。optimize表示进行优化操作；
Msg_type：表示信息类型，其显示的值通常是状态、警告、错误或信息中的一个；
Msg_text:：显示信息。
CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对
索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。
CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最
彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删
除的链接并进行键检验，QUICK只进行快速扫描。
如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。
MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。而且，该语句还可以检查视图是否存在错误。
option参数有5个参数，分别是QUICK、FAST、CHANGED、MEDIUM和EXTENDED。这5个参数的执行效率依次降低。
option选项只对MyISAM类型的表有效，对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。
MariaDB [TEST]> CHECK TABLE products;
+---------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| TEST.products | check | status | OK |
+---------------+-------+----------+----------+
1 row in set (0.001 sec)
优化表
MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MyISAM类型的表都有效。
但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。
OPTILMIZE TABLE语句的基本语法如下：
OPTIMIZE TABLE 表名1[,表名2…];
通过OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。
如果一个表使用了TEXT或者BLOB这样的数据类型，那么更新、删除等操作就会造成磁盘空间的浪费。
因为，更新和删除操作后，以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来，以便以后再利用。
查看日志文件
错误日志。它包含启动和关闭问题以及任意关键错误的细节。此
日志通常名为hostname.err，位于data目录中。此日志名可用
--log-error命令行选项更改。
查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此
日志文件可能会很快地变得非常大，因此不应该长期使用它。此
日志通常名为hostname.log，位于data目录中。此名字可以用
--log命令行选项更改。
二进制日志。它记录更新过数据（或者可能更新过数据）的所有
语句。此日志通常名为hostname-bin，位于data目录内。此名字
可以用--log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，
以前的MySQL版本中使用的是更新日志。
缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这
个日志在确定数据库何处需要优化很有用。此日志通常名为
hostname-slow.log ，位于 data 目录中。此名字可以用
--log-slow-queries命令行选项更改。
在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文
件。
数据库备份，恢复和数据库迁移
1.导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql
2，备份所有数据库；
mysqldump -uroot -proot --all-databases >/tmp/all.sql
3.导出db1中的a1、a2表
mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql
4,只导出表结构不导出数据，--no-data
mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql
5,将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test
6,压缩备份
压缩备份
mysqldump -uroot -proot --databases abc 2>/dev/null |gzip >/abc.sql.gz
还原
gunzip -c abc.sql.gz |mysql -uroot -proot abc
直接复制整个数据库目录；
这种方法最简单，速度也最快。使用这种方法时，最好将服务器先停止。这样，可以保证在复制期间数据库中的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。
这种方法对INNODB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便。但是还原时最好是相同版本的MySQL数据库，否则可能会存储文件类型不同的情况。
使用mysqlhotcopy工具快速备份；
mysqlhotcopy工具只能备份MyISAM类型的表，不能用来备份InnoDB类型的表。
mysqlhotcopy工具的备份方式比mysqldump命令快。mysqlhotcopy工具是一个Perl脚本，主要在Linux操作系统下使用。
mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。其工作原理是，先将需要备份的数据库加上一个读操作锁，然后，用FLUSH TABLES将内存中的数据写回到硬盘上的数据库中，最后，把需要备份的数据库文件复制到目标目录。
[root@localhost ~]#mysqlhotcopy[option] dbname1 dbname2…backupDir/
--help：用来查看mysqlhotcopy的帮助；
--allowold：如果备份目录下存在相同的备份文件，将旧的备份文件名加上_old;
--keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧文件更名；
--flushlog：本次备份之后，将对数据库的更新记录到日志中；
--noindices：只备份数据文件，不备份索引文件；
--user=用户名：用来指定用户名，可以用-u代替；
--password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p紧挨着。或者只使用-p，然后用交换的方式输入密码。这与登录数据库时的情况是一样的；
--port=端口号：用来指定访问端口，可以用-P代替；
--socket=socket文件：用来指定socket文件，可以用-S代替。
使用mysql命令还原；
mysql -u root -p db_library <D:\db_library.sql
相同版本的MySQL数据库之间的迁移；
这种迁移的方式最容易实现，因为迁移前后MySQL数据库的主本版号相同，
所以可以通过复制数据库目录来实现数据库迁移。只有数据库表都是MyISAM类型的才能使用这种方式。
最常用和最安全的方式是使用mysqldump命令来备份数据库。然后使用mysql命令将备份文件还原到新的MySQL数据库中。这里可以将备份和迁移同时进行。
mysqldump –h name1 –u root –password=password1 –all-databases |
mysql –h host2 –u root –password=password2
其中，“|”符号表示管道，其作用是将mysqldump备份的文件送给mysql命令
不同数据库之间的迁移；
MySQL以外的数据库也有类似mysqldump这样的备份工具，可以将数据库中的文件备份成sql文件或普通文件。
但是，因为不同数据库厂商没有完全按照SQL标准来设计数据库。这就造成了不同数据库使用的SQL语句的差异。
例如，微软的SQL Server软件使用的是T-SQL语言。T-SQL中包含了非标准的SQL语句。
这就造成了SQL Server和MySQL的SQL语句不能兼容。除了SQL语句存在不兼容得情况下，不同的数据库之间的数据类型也有差异。
用SELECT …INTO OUTFILE导出文本文件
SELECT[列名] FROM table[WHERE语句]
INTO OUTFILE '目标文件' [OPTION];
FIELDS TERMINATED BY‘字符串’：设置字符串为字段的分隔符，默认值是“\t”；
FIELDS ENCLOSED BY‘字符’：设置字符来括上字段的值。默认情况下不使用任何符号；
FIELDS OPTIOINALLY ENCLOSED BY‘字符’：设置字符来括上CHAR、VARCHAR、和TEXT等字符型字段。默认情况下不使用任何符号；
FIELDS ESCAPED BY‘字符’：设置转义字符，默认值为“\”;
LINES STARTING BY‘字符串’：设置每行开头的字符，默认情况下无任何字符；
LINES TERMINATED BY‘字符串’：设置每行的结束符，默认值是“\n”；
其中，字段之间用“、”隔开，字符型数据用双引号括起来。每条记录以“>”开头。在MySQL的命令行窗口中输入以下命令。
USE db_librarybak
SELECT * FROM tb_bookinfo INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/bookinfo.txt'
FIELDS TERMINATED BY '\、' OPTIONALLY ENCLOSED BY '\"'
LINES STARTING BY '\>' TERMINATED BY '\r\n';
用mysqldump命令导出文本文件
mysqldump –u root –pPassword –T "目标目录" dbname table [option];
其中，Password参数表示root用户的密码，密码紧挨着-p选项；目标目录参数时指导出的文本文件的路径；dbname参数表示数据库的名称；table参数表示表的名称；option表示附件选项。这些选项介绍如下：
--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是“\t”；
--fields-enclosed-by=字符：设置字符来括上字段的值；
--fields-optionally-enclosed-by=字符：设置字符括上CHAR、VARCHAR和TEXT等字符型字段；
--fields-escaped-by=字符：设置转义字符；
--lines-terminated-by=字符串：设置每行的结束符。
mysqldump -u root -p --default-character-set=gbk -T "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/" db_librarybak tb_bookinfo "--lines-terminated-by=\r\n" "--fields-terminated-by=、" "--fields-optionally-enclosed-by=""
用mysql命令导出文本文件
mysql –u root –pPassword –e"SELECT 语句" dbname >D:/name.txt
优化
用SHOW STATUS语句查询MySQL数据库的性能；
SHOW STATUS LIKE ‘value’;
其中，value参数时常用的几个统计参数
Connections：连接MySQL服务器的次数；
Uptime：MySQL服务器的上线时间；
Slow_queries：慢查询的次数；
Com_select：查询操作的次数；
Com_insert：插入操作的次数；
Com_delete：删除操作的次数。
例如，查询MySQL服务器的连接次数，SHOW STATUS LIKE ‘Connections’;
通过profile工具分析语句消耗性能，默认情况下，未开启profile工具。下面的命令查看profile工具是否开启，如果想要开启，可以将profiling设置为1，SET profiling=1;
SHOW VARIABLES LIKE '%pro%';
使用方法；SHOW profiles;
分析查询语句；
EXPLAIN SELECT * FROM tb_bookinfo;
其中各字段所代表的意义如下所示：
id列：指出在整个查询中SELECT的位置。
table列：存放这、所查询的表名。
type列：连接类型，该列中存储很多值，范围从const到ALL。
possible_keys列：指出为了提高查找速度，在MySQL中可以使用的索引。
key列：指出实际使用的键。
rows列：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到该总行数，MySQL必须扫描处理整个查询 ，再成一每个表的行值。
Extra列：包含一些其他信息，设计MySQL如何处理查询。
DESCRIBE语句的使用方法与EXPLAIN语法是相同的，这两者的分析结果也大体相同。“DESCRIBE”可以缩写成“DESC”。
DESCRIBE SELECT * FROM tb_bookinfo;
LIKE的优化；
第一个字符为百分号“%”时，索引不会被使用，如果“%”所在匹配字符串中的位置不是第一位置，则索引会被正常使用，如，EXPLAIN SELECT * FROM tb_bookinfo WHERE bookname LIKE 'Java Web%';
OR关键字优化；
在MySQL中，查询语句只有包含OR关键字时，要求查询的两个字段必须同为索引，如果所搜索的条件中，有一个字段不为索引，则在查询中不会应用索引进行查询。
优化数据库结构；
1，将字段很多的表分解成多个表；
有些表在设计时设置了很多的字段。这个表中有些字段的使用频率很低。当这个表的数据量很大时，查询数据的速度就会很慢。
2，增加中间表；
有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。对于这种情况，可以建立中间表来提高查询速度。
先分析经常需要同时查询哪几个表中的哪些字段。然后将这些字段建立一个中间表，并将原来那几个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计。
3，优化插入记录的速度；
（1）插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时，这些排序会降低插入记录的速度。为了解决这种情况，在插入记录之前先禁用索引。等到记录都插入完毕后再开启索引。禁用索引的语句如下：
ALTER TABLE 表名 DISABLE KEYS;
重新开启索引的语句如下：
ALTER TABLE 表名 ENABLE KEYS;
对于新创建的表，可以先不创建索引。等到记录都导入以后再创建索引。这样可以提高导入数据的速度。
（2）插入数据时，MySQL会对插入的记录进行校验。这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下：
SET UNIQUE_CHECKS=0;
重新开启唯一性检查的语句如下：
SET UNIQUE_CHECKS=1;
（3）优化INSERT语句；当插入大量数据时，建议使用一个INSERT语句插入多条记录的方式。
而且，如果能用LOAD DATA INFILE语句，就尽量用LOAD DATA INFILE语句。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句的速度快。
4，优化多表查询；
执行子查询的时间比执行多表连接查询的时间要少很多。
5，优化表设计；
设计数据表时应优先考虑使用特定字段长度，后考虑使用变长字段。将字段长度设置成其可能应用的最大范围可以充分的优化查询效率。
数据库表的类型InnoDB或BDB表处理行存储与MyISAM或ISAM表的情况不同。在InnoDB或BDB类型表中使用定长列，并不能提高其性能。
锁
1，MyISAM表的表级锁；
（1）为指定数据表添加锁定。其语法如下：
LOCK TABLES table_name lock_type,…
其中table_name为被锁定的表名，lock_type为锁定类型，该类型包括以读方式（READ）锁定表； 以写方式（WRITE）锁定表。
（2）用户执行数据表的操作，可以添加、删除或者更改部分数据。
（3）用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态。其语法如下：
UNLOCK TABLES
2，InnoDB表的行级锁；
为InnoDB表设置锁比为MyISAM表设置锁更为复杂，这是因为InnoDB表即支持表级锁，又支持行级锁。
由于为InnoDB表设置表级锁也是使用LOCK TABLES命令，其使用方法同MyISAM表基本相同。
在InnoDB表中，提供了两种类型的行级锁，分别是读锁（也称为共享锁）和写锁（也称为排他锁）。
InnoDB表的行级锁的粒度仅仅是受查询语句或者更新语句影响的记录。
为InnoDB表设置行级锁主要分为以下3种方式。
在查询语句中设置读锁，其语法格式如下：
SELECT语句 LOCK IN SHARE MODE;
例如，为采用InnoDB存储引擎的数据表tb_account在查询语句中设置读锁，可以使用下面的语句。
SELECT * FROM tb_account LOCK IN SHARE MODE;
在查询语句中设置写锁，其语法格式如下：
SELECT语句 FOR UPDATE;
例如，为采用InnoDB存储引擎的数据表tb_account在查询语句中设置写锁，可以使用下面的语句。
SELECT * FROM tb_account FOR UPDATE;
在更新（包括INSERT、UPDATE和DELTET）语句中，InnoDB存储引擎自动为更新语句影响的记录添加隐式写锁。
通过以上3种方式为表设置行级锁的生命周期非常短暂。为了延长行级锁的生命周期，可以采用开启事务实现。
3，设置事务的隔离级别；
在MySQL中，可以通过执行SET TRANSACTION ISOLATION LEVEL命令设置事务的隔离级别。新的隔离级别将在下一个事务开始时生效。
SET {GLOBAL|SESSION} TRANSACTION ISOLATION LEVEL 具体级别;
其中，具体级别可以是SERIALIZABLE、REPEATABLE READ、READ COMMITTED或者READ UNCOMMITTED，分别表示对应的隔离级别。
例如，将事务的隔离级别设置为读取已提交数据，并且只对当前会话有效，可以使用下面的语句。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
