oracle_基本操作_建表_数据库函数_查

/*
    基本操作
*/

控制台，实例服务，监听：三者服务必须启动

用户名:sys as sysdba
口令:
或
cmd-->  sqlplus scott/tiger 或 sqlplus nolog (不定用户登进去)

disc ;   //断开连接，不退出
run ;  //与/,意义相同
list ;  //已往命令查询
rollback ; //回滚操作，查看更改之前的状态，撤销更改
commit ;  //确认更改

show user; //显示当前用户
conn scott/tiger; //切换到scott
conn scott/tiger as sysdba;
sqlplus sys/tiger@orcl as sysdba; 
show linesize;  //默认为80
show linezise 200;
show pagesize; //显示页码尺寸
set pagesize 30; //增加每页行数为30
/ ;   //上一条命令
col job for a8;  //给job折行
help reserved words; //用于查询关键字

conn sys/system as sysdba;  //未加as sysdba,出现DRA_28009
conn system/admin@orcl as sysdba;

alter user scott account unlock; //解锁scott用户
desc scott.emp; //查看表emp有哪些字段
edit;  //调开缓冲区
edit d:\test.txt; //生成文件
save d:\test.txt; //保存
start d:\test.txt; //启动
help exit;  //帮助
clear buff; //清除缓冲区
clear scr; 	//清屏
@ d:\test.txt;  //执行文件

set wrap off;  //截断行，使其显示在一行
spool d:\test.txt;  //创建文件
spool off;  //关闭
comment on table emp is '员工信息表'; //对表做注释
comment on column emp.ename is '员工名'; //对列做注释
truncate table emp; //物理性删除表
drop table emp;  //能找回表

set time on; //显示系统时间
set time off; //关掉时间

/*
    建表
*/

//要在纯英文环境下输入，若提示缺失右括号，说明出现了中文字符
create table T_student(   
    id number(8) constraint pk_student primary key, //约束名：pk_student
    phone varchar(11) constraint  un_student unique, 
    sex varchar(11) constraint ck_student check(sex in('男','女')),
    email varchar(11) constraint nn_student not null
);

//复制一张T_student表
create table T_student_cp as  
              select * from T_student; 

				
//创建表student_emp,只复制两张表结构里的部分字段,不复制数据,得到空表	 
create table student_emp as  
	      select id,empno from T_student,emp
	          where 1=2; 

alter table emp modify ename char(20); //修改列类型

alter table ex modify ename default 'aaa';  //设默认值

alter table emp rename column hiredate to hire-date; //该列名

alter table ex drop column tel; ///删除列

alter table T_student drop constraint nn_student;  //按约束名删除约束

alter table T_student modify name not null;  //为name添加非空约束

alter table T_student add constraint pk_student primary key(id); //添加主键

alter table T_student add email varchar(20);  //添加列email

/*
    数据库函数
*/

select 2*3 from dual;  //计算2*3

select lower(ename) from emp; //变小写，upper大写

select substr('hello',1,3) from dual; 

select ascii('A') from dual; //A的ASCII值 

select round(127.893,1) from dual; //127.9

select sum(sal),avg(sal),max(sal),min(sal) from emp; //聚合函数

select max(ename) from emp;  //排序ASCII

select count(*) from emp;  //有多少条记录

select sum(sal) from emp group by deptno;  //返回三条记录

select sysdate from dual;

//存储过程函数名：show_empno		
//查看函数show_empno过程的源码
select text from user_source where name='show_empno'; 

/*
    查
    
    执行顺序: where > group by > having > select > order by
                      字表         挑组          选         投影
*/

select * from scott.emp; //当前用户为sysdba,查看scott的emp表

select table_name from user_tables; //查看有多少表

select * from user_tab_comments where table_name='student'; //查看表注释

select * from dec; //查看字典

select deptno,max(sal) from emp 
                       group by deptno  //先分组
		           having sum(sal)>10000; //后用having挑选三个部门中超10000的

select * from emp where sal=
		(select max(sal) from emp);
						
select to_char(hiredate,'yyyy'),count(*) from emp  //统计每个年度的入职人数
	                group by to_char(hiredate,'yyyy');               
						
select empno,ename from emp where sal>&x;   //交互命令

select empno,ename from emp where job='&emp_job'; //输入字符型 
			
select * from emp where sal>2000 and sal<3000;

//查询字段对应的约束名 
select table_name,constraint_name,column_name from user_cons_columns 
				where table_name='T_STUDENT'; //表名必须大写

select * from emp where sal not between 800 and 1500; 

select * from emp where ename like '%s%';  //S有大小写之分 

				
select * from emp where sal>=   //查询薪水大于10号部门平均工资的员工	
		(select avg(sal) from emp where deptno=10); 

						
select deptno,avg(sal) from emp group by deptno; //按分组号排序，查出每个组平均工资

select deptno,avg(sal) from emp group by deptno   //先按组号分组，
				having avg(sal)>2000; //再选出平均工资>2000

								
select * from emp order by deptno,sal desc;  //先按部门号排序，再按工资排序

select * from emp where rownum<=2; //只查前两条



/*********************************多表联查***********************************/	
//左连接
select s.id student_id, b.id book_id from T_student s 
	left join t_book b on s.book_id=b.id;
或
select s.id student_id,b.id book_id from T_student s,t_book b
	where s.book_id=b.id(+);
						
	
//右连接
select s.id student_id, b.id book_id from T_student s 
        right join t_book b on s.book_id=b.id;
或
select s.id student_id,b.id book_id from T_student s,t_book b
	where s.book_id(+)=b.id;


//全连接，T_student表与T_book表全显示
select s.id student_id,b.id book_id from T_student s
	full join t_book b on s.book_id=b.id;


//内连接,根据T_student外键连接到T__book表查询，等值查询(只显示匹配上的)
select s.id student_id, b.id book_id from T_student s 
	join t_book b on s.book_id=b.id;


//条件查询，与内连接结果一致						
select s.id student_id,b.id book_id from T_student s,t_book b
	where s.book_id=b.id;

 

mark：学习笔记