MYSQL（二） - 基本SQL操作

2015/10/03 共 3232 字 10 分钟阅读
DATABASE mysql

AI 摘要: SQL中的预处理语句（Prepare）可以减少每次执行时解析语句的开销，防止SQL注入攻击。预处理语句的SQL语法包括PREPARE、EXECUTE和DEALLOCATE PREPARE三个语句。预处理语句可以在存储过程中使用，但不能在存储的函数或触发器中使用，也不支持多语句（即，由;字符分隔的单个字符串中的多个语句）

SQL: 结构化查询语言，是执行数据库操作的标准语言。通常分为DDL(数据定义)、DML(数据管理)和Select查询类别。
MySQL包括一些其他语句类别，如复制。
与SQL其他相关性内容：语言结构、数据类型、函数和操作

示例

1. Prepare语句

Prepare预处理语句，解决了Mysql重复执行相同语句，不同参数值的解析开销；
SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的。对于sql执行时间较短，sql硬解析的时间占总执行时间的比率越高。
oracle的prepare实际上包括以下阶段：词法分析->语法分析->语义分析->执行计划优化，也就是说oracle的prepare做了更多的事情，execute只需要执行即可。因此，oracle的prepare比mysql更高效。

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

Prepare 接收客户端带?的sql, 硬解析得到语法树(stmt->Lex), 缓存在线程所在的preparestatement cache中。此cache是一个HASH MAP. Key为stmt->id. 然后返回客户端stmt->id等信息。
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语句

PREPARE 准备一份执行语句
EXECUTE 执行准备好的语句
DEALLOCATE PREPARE 释放预准备语句（如果不手动释放，当会话终止，服务器会自动解除分配）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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. 导入、导出

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

1
2
3
4
5
6
7
8
9
//导出(含数据）
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语法导入同时做对应数据处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
//导出
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. 显示信息

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// 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 KEY或UNIQUE索引的新行具有相同的值，否则在插入新行之前将删除旧行
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

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

6.1 其他注意：

如果开启了严格SQL模式，在插入时候对待未设定默认值，同时也没有显示插入赋值的，严格模式会报错；
使用关键字DEFAULT将列显式设置为其默认值，避免编写VALUES不包含表中每列的值的不完整列表；
表达式默认值： DEFAULT(col_name)col_name；
VALUE是VALUES同义词；
插入行数统计，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 简单示例：

1
2
3
4
5
6
7
8
// 支持引用， 但不能(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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
// 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 概览

例程名称与内置SQL函数的名称相同，则会发生语法错误
对于PROCEDURE，将参数指定为IN,OUT或INOUT
对于FUNCTION，参数始终被视为IN参数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// 创建存储过程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：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 存储过程定义
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：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
// 存储过程定义
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函数执行操作，并返回结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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)

相关内容

示例