sql server 基本操作二

基本查询

create database ZK_Test
go

use zk_test
go

create table Student
(
    ID int identity(1,1) primary key,
    Name varchar(20) not null,
    Sex char(2) not null,
    Birthday datetime, 
    Height int
)

select * from student

insert into student (Name, Sex, Birthday, Height)
values
(
    '张三','男','1988-08-12', 172
),
(
    '张三1','女','1986-08-12', 168
),
(
    '我就','男','1988-08-12', 122
),
(
    'kk','男','1988-12-19', 132
),
(
    '吗看','男','1958-9-12', 152
),
(
    '咯跑','男','1976-8-12', 102
),
(
    '用额','男','1988-8-12', 192
),
(
    '离开','男','1988-8-1', 152
)

select distinct * from student

select top 3 * from student

select top 30 percent * from student

declare @count int = 5
select top (@count) * from student 

select Name as 姓名 from student

select Name + 'zk' as 姓名 from student

select Name + 'zk' + convert(varchar(20), birthday, 112) as Name from student

select datediff(year, birthday, getdate()) age from student

--group by操作用by的字段生成了临时表，在其后进行的操作
--只能用临时表有的字段，否则需要使用max、min等对原表进行操作组合成新表
select 
name,max(sex) as sex,max(height) as height
from student 
where height >= 151
group by name
having max(sex) = '男'
order by name desc

select * from student where height = 172

select * from student where sex = '男'

select * from student where height >= 150 and height <= 175

select * from student where height between 150 and 178

select * from student where height in(176, 172, 180)

select * from student where height not in(176, 172, 180)

select * from student where height like '1_2'

select * from student where height like '17%'

select * from student where height like '1[67][012345]'

select * from student where height is null

update student set height = null where id = 8

select * from student where height is not null

--将null值转换为0
select * from student where isnull(height, 0) < 180 

select * from student where exists
(
    select 1
)
--判断某个数据是否有
if exists
(
    select * from student where id=12
)
print 'exist'
else
print 'not exists'

select * from student where exists
(
    select * from student where id=9
)

select * from student as t1 where exists
(
    select * from student where id = t1.id and height > 173
)


select * from student where charindex('张', name, 1) >= 1

select * from student where patindex('张_', name) >= 1

select * from student order by name desc

alter table student add class int default(null)

--循环赋值
declare @no int
set @no =1

while @no < 30
    begin
        update student set class =@no where id=@no
        set @no = @no + 1
    end


select * from student order by sex,class

select class,year(birthday),count(id),avg(height),max(birthday) from 
student group by class,birthday

select class,year(birthday),count(id),avg(height),max(birthday) from 
student group by class,birthday having class=1

select class,year(birthday),count(id),avg(height),max(birthday) from 
student group by class,birthday having avg(height) > 165

select class,sum(isnull(height,0)) from student group by class

select class,avg(height) from student group by class

select class,count(height) from student group by class

select class,max(height) from student group by class

select class,min(height) from student group by class

select class,count(distinct(height)) from student group by class

 高级查询

--临时表
select * from 
(
    select student.*,class.classname from student join class on class.id = student.id
) as T

--物理临时表
create table #tmp_stu
(
    id int identity(1,1) primary key,
    name nvarchar(32)
)
go

insert into #tmp_stu
(name)
values('hehe')

select * from #tmp_stu

drop table #tmp_stu

--into临时表
select class,name,height into #tmp_stu2 from student
select * from #tmp_stu2

--into物理表
select class,name,height into tmp_stu2 from student
select * from tmp_stu2

--复制表结构
select * into student_his from student where 1=2

--表变量
declare @tmp_stu3 table(id int identity(1,1),name varchar(20))
insert @tmp_stu3
(name)
values
(
'dfdf'
)
select * from @tmp_stu3

--CTE公用表达式
with T
as
(
    select student.*,class.classname from student join class on class.id = student.id
)

select * from T

--嵌套子查询
select * from student where id in
(
    select id from class
)

select * from student where id =
(
    select top 1 id from class
)

select * from student where id = any
(
    select id from class
)

select * from student where id = some
(
    select id from class
)

select * from student where id = all
(
    select id from class
)

--相关子查询
select * from student where exists
(
    select id from class where id = student.id
)

--in 子查询数据量小，而外表数据量大
--exists 子查询数据量大，而外表数据量小
--能使用关联，尽量不使用子查询

--横向连接
--内连接
select * from student inner join class on class.id = student.id
and student.id in(6,7,8,9)

select * from student inner join class on class.id <> student.id

select * from student cross join class where class.id <> student.id

--自连接 内连接的特殊情况

--左连接 
select * from student left join class on student.id = class.id

--全连接
select * from student full join class on student.id = class.id

--右连接 
select * from student right join class on student.id = class.id

--交叉连接
select * from student cross join class


--纵向连接
--union
--union all
--except
--intersect

--over function
select * from student

select *,(select max(birthday) as max_birthday from student)
 from student

select *,max(birthday) over() as max_birthday from student

select *,count(id) over() as max_birthday from student

--row_number 排序生成序号
select *,row_number() over(order by id) rn from student

with tmp
as
(
select *,row_number() over(order by id) rn from student
)
select * from tmp where rn between 1 and 5

--rank
select *,rank() over(order by height) rn from student


--dense_rank
select *,dense_rank() over(order by height) rn from student

--ntile
select *,ntile(9) over(order by sex) rn from student

--partition
select *,count(id) over(partition by sex) sex_count from student

select *,rank() over(partition by class order by height desc) sex_count from student

--动态查询
declare @c_ids varchar(200)
declare @c_names varchar(200)
set @c_ids = '1,2'
set @c_names = '张三'
declare @c_name varchar(200)
set @c_name = 'name,sex'

exec ('select ' + @c_name + ' from student where class in('
+ @c_ids + ')')

declare @sql nvarchar(2000)
declare @rowcount int
--传入参数

--set @sql = 'select * from student where class in(@c_ids)'
--exec sys.sp_executesql  @sql,N'@c_ids varchar(20)', @c_ids

--set @sql = 'select * from student where name =@c_names'
--exec sys.sp_executesql  @sql,N'@c_names varchar(20)', @c_names

--传出参数
set @sql = 'select @rowcount = count(*) from student where name =@c_names'
exec sys.sp_executesql  @sql,N'@c_names varchar(20),@rowcount int output',
 @c_names,@rowcount output
 print @rowcount