一、对数据库的操作
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=5
MAXSIZE=25
FILEGROWTH=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 NULL
CONSTRAINT 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的连接方法![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/5f4e424e434f308bafc15daf54eec516.png)
查询”刘伟“老师所讲授的课程,要求列出教师号,教师姓名和课程号
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 TNo
FROM TC
WHERE CNo='C5'))
查询其他系中比计算机系某一教师工资高的教师的姓名和工资
SELECT TN,SAL
FROM T
WHERE(Sal>ANY(SELECT Sal
FROM T
WHERE Dept='计算机'))
AND(Dept<>'计算机')
SELECT TN,SAL
FROM T
WHERE(Sal>SELECT Min(Sal)
FROM T
WHERE Dept='计算机'))
AND(Dept<>'计算机')
使用ALL
查询其他系中比计算机系所有教师工资都高的教师的姓名和工资
SELECT TN,Sal
FROM T
WHERE(Sal>All(SELECT Sal
FROM T
WHERE Dept='计算机'))
AND(Dept<>'计算机')
查询不讲授课程号为C5的教师姓名
SELECT DISTINCT TN
FROM T
WHERE('C5'<>ALL(SELECT CNo
FROM TC
WHERE 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 TNo
FROM TC
WHERE CNo='C5')
)
UPDATE T
SET Sal=(SELECT 1.2*AVG(Sal)
FROM T)
删除数据
DELETE
FROM T
WHERE TN='刘伟'
DELETE
FROM TC
WHERE(TNo=
(SELECT TNo
FROM T
WHERE TN='刘伟’))