mysql8学习笔记④数据库常用操作之查询
类似print的查询语句
select 'Hello ','Mysql',2020+1;
查询某个表中的所有数据
select * from imc_db.imc_class;
查询指定列
-- 查询出所有课程名中包括MYSQL的课程名称
select title
from imc_course
where title like '%MYSQL%';
-- 学习人数大于1000人的课程都有哪些？
-- 列出他们的课程标题和学习人数
select title,study_cnt
from imc_course
where study_cnt > 1000
比较运算符
用=查询NULL的值查不到数据
# 查询返回结果是否包含其中
select 'this is mysql course' like '%mysql%';
select 'XXXXXMySQL' like '_ysql';
# _下划线表示任意一个字符
select 'MySQL' like '_ysql';
from imc_course
where course_id in (1,3,5,7,9,10);
select title
from imc_course
where course_id not in (1,3,5,7,9,10);
-- 学习人数大于等于1000人并且小于等于2000人的课程都有哪些？
-- 列出他们的课程标题和学习人数
select title,study_cnt
from imc_course
where study_cnt
between 1000 and 2000;
逻辑运算符
-- 查询出课程标题含有MYSQL关键字，并且学习人数大于5000人的课程标题和学习人数
select title,study_cnt
from imc_course
where title like '%mysql%' and study_cnt > 5000
-- 查询出课程标题含有MYSQL关键字，或者学习人数大于5000人的课程标题和学习人数
select title,study_cnt
from imc_course
where title like '%mysql%' or study_cnt > 5000
-- 查询出课程标题含有MYSQL关键字并且学习人数小于5000，
-- 课程标题中不包含MYSQL关键字但学习人数大于5000的课程
-- 课程标题和学习人数
-- 两种思路
-- 写两条sql
select title,study_cnt
from imc_course
where title like '%mysql%' and study_cnt < 5000
union all
select title,study_cnt
from imc_course
where title not like '%mysql%' and study_cnt > 5000;
-- 亦或xor
slect title ,study
from imc_course
where study_cnt > 5000 xor title like '%mysql%'
关联查询
-- 查询出每一门课程的课程ID，课程名称和章节名称
select a.course_id,a.title,b.chapter_name
from imc_course a
join imc_chapter b on a.course_id = b.course_id
-- 以上内关联查询出的是两张表中同时存在的数据，插入一个只存在于imc_course表中的数据
insert into imc_course(title,title_desc,type_id,class_id,level_id,online_time,user_id)
values('mysql关联测试','测试mysql的关联查询',8,1,1,now(),29)
select * from imc_course where title ='mysql关联测试'
此时用内查询是查不到这条数据的
创建索引
use mic_db;
insert into imc_class(class_name) values('mysql')
on duplicate key update add_time=CURRENT_TIME;
select * from imc_class;
create unique index uqx_classname on imc_class(class_name)
# between是包含两个值
select title,study_cnt
from imc_course
where study_cnt between 1000 and 2000;
create table test_is(id int,c1 varchar(10),primary key(id));
insert into test_is values(1,'aa'),(2,NULL),(3,'cc');
# is NULL 才能查出NULL的值
select * from test_is where c1 is NULL;
select * from test_is where c1 is NOT NULL;
# like 是一个或多个任意值，_指一个任意值
select 'this is mysql course' like '%mysql%';
select 'xxxmysql' like '_ysql';
select 'xxxmysql' like '%ysql';
左外关联，简称左连接查询出a表中所有记录和b表中匹配的数据，如果b表中没有查询到数据则为空
-- 使用左关联查询，就可以查到只存在a表中的数据
select a.course_id,a.title,b.chapter_name
from imc_course a
left join imc_chapter b on a.course_id = b.course_id
where title='mysql关联测试';
-- 查询出只存在于课程表中，但是不存在于章节表中的课程的课程名称和课程ID信息
select a.course_id,a.title
from imc_course a where course_id not in (select course_id from imc_chapter);
select a.course_id,a.title
from imc_course a
left join imc_chapter b on a.course_id = b.course_id
where b.course_id is NULL
右关联查询：
和左关联查询刚好对应，此时以右表为准，查询出右表的关联数据如果左表中没有数据则为空，left join查询并集，如果加上where条件可以查询出补集
分组查询
Sql的模式如果是严格模式，分组键除了能出现在group by中，还可以出现在select字句中，所有select中出现的非聚合函数列必须同时出现在group by 中，否则查询出的结果可能不准确，严格模式下会报错
show variables like 'sql_mode';
set global sql_mode='ONLY_FULL_GROUP_BY';
# 统计出每个分类下不同难度的课程数量
select level_name,class_name,count(*)
from imc_course a
join imc_class b on b.class_id=a.class_id
join imc_level c on c.level_id = a.level_id
group by level_name,class_name
select level_name,count(*)
from imc_course a
join imc_class b on b.class_id=a.class_id
join imc_level c on c.level_id = a.level_id
group by level_name
group by和 having字句
-- 统计每个分类下课程大于3门的难度有哪些
select level_name,class_name,count(*) as class_num
from imc_course a
join imc_class b on a.class_id=b.class_id
join imc_level c on a.level_id = c.level_id
group by level_name,class_name
having class_num > 3;