Oracle中實現MySQL show index from table命令SQL腳本分享，oraclemysql

Oracle中實現MySQL show index from table命令SQL腳本分享，oraclemysql

實驗數據初始化:

復制代碼 代碼如下:
create table t as select * from hr.employees;
create index inx_t1 on t(employee_id,first_name desc,last_name);
create index inx_t2 on t(job_id,hire_date);

顯示該表所有索引的信息。

以dba登錄

復制代碼 代碼如下:
set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col INDEX_NAME format a20;
select INDEX_NAME,
max(decode(COLUMN_POSITION,1,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c1,
max(decode(COLUMN_POSITION,2,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c2,
max(decode(COLUMN_POSITION,3,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c3,
max(decode(COLUMN_POSITION,4,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c4,
max(decode(COLUMN_POSITION,5,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c5
from (
select INDEX_NAME,COLUMN_NAME,COLUMN_LENGTH,COLUMN_POSITION,DESCEND
from dba_ind_columns
where table_owner='LIHUILIN'
AND table_name='T'
order by INDEX_NAME,column_position
) group by INDEX_NAME;

以普通用戶登錄

復制代碼 代碼如下:
set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col INDEX_NAME format a20;
select INDEX_NAME,
max(decode(COLUMN_POSITION,1,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c1,
max(decode(COLUMN_POSITION,2,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c2,
max(decode(COLUMN_POSITION,3,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c3,
max(decode(COLUMN_POSITION,4,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c4,
max(decode(COLUMN_POSITION,5,COLUMN_NAME||','||COLUMN_LENGTH||','||DESCEND,null)) c5
from (
select INDEX_NAME,COLUMN_NAME,COLUMN_LENGTH,COLUMN_POSITION,DESCEND
from user_ind_columns
where table_name='T'
order by INDEX_NAME,column_position
) group by INDEX_NAME;

但是可以看到,以倒序創建的索引字段，都是以SYS等命名。

Oracle把這種倒序創建的索引字段看成函數索引。

它的信息保存在user_ind_expressions視圖。

user_ind_expressions視圖的COLUMN_EXPRESSION字段類型是long型。

王工的版本可以解決這個問題

復制代碼 代碼如下:
CREATE OR REPLACE FUNCTION long_2_varchar (
   p_index_name IN user_ind_expressions.index_name%TYPE,
   p_table_name IN user_ind_expressions.table_name%TYPE,
   p_COLUMN_POSITION IN user_ind_expressions.table_name%TYPE)
   RETURN VARCHAR2
AS
   l_COLUMN_EXPRESSION LONG;
BEGIN
   SELECT COLUMN_EXPRESSION
     INTO l_COLUMN_EXPRESSION
     FROM user_ind_expressions
    WHERE index_name = p_index_name
          AND table_name = p_table_name
          AND COLUMN_POSITION = p_COLUMN_POSITION;

   RETURN SUBSTR (l_COLUMN_EXPRESSION, 1, 4000);
END;
/

復制代碼 代碼如下:
set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col INDEX_NAME format a20;
SELECT INDEX_NAME,
         MAX (DECODE (COLUMN_POSITION, 1, COLUMN_NAME || ' ' || DESCEND, NULL))
            c1,
         MAX (DECODE (COLUMN_POSITION, 2, COLUMN_NAME || ' ' || DESCEND, NULL))
            c2,
         MAX (DECODE (COLUMN_POSITION, 3, COLUMN_NAME || ' ' || DESCEND, NULL))
            c3,
         MAX (DECODE (COLUMN_POSITION, 4, COLUMN_NAME || ' ' || DESCEND, NULL))
            c4,
         MAX (DECODE (COLUMN_POSITION, 5, COLUMN_NAME || ' ' || DESCEND, NULL))
            c5
    FROM ( SELECT a.INDEX_NAME,
                   REPLACE (
                      DECODE (
                         descend,
                         'DESC', long_2_varchar (b.index_name,
                                                 b.table_NAME,
                                                 b.COLUMN_POSITION),
                         a.column_name),
                      '"',
                      '')
                      COLUMN_NAME,
                   a.COLUMN_LENGTH,
                   a.COLUMN_POSITION,
                   DESCEND
              FROM user_ind_columns a
                   LEFT JOIN
                   user_ind_expressions b
                      ON a.index_name = b.index_name
                         AND a.table_name = b.table_name
             WHERE a.table_name = 'T'
          ORDER BY INDEX_NAME, column_position)
GROUP BY INDEX_NAME;

為何mysql用SHOW TABLE STATUS 查看表的記錄數與負責終端查看到的記錄數不一樣？

與你使用的表引擎有關系。
官方文檔的說明：
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
在myisam這種不支持事務的引擎中，這個值是精確的，在innodb這種支持事務的引擎中，是估算的。
dev.mysql.com/...s.html
 

MySQL的基本命令

啟動：net start mySql;
進入：mysql -u root -p/mysql -h localhost -u root -p databaseName;
列出數據庫：show databases;
選擇數據庫：use databaseName;
列出表格：show tables；
顯示表格列的屬性：show columns from tableName；
建立數據庫：source fileName.txt;
匹配字符：可以用通配符_代表任何一個字符，％代表任何字符串;
增加一個字段：alter table tabelName add column fieldName dateType;
增加多個字段：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令輸入:注意不能將單詞斷開;當插入或更改數據時，不能將字段的字符串展開到多行裡，否則硬回車將被儲存到數據中;
增加一個管理員帳戶：grant all on *.* to user@localhost identified by "password";
每條語句輸入完畢後要在末尾填加分號';'，或者填加'\g'也可以；
查詢時間：select now();
查詢當前用戶：select user();
查詢數據庫版本：select version();
查詢當前使用的數據庫：select database();

1、刪除student_course數據庫中的students數據表：
rm -f student_course/students.*

2、備份數據庫：(將數據庫test備份)
mysqldump -u root -p test>c:\test.txt
備份表格：(備份test數據庫下的mytable表格)
mysqldump -u root -p test mytable>c:\test.txt
將備份數據導入到數據庫：(導回test數據庫)
mysql -u root -p test

3、創建臨時表：(建立臨時表zengchao)
create temporary table zengchao(name varchar(10));

4、創建表是先判斷表是否存在
create table if not exists students(……);

5、從已經有的表中復制表的結構
create table table2 select * from table1 where 1<>1;

6、復制表
create table table2 select * from table1;

7、對表重新命名
alter table table1 rename as table2;

8、修改列的類型
alter table table1 modify id int unsigned;//修改列id的類型為int unsigned
alter table table1 change id sid int unsigned;//修改列id的名......余下全文>>