--恢复表
FLASHBACK TABLE "Student" TO BEFORE DROP; FLASHBACK TABLE "Grade" TO BEFORE DROP;
--给字段起别名
select "GradeID" as 年纪编号,"GradeIdName" as "年纪名称" from "Grade" where "GradeID"=3;
--升降序 默认升序为asc 降序desc
select "GradeID" as 年纪编号,"GradeIdName" as "年纪名称" from "Grade" order by "GradeID" desc;
--升降序：不同的列按照不同的顺序排序
select "StudentNo" as 学号 , "SubjectNo" as 科目号 from "Result" order by "StudentNo" asc,"SubjectNo" desc; select "StudentNo" as 学号,"StudentResult" as 成绩 from "Result" order by "StudentNo" asc,"StudentResult" desc;
--模糊查询 like 像 _：匹配一个字符 %：匹配任意字符
select "StudentName" as 姓名,"Phone" as 电话 from "Student" where "StudentName" like '王%';
--
select "StudentName" as 姓名,"Phone" as 电话 from "Student" where regexp_like("LoginPwd",'[n]');
--查询80-90分之间的学生信息
select * from "Result" where "StudentResult" between 80 and 90; --小值在前，大值在后 --in select * from "Student" where "Address" in('华山路','解放路','长江路');
--查询2000-01-01之后出生的日期的学生 姓名和日期
select "StudentName" as 姓名,"BirthDate" as 出生日期 from "Student" --where "BirthDate">to_date('2000-01-01','yyyy-mm-dd'); where to_char("BirthDate",'yyyy-mm-dd')>'2000-01-01';
--字符函数
select initcap('hello') from dual; --首字大写 select lower('HELLO') from dual; --转小写 select upper('hello') from dual;--转大写 select ltrim('abcxyzab','ab') from dual;--左裁剪 select rtrim('abcxyzab','ab') from dual;--右裁剪 select translate('jack','abcd','1234') from dual;--按照将abcd变成1234的规律去翻译jack select replace('这是MySQL数据库','MySQL','Oracle') from dual;--替换 select instr('hello','o') from dual; --获取下标，结果5 select substr('helloworld',3,5) from dual; --截取字串，从3开始截5个 select concat('a','b') from dual; --拼接字符串
--数字函数
select abs(-3) from dual; --取绝对值 select ceil(3.3) from dual; --ceil:天花板 向上取整，结果为4 select floor(3.3) from dual; --floor:地板 向下取整，结果为3 select sign(3) from dual; --取数字的正负 select power(2,10) from dual; --power(m,n)：计算m的n次方 select mod(10,3) from dual; --取余数 select round(16.758,2) from dual; --四舍五入:16.76 select trunc(16.758,2) from dual; --截断：16.75
--日期函数
select round(months_between(sysdate,to_date('2002-02-20','yyyy,mm,dd'))) from dual;--取两个日期之间的月份差 select ADD_MONTHS(sysdate, 5) from dual; --添加月份 select NEXT_DAY(sysdate, 2) from dual; --计算当前日期后的下一个星期几的日期。注意，星期日1，星期六是7 select LAST_DAY(sysdate) from dual; --查找当前月的最后一天的日期 select round(to_date('2021-03-15','yyyy-mm-dd'),'year') from dual; --按照年份四舍五入（1-6月舍，7-12月入） select round(to_date('2021-09-06','yyyy-mm-dd'),'month') from dual;--按照月份四舍五入（1-15日舍，16-31日入） select round(to_date('2021-03-18','yyyy-mm-dd'),'day') from dual;--按照星期四舍五入（周1-3舍，周4-6入）
--转换函数
select to_char(128000000,'$000,000,000.00') from dual;--将数字转字符串 select to_char(1280000,'$000,000,000.00') from dual;--将数字转字符串 select to_number('123.45') from dual;--将数字字符转数字
--判空函数
select NVL(88, 66) from dual;--如果前表达式为null责取后表达式，否则取他自己 select NVL2(88, 66,77) from dual;--如果表达式1为null责取表达式3，否则取表达式2 select decode('3','1','学语文','2','学数学','敲代码') from dual;