你的位置:首页 > 数据库

[数据库]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;