SQL进阶

约束关键字

主键约束：primary key    每个表中要有一个主键。  特点：数据唯一，且不能为 null。

唯一约束：unique [key]  特点：数据不能重复。可以为null，但只能有一个。

非空约束： not null          特点：用非空约束的列不能为null.

默认约束：default           特点：可以给一个列设置默认的值，在用户未输入时，会使用默认的值。

自动增长：auto_increment    特点：给主键添加自动增长的数值，列只能是整数类型 。

外键约束: foreign key        特点：外键列的数据类型一定要与主键的类型一致，限制主键的数据正确，与外键保持一致。

多表查询

合并结果集:UNION 、 UNION ALL

连接查询

内连接 INNER JOIN ON

外连接 OUTER JOIN ON

-左外连接 LEFT [OUTER] JOIN

- 右外连接 RIGHT [OUTER] JOIN

- 全外连接（MySQL不支持）FULL JOIN

自然连接 NATURAL JOIN

子查询

合并结果集

作用：合并结果集就是把两个 select 语句的查询结果合并到一起

注意：被合并的两个结果：列数、列类型必须相同。

语法：select 列名 from 表名1 UNION(或UNION ALL) select 列名 from 表名2;

UNION：去除重复记录

UNION ALL：不去除重复记录

例：select * from mysql1 union select * from mysql2;

连接查询

连接查询就是求出多个表的乘积，例如 t1 连接 t2 ，那么查询出的结果就是 t1*t2。

连接查询会产生 笛卡尔积 ，假设集合 A={a,b} ，集合 B={0,1,2} ，则两个集合的笛卡尔积为 {(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

笛卡尔积可以通过条件语句来消除。

语法：select 列名 from 表名1，表名2，...，表名n 条件语句;

例：select * from mysql1,mysql2 where mysql1.name = mysql2.name;

内连接

内连接,它不是 SQL标准中的查询方式，可以理解为方言。

语法：select 列名 from 表名1 inner join 表名2 on 表名1.列名 = 表名2.列名 条件语句;

例：select * from mysql1 inner join mysql2 on mysql1.name = mysql2.name;

外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

左外联

注意：

1. 主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示 null。

2. 主表和次表不能随意调换位置。

语法:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名;

例：select * from mysql1 left join mysql2 on mysql1.name = mysql2.name;

右外联

语法：select 列名 from 次表 right join 主表 on 主表.列名=次表.列名;

例：select * from mysql1 right join mysql2 on mysql1.name = mysql2.name;

自然连接

自然连接（ NATURAL INNER JOIN ）：自然连接是一种特殊的等值连接 ，他要求两个关系表中进行连 接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

语句：select 列名 from 表名1 natural join 表名2 ;

例：select * from mysql1 natural join mysql2;

子查询

一个 select 语句中包含另一个完整的 select 语句。

子查询就是嵌套查询，即select中包含select，如果一条语句中存在两个，或两个以上select，那么就是子查询语句了。

子查询出现的位置：

where 后，作为条为被查询的一条件的一部分；

from 后，作表；

当子查询出现在 where 后作为条件时，还可以使用如下关键字：

any

all

子查询结果集的形式：

单行单列（用于条件）

单行多列（用于条件）

多行单列（用于条件）

多行多列（用于表）

例：select * from mysql where age >(select age from mysql where name="张三");

日期运算函数

now() 获得当前系统时间

year( 日期值 ) 获得日期值中的年份

date_add( 日期 ,interval 计算值 计算的字段 );

注：计算值大于 0 表示往后推日期，小于 0 表示往前推日期

例： date_add(now(),interval -40 year);//40 年前的日期

数据库优化

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫

描，如：

select id from t where num is null

最好不要给数据库留 NULL ，尽可能的使用 NOT NULL 填充数据库 .

备注、描述、评论之类的可以设置为 NULL ，其他的，最好不要使用 NULL 。

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or Name = "admin";

5.in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1 , 2 , 3 );

对于连续的数值，能用 between 就不要用 in 了 如： select id from t where num between 1 and 3;

很多时候用 exists 代替 in 是一个好的选择。