java的mysql基础知识_MySQL基础知识（一）

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