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 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操作,隐式游标
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操作,隐式游标