你的位置:首页 > 数据库

[数据库]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月-15Select * 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; --保存这个事务,命名为del22Delete 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 角色名;