创建
SQL> CREATE TABLE STUDENT74( //创建表
2 SNUM NUMBER(2),
3 SNAME CHAR(6),
4 SGENDER CHAR(1),
5 SAGE NUMBER(2),
6 SUNI VARCHAR2(25),
7 SZHUANYE VARCHAR2(25),
8 SPHONE NUMBER(11),
9 SIDCARD NUMBER(18),
10 SADDR VARCHAR2(50));
修改表
SQL> ALTER TABLE STUDENT74
2 MODIFY( //修改字段
3 SNUM NUMBER(2) PRIMARY KEY,
4 SNAME CHAR(6) NOT NULL,
5 SGENDER CHAR(1) DEFAULT 'F',
6 SIDCARD CHAR(18))
7 ADD( //添加字段
8 SCOM VARCHAR2(25),
9 SDATE DATE,
10 SSAL NUMBER(5));
Table altered
删除
DROP TABLE STUDENT74; //删除表
DROP COLUMN //删字段
SQL> ALTER TABLE STUDENT74
2 DROP COLUMN SSAL;
Table altered
SQL> DELETE FROM STUDENT74 WHERE SNUM=2; //删除纪录
1 row deleted
SQL> ROLLBACK; //ROLLBACK撤销操作
Rollback complete
SQL> COMMIT //COMMIT确认操作
2 ;
Commit complete
排他锁
SELECT*FROM STUDENT74 FOR UPDATE;
SELECT*FROM STUDENT74 FOR UPDATE WAIT 10;
SELECT*FROM STUDENT74 FOR UPDATE NOWAIT;
保存点:
SAVEPOINT T1; //设置保存点 T1
DELETE FROM STUDENT74 WHERE SNAME='林朝英';
SAVEPOINT T2;
UPDATE STUDENT74 SET SNAME='姥姥' WHERE SNUM=3;
SAVEPOINT T3;
INSERT INTO STUDENT74 (SNUM,SNAME) VALUES(4,'李莫愁');
ROLLBACK TO T2; //回滚到保存点T2
ROLLBACK TO T1;
插入无限纪录(嵌套)
INSERT INTO BONUS SELECT*FROM BONUS; //每执行一次,把表里的纪录复制一遍然后插入表中
CREATE TABLE STUDENT75
AS
SELECT*FROM STUDENT74; //创建新表,内容从74复制
删除表里所有记录
TRUNCATE TABLE BONUS;
SELECT*FROM SCOTT.STUDENT74; //在SYSTEM(非SCOTT)登陆下查SCOTT下的表
同义词:
CREATE SYNONYM STUDENT74 FOR SCOOT.STUDENT74; //在其他用户名下设置同义词,使得可以直接调用STUDENT74
----序列i++
SELECT *FROM BONUS;
INSERT INTO BONUS VALUES('ZHANG','AAA',5000,3000);
COMMIT;
CREATE SEQUENCE S1 START WITH 3000 INCREMENT BY 2; //START WITH从哪个数字开始 INCREMENT BY 2 表示每次增加2
INSERT INTO BONUS VALUES('&ENAME','BBB',3000,S1.NEXTVAL);
SELECT S1.CURRVAL FROM DUAL; //返回当前自增到的值
SELECT S1.NEXTVAL FROM DUAL; //返回下一个值
DROP SEQUENCE S1; //删除序列
EXAMPLE:建一个表,只有学号,用序列添加
CREATE SEQUENCE T1 START WITH 1 INCREMENT BY 1; //创建序列
CREATE TABLE NUM74(NNO NUMBER(3)); //创建表
INSERT INTO NUM74 VALUES(T1.NEXTVAL); //利用序列反复运行添加纪录 如果用CURRVAL 则一直用当前的值
SELECT*FROM NUM74;
授权SCOTT创建视图的权限
GRANT CREATE VIEW TO SCOTT; //需要以SYSTEM/SYS登陆
CREATE VIEW S74_GIRL
AS
SELECT*FROM STUDENT74 WHERE SGENDER='F'; //创建 视图S74_GIRL
SELECT *FROM S74_GIRL; //查询 视图
INSERT INTO S74_GIRL(SNUM,SNAME,SGENDER,SUNI,SMAJ) VALUES(5,'段誉','M','大理','六脉神剑'); //往视图中插入纪录(违规)但一样能插入
CREATE OR REPLACE VIEW S74_GIRL
AS
SELECT*FROM STUDENT74 WHERE SGENDER='F'
WITH CHECK OPTION; //创建带条件的视图 只能插入sgender=f 记录
CREATE OR REPLACE VIEW S74_GIRL
AS
SELECT *FROM STUDENT74 WHERE SGENDER='F'
WITH READ ONLY; //创建只读试图,只能读,不能进行任何修改
给字段加别名:
SELECT EMPNO"员工工号",ENAME "员工姓名",JOB "职位",MGR "上级领导",HIREDATE "入职时间",SAL "薪水",
COMM "奖金",DEPTNO"部门"FROM EMP;
SELECT SAL||'元' "奖金" FROM EMP; 给字段下面的纪录后面加个元 注意:别名放在最后
SELECT E.ENAME "姓名",E.JOB"职位", E.SAL*12+NVL(E.COMM,0)||'元' "年薪" FROM EMP E; //NVL(E.COMM,0) 把E.COMM中为NULL的纪录以0代替
记录替代 DECODE() 函数
SELECT ENAME"员工",DECODE(JOB,'CLERK','普通员工','SALESMAN','销售','MANAGER','经理','ANALYST','财务分析师','PRESIDENT','总裁')"职位"FROM EMP;
SQL> CREATE TABLE STUDENT74( //创建表
2 SNUM NUMBER(2),
3 SNAME CHAR(6),
4 SGENDER CHAR(1),
5 SAGE NUMBER(2),
6 SUNI VARCHAR2(25),
7 SZHUANYE VARCHAR2(25),
8 SPHONE NUMBER(11),
9 SIDCARD NUMBER(18),
10 SADDR VARCHAR2(50));
修改表
SQL> ALTER TABLE STUDENT74
2 MODIFY( //修改字段
3 SNUM NUMBER(2) PRIMARY KEY,
4 SNAME CHAR(6) NOT NULL,
5 SGENDER CHAR(1) DEFAULT 'F',
6 SIDCARD CHAR(18))
7 ADD( //添加字段
8 SCOM VARCHAR2(25),
9 SDATE DATE,
10 SSAL NUMBER(5));
Table altered
删除
DROP TABLE STUDENT74; //删除表
DROP COLUMN //删字段
SQL> ALTER TABLE STUDENT74
2 DROP COLUMN SSAL;
Table altered
SQL> DELETE FROM STUDENT74 WHERE SNUM=2; //删除纪录
1 row deleted
SQL> ROLLBACK; //ROLLBACK撤销操作
Rollback complete
SQL> COMMIT //COMMIT确认操作
2 ;
Commit complete
排他锁
SELECT*FROM STUDENT74 FOR UPDATE;
SELECT*FROM STUDENT74 FOR UPDATE WAIT 10;
SELECT*FROM STUDENT74 FOR UPDATE NOWAIT;
保存点:
SAVEPOINT T1; //设置保存点 T1
DELETE FROM STUDENT74 WHERE SNAME='林朝英';
SAVEPOINT T2;
UPDATE STUDENT74 SET SNAME='姥姥' WHERE SNUM=3;
SAVEPOINT T3;
INSERT INTO STUDENT74 (SNUM,SNAME) VALUES(4,'李莫愁');
ROLLBACK TO T2; //回滚到保存点T2
ROLLBACK TO T1;
插入无限纪录(嵌套)
INSERT INTO BONUS SELECT*FROM BONUS; //每执行一次,把表里的纪录复制一遍然后插入表中
CREATE TABLE STUDENT75
AS
SELECT*FROM STUDENT74; //创建新表,内容从74复制
删除表里所有记录
TRUNCATE TABLE BONUS;
SELECT*FROM SCOTT.STUDENT74; //在SYSTEM(非SCOTT)登陆下查SCOTT下的表
同义词:
CREATE SYNONYM STUDENT74 FOR SCOOT.STUDENT74; //在其他用户名下设置同义词,使得可以直接调用STUDENT74
----序列i++
SELECT *FROM BONUS;
INSERT INTO BONUS VALUES('ZHANG','AAA',5000,3000);
COMMIT;
CREATE SEQUENCE S1 START WITH 3000 INCREMENT BY 2; //START WITH从哪个数字开始 INCREMENT BY 2 表示每次增加2
INSERT INTO BONUS VALUES('&ENAME','BBB',3000,S1.NEXTVAL);
SELECT S1.CURRVAL FROM DUAL; //返回当前自增到的值
SELECT S1.NEXTVAL FROM DUAL; //返回下一个值
DROP SEQUENCE S1; //删除序列
EXAMPLE:建一个表,只有学号,用序列添加
CREATE SEQUENCE T1 START WITH 1 INCREMENT BY 1; //创建序列
CREATE TABLE NUM74(NNO NUMBER(3)); //创建表
INSERT INTO NUM74 VALUES(T1.NEXTVAL); //利用序列反复运行添加纪录 如果用CURRVAL 则一直用当前的值
SELECT*FROM NUM74;
授权SCOTT创建视图的权限
GRANT CREATE VIEW TO SCOTT; //需要以SYSTEM/SYS登陆
CREATE VIEW S74_GIRL
AS
SELECT*FROM STUDENT74 WHERE SGENDER='F'; //创建 视图S74_GIRL
SELECT *FROM S74_GIRL; //查询 视图
INSERT INTO S74_GIRL(SNUM,SNAME,SGENDER,SUNI,SMAJ) VALUES(5,'段誉','M','大理','六脉神剑'); //往视图中插入纪录(违规)但一样能插入
CREATE OR REPLACE VIEW S74_GIRL
AS
SELECT*FROM STUDENT74 WHERE SGENDER='F'
WITH CHECK OPTION; //创建带条件的视图 只能插入sgender=f 记录
CREATE OR REPLACE VIEW S74_GIRL
AS
SELECT *FROM STUDENT74 WHERE SGENDER='F'
WITH READ ONLY; //创建只读试图,只能读,不能进行任何修改
给字段加别名:
SELECT EMPNO"员工工号",ENAME "员工姓名",JOB "职位",MGR "上级领导",HIREDATE "入职时间",SAL "薪水",
COMM "奖金",DEPTNO"部门"FROM EMP;
SELECT SAL||'元' "奖金" FROM EMP; 给字段下面的纪录后面加个元 注意:别名放在最后
SELECT E.ENAME "姓名",E.JOB"职位", E.SAL*12+NVL(E.COMM,0)||'元' "年薪" FROM EMP E; //NVL(E.COMM,0) 把E.COMM中为NULL的纪录以0代替
记录替代 DECODE() 函数
SELECT ENAME"员工",DECODE(JOB,'CLERK','普通员工','SALESMAN','销售','MANAGER','经理','ANALYST','财务分析师','PRESIDENT','总裁')"职位"FROM EMP;