orcale学习笔记

跟着书上敲的一些常用命令，记录一下，方便以后使用。

（使用PLSQL developer连接oracle时，system用户登录的话，connect as 是啥都能登进去，但是用scott用户登录时必须指定connect as sysdba才能成功登录，否则会报用户名，密码错误）

  创建数据库，解锁账户

1.连接数据库
    sqlplus system/yourPassword@orcl as sysdba

2.orcle账户解锁
    select username ,account_status from dba_users;
     alter user scott account unlock;
     alter user scott identified by root;
     select username ,account_status from dba_users where username='SCOTT';（注意：orcle数据字典里的字段是区分大小写的）

3.查询表空间：
SQL> col tablespace_name format A50;
SQL>  col account_status format A99;
select file_name,tablespace_name from dba_data_files where tablespace_name='SYSTEM';

4.命令行输入dbca可以快速打开Oracle的配置助手（从而进行创建，删除，配置数据库）

报表输出部门人数：

/*设置页眉，页脚*/

Ttitle left '日期：' _date center '使用报表统计各部门人数'

Btitle center '谢谢使用报表功能' right '页：' format 999 skip 2 sql.pno

set Echo off

/*每页显示行数*/

set pagesize 40

/*每行显示的字数*/
set linesize 100

/*清除列的格式化*/

clear columns

/*格式化输出列*/

col empno heading '员工编号' format 9999

col ename heading '员工姓名' format A10
col hiredate heading '受雇日期' justify center
col sal heading '员工工资' format $999,999.99
col deptno heading '部门编号' format 9999

/*重复列不显示*/
break on deptno

/*统计部门人数*/
compute count label '部门人数:' of empno on deptno

select empno,ename,mgr,hiredate,sal,deptno from scott.emp order by deptno;

/*清除格式，关闭页眉页脚*/
clear columns

Ttitle off
Btitle off

建表空间，给表分配用户，给用户授权， 建表

drop tablespace root including contents and datafiles; 
create tablespace  root datafile 'D:\\softManager\\install\\oracle\\app\\lmj\\LTableSpace\\root.dbf' size 5M autoextend on
    next 32m maxsize 2048m
  extent management local;
  
drop user root cascade;
create user root identified by root
default tablespace root
temporary tablespace temp;  
 
grant connect,resource,dba,create session,create table,create view,create sequence,unlimited tablespace to lmj;

/*切换成root用户登录*/

drop table student cascade constraints;
create table student(  
  sno      number(5)   primary key,  
  sname    varchar2(20),  
  sex     char(2)  not null,
  sage    number(2),
  sdept   varchar2(20)
);  
 drop table course cascade constraints;
create table course(  
  cno      number(1)   primary key,  
  cname    varchar2(30) not null,  
  cpno     number(1),
  credit    number(1) not null
); 
 drop table sc cascade constraints;
create table sc(  
  cno      number(1),  
  sno      number(5),  
  grade     number(3),
  constraint PK_SC primary key (sno, cno),
  constraint fk_cno foreign key(cno) references course (cno) on delete cascade,
  constraint fk_sno foreign key(sno) references student (sno) on delete cascade
); 

查看表结构：（sql window下）

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_cols where table_name='TEST';

命令行查看表结构：

     desc sc

 

 

创建同义词：

create public synonym c for root.course;

查询同义词

select * from dba_synonyms;

查询可更新的列：

 select * from user_updatable_columns where TABLE_NAME='VIEW_SC';

创建视图

create view view_sc as select * from student where sex='男' and sdept='IS';

创建索引：

create index sname_index on student(sname);

添加约束

alter table student add check(sex in ('男','女'));

设置列不可用：

alter table student set unused(tel);

修改列：

alter table student add tel number(11);

 alter table student modify sdept varchar2(30);