SQL常用语句
- 表操作
create table t_person1(id int not null,name nvarchar(50),age int null)
drop table t_person1
alter table t_person1 add department vchar(20)
- 数据更新
update Student set Name='李鹏' where Id=5
update a set a.Bonus=500 from Student a inner join Gender b on a.Gender=b.Id where b.GenderName='男'
update vw_Gender set Bonus=200 where GenderName='女'
select * from Student
- 数据插入
insert into Student(Name,Age,Bonus,Gender)values('李强',18,300,1)
- 数据删除
delete from Student where Id=11 //与drop table不同的是delete只是删除数据 表还在
delete from Student where Id in(8,9,10)
- 数据检索
select * from pud
select top 3 * from pud
select pudname,pudprice from pud
select pudname as N'姓名' from pud
select pudprice=pudprice+1 from pud
select count/max/min/avg/sum(money) from t_person1 //聚合函数不能出现在where子句
select * from pud where id=259
select * from pud where pudprice>100
select * from pud where pudname='Kiddy安全座椅限时特价套装：限量版宝宝安全提篮－9个月到4岁儿童安全座椅'
select * from pud where pudname like '%母爱%'
select * from pud where pudname like '%母爱%' and pudprice>100
select * from pud where pudname like '%2%'
select * from pud where pudname like '2%'
select * from pud where pudname like '%2'
select * from pud where CHARINDEX('2',pudname)>0
select * from pud where id in(253,254,255,256)
select id,pudname,pudprice from pud where id in(253,254,255,256)
select 1+1 / select newid() / select getdate()
- 多表关联数据检索
- 添加外键关系: 表设计器中右键 → 关系 → 添加 → …
select a.Name,a.Age,b.GenderName from Student a, Gender b where a.Gender=b.Id
select a.Name,a.Age,b.GenderName from Student a inner join Gender b on a.Gender=b.Id
select a.Name,a.Age,b.GenderName from Student a left join Gender b on a.Gender=b.Id
select a.Name,a.Age,b.GenderName from Student a right join Gender b on a.Gender=b.Id
(创建视图)
select Name, Age, GenderName from vw_Gender
- 联合结果集
- union all: 禁止结果集重复数据抑制/一般保留all
- 子查询
select * from
(
select row_number() over(order by salary desc) as rownum,number,name,salary,age from t_employee
) as a
where a.rownum>=3 and a.rownum<=7 //限制结果集 返回第3行到第7行的数据
- 数据排序
- 升序(ASC/默认) / 降序(DESC)
select top 3 * from pud order by pudprice desc
select top 3 * from pud order by pudprice asc
select top 3 * from pud order by LEN(pudname) desc
select top 3 * from pud order by LEN(pudname) asc
select top 3 * from pud order by salary ASC,age DESC
select top 3 * from pud
where age>20
order by salary ASC,age DESC //where在order by之前
- 数据分组
select age,avg(salary) from t_person1 group by age //没有放在group by子句中的列不能出现在select语句后(聚合函数除外)
- having语句
- having: 对组进行过滤/放在group by之后/where语句中不能出现聚合函数而必须使用having
- 抑制重复数据
select GenderName from
(
select b.GenderName from Student a inner join Gender b on a.Gender=b.Id
)as c group by GenderName
select GenderName from vw_Gender group by GenderName
select distinct(GenderName) from vw_Gender
- 限制结果集行数
- 作用: 分页
select top 5 * from t_person1 order by salary desc //取工资前五
select top 3 * from t_person1
where number not in (select top 5 number from t_person1 order by salary desc;)
order by salary desc //取工资第六开始的前三
select row_number over(order by salary desc) as rownum, number,name,age,salary from t_employee //SQLServer2005之后增加了row_number函数简化实现
- 通配符过滤/模糊匹配
- Like(where)
select * from t_person1 where name like ‘_erry’ //单字符通配符(_)
select * from t_person1 where name like ‘k%’ //0或多字符通配符(%)
- 多值匹配
select * from t_person1 where age=23 or age=25 or age=27
select * from t_person1 where age in (23,25,27)
select * from t_person1 where age>=23 and age<=27
select * from t_person1 where age between 23 and 27
- 空值处理
- 数据库(狭义)中一个列如果没有指定值则为null(表示‘不知道’而不是没有/不同于C#中的null(空/不指向任何对象))
- 游标循环
pl/sql类似于C、C++ 这类语言一次只能处理一条数据库记录 如果想用这类语言处理返回的多条数据就要用到游标
declare @id int
declare Student_Cursor cursor for select Id from Student
open Student_Cursor
fetch next from Student_Cursor into @id
while @@fetch_status=0
begin
update Student --单独对返回的每条数据进行处理
set CreateDaTe=RandTime('2011-9-8','2011-9-11',rand())
where Id=@id
fetch next from Student_Cursor into @id
end
close Student_Cursor
deallocate Student_Cursor
- 其他
declare @str nvarchar(50)
set @str='明天|你要|嫁给|我了'
if charindex('2',@str)>0
begin
print('存在')
end
else
begin
print('不存在')
end
