--获取当前系统时间 备注:dual是一个虚拟的视图,因为oracle中语句要求查询必须具备from
--所以Dual是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select语句块中
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as 日期 from dual;
select sysdate from dual;
--查看当前登录用户
select user from dual;
--创建主键表
create table StuClass (
CID number primary key,
CName varchar2(20) not null
)
--创建外键表
create table Student(
SID number primary key,
SName varchar2(20) not null,
SAge number check(SAge>6 and SAge<30),
ClassID number not null,
constraint FK_Student_StuClass foreign key (ClassID) references StuClass(CID)
)
insert into stuclass values(1,'.net');
insert into student values(1,'小白',18,1);
insert into student values(1,'小白',18,1,'湖北十堰');
--向已经创建的表中增加一个新列
alter table student add SAddress varchar2(255)
drop table student
select * from student
--修改表中指定列的数据类型和类型长度 此时已经修改了.从新登录就可以查看到效果
alter table student modify SAddress varchar2(255)
--删除表中指定的列
alter table student drop column saddress
--创建标识列
create sequence seq_stu minvalue 1 start with 1 increment by 1;
create sequence seq_class minvalue 1 start with 1 increment by 1;
--插入数据
select * from student
insert into stuclass values(seq_class.nextval,'G3T13')
insert into student values(seq_stu.nextval,'小黑',20,1,'湖北十堰')
--更新数据
update student set student.sname='小黑黑' where student.sid=2;
--解锁HR
alter user hr account unlock
alter user hr identified by tiger
--查询USER的所有表
select * from tabs
--单行子查询
select * from employees where salary=(
select max(salary) from employees)
--多行子查询
select * from employees where salary >=all(
select salary from employees where employees.department_id=20)
--多列子查询
select first_name,salary,department_id from employees where (department_id,salary)in(
select department_id, min(salary) from employees group by department_id)
--相关子查询
select * from employees a where exists(
select * from employees b where b.manager_id=a.employee_id) order by department_id,employee_id
--在CREATE TABLE中使用子查询
create table emp(employee_id,first_name,last_name,salary,department_id) as(
select employee_id,first_name,last_name,salary,department_id from employees where department_id =100
)
select * from emp
--在INSERT INTO语句中使用子查询
insert into emp(
select employee_id,first_name,last_name,salary,department_id from employees where department_id =80
)