MySQL快速入門

發表於2016-03-16
MySql

MySQL是一款流行的關係型資料庫, 結構化查詢語言SQL(Structured Query Language)是關係型資料庫通用的操作語言, 但是不同資料庫系統的行為還是有著細微的不同, 本文將以MySQL為例進行介紹.

一個MySql服務例項下可以維護多個database, 其中部分資料庫用於維護使用者和許可權資訊. Mysql提供了一些資料庫管理語句:

  • SHOW DATABASES; 顯示當前例項所擁有的資料庫

  • DROP DATABASES db_name; 刪除指定資料庫

  • CREATE DATABASE [IF NOT EXISTS] db_name; 建立資料庫

  • use db_name; 進入資料庫

MySQL將資料儲存在資料表中, 資料表由資料庫來管理. 此外, 有一些命令可以檢視資料表的元資訊:

  • SHOW TABLES 顯示當前資料庫所有資料表

  • DESC <table_name> 或 DESCRIBE <table_name> 顯示錶結構

DDL

資料定義語言DDL(Data Definition Language)用於運算元據表等資料庫物件，對資料表、索引等進行定義。

資料表table是行列的邏輯結構, 每行是一條記錄, 每列是記錄的一個欄位. 同一張表的所有記錄擁有相同的結構, 建立表時最重要的資訊就是表的結構資訊.

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(20) DEFAULT "",
    `password` CHAR(20) DEFAULT "",
    `gender` CHAR(1),
    `age` INT
);

SQL語句不區分大小寫, 通常我們將SQL關鍵字大寫, 自定義識別符號小寫. 用反點號包括自定義識別符號可以避免它們被認為是SQL關鍵字.

SQL識別符號以字母開頭, 可以使用字母, 數字或三個特殊符號#, _, $.識別符號用於表示表, 列和其它物件的名稱.

所有DDL語句中的DEFAULT項都是可以省略的.

MySQL中常用的資料型別有:

  • INT: 32位有符號整數
  • DOUBLE 雙精度浮點數
  • DECIMAL 精確小數
  • CHAR(limit), 定長字串
  • VARCHAR(limit) 變長字串
  • TEXT 長文字資料
  • DATE 日期
  • DATETIME 日期和時間
  • TIMESTAMP UNIX時間戳

使用drop語句來刪除資料表:

DROP TABLE `user`;

表在建立完成後仍可以修改其結構, 不過要注意對其中資料的影響:

  • 刪除列
ALTER TABLE `user` DROP `age`;
  • 增加列:
ALTER TABLE `user` ADD `age` INT DEFAULT 0;
  • 修改列的型別或名稱
ALTER TABLE `user` CHANGE `gender` `sex` CHAR(1) DEFAULT "M";
ALTER TABLE `user` MODIFY `gender` INT DEFAULT 0;

DML

資料操作語言DML(Data Manipulation Language), 用於操作表中的資料。

插入一條記錄:

INSERT INTO `user` (`username`, `password`) VALUES ("abcd", "1234");
INSERT INTO `user` VALUES ("abcd", "1234");

要向所有沒有預設值且不允許為空的列插入資料.

更新已存在的記錄:

UPTATE `user` SET `username`="abc" WHERE `id`=1;

update可以更新所有符合WHERE子句的記錄, 當沒有WHERE子句時更新該表所有記錄.

當鍵重複時更新記錄, 否則插入記錄:

INSERT INTO `user` VALUES ("a", "2") ON DUPLICATE KEY UPDATE `username`="a", "password"=2;

刪除記錄:

DELETE FROM `user` WHERE `id`=1;

刪除所有符合WHERE子句的記錄, 當沒有WHERE子句時刪除該表所有記錄.

SELECT

查詢語句SELECT是最靈活最複雜也是最重要的SQL語句.

我們建立使用者user和文章post兩張表做為示例:

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(20) DEFAULT "",
    `password` CHAR(20) DEFAULT "",
    `gender` CHAR(1),
    `age` INT
);
CREATE TABLE `post` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    uid INT,
    title TEXT,
    CONTENT TEXT
);

查詢user表中所有記錄的所有欄位.

SELECT * FROM `user`;

SELECT可以可以查詢某個表中符合條件記錄的某些欄位.如所有男性使用者的username:

SELECT `username`
FROM `user`
WHERE `gender`='M';

我們可以使用LIMIT限制返回結果的數量:

SELECT * FROM `user` LIMIT 10 OFFSET 10;

上述查詢跳過前10個(OFFSET 10)結果， 並返回最多10條記錄(LIMIT 10)。

在多表查詢時可能需要指定列的所在的表:

SELECT `user`.`username`
FROM `user`
WHERE `user`.`gender`='M';

為了簡化表示式可以為列和表指定別名:

SELECT u.`username` AS name
FROM `user` u
WHERE u.`gender`='M';

表中可能出現使用者名稱相同的情況, 使用DISTINCT函式去除重複的使用者名稱:

SELECT DISTINCT(`username`)
FROM `user`;

聚集

MySQL可以進行取平均值, 求和等聚集操作.

SELECT AVG(`age`) FROM `user`;

上述語句查詢user表中所有記錄age欄位的平均值. 可以使用GROUP BY子句指定分組聚集方式.

SELECT AVG(`age`) FROM `user`
GROUP BY `gender`;

上述語句將gender欄位相同的記錄視作一組, 求出age欄位的平均值. 我們可以附加where子句查詢男性使用者的平均年齡:

SELECT gender, AVG(`age`) FROM `user`
WHERE gender=`M`
GROUP BY gender;

WHERE子句中無法使用聚集函式, 可以使用HAVING子句完成該功能, 比如查詢發表文章超過3篇的作者:

SELECT uid FROM `post`
HAVING count(id) > 3
GROUP BY uid;

常用的聚集函式有:

  • SUM 求和

  • AVG 求平均值

  • MIN 求最小值

  • MAX 求最大值

  • COUNT 求記錄數

此外還有一些工具函式也順便介紹:

  • LENGETH(txt), LEN(txt): 求文字長度

  • REPLACE(txt, from, to) 替換文字

  • UCASE(txt), LCASE(txt): 轉為大寫 / 小寫

  • mid(txt, start, len): 提取子串

排序

ORDER BY 語句可以根據結果中某一列進行排序:

SELECT * FROM user ORDER BY age DESC LIMIT 10;

把user表中的記錄根據age欄位進行降序排列， 並返回前10條記錄。

SELECT * FROM user ORDER BY age ASC LIMIT 10;

把user表中的記錄根據age欄位進行升序排列， 並返回前10條記錄。

ORDER BY 語句可以進行多列排序:

SELECT * FROM user ORDER BY age DESC, username ASC;

把user表中的記錄根據age欄位進行升序排列，age相同的根據username字典序升序排列。

ORDER BY 語句可以根據聚集函式進行排序:

SELECT post_id, count(*) as comment_count 
FROM comment
GROUP BY post_id
ORDER BY count(*) DESC;

根據文章的評論數進行降序排列。

聯合查詢

現在查詢所有女性使用者發表的文章:

SELECT p.`id` AS post_id, `title`, `content`, u.`username` AS author
FROM `user` u, `post` p
WHERE
    p.`uid`=u.`id`
    AND u.`gender`='F';

字串可以使用單引號'或雙引號"表示.

上述查詢將搜尋user和post表中記錄所有組合, 滿足條件p.uid=u.id的user記錄和post記錄將組合為同一條記錄. 然後查詢組合記錄中符合條件u.gender='F'的記錄.

使用等價的join操作來代替上述查詢:

SELECT p.`id` AS post_id, title, content, u.`username` AS author 
FROM `post` p
JOIN `user` u 
ON p.`uid`=u.`id`
WHERE u.`gender`='F';

join操作有3種:

  • INNER JOIN, JOIN: 查詢出的記錄必須滿足ON條件，若左表或右表中沒有對應的記錄，查詢結果中均不會包含相應記錄。

  • LEFT JOIN: 查詢左表post中所有記錄, 即使右表中沒有對應的記錄. 當沒有右表記錄時, 查詢結果中右表的欄位為空值.

  • REIGHT JOIN 查詢右表user中所有記錄, 即使左表中沒有對應記錄. 當沒有左表記錄時, 查詢結果中左表的欄位為空值.

  • OUTER JOIN: LEFT JOIN 和 RIGHT JOIN 結果的並集，即左右表只有一個表中有記錄即可。

注意若user表中有m條記錄, post表中有n條記錄則連線查詢將會掃描m*n條記錄. 這在表中資料量較大時會非常耗時.

IN 和 EXISTS 查詢

使用IN和EXISTS子查詢可以完成連線查詢的任務.

SELECT *
FROM `post`
WHERE `uid` IN (
    SELECT `user`.`id` FROM `user` WHERE `gender`='F'
);

IN運算子將匹配uid和子查詢的結果, 若記錄的uid在結果集中則IN條件為真.

IN運算子後可以直接書寫列表:

SELECT * FROM user
WHERE username in ('a', 'b');

查詢username為'a'或'b'的使用者。

EXISTS運算子可以起到類似的作用:

SELECT *
FROM `post`
WHERE EXISTS (
    SELECT `user`.`id` FROM `user` WHERE `gender`='F' 
    AND `user`.`id`=`post`.`uid`
);

在EXISTS子查詢中可以訪問外表，如上例exists子查詢中訪問了post表。

上述SQL語句查詢使EXISTS子查詢SELECT user.id FROM user WHERE gender='F' AND user.id=post.uid 結果不為空集的post記錄。即查詢女性作者發表的所有文章，與本節開頭的IN查詢作用相同。

IN和EXISTS都有對應的否定形式:

SELECT *
FROM `post`
WHERE `uid` NOT IN (
    SELECT `user`.`id` FROM `user` WHERE `gender`='F'
);
SELECT *
FROM `post`
WHERE NOT EXISTS (
    SELECT `user`.`id` FROM `user` WHERE `gender`='F' 
    AND `user`.`id`=`post`.`uid`
);

子查詢

SELECT * FROM語句可以把另外一個查詢結果作為資料來源。

新增兩張表:

CREATE TABLE `likes` (
    `uid` INT,
    `post_id` INT,
    PRIMARY KEY (`uid`, `post_id`) 
);

CREATE TABLE `comment` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `uid` INT,
    `post_id` INT,
    `content` TEXT,
);

我們要搜尋所有文章的點贊like數和評論comment數:

SELECT 
    lc.post_id,
    lc.like_count,
    cc.comment_count
FROM (
    SELECT `post_id`, count(*) AS like_count FROM `likes` GROUP BY `post_id`
) lc
OUTER JOIN (
    SELECT `post_id`, count(*) AS comment_count FROM `comment` GROUP BY `post_id`
) cc
ON lc.post_id = cc.post_id;

上述語句中我們將查詢語句SELECT post_id, count(*) AS like_count FROM likes GROUP BY post_id的結果作為一個資料表， 並給它一個別名lc。

類似地，我們將另一個查詢的結果作為資料表cc, 然後將兩個資料表進行JOIN。

檢視

MySQL可以根據查詢建立檢視view物件, 可以用訪問資料表的方式訪問檢視. 檢視只儲存查詢操作不儲存資料, 檢視在被訪問時從資料表中取出資料.

CREATE VIEW `post_detail` AS
SELECT p.`id` AS post_id, title, content, 
    p.`uid` AS author_id, u.`username` AS author_name 
FROM `post` p
JOIN `user` u 
ON p.`uid`=u.`id`
WHERE u.`gender`='F'; 
SELECT * FROM `post_detail`;

約束

約束是在表上強制執行的校驗規則, 用於保證資料的完整性. 約束分為對單列的約束和對多個列集合的約束.

非空約束

MySQL中所有資料型別都可以使用null, 通常使用field IS NULL來判斷某個欄位是否為空.

非空約束限制某一列不允許出現null值, 在建表時新增非空約束:

CREATE TABLE `user` {
  `username` VARCHAR(20) NOT NULL
}

也可以隨時新增或刪除非空約束:

ALTER TABLE `user` modify `username` VARCHAR(20) NULL;
ALTER TABLE `user` modify `username` VARCHAR(20) NOT NULL;

唯一約束

唯一約束要求單列或列的集合不允許出現多個相同的非NULL值, 不允許username列出現重複值:

CREATE TABLE `user` {
  `username` VARCHAR(20) UNIQUE
}

允許username或email重複, 但不允許任意兩條記錄在username相同時email也相同.

CREATE TABLE `user` {
  `username` VARCHAR(20),
  `email` VARCHAR(20),
  UNIQUE(`username`, `email`)
}

為了便於後續操作, 最好使用命名約束:

CREATE TABLE `user` {
  username VARCHAR(20),
  email VARCHAR(20),
  CONSTRAINT uc_user UNIQUE(username, email)
}

使用ALTER新增唯一約束:

ALTER TABLE `user` ADD UNIQUE(`username`);
ALTER TABLE `user` ADD CONSTRAINT uc_user UNIQUE(`username`, `email`);

撤銷唯一約束:

ALTER TABLE `user` DROP CONSTRAINT uc_user;

主鍵約束

主鍵是表上的一列或幾列的集合, 主鍵列必須非空且唯一. 主鍵必須可以唯一確定表中的記錄, 即不存在主鍵列相同的兩條記錄.

每個表最多包含一個主鍵約束:

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(20) DEFAULT "",
);
CREATE TABLE `user` {
  username VARCHAR(20),
  email VARCHAR(20),
  CONSTRAINT user_key PRIMARY KEY(username, email)
}

使用ALTER修改主鍵約束:

ALTER TABLE `user` ADD PRIMARY KEY(`id`);
ALTER TABLE `user` ADD CONSTRAINT user_key PRIMARY KEY(`username`, `email`);

因為最多有一個主鍵約束, 所以刪除時不用指定約束物件:

ALTER TABLE `user` DROP PRIMARY KEY;

外來鍵約束

外來鍵約束將資料表B上的某列或列集合與資料表A的主鍵關聯, 外來鍵列的結構必須與被參照主鍵的結構一致, 但允許外來鍵列的值重複. 資料表B上每條記錄的外來鍵列必須與被參照表A的某條記錄的主鍵相同.

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(20) DEFAULT "",
    `password` CHAR(20) DEFAULT "",
    `gender` CHAR(1),
    `age` INT
);
CREATE TABLE `post` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    uid INT,
    title TEXT,
    CONTENT TEXT,
    FOREIGN KEY(uid) REFERENCES user(`id`) 
);

建立在post表上的外來鍵可以保證post表中每條記錄的uid都指向user表中一條記錄. 避免存在找不到作者的文章.

當刪除被參照表上的某條記錄時, 必須刪除所有參照它的記錄. 即刪除使用者前必須前必須刪除他發表的所有文章, 以保證外來鍵約束不被破壞.

在擁有外來鍵約束的表post上新增資料時必須掃描一遍被參照的user, 這可能消耗很多時間, 在使用外來鍵時必須要考慮到這一點.

相關文章