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