SQL server2

/*with用来指定临时命名的结果集
with tb(age,agecount) as
(
	select age, count(*)
	from tb_Student as tb
	where age is not null
	group by age
)
select *
from tb
*/

/*
别名的三种定义方法:（别名= 列名） （列名as别名）（列名 别名） 
select 编号=ID, name as 姓名, age 年龄  
from tb_Student


like关键字:
%通配符： 包含零个或多个字符的任意字符串
_下划线：匹配任意单个字符
[]通配符：查询一定范围内的任意单个字符，包含两端数据
[^]和[]相反


between and 表示查询条件 大于等于第一个，小于等于第二个值
*/

/*
select子句必须包括在聚合函数或group by子句中
例如下面会显示name既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
select Sex,name from tb_Student group by sex
*/


/*
having子句：如果没使用group by子句，则having和where一样
where子句不能有聚合函数,所以这里用having
select age, count(age) 人数
from tb_Student
group by age
having count(age)>= 2
*/


/*compute没用by时返回一个结果集,用by时返回多个结果集
select *
from tb_Student
order by sex
compute avg(AGE)

select *
from tb_Student
order by sex
compute avg(AGE) by sex
*/

/*union合并操作，列数目要求一样多，对应列的数据类型必须相同或兼容，列的别名是由第一个select决定
默认会去掉相同的行，且会按第一列排序
select * from tb_Student
union
select * from tb_Student2

union all:不删除重复行也不对行进行自动排序

列的数据类型不同时，可用强制类型转换来进行合并
select name from tb_Student
union all
select str(age) from tb_Student2

当合并两个表源时列数不同，向一个表源添加列即可合并
select name , ID from tb_Student
union all
select name,NULL from tb_Student2
*/
v,,bx


/*
嵌套查询
select * from tb_Student
where name= (select name from tb_Student2 where name= '李明')

子查询返回很多值时用in
select * from tb_Student
where name in (select name from tb_Student2)
*/


/*
 联接查询是由一个笛卡尔积运算+一个选取运算构成的查询

内部连接（自然连接）：可能会丢失信息
select *
from tb_Student
join tb_Student2
on tb_Student.ID= tb_Student2.ID



左向外链接，左边表ID全部显示，左边有右边ID没的用NULL表示
select *
from tb_Student
left join tb_Student2
on tb_Student.ID= tb_Student2.ID

完整外链接
select *
from tb_Student
full join tb_Student2
on tb_Student.ID= tb_Student2.ID


交叉联接 即笛卡尔积
select *
from tb_Student
cross join tb_Student2


联接多表
select *
from table1, table2, table3...
where table1.column= table2.column
and table2.column= table3.column..
或者
select *
from table1
join table2
join table3...
on table1.column= table2.column
and table2.column= table3.column..
*/

/* 简单case 函数
select ID, 姓名=
case Sex
	when '男' then 'nan'
	else 'nv'
end
from tb_Student	

使用case函数更新表
update tb_Student 
set AGE=
case when sex='男' then AGE+1
	else AGE- 1
end
*/