oracle语法

政林 2020-01-02 1285浏览 1条评论
首页/正文
分享到: / / / /

oracle语法
-- 创建用户表
create table  user1
(id number(5) primary key ,
username varchar2(10),
password varchar2(10),
rid number(10));

--创建学生表
create table student(sid number(10)  primary key,
stu_name varchar2(20),
sex number(10),
age number(10),
cid number(10),
birthday date,
photo_name varchar2(2000),
photo_path varchar2(2000) 
)

--创建班级表
create table clazz(
cid number(10) primary key,
clazz_name varchar2(1000)
)

--删除表
drop table if exists bonus;

--增加数据
insert into clazz(clazz_name) values(1,'一年级');
insert into clazz(cid,clazz_name) values(2,'一年级');
insert into clazz(cid,clazz_name) values(3,'一年级');
insert into clazz(cid,clazz_name) values(4,'四年级');
insert into clazz(clazz_name) values('71年级');

--更改数据
update clazz set cid=6 where cid=68;

--删除数据
delete from clazz where cid=4;

--查看数据
select * from clazz;

--创建主键自增

--创建一个序列
create sequence cid_sequence  
      minvalue     1  
      nomaxvalue  
      start with   1  
      increment by 1  
      nocycle  ;
      
--创建触发器实现插入数据时自动触发触发器实现主键字段值自增
create trigger cid_table_trigger before insert on clazz for each row when(new.cid is null)  
  begin  
    select cid_sequence.nextval into:new.cid from dual;  
  end;
  
--删除触发器
drop trigger sid_student_trigger;
  
 -- 分页
  select * from (select c.*,rownum-1 rid from clazz c) where rid >=2 and rid <= 3 ;

  
--获得记录数
select count(1) from clazz;
  
--最大值   平均值   最小值
select max(cid),avg(cid),min(cid) from clazz;

-- 
insert into student (stu_name,sex,age,cid) values ('张三2',1,20,2);
insert into student (stu_name,sex,age,cid) values ('张三3',1,20,2);
insert into student (stu_name,sex,age,cid) values ('张三4',1,20,2);


--
select * from student s;

--表连接
select s.stu_name,c.clazz_name 
from student s,clazz c 
where s.cid=c.cid;

--
update student set cid=1 where sid=1;
update student set cid=3 where sid=3;

--
select count(*),cid from student group by cid;

--查看
select index_name, table_name, column_name from user_ind_columns where table_name='student' 


/*mysql  oracle区别:
1.分页      mysql:limit             oracle: rownum
2.自增      mysql:auto_increment    oracle:自增序
3.数据类型  mysql:int   varchar     oracle:number varchar2 blob
4.非空验证  mysql:!=""  null        oracle:is not null    没有""
5.分组      mysql:group             oracle:select column group by column
6.别名      mysql:as  空格          oracle:as  报错       空格ok
7.用户      mysql:root              oracle:scott
8.修改密码  
              mysql:mysqladmin -u root -p old password "new";
              oracle:alter user user_name identified by new_password
9.存在判断: mysql:if exists        oracle:没有

*/
--修改密码
alter user scott identified by scott;

--开启服务
net start OracleServiceORCL

--关闭fuwu
net start OracleServiceORCL

点击这里给我发
消息
最后修改:2020-01-02 21:45:00 © 著作权归作者所有
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付

上一篇

发表评论

评论列表

匿名用户 2022-11-26 16:25:40
The Commission found that Complainant engaged in protected activity when she requested reasonable accommodation in the form of leave due to her inability to work on one day, and she was subjected to adverse treatment when she was given leave without pay instead of the annual leave she requested <a href=http://bestcialis20mg.com/>buying cialis online usa</a> Immune Cell Infiltrates May Portend Better Prognosis Across Several Tumor Types
回复