Oracle学习笔记四 SQL命令（二）：SQL操作语言类别

SQL分为下列语言类别
　　1.数据定义语言（DDL）
　　Create、Alter、Drop

　　2.数据操纵语言（DML）
　　Insert、Select、Delete、Update

　　3.事务控制语言（TCL）
　　Commit、Savepoint、Rollback

　　4.数据控制语言（DCL）
　　Grant、Revoke

数据定义语言（DDL）

数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象

用于操纵表结构的数据定义语言命令有：

　　1.CREATE TABLE

　　2.ALTER TABLE

　　3.TRUNCATE TABLE

　　4.DROP TABLE

创建表

--建立一个微博的web程序，从而创建一个微博的用户表

--用户表信息 帐号，密码，昵称，性别

Create table  blog_user(
 User_name varchar2(20) not null, --用户名
 User_pwd varchar(20) not null,  --用户密码
 User_age int default 15,     --年龄
 User_sex char(2) default '男', --性别
 User_alias varchar(1)  --昵称
)

Create table blog_article(
Art_id int not null,
Art_author varchar2(20) not null,
Art_content varchar2(100),
Art_time date
)

给表中添加主键或外键

1、创建表时同时创建主键约束

（1）无命名：

create table student (studentId int primary key not null, studentName varchar(8), age int)；

（2）有命名：

create table student(studentId int, studentName varchar(8), age int, constraint yy primary key(studendId))；

2、删除表中已有的主键约束
（1）无命名可用 

select * from user_cons_columns;

查找表中主键名称得student表中的主键名为SYS_C002715

alter table student drop constraint SYS_C002715;

（2）有命名

alter table students drop constraint yy;

3、向表中添加主键约束

alter table student add constraint pk_student primary key(studentid);

4、向表中添加外键约束

alter table table_A add constraint FK_name foreign key(id) references table_B(id);

修改表

--修改列的长度

alter table blog_user Modify user_alias varchar(10) 

--添加一列

 Alter table blog_user Add birth date   --增加一个新列

--删除一列

alter table 表名 drop column 列名;

--设置用户名为主键:add constraint 约束名  约束类型

Alter table blog_user Add constraint Pk_user_name primary Key(user_name)

--检查约束，年龄不能为负数

Alter table blog_user Add constraint ck_age check(User_age>0 and User_age<150)

--删除某个约束；

Alter table blog_user_age Drop constraint 约束名;

--添加一个外键

Alter table blog_article Add constraint fk_user_name_art_name foreign Key(Art_author) References blog_user(user_name)

--修改表列名

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名

--修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

--普通删除(数据库回收站中还会有)
drop table 表名

--彻底删除表的命令如下：
drop table 表名 purge

数据操纵语言（DML）

数据操纵语言用于检索、插入和修改数据

数据操纵语言是最常见的SQL命令

数据操纵语言命令包括：

　　SELECT

　　INSERT

　　UPDATE

　　DELETE

伪列

• Oracle 中伪列就像一个表列，但是它并没有存储在表中
• 伪列可以从表中查询，但不能插入、更新和删除它们的值
• 常用的伪列有ROWID和ROWNUM
• ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行
• ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

伪列的使用

Select rownum, rowid,tablename.* from tablename;

DML:数据操纵语言，数据的增删改查操做

Insert     delect     select     update

--创建一个表，数据来自另外一个表
Create table emp2 as select empon, ename,job,salfrom emp

--Select: 查询
Select 列名 from表名，其他查询 [where 条件  group by 列名 having 子句 order by 列名]

--查询整个blog_user表的所有信息
Select * from blog_user;

--只查询名字和密码，昵称 这三列
Select user_name,user_pwd,user_alia from blog_user;

--给列取名
Select user_name as uanem,user_pwd as upwd from blog_uesr

-计算列：列是通过计算而来的（并不存在）
Select sa1,sa1*2 as 双倍工资 from emp; --双倍工资是通过计算来的

--去除重复数据：把内容完全一样的重复数据去除
Select distinct job from emp;

有条件的查询：where 条件

Select * from emp where job='SALESMAN' –所有的销售人员

--获取系统的日期格式
Select sysdate from dual;  -- 22-10月-15

Select * from emp where hiredate  <'1-6月-81'>；-受雇佣在81年6月22日

多个查询条件：and和or组合

--如何判断用户输入是正确的用户名和密码
Select * from blog_user where user_name='admin' and user_pwd='123'

成绩在80到100分之间的所有记录
Select * from表名 where 列名 between 80 an 100;

成绩分别为80.90/100的记录，并按成绩的高低排序
Select * from 表名 where score in(80, 90, 100) score = 80 or score=90 or score=100;

模糊查询

    Like 条件;
    % _   ('%'表示0个，1个或多个字符、'_'表示一个字符)表示通配符，代表任意的内容，代表一个任意内容

--找出所有姓陈的人
Select * from blog_user where user_name like '陈%'
--第三个字母为A的人
Select * from blog_user where user_name like '__A%'

--查找年龄为20、25岁的数据
Select * from blog_user where user_age in(20, 25);

插入数据

--指定列名：
insert into 表名(列名1, 列名2， ……) values(值1, 值2, ……);
Insert into blog_user(user_name, user_pwd, user_sexy) values('old_2',  '123', default)
    
--未指定列名：
insert into 表名 values(值, 值, ……);  有几列就需要几个值
    
--同时插入多条语句
Insert into blog_user(user_name, user_pwd) select ename, empno from emp  --数据来自其他表，需要注意数据类型和个数

修改数据

Udpate 表名 set 列名=新值, 列名2=新值 …… where 条件

Update blog_user set user_sexy='女' where user_name='JONES';
    
--修改多列
Update blog_user set user_age=20, user_alias='老大' where user_name='KING';
    
--模糊匹配修改数据
Update blog_user set user_age = 20 where user_name like 'J%'

删除数据

--Delete 表名 where 条件
    
Delete blog_user where user_name = 'old_2'
    
--删除多行
Delete blog_user where user_age < 18;
Delete bolg_user where user_age is null;  --删除所有age列为null的数据
Delete blog_user where user_age in not null; --删除非空的
    
Truncate table 表名;  --效率比delete要高

事务控制语言（TCL）

事务是最小的工作单元，作为一个整体进行工作

保证事务的整体成功或失败，称为事务控制

用于事务控制的语句有：

　　COMMIT - 提交并结束事务处理

　　ROLLBACK -  撤销事务中已完成的工作

　　SAVEPOINT – 标记事务中可以回滚的点

    
Commit:提交事务，保存。
Rollback:回滚事务，返回到事务开始之前的状态。
Savepoint:保存事务点（类似存档），事务可以回滚到这个位置点。

Delete blog_user where user_age < 22;  --删除所有小于22岁的用户
Savepoint del22;  --保存这个事务，命名为del22
Delete blog_user;  --删除所有数据
Rollback to savepoint del22;  --返回所有事务，只返回上一个保存点之后的数据

数据控制语言（DCL）

数据控制语言为用户提供权限控制命令

用于权限控制的命令有：

　　GRANT 授予权限

　　REVOKE 撤销已授予的权限

GRANT 命令可用于为用户分配权限或角色

GRANT CONNECT TO MARTIN;  --CONNECT角色允许用户连接至数据库，并创建数据库对象

GRANT RESOURCE TO MARTIN;  --RESOURCE角色允许用户使用数据库中的存储空间

GRANT CREATE SEQUENCE TO MARTIN;  --此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中

 

授予用户 MARTIN 操作TEST表对象的权限  

GRANT SELECT ON TEST TO MARTIN;  --允许用户查询 TEST 表的记录

GRANT UPDATE ON TEST TO MARTIN;  --允许用户更新 TEST 表中的记录

GRANT ALL ON TEST TO MARTIN;  --允许用户插入、删除、更新和查询TEST 表中的记录

ALTER USER 命令可用于更改口令

ALTER USER MARTIN IDENTIFIED BY martinpass;  --修改 MARTIN 用户的密码

DROP USER 命令用于删除用户

DROP USER MARTIN CASCADE;  --删除 MARTIN 用户模式

撤销，收回权限

Revoke 权限名 [on 表名]  from 角色名;