create table stu_info

 (

   sno int not null

   ,sname varchar(20) not null

   ,sex  varchar(2) not null

   ,birth  varchar(20) not null

   ,email  varchar(20) not null

   ,telephone int not null

   ,depart  varchar(20) not null

 )

  select distinct depart from dbo.stu_info

  -- select order by depart from dbo.stu_info

  --select sname ,datediff(year,birth,getdate()) from dbo.stu_info

  --命名

  select sname as 姓名 from dbo.stu_info

  -- 把查询结果保存为一个新表

   select sname as 姓名 into sname2 from dbo.stu_info

   -- 查询 sname2  即使用了 into后

   select *  from sname2

   --链接表字段

    select sname+depart as 姓名来源 from dbo.stu_info

  --     where

   select * from dbo.stu_info where sno>3

     select * from dbo.stu_info where sno=3

     select sno,sname,sex,depart from dbo.stu_info where sname>'李四'

 --  查询日期数据 SQL 默认格式 月/日/年

   select sno,birth as 生日,date  from dbo.stu_info where date>'01/05/1980'

    select sno,birth as 生日,date  from dbo.stu_info where date<'01/05/1980' and date>'01/05/1790'

 --  按范围查询数据

  select * from dbo.stu_info where sno between 2 and 4

   select * from dbo.stu_info where email is not null

   --排序查询数据 order by 后接 按哪个字段排名

   select sno,sname,birth from dbo.stu_info order by sname

   -- 设置排名方向 asc 升 desc 降

   select sno,sname,birth from dbo.stu_info order by sname desc

   -- 按多列排序  在desc 后加上需要排序的字段 即可 （升序）

   --........

   -- 按字段位置排序 有时表达式过长，这样减少错误率 , 下面的2 代表select后面字段的第二个值

   select sname ,datediff(year,date,getdate()) as 年龄 from dbo.stu_info order by 2 desc

    -- 查询前 几（2）行数据 关键字 top

    select top 2 sno,sname,birth from stu_info order by birth

    -- 查询前 n）行数据 关键字 top percent   百分之n

    select top 2 percent sno,sname,birth from stu_info order by birth

     -- where 与 order by 结合使用 where 一定在前

     select sno,sname,telephone,depart from stu_info where telephone is not null order by sno desc

 select * from stu_info where depart in ('中文系','外语系','计算机系')

     order by depart asc

     -- like 与 % 通配符   模糊查询   %代表0个或多个字符

     select * from stu_info where sname like '%三%'

     --为了更好的体现like+% 的作用，插入几条语句

     insert into stu_info ( sno,sname,sex,birth,email,telephone,depart,date)

     values(6,'刘三姐','男','zz','zhiniao@gmail.com','','软件系','05/15/1983')

     select * from stu_info

       select * from stu_info where sname like '%三%'

         select * from stu_info where sname like '三%'

         --rtrim 将右边的空格除去

           select * from stu_info where rtrim(sname) like '%三'

     -- 指定个数的字符  ' _ '

     select * from stu_info where sname like '刘%'

     select * from stu_info where sname like '刘_'  --按通配符_个数匹配

 -- 通配符 []

     select * from stu_info where sname like '[张李]%'

      select * from stu_info where sname like '[^张李]%'

 select * from stu_info where sname like '[^张李]#%' escape '#'