数据库 oracle
一、Oracle简介
|
一个认知：Oracle 两个概念： 数据库 关系型数据库 三个名词： sql pl/sql sqlplus 四个对象： table view index sequence -----synonym（同义词） program unit 五种操作： 1.数据检索：Data retrieval select 2.DML 操作语言 insert update delete 3.DDL 定义语言 create alter drop rename truncate（截断表） 4.事务控制： commit rollback savepoint（保存回滚点） 5.DCL grant（设置权限） revoke（取消权限） SDL:软件开发实验室（Software Development Laboratories，SDL） oracle:甲骨文 IBM:Eclipse BEA:Weblogic，tomcat SUN:JBuilder，Netbeans，IDE DB:DataBase,RDBMS DB2,Sybase,Mysql,sql servler 甲骨文公司主要的产品目前分为两大类： 1．服务器（服务器）及工具（主要竞争对手：IBM、微软） * 数据库服务器：2007年最新版本Oracle 11G * 应用服务器：Oracle Application Server * 开发工具：Oracle JDeveloper，Oracle Designer，Oracle Developer，等等 2．企业应用软件（主要竞争对手：德国SAP公司。） * 企业资源计划(ERP)软件。已有10年以上的历史。2005年，并购了开发企业软件的仁科软件公司（PeopleSoft）以增强在这方面的竞争力。 * 客户关系管理(CRM)软件。自1998年开始研发这种软件。2005年，并购了开发客户关系管理软件的希柏软件公司（Siebel） |
二、操作命令
2.1基本操作命令
五种sql分类:
1:数据查询语句
select
2:数据操作语句
insert,delete,update
3:数据定义语句
create,alter,drop,
rename,truncate
4:事物控制的语句
commit,rollback,savepoint
5:权限有关的语句
grant,revoke
2.2登录操作
|
登录到sql*plus这种工具的方式 1：sqlplus 用户名/密码 2：sqlplus 用户名 密码 3：打开运行sql命令行 conn 用户名/密码 清屏:clear screen----clear scr 查看当前用户:show user 创建用户: create user 用户名 identified by 密码 赋予权限(高用户给低用户) grant resource,connect to 用户名 切换用户： conn 用户名/密码 查询当前系统时间 select select sysdate from dual; 查询当前用户的表 select table_name from user_tables; 导入数据 start 盘符:/summit2.sql; 在查表之前必须先导入包 @ D:/oracleXEUniv/summit2.sql; 读取这个文件下的.sql文件 sqlplus命令: 1.login： 系统终端:>sqlplus briup/briup; sqlplus "/as sysdba" SQL:>conn briup/briup; exit; 2.显示表结构： desc table_name;
|
2.3注意事项
|
1）start c:/summit2.sql 导包 2）选择table内容 3)协议适配器错误：需要开启oracle服务 4)查看表desc s_emp 5)终端右键复制：终端---右键--->属性 --->快速编辑模式 6）distinct 去除重复项 7）每一列区别用逗号 8）除了起别名是双引号其他的都是单引号 ||连接符 两个连接符号之间可以用任何喜欢的分割符号分开 用单引号 9）任何数和null相加=null 10）当出现distinct后面两个项时当两个同时重复时才认为是重复项 11）”/ “表示执行 当出现错误时可以将其用 c/ 原来的/现在的 再用“/”执行上次的sql语句：/ ***l 查询上一条语句 ***a 追加（此时的空格至少两个，不然会连接在一起，第一个表示分割，第二个是真正空格的内容）当出现少写的时候使用。 ***del 代表删除某一行的内容，首先根据行数选定错误的行在使用del进行删除，l 查询上一个语句 “/”执行 *** i 进行插入 指定到出错的位置，根据i（空两格）写正确格式 select dept_id ,title from s_emp; 替换： “2:” 先定位到错误的行 c/emps/emp *** spool某一个文件再通过执行命令spool off ---- get c:/b.txt得到文件的内容 edit c:/b.txt编辑 > save c:/b.txt start c:/b.txt启动程序 *************** 表是由列和行构成 列：一条数据 行：表拥有的字段 每一张表都有主键和外键 主键：非空唯一 外键：必须和另一张表的主键关联 *********************************** |
2.4Colunm命令
|
SQL> colu *********查看当前的格式 COLUMN salary ON FORMAT l99,999.99
COLUMN last_name ON FORMAT a10
COLUMN result_plus_xquery ON HEADING 'Result Sequence'
COLUMN other_plus_exp ON FORMAT a44
COLUMN other_tag_plus_exp ON FORMAT a29
COLUMN object_node_plus_exp ON FORMAT a8
COLUMN plan_plus_exp ON FORMAT a60
COLUMN parent_id_plus_exp ON HEADING 'p' FORMAT 990
COLUMN id_plus_exp ON HEADING 'i' FORMAT 990
COLUMN droptime_plus_show_recyc ON HEADING 'DROP TIME' FORMAT a19
COLUMN objtype_plus_show_recyc ON HEADING 'OBJECT TYPE' FORMAT a12
COLUMN objectname_plus_show_recyc O HEADING 'RECYCLEBIN NAME' FORMAT a30
COLUMN origname_plus_show_recyc ON HEADING 'ORIGINAL NAME' FORMAT a16
COLUMN value_col_plus_show_param ON HEADING 'VALUE' FORMAT a30
COLUMN name_col_plus_show_param ON HEADING 'NAME' FORMAT a36
COLUMN units_col_plus_show_sga ON FORMAT a15
COLUMN name_col_plus_show_sga ON FORMAT a24
COLUMN ERROR ON FORMAT A65 word_wrap
COLUMN LINE/COL ON FORMAT A8
COLUMN ROWLABEL ON FORMAT A15 |
|
SQL> colu last_name format a10; 变成一行 SQL> select last_name,title 2 from s_emp; LAST_NAME TITLE ---------- --------------------- Velasquez President Ngao VP, Operations Nagayama VP, Sales Quick-To-S VP, Finance ee |
|
SQL> colu salary format $99,999.99 *************salary的格式 SQL> select salary,last_name 2 from s_emp;
SALARY LAST_NAME ----------- ---------- $2,500.00 Velasquez $1,450.00 Ngao $1,400.00 Nagayama $1,450.00 Quick-To-S ee |
|
SQL> colu salary format l99,999.99 ****salary变成人民币形式 SQL> select salary,last_name 2 from s_emp;
SALARY LAST_NAME -------------------- ---------- ￥2,500.00 Velasquez ￥1,450.00 Ngao ￥1,400.00 Nagayama ￥1,450.00 Quick-To-S
|
|
SQL> colu last_name clear ***********清除刚刚创建的格式 SQL> colu COLUMN salary ON FORMAT l99,999.99
COLUMN result_plus_xquery ON HEADING 'Result Sequence' |
|
SQL> colu salary *************查看当前创建的格式 COLUMN salary ON FORMAT l99,999.99 |
2.5 order by（默认升序）
|
SQL> select last_name,salary 按照升序排列 2 from s_emp 3 order by salary; |
|
QL> select dept_id,salary 2 from s_emp 3 order by dept_id,salary desc; 当部门相同时按照salary的降序排列 |
|
***引号引起来的部分大小写敏感，书写的是哪个就是那个*********** |
|
SQL> select last_name,dept_id 2 from s_emp 3 where last_name='Ngao'; -------*** where last_name='ngao'; 未选定行 没有此人 大小写敏感
LAST_NAME DEPT_ID -------------------------------------------------- ---------- Ngao 41 |
2.6 Between......and (范围)
*************between 小 and 大*******
|
SQL> select last_name,salary 2 from s_emp 3 where salary between 500 and 1000;
LAST_NAME SALARY -------------------------------------------------- -------------------- Smith ￥940.00 Patel ￥795.00 Newman ￥750.00 Markarian ￥850.00 |
2.7字符简单查询
|
In 在其中 Like模糊查询 %:代表0或者多个字符 _:代表1个字符 escape:可以将_或者%自己本身的意思显示出来 In 的操作 ***************** in在此范围内选择 in 只能取in中包含的数据值,不是范围值 查询区域id在1或者3的部门信息 select id,name,region_id from s_dept where region_id in (1,3); ************************************* |
|
SQL> select id ,last_name 2 from s_emp 3 where id in(1,10,24);
ID LAST_NAME ---------- --------------------- 1 Velasquez 10 Havel 24 Dancs |
|
“%” 操作 |
|
SQL> select last_name 2 from s_emp 3 where last_name like 'M%'; ***** 只能以M开头 ************where last_name like '%M%'; *********只要里面有M就可以 LAST_NAME ---------------------------------- Menchu Magee Maduro Markarian |
|
更改日期 |
|
SQL> alter session set nls_date_language=english； 会话已更改。 |
|
创建 SQL> insert into s_dept values(11,'_briup',1); 已创建 1 行。 SQL> commit 提交完成。**********commit会使得其永远存在 |
|
“_” 唯一一个 |
|
SQL> select name 2 from s_dept 3 where name like '\_%' escape '\';********转义前面是_的name
NAME -------------------------------------------- _briup SQL> select name 2 from s_dept 3 where name like '_%'; ******* 至少有一个 只要有就会出现 NAME --------------------------------- Administration Finance Operations Operations Operations Operations Operations Sales Sales Sales |
|
不等号 != <> ^= |
|
优先级 and--->or 最好是加个括号 不需要优先级 |
|
SQL> select last_name,dept_id,salary 2 from s_emp 3 where dept_id = 44 4 or salary >100 5 and dept_id = 42;
LAST_NAME DEPT_ID SALARY -------------------------------------------------- ---------- ---------- Menchu 42 1250 Catchpole 44 1300 Nozaki 42 1200 Patel 42 795 Chang 44 800
|
2.8单值函数
|
lower:转换成小写 upper:转换成大写 initcat:首字母初始化 concat:字符串的合并 substring:字符串的分割 length:字符串的长度 round:四舍五入 truncate:舍去 mod:取余
|
|
小写 SQL> select lower('Helloworld') from dual； upper大写 LOWER('HELLOWORLD') -------------------- helloworld |
|
分割 SQL> select substr('HelloWorld',1,3) from dual; 1 位置，代表 3代表后面的几位 SUBSTR ------ Hel |
|
拼接 SQL> select concat('Hello','world') from dual; CONCAT('HELLO','WORL） -------------------- Helloworld |
|
SQL> select initcap('HelloWorld') from dual; ******8首字母大写
INITCAP('HELLOWORLD' -------------------- Helloworld
SQL> select length('HelloWorld') from dual;
LENGTH('HELLOWORLD') -------------------- 10 |
|
|
|
Round和trunc的区别 （ 前者需要看后面的进位，后者则不需要直接舍去） |
|
SQL> select round(45.946, 2) from dual;
ROUND(45.946,2) --------------- 45.95
SQL> select trunc(45.946, 2) from dual;
TRUNC(45.946,2) --------------- 45.94 |
|
取余 SQL> select mod(1700,300) from dual; MOD(1700,300) ------------- 200
SQL> |
|
SQL> select round(sysdate,'month') from dual;
ROUND(SYSDAT ------------ 01-MAY-16 SQL> select trunc(sysdate,'month') from dual;
TRUNC(SYSDAT ------------ 01-APR-16 |
|
时间格式转换 |
|
SQL> select to_char(sysdate,'YYYY-MM-dd') from dual; TO_CHAR(SYSDATE,'YYY -------------------- 2016-04-17 |
|
SQL> select to_char(sysdate,'YYYY-MM-dd HH24:MI:SSAM') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SSAM') ------------------------------------------ 2016-04-17 17:26:06PM |
|
SQL> select to_char(sysdate,'YY-MM-ddsp HH24:MI:SSAM') from dual; TO_CHAR(SYSDATE,'YY-MM-DDSPHH24:MI:SSAM') ---------------------------------------------------------- 16-04-seventeen 17:27:06PM |
|
To_char的使用 |
|
SQL> select last_name,to_char(salary,'$99,999.99') 2 from s_emp;
LAST_NAME TO_CHAR(SALARY,'$99, -------------------------------------------------- -------------------- Velasquez $2,500.00 Ngao $1,450.00 Nagayama $1,400.00 |
|
to_number |
|
SQL> select to_number('10') from dual;
TO_NUMBER('10') --------------- 10 |
|
|
|
|
|
|
三、多表查询
1.定义
|
其实质也是单表的查询,将我们的多张表通过一定的条件连接成一张表 连接的时候产生笛卡儿积：连接的方式：等连接 查询所有员工的ID， s_emp 名字和所在部门的名称 s_dept |
|
SQL> colu last_name format a10; SQL> select e.last_name,d.id,d.name 2 from s_emp e,s_dept d 3 where e.dept_id = d.id;
LAST_NAME ID NAME ---------- ---------- --------------------- Velasquez 50 Administration Ngao 41 Operations Nagayama 31 Sales Quick-To-S 10 Finance ee |
|
查询名字及所在的部门和所在区域 |
|
SQL> select e. last_name,d.name,r.name 2 from s_emp e,s_dept d,s_region r 3 where e.dept_id=d.id and d.region_id=r.id; |
|
查询欧洲销售部门的薪水在1000-2000的员工信息 |
|
错误： SQL> select e.salary,d.name,e.last_name from s_emp e,s_dept d and e.dept_id=d.id and e.salary !=1500 and length(d.name)=5 order by e.salary desc; and e.dept_id=d.id * 第 3 行出现错误: ORA-00933: SQL 命令未正确结束 |
|
正解：SQL> select e.last_name,e.salary 2 from s_emp e,s_dept d 3 where length(d.name)=5 4 and e.salary!=1500 5 and e.dept_id = d.id 6 order by e.salary desc;
LAST_NAME SALARY ---------- ---------- Nguyen 1525 Sedeghi 1515 Giljum 1490 Dumas 1450 Nagayama 1400 Magee 1400 Patel 795
已选择7行。 |
2.Full join 两张表及时没有关联也可以连接起来
|
Full join 两张表及时没有关联也可以连接起来 例：full join ...on select d.name,e.last_name from s_emp e full join s_dept d on e.dept_id=d.id 标准的sql语句： full join....on... left join...on... right join....on... |
|
SQL> select e.last_name,d.name 2 from s_emp e full join s_dept d 3 on e.dept_id = d.id;
LAST_NAME NAME ---------- ---------------------------------- Velasquez Administration Ngao Operations Nagayama Sales Quick-To-S Finance ee
Ropeburn Administration Urguhart Operations Menchu Operations Biri Operations Catchpole Operations
|
|
SQL> select e.last_name , d.name 2 from s_emp e left join s_dept d 3 on e.dept_id=d.id;
LAST_NAME NAME ---------- ----------- Markarian Operations Chang Operations Patel Sales Dancs Operations Schwartz Operations tom |
|
自连接：在同一张当作两张表来使用 查询员工的上级的信息 select manger.last_name,manger.id from s_emp worker,s_emp manger where worker.manager_id=manger.id(+) 如果外键为null, 外连接： 左外连接 select .. from table1,table2 where table1.colum =table2.colum(+) .... 右外连接 select .. from table1,table2 where table1.colum(+) =table2.colum |
|
查询员工所在部门的信息，包括没有部门号的员工 select d.name,e.last_name from s_emp e,s_dept d where e.dept_id=d.id(+) |
四、组函数
|
讲一组数据处理完之后返回一条记录，某一列相等的值进行分组计算 avg() sum() max() min() count() |
|
平均数 求和 最大值 最小值 总计 |
|
查询销售部的人数最大工资最小工资 |
|
SQL> select avg(salary),count(*),max(salary),min(salary) 2 from s_emp 3 where lower(title) like 's%';
AVG(SALARY) COUNT(*) MAX(SALARY) MIN(SALARY) ----------- ---------- ----------- ----------- 1124.66667 15 1525 750 |
1.Group by 分组
|
Group by 分组 |
|
SQL> select dept_id,count(*) 2 from s_emp 3 where dept_id = 41; select dept_id,count(*) * 第 1 行出现错误: ORA-00937: 不是单组分组函数 |
|
Select 语句中如果有组函数和非组函数，那么其他的非组函数要放到group by中 |
|
SQL> select dept_id,count(*) 2 from s_emp 3 where dept_id = 41 4 group by dept_id;
DEPT_ID COUNT(*) ---------- ---------- 41 4 |
|
SQL> select dept_id ,Avg(salary) 2 from s_emp 3 where Avg(salary)>300 4 group by dept_id; where Avg(salary)>300 * 第 3 行出现错误: ORA-00934: 此处不允许使用分组函数 |
|
组函数的条件必须放在having 语句中 且having在group by的后面 |
|
SQL> select dept_id,title,count(*) 2 from s_emp 3 group by dept_id ,title;
DEPT_ID TITLE COUNT(*) ---------- -------------------------------------------------- ---------- 50 President 1 42 Warehouse Manager 1 45 Stock Clerk 2 1 |
|
当组函数中出现两个以上的非组函数，先按照dept_id分再用title来分 |
2.子查询
|
一条sql语句中嵌套了一条或多条sql语句子查询的结构： 子查询出现情况一： 比较值不确定，需要另外 一个select语句执行后 才能得到，使用子查询 select... from... where columName 操作符 （ select.. from.. where.. group by.. having... order by.. ） group by.. having ... order by..
|
|
子查询的一般思路： |
|
查询和Ngao在同一个部门的员工id,name 第一步：分析需求 s_emp 第二步：查询Ngao所在的部门 select dept_id from s_emp where last_name='Ngao' 第三步，嵌套 select id,last_name,dept_id from s_emp where dept_id=( select dept_id from s_emp where last_name='Ngao' ); |
|
2.子查询实例 |
|
作业： |
|
练习： 1.查看薪资大于chang员工薪资的员工信息（21行） select salary,last_name from s_emp where salary>(select salary from s_emp where last_name = 'Chang')
2.查看薪资大于chang员工薪资或者所在部门在3号区域下的员工的信息（22行） select last_name from s_emp where (salary > (select salary from s_emp where last_name = 'Chang')) or dept_id in (select id from s_dept where region_id = 3);
3.查看薪资大于chang所在区域平均工资的员工信息（16行） select last_name from s_emp where salary > (select Avg(salary) from s_emp e,s_dept d where e.dept_id = d.id and region_id = (select region_id from s_dept d , s_emp e where e.dept_id = d.id and last_name = 'Chang'));
4.查看薪资高于chang员工经理薪资的员工信息（12行） select last_name from s_emp where salary > (select salary from s_emp where id = (select manager_id from s_emp where last_name = 'Chang'));
*********员工的manager id 相当于是manager 的id
5.查看薪资大于chang员工经理的经理所在区域的最低工资的员工的信息(18行） select last_name from s_emp where salary > (select min(salary) from s_emp e ,s_dept d where e.dept_id = d.id and region_id = (select region_id from s_dept where id =(select dept_id from s_emp where id = (select manager_id from s_emp where id = (select manager_id from s_emp where last_name='Chang'))))); *************************************************************************
6.查看客户负责员工中工资大于chang员工的工资的员工信息（5行） select distinct e.id from s_customer c,s_emp e where e.id = c.sales_rep_id and e.salary > (select salary from s_emp where last_name = 'Chang');
7.查看chang员工所在部门其他员工薪资总和(1300) select sum(salary) from s_emp where dept_id =(select dept_id from s_emp e,s_dept d where e.dept_id = d.id and last_name = 'Chang') and (last_name != 'Chang');
SUM(SALARY) ----------- 1300
8.统计不由11号和12号员工负责的客户的人数 8 s_ord select count(*) from s_customer where sales_rep_id not in(11,12); COUNT(*) --------- 8
9.查看部门平均工资大于chang所在部门平均工资的部门信息(10行）
select name from s_dept where id =(select id from s_dept d,s_emp e e.dept_id = d.id having avg(salary) >(select avg(salary) from s_emp where dept_id =(select dept_id from s_emp where last_name = 'Chang') ) ) ; ******************************************************************* select dept_id,avg(salary) from s_emp having avg(salary)> (select avg(salary) from s_emp where dept_id=( select dept_id from s_emp where last_name='Chang')) group by dept_id;
10.查看员工的id，last_name，salary，部门名字，区域名字，这些员工有如下条件：薪资大于chang所在区域的平均工资或者跟chang员工不在同个部门(24)
select e.id,e.last_name,e.salary,d.name,r.name from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id=r.id and (d.id != (select dept_id from s_emp where last_name ='Chang') or e.salary> (select avg(salary) from s_emp e,s_dept d where e.dept_id = d.id and d.region_id = (select region_id from s_dept d,s_emp e where e.dept_id = d.id and e.last_name ='Chang'))); |
|
总结： |
|
1） SQL> select distinct e.id 2 from s_customer c,s_emp e 3 where e.id = c.sales_rep_id and e.salary > （select salary from s_emp where last_name = 'Chang'）; where e.id = c.sales_rep_id and e.salary > （select salary from s_emp where last _name = 'Chang'） * 第 3 行出现错误: ORA-00911: 无效字符 |
|
中文状态下的括号错误 |
|
2） select last_name from s_emp where (salary > (select salary from s_emp where last_name = 'Chang')) or dept_id = (select id from s_dept where region_id = 3); ORA-01427: 单行子查询返回多个行 |
|
select last_name from s_emp where (salary > (select salary from s_emp where last_name = 'Chang')) or dept_id in (select id from s_dept where region_id = 3); |
|
原因是在三号区域下有很多的部门 |
|
3） 使用多表时一定要记得连接各个表 e.dept_id=d.id and d.region_id=r.id |
|
4） SQL> select e.id ,e.last_name,e.salary ,d.name,r.name 2 from s_emp e,s_dept d,s_region r 3 where e.dept_id=d.id and d.region_id=r.id 4 and (e.salary > (select avg(salary) 5 from s_emp where dept_id =(select dept_id 6 from s_emp where last_name = 'Chang'))) 7 or (select dept_id 8 from s_emp where last_name !='Chang'); and (e.salary > (select avg(salary) * 第 4 行出现错误:s ORA-00936: 缺失表达式 |
|
************************************************************ |
3.查询约束条件
|
SQL> select constraint_name 2 from user_constraints;
CONSTRAINT_NAME -------------------------------------- FRIEND_GENDER_CK SYS_C004109 S_INVENTORY_WAREHOUSE_ID_FK S_INVENTORY_PRODUCT_ID_FK S_WAREHOUSE_REGION_ID_FK S_WAREHOUSE_MANAGER_ID_FK S_ITEM_PRODUCT_ID_FK S_PRODUCT_LONGTEXT_ID_FK S_PRODUCT_IMAGE_ID_FK S_ORD_SALES_REP_ID_FK S_ORD_CUSTOMER_ID_FK 好处：不会重复 那张表哪个列的缩写以及约束条件 |
4.创建表
|
create table husband (id number(7) constraint husband_id_pk primary key, name varchar2(10) constraint huaband_name_nn not null, gender varchar2(10) constraint husband_gender_ck check (gender in('male','female')));
|
|
create table wife (id number(7) constraint wife_id_pk primary key, name varchar2(10) constraint wife_name_nn not null, hus_id number(7) constraint wife_hus_id_fk references husband(id));
|
|
********create table table_name(属性 数据类型（指定长度（一个代表一个数字，三个代表一个字符））约束声明 table_name_属性_约束名简称 约束名) |
|
NO |
类型 |
作用 |
|
1 |
VARCHAR2（n） |
表示字符串，其中n表示最大的长度，一般保存长度比较小的内容。 （例：姓名、地址、邮政编码、电话。200字以内都建议使用VARCHAR2） |
|
2 |
NUMBER（n） |
NUMBER（n）：表示整数数据，最多不超过n个长度。可以使用INT代替。 |
|
NUMBER（n，m）：表示小数位占m位，整数位占n－m位。 |
||
|
3 |
DATE |
保存日期时间数据 |
|
4 |
CLOB |
大文本数据，最多可以保存4G数据（例如：保存《红楼梦》）
|
|
5 |
BLOB |
二进制数据，最多可以保存4G内容。可以保存文字、图片、音乐、电影。（很少用到） |
|
SQL> create table wife (id number(7) constraint wife_id_pk primary key, 2 name vaarchar2(10) constraint wife_name_nn not null, 3 hus_id number(7) constraint wife_hus_id_fk references husband(id)); name vaarchar2(10) constraint wife_name_nn not null, * 第 2 行出现错误: ORA-00907: 缺失右括号 |
|
原因：字母写错了 |
|
SQL> select constraint_name 2 from user_constraint 3 where table_name = 'EMP_41'; from user_constraint * 第 2 行出现错误: ORA-00942: 表或视图不存在 |
|
是user_comstraints |
|
插入数据在表里面： |
|
insert into husband values(1,‘liurenquan’,’male’); insert into husband values(1,'baoyu','male'); |
|
SQL> insert into husband values(1,'baoyu','male'); insert into husband values(1,'baoyu','male') * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (BRIUP.HUSBAND_ID_PK) 不能创建两个同样id号的值 |
|
另一种构建表内容的方式： insert into husband values(&husband_id,'&husband_name','male');
SQL> insert into husband values(&husband_id,'&husband_name','male'); 输入 husband_id 的值: 3 输入 husband_name 的值: tom 原值 1: insert into husband values(&husband_id,'&husband_name','male') 新值 1: insert into husband values(3,'tom','male')
已创建 1 行。 |
5.更新：update
|
SQL> update emp_41 2 set salary = 2020 3 where id = 19;
已更新 1 行。
SQL> select * from emp_41;
ID LAST_NAME SALARY ---------- -------------------------------------------------- ---------- 2 Ngao 1450 6 Urguhart 1200 16 Maduro 1400 17 Smith 940 7 Menchu 1250 18 Nozaki 1200 19 Patel 2020
已选择7行。
SQL> update emp_41 2 set last_name = 'tom',salary = 17989;
已更新7行。
SQL> select * from emp_41;
ID LAST_NAME SALARY ---------- -------------------------------------------------- ---------- 2 tom 17989 6 tom 17989 16 tom 17989 17 tom 17989 7 tom 17989 18 tom 17989 19 tom 17989
已选择7行。 |
更新外键
|
SQL> Update wife set hus_id = 3 2 Where id = 2;
已更新 1 行。 SQL> select * from wife;
ID NAME HUS_ID ---------- -------------------- ---------- 1 cehnxue 2 baoyu 3 |
删除
|
SQL> delete from emp_41 2 where id > 10;
已删除4行。
SQL> select * from emp_41;
ID LAST_NAME SALARY ---------- -------------------------------------------------- ---------- 2 tom 17989 6 tom 17989 7 tom 17989
SQL> delete from emp_41;
已删除3行。 |
更新外键的时候一定要注意：
|
先删除外键再删除主键表： SQL> delete from husband; delete from husband * 第 1 行出现错误: ORA-02292: 违反完整约束条件 (BRIUP.WIFE_HUS_ID_FK) - 已找到子记录 主键表里面会报错，因为它里面关联了外键 |
6. ROLLBACK、 COMMIT、SAVEPOINT
想要持久化操作使用commit。主要是防止数据操作的无效性，保存建立的表
当发现自身的操作有错误的时候可以使用rollback
Savepoint a；savepoint 一个点，到想要回到原来的地方可以使用rollback to a
|
SQL> insert into husband values (5,'tom',' 已创建 1 行。 SQL> savepoint a; 保存点已创建。 SQL> update husband 2 set name = 'xue' 3 where id = 2; 已更新 1 行。 SQL> savepoint b; 保存点已创建。 SQL> rollback to a; 回退已完成。 |
7.alter命令
在生活中很多时候会发现表中会临时需要再添加一些内容或者需要改正或者删除某个属性，此时需要使用到alter
注意alter 如同commit
|
Alter table husband Add age number(7) default 18; |
发现当初gender设置错误，打算改正,直接可以将gender删除
|
Alter table husband Drop column gender; |
|
SQL> alter table husband 2 add gender varchar2(10) constraint husband_gender_ck check(gender in('male')); |
8.Modify更改
数据类型
|
Alter table husband Modify gender char(5); |
9.Drop
Drop emp_41;
删除操作，已经commit 就不会再存在
|
SQL> Alter table husband 2 Drop constraint husband_name_nn; Drop constraint husband_name_nn * 第 2 行出现错误: ORA-02443: 无法删除约束条件 - 不存在的约束条件 |
删除级联删除 删除约束 cascade
|
SQL> drop table husband cascade constraint; 表已删除。 |
10.重命名表 rename
|
SQL> rename wife to wife2;
表已重命名。 |
11.delete和truncate
|
SQL> delete wife2; 已删除 1 行。 SQL> rollback; 回退已完成。 SQL> select * from wife2;
ID NAME ---------- -------------------- ---- 1 rose SQL> truncate table wife2; 表被截断。 SQL> rollback; 回退已完成。 SQL> select * from wife2; 未选定行 |
Delete回滚还可以有值，但是truncate 回滚不会回到原来的位置
五、Sequence 序列
在Oracle数据库中，什么是序列呢？它的用途是什么？序列(SEQUENCE)其实是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [] [{CACHE n|NOCACHE}];
INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。
CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
删除序列的语法是：
DROP SEQUENCE 序列名;
其中：
删除序列的人应该是序列的创建者或拥有DROP ANY SEQUENCE系统权限的用户。序列一旦删除就不能被引用了。
序列的某些部分也可以在使用中进行修改，但不能修改SATRT WITH选项。对序列的修改只影响随后产生的序号，已经产生的序号不变。
控制表里面的id
|
SQL> select sequence_name 2 from user_sequences;
SEQUENCE_NAME -------------------------------- S_CUSTOMER_ID S_DEPT_ID S_EMP_ID S_IMAGE_ID S_LONGTEXT_ID S_ORD_ID S_PRODUCT_ID S_REGION_ID S_WAREHOUSE_ID WIFE_ID
已选择10行。
|
|
SQL> desc user_sequences; *******容易不写最后的s******************** 名称 是否为空? 类型 ----------------------------------------- -------- ---------------
SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER |
|
SQL> alter table wife2 drop column hus_id;
表已更改。
SQL> desc wife2; 名称 是否为空? 类型 ----------------------------------------- -------- ------------
ID NOT NULL NUMBER(7) NAME NOT NULL VARCHAR2(10) |
Truncate 清空表里面的内容并且提交
|
Truncate table wife； |
创建序列
|
SQL> create sequence wife_id 2 start with 1 3 increment by 1 4 maxvalue 5 5 nocache;
序列已创建。 |
nextval
|
SQL> select wife_id.nextval from dual;
NEXTVAL ---------- 1 |
currval
|
SQL> select wife_id.currval from dual;
CURRVAL ---------- 1 |
wife_id.nextval
|
SQL> insert into wife values (wife_id.nextval,'rose');
已创建 1 行。
SQL> insert into wife values (wife_id.nextval,'rose');
已创建 1 行。 |
删除序列；
|
SQL> drop sequence wife_iD;
序列已删除。
|
|
Create sequence wife_id Start with 10 Increment by -1 Maxvalue 10 Minvalue 1;
SQL> select * from wife;
ID NAME ---------- ---------------- 10 rose 9 rose 8 rose 7 rose
|
|
Insert into wife value(wife_id.nextval,’rose’); Insert into wife values(wife_id.nextval,’rose’); ********注意values ******** |
初来乍到，个人的一些学习总结，还请多多指教，共勉^_^
