sql2008
第一章 SQL语言基础
1 变量,declare @a varchar(30)
declare @a int,@b int,@c int
set @a=20
set @b=30
begin
set @c=@a;
set @a=@b;
set @b=@c;
end
全局变量是SQL系统内部事先定义好的变量,用户不参与定义,
@@identity,返回最后插入的标识值,
SQL一供提供了30多个全局变量;
@@ERROR:返回执行上一条SQL语句所返回的错误代码;执行成功返回0;
@@FETCH_STATUS:返回上一次使用游标FETCH操作所返回的状态值,且返回为整型;
@@ROWCOUNT:返回上一条SQL语句所景响到数据库行的数目;
常量:123,'ABC','2008-09-25','02/03/08'
2 数据类型:
int, -2(31)
samllint,
tinyint
bigint,
float,
real
decimal,
char(2)
nchar(2)
varchar(20) 8k
txet 存放文本字符,20M
NTEXT
IMAGE
create table testtable
{
userid int identity(1001,1),
username varchar(30),
userinfo text,
userimage image
}
表数据类型:
declarte @vartable table(userid int,username varchar(30)
insert into @vartable(001,csj)
select * from @vartable
自定义数据类型:
sp_addtype newchar 'varchar(30)','not null'
3 复合语句:begin --- end,go
user master
go
create trigger delid
on usertable
for delete
as
if(select count(*) from deleted,usertable where id>0)
begin
rollback transaction
print 'no delete'
end
4 execute exec
5 控制语句:
条件:
if @x<0
case
when a=1 then b
when a=0 then c
end
case
when a=1 then b
when a=0 then c
else d
end
循环语句:
while @x<=100
begin
---
---
break
---
continue
end
gotO转到语句:
declare @a int
select @a=100
Happy:
print @a
select @a=@a+1
while @a<102
goto Happy
6 临时表
只存在存储过程被创建时或者会话期间,是在TEMPDB中的真实表,常驻机构用来保数据集;
定义局部临时表:CREATE TABLE #TEST(X INT,Y INT)
全局临时表:create table ##TEST(X INT,Y INT)
7 常用命令:
print
DBCC whowcontig
kill 结束进程
第二章 常规数据查询
1 查询的基本结构
select * from test where group by id having name order by name desc (asc)
group by :将查询结果分组;
having 查询条件,
order by 排序;
2 列是查询
3 列的计算
select top 3 * from table order by 基本工资 desc;
select * from table where 每周课时 between 10 and 15
between
like
in
=
<>
>
<
<=
>=
and
or
between --- and ---
not between --- and ---
大写转换:upper(username)
小写转换:LOWER(USERNAME);
返回指定长度字符:LEFT(USERNAME,3)
right(name,3)
替换:replace(username,'a','aa')
截取字符串:substring(username, 3)从第3个字符开始截取字符串
initcap()
substr()lenght()
lpad()
数学函数:
abs(s);
len(s)
round(x,1);
sqrt(int) select rand(3);
floor
系统常量:
today
dbservername
别名 as
select userid,(语文 + 数学) as 总分 from table
select count(*) as 记录总数 from table
列的计算,可以加减乘除(/);求余运算:%
4 WHERE 条件查询
5 区间查询
第三章 高级数据过滤
1 组合WHERE语句;
or
and
round(12.12323,-1)
2 IN运算符;
3 NOT运算符;
IS NULL
4 行查询;
MYSQL: select * from table where (id,name)=(100,'csj')
select top 2 * from table order by id desc
5 类型转换;
CONVERT();
convert(varchar(30),prices)
convert(datatime,riqi)
类型转换: cast()
select distinct id,name from table order by id
where not exists(aa)
access:select layerid, iif(layerid ='172','ok','no') as states from layer
6 进制转换;
7 匹配查询;
like,
%, 匹配0个或多个字符;
-, 匹配任意一个单个字符;
[20,29], 匹配之间的结果;
*,
转义字符:select * from table where id like '%8/%%' escape '/' 将/后的解释为字符;
8 空值判断;
IS NULL
COALESCE 将空值转换为实际值;
nullif(X,Y),将两个涵数转换,当X!=Y,返回X,当X=Y返回NULL
第四章 字符串查询
1 字符串查询;
rtirm();
去空格: select rtrim(nusername) as 去空格后 from table
2 截取字符串;
select substring(username,1,1) as name from table
+链接字符串:
select username+userpwd from table
删除某字符串并添加入字符串:
select stuff('abcdefg',2,2,'aaaa')
3 格式字字符串;
lower();upper();
4 替换字符串;
replace(a,'*','')
从字符中分出字符和数字
提取电话号码中的区号和号码:
格式化日期:select dd,cast(dd as smalldatetime),120) as tt from table
5 比较字符串;
字符串相同检查:difference('green',greene') as a
字符串大小写敏感查询:
select NAME='CSJ' from table collate Chinese_PRC_CS_AI
第五章 日期查询
1 格式化日期,转换成不同形式的格式:
select convert(char(10),getdate(),120) as datas
2 时间DUAN查询
between and == (>= <=)
select * from table where date in('2005-01-01','2005-02-30')
select * from table where month(dates)=1
3 获取日期
getdate()
两个日期间的差天数:datediff(day,startdate,enddate)
select datediff(mm,startdate,enddate) * 24 * 60
datediff(mm,startdate,enddate)
日期加运算:dataadd()
第六章 数据排序
1 数据排序;
默认为升序排序:order by
别名排序:
按字符中的某个子串排序:
selct * from table order by substring(username,1,1)按USERNAME中的第一个字符排序;
2 字符串排序;
3 汉字排序;
汉字按姓氏笔画排序:select * from table order by name collate chinese_prc_stroke_cs_as_ks_ws
SQL支持的所有排序规则:
select * from ::fn_helpcollations()
4 按多个列排序;
5 按列序号排序;
第七章 聚合函数与分组统计
1 聚合函数;
2 分组统计;
查询重复的记录:select * from table group by id having count(id)>=2 oder by id
分组统计:分组汇总:cube;rollup
select * from table group by id ,name with cube
rollup 对第一组分组汇总;
compute
第八章 子查询
1 简单子查询;
select * from(select * from tabl1 order by id) tb1
order by id
2 单行子查询;
3 多行子查询;
select * from tabl1 where age> (select avg(a) from tab2)
4 相关子查询;
exists
not esists
select id from table where exists(select * from tabl2 where table.id=tabl2.id)
第9章 多表查询
1 多表联接;
笛卡儿乘积查询:cross join x*y
多表联接查询中设置条件:select id,username from tb1,tb2,tb3 where tb1.id=tb2.id and tb1.id=tb3.id
表别名 select * from tb as tb2
2 内联接;
等值联接=:inner join
select * from tb1 inner join bt2 on tb1.id=tb2.id2
等值联接!=,>,<,>+,<=:inner join
自己然联接:select * from tb1 inner join bt2 on tb1.id=tb2.id where name='csj'
多表联接:select * from (tb1 inner join tb2 on tb1.id=tb2.no) inner join tb3 on tb1.id=bt3.no2
3 外联接;
左外联接:left outer join
右外联接:right outer join
完全联接:full join
4 其他联接;
自联接:select * from tb1 a inner join tb1 b on a.name=b.name
5 组合查询;
union :组合表结构一样;
union all:保留重复行;
交集运算:intersect
差集运算:minus
6 使用联接和联接条件;
except
(select * from a)
except
(select * from b)
except
(select * from c)
intersect
第10章 高级查询
交叉表查询;
1.1 交叉表查询;
1.2 交叉表; pivot
1.3 反转交叉表 unpivot
1.4 行列转换 case when then end
1.5 创建分隔列表;
1.6 查询结果分页显示;between and
1.7 在结果集中隔一行显示数据; select * from dbo.department where dpid%2=0
2.1分布式查询
建立链接:SP_ADDLKINEDSERVER
查看链接:exec sp_linkedservers
删除链接:exec DROPservers 'MYDB'
查看登录映射信息:
使用OPENROWSET进行分布式查询
创键关联电子表格的链接;
创键关联ACCESS数据库的链接;
第11章 插入数据
插入单行数据; insert into tab();
插入多行数据;
将查询结果插入数据表;
insert all
when name='001' then
insert tab1(a,a,a,)values()
when name='002' then
insert tab2()
第12章 更新和删除数据
更新表中的数据
删除表中的数据 truncate table tb
通过视图更新表
第13章 创建操纵数据库和表
第14章 使用视图
创建视图;create view v_test
as select * from tab2
修改视图;
删除视图;
第15章 使用存储过程(带参或不带参)
创建存储过程;create proced pc_test
@id varchar(30) --参数
as select * from ta2
管理存储过程;
if(@@error <>0)
return a
print a
exec pc_test
使用函数;
create function f_test(@a int)
returns int
as
begin
declare @b int
set @b=datepart(wk,@a)
if(@b=1)
select * from b
if(@b=2)
select * from c
return (@b)
end
内接函数返回一个表;
create function f_test(@a varchar(30))
returns table
as
return (select * from dbo.department t1,tuse t2 where t1.dpname=t2.dpname)
