MySQL模糊查询LIKE- BETWEEN-AND-IN

 前言

数据库名称可以为【schoolDB】，字符集【utf8】，排序规则【utf8-gengeral-ci】。

1、建表语句——DDL

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `createDate` datetime DEFAULT NULL COMMENT '创建时间',
  `userName` varchar(20) DEFAULT NULL COMMENT '姓名',
  `pwd` varchar(36) DEFAULT NULL COMMENT '密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别',
  `introduce` varchar(255) DEFAULT NULL COMMENT '个人简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

2、插入语句——DML

INSERT INTO student VALUES(0, '2024-10-09 10:00:00','周星驰','123456',
'15666666666',62,'男','星爷');
INSERT INTO student VALUES(0,'2022-09-20 09:00:00','成龙','134567',
'13266666666',25,'男','龙叔');
INSERT INTO student VALUES(0,'2023-08-06 07:00:00','洪金宝','123457',
'13299999999',34,'男','武打大哥');
INSERT INTO student VALUES(0,'2023-09-06 06:00:00','刘德华','123857',
'13299990099',49,'男','给我一杯我一杯忘情水');
INSERT INTO student VALUES(0,'2023-03-06 01:00:00','郭富城','323457',
'13288888888',47,'男','摇头天王');
INSERT INTO student VALUES(0,'2021-08-06 07:00:00','黎明','923457',
'13266666666',49,'男','帅气天王');
INSERT INTO student VALUES(0,'2024-08-06 08:00:00','张学友','103457',
'13299999019',47,'男','歌神');

3、查询语句

#1、基础查询
SELECT * FROM student;
#2、分列匿名以及筛选数据查询
SELECT userName as '姓名' ,age 年龄,sex '性别',introduce'个人简介'
FROM student;
WHERE pwd is not null;
#3、去重查询
SELECT DISTINCT sex'性别类型' FROM student;
#4、排序查询
SELECT userName as '姓名',age 年龄,sex '性别',introduce '简介' FROM student ORDER BY age desc;
SELECT*FROM student LIMIT 2;
SELECT*FROM student LIMIT 4,2

4、模糊查询

​


​SELECT * FROM student WHERE userName like'你_'; 
SELECT * FROM student WHERE userName LIKE'刘%';
SELECT * FROM student WHERE pwd is null;
SELECT * FROM student WHERE pwd is not null;
SELECT * FROM student WHERE age BETWEEN 30 AND 40;
SELECT * FROM student WHERE userName IN('刘德华','张翰','刘亦菲');