oracle

最新推荐文章于 2023-06-14 18:14:41 发布
冬离~
最新推荐文章于 2023-06-14 18:14:41 发布
阅读量115
点赞数
本文链接：https://blog.csdn.net/Y1013768371/article/details/107439930
版权
  1 一、--基本查询语句
  2 --1.注释 单行注释
  3 /*
  4        多行
  5        注释
  6 */
  7 --2. 查询 所有用户
  8 select * from emp; --查询所有的列
  9 select empno from emp; --查询具体的列
 10 --3.rownum oracle中表示查询结果行号的一个伪列 显示查询结果的行号
 11 select rownum,empno,ename from emp where empno > 7369;
 12 -- 使用行号做分页 rown作为条件时 必须包含1的(不包含1的都为false，包含的为true)
 13 select * from emp where rownum <= 5 --取出前5条记录
 14 
 15 select * from emp where rownum = 2; -- 查询不到结果的
 16 select * from emp where rownum >= 2 and rownum <=3 -- 查询不到结果
 17 
 18 --查询第2条到第3条的记录
 19 select * from (select rownum r, emp.* from emp where rownum <=3) t
 20 where t.r >=2;
 21 
 22 --分页语句1 假如当前第p页 每页显示n条记录的
 23 select * from (select rownum r, emp.* from emp where rownum <=p*n) t
 24 where t.r >=(p-1)*n+1
 25 
 26 select * from (select rownum r, emp.* from emp where rownum <=2*5) t
 27 where t.r >=(2-1)*5+1;
 28 --分页语句2
 29 select * from (select rownum r, emp.* from emp) t
 30 where t.r >=(p-1)*n+1 and rownum <=n
 31 
 32 select * from (select rownum r, emp.* from emp) t
 33 where t.r >=(1-1)*5+1 and rownum <=5
 34 
 35 
 36 --4. 列运算 + - * / mysql中有%运算
 37 --dual 伪表
 38 select 1/2 from dual;
 39 select 1/2 result from emp;
 40 select sal*100-10000 sal,empno from emp;
 41 select comm+100 from emp;--null运算结果还是为null
 42 
 43 --5.|| 字符串拼接的连接符 mysql中没有
 44 select empno||','||sal from emp;
 45 
 46 select * from emp;
 47 
 48 --6.as 别名 oracle中 列别名可以使用as关键字 表别名不可以使用 
 49 --mysql中 表和列都可以使用as关键字取别名
 50 select e.deptno no1,d.deptno as no2 ,ename from emp  e,dept d where e.deptno = d.deptno;
 51 select * from emp;
 52 select * from dept;
 53 
 54 --7.distinct 消除重复行
 55 select distinct deptno from emp;--有员工存在的部门编号
 56 select count(distinct deptno) from emp; --查询存在员工的部门数量
 57 select distinct job,mgr from emp;
 58 
 59 --8.order by 排序 升序asc 降序desc
 60 select * from emp order by sal desc;
 61 select * from emp order by sal desc,ename ;--按照工资降序排列 工资相同时，按照name升序排列
 62 
 63 --9.oracle中的查询内容严格区分大小写 字符串使用''
 64 --  mysql不区分 字符串"" or ''
 65 select * from emp where ename = 'KING';
 66 select * from emp where ename = 'kING';
 67 
 68 二、--基本条件查询
 69 --1.简单条件查询 > >= < <= !=(^=,<>) 查询时，如果该列为null是不会被查询出来的
 70 select * from emp where sal > 3000;
 71 --2.复合条件查询 AND(与) OR(或) NOT(非)  优先级AND > OR
 72 --查询工资>3000 并且所在job为JAVA的员工 
 73 select * from emp where sal > 3000 and job = 'JAVA';
 74 select * from emp where sal > 3000 OR job = 'JAVA';
 75 SELECT * FROM emp where not job = 'JAVA';
 76 
 77 --scm中采购单入库查询的sql 
 78 -- select * from pomain where (paytype='货到付款' and status = 1 or paytype='款到发货' and status = 3 or payType='预付款到发货' and status = 5) and paytype='货到付款'
 79 -- 3. [not] between .. and .. 查询某个字段是否在某个区间范围内
 80 select * from emp where sal between 2900 and 2975 --查询结果包含临界值的
 81 select * from emp where ename between 'B' and 'D'
 82 
 83 --4.[not] in 查询列中的值是否在...之中
 84 select * from emp where ename in ('SCOTT','BLAKE','ABCE');
 85 
 86 --5.[not] like 模糊查询
 87 -- % 匹配0个或者多个任意字符
 88 -- _ 匹配1个任意字符
 89 select * from emp where ename like '_A%';--查询名字中第二个字母为A的用户
 90 
 91 --6. is [not] null 匹配空字符串
 92 --在oracle中不区分'' 和 null的
 93 --mysql中 区分'' 和null
 94 select * from emp where job is null;
 95 select * from emp where job = '';
 96 update emp set job = '' where empno = 2;
 97 
 98 -- 7.any (some)
 99 select * from emp where sal > any(select sal from emp where deptno = 10) -- 大于部门为10的工资的任意一个人的 都会被查询出来 >min(sal)
100 -- all 
101 select * from emp where sal > all(select sal from emp where deptno = 10)  -- > max(sal)
102 
103 --8.[not] exists 
104 select * from emp where exists (select * from emp where empno = 2)
105 
106 -- 练习：查询工资最高的前3名员工，按照工资从高到低排序
107 select * from (select * from emp where sal is not null order by sal desc)where rownum <=3
108 
109 三、--函数
110 --oracle函数
111 --1.数值型函数
112 --1.1 mod(num,num2) 取余数
113 select mod(sal,10) from emp; 
114 --1.2 round(num,保留小数位数) 四舍五入
115 select round(1.9877,2) from dual;
116 
117 --2.字符型函数
118 --2.1 upper or lower查询结果忽略大小写
119 select * from emp where lower(ename) = lower('ScotT')
120 
121 --2.1 concat(str1,str2)字符串拼接 mysql中也有concat(str1,str2,.....)
122 select concat('a','b') from dual;
123 
124 --2.2 substr(str,startIndex(从1开始),length) 截取子字符串
125 select substr('hello world',1,2) from dual;
126 
127 --2.3 length(str) 字符串长度
128 select length('你好') from dual;
129 --2.4 trim
130 select trim('s' from 'sstsrings') from dual ;
131 select trim(Leading 's'  from 'sstsrings') from dual ;
132 
133 --2.5 replace(str,old,new)
134 select Replace ('ABC','B','C') from dual;
135 
136 --3.日期类型
137 select add_months(to_date('2018-01-31 12:30:30','yyyy-mm-dd hh24:mi:ss'),1) from dual;
138 
139 --4.类型转换函数
140 --4.1 to_number 将字符串转成数字
141 select to_number('123.456')+10 from dual;
142 --4.2 to_date(str,format) 将日期格式的字符串转成date类型
143 select to_date('2018-01-31 12:30:30','yyyy-mm-dd hh24:mi:ss') from dual;
144 --计算两个日期之间的间隔 两个date类型的数据相减 得到的结果单位是天
145 select (to_date('2018-01-31 12:30:30','yyyy-mm-dd hh24:mi:ss')-to_date('2018-01-31 12:00:30','yyyy-mm-dd hh24:mi:ss'))*24*60 from dual;
146 
147 --5.其他函数
148 --5.1 nvl(值,显示值) 空值转换函数
149 select nvl(null,'空') from dual;
150 select nvl('123','空') from dual;
151 select nvl('','空') from dual;
152 select nvl(comm,0) from emp;
153 -- mysql select IFNULL(remark,123) from category
154 --5.2 decode()分支函数
155 select decode(18,1,'男',0,'女','其他') from dual;
156 
157 select decode(comm,null,'空',comm) from emp;
158 
159 四、--关联查询
160 select * from emp;
161 select * from dept;
162 -- 关联查询 
163 --1.相等连接 n张表关联查询最少添加n-1个关联条件
164 select empno,ename,emp.deptno,dept.deptno,dname,loc from emp,dept where dept.deptno = emp.deptno;
165 
166 --2.不等连接
167 select * from salgrade;
168 select * from emp,salgrade where sal between losal and hisal;
169 
170 --3.外连接
171 --3.1 left join
172 select 
173       empno,ename,e.deptno,d.deptno,loc ,dname
174 from 
175       emp e left join dept d
176 on
177       e.deptno = d.deptno;--连接条件
178       
179 select * from emp;
180 
181 --2.right join
182 select 
183       empno,ename,e.deptno,d.deptno,loc ,dname
184 from 
185       emp e right join dept d
186 on
187       e.deptno = d.deptno;--连接条件
188       
189       
190 --3.full join (mysql中 没有full join)
191 select 
192       empno,ename,e.deptno,d.deptno,loc ,dname
193 from 
194       emp e full join dept d
195 on
196       e.deptno = d.deptno;--连接条件
197       
198 --4.inner join 和相等连接的查询结果一样
199 select 
200       empno,ename,e.deptno,d.deptno,loc ,dname
201 from 
202       emp e inner join dept d
203 on
204       e.deptno = d.deptno;
205       
206 select * from dept;
207 select * from emp;
208 
209 --多张表 外连接查询语法
210 select 
211     * 
212 from
213 scmuser s left join pomain p on s.account = p.account 
214 left join poitem p2 on p2.poid = p.poid
215 where s.account = 'b2';
216 
217 --5.自连接
218 select * from emp;
219  --查询所有员工的名字以及管理者的名字
220 select 
221  e.ename 员工名,m.ename 管理者名字
222 from
223  emp e, emp m
224 where
225  e.mgr = m.empno;
226  
227  select 
228  e.ename 员工名,m.ename 管理者名字
229 from
230  emp e left join emp m
231 on
232  e.mgr = m.empno;
233  
234  create table football(
235         name varchar2(32)
236  );
237 insert into football values ('德国');
238 insert into football values ('意大利');
239 insert into football values ('阿根廷');
240 insert into football values ('中国');
241 insert into football values ('西班牙');
242 commit;
243    
244 select * from football;
245 --假如每两个足球队都有一场比赛 查询组合关系
246 select * from football a,football b where a.name != b.name;
247 select * from football a,football b where a.name > b.name;
248   
249 五、--统计查询
250 --统计查询
251 -- 统计函数 max() min() sum() avg() count()
252 --count 统计记录的条数
253 select count(*) from emp;
254 select count(sal) from emp; -- 统计sal不为null记录条数
255 
256 --在使用统计函数做查询时 如果不添加group by子句，是把符合条件的所有数据当成一个组来统计的
257 --select 子句只能出现统计函数，不允许出现列的结果的
258 select count(*),count(sal),avg(sal),max(sal) from emp;
259 --group by 子句 分组查询，将group by 子句后的列 值相同分为一组进行统计
260 select count(*),count(sal),avg(sal),max(sal),deptno from emp group by deptno;
261 
262 --查询每个部门的名字以及员工总人数，没有员工的显示0 
263 select count(empno),dname from dept left join emp on dept.deptno = emp.deptno group by dname
264 -- having子句 跟在group by后对统计函数做条件筛选的
265 -- 查询部门人数>
266 select 
267    count(empno),dname 
268 from 
269    dept left join emp 
270 on 
271    dept.deptno = emp.deptno 
272 group by 
273       dname
274 having 
275       count(empno) > 3 
276       
277 --查询语句中的分支
278 oracle :
279 select
280 from
281 where
282 order by
283 group by
284 having 
285 执行顺序：
286 from > where > group by > having >  select > order by
287 
288 select sal s from emp order by s;
289 
290 mysql:
291 select
292 from
293 where
294 order by
295 group by
296 having 
297 limit
298 
299 from > where > group by > having >  select > order by > limit 
300 
301 六、--子查询
302 --查询工资比SCOTT高的员工信息
303 select * from emp where sal > (select sal from emp where ename = 'SCOTT')
304 
305 七、--集合运算
306 --集合运算 合并sql语句的查询结果
307 --uninon/union all
308 select * from emp where deptno in (10,20,30) 
309 union all
310 select * from emp where deptno in (10)
311 
312 八、--增删改
313 --DML操作
314 -- 1. 新增
315 insert into dept values (111,'dfsdf','dfsdf');
316 
317 --将一个查询语句的结果 插入到数据库表中
318 select 22,'a','b' from dual;
319 insert into dept select 22,'a','b' from dual;
320 
321 insert into dept_copy select * from dept;--批量的将dept表中的数据复制到dept_copy表中
322 
323 select * from dept_copy;
324 create table dept_copy(
325        deptno number primary key,
326        dname varchar2(32),
327        loc varchar2(32)
328 )
329 
330 --修改 
331 update dept_copy set (dname,loc) = (select 'a','b' from dual) where deptno = 30;
332 
333 九、--数据结构
334 --1.数据类型
335 --字符串类型 char,varchar,varchar2
336 --char 定长字符串，如果存入的实际内容字节长度不足会使用空格补齐
337 --varchar 变长字符串
338 --varchar2 变长字符串 使用这种类型
339 create table test_char(
340        a char,--默认1个字节
341        b char(10),
342        c varchar(5),--
343        d varchar2(5)    
344 );
345 
346 insert into test_char (a) values ('你');--一个中文UTF-8编码下 是占用3个字节的
347 insert into test_char (b) values ('你');
348 select * from test_char;
349 select length(d) from test_char;
350 insert into test_char(c) values ('你a');
351 
352 insert into test_char(d) values ('你a');
353 
354 十、--创建表
355 --表的创建
356 create table 表名(
357        列名 数据类型 约束,
358        ....
359 )
360 --主表
361 create table class_(
362        id number primary key ,
363        name varchar2(32) default 'class_name' not null,
364        num number check(num between 20 and 100)
365 );
366 insert into class_ (id,num) values (1,39);
367 select * from class_;
368 
369 
370 
371 --表的约束
372 --1.主键约束 primary key
373 --2.not null 非空约束
374 --3.default 值 默认值
375 --4.check 检查约束
376 --5.外键约束
377 
378 --关联外键的表
379 create table student(
380        id number primary key,
381        student varchar2(32) not null,
382        class_id number references class_(id)--添加外键约束 一定是另外一张表的主键
383 )
384 insert into student values (1,'zhagnsan',1);
385 --添加外键后
386 --删除主信息 ：
387 
388 --修改外键的语法
389 alter table STUDENT
390   drop constraint SYS_C0013200;--先删除外键
391 alter table STUDENT
392   add CONSTRAINT mykey foreign key (CLASS_ID)
393   references CLASS_ (ID) on delete cascade;--再新增外键 删除主记录时连带子记录一起删除
394 
395 --使用子查询建表 
396 create table emp_1 as select * from emp;--不能复制约束，只能复制结构和内容
397 select * from emp_1;
398 
399 delete from class_ where id = 1;
400 
401 --2.truncate  DDL语言 清空表数据 清空表效率高 删除表中数据所占用的磁盘空间的
402 --delete DML语言 具有事务功能 删除数据
403        
404 十一、--创建视图
405 --创建视图
406 create or replace view my_view as
407 select e.*,dname,loc from emp e,dept d where e.deptno = d.deptno; 
408 
409 select * from my_view;
410 
411 update my_view set remark = '12121' where empno = 7369;
412 
413 select * from emp;
414 
415 十二、--笔记：
416     1)表取别名不能用as
417     2)in比or效率高
418     3)统计函数默认一个group，不能加一列
419     4)统计函数不能跟在where后面
420     5)执行顺序：先where后group by
421     6)集合的前提是表结构一样
422     7)查增删需要commit
423     8)length函数返回的是字符串的长度，比如你  为3
424     9)long类型是字符串类型
425     10)check约束在mysql不起作用，虽然不报错
426     11)子查询建表无约束，只负责复制数据的结构和内容
427     12)视图存储查询语句的结果集，将其当成表方便以后查询
428     13)回收站recicle bin，只能存储table类型的数据