MySQL常用操作（table相关操作）---DAY_3

删除数据表

DROP TABLE user1;
DROP TABLE IF EXISTS user2;
DROP TABLE IF EXISTS test1,test2,test3;

更改表名字

ALTER TABLE user6 RENAME TO one;
ALTER TABLE user5 RENAME TO ones;
rename TABLE user4 TO two;

增加与删除字段

CREATE TABLE IF NOT EXISTS list1(
id SMALLINT UNSIGNED key auto_increment,
username VARCHAR(20) UNIQUE  NOT NULL,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT'123@qq.com',
sex enum('man','woman','unknown')  DEFAULT'unknown',
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT'徐州',
salary FLOAT(6,2),
regtime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
);

-- ADD CHAR 18
ALTER TABLE list1 ADD card CHAR(18);
ALTER TABLE list1 ADD test1 VARCHAR(20) NOT NULL UNIQUE;
ALTER TABLE list1 ADD test2 VARCHAR(20) NOT NULL FIRST;
ALTER TABLE list1 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;

-- add together
ALTER TABLE list1
add test4 int not null DEFAULT 123 AFTER password,
add test5 FLOAT(6,2) FIRST,
add test6 SET('a','b','c');

-- DELETE
ALTER TABLE list1 drop test1;

-- delete together
ALTER TABLE list1
DROP test2,
DROP test3;

-- add and delete 
alter table list1
add test int UNSIGNED UNIQUE AFTER username,
drop test4;

修改字段

-- modify the index of list
ALTER TABLE list1 MODIFY email VARCHAR(200);
ALTER TABLE list1 MODIFY email VARCHAR(200) NOT NULL DEFAULT '1234@qq.com';
ALTER TABLE list1 MODIFY email VARCHAR(200) AFTER test;
ALTER TABLE list1 MODIFY email VARCHAR(200) FIRST;

改变字段名字

-- change the name of INDEX
ALTER TABLE list1 CHANGE email mail VARCHAR(200) NOT NULL DEFAULT '1234@qq';
ALTER TABLE list1 CHANGE  mail email VARCHAR(200) NOT NULL DEFAULT '1234@qq' AFTER username;
ALTER TABLE list1 CHANGE  test test INT;

删除增加默认值

-- add or delete default values

ALTER TABLE list1 ALTER age set DEFAULT 20;
ALTER TABLE list1 ALTER username set DEFAULT 'wxj';
ALTER TABLE list1 ALTER username DROP DEFAULT ;

增加和删除主键

-- add or delete key 

CREATE TABLE IF NOT EXISTS list2(
id INT
);

ALTER TABLE list2 ADD PRIMARY key(id);

CREATE TABLE IF NOT EXISTS list3(
id INT,
username CHAR(50)
);


ALTER TABLE list3 add PRIMARY key(id,username);
ALTER TABLE list3 DROP PRIMARY KEY;

-- use modify to change index type and then drop key to cancel auto_increment

删除和增加唯一

CREATE TABLE IF NOT EXISTS list4(
id TINYINT key auto_increment ,
username VARCHAR(20)  NOT NULL,
card char(18) UNIQUE DEFAULT '123456789',
test VARCHAR(20) NOT NULL,
test1 VARCHAR(50) NOT NULL
);
ALTER  TABLE list4 ADD UNIQUE(username);
-- RENAME the UNIQUE INDEX as uni_card
ALTER  TABLE list4 ADD CONSTRAINT symbol  UNIQUE key uni_card(test);
-- mul_unique
ALTER  TABLE list4 ADD CONSTRAINT symbol  UNIQUE index uni_test1(test1,test);


ALTER  TABLE list4 DROP INDEX username;
ALTER  TABLE list4 DROP key uni_card;
ALTER  TABLE list4 DROP key uni_test;

修改存储引擎

-- change shtorage engine
ALTER TABLE list4 ENGINE=myisam;

修改自增长的起始值

-- change aoto_increment
ALTER TABLE list4 auto_increment=100;