MYSQL(二) - SQL

SQL: 结构化查询语言,是执行数据库操作的标准语言。通常分为DDL(数据定义)、DML(数据管理)和Select查询类别 。

MySQL包括一些其他语句类别,如复制。

与SQL其他相关性内容:语言结构、数据类型、函数和操作

相关内容

  • SQL语法:https://dev.mysql.com/doc/refman/8.0/en/sql-syntax.html
    • 13.1 Data Definition Statements
    • 13.2 Data Manipulation Statements
    • 13.3 Transactional and Locking Statements
    • 13.4 Replication Statements
    • 13.5 Prepared SQL Statement Syntax
    • 13.6 Compound-Statement Syntax
    • 13.7 Database Administration Statements
    • 13.8 Utility Statements
  • 数据类型:https://dev.mysql.com/doc/refman/8.0/en/data-types.html
    • 11.1 Data Type Overview
    • 11.2 Numeric Types
    • 11.3 Date and Time Types
    • 11.4 String Types
    • 11.5 Spatial Data Types
    • 11.6 The JSON Data Type
    • 11.7 Data Type Default Values
    • 11.8 Data Type Storage Requirements
    • 11.9 Choosing the Right Type for a Column
    • 11.10 Using Data Types from Other Database Engines
  • 语言结构:https://dev.mysql.com/doc/refman/8.0/en/language-structure.html
    • 9.1 Literal Values
    • 9.2 Schema Object Names
    • 9.3 Keywords and Reserved Words
    • 9.4 User-Defined Variables
    • 9.5 Expressions
    • 9.6 Comment Syntax
  • 函数和操作:https://dev.mysql.com/doc/refman/8.0/en/functions.html
    • 12.1 Function and Operator Reference
    • 12.2 Type Conversion in Expression Evaluation
    • 12.3 Operators
    • 12.4 Control Flow Functions
    • 12.5 String Functions
    • 12.6 Numeric Functions and Operators
    • 12.7 Date and Time Functions
    • 12.8 What Calendar Is Used By MySQL?
    • 12.9 Full-Text Search Functions
    • 12.10 Cast Functions and Operators
    • 12.11 XML Functions
    • 12.12 Bit Functions and Operators
    • 12.13 Encryption and Compression Functions
    • 12.14 Locking Functions
    • 12.15 Information Functions
    • 12.16 Spatial Analysis Functions
    • 12.17 JSON Functions
    • 12.18 Functions Used with Global Transaction Identifiers (GTIDs)
    • 12.19 MySQL Enterprise Encryption Functions
    • 12.20 Aggregate (GROUP BY) Functions
    • 12.21 Window Functions
    • 12.22 Performance Schema Functions
    • 12.23 Internal Functions
    • 12.24 Miscellaneous Functions
    • 12.25 Precision Math

示例

1. Prepare语句

Prepare预处理语句,解决了Mysql重复执行相同语句,不同参数值的解析开销;

SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词,语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex)。对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化,多次查询执行时间可能不同,但硬解析的时间是不变的。对于sql执行时间较短,sql硬解析的时间占总执行时间的比率越高。

oracle的prepare实际上包括以下阶段:词法分析->语法分析->语义分析->执行计划优化,也就是说oracle的prepare做了更多的事情,execute只需要执行即可。因此,oracle的prepare比mysql更高效。

Prepare在服务器端的执行过程如下:

  1. Prepare 接收客户端带?的sql, 硬解析得到语法树(stmt->Lex), 缓存在线程所在的preparestatement cache中。此cache是一个HASH MAP. Key为stmt->id. 然后返回客户端stmt->id等信息。
  2. Execute 接收客户端stmt->id参数信息,客户端不需要再发sql过来。服务器根据stmt->id在preparestatement cache中查找得到硬解析后的stmt, 并设置参数,就可以继续后面的优化和执行了

系统变量max_prepared_stmt_count,可以设置最大预处理sql条数,要防止使用预准备语句,请将值设置为0;

  • 预准备语句的SQL语法可以在存储过程中使用,但不能在存储的函数或触发器中使用。
  • 预处理语句的SQL语法不能以嵌套方式使用(不能Prepare嵌Prepare);
  • 预处理语句的SQL语法不支持多语句(即,由;字符分隔的单个字符串中的多个语句 )。

1.1 预处理语句好处

  • 每次执行时解析语句的开销更少。通常,数据库应用程序处理大量几乎相同的语句,只更改子句中的文字或变量值,例如WHERE查询和删除,SET更新和VALUES插入。
  • 防止SQL注入攻击。参数值可以包含未转义的SQL引号和分隔符。

1.2 预处理语句的SQL语法基于三个SQL语句

  1. PREPARE 准备一份执行语句
  2. EXECUTE 执行准备好的语句
  3. DEALLOCATE PREPARE 释放预准备语句 (如果不手动释放,当会话终止,服务器会自动解除分配)
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

2. 导入、导出

它不是用于备份大量数据的快速或可扩展的解决方案,更大的数据优先考虑物理备份;

//导出(含数据)
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

$ mysqldump test_db > /tmp/test_db.sql

//导入
shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

基于LOAD语法导入同时做对应数据处理

//导出
SELECT ... INTO OUTFILE
//选定的值将分配给变量。
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

//导入
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

3. 显示信息

用来快速查看哪些数据库中存在,他们的表或表中的列或索引。

// mysqlshow
shell> mysqlshow [options] [db_name [tbl_name [col_name]]]

$ mysqlshow -v
+--------------------+--------+
|     Databases      | Tables |
+--------------------+--------+
| information_schema |     65 |
| mysql              |     33 |
| performance_schema |    102 |
| sys                |    101 |
| test_db            |      1 |
+--------------------+--------+

// show
mysql> SHOW MASTER LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |   3071654 |
| binlog.000002 |       178 |
| binlog.000003 |      3498 |
+---------------+-----------+

常见SHOW的内容:

  • SHOW {BINARY | MASTER} LOGS
  • SHOW CHARACTER SET [like_or_where]
  • SHOW COLLATION [like_or_where]
  • SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
  • SHOW CREATE DATABASE db_name
  • SHOW CREATE FUNCTION func_name
  • SHOW CREATE PROCEDURE proc_name
  • SHOW CREATE TABLE tbl_name
  • SHOW CREATE TRIGGER trigger_name
  • SHOW CREATE VIEW view_name
  • SHOW DATABASES [like_or_where]
  • SHOW ENGINE engine_name {STATUS | MUTEX}
  • SHOW [STORAGE] ENGINES
  • SHOW GRANTS FOR user
  • SHOW INDEX FROM tbl_name [FROM db_name]
  • SHOW MASTER STATUS
  • SHOW OPEN TABLES [FROM db_name] [like_or_where]
  • SHOW PLUGINS
  • SHOW PROCEDURE CODE proc_name
  • SHOW PROCEDURE STATUS [like_or_where]
  • SHOW PRIVILEGES
  • SHOW [FULL] PROCESSLIST
  • SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
  • SHOW PROFILES
  • SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
  • SHOW SLAVE HOSTS
  • SHOW SLAVE STATUS [FOR CHANNEL channel]
  • SHOW [GLOBAL | SESSION] STATUS [like_or_where]
  • SHOW TABLE STATUS [FROM db_name] [like_or_where]
  • SHOW [FULL] TABLES [FROM db_name] [like_or_where]
  • SHOW TRIGGERS [FROM db_name] [like_or_where]
  • SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

4. 数据定义 - DDL

  • DDL涉及对象有:库、表、视图、索引、触发器、函数、存储过程等
  • DDL涉及操作有:CREATE,ALTER,DROP
  • 表方面还有:
    • RENAME
    • TRUNCATE

5. 数据操作 - DML

DML涉及操作有:

  • CALL:存储过程调用
  • SELECT
    • SELECT … INTO
    • JOIN
    • UNION
  • INSERT
    • INSERT … SELECT
    • INSERT … ON DUPLICATE KEY UPDATE
    • INSERT DELAYED
  • DELETE
  • REPLACE:REPLACE与INSERT类似,实现inserts或者deletes+inserts,除非表中的旧行与PRIMARY KEYUNIQUE索引的新行具有相同的值,否则在插入新行之前将删除旧行
  • DO:仅执行
  • IMPORT_TABLE:导入记录
  • LOAD DATA:以非常高的速度将文本文件中的行读入表中
  • LOAD XML:语句将XML文件中的数据读入表中。

注:IMPORT TABLE仅适用于非TEMPORARY MyISAM,不适用与事务性存储引擎,视图以及临时表

6. MYSQL服务管理

  • 账号管理: SQL 账号管理
  • 组资源管理
  • 表分析和维护
  • 用户自定义函数
  • SET变量设置
  • SHOW语句:SQL SHOW语句
  • 其他管理语句:
    • BINLOG
    • KILL
    • FLUSH
    • CACHE INDEX
    • RESET MASTER/SLAVE
    • SHUTDOWN
  • 实用语句:
    • DESCRIBE
    • EXPLAIN
    • HELP
    • USE
  • 主从同步复制:
    • 控制MASTER
    • 控制SLAVE
    • 控制组复制

6. INSERT SQL

  1. 插入基于明确指定的值:
    • INSERT … VALUES
    • INSERT … SET
  2. 插入从另一个或多个表中选择行:
    • INSERT … SELECT
  3. 如果插入的行存在唯一索引重复问题:
    • INSERT … ON DUPLICATE KEY UPDATE

6.1 其他注意:

  • 如果开启了严格SQL模式,在插入时候对待未设定默认值,同时也没有显示插入赋值的,严格模式会报错;
  • 使用关键字DEFAULT将列显式设置为其默认值,避免编写VALUES不包含表中每列的值的不完整列表;
  • 表达式默认值: DEFAULT(col_name)col_name
  • VALUEVALUES同义词;
  • 插入行数统计,SQL函数:ROW_COUNT()或C:mysql_affected_rows()
  • 插入一个字符串超过字符串列(CHAR, VARCHAR, TEXT,或 BLOB)最大长度,该值将截断为列的最大长度;
  • 将NULL插入已声明为NOT NULL的列中,将使用列数据类型的默认值;
  • 可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id()C API函数找到AUTO_INCREMENT该列的值;
  • 插入重复的UNIQUE索引或 PRIMARY KEY值的行会导致重复键错误

6.2 简单示例:

// 支持引用, 但不能(col2*2,15)
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
    
// 插入多行
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
    
// 插入值列表与插入的值必须一致对应,以下错误
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

6.3 理解插入执行过程,所需的时间:

  • 连接服务:Connecting: (3)
  • 发送查询:Sending query to server: (2)
  • 解析查询:Parsing query: (2)
  • 插入行:Inserting row: (1 × size of row)
  • 插入索引:Inserting indexes: (1 × number of indexes)
  • 关闭连接:Closing: (1)

需要考虑连接后,打开表的初始开销,每个并发运行的查询都会执行一次。

假设使用B树索引,表的大小减慢了log N插入索引的速度。

6.4 插入优化:

  • 将许多小型操作组合到一个大型操作中,使用单个连接,一次发送许多新行的数据,并将所有索引更新和一致性检查延迟到最后。(使用INSERT 包含多个VALUES列表的语句一次插入多行)
  • 如果要将数据添加到非空表中,则可以调整bulk_insert_buffer_size变量以使数据插入更快;
  • 使用LOAD DATA从文本文件加载表时,比使用INSERT语句快20倍;
  • 充分利用列具有默认值,仅在要插入的值与默认值不同时才显式插入值,可以减少了MySQL的解析并提高了插入速度;
  • 针对INNODB表,大多数可以先临时关闭索引,导入后再开启:
    • 在导入操作期间禁用自动提交,因为它会为每个插入执行磁盘日志刷新;
    • 临时关闭UNIQUE KEY检查,节省大量磁盘I/O;
    • 关闭外键FOREIGN KEY检查来加速表导入,节省大量磁盘I/O;
    • 多行语法来减少客户端和服务器之间的通信开销;(使用INSERT 包含多个VALUES列表的语句一次插入多行)
    • 执行批量插入时,按PRIMARY KEY顺序插入行会更快
    • innodb_autoinc_lock_mode自动插入锁模式调整
    • FULLTEXT:先创建无索引表,导入数据,然后再加上索引;

6.5 REPLACE

// version 1 (主键为id)
CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

// version 2 (主键为id,ts
CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

7. 存储过程、函数

7.1 概览

  1. 例程名称与内置SQL函数的名称相同,则会发生语法错误
  2. 对于PROCEDURE,将参数指定为IN,OUT或INOUT
  3. 对于FUNCTION,参数始终被视为IN参数
// 创建存储过程sp
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type
    
routine_body:
    Valid SQL routine statement    

// 创建存储函数sf
CREATE
    [DEFINER = user]
    FUNCTION sf_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
// 要调用存储过程,使CALL语句,可以不带括号
CALL sp_name([parameter[,...]])
CALL sp_name[()]

7.2 简单存储过程示例

  • 使用MySQL的客户机delimiter命令语句从定界符改变;//,当正在定义的存储过程,使得过程体中的;得以被传递到服务器,而不是由mysql本身解释;

  • 示例1:

// 存储过程定义
mysql> delimiter //
    
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
   -> BEGIN
   ->   SELECT COUNT(*) INTO param1 FROM t;
   -> END//
Query OK, 0 rows affected (0.00 sec)
    
mysql> delimiter ;
    
// 存储过程调用
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
    
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)
  • 示例2:
// 存储过程定义
CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
 # Set value of OUT parameter
 SELECT VERSION() INTO ver_param;
 # Increment value of INOUT parameter
 SET incr_param = incr_param + 1;
END;
    
// 存储过程调用
mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------------+------------+
| @version           | @increment |
+--------------------+------------+
| 8.0.3-rc-debug-log |         11 |
+--------------------+------------+

7.3 函数接受一个参数,使用SQL函数执行操作,并返回结果

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)