程序员的SQL金典读书笔记

笔记中所有的练习均使用MYSQL数据库

数据表的创建和管理

创建数据表

  CREATE TABLE person
     (
       name varchar(20),
       age int
    );

定义非空约束

  CREATE TABLE person
    (
        name varchar(20) NOT NULL,
        age int NOT NULL
    );

定义默认值
为字段设置一个默认值，当没有为该字段赋值的时候使用该默认值。

CREATE TABLE teacher
    (
       number varchar(20),
       name varchar(20),
       age int,
       master varchar(5) DEFAULT 'NO'
   );

定义主键
通过主键可以唯一定位一条数据记录，并且在进行外键关联的时候也需要被关联的数据表具有主键。在创建表的时候定义主键是通过PRIMARY KEY关键字来进行定义的。在DB2中，主键字段必须是非空字段（添加非空约束），否则会报错。

  CREATE TABLE bus
    (
      number varchar(20),
      name varchar(20),
      usedAge int,
      PRIMARY KEY (number)
    );

联合主键是指将多个字段联合起来作为主键。

CREATE TABLE bus
    (
      number varchar(20),
      name varchar(20),
      usedAge int,
      PRIMARY KEY (number,name)
    );

定义外键
通过外键可以把互相独立的表关联起来。
定义格式为：
FOREIGN KEY 外键字段名称 REFERENCES 目标表名(被关联的字段)
我们首先建一张部门信息表：

  CREATE TABLE department
    (
      id varchar(20),
      name varchar(20),
      level int,
      PRIMARY KEY (id)
    );

创建员工信息表,将员工信息同部门信息表之间创建关联关系，员工信息表中的部门id作为外键之间部门信息表中的主键id。

CREATE TABLE employee
   (
      number varchar(20),
      name varchar(20),
      departmentId varchar(20),
      FOREIGN KEY (departmentId) REFERENCES department(id)
    );

修改数据表
使用语法是：
ALTER TABLE 待修改的表名 ADD 字段名字段类型
ALTER TABLE 待修改的表名 DROP 字段
删除数据表
DROP TABLE 表名

数据的增删改

首先创建两张表T_Person、T_Debt
CREATE TABLE T_Person(
FName VARCHAR(20),
FAge INT,
FRemark VARCHAR(20),
PRIMARY KEY(FName)
);
CREATE TABLE T_Debt(
FNumber VARCHAR(20),
FAmount DECIMAL(10,2) NOT NULL,
FPerson VARCHAR(20),
PRIMARY KEY(FNumber),
FOREIGN KEY(FPerson) REFERENCES T_Person(FName)
);
1.简单的插入语句
INSERT INTO 表名(字段名) VALUES(字段值)
例如向表T_Person中插入数据

INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Jim',20,'USA');
INSERT INTO T_Person(FName,FAge,FRemark) VALUES('Lili',22,'China');
INSERT INTO T_Person(FName,FAge,FRemark) VALUES('XiaoWang',17,'China');
查看表格T_Person
select * from T_Person;
+----------+------+---------+
| FName    | FAge | FRemark |
+----------+------+---------+
| Jim      |   20 | USA     |
| Lili     |   22 | China   |
| XiaoWang |   17 | China   |
+----------+------+---------+

也可以使用另一种方式，不指定插入的表列，那么这种情况下就会按照定义表中字段的顺序来进行插入。
例如：

INSERT INTO T_Person VALUES('luren1',23,'China');
select * from T_Person;
+----------+------+---------+
| FName    | FAge | FRemark |
+----------+------+---------+
| Jim      |   20 | USA     |
| Lili     |   22 | China   |
| luren1   |   23 | China   |
| XiaoWang |   17 | China   |
+----------+------+---------+

非空约束对数据插入的影响
如果一个字段添加了非空约束，那么就不能想这个字段中插入NULL值。
例如T_Debt中的FAmount字段为非空字段，如果我们执行如下的插入操作

INSERT INTO T_Debt(FNumber,FPerson) VALUES('1','Jim');
就会报错
ERROR 1364 (HY000): Field 'FAmount' doesn't have a default value

主键对数据插入的影响
主键值在同一张表中必须是唯一的，如果插入了表中已经存在的主键值就会违反主键约束而报异常。
例如：

INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES('1',300,'Jim');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

外键对数据插入的影响
外键是指向另一个表中已有数据的约束，因此外键值必须在目标表中寻在，如果插入的数据在目标表中不存在的话就会导致违反外键约束异常。
例如T_Debt表中的字段FPerson字段是指向表T_Person中的FName字段的外键，如果我们执行下面的SQL：

INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES('3',100,'Jerry');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_debt`, CONSTRAINT `t_debt_ibfk_1` FOREIGN KEY (`FPerson`) REFERENCES `T_Person` (`FName`));
因为T_Person中不存在Jerry。

数据的更新
UPDATE语句
例如将数据表T_Person中所有人员的FREMARK字段更新为‘SuperMan’。

UPDATE T_Person SET FRemark = 'SuperMan';
select * from T_Person;
+----------+------+----------+
| FName    | FAge | FRemark  |
+----------+------+----------+
| Jim      |   20 | SuperMan |
| Lili     |   22 | SuperMan |
| luren1   |   23 | SuperMan |
| XiaoWang |   17 | SuperMan |
+----------+------+----------+

带WHERE子句的UPDATE语句
只更新符合过滤条件的行
例如

UPDATE T_Person  SET FAge = 12  WHERE FName = 'Jim';
select * from T_Person;
+----------+------+----------+
| FName    | FAge | FRemark  |
+----------+------+----------+
| Jim      |   12 | SuperMan |
| Lili     |   22 | SuperMan |
| luren1   |   23 | SuperMan |
| XiaoWang |   17 | SuperMan |
+----------+------+----------+

主键对数据更新的影响
如果更新的时候指定的主键和表中已经存在的主键重复的话，就会导致违反主键约束异常。

select * from T_Debt;
+---------+---------+---------+
| FNumber | FAmount | FPerson |
+---------+---------+---------+
| 1       |  200.00 | Jim     |
| 2       |  300.00 | Jim     |
| 3       |  100.00 | Lili    |
+---------+---------+---------+
UPDATE T_Debt SET FNumber = '1' WHERE FPerson = 'Lili';
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

外键对数据更新的影响
外键值必须在目标表中存在，如果更新后的外键值在目标表中不存在的话就会导致违反外键约束异常。

select * from T_Debt;
+---------+---------+---------+
| FNumber | FAmount | FPerson |
+---------+---------+---------+
| 1       |  200.00 | Jim     |
| 2       |  300.00 | Jim     |
| 3       |  100.00 | Lili    |
+---------+---------+---------+
select * from T_Person;
+----------+------+----------+
| FName    | FAge | FRemark  |
+----------+------+----------+
| Jim      |   12 | SuperMan |
| Lili     |   22 | SuperMan |
| luren1   |   23 | SuperMan |
| XiaoWang |   17 | SuperMan |
+----------+------+----------+

UPDATE T_Debt set FPerson = 'Merry' WHERE FNumber = '1';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_debt`, CONSTRAINT `t_debt_ibfk_1` FOREIGN KEY (`FPerson`) REFERENCES `T_Person` (`FName`))

UPDATE T_Debt set FPerson = 'luren1' WHERE FNumber = '1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
select * from T_Debt;
+---------+---------+---------+
| FNumber | FAmount | FPerson |
+---------+---------+---------+
| 1       |  200.00 | luren1  |
| 2       |  300.00 | Jim     |
| 3       |  100.00 | Lili    |
+---------+---------+---------+

数据的删除
例如：

DELETE FROM T_Person;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_debt`, CONSTRAINT `t_debt_ibfk_1` FOREIGN KEY (`FPerson`) REFERENCES `T_Person` (`FName`))
因为T_Debt中的FPerson是指向T_Person的外键，所以必须先删除T_Debt才能删除T_Person

DELETE和Drop的区别
DELETE仅删除数据库中的记录不会删除数据表，而DROP不仅会删除记录而且会删除表。

delete from T_Debt where FNumber = '1';
select * from T_Debt;
+---------+---------+---------+
| FNumber | FAmount | FPerson |
+---------+---------+---------+
| 2       |  300.00 | Jim     |
| 3       |  100.00 | Lili    |
+---------+---------+---------+

delete from T_Debt;
select * from T_Debt;
Empty set (0.00 sec)

drop table T_Debt;
Query OK, 0 rows affected (0.01 sec)

select * from T_Debt;
ERROR 1146 (42S02): Table 'test.t_debt' doesn't exist

数据的检索

首先创建表T_Employee

 CREATE TABLE T_Employee 
 (
    FNumber VARCHAR(20),
    FName VARCHAR(20),
    FAge INT,
    FSalary DECIMAL(10,2),
    PRIMARY KEY(FNumber)
);
 INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV001','Tom',25,8300);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV002','Jerry',28,2300.80);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES001','John',23,5000);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES002','Kerry',28,6200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES003','Stone',22,1200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR001','Jane',23,2200.88);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('HR002','Tina',25,5200.36);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('IT001','Smith',28,3900);

检索出所有的列

select * from T_Employee;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| SALES001 | John  |   23 | 5000.00 |
| SALES002 | Kerry |   28 | 6200.00 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

检索出需要的列

select FNumber,FName from T_Employee;
+----------+-------+
| FNumber  | FName |
+----------+-------+
| DEV001   | Tom   |
| DEV002   | Jerry |
| HR001    | Jane  |
| HR002    | Tina  |
| IT001    | Smith |
| SALES001 | John  |
| SALES002 | Kerry |
| SALES003 | Stone |
+----------+-------+

列的别名
定义别名的语法格式：
列名 AS 别名
AS也可以省略
例如：

select FNumber AS Number,FName AS Name,FAge AS Age,FSalary AS Salary
FROM T_Employee;
+----------+-------+------+---------+
| Number   | Name  | Age  | Salary  |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| SALES001 | John  |   23 | 5000.00 |
| SALES002 | Kerry |   28 | 6200.00 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

按条件过滤检索
例如：
检索出工资小于5000的员工

select * from T_Employee where FSalary < 5000;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| IT001    | Smith |   28 | 3900.00 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

数据汇总
sql中提供了一些聚合函数来完成计算条数、找出最大值或最小值、计算平均值等
函数名说明
MAX 计算字段最大值
MIN 计算字段最小值
AVG 计算字段平均值
SUM 计算字段和
COUNT 统计数据条数
例如
查找大于25岁员工的最高工资

select MAX(FSalary) from T_Employee where FAge > 25;
+--------------+
| MAX(FSalary) |
+--------------+
|      6200.00 |
+--------------+

计算工资大于3800的员工的平均年龄

select AVG(FAge) from T_Employee where FSalary > 3800;
+-----------+
| AVG(FAge) |
+-----------+
|   25.8000 |
+-----------+

计算公司每个月应该支出工资总额

select SUM(FSalary) from T_Employee;
+--------------+
| SUM(FSalary) |
+--------------+
|     34302.04 |

记录条数COUNT

select COUNT(*) from T_Employee;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+

INSERT INTO T_Employee(FNumber,FAge,FSalary)  VALUES('IT002',27,2800);
Query OK, 1 row affected (0.00 sec)

select * from T_Employee;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| IT002    | NULL  |   27 | 2800.00 |
| SALES001 | John  |   23 | 5000.00 |
| SALES002 | Kerry |   28 | 6200.00 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

select count(*),count(FNumber),count(FName) from T_Employee;
+----------+----------------+--------------+
| count(*) | count(FNumber) | count(FName) |
+----------+----------------+--------------+
|        9 |              9 |            8 |
+----------+----------------+--------------+
可以看到FName的统计数据为8，是因为FName中有一个NULL值。

排序
select语句允许使用order by子句来对结果集进行排序，它允许指定按照一个或多个列进行排序，也可以指定排列方式是升序还是降序。

 select * from T_Employee order by FAge ASC;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| SALES003 | Stone |   22 | 1200.00 |
| HR001    | Jane  |   23 | 2200.88 |
| SALES001 | John  |   23 | 5000.00 |
| DEV001   | Tom   |   25 | 8300.00 |
| HR002    | Tina  |   25 | 5200.36 |
| DEV002   | Jerry |   28 | 2300.80 |
| IT001    | Smith |   28 | 3900.00 |
| SALES002 | Kerry |   28 | 6200.00 |
+----------+-------+------+---------+

使用多个排序规则，如果第一个排序规则没有办法区分两条记录的顺序，则会按照第二个排序规则进行排序，以此类推。

高级数据过滤

通配符过滤
单字符匹配
进行单字符匹配的通配符是半角下划线“_”，它匹配单个出现的字符。例如通配符表达式“b_d”匹配第一个字符为b，第二个字符为任意字符，第三个字符为d的字符串。
例如：

select * from T_Employee  where FName like '_erry';
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV002   | Jerry |   28 | 2300.80 |
| SALES002 | Kerry |   28 | 6200.00 |
+----------+-------+------+---------+

多字符匹配
多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。例如通配符表达式“k%”匹配以“k”开头、任意长度的字符串。
例如：
检索FName字段中以“T”开头，长度任意的值，“T%”

select * from T_Employee  where FName like 'T%';
+---------+-------+------+---------+
| FNumber | FName | FAge | FSalary |
+---------+-------+------+---------+
| DEV001  | Tom   |   25 | 8300.00 |
| HR002   | Tina  |   25 | 5200.36 |
+---------+-------+------+---------+

单字符匹配和多字符匹配一起使用
检索T_Employee表中的FName字段匹配如下规则的数据行：最后一个字符为任意字符，倒数第二个字符为“n”，长度任意的字符串。“%n_”
例如

 select * from T_Employee where FName like '%n_';
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

集合匹配
进行集合匹配的时候使用通配符“[]”,方括号包含一个字符集，它匹配与字符集中任意一个字符相匹配的字符。例如通配符表达式“[bt]%”匹配第一个字符为b或者t，长度任意的字符串。但是mysql数据库中不支持这种方式。
例如检索T_Employee中FName字段中以“S”或者“J”开头的长度任意的数据记录。

select * from T_Employee  where FName like '[SJ]%';

使用^{来对集合取反，例如通配符表达式“[}bt]%”匹配第一个字符不为b或者t的长度不限的字符串。

select * from T_Employee  where FName like '[SJ]%';

不过虽然通配符过滤功能很强大，但是通配符过滤进行检索的时候，数据库系统会对全表进行扫描，所以执行速度会非常慢。因此在使用其他方式可以实现相同效果的情况下尽量不要使用通配符过滤。

空值检测
如果要检测T_Employee中所有姓名未知的员工的信息，不能使用FName=null，而是要使用关键字IS NULL。使用方法如下“待检测字段名IS NULL”。
例如：

select * from T_Employee where FName IS NULL;
+---------+-------+------+---------+
| FNumber | FName | FAge | FSalary |
+---------+-------+------+---------+
| IT002   | NULL  |   27 | 2800.00 |
+---------+-------+------+---------+

如果要检测非空字段需要使用IS NOT NULL。
例如：

select * from T_Employee where FName IS NOT NULL;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| SALES001 | John  |   23 | 5000.00 |
| SALES002 | Kerry |   28 | 6200.00 |
| SALES003 | Stone |   22 | 1200.00 |
+----------+-------+------+---------+

反义运算符
在MYSQL中不等于使用"<>"运算符，不大于就是小于等于，不小于就是大于等于。
也可以使用NOT运算符将一个表达式的值取反。也就是将为“真”的表达式的结果变为“假”。使用方式是“NOT(表达式)”。
例如检索出所有年龄不等于22并且工资不小于2000元的员工。

select * from T_Employee where not(FAge=22) and not(FSalary<2000);
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| IT002    | NULL  |   27 | 2800.00 |
| SALES001 | John  |   23 | 5000.00 |
| SALES002 | Kerry |   28 | 6200.00 |
+----------+-------+------+---------+

推荐除了不等于“<>”之外，其他的使用NOT运算符的方式。

多值检测
例如检索出公司年龄为23、25、28的员工的信息。
我们可以使用or来连接多个判断，但是如果要检索的年龄值有很多个，我们就要连接多个or，这样写起来就会很麻烦，并且容易出错、维护难度也大，所以可以使用SQL中的IN语句。使用方法是“IN(值1，值2，值3.......)”。
例如

select * from T_Employee  where FAge in(25,28,30);
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| DEV002   | Jerry |   28 | 2300.80 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900.00 |
| SALES002 | Kerry |   28 | 6200.00 |
+----------+-------+------+---------+

范围值检测
SQL中提供了一个专门用于范围检测的语句“BETWEEN AND”，它可以用于检测一个值是否处于某个范围中（包括范围的边界值）。使用方法是BETWEEN 左范围值 AND 右范围值。

select * from T_Employee where FAge between 23 and 27;
+----------+-------+------+---------+
| FNumber  | FName | FAge | FSalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300.00 |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT002    | NULL  |   27 | 2800.00 |
| SALES001 | John  |   23 | 5000.00 |
+----------+-------+------+---------+

数据库系统对“BETWEEN AND”进行了查询优化，使用该方式进行范围检测将会比其他方式的性能更好，所以范围值检测的时候优先使用“BETWEEN AND”。

低效的“WHERE 1=1”
因为添加了“1=1”的过滤条件之后数据库系统就没有办法使用索引等优化策略，会对数据库表的每行数据进行扫描来比较满足过滤条件的数据行。如果表中的数据量很大的时候查询的速度就会很慢。
数据分组
SQL中使用GROUP BY进行分组，GROUP BY子句必须放在SELECT语句的后面，如果有WHERE子句必须放在WHERE子句的后面。
需要分组的所有的列都必须位于GROUP BY子句的列名列表中，也就是说没有出现在GROUP BY子句中的列（聚合函数除外）是不能放在SELECT语句后的列名列表中的。
比如下面的查询语句就是错误的

select FAge,FSalary from T_Employee group by FAge;
因为输出的结果是采用的分组形式，但是每个员工的工资是不同的，所以不存在统一代表该组工资水平的FSalary。
可以使用
select FAge,AVG(FSalary) from T_Employee group by FAge;
+------+--------------+
| FAge | AVG(FSalary) |
+------+--------------+
|   22 |  1200.000000 |
|   23 |  3600.440000 |
|   25 |  6750.180000 |
|   27 |  2800.000000 |
|   28 |  4133.600000 |
+------+--------------+

group by子句中可以指定多个列，只需要将多个列用逗号隔开就行，指定多个列后，数据库系统会按照定义的分组顺序进行逐层的分组，首先按照第一个分组列进行分组，然后在每个小组中再按照第二个分组列进行再次分组，以此类推进行逐层分组，从而实现组中组的效果。

数据分组和聚合函数
使用group by分组后再使用聚合函数对分组的结果进行统计。
例如，查看每个年龄段的员工的人数

select  FAge,COUNT(*) from T_Employee  group by FAge;
+------+----------+
| FAge | COUNT(*) |
+------+----------+
|   22 |        1 |
|   23 |        2 |
|   25 |        2 |
|   27 |        1 |
|   28 |        3 |
+------+----------+

进行更细的分组
例如每个分公司的各个年龄段的员工数

select FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge from T_Employee group by FSubCompany,FAge;
+-------------+------+-----------------------+
| FSubCompany | FAge | CountOfThisSubCompAge |
+-------------+------+-----------------------+
| NULL        |   22 |                     1 |
| NULL        |   23 |                     1 |
| NULL        |   28 |                     1 |
| Beijing     |   23 |                     1 |
| Beijing     |   25 |                     2 |
| Beijing     |   28 |                     1 |
| ShenZhen    |   27 |                     1 |
| ShenZhen    |   28 |                     1 |
+-------------+------+-----------------------+

HAVING语句
有时候需要对部分分组进行过滤，例如只检索人数多余1个的年龄段。
如下的方式是错误的

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge WHERE COUNT(*)>1
因为聚合函数是不能用在WHERE语句中的，必须使用HAVING子句来代替。
select FAge,count(*) as CountOfThisAge from T_Employee group by FAge having count(*)>1;
+------+----------------+
| FAge | CountOfThisAge |
+------+----------------+
|   23 |              2 |
|   25 |              2 |
|   28 |              3 |
+------+----------------+

HAVING语句和WHERE的语法几乎一样，但是GROUP BY子句要位于WHERE子句的后面，而HAVING子句要位于GROUP BY子句的后面。
另外需要注意的是HAVING语句中不能包含未分组的列名。

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING FName IS NOT NULL;
ERROR 1054 (42S22): Unknown column 'FName' in 'having clause'
可以修改为：
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee WHERE FName IS NOT NULL GROUP BY FAge;
+------+----------------+
| FAge | CountOfThisAge |
+------+----------------+
|   22 |              1 |
|   23 |              2 |
|   25 |              2 |
|   28 |              3 |
+------+----------------+

限制结果集行数
各个主流数据库中限制的结果集的行数的方法各不相同。
MYSQL中提供了LIMIT关键字来限制返回的结果集。
语法格式是“LIMIT 首行行号，要返回的结果集的最大数目”。
例如取按工资降序从第二行开始的最多五条记录：

SELECT * FROM T_Employee ORDER BY FSalary LIMIT 2,5;
+----------+-------+------+---------+-------------+---------------+
| FNumber  | FName | FAge | FSalary | FSubCompany | FDepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV002   | Jerry |   28 | 2300.80 | ShenZhen    | Development   |
| IT002    | NULL  |   27 | 2800.00 | ShenZhen    | InfoTech      |
| IT001    | Smith |   28 | 3900.00 | Beijing     | InfoTech      |
| SALES001 | John  |   23 | 5000.00 | NULL        | NULL          |
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
+----------+-------+------+---------+-------------+---------------+

抑制数据重复
可以使用DISTINCT关键字进行重复数据的抑制。使用语法是SELECT DISTINCT
DISTINCT是对整个结果集进行数据重复抑制，而不是针对每一个列。

select FDepartment from T_Employee;
+---------------+
| FDepartment   |
+---------------+
| Development   |
| Development   |
| HumanResource |
| HumanResource |
| InfoTech      |
| InfoTech      |
| NULL          |
| NULL          |
| NULL          |
+---------------+
select distinct FDepartment from T_Employee;
+---------------+
| FDepartment   |
+---------------+
| Development   |
| HumanResource |
| InfoTech      |
| NULL          |
+---------------+

select distinct FDepartment,FSubCompany from T_Employee;
+---------------+-------------+
| FDepartment   | FSubCompany |
+---------------+-------------+
| Development   | Beijing     |
| Development   | ShenZhen    |
| HumanResource | Beijing     |
| InfoTech      | Beijing     |
| InfoTech      | ShenZhen    |
| NULL          | NULL        |
+---------------+-------------+

计算字段

常量字段

select 'CowNew集团' AS CompanyName,918000000 AS RegAmount,FName,FAge,FSubCompany from T_Employee;
+--------------+-----------+-------+------+-------------+
| CompanyName  | RegAmount | FName | FAge | FSubCompany |
+--------------+-----------+-------+------+-------------+
| CowNew集团   | 918000000 | Tom   |   25 | Beijing     |
| CowNew集团   | 918000000 | Jerry |   28 | ShenZhen    |
| CowNew集团   | 918000000 | Jane  |   23 | Beijing     |
| CowNew集团   | 918000000 | Tina  |   25 | Beijing     |
| CowNew集团   | 918000000 | Smith |   28 | Beijing     |
| CowNew集团   | 918000000 | NULL  |   27 | ShenZhen    |
| CowNew集团   | 918000000 | John  |   23 | NULL        |
| CowNew集团   | 918000000 | Kerry |   28 | NULL        |
| CowNew集团   | 918000000 | Stone |   22 | NULL        |
+--------------+-----------+-------+------+-------------+

'CowNew集团' 和918000000就是常量字段

字段间计算

select FNumber,FName,FAge*FSalary AS FSalaryIndex from T_Employee;
+----------+-------+--------------+
| FNumber  | FName | FSalaryIndex |
+----------+-------+--------------+
| DEV001   | Tom   |    207500.00 |
| DEV002   | Jerry |     64422.40 |
| HR001    | Jane  |     50620.24 |
| HR002    | Tina  |    130009.00 |
| IT001    | Smith |    109200.00 |
| IT002    | NULL  |     75600.00 |
| SALES001 | John  |    115000.00 |
| SALES002 | Kerry |    173600.00 |
| SALES003 | Stone |     26400.00 |
+----------+-------+--------------+

计算字段也可以在WHERE语句或者UPDATE、DELETE中使用。

select * from T_Employee where FSalary/(FAge - 21)>1000 ;
+----------+-------+------+---------+-------------+---------------+
| FNumber  | FName | FAge | FSalary | FSubCompany | FDepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   25 | 8300.00 | Beijing     | Development   |
| HR001    | Jane  |   23 | 2200.88 | Beijing     | HumanResource |
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
| SALES001 | John  |   23 | 5000.00 | NULL        | NULL          |
| SALES003 | Stone |   22 | 1200.00 | NULL        | NULL          |
+----------+-------+------+---------+-------------+---------------+

数据处理函数
例如计算字符串长度的函数LENGTH

select FName,LENGTH(FName) from T_Employee;
+-------+---------------+
| FName | LENGTH(FName) |
+-------+---------------+
| Tom   |             3 |
| Jerry |             5 |
| Jane  |             4 |
| Tina  |             4 |
| Smith |             5 |
| NULL  |          NULL |
| John  |             4 |
| Kerry |             5 |
| Stone |             5 |
+-------+---------------+

取字符串的子串函数，SUBSTRING(字符串，起始位置(从1开始计数)，子串的长度)

select FName,SUBSTRING(FName,1,2) from T_Employee;
+-------+----------------------+
| FName | SUBSTRING(FName,1,2) |
+-------+----------------------+
| Tom   | To                   |
| Jerry | Je                   |
| Jane  | Ja                   |
| Tina  | Ti                   |
| Smith | Sm                   |
| NULL  | NULL                 |
| John  | Jo                   |
| Kerry | Ke                   |
| Stone | St                   |
+-------+----------------------+

字符串的拼接
在有的数据库系统的SQL语法中双引号有其他的含义，然而所有的数据库系统都支持使用单引号包围的形式定义的字符串，所以建议使用单引号包围的形式来定义字符串。
MYSQL中可以使用“+”进行字符串拼接，在使用“+”号的时候，MYSQL会尝试两个字符串转换成数字类型（如果转换失败则认为字段值为0），然后进行字段的加法运算，因此我们会发现如下的例子中“12”+“23”得到的不是1223而是35.

select '12'+'23',FAge + '1' from T_Employee;
+-----------+------------+
| '12'+'23' | FAge + '1' |
+-----------+------------+
|        35 |         26 |
|        35 |         29 |
|        35 |         24 |
|        35 |         26 |
|        35 |         29 |
|        35 |         28 |
|        35 |         24 |
|        35 |         29 |
|        35 |         23 |
+-----------+------------+
select 'abc'+'123',FAge from T_Employee;
+-------------+------+
| 'abc'+'123' | FAge |
+-------------+------+
|         123 |   25 |
|         123 |   28 |
|         123 |   23 |
|         123 |   25 |
|         123 |   28 |
|         123 |   27 |
|         123 |   23 |
|         123 |   28 |
|         123 |   22 |
+-------------+------+
select 'abc'+'d',FAge from T_Employee;
+-----------+------+
| 'abc'+'d' | FAge |
+-----------+------+
|         0 |   25 |
|         0 |   28 |
|         0 |   23 |
|         0 |   25 |
|         0 |   28 |
|         0 |   27 |
|         0 |   23 |
|         0 |   28 |
|         0 |   22 |
+-----------+------+

MYSQL中也可以使用CONCAT函数进行字符串的拼接，
CONCAT函数支持一个或者多个参数，参数类型可以是字符串类型也可以是非字符串类型，对于非字符串类型的参数MYSQL将尝试将其转换成字符串类型。
例如：

select CONCAT('工号为:',FNumber,'的员工的幸福指数:',FSalary/(FAge-21)) from T_Employee;

MYSQL中还提供了支持在待拼接的字符串之间加入指定的分隔符的函数CONCAT_WS。

联合结果集
有时候需要组合两个完全不同的结果集，而这两个查询结果集之间没有必然的联系。SQL中可以使用UNION将两个或多个查询结果集联合为一个结果集。
UNION运算符要放置在两个查询之间
例如查询公司所有员工的信息，包括正式员工和临时员工的信息

select FNumber,FName,FAge from T_Employee UNION select FIdCardNumber,FName,FAge from T_TempEmployee;
+---------------+---------+------+
| FNumber       | FName   | FAge |
+---------------+---------+------+
| DEV001        | Tom     |   25 |
| DEV002        | Jerry   |   28 |
| HR001         | Jane    |   23 |
| HR002         | Tina    |   25 |
| IT001         | Smith   |   28 |
| IT002         | NULL    |   27 |
| SALES001      | John    |   23 |
| SALES002      | Kerry   |   28 |
| SALES003      | Stone   |   22 |
| 1234567890121 | Sarani  |   33 |
| 1234567890122 | Tom     |   26 |
| 1234567890123 | Yalaha  |   38 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
| 1234567890126 | Fotifa  |   46 |
+---------------+---------+------+

联合结果集的原则
使用UNION的两个原则：一、每个结果集必须有相同的列数；二、每个结果集的列必须类型向容（相同类型或者能够转换为同一种数据类型）。

select FNumber,FName,FAge from T_Employee UNION select FIdCardNumber,FAge,FName from T_TempEmployee;
+---------------+-------+---------+
| FNumber       | FName | FAge    |
+---------------+-------+---------+
| DEV001        | Tom   | 25      |
| DEV002        | Jerry | 28      |
| HR001         | Jane  | 23      |
| HR002         | Tina  | 25      |
| IT001         | Smith | 28      |
| IT002         | NULL  | 27      |
| SALES001      | John  | 23      |
| SALES002      | Kerry | 28      |
| SALES003      | Stone | 22      |
| 1234567890121 | 33    | Sarani  |
| 1234567890122 | 26    | Tom     |
| 1234567890123 | 38    | Yalaha  |
| 1234567890124 | 26    | Tina    |
| 1234567890125 | 29    | Konkaya |
| 1234567890126 | 46    | Fotifa  |
+---------------+-------+---------+
上面的结果中可以看出将FAge转换成文本类型

但是有些数据库要求必须类型相同，所以最好保证结果集的每个对应列的数据类型完全相同。

UNION ALL
使用UNION合并两个查询结果集时，其中完全重复的数据会被合并为一条。
如果需要返回所有的记录而不是重复的数据合并就需要使用UNION ALL

函数

省略不写，需要时再查文档

索引与约束

索引用来提高检索数据的速度，约束用来保证数据的完整行。
1.非空约束
需要禁止字段为空的话，就需要使用非空约束

CREATE TABLE T_Person (
    FNumber VARCHAR(20) NOT NULL ,(非空约束)
    FName VARCHAR(20),
    FAge INT
)

唯一约束
用于防止一个特定的列中两个记录有一样的值。

CREATE TABLE T_Person( FNumber VARCHAR(20) UNIQUE, FName VARCHAR(20), FAge INT);

INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '1' , 'kingchou', 20);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '2' , 'stef', 22);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '3' , 'long', 26);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '4' , 'yangzk', 27);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '5' , 'beansoft', 26);

select * from T_Person;
+---------+----------+------+
| FNumber | FName    | FAge |
+---------+----------+------+
| 1       | kingchou |   20 |
| 2       | stef     |   22 |
| 3       | long     |   26 |
| 4       | yangzk   |   27 |
| 5       | beansoft |   26 |
+---------+----------+------+
INSERT INTO T_Person(FNumber,FName,FAge) VALUES('2','kitty',20);
ERROR 1062 (23000): Duplicate entry '2' for key 'FNumber'

上面讲的是定义单字段唯一约束
多字段唯一约束的定义苏耀在所有字段列表的后面，语法格式如下：

CONSTRAINT 约束名  UNIQUE(字段1，字段2，.......)

CHECK约束
CHECK约束用来检查输入到数据库中的值是否满足一个条件，如果不满足这个条件则对数据库的修改就不会成功，一张表中可以存在多个CHECK约束。

CREATE TABLE T_Person
(
        FNumber VARCHAR(20),
        FName VARCHAR(20),
        FAge INT CHECK(FAge>0),
        FWorkYear INT CHECK(FWorkYear>0)
);
INSERT INTO T_Person(FNumber, FName, FAge, FWorkYear)
VALUES('001','John',25,-3)
因为设置的是FWorkYear大于0，所以插入-3会报错。

还有一种在所有字段后加检查约束条件的

CONSTRAINT 约束名 CHECK(约束条件);

主键约束
主键必须能够唯一标识一条记录，主键字段的值必须是唯一的，而且其中不能包含NULL值。
由单一字段组成的主键可以在字段定义之后添加PRIMARY KEY就可以。
如果是由多个字段组成的就称为联合主键，定义的格式是：
CONSTRAINT 主键名 PRIMARY KEY(字段1，字段2.....)
也可以使用
ALTER TABLE 表格名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段1，字段2.......)
外键约束
定义格式是：

FOREIGN KEY 外键字段 REFERENCES 外键表名(外键表的主键字段)

表连接

select * from T_Order;
+-----+---------+--------+-------------+---------+
| FId | FNumber | FPrice | FCustomerId | FTypeId |
+-----+---------+--------+-------------+---------+
|   1 | K001    | 100.00 |           1 |       1 |
|   2 | K002    | 200.00 |           1 |       1 |
|   3 | T003    | 300.00 |           1 |       2 |
|   4 | N002    | 100.00 |           2 |       2 |
|   5 | N003    | 500.00 |           3 |       4 |
|   6 | T001    | 300.00 |           4 |       3 |
|   7 | T002    | 100.00 |        NULL |       1 |
+-----+---------+--------+-------------+---------+
select * from T_OrderType;
+-----+-----------------+
| FId | FName           |
+-----+-----------------+
|   1 | MarketOrder     |
|   2 | LimitOrder      |
|   3 | Stop Order      |
|   4 | StopLimit Order |
+-----+-----------------+
select * from T_Customer;
+-----+-------+------+
| FId | FName | FAge |
+-----+-------+------+
|   1 | TOM   |   21 |
|   2 | MIKE  |   24 |
|   3 | JACK  |   30 |
|   4 | TOM   |   25 |
|   5 | LINDA | NULL |
+-----+-------+------+

从多张表中查询数据，可以使用表连接JOIN，表连接有多种不同的类型，交叉连接(CROSS JOIN)、内连接(INNER JOIN)、外连接(OUTTER JOIN)。

内连接
语法格式如下：
INNER JOIN tableName ON condition
tableName为被连接的表名，condition为连接时的条件。

select FNumber,FPrice from T_Order INNER JOIN T_Customer ON FCustomerId = T_Customer.FId WHERE T_Customer.FName='TOM';
+---------+--------+
| FNumber | FPrice |
+---------+--------+
| K001    | 100.00 |
| K002    | 200.00 |
| T003    | 300.00 |
| T001    | 300.00 |
+---------+--------+

为了避免列名歧义并且提高可读性，在使用表连接的时候建议要显示列所属的表。

select T_Order.FId,T_Order.FNumber,T_Order.FPrice,T_Customer.FId,T_Customer.FName,T_Customer.FAge from T_Order INNER JOIN T_Customer ON T_Order.FCustomerId = T_Customer.FId;
+-----+---------+--------+-----+-------+------+
| FId | FNumber | FPrice | FId | FName | FAge |
+-----+---------+--------+-----+-------+------+
|   1 | K001    | 100.00 |   1 | TOM   |   21 |
|   2 | K002    | 200.00 |   1 | TOM   |   21 |
|   3 | T003    | 300.00 |   1 | TOM   |   21 |
|   4 | N002    | 100.00 |   2 | MIKE  |   24 |
|   5 | N003    | 500.00 |   3 | JACK  |   30 |
|   6 | T001    | 300.00 |   4 | TOM   |   25 |
+-----+---------+--------+-----+-------+------+

在大多数的数据库系统中，INNER JOIN中的INNER是可以省略的，INNER JOIN是默认的连接方式。

可以使用表的别名来简化SQL语句编写
 select o.FId,o.FNumber,o.FPrice,c.FId,c.FName,c.FAge from T_Order o JOIN T_Customer c ON o.FCustomerId = c.FId;
+-----+---------+--------+-----+-------+------+
| FId | FNumber | FPrice | FId | FName | FAge |
+-----+---------+--------+-----+-------+------+
|   1 | K001    | 100.00 |   1 | TOM   |   21 |
|   2 | K002    | 200.00 |   1 | TOM   |   21 |
|   3 | T003    | 300.00 |   1 | TOM   |   21 |
|   4 | N002    | 100.00 |   2 | MIKE  |   24 |
|   5 | N003    | 500.00 |   3 | JACK  |   30 |
|   6 | T001    | 300.00 |   4 | TOM   |   25 |
+-----+---------+--------+-----+-------+------+

不等值连接
到目前，我们例子中的所有连接都是等值连接，也就是on子句包含一个等号运算。还有一种不等值连接，在连接的条件中可以使用小于(<)、大于(>)、不等于(<>)、LIKE、BETWEEN AND等运算符。

select T_Order.FNumber,T_Order.FPrice,T_Customer.FName,T_Customer.FAge from T_Order INNER JOIN T_Customer ON T_Order.FPrice<T_Customer.FAge*5;
+---------+--------+-------+------+
| FNumber | FPrice | FName | FAge |
+---------+--------+-------+------+
| K001    | 100.00 | TOM   |   21 |
| K001    | 100.00 | MIKE  |   24 |
| K001    | 100.00 | JACK  |   30 |
| K001    | 100.00 | TOM   |   25 |
| N002    | 100.00 | TOM   |   21 |
| N002    | 100.00 | MIKE  |   24 |
| N002    | 100.00 | JACK  |   30 |
| N002    | 100.00 | TOM   |   25 |
| T002    | 100.00 | TOM   |   21 |
| T002    | 100.00 | MIKE  |   24 |
| T002    | 100.00 | JACK  |   30 |
| T002    | 100.00 | TOM   |   25 |
+---------+--------+-------+------+

不等值连接产生了大量的查询结果，因为它是对被连接的两张表做了笛卡尔积运算，所以如果想看客户对应的订单，就需要在不等值连接后面加上等值连接匹配条件。

select T_Order.FNumber,T_Order.FPrice,T_Customer.FName,T_Customer.FAge from T_Order INNER JOIN T_Customer ON T_Order.FPrice<T_Customer.FAge*5 AND T_Order.FCustomerId=T_Customer.FId;
+---------+--------+-------+------+
| FNumber | FPrice | FName | FAge |
+---------+--------+-------+------+
| K001    | 100.00 | TOM   |   21 |
| N002    | 100.00 | MIKE  |   24 |
+---------+--------+-------+------+

交叉连接
交叉连接不存在on连接条件，交叉连接会将涉及到的所有表中的所有记录都包含在结果集中。有两种实现方式，分别是显示和隐式的。
隐式连接是在SELECT 语句的FROM语句后将要进行交叉连接的表名列出来。

select c.FId,c.FName,c.FAge,o.FId,o.FNumber,o.FPrice from T_Customer c,T_Order o;
+-----+-------+------+-----+---------+--------+
| FId | FName | FAge | FId | FNumber | FPrice |
+-----+-------+------+-----+---------+--------+
|   1 | TOM   |   21 |   1 | K001    | 100.00 |
|   2 | MIKE  |   24 |   1 | K001    | 100.00 |
|   3 | JACK  |   30 |   1 | K001    | 100.00 |
|   4 | TOM   |   25 |   1 | K001    | 100.00 |
|   5 | LINDA | NULL |   1 | K001    | 100.00 |
|   1 | TOM   |   21 |   2 | K002    | 200.00 |
|   2 | MIKE  |   24 |   2 | K002    | 200.00 |
|   3 | JACK  |   30 |   2 | K002    | 200.00 |
|   4 | TOM   |   25 |   2 | K002    | 200.00 |
|   5 | LINDA | NULL |   2 | K002    | 200.00 |
|   1 | TOM   |   21 |   3 | T003    | 300.00 |
|   2 | MIKE  |   24 |   3 | T003    | 300.00 |
|   3 | JACK  |   30 |   3 | T003    | 300.00 |
|   4 | TOM   |   25 |   3 | T003    | 300.00 |
|   5 | LINDA | NULL |   3 | T003    | 300.00 |
|   1 | TOM   |   21 |   4 | N002    | 100.00 |
|   2 | MIKE  |   24 |   4 | N002    | 100.00 |
|   3 | JACK  |   30 |   4 | N002    | 100.00 |
|   4 | TOM   |   25 |   4 | N002    | 100.00 |
|   5 | LINDA | NULL |   4 | N002    | 100.00 |
|   1 | TOM   |   21 |   5 | N003    | 500.00 |
|   2 | MIKE  |   24 |   5 | N003    | 500.00 |
|   3 | JACK  |   30 |   5 | N003    | 500.00 |
|   4 | TOM   |   25 |   5 | N003    | 500.00 |
|   5 | LINDA | NULL |   5 | N003    | 500.00 |
|   1 | TOM   |   21 |   6 | T001    | 300.00 |
|   2 | MIKE  |   24 |   6 | T001    | 300.00 |
|   3 | JACK  |   30 |   6 | T001    | 300.00 |
|   4 | TOM   |   25 |   6 | T001    | 300.00 |
|   5 | LINDA | NULL |   6 | T001    | 300.00 |
|   1 | TOM   |   21 |   7 | T002    | 100.00 |
|   2 | MIKE  |   24 |   7 | T002    | 100.00 |
|   3 | JACK  |   30 |   7 | T002    | 100.00 |
|   4 | TOM   |   25 |   7 | T002    | 100.00 |
|   5 | LINDA | NULL |   7 | T002    | 100.00 |
+-----+-------+------+-----+---------+--------+

显示的交叉连接是使用CROSS JOIN关键字

select c.FId,c.FName,c.FAge,o.FId,o.FNumber,o.FPrice from T_Customer c cross join T_Order o;
+-----+-------+------+-----+---------+--------+
| FId | FName | FAge | FId | FNumber | FPrice |
+-----+-------+------+-----+---------+--------+
|   1 | TOM   |   21 |   1 | K001    | 100.00 |
|   2 | MIKE  |   24 |   1 | K001    | 100.00 |
|   3 | JACK  |   30 |   1 | K001    | 100.00 |
|   4 | TOM   |   25 |   1 | K001    | 100.00 |
|   5 | LINDA | NULL |   1 | K001    | 100.00 |
|   1 | TOM   |   21 |   2 | K002    | 200.00 |
|   2 | MIKE  |   24 |   2 | K002    | 200.00 |
|   3 | JACK  |   30 |   2 | K002    | 200.00 |
|   4 | TOM   |   25 |   2 | K002    | 200.00 |
|   5 | LINDA | NULL |   2 | K002    | 200.00 |
|   1 | TOM   |   21 |   3 | T003    | 300.00 |
|   2 | MIKE  |   24 |   3 | T003    | 300.00 |
|   3 | JACK  |   30 |   3 | T003    | 300.00 |
|   4 | TOM   |   25 |   3 | T003    | 300.00 |
|   5 | LINDA | NULL |   3 | T003    | 300.00 |
|   1 | TOM   |   21 |   4 | N002    | 100.00 |
|   2 | MIKE  |   24 |   4 | N002    | 100.00 |
|   3 | JACK  |   30 |   4 | N002    | 100.00 |
|   4 | TOM   |   25 |   4 | N002    | 100.00 |
|   5 | LINDA | NULL |   4 | N002    | 100.00 |
|   1 | TOM   |   21 |   5 | N003    | 500.00 |
|   2 | MIKE  |   24 |   5 | N003    | 500.00 |
|   3 | JACK  |   30 |   5 | N003    | 500.00 |
|   4 | TOM   |   25 |   5 | N003    | 500.00 |
|   5 | LINDA | NULL |   5 | N003    | 500.00 |
|   1 | TOM   |   21 |   6 | T001    | 300.00 |
|   2 | MIKE  |   24 |   6 | T001    | 300.00 |
|   3 | JACK  |   30 |   6 | T001    | 300.00 |
|   4 | TOM   |   25 |   6 | T001    | 300.00 |
|   5 | LINDA | NULL |   6 | T001    | 300.00 |
|   1 | TOM   |   21 |   7 | T002    | 100.00 |
|   2 | MIKE  |   24 |   7 | T002    | 100.00 |
|   3 | JACK  |   30 |   7 | T002    | 100.00 |
|   4 | TOM   |   25 |   7 | T002    | 100.00 |
|   5 | LINDA | NULL |   7 | T002    | 100.00 |
+-----+-------+------+-----+---------+--------+

自连接
上面讲的例子都是不同表之间的连接，其实可以同一张表进行连接也就是自连接

select o1.FId,o1.FNumber,o1.FPrice,o2.FId,o2.FNumber,o2.FPrice from T_Order o1 JOIN T_Order o2 ON o1.FTypeId=o2.FTypeId AND o1.FId<o2.FId;
+-----+---------+--------+-----+---------+--------+
| FId | FNumber | FPrice | FId | FNumber | FPrice |
+-----+---------+--------+-----+---------+--------+
|   1 | K001    | 100.00 |   2 | K002    | 200.00 |
|   1 | K001    | 100.00 |   7 | T002    | 100.00 |
|   2 | K002    | 200.00 |   7 | T002    | 100.00 |
|   3 | T003    | 300.00 |   4 | N002    | 100.00 |
+-----+---------+--------+-----+---------+--------+

外部连接

select * from T_Order;
+-----+---------+--------+-------------+---------+
| FId | FNumber | FPrice | FCustomerId | FTypeId |
+-----+---------+--------+-------------+---------+
|   1 | K001    | 100.00 |           1 |       1 |
|   2 | K002    | 200.00 |           1 |       1 |
|   3 | T003    | 300.00 |           1 |       2 |
|   4 | N002    | 100.00 |           2 |       2 |
|   5 | N003    | 500.00 |           3 |       4 |
|   6 | T001    | 300.00 |           4 |       3 |
|   7 | T002    | 100.00 |        NULL |       1 |
+-----+---------+--------+-------------+---------+
select * from T_Customer;
+-----+-------+------+
| FId | FName | FAge |
+-----+-------+------+
|   1 | TOM   |   21 |
|   2 | MIKE  |   24 |
|   3 | JACK  |   30 |
|   4 | TOM   |   25 |
|   5 | LINDA | NULL |
+-----+-------+------+

使用内部连接将订单以及对应的客户信息显示出来

select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o JOIN T_Customer c ON o.FCustomerId=c.FId;
+---------+--------+-------------+-------+------+
| FNumber | FPrice | FCustomerId | FName | FAge |
+---------+--------+-------------+-------+------+
| K001    | 100.00 |           1 | TOM   |   21 |
| K002    | 200.00 |           1 | TOM   |   21 |
| T003    | 300.00 |           1 | TOM   |   21 |
| N002    | 100.00 |           2 | MIKE  |   24 |
| N003    | 500.00 |           3 | JACK  |   30 |
| T001    | 300.00 |           4 | TOM   |   25 |
+---------+--------+-------------+-------+------+

可以看到T002这个订单没有显示出来，因为其FCustomerId为NULL，找不到对应的客户信息。
有时候需要显示出这些NULL值的数据，就需要使用外部连接
外部连接分为左外部连接、右外部连接和全外部连接。
左外部连接：返回左表中的全部数据
右外部连接：返回右表中的全部数据
全外部连接：左右表中的数据都返回
左外部连接：

select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o LEFT JOIN T_Customer c ON o.FCustomerId=c.FId;
+---------+--------+-------------+-------+------+
| FNumber | FPrice | FCustomerId | FName | FAge |
+---------+--------+-------------+-------+------+
| K001    | 100.00 |           1 | TOM   |   21 |
| K002    | 200.00 |           1 | TOM   |   21 |
| T003    | 300.00 |           1 | TOM   |   21 |
| N002    | 100.00 |           2 | MIKE  |   24 |
| N003    | 500.00 |           3 | JACK  |   30 |
| T001    | 300.00 |           4 | TOM   |   25 |
| T002    | 100.00 |        NULL | NULL  | NULL |
+---------+--------+-------------+-------+------+

右外部连接

select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o RIGHT JOIN T_Customer c ON o.FCustomerId=c.FId;
+---------+--------+-------------+-------+------+
| FNumber | FPrice | FCustomerId | FName | FAge |
+---------+--------+-------------+-------+------+
| K001    | 100.00 |           1 | TOM   |   21 |
| K002    | 200.00 |           1 | TOM   |   21 |
| T003    | 300.00 |           1 | TOM   |   21 |
| N002    | 100.00 |           2 | MIKE  |   24 |
| N003    | 500.00 |           3 | JACK  |   30 |
| T001    | 300.00 |           4 | TOM   |   25 |
| NULL    |   NULL |        NULL | LINDA | NULL |
+---------+--------+-------------+-------+------+

全外部连接

select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o FULL OUTER JOIN T_Customer c ON o.FCustomerId=c.FId;
MYSQL不支持使用FULL OUTER JOIN，可以使用左外部连接和右外部连接来模拟全外部连接。
select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o LEFT JOIN T_Customer c ON o.FCustomerId=c.FId 
UNION
select o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge from T_Order o RIGHT JOIN T_Customer c ON o.FCustomerId=c.FId;
+---------+--------+-------------+-------+------+
| FNumber | FPrice | FCustomerId | FName | FAge |
+---------+--------+-------------+-------+------+
| K001    | 100.00 |           1 | TOM   |   21 |
| K002    | 200.00 |           1 | TOM   |   21 |
| T003    | 300.00 |           1 | TOM   |   21 |
| N002    | 100.00 |           2 | MIKE  |   24 |
| N003    | 500.00 |           3 | JACK  |   30 |
| T001    | 300.00 |           4 | TOM   |   25 |
| T002    | 100.00 |        NULL | NULL  | NULL |
| NULL    |   NULL |        NULL | LINDA | NULL |
+---------+--------+-------------+-------+------+

子查询

SQL语句允许将一个查询语句作为一个结果集供其他SQL语句调用，被当作结果集的查询语句被称为子查询，所有使用表的地方几乎都可以使用子查询来代替。

CREATE TABLE T_Reader (FId INT NOT NULL ,FName VARCHAR(50),FYearOfBirth INT,FCity VARCHAR(50),FProvince VARCHAR(50), FYearOfJoin INT);
CREATE TABLE T_Book (FId INT NOT NULL ,FName VARCHAR(50),FYearPublished INT,FCategoryId INT);
CREATE TABLE T_Category (FId INT NOT NULL ,FName VARCHAR(50));
CREATE TABLE T_ReaderFavorite (FCategoryId INT,FReaderId INT);

select * from T_Reader;
+-----+-------+--------------+-----------+-----------+-------------+
| FId | FName | FYearOfBirth | FCity     | FProvince | FYearOfJoin |
+-----+-------+--------------+-----------+-----------+-------------+
|   1 | Tom   |         1979 | TangShan  | Hebei     |        2003 |
|   2 | Sam   |         1981 | LangFang  | Hebei     |        2001 |
|   3 | Jerry |         1966 | DongGuan  | GuangDong |        1995 |
|   4 | Lily  |         1972 | JiaXing   | ZheJiang  |        2005 |
|   5 | Marry |         1985 | BeiJing   | BeiJing   |        1999 |
|   6 | Kelly |         1977 | ZhuZhou   | HuNan     |        1995 |
|   7 | Tim   |         1982 | YongZhou  | HuNan     |        2001 |
|   8 | King  |         1979 | JiNan     | ShanDong  |        1997 |
|   9 | John  |         1979 | QingDao   | ShanDong  |        2003 |
|  10 | Lucy  |         1978 | LuoYang   | HeNan     |        1996 |
|  11 | July  |         1983 | ZhuMaDian | HeNan     |        1999 |
|  12 | Fige  |         1981 | JinCheng  | ShanXi    |        2003 |
+-----+-------+--------------+-----------+-----------+-------------+

select * from T_Book;
+-----+------------------------+----------------+-------------+
| FId | FName                  | FYearPublished | FCategoryId |
+-----+------------------------+----------------+-------------+
|   1 | About J2EE             |           2005 |           4 |
|   2 | Learning Hibernate     |           2003 |           4 |
|   3 | Two Cites              |           1999 |           1 |
|   4 | Jane Eyre              |           2001 |           1 |
|   5 | Oliver Twist           |           2002 |           1 |
|   6 | History of China       |           1982 |           2 |
|   7 | History of England     |           1860 |           2 |
|   8 | History of America     |           1700 |           2 |
|   9 | History of The World   |           2008 |           2 |
|  10 | Atom                   |           1930 |           3 |
|  11 | RELATIVITY             |           1945 |           3 |
|  12 | Computer               |           1970 |           3 |
|  13 | Astronomy              |           1971 |           3 |
|  14 | How To Singing         |           1771 |           5 |
|  15 | DaoDeJing              |           2001 |           6 |
|  16 | Obedience to Authority |           1995 |           6 |
+-----+------------------------+----------------+-------------+

select * from T_Category;
+-----+------------+
| FId | FName      |
+-----+------------+
|   1 | Story      |
|   2 | History    |
|   3 | Theory     |
|   4 | Technology |
|   5 | Art        |
|   6 | Philosophy |
+-----+------------+

select * from T_ReaderFavorite;
+-------------+-----------+
| FCategoryId | FReaderId |
+-------------+-----------+
|           2 |        11 |
|           1 |        12 |
|           3 |         1 |
|           1 |         3 |
|           4 |         4 |
+-------------+-----------+

子查询有两种分别是，一种是只返回一个单值的子查询，另一种是返回一列值的子查询。

单值子查询

select 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4 from DUAL;
+----+---+------------------------------------------+------+
| f1 | 2 | (SELECT MIN(FYearPublished) FROM T_Book) | f4   |
+----+---+------------------------------------------+------+
|  1 | 2 |                                     1700 | 2008 |
+----+---+------------------------------------------+------+

列值子查询
列值子查询会返回一个多行多列的结果集，可以将该结果集看作一个临时的表。
列值子查询可以用在SELECT的FROM子句、INSERT语句、连接和IN子句等。

数据库高级话题

SQL调优

索引
索引是数据库调优的最根本的优化方法，一般是在检索时候使用的字段上创建索引。
常用的优化方法
1、在经常需要检索的字段上创建索引
2、使用预编译查询
3、select语句中避免使用，只检索需要用到的列，即使需要检索所有的列，也不要使用，因为DBMS在解析的过程中还要将*依次转换成所有的列名，会消耗更多的时间。
4、尽量将多条SQL语句压缩到一句SQL中，因为每次执行SQL的时候都需要建立网络连接、进行SQL优化、发送结果等过程，这个过程是非常耗时的。
5、用WHERE子句替换HAVING，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。
6、使用表的别名，当SQL连接多个表时，使用别名可以减少语法错误。
7、避免在索引列上使用计算，如果索引列是计算或者函数的一部分，那么DBMS优化器将不再使用索引而进行全表扫描。
8、如果执行一系列操作是以原子形式完成的，就需要使用事务。

最后编辑于：2020.02.08 10:57:37

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,902评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 84,037评论 2赞 377
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,978评论 0赞 332
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,867评论 1赞 272
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,763评论 5赞 360
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,104评论 1赞 277
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,565评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,236评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,379评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,313评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,363评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,034评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,637评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,719评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,952评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,371评论 2赞 346
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,948评论 2赞 341