黑马程序员——SQL入门

------- Windows Phone 7手机开发、 .Net培训、期待与您交流！ -------

SQLServer的管理

• 需要安装 SQLServer2005 或者 SQLServer2008 ，若要使用 SQLServer 管理工具进行开发还要安装 SQL Server Management Studio ，还可以使用 VisualStudio 进行管理。
• 使用免费的 SQLServerExpress 版本， Express 版本的服务器名称 .\SQLEXPRESS ，对于开发人员来讲和其他版本没有区别。
• SQLServer 的两种验证方式：用户名验证和 Windows 验证，开发时用 Windows 验证就行了。
• 开发人员关注点在开发上，而不是配置、备份等之上，那是 DBA （数据库管理员）做的事情。

常用字段类型：bit(可选值0,1)、datetime、int、varchar、nvarchar(可能含有中文的用nvarchar)

Varchar、nvarchar和char(n)的区别：char(n)不足长度n的部分用空格填充。

• SQL 语句是和 DBMS “交谈”专用的语句，不同 DBMS 都认 SQL 语法。
• SQL 语句中字符串用单引号。
• SQL 语句是大小写不敏感的，不敏感指的是 SQL 关键字，字符串值还是大小写敏感的。
• SQL 主要分 DDL （数据定义语言）和 DML （数据操作语言）两类。 Create Table 、 Drop Table 、 Alter Table 等属于 DDL ， Select,Insert,Update,Delete 等属于 DML 。
• SQLServer 中两种常用的主键数据类型： int （或 bigint ） + 标识列（又称自动增长字段）； uniqueidentifier( 又称 Guid ， UUID)
• 用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在 Insert 的时候不要指定主键的值。
• Guid 算法是一种产生唯一标识的高效算法，它使用网卡 MAC ，地址，纳秒级世界，芯片 ID 码等算出来。保证不会重复。 SQLServer 中生成 GUID 的函数 newid() ， .net 中生成 Guid 的方法： Guid.NewGuid(), 返回是 Guid 类型。
• Int 自增字段的优点 : 占用空间小，无需开发人员干预，易读；缺点：效率低：数据导入导出的时候很痛苦（两个数据库合并的时候， id 的重复使得合并过程很痛苦）。
• Guid 的优点：效率高，数据导入导出方便；缺点占用空间大，不易读。

创建表：

create table T_Person(Id int identity(1,2) not null primary key, Name nvarchar(50),Age int null default 20)

其中primary key把Id设置为主键。

identity(1,2)把主键Id设置为自动增长起始值为1，步长为2。

删除表：

drop table T_Person

插入数据：

insert into T_Person(Name,Age) values(’Jim’,20)

其中主键Id已设置为自动增长，所以不需要赋值。

• Insert 语句可以省略表名后的列名，但是不推荐。
• 如果插入的行中有些字段的值不确定，那么 insert 的时候不指定那些列即可。

更新一列数据：update T_Person set Age=45

更新多列数据：update T_Person set Age=30,Name=’tom’

更新一部分数据：update T_Person set Age=42 where Name=’tom’

用where语句表示只更新Name是’tom’的行，注意SQL中等于判断用单个=，而不是==。

Where中还可以使用复杂的逻辑判断

update T_Person set Age=40 where Name=’tom’ or Age>25

其中or想到于C#中的||（或者）。

Where中可以使用的其他逻辑运算符：or,and,not,<,>,>=,<=,!=(或<>)等。

数据删除：

Delete from T_Person

Delete只是删除数据，表还在，和drop Table不同。

Delete 也可以带where子句来删除一部分数据：

Delete from T_Person where Age>20

数据检索

简单的检索：Select * from T_Person

只检索需要的列：

select Name from T_Person

select Name,Age from T_Person

给列取别名：

Select Name as 姓名，Age as 年龄from T_Person

使用where检索符合条件的数据：

Select Name from T_Person where Age>45

还可以检索不与表关联的数据：

Select 1+1;select newid();select getdate();select @@version

Select count(*)from T_Person

Select max(Age) from T_Person

Select min(Age) fromT_Person

Select avg(Age) from T_Person

Select sum(Age) from T_Person

数据排序：order by

Order by子句位于select语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC），还是降序（从大到小排序，DESC）。

按照年龄升序排序：

Select * from T_Person order by Age ASC

按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序：

Select * from T_Person order by Age DESC, Salary DESC

Order by子句要放到where子句之后：

Select * from T_Person

where Age>23

Order by Age DESC, Salary DESC;

通配符过滤

• 通配符过滤使用Like 。
• “_”匹配单个字符。以任意字符开头，剩余部分为“ erry ”：

Select * from T_Person where Name like ‘_erry’

• “%”匹配任意字符（零个或多个）。检索姓名中包含字母”n”的员工信息：

Select * from T_Person where Name like ‘%n%’

空值处理：

• 数据库中，一个列如果没有指定值，那么值就为 null, 数据库中的 null 表示“不知道”，而不是表示没有。因此 select null+1 结果是

null，因为“不知道”加1的结果还是“不知道”。

• SQL 中使用is null ,is not null来进行空值判断：

Select * from T_Person where Name is null;

Select * from T_Person where Name is not null;

多值匹配：

Select * from T_Person where Age in(23,24,26)

范围值：

Select * from T_Person where Age>=23 and Age<=27;

Select * from T_Person where Age between 23 and 27;

数据分组：

按照年龄进行分组统计各个年龄段的人数：

Select Age,Count(*) from T_Person group by Age

Group by 子句必须放到where语句之后。

没有出现group by子句中的列是不能放到select语句后的列名列表中的（聚会函数除外）

错误：select Name,Age from T_Person group by Age

正确：select Age from T_Person group by Age

Having语句

• 在 where 中不能使用聚合函数，必须使用 Having ，Having要位于 Group by 之后，

select Age，count(*) as人数 from T_Person

Group by Age

Having count(*)>1;

• Having 不能取代 where 。作用不一样， Having 是对组进行过滤。

限制结果集行数：

Select top 5 * from T_Person order by Age DESC。

（*）检索按照年龄高低排序检索从第六名开始一共三个人的信息：

Select top 3 * from T_Person

Where Id not in(select top 5 Id from T_Person order by Age DESC)

Order by Age DESC

SQLServer2005后增加了Row_Number函数简化实现，在本文最后会提到。

去掉重复数据：

Select distinct Name from T_Person

• Distinct 是对整个结果集进行数据重复处理的，而不是针对每一个列，

Select distinct Name,Age from T_Person（结果中就会出现同名的但年龄不会相同，或者年龄相同但名字绝对不会相同）

联合结果集：union

• Union 合并两个查询结果集，并且将其中完全重复的数据行合并为一条。
• Union 因为要进行重复值扫描，所以效率低。
• 因此如果不是确定要合并重复行，那么就用Union all

数字函数（*）

• ABS(): 求绝对值
• Ceiling(): 舍入到最大整数。 3.33 将被舍入为 4 。Ceiling--> 天花板
• Floor(): 舍入到最小整数。 3.33 将舍入为 3 。Floorà地板
• ROUND(): 四舍五入。舍入到“离我半径最近的数”。 Round—> “半径”。Round(3.144,2)=3.14

字符串函数（*）

• Len(): 计算字符串长度
• Lower(),upper(): 转小写，大写。
• Ltrim(): 字符串左侧的空格去掉
• Rtrim(): 字符串右侧的空格去掉
• Ltrim(rtrim(‘ bb ’)) 结果就是’bb’
• Substring(string,start_position,length)

参数string为父字符串，start_position为字符串在父字符串中的起始位置，length为子字符串的长度。

Select substring(‘abcdef111’,2,3)结果为’bcd’

日期函数：

• Getdate(): 取得当前日期时间
• Dateadd(datepart,number,date), 计算增加以后的日期。参数 date 为计算的日期；参数 number 为增量；参数 datepart 为计量单位。

Dateadd(day,3,date)为计算日期date的3天后的日期。

Dateadd(month,-8,date)为计算日期date的8个月前的日期。

• Datediff(datepart,startdate,enddate): 计算两个日期之间的差额。
• Datepart(datepart,date): 返回一个日期的特定部分
• 统计员工的入职年份个数：

Select datepart(year,InDate),count(*)

From T_Employee

Group by DatePart(year,InDate)

类型转换函数：

• Cast(expression as date_type)
• Convert(date_type,expression)
• Select Age,right(Age,1)as 最后一位

空值处理函数

• Isnull(expression,value): 如果 expression 不为空则返回 expression ，否则返回 value 。

Select isnull(Name,’匿名’)as姓名from T_Person

Case函数用法

单值判断，相当于switch case

Case expression

When value1 then returnvalue1

When value2 then returnvalue2

When value3 then returnvalue3

Else defaultreturnvalue

End

例子：

Select Name

(case Level

when 1 then ‘VIP客户’

when 2 then ‘高级客户’

when 3 then ‘普通客户’

else ‘客户类型错误’

)as LevelName

From T_Customer

索引

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

表连接Join

• 有客户表（ T_Customers ）和订单表（ T_Orders ）两个表，客户表字段为： Id,Name,Age. 订单表地段为： Id,BillNo,CustomerId. 订单表通过 CustomerId 关联客户表。

Select o.BIllNo,c.Name,c.Age

From T_Order as o Join T_Customers as c on o.CustomerId=c.Id

子查询：

• 将一个查询语句作为一个结果集供其他 SQL 语句使用，就像使用普通的表一样。所有可以使用表的地方几乎都可以使用子查询来代替。

最简单的子查询：select * from(select * from T_Person where age>30)

• 单值作为子查询： select 1 as column1, 2 as column2,

(select min(Age) from T_Person) as column 3,

(select max(Age) from T_Book) as column 4

• 只有返回且仅返回一行，一列数据的子查询才能当成单值子查询。

下面是错误的：

Select 1 as f1, (select Age from T_Person)

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

Select * from T_Reader

Where YearOfJoin in

(select YearPublished from T_Book)

• 限制结果集。返回第三行和第五行的数据：

Select * from

(

Select row_number() over(order by Salary DESC)as rownum, Number,Name,Salary,Age from T_Employee

)as a

Where a.rownum in(3,5)