[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=铭感