Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks.
Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks.
Oracle数据库操作基本语法
Provides conceptual and usage information about Oracle SQL Developer, a graphical tool that enables you to browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export data; migrate third-party databases to Oracle; view metadata and data in MySQL and third-party databases; and view and create reports
创建表
//创建表
CREATE TABLE classes(
classId NUMBER(2),
cname VARCHAR2(40),
birthday DATE
);
添加一个字段
//添加一个字段
ALTER table classes ADD (sex VARCHAR2(3));
修改字段長度
//修改字段長度
ALTER table classes MODIFY(cname VARCHAR2(20));
修改字段的类型/或是名字(不能有数据)
//修改字段的类型/或是名字(不能有数据)
alter table student modify(xm char(30));
删除一个字段
//删除一个字段
alter table student drop column sal;
重新命名表的名字
//重新命名表的名字
RENAME classes to cls;
删除表
//删除表
drop table cls;
插入数据
//插入数据
select * from cls;
INSERT INTO cls VALUES('01','土狗','06-6月-18','男');
INSERT INTO cls values('02','光頭強',to_date('08-8-18','yyyy-mm-dd'),'男');
INSERT INTO cls values('03','碧亮',to_date('07/7-18','yyyy/mm/dd'),'男');
INSERT INTO cls(CLASSID,CNAME,BIRTHDAY,SEX) VALUES ('04','郭曉峰',null,'娚');
修改日期输入格式
//修改日期输入格式
//临时生效,重启后不起作用
SQL>alter session set nls_date_format = ‘yyyy-mm-dd’;
第一个字符【名字第一个字符为碧的员工的信息】
//第一个字符【名字第一个字符为碧的员工的信息】
SELECT classid,cname FROM cls WHERE CNAME like '碧%';
其它字符【名字第三个字符为強的员工的信息】
//其它字符【名字第三个字符为強的员工的信息】
SELECT classid,cname FROM cls WHERE CNAME like '__強%';
增加一列名稱為薪水
//增加一列名稱為薪水
ALTER TABLE cls ADD(salaries NUMBER(10,2));
插入薪水的數據
//插入薪水的數據
select * from cls;
UPDATE CLS SET SALARIES = 18888 WHERE CLASSID = '10001';
UPDATE CLS SET SALARIES = 16666 WHERE CLASSID = '10002';
UPDATE CLS SET SALARIES = 8888 WHERE CLASSID = '10003';
UPDATE CLS SET SALARIES = 6666 WHERE CLASSID = '10004';
条件组合查询(与、或)
//条件组合查询(与、或)
SELECT * FROM cls where(SALARIES>10000 OR cname='郭曉峰') and CNAME like '土%';
Order by 排序从低到高[默认]
//Order by 排序从低到高[默认]
SELECT * FROM cls ORDER BY SALARIES asc;
修改字段類型、更新數據
//修改字段類型、更新數據
ALTER table cls MODIFY(classid NUMBER(20));
UPDATE cls SET CLASSID = 10004 where classid = 4;
SELECT * from cls;
年薪13薪按照從低到高排序
//年薪13薪按照從低到高排序
SELECT cname "姓名",salaries*13 "年薪" FROM cls ORDER BY "年薪" ASC;
//最高、最低薪水
SELECT MAX(salaries),Min(salaries) FROM cls;
SELECT cname,salaries FROM cls WHERE SALARIES = (SELECT MAX(salaries) FROM cls);
//展示大於平均的薪水
SELECT * FROM cls WHERE salaries > (SELECT AVG(salaries) FROM cls);
重命名表名,增加hiredate字段,增加數據
//重命名表名,增加hiredate字段,增加數據
RENAME cls to vipmembers;
ALTER TABLE vipmembers ADD(hiredate DATE);
SELECT * FROM VIPMEMBERS;
UPDATE VIPMEMBERS SET hiredate = '09-9月-1999' WHERE CLASSID = '10001';
UPDATE VIPMEMBERS SET hiredate = '08-8月-1998' WHERE CLASSID = '10002';
UPDATE VIPMEMBERS SET hiredate = '01-1月-2011' WHERE CLASSID = '10003';
UPDATE VIPMEMBERS SET hiredate = '05-5月-1997' WHERE CLASSID = '10004';
保存还原点、删除数据
//保存还原点、删除数据
RENAME cls to vipmembers;
SQL>savepoint aa;
【1】SQL>delete from vipmembers; //删除表的数据
【2】SQL>drop table vipmembers; //删除表的结构和数据
【3】SQL>delete from vipmembers where classid=’10001’; //删除一条记录
【4】SQL>truncate table vipmembers; //删除表中的所有记录,表结构还在,不写日志,无法扎找回的记录,速度快
查看表结构
//查看表结构
SQL>desc vipmembers ;
查询指定列
//查询指定列
SQL>select classid,cname,salaries from vipmembers;
打开显示操作时间的开关
//打开显示操作时间的开关
SQL>set timing on;
统计表内有多少条记录
//统计表内有多少条记录
SQL>select count(*) from vipmembers;
将工资小于平均工资并且入职年限早于1999-12-1的人工资增加10%
//将工资小于平均工资并且入职年限早于1999-12-1的人工资增加10%
UPDATE VIPMEMBERS set SALARIES=SALARIES*1.1 WHERE SALARIES<(SELECT AVG(salaries) FROM VIPMEMBERS) AND HIREDATE<'01-12月-99';
子查询(用查询结果创建新表)
//子查询(用查询结果创建新表)
CREATE TABLE vipone(classid,name,sal) as select classid,cname,salaries from VIPMEMBERS;
SELECT * FROM VIPONE;
ALTER TABLE vipone ADD (job VARCHAR2(50));
UPDATE VIPONE SET JOB = '教授' WHERE CLASSID = '10001';
UPDATE VIPONE SET JOB = '專家' WHERE CLASSID = '10002';
UPDATE VIPONE SET JOB = '演員' WHERE CLASSID = '10003';
UPDATE VIPONE SET JOB = '網管' WHERE CLASSID = '10004';
union(求并集), union all , intersect(取交集), minus (差集)
//union(求并集), union all , intersect(取交集), minus (差集)
SELECT classid,cname,salaries,job FROM VIPMEMBERS WHERE SALARIES>10000 INTERSECT SELECT classid,name,sal,job FROM VIPONE where JOB='專家';
内嵌视图
//当在from子句中使用子查询的时候,必须给子查询指定别名
SQL>select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) (as ) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
分页
//orcle为表分配的行号
SQL>select a1.*,rownum rn from (select * from emp) a1;
SQL>select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;
//查询内容的变化
//所有的改动(指定查询列)只需更改最里面的子查询(排序)只需更改最里面的子查询
補充:
//事务 (第一次创建,第二次提交)当退出数据库时,系统自动提交事务
SQL>commit;
SQL>savepoint a1; //创建保存点 (保存点的个数没有限制)
SQL>rollback to aa; //使用保存点回滚到aa
SQL>rollback; //回滚到事务创建开始
//只读事务
SQL>set transaction read only
//Java中的事务
Ct.setAutoCommit(false); //设置事务自动提交为否
Ct.commit(); //提交事务
//字符函数
lower(char) //将字符串转换为小写的格式
upper(char) //将字符串装换为大写的格式
length(char) //返回字符串的长度
substr(char,m,n) //取字符串的子串
SQL>select lower(ename) from emp;
SQL>select ename from emp where length(ename)=5;
你可以自己動手練習Oracle数据库操作基本语法在 Oracle SQL Developer 圖形工具[here][2018].