笔记——《SQL从入门到提高》

本笔记整理了杨中科老师在传智播客.Net培训的《SQL从入门到提高》视频讲座。

视频相关链接：http://www.rupeng.com/forum/tj-16325-2477.html

数据库相关概念

 

1、什么是索引？优缺点是什么？

索引是对数据库表中一列或多列的值进行排序的一种单独的、物理的数据库结构。
   优点：
　1) 大大加快数据的检索速度；
　2) 创建唯一性索引，保证数据库表中每一行数据的唯一性；
　3) 加速表和表之间的连接；
　4) 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
   缺点：
　1) 索引需要占物理空间；
　2) 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

2、业务主键和逻辑主键

业务主键是使用有业务意义的字段做主键，比如身份证号，银行账号等；

逻辑主键是使用没有任何业务意义的字段做主键。因为很难保证业务主键不会重复（身份证号重复）、不会变化（账号升位），因此推荐使用逻辑主键。

SQL Server 的使用

 

1、SQL Server 两种常用的主键数据类型

  1) int(或 bigint) + 标识列（又称自动增长字段）
     用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。
     优点：占用空间小、无需开发人员干预、易读；
     缺点：效率低，数据导入导出的时候很痛苦。
     设置：“修改表”->选定主键->“列属性”->“标识规范”选择“是”

  2) uniqueidentifier(又称GUID、UUID)
     GUID算法是一种可以产生唯一表示的高效算法，它使用网卡MAC、地址、纳秒级时  间、芯片ID码等算出来的，这样保证每次生成的GUID永远不会重复，无论是同一计算机还是不同计算机。在公元3400年前产生的GUID与任何其他产生过的GUID都不相同。SQL Server中生成GUID的函数newid()。
     优点：效率高、数据导入导出方便；
     缺点：占用空间大、不易读。

  业界主流倾向于使用GUID。

SQL 基础

 

1、DDL(数据定义语言)

 

  1) 创建数据表：

?

--创建表，SQL Server 的数据类型.

 

CREATE TABLE T_Person(

    Id int NOT NULL,

    Name nvarchar(50),

    Age int NULL,

    PRIMARY KEY(Id));

 

--创建表，添加外键.

 

CREATE TABLE T_Users(

    StudentNo CHAR(4),

    CourseNo CHAR(4),

    Score INT,

    PRIMARY KEY(StudentNo),

    FOREIGN KEY(CourseNo) REFERENCES T_Course(CourseNo));

  2) 修改表结构：

?

--修改表结构，添加字段

ALTER TABLE T_Person ADD NickName nvarchar(50) NULL;

 

--修改表结构，删除字段

ALTER TABLE T_Person DROP NickName;

  3) 删除数据表：

?

DROP TABLE T_Person;

 4) 创建索引：

?

CREATE [UNIQUE] INDEX <索引名> ON <基本表名>(<列名序列>);

 

2、DML(数据操纵语言)

 

  1) 插入语句：

?

INSERT INTO T_Person(Id, Name, Age) VALUES(1, 'Jim', 20);

  2) 更新语句：

?

UPDATE T_Person SET Age=Age+1 where Name='tom' or Age<25;

  3) 删除语句：

?

--删除表中所有记录.

DELETE FROM T_Person;

 

--删除表中指定记录.

DELETE FROM T_Person WHERE Age>20;

  4) 查询语句：

 

?

--简单的数据查询.

SELECT * FROM T_Employee;

 

--只查询需要的列.

SELECT FNumber FROM T_Employee;

 

--给列取别名.

SELECT FNumber AS 编号, FName AS 姓名 FROM T_Employee;

 

--使用 WHERE 查询符合条件的记录.

SELECT FName FROM T_Employee WHERE FSalary<5000;

 

--对表记录进行排序.

SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC;

 

--ORDER BY 子句要放在 WHERE 子句之后.

SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC;

 

--WHERE 中可以使用的逻辑运算符：or、and、not、<、>、=、>=、<=、!=、<>等.

 

--查询不与任何表关联的数据.

SELECT 1+1;

SELECT newid();

SELECT getdate();

 

--模糊匹配，首字母未知.

SELECT * FROM T_Employee WHERE FName LIKE '_arry';

 

--模糊匹配，前后多个字符未知.

SELECT * FROM T_Employee WHERE FName LIKE '%n%';

 

 

--NULL 表示“不知道”，有 NULL 参与的运算结果一般都为 NULL.

 

--查询数据是否为 NULL，不能用 = 、!= 或 <>.

SELECT * FROM T_Employee WHERE FName IS NULL;

SELECT * FROM T_Employee WHERE FName IS NOT NULL;

 

--查询在某个范围内的数据，IN 表示包含于.

SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28);

 

--下面两句等价。

SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30;

SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30;

数据分组

?

--下面语句操作顺序：先按年龄相同的进行分组，然后显式每组年龄，并统计出每组行数.

--注意：聚合函数是对分组操作结果进行运算.

SELECT FAge,COUNT(*) FROM T_Employee GROUP BY FAge;

 

--下面语句错误，因为一组中各行姓名 FName 是不同的，

--不能使用没有在 GROUP BY 中出现的字段，聚合函数除外.

SELECT FAge,FName,COUNT(*) FROM T_Employee GROUP BY FAge;

 

--可以像下面这样写，因为一组中最大工资只有一个.

SELECT FAge,MAX(FSalary),COUNT(*) FROM T_Employee GROUP BY FAge;

 

--GROUP BY 应该放在 WHERE 的后面，聚合函数是不能出现在 WHERE 语句中的，

--WHERE 是对原始表记录进行条件操作，ORDER BY 是在条件操作之后进行分组操作，

--HAVING 放在 ORDER BY 后面，用来对分组操作的结果集进行筛选操作，

--HAVING 是 ORDER BY 的子句，不能代替 WHERE，HAVING 中的字段必须出现在 GROUP BY 中.

 

--下面语句操作顺序：先按年龄相同的进行分组，然后筛选出行数大于 1 的分组。

SELECT FAge,COUNT(*) FROM T_Employee

    GROUP BY FAge

        HAVING COUNT(*)>1;

 

--下面语句是错误的，因为 FSalary 在每个分组中不是唯一的，不能用来标识分组.

--HAVING能用的列和 SELECT 中能用的列是一样的。

SELECT FAge,COUNT(*) FROM T_Employee

    GROUP BY FAge

        HAVING FSalary>2000;

限制结果集

?

--限制结果集的行数：

 

SELECT TOP 5 * FROM T_Employee ORDER BY FSalary DESC;

 

--下面语句意图：按照工资从高到低排序，检索从第六名开始一共三个人的信息。

--操作顺序：从内层到外层

--先查询倒序前五名的编号，然后排除前五名编号，

--得到的数据从第六名开始，最后按倒序从第六名开始取前三名。

 

SELECT TOP 3 * FROM T_Employee

    WHERE FNumber NOT IN (

        SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)

    ORDER BY FSalary DESC;

 

 

--去除重复数据，所去除的是完全重复的记录，即所有字段都相同者去掉。

 

--查询所有员工的部门信息，部门信息重复.

SELECT FDepartment FROM T_Employee;

 

--想要查询公司包含多少个部门，结果集中部门信息重复，加关键字 DISTINCT 可以消除重复。

SELECT DISTINCT FDepartment FROM T_Employee;

联合结果集

?

--把 UNION 前后的查询结果集合为一个结果集，返回的结果字段数必须相同，各字段类型必须相容。

--默认情况下联合结果集会自动去除重复。如果不想去除重复数据，需要在 UNION 后加上 ALL。

 

SELECT FName,FAge,'临时工，无部门' FROM T_TempEmployee

UNION

SELECT FName,FAge,FDepartment FROM T_Employee;

 

--不去除重复的联合结果集.

SELECT FName,FAge,'临时工，无部门' FROM T_TempEmployee

UNION ALL

SELECT FName,FAge,FDepartment FROM T_Employee;

 

--使用联合结果集做报表.

SELECT '正式员工最高年龄',MAX(FAge) FROM T_Employee

UNION ALL

SELECT '正式员工最低年龄',MIN(FAge) FROM T_Employee

UNION ALL

SELECT '临时工最高年龄',MAX(FAge) FROM T_TempEmployee

UNION ALL

SELECT '临时工最低年龄',MIN(FAge) FROM T_TempEmployee;

 

--使用联合结果集查询所用员工工资合计.

SELECT FNumber, FSalary FROM T_Employee

UNION

SELECT '工资合计',SUM(FSalary) FROM T_Employee;

SQL Server 部分函数

1、数学函数

ABS()：求绝对值
CEILING()：舍入到最大整数（上限）。CEILING(3.33)=4，CEILING(-3.33)=-3
FLOOR()：舍入到最小整数（下限）。FLOOR(3.33)=3，FLOOR(-3.33)=-4
ROUND()：四舍五入。舍入到“离我半径最近的数”。ROUND(3.1415,3)=3.142

2、字符串函数

LEN()：求字符串长度。LEN('abc')=3
LOWER()、UPPER()：转小写、大写
LTRIM()：去掉字符串左侧空格
RTRIM()：去掉字符串右侧空格
SUBSTRING(string,start_position,length)：取子串，参数 string 为主字符串，start_position 为子字符串在主字符串中的起始位置，length 为子字符串的最大长度。

3、日期函数

GETDATE()：取当前日期时间
DATEADD(datepart,number,date)：计算增加以后的日期。参数 date 为待计算的日期，参数 datepart 为计量单位（YEAR,YY,MONTH,MM,DAY,DD 等）DATEADD(DAY,3,date)为计算日期 date 的 3 天后的日期，DATEADD(MONTH,-8,date)为计算日期 date 的 8 个月前的日期
DATEDIFF(datepart,startdate,enddate)：计算两个日期之间的差额。datepart 为计量单位
DATEPART(datepart,date)：返回一个日期的特定的部分

?

--查询员工入职年数.

SELECT FName,FInDate,DATEDIFF(YEAR,FInDate,GETDATE()) FROM T_Employee;

 

--查询各入职年数的员工个数.

SELECT DATEDIFF(YEAR,FInDate,GETDATE()),COUNT(*) FROM T_Employee

    GROUP BY DATEDIFF(YEAR,FInDate,GETDATE());

4、类型转换函数

CAST(expression AS date_type)
CONVERT(date_type,expression)

?

--字符串转换为整数

SELECT CAST('123' AS INTEGER);

 

--字符串转换成日期时间类型

SELECT CAST('2008-08-08' AS DATETIME), CONVERT(DATETIME,'2008-08-08');

SELECT DATEPART(YEAR,CAST('2008-08-08' AS DATETIME));

 

--数字转换成字符串

SELECT CONVERT(varchar(50),123);

 

SELECT FIdNumber,

    RIGHT(FIdNumber,3) AS 后三位,

    CAST(RIGHT(FIdNumber,3) AS INTEGER) AS 后三位的整数形式,

    CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 AS 后三位加一,

    CONVERT(INTEGER,RIGHT(FIdNumber,3))/2 AS 后三位除以2

    FROM T_Person;

5、空值处理函数

ISNULL(expression,value)：如果 expression 不为空则返回 expression，否则返回 value

?

--当记录中 FName 字段为 NULL 时显式“佚名”

SELECT ISNULL(FName,'佚名') AS 姓名 FROM T_Employee;

5、流控制函数

单值判断，相当于 switch-case
CASE expression
WHEN value1 THEN return_value1
WHEN value2 THEN return_value2
WHEN value3 THEN return_value3
ELSE default_return_value
END

?

SELECT FName,(

    CASE FLevel

    WHEN 1 THEN 'VIP客户'

    WHEN 2 THEN '高级客户'

    WHEN 3 THEN '普通客户'

    ELSE '客户类型错误'

    END) AS FLevelName

    FROM T_Customer;

 

--范围判断

SELECT FName,(

    CASE

    WHEN FSalary<2000 THEN '低收入'

    WHEN FSalary>=2000 AND FSalary<=5000 THEN '中等收入' 

    ELSE '高收入'

    END)

    FROM T_Employee;

练习

?

--有一张表T_Scroes，记录比赛成绩：

 

--Date       Name   Scroe

--2008-8-8   拜仁   胜

--2008-8-9   奇才   胜

--2008-8-8   湖人   胜

--2008-8-10  拜仁   负

--2008-8-8   拜仁   负

--2008-8-12  奇才   胜

 

--要求输出下面格式：

--Name  胜  负

--拜仁  1   2

--湖人  1   0

--奇才  2   0

 

--注意：在中文字符串前加 N，比如 N'胜'

 

--下面运算顺序：

 

--先执行内层查询，得到如下中间结果集：

 

--Name   胜  负

--拜仁   1   0

--奇才   1   0

--湖人   1   0

--拜仁   0   1

--拜仁   0   1

--奇才   1   0

 

--然后对中间结果集按队名 Name 进行分组，最后计算出每组“胜”字段的和与“负”字段的和。

 

SELECT Name,

    SUM

       (CASE Score

        WHEN N'胜' THEN 1

        ELSE 0

        END) AS 胜,

    SUM

       (CASE Score

        WHEN N'负' THEN 1

        ELSE 0

        END) AS 负

    FROM T_Scroes

    GROUP BY Name;

?

--创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。

--要求：

--    1) 输出所有数据中通话时间最长的5条记录。

--    2) 输出所有数据中拨打长途号码（对方号码以0开头）的总时长。

--    3) 输出本月通话总时长最多的前三个呼叫员的编号。

--    4) 输出本月拨打电话次数最多的前三个呼叫员的编号。

--    5) 输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。

--          记录呼叫员编号、对方号码、通话时长

--          ......

--          汇总[市内号码总时长][长途号码总时长]

 

 

--Id   CallerNumber   TellNumber    StartDateTime     EndDateTime

--1    001            02088888888   2010-7-10 10:01   2010-7-10 10:05

--2    001            02088888888   2010-7-11 13:41   2010-7-11 13:52

--3    001            89898989      2010-7-11 14:42   2010-7-11 14:49 

--4    002            02188368981   2010-7-13 21:04   2010-7-13 21:18

--5    002            76767676      2010-6-29 20:15   2010-6-29 20:30

--6    001            02288878243   2010-7-15 13:40   2010-7-15 13:56

--7    003            67254686      2010-7-13 11:06   2010-7-13 11:19

--8    003            86231445      2010-6-19 19:19   2010-6-19 19:25

--9    001            87422368      2010-6-19 19:25   2010-6-19 19:36

--10   004            40045862245   2010-6-19 19:50   2010-6-19 19:59

 

 

--创建数据表T_Callers.

 

CREATE TABLE T_Callers

   (Id int NOT NULL,

    CallerNumber varchar(3),

    TellNumber varchar(13),

    StartDateTime datetime,

    EndDateTime datetime,

    PRIMARY KEY(Id));

 

--插入数据.

 

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (1,'001','02088888888','2010-7-10 10:01','2010-7-10 10:05');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (2,'002','02088888888', '2010-7-11 13:41','2010-7-11 13:52');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (3,'003','89898989',      '2010-7-11 14:42',   '2010-7-11 14:49');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (4,'004','02188368981',   '2010-7-13 21:04',   '2010-7-13 21:18');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (5,'005','76767676',      '2010-6-29 20:15',   '2010-6-29 20:30');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (6,'006','02288878243',   '2010-7-15 13:40',   '2010-7-15 13:56');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (7,'007','67254686',      '2010-7-13 11:06',   '2010-7-13 11:19');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (8,'008','86231445',      '2010-6-19 19:19',   '2010-6-19 19:25');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (9,'009','87422368',      '2010-6-19 19:25',   '2010-6-19 19:36');

INSERT INTO T_Callers(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

    VALUES (10,'010','40045862245',   '2010-6-19 19:50',   '2010-6-19 19:59');

 

--修改呼叫员编号.

 

UPDATE T_Callers SET CallerNumber='001' WHERE Id IN (1,2,3,6,9);

UPDATE T_Callers SET CallerNumber='002' WHERE Id IN (4,5);

UPDATE T_Callers SET CallerNumber='003' WHERE Id IN (7,8);

UPDATE T_Callers SET CallerNumber='004' WHERE Id=10;

 

--题 1)：

--@计算通话时间；

--@按通话时间降序排列；

--@取前5条记录。

 

SELECT TOP 5 * FROM T_Callers

    ORDER BY DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC;

 

--题 2)：

--@查询拨打长途号码的记录；

--@计算各拨打长途号码的通话时长；

--@对各拨打长途号码的通话时长进行求和。

 

SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS 长途总时长

    FROM T_Callers

    WHERE TellNumber LIKE '0%';

 

--题 3)：输出本月通话总时长最多的前三个呼叫员的编号.

--@按呼叫员编号进行分组；

--@计算各呼叫员通话总时长；

--@按通话总时长进行降序排列；

--@查询前3条记录中呼叫员的编号。

 

SELECT TOP 3 CallerNumber FROM T_Callers

    GROUP BY CallerNumber

    ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC;

 

--题 4) 输出本月拨打电话次数最多的前三个呼叫员的编号.

--@按呼叫员编号进行分组；

--@计算个呼叫员拨打电话的次数；

--@按呼叫员拨打电话的次数进行降序排序；

--@查询前3条记录中呼叫员的编号。

 

SELECT TOP 3 CallerNumber FROM T_Callers

    GROUP BY CallerNumber

    ORDER BY COUNT(*) DESC;

 

--题5) 输出所有数据的拨号流水，并且在最后一行添加总呼叫时长：

 

--        记录呼叫员编号、对方号码、通话时长

--        ......

--        汇总[市内号码总时长][长途号码总时长]

 

--@计算每条记录中通话时长；

--@查询包含不加 0 号码，即市内号码的记录；

--@计算市内号码通话总时长；

--@查询包含加 0 号码，即长途号码的记录；

--@计算长途号码通话总时长；

--@联合查询。

 

(SELECT

    CallerNumber AS 呼叫员编号,

    TellNumber AS 对方号码,

    DATEDIFF(SECOND,StartDateTime,EndDateTime) AS 通话时长

    FROM T_Callers)

UNION

(SELECT

    '汇总',

    CAST(SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS varchar),

    CAST(

       (SELECT

            SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))

            FROM T_Callers

            WHERE TellNumber LIKE '0%')

        AS varchar)

    FROM T_Callers

    WHERE TellNumber NOT LIKE '0%');

 

 

--另一种查询汇总的方法：

 

SELECT '汇总',

    SUM(

        CASE

        WHEN TellNumber NOT LIKE '0%'

            THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)

        ELSE 0

        END),

    SUM(CASE

        WHEN TellNumber LIKE '0%'

            THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)

        ELSE 0

        END)

    FROM T_Callers;

 

 

--题 1)：输出所有数据中通话时间最长的5条记录.

--@计算通话时间；

--@按通话时间降序排列；

--@取前5条记录。

 

SELECT TOP 5 * FROM T_Callers ORDER BY DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC;

 

 

--题 2)：输出所有数据中拨打长途号码（对方号码以0开头）的总时长.

--@查询拨打长途号码的结果集；

--@计算各拨打长途号码的通话时长；

--@对各拨打长途号码的通话时长进行求和。

 

SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS 长途总时长

    FROM T_Callers

    WHERE TellNumber LIKE '0%';

 

 

--题 3)：输出本月通话总时长最多的前三个呼叫员的编号.

--@按呼叫员编号进行分组；

--@计算各呼叫员通话总时长；

--@按通话总时长进行降序排列；

--@查询前3条记录中呼叫员的编号。

 

SELECT TOP 3 CallerNumber FROM T_Callers

    GROUP BY CallerNumber

    ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC;

 

 

--题 4) 输出本月拨打电话次数最多的前三个呼叫员的编号.

--@按呼叫员编号进行分组；

--@计算个呼叫员拨打电话的次数；

--@按呼叫员拨打电话的次数进行降序排序；

--@查询前3条记录中呼叫员的编号。

 

SELECT TOP 3 CallerNumber FROM T_Callers

    GROUP BY CallerNumber

    ORDER BY COUNT(*) DESC;

 

 

--题5) 输出所有数据的拨号流水，并且在最后一行添加总呼叫时长：

 

--        记录呼叫员编号、对方号码、通话时长

--        ......

--        汇总[市内号码总时长][长途号码总时长]

 

--@计算每条记录中通话时长；

--@查询包含不加 0 号码，即市内号码的记录；

--@计算市内号码通话总时长；

--@查询包含加 0 号码，即长途号码的记录；

--@计算长途号码通话总时长；

--@联合查询。

 

(SELECT

    CallerNumber AS 呼叫员编号,

    TellNumber AS 对方号码,

    DATEDIFF(SECOND,StartDateTime,EndDateTime) AS 通话时长

    FROM T_Callers)

UNION

(SELECT

    '汇总',

    CAST(SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) AS varchar),

    CAST(

       (SELECT

            SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))

            FROM T_Callers

            WHERE TellNumber LIKE '0%')

        AS varchar)

    FROM T_Callers

    WHERE TellNumber NOT LIKE '0%');

 

 

--另一种查询汇总的方法：

SELECT '汇总',

    SUM(

        CASE

        WHEN TellNumber NOT LIKE '0%'

            THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)

        ELSE 0

        END),

    SUM(CASE

        WHEN TellNumber LIKE '0%'

            THEN DATEDIFF(SECOND,StartDateTime,EndDateTime)

        ELSE 0

        END)

    FROM T_Callers;