笔记——《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;