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