- 创建数据库，指定字符集为utf-8
CREATE DATABASE IF NOT EXISTS isp_datasync_scheduler DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
- 创建数据库，指定字符集为gbk
CREATE DATABASE yourdb DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
- 添加表字段
ALTER TABLE `ua_coupon_member` ADD coupon_card_no VARCHAR(100) DEFAULT NULL COMMENT '第三方卡券卡号' after amount;
- 修改字段为not null，还要把原来的类型也写出来
ALTER TABLE jw_user_role MODIFY zk_env VARCHAR(16) NOT NULL;
- 修改字段类型
ALTER TABLE tb_article MODIFY COLUMN NAME CHAR(50);
- 更改列名
ALTER TABLE student change physics physisc char(10) not null;
- 删除列名
alter table lc_tuiguang.ua_tuiguang_channel_config drop column activity_page;
- 建立普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;
删除索引
drop index index_name on table_name ;
- 建立组合索引
ALTER TABLE `ua_app_request_limit` ADD INDEX IDX_index_name (request_id,request_method);
- 建立唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`);
- 修改表字段长度
ALTER TABLE ua_app_member_contacts MODIFY COLUMN contact_emails VARCHAR(2000);
- 查询当前库所有表
select table_name from information_schema.tables where table_schema='当前数据库';
- 获取表主键
SELECT
k.column_name
FROM
information_schema.table_constraints t
JOIN
information_schema.key_column_usage k
USING
(constraint_name,table_schema,table_name)
WHERE
t.constraint_type='PRIMARY KEY'
AND t.table_schema='wlctt'
AND t.table_name='dict'
