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

时间:2021-06-30 20:02:49

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 employee
group by deptno;

7.多表查询

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

Select * from emp,dept;

等值连接

select empno, ename, sal, emp.deptno, dname from emp, dept
where 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;