sql语言

sql语言

sql对大小写不敏感！！！

DDL - 数据定义语言

	创建	删除	修改
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index

table

create

create table <表名> （
<列名> <数据类型>[ <列级完整性约束条件> ] 
[，<列名> <数据类型>[ <列级完整性约束条件>] ] 
…… [，<表级完整性约束条件> ] 
）；

create table S
(Sno CHAR(5) NOT NULL UNIQUE，
Sname CHAR(20) UNIQUE，
Ssex CHAR(1) ，
Sage INT，
Sdept CHAR(15)
)；

常用基本类型

类型	含义
char(n)	存放固定长度的字符串，用户指定长度为n。如果没有使用n个长度则会在末尾添加空格。
varchar(n)	可变长度的字符串，用户指定最大长度n。char的改进版，大多数情况下我们最好使用varchar。
int	整数类型
smallint	小整数类型
numeric(p,d)	定点数，精度由用户指定。这个数有p位数字（包括一个符号位）d位在小数点右边。
real ,double precision	浮点数和双精度浮点数。
float(n)	精度至少位n位的浮点数

完整性约束

类型	含义	示例
primary key	主码约束,后面括号中是作为主码的属性	primary key（student_id）
foreign key references	参照完整性约束，括号中为外码，references后为外码的表	foreign key(course_id) references Course
not null	非空值约束，约束前面为属性的定义	name varchar(10) not null
unique	唯一性约束，约束数据库表中的每条记录不能重复 primary key = unique + not null	unique(student_id)
default	缺省值，关键字后面的为该属性的默认值	SEX char(2) default’男’

alter

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

子句	含义
add	增加新列和新的完整性约束条件
drop	删除指定的列或完整性约束条件
modify（rename/change）	用于修改列名和数据类型

向学生表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE S ADD Scome DATE；
• 不论基本表中原来是否已有数据，新增加的列一律为空值。

 删除属性列
例：删除学生表中“入学时间”属性列。
ALTER TABLE S Drop Scome；

 删除属性列
例：删除学生表中“入学时间”属性列。
ALTER TABLE S Drop Scome；

 删除学生姓名必须取唯一值的约束。
ALTER TABLE S DROP UNIQUE(Sname)；

drop

删除基本表
DROP TABLE <表名> [ RESTRICT | CASCADE]; 
• 删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述，数据、表上的索引都删除；表上的视图往往仍然保留，但无法引用

选项	含义
restrict	如果有视图或者约束条件涉及要删除的表时，就禁止dbms执行该命令
cascade	将该表与其涉及的对象一起删除

index

create

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；
– 用<表名>指定要建索引的基本表名字
– 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
– 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
– UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
– CLUSTER表示要建立的索引是聚集索引

学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。
CREATE UNIQUE INDEX Stusno ON S (Sno)；
CREATE UNIQUE INDEX Coucno ON C (Cno)；
CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；

• 聚集索引

  –聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有一个聚集索引。

  –只有当表包含聚集索引时，表中的数据行才按排序顺序存储。

  –如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

• 唯一值索引

  – 对于已含重复值的属性列不能建UNIQUE索引

  – 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。

• 聚簇索引

  – 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致

  – 在一个基本表上最多只能建立一个聚簇索引

  –聚簇索引的用途：对于某些类型的查询，可以提高查询效率

  –聚簇索引的适用范围

  • 很少对基表进行增删操作

  • 很少对其中的变长列进行修改操作

drop

ROP INDEX <索引名>；
– 删除索引时，系统会从数据字典中删去有关该索引的描述。

删除S表的Stusname索引。
DROP INDEX Stusname；

索引的使用技巧

• 小表不需要索引

• 数据列中有较多不相同数据时可使用索引

• 查询要返回的数据很少时可用索引

• 需要经常更新数据时不宜用索引

• 聚集索引会引起数据表中数据的排序

DQL - 数据查询语言

select

SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1>
[ HAVING <条件表达式>]]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

SELECT子句的<目标列表达式>为表达式
–算术表达式
–字符串常量
–函数
–列别名等
例：查询学生生日
SELECT Sname, 2019-Sage as BirthYear
FROM S；

关键词	含义
select	表示要查找出的表所含有的属性
all	在select后加入关键字all表示不去重（默认）
distinct	在select后加入关键字distinct表示将结果去重（后面的所有属性列）
from	表示要操作的表
where	判断条件，根据该判断条件选择信息
group by	将在group by上取值相同的信息分在一个组里
having	对group by产生的分组进行筛选，可以使用聚集函数
order by	让查询结果中的信息按照给定的属性排序（默认asc，asc升序，desc降序）
as	将as前的关系起一个别名，在此语句中，可以用别名来代指这个表或属性
*	在select中通过: “表名.*” 来表示查找出这个表中所有的属性
union/union all	将两个SQL语句做并运算，并且自动去重，添加all表示不去重
intersect/intersect all	将两个SQL语句做交运算，并且自动去重，添加all表示不去重
except/except all	将两个SQL语句做差运算，并且自动去重，添加all表示不去重

集函数

类型	含义
avg	平均值
min	最小值
max	最大值
sum	总和
count	计数

查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC；
注：用DISTINCT以避免重复计算学生人数

计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 '；

使用GROUP BY子句分组 细化集函数的作用对象 
– 未对查询结果分组，集函数将作用于整个查询结果 
– 对查询结果分组后，集函数将分别作用于每个组
• GROUP BY子句的作用对象是查询的中间结果表 
• 分组方法：按指定的一列或多列值分组，值相等的为一组 
• 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分 组属性和集函数

使用HAVING短语筛选最终输出结果
只有满足HAVING短语指定条件的组才输出 
• HAVING短语与WHERE子句的区别：作用对象不同 
–WHERE子句作用于基表或视图，从中选择满足条件的元组。 
–HAVING短语作用于组，从中选择满足条件的组。

查询有3门以上课程是90分以上的学
生的学号及（90分以上的）课程数
SELECT Sno, COUNT(*)
FROM SC
WHERE Grade>=90
GROUP BY Sno
HAVING COUNT(*)>=3;

where子句查询条件

查询条件 		谓词
比较 			 =,<>,>,>=,<,<=
算术运算 		+ - * /
确定范围 		Between And ,Not Between And
确定集合 		IN , NOT IN
空值 			 IS NULL ,IS NOT NULL
多重条件 		AND , OR
字符匹配 		Like , Not Like

询计算机系年龄在20岁以下的学
生姓名。
SELECT Sname
FROM S
WHERE Sdept= 'CS' AND Sage<20；

查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECT Sname，Sdept，Sage
FROM S
WHERE Sage NOT BETWEEN 20 AND 23；

查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
SELECT Sname，Ssex
FROM S
WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' );

• 字符匹配

  • [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <转义字符>’]
  <匹配串>：指定匹配模板
  匹配模板：固定字符串或含通配符的字符串
  当匹配模板为固定字符串时，
  可以用 = 运算符取代 LIKE 谓词
  用 != 或 < >运算符取代 NOT LIKE 谓词
  
  % (百分号) (ACCESS中为*) 代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，	addgb，ab 等都满足该匹配串。
  
  _ (下横线) (ACCESS中为？)代表任意单个字符
  例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb
  等都满足该匹配串。
  
  当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE ‘<
  转义字符>’ 短语对通配符进行转义。
  如：like '_ _\%' escape '\'
  在access中查找通配符用[ ]
  
  例：查询名字中第2个字为"阳"字的学生的姓名和学号。
  SELECT Sname，Sno
  FROM S
  WHERE Sname LIKE '_阳%'；
  
  查询DB_Design课程的课程号和学分。
  SELECT Cno，Ccredit
  FROM C
  WHERE Cname LIKE 'DB\_Design' 
  ESCAPE '\‘;
  

连接查询

同时涉及多个表的查询称为连接查询。

一般格式：
• [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
比较运算符：=、>、<、>=、<=、!=

查询学生及其选修课程的情况
SELECT S.*，SC.*
FROM S 
INNER JOIN SC 
ON S.Sno=SC.Sno;
等价于：
SELECT S.*，SC.*
FROM S，SC
WHERE S.Sno = SC.Sno；

其余连接查询知识点略（感觉有些杂了，不好写）