SQL语句笔记

SQL语句笔记(SQL语句不区分大小写)

一、数据库操作

1、创建数据库

CREATE DATABASE database-name(此语句的路径,初始大小,容量值及增长量皆为默认)

自己设定一些值"[]"中的语句可选用或者不用

  • 代码如下
CREATE DATABASE 数据库名称
[ON
[FILEGROUP 文件组名称]      //"[]"中内容均为可选设定,可不写,不写即为默认值。
(
NAME=数据库逻辑名称,
FILENAME=存储路径,
SIZE=数据初始大小,
MAXSIZE=最大容量值,
FILEGROWTH=自动增长容量值  
)                        //用"()"括起来的语句除最后一句,每一句用英文逗号隔开
]
[LOG ON
(
NAME=日志逻辑名称,
FILENAME=存储路径,
SIZE=日志文件初始大小,
MAXSIZE=日志文件最大容量值,
FILEGROWTH=日志文件自动增长容量值
)
]
[COLLATE 数据库校验方式] //可为Windows校验也可以是SQL校验
[FOR ATTACH]          //将已存在的数据库文件附加到新的数据库中

//"[]"中内容均为可选设定,可不写,不写即为默认值。

//用"()"括起来的语句除最后一句,每一句用英文逗号隔开

修改数据库

  • ADD FILE:向数据库中添加数据文件。

  • ADD LOG FILE:向数据库中添加日志文件。

  • REMOVE FILE:从数据库中删除逻辑文件,并删除物理文件。如果文件不为空,则无法删除。

  • MODIFY FILE:指定要修改的文件。

  • ADD FILEGROUP:向数据库中添加文件组。

  • REMOVE FILEGROUP:从数据库中删除文件组。若文件组非空,无法将其删除,需要先从文件组中删除所有文件。

  • MODIFY FILEGROUP:修改文件组名称、设置文件组的只读(READ_ONLY)或者读写(READ_WRITE)属性、指定文件组为默认文件组(DEFAULT)。

  • ALTER DATABASE命令可以在数据库中添加或删除文件和文件组、更改数据库属性或其文件和文件组、更改数据库排序规则和设置数据库选项。应注意的是,只有数据库管理员(DBA)或具有CREATE DATABASE权限的数据库所有者才有权执行此命令。

ALTER DATABASE 数据库名称
ADD FILE(具体文件格式)
[,…n]
[TO FILEGROUP 文件组名]
|ADD LOG FILE(具体文件格式)
[,…n]
|REMOVE FILE 文件逻辑名称
|MODIFY FILE(具体文件格式)
|ADD FILEGROUP 文件组名
|REMOVE FILEGROUP 文件组名
|MODIFY FILEGROUP 文件组名
{ READ_ONLY|READ_WRITE,
     | DEFAULT,
     | NAME = 新文件组名}
}

其中,“具体文件格式”为:

(   
	NAME = 文件逻辑名称
    [ , NEWNAME = 新文件逻辑名称]
    [ , SIZE = 初始文件大小] 
    [ , MAXSIZE = 文件最大容量] 
    [ , FILEGROWTH = 文件自动增长容量] 
) 

二、数据表

数据类型:

字符型
类型说明
CHAR(size)用于表示固定长度的字符串,该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 255 个字符,默认值为 1。
VARCHAR(size)用于表示可变长度的字符串,该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 65535 个字符。
TINYTEXT表示一个最大长度为 255(28-1)的字符串文本。
TEXT(size)表示一个最大长度为 65,535(216-1)的字符串文本,也即 64KB。
MEDIUMTEXT表示一个最大长度为 16,777,215(224-1)的字符串文本,也即 16MB。
LONGTEXT表示一个最大长度为 4,294,967,295(232-1)的字符串文本,也即 4GB。
ENUM(val1, val2, val3,…)字符串枚举类型,最多可以包含 65,535 个枚举值。插入的数据必须位于列表中,并且只能命中其中一个值;如果不在,将插入一个空值。
SET( val1,val2,val3,…)字符串集合类型,最多可以列出 64 个值。插入的数据可以命中其中的一个或者多个值,如果没有命中,将插入一个空值。

说明:ENUM 类型相当于单选题,SET 类型相当于多选题。

整型
类型大小(字节)有符号数取值范围无符号数取值范围说明
TINYINT1(-128, 127)(0, 255)超小整数
SMALLINT2(-32 768, 32 767)(0, 65 535)小整数
MEDIUMINT3(-8 388 608, 8 388 607)(0, 16 777 215)中等整数
INT 或 INTEGER4(-2 147 483 648, 2 147 483 647)(0, 4 294 967 295)整数
BIGINT8(-263, 263-1)(0, 264-1)大整数
BOOL布尔类型,只有 true 和 false 两个有效值;零值被认为是 false,非零值被认为是 true。 注意,MySQL 并不真正支持 BOOL 类型,BOOL 是 TINYINT(1) 的别名。
浮点型
类型分类说明
FLOAT(size, d)浮点数(近似值)单精度浮点数类型,4 个字节大小。size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。
FLOAT§单精度浮点数类型,参数 p 用来决定使用 FLOAT 类型还是 DOUBLE 类型:如果 p 的取值介于 0 和 24 之间,那么数据类型将变成 FLOAT();如果 p 的取值介于 25 和 53 之间,那么数据类型将变成 DOUBLE()。
DOUBLE(size, d)双精度浮点数类型,size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。
DECIMAL(size, d)定点数(精确值)定点数类型,size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。size 的最大值是 65,默认值是 10;d 的最大取值是 30,默认值是 0。
DEC(size, d)等价于 DECIMAL(size, d)。
日期
类型说明
DATE日期类型,格式为 YYYY-MM-DD,取值范围从 ‘1000-01-01’ 到 ‘9999-12-31’。
DATETIME(fsp)日期和时间类型,格式为 YYYY-MM-DD hh:mm:ss,取值范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
TIMESTAMP(fsp)时间戳类型,它存储的值为从 Unix 纪元(‘1970-01-01 00:00:00’ UTC)到现在的秒数。TIMESTAMP 的格式为 YYYY-MM-DD hh:mm:ss,取值范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC。
TIME(fsp)时间类型,格式为 hh:mm:ss,取值范围从 ‘-838:59:59’ 到 ‘838:59:59’。
YEAR四位数字的年份格式,允许使用从 1901 到 2155 之间的四位数字的年份。此外,还有一个特殊的取值,就是 0000。
二进制
类型说明
BIT(size)二进制位(Bit)类型,位数由 size 参数指定;size 的大小从 1 到 64,默认值为 1。
BINARY(Size)等价于 CHAR() 类型,但是存储的是二进制形式的字节串。size 参数以字节(Byte)为单位指定列的长度,默认值为1。
VARBINARY(Size)等价于 VARCHAR() 类型,但是存储的是二进制形式的字节串。size 参数以字节(Byte)为单位指定列的最大长度。
TINYBLOB存储较小的二进制数据,最多可容纳 255 (28-1)个字节。
BLOB(size)用来储存二进制数据,最多可以容纳 65,535(216-1)个字节,也即 64KB。
MEDIUMBLOB存储中等大小的二进制数据,最多可以容纳 16,777,215(224-1)字节,也即 16MB。
LONGBLOB存储较大的二进制数据,最多可容纳 42,94,967,295(232-1)字节,也即 4GB。

1、创建数据表

CREATE TABLE 表名(col1 type1 [not null][primary key],col2 type2 [not null],…)

NULL/NOTNULL 约束
  • 括号里分别是列的属性名,数据类型,和约束,当为NULL时表示不知道,对于主键列,不允许出现空值,否则主键列失去唯一标识性。因此加了约束
唯一约束 UNIQUE

​ 用来指明基本表在某一列或多个列的组合列上的取值必须唯一(例如确保同一性别的同学没用重名)

  • 用于列约束格式为 [CONSTRAINT <约束名>] UNIQUE
  • 用于表约束 [CONSTRAINT <约束名>] UNIQUE(列名,[列名])
主键约束 PRIMARY KEY

​ 用于定义基本表的主键,起唯一标识作用

PRIMARY KEY与UNIQUE 的区别:

  1. 在一个基本表中只能定义一个PRIMARY KEY约束,但可定义多个UNIQUE约束。

  2. 对于指定为PRIMARY KEY的一个列或多个列的组合,其中任何一个列都不能出现NULL值,而对于UNIQUE所约束的唯一键,则允许为NULL

  3. 不能为同一个列或一组列,既定义UNIQUE约束,又定义PRIMARY KEY约束。

  • 用于定义列约束 CONSTRAINT <约束名> PRIMARY KEY
  • 用于定义表约束 [CONSTRAINT <约束名>] PRIMARY KEY (<列名>[{,<列名>}])
外键约束 FOREIGN KEY 约束

​ 外键约束指定某一列或几列作为外部键。其中包含外部键的表称为从表,包含外部键所引用的主键或唯一键的表称为主表。

  • [CONSTRAINT<约束名>] FOREIGN KEY REFERENCES <主表名> (<列名>[{,<列名>}])
CHECK 约束

**CHECK约束用来检查字段值所允许的范围 **

​ 在建立CHECK约束时,需要考虑以下几个因素:一个表中可以定义多个CHECK约束,每个字段只能定义一个CHECK约束,在多个字段上定义的CHECK约束必须为表约束,当执行INSERT、UNDATE语句时,CHECK约束将验证数据。

  • CHECK既可用于列约束,也可用于表约束。

    [CONSTRAINT <约束名>] CHECK (<条件>)

初始化表 table1

TRUNCATE TABLE table1

列出数据库里所有的表名

SELECT NAME FROM SYSOBJECTS WHERW TYPE=‘U’ //U代表用户

2、删除新表

DROP TABLE 表名

只能删除自己建立的表,不能删除其他用户所建的表

3、增加/修改一个列

Alter table tabname add column col type

注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

  • ADD方式用于增加新列和完整性约束
ALTER TABLE <表名>
ADD <列定义> | <完整性约束定义>
  • ALTER方式用于修改某些列
ALTER TABLE <表名>
ALTER COLUMN <列名> <数据类型> [NULL | NOT NULL] 
  • DROP方式只用于删除完整性约束
ALTER TABLE<表名>
DROP CONSTRAINT <约束名> 
列出表里的所有的列名

select name from syscolumns whereid=object_id(‘表名’)

7、主键

添加主键: Alter table tabname add primarykey(col)

删除主键: Alter table tabname drop primary key(col)

8、索引

创建索引:create[unique] index idxname on tabname(col….)

删除索引:drop index idxname

注:索引是不可更改的,想更改必须删除重新建。

9、 视图

创建视图:create view viewname as select statement

删除视图:drop view viewname

10、 简单的基本sql语句

选择:select * fromtable1 where 范围

插入:insert intotable1(field1,field2) values(value1,value2)

删除:delete from table1where 范围

更新:update table1 set field1=value1 where 范围

查找:select* from table1 where field1 like ’%value1%’ —like的语法很精妙,查资料!

排序:select * from table1 order by field1,field2 [desc]

总数:selectcount as totalcount from table1

求和:select sum(field1) as sumvalue from table1

平均:selectavg(field1) as avgvalue from table1

最大:select max(field1) as maxvalue from table1

最小:selectmin(field1) as minvalue from table1

11、 高级查询运算词

A: UNION 运算符

UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B: EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。

C: INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECTALL),不消除重复行。

注:使用运算词的几个查询结果行必须是一致的。

12、分组

Groupby:

一张表,一旦分组 完成后,查询后只能得到组相关的信息。

组相关的信息:(统计信息) count,sum,max,min,avg 分组的标准)

在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据

在selecte统计函数中的字段,不能和普通的字段放在一起;

三、查询

3.1单表查询

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]	//投影	
〈列名〉[AS 别名1] [{,〈列名〉[ AS 别名2]}]
FROM〈表名〉[[AS] 表别名]
[WHERE〈检索条件〉]									//选取
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]]
3.1.1无条件查询
  • 无条件查询是指只包含“SELECT…FROM”的查询,相当于只对关系(表)进行投影操作。不进行选取。
3.1.2条件查询

当要在表中找出满足某些条件的行时,则需使用 “ WHERE ” 子句指定查询条件。

  • 常用比较运算符
运算符含义
=, >, <, >=, <=, != ,< >比较大小
AND, OR, NOT多重条件
BETWEEN AND确定范围
IN确定集合
LIKE字符匹配
IS NULL空值
3.1.3多重条件查询

WHERE子句需要指定一个以上的查询条件时,则需要使用逻辑运算符ANDORNOT

优先级:()>NOT>AND>OR

3.1.4确定集合
  • 利用“IN”操作可以查询属性值属于指定集合的元组。利用“NOT IN”可以查询指定集合外的元组。
WHERE 表名 IN('C1’, 'C2’)

WHERE 表名 NOT IN('C1''C2') 
3.1.5模糊查询
  • 当不知道完全精确的值时,用户可以使用LIKE或NOT LIKE进行部分匹配查询(也称模糊查询)
<属性名> LIKE <字符串常量> //属性名为字符型,字符串常量为通配符
通配符功能实例
%代表0个或多个字符‘ab%’,ab后可接任意字符串
_(下划线)代表一个字符‘a_b’,‘a’和‘b’之间有一个字符
[ ]表示在某一范围的字符[0-9],0~9之间的字符
[ ^ ]表示不在某一范围的字符[ ^ 0-9],不在0~9之间的字符
3.1.6空值查询
  • 某个字段没有值称为具有空值(NULL)

  • 空值不同于零和空格,它不占任何存储空间

例:查询属性名3没有值的相应的属性名1和属性名2

SELECT 属性名1,属性名2
	FROM 表名
	WHERE 属性名3 IS NULL
常用库函数
  • 使用库函数进行查询时,通常要给查询的每一项内容加上别名,否则查询结果中就不显示列名
函数名称功 能
AVG按列计算平均值
SUM按列计算值的总和
MAX求一列中的最大值
MIN求一列中的最小值
COUNT按列值计个数

求选修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
FROM S
3.1.7分组查询 GROUO BY
  • GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的值。
GROUP BY 查询结果
  • 若在分组后还要按照一定的条件进行筛选,则需使用HAVING子句
GROUP BY 查询结构
HAVING (筛选条件)
3.1.8查询结果的排序
  • 当需要对查询结果排序时,应该使用ORDER BY子句,ORDER BY子句必须出现在其他子句之后。排序方式可以指定,DESC为降序,ASC为升序,缺省时为升序。

按成绩降序排列

ORDER BY Score DESC

3.2多表的连接查询

  • ···表之间满足一定条件的行进行连接时,FROM子句指明进行连接的表名,WHERE子句指明连接的列名及其连接条件。
  • ···利用关键字JOIN进行连接:当将JOIN 关键词放于FROM子句中时,应有关键词ON与之对应,以表明连接的条件。
SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
〈列名〉[AS 别名1] [{,〈列名〉[ AS 别名2]}]
FROM〈表名1[[AS]1别名] [{,〈表名2[[AS]2别名]}]
[WHERE〈检索条件〉]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]]
3.2.1JION的分类
INNER JOIN显示符合条件的记录,此为默认值
LEFT(OUTER)JOIN为左(外)连接,用于显示符合条件的数据行以及左边表中不符合条件的数据行,此时右边数据行会以NULL来显示
RIGHT(OUTER)JOIN右(外)连接,用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示
FULL(OUTER)JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示
CROSS JOIN将一个表的每一个记录和另一表的每个记录匹配成新的数据行
SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
列名1 [AS 别名1][ ,〈列名2[ AS 别名2]]
FROM〈表名1[[AS]1别名]			
[INNER/RIGHT/FULL/OUTER/CROSS] JOIN 
表名2 [[AS]2别名]
ON 条件
3.2.2内连接查询
等值连接
  • 连接条件的一般格式为:

    [<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>

    其中的比较运算符主要有:=、>、<、>=、<=、!=。

    当比较运算符为“=”时,称为等值连接,其他情况为非等值连接

3.2.3外连接查询

​ 在内连接操作中,不满足连接条件的元组不能作为查询结果输出。在外部连接中,参与连接的表有主从之分,以主表的每行数据去匹配从表的数据列。

​ 符合连接条件的数据将直接返回到结果集中,对那些不符合连接条件的列,将被填上NULL值后再返回到结果集中(对BIT类型的列,由于BIT数据类型不允许有NULL值,因此将会被填上0值再返回到结果中)。

  • 外部连接分为左外部连接和右外部连接两种。以主表所在的方向区分外部连接,主表在左边,则称为左外部连接;主表在右边,则称为右外部连接。
使用外连接

A、left (outer) join:

左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B:right (outer) join:

右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。

C:full/cross (outer) join:

全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

3.2.4交叉查询 CROSS JOIN

交叉查询(CROSS JOIN)对连接查询的表没有特殊的要求,任何表都可以进行交叉查询操作。

3.2.5自连接查询
  • 当一个表与其自身进行连接时,称为表的自身连接;
  • 需要给表起别名加以区别;
  • 由于所有属性名都是同名属性,因此必须使用表别名前缀。

3.3子查询(嵌套查询)

​ 一个SELECT-FROM-WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为嵌套查询。

  • SQL语言允许多层嵌套查询。层层嵌套方式反映了SQL语言的结构化。
  • ==子查询的限制:不能使用ORDER BY子句。==有些嵌套查询可以用连接查询替代
3.3.1普通子查询(子查询的查询条件不依赖父查询)
  • 引出子查询的谓词

    • 带有比较运算符的子查询 返回单值

    • 带有ANY或ALL谓词的子查询 返回一组值

    • 带有IN谓词的子查询 返回一组值

    • 带有EXISTS谓词的子查询

带有ANYALL谓词的子查询

​ 如果子查询的返回值不止一个,而是一个集合时,则不能直接使用比较运算符,可以在比较运算符和子查询之间插入ANY或ALL。

ANY 任意一个值

ALL 所有值

  • 配合使用比较运算符

ANY

>ANY大于子查询结果中的任意一个值(最小值)
<ANY小于子查询结果中的任意一个值(最大值)
>=ANY大于等于子查询结果中的任意一个值
<=ANY小于等于子查询结果中的任意一个值
=ANY等于子查询结果中的任意一个值
<>ANY不等于子查询结果中的某一个值

ALL

>ALL大于子查询结果中的所有值(最大值)
<ALL小于子查询结果中的所有值(最小值)
>=ALL大于等于子查询结果中的所有值
<=ALL小于等于子查询结果中的所有值
=ALL等于子查询结果中的所有值
<>ALL不等于子查询结果中的任何一个值
带有IN谓词的子查询
  • 使用 IN 代替 “=ANY”
3.3.2相关子查询(子查询的查询条件依赖父查询)

​ 相关子查询的执行顺序是:首先选取父查询表中的第一行记录,内部的子查询利用此行中相关的属性值进行查询,然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件,则把该行放入父查询的查询结果集合中。重复执行这一过程,直到处理完父查询表中的每一行数据。

带有EXISTS谓词的子查询
  • 带有EXISTS的子查询不返回任何实际数据,它只得到逻辑值“真”或“假” 。

  • 当子查询的的查询结果集合为非空时,外层的WHERE子句返回真值,否则返回假值。

  • NOT EXISTS与此相反。

  • 由EXISTS引出的子查询,其目标列表达式通常都用“*”,因为带有EXISTS的子查询只返回真值或假值,给出列名无实际意义。

3.3.3集合运算查询

  • 合并查询就是使用UNION 操作符将来自不同查询的数据组合起来,形成一个具有综合信息的查询结果,UNION操作会自动将重复的数据行剔除。

  • 参加合并查询的各子查询的使用的表结构应该相同,即各子查询中的数据数目和对应的数据类型都必须相同。

存储查询结果到表中
  • 使用SELECT…INTO 语句可以将查询结果存储到一个新建的数据库表或临时表中 。

四、数据表的操纵

添加数据

(1)添加一行新记录
INSERT INTO <表名> [(<列名1>[,<列名2>])] VALUES (<>)
  • 列名是可选项。列名的排列顺序不一定要和表定义时的顺序一致,但当指定列名时,VALUES子句中值的排列顺序必须和列名表中的列名排列顺序一致,个数相等,数据类型一一对应。

  • <表名>是指要添加新记录的表

  • <列名>是可选项,指定待添加数据的列

  • VALUES子句指定待添加数据的具体值。

  • 必须用逗号将各个数据分开,字符型数据要用单引号括起来。

  • 如果INTO子句中没有指定列名,则新添加的记录必须在每个属性列上均有值,且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。

(2)添加一行记录的部分数据值

​ 将VALUES子句中的值按照INTO子句中指定列名的顺序添加到表中,对于INTO子句中没有出现的列,则新添加的记录在这些列上将赋NULL值,如上例的Age即赋NULL值。但在表定义时有NOT NULL约束的属性列不能取NULL值,插入时必须赋值。

添加一行记录

INSERT INTO <表名> [(<列名1>[,<列名2>])] VALUES (<>)
(3)添加多行记录
  • 将一个表中的数据抽取数行添加到另一个表中,可以通过子查询来实现。
INSERT INTO <表名> [(<列名1>[,<列名2>])]
子查询 

修改数据 UPDATE

SQL 命令修改数据

修改数据与修改基本表的区别

  • updatealter
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>][WHERE <条件>] 
  • 有WHERE条件,修改符合要求的对应字段。

  • 无WHERE条件,修改表中所有对应的字段。

删除数据 DELETE

删除数据与删除基本表的区别

  • deletedrop
DELETE 
FROM<表名>
[WHERE <条件>]
  • 有WHERE条件,删除满足条件的记录
  • 无WHERE条件,系统就会自动删除该表中所有的记录

五、视图

​ 视图是从一个或几个基本表(或视图)导出的表,它与基本表不同,是一个虚表。同基本表一样,视图包含一系列带有名称的列和行数据。

  • 数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。

  • 基表中的数据发生变化,从视图中查询出的数据也随之改变。

创建视图

SQL 命令创建视图

CREATE VIEW  view_name  [ (column[, ...n]) ] 
AS select_statement 
[ WITH CHECK OPTION ][;]
<view_attribute>::=
{
	[ENCRYPTION]
	[SCHEMBINDING]
	[VIEW_METADATA] 
}

( 1 ) view_name:视图的名称,必须符合SQL Server 的标识符命名规则。
( 2 ) column:视图的列名称。仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有相同的名称(通常是由于联接的原因);视图中的某个列的指定名称不同于其派生来源列的名称。
( 3 ) select_statement:定义视图的SEIFCT语句。该语句可以使用多个表和其他视图。

( 4)CHECK OPTION:设置针对视图的所有数据修改语句都必须符合select_statement 中规定的条件。

( 5)ENCRYPTION:视图是加密的,如果加上这个选项,则无法修改视图。因此,创建视图时需要将脚本保存,否则再也不能修改了。

( 6 ) SCHEMABINDING:和底层引用的表进行定义绑定。加上这个选项的话,则视图引用的表不能随便更改构架(例如列的数据类型),如果需要更改底层表构架,则先 DROP或者ALTER在底层表之上绑定的视图。SCHEMABINDIN常用于定义索引视图。

( 7 )VIEW_METADATA:不设置该选项,返回给客户端的metadata是视图所引用表的metadatao设置了该选项,则返回视图自身的 metadata。通俗点说,VIEW_METADATA可以让视图看起来貌似表一样,视图的每一列的定义直接告诉客户端,而不是所引用的底层表列的定义。

修改视图

SQL命令修改视图

ALTER VIEW <视图名>[(<视图列表>)]
AS <子查询>

删除视图

SQL命令删除视图

DROP VIEW <视图名>
  • 该语句从数据字典中删除指定的视图定义。

  • 视图删除后,由该视图导出的其他视图定义仍在数据字典中,但已不能使用,必须使用drop view语句删除。

  • 删除基表后,由该基表导出的所有视图定义都必须使用drop view语句删除。

查询视图

  • DBMS内部执行对视图的查询时采用的的方法:视图消解

视图消解是指将用户执行的对视图的查询,在DBMS内部转换成对导出视图的基本表的查询。

  • DBMS先进行有效性检查,检查查询的表、视图等是否存在。

  • 如果存在,则从数据字典中取出视图的定义,把视图定义中的子查询与用户的查询结合起来,转换成等价的对基本表的查询。

  • DBMS最终执行转换后的查询。

更新视图

  • 更新视图是指通过视图来添加、修改和删除数据。
  • 由于视图是一张虚表,所以对视图的更新,最终转换成对基本表的更新。
  • 像查询视图那样,对视图的更新操作也是通过视图消解,转换为对基本表的更新操作一样 。

添加 INSERT

修改 UPDATE

删除 DELETE

六、索引

  • 索引是一种可以加快检索的数据库结构。

  • 通过创建良好的索引,可以显著提高数据库查询和应用程序的性能。

  • 索引一旦创建,将由DBMS自动管理和维护。当操作数据时,DBMS会自动更新索引。

要避免在一个表中创建大量的索引,否则会影响数据操作的性能,降低系统的响应速度。

索引类型

聚集索引
  • 在聚集索引中,表中行的物理存储顺序与索引键的逻辑顺序相同。 由于真正的物理存储只有一个,因此,一个表只能包含一个聚集索引。
非聚集索引
  • 非聚集索引与聚集索引具有相似的索引结构。

不同的是,非聚集索引不影响数据行的物理存储顺序。

唯一索引
  • 唯一索引能够保证索引键中不包含重复的值,从而使表中的每一行在某种方式上具有唯一性。
视图索引
  • 对视图而言,查询动态生成的结果集开销很大,特别是涉及对大量行进行复杂处理的视图。

如果在查询中频繁地引用这类视图,可通过对视图创建唯一聚集索引来提升性能,这类索引称为视图索引

全文索引
  • 全文索引是目前搜索引擎的关键技术之一。试想在1兆大小的文件中搜索一个词,可能需要几秒,在更大的文件中搜索开销会更大。为加快此类检索速度,出现了全文。索引技术,也称倒排文档技术。
XML索引
  • 可以对xml数据类型列创建XML索引。XML索引对列中xml实例的所有标记、值和路径进行索引,提 高查询性能。

创建索引

CREATE [ UNIQUE ][ CLUSTERED | NONCLUSTERED]  INDEX  index_name 
ON table_or_view_name  ( column_name [ ASC | DESC ] [ ,…n] )
[ WITH  <index_option>  [, ...n] ]
[ ON  { filegroup_name | "default” } ]

修改索引

ALTER INDEX { index_name | ALL }
   ON table_or_view_name
   { REBUILD 
        [ [PARTITION = ALL]
            [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
            | [ PARTITION = partition_number 
                    [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
               ]   ]  ]
    | DISABLE
    | REORGANIZE 
         [ PARTITION = partition_number ]
         [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }[ ; ]

删除索引

DROP INDEX <table or view name>.<index name>
DROP INDEX <index name> ON <table or view name>

查看索引

Sp_helpindex [@objname =] 'name' 		//[@objname =] 'name'表的名称
  • 如果要更改索引名称,可利用Sp_rename存储过程更改,其语法如下:
    • Sp_rename ‘数据表名.原索引名’, ‘新索引名’

七、TIP小技巧sql

1、复制表

(只复制结构,源表名:a 新表名:b) (Access可用)

法一:select * into bfrom a where 1<>1(仅用于SQlServer)

法二:select top 0 * intob from a

2、拷贝表

(拷贝数据,源表名:a 目标表名:b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、跨数据库之间表的拷贝

(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from bin ‘具体数据库’ where 条件

例子:…from b in’"&Server.MapPath(".")&"\data.mdb"&"’ where…

4、子查询(表名1:a 表名2:b)

select a,b,c from a where a IN (select dfrom b ) 或者: select a,b,c from a wherea IN (1,2,3)

5、显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from tablea,(select max(adddate) adddate from table where table.title=a.title) b

6、外连接查询(表名1:a 表名2:b)

select a.a, a.b, a.c, b.c, b.d, b.f from aLEFT OUT JOIN b ON a.a = b.c

7、在线视图查询(表名1:a )

select * from (SELECT a,b,c FROM a) T wheret.a > 1;

8、between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、两张关联表,删除主表中已经在副表中没有的信息

delete from table1 where not exists (select * from table2 where table1.field1=table2.field1 )

11、数据库分页

一条sql 语句搞定数据库分页:select top 10 b.*from (select top 20 主键字段,排序字段 from 表名 orderby 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

具体实现:

关于数据库分页:

declare @start int,@end int

@sql nvarchar(600)

set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’

exec sp_executesql @sql

注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)

12、选择在每一组b值相同的数据中对应的a最大的记录的所有信息

(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablenametb where tb.b=ta.b)

13、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a fromtableB) except (select a from tableC)

14、随机选择记录

select newid()

15、删除重复记录

1),delete fromtablename where id not in (select max(id) from tablename group bycol1,col2,…)

2),select distinct *into temp from tablename

delete from tablename

insert into tablename select* from temp

评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作

3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段

alter table tablename

–添加一个自增列

add column_b int identity(1,1)

delete from tablename where column_b not in(

select max(column_b) from tablename group by column1,column2,…)

alter table tablename drop column column_b

八、练习题

5张选课教师、学生、关系表

在这里插入图片描述

在这里插入图片描述

单表查询

练习题3.1.1
  1. 查询教师的全部信息
SELECT *
FROM T
  1. 查询全体教师的姓名教师号和系别

    SELECT TN,Tno,Dept
    FROM T
    
练习题3.1.2
  • 查询年龄大于18岁的学生的学号、姓名和系别。
SELECT SNo,SN,Dept
FROM S
WHERE Age>18
练习题3.1.3
  • 查询所属院系是信息系且工资大于等于1000的教师的教师号、姓名和职称。
SELECT TNo,TN,Prof
FROM T
WHERE Dept='信息'
AND Sal>1000
练习题3.1.5
  • 1、查询所有姓吴的学生的学号和姓名
SELECT SNo,SN
FROMS
WHERE SN LIKE '吴%'
  • 2、查询工资大于1000且年龄大于30岁的教师的教师号、姓名、系别
SELECT TNo,TN,Dept
FROM T
WHERE Sal>1000 AND Age>30
练习 题常用库函数
  • 1、统计选修C1课程的学生总数o
SELECT COUNT(SNo)AS总人数
FROM SC 
WHERE CNo='C1'
练习题3.1.7
  • 查询选修三门以上(含三门)课程的学生的学号和成绩平均分
SELECT SNo,AVG(Scpre)
AS 平均分
FROM SC
GROUP BY SNo HAVING
COUNT(*)>=3
练习题3.1.8
  • 查询选修C2课程的学号、课程号和成绩。查询结果按成绩升序排列,成绩相同再按学号降序排列。
SELECT SNo,Score
FROM SC
WHERE CNo='C2'
ORDER BY Score,SNo
DESC

在这里插入图片描述

在这里插入图片描述

多表查询

练习题 3.2.2
  • 查询“赵亦”同学所选修的课程,要求列出学号、姓名、课程号
//方法1
SELECT S.SNo,SN,CNo
FROM S,SC
WHERE(S.SNo=SC.SNo)
AND(SN='赵亦')

//方法2
SELECT S.SNo,SN,CNo
FROM S INNER JOIN SC
ON S.SNo=SC.SNo
WHERE(SN='亦'
练习题 3.2.3
  • 查询所有老师的授课信息,列出教师号、姓名、课程号及课程名。(没有授课的教师的授课信息显示为空)
SELECT T.TNo,TN,CNo,CN
FROM T
LEFT OUTER JOIN TC
ON T.TNo=TC.TNo
LEFT OUTER JOIN C
ON T.CNo=TC.CNo
练习题 3.2.4
  • 检索所有老师的姓名、年龄以及授课名称
//方法1
SELECT TN,Age,CN
FROM T,C,TC
WHERE T.TNo=TC.TNo
AND TC.CNo=C.CNo

//方法2
SELECT R3.TN,R3.Age,R4.CN
FROM
(SELECT CNo,CNo FROM T)AS R3
INNER JOIN
(SELECT R2.TNo,R1.CN)
FROM SELECT CNo,CN FROM C) AS R1
INNER JOIN
(SELECT TNo,CNo FROM TC) AS R2
ON R1.CNo=R2.CNo) AS R4
ON R3.TNo=R4.TNo

//方法3
SELECT R3.TN,R3.Age,R1.CN
FROM (SELECT TNo,TN,Age FROM T) AS R3,(SELECT CNo,CN FROM C) AS R1,(SELECT TNo,CNo FROM TC) AS R2 
WHERE R1.CNo=R2.CNo AND R3.TNo=R2.TNo

子查询

练习题 3.3.1

1、查询不是计算机系的选修C1课程的学生的姓名和年龄。(使用子查询方式)

SELECT SN,AGE
FROM S 
WHERE SNO=ANY
(SELECT SNO
FROM SC
WHERE CNO=‘C1’)
AND DEPT<>’计算机’

2、查询比计算机系某一同学年龄小的学生的姓名和年龄

//方法1
SELECT SN,AGE
FROM S
WHERE AGE<ANY
(SELECT AGE
FROM S
WHERE DEPT=‘计算机’)
  
  
//方法2
SELECT SN,AGE
FROM S
WHERE AGE<
(SELECT MAX(AGE)
FROM S
WHERE DEPT=‘计算机’)
练习题 3.3.2

​ 查询选修C2课程的学生姓名

SELECT SN
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE CNO=‘C2’AND S.SNO=SC.SNO)
  • 查询选修C2课程的学生的学号、姓名(使用连接查询、普通子查询、使用exists关键字的相关子查询)
//连接查询:
SELECT S.SNo,SN 
FROM S,SC
WHERE S.SNo=SC.SNo AND CNo='C2'

//普通子查询:
SELECT SNo,SN 
FROM S
WHERE SNo=ANY(
    SELECT SNo FROM SC
    WHERE CNo='C2')

//相关子查询:
SELECT SNOSN FROMS
WHERE EXISTS(
    SELECT* FROM SC 
    WHERE CNo='C2'AND S.SNo=SC.SNo)

视图

  • 创建一个计算机系学生的视图,该视图的属性列由学号、姓名、课程号和任课教师号组成。
CREATE VIEW JSJ_S
AS
SELECT S.SNO,SN,SC.CNO,TNO
FROM S,SC,TC
WHERE S.SNO=SC.SNO AND SC.CNO=TC.CNO
AND DEPT=‘计算机’
  • 5
    点赞
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值