目錄
- 系列教程
- 一、SQL語言的簡介和規范
- 二、數據庫操作
- 三、表操作
- 四、DML: 數據操作語言
- 1、INSERT 插入數據
- 2、UPDATE 修改數據
- 3、DELETE 刪除數據
- 五、SELECT：數據查詢
- 六、多表查詢
- 1、交叉連接
- 2、內連接
- 3、外連接
- 4、完全外連接
- 5、自連接
- 七、子查詢
- 八、數據類型
- 1、數值型
- 2、字符型
- 3、日期時間型
- 4、布爾型
- 總結
系列教程
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 數據定義語言
DML: Data Manipulation Language 數據操作語言
DCL：Data Control Language 數據控制語言
DQL：Data Query Language 數據查詢語言
二、數據庫操作
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、查看數據庫列表
三、表操作
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語法內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL 最基本的SQL語法/語句
- MySQL與Oracle的語法區別詳細對比
- MySQL 創建索引(Create Index)的方法和語法結構及例子
- 淺析Mysql Join語法以及性能優化
- MySQL ALTER語法的運用方法
- MySQL prepare語句的SQL語法
- MySQL進階SELECT語法篇
- MySQL SQL 語法參考
- MySQL Order By語法介紹
- MySQL DELETE語法使用詳細解析
