MYSQL基础练习(一)

MySQL基础知识入门(一)
[https://blog.csdn.net/m0_49197858/article/details/122896812?spm=1001.2014.3001.5502]
知识点相关练习

CREATE TABLE student3 (id INT, NAME VARCHAR(20),
age INT , sex VARCHAR(5),address VARCHAR(100),math INT ,
english INT) ;

-- 添加所有的表数据
INSERT INTO student3(id,NAME,age,sex,address,math,english)
VALUES 
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65); 


SELECT 
s3.`id` AS '编号',
s3.`name`'姓名',
s3.`age`'年龄',
s3.`sex`'性别',
s3.`address`'地址',
s3.`math`'数学成绩',
s3.`english`'英语成绩'
FROM student3 AS s3 ;

-- 查询年龄大于等于20的学生信息
SELECT
s.id,
s.name,
s.age,
s.math,
s.english
FROM
student3 AS s

WHERE
age >= 20 ;

-- 查询年龄在20和30岁之间的学生信息(所有信息)
SELECT
*
FROM
	student3

WHERE
age >= 20 AND age <=30 ;

-- 查询两者之间的学生信息  bewteen 值1 and  值2 
SELECT
*
FROM
	student3
WHERE
age
BETWEEN 20 AND 30 ;

-- 查询年龄不是20岁的学生的信息
SELECT
*
FROM
student3
WHERE age <> 20 ;

-- 查询英语成绩是null的学生信息
SELECT
*
FROM
student3
WHERE
english IS NULL ;

-- 查询英语成绩不是null的学生的姓名
SELECT
s.`name`
FROM
student3 s
WHERE
english IS NOT NULL ;

-- 查询年龄是18岁或者20岁或者55岁的学生信息
-- 多个 "或者" 的关系:in(值1,值2,值3) ;
SELECT 
*
FROM
student3
WHERE
age
IN(18,20,55) ;


-- 查询所有的信息,包括数学和英语的总成绩

SELECT
s.`id` 	编号,
s.`name`姓名,
s.`age`年龄,
s.`sex`性别,
s.`address`地址,
(s.math+IFNULL(s.english,0)) 总成绩

FROM
student3 s ;



-- 模糊查询
-- select 指定的字段列表 from 表名 where 字段名称 like '%xx%'

-- 模糊查的匹配符号'%某个字%',模糊匹配包含的xx字的信息(使用最多:模糊检索)
-- 单个字符'%_化',模糊匹配第二个字包含"化"的学生信息

#1 模糊查询所有姓"马"的学生信息

SELECT
*
FROM
student3
WHERE
NAME LIKE '%马%'

#2 模糊查询第二个字包含"化"的学生信息
SELECT
*
FROM
student3
WHERE
NAME LIKE "%_化%" ;


#3 模糊查询名字是三个字的学生信息
-- 这里查询出来一个"柳岩",是因为"岩"字后面有一个空格

SELECT
*
FROM
student3
WHERE
NAME LIKE "___";


-- 聚合函数查询--->查询的结果:是单行单列的

# count(字段):通过指定字段:查询总记录数
# avg(字段):针对某个字段求平均分
# sum(字段):针对某个字段求和
# max(字段):针对某个字段获取里面的最大值
# min(字段):针对某个 字段获取里面的最小值

-- 业务字段的查询总记录数:但是english有一个学生null值
SELECT
COUNT(english) 总人数
FROM
student3

 -- 业务字段的查询总记录数:但是english有一个学生null值
SELECT
COUNT(IFNULL(english,0)) 总人数
FROM
student3

-- 一般都是非业务字段查询下表中的总记录数
SELECT
COUNT(id) 总记录数
FROM
student3

-- 求数学成绩的平均分
SELECT
	AVG(math)'数学平均分'
FROM
student3

-- 求英语平均分
SELECT
	AVG(IFNULL(english,0)) 英语平均分
FROM
student3

-- 求英语总分
SELECT
	SUM(IFNULL(english,0)) 总分
FROM
student3 ;

-- 查询英语最高分
SELECT
	MAX(IFNULL(english,0)) 英语最高分
FROM
student3;

-- select 嵌套select语句
# 需求:查询员工工资最高的员工信息
	#1)查询出来最高工资是多少 max(工资)
	#2)最高工资对应的员工信息
# select * from emp where 工资 = (select max(工资) from emp) ;

-- 排序查询order by 字段名称 (排序规则) asc 或者 desc

-- 按学生的数学成绩进行升序排序
SELECT
s.`id`,
s.`name`,
s.`math`
FROM
student3 s
ORDER BY math;-- 后面没有带排序规则:默认升序排序asc

-- 将数学成绩降序排列
SELECT
s.`id`,
s.`name`,
s.`math`
FROM
student3 s
ORDER BY math DESC ;

-- 英语成绩升序排列
SELECT
*
FROM
student3
ORDER BY IFNULL(english,0) ASC ;

-- 同时先数学升序,英语成绩降序排列
# select 字段列表 from 表名 order by 字段名称1 排序方式1，字段名称 排序方式2

SELECT
*
FROM
student3
ORDER BY math ASC ,
IFNULL(english,0) DESC;

-- 分组查询 group by
-- group by 的后面可以跟的分组字段
-- select 分组字段 , 聚合函数 from 表名 group by 分段字段;

SELECT
sex ,性别
AVG(math) 数学平均分

FROM
student3
GROUP BY -- 分组查询
sex ; -- 分组的字段

-- 注意事项:group by 后面不能跟聚合函数

-- group by 和 where 一块使用
# where 条件必须在group by 之前 (必须先满足条件,才能分组)
SELECT
sex '性别',
AVG(math)'数学平均分'
FROM
student3
WHERE math>70 -- 条件满足
GROUP BY sex ; -- 参与分组

-- having :筛选查询
-- 注意事项:having 后面可以跟聚合函数

# 需求:
	# 按照性别分组,查询他们的对应的平均分
	# 条件:数学成绩不大于70分的人不参与分组 where math > 70 ;
	# 筛选出总人数大于2的信息
SELECT
sex '性别',
AVG(math)'数学平均分'
FROM
student3
WHERE math > 70
GROUP BY sex 
HAVING COUNT(id) > 2 ;


# ----------- 优化-----------------
SELECT
sex '性别',
AVG(math)'数学平均分',
COUNT(id) 人数
FROM
student3
WHERE math > 70 -- 条件满足
GROUP BY sex -- 参与分组

HAVING 人数 > 2 ;



-- 插入几条数据
INSERT INTO student3 VALUES
(9,'张三丰',25,'男','西安',97,87),
(10,'张三',25,'男','西安',57,87),
(11,'德亚',18,'男','南京',56,65); 




-- 分页查询 关键字 limit
-- select 字段列表 from 表名 limit 起始行数,每页显示的条数
# 起始行数从0开始 -- 计算 = (当前页码-1)*每页显示的条数
-- 查询第一页的数据,每页显示两条
 
SELECT
*
FROM
student3
LIMIT 0 , 2 ;

-- 第二页数据

SELECT
*
FROM
student3
LIMIT 2 , 2 ;

-- 第三页数据
SELECT
*
FROM
student3
LIMIT 4 ,2 ;
-- 第四页数据
SELECT
*
FROM
student3
LIMIT 6 ,2 ;
-- 第五页数据
SELECT
*
FROM
student3
LIMIT 8 ,2 ;

-- 第六页数据
SELECT
*
FROM
student3
LIMIT 10 ,2 ;