一、关于数据库的SQL命令
1.创建数据库
CREATE DATABASE 数据库名称
[ON
[FILEGROUP 文件组名称]
(
NAME=数据文件逻辑名称,
FILENAME='路径+数据文件名',
SIZE=数据文件初始大小,
MAXSIZE=数据文件最大容量,
FILEGROWTH=数据文件自动增长容量,
)]
[LOG ON
(
NAME=日志文件逻辑名称,
FILENAME='路径+日志文件名',
SIZE=日志文件初始大小,
MAXSIZE=日志文件最大容量,
FILEGROWTH=日志文件自动增长容量,
)]
[COLLATE 数据库校验方式名称]
[FOR ATTACH]
注释：
1.用[ ]括起来的语句，可以选用或者不选用.
2.如果只用 “CREATE DATABASE 数据库名称”，DBMS会默认逻辑名称、文件组大小等属性.
2.修改数据库
ALTER DATABASE 数据库名称
//1.向数据库中添加数据文件
ADD FILE(
具体文件格式)
[TO FILEGROUP 文件组名]
//2.向数据库中添加数据文件
|ADD LOG FILE(
具体文件格式)
//3.向数据库删除逻辑文件，并删除物理文件
|REMOVE FILE 文件逻辑名称
//4.指定要修改的文件
|MODIFY FILE(
具体文件格式)
//5.向数据库中添加文件组
|ADD FILEGROUP 文件组名
//6.从数据库中删除文件组
|REMOVE FILEGROUP 文件组名
//7.修改文件组名称，或设置文件组的只读、读写，指定文件组为默认文件组
|MODIFY FILEGROUP 文件组名
{
READ_ONLY|READ_WRITE,
| DEFAULT,
| NAME = 新文件组名 }
其中,“具体文件格式”为:
(
NAME = 文件逻辑名称
[,NEWNAME = 新文件逻辑名称]
[,SIZE = 初始文件大小]
[,MAXSIZE = 文件最大容量]
[,FILEGROWTH = 文件自动增长容量]
)
其中“|”表示单选
3.删除数据库
DROP DATABASE 数据库名称
1
4.查询数据库
4.1用系统存储过程显示数据库结构
Sp_helpdb [[@dbname=] 'name']
1
4.2用系统存储过程显示文件信息
Sp_helpfile [[@filename =] 'name']
1
4.3用系统存储显示文件组信息
Sp_helpfilegroup [[@filegroupname =] 'name']
1
二、数据类型介绍
1.数值
2.字符串
3.时间日期
4.数据库的字段属性
4.1
UnSigned
无符号的
声明了该列不能为负数
4.2
ZEROFILL
0填充的
不足位数的用0来填充 , 如int(3),5则为005
4.3
Auto_InCrement
通常理解为自增，自动在上一条记录的基础上默认+1
通常用来设计唯一的主键，必须是整数类型
可定义起始值和步长
当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
4.4
NULL 和 NOT NULL
默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值
4.5
DEFAULT
默认的，用于设置默认值
4.6
拓展
三、关于数据表的SQL命令
1.创建数据表
CREATE TABLE 表名
( 列名 数据类型 表约束，
)
例子：
CREATE TABLE S
( SNO VARCHAR(6),
SN NVARCHAR(10),
SEX NCHAR(1) DEFAULT '男', )
2.数据表的约束
2.1 NULL/NOT NULL约束
NULL值不是0，也不是空白，指“不知道’、”不确定“的意思，
NOT NULL指不允许出现空值。
2.2 UNIQUE约束(唯一约束)
用于指明基本表在某一列或多个列的组合上取值必须唯一
建立UNIQUE约束时，需要考虑以下几个因素。
1.使用 UNIQUE约束的字段允许为NULL值，但系统为保证其唯一性,最多只可以出现一个NULL值。
2.一个表中可以允许有多个 UNIQUE约束
3.UNIQUE约束用于强制在指定字段上创建一个 UNIQUE索引,缺省为非聚集索引，UNIQUE既可用于列约束,也可用于表约束。
2.3 PRIMARY KEY 约束(主键约束)
语法格式：
CONSTRAINT S_Prim PRIMARY KEY (列约束)
PRIMARY KEY (表约束)
PRIMARY KEY 约束用于定义基本表的主键，起唯一标识作用，其值不能为空.
PRIMARY KEY与 UNIQUE约束类似,通过建立唯一索引来保证基本表在主键列取值的唯一性,但它们之间存在着很大的区别.
1.在一个基本表中只能定义一个 PRIMARY KEY约束,但可定义多个 UNIQUE约束。
2.对于指定为 PRIMARY KEY的一个列或多个列的组合,其中任何一个列都不能出现NULL值,而对于 UNIQUE所约束的唯一键,则允许为NULL。
3.不能为同一个列或一组列,既定义 UNIQUE约束,又定义 PRIMARY KEY约束。
2.4 FOREIGN KEY 约束(外键约束)
语法格式：
NOT NULL FOREIGN KEY REFERENCES 主表名(列名)
例子：
例子：
CREATE TABLE SC
( SNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES S(SNO),
CNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES C(CNO),
CONSTRAINT S_C_Prim PRIMARY KEY (SNO,CNO) )
1.外部键必须是主表中的一个主键值或唯一值，因此才有最后一行SQL命令
2.其次主键值不能为空，因此有NOT NULL
外键约束作用：
指定一列或几列作为外部键，包含外部键的表称为从表，
包含外部键所引用的主键或唯一键称为主表，
系统保证从表在外部键上的取值值是主表中某一个主键值或唯一键，或者取空值.
2.5 CHECK约束
CHECK约束用于检查字段值所允许的范围
语法格式：
CHECK (条件)
例子：
建立一个SC表,定义 Score的取值范围为0~100之间。
CREATE TABLE SC
( SNO VARCHAR(6)
CNO VARCHAR(6)
Score NUMERIC(4, 1) CHECK (Score>=0 AND Score<=100) )
建立CHECK约束，需要考虑以下几个因素
1.一个表中可以定义多个 CHECK约束。
2.每个字段只能定义一个 CHECK约束。
3.在多个字段上定义的 CHECK约束必须为表约束。
4.当执行 INSERT、 UPDATE语句时, CHECK约束将验证数据。
3.修改数据表
3.1 ADD方式
ADD方式用于增加新列和完整性约束
语法格式：
ALTER TABLE 表名
ADD
列名 数据类型
....
3.2 ALTER方式
ALTER方式用于修改某些列
ALTER TABLE 表名
ALTER COLUMN
列名 数据类型
....
使用此方式有如下一些限制
1.不能改变列名
2.不能将含有空值的列的定义修改为 NOT NULL约束
3.若列中已有数据,则不能减少该列的宽度,也不能改变其数据类型。
4.只能修改 NULL/NOT NULL约束其他类型的约束在修改之前必须先将约束删除,然后再新添加修改过的约束定义。
3.3 DROP方式
DROP方式只用于删除完整性约束定义
其语法格式为
ALTER TABLE<表名>
DROP CONSTRAINT<约束名>
【例】删除S表中的主键。
ALTER TABLE S
DROP CONSTRAINT S Prim
4.删除数据表
语法格式：
DROP TABLE 表名
四、关于数据表查询的SQL命令
1.单关系表的数据查询
1.1 无条件查询
语法格式：
SELECT 列名
FROM 表名
//1. *表示表的全部列名
SELECT *
FORM S
//2. DISTINCT关键字用于消除重复行
SELECT DISTINCT SNO
FROM SC
//3. AS 表示别名，输出别名
SELECT SN AS Name,SNO,AGE
FROM S
1.2 条件查询
| 运算符 | 含义 |
|---|---|
| =、>、<、>=、<= 、!=、<> | 比较大小 |
| AND、OR、NOT | 多重条件 |
| BETWEEN AND | 确定范围 |
| IN | 确定集合 |
| LIKE | 字符匹配 |
| IS NULL | 空值 |
语法格式：
SELECT 列名
FROM 表名
WHERE 条件
1.比较大小
> SELECT SNO,SCORE
> FROM SC WHERE CNO='C1'
2.多重条件查询 优先级从高到低:NOT、AND、OR
例:查询工资在1000元~1500元之间的教师的教师号、姓名及职称。
SELECT TNO, TN, Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500
等价于
SELECT TNO, TN Prof
FROM T
WHERE Sal>=1000 AND Sal<=1500
3.确定集合
利用"IN"操作可以查询属性值属于指定集合的元组
【例】查询选修C1或C2的学生的学号、课程号和成绩。
SELECT SNO, CNo, Score
FROM SC
WHERE CNO IN('C1,C2)
此语句也可以使用逻辑运算符“OR”实现。
SELECT SNO, CNo, Sco
FROM SC
WHERE CNO='Cl' OR 'CNo' = C2
利用“NOTN”可以查询指定集合外的元组。
【例3-30】查询没有选修C1,也没有选修C2的学生的学号、课程号和成绩。
SELECT SNO, CNo, Score FROM SC
WHERE CNo NOT IN ('C1,'C2')
等价于
SELECT SNO, CNo, Score FROM-SC
WHERE (CNO <>'C1)AND (CNo<>'C2')
4.部分匹配查询
语法格式:
<属性名> LIKE <字符串常量>
1
2
| 通配符 | 实例 |
|---|---|
| % | ab%,’ab‘后可接任意字符串 |
| _(下划线) | ‘a_b’,'a’与’b’之间可有一个字符 |
| [ ] | [0-9],0~9之间的字符 |
| [^ ] | [^0-9],不在0~9之间的字符 |
【例3-31】查询所有姓张的教师的教师号和姓名。 查询结果如下
SELECT TNO, TN
FROM T
WHERE TN LIKE'张%'
【例3-32】查询姓名中第二个汉字是“力”的教师号和姓名。
SELECT TNO,TN
FROM T WHERE TN LIKE’_力%'
6.空值查询
某个字段没有值称为具有空值(NUL)通常没有为一个列输入值时,该列的值就是空值。
【例3—33】查询没有考试成绩的学生的学号和相应的课程号
SELECT SNO,CNO
FROM SC
WHERE SCORE IS NULL
1.3常用库函数及统计查询
| 函数 | 功能 |
|---|---|
| AVG | 按列计算平均值 |
| SUM | 按列计算值的总和 |
| MAX | 求一列中的最大值 |
| MIN | 求一列中的最小值 |
| COUNT | 按列值统计个数 |
【例】求学号为S1的学生的总分和平均分。
SELECT SUM(Score) As TotalScore, AVG(Score) As AvgScore
FROM SC
WHERE (SNO =S1)
上述查询语句中AS后面的 TotalScore和 AvgScore是别名,
别名会显示在查询结果中.
让使用实例者能清楚地知道查询内容所表示的含义，
结果中就不显示列名.
注意：在使用库函数进行查询时，通常要给查询的每一项内容加别名，
否则查询结果就不会显示列名.
【例3-35】求选修C1号课程的最高分、最低分及之间相差的分数。
SELECT MAX (Score) AS MaxScore,MIN (Score) AS MinScore,
MAX(Score)- MIN(Score) AS Diff
FROM SC
WHERE (CNO ='C1')
【例3-36】求计算机系学生的总数。
SELECT COUNT( SNO) FROM S
WHERE Dept='计算机'
【例3-37】求学校中共有多少个系
SELECT COUNT (DISTINCT Dept) As DeptNum
FROM S
//注加入关键字 DISTINCT后表示消去重复行,
//可计算字段“Dept”不同值的数目。
用 COUNT(*)来统计元组个数.
1.4分组查询
1.4.1 GROUP BY子句
GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的值。
【例3-40】查询每个教师的教师号及其任课的门数。
SELECT TNO, COUNT(*)AS C_Num
FROM TC
GROUP BY TNo
GROUP BY子句按TNo的值分组,
所有具有相同TNo的元组为一组,
对每一组使用函数COUNT进行计算,
统计出各位教师任课的门数。
1.4.2 HAVING子句
若在GROUP BY子句分组后还要按照一定的条件进行筛选,
则需使用 HAVING子句。
【例3-41】查询选修两门以上(含两门)课程的学生的学号和选课门数。
表的连接方法
SELECT SNO, COUNT(*) AS SC_Num
FROM SC
GROUP BY SNO
HAVING ( COUNT(*)>=2 )
GROUP BY子句按SNo的值分组,
所有具有相同SNo的元组为一组,
对每一组使用函数 COUNT进行计算,
统计出每个学生选课的门数。
HAVING子句去掉不满足 COUNT(*)>=2的组。
注意：
一、.当在一个SQL查询中同时使用 WHERE子句, GROUP BY子句和 HAVING子句时,其顺序是WHERE、 GROUP BY、HAVING。
二、WHERE与 HAVING子句的根本区别在于作用对象不同。
1.WHERE句作用于基本表或视图,从中选择满足条件的元组;
2.HAVING子句而作用于组,选择满足条件的组，必须用在 GROUP BY子句之后,但GROUP BY子句可没有 HAVING子句
1.5查询结果的排序
1.5.1 结果排序 ORDER BY子句
当需要对查询结果排序时,应该使用 ORDER BY子句, ORDER BY Y子句必须出现在其他子句之后。
排序方式可以指定,DESC为降序,ASC为升序,缺省时为序案犬认人
SQL查询语句大全
Select
Select+聚合函数
总数
count(*)表示计算总行数，括号中写星与列名，结果相同
例1.查询登录系统学总数
select count(*) from StudentBindPaperTypeEntity
最大值
max(列)求此列的最大值
例2.求下表的最大编号
select max(StudentID) from StudentBindPaperTypeEntity
最小值
min(列)求此列的最小值
例3.求下表编号最小编号
select min(StudentID) from StudentBindPaperTypeEntity
求和
sum(列)求此列之和(注:sum运算符与数字类型连用)
例4.查询当前在线的学生(IsUse=0表示未在线，1表示在线)
select SUM(IsUse) from StudentBindPaperTypeEntity
平均值
avg(列) 表示求此列的平均值(注：avg运算符与数字类型连用)
例5：查询学生编号的平均数
select avg(StudentID) from StudentBindPaperTypeEntity
Select+case…when…then语句
case…when…then语句，相当于编程语言中if判断
例1根据IsUser字段查询学生是否在线
select a.StudentID,
(case a.IsUse
when '0' then '未在线'
when '1' then '在线' else '未上传' end) as 在线情况
from StudentBindPaperTypeEntity as a
显示情况：
select+top
top：取表中前多少的数据
例1.取出表中第几行数据(如第一行)
select top 1 * from StudentBindPaperTypeEntity
例2.取出表中百分之多少数据
select top 50 percent * from StudentBindPaperTypeEntity
from(表)+连接查询
连接查询
1.内连接：Inner join
2.左连接：Left join
3.右连接：Right join
例子中涉及的表
StudentInfoEntity：全校学生的信息
ScoreEntity：学生考试的成绩(并不全包含全校学生)
from+inner join
例1.查出这两个表中共有的信息(as为表的别名，方便)
select score.studentID,score.score,s.CollegeID,s.major,s.majorClass
from ScoreEntity as score inner join StudentInfoEntity as s on score.studentID=s.studentID
where score.CollegeID=02
显示结果
from+left join
左外连接：左表的值会全部显示出来，右表的值显示on条件搜索的的结果，搜索不到为NULL
例1两个表作左外连接
select score.studentID,score.score,s.CollegeID,s.major,s.majorClass
from StudentInfoEntity as s left join ScoreEntity as score on s.studentID=score.studentID
显示结果：(个别)
from+right join
右外连接与左外连接相反(右表的值全部显示出来)
例1两个表做右外连接
select score.studentID,score.score,s.CollegeID,s.major,s.majorClass
from ScoreEntity as score right join StudentInfoEntity as s on s.studentID=score.studentID
现在两个表换了位置，结果是一样的
Where(条件语句查询)
比较运算符
例1.查询学号>18832650890的学生
select * from StudentBindPaperTypeEntity where StudentID>18832650890
例2.查询学号!=18832650890的学生(<>同效)
select * from StudentBindPaperTypeEntity where StudentID!=18832650890
模糊查询
like
%表示任意多个字符
例1.查询1月8号考试的学生
select * from StudentBindPaperTypeEntity where TimeTamp like '2020-01-08%'
例2.查询不是1月8号考试的学生
select * from StudentBindPaperTypeEntity where TimeTamp not like '2020-01-08%'
范围查询
in关键字为非连续查询
例1.查询两个不相邻的学号的学生
select * from StudentBindPaperTypeEntity where StudentID in('19100142001','19100142006')
Between…and…为连续查询(注：sql软件情况不一样，可能不包含and后的值)
例2.查询两个学号之间的学生
select * from StudentBindPaperTypeEntity where StudentID Between 19100142001 and 19100142006
空判断
is null判断为空
例1.查询没有试卷的学生
select * from StudentBindPaperTypeEntity where PaperType is null
is not null 判断非空
例2.查询有试卷的学生
select * from StudentBindPaperTypeEntity where PaperType is not null
优先级
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用
group by(分组)
作用：将字段间一对多的关系，向一的方向靠拢分组
例1.查出参加考试有几个学院
select CollegeID from StudentBindPaperTypeEntity group by CollegeID
显示结果：
group by+聚合函数
例2.查出各个学院参加考试的人数
select CollegeID, count(StudentID) from StudentBindPaperTypeEntity group by CollegeID
显示结果：
其实group by + 聚合函数是从group by + group_concat()演变过来的，SqlServer不支持这个函数
group by+having
having的作用跟where子句功能一样，只不过having只用在group by
例3.查出学院ID大于10的学院
select CollegeID from StudentBindPaperTypeEntity group by CollegeID having CollegeID>10
显示结果：
Order by(排序)
排序查询语法：
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
如果列1的值相同，则按照列2排序，以此类推
1.asc从小到大
2.desc从大到小
例1.根据学院分组ID降序(desc)
select CollegeID from StudentBindPaperTypeEntity group by CollegeID order by CollegeID desc
例2.将上表升序(asc)
select CollegeID from StudentBindPaperTypeEntity group by CollegeID order by CollegeID asc
