MySQL常用查询语句
首先创建一张表，根据这张表来执行查询操作
新建一张表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for push_message_info
-- ----------------------------
DROP TABLE IF EXISTS `push_message_info`;
CREATE TABLE `push_message_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水ID(主键)',
`afterSchool` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经过校门',
`pushDate` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '推送消息时间',
`personNo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 353454 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
插入一些数据
INSERT INTO `push_message_info` VALUES (286150, '4号门进', '2022-04-01 07:04:53', '201705019');
INSERT INTO `push_message_info` VALUES (286151, '4号门进', '2022-04-01 07:04:53', '201705019');
INSERT INTO `push_message_info` VALUES (286152, '4号门进', '2022-04-01 07:18:53', '201806028');
INSERT INTO `push_message_info` VALUES (286153, '1号门进', '2022-04-01 07:32:21', '201806042');
INSERT INTO `push_message_info` VALUES (286154, '1号门进', '2022-04-01 07:32:22', '201806039');
INSERT INTO `push_message_info` VALUES (286155, '1号门进', '2022-04-01 07:37:14', '201806006');
INSERT INTO `push_message_info` VALUES (286156, '1号门进', '2022-04-01 07:39:23', '201806050');
INSERT INTO `push_message_info` VALUES (286157, '1号门进', '2022-04-01 07:39:47', '201806016');
INSERT INTO `push_message_info` VALUES (286158, '1号门进', '2022-04-01 07:40:45', '201601004');
INSERT INTO `push_message_info` VALUES (286159, '1号门进', '2022-04-01 07:43:26', '201806001');
INSERT INTO `push_message_info` VALUES (286160, '1号门出', '2022-04-01 07:41:52', '201806001');
INSERT INTO `push_message_info` VALUES (286161, '1号门出', '2022-04-01 07:41:55', '201806001');
INSERT INTO `push_message_info` VALUES (286162, '4号门出', '2022-04-01 07:44:13', '201806050');
INSERT INTO `push_message_info` VALUES (286163, '4号门出', '2022-04-01 07:44:15', '201806050');
INSERT INTO `push_message_info` VALUES (286164, '4号门出', '2022-04-01 07:44:18', '201806050');
INSERT INTO `push_message_info` VALUES (286165, '4号门出', '2022-04-01 07:44:21', '201806050');
INSERT INTO `push_message_info` VALUES (286166, '4号门出', '2022-04-01 07:44:24', '201806015');
INSERT INTO `push_message_info` VALUES (286167, '3号门出', '2022-04-01 07:44:06', '201806042');
INSERT INTO `push_message_info` VALUES (286168, '4号门出', '2022-04-01 07:44:27', '201806050');
INSERT INTO `push_message_info` VALUES (286169, '4号门出', '2022-04-01 07:44:30', '201806050');
INSERT INTO `push_message_info` VALUES (286170, '4号门出', '2022-04-01 07:44:34', '201806050');
INSERT INTO `push_message_info` VALUES (286171, '4号门出', '2022-04-01 07:44:36', '201806050');
INSERT INTO `push_message_info` VALUES (286172, '4号门出', '2022-04-01 07:44:39', '201806050');
INSERT INTO `push_message_info` VALUES (286173, '4号门出', '2022-04-01 07:44:42', '201806050');
INSERT INTO `push_message_info` VALUES (286174, '3号门出', '2022-04-01 07:44:24', '201806015');
INSERT INTO `push_message_info` VALUES (286175, '4号门出', '2022-04-01 07:44:45', '201806050');
INSERT INTO `push_message_info` VALUES (286176, '4号门出', '2022-04-01 07:44:48', '201806050');
1、基本select 语句
简单条件查询
select 字段 from 表名 where 条件
-- 查询所有字段
SELECT * from push_message_info WHERE id = 286150;
--------------------------------------------------
结果：
286150 4号门进 2022-04-01 07:04:53 201705019
--------------------------------------------------
-- 查询指定字段
SELECT personNo,afterSchool,pushDate from push_message_info WHERE id = 286150;
--------------------------------------------------
结果：
201705019 4号门进 2022-04-01 07:04:53
--------------------------------------------------
注 意
• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写。
• 使用缩进提高语句的可读性。
where 条件过滤比较运算符
例如
SELECT * from push_message_info WHERE id = 286150;
--------------------------------------------------
结果：
286150 4号门进 2022-04-01 07:04:53 201705019
--------------------------------------------------
其它同理
其他比较运算
SELECT personNo,afterSchool,pushDate from push_message_info WHERE id BETWEEN 286150 and 286155;
--------------------------------------------------
结果：
286150 201705019 4号门进 2022-04-01 07:04:53
286151 201705019 4号门进 2022-04-01 07:04:53
286152 201806028 4号门进 2022-04-01 07:18:53
286153 201806042 1号门进 2022-04-01 07:32:21
286154 201806039 1号门进 2022-04-01 07:32:22
286155 201806006 1号门进 2022-04-01 07:37:14
--------------------------------------------------
SELECT id,personNo,afterSchool,pushDate from push_message_info WHERE id IN(286150,286155) ;
--------------------------------------------------
结果：
286150 201705019 4号门进 2022-04-01 07:04:53
286155 201806006 1号门进 2022-04-01 07:37:14
--------------------------------------------------
SELECT id,personNo,afterSchool,pushDate from push_message_info WHERE id LIKE('28615%') ;
--------------------------------------------------
结果：
286150 201705019 4号门进 2022-04-01 07:04:53
286151 201705019 4号门进 2022-04-01 07:04:53
286152 201806028 4号门进 2022-04-01 07:18:53
286153 201806042 1号门进 2022-04-01 07:32:21
286154 201806039 1号门进 2022-04-01 07:32:22
286155 201806006 1号门进 2022-04-01 07:37:14
286156 201806050 1号门进 2022-04-01 07:39:23
286157 201806016 1号门进 2022-04-01 07:39:47
286158 201601004 1号门进 2022-04-01 07:40:45
286159 201806001 1号门进 2022-04-01 07:43:26
--------------------------------------------------
SELECT id,personNo,afterSchool,pushDate from push_message_info WHERE id is null ;
逻辑运算
-- 逻辑并
SELECT * from push_message_info WHERE id LIKE('28615%') and afterSchool='4号门进' ;
--------------------------------------------------
结果：
286150 201705019 4号门进 2022-04-01 07:04:53
286151 201705019 4号门进 2022-04-01 07:04:53
286152 201806028 4号门进 2022-04-01 07:18:53
--------------------------------------------------
SELECT * from push_message_info WHERE id LIKE('28615%') or afterSchool='4号门进' ;
--------------------------------------------------
结果：
数据有点多不展示
--------------------------------------------------
SELECT id,personNo,afterSchool,pushDate from push_message_info WHERE id not IN(286501,287147);
--------------------------------------------------
结果：
数据有点多不展示
--------------------------------------------------
列的别名
列的别名:
• 重命名一个列。
• 便于计算。
• 紧跟列名，也可以在列名和别名之间加入关键字 ‘AS’，别名使用双引号，以便在别名中包含空 格或特殊的字符并区分大小写。
使用别名
-- 列别名的使用
SELECT personNo as pn,afterSchool a,pushDate pd from push_message_info WHERE id = 286150;
--------------------------------------------------
结果：
pn a pd
201705019 4号门进 2022-04-01 07:04:53
--------------------------------------------------
显示表结构
DESCRIBE push_message_info
--------------------------------------------------
结果：
field type null key Extra
id bigint(20) NO PRI auto_increment
afterSchool varchar(50) YES
pushDate varchar(50) YES
personNo varchar(50) YES
--------------------------------------------------
排序
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。
默认升序
SELECT * from push_message_info WHERE id BETWEEN 286501 and 286505 ORDER BY personNo ;
--------------------------------------------------
结果：
286502 4号门进 2022-04-01 07:50:34 201701044
286501 4号门进 2022-04-01 07:50:32 201804051
286505 2号门进 2022-04-01 07:54:17 201905029
286503 3号门进 2022-04-01 07:53:17 201905037
286504 3号门进 2022-04-01 07:53:17 201905037
--------------------------------------------------
降序
SELECT * from push_message_info WHERE id BETWEEN 286501 and 286505 ORDER BY personNo DESC;
--------------------------------------------------
结果：
286503 3号门进 2022-04-01 07:53:17 201905037
286504 3号门进 2022-04-01 07:53:17 201905037
286505 2号门进 2022-04-01 07:54:17 201905029
286501 4号门进 2022-04-01 07:50:32 201804051
286502 4号门进 2022-04-01 07:50:34 201701044
--------------------------------------------------
按别名排序
SELECT personNo as pn,afterSchool,pushDate from push_message_info WHERE id BETWEEN 286501 and 286505 ORDER BY personNo DESC;
--------------------------------------------------
结果：
pn afterSchool pushDate
201905037 3号门进 2022-04-01 07:53:17
201905037 3号门进 2022-04-01 07:53:17
201905029 2号门进 2022-04-01 07:54:17
201804051 4号门进 2022-04-01 07:50:32
201701044 4号门进 2022-04-01 07:50:34
--------------------------------------------------
多个列排序
SELECT * from push_message_info WHERE id BETWEEN 286501 and 286505 ORDER BY personNo,pushDate DESC;
--------------------------------------------------
结果：
286502 4号门进 2022-04-01 07:50:34 201701044
286501 4号门进 2022-04-01 07:50:32 201804051
286505 2号门进 2022-04-01 07:54:17 201905029
286503 3号门进 2022-04-01 07:53:17 201905037
286504 3号门进 2022-04-01 07:53:17 201905037
--------------------------------------------------
