SQLServer数据库基础-查询笔记（一）

 --数据库查询   重点


select * from  Tb_Stu_Info
SELECT * FROM dbo.Tb_Subject


select  Stu_Name '姓名',
		Stu_xb'性别',
		Stu_Birthday'生日'
from    dbo.Tb_Stu_Info

--查询前三列的信息
select top 3 * from dbo.Tb_Stu_Info

--查询分数表
select * from dbo.Tb_Stu_Score

--基于计算查询出新的列
select   stu_no, subject_id,score'期末成绩',
          score * 0.7+30 '总评成绩'
from     Tb_Stu_Score

--基于条件查询
--查询表中所有女性的信息
select * from Tb_Stu_Info	 
where Stu_xb='女'--可以用= > < 等


--查询成绩表中在60分以上80分以下的同学
select * from  Tb_Stu_Score
where (score >=60 and score<=70) or 
      (score >=80 and score<=90)       --and相当于java中的& or 相当java中的||

--where 条件可以是and or not
select * from  Tb_Stu_Score
where not score >=60 and score<=70
where score <60 or score>70

--between and


--查询以下分数60 65 70 75 80 85 
select * from  Tb_Stu_Score
where   score=60 or 
		score=65 or 
		score=70 or 
		score=75 or 
		score=80 or 
		score=85
--查询以下分数60 65 70 75 80 85  
select * from  Tb_Stu_Score 
where score in (60,65,70,75,80,85)  
--加not取反
select * from  Tb_Stu_Score 
where score not in (60,65,70,75,80,85)  


--模糊查询 like 相似
--%任意长度字符串 ，下划线（任意单个字符），
--查询湖南人的所有信息 


select  * from Tb_Stu_Info
where   Stu_Address   like '湖南%'

--1  %表示任意长度
select  Stu_Name 
from Tb_Stu_Info
where  Stu_Name   like '%陈%'


--2  倒数第二字是怡，下划线表示一个字符
select  Stu_Name 
from Tb_Stu_Info
where  Stu_Name   like '%怡_'

--3  倒数第二个字是‘怡’字或者是‘翠’的信息  []-待选列表
 select  Stu_Name 
from Tb_Stu_Info
where  Stu_Name  like '%[怡，翠]_' 
                 

--4  倒数第二个字不是‘怡’字或者是‘翠’的信息  [^]-非待选列表
 select  Stu_Name 
from Tb_Stu_Info
where  Stu_Name  like '%[^怡，翠]_' 

--5 姓陈或者姓李并且数第二个字不是‘怡’字或者是‘翠’的信息
 select  Stu_Name 
from Tb_Stu_Info
where  Stu_Name  like  '%[陈,李]%[^怡，翠]_' 


                                                                                   
--order by
select * from  Tb_Stu_Score
where subject_id=1 
order by score desc


--order by 
--依据某个列来排序
--order by 列名  asc(升序、)desc(降序)
--可以针对任何数据
				



select *from  Tb_Stu_Score
where  Subject_id=1
order by score --desc     



select *from   Tb_Stu_Info
--where   Stu_Birthday
order by Stu_Birthday --desc      



select *from   Tb_Stu_Info
--where   Stu_Birthday
order by  Stu_Name --desc             

--order by 
--依据某个列来排序
--order by 列名  asc(升序、)desc(降序)
--可以针对任何数据
				



select *from  Tb_Stu_Score
where  Subject_id=1
order by score --desc     



select *from   Tb_Stu_Info
--where   Stu_Birthday
order by Stu_Birthday --desc      



select *from   Tb_Stu_Info
--where   Stu_Birthday
order by  Stu_Name --desc          



select ascii(9) --查询九的ascii码  

select char(57) --查询57对印的数值

select charindex('E','HELLO')--返回第一个字符在第二个字符串首次出现的位置

select left('JAMES',3)--截取字符串，从左到右截取3个字符 

select right('JAMES',3)--截取字符串，从右到左截取3个字符

select len('JAMES')--显示字符串中的字符的个数

select lower ('JAMES')--将大写变小写

select upper ('James')--将小写变大写

select ltrim ('   J ames')--将字符串前的空格去掉

select ltrim ('James  ')--将字符串后的空格去掉