Oracle基础语句教程

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是不重复的。