Mysql DBA Advanced Operations Learning Note-DQL Statement Select Knowledge Explained

9.9.8 Querying data

9.9.8.1 all data rows of a query table

(1) Command Syntax:select< field 1, field 2,...>from< table name >where< expression >

(2) The following: View all data in the table WWN

A. Entering a specified database query

[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. Directly querying the data in the tables below the library;

[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. Recommendation of a specified field query

[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 querying partial data for a table based on specified criteria

(1): View the first 2 rows of data in the table test table

Execute command:

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

(2) query data based on fixed conditions

Execute command:

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

Query characters are quoted

[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 |

Multiple conditions are queried with and, the query condition satisfies both id=2 and NAME=ZBF, and query conditions are satisfied with OR.

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

(3) Specifying a fixed condition range query

Execute command:

Multiple conditional and fetch intersections

[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)

Multiple conditions or pull-set

[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) Other query function

A. Sorting function

Ascending:

[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 |+----+-----------+

Descending:

[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 Multi-Table query

9.9.9.1 establishing several related tables

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 populating the associated table with data

1. Student Table Insert Data

[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. Curriculum Insert Data

[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. Select a schedule to insert data

[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. Joint enquiry, query student results

[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 运维开发  |+-----+--------+-------+----------------------+

You can also sort the query

[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 using the Explain Query Select query statement to get execution query plan information

Judging index

A. There is no index in the table, query the select query with explain

[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. Create an index to the name column in the test table.

[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. This time we created an index to the name column in the test table and queried the select query with explain.

[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

Tips:

See the Official Handbook for explain syntax:

Official manuals need to master the chapters 5,6,7,8,10,11,13,14,15

Mysql DBA Advanced Operations Learning Note-DQL Statement Select Knowledge Explained