这是MySQL的速查表。
希望您会发现它有趣且有用。

MySQL创建表

表名是customer。
主键是ID。

CREATE TABLE `customer` (
    `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    `age` INT UNSIGNED DEFAULT NULL,
    `accountstatus` ENUM('ACTIVE', 'INACTIVE', 'DELETED') DEFAULT 'ACTIVE',
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id_customer_UNIQUE` (`id`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

创建另一个表customerorder。
主键是订单号，外键是客户号。

CREATE TABLE `customerorder` (
    `orderid` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    `customerid` INT UNSIGNED NOT NULL,
    `amount` FLOAT(10 , 2 ) DEFAULT 0,
    `orderstatus` ENUM('PAID', 'DUE') DEFAULT 'DUE',
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`orderid`),
    UNIQUE KEY `orderid_customerorder_UNIQUE` (`orderid`),
    KEY `fk_customerid_customerorder` (`customerid`),
    CONSTRAINT `fk_customerid_customerorder` FOREIGN KEY (`customerid`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

MySQLRENAME表

假设我们有一个表c1，我们想将其重命名为c2。

RENAME TABLE c1 TO c2;

MySQLDROP表

删除表c2。

DROP TABLE IF EXISTS c2;

MySQL插入数据

将数据插入客户表。

INSERT INTO `customer` (`name`, `age`, `lastmodified` , `created`)
VALUES
('theitroad', 25, '2015-01-01 10:20:30', '2015-01-01 10:20:30');

多次插入

INSERT INTO `customer` (`name`, `age`, `lastmodified` , `created`)
VALUES
('theitroad', 25, '2015-01-01 10:20:30', '2015-01-01 10:20:30'),
('Dawood', 20, '2015-01-01 10:20:30', '2015-01-01 10:20:30');

INSERT INTO `customerorder` (`customerid`, `amount`, `orderstatus`, `lastmodified` , `created`)
VALUES
('1', 250, 'PAID', '2015-01-01 10:20:30', '2015-01-01 10:20:30'),
('2', 100, 'PAID', '2015-01-01 10:20:30', '2015-01-01 10:20:30'),
('1', 500, 'PAID', '2015-01-01 10:20:30', '2015-01-01 10:20:30'),
('1', 700, 'PAID', '2015-01-01 10:20:30', '2015-01-01 10:20:30'),
('2', 800, 'PAID', '2015-01-01 10:20:30', '2015-01-01 10:20:30');

MySQL SELECT

通过ID选择客户。

SELECT 
    *
FROM
    customer
WHERE
    id = 1;

Select only required columns by id.

SELECT 
    id AS customerid, name, age, accountstatus, lastmodified, created
FROM
    customer
WHERE
    id = 1;

SELECT-按日期过滤

按日期选择客户过滤器。

SELECT 
    *
FROM
    customer
WHERE
    created >= '2015-01-01 00:00:00'
    AND created <= '2015-01-01 23:59:59';

SELECT-按精确文本过滤

选择客户过滤器的确切名称。

SELECT 
    *
FROM
    customer
WHERE
    name = 'theitroad';

SELECT-通过开始文本过滤

选择名称以给定单词开头的客户。

SELECT 
    *
FROM
    customer
WHERE
    name LIKE 'Yu%';

SELECT-通过结束文本进行过滤

选择名称以给定单词结尾的客户。

SELECT 
    *
FROM
    customer
WHERE
    name LIKE '%od';

SELECT-按值中的文本过滤

选择名称包含给定单词的客户。

SELECT 
    *
FROM
    customer
WHERE
    name LIKE '%su%';

MySQL更新

更新客户表。

UPDATE customer 
SET 
    name = 'theitroad',
    age = 25
WHERE
    id = 1;

MySQL删除

从客户表中删除。

DELETE FROM customer 
WHERE
    id = 1;

MySQL添加列

将新列dateofbirth添加到客户表。

ALTER TABLE customer
ADD COLUMN dateofbirth DATETIME DEFAULT NULL
AFTER age;

MySQL修改列

修改客户表的列dateofbirth。

ALTER TABLE customer
MODIFY COLUMN dateofbirth DATE DEFAULT NULL
AFTER age;

MySQL重命名列

将列dateofbirth重命名为customer表的dob。

ALTER TABLE customer
CHANGE COLUMN dateofbirth dob DATE DEFAULT NULL
AFTER age;

MySQL重命名和修改列

将列dob重命名为dateofbirth，并将客户表的DATE类型修改为DATETIME。

ALTER TABLE customer
CHANGE COLUMN dob dateofbirth DATETIME DEFAULT NULL
AFTER age;

MySQL删除列

删除客户表的列dateofbirth。

ALTER TABLE customer
DROP COLUMN dateofbirth;

MySQL添加唯一键

将唯一键orderid_customerorder_UNIQUE添加到customerorder表。

ALTER TABLE customerorder
ADD UNIQUE KEY `orderid_customerorder_UNIQUE` (`orderid`);

MySQL删除唯一键

删除客户订单表的唯一键orderid_customerorder_UNIQUE。

ALTER TABLE customerorder
DROP INDEX orderid_customerorder_UNIQUE;

MySQL添加索引

将索引fk_customerid_customerorder添加到customerorder表中。

ALTER TABLE customerorder
ADD INDEX `fk_customerid_customerorder` (`customerid`);

MySQL删除索引

删除customerorder表的索引fk_customerid_customerorder。

ALTER TABLE customerorder
DROP INDEX fk_customerid_customerorder;

MySQL添加约束-外键

将外键customerid添加到customerorder表。

ALTER TABLE customerorder
ADD CONSTRAINT `fk_customerid_customerorder` FOREIGN KEY (`customerid`)
    REFERENCES `customer` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

MySQL删除外键约束

删除customerorder表的约束fk_customerid_customerorder。

ALTER TABLE customerorder
DROP FOREIGN KEY fk_customerid_customerorder;