/*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
*/
SQL server2
最新推荐文章于 2023-08-21 22:00:16 发布