SQL学习笔记（一）
来源【SQL教程｜菜鸟教程】
1. 创建数据库、显示数据库、使用数据库
mysql> create database test;
mysql> show databases;
mysql> use test;
2. 创建数据表、插入数据
+------+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+------+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+------+--------------+---------------------------+-------+---------+
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');
+------+---------+-------+------------+
| aid | site_id | count | date |
+------+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+------+---------+-------+------------+
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');
3. SELECT、SELECT LIMIT、SELECT DISTINCT 、WHERE、AND/OR
# 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多列
4. INSERT INTO、UPDATE、DELETE
# 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;
5. LIKE、NOT LIKE、IN、NOT IN、BETWEEN AND
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');
6. 别名AS
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="菜鸟教程";
