常用sql进阶语句

一、扩展数据库表字段长度

?

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

?

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

　　

二、给表增加索引（mysql）

1.添加PRIMARY KEY（主键索引）

?

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

2.添加UNIQUE(唯一索引)

?

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

3.添加INDEX(普通索引)

?

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

4.添加FULLTEXT(全文索引)

?

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

5.添加多列索引

?

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

 

 

三、多表关联并update，使用in语句

?

1 2	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、查看已经锁定的数据库进程

?

1 2 3

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)引起的

?

1 2 3

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进程

?

1	ALTER system kill session '789, 7296';

 

五、表空间维护（oracel）

1、创建表空间

?

1 2 3 4 5

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

2、查看表空间的状态

?

1	select tablespace_name,status from dba_tablespaces;

3、查看表空间物理文件的名称及大小

?

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

4、查看数据表空间使用率

?

1 2 3 4 5 6 7 8 9

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、查询表空间每天的使用情况

?

1 2 3 4 5 6 7 8 9 10 11

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、表空间扩容

 

?

1 2 3 4 5 6 7 8

-- 修改建立的数据文件的大小 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、删除表空间

-- 删除所有数据库对象与删除数据文件

?

1	drop tablespace XXX including contents and contents;

8、 重命名表空间

?

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

9、移动表空间的数据文件

?

1 2 3 4 5 6

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、修改表空间的自动扩展性

?

1 2 3

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;

 

六、赋权语句

?

1 2	--从任意ip地址连接的用户名为root，密码为123的用户赋予所有的权限。其中的"%"为任意的ip地址，如果想设为特定的值也可以设定为特定的值。 执行了该语句后就可以在其他机器上以root:123访问到该机器上了 grant all privileges on   *.* to root@"%" identified by '123' with grant option;   flush privileges;

具体说明：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

--给来自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