菜鸟的再次MY SOL练习模糊查询等

1制表

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) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT NULL COMMENT '性别',
  `introduce` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

2查询的表达式，如模糊查询、BETWEEN AND

SELECT*FROM student WHERE introduce LIKE '%天%'
SELECT*FROM student WHERE createDate BETWEEN '2024-02-24 00:00:00' and '2024-02-26 10:00:00';
SELECT*FROM student WHERE userName  not in ('女娲','蚩尤','伏羲'); 

3聚合函数

SELECT count(*)'数据总条数' from student WHERE pwd is not null;
SELECT max(age)'年龄最大' from student;
SELECT min(age)'年龄最小'from student;
SELECT avg(age)'平均年龄' from student;
SELECT sum(age)'年龄总和'from student;
SELECT sum(age)/(SELECT COUNT(*)'数据总条数' from student)'平均年龄' from student;