sql 基础查询

--select查询结果为常量
select 1 
select 'select'

--alias 别名的使用
select 1 as aa  
select stuNo as id from stuInfo--列别名
select s.stuNO From StuInfo as s --表别名

--数据类型的转化 cast convert
select CAST(stuNo as varchar(10)) as NewstuNo from stuInfo  
select CONVERT(varchar(10),stuNo) as NewStuNo from stuInfo

--时间日期的格式化
select CONVERT(varchar(30),dateTest,101) as newDate from dateTT-- 月日年
select CONVERT(varchar(30),dateTest,103) as newDate from dateTT--日月年
select CONVERT(varchar(30),dateTest,102) as newDate from dateTT--年月日

--判断一列如果为NULL则赋值为‘’
select ISNULL(name,'12') as NewName from stuInfo where stuNO='2014'
select coalesce(name,'12') as NewName from stuInfo where stuNO='2014'

-- case when 条件  then  值1 else 值2 end
select sex, case when sex='man' then 'NewMan' else 'NewWoman' end from stuInfo

--order by 语句排序 升序：ASC 降序DESC
select * from stuInfo order by age 
select * from stuInfo order by age desc

--where 数据的刷选
--（1）简单的条件筛选
select * from stuInfo where age=20
select * from stuInfo where age<>20
--（2）复合条件  and or between in 
select * from stuInfo where age>=30 and age<=40
select * from stuInfo where age>=30 or age<=40
select * from stuInfo where age in(20,30,40)
select * from stuInfo where age between 20 and 40 

--like 的使用 %用于指定任意个数的字符 下划线_指定单个字符
select * from stuInfo where name like 'l%'
select * from stuInfo where name like '_s'
select * from stuInfo where name is null

--top操作符 可以结合排序使用
select top 2 * from stuInfo 

--从多个表中查询数据
--innner join 保留两个表中共有的数据
select * from stuInfo as s inner join gradeInfo as g on s.stuNo=g.id
--（1）left outer join  （2）right outer join 保留其中一方的所有数据
select * from stuInfo as s left outer join gradeInfo as g on s.stuNo=g.id
select * from stuInfo as s right join gradeInfo as g on s.stuNo=g.id
--（3）full outer join 保留两表中的数据不匹配的为空
select * from stuInfo as s full outer join gradeInfo as g on s.stuNo=g.id
--表的自联结
select s.name ,b.age  from stuInfo as s inner join stuInfo as b on s.stuNo=b.stuNo

--子查询
--在select中嵌套select
select * from stuInfo as s where exists(select 1 from gradeInfo as g where s.stuNo=g.id) 
--distinct 检索唯一结果
select distinct sex from stuInfo