模糊查询与分组查询

CREATE DATABASE Two20210603 DEFAULT CHARSET= utf8;
use Two20210603;

CREATE TABLE student(
`name`VARCHAR(10),
sex char,
age int,
address VARCHAR(100)

);

INSERT into student VALUES("王笑笑","男", 19 ,"许昌"),
("王大","男",20  ,"许昌"),("王二","男", 20 ,"18"),("王八","男",20  ,"许昌"),
("吴用","男", 18 ,"沧州"),("吴优","男", 18 ,"曹县"),("吴晗","男", 45 ,"安阳");

SELECT * from student;
 
-- 模糊查询 like
SELECT * FROM student where `name` LIKE "王%";  -- % 匹配零或者多个字符
SELECT * FROM student where `name` LIKE "王_";   -- _ 匹配一个字符

INSERT into student VALUES ("梅超粉","女", 58 ,"江南"),("梅用","男",56  ,"南京"),
("马超",'男', 25 ,"西凉")
;

SELECT * from student where `name` LIKE "%用";
SELECT * from student where `name` LIKE "%";        # 匹配所有的数据
SELECT * from student where `name` LIKE "_超%" ;
-- 平均数
SELECT avg(age) FROM student;
-- 条数
SELECT count(*) FROM student;
-- 最大值
SELECT max(age) FROM student;
-- 最小值
SELECT min(age) FROM student;
-- 求和
SELECT sum(age) FROM student;



-- 语法

SELECT 字段1，字段2 from 表明 GROUP BY 字段1，字段2 ;  


-- having 分组之后条件的筛选
SELECT 字段1，字段2 from  GROUP BY HAVING 字段(条件);