Oracle数据库之SQL基础（二）

一、约束

❤ 1、约束概述

约束作用：

　　（1）定义规则

　　（2）确保完整性:包括数据的精确性、可靠性。以确保数据不会出错，或者尽量减少出错。

约束的类型：

　　（1）非空约束

　　（2）主键约束

　　（3）外键约束（右外键约束）（左外键约束）

　　（4）唯一约束

　　（5）检查约束

❤ 2、非空约束

（1）在创建表时设置非空约束
　　语法：CREATE TABLE table_name(column_name datatype NOT NULL,...);
（2）在修改表时添加非空约束
　　语法：ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
　　注：添加非空约束的字段不能有null数据存在。
（3）在修改表时去除非空约束
　　语法：ALTER TABLE table_name MODIFY column_name datatype NULL;

在修改表的时候添加非空约束时，先把原有数据删除：
　　delete from userinfo；
然后再修改表：
　　alter table userinfo modify username varchar2（20） not null；

❤ 3、主键约束

作用：确保表中的每一行数据的唯一性。
设置了主键约束的字段，这个字段必须是非空且值是唯一的；
一个表中只能设置一个主键约束；
一个主键约束可以有多个字段组成(这个被称为联合主键或复合主键)。

在创建表时设置主键约束：

　　（1）在创建表时设置主键约束(列级)
　　　　语法：CREATE TABLE table_name(column_name datatype PRIMARY KEY,...);

创建了主键约束时它会自动加上非空约束。

　　（2）在创建表时设置主键约束（表级）
　　　　语法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT pk_name PRIMARY KEY(column_name1,...));

在修改表时设置主键约束：

　　（1）在修改表时添加主键约束
　　　　语法：ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name1,...);
　　　　注：如果表中有内容时，准备添加主键的字段里的数据必须唯一。

　　（2）更改约束名称

　　　　语法：ALTER TABLE table_name RENAME CONSTRAINT old_pk_name TO new_pk_name;

　　（3）禁用主键约束

　　　　语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT pk_name;

　　（4）删除主键约束

　　　　语法：ALTER TABLE table_name DROP CONSTRAINT pk_name;
　　　　　　　或ALTER TABLE table_name DROP PRIMARY KEY [CASCADE]; [CASCADE]为可选项，在级联操作中使用。

❤ 4、外键约束

在创建表时添加外键约束：外键约束（表示主从表之间的关系）

　　（1）在创建表时设置外键约束（列级）
　　　　语法：CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),...); --其中table2为主表，table1为从表

　　　　注：1）设置外键约束时，主表字段必须为该表的主键字段
　　　　　　2）主从表中相应的字段必须是同一个数据类型（外键约束的字段和主表的主键字段）
　　　　　　3）从表中外键字段的值必须来自主表中相应字段的值，或者为null值

　　（2）在创建表时设置外键约束（表级）
　　　　语法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name2(column_name)[ON DELETE CASCADE]);
　　　　注：1）table_name2为主表名；
　　　　　　2）[ON DELETE CASCADE]表示级联删除的意思；
　　　　　　3）约束的名字也是唯一的。

在修改表时添加外键约束:

　　语法：ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column_name) REFERENCES table_name2(column_name)[ON DELETE CASCADE];

　　注：1）table_name2为主表名；
　　　　2）[ON DELETE CASCADE]表示级联删除的意思；
　　　　3）约束的名字也是唯一的。

❤ 5、删除主键约束

　　（1）查看约束
　　　　语法：select constrain_name,constraint_type,status from user_constraints where table_name='XXXX';
　　　　- status P代表主键 R代表外键

　　

　　（2）禁用外键约束
　　　　语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT fk_name;　　

　　（3）删除外键约束
　　　　语法：ALTER TABLE table_name DROP CONSTRAINT pk_name;

❤ 6、唯一约束

　　作用：保证设置唯一约束的字段值的唯一性

　　唯一约束与主键约束的区别：
　　　（1）主键约束的字段为非空的，而唯一约束只允许有一个值为null
　　　（2）一张表只能有一个主键约束，而唯一约束可以有多个

　　在创建表时设置唯一约束：

　　　　（1）在创建表时设置唯一约束（列级）
　　　　　　语法：CREATE TABLE table_name(column_name datatype UNIQUE,...);

　　

　　　　（2）在创建表时设置唯一约束（表级）
　　　　　　语法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT u_name UNIQUE(column_name));
　　　　　　注：1）唯一约束的名称（u_name）必须唯一；
　　　　　　　　2）如果想设置多个唯一约束字段，需要将子句（CONSTRAINT u_name UNIQUE(column_name)）重复书写即可。

　　　　

　　在修改表时添加唯一约束：

　　　　语法：ALTER TABLE table_name ADD CONSTRAINT u_name UNIQUE(column_name);

　　

　　禁用唯一约束：
　　　　语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT u_name;

　　删除唯一约束：
　　　　语法：ALTER TABLE table_name DROP CONSTRAINT u_name;

❤ 7、检查约束

　　作用：让表中的值更具有实际意义。
　　注：检查约束在一张表中也是可以有多个的。

在创建表时设置检查约束：
　　（1）在创建表时设置检查约束（列级）
　　　　语法：CREATE TABLE table_name(column_name datatype CHECK(expressions),...);
　　　　注：expressions为条件表达式

　　

　　（2）在创建表时设置检查约束（表级）
　　　　语法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT c_name CHECK(expressions));

　　

在修改表时添加检查约束：

　　语法：ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expressions);

　　

禁用检查约束：

　　语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT ck_name;

删除检查约束：

　　语法：ALTER TABLE table_name DROP CONSTRAINT ck_name;

❤ 8、约束小结

• 非空约束 NOT NUL （禁止插入字段为空）
• 主键约束 PRIMARY KEY （每张表只能有一个，可以由多个字段构成）
• 外键约束 FOREIGN KEY （涉及两张表之间的关系，约束字段与外表字段匹配，类型相同，数据必须 IN {外表数据}）
• 唯一约束 UNIQUE （保证数据的唯一性，可以由多字段构成）
• 检查约束 CHECK （保证数据值的安全可靠，并允许范围内）

在创建表时设置约束：

　　只有非空约束是只有列级设置且非空约束是没有名字的，其他约束即可以列级设置也可以表级设置。

在修改表时添加约束：

　（非空）alter table table_name modify column_name datatype not null;

　　只有非空约束是用修改字段的方式添加约束的，其他的雷同。

更改约束的名称：

　　非空约束是没有名字的，

　　所以其他的约束都可以通过以下语法修改：ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;可以通过数据字典（user_constraints）来查看。

禁用约束：

　　非空约束不需要禁用，

　　其他约束的禁用语法是：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT constraint_name;可以通过数据字典（user_constraints）来查看。

删除约束：

　　非空约束的删除还是通过修改字段的方式来删除，即在字段类型后加个NULL即可，

　　其他的都可以通过语法：ALTER TABLE table_name DROP CONSTRAINT constraint_name;来删除；

　　主键约束还可以通过语法：ALTER TABLE table_name DROP CONSTRAINT PRIMARY KEY;来删除；

　　可以通过数据字典（user_constraints）来查看。

练习知识点：

　　Oracle中规定一张表中只能有一个主键，因此主键约束只能有一个。

　　外键约束涉及到两个表，比如部门表dept包括部门编号（deptno），员工表employee中的部门（deptnum）作为外键参照部门表中的deptno字段。

　　唯一约束要求每个字段不能有重复值，可以有空值，但是空值只能有一个。

二、查询语句

❤ 查询概述

• 查询基本语句；
• 在SQL*PLUS中设置格式（对查询结果的格式设置）；
• 查询表中的所有字段及指定字段；
• 给字段设置别名（针对查询结果进行的，并没有更改字段的名字）；
• 运算符和表达式；
• 在select语句中使用运算符；
• 带条件的查询；
• 模糊查询；
• 范围查询；
• 对查询结果排序；
• case...when语句的使用（条件语句）；
• decode函数的使用

❤ 1、基本查询语句

　　语法：SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions];

　　即：Select [Distinct] 列名 .. From 表名 （使用 distinct 不显示重复数据）

❤ 2、在SQL*PLUS中设置格式

　　作用：配合select语句使用，设置查询结果显示的格式，不会修改数据库表字段。

　　（1）设置字段显示样式
　　　　语法：COLUMN column_name HEADING new_name;
　　　　注：COLUMN 可以简写成COL

　　

　　（2）设置字段的格式化样式
　　　　语法：COLUMN column_name FORMAT dataformat;
　　　　注：如果是字符类型的值，只能设置它显示的长度。其中a后面跟数字表示字符串显示长度，例：a10 表示显示10位的长度的字符串；数字则用‘9’代表一位，例：999.9 表示整数位为三位，小数位为一位。如果设置的长度超出数据最大长度，则用‘#’显示。还可以在数字前面加‘$’,例：$999.9 则查询后显示的结果会在结果前加上'$'符。

　　

　　

　　

　　

　　（3）清除设置格式
　　　　语法：COLUMN column_name CLEAR;

　　

❤ 3、查询表中的所有字段及指定字段

　　（1）查询所有字段：

　　　　语法：select * from table_name;

　　

设置格式：

　　

　　（2）查询指定字段：
　　　　语法：select column1, ... from table_name;

　　

❤ 4、给字段设置别名

　　语法：SELECT column_name AS new_name,... FROM table_name;
　　其中：as是可以省略的，用空格来隔开原字段名和新字段名即可。
　　注释：
　　　　1）column(col) col_name heading new_name;//设置的是字段的查询结果的名字
　　　　2）column(col) col_name format datatype;//设置的是字段的查询结果的类型范围
　　　　3）column(col) col_name clear; //清除所设置的格

❤ 5、运算符和表达式

　　（1）表达式 = 操作数 + 运算符
　　（2）oracle中的操作数可以是常量、变量和表当中的字段
　　（3）oracle中的运算符：

　　　　　　　　算数运算符（+ - * /）；

　　　　　　　　比较运算符(> < = >= <= <>)；（如5>2结果为true，布尔类型 ）

　　　　　　　　逻辑运算符(and，or， not)

　　　　　　　　

在select语句中使用运算符：

　　（1）使用算数运算符

　　

所有工资结果加200，但并不会影响表中的数据。（表中数据进行算术运算后，数据值并不会正真的被改变，改变需要使用update语句）

　　（2）使用比较运算符

　　

注释：不等于用！=或者<>表示

❤ 6、带条件的查询（where语句）

（1）单一条件查询

（2）多条件的查询(利用逻辑运算符来连接表达式)

例：查询员工姓名是aaa，或者工资大于2000的员工信息

可以注意：运算符是有优先级的。

• 逻辑运算符的优先级：按not、and、or的顺序依次递减
• 比较运算符的优先级高于逻辑运算符

❤ 7、模糊查询

（1）通配符（'_','%'）的使用:

　　一个‘_’代表一个字符，一个‘%’代表0到多个任意字符。

（2）使用LIKE查询：

❤ 8、范围查询

（1）between……and

　　between ～ and ～查询的区间包含两个范围限制值；
　　not between ～and～ 查询不包含在此范围的值；

　　范围查询，如查找工资在800到2000的(包括了800和2000)：
　　　　select * from users where salary between 800 and 2000;
　　如果想要不包括800和2000这两个数，就可以这样写：
　　　　select * from users where salary not between 800 and 2000;

（2）in/not in

　　select * from users where username in('aaa','bbb');
　　select * from users where username not in('aaa','bbb');

❤ 9、对查询结果进行排序

语法：SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions] ORDER BY column_name1 DESC|ASC,...
　　其中，DESC是降序排序，ASC是升序排序，默认情况下是升序排序

❤ 10、case...when语句

基本语法：([when后面跟的是判断的条件，then是条件为真是显示出来的东西])
语法1：CASE column_name WHEN value1 THEN result1, ... [ELSE resultn] END;
语法2：CASE WHEN column_name=value1 THEN result1, ...[ELSE resultn] END;
注：value1、result1需要加单引号,表示字符串，当多个when...then...使用时，中间用空格隔开即可，不能用逗号隔开

语法1示例：

语法二示例：

❤ 11、decode函数的使用

与前面的case ...when .. then (else) end 类似

语法：DECODE(column_name,value1,result1,...,defaultvalue);
（ decode与case ...when .. then (else) end 类似，但decode的条件指定为==的意思）
解释：Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）From talbename;