oracle基本笔记整理

select * from scott.emp;
select eName from scott.emp;
select rowid,ename from scott.emp where ename='SMITH';
select emp.*,rownum from scott.emp where rownum<11;
--创建学员信息表
create table student
(
stuNo char(6) not null,
stuName varchar2(20) not null,
stuAge number(3,0) not null,
stuID number(18,0),
stuSeat number(2,0)
);
insert into stuinfo(stuNo,stuname,stuAge,stuSeat)values('2','活动',25,3)
select * from stuinfo
--查询表的位置
select tablespace_name,table_name from user_tables where table_name=upper('stuinfo');
--创建表空间
create tablespace test 
datafile 'D:\oracle\shujuku\test.ora' 
size 1000M;
create user test identified by test default tablespace test  quota 500M on users;
grant all privileges to test;
--查看表空间
select file_name,tablespace_name,bytes,autoextensible from dba_data_files where tablespace_name='test';
select * from test.stuinfo;
--提交事务
commit;
select * from scott.emp;
--修改密码
alter user system identified by 123;
select * from scott.emp;
--rowid伪列数据对象编号  文件编号  块编号  行编号  
select e.*,rowid from scott.emp e;
--rownum,从1开始，大于1的东西查不出来，小于等于某个值可以查询
select e.*,rownum from scott.emp e where rownum<=10;
select * from scott.dept;
insert into scott.dept(deptno,dname,loc)values('5','1111','dsds'); 
commit;
delete from scott.dept where deptno='5' ;commit;
--
--创建学员信息表
create table student
(
stuNo number not null,
stuName varchar2(20) not null,
stuAge number(3,0) not null,
stuSeat number(2,0)
);
select * from student
insert into student(stuNo,stuname,stuAge,stuSeat)values('1','张三',18,1);
insert into student(stuNo,stuname,stuAge,stuSeat)values('2','李四',20,2);
insert into student(stuNo,stuname,stuAge,stuSeat)values('3','王五',15,3);
insert into student(stuNo,stuname,stuAge,stuSeat)values('4','张三',18,4);
insert into student(stuNo,stuname,stuAge,stuSeat)values('5','张三',20,5);
--事务的处理
--没有添加进去编号8
insert into student(stuNo,stuname,stuAge,stuSeat)values('6','王五1',12,6);
insert into student(stuNo,stuname,stuAge,stuSeat)values('7','张三1',14,7);
savepoint a;
insert into student(stuNo,stuname,stuAge,stuSeat)values('8','张三',20,5);
rollback to savepoint a;
commit;
select * from test.student;
--选择无重复的行distinct
select distinct stuname from student;
--选择重复的行distinct(姓名和年龄)
select distinct stuname ||stuage from student;
/*
注释的重要性
*/
--别名
select distinct stuname "姓名"  from student;
--复制一个表 as后边加一个select 
create table newstudent1 as select * from student;
select * from newstudent1 ; 
--复制表的结构（不包括数据）
create table newstudent as select * from student where 1=2;
select * from newstudent;
--查询表中的记录数
select count(1) from student;
--查询姓名和年龄中不存在重复的记录 
--大于等于是查询重复的，小于是查询不重复的
select stuname,stuage from student group by stuname,stuage having(count(stuname||stuage)<2);
select stuname,stuage from student group by stuname,stuage having(count(stuname||stuage)>1);
select stuname from student group by stuname having(count(stuname)<5);
--查询用户数量大于10的
select * from user_all_tables a where a.num_rows>1;
--添加列，删除列
alter table student add(phone varchar2(20),
                        emil varchar2(20));
alter table student drop(phone);
select * from student;
select * from stuinfo;
select stuname from student group by stuname having(count(stuname)>1);
/*
oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期.
1:得到当前月第一天与最后一天
*/
select
to_char(trunc(sysdate,'MONTH'),'yyyymmdd')
 firstday
, to_char(last_day(trunc(sysdate,'MONTH')),'yyyymmdd') lastday
     from dual;
     
--2:得到上月第一天与上月最后一天
SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,'yyyymmdd') firstday
,to_char(last_day(add_months(SYSDATE, -1)),'yyyymmdd') 
lastday 
FROM dual;
--3:得到上上个月第一天与上上个月最后一天
SELECT
to_char( last_day(add_months(SYSDATE, -3)) + 1 ,'yyyymmdd') 
firstday
,to_char(last_day(add_months(SYSDATE, -2)),'yyyymmdd')
 lastday 
FROM dual;
--4:得到下个月第一天与下个月最后一天
SELECT to_char( last_day(add_months(SYSDATE, 0)) + 1 ,'yyyymmdd') 
firstday
,to_char(last_day(add_months(SYSDATE, 1)),'yyyymmdd')
 lastday 
FROM dual;