Oracle学习交流（1）-----SQL语言基础

 

--获取当前系统时间 备注: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
)