主頁 > 知識庫 > MySQL系列之四 SQL語法

MySQL系列之四 SQL語法

系列教程

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

一、SQL語言的簡介和規(guī)范

是一種特定目的程序語言，用于管理關(guān)系數(shù)據(jù)庫管理系統(tǒng)（RDBMS），或在關(guān)系流數(shù)據(jù)管理系統(tǒng)（RDSMS）中進(jìn)行流處理。

20世紀(jì)70年代，IBM開發(fā)出SQL，用于DB2
1981年，IBM推出SQL/DS數(shù)據(jù)庫
業(yè)內(nèi)標(biāo)準(zhǔn)微軟和Sybase的T-SQL，Oracle的PL/SQL
SQL作為關(guān)系型數(shù)據(jù)庫所使用的標(biāo)準(zhǔn)語言，最初是基于IBM的實現(xiàn)在1986年被批準(zhǔn)的。1987年，“國際標(biāo)準(zhǔn)化組織(ISO)”把ANSI(美國國家標(biāo)準(zhǔn)化組織) SQL作為國際標(biāo)準(zhǔn)。
SQL：ANSI SQL ——SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL語言的規(guī)范

在數(shù)據(jù)庫系統(tǒng)中，SQL語句不區(qū)分大小寫(建議用大寫)
但字符串常量區(qū)分大小寫
SQL語句可單行或多行書寫，以“;”結(jié)尾
關(guān)鍵詞不能跨多行或簡寫
用空格和縮進(jìn)來提高語句的可讀性
子句通常位于獨立行，便于編輯，提高可讀性
注釋：

SQL標(biāo)準(zhǔn)：
- /* 注釋內(nèi)容 */ 多行注釋
- -- 注釋內(nèi)容單行注釋，注意有空格
MySQL注釋： #

數(shù)據(jù)庫對象的命名規(guī)則

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

SQL語句的分類

DDL: Data Defination Language 數(shù)據(jù)定義語言

- CREATE, DROP, ALTER

DML: Data Manipulation Language 數(shù)據(jù)操作語言

- INSERT, DELETE, UPDATE

DCL：Data Control Language 數(shù)據(jù)控制語言

- GRANT, REVOKE

DQL：Data Query Language 數(shù)據(jù)查詢語言

- SELECT

二、數(shù)據(jù)庫操作

1、創(chuàng)建庫

CREATE DATABASE [IF NOT EXISTS] db_name; 創(chuàng)建數(shù)據(jù)庫

CHARACTER SET 'character set name' 設(shè)置字符集類型

COLLATE 'collate name' 設(shè)置排序規(guī)則

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

查看支持所有排序規(guī)則：SHOW COLLATION;

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

2、刪除庫

我不會

3、查看數(shù)據(jù)庫列表

SHOW DATABASES;

三、表操作

1、創(chuàng)建表

方法一：直接創(chuàng)建
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'));

方法二：通過查詢現(xiàn)存表創(chuàng)建；新表會被直接插入查詢而來的數(shù)據(jù)

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

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

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

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

方法三：通過復(fù)制現(xiàn)存的表的表結(jié)構(gòu)創(chuàng)建，但不復(fù)制數(shù)據(jù)

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; 查看表結(jié)構(gòu)

SHOW CREATE TABLE tbl_name; 查看創(chuàng)建表的命令

SHOW TABLE STATUS LIKE 'tbl_name'; 查看表狀態(tài)

SHOW TABLE STATUS FROM db_name; 查看指定庫中所有表狀態(tài)

SHOW ENGINES; 查看所有存儲引擎

四、DML: 數(shù)據(jù)操作語言

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 插入數(shù)據(jù)

單條記錄插入

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      |
+----+---------------+------+--------+

從其他表查詢數(shù)據(jù)保存到此表中

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 修改數(shù)據(jù)

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

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

3、DELETE 刪除數(shù)據(jù)

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

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

五、SELECT：數(shù)據(jù)查詢

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

1、選擇

MariaDB [testdb]> SELECT * FROM students WHERE name='maria';  #查詢maria的信息
MariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5;  #查詢2到5號學(xué)生的信息
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 主機(jī),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的，正序排序，取前三條記錄

六、多表查詢

為了練習(xí)，我們將表在擴(kuò)展一下

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：內(nèi)連接

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、內(nèi)連接

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    |
+---------------+---------------+

七、子查詢

子查詢：在查詢語句嵌套著查詢語句，性能較差，基于某語句的查詢結(jié)果再次進(jìn)行的查詢

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

用于比較表達(dá)式中的子查詢；子查詢僅能返回單個值

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

用于IN中的子查詢：子查詢應(yīng)該單鍵查詢并返回一個或多個值從構(gòu)成列表

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

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

八、數(shù)據(jù)類型

選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要，三大原則：

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

1、數(shù)值型

精確數(shù)值

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

近似數(shù)值

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

2、字符型

定長

- CHAR（不區(qū)分大小寫）255

- BINARY（區(qū)分大小寫）

變長

VARCHAR（不區(qū)分大小寫）65,535
VARBINNARY（區(qū)分大小寫）

TEXT（不區(qū)分大小寫）

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

BLOB（區(qū)分大小寫）

TINYBLOB 微二進(jìn)制大對象 255
BLOB 二進(jìn)制大對象 64K
MEDIUMBLOB 中二進(jìn)制大對象 16M
LONGBLOB 長二進(jìn)制大對象 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

總結(jié)

到此這篇關(guān)于SQL語法的文章就介紹到這了,更多相關(guān)SQL語法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL 最基本的SQL語法/語句
MySQL與Oracle的語法區(qū)別詳細(xì)對比
MySQL 創(chuàng)建索引(Create Index)的方法和語法結(jié)構(gòu)及例子
淺析Mysql Join語法以及性能優(yōu)化
MySQL ALTER語法的運用方法
MySQL prepare語句的SQL語法
MySQL進(jìn)階SELECT語法篇
MySQL SQL 語法參考
MySQL Order By語法介紹
MySQL DELETE語法使用詳細(xì)解析

標(biāo)簽：湘潭蘭州黃山仙桃銅川崇左湖南衡水

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL系列之四 SQL語法》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。