SQL功能 | 谓词 |
---|---|
数据定义(DDL) | CREATE、DROP、ALTER |
数据查询(DQL) | SELECT |
数据操纵(DML) | INSERT、UPDATE、DELETE |
数据控制(DCL) | GRANT、REVOKE、DENY |
数据类型 | 描述 | 存储 |
---|---|---|
tinyint | 允许从0到255的所有数字 | 1字节 |
smallint | 允许从-32768到32767的所有数字 | 2字节 |
int | 允许从-2147483648到2147483647的所有数字 | 4字节 |
bigint | 允许介于-9223372036854775808和9223372036854775807之间的所有数字 | 8字节 |
decimal(p,s)或numeric(p,s) | 固定精度和比例的数字,允许从-10^38+1 到 10的38次方-1之间的数字,p参数知识可以存储的最大位数(小数点左侧和右侧,p必须是1~38之间的值,默认是18;s参数指示小数点右侧存储的最大位数,s必须是0到p之间的值,默认是0。) | 5-17字节 |
smallmoney | 介于 -214748.3648和 214748.3647之间的货币数据 | 4字节 |
money | 介于 -922337203685477.5808 和 922337203685477.5807之间的货币数据。 | 8字节 |
float(n) | 从 -1.79E + 308 到 1.79E + 308的浮动精度数字数据;参数n知识该字段保存4字节还是8字节,float(24)保存4字节,而float(53)保存8字节,n的默认值是53. | 4或8字节 |
real | 从3.40E + 38 到3.40E + 38的浮动精度数字数据。 | 4字节 |
数据类型 | 描述 | 存储 |
---|---|---|
datetime | 从1753.1.1到9999.12.31,精度为3.33ms | 8字节 |
datetime2 | 从1753.1.1到9999.12.31,精度为100ns | 6-8字节 |
samlldatetime | 从1900.1.1到2079.6.6,精度为1min | 4字节 |
date | 仅存储日期,从0001.1.1到9999.12.31 | 3字节 |
time | 仅存储时间,精度为100ns | 3~5字节 |
datetimeoffset | 与datetime2相同,外加时区便宜 | 8~10字节 |
timestamp | 存储唯一的数字,每当创建或修改某行时,该数字会更新,timestamp基于内部时钟,不对应真实时间,每个表只能有一个timestamp变量。 |
数据类型 | 描述 | 存储 |
---|---|---|
char(n) | 固定长度的字符串,最多8000个字符 | n字节 |
varchar(n) | 可变长度的字符串,最多8000个字符 | 由实际长度决定 |
varchar(max) | 可变长度的字符串,最多1073741824个字符 | 由实际长度决定 |
text | 可变长度的字符串,虽多2GB的字符数据 | 由实际长度决定 |
nchar(n) | 固定长度的Unicode数据,最多4000个字符 | n字节 |
nvarchar(n) | 可变长度的Unicode数据,最多4000个字符 | 由实际长度决定 |
nvarchar(max) | 可变长度的Unicode数据,最多536870912个字符 | 由实际长度决定 |
ntext | 可变长度的Unicode数据,最多2GB字符数据 | 由实际长度决定 |
数据类型 | 描述 | 存储 |
---|---|---|
bit | 允许0、1或NULL | |
binary(n) | 固定长度的二进制数据,最多8000B | n B |
varbinary(n) | 可变长度的二进制数据,最多8000B | 由实际长度决定 |
varbinary(max) | 可变长度的二进制数据,最多2GB | 由实际长度决定 |
image | 可变长度的二进制数据,最多2GB | 由实际长度决定 |
语法格式:
CREATE DATABASE NAME[[PRIMARY]<文件说明>[,.....n][,FILEGROUP 文件组名[,.....n]]][[LOG ON<文件说明>[,.....n]]]
案例:①创建图书馆数据库“Library”。②主要数据文件逻辑名称为“Library”③物理文件名为"D:\图书管理数据\Library.mdf"④数据库的事务日志文件逻辑名称为“Library_log”⑤物理文件名为“E:\图书管理日志\Library_log.ldf”⑥初始大小为1MB,最大容量为600MB,增长速度为10%。
CREATE DATABASE LibraryON PRIMARY(NAME = Library,FILENAME = 'D:\图书管理数据\Library.mdf',SIZE = 5,MAXSIZE = 20GB,FILEGROWTH = 10%)LOG ON(NAME = Library_log,FILENAME = 'E:\图书管理日志\Library_log.ldf',SIZE = 1,MAXSIZE = 600,FILEGROWTH = 10%)
语法格式:
ALTER DATABASE 数据库名 {MODIFY NAME = 新数据文件名|ADD FILE <文件说明>[,....n]|ADD LOG FILE <文件说明>[,....n]|REMOVE FILE 逻辑名称|MODIFY FILE <文件说明>|ADD FILEGROUOP 文件组名|REMOVE FILEGROUP 文件组名|MODIFY FILEGROUP 文件组名...}
<文件说明>
(NAME = 逻辑名称[,NEWNAME = 新逻辑名称][,FILENAME = '物理文件名'][,SIZE = 初始大小[KB|MB|GB|TB]][,MAXSZIE = {最大大小[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH = 自动增长量[KB|MB|GB|TB|%]]
)
案例:①修改图书馆里数据库“Library”。②添加文件组“Library_Group”。③添加次要数据文件“Library_data2”到“Library_Group”文件组④添加事务日志文件“Library_log2”。⑤修改次要数据文件“Library_data2”的最大大小为20GB,自动增长量为10%。⑥删除次要数据文件“Library_data2”和事务日志文件“Library_log2”。⑦删除次要文件组“Library_Group”。
//添加文件组“Library_Group”
ALTER DATABASE LibraryADD FILEGROUP Library_Group//添加次要数据文件“Library_data2”到“Library_Group”文件组
ALTER DATABASE LibraryADD FILE(NAME = Library_data2,FILENAME = 'D:\图书馆管理数据\Library_data2.ndf')TO FILEGROUP Library_GROUP//添加事务日志文件“Library_log2”
ALTER DATABASE LibraryADD LOG FILE(NAME = Library_log2,FILENAME='E:\图书馆管理数据\Library_log2.ldf',SIZE=5MB,MAXSIZE=300MB,FILEGROWTH=5MB)//修改次要数据文件“Library_data2”的最大大小为20GB,自动增长量为10%。
ALTER DATABASE LibraryMODIFY FILE(NAME = Library_data2,MAXSIZE = 20GB,FILEGROWTH = 10%)//删除次要数据文件“Library_data2”和事务日志文件“Library_log2”。
ALTER DATABASE LibraryREMOVE FILE Library_data2
GO
ALTER DATABASE LibraryREMOVE FILE Libiary_log2//删除次要文件组“Library_Group”。
ALTER DATABASE LibraryREMOVE FILEGROUP Library_Group
语法格式:
DROP DATABASE <数据库名>[,...n]
实例:先创建一个数据库“DB1”再删除
//创建
CREATE DATABASE DB1//删除
DROP DATABASE DB1
案例:图书管理表的创建与管理
四个关系模式:
ReaderType(TtpeID,Typename,LimitNum,LimitDays)
PK:TypeID
Reader(RID,Rname,TypeID,Lendnum)
PK:RID FK:TypeID
Book(BID,Bname,Authoe,Publisher,Price,LentOut)
PK:BID
Borrow(RID<BID<LendDate,ReturnDate,SReturnDate)
PK:RID+BID+LendDate FK:RID<BID
功能:再数据库中创建新表,为表定义各列的名称、数据类型和完整性约束等。
CREATE TABLE 表名({<列定义>|[<表级完整性约束>]}[,...n][,...n])
列定义语法说明:
列名 数据类型 --列名及其数据类型
[NULL|NOT NULL] --非空值约束,默认为NULL
[[CONSTRAINT 约束名]DEFAULT 常量表达式] --默认值定义,可以为其定义一个约束名称
|[IDENTITY(标识种子,标识增量)] --标识列定义
[<列级完整性约束>]//列级完整性约束
[CONSTRAINT 约束名] --可选项,表示以下约束定义的开始,并给约束一个名称
PRIMARY KEY --主键约束
|UNIQUE --唯一键约束
|[FOREIGN KEY]REFERENCES 主键表(主键) --外键约束
|CHECK(逻辑表达式) --检查约束
表级完整性约束语法定义:
[CONSTRAINT 约束名] --表示以下为表约束定义的开始,并可给约束一个名称
PRIMARY KEY(列名[,...n]) --主键约束(一个或多个列组合)
|UNIQUE(列名[,...n]) --唯一键约束(一个或多个列组合)
|FOREIGN KEY(列名[,...n])REFERENCES 主键表(列名[,...n]) --外键约束(一个或多个列组合)
|CHECK(逻辑表达式) --检查约束
创建表:
//1.创建读者类型表“ReaderType”
USE Library
GO
CREATE TABLE ReaderType(TypeID int NOT NULL PRIMARY KEY, --读者类型编号,主键Typename char(8) NULL, --读者类型名称LimitNum int NULL, --限借数量LimitDays int NULL --限借天数)//2.创建读者表“Reader”
CREATE TABLE Reader(RID char(10) NOT NULL PRIMARY KEY, --读者编号,主键Rname char(8) NULL, --读者姓名TypeID int NULL, --读者类型Lendnum int NULL, --已借数量FOREIGN KEY(TypeID)REFERENCES ReaderType(TypeID) ON DELETE NO ACTION --外键,不删除级联)//3.创建图书表“Book”
CREATE TABLE Book(BID char(13) PRIMARY KEY,Bname varchar(42) NULL,Author varchar(20) NULL,Publisher varchar(28) NULL,Price decimal(7,2) NULL CHECK(Price>0),LendOut bit --s是否借出)//4.创建图书借阅表“Borrow”
CREATE TABLE Borrow(RID char(10) NOT NULL FOREIGN KEY REFERENCES Reader(RID) --外键约束ON UPDATE CASCADE ON DELETE CASCADE,BID char(13) NOT NULL FOREIGN KEY REFERENCES Book(BID)ON UPDATE CASCADE,LendDate date NOT NULL DEFAULT(GETDATE()), --借书日期,默认值定义ReturnDate date NULL, --还书日期SRturnDate date NULL, --应还日期PRIMARY KEY(RID,BID,LendDate) --定义RID+BID+LenDate为主键约束)
语法结构:
ALTER TABLE 表名(ALTER COLUMNS 列名 --指定一个要修改的列数据类型 [NULL|NOT NULL] --指定修改的数据类型和非空值约束|ADD{<列定义>|<表级完整性约束>}[,...n] 指定添加一个或多个列定义或表级约束|DROP[CONSTRAINT]约束名[,...n] --指定删除一个或多个约束|COLUMN 列名[,...n] --指定删除一个或多个列)
1.修改数据类型(与空置约束)
案例:修改读者表“Book”中“Publisher”的类型varchar(28)改为“varchar(30)”。
USE Library
GO
ALTER TABLE Book
ALTER COLUMN Publisher varchar(30) NOT NULL --修改列定义
2.添加列或约束
案例:为表“Reader添加邮件地址列和检查约束”
ALTER TABLE Reader
ADD Email varchar(20) NULL CONSTRAINT Reader_Email CHECK(Email LIKE '%@%')
案例:为表“Book”的主键图书编号“BID”添加(BID LIKE ‘%/%’)的检查约束。
ALTER TABLE Book
ADD CONSTRAINT CK_Book_BID CHECK(BID LIKE '%/%')
3.删除列或约束
案例:删除“Reader”中的“Email”列的约束和列。
ALTER TABLE Reader
DROP CONSTRAINT Reader_Email,
COLUMN Email
语法格式:
DROP TABLE 表名
案例:先创建Table1,然后删除
USE Library
GO
DROP TABLE Table1
1.INSERT … VALUES
语法格式:
INSERT [INTO] 表名|视图名[(列表名)]
VALUES(常量表)
插入一列所有的值
案例:为表“ReaderType”插入数据行
USE library
GO
INSERT INTO ReaderType VALUES(1,'教师',6,90)
INSERT INTO ReaderType VALUES(2,'职员',4,60)
INSERT INTO ReaderType VALUES(3,'学生',3,30)
插入一行部分列的值
案例:为表“Reader”插入读者“张子健”的部分信息,用同样的语句添加其他数据行。
USE library
GO
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2000186010,'张子健',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2000186011,'赵良宇',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2003216008,'张英',2)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2004060003,'李亚茜',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2004216010,'任灿灿',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2014216117,'孟霞',3)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2015216008,'杨淑华',3)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2015216009,'程鹏',3)
插入表“Book”的数据行
USE library
GO
INSERT INTO Book VALUES('F270.7/34','ERP从内部集成开始','陈启申','电子工业出版社',45.00,'False')
INSERT INTO Book VALUES('F270.7/455','SAP基础教程','黄佳','人民邮电出版社',55.00,'True')
INSERT INTO Book VALUES('F270.7/56','ERP系统的集成应用','金蝶软件','清华大学出版社',35.00,'False')
INSERT INTO Book VALUES('F275.3/65','SAP财务管理大全','王纹','清华大学出版社',29.00,'True')
INSERT INTO Book VALUES('TP311.138/125','数据库应用技术','周慧','人民邮电出版社',46.00,'True')
INSERT INTO Book VALUES('TP311.138/136','SQL Server 2008基础教程','Robin Dewson','人民邮电出版社',55.00,'True')
INSERT INTO Book VALUES('TP311.138/230','SQL Server 2005基础教程','Robin Dewson','人民邮电出版社',89.00,'False')
INSERT INTO Book VALUES('TP311.138/235','SQL Server 2008从入门到精通','Mike Hotek','清华大学出版社',46.00,'True')
INSERT INTO Book VALUES('TP311.138/78','数据库系统概论','萨师煊','高等教育出版社',25.00,'False')
INSERT INTO Book VALUES('TP312/429','C#入门经典','Karli Watson','清华大学出版社',98.00,'False')
插入表“Borrow“的数据行
USE library
GO
USE library
GO
INSERT INTO Borrow VALUES(2000186010,'F270.7/455','2017-05-30','2017-07-14',NULL)
INSERT INTO Borrow VALUES(2000186011,'TP311.138/125','2017-11-26',NULL,NULL)
INSERT INTO Borrow VALUES(2004216010,'TP311.138/235','2017-11-15',NULL,NULL)
INSERT INTO Borrow VALUES(2014216117,'TP312/429','2017-04-09','2017-05-02',NULL)
INSERT INTO Borrow VALUES(2015216008,'F275.3/65','2017-10-28',NULL,NULL)
INSERT INTO Borrow VALUES(2015216009,'F270.7/455','2017-11-17',NULL,NULL)
INSERT INTO Borrow VALUES(2015216009,'TP311.138/136','2017-10-30',NULL,NULL)
2. INSERT … SELECT
语法结构:
INSERT 表名 SELECT 子查询
案例:将图书部Book中人民邮电出版社的图书插入到新建的BookPostTel表中
//1.创建BookPostTel表
USE library
GO
CREATE TABLE BookPostTel
(
BID char(13) PRIMARY KEY NOT NULL,
Bname varchar(42) NULL,
Author varchar(20) NULL,
Publisher varchar(30) NULL,
Price decimal(7,2) NULL CHECK (Price>0)
)//2.将图书部Book中人民邮电出版社的图书插入到新建的BookPostTel表中
USE library
GO
INSERT BookPostTel
SELECT BID,Bname,Author,Publisher,Price FROM Book
WHERE Publisher='人民邮电出版社'
UPDATE … SET
语法结构:
UPDATE 表名
SET <列名=表达式>[,...n]
[WHERE 逻辑表达式]
功能:
案例:将读者类型表”Reader Type“中学生的原限借数量3册增加2册,原限借天数30天增加5天。
USE Library
GO
UPDATE ReaderType
SET LimitNum=LimitNum+2,LimitDays=LimitDays+5
WHERE Typename='学生'
案例:将读者类型表”Reader Type“中所有数据行的限借天数都增加10天。
UPDATE ReaderType
SET LimitDays = LimitDays + 10
案例:删除表”BookPostTel“中作者成刚的图书信息。
USE Library
DELETE BookPostTel WHERE Author='成刚'
案例:删除表”BookPostTel“中所有图书信息。
USE Library
DELETE BookPostTel
使用SQL创建索引
CREATE INDEX ON
语法结构:
CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]
INDEX 索引名
ON <表明|视图名> (列名[ASC|DESC][,...n])
功能:
案例:为了提高依据图书名查找图书的速度,在数据库”Library“中为图书表”Book“创建一个不唯一、非聚集索引”Bookindex“,索引键为”Bname“,升序排列。
USE Library
GO
CREATE NONCLUSTERED
INDEX Bookindex
ON Book(Bname ASC)
使用SQL删除索引
DROP INDEX
语法结构:
DROP INDEX 表明.索引名
案例:删除表”Book“的索引”Bookindex“。
USE Library
GO
DROP INDEX Book.Bookindex
INSERT INTO Student VALUES(2014216001,'赵成刚','男','1995-05-05','计算机应用技术',405.0)
INSERT INTO Student VALUES(2014216002,'李敬','女','1995-01-06','计算机应用技术',395.5)
INSERT INTO Student VALUES(2014216003,'郭洪亮','男','1995-04-12','计算机应用技术',353.0)
INSERT INTO Student VALUES(2014216004,'吕珊珊','女','1996-10-11','计算机信息管理',353.0)
INSERT INTO Student VALUES(2014216005,'高全英','女','1996-07-05','计算机信息管理',387.5)
INSERT INTO Student VALUES(2014216006,'郝莎','女','1994-08-03','计算机信息管理',372.0)
INSERT INTO Student VALUES(2014216007,'张峰','男','1995-09-03','软件技术',389.0)
INSERT INTO Student VALUES(2014216111,'吴秋娟','女','1995-08-05','软件技术',408.0)
INSERT INTO Student VALUES(2014216112,'穆金华','男','1995-10-06','软件技术',365.0)
INSERT INTO Student VALUES(2014216115,'张欣欣','女','1995-04-12','计算机网络技术',315.5)
INSERT INTO Student VALUES(2014216117,'孟霞','女','1996-01-11','计算机网络技术',334.0)
INSERT INTO Student VALUES(2015216008,'杨树华','女','1996-07-05','计算机应用技术',329.0)
INSERT INTO Student VALUES(2015216009,'程鹏','男','1996-08-03','计算机应用技术',342.6)
INSERT INTO Student VALUES(2015216030,'李岩','男','1997-09-03','计算机信息管理',316.0)
INSERT INTO Student VALUES(2015216031,'周梅','女','1997-06-03','计算机信息管理',312.0)
INSERT INTO Student VALUES(2015216032,'管西芬','女','1997-10-11','计算机信息管理',326.0)
INSERT INTO Student VALUES(2015216056,'刘明明','女','1997-10-09','软件技术',357.0)
INSERT INTO Student VALUES(2015216057,'孙政先','男','1996-05-16','软件技术',362.5)
INSERT INTO Student VALUES(2015216058,'王婷','女','1997-04-13','软件技术',356.0)
INSERT INTO Student VALUES(2015216088,'吕文昆','男','1996-09-03','计算机网络技术',335.0)
INSERT INTO Student VALUES(2015216089,'姜丽丽','女','1997-10-18','计算机网络技术',368.0)
INSERT INTO Course VALUES(16020010,'微机组装与维护',2.0)
INSERT INTO Course VALUES(16020011,'操作系统安装与使用',2.0)
INSERT INTO Course VALUES(16020012,'软件文档编辑与制作',3.5)
INSERT INTO Course VALUES(16020013,'面向过程程序设计',10.0)
INSERT INTO Course VALUES(16020014,'数据库开发与维护',6.5)
INSERT INTO Course VALUES(16020015,'面向对象程序设计',7.5)
INSERT INTO Course VALUES(16020016,'数字媒体采集与处理',4.0)
INSERT INTO Course VALUES(16020017,'静态网页设计与制作',3.0)
INSERT INTO Course VALUES(16020018,'Web标准设计',4.0)
INSERT INTO Course VALUES(16020019,'Web应用程序设计',7.0)
INSERT INTO Course VALUES(16020020,'计算机组网与管理',3.5)
INSERT INTO Course VALUES(16020021,'软件测试与实施',2.5)
INSERT INTO SC VALUES(2014216001,16020010,96.0)
INSERT INTO SC VALUES(2014216001,16020011,80.0)
INSERT INTO SC VALUES(2014216002,16020010,67.0)
INSERT INTO SC VALUES(2014216003,16020012,78.0)
INSERT INTO SC VALUES(2014216003,16020013,87.0)
INSERT INTO SC VALUES(2014216003,16020014,85.0)
INSERT INTO SC VALUES(2014216111,16020014,89.0)
INSERT INTO SC VALUES(2014216111,16020015,90.0)
INSERT INTO SC VALUES(2015216089,16020010,58.0)
语句结构:
SELECT 表达式[,...n] --投影(计算统计)
[INTO 新表名] --保存
FROM 表明[,...n] --连接
[WHERE 逻辑表达式] --选择
[GROUP BY 表达式][,...n] --分组统计
[HAVING 逻辑表达式] --限定分组统计
[ORDER BY 表达式][,...n] --排序
功能说明:
SELECT [ALL|DISTINCT] [TOP n] 表达式[,...n]
功能说明:
//语法
列名 [,...n]
//说明:当表达式为列名时,按照列名对表进行投影查询
案例:从教务管理数据库”EDUC“的学生版”Student"中查询出男生的学号、姓名、性别和专业的数据信息。
USE EDUC
GO
SELECT SID,Sname,Sex,Specialty FROM Student WHERE Sex='男'
//语法
TOP n [PERCENT]
//说明:如果未指定关键字PERCENT,则返回查询结果集的前n行数据。如果制定了关键字PERCENT,n就是查询返回结果集行的百分比
案例1:从教务管理数据库”EDUC“的课程表”Course“中查询出前3行的数据。
案例2:从教务管理数据库”EDUC“的学生表”Student“中查询出前20%的数据行。
//案例一
SELECT TOP 3 CID,Cname,Credit FROM Course
//案例二
SELECT TOP 20 PERCENT SID,Sname,Sex,Birthdate FROM Student
//语法
ALL|DISTINCT
//功能说明:指定ALL(缺省)关键字将保留查询结果集中全部数据行。当对表进行投影操作之后,在查询结果集中可能会出现重复的数据行,使用DISTINCT关键字可消除查询结果集中的重复数据行。
案例:从教务管理数据库”EDUC“的学生表”Student”中查询出学校各专业的名称。
SELECT DISTINCT Specialty FROM Student
//语法
*
//说明:若要投影表中所有的列并且不需要改变其顺序,可不必列出表中的所有列名,而用“*”替代。
案例:从教务管理数据库“EDUC”的学生表“Student”中查询出前3行的数据。
SELECT TOP 3 * FROM Student
//语法
表达式[,...n]
//说明:在SELECT子句的表达式中可以使用加(+)、减(-)、乘(*)、除(/)、取模(%)和字符连接(+)等运算符及各种函数进行运算,通过对表达式的计算来获取查询结果的列值。值得注意的是,对表中的列的计算只是影响查询结果,并不改变表中的数据。
案例:从教务管理数据库“EDUC”的课程“Course”表里查询出学分对应的课程学时(假设每18学时计1学分),显示前5行。
//表达式含运算符
SELECT TOP 5 CID,Cname,Credit,Credit*18 FROM Course
//表达式带函数
SELECT TOP 5 CID,Cname,Credit,STR(Credit*18,3,0)+'学时' FROM Course
//语法
1.'指定的列标题'=表达式
2.表达式 AS ‘指定的列标题’
说明:
案例:前例以中文显示列标题
SELECT TOP 5 CID AS '课程号',Cname AS '课 程 名',Credit 学分,学时=STR(Credit*18,3,0)
FROM Course
//语法
函数名([ALL|DISTINCT] 表达式|*)
说明:
常用函数 | 说明 |
---|---|
COUNT | 统计列中选取的项目个数或查询输出的行数 |
SUM | 计算指定的数值型列表表达式的总和 |
AVG | 计算指定的数值型列名表达式的平均值 |
MAX | 求出指定的数值、字符或日期型列名表达式的最大值 |
MIN | 求出指定的数值、字符或日期型列名表达式的最小值 |
案例:从学生表”Student“中统计出男生的人数。
//案例:从学生表”Student“中统计出男生的人数。
SELECT COUNT(*) AS 人数
FROM Student
WHERE Sex='男'
//案例:从学生表”Student“中给统计出专业个数。注意,DISTINCT关键字的作用时消除重复行,即每个专业只记一次。
SELECT COUNT(DISTINCT Specialty) AS 专业个数
FROM Student
//案例:从学生表”Student“中统计出学生的总数、录取分数”AScores“的最高分、最低分、总分(无实际意义,仅为举例)和平均分。
SELECT COUNT(*) AS 总人数,MAX(AScores) AS 最高分,MIN(AScores) AS 最低分,SUM(AScores) AS 总分,STR(AVG(AScores),5,1) AS 平均分
FROM Student
//语法
FROM 表名[,...n]
说明:
案例:从教务管理数据库”EDUC“中查询出学生的学号、姓名、所选课程名和成绩信息。
SELECT Student.SID,Sname,Cname,Scores --投影个表的列
FROM Student,SC,Course --三个表进行笛卡尔积
WHERE Student.SID =SC.SID
AND SC.CID=Course.CID --等值连接条件
//语法
表明[AS] 别名
说明:
//案例:同上例,为基表”Student“、”Course“和”SC“指定别名为x、y、z以简化表名
SELECT x.SID,Sname,Cname,Scores --投影各表的列
FROM Student AS x,SC AS y,Course AS Z --为三个表指定别名
WHERE x.SID=y.SID AND y.CID=z.CID --等值条件连接
//案例:从选课表”SC“中查询出选了至少两门课程的学生的学号。
SELECT x.SID,x.CID,y.CID
FROM SC x ,SC y --为表指定别名,实现自连接
WHERE x.SID=y.SID AND x.CID<>y.CID
//对于上例在SELECT子句中对学号进行投影,并加上DISTINCT参数,则会消除查询中重复的行
SELECT DISTINCT x.SID AS 学号
FROM SC x,SC y
WHERE x.SID=y.SID AND x.CID<>y.CID
//语法
WHERE 逻辑表达式
说明:
逻辑表达式 | 由列名、常量、变量】函数、子查询以及比较运算符或逻辑运算都组成,其值为真(1,‘TRUE’)或假(0,‘FALSE’) |
比较运算符 | =(等于)、<>(不等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于) |
逻辑运算符 | NOT(非)、AND(与)、OR(或) |
//案例:从学生表“Student”中查询出学生赵成刚的信息。
SELECT * FROM Student
WHERE Sname='赵成刚'
//案例:从学生表“Student”中查询出软件技术专业的学生信息。
SELECT * FROM Student
WHERE Specialty='软件技术'
//案例:从学生表“Student”中查询出到2019年满22岁的学生信息,结社系统日期为2019年。
SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate) 年龄
FROM Student
WHERE DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22
//案例:从学生表“Student”中查询出到2019年满22岁或大于22的女生信息,系统日期同上。
SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate) 年龄
FROM Student
WHERE DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22
AND Sex='女'
//案例:从学生表“Student”中查询出到2019年不满22岁的男生信息。
SELECT * DATEPART(year,GETDATE())-DATEPART(year,Birthdate) 年龄
FROM Student
WHERE NOT(DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22)
AND NOT(Sex='女')
//从学生表“Student”中查询出学号为2014216007和2015216089的学生信息
SELECT * FROM Student
WHERE SID = '2014216007' OR SID = '2015216089'
//语法
表达式[NOT] BETWEEN 开始值 AND 结束值
说明:指表达式的值是否在开始值和结束值之间,其中表达式可以为表中的列名。
//案例:从学生表“Student”中查询出入学录取分数在350分到360分之间的学生信息。
SELECT * FROM Student
WHERE AScores BETWEEN 350 AND 360
//语法
表达式[NOT] LIKE 字符串
说明:
//案例:从学生表“Student”中查询出“李”姓的学生信息。
SELECT * FROM Student
WHERE Sname LIKE '李%' --模式匹配模糊查询
//案例:从课程表“Course”中查询出有关程序设计方面的课程。
SELECT * FROM Course
WHERE Cname LIKE '%程序设计%'
)
//语法
表达式 [NOT] IN (列表|子查询)
其中列表为:表达式[,...n]
说明:
//案例:从学生表“Student”中查询学号为2014216007和2015216089的学生信息。
SELECT * FROM Student
WHERE SID IN('2014216007','2015216089')
//语法
表达式 IS [NOT] NULL
说明:
//案例:从教务管理数据库“EDUC”中查询出考试成绩没有登记 的学生信息。可先把表“SC”的某行的列“Scores”值改为空值“NULL(大写)”,注意不要改为0.0也是分数。
SELECT Student.SID,Sname,Cname,Scores
FROM Student,SC,Course
WHERE Student.SID=SC.SID AND SC.CID=Course.CID AND Scores IS NULL --空值判断
//语法
GROUP BY 列表达式[,...n]
说明:
注意:在SELECT子句中投影的列表达式必须包含在聚合函数中或者出现在相应的GROUP BY后的列表达式中。
//案例:从学生表”Student“中查询出各专业学生入学录取平均分。
SELECT Specialty AS 专业,'平均分'=str(AVG(AScores),5,1) --求个专业入学录取平均分
FROM Student GROUP BY Specialty --根据专业名称分组//案例:从学生表”Student“中查询出各专业的学生总数,要求查询结果显示专业名称和人数两个列。
SELECT '专业'=Specialty,'人数'=COUNT(*) --统计各专业学生人数
FROM Student
GROUP BY Specialty --根据专业名称进行分组//案例:从选课表”SC“中统计出每位学生的总成绩,要求查询结果显示出学生的学号、姓名和总成绩。
SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores) --求每位学生成绩的总和
FROM SC,Student
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname --按照学号SID分组
//语法
HAVING 逻辑表达式
说明:
//案例:
从选课表”SC“和学生表”Student“中查询总成绩超过150分的学生的学号、姓名和总成绩。
SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)
FROM SC,Student
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname
HAVING SUM(Scores)>150
//WHERE SUM(Sorese)>150是完全错误的,因为聚合函数SUM(Scores)不能放在WHERE子句的逻辑表达式中。
//语法
ORDER BY {表达式[ASC/DESC]}[,...n]
说明:
//案例:从选课表”SC“和学生表”Student“中统计出每位学生的总成绩,并将结果按照总成绩降序排序。
SELECT SC.SID,Student.Sname,'总成绩'=SUM(Scores)
FROM SC,Student
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname
ORDER BY SUM(SCORES) DESC --按照总成绩降序排序
//案例:从教务管理数据库”EDUC“中查询出每个学生的选课门数并按选课门数的多少进行升序排序。
SELECT Student.SID AS 学号,Student.Sname AS 姓名,COUNT(*) AS 选课门数
FROM Student,sc
WHERE Student.SID=SC.SID
GROUP BY Student.SID,Student.Sname
ORDER BY COUNT(*) --按选课门数升序排序
//案例:从学生表"Student"中查询出软件技术专业学生信息,并按照性别升序和入学录取成绩降序排列。
SELECT *
FROM Student
WHERE Specialty='软件技术'
ORDER BY Sex ASC,Ascores DESC
//语法
INTO 新表名
说明:
//案例:从选课表”SC“中将学号和课程号的内容保存为新表student_course。
SELECT SID,CID
INTO student_course
FROM SC
SELECT … FROM … JOIN … ON
说明:在SQLSERVER中,可以使用两种语法形式
//语法
FROM 表名1[连接类型] JOIN 表名2 ON 连接条件
说明:
连接类型 | 意义 |
---|---|
INNER JOIN | 内连接 |
LEFT [OUTER] JOIN | 左外连接 |
RIGHT [OUTER] JOIN | 右外连接 |
CROSS JOIN | 交叉连接 |
//语法
FROM 表名1 INNER JOIN 表名2 ON 连接表达式
说明:
//案例:
从图书馆里数据库”LIbrary“中查询每位读者的详细信息(读者及读者类型),允许由重复列。
USE Library
GO
SELECT Reader.*,ReaderType.*
FROM reader INNER JOIN ReaderType ON
Reader.TypeID=ReaderType.TypeID --内连接
//案例:查询每个读者的详细信息(读者及节约图书的信息),不允许有重复列。
SELECT Reader.*,Borrow.LendDate,Borrow.ReturnDate,Book.BID,Book.Bname,Book.Author,Book.Price
FROM Reader INNER JOIN Borrow ON
Reader.RID=Borrow.RID --表1内连接表2
INNER JOIN Book ON Borrow.BID=Book.BID --再内连接表3
//语法:
FROM 表名1 LEFT[OUTER] JOIN 表名2 ON 连接表达式
说明:
//案例:从表”Reader"和表”Borrow“中查询出读者的借阅情况,包括没有借过书的读者情况。
SELECT Reader.*,Borrow.RID,BID
FROM Reader LEFT OUTER JOIN BorroW
ON Reader.RID=Borrow.RID
//说明:连接结果保留了表”Reader“中不满足等值条件的第3行和第4行,表”Borrow“中相应的各列为NULL值,说明读者张英和李亚茜没借过书。
//语法:
FROM 表名1 RIGHT[OUTER] JOIN 表名2 ON 连接表达式
说明:
//案例:从表”Borrow"和表”Book“中查询出图书被的借阅情况,包括没有被借的书的的情况。
SELECT Borrow.RID,Borrow.BID,Bname,Author
FROM Borrow RIGHT OUTER JOIN Book
ON Borrow.BID=Book.BID
//说明:连接结果介入了表”Book“中不满足等值条件的第1、4、8、10行,表”Borrow“中相应的列为NULL,说明图书《ERP从内部集成开始》、《ERP系统的集成应用》、《SQL Server 2008数据库设计与实现》和《数据库系统概论》四本书没有被基础过,《SAP基础教程》被借出过两次。
//语法
FROM 表名1 FULL[OUTER] JOIN 表名2 ON 连接表达式
说明:
//案例:借阅和读者的全外连接
SELECT Reader.*,Borrow.RID,Borrow.BID
FROM Borrow FULL OUTER JOIN Reader
ON Borrow.RID=Reader.RID
//语法
FROM 表1 别名1 JOIN 表名1 别名2 ON 连接表达式
说明:
//案例:从图书管理数据库”Library“中查询借了两本以上图书的读者的借书信息
//步骤1:表”Borrow“与表”Borrow“自连接
SELECT x.RID,x.BID,x.ReturnDate,y.RID,y.BID
FROM Borrow x JOIN Borrow y ON x.RID=y.RID --读者百年好等值条件自连接
WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL --还期为空,即尚未归还
说明:
//步骤2:对表”Borrow“与表”Borrow“自连接加上选择条件和投影操作。
SELECT x.RID,x.BID,x.ReturnDate
FROM Borrow x JOIN Borrow y ON x.RID=y.RID --读者百年好等值条件自连接
WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL --还期为空,即尚未归还
AND x.BID<>y.BID --图书编号不同,即两本不同的书
说明:
//语法
FROM 表名1 CROSS JOIN 表名2
说明:
//语法
FROM 表名1 JOIN 表名2 ON 连接表达式 JOIN 表名3 ON 连接表达式
说明:
//案例:对读者表”Reader“、借阅表”Borrow“和图书表”Book“三个表进行等值连接。
SELECT t1.RID,Rname,BnamE,LendDate
FROM Reader t1 JOIN Borrow t2 ON t1.RID=t2.RID JOIN BooK t3 ON t2.BID=t3.BID
SELECT … FROM … SELECT …
功能说明:
//语法
表达式 [NOT] IN(列表|子查询)
说明:
//案例:从图书管理数据库“Library”中查询出节约过“人民邮电出版社”出版图书的读者编号(不包括重复的行)。
USE Library
SELECT DISTINCT RID --投影满足条件的读者编号,去重复行
FROM Borrow --对于表“Borrow”中的每一行数据判断其所借图书的编号是否在对表“Book”子查询的集合中
WHERE BID IN (SELECT BID FROM Book WHERE Publisher='人民邮电出版社') --人民邮电出版社图书的编号集合
//案例:从图书管理数据库“Library”中查询出没有借过书的读者信息。SELECT * FROM Reader --对于表中的每一位读者判断读者编号是否不在子查询的集合中WHERE RID NOT IN (SELECT RID FROM Borrow) --借了书的读者编号集合
//语法
表达式 比较运算符SOME|ANY(子查询)
说明:
//案例:从图书馆里数据库“Library”中查询当前借出的清华大学出版社的图书借期等信息。
SELECT Borrow.RID,Borrow.BID,Borrow.LendDate,Book.Bname,Book.Publisher,Book.LendOut
FROM Borrow,Book --对于借阅表中的每借出的一本书
WHERE Borrow.BID=SOME --图书编号是否在子查询集合中
(SELECT BID FROM Book WHERE LendOut='True' AND Publisher='清华大学出版社')
AND Borrow.BID=Book.BID --等值连接条件
//语法
表达式 比较运算符 ALL(子查询)
说明:
//案例:从借阅表“Borrow”中查询出读者编号RID最大的读者的结束情况。
SELECT *
FROM Borrow
WHERE RID>=ALL(SELECT RID FROM Reader)
//语法
[NOT] EXISTS(子查询)
说明:
//案例:从图书管理数据库“Library”中,用EXISTS子查询查询出借阅了“人民邮电出版社”出版图书的读者编号。
SELECT DISTINCT RID
FROM Borrow --对于每一条借阅信息的图书编号BID
WHERE EXISTS --判定子查询是否有满足子查询条件的返回值
(SELECT * FROM Book WHERE Borrow.BID=Book.BID AND Publisher='人民邮电出版社')
注意:
代码中WHERE子句后的“Borrow.BID=Book.BID”并不是等值连接条件,而是子查询中的选择条件,判断主查询的“Borrow.BID”与子查询的“Book.BID”是否相等。
在update语句中的使用
update更新数据语句:
//案例:计算读者表“Reader”中的已借书数量列“Lendnum”
UPDATE Reader
SET Lendnum=
(SELECT COUNT(*) FROM Borrow
WHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID) --从借阅表“Borrow”中统计出每个读者借书的册数
//案例:计算借阅表“Borrow”中的应还日期列“SReturnDate”值。
UPDATE Borrow --更新借阅表数据
SET SReturnDate=DATEADD(dd, --对借阅表的每一行的应还日期列赋值函数的第二个参数“限借天数”加上第三个参数“借期”得到“应还日期”
(SELECT ReaderType.LimitDays --子查询得到限借天数
FROM Reader INNER JOIN ReaderType ON Reader.TypeID=ReaderType.TypeID --等值连接和条件
WHERE Borrow.RID=Reader.RID), --借阅表对应的读者编号条件描述
Borrow.LendDate) --函数的第三个参数为借期
主要区别:子查询中的表和主查询中的表分为内外嵌套的查询,而连接查询是同在一个层面的查询。
//案例:查询出图书表“Book”中价格最低的图书的编号和书名,采用子查询完成此任务。
SELECT BID AS 图书编号,Bname AS 书名
FROM Book
WHERE Price=(SELECT MIN(Price) FROM Book)//案例:连接查询:查询读者编号、读者姓名、所借图书和借阅时间。
SELECT Reader.RID,Reader.Rname,Book.Bname,Borrow.LendDate
FROM Reader,Borrow,Book
WHERE Reader>RID=Borrow.RID
AND Book.BID=Borrow.BID
//语法:
SELECT_1
UNION [ALL]
SELECT_2
UNION [ALL]
SELECT_3
...
说明:
//案例(不包括重复行):从图书管理数据库“Library”的图书表“Book”中,查询出“人民邮电出版社”出版的图书和“清华大学出版社”出版的图书的作者名,不包括重复的行。
SELECT Author
FROM Book
WHERE Publisher='人民邮电出版社'
UNION
SELECT Author
FROM Book
WHERE Publisher='清华大学出版社'
//案例(包括重复行):从图书管理数据库“Library”的图书表“Book”中,查询出“人民邮电出版社”出版的图书和“清华大学出版社”出版的图书的作者名,包括重复行。
SELECT Author
FROM Book
WHERE Publisher='人民邮电出版社'
UNION ALL
SELECT Author
FROM Book
WHERE Publisher='清华大学出版社'
//案例:从图书管理数据库“Library”的图书表“Book”中,查询出“人民邮电出版社”出版的图书和“清华大学出版社”出版的图书的作者名,不包括重复的行并将查询结果进行排序。
SELECT Author
FROM Book
WHERE Publisher='人民邮电出版社'
UNION
SELECT Author
FROM Book
WHERE Publisher='清华大学出版社'
ORDER BY Author DESC
CREATE VIEW
//语法
CREATE VIEW 视图名[(列名)[,...N]] --指定视图列名
AS SELECT查询语句
说明:
//在图书管理数据库“Libiary”中,由图书表“Book”创建出人民邮电出版社出版的图书视图“View——BookPostTel”。
USE Library
GO
CREATE VIEW View_BookPostTel
AS
(SELECT BID,Bname,Author,Publisher,Price
FROM Book
WHERE Publisher='人民邮电出版社'
)
//创建视图“View_RBorrow”,得到读者借书应还日期信息。
CREATE VIEW View_Rborrow(读者编号,姓名,图书编号,图书名,应还日期)
AS
(SELECT Reader.RID,Reader.Rname,Book.BID,Book.Bname,
DATEADD(dd,ReaderType.LimitDays,Borrow.LendDate)
FROM Reader INNER JOIN Borrow ON Reader.RID=Borrow.RID
INNER JOIN ReaderType ON Reader.TypeID=ReaderType.TypeID
INNER JOIN Book ON Borrow.BID=Book.BID
WHERE Borrow.ReturnDate IS NULL)
//创建视图“View_Overdue”,从视图“View_RBorrow”中查询出借阅超期的读者信息。
CREATE VIEW View_Overdue
AS
(SELECT * FROM View_RBorrow
WHERE(应还日期<GETDATE())))
//语法
ALTER VIEW 视图名
AS SELECT 查询语句
说明:此命令可以修改已经创建了的视图,除命令不同以外,其他参数与创建视图语句中的完全相同。
//修改人民邮电出版社出版的图书视图“View_BookPostTel”,为视图指定列名。
ALTER VIEW View_BookPostTel(图书编号,书名,作者,出版社,价格)
AS
(SELECT BID,Bname,Auhtor,Publisher,Price
FROM Book
WHERE Publisher='人民邮电出版社')
1.3使用T-SQL删除视图
//语法
DROP VIEW 视图名
//删除视图V1_BOOKS
DROP VIEW V1_BOOKS
2.使用T-SQL应用视图
注意:
//从读者借书应还日期信息视图“View_RBorrow”中查询出读者“程鹏”所借图书的应还日期等信息。
SELECT 读者编号,姓名,图书编号,图书名,应还日期
FROM View_RBorrow
WHERE 姓名=’程鹏‘//通过视图“View_BookPostTel”插入一本人民邮电出版社出版的名为“SQL Server 2008数据库设计与实现”的 图书。
INSERT INTO View_BookPostTel(图书编号,书名,作者,出版社,价格)
VALUES('TP311.138/231','SQL Server 2008数据库设计与实现','Louis Davidson','人民邮电出版社','89.00')//通过视图“View_BookPostTel”更新人民邮电出版社出版的名为“SQL Server 2008数据库设计与实现”的 图书为“SQL Server 2008数据库设计”。
UPDATE View_BookPostTel
SET 书名=’SQL Server 2008数据库设计‘
WHERE 书名=’SQL Server 2008数据库设计与实现‘//通过视图“View_BookPostTel”删除人民邮电出版社出版的名为“SQL Server 2008数据库设计”的图书。
DELETE FROM View_BookPostTel
WHERE 书名=’SQL Server 2008数据库设计‘
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态