【黑马程序员】SQL笔记四

  

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

--列名字段也可以用来运算
select 1+1 as 列1,getdate() as 日期,newid() as 编号
 
select FName as 姓名,FSalary+1000 as 月薪,getdate() as 当前日期 from T_Employee --给表里所有人的工资加一千
 
---聚合函数

select count(*) from T_Employee         --统计表里共有多少条记录

select max(FSalary) from T_Employee    --查询表里的最高工资

select min(FSalary) from T_Employee    --查询表里的最低工资

select avg(FSalary) from T_Employee    --查询表里的平均工资

select sum(FAge) from T_Employee        --表里所有人年龄的和


--------------------------------------------------------------------
-----排序--
select * from T_Employee order by FAge  asc   ----按照指定的字段（年龄）进行升序排序
select * from T_Employee order by FAge  desc   ----按照指定的字段（年龄）进行降序排序

select * from T_Employee order by FAge  desc,FSalary asc --首先按照年龄进行降序排列，如果年龄相同的，工资进行升序排列
select * from T_Employee where FAge>=25 order by FAge  asc  --注意where子句要放在order by 子句之前
select * from T_Employee where FAge>=25 order by FAge,FID  asc --按多列排序
-------------------------------------------------------------------
---通配符过滤，也叫模糊查询
--通配符有单字符匹配通配符“_”和多字符匹配的通配符“%”  
select * from T_Employee where FName like '_erry'   --任意单字符开头，后面是erry的名字
select * from T_Employee where FName like 'k%'   --k开头，任意长度的字符串的名字
select * from T_Employee where FName like '%k%'  --名字里面包含k这个字符的
--通配符还有[] 和[^]
--[]括号中所指定的字符，如 like 'pwa[1-2]'; [^]不在括号中所指定的字符，如 like '%wa[^1-2]'
-------------------------------------------------------------------
---null不代表没有或者空，在数据库中null代表不知道
select null+1   select null+'123' --这里都不返回任何数据，因为有null参与的运算结果都是nul,数据库也不知道返回什么

select * from T_Employee where FName=null --错误写法，不能用=
select * from T_Employee where FName is null --正确的写法用is
select * from T_Employee where FName is not null  --不为空用is not null

select * from T_Employee where FAge=23 or fAge=25 or fage=28
select * from T_Employee where FAge in (23,25,28) --更简单的方法用in 加条件
select * from T_Employee where FAge>20 and FAge<30
select * from T_Employee where FAge between 20 and 30 --两种执行结果一样，都是查询年龄在20至30岁之间的

----数据分组----group by
select Fage,fSalary from T_Employee group by Fage --错误，没有出现在group by 子句中的列是不能放到select语句后的列名列表中的（聚合函数除外）

select Fage,max(fSalary) from T_Employee group by Fage --正确

select FAge,count(*) from T_Employee
where count(*)>1
group by FAge           ---错误，聚合函数不能出现在where子句中，要查找分组中个数大于1的，这时可以用having


select FAge,count(*) from T_Employee
group by FAge                   ---正确
having count(*)>1     

select fage,count(*) from T_Employee group by Fage
having FSalary>2000   ---错误，having是对分组后的信息的过滤，能用的列和select中能用的列是一样的，having无法代替where


----------------------------------------------------------------------------------
---限制结果集行数
select Top 3 * from T_Employee order by FSalary desc  --取出工资表中的前三行数据按照降序排列，top 3 代表前三行

select Top 3 * from T_Employee 
where Fnumber not in (select top 5 FNumber from T_Employee order by Fsalary desc) --这条子查询找出了前5条记录，并用where条件排除掉了
order by FSalary desc   ---因此查询的结果是从第6条开始的前3条记录

select top 20 percent SName,Saddress from Students where Ssex=0 --percent返回百分之20行
---------------------------------------------------
---去掉数据重复    --注意distinct针对的是整个查询结果，而不是一个字段列
select distinct FDepartment from T_Employee --过滤掉工资表中部门重复的记录

select distinct FDepartment,FSubcompany from T_Employee --过滤掉工资表中部门,分公司都重复的记录

-------------------------------------------------------------------------
---联合结果集-----基本原则：每个结果集必须有相同的列数；每个结果集的列的数据类型必须相容
---注意：union会把两个表完全相同的数据合并，造成数据丢失，如果不想让他合并，用union all

select FName,FAge,FSubCompany from tempEmployee 
union
select FName,FAge,FSubCompany from  T_Employee   --列，数据类型完全一样

select FName,FAge,0 from tempEmployee 
union
select FName,FAge,FSubCompany from  T_Employee

select FName,FAge,'临时工，无部门' from tempEmployee 
union all
select FName,FAge,FDepartment from  T_Employee   --另一个表中没有这个列的要用相同数据类型的字符将它补齐

--union 因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，就用union all
---案例：--做报表的时候就可以这样用
select '正式员工最高年龄',max(FAge) from T_employee
union all
select '正式员工最低年龄',min(FAge) from T_employee
union all
select '临时员工最高年龄',max(FAge) from T_employee
union all
select '临时员工最低年龄',min(FAge) from T_employee

--案例2：
select FNumber,FSalary from T_Employee
union all
select '工资合计',sum(FSalary) from T_Employee

-----------------------------------------------------------------------------------

 

---数字函数
abs()                --求绝对值
ceiling() --天花板  --舍入到最大整数，3.33--4，2.89--3，-3.61--3
floor()   --地板    --舍入到最小整数，3.33--3，2.89--2，-3.61-- -4
round()   --半径    --四舍五入，舍入到离我半径最近的数，它有两个参数，如round(3.1415926,3)代表精确到小数点后3位

---字符串函数
len()   --截取字符串的长度
ltrim   --去掉字符串左边的空格
rtrim   --去掉字符串右边的空格 --例：
select ltrim('  bb  '),rtrim('  bb  '),ltrim(rtrim('  bb  ')) --最后一个是去掉两边的空格

substring()--截取指定字符串，指定位置，指定个数的字符串，它有3个参数，例：
select substring('abcdefgh',2,3) --从第2个开始，截取3个
select FName, substring(FName,2,2) from T_Employee --注意，函数后面都可以写列名

---日期函数
getdate()    --取得当前日期
dateadd(datepart,number,date)  --计算增加以后的日期。参数datepart为计量单位，number为增量，date为待计算的日期
--附：datepare计量单位表
--hh           小时
--day 或 dd     天
--month 或 mm   月
--qq   季度
--案例：
select dateadd(day,3,getdate())  --在当前日期下加3天

datediff(datepart,startdate,enddate)    --计算两个日期之间的差额
--案例：
select datediff(hh,getdate(),dateadd(day,3,getdate())) --计算两个日期相差的小时数（72）

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())   --计算按入职年数分组的员工的个数

datepart(datepart,date)      --返回一个日期的特定部分
---案例：
select datepart(year,getdate()),datepart(month,getdate()) --取出当前时间的年份，月份

select datepart(year,getdate()),count(*) from T_Employee
group by datepart(year,getdate())      --按照入职年份分组，取出每一个年份的入职人数

-----类型转换函数
cast(expression as data_type)     --Cast() ,as前面是要转换的表达式，as后面是要转换的数据类型
convert(expression as data_type)  --两种一样，有两个参数，convert前面是要转换的数据类型，as后面是要转换的表达式
--案例：
select cast('123',as int),cast('2011-03-2' as datetime),
convert(datetime,'2011-03-02'),convert(varchar(5),123)

select FidNumber,
right(FidNumber,3) as --取后3位
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
-----空值处理函数
isnull(expression,valu  --如果expression不为空则返回expression,否则返回value)
--案例：
select isnull(FName,'佚名') from T_Employee  --为空则返回‘佚名'

------case end 函数
--案例：
select FName,
(
case fleve1
when 1 then '普通客户'
when 2 then '会员'
when 3 then 'VIP'
else '未知客户类型'
end
)as 客户类型
from T_Customer
--案例2：
select FName,
(
case
when FSalary<2000 then '低收入'
when FSalary>=2000 and Fsalary<=5000 then '中等收入'
else '高收入'
end
) as 收入水平
from T_Employee

 

---索引 index
--对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。
--创建索引的方式，在表设计器中点击右键，选择索引键--添加--在列中选择索引包含的列
--使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低insert,update,delete的速度，只在经常检索的字段上（where）创建索引
--即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。



--表链接 join

select o.billNo,c.Name,c.Age from orders as o join
Customers as c on o.Id=c.Id

--要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名，客户年龄。
select o.billNo,c.Name,c.Age from orders as o join
Customers as c on o.Id=c.Id
where c.Age>15

--要求显示所有年龄大于平均年龄的顾客购买的订单号、客户姓名，客户年龄。
select o.billNo,c.Name,c.Age from orders as o join
Customers as c on o.Id=c.Id
where c.Age>(select avg(age) from T_Customers)

----子查询
--将一个查询语句做为一个结果集供其它SQL语句使用，
--单值做为子查询：
select 1 as f1,2,(select min(Fyearpublished) from T_book),(select max(Fyearpublished) from T_book)as f4

--只有返回一行、一列数据的子查询才能当成单值子查询。
select 1 as f1,2,(select Fyearpublished from T_book) --错误，子查询返回的不是一行，一列

--单列多行的子查询
select * from T_Reader
where FyearofJoin in
(
 select Fyearpublished from T_book
)

--SQL server 2005之后新增 row_number()函数,对查询结果排序
select * from
(
 select row_number() over(order by FSalary desc) as rownum,
FNumber,FName,Fsalary,FAge from T_Employee
) as e1
where e1.rownum>=3 and e1.rownum<=5    --取第3行到第5行

 

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