MySQL系列之四 SQL語法

Posted on 2021-07-03 by WalkonNet

系列教程

MySQL系列之開篇 MySQL關系型數據庫基礎概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二多實例配置
MySQL系列之三基礎篇
MySQL系列之五視圖、存儲函數、存儲過程、觸發器
MySQL系列之六用戶與授權
MySQL系列之七 MySQL存儲引擎
MySQL系列之八 MySQL服務器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務隔離實現並發控制
MySQL系列之十一日志記錄
MySQL系列之十二備份與恢復
MySQL系列之十三 MySQL的復制
MySQL系列之十四 MySQL的高可用實現
MySQL系列之十五 MySQL常用配置和性能壓力測試

一、SQL語言的簡介和規范

是一種特定目的程序語言，用於管理關系數據庫管理系統（RDBMS），或在關系流數據管理系統（RDSMS）中進行流處理。

20世紀70年代，IBM開發出SQL，用於DB2
1981年，IBM推出SQL/DS數據庫
業內標準微軟和Sybase的T-SQL，Oracle的PL/SQL
SQL作為關系型數據庫所使用的標準語言，最初是基於IBM的實現在1986年被批準的。1987年，“國際標準化組織(ISO)”把ANSI(美國國傢標準化組織) SQL作為國際標準。
SQL：ANSI SQL ——SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL語言的規范

在數據庫系統中，SQL語句不區分大小寫(建議用大寫)
但字符串常量區分大小寫
SQL語句可單行或多行書寫，以“;”結尾
關鍵詞不能跨多行或簡寫
用空格和縮進來提高語句的可讀性
子句通常位於獨立行，便於編輯，提高可讀性
註釋：

SQL標準：
- /* 註釋內容 */ 多行註釋
- — 註釋內容單行註釋，註意有空格
MySQL註釋： #

數據庫對象的命名規則

– 必須以字母開頭
– 可包括數字和三個特殊字符（# _ $）
– 不要使用MySQL的保留字
– 同一database(Schema)下的對象不能同名

SQL語句的分類

DDL: Data Defination Language 數據定義語言

- CREATE, DROP, ALTER

DML: Data Manipulation Language 數據操作語言

- INSERT, DELETE, UPDATE

DCL：Data Control Language 數據控制語言

- GRANT, REVOKE

DQL：Data Query Language 數據查詢語言

- SELECT

二、數據庫操作

1、創建庫

CREATE DATABASE [IF NOT EXISTS] db_name; 創建數據庫

CHARACTER SET ‘character set name’ 設置字符集類型

COLLATE ‘collate name’ 設置排序規則

查看支持所有字符集：SHOW CHARACTER SET;

查看支持所有排序規則：SHOW COLLATION;

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;

2、刪除庫

我不會

3、查看數據庫列表

SHOW DATABASES;

三、表操作

1、創建表

方法一：直接創建
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,…)

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,phone CHAR(11),gender ENUM('M','F'));

方法二：通過查詢現存表創建；新表會被直接插入查詢而來的數據

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

MariaDB [testdb]> CREATE TABLE user SELECT user,host,password FROM mysql.user;

如果隻想模仿查詢舊表創建一個無記錄的表我們可以加入條件 WHERE 0=1;

MariaDB [testdb]> CREATE TABLE user2 SELECT user,host,password FROM mysql.user WHERE 0=1;

方法三：通過復制現存的表的表結構創建，但不復制數據

CREATE TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name

MariaDB [testdb]> CREATE TABLE user3 LIKE mysql.user;

2、修改表

ALTER TABLE tbl_name [alter_specification [, alter_specification] …]

增加屬性 ADD

MariaDB [testdb]> ALTER TABLE students ADD age TINYINT AFTER name;

刪除屬性 DROP

MariaDB [testdb]> ALTER TABLE students DROP phone;

修改屬性 CHANGE, MODIFY

MariaDB [testdb]> ALTER TABLE students CHANGE age ages TINYINT(2) NOT NULL;

MariaDB [testdb]> ALTER TABLE students MODIFY gender ENUM('M','F');

3、刪除表

MariaDB [testdb]> DROP TABLE user3;

4、查看表

SHOW TABLES; 列出庫中所有的表

DESC [db_name.]tb_name; 查看表結構

SHOW CREATE TABLE tbl_name; 查看創建表的命令

SHOW TABLE STATUS LIKE ‘tbl_name’; 查看表狀態

SHOW TABLE STATUS FROM db_name; 查看指定庫中所有表狀態

SHOW ENGINES; 查看所有存儲引擎

四、DML: 數據操作語言

MariaDB [testdb]> DESC students;  #示例表
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)         | NO   |     | NULL    |                |
| ages   | tinyint(2)          | NO   |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+

1、INSERT 插入數據

單條記錄插入

INSERT INTO tb_name (col1,col2,…) VALUES (val1,val2,…);

MariaDB [testdb]> INSERT students(id,name,ages,gender) VALUES (1,'tom',26,'M');
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('jerry',19,'M'); 
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('maria',19,'M');
MariaDB [testdb]> INSERT students SET name='ouyangfeng',ages=56,gender='M';

多條記錄插入

INSERT INTO tb_name (col1,col2,…) VALUES (val1,val2,…)[,(val1,val2,…),…];

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('xiaolongnv',18,'F'),('dongfangbubai',28,'F');

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+
| id | name          | ages | gender |
+----+---------------+------+--------+
|  1 | tom           |   26 | M      |
|  2 | jerry         |   19 | M      |
|  3 | maria         |   19 | M      |
|  4 | xiaolongnv    |   18 | F      |
|  5 | dongfangbubai |   28 | F      |
|  6 | ouyangfeng    |   56 | M      |
+----+---------------+------+--------+

從其他表查詢數據保存到此表中

MariaDB [testdb]> ALTER TABLE students ADD address TEXT;  #加個字段做測試用

MariaDB [testdb]> INSERT students(name,address) SELECT user,host FROM mysql.user;

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+-----------+
| id | name          | ages | gender | address   |
+----+---------------+------+--------+-----------+
|  1 | tom           |   26 | M      | NULL      |
|  2 | jerry         |   19 | M      | NULL      |
|  3 | maria         |   19 | M      | NULL      |
|  4 | xiaolongnv    |   18 | F      | NULL      |
|  5 | dongfangbubai |   28 | F      | NULL      |
|  6 | ouyangfeng    |   56 | M      | NULL      |
|  7 | root          |    0 | NULL   | 127.0.0.1 |
|  8 | root          |    0 | NULL   | ::1       |
|  9 |               |    0 | NULL   | centos7   |
| 10 | root          |    0 | NULL   | centos7   |
| 11 |               |    0 | NULL   | localhost |
| 12 | root          |    0 | NULL   | localhost |
+----+---------------+------+--------+-----------+

2、UPDATE 修改數據

UPDATE tbl_name SET col1=value1,col2=value2,… WHERE col=value;

MariaDB [testdb]> UPDATE students SET gender='F' WHERE id=3;

3、DELETE 刪除數據

MariaDB [testdb]> DELETE FROM students WHERE name='';  #刪除名字為空的記錄
MariaDB [testdb]> TRUNCATE TABLE user;  #情況表記錄

註意：一定要有限制條件（WHERE | LIMIT），否則將修改所有行的指定字段

五、SELECT：數據查詢

AS：別名
WHERE：指明過濾條件以實現“選擇”的功能
- +, -, *, /, %：算術操作符
- =, !=, <>, >, <, >=, <=：比較操作符
- BETWEEN min_num AND max_num：在min_num和max_mun之間
- IN (element1,element2,…)：在element…中的
- IS NULL：為空
- IS NOT NULL：不為空
- LIKE：做匹配，像。。。
  %：任意長度的任意字符
  _：單個任意字符
- RLIKE：正則表達式，不建議用
- REGEXP：同上
- NOT, AND, OR, XOR：邏輯操作符
GROUP BY：根據指定的條件把查詢結果進行“分組”以用於做“聚合”運算
- AVG() 平均數
- MAX() 最大數
- MIN() 最小數
- COUNT() 統計
- SUM() 求和
- HAVING ：對分組聚合運算後的結果指定過濾條件。類似WHERE的作用，但隻能在分組中使用
ORDER BY：排序
- ASC：正序，默認
- DESC：倒序
- -KEYWORD：在排序時在關鍵字前加-可以避免把NULL排在前邊
LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制

1、選擇

MariaDB [testdb]> SELECT * FROM students WHERE name='maria';  #查詢maria的信息
MariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5;  #查詢2到5號學生的信息
MariaDB [testdb]> SELECT * FROM students WHERE name IN ('jerry','xiaolongnv');  #查詢jerry和xiaolongnv的信息
MariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL;  #查詢年齡不為空的信息
MariaDB [testdb]> SELECT * FROM students WHERE name LIKE '%o%';  #查詢姓名中包含'o'的信息

2、投影

MariaDB [testdb]> SELECT user AS 用戶,host AS 主機,password AS 密碼 FROM mysql.user;

3、分組

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender;  #查詢男生、女生年齡的平均值
MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender HAVING gender='M';  #隻顯示男生的平均年齡信息

4、排序

MariaDB [testdb]> SELECT * FROM students ORDER BY ages DESC;  #按年齡排序，倒序顯示
MariaDB [testdb]> SELECT * FROM students WHERE ages > 0 ORDER BY ages LIMIT 3;  #按年齡排序，過濾年齡大於0的，正序排序，取前三條記錄

六、多表查詢

為瞭練習，我們將表在擴展一下

MariaDB [testdb]> DELETE FROM students WHERE id BETWEEN 7 AND 12;
MariaDB [testdb]> CREATE TABLE score (id TINYINT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,score TINYINT(3));
MariaDB [testdb]> ALTER TABLE students ADD sid TINYINT(2); 
MariaDB [testdb]> UPDATE students SET sid=6 WHERE id=6;
MariaDB [testdb]> INSERT score SET score=87;
MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+
| id | name          | ages | gender | address | sid  |
+----+---------------+------+--------+---------+------+
|  1 | tom           |   26 | M      | NULL    |    1 |
|  2 | jerry         |   19 | M      | NULL    |    2 |
|  3 | maria         |   19 | F      | NULL    |    3 |
|  4 | xiaolongnv    |   18 | F      | NULL    |    4 |
|  5 | dongfangbubai |   28 | F      | NULL    |    5 |
|  6 | ouyangfeng    |   56 | M      | NULL    |    6 |
+----+---------------+------+--------+---------+------+
MariaDB [testdb]> SELECT * FROM score;   
+----+-------+
| id | score |
+----+-------+
|  1 |    99 |
|  2 |    98 |
|  3 |    88 |
|  4 |    68 |
|  5 |    78 |
|  6 |    87 |
+----+-------+

JOIN ON：交叉連接

INNER JOIN ON：內連接

LEFT OUTER JOIN ON：左外連接

RIGHT OUTER JOIN ON：右外連接

UNION ON：完全外連接

MariaDB [testdb]> SELECT * FROM students AS s,score AS o WHERE s.sid=o.id;  #倆張表取交集

1、交叉連接

MariaDB [testdb]> SELECT * FROM students JOIN score;

2、內連接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t INNER JOIN score AS s ON t.sid=s.id;
+---------------+-------+
| name          | score |
+---------------+-------+
| tom           |    99 |
| jerry         |    98 |
| maria         |    88 |
| xiaolongnv    |    68 |
| dongfangbubai |    78 |
| ouyangfeng    |    87 |
+---------------+-------+

3、外連接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t LEFT JOIN score AS s ON t.sid=s.id;  #左外連接
+---------------+-------+
| name          | score |
+---------------+-------+
| tom           |    99 |
| jerry         |    98 |
| maria         |    88 |
| xiaolongnv    |    68 |
| dongfangbubai |    78 |
| ouyangfeng    |    87 |
+---------------+-------+

MariaDB [testdb]> SELECT * FROM students AS t RIGHT JOIN score AS s ON t.sid=s.id;  #右外連接

4、完全外連接

MariaDB [testdb]> SELECT name,address FROM students
    -> UNION
    -> SELECT user,host FROM mysql.user;
+---------------+-----------+
| name          | address   |
+---------------+-----------+
| tom           | NULL      |
| jerry         | NULL      |
| maria         | NULL      |
| xiaolongnv    | NULL      |
| dongfangbubai | NULL      |
| ouyangfeng    | NULL      |
| root          | 127.0.0.1 |
| root          | ::1       |
|               | centos7   |
| root          | centos7   |
|               | localhost |
| root          | localhost |
+---------------+-----------+

5、自連接

MariaDB [testdb]> ALTER TABLE students ADD tid TINYINT(2);  #再加一個tid字段
MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+------+
| id | name          | ages | gender | address | sid  | tid  |
+----+---------------+------+--------+---------+------+------+
|  1 | tom           |   26 | M      | NULL    |    1 |    2 |
|  2 | jerry         |   19 | M      | NULL    |    2 |    1 |
|  3 | maria         |   19 | F      | NULL    |    3 |    4 |
|  4 | xiaolongnv    |   18 | F      | NULL    |    4 |    5 |
|  5 | dongfangbubai |   28 | F      | NULL    |    5 |    4 |
|  6 | ouyangfeng    |   56 | M      | NULL    |    6 |    4 |
+----+---------------+------+--------+---------+------+------+

MariaDB [testdb]> SELECT s1.name AS studentname,s2.name AS teachername FROM students AS s1 INNER JOIN students AS s2 ON s1.id=s2.tid;
+---------------+---------------+
| studentname   | teachername   |
+---------------+---------------+
| jerry         | tom           |
| tom           | jerry         |
| xiaolongnv    | maria         |
| dongfangbubai | xiaolongnv    |
| xiaolongnv    | dongfangbubai |
| xiaolongnv    | ouyangfeng    |
+---------------+---------------+

七、子查詢

子查詢：在查詢語句嵌套著查詢語句，性能較差，基於某語句的查詢結果再次進行的查詢

1、用在WHERE子句中的子查詢

用於比較表達式中的子查詢；子查詢僅能返回單個值

MariaDB [testdb]> SELECT name,ages FROM students WHERE ages > (SELECT AVG(ages) FROM students);    #查詢大於平均年齡的同學

用於IN中的子查詢：子查詢應該單鍵查詢並返回一個或多個值從構成列表

2、用於FROM子句中的子查詢

SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias WHERE Clause;

八、數據類型

選擇正確的數據類型對於獲得高性能至關重要，三大原則：

更小的通常更好，盡量使用可正確存儲數據的最小數據類型
簡單就好，簡單數據類型的操作通常需要更少的CPU周期
盡量避免NULL，包含為NULL的列，對MySQL更難優化

1、數值型

精確數值

INT
- TINYINT 微整型 1
- SMALLINT 小整型 2
- MEDIUMINT 中整型 3
- INT 整型 4
- BIGINT 大整型 8
DECIMAL 精確定點型

近似數值

FLOAT 單精度浮點型 4
DOUBLE 雙精度浮點型 8
REAL
BIT

2、字符型

定長

– CHAR（不區分大小寫）255

– BINARY（區分大小寫）

變長

VARCHAR（不區分大小寫）65,535
VARBINNARY（區分大小寫）

TEXT（不區分大小寫）

TINYTEXT 255
TEXT 65,535
MEDIUMTEXT 16,777,215
LONGTEXT 4,294,967,295

BLOB（區分大小寫）

TINYBLOB 微二進制大對象 255
BLOB 二進制大對象 64K
MEDIUMBLOB 中二進制大對象 16M
LONGBLOB 長二進制大對象 4G

ENUM 枚舉 65535種變化

SET 集合 1-64個字符串，可以隨意組合

3、日期時間型

DATE 3
TIME 3
DATETIME 8
TIMESTAMP 4
YEAR{2|4} 1

4、佈爾型

BOOL，BOOLEAN：佈爾型，是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真。

參考官方文檔：https://dev.mysql.com/doc/refman/5.5/en/data-types.html

總結

到此這篇關於SQL語法的文章就介紹到這瞭,更多相關SQL語法內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

MySQL系列之四 SQL語法

目錄

系列教程

一、SQL語言的簡介和規范

二、數據庫操作

1、創建庫

2、刪除庫

3、查看數據庫列表

三、表操作

1、創建表

2、修改表

3、刪除表

4、查看表

四、DML: 數據操作語言

1、INSERT 插入數據

2、UPDATE 修改數據

3、DELETE 刪除數據

五、SELECT：數據查詢

六、多表查詢

1、交叉連接

2、內連接

3、外連接

4、完全外連接

5、自連接

七、子查詢

八、數據類型

1、數值型

2、字符型

3、日期時間型

4、佈爾型

總結

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

系列教程

一、SQL語言的簡介和規范

二、數據庫操作

1、創建庫

2、刪除庫

3、查看數據庫列表

三、表操作

1、創建表

2、修改表

3、刪除表

4、查看表

四、DML: 數據操作語言

1、INSERT 插入數據

2、UPDATE 修改數據

3、DELETE 刪除數據

五、SELECT：數據查詢

六、多表查詢

1、交叉連接

2、內連接

3、外連接

4、完全外連接

5、自連接

七、子查詢

八、數據類型

1、數值型

2、字符型

3、日期時間型

4、佈爾型

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆