sqlpluls oracle plsql 需要记住

Sqlplus / as sysdba;

alter user sys identified by"123";

连接数据库：conn hr/hr;

SELECT * FROM ALL_ALL_TABLES;

SELECT * FROM all_users;

换数据库：

SELECT * FROM DUAL;

CREATE TABLE MYTABLE(STUNUMBER VARCHAR2(20) PRIMARY KEY,

STUNAME VARCHAR2(45) NOT NULL,

STUPWD VARCHAR2(20) NOT NULL);

SELECT * FROM MYTABLE;

INSERT INTO MYTABLE VALUES (123, 'HELLO', 'KITTY');

INSERT INTO MYTABLE VALUES ('124', 'HELLO', 'KITTY');

UPDATE MYTABLE M SET M.STUPWD = 'KITTT';

DELETE FROM MYTABLE M WHERE M.STUNUMBER = '123';

--思考 ‘ 和 " 或者说应用特殊字符

CREATE TABLE ANOTHER AS

SELECT * FROM MYTABLE;

SELECT * FROM ANOTHER;

INSERT INTO ANOTHER

SELECT * FROM MYTABLE;

--update table's keyword

ALTER TABLE ANOTHER ADD(AGE NUMBER(3));

ALTER TABLE ANOTHER ADD(SEX NUMBER(3));

ALTER TABLE ANOTHER DROP COLUMN AGE;

ALTER TABLE ANOTHER DROP(AGE, SEX);

ALTER TABLE ANOTHER MODIFY AGE VARCHAR2(9);

ALTER TABLE ANOTHER rename column AGE to aage;

ALTER TABLE ANOTHER RENAME TO OTHER;

ALTER TABLE ANOTHER MOVE TABLESPACE USERS;

SELECT TABLESPACE_NAME, TABLE_NAME

FROM USER_TABLES

WHERE TABLE_NAME = 'ANOTHER';

DROP TABLE ANOTHER;

DROP TABLE ANOTHER CASCADE CONSTRAINTS;

ALTER TABLE ANOTHER READ ONLY; --READ WRITE

--定义和管理数据完整性约束

ALTER TABLE ANOTHER MODIFY AGE NOT NULL;

ALTER TABLE ANOTHER MODIFY AGE NULL;

ALTER TABLE ANOTHER ADD CONSTRAINT P_K PRIMARY KEY(STUNUMBER);

ALTER TABLE ANOTHER DROP CONSTRAINT P_K;

ALTER TABLE ANOTHER DISABLE CONSTRAINT P_K;

ALTER TABLE ANOTHER ENABLE CONSTRAINT P_K;

ALTER TABLE ANOTHER ADD CONSTRAINT A_F_K FOREIGN KEY(STUNUMBER) REFERENCES MYTABLE ON DELETE CASCADE;