SQL入门（2）

Union

用于连接两个或多个select结果集，在连接时，如果不用all 即 union all 则过滤重复行。

学生表信息（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

教师表信息（Teachers）：

ID	Name
101	Mrs Lee
102	Lucy

 

 

 

1）基本UNION查询，查询学校教师、学生的总的信息表，包括ID和姓名

SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers

查询结果：（过滤了相同数据）

ID	Name
101	Mrs Lee
101	Tom
102	Lucy

 

 

 

 

2）查询教师学生全部姓名

 因为UNION只会选择不同的值，如果学生中和教师中有重名的情况，这就需要UNION ALL

SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers

查询结果：

ID	Name
101	Tom
102	Lucy
101	Mrs Lee
102	Lucy

 

 

 

  

 

2、INNER JOIN（内连接）

INNER JOIN（内连接），也成为自然连接

作用：根据两个或多个表中的列之间的关系，从这些表中查询数据。

注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

重点：内连接，只查匹配行。

学生表信息（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

专业信息表（Majors）： 

ID	Name
10	English
12	Computer

 

 

 

实例：查询学生信息，包括ID,姓名、专业名称

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID

 查询结果：

ID	Name	MajorName
101	Tom	English

 

 

根据结果可以清晰看到，确实只有匹配的行。学生Lucy的信息丢失了。

3、外连接

与内连接相比，即使没有匹配行，也会返回一个表的全集。

外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULLOUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。

重点：至少有一方保留全集，没有匹配行用NULL代替。

1）LEFT OUTER JOIN，简称LEFT JOIN，左外连接（左连接）

结果集保留左表的所有行，但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。

依然沿用内链接的例子

（1）使用左连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID

 

结果：

ID	Name	MajorName
101	Tom	English
102	Lucy	NULL

 

 

 

 

 

 结论：

通过结果，我们可以看到左连接包含了第一张表的所有信息，在第二张表中如果没有匹配项，则用NULL代替。 

 

2）RIGHT JOIN(right outer join)右外连接(右连接)

右外连接保留了第二个表的所有行，但只包含第一个表与第二个表匹配的行。第一个表相应空行被入NULL值。

右连接与左连接思想类似。只是第二张保留全集，如果第一张表中没有匹配项，用NULL代替

依然沿用内链接的例子，只是改为右连接

（1）使用右连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students RIGHT JOIN Majors
ON Students.MajorID = Majors.ID

查询结果：

ID	Name	MajorName
101	Tom	English
NULL	NULL	Computer

 

 

 

 

 

通过结果可以看到，包含了第二张表Majors的全集，Computer在Students表中没有匹配项，就用NULL代替。 

3）FULL JOIN (FULL OUTER JOIN，全外连接）

全外连接，简称：全连接。会把两个表所有的行都显示在结果表中

1)使用全连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID

查询结果： 

ID	Name	MajorName
101	Tom	English
102	Lucy	NULL
NULL	NULL	Computer

 

 

 

 

 

 

包含了两张表的所有记录，没有记录丢失，没有匹配的行用NULL代替。

4、CROSS JOIN（交叉连接）

交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。 

简单查询两张表组合，这是求笛卡儿积，效率最低。

笛卡儿积：笛卡尔乘积，也叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

1）交叉连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors

查询结果：

ID	Name	MajorName
101	Tom	English
102	Lucy	English
101	Tom	Computer
102	Lucy	Computer

 

 

 

 

 

 

 

2）查询多表，其实也是笛卡儿积，与CROSS JOIN等价，以下查询同上述结果一样。

这个可能很常见，但是大家一定要注意了，这样就查询了两张表中所有组合的全集。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,Majors

3）加了查询条件

注意:在使用CROSS JOIN关键字交叉连接表时，因为生成的是两个表的笛卡尔积，因而不能使用ON关键字，只能在WHERE子句中定义搜索条件。

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID

查询结果：

ID	Name	MajorName
101	Tom	English

 

 

 

 

查询结果与INNER JOIN一样，但是其效率就慢很多了

 

 

 其他常用的SQL，在这里集合。

1、SELECT INTO

从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份或者用于对记录进行存档。

语法：

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

IN 子句可用于向另一个数据库中拷贝表。

1）备份表信息

SELECT ID,Name 
INTO Students_Backup
FROM Students

2)复制到备份库

SELECT *
INTO Students IN 'Backup.mdb'
FROM Students

2、IDENTITY 

作用：创建唯一的，递增的列

注意：一张表中只能有一列为IDENTITY

1）创建学生信息表时，指定其ID为自增列，从1开始，每次递增1

IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
--学生信息表
CREATE TABLE Students(
ID int primary key IDENTITY(1,1) not null,
Name nvarchar(50),
Age int,
Sex bit,
City nvarchar(50),
MajorID int
)

 

2）如果指定了自增列，又需要插入指定ID的值，需要停止INDENTIY，执行后再开启。

SET IDENTITY_Insert Students ON 
insert Students(ID,Name,Age,City) values(10,'Jim',18,'NewYank') 
SET IDENTITY_Insert Students OFF 

3）与SELECT INTO 合用，插入行号

这个常常用于临时表分页时使用。

注意：如果查询的列中有自增列，需要将其删除，或者屏蔽，因为一张表中只有一个IDENTITY字段。

SELECT IDENTITY(int,1,1) AS RowNumber,Name,Age,Sex,City
INTO Students_Backup
FROM Students

SCOPE_IDENTITY() 

还有一个常用的函数，与此一起使用 SCOPE_IDENTITY()

常常在有IDENTITY列的插入时，需要返回当前的行的IDENTITY的列值。

如： 

IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
--学生信息表
CREATE TABLE Students(
    ID int IDENTITY(1,1) PRIMARY KEY not null,
    Name nvarchar(50),
    Age int,
    Sex bit,
    City nvarchar(50),
    MajorID int
)

INSERT INTO Students(Name,Age,Sex,City,MajorID) VALUES('Jim',18,0,'ShangHai',12)
SELECT SCOPE_IDENTITY()

 

这个例子，每次返回插入记录的ID的值。如果有其他关联表用到此ID，这时就不用再从数据库查一遍了。 

3、OBJECT_ID 

返回架构范围内对象的数据库对象标识号。

1）查询表是否存在

SELECT OBJECT_ID(N'Students',N'U')

与以下语句等价：

SELECT id FROM sysobjects WHERE name=N'Students' and type=N'U'

 

2）常常用于创建表、视图时，做判定。保证脚本的重复执行

创建学生信息表时，需要判定该表是否存在，如果存在则删除

IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
--学生信息表
CREATE TABLE Students(
ID int primary key IDENTITY(1,1) not null,
Name nvarchar(50),
Age int,
Sex bit,
City nvarchar(50),
MajorID int
)

 

 4、跨库执行

 

如果系统需要多个数据库，当执行跨库脚本，无需再次进行连接，可以执行如下脚本

 

实例：当前在master库，查询TestDB库的Students表信息：

 

USE master

SELECT * FROM TestDB..Students

 

 

常用函数

1、LEN函数

计算字段值的长度

SELECT LEN(Name) AS NameLength FROM Students

2、FORMAT函数

FORMAT 函数用于对字段的显示进行格式化。

语法：

SELECT FORMAT(column_name,format) FROM table_name

1）时间格式化

SELECT FORMAT(GETDATE(),'yyyy-MM-dd') 

结果：2014-05-13

3、CAST函数

1、实例：将价格转为整型

SELECT CAST(Price AS smallint) AS CPrice FROM Orders

结果：10

2、将字段解析为XML

SELECT CAST(Scheme AS xml) AS CPrice FROM Orders

结果：

<xml>
  <ProductID>101</ProductID>
  <ProductName>Card</ProductName>
</xml>

 4、CONVERT

语法：

 CONVERT(data_type,expression[,style])

说明: 此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar) 相互转换的时候才用到.

1）实例：时间转换为指定形式

SELECT CONVERT(NVARCHAR(20),GETDATE(),120) 

结果：2014-05-13 23:49:34

2）实例转为XML格式

SELECT CONVERT(xml,Scheme) FROM Orders

结果：

<xml>
  <ProductID>101</ProductID>
  <ProductName>Card</ProductName>
</xml>