mysql>    create database test;

mysql>    show databases;

mysql>    use test;

mysql>  CREATE TABLE Websites ( id int, name varchar(255), url varchar(255), alexa int, country varchar(255) );

  mysql> INSERT INTO Websites VALUES (1, 'Google', 'https://www.google.cm/', 1, 'USA');

  mysql> INSERT INTO Websites VALUES (2, '淘寶', 'https://www.taobao.com/', 13, 'CN');

  mysql> INSERT INTO Websites VALUES (3, '菜鳥教程', 'http://www.runoob.com/', 4689, 'CN');

  mysql> INSERT INTO Websites VALUES (4, '微博', 'http://weibo.com/', 20, 'CN');

  mysql> INSERT INTO Websites VALUES (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA');

mysql> create table access_log (aid int, site_id int, count int,date varchar(255));
mysql> insert into access_log values(1,1,45,'2016-05-10');
mysql> insert into access_log values(2,3,100,'2016-05-13');
mysql> insert into access_log values(3,1,230,'2016-05-14');
mysql> insert into access_log values(4,2,10,'2016-05-14');
mysql> insert into access_log values(5,5,205,'2016-05-14');
mysql> insert into access_log values(6,4,13,'2016-05-15');
mysql> insert into access_log values(7,3,220,'2016-05-15');
mysql> insert into access_log values(8,5,545,'2016-05-16');
mysql> insert into access_log values(9,3,201,'2016-05-17');

# SELECT、SELECT LIMIT

mysql> SELECT name,country FROM Websites;
mysql> SELECT * FROM Websites;
mysql> SELECT * FROM Websites LIMIT 2; #SELECT LIMIT

# SELECT DISTINCT
mysql> SELECT DISTINCT country FROM Websites;
mysql> SELECT DISTINCT country,url FROM Websites; # country,url的組合是唯一的

# WHERE
mysql> SELECT * FROM Websites WHERE id=1;

#AND/OR
mysql-> SELECT * FROM Websites
     -> WHERE alexa > 15
     -> AND (country='CN' OR country='USA');

#ORDER BY
mysql> SELECT * FROM Websites ORDER BY alexa DESC; #DESC降序
mysql> SELECT * FROM Websites ORDER BY country,alexa DESC; #ORDER BY多列

# INSERT INTO # 插入新記錄
mysql> INSERT INTO Websites (name, url, alexa, country) 
       -> VALUES ('百度','https://www.baidu.com/','4','CN');

#UPDATE # 更新某記錄
mysql> UPDATE Websites 
    -> SET alexa='5000', country='USA' 
    -> WHERE name='菜鳥教程';

#DELETE # 刪除某記錄
mysql> DELETE FROM Websites
    -> WHERE name='Facebook' AND country='USA';

#DELETE # 刪除表中所有記錄
mysql> DELETE FROM WebSites;

mysql> SELECT * FROM Websites WHERE name NOT LIKE '%oo%'; # % 替代 0 個或多個字符
mysql> SELECT * FROM Websites WHERE name LIKE '%oo%';
mysql> SELECT * FROM Websites WHERE name LIKE 'g_ogle'; # _ 替代一個字符
mysql> SELECT * FROM Websites WHERE name LIKE 'G_o_le';

  mysql> SELECT * FROM Websites WHERE name REGEXP '^[GFs]'; # 選取 name 以 "G"、"F" 或 "s" 開始的所有網站

  mysql> SELECT * FROM Websites WHERE name REGEXP '^[A-H]'; # 選取 name 以 A 到 H 字母開頭的網站

# [^charlist] 或 [!charlist]不在字符列中的任何單一字符

  mysql> SELECT * FROM Websites WHERE name REGEXP '^[^A-H]'; # 選取 name 不以 A 到 H 字母開頭的網站

  # IN

  mysql> SELECT * FROM Websites WHERE name IN ('Google','菜鳥教程');

  # BETWEEN AND

  mysql> SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;     # 選取 alexa 介于 1 和 20 之間的所有網站

  mysql> SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20; # 選取 alexa 不介于 1 和 20 之間的所有網站

  # BETWEEN AND、NOT IN 

  mysql> SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');

mysql> SELECT name AS n, country AS c FROM Websites;#列名

mysql> SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites; # 列的值、列名

mysql> SELECT w.name, w.url, a.count, a.date
    ->  FROM Websites AS w, access_log AS a
    -> WHERE a.site_id=w.id and w.name="菜鳥教程"; #表名

mysql> SELECT Websites.name, Websites.url, access_log.count, access_log.date
    -> FROM Websites, access_log
    -> WHERE Websites.id=access_log.site_id and Websites.name="菜鳥教程";