SQL学习

一、分区表

1.1、分区的类型

 1.2、分区表的原数据

(root@localhost) [dbt3]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
(root@localhost) [information_schema]> desc PARTITIONS;
+-------------------------------+---------------------+------+-----+---------+-------+
| Field                         | Type                | Null | Key | Default | Extra |
+-------------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG                 | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)         | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)         | NO   |     |         |       |
| PARTITION_NAME                | varchar(64)         | YES  |     | NULL    |       |
| SUBPARTITION_NAME             | varchar(64)         | YES  |     | NULL    |       |
| PARTITION_ORDINAL_POSITION    | bigint(21) unsigned | YES  |     | NULL    |       |
| SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES  |     | NULL    |       |
| PARTITION_METHOD              | varchar(18)         | YES  |     | NULL    |       |
| SUBPARTITION_METHOD           | varchar(12)         | YES  |     | NULL    |       |
| PARTITION_EXPRESSION          | longtext            | YES  |     | NULL    |       |
| SUBPARTITION_EXPRESSION       | longtext            | YES  |     | NULL    |       |
| PARTITION_DESCRIPTION         | longtext            | YES  |     | NULL    |       |
| TABLE_ROWS                    | bigint(21) unsigned | NO   |     | 0       |       |
| AVG_ROW_LENGTH                | bigint(21) unsigned | NO   |     | 0       |       |
| DATA_LENGTH                   | bigint(21) unsigned | NO   |     | 0       |       |
| MAX_DATA_LENGTH               | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH                  | bigint(21) unsigned | NO   |     | 0       |       |
| DATA_FREE                     | bigint(21) unsigned | NO   |     | 0       |       |
| CREATE_TIME                   | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME                   | datetime            | YES  |     | NULL    |       |
| CHECK_TIME                    | datetime            | YES  |     | NULL    |       |
| CHECKSUM                      | bigint(21) unsigned | YES  |     | NULL    |       |
| PARTITION_COMMENT             | varchar(80)         | NO   |     |         |       |
| NODEGROUP                     | varchar(12)         | NO   |     |         |       |
| TABLESPACE_NAME               | varchar(64)         | YES  |     | NULL    |       |
+-------------------------------+---------------------+------+-----+---------+-------+
25 rows in set (0.00 sec)

1.3、分区规则

分区列必须是唯一索引的组成部分

二、表结构范式

范式：数据库范式是一个可以帮助避免数据异常，和数据管理时候出现其他问题的技术，他由各阶段的表转化组成：第一范式、第二范式、第三范式；

范式的目的：

               消除数据冗余； 

               使数据的更改更容易，而且避免这样做的时候出现异常；

               更容易执行一致性约束；

               产生一个更容易理解的结构；

 三、SQL语句

用于排序的内存

(root@localhost) [db1]> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.06 sec)

配置文件配置sort_buffer_size大小

#session memory
sort_buffer_size=32M

3.1、Group by

SELECT
    DATE_FORMAT(o_orderDATE, '%Y%m'),
    sum(o_totalprice)
FROM
    orders
GROUP BY
    DATE_FORMAT(o_orderDATE, '%Y%m');

每个员工，每个月的订单总量，及平均值

SELECT
    DATE_FORMAT(o_orderDATE, '%Y-%m'),
    count(1),
    o_clerk,
    sum(o_totalprice),
    avg(o_totalprice)
FROM
    orders
GROUP BY
    DATE_FORMAT(o_orderDATE, '%Y-%m'),
    o_clerk;

查询过程中，查看系统产生基于磁盘的表

(root@localhost) [dbt3]> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 25    |
| Created_tmp_tables      | 7     |
+-------------------------+-------+
3 rows in set (0.01 sec)

将排序临时数据表空间的设置写入配置文件，空间足够，表将不在写入磁盘

tmp_table_size=32M

3.2、count(*)

(root@localhost) [db1]> select * from f;
+------+
| aa   |
+------+
|    1 |
|    3 |
|    5 |
| NULL |
+------+
4 rows in set (0.00 sec)

(root@localhost) [db1]> select count(1),count(*),count(100),count(aa) from f;
+----------+----------+------------+-----------+
| count(1) | count(*) | count(100) | count(aa) |
+----------+----------+------------+-----------+
|        4 |        4 |          4 |         3 |
+----------+----------+------------+-----------+
1 row in set (0.01 sec)

3.3、group_concat函数，将分组以外的列，列出来

SELECT
    o_clerk,
    GROUP_CONCAT(o_orderstatus)
FROM
    orders
GROUP BY
    o_clerk;

默认使用逗号分割，可以自己定义

SELECT
    o_clerk,
    GROUP_CONCAT(o_orderstatus SEPARATOR ':')
FROM
    orders
GROUP BY
    o_clerk;

 3.4、子查询

select * from orders where o_orderpriority in ('5-LOW','2-HIGH');

 3.5、SQL中设置变量

set @a:=0;
select @a:=@a+1,emp_no,birth_date,first_name,last_name from employees limit 10;

四、Prepared Statement

MySQL 5.7 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol. Using prepared statements with placeholders（占位符） for parameter（参数） values has the following benefits:

 MySQL 5.7 提供服务器端的。这个功能利用高效的客户端/服务器二进制协议。使用带参数占位符的存储过程有一下优势：

 

• Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical（几乎相同） statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

• Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

         减少每次语句执行的语法分析。典型的，数据库应用程序处理大量几乎相同的语句，Prepare可以只更改字符值或者变量值，比如查询或者删除的WHERE子句，或者update中的SET变量，insert语句中的VALUES值；

        防范SQL注入攻击，存储过程的定义的值可以包含未转义的SQL引号和分隔符；

The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:

(root@localhost) [(none)]> prepare stmt1 from 'select sqrt(pow(?,2)+pow(?,2)) as hypotenuse';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

(root@localhost) [(none)]> set @a=3;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> set @b=4;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> execute stmt1 using @a,@b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> DEALLOCATE PREPARE stmt1;

SQL语句作为变量

SET @s = 'select * from employees where 1=1';
SET @s = concat(@s, ' and gender="m"');
SET @s = concat(@s,    ' and birth_date >= "1960-01-01"');

PREPARE stmt FROM    @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 五、一个简单的SQL注入

select * from employees where emp_no=10001 or 1=1;

可以查到全部的表记录

用存储过程避免SQL注入

(root@localhost) [employees]> set @s='select * from employees where emp_no=?';
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [employees]>  set @a='10001 or 1=1';
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [employees]> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

(root@localhost) [employees]> execute stmt using @a;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

 六、DML语句

insert，插入如果有重复的语句

insert into t6 values(2) on DUPLICATE key update id=id+10;

 七、临时表

创建临时表

(root@localhost) [db1]> create temporary table a(id int);
Query OK, 0 rows affected (0.00 sec)

临时表是会话级别的，回话退出，表就不存在了；