Mysql DBA 進階營運學習筆記-DQL語句之select知識講解

標籤：red   table   ati   ceshi   order by   column   nim   表示   compute   

9.9.8 查詢資料

9.9.8.1 查詢表的所有資料行

（1）命令文法：select<欄位1，欄位2，…>from<表名>where<運算式>

（2）列子：查看錶wwn中所有資料

a.進入指定資料庫查詢

[email protected] 04:5152->use wwnDatabase changed[email protected] 04:5159->select * from test-> ;+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |+----+-----------+

b.直接查詢庫下面的表的資料；

[email protected] 04:5311->use zbfDatabase changed[email protected] 04:5915->select id from test;ERROR 1146 (42S02): Table ‘zbf.test‘ doesn‘t exist[email protected] 04:5919->select * from wwn.test;?用點號分隔庫和表+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |+----+-----------+

c.推薦指定欄位查詢

[email protected] 04:5938->use wwn;Database changed[email protected] 05:0134->select id,name from test;+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |+----+-----------+

9.9.8.2 根據指定條件查詢表的部分資料

（1）列子：查看錶test表中前2行資料

執行命令：

[email protected] 05:0156->select id,name from test limit 2;+----+--------+| id | name   |+----+--------+|  1 | wwnwan ||  2 | zbf|+----+--------+

（2）根據固定條件查詢資料

執行命令：

[email protected] 05:0500->select * from test where id=1;+----+--------+| id | name   |+----+--------+|  1 | wwnwan |+----+--------+1 row in set (0.00 sec)

查詢字元要加引號

[email protected] 05:1036->select id,name from test where name=wwnwan;ERROR 1054 (42S22): Unknown column ‘wwnwan‘ in ‘where clause‘[email protected] 05:1752->select id,name from test where name=‘wwnwan‘;+----+--------+| id | name   |+----+--------+|  1 | wwnwan |

多個條件用and查詢，查詢條件既要滿足id=2又要滿足name=zbf;用or查詢，查詢條件都滿足。

[email protected] 05:2540->select id,name from test where id=2 and name=‘zbf‘;+----+------+| id | name |+----+------+|  2 | zbf  |+----+------+

（3）指定固定條件範圍查詢

執行命令：

多個條件and取交集

[email protected] 05:3022->select id,name from test where id>2 and id<4;+----+------+| id | name |+----+------+|  3 | lisi |+----+------+1 row in set (0.00 sec)

多個條件or取並集

[email protected] 05:3344->select id,name from test where id>2 or id<4;+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |

（4）其他查詢功能

a.排序功能

升序：

[email protected] 05:3733->select id,name from test order by id asc;+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |+----+-----------+

降序：

[email protected] 05:3757->select id,name from test order by id desc;+----+-----------+| id | name  |+----+-----------+|  5 | nimei ||  4 | woshishei ||  3 | lisi  ||  2 | zbf   ||  1 | wwnwan|+----+-----------+

9.9.9多表查詢

9.9.9.1 建立幾個關聯表

Create Table: CREATE TABLE `student` (  `Sno` int(10) NOT NULL COMMENT ‘學號‘,  `Sname` varchar(16) NOT NULL COMMENT ‘姓名‘,  `Ssex` char(2) NOT NULL COMMENT ‘性別‘,  `Sage` tinyint(2) NOT NULL DEFAULT ‘0‘ COMMENT ‘學生年齡‘,  `Sdept` varchar(16) DEFAULT NULL COMMENT ‘學生所在系別‘,  PRIMARY KEY (`Sno`),  KEY `index_Sname` (`Sname`)) ENGINE=InnoDB DEFAULT CHARSET=latin1Create Table: CREATE TABLE `course` (  `Cno` int(10) NOT NULL COMMENT ‘課程表‘,  `Cname` varchar(64) NOT NULL COMMENT ‘課程名‘,  `Ccredit` tinyint(2) NOT NULL COMMENT ‘學分‘,  PRIMARY KEY (`Cno`)) ENGINE=InnoDB DEFAULT CHARSET=latin1Create Table: CREATE TABLE `SC` (  `SCid` int(12) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵‘,  `Cno` int(10) NOT NULL COMMENT ‘課程號‘,  `Sno` int(10) NOT NULL COMMENT ‘學號‘,  `Grade` tinyint(2) NOT NULL COMMENT ‘學產生績‘,  PRIMARY KEY (`SCid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

9.9.9.2 往關聯表中填充資料

1.學生表插入資料

[email protected] 07:0129->insert into student values(0001,‘張三‘,‘男‘,‘22‘,‘電腦網路‘);Query OK, 1 row affected, 1 warning (0.00 sec)[email protected] 07:0616->insert into student values(0002,‘李四‘,‘男‘,‘21‘,‘電腦網路‘);Query OK, 1 row affected, 1 warning (0.00 sec)[email protected] 07:0625->insert into student values(0003,‘王二‘,‘男‘,‘28‘,‘物流管理‘);Query OK, 1 row affected, 1 warning (0.00 sec)[email protected] 07:0844->insert into student values(0004,‘脈動‘,‘男‘,‘29‘,‘computer application‘);Query OK, 1 row affected, 2 warnings (0.00 sec)[email protected] 07:0852->insert into student values(0005,‘woshishei‘,‘女‘,‘26‘,‘電腦科學與技術‘);Query OK, 1 row affected, 2 warnings (0.00 sec)[email protected] 07:0944->insert into student values(0006,‘瑩瑩‘,‘女‘,‘26‘,‘護士‘);Query OK, 1 row affected, 1 warning (0.00 sec)

2.課程表插入資料

[email protected] 07:1221->insert into course values(1001,‘linux中進階營運‘,‘3‘);Query OK, 1 row affected (0.00 sec)[email protected] 07:1349->insert into course values(1002,‘linux進階架構師‘,‘3‘);Query OK, 1 row affected (0.00 sec)[email protected] 07:1410->insert into course values(1003,‘MySQL 進階Dba ‘,‘4‘);Query OK, 1 row affected (0.00 sec)[email protected] 07:1507->insert into course values(1004,‘Python 營運開發‘,‘4‘);Query OK, 1 row affected (0.00 sec)[email protected] 07:1555->insert into course values(1005,‘Jave web 開發‘,‘3‘);Query OK, 1 row affected (0.00 sec)

3.選課表插入資料

[email protected] 07:1618->insert into SC(Sno,Cno,Grade) values(0001,1001,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2042->insert into SC(Sno,Cno,Grade) values(0001,1002,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2105->insert into SC(Sno,Cno,Grade) values(0001,1003,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2114->insert into SC(Sno,Cno,Grade) values(0001,1004,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2121->insert into SC(Sno,Cno,Grade) values(0002,1001,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2209->insert into SC(Sno,Cno,Grade) values(0002,1002,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2215->insert into SC(Sno,Cno,Grade) values(0002,1003,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2221->insert into SC(Sno,Cno,Grade) values(0002,1004,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2228->insert into SC(Sno,Cno,Grade) values(0003,1001,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2318->insert into SC(Sno,Cno,Grade) values(0003,1002,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2328->insert into SC(Sno,Cno,Grade) values(0003,1003,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2334->insert into SC(Sno,Cno,Grade) values(0003,1004,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2339->insert into SC(Sno,Cno,Grade) values(0004,1001,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2405->insert into SC(Sno,Cno,Grade) values(0004,1002,3);Query OK, 1 row affected (0.00 sec)[email protected] 07:2413->insert into SC(Sno,Cno,Grade) values(0004,1003,4);Query OK, 1 row affected (0.00 sec)[email protected] 07:2421->insert into SC(Sno,Cno,Grade) values(0004,1004,4);Query OK, 1 row affected (0.00 sec)

4.聯集查詢，查詢學產生績

[email protected] 12:5043->select student.Sno,student.Sname,SC.Grade,course.Cname from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;+-----+--------+-------+----------------------+| Sno | Sname  | Grade | Cname|+-----+--------+-------+----------------------+|   1 | 張三 | 3 | linux中進階營運 ||   2 | 李四 | 3 | linux中進階營運 ||   3 | 王二 | 3 | linux中進階營運 ||   4 | 脈動 | 3 | linux中進階營運 ||   1 | 張三 | 3 | linux進階架構師 ||   2 | 李四 | 3 | linux進階架構師 ||   3 | 王二 | 3 | linux進階架構師 ||   4 | 脈動 | 3 | linux進階架構師 ||   1 | 張三 | 4 | MySQL 進階Dba  ||   2 | 李四 | 4 | MySQL 進階Dba  ||   3 | 王二 | 4 | MySQL 進階Dba  ||   4 | 脈動 | 4 | MySQL 進階Dba  ||   1 | 張三 | 4 | Python 營運開發  ||   2 | 李四 | 4 | Python 營運開發  ||   3 | 王二 | 4 | Python 營運開發  ||   4 | 脈動 | 4 | Python 營運開發  |+-----+--------+-------+----------------------+

也可以對查詢進行排序

[email protected] 12:5651->select student.Sno,student.Sname,SC.Grade,course.Cname from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno;+-----+--------+-------+----------------------+| Sno | Sname  | Grade | Cname|+-----+--------+-------+----------------------+|   1 | 張三 | 3 | linux中進階營運 ||   1 | 張三 | 4 | MySQL 進階Dba  ||   1 | 張三 | 3 | linux進階架構師 ||   1 | 張三 | 4 | Python 營運開發  ||   2 | 李四 | 3 | linux進階架構師 ||   2 | 李四 | 4 | Python 營運開發  ||   2 | 李四 | 3 | linux中進階營運 ||   2 | 李四 | 4 | MySQL 進階Dba  ||   3 | 王二 | 3 | linux中進階營運 ||   3 | 王二 | 4 | MySQL 進階Dba  ||   3 | 王二 | 3 | linux進階架構師 ||   3 | 王二 | 4 | Python 營運開發  ||   4 | 脈動 | 3 | linux進階架構師 ||   4 | 脈動 | 4 | Python 營運開發  ||   4 | 脈動 | 3 | linux中進階營運 ||   4 | 脈動 | 4 | MySQL 進階Dba  |+-----+--------+-------+----------------------+

9.9.10 使用explain查詢select查詢語句擷取執行查詢計劃資訊

判斷索引

a.表中沒有索引，用explain查詢select查詢

[email protected] 02:1846->explain select * from test where name=‘nimei‘\G*************************** 1. row ***************************   id: 1  select_type: SIMPLEtable: test type: ALLpossible_keys: NULL  key: NULL  key_len: NULL  ref: NULL rows: 5  查詢掃描的行數，沒有索引掃描5次Extra: Using where1 row in set (0.00 sec)

b.給test表中的name列建立索引。

[email protected] 02:1859->create index index_name on test(name);Query OK, 5 rows affected (0.06 sec)Records: 5  Duplicates: 0  Warnings: 0

c.這次我們給test表中的name列建立了索引，在用explain查詢select查詢。

[email protected] 02:2502->explain select * from test where name=‘nimei‘\G*************************** 1. row ***************************   id: 1  select_type: SIMPLEtable: test type: refpossible_keys: index_name  key: index_name 表示查詢已經走索引了  key_len: 20 索引的長度，因為基於整個列建立索引  ref: const rows: 1 查詢掃描行數，有索引掃描了一行Extra: Using where

提示：

Explain文法見官方手冊：

官方手冊需要掌握的章節5,6,7,8,10,11,13,14,15

Mysql DBA 進階營運學習筆記-DQL語句之select知識講解