《数据库SQL语句汇总》由会员分享，可在线阅读，更多相关《数据库SQL语句汇总（12页珍藏版）》请在金锄头文库上搜索。
1、创建数据库CREATE DATABASE testON PRIMARY(NAME=test,FILENAME=E:练习数据 test.mdf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOG ON(NAME=test_log,FILENAME=F: 习日志 testog.ldf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB)修改数据库添加ALTER DATABASE testADD FILE(NAME=test1,FILENAME-E: 习数据test1.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5M
2、B),(NAME=test2,FILENAME=E:练习数据、test2.ndf,SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=1MB)GOALTER DATABASE testADD LOG FILE(NAME=testlog1FILENAME=;F:习日志、testlog1.1df,SIZE=5MB,MAXSIZE=100MB,FILEGR0WTH=5MB)GO删除数据库DROP DATABASE test创建表USE LibraryGOCREATE TABLE ReaderType(TypeID int NOT NULL PRIMARYKEY；Typename cha
3、r(8) Null,LimitNum int Null,LimitDays int Null)USE LibrarygoCREATE TABLE Reader(RID char(10) NOT NULL PRIMARYKEY,Rname char(8) NULL,TypeID int NULL,Lendnum int NULL,FOREIGN KEY(TypeID) REFERENCES ReaderType(TypeID)ON DELETE ON ACTION)修改表修改属性USE LibraryGOALTER TABLE BookALTER COLUMN 列名 varchar(30) NO
4、T NULLGO添加或删除列1、USE LibraryGOALTER TABLE BookADD ISBN varchar(17) NULLGO2、USE LibraryGOALTER TABLE BookDROP COLUMN ISBNGO添加或删除约束1、USE LibraryGOALTER TABLE BorrowADD CONSTRAINT PK_BH PRIMARYKEY(RID,BID,LendDate)GO2、USE LibraryGOALTER TABLE BorrowDROP CONSTRAINT PK_BHGO3、USE LibraryGOALTER TABLE BookA
5、DD CONSTEAINT CK_Book_PR CHECK(Price)O)GO删除表USE LibraryGODROP TABLE Table添加数据行1、USE LibraryGOINSERT INTO ReaderType VALUES(1,教师，20,90)GO2、USE LibraryGOINSERT ReaderType(RID,Rname,TypeID,Email)VALUES(200186010,张子健,1,)GO3、INAERT 新表SELECT SID,Sname,Sex,Birthday,SpecialtyFROM studentWHERE Specialty-计算机信
6、息管理修改数据1、USE LibraryGOUPDATE ReaderTypeSET LimitNum=LimitNum+2WHERE Typename=学生2、USE LibraryGOUPDATE ReaderTypeSET LimitDays=LimitDays+53、USE LibraryGOUPDATE ReaderSET Lendnum=(SELECT COUNT(*)FROM BorroeWHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID) 删除数据行1、USE EDUCGODELET studentIMWHERE SID=200
7、5206118GO2、USE EDUCGODELET studentIM创建索引USE EDUCGOCREATE NONCLUSTERED INDEX Studentindex2ON Student(Sname ASC)删除索引USE EDUCGODROP INDEX Student.Studentindex查询USE EDUCGOSELECT SID,Sname,SexFROM StudentWHERE Sex=男查询前5行USE EDUCGOSELECT TOP 5 BID,Bname,AuthorFROM Book查询前20%的数据行USE EDUCGOSELECT TOP 20 PER
8、CENT SID,Sname,Sex,BirthdayFROM Student消除重复数据行USE EDUCGOSELECT DISTINCT SpecialtyFROM Student查询所有USE EDUCGOSELECT * FROM ReaderType计算USE EDUCGOSELECT BID,Bname,Author,PubComp,Price,Price*0.9FROM Book查询所有结果USE LibraryGOSELECT TypeID,Typrname,LimitNum,册,LimitDays,天FROM ReaderType自定义列标题USE LibraryGOSEL
9、ECT BID AS 读者编号,Bname AS 书名，Author AS 作者，出版社=PubComp,原价=Price, 折价=STR(Price*0.9,6,2)+元FROM Book求总数1、USE LibraryGOSELECT COUNT(*) AS 册数FROM BookWHERE PubComp=高等教育出版社2、USE LibraryGOSELECT COUNT (DISTINCT (PubComp) AS 出版社个数FROM Book总数、最大、最小、平均、折后USE LibraryGOSELECT COUNT(Price) AS 册数，MAX(Price) AS 最高价，
10、MIN(Price) AS 最低价，SUM(Price) AS 总价值，STR(SUM(Price*0.9),8,2) AS 折后总价值，STR(AVG(Price),6,2) AS 平均价FROM Book连接查询，指定基本表1、USE EDUCGOSELECT Student.SID,Sname,Cname,GrradeFROM Student,SC,CourseWHERE Student.SID=SC.SID AND SC.CID=Course.CID2、USE EDUCGOSELECT * FROM StudentWHERE Sname=赵成刚3、USE EDUCGOSELECT *
11、FROM StudentWHERE Sex=男4、USE EDUCGOSELECT * FROM StudentWHERE DATEPART(year,GETDATE()-DATEPART(year,Birthday)+l245、USE EDUCGOSELECT * FROM StudentWHERE DATEPART(year,GETDATE()-DATEPART(year,Birthday)+124AND Sex=女6、USE EDUCGOSELECT * FROM StudentWHERE NOT(DATEPART(year,GETDATE()-DATEPART(year,Birthda
12、y)+124)AND NOT (Sex= 女)7、USE LibraryGOSELECT BID AS 图书编号，Bname AS 书名，Price AS 定价FROM BookWHERE Price BETWEEN 10 AND 158、USE EDUCGOSELECT * FROM StudentWHERE Sname LIKE 李 9、USE LibraryGOSELECT * FROM BookWHERE Bname LIKE %ERP%10、USE EDUCGOSELECT * FROM StudentWHERE SID IN(2005216007,2006216008)11、USE
13、 LibraryGOSELECT Borrow.RID,Rname,BID,ReturnDateFROM Borrow,ReaderWHERE Borrow.RID=Reader.RID AND ReturnDate is nullGROUP BY字句分组统计查询1、USE LibraryGOSELECT 出版社=PubComp,总价=SUM(Price)FROM BookGROUP BYPubComp2、USE EDUCGOSELECT 专业-Specialty，人数=COUNT (*)FROM StudentGROUP BYSpecialty3、USE EDUCGOSELECT SC.SI
14、D,Student.Sname,总成绩=SUM(GRADE)FROM SC,StudengWHERE SC.SID=Student.SIDGROUP BYSC.SID,Student.SnameORDER BY字句排序查询1、USE EDUCGOSELECT SC.SID,Student.Sname,总成绩=SUM(GRADE)FROM SC,StudentWHERE SC.SID=Student.SIDGROUP BYSC.SID,Student.SnameORDER BYSUM(GRADE) DESC2、USE EDUCGOSELECT Student.SID AS 学号，Student.Sname AS 姓名，COUNT(*) AS 选课门数FROM Student,SCWHERE Student.SID=SC.SIDGROUP BY Student.SID,Student.Snam
