个人的尚学堂数据库oracle笔记（1）

1.查询语句
select *from emp;
select *from dept;
select * from salgrade;
select ename ,sal *12 from emp;/*年薪*/
select 2*3 from emp;/*计算表达式*/
select ename ,sal*12 as "anuual sal" from emp;/* ""保持大小写，年薪*/

select 2*3 from dual;/*用于计算*/

select ename || sal from emp;/*字符串连接||*/

select distinct deptno from  emp ;/*消除重复行*/
select distinct deptno ,job from emp;

select *from emp  where  deptno = 10;
 
select *from emp  where ename = 'clerk';
select *from emp  where deptno <> 10;
 
select ename , sal from emp  where sal between 800 and 1500;

select ename , sal from emp  where sal >-800 and sal <= 1500;


select ename , sal, comm from emp  where  comm is null;
select ename , sal, comm from emp  where  comm is not  null;
select ename , sal, comm from emp  where  ename in ('king', 'smith','abc');

select ename , sal, hiredate from emp where hiredate > '20-2-1981';

select ename  from emp where ename like '_a%';
select ename from emp where ename like '%$%%' escape '$';

select *from dept order by deptno desc;

select empno, ename from emp order by empno desc;

select ename ,sal*12 annual_sal from emp
where ename not like '_A%' and sal > 800
order by sal desc;



select lower(ename) from emp ;

select upper(ename) from emp ;

select ename from emp where  lower(ename) like 'a%';

select substr(ename,2,3) from emp ; /*从第二个函数开始写3个*/
/*其他函数省略*/

select to_char(sal ,'L00000.00') FROM emp;/*字符转换*/

select to_char(hiredate ,'yyyy-mm-dd hh:mi:ss') FROM emp;/*字符转换*/

select to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss') FROM dual;/*字符转换*/
 
select ename ,hiredate from emp where hiredate > to_char('2000-12-7','yyyy-mm-dd');
 
 select count (ename)from emp;
 select count (distinct deptno) from dept;
 
 



2.DML语句（数据操作语言）

insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

insert into dept values (10,'accounting','new york');
insert into dept values (20,'research','dallas');
insert into dept values (30,'sales','chicago');
insert into dept values (40,'opeartions','hoston');

insert into emp values (7369,'smith','clerk',7902,to_date( '17-12-80' , 'dd-mm-yy'),800, null ,20);
insert into emp values (7499,'allen','salesman',7698,to_date( '20-12 -81' ,'dd-mm-yy' ),1600, 300,30);
insert into emp values (7566,'jones','manager',7839,to_date( '02-4-81','dd-mm-yy'),2975,null ,20);
insert into emp values (7654,'martin','salesman',7698,to_date( '28-9-81' , 'dd-mm-yy' ),1250,1400,30);
insert into emp values (7566,'jones','manager',7839,to_date( '02-4-81' , 'dd-mm-yy'),2975, null ,20);
insert into emp values ( 7698 ,'blake','manager',7839,to_date( '01-5-81','dd-mm-yy' ),2850,null,30);
insert into emp values ( 7702 ,'clerk','manager',7839,to_date( '09-6-81' ,'dd-mm-yy' ),2450, null ,10);

/*缺失值一定要用null代替，否则报错*/
insert into emp values ( 7700 ,'scott','analyst',7566,to_date( '19-4-07' , 'dd-mm-yy'),3000,null ,20);
insert into emp values (  7039,'king','president', null ,to_date( '17-11-01', 'dd-mm-yy'),5000, null ,10);
insert into emp values (  7844 ,'turner','salesman', 7698,to_date( '08-9-81', 'dd-mm-yy'),1500,0 ,30);
insert into emp values (  7876 ,'adoms','clerk',7788 ,to_date( '23-5-87', 'dd-mm-yy'),1100, null ,20);
insert into emp values ( 7900  ,'james','clerk',7698 ,to_date( '03-12-81', 'dd-mm-yy'),950, null,30);
insert into emp values ( 7902  ,'ford','analyst',7566 ,to_date( '03-12-81', 'dd-mm-yy'),3000, null ,20);
insert into emp values ( 7934  ,'miller','clerk', 7782,to_date('23-1-82', 'dd-mm-yy'),1300,null ,10);



3.DDL语句（数据定义语言）
create table emp(
empno number(4) not null,
ename varchar(10),
job varchar(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))

create table dept(deptno number(2) not null, dname varchar(14),
loc varchar(13))

create table  salgrade(cgrade number,losal number,hisal number )





4.事务控制语句

delete from emp where ENAME= 'smith'