西安网站设计锦,石家庄网站建设雨点牛,分销系统解决方案,桐乡市住房建设局网站oracle#xff0c;简单来说就是数据库#xff0c;数据库 #xff0c;顾名思义#xff0c;就是存放数据的容器#xff01;#xff01;
不知道oracle的我先科普一下吧~~~科普#xff0c;科学普及简称科普#xff0c;又称大众科学或者普及科学#xff0c;是指利用各种传… oracle简单来说就是数据库数据库 顾名思义就是存放数据的容器
不知道oracle的我先科普一下吧~~~科普科学普及简称科普又称大众科学或者普及科学是指利用各种传媒以浅显的、让公众易于理解、接受和参与的方式向普通大众介绍自然科学和社会科学知识、推广科学技术的应用、倡导科学方法、传播科学思想、弘扬科学精神的活动。 nice科普完毕接下来废话不多说了直接上代码案例前人说脑子是个好东西得用起来后人补充到古人说的对 select * from scott.emp;
select eName from scott.emp;
select rowid,ename from scott.emp where enameSMITH;
select emp.*,rownum from scott.emp where rownum11;--创建学员信息表
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_nameupper(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_nametest;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 rownum10;
select * from scott.dept;
insert into scott.dept(deptno,dname,loc)values(5,1111,dsds);
commit;delete from scott.dept where deptno5 ;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 studentinsert 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 12;
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_rows1;--添加列删除列
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) lastdayfrom 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了拿走不谢
