Oracle自学之基本语法

select count(*) from emp t;

create table student(
       name varchar2(20),
       age number(3)
);

--insert into student values("aaa",10);
insert into student values('aaa',10);
insert into student values('bbb',20);
insert into student values('ccc',30);

select * from student;

delete from student where name='aaa';

select empno,ename,sal*12 from emp;

select empno,ename,sal*（12+1000） from emp;

select ename || 'is a ' || job from emp;

select empno,ename || ' ''s annual salary is ' || sal*12 from emp;

select empno as 员工编号,ename 员工姓名,sal*12 年薪 from emp;

select empno "Empno",ename 员工姓名,sal*12 年薪 from emp;

select distinct empno ,sal  from emp order by empno desc;

select empno as 员工编号,ename 员工姓名,sal*12 annsal from emp order by annsal; 

select * from emp where hiredate ='02-4月-81';

select * from emp where sal between 1600 and 2900;

select * from emp where ename like 'S%';

select * from emp where ename like '_S%';

select * from emp where ename like '%\_%' escape '\';

select * from emp where comm is null;

--转换成小写
select lower('EMPNO') from dual;
--转换成大写
select upper('empnoA') from dual;
--首字母大写
select initcap('hdjasbdjaks') from dual;
--字符串连接
select concat('EMPNO','hahha ddd') from dual;
--截取子串
select substr('ABCDEFG',4,3) from dual;
--返回字符串长度
select length('ABCDEFG') from dual;
--定位子串
select instr('ABCDEFG','D') from dual;
--左侧填充
select lpad('ABCDEFG',15,'*') from dual;
--右侧填充
select rpad('ABCDEFG',15,'*') from dual;
--过滤首尾空格
select trim('    ABCDEFG   ') from dual;
--替换
select replace('ABCDEFG','A','AAA') from dual;
--取绝对值
select abs(-3.14) from dual;
--计算在日期x基础上增加y个月后的日期
select add_months(sysdate,2) from dual;

------------------------------------------------------------------------------------------------
--游标：是构件在PL/SQL中，用来查询数据库，获取记录集合或者结果集合的指针，它可以让开发者一次访问一行结果集。
--Oracle中游标的分类：显示游标，隐式游标
--我们如果采用select * from t;查询多行语句，就不能放到一个变量里头去
--游标可以让我们定位到所需要的那一行，就类似指针
--你做任何一个查询都会有游标，做任何一个DML操作，隐式游标