MySQL基础知识（1）_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