零基礎學SQL、MySQL以及Workbench筆記

上班之餘，花了兩周時間學了下SQL、MySQL以及Workbench，算是初步入門。周末邊刷著烈火如歌，邊寫了篇整理筆記，方便日後查看學習~

——SQL學習記錄——

SQL: Structured Query Language。結構化查詢語言。

相關學習借鑒資料：《SQL必知必會》，知乎，w3cschool，我要自學網

MySQL安裝：MySQL資料庫5.7.20的下載與安裝

Workbench安裝：MySQL Workbench使用方法總結

語法規則：

SQL不區分大小寫，通常做法是用大寫編寫所有SQL命令。

在SQL中，會忽略空格和換行。

一個SQL語句可以放在一個或多個文本行上。另外，多個SQL語句可以組合在一個文本行上。

SQL常用語句：Select，insert，update，delete，show等。

• SHOW語句

SHOW DATABASES（列出伺服器管理的資料庫）SHOW TABLES （顯示當前選定的資料庫中的所有表）SHOW columns FROM customers （顯示「customers」表中的列）

• SELECT語句（劃重點劃重點）

SELECT column_1, column_2, column_3 FROM table1;（從table1表中，選取3列數據）SELECT * FROM table1; （*通配符，表示所有。選取table1表中的所有列）SELECT DISTINCT column_1, column_2 FROM table1;（distinct用於返回唯一值，去掉重複記錄）SELECT * FROM table1 LIMIT 6; （limit限定子集範圍。選取table1表中前6條記錄）SELECT * FROM table1 LIMIT 2, 10; （選取table1表中從第3條開始的前10個結果）SELECT table1. column_1 FROM table1;（列名前提供表名，用點分隔。完全限定名稱）SELECT * FROM table1 ORDER BY column_1;（order by關鍵字默認按升序ASC排序）SELECT * FROM table1 ORDER BY column_1 DESC;（按DESC降序排序）SELECT * FROM table1 ORDER BY column_1, column_2;（多列排序，先按列1，再按列2）SELECT * FROM customers WHERE ID=6；（從customers表中選擇ID為6的記錄）SELECT * FROM customers WHERE ID BETWEEN 2 AND 6；（選取ID介於2和6之間的記錄）SELECT * FROM customers WHERE ID IN(3,4,6)；（選取ID為3,4,6的記錄）SELECT * FROM customers WHERE City=』London』;（單引號環繞文本值，選取London相關的記錄）SELECT * FROM customers WHERE City=』London』AND state=』CA』;（AND語句）SELECT * FROM customers WHERE City=』London』AND (Age=25 OR Age=28);（結合AND和OR）SELECT ID, Salary+500 AS Salary FROM employees; （算術運算符+，為員工工資加500）SELECT CONCAT (FirstName,』,』,City）FROM customers;（concat()函數用於連接兩個或多個文本值，並返回連接字元串。把FirstName與City連接起來，用逗號分隔） 執行輸出結果： CONCAT (FirstName,』,』,City） John, New YorkSELECT CONCAT (FirstName,』,』,City） AS new_column FROM customers; （AS關鍵字為列自定義名稱） 執行輸出結果： new_column John, New York

• LIKE操作符

LIKE操作符用於在WHERE子句中搜索列中的指定模式。SQL模式允許使用「_」匹配任何單個字元，而使用「%」匹配任意數目字元（包括零個字元）。案例：選擇FirstName以字母「D」開頭的所有員工：SELECT * FROM employees WHERE FirstName LIKE 『D%』;案例：選擇LastName以字母「s」結尾的所有員工：SELECT * FROM employees WHERE LastName LIKE 『%s』;

• 函數

UPPER()函數：將指定字元串中的所有字母轉換為大寫。（Smith>>SMITH）LOWER()函數：將指定字元串中的所有字母轉換為小寫。（Smith>>smith）案例：SELECT FirstName, UPPER(LastName) AS LastName FROM employees;SQRT()：平方根。 AVG()：平均值。 SUM():求和。 MIN():最小值。案例：SELECT Salary, SQRT(Salary) FROM employees;

子查詢：當一個查詢是另一個查詢的條件時，稱之為子查詢。

例子： SELECT * FROM items WHERE cost>500 ORDER BY cost DESC;

SELECT Salary FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees);

表連接：組合來自兩個或對個表的數據。表連接創建一個臨時表，顯示連接表中的數據。

案例：連接 customers表和orders表

SELECT customers.ID, customers.Name, orders.Name, orders.Amount FROM customers, orders WHERE customers.ID=orders.Customer_ID ORDER BY customers.ID;

自定義別名：AS

SELECT ct. ID, ct. Name, ord. Name, ord. Amount FROM customers AS ct, orders AS ord WHERE ct. ID=ord. Customer_ID ORDER BY customers.ID;

• 連接

JOIN操作符：橫向連接。又分內連接和外連接。

案例：有三個表如下：

內連接：SELECT * FROM user_1, user_2 WHERE user_1.id = user_2.id

左外連接：SELECT * FROM user_1 LEFT OUTER JOIN user_2 ON user_1.id = user_2.id

三個表左外連接：SELECT * FROM user_1 LEFT OUTER JOIN user_2 ON user_1.id = user_2.id LEFT OUTER JOIN user_3 ON user_1.id = user_3.id

右外連接：SELECT * FROM user_1 RIGHT OUTER JOIN user_2 ON user_1.id = user_2.id

全外連接：SELECT * FROM user_1 FULL OUTER JOIN user_2 ON user_1.id = user_2.id

UNION操作符：縱向連接UNION將多個數據集合併到單個數據集中，並刪除任何現有的重複項。UNION ALL將多個數據集合併到一個數據集中，但不會刪除重複的行。UNION ALL比UNION運行速度快，因為它不會對數據集執行重複刪除操作。UNION內部每個SELECT語句必須擁有相同數量的列。列也必須擁有相似的數據類型。同時，每個SELECT語句中的列的順序必須相同。案例：SELECT ID, City FROM table1 UNION SELECT ID, City FROM table2;（不包括重複值）SELECT ID, City FROM table1 UNION ALL SELECT ID, City FROM table2;（包括重複值）

CREATE TABLE語句：創建表

CREATE TABLE uesrs ( UserID int, FirstName varchar(100), LastName varchar(100), City varchar(100); PRIMARY KEY(UserID));

SQL約束：約束可在創建表時規定

NOT NULL：指示某列不能存儲NULL值。

UNIQUE：保證每列的每行必須有唯一值。

PRIMARY KEY：NOT NULL和UNIQUE的結合。主鍵。

FOREIGN KEY：保證一個表中的數據匹配另一個表中的值的參照完整性。

CHECK：保證列中的值符合指定的條件。

DEFAULT：規定沒有給列賦值時的默認值。

AUTO INCREMENT：自動遞增，插入新記錄時，自動創建主鍵欄位的值。

案例： UserID int NOT NULL AUTO_INCREMENT, PRIMARY KEY (UserID)

CREATE TABLES users (id int NOT NULL AUTO_INCREMENT, username varchar(40) NOT NULL, password varchar(10) NOT NULL, PRIMARY KEY(id) );

INSERT INTO語句：用於向資料庫中的表添加新的數據行。

案例：向Employees表中插入新的一行信息。

INSERT INTO Employees VALUES (6, 『Andrew』, 『Thomas』, 43);

INSERT INTO Employees( ID, FirstName, LastName, Age) VALUES (6, 『Andrew』, 『Thomas』, 43); （指定表列名後，再插入數據）

ALTER TABLE:用於在已有的表中添加、刪除或修改列。

案例：給「people」表中添加一個名為「Birthday」的列

ALTER TABLE people ADD Birthday date;

ALTER TABLE people DROP COLUMN Birthday ; （刪除「Birthday」列）

ALTER TABLE people CHANGE FirstName name varchar(55);（將「FirstName」列重命名為「name」）

UPDATE語句：更新表中記錄

UPDATE Employees SET Salary=6000 WHERE ID=1; （WHERE子句規定哪條記錄需要更新，如果省略WHERE子句，所有記錄都將被更新！）

UPDATE Employees SET Salary=5500, FirstName=』Robert』WHERE ID=1; （逗號分隔，更新多列）

RENAME TABLE: 重命名整個表。

RENAME TABLE people TO users;

DELETE 語句：刪除表中記錄

DELETE FROM Employees WHERE ID=1;（WHERE子句規定哪條記錄需要刪除，如果省略WHERE子句，所有記錄都將被刪除！）

DROP TABLE: 刪除整個表。

DROP TABLE people；

SQL視圖：是基於SQL語句的結果集的可視化的表。視圖包含行和列，就像一個真實的表。

案例：創建一個視圖List，顯示每個員工的「FirstName」和「Salary」。

CREATE VIEW List AS SELECT FirstName, Salary FROM Employees;

可以像查詢實際表一樣查詢列表視圖：

SELECT * FROM List;

DROP VIEW:刪除視圖。

DROP VIEW List;

——附錄——

• 數據類型

Mysql支持的數據類型有哪些？

SQL語句中，創建一個數據表時create table user{user_id int unsigned};

當中的unsigned表示，數據項user_id恆為正整數。例如如果tinyint最大是127，那tinyint unsigned最大就可以到 127 * 2。unsigned 屬性只針對整型，而binary屬性只用於char 和varchar。

• SQL運算符

• UTF-8和GBK有什麼區別

GBK編碼專門用來解決中文編碼的，是雙位元組的。不論中英文都是雙位元組的。

——使用MySQL資料庫——

創建名為samp的資料庫：Create database samp character set gbk;（字元編碼指定為gbk）查看已經創建的資料庫：Show databases;（類似的，查看錶：show tables; 。Describe表名: 查看已創建表的詳細信息）指定要使用的資料庫：Use samp （可以不加分號。輸入後提示Database changed）創建表students: Create table students ( id int unsigned not null auto_increment primary key, name char(8) not null, Sex char(4) not null, age tinyint unsigned not null, tel char(13) null default 『-』);向表中插入數據：insert into students values(NULL, 『王剛『,『男』,20, 『13811223344』)；查詢表中數據：select * from students;更新表中數據：將所有人年齡增加1：Update students set age=age+1;在表的最後追加列address：Alter table students add address char(60);在名為age的列後插入列birthday：Alter table students add birthday date after age;修改列，將表tel列改名為telphone。Alter table studengts change tel telphone char(13) default 『-』;將name列的數據類型改為char(16)。Alter table students change name name char(16) not null;重命名students表為workmates。Alter table students rename workmates;刪除列birthday：Alter table workmates drop birthday;刪除表中數據：刪除id為1的行：delete from workmates where id=1;刪除表中所有數據：delete from workmates;刪除表: Drop table workmates;刪除整個資料庫：Drop database samp;輸入exit或quit退出登錄。

相關鏈接：

MySQL Workbench導入csv文件

推薦閱讀：