MySQL数据库专栏（三）数据表查询操作_mysql 根据专业表查询学校信息表怎么查-优快云博客

本文链接：https://blog.youkuaiyun.com/sunsddd/article/details/142735199

摘要

主要讲述SQL语句的基础查询、内联查询、外联查询、子查询的应用

基础查询
   查询表的全部字段
   查询表的指定字段
   带条件语句的查询
   去重复（DISTINCT）查询
   包含（IN）查询
   范围（BETWEEN AND）查询
   模糊（LIKE）查询
   查询结果排序
   简单分组查询
   统计分组查询
内联查询
   应用说明及准备
   内联查询语法结构
   使用实例
外连查询
   外连接使用概述
   左外连接
   右外连接
子查询
   子查询概述
   数据表准备
   带关键字IN的子查询
   注意事项

1、基础查询

1.1、查询表的全部字段

Select * from 表名

实例

select * from t_user;

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

| id | user_name | password | nick_name | user_no            | status | phone       |

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

| 1 | 张三      | 12345678 | 红太阳     | 131024685941523145 |      0 | 13465231587 |

| 2 | 张三1     | 12345671 | 红太阳1    | 131024685941523141 |      1 | 13465231581 |

| 3 | 张三2     | 12345672 | 红太阳2    | 131024685941523142 |      1 | 13465231582 |

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

1.2、查询表的指定字段

Select field1,field2,…,fieldn from 表名

实例

select id,nick_name,phone from t_user;

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

| id | nick_name | phone       |

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

| 1 | 红太阳     | 13465231587 |

| 2 | 红太阳1    | 13465231581 |

| 3 | 红太阳2    | 13465231582 |

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

1.3、带条件语句的查询

Select field1,field2,...,fieldn from 表明 where 字段名=需要匹配的字段内容

实例

select id,user_name,nick_name,user_no,phone from t_user where user_name='张三';

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

| id | user_name | nick_name | user_no | phone |

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

| 1 | 张三 | 红太阳 | 131024685941523145 | 13465231587 |

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

1.4、去重复（DISTINCT）查询

当在MySQL中执行简单数据查询时，有时会显示出重复数据。为了实现查询不重复数据，MySQL提供了DISTINCT功能，SQL语法如下：SELECT DISTINCT field1,field2,…,fieldn FROM tablename;

实例

select distinct status from t_user;

+--------+

| status |

+--------+

| 0 |

| 1 |

+--------+

1.5、包含（IN）查询

有的时候，当我们需要查询的目标记录限定在某个集合中的时候，在MySQL中可以使用关键字IN来实现，关键字IN可以实现判断字段的数值是否在指定集合中，该关键字的具体语句形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE filedm IN(value1,value2,value3,…,valuen);

实例

select user_name,nick_name,user_no,status,phone from t_user where status IN(1);

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

| user_name | nick_name | user_no            | status | phone       |

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

| 张三1     | 红太阳1    | 131024685941523141 |      1 | 13465231581 |

| 张三2     | 红太阳2    | 131024685941523142 |      1 | 13465231582 |

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

1.6、范围（BETWEEN AND）查询

当我们需要查询指定范围内的数据（如： id 从0 到 100）的时候，MySQL提供了关键字BETWEEN AND，用来实现判断字段的数值是否在指定范围内的条件查询。该关键字的具体语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE fieldm BETWEEN minvalue AND maxvalue

实例

select id,user_name,nick_name,user_no,status,phone from t_user where id between 1 and 2;

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

| id | user_name | nick_name | user_no            | status | phone       |

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

| 1 | 张三      | 红太阳     | 131024685941523145 |      0 | 13465231587 |

| 2 | 张三1     | 红太阳1    | 131024685941523141 |      1 | 13465231581 |

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

1.7、模糊（LIKE）查询

当我们只想用字符串中间的一部分特征查找含有特征字串的信息时，MySQL提供了关键字LIKE来实现模糊查询，需要使用通配符，具体语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE fieldm LIKE value;

实例

select id,user_name,nick_name,user_no,status,phone from t_user where phone like '%587%';

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

| id | user_name | nick_name | user_no | status | phone |

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

| 1 | 张三 | 红太阳 | 131024685941523145 | 0 | 13465231587 |

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

1.8、查询结果排序

在MySQL中，从表中查询出的数据可能是无序的，或者其排列顺序不是用户所期望的顺序，为了使查询结果的顺序满足用户的要求，可以使用关键字ORDER BY对记录进行排序，其语法形式如下：SELECT field1, field2, field3, …, fieldn FROM tablename ORDER BY fieldm [ASC|DESC]

实例

select * from t_user order by id desc;

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

| id | user_name | password | nick_name | user_no            | status | phone       |

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

| 3 | 张三2     | 12345672 | 红太阳2    | 131024685941523142 |      1 | 13465231582 |

| 2 | 张三1     | 12345671 | 红太阳1    | 131024685941523141 |      1 | 13465231581 |

| 1 | 张三      | 12345678 | 红太阳     | 131024685941523145 |      0 | 13465231587 |

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

1.9、简单分组查询

MySQL软件提供了5个统计函数来帮助用户统计数据，可以使用户很方便地对记录进行统计数、计算和、计算平均数、计算最大值和最小值，而不需要查询所有数据。

在具体使用统计函数时，都是针对表中所有记录数或指定特定条件（WHERE子句）的数据记录进行统计计算。在现实应用中，经常会先把所有数据记录进行分组，再对这些分组后的数据记录进行统计计算。

MySQL通过SQL语句GROUP BY来实现，分组数据查询语法如下：SELECT function()[,filed ] FROM tablename WHERE CONDITION GROUP BY field;

在上述语句中，参数field表示某字段名，通过该字段对名称为tablename的表的数据记录进行分组。

注意：在具体进行分组查询时，分组所依据的字段上的值一定要具有重复值，否则分组没有任何意义。

五个统计函数简介

统计数量

COUNT(*)：该种方式可以实现对表中记录进行统计,不管表字段中包含的是NULL值还是非NULL值。

COUNT(field):该种方式可以实现对指定字段的记录进行统计,在具体统计时将忽略NULL值。

统计计算平均值

该函数只有一种使用方式。

AVG(field)使用方式:该种方式可以实现对指定字段的平均值进行计算,在具体统计时将忽略NULL值。

统计计算求和

该函数只有一种使用方式。

SUM(field)使用方式:该种方式可以实现计算指定字段值之和,在具体统计时将忽略NULL值。

统计最大值

该函数只有一种使用方式。

MAX(field)使用方式:该种方式可以实现计算指定字段值中的最大值,在具体统计时将忽略NULL值。

统计最小值

该函数只有一种使用方式。

MIN(field)使用方式:该种方式可以实现计算指定字段值中的最小值,在具体统计时将忽略NULL值。

实例

select status,count(*) from t_user group by status;

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

| status | count(*) |

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

| 0 | 1 |

| 1 | 2 |

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

1.10、统计分组查询

在MySQL中，只实现简单的分组查询有时候可能没有任何实际意义，因为关键字GROUP BY单独使用时，默认查询出每个分组中随机的一条记录，具有很大的不确定性，一般建议将分组关键字与统计函数一起使用。

如果想显示每个分组中的字段，可以通过函数GROUP_CONCAT()来实现。该函数可以实现显示每个分组中的指定字段，函数的具体语法形式如下：SELECT GROUP_CONCAT(field) FROM tablename WHERE CONDITION GROUP BY field;

实例

select status,GROUP_CONCAT(nick_name) nick_name,count(nick_name) from t_user GROUP BY status;

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

| status | nick_name             | count(nick_name) |

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

|      0 | 红太阳                |                1 |

|      1 | 红太阳1,红太阳2       |                2 |

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

2、内联查询

2.1、内联查询概述

MySQL中的内联查询（Inner Join）是一种SQL查询操作，用于结合两个或多个表中的行。当两个表通过共同的字段（通常是外键和主键）相关联时，内联查询会返回这些表中所有匹配的行。如果在一个表中存在与另一个表连接条件不匹配的行，那么这些行就不会出现在查询结果中。

2.2、应用说明及准备

在实际开发中，我们会联合多个表来实现查询，比如把员工表和部门表联合起来就同时可以看到员工和所在部门的信息，员工表有员工姓名，手机号，岗位，所属部门等信息，部门表有部门名称、部门编号等信息，表结构如下：

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE IF NOT EXISTS `t_department` (

  `department_no` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_name` varchar(128) NOT NULL DEFAULT ''  COMMENT '部门名称',

  PRIMARY KEY (`department_no`),

  UNIQUE KEY `department_name` (`department_name`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE IF NOT EXISTS `t_employee` (

  `id` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_no` INT unsigned NOT NULL DEFAULT  '0'  COMMENT '部门表id',

  `name` varchar(128) NOT NULL DEFAULT ''  COMMENT '登录名',

  `position` varchar(128) NOT NULL DEFAULT ''  COMMENT '岗位',

  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',

  PRIMARY KEY (`id`),

  INDEX `name` (`name`),

  UNIQUE KEY `phone` (`phone`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

ALTER TABLE t_department AUTO_INCREMENT = 10000;

insert into t_department(`department_name`) values('软件部');

insert into t_department(`department_name`) values('测试部');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','张三','开发','13533265485');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','李四','开发','13533265486');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','张五','测试','13533265487');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','李六','测试','13533265488');

2.3、内联查询语法结构

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

2.4、使用实例

Select e.name,e.position,d.department_name,e.phone from t_employee e inner join t_department d on e.department_no=d.department_no;

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

| name   | position | department_name | phone       |

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

| 张三   | 开发     | 软件部          | 13533265485 |

| 李四   | 开发     | 软件部          | 13533265486 |

| 张五   | 测试     | 测试部          | 13533265487 |

| 李六   | 测试     | 测试部          | 13533265488 |

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

3、外连查询

3.1、外连接使用概述

当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。

外连接数据查询语法形式如下

SELECT field1,field2,…,fieldn

FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2

ON CONDITION

在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。

外连接查询可以分为以下二类

3.2、左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。

数据表准备

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE IF NOT EXISTS `t_department` (

  `department_no` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_name` varchar(128) NOT NULL DEFAULT ''  COMMENT '部门名称',

  PRIMARY KEY (`department_no`),

  UNIQUE KEY `department_name` (`department_name`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE IF NOT EXISTS `t_employee` (

  `id` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_no` INT unsigned NOT NULL DEFAULT  '0'  COMMENT '部门表id',

  `name` varchar(128) NOT NULL DEFAULT ''  COMMENT '登录名',

  `position` varchar(128) NOT NULL DEFAULT ''  COMMENT '岗位',

  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',

  PRIMARY KEY (`id`),

  INDEX `name` (`name`),

  UNIQUE KEY `phone` (`phone`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

ALTER TABLE t_department AUTO_INCREMENT = 10000;

insert into t_department(`department_name`) values('软件部');

insert into t_department(`department_name`) values('测试部');

insert into t_department(`department_name`) values('采购部');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','张三','开发','13533265485');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10000','李四','开发','13533265486');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','张五','测试','13533265487');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('10001','李六','测试','13533265488');

insert into t_employee(`department_no`,`name`,`position`,`phone`) values('0','孙七','测试','13533265489');

查询所有员工的姓名、岗位、手机号、部门信息，具体SQL语句如下

select e.name,e.position,e.phone,d.department_name from t_employee as e left join t_department as d on e.department_no=d.department_no;

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

| name   | position | phone       | department_name |

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

| 张三   | 开发     | 13533265485 | 软件部          |

| 李四   | 开发     | 13533265486 | 软件部          |

| 张五   | 测试     | 13533265487 | 测试部          |

| 李六   | 测试     | 13533265488 | 测试部          |

| 孙七   | 测试     | 13533265489 | NULL            |

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

3.3、右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。

查询所有部门的员工信息，具体SQL语句如下

select e.name,e.position,e.phone,d.department_name from t_employee as e right join t_department as d on e.department_no=d.department_no;

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

| name   | position | phone       | department_name |

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

| 李六   | 测试     | 13533265488 | 测试部          |

| 张五   | 测试     | 13533265487 | 测试部          |

| 李四   | 开发     | 13533265486 | 软件部          |

| 张三   | 开发     | 13533265485 | 软件部          |

| NULL   | NULL     | NULL        | 采购部          |

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

4、子查询

4.1、子查询概述

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。

带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛。

4.2、数据表准备

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE IF NOT EXISTS `t_department` (

  `department_no` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_name` varchar(128) NOT NULL DEFAULT ''  COMMENT '部门名称',

  PRIMARY KEY (`department_no`),

  UNIQUE KEY `department_name` (`department_name`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE IF NOT EXISTS `t_employee` (

  `id` INT unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `department_no` INT unsigned NOT NULL DEFAULT  '0'  COMMENT '部门表id',

  `name` varchar(128) NOT NULL DEFAULT ''  COMMENT '登录名',

  `position` varchar(128) NOT NULL DEFAULT ''  COMMENT '岗位',

  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',

`salary` INT unsigned NOT NULL DEFAULT  '0'  COMMENT '薪资',

  PRIMARY KEY (`id`),

  INDEX `name` (`name`),

  UNIQUE KEY `phone` (`phone`)

) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;

ALTER TABLE t_department AUTO_INCREMENT = 10000;

insert into t_department(`department_name`) values('软件部');

insert into t_department(`department_name`) values('测试部');

insert into t_department(`department_name`) values('采购部');

insert into t_employee(`department_no`,`name`,`position`,`phone`,`salary`) values('10000','张三','开发','13533265485','10000');

insert into t_employee(`department_no`,`name`,`position`,`phone`,`salary`) values('10000','李四','开发','13533265486','11000');

insert into t_employee(`department_no`,`name`,`position`,`phone`,`salary`) values('10001','张五','测试','13533265487','12000');

insert into t_employee(`department_no`,`name`,`position`,`phone`,`salary`) values('10001','李六','测试','13533265488','13000');

insert into t_employee(`department_no`,`name`,`position`,`phone`,`salary`) values('0','孙七','测试','13533265489','14000');

查询薪资大于平均薪资的员工信息

select AVG(salary) from t_employee;

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

| AVG(salary) |

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

| 12000.0000 |

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

select name,phone,salary from t_employee where salary > (select AVG(salary) from t_employee);

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

| name   | phone       | salary |

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

| 李六   | 13533265488 | 13000 |

| 孙七   | 13533265489 | 14000 |

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

注意：使用比较运算符时，select 子句获得的记录数不能大于1条!!!

4.3、带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：

NOT IN的用法与IN相同。

例如：查询张三、李四、张五中最高薪资是多少：

Select MAX(salary) from t_employee where salary in (select salary from t_employee where name in ('张三','李四','张五') ) ;

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

| MAX(salary) |

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

| 12000 |

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