oracle基本语法2

--查询表中所有的数据--
select * from studentinfo;
--查询部分列--
select studentName,studentaddr,studentTel from studentInfo;
--列的别名--
select studentName as 姓名,studentAddr as 地址 ,studentTel 电话 from studentInfo;
--查询部分行(where条件)--
--=--
select * from studentInfo where studentsex = 1;
select * from studentInfo where studentsex = 1 and studentName = '李四';
select * from studentInfo where studentsex = 1 and (studentName = '李四' or studentName = '张三');
--<>--
select * from studentInfo where studentName <> '张三';
select * from studentInfo where studentName != '张三';
----
select * from scoreinfo where score >= 80;
select * from scoreinfo where score > 80 or score < 60;
select * from scoreinfo where score <= 80 and score >=60;
select * from scoreinfo where score between 60 and 80;
--模糊查询--
select * from studentInfo where studentTel is null;
select * from studentInfo where studentTel is not null;
--练习:查询没有发货的订单信息--
select * from king_orders where shipdate is null;
--练习:被推荐进来的客户信息--
select * from king_customer where  referred is not null;
--in 使用--
select * from scoreInfo where courseid in('001','002');
select * from scoreInfo where courseid in('001','002','sdfs');
select * from scoreInfo where courseid in('345345','123');
--in 使用--
select * from scoreInfo where courseid not in('001','002');
select * from scoreInfo where courseid in('001','002','sdfs');
select * from scoreInfo where courseid in('345345','123');
--any 使用--
select * from scoreInfo where score >any (50,60,70);
--all 使用--
select * from scoreInfo where score >all (50,60,70);
--like--
select * from studentInfo where studentName like '张%';
select * from studentInfo where studentName not like '张%';
--查询书名带_的图书信息--
select * from king_books where title like '%$_%' ESCAPE '$';

select * from studentInfo where studentNo = '&s';
--排序  永远都是最后一个字句--
select * from studentInfo order by studentName;
select * from studentInfo order by studentName asc;
select * from studentInfo order by studentbirth desc;

select * from studentInfo where 1=1 order by studentsex desc,studentTel desc;

--查询中的运算--
select studentno,courseID,score+20 from scoreInfo where courseid = '003';
select studentno,courseID,score+20 from scoreInfo where courseid = '003' and score+20<100;
select * from scoreInfo order by score+20;
select studentNo||':'||studentName from studentInfo;
--利润超过成本50%的图书信息--
select * from king_books where (retail-costing)/costing>0.5;
--函数--
--日期函数--
select sysdate from dual;
select add_months(sysdate, -5) from dual;
select months_between(sysdate,'22-7月-13') from dual;
select last_day(sysdate) from dual;
--字符函数--
select ltrim('   呵呵') from dual;
select '    呵呵     ' from dual;
select rtrim('呵呵   ') from dual;
----
select trim('    呵呵    ')from dual;
select replace('jack and jue','j','bl') from dual;
--len   length --
select length('tingshuozifuchuanhenchang') from dual;
select substr('sdfsdfsdf',3,4) from dual;