资讯主题 wordpress,成都网站关键词推广优化,公众号运营思路,护肤品网站制作 网新科技1. 建立数据库备注#xff1a;1) oracle 不同于mysql 可以直接create database2) oracle 创建schema时对应一个用户#xff0c;即该schema的访问用户#xff0c;与用户一一对应#xff1b;但可以存在多个访问用户(带权限控制)1.1 创建数据库文件CREATE TABLESPACE XX LOGGI…1. 建立数据库备注1) oracle 不同于mysql 可以直接create database2) oracle 创建schema时对应一个用户即该schema的访问用户与用户一一对应但可以存在多个访问用户(带权限控制)1.1 创建数据库文件CREATE TABLESPACE XX LOGGING DATAFILE ‘D:\app\XX\oradata\orcl\XX.dbf‘ SIZE 1000M;create temporary tablespace XX tempfile ‘D:\app\XX\oradata\orcl\XX.dbf‘ size 1000m;1.2 创建用户CREATE USER XX IDENTIFIED BY XX DEFAULT TABLESPACE XX TEMPORARY TABLESPACE XX;1.3 授权grant connect, resource to XX;grant create session to XX;2. 数据库操作(默认sccot用户)2.1 createcreate table persons(person_id NUMBER PRIMARY KEY,first_name VARCHAR2(50) NOT NULL,last_name VARCHAR2(50) NOT NULL,score NUMBER,type VARCHAR2(20));备注1) ORA-02000: missing ALWAYS keyword : 在11g版本里不用用GENERATED BY DEFAULT AS IDENTITY 要用PRIMARY KEY2) oracle 本来只有number类型用作number(19,2)即有小数位后为了兼容其他数据库新增int只能是整形2.2 insertinsert into persons values(1, ‘fred‘, ‘xu‘, 0, ‘a‘);insert into persons values(SEQ_PERSON_ID.NEXTVAL, ‘fred3‘, ‘xu3‘, 0, ‘b‘)备注1)oracle下设置自增没有mysql那么简单步骤如下1.1) CREATE SEQUENCE SEQ_PERSON_ID start with 100; #创建一个序列1.2) INSERT INTO persons VALUES(SEQ_PERSON_ID.NEXTVAL, ‘fred1‘, ‘xu1‘, 0, ‘a‘);#此时插入persons表记录的person_id被设置成了1001.3)也可以采用触发器的形式CREATE TRIGGER persons_triggerBEFORE INSERT ON personsFOR EACH ROWWHEN (new.person_id is null)beginselect SEQ_PERSON_ID.nextval into :new.person_id from sys.dual;end;备注sys.dual 是个虚拟表oracle保证里面只有一条记录:new— 触发器执行过程中触发表作操作的当前行的新纪录:old— 触发器执行过程中触发表作操作的当前行的旧纪录在有触发器之后插入数据时不需要填写主键 INSERT INTO persons(first_name, last_name, score, person_type) VALUES(‘fred2‘, ‘xu2‘, 0, ‘a‘);2.3 selectselect * from persons where first_name ‘fred‘ or last_name ‘xu1‘select * from persons where first_name like ‘%fred%‘select * from persons where CONCAT(first_name, last_name) like ‘%1%‘select * from users where rownum BETWEEN 0 AND 5 #利用rownum关键字分页2.4 group byselect AVG(score), person_type from persons group by person_typeselect AVG(score), person_type from persons group by person_type having person_type ‘b‘备注1)ORA-00979: not a GROUP BY expression group by 后的列要能被处理2.5 inselect * from persons where person_type in (‘a‘,‘b‘);2.6 insert allinsert allinto persons(first_name, last_name, score, person_type) values(‘test‘, ‘test‘, 1, ‘c‘)into persons(first_name, last_name, score, person_type) values(‘test1‘, ‘test1‘, 1, ‘c1‘)into persons(first_name, last_name, score, person_type) values(‘test2‘, ‘test2‘, 1, ‘c2‘)SELECT 1 FROM dual;备注SELECT 1 FROM dual; 最后一次select必须有2.7 视图create view person_view as select * from persons where first_name like ‘%f%‘select * from person_view;备注视图是需表只是逻辑定义除非是一种物化视图那个才是有物理占用2.8 索引create index person_index on persons (first_name);
