oracle 基本语句

---ddl

create table
 student(sno  char(6), sname varchar(10), sex char(6), birthday date, dno char(6));
 
 
 alter table student add (address varchar2(20));
 
 alter table student modify(address varchar2(50));
 
 
 alter table student rename to stu;
 
 select * from cat;
 
 alter table stu rename to student
 
 alter table student rename column address to  familyaddress
 
 select * from student
 
 alter table student rename column familyaddress to address
 
 alter table student add(ilovelx varchar2(10) );
 
 select * from student
 
 alter table student drop(ilovelx)
 
 ---试一下modify 可以用来改变列的类型，，也可以用来给类添加主键约束吗
 --结果是可以的！！
 alter table student modify(sno char(6) constraint pk_student_sno primary key);
 
 
 select * from student
 
 ---oracle中日期的表达方式 dd-mon-yy 而且月份后面还得带一个月:  10-5月-1991
 insert into student values('s01','lx','man','10-5月-1991','d01','buqingchu','iloveu');
  insert into student values('s02','lx','man','10-5月-1991','d01','buqingchu','iloveu');
 
 
 
 

create table dep (dno char(6) constraint pk_dep_dno primary key ,dname varchar2(20),tel varchar2(10));
drop table dep

alter table dep modify (tel varchar2(20));

---下面两个语句一起执行的时候  前一条语句要以;结束
 insert into dep values('s01','计算机','010-58789657');
 insert into dep values('s02','外语','010-58789657');

select * from dep

select * from student
 
delete   from student

----modify可以给列添加主键约束，那么可以给列添加外键约束吗？ 
alter table student modify(dno char(6) constraint fk_student_dno references dep(dno));

---scott 用户的下的模型 表

select * from emp

select ename, sal*12 年工资，sysdate 日期  from emp

select *  from emp  order by  sal desc

--order by  默认是asc
select * from emp where comm  is null  order by sal

--order by 默认是asc    日期在前面的小 
select * from  emp where comm is not null order by hiredate 

--连接操作
select * from emp, dept where emp.deptno=dept.deptno  order by  dept.deptno desc,emp.hiredate  ,emp.sal desc

 

--连接操作  列 冠以表 别名
select  e.empno,e.ename,e.job,e.hiredate from emp e,dept d
 where e.deptno=d.deptno  order by  d.deptno desc,e.hiredate ,e.sal desc