SQL基礎(chǔ)

發(fā)布時間：2020-05-29 07:57:48 來源：網(wǎng)絡(luò) 閱讀：163 作者：何小帥欄目：系統(tǒng)運維

1 SQL語句分類

名稱	作用	語法關(guān)鍵字
DDL	數(shù)據(jù)定義語言	CREATE,DROP,ALTER
DML	數(shù)據(jù)操縱語言	INSERT,DELETE,UPDATE
DQL	數(shù)據(jù)查詢語言	SELECT
DCL	數(shù)據(jù)控制語言	GRANT,REVOKE,COMMIT,ROLLBACK

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

數(shù)值類型
日期/時間類型
字符串（字符）類型

2.1 數(shù)值類型

2.1.1 整數(shù)型

類型	儲存空間（字節(jié)）	范圍
tinyint(m)	1	-128~127
smallint(m)	2	-32768~32767
mediumint(m)	3	-8388608~8388607
int(m)	4	-2147483648~2147483647
bigint(m)	8	-2^63~2^63-1

上述數(shù)據(jù)類型，如果加修飾符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.1.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ù)還以實際為準(zhǔn)，即6位

2.1.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ù)點前的9個數(shù)字用4個字節(jié)，小數(shù)點后的9個數(shù)字用4個字節(jié)，小數(shù)點本身占1個字節(jié)
浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節(jié)存儲。double占用8個字節(jié)
因為需要額外的空間和計算開銷，所以應(yīng)該盡量只在對小數(shù)進行精確計算時才使用decimal，例如存儲財務(wù)數(shù)據(jù)。但在數(shù)據(jù)量比較大的時候，可以考慮使用bigint代替decimal

2.2 日期/時間類型

數(shù)據(jù)類型	值
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR(2),YEAR(4)	00,0000

2.3 字符串類型

數(shù)據(jù)類型	值
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集合
ENUM是一個字符串對象，其值是從允許值的列表中選擇的，這些值在表創(chuàng)建時在列規(guī)范中明確枚舉
SET是可以具有零個或多個值的字符串對象，每個值都必須從創(chuàng)建表時指定的允許值列表中選擇。 SET由多個set成員組成的列值用用逗號（,）分隔的成員指定。這樣的結(jié)果是 SET成員值本身不應(yīng)包含逗號。

2.3.1 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類型的快

2.3.2 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
數(shù)據(jù)類型

2.3.3 BLOB和TEXT比較

1.BLOB和test存儲方式不同，text以文本方式存儲，英文存儲區(qū)分大小寫，而blob以二進制方式存儲，不區(qū)分大小寫
2.BLOB存儲的數(shù)據(jù)只能整體讀出
3.text可以指定字符集，blob不用指定字符集

2.4 修飾符

2.4.1 適合所有類型的修飾符：

名稱	作用
NULL	數(shù)據(jù)列可包含NULL值
NOT NULL	數(shù)據(jù)列不允許包含NULL值
DEFAULT	默認值
PRIMARY KEY	主鍵
UNIQUE KEY	唯一鍵
CHARACTER SET name	指定一個字符集

2.4.2 適用數(shù)值型的修飾符

名稱	作用
AUTO_INCREMENT	自動遞增，適用于整數(shù)類型，不支持列的負值。
UNSIGNED	無符號，可用于僅允許一列中使用非負數(shù)，或者在您需要該列的較大的較高數(shù)字范圍時使用

3 DDL語句

3.1 創(chuàng)建表

HELP CREATE TALBE;

創(chuàng)建表的方法

3.1.1 直接創(chuàng)建

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修飾符, col2 type2 修飾符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表選項：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

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

范例：

mysql> CREATE TABLE student (id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)

mysql> DESC student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE employee (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED,PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.02 sec)

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

語法：

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

【例】通過查詢現(xiàn)存表student的數(shù)據(jù)來創(chuàng)建新表new_student：

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> create table new_student select * from student;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from new_student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> desc new_student;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(10) unsigned    | NO   |     | 0       |       |
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注：這種創(chuàng)建表的方法雖然把現(xiàn)存表的查詢結(jié)果插入到了新表中，但是沒有把現(xiàn)存表的表結(jié)構(gòu)的修飾符插入到新表中。

3.1.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) }

【例】通過查詢現(xiàn)存表student的數(shù)據(jù)來創(chuàng)建新表new2_student：

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> create table new2_student like student;
Query OK, 0 rows affected (0.30 sec)

mysql> desc new2_student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from new2_student;
Empty set (0.00 sec)

注：從上面的結(jié)果中可以發(fā)現(xiàn)，此種創(chuàng)建新表的方法，只是復(fù)制了現(xiàn)在表的表結(jié)構(gòu)，而不復(fù)制數(shù)據(jù)。

3.2 表查看

3.2.1 查看支持的engine類型

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

注：默認是innodb存儲引擎

3.2.2 查看當(dāng)前數(shù)據(jù)庫有哪些表

SHOW TABLES [FROM db_name]

3.2.3 查看表結(jié)構(gòu)

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

3.2.4 查看表創(chuàng)建命令

SHOW CREATE TABLE tbl_name;

3.2.5 查看表狀態(tài)

SHOW TABLE STATUS LIKE 'tbl_name';

3.2.6 查看庫中所有表狀態(tài)

show table status from db_name;

3.3 修改和刪除表

3.3.1 刪除表

DROP TABLE [IF EXISTS] 'tbl_name';

3.3.2 修改表

語法：
ALTER TABLE tbl_name

獲取幫助：
help alter table

# 在表中添加字段：add
語法：
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

# 刪除表中字段：drop
語法：
DROP [COLUMN] col_name

# 修改表中字段：
change（字段名）
語法：
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

modify（字段屬性）
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

范例：

3.3.2.1 修改student表名為S1

mysql> alter table student rename s1;
Query OK, 0 rows affected (0.01 sec)

3.3.2.2 在s1表中的name字段后面添加phone字段，并且設(shè)置數(shù)據(jù)類型為可變長的字符串。

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table s1 add phone varchar(11) after name;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| phone | varchar(11)         | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.3 修改s1表中的phone字段的數(shù)據(jù)類型為int

mysql> alter table s1 modify phone int;
Query OK, 2 rows affected (7.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| phone | int(11)             | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.4 修改s1表中的phone字段的名稱為mobile，并且將其數(shù)據(jù)類型修改為定長字符串char

mysql> alter table s1 change column phone mobile char(11);
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| mobile | char(11)            | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.5 刪除s1表中的mobile字段

mysql> alter table s1 drop column mobile;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

修改s1表的字符集類型為utf8

mysql> alter table s1 character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改s1表中的name字段名為new_name，并且為其設(shè)置字符集為utf8

mysql> alter table s1 change name new_name varchar(20) character set utf8;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

4 DML語句

INSERT,DELETE,UPDATE

4.1 INSERT語句

功能：一次插入一行或多行數(shù)據(jù)  
獲取幫助：help insert
簡化語法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val2,...)

4.2 UPDATE語句

獲取幫助：help update
語法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

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

可以利用以下選項避免此錯誤

mysql -U | --safe-updates| --i-am-a-dummy

范例

4.2.1 沒加限制條件直接更新s1表的age為25，將導(dǎo)致所有行的age段都為25

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   22 | NULL  |
|  2 | xiaohong |   20 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

mysql> update s1 set age=25 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   25 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

4.2.2 加where限制條件，只更新id為1的age值為18，以避免上面所發(fā)生的錯誤

mysql> update s1 set age=18 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   18 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

4.2.3 登錄MySQL服務(wù)器的時候加上-U選項（也可以是其它兩個），然后更新age字段的值為30，如果不加限制條件將直接報錯，加上此選項從而避免了更新所有行的指定字段值的錯誤

[root@CentOS7-01 ~]#mysql -U -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mytest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   18 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

mysql> update s1 set age=30;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
mysql> update s1 set age=30 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4.3 DELETE語句

獲取幫助：help delete;

語法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

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

如果只想清空表，但保留表結(jié)構(gòu)，可以使用下面語句

truncate table tbl_name;

5 DQL語句

5.1 單表查詢

獲取幫助：help select
語法：
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [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}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

說明：

1)字段顯示可以使用別名
字段1 AS 字段1別名，字段2 AS 字段2別名

2)WHERE字句：指明過濾條件以實現(xiàn)“選擇”的功能。
過濾條件：布爾型表達式
算術(shù)操作符：+,-,*,/,%
比較操作符：=,<=>(相等或都為空),<>,!=(非標(biāo)準(zhǔn)SQL),>,>=,<,<=
BETWEEN min_num AND max_num # 在兩個值之間
IN(element1,element2,...) # ()中的任意單個值，相當(dāng)于列表
IS NULL #為空
IS NOT NULL #不為空
DISTINCT #去除重復(fù)列，范例：SELECT DISTINCT gender FROM students;
LIKE:
% 任意長度的任意字符
_ 任意單個字符
RLIKE：正則表達式，索引失效，不建議使用
REGEXP：匹配字符串可用正則表達式書寫模式，同上
邏輯操作符：NOT，AND，OR，XOR
3)GROUP：根據(jù)指定的條件把查詢結(jié)果進行“分組”以用于做“聚合”運算，先過濾再分組
常見聚合函數(shù)：avg(), max(), min(), count(), sum()
HAVING: 對分組聚合運算后的結(jié)果指定過濾條件，先分組再過濾
4)ORDER BY: 根據(jù)指定的字段對查詢結(jié)果進行排序
升序：ASC
降序：DESC
5)LIMIT [[offset,]row_count]：對查詢的結(jié)果進行輸出行數(shù)數(shù)量限制
6)對查詢結(jié)果中的數(shù)據(jù)請求施加“鎖”
FOR UPDATE: 寫鎖，獨占或排它鎖，只有一個讀和寫操作
LOCK IN SHARE MODE: 讀鎖，共享鎖，同時多個讀操作

5.1.1 練習(xí)

使用students表進行練習(xí)，表內(nèi)容如下

+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |    NULL |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |    NULL |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  45 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
+-------+---------------+-----+--------+---------+-----------+

1.查詢年齡大于25歲，且為男性的同學(xué)的名字和年齡

mysql> select name,age,gender from students where age > 25 and gender='m';
+-----------+-----+--------+
| name      | age | gender |
+-----------+-----+--------+
| Xie Yanke |  53 | M      |
| Ding Dian |  32 | M      |
| Yu Yutong |  26 | M      |
| Shi Qing  |  46 | M      |
+-----------+-----+--------+
4 rows in set (0.00 sec)

2.以ClassID為分組依據(jù)，顯示每組的平均年齡

mysql> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  36.0000 |
|       1 |  22.0000 |
|       2 |  22.0000 |
|       3 |  35.5000 |
|       4 |  32.0000 |
|       5 |  46.0000 |
|       6 |  20.0000 |
|       7 |  17.0000 |
+---------+----------+
8 rows in set (0.00 sec)

3.顯示第2題中平均年齡大于30的分組及平均年齡

mysql> select classid,avg(age) from students where age>30 group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  53.0000 |
|       3 |  45.0000 |
|       4 |  32.0000 |
|       5 |  46.0000 |
+---------+----------+
4 rows in set (0.00 sec)

4.顯示以L開頭的名字的同學(xué)的信息

mysql> select * from students where name like 'l%';
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     8 | Lin Daiyu |  17 | F      |       7 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

5.顯示TeacherID非空的同學(xué)的相關(guān)信息

mysql> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |    NULL |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

6.以年齡排序后，顯示年齡最大的前3位同學(xué)的信息

mysql> select * from students order by age desc limit 3;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |    NULL |        16 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    10 | Yue Lingshan |  45 | F      |       3 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

7.查詢年齡大于等于20歲，小于等于25歲的同學(xué)的信息

mysql> select * from students where age between 20 and 25;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

8.以ClassID分組，顯示每班的同學(xué)的人數(shù)

mysql> select classid as 班級id,count(stuid) as 班級人數(shù) from students group by classid;
+----------+--------------+
| 班級id   | 班級人數(shù)     |
+----------+--------------+
|     NULL |            2 |
|        1 |            1 |
|        2 |            1 |
|        3 |            2 |
|        4 |            1 |
|        5 |            1 |
|        6 |            1 |
|        7 |            1 |
+----------+--------------+
8 rows in set (0.00 sec)

9.以Gender分組，顯示其年齡之和

mysql> select gender 性別,sum(age) 年齡之和 from students group by gender;
+--------+--------------+
| 性別   | 年齡之和     |
+--------+--------------+
| F      |          101 |
| M      |          201 |
+--------+--------------+
2 rows in set (0.00 sec)

10.以ClassID分組，顯示其平均年齡大于25的班級

mysql> select classid,avg(age) 平均年齡 from students group by classid having 平均年齡 > 25;
+---------+--------------+
| classid | 平均年齡     |
+---------+--------------+
|    NULL |      36.0000 |
|       3 |      35.5000 |
|       4 |      32.0000 |
|       5 |      46.0000 |
+---------+--------------+
4 rows in set (0.00 sec)

11.以Gender分組，顯示各組中年齡大于25的學(xué)員的年齡之和

mysql> select gender,sum(age) from students where age > 25 group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| F      |       45 |
| M      |      157 |
+--------+----------+
2 rows in set (0.00 sec

12.顯示其年齡大于平均年齡的同學(xué)的名字

mysql> select name,age from students where age > (select avg(age) from students);
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Yue Lingshan |  45 |
+--------------+-----+
4 rows in set (0.00 sec)

13.對classid正序排序，NULL記錄排在最后

mysql> select * from students order by -classid desc;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|    10 | Yue Lingshan |  45 | F      |       3 |      NULL |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|     3 | Xie Yanke    |  53 | M      |    NULL |        16 |
|     7 | Xi Ren       |  19 | F      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)

5.1.2 SQL注入

使用user表進行練習(xí)，表內(nèi)容如下

+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  2 | root  | M      | 88888    |
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+

【例1】

mysql> select * from user where name='admin' and password='' or 1=1;
+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  2 | root  | M      | 88888    |
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+
2 rows in set (0.01 sec)

【例2】

mysql> select * from user where name='admin'; -- and password='abc123';
+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+
1 row in set (0.00 sec)

向AI問一下細節(jié)