子查询
子查询可以分为单行子查询和多行子查询
单行子查询
[1] 将一个查询的结果作为另外一个查询的条件来使用
特点:
子查询在主查询前执行一次
主查询使用子查询的结果
- SELECT * FROM EMP
- WHERE SAL> (SELECT SAL FROM EMP WHERE ENAME=CLARK);
[2] 子查询可以作为 insert 语句的值来使用
-- 今天来了一个新员工, 将他添加到与 SCOTT 同一个部门
- INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
- VALUES(1001, ALEX, (SELECT DEPTNO FROM EMP WHERE ENAME=SCOTT));
[3] 子查询可以作为 update 的条件或者修改的值来使用
-- 作为修改的值
-- 将新员工 ALEX 的工作修改为与 SMITH 的工作相同
UPDATE EMP SET JOB=(SELECT JOB FROM EMP WHERE ENAME=SMITH) WHERE ENAME=ALEX;
-- 作为条件
-- 将比 FORM 工资低的员工的薪资都加 1000 块
UPDATE EMP SET SAL=SAL+1000 WHERE SAL<(SELECT SAL FROM EMP WHERE ENAME=FROM);
[4] 子查询可以作为 delete 的条件使用
1 DELETE EMP WHERE SAL> (SELECT SAL FROM EMP WHERE ENAME=FORM);
[5] 子查询的结果可以作为一个表来使用
-- 查询员工的编号, 姓名, 经理姓名
- SELECT E.ENAME, E.EMPNO, M.MNAME
- FROM EMP E, (SELECT EMPNO, ENAME FROM EMP) M
- WHERE E.MGR = M.MNAME;
多行子查询
子查询的结果返回是 多行数据
all: 和子查询返回的所有值比较
-- 查询工资低于任何一个 CLERK 岗位的工资的雇员信息
SELECT * FROM EMP WHERE SAL <ANY(SELECT SAL FROM EMP WHERE JOB=CLERK);
any: 和子查询返回的任意一个值比较
-- 查询工资比所有的 SALESMAN 职位都高的雇员的编号, 名字和工资
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL> ALL(SELECT SAL FROM EMP WHERE JOB = SALESMAN);
in : 等于列表中的任何一个
-- 查询部门 20 中的职务同部门 10 的雇员一样的雇员信息
SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE DEPTNO = 10) AND DEPTNO = 20;
创建用户
创建
-- CREATE USER 用户名 IDENTIFIED BY 密码
-- 使用 SCOTT 用户无法创建, 因为权限不足, 登录 sys 账户创建即可
CREATE USER ABC IDENTIFIED BY 123;
给用户赋予权限 权限是执行特定类型的 sql 命令或者访问其他对象的权利
-- 赋予 ABC 临时用户
GRANT CONNECT TO ABC; -- CONNECT: 临时用户
-- 给 ABC 赋予 DBA 角色 角色是具有名称的一组权限的组合
GRANT DBA TO ABC;
-- 撤销权限
REVOKE CONNECT FROM ABC; -- 撤销 ABC 的临时权限
-- 删除一个用户
DROP USER ABC; -- 删除用户 ABC
系统常用的预定义角色
CONNECT: 临时用户
RESOURCE: 更为可靠的正式用户
DBA: 数据库管理员角色, 拥有管理员的最高权限
创建表
- /*
- CREATE TABLE 表名(
- 列名 数据类型 [默认 表达式], ...
- );
- */
-- 创建一个表
- CREATE TABLE STUDENT(
- SNO NUMBER(6),
- NAME VARCHAR2(20),
- SEX VARCHAR2(2),
- AGE VARCHAR2(3)
- );
对表的一些常用操作
-- 向已有的表中插入一个列
ALTER TABLE STUDENT ADD ADDRESS VARCHAR2(20); -- 插入 ADDRESS 列
-- 将已有表中的列删除
ALTER TABLE STUDENT DROP COLUMN SNO; -- 删除 SNO 列
-- 将已有表中的列进行修改(默认只对新添加的数据有效, 对已经添加的数据无效)
ALTER TABLE STUDENT MODIFY(SEX VARCHAR2(4) DEFAULT 男);
-- 对已有表中的列重命名
ALTER TABLE STUDENT RENAME COLUMN NAME TO SNAME; -- 将 NAME 列的名字改为 SNAME
-- 给表重命名
RENAME STUDENT TO STU; -- 将表 STUDENT 重命名为 STU
约束
数据完整性约束
1. 表的数据有一定的取值范围和联系, 多表之间的数据有时也有一定的参照关系
2. 在创建表和修改表时, 可通过定义约束条件来保证数据的完整性和一致性
3. 约束条件是一些规则, 在对数据进行插入删除和修改时要对这些规则进行验证, 从而起到约束作用
完整性约束分类
域完整性约束 (非空 not null, 检查 check) 字段约束
实体完整性约束(唯一 unique, 主键 primary key) 行和行之间的约束
参照 (引用) 完整性约束(外键 foreign key) 表和表之间的约束
主键约束 (primary key) PK_表名_字段名
要求主键类数据唯一, 并且不允许为空, 主键可以包含表的一列或多列, 多列组成的主键称之为 - 复合主键
唯一约束(unique) UK_表名_字段名
要求该列值唯一, 允许为空, 而且 null 可以是多个
检查约束 (check) CK_表名_字段名
某列取值范围显示, 格式限制等, 如年龄拘束, 邮件限制 email like %@% --length (pwd) =6
非空约束(not null) NN_表名_字段名
某列内容 (值) 不为空
外键约束(foreign key) FK_表名_字段名
用于两个表之间建立关系, 需要制定引用主表的那列, 外键同创是用来约束两个表之间的数据关系
定义外键的那张表称之为子表, 另一张表称之为主表, 在表的创建过程中, 应该先创建主表, 后创建子表
在创建表时创建约束
CREATE TABLE STU(
STUNO NUMBER(3) CONSTRAINT PK_STU_ID PRIMARY KEY, -- 学号长度是 3, 为主键约束, 约束名是 PK_STU_ID
STUNAME VARCHAR2(20) NOT NULL -- 名字不能为空
);
-- 查看表中的约束
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = STU;
先创建表, 然后再添加约束
- CREATE TABLE STU(
- STUNO NUMBER(3),
- STUNAME VARCHAR2(20)
- );
-- 添加主键约束
ALTER TABLE STU ADD CONSTRAINT PK_STU_STUNO PRIMARY KEY(STUNO);
添加复合主键
-- 创建表
- CREATE TABLE STU(
- STUNO NUMBER(3),
- STUNAME VARCHAR2(20)
- );
-- 添加符合主键
- ALTER TABLE STU ADD CONSTRAINT PK_STU_STUNO PRIMARY KEY(STUNO, STUNAME);
- /*
- 根据主键唯一的性质, 往 STU 里面添加的数据不能有 STUNO 和 STUNAME 同
- 时一致的情况, 但是单个 STU 或 STUNAME 相同时允许的
- */
创建一张表, 并创建主键唯一非空检查约束
CREATE TABLE STUDENT(
STUNO NUMBER(4) CONSTRAINT PK_STU_STUNO PRIMARY KEY, -- 主键约束
SNAME VARCHAR2(10) CONSTRAINT NN_STU_SNAME NOT NULL, -- 非空
SEX VARCHAR2(3) DEFAULT 男, -- 默认值
AGE NUMBER(3) CONSTRAINT CK_STU_AGE CHECK(AGE BETWEEN 18 AND 30), -- CHECK 约束, 添加的年龄必须是 18 到 30 岁的
EMAIL VARCHAR2(20) CONSTRAINT CK_STU_EMAIL CHECK(EMAIL LIKE %@%), -- CHECK 约束, 添加的邮箱中必须有 @符号
PSWD VARCHAR2(10) CONSTRAINT CK_STU_PSWD CHECK(LENGTH(PSWD)>= 6) -- CHECK 约束, 添加的密码必须大于等于 6 位
);
外键
-- 创建一张表作为主表
CREATE TABLE GRADE(
GRADEID NUMBER(2) CONSTRAINT PK_GRADEID PRIMARY KEY, -- 添加主键约束
- GRADENAME VARCHAR2(20) NOT NULL
- );
-- 再创建一张表作为子表
- CREATE TABLE STUDENT(
- STUNO NUMBER(4) CONSTRAINT PK_STU_STUNO PRIMARY KEY,
- SNAME VARCHAR2(10) CONSTRAINT NN_STU_SNAME NOT NULL,
SEX VARCHAR2(3) DEFAULT 男.
- CLASSID NUMBER(10)
- );
- /*
- 添加外键, 将两张表联系起来
- 将 STUDENT 表的 CLASSID 与 GRADE 表的 GRADEID 项建立连接
- 所以 CLASSID 是 STUDENT 表的外键
- */
- ALTER TABLE STUDENT ADD CONSTRAINT FK_STUDENT_CLASSID FOREIGN KEY(CLASSID) REFERENCE GRADE(GRADEID);
来源: http://www.bubuko.com/infodetail-2537953.html