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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL 百萬級分頁優(yōu)化(Mysql千萬級快速分頁)
MySql 性能到底能有多高?用了php半年多,真正如此深入的去思考這個問題還是從前天開始。有過痛苦有過絕望,到現(xiàn)在充滿信心2012-11-11MySQL里的found_row()與row_count()的解釋及用法
MySQL中有兩個函數(shù)來計算上一條語句影響了多少行,不同于SqlServer/Oracle,不要因為此方面的差異而引起功能問題2013-02-02