Oracle學習筆記四 SQL命令（二）：SQL操作語言類別，oracle學習筆記

Oracle學習筆記四 SQL命令（二）：SQL操作語言類別，oracle學習筆記

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 角色名;