您好,登錄后才能下訂單哦!
這篇文章主要講解了“SQL常用語法介紹”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“SQL常用語法介紹”吧!
系列教程
一、SQL語言的簡介和規(guī)范
二、數(shù)據(jù)庫操作
1、創(chuàng)建庫
2、刪除庫
3、查看數(shù)據(jù)庫列表
三、表操作
1、創(chuàng)建表
2、修改表
3、刪除表
4、查看表
四、DML: 數(shù)據(jù)操作語言
1、INSERT 插入數(shù)據(jù)
2、UPDATE 修改數(shù)據(jù)
3、DELETE 刪除數(shù)據(jù)
五、SELECT:數(shù)據(jù)查詢
六、多表查詢
1、交叉連接
2、內(nèi)連接
3、外連接
4、完全外連接
5、自連接
七、子查詢
八、數(shù)據(jù)類型
1、數(shù)值型
2、字符型
3、日期時間型
4、布爾型
總結(jié)
是一種特定目的程序語言,用于管理關(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
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;
我不會
SHOW DATABASES;
方法一: 直接創(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;
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');
MariaDB [testdb]> DROP TABLE user3;
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; 查看所有存儲引擎
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 | | +--------+---------------------+------+-----+---------+----------------+
單條記錄插入
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 | +----+---------------+------+--------+-----------+
UPDATE tbl_name SET col1=value1,col2=value2,... WHERE col=value;
MariaDB [testdb]> UPDATE students SET gender='F' WHERE id=3;
MariaDB [testdb]> DELETE FROM students WHERE name=''; #刪除名字為空的記錄 MariaDB [testdb]> TRUNCATE TABLE user; #情況表記錄
注意:一定要有限制條件(WHERE | LIMIT),否則將修改所有行的指定字段
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; #倆張表取交集
MariaDB [testdb]> SELECT * FROM students JOIN score;
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 | +---------------+-------+
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; #右外連接
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 | +---------------+-----------+
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ù)類型對于獲得高性能至關(guān)重要,三大原則:
更小的通常更好,盡量使用可正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型
簡單就好,簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期
盡量避免NULL,包含為NULL的列,對MySQL更難優(yōu)化
精確數(shù)值
INT
TINYINT 微整型 1
SMALLINT 小整型 2
MEDIUMINT 中整型 3
INT 整型 4
BIGINT 大整型 8
DECIMAL 精確定點型
近似數(shù)值
FLOAT 單精度浮點型 4
DOUBLE 雙精度浮點型 8
REAL
BIT
定長
- 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個字符串,可以隨意組合
DATE 3
TIME 3
DATETIME 8
TIMESTAMP 4
YEAR{2|4} 1
BOOL,BOOLEAN:布爾型,是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真。
參考官方文檔:https://dev.mysql.com/doc/refman/5.5/en/data-types.html
感謝各位的閱讀,以上就是“SQL常用語法介紹”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對SQL常用語法介紹這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關(guān)知識點的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。