黑马程序员-SQL相关知识

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

 

一、

MSSQL Server数据库中经常用到的数据类型有：bit、int、bigint、smallint、float、image、text、ntext、money、smallmoney、datetime、smalldatetime、decimal、char、nchar、verchar、nverchar等。

SQL语句是操作数据库的专用语言。不同于C#等编程语言，在SQL中字符串用单引号括起来。另外，在SQL语句中，关键字是不区分大小写的。SQL主要分DDL(数据定义语言)和DML(数据操作语言)两类。Create Table、Drop Table等属于DDL，Select、Insert等属于DML。

示例SQL语句：

create table Person(id int not null,name char(20),age int)   //创建表，并添加列。
drop table Person         //删除表

insert into Person(id,name,age) values(1,'王五',20)         //向表中添加数据。

二、主键

MSSQL Server中有两种常用的主键数据类型：int(或bigint)+标识列(又称自动增长字段)：uniqueidentifier(又称Guid、UUID)。用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。将字段的“是标识列”设置为“是”，一个表只能有一个标识列。

Guid算法是一种可以产生唯一标识的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生成的GUID永远不会重复，无论是同一个计算机上还是不同的计算机。在公元3400年以前产生的GUID与任何其他产生过的GUID都不相同。MSSQL Server中生成GUID的函数newid()，.Net中生成Guid的方法：Guid.NewGuid()，返回是Guid类型。

如：select newid()

       Guid id = Guid.NewGuid();

int自增字段的优点：占用空间小、无需开发人员干预、易读；缺点；效率低；数据导入导出的时候很痛苦。

Guid的优点：效率高、数据导入导出方便；缺点占用空间大、不易读。业界主流倾向于使用Guid。

三、数据插入

insert语句可以省略表名后的列名，但是不推荐。如果插入的行中有些字段的值不确定，那么insert的时候不指定那些列即可。可以给字段设默认值，如果是Guid类型的主键，则把默认值设定为newid()就可自动生成，但是很少这么做。

如：insert into Person3(id,name,age)values(newid(),'王五',30)

四、数据更新

更新一列：update Person1 set age=30

更新多列：update Person1 set age=30, Name='tom'

更新一部分数据：update Person1 Set age=30 where Name='tom'，用where语句表示只更新Name是'tom'的行，注意SQL中等于判断用单个=，而不是==。

where中还可以使用复杂的逻辑判断 如：update Person1 Set age=30 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_Employee。只检索需要的列：select FNumber from T_Employee；select FName,FAge from T_Employee
列别名：select FNumber as 编号,FName as 姓名,FAge as 年龄 from T_Employee  。 使用where 检索符合条件的数据：select FName from T_Employee where FSalary<5000。 还可以检索不与任何表关联的数据：select 1+1；select newid()；select getdate()。

七、数据汇总

SQL聚合函数：max(最大值)、min(最小值)、avg(平均值)、sum(和)、count(数量) 。  

示例：select count(*) from T_Employee where fsalary>5000
            select max(FSalary) from T_Employee
            select min(FSalary) from T_Employee
            select avg(FSalary) from T_Employee
            select sum(fsalary) from T_employee

八、数据排序

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

按照年龄升序排序所有员工信息的列表：select * from T_Employee order by FAge ASC

按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序：select * from T_Employee order by FAge DESC,FSalary ASC

order by 子句必须放到where子句之后：select * from T_Employee where FAge>23 order by FAge DESC,FSalary ASC

九、通配符过滤

通配符过滤使用like 。    单个字符匹配的通配符用半角下划线字符“_”，它匹配单个出现的字符。如：以任意字符开头，剩余部分为"erry"：select * from T_Employee where FName like '_erry'。

多字符匹配的通配符为半角百分号"%"，它匹配任意次数(零或多个)出现的任意字符。"k%"匹配以"k"开头、任意长度的字符串。如：检索姓名中包含字母"n"的员工信息：select * from T_Employee where FName like '%n%'。

十、NULL值处理

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

select * from T_Employee  where FName=null；和select * from T_Employee where FName!=null都没有任何返回值，因为数据库也“不知道”。

SQL中使用 is null、is not null 来进行空值判断：select * from T_Employee where FName is null ；select * from T_Employee where FName is not null。

十一、多值匹配

select FAge,FNumber,FName from T_Employee where FAge in (23,25,28)；

范围值：select * from T_Employee where FAge >= 23 and FAge <=27；select *from T_Employee where FAge between 23 and 27。

十二、数据分组

按照年龄进行分组统计各个年龄段的人数：select FAge,count(*) from T_Employee group by FAge 。

group by 子句必须放到where 语句之后。没有出现在group by 子句中的列是不能放到select 语句后的列名列表中的（聚合函数中除外）。

如：错误：select FAge,FSalary from T_Employee group by FAge 。

       正确：select FAge,avg(FSalary) from T_Employee group by FAge 。

十三、having语句

在where 中不能使用聚合函数，必须使用having ，having要位于group by 之后，having 是分组之后用来限制输出条件的。如：select FAge,count(*) as 人数 from T_Employee group by FAge  having count(*)>1 。

having 子句中的列必须是包含在聚合函数或group by 子句中的列。如：select FAge,count(*) from T_Employee group by FAge havingFSalary>2000 是错误的。因为FSalary列既不包含在select 语句中，也不包含在group by 语句中。

十四、限制结果集行数

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 ；//查询T_Employee 表的FDepartment 字段

select distinct FDepartment from T_Employee ；//用distinct 关键字清除查询结果中的重复数据。

distinct 关键字是对整个结果集的重复进行处理，而不是针对每一个列。

十六、联合结果集

简单的结果集联合：select FNumber,FName,FAge from T_Employee union select FIdCardNumber ,FName,FAge from T_TempEmployee

使用联合查询的基本原则：每个结果集必须有相同的列数；并且对应列的数据类型必须相容。如：

select FNumber,FName,FAge,FDepartment from T_Employee
union
select FIdCardNumber,FName,FAge,'临时工，无部门' from T_TempEmployee

union all：union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用union all。如：

select FNumber,FSalary from T_Employee
union all
select '工资合计',sum(FSalary) from T_Employee

十七、（1）数字函数

abs()：求绝对值。  如：select abs(-98) ；ceiling()：舍入到最大整数，3.33将被舍入为4，2.89将被舍入为3，-3.61舍入为-3。如：select ceiling(23.43)；floor()：舍入到最小整数，3.33将被舍入为3，2.89将被舍入为2，-3.34将被舍入为-4。如：select floor(23.5)。round()：四舍五入。舍入到“离我半径最近的数”。如：select round(324.324,2)，它有两个参数。

（二）字符串函数

len()：计算字符串长度。如：select len(FName) from T_Employee；lower()：转小写。upper()：转大写。ltrim()：去掉字符串左侧的空格。rtrim()：去掉字符串右边的空格。如：select rtrim(a   )；ltrim(rtrim())：去掉两边的空格。substring(string,start_position,length)：截取字符串函数。参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。如：select substring('jsalkfjlskjf',2,3)；

（三）日期函数

getdate()：取得当前日期时间。dateadd(datepart,number,date)：计算增加以后的日期，参数datepart为计量单位（如：year,month,day），number为增加的数量（可以为负数，表示减少），date为表示待增加的日期。如：select dateadd(month,3,getdate())；datediff(datepart,startdate,enddate)：计算两个日期之间的差额。datepart为计量单位。如：select datediff(year,Findate,getdate()) from T_Employee；datepart(datepart,date)：返回日期的特定部分（如：年、月、日），date表示目标日期。如：select datepart(year,getdate())。

（四）类型转换函数

cast(expression as data_type)，convert(data_type,expression)这两个函数都可以进行类型转换，如：select cast('123' as int)、select convert(int,'123')；left(目标，n)和right(目标，n)函数分别表示截取目标左侧或右侧的n个字符，如：select right(FName,3) from T_Employee表示截取FName字段的右侧的3个字符。

（五）空值处理函数

isnull(expression,value) ：如果expression不为空则返回expression，否则返回value。如：select isnull(FName,'佚名') as '姓名' from T_Employee;

（六）case函数-用法一

单值判断，相当于switch case  如：select FName,
                                                               (
                                                               case FLevel
                                                               when 1 then '普通用户' 
                                                               when 2 then '会员'
                                                               when 3 then 'VIP'
                                                               else '未知用户'
                                                               end
                                                               ) as 用户级别 from T_Customer

case函数用法二：如：select FName,
                                          (
                                          case
                                          when FSalary<2500 then '低收入'
                                          when FSalary>=2500 and FSalary<5000 then '中等收入'
                                          else '高收入'
                                          end
                                          ) as 收入水平 from T_Employee;

练习：

--case函数的使用，相当于switch()...case
select FName,
(
case FLevel
when 1 then '普通用户'
when 2 then '会员'
when 3 then 'VIP'
else '未知用户'
end
)
as 用户级别 from T_Customer

--case函数的使用，相当于if...else
select FName,
(
case
when FSalary<2500 then '低收入'
when FSalary>=2500 and FSalary<5000 then '中等收入'
else '高收入'
end
) as 收入水平 from T_Employee;

--创建T_Money表
create table T_Money(Fnumber char(10),Famount int);
--在表中插入数据
insert into T_Money(Fnumber,Famount) values('r1',10);
insert into T_Money(Fnumber,Famount) values('r2',20);
insert into T_Money(Fnumber,Famount) values('r3',-10);
insert into T_Money(Fnumber,Famount) values('r4',15);
insert into T_Money(Fnumber,Famount) values('r5',-20);
--case 函数
select Fnumber,
(
case
when Famount<0 then 0
else Famount
end
) as 收入,
(
case
when Famount<0 then abs(Famount)
else 0
end
) as 支出 from T_Money

--创建表T_Scores
create table T_Scores(Date datetime,Name varchar(20),Score char(10));
--向表中添加数据
insert into T_Scores(Date,Name,Score) values('2008-8-8',N'拜仁',N'胜');
insert into T_Scores(Date,Name,Score) values('2008-8-9',N'奇才',N'胜');
insert into T_Scores(Date,Name,Score) values('2008-8-9',N'湖人',N'胜');
insert into T_Scores(Date,Name,Score) values('2008-8-10',N'拜仁',N'负');
insert into T_Scores(Date,Name,Score) values('2008-8-8',N'拜仁',N'负');
insert into T_Scores(Date,Name,Score) values('2008-8-12',N'奇才',N'胜');
--删除表T_Scores中的所有数据
delete from T_Scores

--按列Name进行分组查询各队的胜负数，用到了case函数和sum()
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_Scores
group by Name

--创建T_Work表
create table T_Work(Id int,OtherNumber varchar(20),StartTime datetime,EndTime datetime);

--查询表T_Work中的约束
exec sp_helpconstraint T_Work
--删除表T_Work中的PK_T_Work约束
alter table T_Work drop constraint PK_T_Work
--在表T_Work中添加列CallNumber
alter table T_Work add CallNumber int;
--插入数据
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(001,'0208888888','2010-7-10 10:00:00','2010-7-10 10:05:03');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(001,'0208888888','2010-7-11 13:00:00','2010-7-11 13:01:10');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(001,'89898989','2010-7-11 14:06:00','2010-7-11 14:09:00');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(002,'98987676','2010-7-13 21:06:00','2010-7-13 21:08:08');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(002,'02188839389','2010-6-29 20:11:00','2010-6-29 20:16:06');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(001,'76767676','2010-7-15 13:16:00','2010-7-15 13:26:00');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(003,'0227864656','2010-7-13 11:16:00','2010-7-13 11:17:09');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(003,'676765777','2010-7-19 19:26:02','2010-7-19 19:30:33');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(001,'89977653','2010-6-19 15:16:02','2010-6-19 15:26:10');
insert into T_Work(CallNumber,OtherNumber,StartTime,EndTime) values(004,'400400400','2010-6-19 21:06:00','2010-6-19 15:26:10');
--更新一条数据
update T_Work set StartTime='2010-6-19 15:16:02' where Id=10;

 

--练习：
--查询表中按通话时间排序的前五条数据
select top(5) * from T_Work
order by datediff(second,StartTime,EndTime) DESC;
--输出拨打号码以0开头的所有记录
select * from T_Work where OtherNumber like '0%';
--输出所有数据中拨打长途号码的总时长
select sum(datediff(second,StartTime,EndTime)) from T_Work
where OtherNumber like '0%';
--输出本月通话总时长最多的前三个呼叫员的编号  (分步进行)
select CallNumber,OtherNumber,datediff(month,StartTime,getdate()) from T_Work;

select * from T_Work where datediff(month,StartTime,getdate())=0;

select top(3) CallNumber from T_Work
where datediff(month,StartTime,getdate())=0
group by CallNumber
order by sum(datediff(second,StartTime,EndTime)) DESC;
--输出本月拨打电话次数最多的前三个呼叫员的编号
select CallNumber,count(*) from T_Work
where datediff(month,StartTime,getdate())=0
group by CallNumber
order by count(*) DESC;
--输出所有数据的拨号流水,并在最后一行输出总时长
--汇总 [市内号码总时长][长途号码总时长]
select convert(nvarchar,CallNumber),OtherNumber,datediff(second,StartTime,EndTime) from T_Work
union all
select N'汇总',N'市内号码总时长',sum(datediff(second,StartTime,EndTime)) from T_Work
where OtherNumber not like '0%'
union all
select N'汇总',N'长途号码总时长',sum(datediff(second,StartTime,EndTime)) from T_Work
where OtherNumber like '0%';

十八、索引

 创建索引的方式：在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。

使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也要同步更新索引，因此会降低insert、update、delete的速度。只在经常检索的字段上创建索引。

 即使创建了索引 ，仍然有可能会全表扫描，比如like、函数、类型转换等。

十九、表连接join

--利用 join on进行多表合并查询

--查询年龄大于15的订单号、客户姓名、客户年龄
select O.BillNo as 订单号,C.Name as 客户姓名,C.Age as 客户年龄
from T_Customers as C join T_Orders as O on C.Id=O.CustomerId
where C.Age>15;
--查询年龄大于平均年龄的顾客购买的订单
select O.BillNo as 订单号,C.Name as 客户姓名,C.Age as 客户年龄
from T_Customers as C join T_Orders as O on C.Id=O.CustomerId
where C.Age>(select avg(Age) from T_Customers);

join 还包括 inner join、left join、right join等。

二十、子查询

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。如：select * from (select * from T_Orders where FAge<30)

单值作为子查询：select 1 as f1，2，(select min(FYearPublished) from T_Book)，(select max(FYearPublished) from T_Book) as f4    只能返回一行、一列数据的子查询才能当成单值子查询。

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

 

 

 

 

------- Windows Phone 7手机开发、 .Net培训、期待与您交流！ ------　　详细请查看： http://net.itheima.com