一、对数据库的操作

1. 用SQL语句创建数据库

如创建教师数据库

create DATABASE Teach
ON
(NAME=Teach_data,FILENAME='D:\Teachdata.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5
)
LOG ON
(NAME=Teach_Log,FILENAME='D:\TeachLog.mdf,SIZE=5MAXSIZE=25FILEGROWTH=5
)	

2.用SQL语句修改数据库

如修改Teach数据库中的Teach_Data文件增容方式为一次增加20MB

ALTER DATABASE Teach
MODIFY FILE
(NAME=Teach_Data,FILEGROWTH=20MB
)

修改数据库Teach，增加一个次要数据文件，逻辑名称Teach_Datanew,存放在E盘根目录下，文件名为Teach_Datanew.ndf。数据文件的初始大小为100MB,最大容量为200MB，文件自动增长为10MB

ALTER DATABASE Teach
ADD FILE(NAME=Teach_Datanew,FILENAME='E:\Teach_Datanew.ndf',SIZE=100,MAXSIZE=200,FILEGROWTH=10
)

从Teach数据库中删除增加的次要数据文件

ALTER DATABASE Teach
REMOVE FILE Teach_Datanew

3.用SQL语句删除数据库

DROP DATABASE 数据库名称

替换：Ctrl/Command + G

二、对数据表的操作

1.创建数据表

其中列定义为

如创建学生表S

CREATE TABLE S
(SNo CHAR(6),SN VARCHAR(8),Sex NCHAR(2) DEFAULT'女',Age INT,Dept NVARCHAR(20)
)

定义数据表的约束

NULL/NOT NULL

NOT NULL 表示主键列不允许出现空值

UNIQUE约束（唯一约束）

指明基本表在某一列或多个列的组合上的取值必须唯一
如建立一个表S，定义SN为唯一键

CREATE TABLE S
(SNo CHAR(6),SN VARCHAR(8) UNIQUE,Sex NCHAR(2) DEFAULT'女',Age INT,Dept NVARCHAR(20)
)

建立一个表S，定义SN+SEX为唯一键

CREATE TABLE S
(SNo CHAR(6),SN VARCHAR(8),Sex NCHAR(2) DEFAULT'女',Age INT,Dept NVARCHAR(20),CONSTRAINT S_UNIQ UNIQUE(SN,Sex)
)

PRIMARY KET 主键约束

用于定义基本表的主键，起唯一标识作用
·不能为NULL，不能重复
·不能对同一个列或同一组列既定义UNIQUE约束，又定义PRIMARY KEY约束

如建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为主键

CREATE TABLE S
(SNo CHAR(6) CONSTRAINT S Prim PRIMARY KEY,SN CHAR(8),Sex CHAR(2),Age INT,Dept VARCHAR(20)
)
CREATE TABLE C
（CNo CHAR(5) CONSTRAINT C Prim PRIMARY KEY,CN CHAR(20),CT INT
)

建立一个SC表，定义SNo+CNo为SC的主键

CREATE TABLE SC
(SNo CHAR(5) NOT NULL,CNo CHAR(5) NOT NULL,Score NUMERIC(4,1),CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo)
）

外键约束

如建立一个SC表，定义SNo，CNo为SC的外部键。

CREATE TABLE SC
(SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo),CNo CHAR(5) NOT NULL CONSTRAINT C_Fore
FOREIGN KEY REFERENCES C(CNo),Score NUMERIC(4,1),CONSTRAINT S_C_Prim PRIMARY KEY(SNo,CNo)
)

CHECK 约束

CHECK约束用于检查字段值所允许的范围
在建立CHECK约束时，需要考虑以下几个因素：
·一个表可以定义多个CHECK约束
·每个字段只能定义一个CHECK约束
·在多个字段上定义的CHECK约束必须为表约束
·当执行INSERT、UPDATE语句时CHECK约束将验证数据

如建立一个SC表，定义Score的取值范围为1-100

CREATE TABLE SC
(SNo CHAR(5),CNo CHAR(5),Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score<=100)
)

建立包含完整性定义的学生表

CREATE TABLE S
(SNo CHAR(5) CONSTRAINT S_Prim PRIMARY KEY, --主键约束SN CHAR(8) CONSTRAINT SN_Cons NOT NULL, --非空约束Sex CHAR(2) DEFAULT'女',Age INT CONSTRAINT Age_Cons NOT NULLCONSTRAINT Age_Chk CHECK(Age BETWEEN 15 AND 50),Dept CHAR(10) CONSTRAINT Dept_Cons NOT NULL
)

·如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级上也可以定义在表级上。

2.修改数据表

如在S表中增加一个班号列和住址列

ALTER TABLE S
ADD
CLASS_No CHAR(6),
Address CHAR(40)

在SC表中增加完整性约束定义，使Score在0~100之间

ALTER TABLE SC
ADD
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)  把S表中的SN列加宽到10个字符
ALTER TABLE S
ALTER COLUMN
SN CHAR(10)删除S表中的主键
ALTER TABLE S
DROP CONSTRAINT S_Prim

3.删除数据表

三、数据查询

1.Select命令的格式及基本使用

无条件查询的基本格式

如选出所有店名

SELECT Store_Name FROM Store_Information

如学生表S(SNo,SN,AGE,DEPT,SEX)
1.查询全体学生的学号、姓名和年龄

SELECT SNo,SN,Age
FROM S

2.查询学生的全部信息

SELECT *
FROM S

去重查询的基本格式

SELECT DISTINCT Store_Name FROM Store_Information

2.条件查询

常用的比较运算符

条件查询的基本格式

如查询营业额超过1000的商店名称

SELECT Store_Name FROM Store_Information WHERE Sales>1000

如学生选课表S(SNo,CNo，Score）
1.查询选修课程号为‘C1’的学生的学号和成绩

SELECT SNo,Score
FROM SC
WHERE CNO='C1'

2.查询成绩高于85分的学生的学号、课程号和成绩

SELECT SNo,CNo,Score
FROM SC
WHERE Score>85

多重条件查询的基本格式

查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩

SELECT SNo,CNo,Score
FROM SC 
WHERE(CNo='C1'OR CNo='C2')AND(Score>=85)

确定集合的条件查询的基本格式

·利用IN操作可以查询属性值属于指定集合的元组

如查询选修C1或C2的学生的学号、课程号和成绩

SELECT SNo，CNo，Score
FROM SC
WHERE CNo IN（'C1','C2')

确定范围的条件查询的基本格式

如教师表T(TNo,TN,Prof,Sal)
查询工资在1000至1500元之间的教师的教师号、姓名及职称

SELECT TNo,TN,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500

查询工资不在1000至1500元之间的教师的教师号、姓名及职称

SELECT TNo,TN,Prof
FROM T
WHERE Sal NOT BETWEEN 1000 AND 1500

部分匹配的条件查询的基本格式

·Like能让我们依据一个套式（pattern）来找出我们要的资料
·‘A_Z’:所有以’A’起头，另一个任何值的字原，且以’Z’为结尾的字串
·‘ABC%’：所有以‘ABC’起头的字串
·‘%XYZ’：所有以‘XYZ’结尾的字串
·‘%AN%’:所有含有‘AN’这个套式的字串
·[ ]表示在某一范围内的字符。
如[0-9]
·[^]表示不在某一范围内的字符。如[^0-9]

如教师表T(TNo,TN,Prof,Sal)
查询所有姓张的教师的教师号和姓名

SELECT TNo,TN
FROM T
WHERE TN LIKE '张%'

查询姓名中第二个汉字是”力“的教师号和姓名

SELECT TNo,TN
FROM T
WHERE TN LIKE '_力%'

空值查询

·某个字段没有值称之为具有空值(NULL)

如查询没有考试成绩的学生的学号和相应的课程号

SELECT SNo,CNo
FROM SC
WHERE Score IS NULL

3.常用函数查询

函数查询的基本格式

如求选修C1号课程的最高分、最低分及之间相差的分数

SELECT MAX(Score)AS MAXSCORE,MIN(Score)AS MinScore,MAX(Score)-Min(Score) AS Diff
FROM SC
WHERE（CNo='C1'）

求学校中共有多少个系

SELECT COUNT(DISTINCT Dept)AS DeptNUm  --DISTINCT消去重复行
FROM S

·COUNT和DISTINCT经常合起来用
目的是找出表格中有多少不同的资料

SELECT COUNT(DISTINCT Store_Name)
FROM Store_Information

统计有成绩同学的人数

SELCECT COUNT(Score)
FROM SC

求计算机系学生的总数

SELECT COUNT(*) FROM S
WHERE Dept='计算机'

4.分组查询

分组查询的基本格式

·当选择不止一个列，且其中至少一列包含函数的运用时，就需要用到GROUP BY指令。
除了有包含函数的列外，都需要放在GROUP BY的子句中

如查询各个教师的教师号及其任课的门数

SELECT TNo,COUNT(*)
FROM TC
GROUP BY TNo

所有具有相同TNo的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数

·有HAVING子句的SQL一定要包含GROUP BY子句
·若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句
如查询哪些店的营业额超过1500

SELECT Store_NAME,SUM(Sales)
FROM Store_INformation
GROUP BY Store_Name
HAVING SUM(sales)>1500

查询选修两门以上课程的学生的学号和选课门数

SELECT SNo,COUNT(*) FROM SC
GROUP BY SNo
HAVING(COUNT(*)>=2)

5.别名

6.排序查询

如查询选修C1的学生学号和成绩，并按成绩降序排列

SELECT SNo,Score
FROM SC
WHERE(CNo='C1')
ORDER BY Score DESC

查询选修C2，C3，C4或C5课程学生的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列

SELECT SNo,CNo,Score
FROM SC
WHERE CNo IN('C2','C3','C4','C5')
ORDER BY SNo,Score DESC

默认为升序

求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查新结果按总成绩降序列出

SELECT SNo,SUM(Score)AS TotalScore
FROM SC
WHERE(Score>=60)  --筛选Score>=50的元组
GROUP BY SNo  --将选出的元组按SNo分组
HAVING(COUNT(*)>3)  --筛选选课三门以上的小组
ORDER BY SUM(Score) DESC    --将选取结果排序

数据表连接及连接查询

·连接查询：一个查询需要对多个表进行操作
·表之间的连接：连接查询的结果集或结果表
·连接字段：数据表之间的联系是通过表的字段值来体现的
·表的连接方法：
表之前满足一定条件的行进行连接时，FROM子句指明进行连接的表明，WHERE子句指明连接的列名及其连接条件
利用关键字JOIN进行连接：当将JOIN关键字放于FROM子句中时，应有关键字ON与之对应，以表明连接的条件

等值连接与非等值连接

查询”刘伟“老师所讲授的课程，要求列出教师号，教师姓名和课程号

SELECT T.TNo,TN,CNo
FROM T,TC
WHERE(T.TNo=TC.TNo)AND(TN='刘伟')

关键字INNER JOIN的连接方法

查询”刘伟“老师所讲授的课程，要求列出教师号，教师姓名和课程号

SELECT T.TNo,TN,CNo
FROM T INNER JOIN TC
ON T.TNo=TC.TNo
WHERE(TN='刘伟')

查询所有选课学生的学号、姓名、选课名称及成绩

SELECT S.SNo,SN,CN,Score
FROM S,C,SC
WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo

查询每门课程的课程名、任课教师姓名及其职务、选课人数。

SELECT CN,TN,Prof,COUNT(SC.SNo)
FROM C,T,TC,SC
WHERE T.TNO=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo
GROUP BY SC.CNo

自身连接

当一个表格与其自进行连接操作时，称为自身连接

查询所有比”刘伟“工资高的教师姓名、工资和刘伟的工资

SELECT X.TN,X.SAL AS SAL_a,Y.SAL AS SAL_b
FROM T AS X,T AS Y
WHERE X.SAL>Y.SAL AND Y.TN='刘伟'

SELECT X.TN,X.SAL,Y.SAL
FROM T AS X INNER JOIN T AS Y
ON X.SAL>Y.SAL
WHERE Y.TN='刘伟'

JOIN的分类

外连接

Persons

Orders

LEFT JOIN


RIGHT JOIN


FULL JOIN

子查询

子查询用于连接表格
Store_Information

Geography

子查询用于连接表格
运用subquery来找出所有在西部的店的营业额

SELECT SUM(Sales)
FROM Store_information
WHERE Store_Name IN
(
SELECT store_name FROM Geography
WHERE regoin_name='West'
)

返回一个值的子查询
如查询与”刘伟“老师职称相同的教师号和姓名

SELECT TNo,TN
FROM T
WHERE Prof=
(SELECT Prof
FROM T
WHERE TN='刘伟')

查询讲授课程号为C5的教师姓名
(使用ANY）

SELECT TN
FROM T
WHERE(TNo=ANY(SELECT TNoFROM TCWHERE CNo='C5'))

查询其他系中比计算机系某一教师工资高的教师的姓名和工资

SELECT TN,SAL
FROM T
WHERE(Sal>ANY(SELECT SalFROM TWHERE Dept='计算机'))
AND(Dept<>'计算机')

SELECT TN,SAL
FROM T
WHERE(Sal>SELECT Min(Sal)FROM TWHERE Dept='计算机'))
AND(Dept<>'计算机')

使用ALL
查询其他系中比计算机系所有教师工资都高的教师的姓名和工资

SELECT TN,Sal
FROM T
WHERE(Sal>All(SELECT SalFROM TWHERE Dept='计算机'))
AND(Dept<>'计算机')

查询不讲授课程号为C5的教师姓名

SELECT DISTINCT TN
FROM T
WHERE('C5'<>ALL(SELECT  CNoFROM TCWHERE TNo=T.TNo))

合并查询

[SQL 语句1]UNION[SQL语句2]

找出所有有营业额的日子

SELECT Txn_Date FROM Store_Information
UNION SELECT Txn_Date FROM Internet_Sales

从SC数据表中查询出学号为S1同学的学号和总分
再从SC数据表中查询出学号为S5同学的学号和总分
然后将两个查询结果合并为一个结果集

SELECT SNo AS 学号，SUM(Score) AS 总分
FROM SC
WHERE(SNo='S1')
GROUP BY SNo
UNION
SELECT SNo AS 学号,SUM(Score) AS 总分
FROM SC
WHERE(SNo='S5')
GROUP BY SNo

查询结果到表中

从SC数据表中查询所有同学的学号和总分，并将查询结果存放到一个新的数据表cal_Table中

SELECT SNo AS 学号,SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo

四、数据操纵

添加数据

添加一条新纪录

在S表中添加一条学生记录

INSERT INTO S(SNo,SN,Age,Sex,Dept)
VALUES('20','ymh',21,'女','计算机')

添加一行记录的部分值

在SC表中添加一条选课记录

INSERT INTO SC(SNo,CNo)
VALUES('S7','C1')

求出各系教师的平均工资，把结果存放在新表AvgSal中

CREATE TABLE AvgSal
(Department VARCHAR(20),Average SMALLINT
)

修改数据

UPDATE T
SET Dept='信息'
WHERE TN='刘伟'

UPDATE S
SET Age=Age+1

UPDATE T
SET Comm=Comm+100
WHERE(TNo IN(SELECT TNoFROM TCWHERE CNo='C5')
)

UPDATE T
SET Sal=(SELECT 1.2*AVG(Sal)FROM T)

删除数据

DELETE
FROM T
WHERE TN='刘伟'

DELETE
FROM TC
WHERE(TNo=(SELECT TNoFROM TWHERE TN='刘伟’))