零基础MySQL8.0（二）DML以及常用SQL记录

CJCChester

已于 2023-10-26 07:35:29 修改

阅读量65

点赞数

分类专栏：数据库文章标签： sql 数据库 mysql database

于 2023-10-15 23:03:13 首次发布

本文链接：https://blog.csdn.net/weixin_50624398/article/details/133834153

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前几天写了个入门记录，到处翻、写一大堆，非常非常麻烦。这里就不做什么了，只做记录和实际操作，哪怕我照搬官方文档也简单得多。

下面直接贴sql，敲过可行。
建数据库、建表

-- 展示所有数据库
-- 显然--是注释，但记得--后加一个空格
-- 显然以;作为结尾
show databases;

-- 建数据库
CREATE DATABASE if NOT EXISTS sql_learn; -- 如果不存在才做

-- 建表
/* 从命令可知，大小写不敏感，但最好分，某些系统可能出错。要么全大写要么全小写，不要混着，这里是错误示范
.是间隔符，获取下一级的表结构
``不是单引号，是反引号，esc下面那个,加在数据库名、表名、字段名子类的地方
也可不加，名字古怪的最好加上让数据库能认 */ -- 很明显/**/是多行注释
create table if not exists `sql_learn`.`imc_class` ( 
	`class_id` int(5) unsigned not null auto_increment comment '课程ID',  -- 整形长度5，非负数，非空，自增，行注释
	`class_name` varchar(10) NOT NULL DEFAULT '' comment '课程名称', -- varchar:长度最大10的变长字符串，默认值空字符串
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP comment '添加课程时间', -- 时间戳类型，默认值为内置的值CURRENT_TIMESTAMP
	primary key(`class_id`) -- 主键，表数据的唯一标识
 ) comment '课程表'; -- 表注释

 -- 看看建表有没
show tables;
-- 使用数据库
use sql_learn; -- 不切换，用.操作也行

新增表数据

-- 新增
-- 注意主键要不同，否则报错；非空且没有默认或自增的不能不填属性值，否则报错
insert into imc_class values(1,'websocket',CURRENT_TIMESTAMP); -- 不标注列，要把所有属性值都对应写上
insert into imc_class(class_id,class_name) values(2,'websocket2'); -- 只写个别属性值，把对应列写上
insert into imc_class(class_name) values ('mysql'),('redis'),('oracle'); -- 也可以一次插入多行
-- 例
CREATE UNIQUE INDEX uni_class_name ON imc_class(class_name); --创建一个唯一索引，此时class_name也不能重复了
insert into imc_class(class_name) values ('mysql'); -- 报错重复
insert into imc_class(class_name) values ('mysql') -- 更新class_name为mysql的数据的时间
	ON DUPLICATE KEY -- 显示影响两行，原因是删除原来新建一条
	UPDATE add_time = CURRENT_TIMESTAMP;

查询表数据，比较复杂了，因为最多的操作就是查询
在这里插入图片描述

# 查询，#也是注释符号，但更多是shell命令的，sql里好像不是很通用，我只知道mysql支持的
select '你好' as nihao, 'hel lo ' 嗨, 3*2; -- 可以获取字符串、计算结果之类；as：别名，可省略
select * from imc_class; -- 获取所有数据的所有属性
select class_id, class_name from imc_class; -- 获取所有数据的这两列

// 过滤，//也是注释，通用性不知道。建议还是--和/**/
select * from imc_class where class_id = 1; -- where条件
select * from imc_class where class_id != 1;
select * from imc_class where class_id >= 4;

select * from imc_class where class_id in (1,2,3); -- in 散列区间，1或2或3
select * from imc_class where class_id not in (1,2);
select * from imc_class where add_time is not null; -- 不要用=，要用is
select * from imc_class where add_time BETWEEN '2023-10-15 00:20:00' AND '2023-10-15 00:37:00'; -- BETWEEN AND 范围区间
select * from imc_class where class_name like "%web%"; -- 查询class_name的值包含web的，%为通配符，表0到多个任意字符

select '我是一个粉刷匠' like '%个%'; // 1处
select '我是一个粉刷匠' not like '%个%'; // 0处
select '我是一个粉刷匠' not like '_个' // 1处，_表单个任意字符

-- 逻辑运算符
create table if not exists course( -- 再建一张课程表
	course_id int(5) unsigned auto_increment, 
	course_name varchar(20),
	stu_cnt int(10) comment '学习人数',
	primary key(course_id)
) comment '课程表';
-- 已经在表里生成了一些数据
select * from imc_class where add_time BETWEEN '2023-10-15 00:20:00' AND '2023-10-15 00:37:00' and class_name like "%web%"; -- and 组合过滤条件
select * from course where stu_cnt > 9000 or course_name like '%mysql_'; -- or 或条件
select * from course -- xor 异或条件，等价于下两条
	where stu_cnt > 9000 xor course_name not like '%sql%';
select * from course -- 等价于上一条 and的优先级高于or
where stu_cnt > 9000 and course_name not like '%sql%'
  or stu_cnt <= 9000 and course_name like '%sql%';
select * from course where stu_cnt > 9000 and course_name not like '%sql%' -- 两个子查询合并，子查询就是查询中有查询，内层的查询结果作为外层查询的数据来源
	union all select * from course where stu_cnt <= 9000 and course_name like '%sql%';

-- 关联表查询 连接 join … on …
-- 建个章节表
create table if not exists chapter(
	chapter_id int(10) unsigned auto_increment,
	course_id int(5), -- 关联course表的主键
	chapter_name varchar(10),
	primary key(chapter_id),
	unique key(course_id, chapter_name) -- 唯一索引、联合索引（复合索引）
	-- 索引（index或者key）就是一个提前建好、以后每次查找可以加快查询速度的东西
	-- 也有多种和对应用途，比如unique index唯一索引就是保证值唯一，插入同样值将报错
) comment '章节表';
-- 老规矩生成了一点数据
-- 查询课程名称包含sql的课程的课程名称和章节名称
SELECT c.course_name '课程名称',ch.chapter_name '章节名称' -- 别名
	FROM course c join chapter ch on c.course_id = ch.course_id -- 联表，同一课程 join默认内连接inner join
	where c.course_name not like '%sql%';
-- 查询课程名称包含sql的课程的课程名称和章节名称
SELECT c.course_name '课程名称',ch.chapter_name '章节名称' -- 别名
	FROM course c left join chapter ch on c.course_id = ch.course_id -- 左连接，包左不包右，左边有就行，右边没有的查出来的列会显示null
	where c.course_name not like '%sql%';
--询出只存在于课程表中，但是不存在于章节表中的课程的课程名称和课程ID信息
SELECT a.course_id,a.course_name
	FROM course a
	WHERE course_id NOT IN (SELECT b.course_id FROM chapter b);
SELECT a.course_id,a.course_name
	FROM course a
	LEFT JOIN chapter b ON b.course_id = a.course_id
	WHERE b.course_id IS NULL;
-- right join就不说了，很少用，就是左连接的相反

-- 注，以下表结构和数据未建立，直接copy命令了，作参考吧

-- 分组和having 
--统计每个分类下不同难度的课程的数量
SELECT level_name,class_name,COUNT(*) -- 聚合函数，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的除了聚合函数的列都应该写在group by后分组，不然分的不对，不是每个分类下不同难度了
--统计每个分类下课程大于3门的难度有那些。
SELECT class_name, 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 class name, level_name HAVING COUNT(*) > 3 -- having对聚合函数列进行过滤，where不行

-- 常用的聚合函数
select count(course_id),count(distinct user_id) from course; -- 查询课程id列不为空的数据条数，用户id去重后的个数
-- 按课程难度级别分组，统计出各难度的总的学习人数
select level_name,sum(study_cut) -- sum是对数值列的值进行求和
	from course a join imc_level b on b.level_id = a.level_id
	group by level_name;
-- 统计出每门课程的平均学习人数
select sum(study_cnt)/count(study_cnt) a, avg(study_cnt) from course; -- avg 用在数值列上求平均值，这里a=b
-- 查询出学习人数最多的课程
select course_name from course where study_cnt = ( select max(study_cnt) from course ); -- max 列的最大值，同理还有求最小值的min函数，不介绍了

-- 排序、分页
select course_name,study_cnt from course order by study_cnt desc; -- 按学习人数降序排序，升序是asc
-- 按学习人数降序查询课程id和课程名称，每页10条，查询第3页
select course_id,course_name from course order by study_cnt desc 
	limit 20,10; -- limit限制返回的行数，两个参数：起始偏移量offset和分页大小pageSize，pageSize * (pageNum - 1) = offset，这里就是 10 * (3-1) = 20;
--