oracle基本笔记整理

oracle，简单来说就是数据库，数据库 ，顾名思义，就是存放数据的容器！！

不知道oracle的我先科普一下吧~~~科普，科学普及简称科普，又称大众科学或者普及科学，是指利用各种传媒以浅显的、让公众易于理解、接受和参与的方式向普通大众介绍自然科学和社会科学知识、推广科学技术的应用、倡导科学方法、传播科学思想、弘扬科学精神的活动。

nice，科普完毕，接下来废话不多说了，直接上代码案例，前人说：脑子是个好东西，得用起来！！！后人补充到：古人说的对！！！

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;

nice,有的人可能之前没学过数据库，比如说my sql ,sql server 等等，直接上来就是oracle，所以，推荐一部视频，即使你没有学过sql server什么的，或者学过没深入理解的，那么，请点击这里，密码: 3ydr，一共46节课，足足够你学会oracle了，拿走不谢！！！