SELECT * FROM TTB_N_TURN_RULE_SELECT TNTRS WHERE (TNTRS.PRE_SERIES_ID = 317
OR TNTRS.NEXT_SERIES_ID = 317 ) AND TNTRS.CLUSTER_ID = 343--基本查询，条件查询，重用函数。联合查询。统计分组查询。子查询。集合运算。
create table employee (
id int not null ,
name varchar2(50) null,
deptno int null
);
alter table employee add primary key (id);
alter table employee add age int null;
insert into employee (id, name, deptno, age) values (1, 'zhangsan', 1, 20);
rollback;
delete employee ;
alter table employee add check (age
select * from employee;
rename employee to a;
select * from employee; --error
select * from a;
rename a to employee;
create table dept (
deptno int not null,
name varchar2(50) null
);
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno); --error
insert into dept (deptno, name) values (1, 'aowin1');
select * from dept;
alter table employee
modify deptno null;
alter table dept
add primary key (deptno);
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno);
delete dept ; --error
alter table employee drop constraint fk_employee_dept;
delete dept;
select * from dept;
select * from employee;
rollback;
select * from dept;
select * from employee;
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno) on delete cascade;
select * from dept;
select * from employee;
delete dept;
select * from dept;
select * from employee;
rollback;
alter table employee drop constraint fk_employee_dept;
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno) on delete set null;
delete dept;
select * from dept;
select * from employee;
rollback;
select * from dept;
select * from employee;
truncate table dept; --error
insert into dept (deptno, name) values (2, 'aowin2');
insert into dept (deptno, name) values (3, 'aowin3');
insert into dept (deptno, name) values (4, 'aowin4');
insert into employee (id,name,deptno,age) values (2, 'lisi',1, 22);
insert into employee (id,name,deptno,age) values (3, 'wangwu',2, 23);
insert into employee (id,name,deptno,age) values (4, 'xiaoliu',null, 22);
select employee.*, dept.name dept_name from employee
inner join dept on employee.deptno=dept.deptno and employee.name > all ('o') ;
select employee.*, dept.name as dept_name from employee
left join dept on employee.deptno=dept.deptno;
--nvl
select employee.*, nvl(dept.name,'no class') dept_name from employee
left join dept on employee.deptno=dept.deptno;
--decode
select temp.* from (select rownum||'' inner_rownum, employee.*, decode(dept.name,'aowin1','和盈201','aowin2','和盈202','aowin3','和盈203') dept_name from employee
inner join dept on employee.deptno=dept.deptno ) temp order by deptno desc, name asc ;
--
select greatest(1,2,3) from dual;
--greatest 无作用
select employee.*, dept.name dept_name, greatest(age) max_age from employee
left join dept on employee.deptno=dept.deptno;
--max,group by 查询中使用
select employee.deptno, max(age) max_age, count(*) person_count from employee group by deptno;
select 'sstsrings' from dual ;
select trim(leading 's' from 'sstsrings') from dual ;
select trim(trailing 's' from 'sstsrings') from dual ;
select trim(both 's' from 'sstsrings') from dual ;
select trim(leading | trailing|both 's' from 'sstsrings') from dual ;
select Replace ('ABC','B','C') from dual;
select to_char(1234.5,'$9999.9') from dual;
select to_date('1990-08-08','yyyy-mm-dd') from dual;
select to_number(' 123') from dual;
--count, sum, max, min
select employee.deptno from employee
inner join dept on employee.deptno=dept.deptno
and not exists( select * from dept d where d.name='zhao' );
drop table dept CASCADE CONSTRAINTS;
SELECT * FROM TTB_N_TURN_RULE_SELECT TNTRS WHERE (TNTRS.PRE_SERIES_ID = 317
OR TNTRS.NEXT_SERIES_ID = 317 ) AND TNTRS.CLUSTER_ID = 343--基本查询，条件查询，重用函数。联合查询。统计分组查询。子查询。集合运算。
create table employee (
id int not null ,
name varchar2(50) null,
deptno int null
);
alter table employee add primary key (id);
alter table employee add age int null;
insert into employee (id, name, deptno, age) values (1, 'zhangsan', 1, 20);
rollback;
delete employee ;
alter table employee add check (age
select * from employee;
rename employee to a;
select * from employee; --error
select * from a;
rename a to employee;
create table dept (
deptno int not null,
name varchar2(50) null
);
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno); --error
insert into dept (deptno, name) values (1, 'aowin1');
select * from dept;
alter table employee
modify deptno null;
alter table dept
add primary key (deptno);
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno);
delete dept ; --error
alter table employee drop constraint fk_employee_dept;
delete dept;
select * from dept;
select * from employee;
rollback;
select * from dept;
select * from employee;
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno) on delete cascade;
select * from dept;
select * from employee;
delete dept;
select * from dept;
select * from employee;
rollback;
alter table employee drop constraint fk_employee_dept;
alter table employee
add constraint fk_employee_dept foreign key (deptno) references dept (deptno) on delete set null;
delete dept;
select * from dept;
select * from employee;
rollback;
select * from dept;
select * from employee;
truncate table dept; --error
insert into dept (deptno, name) values (2, 'aowin2');
insert into dept (deptno, name) values (3, 'aowin3');
insert into dept (deptno, name) values (4, 'aowin4');
insert into employee (id,name,deptno,age) values (2, 'lisi',1, 22);
insert into employee (id,name,deptno,age) values (3, 'wangwu',2, 23);
insert into employee (id,name,deptno,age) values (4, 'xiaoliu',null, 22);
select employee.*, dept.name dept_name from employee
inner join dept on employee.deptno=dept.deptno and employee.name > all ('o') ;
select employee.*, dept.name as dept_name from employee
left join dept on employee.deptno=dept.deptno;
--nvl
select employee.*, nvl(dept.name,'no class') dept_name from employee
left join dept on employee.deptno=dept.deptno;
--decode
select temp.* from (select rownum||'' inner_rownum, employee.*, decode(dept.name,'aowin1','和盈201','aowin2','和盈202','aowin3','和盈203') dept_name from employee
inner join dept on employee.deptno=dept.deptno ) temp order by deptno desc, name asc ;
--
select greatest(1,2,3) from dual;
--greatest 无作用
select employee.*, dept.name dept_name, greatest(age) max_age from employee
left join dept on employee.deptno=dept.deptno;
--max,group by 查询中使用
select employee.deptno, max(age) max_age, count(*) person_count from employee group by deptno;
select 'sstsrings' from dual ;
select trim(leading 's' from 'sstsrings') from dual ;
select trim(trailing 's' from 'sstsrings') from dual ;
select trim(both 's' from 'sstsrings') from dual ;
select trim(leading | trailing|both 's' from 'sstsrings') from dual ;
select Replace ('ABC','B','C') from dual;
select to_char(1234.5,'$9999.9') from dual;
select to_date('1990-08-08','yyyy-mm-dd') from dual;
select to_number(' 123') from dual;
--count, sum, max, min
select employee.deptno from employee
inner join dept on employee.deptno=dept.deptno
and not exists( select * from dept d where d.name='zhao' );
drop table dept CASCADE CONSTRAINTS;