1 复制表结构和数据
create table job_code_back select * from job_code
2 复制表结构
create table job_code_back select * from job_code where 1=0
3 将查询出的数据存入另一张表
INSERT INTO job_code_back (job_name, job_code)SELECT job_name, job_codeFROM job_code
4 mysql查询数据库中有每张表有多少字段
SELECT TABLE_NAME,COUNT(TABLE_NAME) FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='open_app' //open_app为数据库名 group by TABLE_NAME
5 mysql查询一张表有哪些字段和字段注释
SELECT column_name,COLUMN_comment FROM information_schema. COLUMNS WHERE TABLE_NAME='haier_subjoin_down_customer_list'-- 表名
6 oracle查询表的名称,字段信息以及字段注释
select ut.COLUMN_NAME,--字段名称 uc.comments,--字段注释 ut.DATA_TYPE,--字典类型 ut.DATA_LENGTH,--字典长度 ut.NULLABLE--是否为空from user_tab_columns utinner JOIN user_col_comments ucon ut.TABLE_NAME = uc.table_name and ut.COLUMN_NAME = uc.column_namewhere ut.Table_Name='RC_METADATA' --表名order by ut.column_name
7 oracle为id设置自增长
CREATE TABLE departments (--创建表 ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL);ALTER TABLE departments ADD (--加主键 CONSTRAINT dept_pk PRIMARY KEY (ID));CREATE SEQUENCE dept_seq START WITH 1; --创建序列CREATE OR REPLACE TRIGGER dept_bir --创建触发器BEFORE INSERT ON departments FOR EACH ROWBEGIN SELECT dept_seq.NEXTVAL INTO :new.id FROM dual;END;
8 mysql设置creatime默认当前时间 CURRENT_TIMESTAMP ,update实时更新时间,勾选下方的"根据当前时间戳更新"
9 mysql根据数据库名查询所有表的结构
SELECT
TABLE_NAME AS 表名, COLUMN_NAME AS 字段名, DATA_TYPE AS 数据类型, CHARACTER_MAXIMUM_LENGTH AS 长度, COLUMN_COMMENT as 注释FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'open_bank'10 mysql添加字段 alter table loan_notic_record add 字段名 VARCHAR(长度) COMMENT '注释';
alter table loan_notic_record add bus_type VARCHAR(10) COMMENT '业务类型';