13.2.9 单表查询1

发表于 2019-05-25 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 11k 阅读时长 ≈ 10 分钟

13.2.9单表查询

select语句的功能就是查询数据。select语句也是SQL语句中功能最丰富的语句，select语句不仅可以执行单表查询，而且可以执行多表连接查询，还可以进行子查询，select语句用于从一个或多个数据表中选出特定行、特定列的交集。select语句最简单的功能如图13.10所示。
这里有一张图片

测试数据

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50508
Source Host           : localhost:3306
Source Database       : select_test

Target Server Type    : MYSQL
Target Server Version : 50508
File Encoding         : 65001

Date: 2019-05-25 15:51:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `student_table`
-- ----------------------------
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table_ibfk_1` FOREIGN KEY (`java_teacher`)
  REFERENCES `teacher_table` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_table
-- ----------------------------
INSERT INTO `student_table` VALUES ('1', '张三', '1');
INSERT INTO `student_table` VALUES ('2', '张三', '1');
INSERT INTO `student_table` VALUES ('3', '李四', '1');
INSERT INTO `student_table` VALUES ('4', '王五', '2');
INSERT INTO `student_table` VALUES ('5', '_王五', '2');
INSERT INTO `student_table` VALUES ('6', null, '2');
INSERT INTO `student_table` VALUES ('7', '赵六', '3');

-- ----------------------------
-- Table structure for `teacher_table`
-- ----------------------------
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher_table
-- ----------------------------
INSERT INTO `teacher_table` VALUES ('1', 'Yeeku');
INSERT INTO `teacher_table` VALUES ('2', 'Leegang');
INSERT INTO `teacher_table` VALUES ('3', 'Martine');

语法格式

单表查询的select语句的语法格式如下：

1
2
3

select column1,column2,...
from 数据源
[where condition]

数据源可以是表、视图等。
select后的列表用于确定选择哪些列
where条件用于确定选择哪些行

选择所有行

只有满足where条件的记录才会被选择出来;如果没有where条件,则默认选出所有行。

选择所有列

如果想选择出所有列,则可使用星号(*)代表所有列。

选择特定行特定列

如果增加where条件，则只选择出符合where条件的记录。如下SQL语句将选择出student_table表中java_teacher值大于3的记录的student_name列的值。

1
2
3

select student_name 
from student_table 
where java_teacher>3;

运行结果:

mysql>
select student_name 
from student_table 
where java_teacher>2;
+--------------+
| student_name |
+--------------+
| 赵六         |
+--------------+
1 row in set

选择所有行所有列

下面的SQL语句将会选择出teacher_table表中的所有行、所有列的数据。

1	select * from teacher_table;

运行效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

使用算术运算符常亮变量

当使用select语句进行查询时,还可以在select语句中使用算术运算符(+、-、*、/),从而形成算术表达式。使用算术表达式的规则如下。

对**数值型数据列**、变量、常量可以使用算术运算符(+、-、*、/)创建表达式;
对**日期型数据列、变量、常量可以使用部分算术运算符(+、-)创建表达式,两个日期之间可以进行减法运算,日期和数值之间可以进行加、减运算**;
运算符不仅可以在列和常量、变量之间进行运算，也可以在两列之间进行运算。不论从哪个角度来看，数据列都很像一个变量，只是这个变量的值具有指定的范围——逐行计算表中的每条记录时，数据列的值依次变化。因此能使用变量的地方，基本上都可以使用数据列。
下面的select语句中使用了算术运算符。

实例

把数据列当成一个变量

1 2	select teacher_id+5 from teacher_table;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select teacher_id+5
from teacher_table;
+--------------+
| teacher_id+5 |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set

查询出teacher_table表中teacher_id*3>4的记录

1
2
3

select * from
teacher_table
where teacher_id*3>4;

运行结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select * from
teacher_table
where teacher_id*3>4;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
2 rows in set

select后写常亮变量

需要指出的是，select后的不仅可以是数据列，也可以是表达式，还可以是变量、常量等。例如，如下语句也是正确的。

1 2	select 3*5,20 from teacher_table;

运行结果:

mysql> select 3*5,20
from teacher_table;
+-----+----+
| 3*5 | 20 |
+-----+----+
|  15 | 20 |
|  15 | 20 |
|  15 | 20 |
+-----+----+
3 rows in set

运算符优先级

SQL语句中算术运算符的优先级与Java语言中的运算符优先级完全相同，乘法和除法的优先级高于加法和减法，同级运算的顺序是从左到右，表达式中使用括号可强行改变优先级的运算顺序。

字符串连接运算

MySQL中没有提供字符串连接运算符，即无法使用加号（+）将字符串常量、字符串变量或字符串列连接起来。MySQL使用concat函数来进行字符串连接运算。

1 2	select concat(teacher_name,'-教授') from teacher_table;

运行结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select concat(teacher_name,'-教授')
from teacher_table;
+------------------------------+
| concat(teacher_name,'-教授') |
+------------------------------+
| Yeeku-教授                   |
| Leegang-教授                 |
| Martine-教授                 |
+------------------------------+
3 rows in set

与null运算

对于MySQL而言，
**如果在算术表达式中使用null，将会导致整个算术表达式的返回值为null**；
**如果在字符串连接运算中出现null，将会导致连接后的结果也是null**。
如下SQL语句将会返回null。

1 2	select concat(teacher_name,null) from teacher_table;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select concat(teacher_name,null)
from teacher_table;
+---------------------------+
| concat(teacher_name,null) |
+---------------------------+
| NULL                      |
| NULL                      |
| NULL                      |
+---------------------------+
3 rows in set
mysql>

而对某些数据库而言，如果让字符串和null进行连接运算，它会把null当成空字符串处理。

取别名

给列取别名

如果不希望直接使用列名作为列标题，则可以为数据列或表达式起一个别名，为数据列或表达式起别名时，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开。如下SQL语句所示：

1 2	select teacher_id+5 as MY_ID from teacher_table

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 as MY_ID
from teacher_table;
+-------+
| MY_ID |
+-------+
|     6 |
|     7 |
|     8 |
+-------+
3 rows in set
mysql> select teacher_id+5
from teacher_table;
+--------------+
| teacher_id+5 |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set
mysql>

别名中有特殊字符的情况: 添加双引号

为列起别名，可以改变列的标题头，用于表示计算结果的具体含义。如果列别名中使用特殊字符（例如空格），或者需要强制大小写敏感，都可以通过为别名添加双引号来实现。如下SQL语句所示：

1 2	select teacher_id+5 "MY`'#@! &%ID" from teacher_table;

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 "MY`'#@! &%ID"
from teacher_table;
+--------------+
| MY`'#@! &%ID |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set
mysql>

多列取别名

如果需要选择多列，并为多列起别名，则列与列之间以逗号隔开，但列和列别名之间以空格隔开。如下SQL语句所示：

1 2	select teacher_id+5 MY_ID,teacher_name 老师名 from teacher_table;

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_ID,teacher_name 老师名
from teacher_table;
+-------+---------+
| MY_ID | 老师名  |
+-------+---------+
|     6 | Yeeku   |
|     7 | Leegang |
|     8 | Martine |
+-------+---------+
3 rows in set

给表取别名

不仅可以为列或表达式起别名，也可以为表起别名，为表起别名的语法和为列或表达式起别名的语法完全一样，如下SQL语句所示：

1
2
3

select teacher_id+5 MY_ID,teacher_name 老师名
#为teacher_table起别名t
from teacher_table t;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_ID,teacher_name 老师名
#为teacher_table起别名t
from teacher_table t;
+-------+---------+
| MY_ID | 老师名  |
+-------+---------+
|     6 | Yeeku   |
|     7 | Leegang |
|     8 | Martine |
+-------+---------+
3 rows in set

多列运算

前面已经提到，列名可以当成变量处理，所以运算符也可以在多列之间进行运算，如下SQL语句所示：

select teacher_id+5 MY_TD,
concat(teacher_name,teacher_id) teacher_name_id
from teacher_table
where teacher_id*2>3;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_TD,
concat(teacher_name,teacher_id) teacher_name_id
from teacher_table
where teacher_id*2>3;
+-------+-----------------+
| MY_TD | teacher_name_id |
+-------+-----------------+
|     7 | Leegang2        |
|     8 | Martine3        |
+-------+-----------------+
2 rows in set

不出现列名

甚至可以在select、where子句中都不出现列名，如下SQL语句所示：

1
2
3

select 5+4
from teacher_table
where 2<9;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select 5+4
from teacher_table
where 2<9;
+-----+
| 5+4 |
+-----+
|   9 |
|   9 |
|   9 |
+-----+
3 rows in set

这种情况比较特殊：where语句后的条件表达式总是true，所以会把teacher_table表中的每条记录都选择出来——但SQL语句没有选择任何列，仅仅选择了一个常量，所以SQL会把该常量当成一列，teacher_table表中有多少条记录，该常量就出现多少次。

选择常量

对于选择常量的情形，指定数据表可能没有太大的意义，所以MySQL提供了一种扩展语法，允许select语句后没有from子句，即可写成如下形式：

1	select 5+4;

查询结果:

mysql> select 5+4;
+-----+
| 5+4 |
+-----+
|   9 |
+-----+
1 row in set

上面这种语句并不是标准SQL语句。例如，Oracle就提供了一个名为dual的虚表（最新的MySQL数据库也支持dual虚表），它没有任何意义，仅仅相当于from后的占位符。如果选择常量，则可使用如下语句：

1	select 5+4 from dual;

结果:

mysql> select 5+4 from dual;
+-----+
| 5+4 |
+-----+
|   9 |
+-----+
1 row in set

清除重复行

select默认会把所有符合条件的记录全部选出来，即使两行记录完全一样。如果想去除重复行，则可以使用distinct关键字从查询结果中清除重复行。比较下面两条SQL语句的执行结果：

#包括重复行
select student_name,java_teacher
from student_table;
#去除重复行
select distinct student_name,java_teacher
from student_table;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select student_name,java_teacher
from student_table;
+--------------+--------------+
| student_name | java_teacher |
+--------------+--------------+
| 张三         |            1 |
| 张三         |            1 |
| 李四         |            1 |
| 王五         |            2 |
| _王五        |            2 |
| NULL         |            2 |
| 赵六         |            3 |
+--------------+--------------+
7 rows in set
mysql> #去除重复行
select distinct student_name,java_teacher
from student_table;
+--------------+--------------+
| student_name | java_teacher |
+--------------+--------------+
| 张三         |            1 |
| 李四         |            1 |
| 王五         |            2 |
| _王五        |            2 |
| NULL         |            2 |
| 赵六         |            3 |
+--------------+--------------+
6 rows in set

注意
使用distinct去除重复行时,distinct紧跟在select关键字后面。它的作用是去除后面字段组合的重复值,而不管对应记录在数据库里是否重复。例如,(1,ˈaˈ,ˈbˈ)和(2,ˈaˈ,ˈbˈ)两条记录在数据库里是不重复的,但如果仅选择后面两列,则distinct会认为两条记录重复。

13.2.9单表查询

测试数据

语法格式

选择所有行

选择所有列

选择特定行特定列

选择所有行所有列

使用算术运算符 常亮 变量

实例

select后写常亮变量

运算符优先级

字符串连接运算

与null运算

取别名

给列取别名

别名中有特殊字符的情况: 添加双引号

多列取别名

给表取别名

多列运算

不出现列名

选择常量

清除重复行

使用算术运算符常亮变量