Mysql常用sql語句

╰半夏微凉° 2022-05-21 07:23 106阅读 0赞

--創建表
    
    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';