常用sql進階語句

一、擴充套件資料庫表欄位長度

--mysql
alter table user modify name  varchar2 (32);

--oracle
alter table A modify(name varchar2(4000))

　　

二、給表增加索引（mysql）

1.新增PRIMARY KEY（主鍵索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.新增UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (`column`)

3.新增INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.新增FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.新增多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

 

 

三、多表關聯並update，使用in語句

select * from ok.table_001_reg t where to_char(order_id) in (select to_char(t.order_id) from ok.table_002 t where  t.test_sn ='7777601') for update;
delete  from ok.table_001_reg t where to_char(order_id) in (select to_char(t.order_id) from ok.table_002 t where  t.test_sn  in ('7777601'));

　　

四、檢視與清除死鎖（oracel）

1、檢視已經鎖定的資料庫程式

select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

2、查詢出是哪個sid(session)引起的

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

3、kill程式

ALTER system kill session '789, 7296';

 

五、表空間維護（oracel）

1、建立表空間

CREATE TABLESPACE TBS_TR_DATA
DATAFILE '/oradata/rTBS_TR_DATA_001.dbf'
SIZE 64G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ONLINE;

2、檢視錶空間的狀態

select tablespace_name,status from dba_tablespaces;

3、檢視錶空間物理檔案的名稱及大小

SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;

4、檢視資料表空間使用率

SELECT a.tablespace_name,
a.bytes/(1024*1024) total_M,
b.bytes/(1024*1024) used_M,
c.bytes/(1024*1024) free_M,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

5、查詢表空間每天的使用情況

select a.name, b.*
from v$tablespace a,
(select tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
max(tablespace_usedsize * 8 / 1024) used_size
from dba_hist_tbspc_space_usage
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
trunc(sysdate - 30) group by tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) order by
tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
where a.ts# = b.tablespace_id ;

6、表空間擴容

 

-- 修改建立的資料檔案的大小
SQL> col file_name for a60
SQL> select file_name,bytes/1024/1024 from dba_data_files;
SQL> alter database datafile '/home/oracle/app/oradata/orcl/users01.dbf'resize 51M;
SQL> select file_name,bytes/1024/1024 from dba_data_files;
-- 增加表空間的資料檔案
SQL> alter tablespace andy add datafile '/home/oracle/app/oradata/orcl/andy02.dbf'size 1M
autoextend on next 1m maxsize 2m ;

7、刪除表空間

-- 刪除所有資料庫物件與刪除資料檔案

drop tablespace XXX including contents and contents;

8、 重新命名錶空間

alter tablespace tablespace_name rename to new_table_name;
alter tablespace andy rename to newandy;

9、移動表空間的資料檔案

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY';
SQL> alter tablespace newandy offline;
[oracle@11g ~]$ mv /home/oracle/app/oradata/orcl/andy01.dbf /home/oracle/app/oradata/andy01.dbf
SQL> alter tablespace newandy rename datafile '/home/oracle/app/oradata/orcl/andy01.dbf' to '/home/oracle/app/oradata/andy01.dbf';
SQL> alter tablespace newandy online;
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'NEWANDY';

10、修改表空間的自動擴充套件性

SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
SQL> alter database datafile file_name autoextend off|on [next number K|M maxsize unlimited|number K|M]
SQL> select tablespace_name,status,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

 

六、賦權語句

--從任意ip地址連線的使用者名稱為root，密碼為123的使用者賦予所有的許可權。其中的"%"為任意的ip地址，如果想設為特定的值也可以設定為特定的值。 執行了該語句後就可以在其他機器上以root:123訪問到該機器上了
grant all privileges on   *.* to root@"%" identified by '123' with grant option;   flush privileges;   

具體說明：

--給來自10.163.225.87的使用者joe分配可對資料庫vtdc的employee表進行select,insert,update,delete,create,drop等操作的許可權，並設定口令為123。
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
--給來自10.163.225.87的使用者joe分配可對資料庫vtdc的employee表進行select,insert,update,delete,create,drop等操作的許可權，並設定口令為123。  
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
--給來自10.163.225.87的使用者joe分配可對資料庫vtdc所有表進行所有操作的許可權，並設定口令為123。
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
--給來自10.163.225.87的使用者joe分配可對所有資料庫的所有表進行所有操作的許可權，並設定口令為123。
mysql>grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;
--給本機使用者joe分配可對所有資料庫的所有表進行所有操作的許可權，並設定口令為123。
mysql>grant all privileges on *.* to joe@localhost identified by ‘123′;
--將兩個使用者對兩個表的所有操作許可權收回
REVOKE ALL PRIVILEGES ON TABLE A,B,C FROM U1,U2;
--將所有使用者對錶A的所有查詢許可權收回
REVOKE SELECT ON TABLE A FROM PUBLIC;
--將使用者U1對錶A的Tname的修改許可權收回
REVOKE UPDATE(Tname) ON TABLE A FROM U1;
--單獨密碼修改(mysql)
alter user 'root'@'localhost' identified by '123456'修改root本地密碼為123456