mysql笔记

建表语句：
DROP TABLE IF EXISTS `paper`;
CREATE TABLE `paper` (
`paper_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'paperID',
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'paper名称',
`number` int(11) NOT NULL COMMENT 'paper数量',
`detail` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'paper描述',
`test1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`paper_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'paper表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

（ALTER使用）改变表结构：
ALTER table paper add test VARCHAR(50);

alter TABLE paper drop test;

alter TABLE paper modify test int；

alter TABLE paper CHANGE test test1 VARCHAR(50)；

索引使用：
test为表字段
alter TABLE paper add index index_test(test);

alter TABLE paper drop index index_test
查看所有索引 paper为表名
show index from paper;

复制表（包括结构，索引等等）：第一步：SHOW CREATE TABLE paper 得到建表语句
第二步：修改表名，新建表
第三步：使用 INSERT INTO... SELECT 语句 填充数据
例子：INSERT INTO paper_xx (paper_id,name,number,detail,test) select paper_id,name,number,detail,test from paper;

复制表（仅数据）：create table xx_日期 as select * from tablename;