Oracle学习笔记四 SQL命令(二):SQL操作语言类别

时间:2022-10-30 15:30:39

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='' 成绩在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', '', 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 角色名;