关于数据库的一些基础知识及常用语句 Oracle、MySQL

关于数据库的一些基础知识及常用语句 Oracle、MySQL

Oracle：大型数据库，在做大型业务或大数据时常用。 MySQL：中级数据库，中小企业使用。SQLite：轻量级数据库。

（一）、基础知识：

1、关系模型的术语：

• 关系：一个关系对应一张表
• 元组：表中的一行
• 属性：表中的一列
• 码（码键）：表中的某个属性组
• 分量：元组中的一个属性值
• 关系模式：关系名（属性1，属性2，... ，属性n）
• 候选码：关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，该属性组为候选码
• 主码：若一个关系有多个候选码，而选定其中一个为主码（主码唯一标识一个元组，主码相同等价于同一个元组）
• 主属性：候选码的诸属性称为主属性
• 外码：在该表中不是主码，但是在其他表中是主码

2、基本的关系操作：

1）插入、删除、修改

2）查询（①选择，②投影，③连接，④除，⑤并，⑥交，⑦差，...）

3、关系的完整性（3类）：

1）实体完整性：（针对主码而言）主键不能为空值，候选码里的属性都是主属性。每个实体是可区分的，关键字不可重复。eg：学号

2）参照完整性：（针对外码而言）外码的值要么为表中值的子集（关键字是它的表），要么为空。

3）用户定义的完整性

【注】：实体完整性和参照完整性是关系必须满足的两个属性。

（二）、SQL（结构化查询语言）：

                                 表1. SQL的动词：                                                                                                                                     表2. SQL的数据定义语句：     

SQL功能	动词
数据查询	SELECT(查询)
数据定义	CREATE(创建)，DROP(删除数据库)，ALTER(修改)
数据操纵	INSERT(插入)，UPDATE(更新)，DELETE(删除记录)
数据控制	GRANT(赋予权限)，REVOKE(从当前用户或组中取消权限)

操作对象	操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

【注】：在创建的表中，理论上不存放其年龄，而是存放其出生日期。

【例子2-1】：

创建一个 “学生”表 Student：

CREATE TABLE Student
 (Sno CHAR(9) PRIMARY KEY,    /*列级完整性约束条件，Sno是主码*/
 Sname CHAR(20) UNIQUE,     /*UNIQUE表示取唯一值*/
 Ssex CHAR(2),
 Sage SAMLLINT,
 Sdept CHAR(20)
);

创建一个 “课程”表 Course：

CREATE TABLE Course
 (Cno CHAR(4) PRIMARY KEY,  /*列级完整性约束条件，Cno是主码*/
 Cname CHAR(40) NOT NULL,  /*列级完整性约束条件，Cname不能取空值*/
 Cpno CHAR(4),  /*Cpno的含义是先修课*/
 Ccredit SMALLINT,
 FOREING KEY(Cpno) REFERENCES Course(Cno)  /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

PS：参照表和参照表可以是同一个表。

创建一个学生选课表SC：

CREATE TABLE SC
 (Sno CHAR(9),
 Cno CHAR(4),
 Grade SMALLINT,
 PRIMARY KEY(Sno,Cno),  /*主码由2个属性构成，必须作为表级完整性进行定义*/  /*实体完整性*/
 FOREIGN KEY(Sno) REFERENCES Student(Sno),  /*表级完整性约束条件，Sno是外码，被参照表是Student*/  /*参照完整性*/
 FOREIGN KEY(Cno) REFERENCES Course(Cno),  /*表级完整性约束条件，Cno是外码，被参照表是Course*/  /*参照完整性*/
 CHECK((Grade IS NULL) OR (Grade BETWEEN 0 AND 100))/*用户自定义的完整性*/
);

【注】：Sno表示编号，用CHAR类型，而不用INT类型的原因：为了能让0001这种编号能够被表示。

在数据类型中，CHAR(10) 和 VARCHAR(10)，同样输入5个字符，CHAR(10)是前5个占位 但为空格，VARCHAR(10)是只占5个字符。

                         CHAR(n)：表示固定长度为n的字符串，VARCHAR(n)：表示最大长度为n的字符串。

修改基本表：

格式：

ALTER TABLE  <表名>

 [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]

 [ADD <表级完整性约束>]

 [DROP [COLUMN]<列名> [CASCADA | RESTRICT]]

 [DROP CONSTRAINT <完整性约束> [RESTRICT | CASCADE]]

 [ALTER COLUMN <列名> <数据类型>];

注：不论基本表中原来是否已有数据，新增加的列一律为空值。

删除基本表：

格式：

DROP TABLE <表名> [RESTRICT | CASCADE];

•  RESTRICT：欲被删除的表不能被其他表的约束引用、不能有视图、不能有触发器、不能有存储过程或函数等，否则 该表不能被删除。
•  CASCADE：该表没有限制条件，删除表的同时，其相关的依赖对象，如视图等，都会被一起删除。

数据查询：

格式：

SELECT [ALL | DISTINCT] <目标列表达式>  [,<目标列表达式>]...         // 这个<目标列表达式>不仅可以是表中的属性列，还可以是表达式

FROM <表名或视图名>  [,<表名或视图名>...] | (<SELECT语句>) [AS] <别名>

 [WHERE <条件表达式>]

 [GROUP BY <列名1> [HAVING <条件表达式>]]                         //（满足条件）的<列1>的值排列成一个组

 [ORDER BY <列名2> [ASC | DESC]];                                    // 按<列2>的值升序或降序排列

【例子】：

在查询结果中丢掉重复的行：

SELECT DISTINCT Sno
FROM SC；

• WHERE语句常用的查询条件：

查询条件	谓词
比较	=，>，<，>=，<=，!=（不等于），<>（不等于），!>（不大于），!<（不小于）；NOT+上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件（逻辑运算）	AND，OR，NOT

• 谓词 LIKE：可以用来进行字符串的匹配。语法如下：

              [NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']

          含义：查找指定的属性列值与<匹配串>相匹配的元组。

          <匹配串>中可以含有通配符 % 和 _ 

                %： 表示任意长度（长度可以为0）； _： 表示任意单个字符。

          ESCAPE '\'：表示'\'为换码字符

【例子】：找出年龄比“王伟”同学大的同学的年龄和姓名

SELETE S1.Sname,S1.Age
FROM Student S1,Student S2
WHERE S1.Age > S2.Age
  AND S2.Sname = '王伟';

聚集函数：

COUNT(*)	统计元组个数（统计行数）
COUNT([DISTINCT | ALL] <列名>)	统计一列中值的个数
SUM([DISTINCT | ALL] <列名>)	计算一列值的总和（此列必须是数值型）
AVG([DISTINCT | ALL] <列名>)	计算一列值的平均值（此列必须是数值型）
MAX([DISTINCT | ALL] <列名>)	计算一列值的最大值
MIN([DISTINCT | ALL] <列名>)	计算一列值的最小值

• DISTINCT：在计算时要取消重复值；  ALL：计算时不取消重复值。

【注】：在WHERE子句中，不能用聚集函数作为条件表达式。 

自身连接、外连接、左外连接、右外连接.

嵌套查询：

一个SELETE-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

（1）带 IN 谓词的子查询：

【例子】：查询选修了课程名为“信息系统”的学生学号和姓名。

SELETE Sno,Sname  /*3*/
FROM Student
WHERE Sno IN
    (SELETE Sno   /*2*/
      FROM SC
      WHERE Cno IN
        (SELETE Cno   /*1*/
          FROM Course
          WHERE Cname='信息系统'
        )
    );

【实现一】1：首先在Course关系中找出“信息系统”的课程号，结果为3号

                  2：然后在SC关系中找出选秀了3号课程的学生学号

                 3：最后在Student关系中取出Sno 和 Sname

SELETE Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
    SC.Cno=Course.Cno AND
    Course.Cname='信息系统';

【实现二】：用连接查询实现。实际运算中，能够用连接查询实现的尽量用连接查询实现。

（2）带有 ANY (SOME) 或 ALL谓词的子查询：

• >ANY  大于子查询结果中的某个值
• >ALL   大于 所有值
• <ANY  小于 某个值
• <ALL   小于 所有值
• >=ANY  大于等于 某个值
• >=ALL   大于等于 所有值
• <=ANY  小于等于 某个值
• <=ALL   小于等于 所有值
• =ANY  等于 某个值
• =ALL   等于 所有值
• !=（或<>）ANY  不等于 某个值
• !=（或<>）ALL   不等于 所有值

（3）带有EXISTS谓词的子查询：

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 “true”  或  逻辑假值 “false”。

放在WHERE的后面，eg：SELETE - FROM -WHERE EXISTS   或者   SELETE - FROM - WHERE NOT EXISTS

集合查询：

集合操作包括：并UNION、交INTERSECT、差EXCEPT

【例子】：查询选修了课程1或者选修了课程2的学生

SELETE Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

插入数据：

格式：

INSERT

INTO <表名> [(<属性列1>[,<属性列2>]...)]

VALUES(<常量1>[,<常量2>]...);

【注】：① 属性列与值要一一对应，不加值的会自动地赋空值

              ② 可以插入子查询，格式： INSERT

                                                    INTO <表名> [(<属性列1>[,<属性列2>]...)]

                                                    子查询;

修改数据：

格式：

UPDATE <表名>

SET <列名>=<表达式> [，<列名>=<表达式>]...

[WHERE <条件>];

【注】：① WHERE子句条件可以省略，如果省略 则表示要修改表中的所有元组。

              ② 可以插入子查询

删除数据：

格式：

DELETE

FROM <表名>

[WHERE <条件>];

【注】：① WHERE子句条件可以省略，如果省略 则表示要删除表中的所有元组。

              ② 可以插入子查询