oracle学习篇(二)

oracle学习篇(二)

1添加约束

1.1 添加主键约束

1.1.1 语法

alter table 表名
add constraint 约束名 约束关键字 key(添加约束的字段);

1.1.2 示例代码

alter table infos 
add constraint pk_stuid primary key(stuid);

1.2 添加检查约束

1.2.1 语法

alter table 表名
add constraint ck_约束字段 check(约束条件);

1.2.2 例子

alter table infos
add constraint ck_age check(age between 0 and 100);

1.2.3 若插入的数据不符合格式时,会出现如下所示的错误提示

1.4 说明

1.4.1 说明内容

注意:添加check约束前表里面的数据需要符合check约束
不然就会提示你约束添加不成功
同一个用户名下的约束只能有一个

2 添加数据

2.1 添加单条数据

2.2.1 语法

INSERT INTO 表名 VALUES (列1的值,列2的值...列n的值);

2.2.2 示例代码

INSERT INTO "stu" VALUES ('2','男');

2.2 添加多条数据

2.2.1 语法

INSERT INTO 表名
SELECT 列1的值,列2的值...列n的值 FROM dual
UNION 
SELECT 列1的值,列2的值...列n的值 FROM dual
UNION 
SELECT 列1的值,列2的值...列n的值 FROM dual;
-- 注意 union要求列要一模一样,且一般是按照列名一一在select后面去列举出来

2.2.2 示例代码

INSERT INTO "stu"
SELECT '3','女' FROM dual
UNION 
SELECT '4','男' FROM dual;
-- 注意 "stu"是因为我定义的时候就加上了双引号,用于进行查表时区分大小写

2.2.3 运行截图

3 查询系统时间及把系统时间转换成指定的字符串格式

3.1 示例代码

select sysdate,to_char(sysdate,'day yyyy-mm-dd hh24:mi:ss')from dual;
-- to_char函数是将日期函数转换成指定格式的字符串,
-- 其格式是yyyy-mm-dd代表年-月-日,有12进制和24进制(12进制写hh12就行)

3.2 示例代码运行截图

4 case用法

4.1 类似if…else…if版本

4.1.1 语法

SELECT 列名1,列名2...列名n,case
  WHEN 列名 条件1 then 满足条件1返回的值
  WHEN 列名 条件2 then 不满足条件1,满足条件2返回的值
  WHEN 列名 条件3 then 不满足条件1和条件2,满足条件3返回的值
  else  不满足以上所有条件返回的值 end 列名
  from 表名
  -- 作为列的别名,如果是中文,可以在在双引号里面写中午,也可以省略双引号不写

4.1.2 示例代码

--查询员工编号 姓名、工资、自定义级别
SELECT empno,ename,sal,case
  WHEN sal between 0 and 1000 then '混日子'
  WHEN sal between 1001 and 2000 then '划水的'
  WHEN sal between 2001 and 3000 then '摸鱼的'
  else  '做事的' end 自定义级别
  from emp

4.1.3 运行截图

4.2 类似switch版本

4.2.1 语法

SELECT 列名1,列名2...列名n,case 需要等值判断的字段
  WHEN 列名 条件1 then 满足条件1返回的值
  WHEN 列名 条件2 then 不满足条件1,满足条件2返回的值
  WHEN 列名 条件3 then 不满足条件1和条件2,满足条件3返回的值
  else  不满足以上所有条件返回的值 end 列名
  from 表名
  -- 作为列的别名,如果是中文,可以在在双引号里面写中午,也可以省略双引号不写

4.2.2 示例代码

-- 查询员工姓名、职位、职位别称
SELECT ename,job,case job
when 'SALESMAN' then '耍嘴皮子的'
when 'ALLEN' then '靠脸的'
  when 'MANAGER' then '管事的'
  else '摸鱼的' end 职位别称
FROM emp

4.2.3 运行截图

4.3 总结

如果是要等值连接使用case 等值字段 when then,即采用第二种
如果是范围查找使用case when then...采用第一种

5 查看工资等级表

5.1 示例代码

SELECT e.ename,e.sal,s.grade
FROM emp e
INNER JOIN salgrade s
ON e.sal between s.losal and s.hisal

5.2 示例代码运行截图

6 序列

6.1 原因

因为oracle是非自增型数据库,所以引入了序列进行自动增长

6.2 创建序列

6.2.1 语法

create sequence 序列名;

6.2.2 示例代码

create sequence sq1;
-- 一个用户下的序列名称不能相同,

6.3 查看当前序列值

6.3.1 语法

select 序列名.currval from dual

6.3.2 示例代码

select sq1.currval from dual

6.3.3 运行截图

6.4 查看下一个序列值

6.4.1 语法

select 序列名.nextval from dual

6.4.2 示例代码

select sq1.nextval from dual

6.4.3 运行截图

6.5 使用序列实现字段自增长

6.5.1 单条插入

a 语法

INSERT INTO 表名 VALUES (序列名.nextval,列2的值....列n的值);
-- 序列名.nextval字段需要是int类型字段或者number类型字段

b 示例代码

INSERT INTO "stu" VALUES (s1.nextval,'男');

c 运行结果验证

6.5.2 多条插入

a 语法

-- 先创建触发器
CREATE OR REPLACE TRIGGER 触发器名字
  BEFORE INSERT ON 表名
  FOR EACH ROW -- WHEN (new.giftID is null) 设置主键存在时，不触发触发器
BEGIN
  SELECT 序列名.NEXTVAL INTO:NEW.自增的字段 FROM DUAL;
END;
-- 使用Insert all的语法去进行插入
INSERT ALL  INTO 表名  (非自增字段1,非自增字段2...非自增字段n) VALUES (非自增字段1的值,非自增字段2的值...非自增字段n的值)
           INTO 表名 (非自增字段1,非自增字段2...非自增字段n) VALUES (非自增字段1的值,非自增字段2的值...非自增字段n的值)
           INTO 表名 (非自增字段1,非自增字段2...非自增字段n) VALUES (非自增字段1的值,非自增字段2的值...非自增字段n的值)
SELECT * FROM dual;  

b 示例代码

-- 先创建触发器
CREATE OR REPLACE TRIGGER test1
  BEFORE INSERT ON "stu"
  FOR EACH ROW -- WHEN (new.giftID is null) 设置主键存在时，不触发触发器
BEGIN
  SELECT s1.NEXTVAL INTO:NEW.id FROM DUAL;
END;
-- 使用Insert all的语法去进行插入
INSERT ALL  INTO "stu"  (sex) VALUES ('男')
           INTO "stu" (sex) VALUES ('女')
           INTO "stu" (sex) VALUES ('男')
SELECT * FROM dual;  

c 运行结果验证

7 索引

7.1 索引的初识

按照数据结构划分,常见的索引有B+ tree索引和hash索引
其中B+ tree是范围查找,hash索引是匹配查找
当单个索引的行小于或者等于整个表行数的20%左右的时候索引就非常有用
主键字段是默认加上索引的
每次进行表的数据的更改,索引的空间都会进行相应的变更