[TOC] ## 數據庫操作 ### 檢測數據庫是否存在 ``` select COUNT(1) from sysobjects where NAME='antdbms_jiuqi' and SUBTYPE$ is null ``` ## 添加數據 ### 對自增字段進行賦值 ``` SET IDENTITY_INSERT "md_user" ON; INSERT INTO "md_user" ("user_id" , "login_name" ) VALUES (1 , 'superadmin' ); SET IDENTITY_INSERT "md_user" OFF; commit; ``` 注意使用php pdo 時,可不需要 追加commit ## 表操作 ### 創建表 ``` CREATE TABLE "user" ( "user_id" int IDENTITY NOT NULL, "login_name" VARCHAR NOT NULL DEFAULT '', "user_name" VARCHAR NOT NULL DEFAULT '', "password" VARCHAR NOT NULL DEFAULT '', "status" INT NOT NULL DEFAULT 1, "update_time" DATETIME NOT NULL DEFAULT 0, PRIMARY KEY("user_id") ) ``` 1. 字段和表使用雙引號,默認值使用單引號 2. 如果表名或字段名與系統不沖突,可不需要用雙引號 3. `IDENTITY` 表示對改字段進行自增 ### 查看表結構 ``` select a.column_name,data_type,decode(nullable,'Y',0,1) notnull,data_default,decode(a.column_name,b.column_name,1,0) pk from user_tab_columns a,(select column_name from user_constraints c,user_cons_columns col where c.constraint_name=col.constraint_name and c.constraint_type='P'and c.table_name='sys_app') b where table_name='sys_app' and a.column_name=b.column_name(+) ```  ### 檢測表是否存在 ``` SELECT COUNT(*) FROM all_tables WHERE OWNER='antdbms' AND TABLE_NAME = 'hs_user' ``` ### 給表添加注釋 ``` COMMENT ON TABLE table_name IS 'your_comment'; ``` 注意 comment 是使用單引號 ## 字段 ### 檢測字段是否存在 ``` SELECT column_name,data_type,nullable,data_default FROM USER_TAB_COLUMNS WHERE TABLE_NAME='hs_user' ``` ### 重命名字段名 ``` ALTER TABLE student RENAME COLUMN ADDIN_CODE1TO ADDIN_CODE_NEW; ``` ### 修改表字段類型 ``` ALTER TABLE student MODIFY age varchar(10); ``` ## 索引 ### 查看表索引 ``` SELECT TABLE_OWNER,TABLE_NAME ,COLUMN_NAME,INDEX_NAME from DBA_IND_COLUMNS WHERE TABLE_NAME='ext_report_recv'; ``` ### 添加索引 ``` CREATE INDEX REPORT_ID_RECV ON ext_report_recv(REPORT_ID) ; ``` ### 刪除索引 ``` DROP INDEX IF EXISTS REPORT_ID_RECV ``` ## 外鍵 ### 查詢外鍵 ``` SELECT a.constraint_name, a.table_name as table_name, b.table_name as referenced_table_name, a.column_name as column_name, b.column_name as referenced_column_name FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_cons_columns b ON c.r_owner = b.owner AND c.r_constraint_name = b.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = 'student' ```  ## 其他 字符串大小寫檢測 ``` SELECT SF_GET_CASE_SENSITIVE_FLAG(); ``` 0 =不敏感,1=銘感