SQL入门

use r1001

--查询全部行和列
select * from student

--查询部分行
select * from student where sname='胡兵兵'

--查询部分列，通过查询列表过滤列
select SCode, SName
from student

--给列取别名
--as
select SCode as 学号, SName as 姓名 from student

--空格
select SCode 学号, SName 姓名 from student

--等号
select 学号 = SCode, 姓名 = SName from student

--查询NULL行
select * from student where saddress is null
--查询空行
select * from student where saddress = ''

--使用常量列
select *, '软件1001' SClass from student

--限制固定行数top
select top 3 SCode, SName from student

--返回百分之多少行
select top 30 percent SCode, SName from student

--升序
select * from student order by sname

--降序
select * from student order by sname desc

--多列排序
select * from student order by sAddress, sname desc

--*************************************************
select * from student where sName like '叶_'
select * from student where sName like '叶%'
select * from student where sName like '[叶,唐,周]%'
select scode, sname from student where sName like '[^叶,唐,周]%'

select scode, sname from student
where sName like '叶%'
or sName like '周%'
or sName like '唐%'

select * from student
where (scode >= 1 and scode <= 7) and saddress = '长沙'

select * from student
where scode in (1,7) and saddress = '长沙'

select * from student where sname like '_[0-3]%'

update student
set saddress='长沙', sbirth=getdate()
where scode between 1 and 3