当前位置： 首页 > article >正文

【专题】关系数据库标准语言SQL

article 2024/10/18 12:18:22

1. SQL语言介绍

SQL(Structured Query Language)是结构化查询语言，它是一种在关系数据库中定义和操纵数据的标准语言，是用户与数据库之间进行交流的接口。

1.1 SQL数据库的体系结构

使用SQL关系数据库的特点：

  • SQL用户可以是应用程序，也可以是终端用户。SQL语言可以被嵌人在宿主语言的程序(例如Python、C++、Java等)中使用，也可以作为独立的用户接口在DBMS环境下被用户直接使用。

  • SQL用户可以用SQL语言对基本表和视图进行查询。

  • 一个视图是从若干基本表或其他视图上导出的表。在数据库中只存放该视图的定义,不存放该视图所对应的数据，这些数据仍然存放在导出该视图的基本表中。因此，可以说视图是一个虚表。

  • 一个或一些基本表对应一个数据文件。一个基本表也可以存放在若干数据文件中。一个数据文件对应存储设备上的一个存储文件。

  • 一个基本表可以带若干索引。索引也存放在数据文件中。

  • 一个表空间可以由若干数据文件组成。

  • 一个数据库可以由多个存储文件组成。

1.2 SQL的特点

  • 综合统一：

    SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。

  • 高度非过程化：

    SQL进行数据操作，只要提出“做什么”，而无须指明“怎么做”，因此无需了解存储路径。存储路径的选择以及SQL的操作过程由系统自动完成。这样可以减轻用户的负担，也提高了数据独立性。

  • 面向集合的操作方式：

    SQL采用集合操作方式，不仅一次查找的结果可以是若干记录的集合，而且一次插入、删除、更新等操作的对象也可以是若干记录的集合。

  • 同一种语法结构提供两种使用方式：

    1. SQL既是独立的语言，又是嵌入式语言。

    2. 作为独立的语言，用户可以在终端键盘上直接输入SQL命令，对数据库进行操作;

    3. 作为嵌入式语言，SQL语言可以被嵌入到高级语言程序中，供程序员设计程序时使用。

    4. 在这两种不同的使用方式下，SQL语言的语法结构基本上是一致的。

  • 语言简洁，易学易懂：

    SQL语言的结构、语法、词汇等本质上都是英语的结构、语法和词汇，容易学习和使用。

SQL语言的只要动词：

  1. 数据定义：CREATE、DROP、ALTER。

  2. 数据操纵：SELECT、INSERT、UPDATE、DELETE。

  3. 数据控制：GRANT、REVOKE、COMMIT、ROLLBACK。

1.3 SQL语言的组成

数据定义语言（DDL）：

  • DDL用来定义(CREATE)、修改(ALTER)、删除(DROP)数据库中的各种对象。

数据操纵语言（DML）：

  • DML的命令用来查询(SELECT)、插入(INSERT)、修改(UPDATE)、删除(DELETE)数据库中数据。

数据控制语言（DCL）：

  • 用于事务控制、并发控制、完整性和安全性控制等。

2. 数据定义

2.1 数据库的定义和删除

  1. 创建数据库。

    CREATE DATABASE 数据库名;

  1. 选择数据库。

    USE 数据库名;

  2. 删除数据库。

    DROP DATABASE 数据库名;

2.2 数据类型

表是数据库存储数据的基本单元，表是由表结构和记录组成。

  • 定义/创建表就是创建表的结构。

  • 表结构的每列包括列（字段）名、数据类型和数据长度。

字符串类型：

  • CHAR(L)，描述定长的字符串。取值范围为0~255。

  • VARCHAR(L)，描述变长的字符串。存储长度取决于字符串的实际长度，而不是定义的长度。取值范围为0~65535。

字符串值必须用单引号括起来。如 'abc'、'女'。

数值类型：

  • INT，用于表示整数，存储长度默认为4个字节。

  • DECIMAL( p , s )，可以用来表示所有的数值数据。其中p表示有效数字的位数，s表示显示小数的位数。

如：123.45可以声明的数据类型为：DECIMAL(5,2)。

p和s在定义时可以省略。

日期和时间类型：

  • DATE，用来保存固定长度的日期数据。日期值格式为'YYYY-MM-DD'。

  • TIME，用来保存固定长度的时间数据。时间值格式为'HH:MM:SS'。

  • DATETIME，用来保存固定长度的日期时间数据。日期时间值格式为' YYYY-MM-DD HH:MM:SS'。

如：'2023-05-20'

布尔类型：

BOOLEAN，只有两个值：TRUE 、FALSE

2.3 基本表的定义、删除和修改

创建表：

CREATE TABLE 表名(
    列名 数据类型[(长度[,小数位])]  [DEFAULT 默认值] 
    [, …] 
);

  • 示例：

CREATE TABLE product(
    p_code    DECIMAL(6),
    p_name   VARCHAR(30),
    p_price   DECIMAL(5,2)
  );

查看表结构：

DESC 表名;
DESCRIB 表名;

  • 示例：

    查看product表的表结构信息。

    DESC product;

综合示例：

创建ord表，将id列设置为自动增长列，主键，ordate列设置默认值为当前系统时间。

CREATE TABLE ord(
    id INT AUTO_INCREMENT  PRIMARY KEY,
    ordno DECIMAL(8),
    p_code DECIMAL(6),
    s_code DECIMAL(6),
    ordate DATETIME                                                                     ,
    price DECIMAL(8,2)
);

  • AUTO_INCREMENT PRIMARY KEY 设置主键自增；

  • DATETIME 设置默认值。

利用子查询来创建表：

CREATE TABLE 表名
SELECT语句;

  • 示例：

    根据dept表，生成新表dept_c。

// 1.
CREATE TABLE dept_c
// 2.
SELECT * FROM dept_c;
// 可以使用SELECT语句查询表中数据
SELECT * FROM DEPT_C

修改表的结构：

  • 在一个表中增加一个新列。

    一个ALTER TABLE ... ADD语句只能为表添加一个新列，如果要增加多个新列，则需要使用多个ALTER TABLE ... ADD。

ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型[(长度[,小数位])] [DEFAULT 默认值];

示例：

为dept_c表增加一个新列telephone。

ALTER TABLE dept_c ADD telephone VARCHAR(11);
DESC dept_c;

  • 修改一个表中已有的列的数据类型。

ALTER TABLE 表名 MODIFY [COLUMN] 列名 数据类型[(长度[,小数位])] [DEFAULT 默认值];

示例：

对dept_c表中的telephone列进行修改，数据类型不变，长度改为13，默认值为0431-86571302。

ALTER TABLE dept_c MODIFY telephone VARCHAR(13) DEFAULT '0431-86571302';
DESC dept_c;

  • 修改一个表中已有列的名称。

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新数据类型;

示例：

将dept_c表中的dname字段名称改为deptname，数据类型保持不变。

ALTER TABLE dept_c CHANGE dname deptname varchar(14);
DESC  dept_c;

  • 从一个表中删除一列。

ALTER TABLE 表名 DROP [COLUMN] 列名;

示例：

删除dept_c表中的telephon列。

ALTER TABLE dept_c DROP telephone; 
DESC  dept_c;

截断表和删除表

  • 截断表：

    当一个表中的数据不再需要时，可以使用截断语句将它们全部删除，即截断。

    截断语句只删除表中的所有数据行，但表的结构仍然保留。

TRUNCATE TABLE 表名；

  • 删除表：

    当不仅要删除表中的数据而且要删除表的结构时，可以使用删除语句。

DROP TABLE 表名；

示例：

CREATE TABLE dept_bk SELECT * FROM dept;
TRUNCATE TABLE  dept_bk;
SELECT * FROM dept_bk;
DROP TABLE dept_bk;

3. 数据查询

查询语句的基本格式：

SELECT * | 列名 | 列表达式[, 列名 | 列表达式 , ……]
    FROM 表名或视图名[, 表名或视图名 , ……]
    [ WHERE 条件表达式 ]
    [ GROUP BY 分组列名1][, 分组列名2,……]]  
    [ HAVING 条件表达式 ] ]
    [ ORDER BY 排序列名1 [ ASC| DESC [, 排序列名2 [ ASC|DESC ]],……];

整个语句的执行过程：

  1. 读取FROM子句中的表、视图的数据，如果是多个表或视图，执行笛卡尔儿积操作。

  2. 选择满足WHERE子句中给出的条件表达式的记录。

  3. 按GROUPBY子句中指定列的值对记录进行分组，同时提取满足HAVING子句中组条件表达式的那些组。

  4. 按SELECT子句中给出的列名或列表达式求值输出。

  5. ORDERBY子句对输出的记录进行排序，按ASC升序排列或按DESC降序排列。

SELECT语句既可以完成简单的单表查询,也可以完成复杂的连接查询和嵌套查询。

3.1 基本查询

SELECT子句的规定：

SELECT [ ALL | DISTINCT] * | 字段名或字段名表达式序列

  • DISTINCT选项表示输出无重复结果的记录；

  • ALL选项是默认的，表示输出所有记录，包括重复记录。

  • * 表示选取表中所有的字段。

  1. 查询所有列。

    SELECT * FROM 表名;

示例：

查询dept表的全部数据。

SELECT * FROM dept;

  1. 查询指定列。

    SELECT 列名1 , 列名2 , …… FROM 表名;

示例：

查询dept表的部门编号deptno和部门名称dname信息。

SELECT deptno,dname FROM dept;

  1. 去掉重复行。

    SELECT distinct * | 字段名1 , 字段名2 , … FROM 表名;

示例：

查询雇员表emp中各部门的职务信息。

SELECT deptno,job FROM emp;

去掉查询结果中重复行：

SELECT DISTINCT deptno,job FROM emp;

列起别名的操作：

SELECT 字段名1 [AS] 列别名, 字段名2 列别名 , … FROM 表名;

示例：

查询emp表员工的empno、ename、hiredate，输出显示的列名为雇员编号、雇员姓名、雇佣日期。

SELECT empno AS 雇员编号,ename 雇员姓名,hiredate 雇佣日期 FROM emp;

使用WHERE子句指定查询条件:

SELECT * | 列名1 , 列名2 , …… FROM 表名 WHERE 条件;

  • 算术比较判断（<、<=、>、>=、<> 或 !=、 =）

示例：

查询emp表中SMITH的雇佣日期。

SELECT ename, hiredate FROM empWHERE ename='SMITH';

  • 逻辑比较判断（NOT、AND、OR）

示例：

查询emp表中在部门10工作的、工资高于1000或岗位是CLERK的所有雇员的姓名、岗位、工资的信息。

SELECT ename,job,sal FROM emp WHERE deptno='10' AND (sal>1000 OR job='CLERK');

优先级（高→低）：NOT、AND、OR

之间判断：

表达式 [NOT] BETWEEN 小值 AND 大值

包括小值和大值的边界值。闭区间。

示例：

查询emp表工资在2500~3000之间，1981年聘用的所有雇员的姓名、工资、聘用日期信息。

SELECT ename,sal,hiredate FROM emp
    WHERE sal BETWEEN 2500 AND 3000
    AND hiredate BETWEEN '01-1月-81' AND '31-12月-81';

字符串模糊判断:

字符串 [NOT] LIKE 匹配模式

  • %用于表示0个或任意多个字符；

  • _表示任意1个字符。

示例：

查询emp表中所有姓名以K开头或姓名第2个字母为C的员工的姓名、部门号及工资的信息。

SELECT ename,deptno,sal FROM emp
    WHERE ename LIKE 'K%' OR ename LIKE '_C%';

空值判断：

表达式 IS [NOT] NULL

示例：

查询emp表中1981年聘用且没有补助的员工的姓名和职位信息。

SELECT ename,job FROM emp
    WHERE hiredate BETWEEN '01-1月-1981' AND '31-12月-1981'
    AND comm IS NULL;

之内判断：

表达式 [NOT] IN (集合)

示例：

查询emp表中部门20和30中的，岗位是CLERK的所有雇员的部门号、姓名、工资的信息。

SELECT deptno,ename,sal FROM emp 
    WHERE deptno  IN (20,30)  AND  job='CLERK';

使用ORDER BY子句对查询结果排序:

SELECT  * | 列名1 , 列名2 , …… FROM  表名 WHERE  条件
    ORDERY  BY  列1[ASC/DESC][,列2[ASC/DESC]……];

  • ASC为升序，DESC为降序，默认为ASC。

  • 当SELECT语句中同时包含多个子句，如WHERE、GROUP BY、HAVING、ORDER BY，ORDER BY 子句必须放至最后。

  • 可以使用列的别名、列的位置进行排序。

示例：

以部门号的降序、姓名升序，显示emp表中工资在2000~3000员工的部门号、姓名、工资、补助信息。

SELECT deptno,ename,sal,comm FROM  emp
    WHERE  sal  BETWEEN  2000  AND  3000
    ORDER  BY  deptno  DESC,ename;

3.2 分组查询

聚合函数：

名称函 数说 明
统计个数COUNT(*) COUNT(列名)计算记录的个数 对一列中的值计算个数
求和SUM(列名)求某一列值的总和
求平均值AVG(列名)求某一列值的平均值
求最大值MAX(列名)求一列值的最大值
求最小值MIN(列名)求一列值的最小值

除了COUNT(*)之外，其他聚合函数（包括COUNT(列名)）都忽略对列值为NULL值的统计。

示例：

统计30部门员工的平均工资、总补助款、总人数、补助人数、最高工资和最低工资。

SELECT AVG(sal) AS 平均工资,SUM(comm) 总补助款,
    COUNT(*) AS 总人数,COUNT(comm) 补助人数,
    MAX(sal) AS 最高工资,MIN(sal)  最低工资
    FROM  emp
    WHERE deptno=30;

使用GROUP BY子句：

SELECT * | 列名1,列名2,…… FROM 表名 WHERE 条件 GROUP BY 分组列1[,分组列2,……];

  • 按单列分组:

示例：

查询emp表中每个部门的平均工资和最高工资，并按部门编号升序排序。

SELECT deptno,AVG(sal) 平均工资,MAX(sal) 最高工资 FROM emp
    GROUP BY deptno
    ORDER BY deptno;

  • 按多列分组:

示例：

查询emp表中每个部门、每种岗位的平均工资和最高工资。

SELECT deptno,job,AVG(sal) 平均工资,MAX(sal) 最高工资 
    FROM emp
    GROUP BY deptno,job
    ORDER BY deptno;

使用HAVING子句:

SELECT  * | 列名1,列名2,…… FROM 表名 WHERE 条件
    GROUP  BY  分组列1[,分组列2,……]
    HAVING  对分组统计后的结果进行条件限制;

示例：

查询每门课成绩及格人数超过3人（含3人）的人数。

SELECT 课号,COUNT(*) 及格人数 FROM  选课表  
    WHERE 成绩>=60
    GROUP BY 课号 HAVING COUNT(*)>=3;

3.3 多表连接查询

连接查询是指对两个或两个以上的表或视图的查询。

  • 要连接的表都要放在FROM子句中，表名之间用逗号分开。

    比如：FROM dept,emp。

  • 为了书写方便，可以为表起别名，表的别名在FROM子句中定义。

    比如：FROM dept d , emp AS e。

    注意：别名一经定义，在整个查询语句中就只能使用表的别名而不能再使用表名。

  • 连接的条件放在WHERE子句中。

    比如: WHERE d.deptno=e.deptno。

  • 如果多个表中有相同列名的列时，在使用这些列时，必须在这些列的前面冠以表名来区别，表名和列名之间用句号隔开。

    比如：SELECT e.deptno

相等连接：

  • 是把两个表中，指定列的值相等的行横向连接起来。

示例：

查询工资大于等于3000的员工的员工编号、姓名、工资、所在部门编号及部门所在地址，结果按部门编号进行排序。

// 1. 
SELECT empno,ename,sal,e.deptno,loc   FROM emp e,dept d
    WHERE e.deptno=d.deptno  AND  sal>=3000
    ORDER BY e.deptno;
// 2. 
SELECT empno,ename,sal,e.deptno,loc
    FROM  emp  e  INNER  JOIN  dept  d ON  e.deptno=d.deptno
    WHERE sal>=3000
    ORDER BY e.deptno;

自连接：

  • 通过把一个表定义了两个不同别名的方法（即把一个表映射成两个表）来完成的自已与自己的连接。

示例：

查询emp表中在部门20工作的雇员的姓名及其管理员的姓名。

SELECT e.ename 雇员,m.ename 经理
    FROM emp e,emp m
    WHERE e.mgr=m.empno AND e.deptno=20;  

不等值连接：

示例：

salgrade表中存放着工资等级的信息，查询20号部门员工的姓名、工资及工资等级的信息。

SELECT  ename,sal,grade   FROM   emp e,salgrade s
    WHERE  e.sal  BETWEEN s.losal  AND s.hisal
    AND  deptno=20;

左外连接：

FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列=表2.列

  • 左外连接的结果是：显示表1中所有记录和表2中与表1.列相同的记录，如果表2中没有与表1相同的记录，则相应列的值为NULL。

示例：

显示10和40的部门及员工的相关信息，包括没有员工的部门。

SELECT loc,dept.deptno,emp.deptno,ename,empno
    FROM  dept  LEFT  OUTER JOIN emp ON dept.deptno=emp.deptno
    WHERE dept.deptno=10 OR dept.deptno=40;

右外连接:

FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列=表2.列

  • 右外连接的结果是：显示表2中所有记录和表1中与表2.列相同的记录，如果表1中没有与表2相同的记录，则相应列的值为NULL。

示例：

用右外连接实现显示10和40的部门及员工的相关信息，包括没有员工的部门。

SELECT empno,ename,emp.deptno,dept.deptno,loc
    FROM  emp  RIGHT  OUTER JOIN  dept ON  emp.deptno=dept.deptno
    WHERE dept.deptno=10 OR dept.deptno=40;

3.4 子查询

子查询是指嵌入在其它SQL语句中的一个查询，子查询需要用（）括起来。使用子查询，可以构建复杂的查询，增强SQL语句的功能。

子查询过程：

  1. 首先取外层查询中表的第1个记录，根据它与内层查询相关的列值进行内层查询的处理(例如WHERE子句的处理)，若处理结果为真，则取此记录放人结果集。

  2. 然后取外层表的下一个记录进行内层查询的处理。

  3. 重复这一过程，直到外层查询中表的全部记录处理完为止。

返回单值的子查询：

示例：

查询与SCOTT工作岗位相同的员工的员工编号、姓名、工资、岗位信息。

SELECT empno,ename,sal,job FROM emp
    WHERE job=( 
        SELECT job  FROM emp 
            WHERE ename='SCOTT' 
            );

返回多值的子查询：

  • 使用IN操作符的多值子查询－实现多值的等号比较。

示例：

查询工资为所任岗位最高的员工的职工编号、姓名、岗位和工资的信息，不包含岗位为CLERK和PRESIDENT的员工。

SELECT empno,ename,job,sal FROM emp
    WHERE sal IN (
        SELECT MAX(sal) FROM emp GROUP BY job
        )
    AND job<>'CLERK' AND job<>'PRESIDENT';

  • 使用ALL操作符的多值子查询.

    1. ALL操作符比较子查询返回列表中的每一个值。

    2. <ALL：为小于最小的； >ALL：为大于最大的。

示例：

查询高于部门20的所有雇员工资的雇员信息。

SELECT ename,sal,job FROM emp
    WHERE sal>ALL(
        SELECT sal FROM emp WHERE deptno=20
        );

  • 使用ANY操作符的多值子查询

    1. ANY操作符比较子查询返回列表中每一个值。

    2. <ANY：为小于最大的；>ANY：为大于最小的。

示例：

查询高于部门10的任意雇员工资的雇员信息。

SELECT ename,sal,job FROM emp
    WHERE sal>ANY(
        SELECT sal FROM emp WHERE deptno=10
        );

  • 使用EXISTS操作符的多行查询

    1. EXISTS操作符比较子查询返回列表的每一行。

    2. 外层查询的WHERE子句格式为：WHERE EXISTS；

    3. 内层子查询中必须有WHERE子句，给出外层查询和内层子查询所使用表的连接条件。

示例：

查询工作在CHICAGO的雇员姓名、部门编号、工资、岗位的信息。

SELECT ename,deptno,sal,job FROM emp
    WHERE EXISTS  ( 
        SELECT * FROM dept
            WHERE dept.deptno=emp.deptno AND loc='CHICAGO'
            );

3.5 合并查询结果

当两个SELECT查询结果的结构完全一致时，可以对这两个查询执行并的运算，运算符为UNION。

SELECT 语句1 UNION [ALL] SELECT 语句2;

  • UNION在连接数据表的查询结果时，结果中会删除重复的行，所有返回的行都是唯一的。

  • 使用UNION ALL的时候，结果中不会删除重复行。

示例：

查询岗位为MANAGER的员工信息，再查询10号部门员工信息，使用UNION连接查询结果。

SELECT empno,ename,deptno,job FROM emp WHERE job='MANAGER'
UNION 
SELECT empno,ename,deptno,job FROM emp WHERE deptno=10;

4. 数据的维护

4.1 插入数据

INSERT语句：

INSERT  INTO  表名[ (列名1[,列名2……]) ]
    VALUES(值1[,值2……]) 
            [,(值1[，值2……]),……,(值1[，值2……])];

  • 插入数据时，列的个数、数据类型、顺序必须要和提供的数据的个数、数据类型、顺序保持一致或匹配。

  • 如果省略了表名后列的列名表，即表示要为所有列插入数据。

示例：

拟新建一个部门，编号为80，地址为“SHANGHAI”，但并没有确定该部门的名字，完成此条记录的插入。

INSERT  INTO  dept_c
    VALUES(80,NULL,'SHANGHAI');

利用子查询向表中插入数据:

INSERT  INTO  表名 [ (列名1[,列名2……]) ]
    SELECT语句;

示例：

先将dept_c表中的记录全部删除，再使用INSERT命令将dept表中的记录插入到dept_c表中。

TRUNCATE  TABLE  dept_c;
​
INSERT  INTO  dept_c
    SELECT  *  FROM  dept   
    WHERE  deptno=10  OR  deptno=20 OR deptno=40;

4.2 更新数据

UPDATE语句：

UPDATE  表名
    SET 列名=值[,列名=值,……]
    [ WHERE 条件 ]

如果不用WHERE子句限定要更新的数据行，则会更新表中所有记录行对应的列值。

示例：

更新dept_c表中部门10的地址为CHINA。

SET SQL_SAFE_UPDATES=0;
UPDATE  dept_c  SET  loc='CHINA'
    WHERE  deptno=10;
SELECT * FROM dept_c;

利用子查询修改记录:

示例：

根据dept表更新dept_c表中部门40的部门名称。

UPDATE dept_c
    SET  dname=(
        SELECT dname FROM dept WHERE deptno=40
        )
    WHERE deptno=40;
SELECT * FROM dept_c;
​

4.3 删除数据

DELETE语句：

DELETE FROM 表名 [ WHERE 条件 ];

  • DELETE是按行删除数据，不是删除行中某些列的数据。

  • 如果不用WHERE子句限定要删除的数据行，则会删除整个表的数据行。

  • 删除表中所有数据行，也可用截断表的语句实现，其格式为： TRUNCATE TABLE 表名;。

  • TRUNCATE TABLE语句删除所有记录的速度要快一些。

示例：

删除 dept_c表中部门10的记录。

DELETE FROM  dept_c 
    WHERE  deptno=10;

利用子查询删除行:

示例：

根据emp表创建其副本emp_c，删除emp_c表中工作在RESEARCH部门的员工的数据行。

DELETE FROM emp_c 
    WHERE  deptno=(
        SELECT  deptno FROM  dept  
            WHERE  dname='RESEARCH'
            );

5. 索引和视图

5.1 索引的定义

  • 索引是一种提高数据检索效率的数据库对象。

  • 索引是基于表而建立的。

  • 索引由系统自动维护和使用，不需要用户参与。

CREATE INDEX xh_idx ON 学生表(学号);

索引的优点：

  • 加快数据的检索速度，这也是创建索引的最主要的原因。

索引的缺点：

  1. 创建索引和维护索引要耗费时间，耗费时间随着数据量的增加而增加。

  2. 索引需要占用物理空间，除了数据表所占数据空间之外，每一个索引还要占一定的物理空间。

  3. 当对表进行Insert、Delete和Update数据时，要动态的维护索引，从而降低了数据的维护速度。

索引的分类：

  • 普通索引。 可以创建在任何数据类型的列上。

  • 唯一性索引(UNIOUE)。

    作为唯一性索引的列，其值必须是唯一的，允许有空值。

  • 单列索引和多列索引。

5.2 索引的创建与删除

创建索引：

CREATE [UNIQUE] INDEX 索引名 
    ON 表名(列名[,列名]……)

示例：

为emp_c表按员工的名字（ename）建立索引，索引名为emp_ename_idx。

CREATE  INDEX  emp_ename_idx ON  emp_c(ename);

查看索引:

SHOW  INDEX  FROM  表名;

示例：

查看emp_c表的所有的索引信息。

SHOW  INDEX  FROM  emp_c;

删除索引：

DROP  INDEX  索引名 ON  表名;

示例：

删除emp_c表中已建立的索引emp_job_sal_idx。

DROP  INDEX emp_job_sal_idx ON emp_c;

5.3 视图

视图（View）是由SELECT查询语句定义的一个逻辑表，不存放数据，只存放视图的定义语句，是一个“虚拟表”。

视图的使用和管理与表相似，可以创建、更改和删除，也可以通过视图对数据库中的数据进行增删改查操作。

视图的优点：

  1. 隐藏数据的逻辑复杂性并简化查询语句 。

  2. 提供某些安全性保证，简化用户权限的管理。

    视图可以实现让不同的用户看见不同的列，从而保证某些敏感的数据不被某些用户看见。

  3. 对重构数据库提供了一定的逻辑独立性。

    • 视图是数据库三级模式中外模式在具体DBMS中的体现。

    • 当重构数据库时，即概念模式发生改变，通过模式/外模式映射，外模式即视图不用改变，则与视图有关的应用程序也不用改变，保证了数据的逻辑独立性。

创建视图：

CREATE [OR  REPLACE] VIEW 视图名[(别名[,别名]…)]    
    AS SELECT语句
        [WITH  CHECK  OPTION];

  • OR REPLACE：如果所创建的视图已经存在，Oracle重建这个视图

  • 别名：为视图所产生的列定义的列名。

  • WITH CHECK OPTION：所插入或修改的数据行必须满足视图所定义的WHERE条件。

  • 注意在子查询语句中不能包含ORDER BY子句。

示例：

创建带有WITH CHECK OPTION选项的视图。

CREATE VIEW v_dept_chk 
    AS SELECT empno,ename,job,deptno FROM  emp  
        WHERE  deptno=10
    WITH  CHECK  OPTION;

修改视图：

  1. 使用CREATE OR REPLACE VIEW语句修改视图。

示例：

创建带有WITH CHECK OPTION选项的视图，修改建立的视图v_dept_chk，取消约束条件检查。

CREATE OR REPLACE VIEW  v_dept_chk
    AS SELECT empno,ename,job,deptno FROM  emp
        WHERE  deptno=10;

  1. 使用ALTER VIEW语句修改视图.

示例：

创建带有WITH CHECK OPTION选项的视图，修改建立的视图v_dept_chk，取消约束条件检查。

ALTER VIEW  v_dept_chk
    AS SELECT empno,ename,job,deptno FROM  emp
        WHERE  deptno=10;

删除视图：

DROP VIEW 视图名;

示例：

删除已创建的视图v_dept_chk。

DROP VIEW v_dept_chk;

更新（INSERT、UPDATE、DELETE）视图数据:

示例：

通过视图v_tj，删除部门10的记录。

CREATE VIEW v_tj
    AS SELECT deptno,COUNT(*) 人数 FROM emp
        GROUP BY deptno;
DELETE FROM v_tj WHERE deptno=10;

不能更新数据的视图:

  1. 视图中包括SUM()、COUNT()、MAX()和MIN()、AVG()等函数;

  2. 视图中包括UNION、UNION ALL、DISTINCT、GROUP BY.HAVING等子句。

  3. 如果在一个视图中包含了由表达式组成的列，则不能通过该视图进行UPDATE、INSERT操作。

  4. 如果在一个视图中没有包含引用表中那些不能为空的列，则不能通过该视图进行INSERT操作。


http://www.kler.cn/news/354412.html

相关文章：

  • 人工智能需要学习哪些语言?
  • NodeJS火锅店点单系统-计算机毕业设计源码86547
  • Jmeter之GET与POST 请求的参数存放位置
  • 基于SpringBoot+Vue+uniapp的海产品加工销售一体化管理系统的详细设计和实现(源码+lw+部署文档+讲解等)
  • 服务器数据恢复—服务器硬盘指示灯亮黄灯，raid崩溃的数据恢复案例
  • 机器学习-决策树详解
  • 携程线下一面，面试内容：
  • 【东方oj题解】1893、1821、1822
  • C++研发笔记2——学习规划概览
  • docker方式k8s环境搭建及pod简介
  • FFmpeg的简单使用【Windows】--- 视频混剪+添加背景音乐
  • 2. MySQL数据库基础
  • 医疗病历交互系统：Spring Boot技术解析
  • 【pytorch深度学习】CIFAR10图像分类
  • 【Mac苹果电脑安装】DBeaverEE for Mac 数据库管理工具软件教程【保姆级教程】
  • 【Linux安全基线】- CentOS 7/8安全配置指南
  • C++设计模式 单例模式
  • 滚雪球学Redis[6.2讲]：Redis脚本与Lua：深入掌握Redis中的高效编程技巧
  • ModuleNotFoundError: No module named ‘pdfminer.high_level‘
  • buffer/cache内存优化_posix_fadvise_主动释放读缓存cache