MySQL-表结构

• 创建新闻表

编号、标题、内容、发布时间、点击量、是否置顶、分类、发布人

CREATE TABLE IF NOT EXISTS cms_news(
id INT,
title VARCHAR(50),
content TEXT,
pubTime INT,
clickNum INT,
isTop TINYINT(1) COMMENT '0代表不置顶，1代表置顶'
);ENGINE=Innodb DEFAULT CHARSET=UTF-8;

• 列出所有表

SHOW TABLES;

• 查看表结构

DESC cms_news;
DESCRIBE cms_news;
SHOW COLUMNS FROM cms_news;

• 向表中插入记录 INSERT tbl_name VALUE|VALUES(值,·,·,)

INSERT cms_news VALUES(1,"This is Title","内容",'time'···);

• 主键

PRIMARY KEY

• 无符号的

UNSIGNED

• 自增长 AUTO_INCREMENT

CREATE TABLE IF NOT EXISTS user(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)ENGINE=Innodb AUTO_INCREMENT=100 DEFAULT=utf-8;

INSERT user VALUES(1,'king');
INSERT user(usename) VALUES('queen');

• 修改自增长的值

ALTER TABLE user AUTO_INCREMENT = 500;

• 非空 NOT NULL

CREATE TABLE IF NOT EXISTS user(
in INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);

• 默认值 DEFAULT

CREATE TABLE IF NOT EXISTS user(
in INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京'，
sex ENUM('男','女','保密')
);

• 唯一 UNIQUE KEY

CREATE TABLE IF NOT EXISTS user(
in INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
card CHAR(18) UNIQUE
);

• 创建表

CREATE TABLE [IF NOT EXISTS] tal_name(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]
);EGNINE=Innodb CHARSET=UTF-8 AUTO_INCREMENT=100;

• 创建用户表user

CREATE TABLE IF NOT EXISTS user(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL DEFAULT '1Q2W3E4R',
email VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密',
addr VARCHAR(200) NOT NULL,
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DAFAULT 'default.jpg'
);

• 修改表名 user–>user11

ALTER TABLE user RENAME TO user11;
ALTER TABLE user RENAME AS user11;
ALTER TABLE user RENAME user11;

• 添加字段 ADD

ALTER TABLE user ADD card VARCHAR(18);
ALTER TABLE user ADD test1 VARCHAR(100) NOT NULL UNIQUE;

• 指定字段位置:

ALTER TABLE user ADD test2 VARCHAR(100) NOT NULL FIRST;
ALTER TABLE user ADD test2 VARCHAR(100) NOT NULL AFTER username;

• 删除字段

ALTER TABLE user DROP test1;

• 添加字段同时删除另一个字段

ALTER TABLE user
ADD test1 VARCHAR(100) NOT NULL UNIQUE AFTER sex,
DROP addr;

• 修改字段类型 MODIFY

ALTER TABLE user MODIFY email VARCHAR(200);
ALTER TABLE user MODIFY email VARCHAR(100) NOT NULL DEFAULT '123456@163.com';

• 修改字段位置 MODIFY

ALTER TABLE user MODIFY email VARCHAR(50) AFTER username;

• 修改字段类型并且移动位置 MODIFY

ALTER TABLE user MODIFY addr VARCHAR(150) NOT NULL DEFAULT '北京' FIRST;

• 修改字段名称 CHANGE

ALTER TABLE user MODIFY addr address VARCHAR(150) NOT NULL DEFAULT '北京' FIRST;

• 给字段添加默认值

ALTER TABLE user ALTER email SET DEFAULT '1223456@163.com';

• 给字段删除默认值

ALTER TABLE user ALTER age DROP DEFAULT;

• 添加主键

CREATE TABLE IF NOT EXISTS user1(
id INT;
card VARCHAR(18);
username VARCHAR(20) NOT NULL
);
ALTER TABLE user1 ADD PRIMARY KEY(id);
ALTER TABLE user1 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

• 复合主键

ALTER TABLE user1 ADD PRIMARY KEY(id,card);

• 删除主键

ALTER TABLE user1 DROP PRIMARY KEY;

• 添加唯一索引

CREATE TABLE IF NOT EXISTS user2(
id TINYINT UNSIGNED KEY NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18) NOT NULL,
test VARCHAR(20) NOT NULL,
test1 CHAR(32) NOT NULL
);

ALTER TABLE user2 ADD NUIQUE(username);
ALTER TABLE user2 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);

• 给test和test1字段添加联合索引：

ALTER TABLE user2 ADD CONSTRAINT symbol UNIQUE INDEX muluni_test_test1(test,test1);

• 删除唯一索引

ALTER TABLE user2 DROP INDEX username;
ALTER TABLE user2 DROP KEY uni_card;
ALTER TABLE user2 DROP KEY muluni_test_test1;

• 修改表的存储引擎

ALTER TABLE user2 ENGINE=myisam;

• 删除表

DROP TABLE user1;
DROP TABLE IF EXISTS user1;