Mysql常用sql語句

 --創建表

CREATE TABLE lrwf31h (

       wf_sale VARCHAR (8) NOT NULL DEFAULT '',
       wf_type VARCHAR (1) NOT NULL DEFAULT '',
       ie_ymd VARCHAR (10) NOT NULL DEFAULT '',
       ie_time VARCHAR (11) NOT NULL DEFAULT '',
       ie_user VARCHAR (20) NOT NULL DEFAULT '',
       ie_lymd VARCHAR (10) NOT NULL DEFAULT '',
       ie_ltime VARCHAR (11) NOT NULL DEFAULT '',
       ie_luser VARCHAR (20) NOT NULL DEFAULT '',
       uid BIGINT(28) NOT NULL AUTO_INCREMENT,
       UNIQUE KEY lrwf31h_2(uid),
       UNIQUE KEY lrwf31h_1(wf_sale,wf_type)
)

--清空記錄,包含uid自增長的緩存
TRUNCATE lremailstate1
--創建索引
CREATE UNIQUE INDEX lremailstate1_1 ON lremailstate1 (mail_no,mail_to,mail_state)
--刪除索引lremailstate1_1
ALTER TABLE lremailstate1 DROP INDEX lremailstate1_1;
--將字段設置關鍵字
 ALTER TABLE lremailstate1  ADD PRIMARY KEY (uid)
---修改成自增長性
ALTER TABLE lremailstate1 CHANGE uid uid BIGINT(28) NOT NULL AUTO_INCREMENT;
--自增長性關鍵字
  ALTER TABLE lremailstate1 CHANGE uid uid BIGINT(28) PRIMARY KEY  NOT NULL AUTO_INCREMENT;
--刪除自增長性
ALTER TABLE lremailstate1 CHANGE uid uid BIGINT(10);
--刪除關鍵字
ALTER TABLE lremailstate1 DROP PRIMARY KEY;
-- 增加字段
ALTER TABLE lrmsgtag ADD mail_no DECIMAL(28,0) NOT NULL DEFAULT 0;
ALTER TABLE lrmsgtag ADD mail_type INT NOT NULL DEFAULT 0;
清空查詢緩存
FLUSH QUERY CACHE
RESET QUERY CACHE

--查詢表的欄位
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.COLUMNS WHERE table_name = 'lrtd00d1' AND table_schema='maildb';