基础SQL语句大全

第一早：

—创立数据库

createdatabasemydb

on

(

Eame=mydb_Data,

filename=1D:\DBFile\mydb_Data.mdf,,

siz㊀=2MB,

maxsize=10MB,

filegrowth=lMB

)

logon

(

rame=mydb_Ilog/.

filename=1D:\DBFile\mydb_Log,IdfT,

size=lMB,

maxsize=10MB,

filegrowth=10%

)

—查看数据库信息

execsp_helpdbmydb

usemydb

一修改信息

alterdatabasemydb

modifyfile

Eame=mydb_Data,

size=3MB

)

一修改数据库名称

alterdatabasefirstdb

modifyname=seconddb

execsp_renamedb1seconddbI1firstdb

一删除数据库

dropdatabasefirstdb

createtablettt

(idint)

第二章：

一翻开自己的数据库

usemydb

―创立简单表

createtabletbl

(

sidintz

snamevarchar(20),

sexchar(2)

)

select*fromtbl

—自动获取列值的表

--default约束

createtabletb2

(

sidintz

snamevarchar(20),

sexchar(2)default1571

)

select*fromtb2

insertintotb2values(1,'张三I‘男')

insertintotb2(sid,sname)values(2,'李四')

insertintotb2values(3,13EE,,null)

一一自动编号

createtabletb3

(

sidintidentity(1000,10),

snamevarchar(20)

)

select*fromtb3

insertintotb3valu㊀s('张三')

iiiaertintotb3values('李四')

enoint,

cnamevarchar(30),

constraintpk_courseprimarykey(eno)

)

select*fromcourse

insertintocoursevalu㊀s(1JC#根底’)

insertintocoursevalues(1,'SQLServer1)

insertintocoursevalues(2,'SQLServer1)

insertintocourse(cname)values(1WindowsForm1)

―成绩表(学号，课程号，成绩)

createtablescore

(

snochar(4)referencesstudents(sno),

enoint,

gradeintz

Constraintfk_courseforeignkey(eno)references

course(eno),

Constraintpk_scoreprimarykey(sno,eno)

)

select*fromstudents

select*fromcourse

select*fromscore

insertintoscorevalues('ssss',3/76)--error

insertintoscorevalues('sOOl',3,76)--error

insertintoscorevalues('sOOl',1,76)

1

insertintoscorevalues(*s002,lr76)

insertintoscorevalues(,s002,,null,75)

一唯一性约束

createtabletb4

tidintuniquez

tnamevarchar(10)

)

select*fromtb4

1

insertintotb4values(1z*sa)

11

insertintotb4values(1zsa)--error

insertintotb4values(null,1pa1)

insertintotb4values(1\*da1)

--check检查约束

createtabletest

sidintidentity(1,1)primarykey,

snamevarchar(20),

markintcheck(mark>=0andmark<=100),

zipcodechar(6),

constraintck_zipcheck(zipcode

like1[0-9][0-9][0-9][0-9][0-9][0-9]1)

)

select*fromtest

1

insertintotestvalues(,张三,,-33ral24351)--error

insertintotestvalu㊀s('张三I33Jal2435')error

insertintotestvalues(1,,33,17124351)

insertintotest(sname)values('李四')

--test添力口列ag㊀

altertabletest

addageintcheck(age>=18andage<=30)

insertintotest(sname,age)values('李四31)

--scor㊀表添加检查约束

a]tertablescore

addConstraintck_scorecheck(grade>=0and

grade<=100)

一删除t㊀st表中age列

altertabletest

dropcolumnage

―先删除约束

altertabletest

dropCK__testage29572725

execspcolumnstest

--修改test表中sname的长度

altertabletest

altercolumnsnamevarchar(30)

—修改列名

execsp_rename*test.sname*,f姓名,,1column

--删除t㊀st

droptabletest

select*fromtb4

insertintotb4(tname,tid)values('ss3)

insertintotb4values(2,*dd1)

insertintotb4(tid)values(4)

insertintotb4

select5,*aa

select63uu

updatetb4settname=1ww

updatetb4settid=10,tname=1sa1wheretid=0

deletefromtb4wheretid=10

deletefromtb4

第三章：

--指定列名

selectsname,sno,classfromstu_info

--显示所有的列

select*fromstuinfo

一显示年龄列(使用表达式)

selectsname,snoAsex,2023-birthfromstu_info

一一列别名

selectsnameas姓名，年龄=2023—birth,class班级from

stuinfo

―统计人数

selectcount(class)总人数fromstu_info

selectcount(*)总人数fromstu_info

select*fromgrade

selectcount(id)fromgrade

—统计有成绩的学生人数

selectcount(distinctsno)fromgrade

―统计成绩列的总和

selectsum(mark)总成绩fromgrade

一统计最高分

selectmax(mark)fromgrade

selectmax(sname)fromstu_info

usenorthwind

select*fromorders

selectmax(orderdate)fromorders

usestudent

select*fromgrade

selectcount(distincteno)fromgrade

一最低成绩

selectmin(mark)fromgrade

—求平均分

selectavg(mark)fromgrade

selectsum(mark)/count(*)fromgrade

selectcount(sno),snofromgrade--error

selectcount(*)成绩个数，总分=sum(mark)，最高分

=max(mark),

min(mark)最低分，平均成绩=a？9(mark)fromgrade

select*fromgradewheremark>=60

select*fromstu_infowheresname=’胡锦涛.

selectsno,sname,2023-birth年龄fromstu_infowh㊀re

2023-birth>23

select*fromgradewheremarkbetween60and80

select*fromgradewheremarknotbetween60and80

usenorthwind

select*fromorderswhereorderdate

between11996-7-11and11996-7-311

usestudent

select*fromgradewheremarkin(60,70,80,90,100)

select*fromstu_infowheredeptin「计算机系经管

系D

selectsno,sname,2023-birthfromstuinfowhere

2023-birthin(24,26)

select*fromstuinfowheresnamelik㊀'胡％

select*fromstuinfowheresnamelik㊀'胡

select*fromstuinfowheresnamelike'胡

select*fromstu_infowheresnamelike1%i1^%

select*fromstu_infowhereclasslik㊀'0[5,6]%.

select*fromcoursewherecnamelike1DB$%1ESCAPE1$

select*fromstuinfoWHEREclassisnotnull

一60-80分的成绩

select*fromgradewheremark>=60andmark<=80

―计算机系，经管系的学生

select*fromstu_infowheredept=1计算机系，ordept=1

经管系，

select*fromstu_infowheredept='经管系,orsex='女

select*fromstu_infowheredept=1经管系'andsex

女Prd㊀pt=，计算机系，

select*fromstu_infowheredept=T经管系1and(sex=

女，ordept=，计算机系')

select*fromstu_infowheredept廿计算机系'ordept=

经管系，ands㊀x=f女，

select*fromstu_infowh㊀r㊀(d㊀pt=，计算机系Prdept=

经管系')diid女'

selecttop5.fromstuinfo

select*fromgradewheremark>=60orderbymarkdesc

select*fromgradewheresnoisnotnull

orderbysnoasc,markdesc

selectsex,count(*)fromstu_infogroupbysex

selectsno,count(*)成绩数，max(mark)最高分，min(mark)

最低分，

sum(mark)总分，avg(mark)平均分fromgradegroupbysno

第三章

altertableemployee

addconstraintfk_employeeforeignkey(deptid)

referencesdept(deptid)

altertableemployee

addconstraintpk_employeeprimarykey(employeeid)

alterdatabasemybasel

modifyfile(name=mybase_log^

size=2MB,

maxsize=10MB,

filegrowth=10%

)

usestudent

selectsno,count(*)fromgradewheresno

isnotnullgroupbysno

havingcount(*)>=3

select*fromstu_infocomputecount(sno)

select*fromgradeorderbysnocomputecount(mark)by

sno

select*fromstuinfowhere2023-birth>(

select2023-birth年龄fromstu_infowheresname

张三1)

selectsno,snamefromstu_infowheresnoin(

selectdistinctsnofromgrade)

select*fromstu_infowheresnoin(

selectsnofromgradewherecno=(

selectenofromcoursewherecname=*ASP.NET1))

selectsno,snamefromstu_infowheresnoin(

se]ectsnofromgradegronpbysno

havingavg(mark)>(

selectavg(mark)fromgrade))

一交叉连接

selectstu_info.*,grade.romstu_info,grade

selectstu_info.*,grade.rorr.stu_infocrossjoin

cJrade

―内连接等值连接

selectstu_info.*,grade.*fromstu_info,grade

wherestuinfo.sno=grade.sno

selectstu_info.*,grade.*frorr.stu_info

innerjoingradeonstu_info.sno=grade.sno

—自然连接

selectstu_info.sno,sname,eno,mark,classfrom

stu_infozgrade

wherestu_info.sno=grade.sno

selectstu_info.snozsname,cnczmarkfromstu_info

innerjoingradeonstu_info.sno=grade.sno

—不等连接

selectstu_info.*,grade.*fromstu_info,grade

wherestu_info.snoograde.sno

selectstu_info.*,grade.rorr.stu_info

innerjoingradeonstu_info.snoograde.sno

selectstu_info.sno,sname,cnamermark,classfrom

stu_info,gradezcourse

wherestu_info.sno=grade.snoandgradeo=courseo

selectstu_info.sno,sname,cname,mark,classfrom

stu_info

innerjoingradeonstu_info.sno=grade.sno

innerjoincourseongradeo=courseo

一左外连接

selectstuinfo.*,grade.*fromstuinfo,grade

wherestu_info.sno*=grade.sno

selectstu_info.*,grade.*frorr.stu_info

leftouterjoingradeonstu_info.sno=grade.sno

--右外连接

selectstu_info.grade.*fromstuinfo,grade

wherestu_info.sno=*grade.sno

selectstu_info.★,grade.*frorr.stu_info

rightouterjoingradeonstu_info.sno=grade.sno

―全外连接

selectstu_info.*,grade.*frorr.stu_info

fullouterjoingradeonstu_info.sno=grade.sno

select*fromcourse

--自连接

selecta.*,b.*fromcoursea,coursebwhere

a.precursor=bo

selectaame课程名称ame先修课名称fromcourse

&rcoursebwherea.precursor=bo

selecta.*,b.*,c.romcoursea,courseb,coursec

wherea.precursor=boandb.precursor=co

selectaame课程名称ame先修课名称ame间接先修

课fromcoursea,courseb,coursec

wherea.precursor=boandb.precursor=co

一联合查询

selectenofromcourse

union

selectbirthfromstu_info

第四章

usestudent

select*fromstu_info

insertintostu_info(sno,snarr.e)values(11007,,'新

加D

select*fromstu_info

select*fromstuinfowherebirth<=1986

createnonclusteredindexixbirth

onstu_info(birthdesc,sno)

select*fromstuinfo(index=ixbirth)

seiect.*fromstuinfowherebirth<=l986

select*fromstu_infowheredeptin(,计算机系，J经管

系’)

createdefaultdf_zipasT7100001

createtabletbl

tidintz

zipcodechar(6)

)

insertintotbl(tid)values(1)

select*fromtbl

execsp_bindefault1df_zip',Ttbl.zipcode'

insertintotbl(tid)values(2)

execsp_unbindefault'tbl.zipcode'

insertintotbl(tid)values(3)

createruleru_zipas

@sslike1[0-9][0-9][0-9][0-9][0-9][0-9]1

execsp_bindrule1ru_zip','tbl.zipcode'

insertintotblvalues(4,

execsp_unbindrule1tbl.zipcode'

insertintotblvalues(5,171abed1)

createtabletb2

(

tidint,

zipuserzip

)

insertintotb2values(2,171ddddf)

insertintotb2(tid)values(2)

select*fromtb2

第五章

usetest

Ex㊀csp_databases

execsptables

execsp_columns°stuinfo°

execsp_help*stuinfo'

execsp_heIptext1v_stuinfo1

execsp_heIptext1v_stumark1

createprocedureup_nopass_students

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:1+convert(varchar(10),@avg)

select

s・stuno,stuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.sttmo=m.stuno

wherewrittenexam<60orlabexam<60

GO

一执行存储过程

executeup_nopass_students

―添加两个输入参数

alterprocedureup_nopass_students

gwpassint,

glpassint

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

!

为:+convert(varchar(10)r@avg)

select

s・stunoAstuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@lpass

GO

―执行存储过程

executeup_nopass_students

(slpass=65,@wpass=80

execup_nopass_students80,65

execup_nopass_students60,60

―输入参数添加默认值

alterprocedureup_nopass_students

Gwpassint=60z

Glpassint=60

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:,^convert(varchar(10),@avg)

select

s・stuno,stuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@lpass

GO

―执行存储过程

executeup_nopass_students

execup_nopass_students80,65

execup_nopass_students90

execup_nopass_students@lpass=70

―带返回值的存储过程

rei_ip_nopass_students

(swpassint=60,

glpassint=60

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:T+convert(varchar(10),@avg)

select

s・stuno,stuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@lpass

return®@rowcount

GO

declare@numint

exec@num=up_nopass_students

print1未通过考试的学生人数为：

1+Convert(varchar(4),@num)

一添加输出参数

alterprocedureup_nopass_students

(scountintoutput,

(swpassint=60r

(slpassint=60

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:*+convert(varchar(10),@avg)

select@count=count(*)fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@Ipass

select

s・stunozstuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@lpass

return@@rowcount

GO

一添加输出参数

alterprocedureup_nopass_students

(snamevarchar(20)output,

Gwpassint=60A

Glpassint=60

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:T4-convert(varchar(10),@avg)

select@name=stunamefromstuinfowherestunoin(

selecttop1stunofromstumarks

orderbywrittenexamdesc)

select

s・stuno,stuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewritt㊀n㊀xam〈@wpassorlab㊀xam〈@lpass

return@@rowcount

GO

一带输出参数的执行

declare@namevarchar(20)

execup_nopass_students@nameoutput,70,80

print1本次考试笔试第一名是：f+@name

--带有异常的执行

declare@namevarchar(20)

execup_nopass_students@nameoutput,170,-80

print1本次考试笔试第一名是：1+@name

―自定义错误提示

alterprocedureup_nopass_students

(snamevarchar(20)output,

(swpassint=60,

Glpassint=60

as

declare@avgfloat

select@avg=avg(labexam)fromstumarks

print1本次考试机试平均分

为:"-i-coiivert(varchar(10),@avg)

select@name=stunamefromstuinfowherestunoin(

selecttop1stunofromstumarks

orderbywrittenexamdesc)

if((@wpassbetween0and100)and(@lpassbetween

0and100))

begin

select

s・stuno,stuname,examno,writtenexam,labexam

fromstuinfos

innerjoinstumarksmons.stuno=m.stuno

wherewrittenexam<@wpassorlabexam<@lpass

end

else

begin

raiserror「及格线应该在-TOO之间，请重新输入！！！！！

116,1)

end

return®@rowcount

GO

一带有异常的执行

declare@namevarchar(20)

ex㊀cup_nopass_students@nameoutput,170,-80

p匚inL本次考试笔试第,名是：1+@name

cr㊀at㊀procup_searchlab

(stbNamevarchar(30)

as

declare@strvarchar(100)

set@str=1select*from1+@tbName

print@str

exec(@str)

GO

execup_searchTab!stuinfo1

execup_searchTab1stumarks*

execup_searchTab*ssssssssssssssssss

alterprocup_searchTab

GtbNamevarchar(30)

as

declare@strvarchar(100)

if㊀xists(s㊀1㊀ct*fromsysobjects

wherename=@tbName)

begin

set@str=*select*from1+@tbName

exec(@str)

end

else

set@str=，表不存在！!

print@str

GO

execup_searchTab1stumarks*

execup_searchTab*ssssssssssssssssss*

selecttop5*fromstuinfowherestunonotin

(selecttop0stunofromstuinfo)

selecttop5*fromstuinfowherestunonotin

(selecttop5stunofromstuinfo)

selecttop5romstuinfowherestunonotin

(selecttop10stunofromstuinfo)

selecttop5*fromstuinfowherestunonotin

(selecttop15stunofromstuinfo)

createprocGetByPage

(spageindexint=lr

(spagesizeint=5

as

declare@strvarchar(1000)

set@str=1selecttop

1+Convert(varchar(4),@pagesize)+**from

stuinfowherestunonotin

(selecttop

1+Convert(varchar(4),@Pagesize*(@pageindex-l))

stunofromstuinfo)1

ex㊀c(@str)

go

execGetByPage

execGetByPage2

execGetByPage3

execGetByPage4

createprocup_StatTab

(stbNamevarchar(30)

as

declare@strvarchar(100)

ifexists(select*fromsysobjects

wherename=@tbName)

begin

set@str=1selectCount(*)from1+@tbName

exec(@str)

end

else

set@str='表'''+@tbNam㊀+'〃不存在！!!T

print@str

GO

execup_StatTab1stuinfo1

alterprocup_StatTab

(snumintoutputr

GtbNamevarchar(30)

as

declare@strvarchar(100),@strlvarchar(100)

ifexists(select*fromsysobjects

wherename=@tbName)

begin

set@str=1selectCount(*)from1tbName

set@strl=1select@count=CouEt(*)from1+@tbName

exec(@str)

print@strl

exec(@strl)

end

else

set@str=，表'JtbNam㊀+，〃不存在！!!1

print@str

GO

declare@nint

execup_StatTab@noutput,^tuinfo*

alterprocup_StatTab

©numintoutputf

(stbNamevarchar(30)

as

declare@strvarchar(100),@strlnvarchar(100)

if㊀xists(s㊀1㊀ct*fromsysobjects

wherename=@tbName)

begin

set@str=1selectCount(*)from1+@tbName

set@strl=*select@count=CouEt(*)from,+@tbName

exec(@str)

print@strl

execsp_executesql@strl,N!@countint

output1,@numoutput

end

else

set@str='表J+@tbNam㊀+，〃不存在！!!*

print@str

GO

declare@nint

ex㊀cup_StatTab@noutput,fstuinfof

print@n

第六章

select*fromstuinfo

select*fromstumarks

aropviewv_students

createviewv_students

(学号，姓名，性别，年龄)

withencryption

as

selectstuno,stuname,stusex<stuagefromstuinfo

wherestuage>22

withcheckoption

select*fromv_students

select*fromview_marks

execsp_helptext1view_marks1

execsp_helptext1v_students1

insertintov_studentsvalues(1s500001,,仇轩I'男

123)

--error

insertintoview_marksvalues(1s500001,1仇轩

1y2900001，男187,78)

insertinto

view_marks(examno,writtenexam,labexam)values(*s2

90000^87,78)

select*fromstuinfo

select*fromstumarks

updatev_studentsset年龄=25where学号='s50000’

deletefromv_studentswhere学号='s50000’

deletefromview_markswherestuno=1s25400,

第七章

usemydb

CREATETABLEbank

(customerNameCHAR(10),一—顾客姓名

currentMoneyMONEY--当前余额

)

GO

ALTERTABLEbank

ADDCONSTRAINTCK_currentMoney

CHECK(currentMoney>=l)

GO

INSERTINTObank(customerName,currentMoney)

VALUES「张三I1000)

INSERTINTObank(customerName,currentMoney)

VALUES「李四\1)

select*frombank

updatebanksetcurren