SQL常用查询语句

查询

select [列名]，[列名]...    from [表名]     //查询列

select [列名]-数值 from [表名]          //数值算法

select‘xxx'，[列名] from [表名]          //在列前一列插入一列为xxx

select * from [表名] where [列名=xxx] or[列名=yyy] //在表中查询列中为xxx和yyy的一行

select [列名] as xx from [表名]           //把列名改为xx

select distinet [列名] from [表名] //在一列中去重

select * from [表名] order by [列名]    //降序排列（升序desc）

select top n（ percent）* from [表名]      //查询表的前n（%n）行

select * from [表名] where [列名] <（>=）n（between n and n）  //表中小于（大于）n的一列

select * from [表名] where [列名] in ('XXX','YYY')     //列中有xxx和yyy的行

select * from [表名] where [列名] like 'n%' //列里面带有n的行

select count（[列名]） from [表名]   //表中列有几列

并union 交interset 差except

语法格式：   select语句 [union[all]][interset][except]select语句

union运算符后的关键字all表示合并的结果包括所有行，不使用all则在合并的结果去除重复行

 

union并

查询"电子商务"“软件工程”“市场营销”三个专业的心声信息

select * from student where SMajor='电子商务'

union

select * from student where SMajor='软件工程'

union

select * from student where SMajor='市场营销'

 

except差

select * into stucomputer2 from student where SMajor='计算机管理'

except

select * from student where SNativePlace='北京自连接

 

 

自连接：

select A.[别名1] 表的字段列表，B.[别名2] 表的字段列表

from 表 as A join 表 as B

on 连接条件  （A.[别名1]=B.[别名2]）

where A.[别名1]='xxx' and B.[别名2]<>'xxx'

 

eg:通过比较学号和专业来比较赵非

select A.SId,A.SName,A.SMajor,B.SId,B.SName,B.SMajor

from student as A join student as B

on A.SName=B.Sname

where A.SId='201410102' and B.Sid<> '201410102'

 

外连接：

from 表1 left、right、full（outer） join 表2

on 连接条件

 

 

 

无关子查询

eg：

查询与孙鑫同一专业的学生信息

select * from student

where SMajor=(select SMajor from student where SName='孙鑫')

 

eg:

查询所有上海学籍的同学

select * from student

where SId in (select sid from student where SNativePlace='上海')

 

eg：

查询入学成绩高于电子商务专业所有学生的学生信息

select * from student

where SEnterScore > all (select SEnterScore from student where SMajor='电子商务')

 

嵌套查询

eg：把上官玲同学的补助提高100

update student set SSubsidy =SSubsidy+100

where SName=(select SMajor from student where SName='上官玲')

 

删除查询：

eg：删除计算机管理专业孙鑫同学的成绩信息

delete from student

where SId=(select SId from student where SName='孙鑫' and SMajor='计算机管理')

 

左查询：

eg：在左边显示

select course.SId,CId,CName,student.SId,student.SEnterScore

from student right outer join course

on student.SId=course.SId

 

视图

新建视图

Create view [视图名]([列名]) As 子查询

Eg:显示计算机信息管理专业的学生信息的

create view sviewcom as

select * from student where SMajor='计算机信息管理'

select * from sviewcom

 

 

 

对视图进行插入修改删除

Alter view [视图名] as 子查询

Eg:删掉刚才的视图表

alter view sviewcom

as

select * from student where SMajor='计算机信息管理'

eg：创建索引

alter view sviewcom with schemabinding

as

select SId from dbo.student where SMajor='计算机信息管理'

create unique clustered index ind_sid on sviewcom(SId)

 

 

 

create procedure stuscore

as

select * from SC where SCore>90

 

exec stuscore

 

查询各专业学生人数

create proc smajornum

as

select smajor,count(*)as 人数

from student

group by smajor

exec smajornum

 

创建存储过程stuScoreInfo，该存储过程根据传入的课程编号和课程名称查询一下信息：学生姓名，性别，入学成绩，出生地

create proc stuScoreInfo

as

select student.SName,SSex,SEnterScore,SBirthday

from student

exec stuScoreInfo

 

编写带参数的存储过程，根据传入的课程名称统计该课程的平均成绩

create proc AVGSMajor @stumajor varchar(20)

as

select student.SMajor,avg(SEnterScore)as 平均分

from student

where SMajor=@stumajor

group by student.SMajor

exec AVGSMajor 计算机信息管理

 

编写带参数的存储过程，根据传入的专业名称查询该专业学生的课程成绩

 

 

备份和恢复

--backup database tmp

--to disk='(E:\data\tmp.bak)'

use master

restore database tmp from disk='(E:\data\tmp1.bak)'with replace