Oracle基础语句教程

爱被打了一巴掌 2022-12-03 15:48 206阅读 0赞

1.SELECT \* FROM data where student\_id = 20180163010;–查找数据  
select distinct \* from data; --只展示不重复的数据  
2.SELECT id, name,phone\_number FROM data ORDER BY 1 DESC, 2;–排序用数字  
3.SELECT id, name,phone\_number FROM data ORDER BY id ASC NULLS FIRST;–空格的排在前边  
4.truncate TABLE data;–清除表中所有数据  
5.SELECT id, name FROM customers ORDER BY UPPER( name );  
SELECT \* FROM test\_data ORDER BY UPPER( name );–按字母大小写来排序  
6.select \*, count（distinct name） from data\_learn group by name;–count（）表示数量

7.UPDATE DFC\_ADMS\_STU a SET a.PM\_TEL = (SELECT b.TEL FROM AAA b WHERE  
a.STU\_NUM = b.STUNUM) WHERE DEPT\_ID = ‘10454-02’ and a.STU\_NUM in  
(SELECT STUNUM FROM AAA b WHERE b.TEL is not null) and a.PM\_TEL is null;–更新数据例子

8.UPDATE DFC\_STU\_BASE a SET a.PM\_TEL = (SELECT b.PHONE\_NUMBER FROM data b WHERE  
a.STU\_NUM = b.STUDENT\_ID) where a.STU\_NUM in (SELECT STUDENT\_ID FROM data );  
–将另一个数据库的信息更新到另一个数据库中，where语句限定更新的语句必须在DFC\_STU\_BASE

9.rollback;–回滚数据库上一次的操作  
10.commit;–数据提交  
11.select \* from DFC\_STU\_BASE where PM\_TEL = ‘17861314213’;–定点查询数据  
12.SELECT DISTINCT state FROM locations ORDER BY state NULLS FIRST;–检测多条空数据  
13.select \* from character where letter in (‘A’,‘B’,‘C’);  
select \* from character where letter=‘A’ or letter=‘B’ or letter=‘C’;  
–in和=的用法  
14.any some all 用法：  
Any  
带【any】的嵌套查询和【some】的嵌套查询功能是一样的。早期的SQL仅仅允许使用【any】，后来的版本为了和英语的【any】相区分，引入了【some】，同时还保留了【any】关键词。  
any:  
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >any(select sal from scott.emp where job=‘MANAGER’);  
带any的查询过程等价于两步的执行过程。  
1）执行“select sal from scott.emp where job=‘MANAGER’”  
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 or sal>2850 or sal>2450;  
some  
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =some(select sal from scott.emp where job=‘MANAGER’);  
带some的嵌套查询与any的步骤相同。  
1）子查询,执行“select sal from scott.emp where job=‘MANAGER’”,其结果如图4.22所示。  
2）父查询执行下列语句。  
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =2975 or sal=2850 or sal=2450;  
all 是查询还可以是子查询  
如：  
select name from edit  
其中name前省略了all.  
name前可以加ALL|DISTINCT  
all是所有记录.  
distinct是不重复的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neXVud2VpX0Jsb2c_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neXVud2VpX0Jsb2c_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/4c1209619c1f4b22ac69f114c21a2d8a.png