时间：2021-07-01 10:21:17 帮助过：2人阅读
|
一个认知：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） |
五种sql分类:
1:数据查询语句
select
2:数据操作语句
insert,delete,update
3:数据定义语句
create,alter,drop,
rename,truncate
4:事物控制的语句
commit,rollback,savepoint
5:权限有关的语句
grant,revoke
|
登录到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;
|
|
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启动程序 *************** 表是由列和行构成 列：一条数据 行：表拥有的字段 每一张表都有主键和外键 主键：非空唯一 外键：必须和另一张表的主键关联 *********************************** |
|
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 |
|
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 |
*************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 |
|
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
|
|
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 |
|
|
|
|
|
|
|
其实质也是单表的查询,将我们的多张表通过一定的条件连接成一张表 连接的时候产生笛卡儿积：连接的方式：等连接 查询所有员工的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行。 |
|
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 |
|
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来分 |
|
一条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: 缺失表达式 |
|
************************************************************ |
|
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 好处：不会重复 那张表哪个列的缩写以及约束条件 |