SQL常用操作(中)

JOIN 连接

用于根据两个或多个表中的列之间的关系，将这些表的行结合起来并从中查询数据。

下图是LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法：

下面举例介绍内连接(INNER JOIN),外链接(LEFT JOIN,RIGHT JOIN,OUTERJOIN)的用法：

1.INNER JOIN:表中至少有一个匹配时返回行

语法：
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name；

注意：INNER JOIN 和JOIN 是相同的，INNER 可省略。

下面使用"STUDENT"和"SCORE"这两个表，从表结构上看，两表之间能够建立起联系的是s_id列。

实例：SELECT s.s_id,s.s_name,sc.c_id,sc.s_score FROM student as s INNER JOIN score as sc on s.s_id=sc.s_id and sc.c_id=01;

结果集(数学集合中的交集）

c_id=01是我为了方便结果图做的删选条件，上面select语句中用到了上篇讲的别名，我们也可以order by下，只需在后面加上order by column_name ASC|DESC.

注释：INNER JOIN 关键字在表中存在至少一个匹配时返回行，如果"student"表中的行在"score"表中没有匹配，则不显示这些行，比如例子中的"07,08,09"号学生信息。

2.LEFT JOIN:即使右表中没有匹配，也从左表返回所有的行

语法：SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name；

注释：在某些数据库中，LEFT JOIN 写成LEFT OUTER JOIN

实例：SELECT s.s_id,s.s_name,sc.c_id,sc.s_score FROM student as s LEFT JOIN score as sc on s.s_id=sc.s_id and sc.c_id=01;

结果集

只是将上面的select语句中INNER 改为LEFT,结果是多了3条记录，即没在score表中匹配，也会显示出来。

3.RIGHT JOIN:即使左表中没有匹配，也从右表返回所有的行

语法：SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name；

注释：在某些数据库中，RIGHT JOIN 写成RIGHT OUTER JOIN

实例：SELECT sc.s_id,s.s_name,sc.c_id,sc.s_score FROM student as s RIGHT JOIN score as sc on s.s_id=sc.s_id;

这里我们修改下student表数据，将"郑秀竹"这条记录删除，构造在student表中没有匹配，但右边依然返回所有行。

结果集

使用RIGHT JOIN时，以右表为基准。

4.FULL JOIN:只要其中一个表中存在匹配，就返回行

语法：SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name；

注释：在某些数据库中，FULL JOIN 写成FULL OUTER JOIN

逻辑同上。

在使用JOIN时，on 和 where 条件的区别如下：

1.on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

2.where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设两张表

表1

表2

实例1：select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'

结果集

实例2：select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA'）

结果集

2条SQL命令，注意on和where的使用方法，显示过程和结果是不同的。

以上不同结果的关键原因在于 left join、right join、full join 的特殊性，不管 on 上的条件是否为真都会返回 left或 right 表中的记录，full 则具有 left 和 right 的特性的并集。而 inner jion 没这个特殊性，则条件放在 on 中和 where中，返回的结果集是相同的。

UNION 和UNION ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION 语法：

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name；

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION ALL语法：

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name；

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面实例使用的原始表如下：

Employees_China

Employees_USA

实例：SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA；

结果集

注释：在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。但是UNION ALL 可以列出所有雇员的名字。

实例：SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA；

结果集

使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

SELECT INTO 语句

用于创建表的备份复件或者用于对记录进行存档。

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语法：

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename;

实例1：制作备份复件

SELECT * INTO Student_backup FROM Student;(制作Student表的备份复件）

IN 子句可用于向另一个数据库中拷贝表：（可以拷贝所有列或某几列）

SELECT * INTO Student IN 'Backup.mdb' FROM student;

实例2：带有 WHERE 子句

SELECT s_name,s_birth INTO Student_backup FROM Student where s_sex='男';

上面命令是从student表中提取性别是男的学生姓名及出生日期2列，组成新的student_backup表。

实例3：被连接的表

下面的例子创新一个新表：Student_Score_Backup,其中包含从student和score两个表中取得的信息

SELECT student.s_name,score.c_id,score.s_score INTO Student_Score_Backup FROM student INNER JOIN score ON student.s_Id=score.s_Id;

提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可

SELECT * INTO newtable FROM table1 WHERE 1=0;

INSERT INTO SELECT 语句

是从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

INSERT INTO SELECT 语法：

INSERT INTO table2 SELECT * FROM table1;（插入所有列）

INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;（需要的列）

select into from 和 insert into select 都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from要求目标表存在。

CREATE DATABASE 语句

用于创建数据库

CREATE DATABASE 语法：

CREATE DATABASE database_name;

实例：CREATE DATABASE User_Info;

CREATE TABLE 语句

用来创建数据库中的表

CREATE TABLE 语法：

CREATE TABLE 表名称 (列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,....)

数据类型&描述

实例：创建一个名为 "Persons" 的表，包含五列：ID_P、LastName、FirstName、Address 和 City。

CREATE TABLE Persons(

Id_P int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255));

Id_P 列的数据类型是 int，含整数。其余 4 列的数据类型是 varchar，最大长度为 255 个字符。

空的"Persons"表如下：（可通过INSERT INTO 插入数据）

Persons

下一遍从"SQL 约束"开始......