SQL語句簡介及練習(xí)

SQL語言的興起與語法標(biāo)準

20世紀70年代，IBM開發(fā)出SQL，用于DB2

1981年，IBM推出SQL/DS數(shù)據(jù)庫

業(yè)內(nèi)標(biāo)準微軟和Sybase的T-SQL，Oracle的PL/SQL

SQL作為關(guān)系型數(shù)據(jù)庫所使用的標(biāo)準語言，最初是基于IBM的實現(xiàn)在1986年被批準的。1987年，“國際標(biāo)準化組織(ISO)”把ANSI(美國國家標(biāo)準化組織) SQL作為國際標(biāo)準。

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)鍵詞不能跨多行或簡寫

用空格和縮進來提高語句的可讀性

子句通常位于獨立行，便于編輯，提高可讀性

注釋：

SQL標(biāo)準：

/*注釋內(nèi)容*/ 多行注釋

-- 注釋內(nèi)容 單行注釋，注意有空格

MySQL注釋：

除了SQL標(biāo)準外還可以用#

數(shù)據(jù)庫對象

數(shù)據(jù)庫的組件(對象)：

數(shù)據(jù)庫、表、索引、視圖、用戶、存儲過程、函數(shù)、觸發(fā)器、事件調(diào)度器等

命名規(guī)則：

必須以字母開頭

可包括數(shù)字和三個特殊字符（# _ $，#和$盡量別用）

不要使用MySQL的保留字

同一database(Schema)下的對象不能同名，即使是不同類型的對象

SQL語句分類

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

SQL語句構(gòu)成

SQL語句構(gòu)成：

Keyword組成clause（子句）

多條clause組成語句

示例：

SELECT * SELECT子句

FROM products FROM子句

WHERE price>400 WHERE子句

說明：此為一組SQL語句，由三個子句構(gòu)成，SELECT,FROM和WHERE是關(guān)鍵字

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

創(chuàng)建數(shù)據(jù)庫：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; #創(chuàng)建數(shù)據(jù)庫

CHARACTER SET 'character set name' #指定字符集

COLLATE 'collate name' #指定排序方式

刪除數(shù)據(jù)庫

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

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

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

獲取命令使用幫助：

mysql> HELP KEYWORD;

例：HELP CREATE DATABASE;

看到基本用法及幫助文檔的鏈接，可以去看

URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html

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

mysql> SHOW DATABASES;

[root@centos7 ~]#cat /var/lib/mysql/db1/db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

表

表：二維關(guān)系

設(shè)計表：遵循規(guī)范

定義：字段，索引

字段：字段名，字段數(shù)據(jù)類型，修改符

約束，索引：應(yīng)該創(chuàng)建在經(jīng)常用作查詢條件的字段上

創(chuàng)建表

創(chuàng)建表：CREATE TABLE

(1) 直接創(chuàng)建

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]

[partition_options] select_statement

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

注意：

Storage Engine是指表類型，也即在表創(chuàng)建時指明其使用的存儲引擎，同一庫中不同表可以使用不同的存儲引擎

同一個庫中表建議要使用同一種存儲引擎類型

創(chuàng)建表

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修飾符, col2 type2 修飾符, ...) #[IF NOT EXISTS]字段在腳本中一般要加

字段信息

?col type1

?PRIMARY KEY(col1,...)

?INDEX(col1, ...)

?UNIQUE KEY(col1, ...)

表選項：

?ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine類型

?ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

獲取幫助：mysql> HELP CREATE TABLE;

表操作

查看所有的引擎：SHOW ENGINES

查看表：SHOW TABLES [FROM db_name]

查看表結(jié)構(gòu)：DESC [db_name.]tb_name

刪除表：DROP TABLE [IF EXISTS] tb_name

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

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

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

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

數(shù)據(jù)類型：

數(shù)據(jù)長什么樣？

數(shù)據(jù)需要多少空間來存放？

系統(tǒng)內(nèi)置數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型

MySql支持多種列類型：

數(shù)值類型

日期/時間類型

字符串(字符)類型

https://dev.mysql.com/doc/refman/5.5/en/data-types.html

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

更小的通常更好，盡量使用可正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型

簡單就好，簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期

盡量避免NULL，包含為NULL的列，對MySQL更難優(yōu)化（可填個默認值）

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

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

1、整型

tinyint(m) 1個字節(jié) 范圍(-128~127)

smallint(m) 2個字節(jié) 范圍(-32768~32767)

mediumint(m) 3個字節(jié) 范圍(-8388608~8388607)

int(m) 4個字節(jié) 范圍(-2147483648~2147483647)

bigint(m) 8個字節(jié) 范圍(+-9.22*10的18次方)

取值范圍如果加了unsigned，則最大值翻倍，如tinyint unsigned的取值范圍為(0~255)

int(m)里的m是表示SELECT查詢結(jié)果集中的顯示寬度，并不影響實際的取值范圍，規(guī)定了MySQL的一些交互工具（例如MySQL命令行客戶端）用來顯示字符的個數(shù)。對于存儲和計算來說，Int(1)和Int(20)是相同的

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

2、浮點型(float和double)，近似值

float(m,d) 單精度浮點型 8位精度(4字節(jié)) m總個數(shù)，d小數(shù)位

double(m,d) 雙精度浮點型16位精度(8字節(jié)) m總個數(shù)，d小數(shù)位

設(shè)一個字段定義為float(6,3)，如果插入一個數(shù)123.45678,實際數(shù)據(jù)庫里存的是123.457，但總個數(shù)還以實際為準，即6位

3、定點數(shù)

在數(shù)據(jù)庫中存放的是精確值,存為十進制

decimal(m,d) 參數(shù)m<65 是總個數(shù)，d<30且 d<m 是小數(shù)位

MySQL5.0和更高版本將數(shù)字打包保存到一個二進制字符串中（每4個字節(jié)存9個數(shù)字）。例如，decimal(18,9)小數(shù)點兩邊將各存儲9個數(shù)字，一共使用9個字節(jié)：小數(shù)點前的數(shù)字用4個字節(jié)，小數(shù)點后的數(shù)字用4個字節(jié)，小數(shù)點本身占1個字節(jié)

浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節(jié)存儲。double占用8個字節(jié)

因為需要額外的空間和計算開銷，所以應(yīng)該盡量只在對小數(shù)進行精確計算時才使用decimal——例如存儲財務(wù)數(shù)據(jù)。但在數(shù)據(jù)量比較大的時候，可以考慮使用bigint代替decimal

4、字符串(char,varchar,_text)

char(n) 固定長度，最多255個字符

varchar(n)可變長度，最多65535個字符

tinytext 可變長度，最多255個字符

text 可變長度，最多65535個字符

mediumtext 可變長度，最多2的24次方-1個字符

longtext 可變長度，最多2的32次方-1個字符

BINARY(M) 固定長度，可存二進制或字符，長度為0-M字節(jié)

VARBINARY(M) 可變長度，可存二進制或字符，允許長度為0-M字節(jié)

內(nèi)建類型：ENUM枚舉, SET集合

char和varchar：

?1.char(n) 若存入字符數(shù)小于n，則以空格補于其后，查詢之時再將空格去掉。所以char類型存儲的字符串末尾不能有空格，varchar不限于此。

?2.char(n) 固定長度，char(4)不管是存入幾個字符，都將占用4個字節(jié)，varchar是存入的實際字符數(shù)+1個字節(jié)（n< n>255)，所以varchar(4),存入3個字符將占用4個字節(jié)。

?3.char類型的字符串檢索速度要比varchar類型的快

varchar和text：

?1.varchar可指定n，text不能指定，內(nèi)部存儲varchar是存入的實際字符數(shù)+1個字節(jié)（n< n>255)，text是實際字符數(shù)+2個字節(jié)。

?2.text類型不能有默認值

?3.varchar可直接創(chuàng)建索引，text創(chuàng)建索引要指定前多少個字符。varchar查詢速度快于text

5.二進制數(shù)據(jù)：BLOB

?BLOB和text存儲方式不同，TEXT以文本方式存儲，英文存儲區(qū)分大小寫，而Blob是以二進制方式存儲，不分大小寫

?BLOB存儲的數(shù)據(jù)只能整體讀出

?TEXT可以指定字符集，BLOB不用指定字符集

6.日期時間類型

?date 日期 '2008-12-2'

?time 時間 '12:25:36'

?datetime 日期時間 '2008-12-2 22:06:44'

?timestamp 自動存儲記錄修改時間

?YEAR(2), YEAR(4)：年份

timestamp字段里的時間數(shù)據(jù)會隨其他字段修改的時候自動刷新，這個數(shù)據(jù)類型的字段可以存放這條記錄最后被修改的時間

修飾符

所有類型：

?NULL 數(shù)據(jù)列可包含NULL值

?NOT NULL 數(shù)據(jù)列不允許包含NULL值

?DEFAULT 默認值

?PRIMARY KEY 主鍵

?UNIQUE KEY 唯一鍵

?CHARACTER SET name 指定一個字符集

數(shù)值型

?AUTO_INCREMENT 自動遞增，適用于整數(shù)類型

?UNSIGNED 無符號

示例

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

DESC students;

CREATE TABLE students2 (id int UNSIGNED NOT NULL ,
name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

DROP TABLE [IF EXISTS] 'tbl_name'; [CASCADE](級聯(lián)刪除)

ALTER TABLE 'tbl_name'

字段：

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

刪除字段：drop

修改字段：

alter（默認值）, change（字段名）, modify（字段屬性）

索引:

添加索引：add index

刪除索引: drop index

表選項

修改:

查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

查看幫助：Help ALTER TABLE

修改表示例

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
Help ALTER TABLE 查看幫助

修改表示例

ALTER TABLE students ADD gender ENUM('m','f')（enum單選/枚舉，set多選）
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students ADD INDEX(age);
DESC students;
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;

DML語句

DML:INSERT, DELETE, UPDATE, SELECT

INSERT：

一次插入一行或多行數(shù)據(jù)

語法

INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE 如果重復(fù)更新之

col_name=expr

[, col_name=expr] ... ]

簡化寫法：

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

DML語句

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] ... ]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] ... ]

DML語句

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

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

限制條件：

WHERE

LIMIT

Mysql 選項：--safe-updates| --i-am-a-dummy|-U

DML語句

DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

可先排序再指定刪除的行數(shù)

注意：一定要有限制條件，否則將清空表中的所有數(shù)據(jù)

限制條件：

WHERE

LIMIT

TRUNCATE TABLE tbl_name; 清空表

DQL語句

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr ...]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[FOR UPDATE | LOCK IN SHARE MODE]

SELECT

字段顯示可以使用別名：

col1 AS alias1, col2 AS alias2, ...（AS可不寫）

WHERE子句：指明過濾條件以實現(xiàn)“選擇”的功能：

過濾條件：布爾型表達式

算術(shù)操作符：+, -, *, /, %

比較操作符：=, !=, <>, <=, >, >=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2, ...)

IS NULL

IS NOT NULL

SELECT

DISTINCT 去除重復(fù)列

SELECT DISTINCT gender FROM students;

LIKE:

%: 任意長度的任意字符

_：任意單個字符

RLIKE：正則表達式，索引失效，不建議使用

REGEXP：匹配字符串可用正則表達式書寫模式，同上

邏輯操作符：

NOT

AND

OR

XOR

SELECT

GROUP：根據(jù)指定的條件把查詢結(jié)果進行“分組”以用于做“聚合”運算

avg(), max(), min(), count(), sum()

建議：一旦用分組，前邊select只需要寫用以分組的字段，和統(tǒng)計的函數(shù)，寫別的字段沒有意義

HAVING: 對分組聚合運算后的結(jié)果指定過濾條件（在分組前做過濾用where）

ORDER BY: 根據(jù)指定的字段對查詢結(jié)果進行排序

升序：ASC

降序：DESC

示例：

MariaDB [db1]> select * from students order by score; 順序，NULL在前

MariaDB [db1]> select * from students order by -score desc; 順序，但NULL在后

LIMIT [[offset,]row_count]：對查詢的結(jié)果進行輸出行數(shù)數(shù)量限制

對查詢結(jié)果中的數(shù)據(jù)請求施加“鎖”

FOR UPDATE: 寫鎖，獨占或排它鎖，只有一個讀和寫

LOCK IN SHARE MODE: 讀鎖，共享鎖，同時多個讀（但只能有一個寫）

示例

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students

練習(xí)

練習(xí)數(shù)據(jù)庫下載鏈接：https://pan.baidu.com/s/11Qk9yI9lx6oZVCGXhvQ6PQ

下載其中的hellodb_innodb.sql，輸命令mysql < hellodb_innodb.sql生成一個hellodb數(shù)據(jù)庫：

[root@centos7 ~]#mysql  #連接mariadb
MariaDB [(none)]> use hellodb  #使用hellodb數(shù)據(jù)庫
MariaDB [hellodb]> show tables;  #共有7張表

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

+-------------------+

M