1 命令行命令
SHOW databases;
CREATE DATABASE books;
USE books;
CREATE TABLE book (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)
) ENGINE MyISAM;
DESCRIBE book;
2 写入数据
INSERT INTO book(author, title, type, year) VALUES('Mark Twain', 'The Adventures of Tom Sawyer', 'Fiction', '1876');
3 读取数据
SELECT * FROM book;
4 修改表名
ALTER TABLE book RENAME bookTable;
5 修改列的数据类型
ALERT TABLE book MODIFY year SMALLINT;
6 增加列
ALERT TABLE book ADD pages SMALLINT UNSIGNED;
7 修改列名
ALERT TABLE book CHANGE type category VARCHAR(16);
注意:后面的数据类型即使没改也需要附上。
8 删除列
ALTER TABLE book DROP pages;
9 删除表
DROP TABLE book;
10 添加索引
ALTER TABLE book ADD INDEX(author(20));
CREATE INDEX author ON book (author(20));
11 SELECT COUNT
SELECT COUNT(*) FROM book;
12 SELECT DISTINCT
SELECT DISTINCT author FROM book;
13 DELETE
DELETE FROM book WHERE title='abc';
14 LIKE
SELECT author FROM book WHERE author LIKE 'win%';
15 LIMIT
SELECT author FROM book LIMIT 3;
SELECT author FROM book LIMIT 3, 1;
16 UPDATE ... SET
UPDATE book SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain';
17 ORDER BY
SELECT author FROM book ORDER BY author;
SELECT author FROM book ORDER BY author DESC;
18 GROUP BY
SELECT category, COUNT(author) FROM book GROUP BY category;
19 联表查询
SELECT name, author, title from customers, book WHERE customers.isbn=classics.isbn;
20 NATURAL JOIN
SELECT name, author, title FROM customers NATURAL JOIN book;
21 JOIN...ON
SELECT name, author, title FROM customers JOIN classics ON customers.isbn=classics.isbn;
22 AS
SELECT name, author, title from customers AS cust, book AS bk WHERE cust.isbn=bk.isbn;
23 逻辑运算符AND,OR,NOT