MYSQL详解及习题

原创

武汉淘淘 2022-11-16 10:12:30 博主文章分类：知识详解 ©著作权

©著作权归作者所有：来自51CTO博客作者武汉淘淘的原创作品，请联系作者获取转载授权，否则将追究法律责任

常用操作

创建表

CREATE TABLE `xxl_job_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_name` varchar(64) NOT NULL COMMENT '执行器AppName',
  `title` varchar(12) NOT NULL COMMENT '执行器名称',
  `address_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '执行器地址类型：0=自动注册、1=手动录入',
  `address_list` text COMMENT '执行器地址列表，多地址逗号分隔',
  `sync_status` tinyint(4) DEFAULT NULL COMMENT '同步状态 0:同步中 1:成功 2:失败',
  `creator` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `updator` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除（1：是，0：否）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT  CHARSET=utf8mb4 COMMENT='账户表';

新建表时，自动设置创建数据行时间与修改数据行时间

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `task_id` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '任务id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

更新字段

ALTER TABLE 表名 MODIFY COLUMN 列名 datetime DEFAULT CURRENT_TIMESTAMP NOT NULL;
ALTER TABLE 表名 MODIFY COLUMN 列名 datetime DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL;

获得时间

sql 语句

create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01','赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

常用概念

事务

题目

案例

# 选择课程1的学生
select * from sc where sc.CID = '01';

select * from sc where sc.CID = '02';
# 找到课程1的分数大于课程2的分数的学生
select * from student right join (select t1.SID ,t1.CID ,t1.score,t2.CID as 't2.CID',t2.score as 't2.score' from (select * from sc where sc.CID = '01') as t1 ,(select * from sc where sc.CID = '02') as t2 where t1.score>t2.score) r
on student.SID = r.SID;

# 查看某个教师看了哪几门课程
select Tname ,Cname from course,teacher where course.TID = teacher.TID;

# 内联的写法 差集
select * from course inner join teacher t on course.TID = t.TID;

# 查询参加两个课程的学生两门课程的成绩
select Sname ,c.ascore,c.bscore  from student right join (select a.SID,a.CID as acid,a.score as ascore,b.CID as bcid,b.score as bscore from (select * from sc where sc.CID = '01') as a INNER JOIN (select * from sc where sc.CID = '02') as b on a.SID = b.SID) as c on student.SID = c.SID;

# 查询学生不存在" 01 "课程但存在" 02 "课程的情况
select * from sc where  SID not in (select SID from sc where CID = '01') and CID = '02';

# 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
# 查询叙述选择课程的均分   AVING 子句可以让我们筛选分组后的各组数据。
select t1.sid,t1.sname,t2.avgscore from student as t1 inner join (select SID,avg(sc.score) as avgscore from sc group by sc.SID having avgscore >= 60) as t2 on t1.SID = t2.SID;

# distinct 用在
select Sname from student where Sid in (select distinct SID from sc);

#EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
# 查询没有选课的学生是否存在
select * from student where exists (select SID from student where SID not in (select DISTINCT SID from sc));
select Tname from teacher where exists(select * from course where teacher.TID = course.TID );
select  exists(select * from course where TID = '03') as 'flag';
# 返回的之列每一行都会有一个true false true就显示
select Tname from teacher where exists(select * from course where teacher.TID = course.TID);
select Tname from teacher where exists(select * from course where teacher.TID = course.TID and course.TID = '04');
select Tname from teacher where true;

# 查询李老师的数量
select count(*) from teacher where teacher.Tname like '李%';

# 课程按得分进行排列
select * from sc order by score DESC limit 3;

# union 表的使用  将相同的列 合并起来
select SID from student union select SID from sc;


# 查询张三老师教授过的课程
# 张三老师教授过的ID
select TID from teacher where Tname = '张三';
# 01 课程号教授的课程
select CID from course where TID in (select TID from teacher where Tname = '张三');
# 03 学生学习过这个课程号的
select t1.SID from student as t1 right join (select SID, CID,score from sc where CID in (select CID from course where TID in (select TID from teacher where Tname = '张三'))) as t2 on t2.SID = t1.SID;

# 查询什么课都没选的学生成绩
# 查询课程表中存在的学生SID
select DISTINCT SID from sc;
#
select Sname from student where SID not in (select DISTINCT SID from sc);


# 查询两门不及格的学生的成绩
# 查询所有不及格的学生成绩
select SID, cid as 'c' , score from sc where score < 60;
select Sname ,t2.avgscore,t2.c from student t1 right join (select SID, count(cid) as c , avg(score) as avgscore  from sc where score < 60 group by SID) as t2 on t1.SID = t2.SID

# 查询课程1 成绩比课程2 成绩低的学生  展示课程1 成绩 和课程2 成绩
# 查询课程1 的成绩
select * from sc where CID = '01';
select * from sc where CID = '02';

# 学生课程1 成绩 大于课程2 成绩的学生
select t1.SID,t1.CID,t1.score,t2.CID,t2.score from sc t1 right join (select * from sc where CID = '02') t2 on t1.SID = t2.SID where t1.score > t2.score;

# 学生按总成绩排名
# 统计每个学生总成绩
select SID,sum(score) from sc group by SID;

# 查询男女生人数
select Ssex ,count(*) from student group by student.Ssex;

# 查询每个课程的人数
SELECT sc.CId, count(sc.SId)
FROM sc
GROUP BY sc.CID;

# 查询各个学生的年龄段
select SID,Sname,(year(CURDATE())-year(Sage)) as age from student order by age;

# 增加两天
select DATE_ADD(Sage,INTERVAL 2 DAY ) from student;

# 设置变量
set @day = 8-dayofweek(curdate());
select @day;

# for 循环插入数据
# https://www.jianshu.com/p/cb0152efac32
create procedure for_insert(n int)
begin
        declare i int default 1;
        loop_name:loop
            if i>n then
                leave loop_name;
            end if;
            insert into student values (concat(i,'SID'),concat(i,'Sname'),curdate(),'female');
            set i=i+1;
        end loop;
end;
# 执行存储过程
call for_insert(5);
# 删除存储过程
drop procedure if exists  sums;

# 查询本周的生日
select * from student
where date_format(Sage,'%m%d')
between date_format(curdate(),'%m%d')
and date_format(date_add(curdate(),interval @day day),'%m%d');

# 查询本月过生日的学生
select * from student
where month(Sage) = month(curdate())

# 查询下个月过生日的学生
select * from student
where MONTH(Sage) = month(DATE_ADD(CURDATE(),interval 1 month ));

# 查询在 上个月过生日的学习
select * from student
where month(Sage) = month(DATE_SUB(curdate(),interval 1 month ))

# 查询在6月到10月过生日的学生
select * from student
where month(Sage) between '04' and '11';

函数大全

参考

不常用语法

UNION
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
设置变量

# 设置变量
set @day = 8-dayofweek(curdate());
select @day;

循环插入数值

# for 循环插入数据
# https://www.jianshu.com/p/cb0152efac32
create procedure for_insert(n int)
begin
        declare i int default 1;
        loop_name:loop
            if i>n then
                leave loop_name;
            end if;
            insert into student values (concat(i,'SID'),concat(i,'Sname'),curdate(),'female');
            set i=i+1;
        end loop;
end;
# 执行存储过程
call for_insert(5);
# 删除存储过程
drop procedure if exists  sums;