oracle之子查询、创建用户、创建表、约束

子查询

子查询可以分为单行子查询和多行子查询

单行子查询

　　[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的条件使用

 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) REFERENCES GRADE(GRADEID);
21 /* 删除外键 */
22 ALTER TABLE STUDENT DROP CONSTRAINT FK_STUDENT_CLASSID;

利用触发器和序列让列自增

-- 首先创建一张表
CREATE TABLE album(
    id INTEGER CONSTRAINT PK_abbum_id PRIMARY KEY,  -- 设置主键
    title VARCHAR2(255),
    image VARCHAR2(255),
    content VARCHAR2(255),
    notice VARCHAR2(255),
    time VARCHAR2(255)
);

-- 创建自增长序列
CREATE SEQUENCE album_id_increace
MINVALUE 1
MAXVALUE 9999999
INCREMENT BY 1
START WITH 1;

-- 创建触发器
CREATE OR REPLACE TRIGGER album_id_tri
BEFORE INSERT ON album    -- 触发条件：当向表中插入一条数据触发
FOR EACH ROW              -- 对每一行都检测是否触发
BEGIN
  SELECT album_id_increace.Nextval    -- 获取序列的值
  INTO :NEW.ID FROM dual;                  -- 将该值插入到album表的id项
END;

-- 提交
COMMIT;