溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
MySQL存儲引擎的配置
SQL數據導入/導出
操作表記錄
查詢及匹配條件1 MySQL存儲引擎的配置
1.1 問題
本案例要求MySQL數據存儲引擎的使用，完成以下任務操作：
可用的存儲引擎類型
查看默認存儲類型
更改表的存儲引擎1.2 步驟
實現此案例需要按照如下步驟進行。
步驟一：查看存儲引擎信息
登入MySQL服務器，查看當前支持哪些存儲引擎。
使用mysql命令連接，以root用戶登入：
[root@dbsvr1 ~]# mysql -u root –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> 執行SHOW ENGINES\G指令可列表查看，MySQL 5.6可用的存儲引擎有9種（除最后的FEDERATED以外，其他8種都支持），其中默認采用的存儲引擎為InnoDB：
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT //此存儲引擎為默認
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO //此引擎不被支持
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.01 sec)或者直接查看系統變量default_storage_engine 的值，也可確認默認采用的存儲引擎是InnoDB：
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)步驟二：修改默認存儲引擎
在 mysql> 環境中，可以直接通過SET指令更改默認的存儲引擎（只在本次連接會話過程中有效，退出重進即失效） 。比如臨時修改為MyISAM，可執行下列操作：
mysql> SET default_storage_engine=MyISAM; //改用MyISAM引擎
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'default_storage_engine'; //確認結果
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)若希望直接修改MySQL服務程序所采用的默認存儲引擎，應將相關設置寫入配置文件/etc/my.cnf，并重啟服務后生效。比如：
[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default_storage_engine=MEMORY //改用MEMORY引擎
[root@dbsvr1 ~]# systemctl restart mysqld.service //重啟服務重新登入 mysql> 確認修改結果：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MEMORY | //默認引擎已修改
+------------------------+--------+
1 row in set (0.00 sec)
mysql> exit
Bye為了避免后續實驗障礙，測試完后記得恢復原狀——移除默認引擎設置，或者將其修改為InnoDB即可：
[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default_storage_engine=InnoDB
[root@dbsvr1 ~]# systemctl restart mysqld.service確認恢復結果（選項 -e 可調用指定的SQL操作后返回Shell命令行）：
[root@dbsvr1 ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
Enter password:
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+2 SQL數據導入/導出
2.1 問題
使用SQL語句完成下列導出、導入操作：
將/etc/passwd文件導入userdb庫userlist表并給每條記錄加編號
將userdb庫userlist表中UID小于100的前10條記錄導出，存為/dbak/ulist.txt文件2.2 步驟
實現此案例需要按照如下步驟進行。
步驟一：將/etc/passwd文件導入MySQL數據庫
導入后的表結構取決于/etc/passwd配置文件。若一時記不住各字段的含義，也可以查看passwd配置文件的man手冊頁，找到格式描述相關的說明，比如：
[root@dbsvr1 ~]# man 5 passwd
.. ..
Each line of the file describes a single user, and contains seven colon-sep‐
arated fields:
name:password:UID:GID:GECOS:directory:shell //各字段的順序、大致用途
The field are as follows: //以下詳細解釋各字段的作用
name This is the user's login name. It should not contain capital
letters.
password This is either the encrypted user password, an asterisk (*), or
the letter 'x'. (See pwconv(8) for an explanation of 'x'.)
UID The privileged root login account (superuser) has the user ID 0.
GID This is the numeric primary group ID for this user. (Additional
groups for the user are defined in the system group file; see
group(5)).
GECOS stands for "General Electric Comprehensive Operating Sys‐
tem", which was renamed to GCOS when GE's large systems division
was sold to Honeywell. Dennis Ritchie has reported: "Sometimes
we sent printer output or batch jobs to the GCOS machine. The
gcos field in the password file was a place to stash the infor‐
mation for the $IDENTcard. Not elegant."
directory This is the user's home directory: the initial directory where
the user is placed after logging in. The value in this field is
used to set the HOME environment variable.
shell This is the program to run at login (if empty, use /bin/sh). If
set to a nonexistent executable, the user will be unable to
login through login(1). The value in this field is used to set
the SHELL environment variable.
.. ..1）新建userdb庫、userlist表
以數據庫用戶root登入MySQL服務：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> 新建userdb庫，切換到userdb庫：
mysql> CREATE DATABASE userdb;
Query OK, 1 row affected (0.00 sec)
mysql> USE userdb;
Database changed新建userlist表，字段設置及相關操作參考如下：
mysql> CREATE TABLE userlist(
-> username varchar(24) NOT NULL,
-> password varchar(48) DEFAULT 'x',
-> uid int(5) NOT NULL,
-> gid int(5) NOT NULL,
-> fullname varchar(48),
-> homedir varchar(64) NOT NULL,
-> shell varchar(24) NOT NULL
-> );
Query OK, 0 rows affected (0.70 sec)確認userlist表的結構：
mysql> DESC userlist;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(24) | NO | | NULL | |
| password | varchar(48) | YES | | x | |
| uid | int(5) | NO | | NULL | |
| gid | int(5) | NO | | NULL | |
| fullname | varchar(48) | YES | | NULL | |
| homedir | varchar(64) | NO | | NULL | |
| shell | varchar(24) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)2）如果直接導入會報錯。在MySQL 5.7.6版本之后，導入文件只能在secure_file_priv指定的文件夾下。執行show variables like '%secure%'命令顯示文件目錄：
mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE userlist FIELDS TERMINATED BY ':';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)3）執行導入操作
將/etc/passwd文件復制到/var/lib/mysql-files/目錄下，
讀取/var/lib/mysql-files/passwd文件內容，以“:”為分隔，導入到userlist表中：
[root@dbsvr1 ~]#cp /etc/passwd /var/lib/mysql-files/
mysql> LOAD DATA INFILE '/var/lib/mysql-files/passwd'
-> INTO TABLE userlist
-> FIELDS TERMINATED BY ':';
Query OK, 39 rows affected (0.11 sec)
Records: 39 Deleted: 0 Skipped: 0 Warnings: 0上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因為這是默認的情況（每行一條原始記錄），除非需要以其他字符分割行，才需要用到這個。比如，以下操作指定了行分隔為'\n'，將/var/lib/mysql-files/passwd文件的內容導入另一個表userlist2，最終userlist2表的內容與userlsit的內容是一樣的：
代碼
4）確認導入結果
分別統計userlist、userlist2表內的記錄個數：
mysql> SELECT COUNT(*) FROM userlist;
+----------+
| COUNT(*) |
+----------+
| 39 | //userlist表有39條記錄
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM userlist2;
+----------+
| COUNT(*) |
+----------+
| 39 | //userlist表也有39條記錄
+----------+
1 row in set (0.00 sec)查看userlist表的前10條記錄，列出用戶名、UID、GID、宿主目錄、登錄Shell：
mysql> SELECT username,uid,gid,homedir,shell
-> FROM userlist LIMIT 10;
+----------+-----+-----+-----------------+----------------+
| username | uid | gid | homedir | shell |
+----------+-----+-----+-----------------+----------------+
| root | 0 | 0 | /root | /bin/bash |
| bin | 1 | 1 | /bin | /sbin/nologin |
| daemon | 2 | 2 | /sbin | /sbin/nologin |
| adm | 3 | 4 | /var/adm | /sbin/nologin |
| lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | 5 | 0 | /sbin | /bin/sync |
| shutdown | 6 | 0 | /sbin | /sbin/shutdown |
| halt | 7 | 0 | /sbin | /sbin/halt |
| mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | 11 | 0 | /root | /sbin/nologin |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)查看userlist2表的前10條記錄，同樣列出用戶名、UID、GID、宿主目錄、登錄Shell：
mysql> SELECT username,uid,gid,homedir,shell
-> FROM userlist2 LIMIT 10;
+----------+-----+-----+-----------------+----------------+
| username | uid | gid | homedir | shell |
+----------+-----+-----+-----------------+----------------+
| root | 0 | 0 | /root | /bin/bash |
| bin | 1 | 1 | /bin | /sbin/nologin |
| daemon | 2 | 2 | /sbin | /sbin/nologin |
| adm | 3 | 4 | /var/adm | /sbin/nologin |
| lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | 5 | 0 | /sbin | /bin/sync |
| shutdown | 6 | 0 | /sbin | /sbin/shutdown |
| halt | 7 | 0 | /sbin | /sbin/halt |
| mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | 11 | 0 | /root | /sbin/nologin |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)步驟二：為userlist表中的每條記錄添加自動編號
這個只要修改userlist表結構，添加一個自增字段即可。
比如，添加一個名為sn的序號列，作為userlist表的第一個字段：
1）添加自增主鍵字段sn
mysql> ALTER TABLE userlist
-> ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 02）驗證自動編號結果
查看userlist表的前10條記錄，列出序號、用戶名、UID、GID、宿主目錄：
mysql> SELECT sn,username,uid,gid,homedir
-> FROM userlist LIMIT 10;
+----+----------+-----+-----+-----------------+
| sn | username | uid | gid | homedir |
+----+----------+-----+-----+-----------------+
| 1 | root | 0 | 0 | /root |
| 2 | bin | 1 | 1 | /bin |
| 3 | daemon | 2 | 2 | /sbin |
| 4 | adm | 3 | 4 | /var/adm |
| 5 | lp | 4 | 7 | /var/spool/lpd |
| 6 | sync | 5 | 0 | /sbin |
| 7 | shutdown | 6 | 0 | /sbin |
| 8 | halt | 7 | 0 | /sbin |
| 9 | mail | 8 | 12 | /var/spool/mail |
| 10 | operator | 11 | 0 | /root |
+----+----------+-----+-----+-----------------+
10 rows in set (0.00 sec)步驟三：從MySQL數據庫中導出查詢結果
以將userdb庫userlist表中UID小于100的前10條記錄導出為/var/lib/mysql-files/ulist.txt文件為例。
1）確認存放導出數據的文件夾
[root@dbsvr1 ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 19 4月 7 11:15 /var/lib/mysql-files/2）導出userlsit表中UID小于100的前10條記錄
如果以默認的'\n' 為行分隔，導出操作同樣可不指定LINES TERMINATED BY：
mysql> SELECT * FROM userdb.userlist WHERE uid<100
-> INTO OUTFILE '/var/lib/mysql-files/ulist.txt'
-> FIELDS TERMINATED BY ':';
Query OK, 24 rows affected (0.00 sec)3）確認導出結果
返回到Shell命令行，查看/var/lib/mysql-files/ulist.txt文件的行數：
[root@dbsvr1 ~]# wc -l /var/lib/mysql-files/ulist.txt
24 /var/lib/mysql-files/ulist.txt查看/var/lib/mysql-files/ulist.txt文件的最后10行內容：
[root@dbsvr1 ~]# tail /var/lib/mysql-files/ulist.txt
19:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
24:rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin
25:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
28:radvd:x:75:75:radvd user:/:/sbin/nologin
29:ntp:x:38:38::/etc/ntp:/sbin/nologin
33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
35:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
36:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
37:tcpdump:x:72:72::/:/sbin/nologin
39:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false3 操作表記錄
3.1 問題
練習表記錄的操作
表記錄的插入
表記錄的更新
表記錄的查詢
表記錄的刪除3.2 步驟
實現此案例需要按照如下步驟進行。
步驟一：創建stu_info表，并確保stu_info表記錄為空。
在userdb庫中創建stu_info表：
[root@dbsvr1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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 userdb;
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> CREATE TABLE stu_info(
-> name varchar(12) NOT NULL,
-> gender enum('boy','girl') DEFAULT 'boy',
-> age int(3) NOT NULL
-> );
Query OK, 0 rows affected (0.23 sec)刪除stu_info表的所有記錄：
mysql> DELETE FROM stu_info;
Query OK, 0 rows affected (0.00 sec) //stu_info表剛建立 刪除零條記錄確認刪除結果：
mysql> SELECT * FROM stu_info;
Empty set (0.00 sec)步驟二：練習表記錄的操作
1）插入記錄時，指定記錄的每一個字段的值
這種情況下，不需要明確指出字段，但每條記錄的值的順序、類型都必須與表格結構向一致，否則可能無法正確插入記錄。
比如，以下操作將向stu_info表插入3條表記錄：
mysql> INSERT stu_info VALUES
-> ('Jim','girl',24),
-> ('Tom','boy',21),
-> ('Lily','girl',20);
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0完成插入后確認表記錄：
mysql> SELECT * FROM stu_info;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
+------+--------+-----+
3 rows in set (0.00 sec)2）插入記錄時，只指定記錄的部分字段的值
這種情況下，必須指出各項值所對應的字段；而且，未賦值的字段應設置有默認值或者有自增填充屬性或者允許為空，否則插入操作將會失敗。
比如，向stu_info表插入Jerry的年齡信息，性別為默認的“boy”，自動編號，相關操作如下：
mysql> INSERT INTO stu_info(name,age)
-> VALUES('Jerry',27);
Query OK, 1 row affected (0.04 sec)類似的，再插入用戶Mike的年齡信息：
mysql> INSERT INTO stu_info(name,age)
-> VALUES('Mike',21);
Query OK, 1 row affected (0.05 sec)確認目前stu_info表的所有記錄：
mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
| Jerry | boy | 27 |
| Mike | boy | 21 |
+-------+--------+-----+
5 rows in set (0.00 sec)3）更新表記錄時，若未限制條件，則適用于所有記錄
將stu_info表中所有記錄的age設置為10：
mysql> UPDATE stu_info SET age=10;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5 Changed: 5 Warnings: 0確認更新結果：
mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 10 |
| Tom | boy | 10 |
| Lily | girl | 10 |
| Jerry | boy | 10 |
| Mike | boy | 10 |
+-------+--------+-----+
5 rows in set (0.00 sec)4）更新表記錄時，可以限制條件，只對符合條件的記錄有效
將stu_info表中所有性別為“boy”的記錄的age設置為20：
mysql> UPDATE stu_info SET age=20
-> WHERE gender='boy';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0確認更新結果：
mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 10 |
| Tom | boy | 20 |
| Lily | girl | 10 |
| Jerry | boy | 20 |
| Mike | boy | 20 |
+-------+--------+-----+
5 rows in set (0.00 sec)5）刪除表記錄時，可以限制條件，只刪除符合條件的記錄
刪除stu_info表中年齡小于18的記錄：
mysql> DELETE FROM stu_info WHERE age < 18;
Query OK, 2 rows affected (0.03 sec)確認刪除結果：
mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Tom | boy | 20 |
| Jerry | boy | 20 |
| Mike | boy | 20 |
+-------+--------+-----+
3 rows in set (0.00 sec)6）刪除表記錄時，如果未限制條件，則會刪除所有的表記錄
刪除stu_info表的所有記錄：
mysql> DELETE FROM stu_info;
Query OK, 3 rows affected (0.00 sec)確認刪除結果：
mysql> SELECT * FROM stu_info;
Empty set (0.00 sec)4 查詢及匹配條件
4.1 問題
練習常見的SQL查詢及條件設置
創建stu_info表，并插入數據
練習常見SQL查詢及條件設置4.2 步驟
實現此案例需要按照如下步驟進行。
步驟一：根據任務要求建立員工檔案表stu_info（如上個實驗已創建，可將上個實驗stu_info表中記錄清除后繼續使用）
1）在userdb庫中創建stu_info表
以root用戶登入MySQL服務器：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>打開test庫：
mysql> USE userdb;
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創建stu_info表，包括name、gender、age三個字段：
mysql> CREATE TABLE stu_info(
-> name varchar(12) NOT NULL,
-> gender enum('boy','girl') DEFAULT 'boy',
-> age int(3) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)確認表結構：
mysql> DESC stu_info;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| name | varchar(12) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
| age | int(3) | NO | | NULL | |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)2）準備測試表格
向建立的stu_info表插入幾條測試記錄
mysql> INSERT INTO stu_info VALUES
-> ('Jim','girl',24),
-> ('Tom','boy',21),
-> ('Lily','girl',20),
-> ('Jerry','boy',27),
-> ('Mike','boy',21)
-> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0確認stu_info表的所有記錄內容：
mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
| Jerry | boy | 27 |
| Mike | boy | 21 |
+-------+--------+-----+
5 rows in set (0.00 sec)步驟二：練習常見SQL查詢及條件設置
1）常用的表記錄統計函數
查詢stu_info表一共有多少條記錄（本例中為5條）：
mysql> SELECT count(*) FROM stu_info;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)計算stu_info表中各學員的平均年齡、最大年齡、最小年齡：
mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
| 22.6000 | 27 | 20 |
+----------+----------+----------+
1 row in set (0.00 sec)計算stu_info表中男學員的個數：
mysql> SELECT count(gender) FROM stu_info WHERE gender='boy';
+---------------+
| count(gender) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)2）字段值的數值比較
列出stu_info表中年齡為21歲的學員記錄：
mysql> SELECT * FROM stu_info WHERE age=21;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Tom | boy | 21 |
| Mike | boy | 21 |
+------+--------+-----+
2 rows in set (0.00 sec)列出stu_info表中年齡超過21歲的學員記錄：
mysql> SELECT * FROM stu_info WHERE age>21;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
2 rows in set (0.00 sec)列出stu_info表中年齡大于或等于21歲的學員記錄：
mysql> SELECT * FROM stu_info WHERE age>=21;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Jerry | boy | 27 |
| Mike | boy | 21 |
+-------+--------+-----+
4 rows in set (0.00 sec)列出stu_info表中年齡在20歲和24歲之間的學員記錄：
mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
| Mike | boy | 21 |
+------+--------+-----+
4 rows in set (0.00 sec)3）多個條件的組合
列出stu_info表中年齡小于23歲的女學員記錄：
mysql> SELECT * FROM stu_info WHERE age < 23 AND gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Lily | girl | 20 |
+------+--------+-----+
1 row in set (0.00 sec)列出stu_info表中年齡小于23歲的學員，或者女學員的記錄：
mysql> SELECT * FROM stu_info WHERE age < 23 OR gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
| Mike | boy | 21 |
+------+--------+-----+
4 rows in set (0.00 sec)如果某個記錄的姓名屬于指定范圍內的一個，則將其列出：
mysql> SELECT * FROM stu_info WHERE name IN
-> ('Jim','Tom','Mickey','Minnie');
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
+------+--------+-----+
2 rows in set (0.00 sec)4）使用SELECT做數學計算
計算1234與5678的和：
mysql> SELECT 1234+5678;
+-----------+
| 1234+5678 |
+-----------+
| 6912 |
+-----------+
1 row in set (0.00 sec)計算1234與5678的乘積：
mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
| 7006652 |
+-----------+
1 row in set (0.00 sec)計算1.23456789除以3的結果：
mysql> SELECT 1.23456789/3;
+----------------+
| 1.23456789/3 |
+----------------+
| 0.411522630000 |
+----------------+
1 row in set (0.00 sec)輸出stu_info表各學員的姓名、15年后的年齡：
mysql> SELECT name,age+15 FROM stu_info;
+-------+--------+
| name | age+15 |
+-------+--------+
| Jim | 39 |
| Tom | 36 |
| Lily | 35 |
| Jerry | 42 |
| Mike | 36 |
+-------+--------+
5 rows in set (0.00 sec)5）使用模糊查詢，LIKE引領
以下劃線 _ 匹配單個字符，% 可匹配任意多個字符。
列出stu_info表中姓名以“J”開頭的學員記錄：
mysql> SELECT * FROM stu_info WHERE name LIKE 'J%';
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
2 rows in set (0.00 sec)列出stu_info表中姓名以“J”開頭且只有3個字母的學員記錄：
mysql> SELECT * FROM stu_info WHERE name LIKE 'J__';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
+------+--------+-----+
1 row in set (0.00 sec)6）使用正則表達式，REGEXP引領
列出stu_info表中姓名以“J”開頭且以“y”結尾的學員記錄：
mysql> SELECT * FROM stu_info WHERE name REGEXP '^J.*y$';
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jerry | boy | 27 |
+-------+--------+-----+
1 row in set (0.00 sec)效果等同于：
mysql> SELECT * FROM stu_info WHERE name Like 'J%y';
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jerry | boy | 27 |
+-------+--------+-----+
1 row in set (0.00 sec)列出stu_info表中姓名以“J”開頭或者以“y”結尾的學員記錄：
mysql> SELECT * FROM stu_info WHERE name REGEXP '^J|y$';
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Lily | girl | 20 |
| Jerry | boy | 27 |
+-------+--------+-----+
3 rows in set (0.00 sec)效果等同于：
mysql> SELECT * FROM stu_info WHERE name Like 'J%' OR name Like '%y';
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jim | girl | 24 |
| Lily | girl | 20 |
| Jerry | boy | 27 |
+-------+--------+-----+
3 rows in set (0.00 sec)7）按指定的字段排序，ORDER BY
列出stu_info表的所有記錄，按年齡排序：
mysql> SELECT * FROM stu_info GROUP BY age;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Lily | girl | 20 |
| Tom | boy | 21 |
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
4 rows in set (0.00 sec)因默認為升序（Ascend）排列，所以上述操作等效于：
mysql> SELECT * FROM stu_info GROUP BY age ASC;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Lily | girl | 20 |
| Tom | boy | 21 |
| Jim | girl | 24 |
| Jerry | boy | 27 |
+-------+--------+-----+
4 rows in set (0.00 sec)若要按降序（Descend）排列，則將ASC改為DESC即可：
mysql> SELECT * FROM stu_info GROUP BY age DESC;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jerry | boy | 27 |
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
+-------+--------+-----+
4 rows in set (0.00 sec)8）限制查詢結果的輸出條數，LIMIT
查詢stu_info表的所有記錄，只列出前3條：
mysql> SELECT * FROM stu_info LIMIT 3;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim | girl | 24 |
| Tom | boy | 21 |
| Lily | girl | 20 |
+------+--------+-----+
3 rows in set (0.00 sec)列出stu_info表中年齡最大的3條學員記錄：
mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;
+-------+--------+-----+
| name | gender | age |
+-------+--------+-----+
| Jerry | boy | 27 |
| Jim | girl | 24 |
| Tom | boy | 21 |
+-------+--------+-----+
3 rows in set (0.00 sec)9）分組查詢結果，GROUP BY
針對stu_info表，按性別分組，分別統計出男、女學員的人數：
mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| boy | 3 |
| girl | 2 |
+--------+---------------+
2 rows in set (0.00 sec)列出查詢字段時，可以通過AS關鍵字來指定顯示別名，比如上述操作可改為：
mysql> SELECT gender AS '性別',count(gender) AS '人數'
-> FROM stu_info GROUP BY gender;
+--------+--------+
| 性別 | 人數 |
+--------+--------+
| boy | 3 |
| girl | 2 |
+--------+--------+
2 rows in set (0.00 sec)免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
