Mysql DBA 高级运维学习之路-DQL语句之select知识讲解

最新推荐文章于 2023-05-16 16:22:05 发布

weixin_33720078

最新推荐文章于 2023-05-16 16:22:05 发布

阅读量149

点赞数

文章标签：运维操作系统 python

原文链接：http://blog.51cto.com/10642812/2065809

版权

1.select查询数据

命令语法：select<字段1，字段2，…>from<表名>where<表达式>

1.1 查询表中所有数据

方法一：进入指定数据库查询

mysql>use linzhongniao
Database changed
mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314     |
|  3 | lisi        |
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
5 rows in set (0.00 sec)

方法二：直接查询库下面的表的数据

语法：select * from 库.表名库和表用点号分隔

mysql> select * from linzhongniao.test;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
5 rows in set (0.00 sec)

1.2 根据指定条件查询表的部分数据

（1）查看表test表中前2行数据

前两行的数据用limit 2表示

mysql> select * from linzhongniao.test limit 2;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
+----+-------------+
2 rows in set (0.00 sec)

(2)根据指定字段查询

select 中的好包括所有数据，只查询test表中的name字段

mysql> select name from linzhongniao.test;
+-------------+
| name|
+-------------+
| linzhogniao |
| wwn1314 |
| lisi|
| woshishei   |
| xiaozhang   |
+-------------+
5 rows in set (0.00 sec)

(3)where条件查询数据

查询test表中id为1的数据

执行命令：

mysql> select * from linzhongniao.test where id='1';
 +----+-------------+
| id | name|
 +----+-------------+
|  1 | linzhogniao |
 +----+-------------+
1 row in set (0.00 sec)

注意：where查询条件要用单引号引起来，如果查询条件是字符串不引起来会报错，如下所示：

mysql> select * from linzhongniao.test where name=wwn1314;
ERROR 1054 (42S22): Unknown column 'wwn1314' in 'where clause'
mysql> select * from linzhongniao.test where name='wwn1314';
+----+---------+
| id | name|
+----+---------+
|  2 | wwn1314 |
+----+---------+
1 row in set (0.00 sec)

多个where条件查询用and分隔，查询条件既要满足id=2又要满足name=zbf;还可以用or查询，查询条件满足一个即可。

mysql> select * from linzhongniao.test where id='2' and name='linzhongiao';
Empty set (0.00 sec)

mysql> select * from linzhongniao.test where id='2' or name='linzhongiao'; 
+----+---------+
| id | name|
+----+---------+
|  2 | wwn1314 |
+----+---------+
1 row in set (0.00 sec)

1.3 指定固定条件范围查询

执行命令：

(1)多个条件and取交集

mysql> select * from linzhongniao.test where id>2 and id<4;
+----+------+
| id | name |
+----+------+
|  3 | lisi |
+----+------+
1 row in set (0.00 sec)

(2)多个条件or取并集

mysql> select * from linzhongniao.test where id>2 or id<4; 
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
5 rows in set (0.00 sec)

1.4 排序查询

(1)按升序进行查询

mysql> select * from linzhongniao.test order by id desc;
+----+-------------+
| id | name|
+----+-------------+
|  5 | xiaozhang   |
|  4 | woshishei   |
|  3 | lisi|
|  2 | wwn1314 |
|  1 | linzhogniao |
+----+-------------+
5 rows in set (0.00 sec)

(2)按降序进行查询

system@ceshi 05:3757->select id,name from test order by id desc;
+----+-----------+
| id | name  |
+----+-----------+
|  5 | nimei |
|  4 | woshishei |
|  3 | lisi  |
|  2 | zbf   |
|  1 | wwnwan|
+----+-----------+

2.多表查询

2.1建立几个关联表

mysql> 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) 
 -> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE course(   
 -> Cno int(10) NOT NULL COMMENT '课程表',  
 -> Cname varchar(64) NOT NULL COMMENT '课程名',  
 -> Ccredit tinyint(2) NOT NULL COMMENT '学分',  
 -> PRIMARY KEY (Cno)  
 -> );
Query OK, 0 rows affected (0.01 sec)
mysql> 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)  
 -> );
Query OK, 0 rows affected (0.00 sec)

2.2 往关联表中插入数据

(1)学生表插入数据

mysql> insert into student values(0001,'张三','男','22','计算机网络');
Query OK, 1 row affected (0.10 sec)

mysql> insert into student values(0002,'李四','男','21','计算机网络');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(0003,'王二','男','28','物流管理');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(0004,'脉动','男','29','computer application');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> insert into student values(0005,'woshishei','女','26','计算机科学与技术');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(0006,'莹莹','女','26','护士');
Query OK, 1 row affected (0.00 sec)

(2)课程表插入数据

mysql> insert into course values(1001,'linux中高级运维','3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into course values(1002,'linux高级架构师','3');
Query OK, 1 row affected (0.10 sec)

mysql> insert into course values(1003,'MySQL 高级Dba ','4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into course values(1004,'Python 运维开发','4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into course values(1005,'Jave web 开发','3');
Query OK, 1 row affected (0.00 sec)

(3)选课表插入数据

mysql> insert into SC(Sno,Cno,Grade) values(0001,1001,3);
Query OK, 1 row affected (0.24 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0001,1002,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0001,1003,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0001,1004,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0002,1001,3);
Query OK, 1 row affected (0.10 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0002,1002,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0002,1003,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0002,1004,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0003,1001,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0003,1002,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0003,1003,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0003,1004,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0004,1001,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0004,1002,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0004,1003,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into SC(Sno,Cno,Grade) values(0004,1004,4);
Query OK, 1 row affected (0.00 sec)

2.3 查询数据

(1)联合查询，查询学生成绩和课程名

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

(2)也可以根据学生编号进行查询

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

3.结合explain获取执行查询计划信息

判断索引explain可以查看查询记录的次数，给要经常查询的列添加索引可以减少查询的次数

(1)表中没有索引，用explain查询select查询

mysql>explain select * from test where name='nimei'\G
*************************** 1. row ***************************
   id: 1
  select_type: SIMPLE
table: test
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 5  查询扫描的行数，没有索引扫描5次
Extra: Using where
1 row in set (0.00 sec)

b.给test表中的name列创建索引。

mysql>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查询。

mysql>explain select * from test where name='nimei'\G
*************************** 1. row ***************************
   id: 1
  select_type: SIMPLE
table: test
 type: ref
possible_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

转载于:https://blog.51cto.com/10642812/2065809