浅谈SQL基本概念及其用法

这两天学习了SQL基础知识，简单总结下……

一、数据库概述：

1、DBMS(DataBaseManagementSystem,数据库管理系统)和数据库。平时谈到“数据库”可能有两种含义：MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类(Catalog)
2、数据库的构成-管理软件/服务/数据文件(表,视图...)
3、不同品牌的DBMS有自己的不同的特点：MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。对于开发人员来讲，大同小异
4、SQL的全称是：Structured Query Language（结构化查询语言）SQL<>SQLServer<>MSSQLServer。最常见的错误。
5、除了Access、SQLServerCE等文件型数据库之外，大部分数据库都需要数据库服务器才能运行。学习\开发时是连接本机的数据库，上线运行时是数据库运行在单独的服务器。

二、数据库开中的概念：

1、Catalog(分类)(又叫数据库DataBase,表空间TableSpace),不同类的数据应该放到不同的数据库中
         1）便于对各个Catalog进行个性化管理 
         2）避免命名冲突3安全性更高
2、Table(表):书都放到书架上,碗都放到橱柜中,不同类型的资料放到不同的“格子”中，将这种区域叫做“表”(Table)。不同的表根据放的数据不同进行空间的优化，找起来也方便。
3、列(Column)、字段(Field)要概念

4、Primary（主键）：1）主键就是一个表中每个数据行的唯一标识。不会有重复值的列才能当主键。一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键
2）主键有两种选用策略：业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。

三、数据库的管理：

1、需要安装SQLServer2005或者SQLServer2008，若要使用SQLServer管理工具进行开发还要安装SQL Server Management Studio，还可以使用VisualStudio进行管理
2、使用免费的SQLServerExpress版本，Express版本的服务器名称. \SQLEXPRESS，对于开发人员来讲和其他版本没有区别。
3、SQLServer的两种验证方式：用户名验证和Windows验证，开发时用Windows验证就行。
4、开发人员关注点在开发上,而不是配置/备份等之上,那是DBA做的事情。
创建数据库，创建表，设置主键
5、SQLServer2008中：编辑200行；SQLServer2005中：打开表。
6、常用字段类型：bit(可选值0、1)、datetime、int、varchar、nvarchar（可能含有中文用nvarchar）
7、Nvarchar(50)、Nvarchar(MAX)
8、varchar、nvarchar 和char(n)的区别： char(n)不足长度n的部分用空格填充。Var：Variable，可变的。

四、SQL语句入门：

1、SQL语句是和DBMS“交谈”专用的语句，不同DBMS都认SQL语法
2、SQL语句中字符串用单引号。
3、SQL语句是大小写不敏感的,不敏感指的是SQL关键字,字符串值还是大小写敏感的
4、创建表、删除表不仅可以手工完成,还可以执行SQL语句完成,在自动化部署、数据导入中用的很多,CREATE TABLE T_Person(Id         int NOT NULL,Name nvarchar(50),Age int NULL)、Drop table T_Person1
5、简单的Insert语句。INSERT INTO T_Person(Id,Name,Age) VALUES(1,'Jim',20)
6、（*） SQL主要分DDL（数据定义语言）和DML（数据操作语言）两类。Create Table、Drop Table、Alter Table等属于DDL，            Select、Insert、Update、Delete等属于DML。

7、一个SQL查询例子：(条件查询)

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

select(case when A>B then A else B end),(case when B>C then B else C end ) from T

五、主键的选择：（Globally Unique Identifier(叫全球唯一标识符)）

1、SQLServer中两种常用的主键数据类型：int（或bigint）+标识列(又称自动增长字段);uniqueidentifier(又称Guid、UUID)
2、用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。将字段的“是标识列”设置为“是”，一个表只能有一个标识列。
3、Guid算法是一种可以产生唯一标识的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生成的GUID永远不会重复，无论是同一个计算机上还是不同的计算机。在公元3400年以前产生的GUID与任何其他产生过的GUID都不相同。SQLServer中生成GUID的函数newid()，.Net中生成Guid的方法：Guid.NewGuid()，返回是Guid类型。
4、（*）Int自增字段的优点：占用空间小、无需开发人员干预、易读；缺点：效率低；数据导入导出的时候很痛苦。
5、（*）Guid的优点：效率高、数据导入导出方便；缺点占用空间大、不易读。
6、业界主流倾向于使用Guid。

六、新增（插入）数据：Inert into Table(col,col2...)values(data1,data2...)

1、Insert语句可以省略表名后的列名，但是不推荐
2、如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。
3、给可以给字段默认值，如果Guid类型主键的默认值设定为newid()就会自动生成，很少这么干
4、主键：
     insert into Person3(Name,Age) values('lily',38);
     insert into Person4(Id,Name,Age) values(newid(),'tom',30);

七、更新数据： update Table set col=value,col2=value2 where ...
1、更新一个列:UPDATE T_Person Set Age=30
2、更新多个列:UPDATE T_Person Set Age=30,Name=‘tom’
3、更新一部分数据： UPDATE T_Person Set Age=30 where Name=‘tom’，用where语句表示只更新Name是’tom’的行，注意SQL中等于判断用单个=，而不是==
4、Where中还可以使用复杂的逻辑判断UPDATE T_Person Set Age=30 where Name=‘tom’ or Age<25，or相当于C#中的||（或者）
5、update Person1 set NickName=N'二十岁' 
6、where (Age>20 and Age<30) or(Age=80)
7、Where中可以使用的其他逻辑运算符：or、and、not、<、>、>=、<=、!=（或<>）等

八、删除数据：delete [from] Table where col=data
1、删除表中全部数据：DELETE FROM T_Person。
2、Delete只是删除数据，表还在，和Drop Table不同。
3、Delete 也可以带where子句来删除一部分数据：DELETE FROM T_Person WHERE FAge > 20

九、数据检索： select col,col2[,*] from Table where ...
1、执行备注中的代码创建测试数据表。
2、简单的数据检索 ：SELECT * FROM T_Employee
3、只检索需要的列 ：SELECT FNumber FROM T_Employee 、SELECT FName,FAge FROM T_Employee
4、列别名：SELECT FNumber AS 编号,FName AS 姓名,FAge AS Age111 FROM T_Employee 
5、使用where检索符合条件的数据：SELECT FName FROM T_Employee WHERE FSalary<5000。
6、还可以检索不与任何表关联的数据：select 1+1;select newid();select getdate();

十、数据汇总(聚合函数)

1、SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）
2、大于25岁的员工的最高工资 ：
      SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25 
3、最低工资和最高工资：
      SELECT MIN(FSalary),MAX(FSalary) FROM  T_Employee 

十一、数据排序：

1、ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。 
2、按照年龄升序排序所有员工信息的列表：
    SELECT * FROM  T_Employee ORDER BY FAge ASC 
3、按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序 ：SELECT * FROM  T_Employee ORDER BY FAge                   DESC,FSalary DESC（多个排序条件）
4、ORDER BY子句要放到WHERE子句之后 ：SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary     DESC 

十二、通配符过滤：

1、通配符过滤关键字使用LIKE 。
2、单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。
   eg:以任意字符开头，剩余部分为“erry”
   SELECT * FROM T_Employee WHERE FName LIKE '_erry' 
3、多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。 “k%”匹配以“k”开头、任意长度的字符串
   eg:检索姓名中包含字母“n”的员工信息 
   SELECT * FROM T_Employee WHERE FName LIKE '%n%'.

十三、空值处理：

1、数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null，数据库中的null表示“不知道”，而不是表示没有。
2、因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。
3、SELECT * FROM T_Employee WHERE FNAME=null ； SELECT * FROM T_Employee WHERE FNAME!=null ；
    都没有任何返回结果，因为数据库也“不知道”。
4、SQL中使用is null、is not null来进行空值判断： 
5、SELECT * FROM T_Employee WHERE FNAME is null ； SELECT * FROM T_Employee WHERE FNAME is not null ；

十四、数据分组：

1、按照年龄进行分组统计各个年龄段的人数：SELECT FAge,Count(*) FROM T_Employee GROUP BY Fage
2、GROUP BY子句必须放到WHERE语句的之后 
3、没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)
错误：
 SELECT FAge,FSalary FROM T_Employee GROUP BY FAge 
正确：
 SELECT FAge,AVG(FSalary) FROM T_Employee GROUP BY FAge

十五、 Having语句(分组后查询条件)

1、在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后：
    SELECT FAge,COUNT(*) AS 人数 FROM T_Employee 
    GROUP BY FAge 
    HAVING COUNT(*)>1 
2、注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。
3、顺序：先where,后group by,再having.

十六、限制结果集行数

1、SELECT top 5 * FROM T_Employee order by FSalary Desc 
2、（*）检索按照工资从高到低排序检索从第六名开始一共三个人的信息 ：
    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 
3、SQLServer2005后增加了Row_Number函数简化实现，后面会讲。

十七、DISTINCT：去掉数据重复 

1、执行备注中的SQL语句，Alter和Insert单独执行
    SELECT FDepartment FROM T_Employee
    SELECT DISTINCT FDepartment FROM T_Employee 

2、DISTINCT是对整个结果集进行数据重复处理的，而不是针对每一个列，因此下面的语句并不会只保留Fdepartment进行重复值处理：
    SELECT DISTINCT FDepartment,FSubCompany 
     FROM T_Employee 

十八、UNION：联合结果集

1、执行备注中的代码
2、简单的结果集联合：
3、SELECT FNumber,FName,FAge FROM T_Employee  UNION  SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee 
4、基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。 
SELECT FNumber,FName,FAge,FDepartment FROM T_Employee  UNION  SELECT FIdCardNumber,FName,FAge,‘临时工，无部门' FROM T_TempEmployee

十九、UNION  ALL

SELECT FName FROM T_Employee UNION  
SELECT FName FROM T_TempEmployee
1.UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条
SELECT FName FROM T_Employee
UNION ALL
SELECT FName FROM T_TempEmployee 
2.Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

案例：

1）要求查询员工的最低年龄和最高年龄，临时工和正式员工要分别查询 
     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   UNION ALL

2）查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计。 
SELECT FNumber,FSalary FROM T_Employee   UNION ALL     SELECT '工资合计',SUM(FSalary) FROM T_Employee

二十、数字函数：

执行备注中的代码：
1、ABS() ：求绝对值。
2、CEILING()：舍入到最大整数 。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。 Ceiling→天花板
3、FLOOR()：舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。 Floor→地板。
4、ROUND()：四舍五入。舍入到“离我半径最近的数” 。Round→“半径”。Round(3.1425,2)。

二十一、字符串函数：

1、LEN() ：计算字符串长度
2、LOWER() 、UPPER () ：转小写、大写
3、LTRIM()：字符串左侧的空格去掉 
4、RTRIM () ：字符串右侧的空格去掉 
   LTRIM(RTRIM('         bb        '))
5、SUBSTRING(string,start_position,length)
  参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。
     SELECT  SUBSTRING('abcdef111',2,3) 

二十二、日期函数：

1、GETDATE() ：取得当前日期时间 
2、DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位，可选值见备注。DATEADD(DAY, 3,date)为计算日期date3天后的日期，而DATEADD(MONTH ,-8,date)为计算日期date8个月之前的日期 
3、DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 datepart 为计量单位，可取值参考DateAdd。
统计不同工龄的员工的个数：
select DateDiff(year,FInDate,getdate()),count(*) from T_Employee group by DateDiff(year,FInDate,getdate())
4、DATEPART (datepart,date)：返回一个日期的特定部分 
统计员工的入职年份个数：
select DatePart(year,FInDate),count(*) from T_Employee
               group by DatePart(year,FInDate)

二十三、类型转换函数：

1、CAST ( expression AS data_type)
2、CONVERT ( data_type, expression) 
3、SELECT FIdNumber,
     RIGHT(FIdNumber,3) as 后三位,
     CAST(RIGHT(FIdNumber,3) AS INTEGER) as 后三位的整数形式,
     CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 as 后三位加1,
     CONVERT(INTEGER,RIGHT(FIdNumber,3))/2 as 后三位除以2
    FROM T_Person

二十四：空值处理函数：

1、执行备注中的代码
2、ISNULL(expression,value) ：
    如果expression不为空则返回expression，否则返回value
    SELECT ISNULL(FName,'佚名') as 姓名 FROM
    T_Employee 

二十五：CASE函数用法：

1、单值判断，相当于switch case
CASE expression 
           WHEN value1 THEN returnvalue1 
           WHEN value2 THEN returnvalue2 
           WHEN value3 THEN returnvalue3 
           ELSE defaultreturnvalue 
      END 
2、例子：  
SELECT  FName, 
           (CASE FLevel   WHEN 1 THEN 'VIP客户'  
           WHEN 2 THEN '高级客户'  
           WHEN 3 THEN '普通客户' 
           ELSE '客户类型错误'  
           END) as FLevelName 
      FROM T_Customer

3、测试数据在备注中
CASE 
           WHEN condition1 THEN returnvalue1 
           WHEN condition 2 THEN returnvalue2 
           WHEN condition 3 THEN returnvalue3 
           ELSE defaultreturnvalue 
END 
相当于if…else…else….
select FName,
{
   case
   when  FSalary<2000 then '低收入'  when FSalary>=2000 and FSalary<=5000 then '中等收入'
else '高收入'
end
}as 收入水平 from T_Employee

二十六、索引Index：

1、全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。
2、如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。
3、创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。
4、使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。
5、（*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。

二十七、表连接Join：

1、有客户表（T_Customers）和订单表（T_Orders）两个表，客户表字段为：Id、Name、Age，订单表字段为：Id、BillNo、CustomerId，订单表通过CustomerId关联客户表。测试数据见备注。
2、SELECT o.BillNo,c.Name,c.Age from T_Orders as o
3、JOIN T_Customers as c on o.CustomerId=c.Id
4、join是和哪个表连接，on后是连接的关系是什么。(多表)
5、要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄。
6、要求显示年龄大于平均年龄的顾客购买的订单
7、Inner Join、Left Join、Right Join

二十八：子查询：

1、将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。SELECT * FROM(SELECT * FROM T2 where FAge<30) 
2、执行备注中的SQL。
3、单值做为子查询：SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished)  FROM T_Book) AS f4
4、只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。下面的是错误的：SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)
5、SELECT * FROM T_ReaderFavorite WHERE FCategoryId=(SELECT FId FROM T_Category WHERE FName='Story')

实例：子查询的两种特殊情况：

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。

SELECT * FROM T_Reader    WHERE FYear Of  Join IN(select FYearPublished FROM T_Book)

限制结果集。返回第3行到第5行的数据（ROW_NUMBER 不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）：

SELECT * FROM(SELECT ROW_NUMBER()OVER(ORDER BY FSalary DESC) AS rownum,FNumber,FName,FSalary,FAge FROM T_Employee) AS a  WHERE a.rownum>=3 AND a.rownum<=5

技术不断更新中，在此做的一些笔记肯定不全面，请大家谅解，待续……