----建表
CREATE TABLE STUDENT
( ID NUMBER(38) PRIMARY KEY,
NAME VARCHAR2(30),
CODE VARCHAR2(9),
SEX NUMBER(1),
PHONE VARCHAR2(13),
ADDRESS VARCHAR2(100),
EMAIL VARCHAR2(50)
)
----删除表
DROP TABLE STUDENT
----修改表的某一列
ALTER TABLE STUDENT MODIFY NAME VARCHAR2(35)
----添加新列
ALTER TABLE STUDENT ADD(XX1 VARCHAR2(10),XX2 VARCHAR2(10))
----删除列
ALTER TABLE STUDENT DROP(XX1,XX2)
----创建新的SEQUENCE
CREATE SEQUENCE WXJSEQ INCREMENT BY 1 START WITH 1
SELECT WXJSEQ.NEXTVAL FROM DUAL
----插入新的记录
INSERT INTO STUDENT VALUES (WXJSEQ.NEXTVAL,'王先江', '011150232', '0', '13235915923', '福州市西洪路西洪花园4#603', 'feixiange23@163.com')
--删除表的所有记录,不释放空间,会产生碎片
DELETE STUDENT
----删除记录
DELETE FROM STUDENT
----删除表的所有记录,释放空间,不会产生碎片
TRUNCATE TABLE STUDENT
----删除索引
DROP INDEX IDX_STUDENT_NAME
----建立索引
CREATE INDEX IDX_STUDENT_NAME ON STUDENT(NAME ASC)
----删除视图
DROP VIEW SVIEW
----创建新视图
CREATE VIEW SVIEW AS (SELECT * FROM STUDENT WHERE SEX = 0)
SELECT * FROM SVIEW
SELECT * FROM STUDENT
SELECT S.* FROM (SELECT ROWNUM RN,S.* FROM STUDENT S WHERE ROWNUM <=3) S WHERE S.RN >1
---创建一个指向10g数据库的dblink
---using后接的是目标数据库IP、监听端口、服务名。如果监听端口是1521的话可以省略不写。
---创建的连接的名称为 suk10g,访问数据库的用户名为 suk,密码为:suk
create database link suk10g connect to suk identified by suk using '172.25.4.89:1522/ora10g'
----远程数据库服务名:SKY,用户名:先江,密码:SKY
CREATE DATABASE LINK LINKSKY CONNECT TO XIANJIANG IDENTIFIED BY SKY USING '192.168.1.100:1521/SKY'