零基础MySQL8.0(二)DML以及常用SQL记录

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

下面直接贴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;
-- 

手敲太慢,下面放截图了
视图view
在这里插入图片描述
select * from vm_course; – 此时返回完整结果

删除
在这里插入图片描述
在这里插入图片描述
create unique index uqx_type_name on imc_type(type_name);

修改
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

常用时间函数
在这里插入图片描述

在这里插入图片描述
秒转时,时转秒
在这里插入图片描述
在这里插入图片描述在这里插入图片描述

在这里插入图片描述

常用的字符串函数
拼接字符串
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
四位小数
在这里插入图片描述
字符串截取
在这里插入图片描述

在这里插入图片描述
从1开始:
在这里插入图片描述
截取,按.分割,右数第2个点后的部分
在这里插入图片描述
locate是定位函数,返回位置,类似编程语言中的indexOf
在这里插入图片描述
去空格,去两边x
在这里插入图片描述

其他常用函数
在这里插入图片描述

在这里插入图片描述

sql高级特性

  1. 公共表表达式(msyql8.0之后)
    union all 如下会有两行重复,子查询就不行
    在这里插入图片描述
    recursive表示自引用,常用作递归。1每次加1直到10

窗口函数
在这里插入图片描述
在这里插入图片描述
排名score:rownumber:相同也会1234,rank相同排名也相同,然后间隔相同数量的名次,denserank相同排名也相同,不间隔
在这里插入图片描述
在这里插入图片描述
再selct * from cnt < 3;
在这里插入图片描述
在这里插入图片描述
这个有点坑,因为不报错,只有select B.A1才报错
在这里插入图片描述

未完待续……

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值