MySQL各类查询语句DQL--like_in_between_and

建表语句

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` datetime DEFAULT NULL,
  `userName` varchar(20) DEFAULT NULL,
  `pwd` varchar(36) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `age` tinyint(3) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据

insert into student values(0,'2024-02-25 10:00:00','赵灵儿','123',
'5156154154',16,'女','逍遥');
insert into student values(0,'2024-02-25 10:00:00','王于','123',
'5156154154',17,'女','慕容');
insert into student values(0,'2024-02-25 10:00:00','龙','123',
'5156154154',22,'女','我我我');
insert into student values(0,'2024-02-25 10:00:00','杨过','123',
'5156154154',18,'男','一遇');
insert into student values(0,'2024-02-25 10:00:00','杨潇','123',
'5156154154',27,'男','养过');
insert into student (userName,age,introduce)values('黄衣',26,
'杨过与龙姑娘的大女');

查询语句

# like的使用语法，where 后 先写查询列再写like，最后写匹配字符串
SELECT * FROM `student` WHERE userName LIKE '杨_';
SELECT * FROM `student` WHERE userName LIKE '杨%';
SELECT * FROM `student` WHERE inteoduce LIKE '%蓉%';
# 模糊查询基本上所有后台管理系统都会有此功能
SELECT * FROM `student` WHERE pwd is not null;
# 范围查询 between and是包含==的，既可以查询数据范围，也可以查询时间范围，用途广泛
SELECT * FROM `student` WHERE age between 22 and 30;
SELECT * FROM `student` WHERE CURRENT_DATE BETWEEN '2024-02-22 00:00:00' and '2024-02-28 0:00:00';、
# in关键字的使用，不建议使用这个关键字，因为它不一定走索引，在百万的数据中会很卡
SELECT * FROM `student` WHERE userName in ('赵灵儿','杨过','龙姑娘');