数据库学习笔记（SQL语句）_数据库笔记sql语句-CSDN博客

本文链接：https://blog.csdn.net/fatever/article/details/73504109

根据已有的表或查询结果来创建表：

- CREATE TABLE ... LIKE会根据原有表创建一个新表。该语句会完整的复制原有表的结构以创建一个新的空表。如果想插入数据，还需要另外的插入语句（如INSERT INTO ... SELECT）但它不能只选原表的某几列或其他表中的列。
- CREATE TABLE ... SELECT可以根据一条SELECT语句的查询结构创建一个新表并把数据填入表中。这种做法不会把原有表里的任何索引复制过去，也不会复制如AUTO_INCREMENT等列属性。

如果要尝试修改表的内容，也不想直接改变原有表的内容，可以使用TEMPORARY表（但某些情况下，如连接池或永久性连接等机制会在你应用程序终止时，让你与MySQL服务器之间的连接保持打开。这时TEMPORARY表不会自动消失）。

强制类型转换

在利用CREATE TABLE ... SELECT或INSERT ... FROM时，可能会希望对数据的类型进行一些变更。这时候可以使用CAST()函数，例如：

 CREATE TABLE mytable 
 ( i INT UNSIGNED, t TIME, d DECIMAL(10,5))
 SELECT
 1 AS i
 CAST( CURTIME() AS TIME) AS t,
 CAST( PI() AS DECIMAL(10,5)) AS d;

为了使表的列与外来数据匹配（与位置无关），在SELECT部分提供别名是必须的。

分区表

MySQL可以支持让表的内容分散存储到不同的物理位置中，得到分区表，从而在搜索和访问时提高效率。
创建分区表时除了提供CREATE TABLE语句外，还要指定PARTITION BY子句（它定义一个可以把行分配到各个分区的分区函数）。分区函数可以根据范围、值列表、散列值来分配各行。例如创建一个根据年份分区的表：

CREATE TABLE time_log ( mydate  DATETIME NOT NULL )
PARTITION BY RANGE( YEAR( mydate ))
(
PARTITION p0 VALUES LESS THAN (2010),
PARTITION p1 VALUES LESS THAN (2011),
PARTITION pleft VALUES LESS MAXVALUE
);

这样在2011年以后的分区都会被分到pleft分区。另外，如果有需要，还可以用ALTER TABLE ... REORGANIZE PARTITON ... INTO(...)的方式来对某个分区进一步划分。
默认情况下，MySQL会将分区存储在专属于分区表的数据库目录里。若想将存储分散到指定地方，则需要使用分区选项DATA_DIRECTORY和INDEX_DIRECTORY。

索引

可以对同一个表创建多个索引。
索引可以只包含唯一值（唯一索引），也可以包含重复值（常规索引）
对于ENUM和SET以外的字符串类型，可以只对列名左边的n个字符或n个字节创建索引（尤其是对于BLOB或TEXT类型）。
FULLTEXT索引里的列是以满列值的方式进行索引的，不能进行前缀索引。即使指定了前缀索引也会被忽略。
PRIMARY KEY和UNIQUE索引的区别：
- 每个表只能包含一个PRIMARY KEY，因为PRIMARY KEY的索引名字总是“PRIMARY”，而其他的索引（包括UNIQUE）可以被自定义名字。
- PRIMARY KEY不可以包含NULL值，而UNIQUE索引可以，且可以包含多个NULL值，因为NULL值不会与任何值相等。

CREATE INDEX和DROP INDEX在内部都会被自动处理成ALTER TABLE语句。所以下面两句命令是等价的：

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

更改表结构

更改列的数据类型/字符集：可以使用CHANGE或MODIFY子句。

ALTER TABLE table MODIFY col_name ...    //...输入新的数据类型
ALTER TABLE table CHANGE col_name new_col_name ...  //CHANGE可以同时将列重命名

更改存储引擎：

ALTER TABLE table ENGINE = engine_name;

重新命名表：

ALTER TABLE table RENAME TO new_name;
RENAME TABLE t1 TO t1_new, t2 TO t2_new;

多表检索

内连接：即生成笛卡尔积的过程，将某个表的每一行与另一个表的而每一个行的所有可能组合进行连接。
CROSS JOIN 和JOIN以及连接运算符,（逗号）都等同于INNER JOIN（不过逗号的优先级和其他连接类型不同，有时可能导致错误，应尽量避免使用逗号运算符）。
如果增加WHERE（或ON）子句，实现表之间的某些列值匹配规则，可以将结果集减少到一个更适合管理的大小。
左（外）连接和右（外）连接
外连接除了显示同样的匹配结果（匹配方法不仅限于相等），还可以把其中一个表里没有匹配的行也显示出来。例如：左连接会把左表里未与右表匹配的行也显示在结果中，右连接则相反。
外连接必须使用ON子句来进行连接匹配（而内连接可以不进行匹配）。如果想对连接结果进行筛选，可以再使用WHERE子句。例如：下面语句可以将左表里那些在右表里无匹配的行找出来：
```
SELECT ... FROM t1 LEFT t2 ON...WHERE t2.col IS NULL    //要求t2.col本身没有NULL值。
```
NATURAL LEFT JOIN会按LEFT JOIN规则对左右两个表里的所有同名的列进行匹配（因此它不能也不需要制定ON或USING子句）。

子查询

子查询是指：用括号括起来，并嵌入另一条语句里的那条SELECT语句。一般在WHERE或FROM中。

在外层查询里使用关系比较运算符=、<>、>、>=等时，比较对象（子查询结果）必须只产生一个值。也就是说，它是个标量子查询。有时候可以使用LIMIT 1来限制子查询结果。如果子查询返回的返回的是一个行（有多个列的数据），可以用行构造器ROW(col1，col2...)来实现一组值与子查询结果的比较。如WHERE ROW(firstname, lastname) = (SELECT子句)。
IN和NOT IN：当子查询返回多个行时，可以使用运算符IN和NOT IN来确定某个给定的值是否存在于某一组值里。IN和NOT IN其实是= ANY和<> ALL的同义词。
ALL、ANY和SOME：通常与某个关系比较运算符一起使用。ALL要求比较值与每一个子查询结果相比时都为真；ANY则要求比较值与任意子查询结果返回值之间的关系满足即可。SOME是ANY的同义词。
EXISTS和NOT EXISTS：只测试子查询是否返回了行。如果有则EXISTS为真。
相关子查询：不相关的子查询自己可以作为一条单独的查询命令执行。而相关的子查询中，子查询引用了外层查询里的值。

`UNION`实现多表整合

UNION需要写出多条SELECT语句，然后把UNION放到它们中间。每条SELECT语句必须检索出相同数量的列。如果一条UNION语句中各相应列的数据类型不一样，MySQL会进行必要的类型转换。
默认情况下，UNION会将重复行剔除掉。如果想保留，则需要使用UNION ALL
如果想将UNION结果作为整体进行排序，那么需要用括号把每一个SELECT子句括起来。