oracle数据库之入门级语句(DOS命令符)

1.连接数据库

普通用户连接数据库: 

conn  scott/tiger   --(默认的用户名/密码)、conn 即"connection"连接数据库的意思

超级管理员连接:

 Conn sys/sys as sysdba--oracle数据库不区分大小写

2.基础语句
保存sql文件

save c:\test.txt

编辑sql语句,用记事本编辑

ed c:\1.txt --编辑 SQL 语句ed  --直接打开上一次缓存sql语句

描述数据表的结构

desc employee; --打开employee员工表的结构

3.DDL(改变表结构)

增添一个数据表

Create table student(id number(10),--number(10)表示长度为10的整数,number(10,2)表示长度为10,小数位为2的浮点型数值name varchar2(10)) ;

删除一个表

Drop table student;

清空一个表

Truncate table student;

//正常情况下删除数据， 如果发现删除错了， 则可以通过 rollback 回滚。 如果使用了截断表，
则表示所有的数据不可恢复了.所以速度很快

修改一个表

rename student to stu;--将student表名改为stu

添加列属性

alter table student add age number(5) ;--增加一个年龄字段

修改字段

alter table student modify age varchar2(5); --将原有的age字段类型改为varchar2

4.DML(改变数据结构)

插入数据

insert into student(id,name) values(1001,charles);

insert into student values(1001,charles); --两种写法

修改数据

update student set name='charles' where id=1001

删除数据

delete from student;--注意区别drop

查询数据

select * from student where id=1001

5.1字符函数

这是oracle数据库特有的函数，举几个常用的例子

Upper大写

SELECT  Upper ('abcde') FROM dual ;--dual表是内置的只有一个元组的表

SELECT * FROM emp WHERE ename=UPPER('smith') ;

Lower小写

SELECT lower('ABCDE') FROM dual ;

Initcap首字符大写

Select initcap(ename) from emp;

Substr截取字符串

Select substr(„abcde‟,length(„abcde‟)-2) from dual;

Select substr(„abcde‟,-3,3) from dual;

Length字符长度

Select length(ename) from emp;

Replace替换字符

Select replace(ename,‟a‟,‟A‟) from emp;

Instr查询字符下标

Select instr('Hello World','or') from dual; 

Lpad

lpad('Smith',10,'*') 左侧填充 lpad() *****Smith

Rpad

rpad('Smith',10,'*') 右侧填充 rpad()Smith*****

Trim去除空格

trim(' Mr Smith ') 过滤首尾空格 trim() Mr Smith

5.2日期函数

Months_between()select months_between(sysdate,hiredate) from emp;

Add_months()select add_months(sysdate,1) from dual;

Next_day()select next_day(sysdate,'星期一') from dual;

Last_dayselect last_day(sysdate) from dual;

5.3转换函数

Next_day()select next_day(sysdate,'星期一') from dual;--dual表是内置的只有一个元组的表

Last_dayselect last_day(sysdate) from dual;

5.4通用函数

NVL()函数    若值为null，用相应值替换select nvl(comm,0) from emp;--用0替换空值

6.分组函数

Avg ，max ，min ，sum，count(*)

需要注意的是，不允许在 WHERE 子句中使用分组函数.

分组统计各部门下工资>500 的员工的平均工资、Select avg(sal) from emp where sal>500 group by deptno ;

统计各部门下平均工资大于 500 的部门select deptno,avg(sal) from emp group by deptno having avg(sal)>500 ;

算出部门 30 中得到最多奖金的员工奖金Select max(comm) from emp where deptno = 30 ;

算出部门 30 中得到最多奖金的员工姓名select ename from emp where comm = (select max(comm) from emp wheredeptno=30);

算出每个职位的员工数和最低工资Select job,min(sal),count(*) from emp group by job;

分组嵌套--注意不要嵌套太深

select max(avg(salary))from employeegroup by deptno;

7.多表查询

如果直接从两表查询，而不做任何连接操作，得到的结果集就是笛卡尔积

Select * from emp,dept;

等值连接

select empno, ename, sal, emp.deptno, dname from emp, deptwhere emp.deptno = dept.deptno;

左外连接

select empno,ename,dname from emp left outer join dept on emp.deptno =dept.deptno;

全连接

select empno,ename,dname from emp full outer join dept on emp.deptno =dept.deptno;

8.分页查询

查询第3到第5条记录

select * from (select rownum no,e.* from--内置rownum字段(select * from emp order by sal desc) e where rownum<=5 ) where no>=3;