• 最近在自学后台，写到MySQL感觉初学容易记混，在这里列出来方便日后查看。
• 转发时请注明出处，尊重他人也是尊重自己。
• 有错误随时指正，我会及时修正方便后人查阅。

显示所有数据库

SHOW databases；

显示数据库中所有表

SHOW tables;

显示表结构

DESCRIBE classics;

登录

mysql -u root(name) -p

使用数据库

USE publications;

创建数据库

CREATE DATABASE publications;

创建用户

GRANT ALL ON publications.* TO 'jim'@'localhost' IDENTIFIED BY 'password';

增

创建表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4)) ENGINE MyISAM;

USE publications;
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE MyISAM;

添加自增数据，添加某一列

ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4),
 id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;

向表中添加数据

INSERT INTO classics(author, title, type, year) VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
INSERT INTO classics(author, title, type, year) VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');
INSERT INTO classics(author, title, type, year) VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856');
INSERT INTO classics(author, title, type, year)  VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');
INSERT INTO classics(author, title, type, year) VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

添加新列

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

删

删除某一列

ALTER TABLE classics DROP id;

删除列

ALTER TABLE classics DROP pages;

删除行

DELETE FROM classics WHERE title='Little Dorrit';

删除表

CREATE TABLE disposable(trash INT);
DESCRIBE disposable;
DROP TABLE disposable;
SHOW tables;

改

重命名表

ALTER TABLE classics RENAME pre1900;
ALTER TABLE pre1900 RENAME classics;

重命名列(type -> category)

ALTER TABLE classics CHANGE type category VARCHAR(16);

改变列中的数据类型

ALTER TABLE classics MODIFY year SMALLINT;

UPDATE...SET 更新字段

UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain';
UPDATE classics SET category='Classic Fiction' WHERE category='Fiction';

查

SELECT数据库查询

SELECT something FROM tablename;

SELECT * FROM classics;
SELECT author,title FROM classics;
SELECT title,isbn FROM classics;

SELECT COUNT 计数

SELECT COUNT(*) FROM classics;

SELECT DISTINCT 去重

SELECT author FROM classics;
SELECT DISTINCT author FROM classics;

WHERE SQL的核心与灵魂

• 限定搜索

SELECT author,title FROM classics WHERE author="Mark Twain";
SELECT author,title FROM classics WHERE isbn="9781598184891 ";

• 模糊搜索

SELECT author,title FROM classics WHERE author LIKE "Charles%";
SELECT author,title FROM classics WHERE title LIKE "%Species";
SELECT author,title FROM classics WHERE title LIKE "%and%";

• LIMIT 限制返回数量搜索

SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1;

• MATCH...AGAINST

SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('and');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('curiosity shop');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('tom sawyer');

• MATCH...ATAINST...布尔模式

SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('+charles -species' IN BOOLEAN MODE);
SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('"origin of"' IN BOOLEAN MODE);

索引

创建索引

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;

两种创建方式
ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

创建表时添加索引

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year)) ENGINE MyISAM;

主键

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

用数据填充列，并设置主键

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856';
UPDATE classics SET isbn='9780099533474' WHERE year='1841';
UPDATE classics SET isbn='9780192814968' WHERE year='1594';
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

创建带索引和主键的表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 isbn CHAR(13),
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year),
 PRIMARY KEY (isbn)) ENGINE MyISAM;

创建FULLTEXT索引

ALTER TABLE classics ADD FULLTEXT(author,title);

ORDER BY 通过一个或多个列按升序或降序排序返回结果

升序
SELECT author,title FROM classics ORDER BY author;
降序
SELECT author,title FROM classics ORDER BY title DESC;

• 按author排序后，再按year降序排列

SELECT author,title,year FROM classics ORDER BY author,year DESC;
等价于
SELECT author,title,year FROM classics ORDER BY author ASC, year DESC;

GROUP BY 分组检索信息

SELECT category,COUNT(author) FROM classics GROUP BY category;

连接表

CREATE TABLE customers (
 name VARCHAR(128),
 isbn VARCHAR(13),
 PRIMARY KEY (isbn)
 ) ENGINE MyISAM;
INSERT INTO customers(name,isbn) VALUES('Joe Bloggs','9780099533474');
INSERT INTO customers(name,isbn) VALUES('Mary Smith','9780582506206');
INSERT INTO customers(name,isbn) VALUES('Jack Wilson','9780517123201');
SELECT * FROM customers;

• 两个表接成一个单独的SELECT

SELECT name,author,title from customers,classics WHERE customers.isbn=classics.isbn;

NATURAL JOIN 使用NATURAL JOIN自动合并

SELECT name,author,title FROM customers NATURAL JOIN classics;

JOIN...ON 指定一列将两表连在一起

SELECT name,author,title FROM customers JOIN classics ON customers.isbn=classics.isbn;

使用AS 通过AS创建别名，节省输入和提高查询可读性

SELECT name,author,title FROM customers AS cust, classsics AS class WHERE cust.isbn=class.isbn;

使用基本运算符

SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author LIKE "%Darwin";
SELECT author,title FROM classics WHERE author LIKE "%Mark Twain%" OR author LIKE "%Samuel Langhorne Clemens%";
SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author NOT LIKE "%Darwin";