3969mysql数据库应用_MySQL数据库应用（9）DQL之select知识

一、查看数据

1、查询表的所有数据行

1)命令语法：select from where

其中，select，from，where 是不能随便改的，是关键字，支持大小写

2)例：查看表test中所有数据

a.进入指定库后查询

mysql>use oldboy

Database changed

mysql> select * fromtest;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00sec)

mysql> select id,name fromtest;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00 sec)

2、根据指定条件查询

1)例：查看表test中前2行数据

执行命令：

mysql> select id,name from test limit 2;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

+----+---------+

2 rows in set (0.00sec)

mysql> select id,name from test limit 0，2;

ERROR1327 (42000): Undeclared variable: 0，2mysql> select id,name from test limit 0,2;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

+----+---------+

2 rows in set (0.00 sec)

2)指定固定条件查询数据

执行命令：

mysql> select id,name from test where id=1;+----+--------+

| id | name |

+----+--------+

| 1 | oldboy |

+----+--------+

1 row in set (0.00sec)

mysql> select id,name from test where name='oldgirl';+----+---------+

| id | name |

+----+---------+

| 2 | oldgirl |

+----+---------+

1 row in set (0.00sec)

#提示：字符类型的查询条件的值要带引号。

mysql> select id,name from test where name='oldgirl' and id=5;

Emptyset (0.00sec)

mysql> select id,name from test where name='oldgirl' or id=5;+----+---------+

| id | name |

+----+---------+

| 2 | oldgirl |

| 5 | kaka |

+----+---------+

2 rows in set (0.11 sec)

3)指定固定条件范围查数据

执行命令：

mysql> select id,name from test where id>2 and id<4;+----+------+

| id | name |

+----+------+

| 3 | inca |

+----+------+

1 row in set (0.11sec)

mysql> select id,name from test where id>2 or id<4;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.11 sec)

4)其他查询功能

a.排序功能

mysql> select id,name fromtest order by id;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00sec)

mysql> select id,name fromtest order by id asc;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00sec)

mysql> select id,name fromtest order by id desc;+----+---------+

| id | name |

+----+---------+

| 5 | kaka |

| 4 | zuma |

| 3 | inca |

| 2 | oldgirl |

| 1 | oldboy |

+----+---------+

5 rows in set (0.00 sec)

3、多表查询

1)建立几个关联表

要实现多表连表查询，就需要有关联表及数据，表名及字段内容如下：

==================================——学生表：Student(Sno，Sname，Ssex，Sage，Sdept)

————————(学号-主键，姓名，性别，年龄，所在系)===================================drop tables student；

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=latin1==================================——课程表：Course(Cno，Cname，Ccredit)

————(课程号-主键，课程名，学分)===================================Create 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=utf8============================——选课表：SC(Sno，Cno，Grade)

————(学号-主键，课程号-主键，成绩)

————其中SC表主键参照Student，Course表============================Create 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=utf8

2)往关联表中填充数据

a.学生表插入数据：

insert into student values(0001,'宏志','男','30','计算机网络');

insert into student values(0002,'王硕','男','30','computer application');

insert into student values(0003,'oldboy','男','28','物流管理');

insert into student values(0004,'脉动','男','29','computer application');

insert into student values(0005,'oldgirl','女','26','计算机科学与技术');

insert into student values(0006,'莹莹','女','22','护士');

b.课程表插入数据：

insert into course values(1001,'Linux中高级运维',3);

insert into course values(1002,'Linux高级架构师',5);

insert into course values(1003,'MySQL高级DBA',4);

insert into course values(1004,'Python运维开发',4);

insert into course values(1005,'Java web 开发',3);

c.选课表插入数据

insert into SC(Sno,Cno,Grade) values(0001,1001,4);

insert into SC(Sno,Cno,Grade) values(0001,1002,3);

insert into SC(Sno,Cno,Grade) values(0001,1003,1);

insert into SC(Sno,Cno,Grade) values(0001,1004,6);

insert into SC(Sno,Cno,Grade) values(0002,1001,3);

insert into SC(Sno,Cno,Grade) values(0002,1002,2);

insert into SC(Sno,Cno,Grade) values(0002,1003,2);

insert into SC(Sno,Cno,Grade) values(0002,1004,8);

insert into SC(Sno,Cno,Grade) values(0003,1001,4);

insert into SC(Sno,Cno,Grade) values(0003,1002,4);

insert into SC(Sno,Cno,Grade) values(0003,1003,2);

insert into SC(Sno,Cno,Grade) values(0003,1004,8);

insert into SC(Sno,Cno,Grade) values(0004,1001,1);

insert into SC(Sno,Cno,Grade) values(0004,1002,1);

insert into SC(Sno,Cno,Grade) values(0004,1003,2);

insert into SC(Sno,Cno,Grade) values(0004,1004,3);

insert into SC(Sno,Cno,Grade) values(0005,1001,5);

insert into SC(Sno,Cno,Grade) values(0005,1002,3);

insert into SC(Sno,Cno,Grade) values(0005,1003,2);

insert into SC(Sno,Cno,Grade) values(0005,1004,9);

查看三张表的结果：

mysql> select * fromstudent;+-----+---------+------+------+--------------------------+

| Sno | Sname | Ssex | Sage | Sdept |

+-----+---------+------+------+--------------------------+

| 1 | 宏志 | 男 | 30 | 计算机网络 |

| 2 | 王硕 | 男 | 30 | computer applica |

| 3 | oldboy | 男 | 28 | 物流管理 |

| 4 | 脉动 | 男 | 29 | computer applica |

| 5 | oldgirl | 女 | 26 | 计算机科学与技术 |

| 6 | 莹莹 | 女 | 22 | 护士 |

+-----+---------+------+------+--------------------------+

6 rows in set (0.00sec)

mysql> select * fromcourse;+------+----------------------+---------+

| Cno | Cname | Ccredit |

+------+----------------------+---------+

| 1001 | Linux中高级运维 | 3 |

| 1002 | Linux高级架构师 | 5 |

| 1003 | MySQL高级DBA | 4 |

| 1004 | Python运维开发 | 4 |

| 1005 | Java web 开发 | 3 |

+------+----------------------+---------+

5 rows in set (0.00sec)

mysql> select * fromSC;+------+------+-----+-------+

| SCid | Cno | Sno | Grade |

+------+------+-----+-------+

| 1 | 1001 | 1 | 4 |

| 2 | 1002 | 1 | 3 |

| 3 | 1003 | 1 | 1 |

| 4 | 1004 | 1 | 6 |

| 5 | 1001 | 2 | 3 |

| 6 | 1002 | 2 | 2 |

| 7 | 1003 | 2 | 2 |

| 8 | 1004 | 2 | 8 |

| 9 | 1001 | 3 | 4 |

| 10 | 1002 | 3 | 4 |

| 11 | 1003 | 3 | 2 |

| 12 | 1004 | 3 | 8 |

| 13 | 1001 | 4 | 1 |

| 14 | 1002 | 4 | 1 |

| 15 | 1003 | 4 | 2 |

| 16 | 1004 | 4 | 3 |

| 17 | 1001 | 5 | 5 |

| 18 | 1002 | 5 | 3 |

| 19 | 1003 | 5 | 2 |

| 20 | 1004 | 5 | 9 |

+------+------+-----+-------+

20 rows in set (0.00 sec)

多表查询练习：

mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 1 | 宏志 | Linux中高级运维 | 4 |

| 2 | 王硕 | Linux中高级运维 | 3 |

| 3 | oldboy | Linux中高级运维 | 4 |

| 4 | 脉动 | Linux中高级运维 | 1 |

| 5 | oldgirl | Linux中高级运维 | 5 |

| 1 | 宏志 | Linux高级架构师 | 3 |

| 2 | 王硕 | Linux高级架构师 | 2 |

| 3 | oldboy | Linux高级架构师 | 4 |

| 4 | 脉动 | Linux高级架构师 | 1 |

| 5 | oldgirl | Linux高级架构师 | 3 |

| 1 | 宏志 | MySQL高级DBA | 1 |

| 2 | 王硕 | MySQL高级DBA | 2 |

| 3 | oldboy | MySQL高级DBA | 2 |

| 4 | 脉动 | MySQL高级DBA | 2 |

| 5 | oldgirl | MySQL高级DBA | 2 |

| 1 | 宏志 | Python运维开发 | 6 |

| 2 | 王硕 | Python运维开发 | 8 |

| 3 | oldboy | Python运维开发 | 8 |

| 4 | 脉动 | Python运维开发 | 3 |

| 5 | oldgirl | Python运维开发 | 9 |

+-----+---------+----------------------+-------+

20 rows in set (0.00sec)

排序：

mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno;+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 1 | 宏志 | Linux中高级运维 | 4 |

| 1 | 宏志 | Python运维开发 | 6 |

| 1 | 宏志 | MySQL高级DBA | 1 |

| 1 | 宏志 | Linux高级架构师 | 3 |

| 2 | 王硕 | Linux中高级运维 | 3 |

| 2 | 王硕 | Python运维开发 | 8 |

| 2 | 王硕 | MySQL高级DBA | 2 |

| 2 | 王硕 | Linux高级架构师 | 2 |

| 3 | oldboy | Linux中高级运维 | 4 |

| 3 | oldboy | Python运维开发 | 8 |

| 3 | oldboy | MySQL高级DBA | 2 |

| 3 | oldboy | Linux高级架构师 | 4 |

| 4 | 脉动 | Python运维开发 | 3 |

| 4 | 脉动 | MySQL高级DBA | 2 |

| 4 | 脉动 | Linux高级架构师 | 1 |

| 4 | 脉动 | Linux中高级运维 | 1 |

| 5 | oldgirl | Python运维开发 | 9 |

| 5 | oldgirl | MySQL高级DBA | 2 |

| 5 | oldgirl | Linux高级架构师 | 3 |

| 5 | oldgirl | Linux中高级运维 | 5 |

+-----+---------+----------------------+-------+

20 rows in set (0.00 sec)

4、使用explain查询select查询语句执行计划

即使用索引情况

mysql>show tables;+------------------+

| Tables_in_oldboy |

+------------------+

| SC |

| course |

| student |

| test |

+------------------+

4 rows in set (0.00sec)

mysql>desc test;+-------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| name | char(20) | NO | | NULL | |

+-------+----------+------+-----+---------+----------------+

2 rows in set (0.00sec)

mysql> select * from test where name='oldboy';+----+--------+

| id | name |

+----+--------+

| 1 | oldboy |

+----+--------+

1 row in set (0.00sec)

mysql> explain select * from test where name='oldboy'\G*************************** 1. row ***************************id:1select_type: SIMPLE

table: test

type: ALL

possible_keys: NULL

key: NULL

key_len: NULLref: NULL

rows:5Extra: Usingwhere

1 row in set (0.00sec)

mysql>create index index_name on test(name);

Query OK,0 rows affected (0.63sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc test;+-------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| name | char(20) | NO | MUL | NULL | |

+-------+----------+------+-----+---------+----------------+

2 rows in set (0.00sec)

mysql> explain select * from test where name='oldboy'\G*************************** 1. row ***************************id:1select_type: SIMPLE

table: test

type:refpossible_keys: index_name

key: index_name

key_len:20

ref: constrows:1Extra: Usingwhere; Using index1 row in set (0.00 sec)