oracle学习笔记（4）_orcle中的基本命令

一，cmd中运行的命令

命令行中输入：“sqlplusw”命令，启动oracle自带的pl/sql
命令行中输入：“sqlplus /nolog” 启动oracle自带的pl/sql,
此时只是启动，连接需要下面的代码：Conn system/tiger as sysdba; 
连接数据库http://localhost:5560/isqlplus/使用浏览器进行远程登录并进行数据库操作了
emctl start dbconsole，就可以启动 OracleDbConsole 服务，
http://localhost:5500/em 启动dbconsole（其中可能出现问题，在博客"Environment variable ORACLE_SID not defined. Please defineit"有详细说明）
oracle的启动
startup open  //注意cmd中不要“；”

oracle的关闭
shutdown immediate

二，用户和权限

1.创建用户

CREATE USER 用户名 IDENTIFIED BY 密码 [ACCOUNTLOCK|UNLOCK] //锁定状态无法登陆

例如：

create user tom identified by tom accountunlock;

2.授权

语法：GRANT角色|权限 TO 用户（角色）

SQL> grant connect to tom; 
Grant succeeded 
SQL> grant resource to tom ; 
Grant succeeded

•  CONNECT 角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予 他们 CONNECT role。CONNECT 是使用 Oracle 的简单权限，拥有 CONNECT 角色的用 户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。
•  RESOURCE 角色，更可靠和正式的数据库用户可以授予 RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器 （trigger）、索引（index）等。
•  DBA 角色，DBA role 拥有所有的系统权限----包括无限制的空间限额和给其他用户授 予各种权限的能力。用户 SYSTEM 拥有 DBA 角色。
• 一般情况下，一个普通的用户（如 SCOTT），拥有 CONNECT 和 RESOURCE 两个角色即可进行常规的数据库开发工作。

3.回收权限

REVOKE   角色|权限   FROM   用户 （角色）
SQL> revoke resource from tom; 
Revoke succeeded 

4.修改用户的密码 

ALTER USER 用户名 IDENTIFIED BY 新密码
SQL> alter user tom identified by aa; 
User altered

 

5.修改用户处于锁定（非锁定）状态

ALTER USER 用户名 ACCOUNTLOCK|UNLOCK
SQL> alter user scott account unlock; 
User altered

 

三，基本SQL语句

1.Oracle SQL 语句由如下命令组成： 

•   数据定义语言（DDL），包括 CREATE（创建）命令、ALTER（修改）命令、DROP（删 除）命令等。
•  数据操纵语言（DML），包括 INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）等。
•  数据查询语言（DQL），包括基本查询语句、Order By 子句、Group By 子句等。
•   事务控制语言（TCL），包括 COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
• 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

 2.oracle中的数据类型

 

类型	含义
CHAR(length)	存储固定长度，length是最大位数，如果位数小于 length，用空格填充。默认长度是 1，最长不超过 2000 字节
VARCHAR2(length)	存储可变的长度，length是最大位数，如果位数小于 length，用空格填充。默认长度是 1，最长不超过 4000字节
NUMBER(p，s)	既可以存储浮点数，也可以存储整数，P表示数字的最大位数（如果是小数，包括整数部分小数点和小数部分，p默认38位），s是小数位数
DATE	存储日期和时间，存储纪元、4 位年、月、日、时、分、秒，存储时间。从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后 6 位，同时包含时区
CLOB	存储大的文本，比如存储非结构化的 XML 文档
BLOB	存储二进制对象，如图形、视频、声音等

 

3.日期函数

 sysdate 内置函数可以获取当前的系统日期和时间，返回 DATE类型，

 systimestamp 函数可以返回当前日期、时间和时区。

 

SQL> select sysdate ,systimestamp fromdual; 
SYSDATE    SYSTIMESTAMP
-------------------------------------------------------------------------------------------
2013-08-14 14-8月 -13 04.27.56.687000 下午 +08:00
 

Oracle 的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from 后面使用 DUAL 表，dual 表在系统中只有一行一列，该表在输出单行函数时为了 

select…from 的语法完整性而使用。

 

4.创建表和约束

CREATE TABLE INFOS    
(    
STUID VARCHAR2(7) NOT NULL,  --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL,  --姓名
GENDER VARCHAR2(2) NOT NULL,  --性别
AGE NUMBER(2) NOT NULL,  --年龄
SEAT NUMBER(2) NOT NULL,  --座号
ENROLLDATE DATE,  --入学时间  
STUADDRESS VARCHAR2(50) DEFAULT '地址不详',--住址
CLASSNO VARCHAR2(4) NOT NULL  --班号 班号=学期序号+班级序号
);
增加约束条件：
--增加主键的约束
ALTER TABLE INFOS ADD CONSTRAINT PK_INFOSPRIMARY KEY(STUID)
/
--性别只能是男或女
ALTER TABLE INFOS ADD CONSTRAINTCK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女')
/
--范围约束
ALTER TABLE INFOS ADD CONSTRAINTCK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50)
/
ALTER TABLE INFOS ADD CONSTRAINTCK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100)
/
ALTER TABLE INFOS ADD CONSTRAINTCK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR
(CLASSNO >='2001' ANDCLASSNO<='2999'))
/

  在 Oracle 代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过 语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

CREATE TABLE SCORES            
(                
ID NUMBER ,   --ID  ① 
TERM VARCHAR2(2),                --学期 S1或S2
STUID VARCHAR2(7) NOT NULL,     --学号
EXAMNO VARCHAR2(7) NOT NULL,         --考号 E+班号+序号
WRITTENSCORE NUMBER(4,1) NOT NULL,    --笔试成绩
LABSCORE NUMBER(4,1) NOT NULL       --机试成绩
)
/
ALTER TABLE SCORES ADD CONSTRAINTCK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')
/ 
--外键约束
ALTER TABLE SCORES ADD CONSTRAINTFK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID)
/

 

SQL Server 中可以使用 identify 创建自动增长列，但是Oracle 中的自动增长需要借助序列（Sequence）完成

5.数据操纵语言（DML）

（1）简单查询

SELECT   *|列名|表达式    FROM      表名   WHERE    条件     ORDER BY    列名

（2）根据结果集创建表

CREATE    TABLE     表名    AS    SELECT    语句
SQL> CREATE TABLE INFOS1 AS SELECT *FROM INFOS;
TABLE CREATED

使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。

如果只复制表结构，只需使查询的条件不成立（比如 where 1=2），就不会查询从出任何数据，从而复制一个表结构。 

SQL> CREATE TABLE INFOS2 AS SELECT *FROM INFOS WHERE 1=2; 

（3）插入

INSERT   INTO    表名(列名 1，列名 2……)  VALUES (值 1，值 2……)

列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。 

值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

（4）插入一个结果集

SQL> INSERT INTO INFOS2 SELECT * FROMINFOS;
SQL> INSERT INTO INFOS 
SELECT 's100106','卢俊义','男',23,5,TO_DATE('2009-8-908:00:10','YYYY-MM-DD HH24:MI:SS'), '青龙寺','1001' FROMDUAL; 
1 rows inserted 
SQL>COMMIT; 

（5）update 更新操作

UPDATE    表名   SET   列名 1=值，列名 2=值…… WHERE   条件

 （6）删除 delete

DELETE    FROM    表名    WHERE      条件

  （7） truncate 删除操作

TRUNCATE  TABLE 表名

把表中的所有数据一次性全部删除

TRUNCATE 和 DELETE 都能把表中的数据全部删除，他们的区别是： 

1.      TRUNCATE 是 DDL 命令，删除的数据不能恢复；DELETE 命令是 DML 命令，删除后的数据可以通过日志文件恢复。 

2.      如果一个表中数据记录很多，TRUNCATE相对 DELETE 速度快。 

由于 TRUNCATE 命令比较危险，因此在实际开发中，TRUNCATE 命令慎用。

 

 修改列名

alter table taw_wp_assess_model_content  drop column ASSESS_MODEL_ID;
alter table taw_wp_assess_model_content  drop column YEAR_CONTENT_ID;
alter table taw_wp_assess_model_content  drop column SCORE;

alter table taw_wp_assess_model_content  add  model_content_id VARCHAR2(32);
alter table taw_wp_assess_model_content  add  plan_function_id VARCHAR2(32);
alter table taw_wp_assess_model_content  add  SCORE INTEGER;

 

修改列的类型
 

alter table TAW_WP_ASSESS_MODEL_CONTENT modify score NUMBER(10,2);