Mysql学习

已于 2022-02-06 19:59:10 修改
阅读量1.6k 收藏 1
点赞数
分类专栏： 基础环境 文章标签： mysql 数据库 sql
于 2019-09-05 13:56:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44818523/article/details/100556150
版权

一、数据准备

# 创建数据库
create database sql50;
use sql50;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher`  (
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('01', '张三');
INSERT INTO `Teacher` VALUES ('02', '李四');
INSERT INTO `Teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course`  (
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Sage` datetime(0) NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `Student` VALUES ('03', '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `Student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `Student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `Student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `Student` VALUES ('11', '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `Student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `Student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '女');

SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for SC
-- ----------------------------
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of SC
-- ----------------------------
INSERT INTO `SC` VALUES ('01', '01', 80.0);
INSERT INTO `SC` VALUES ('01', '02', 90.0);
INSERT INTO `SC` VALUES ('01', '03', 99.0);
INSERT INTO `SC` VALUES ('02', '01', 70.0);
INSERT INTO `SC` VALUES ('02', '02', 60.0);
INSERT INTO `SC` VALUES ('02', '03', 80.0);
INSERT INTO `SC` VALUES ('03', '01', 80.0);
INSERT INTO `SC` VALUES ('03', '02', 80.0);
INSERT INTO `SC` VALUES ('03', '03', 80.0);
INSERT INTO `SC` VALUES ('04', '01', 50.0);
INSERT INTO `SC` VALUES ('04', '02', 30.0);
INSERT INTO `SC` VALUES ('04', '03', 20.0);
INSERT INTO `SC` VALUES ('05', '01', 76.0);
INSERT INTO `SC` VALUES ('05', '02', 87.0);
INSERT INTO `SC` VALUES ('06', '01', 31.0);
INSERT INTO `SC` VALUES ('06', '03', 34.0);
INSERT INTO `SC` VALUES ('07', '02', 89.0);
INSERT INTO `SC` VALUES ('07', '03', 98.0);

SET FOREIGN_KEY_CHECKS = 1;

二、SQL各种连接查询详解

2.1、交叉连接

-- 交叉连接（不带on子句，返回的是两表的乘积，也叫笛卡尔积）
# 隐式交叉连接 没有cross join
SELECT * from Student , Course WHERE CId = '03';
# 显式交叉连接 有cross join
SELECT * from Student CROSS JOIN Course WHERE CId = '03';

2.2、内连接

# 隐式的内连接，没有inner join，形成的中间表为两个表的笛卡尔积。
SELECT * FROM Student, SC WHERE Student.SId = SC.SId;

# 显示的内连接，一般称为内连接，有inner join，形成的中间表为两个表经过on条件过滤后的笛卡尔积。
SELECT * FROM Student INNER JOIN SC on Student.SId = SC.SId;

2.3、外连接

# 1、左外连接(left outer join) 左表全部数据+右表符合要求数据
SELECT Student.Sname , SC.score from Student LEFT JOIN SC on Student.SId = SC.SId;

# 2、右外连接(left outer join) 左表符合要求数据+右表全部数据
SELECT Student.Sname , SC.score from Student RIGHT JOIN SC on Student.SId = SC.SId;

# 3、全外连接（不重复全集）
# mysql是不支持全外的连接的，这里给出的写法适合oracle和db2。
SELECT Student.Sname , SC.score from Student RIGHT JOIN SC on Student.SId = SC.SId;

# 推荐在写连接查询的时候，on后面只跟连接条件，而对中间表限制的条件都写到where子句中。
SELECT Student.Sname , SC.score from Student LEFT JOIN SC on Student.SId = SC.SId WHERE SC.score>88.0;

两表连接查询选择方式的依据：

1、 查两表关联列相等的数据用内连接。

2、 col_l是col_r的子集时用右外连接。

3、 col_r是col_l的子集时用左外连接。

4、 col_r和col_l彼此有交集但彼此互不为子集时候用全外。

5、 求差操作的时候用联合查询。

三、备

四、sql 50 题

4.1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

# 左连接自交
select *
from Student as s
         right join
     (
         select t1.SId, score_for_c01, score_for_c02
         from (select SId, score as score_for_c01 from SC as sc where sc.CId = 01) as t1,
              (select SId, score as score_for_c02 from SC as sc where sc.CId = 02) as t2
         where t1.SId = t2.SId
           and t1.score_for_c01 > t2.score_for_c02
     ) as r
     on s.SId = r.SId;

# +---+-----+-------------------+----+---+-------------+-------------+
# |SId|Sname|Sage               |Ssex|SId|score_for_c01|score_for_c02|
# +---+-----+-------------------+----+---+-------------+-------------+
# |02 |钱电   |1990-12-21 00:00:00|男   |02 |70.0         |60.0         |
# |04 |李云   |1990-12-06 00:00:00|男   |04 |50.0         |30.0         |
# +---+-----+-------------------+----+---+-------------+-------------+

4.2.查询同时存在"01"课程和"02"课程的情况

# 注意查找效率，每次生成的关系表越小越好
select t1.*, t2.CId, t2.score
from (select * from SC as sc where sc.CId = '01') as t1,
     (select * from SC as sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

# +---+---+-----+---+-----+
# |SId|CId|score|CId|score|
# +---+---+-----+---+-----+
# |01 |01 |80.0 |02 |90.0 |
# |02 |01 |70.0 |02 |60.0 |
# |03 |01 |80.0 |02 |80.0 |
# |04 |01 |50.0 |02 |30.0 |
# |05 |01 |76.0 |02 |87.0 |
# +---+---+-----+---+-----+

4.3.查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)

# 典型的外联结
select t1.*, t2.CId, t2.score
from (select * from SC as sc where sc.CId = '01') as t1
         left join
     (select * from SC as sc where sc.CId = '02') as t2
     on t1.SId = t2.SId;

# +---+---+-----+----+-----+
# |SId|CId|score|CId |score|
# +---+---+-----+----+-----+
# |01 |01 |80.0 |02  |90.0 |
# |02 |01 |70.0 |02  |60.0 |
# |03 |01 |80.0 |02  |80.0 |
# |04 |01 |50.0 |02  |30.0 |
# |05 |01 |76.0 |02  |87.0 |
# |06 |01 |31.0 |NULL|NULL |
# +---+---+-----+----+-----+

4.4. 查询不存在"01"课程但存在"02"课程的情况

# 采用子查询（联结也可以，但本题子查询简单）
select *
from SC as sc
where sc.SId not in (
    select SId
    from SC as sc
    where sc.CId = '01'
)
  AND sc.CId = '02';

# +---+---+-----+
# |SId|CId|score|
# +---+---+-----+
# |07 |02 |89.0 |
# +---+---+-----+

4.5.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

# 聚合函数分组和HAVING条件过滤，获得sid，最后与学生表联结
select s.SId, s.Sname, t.avg_score
from Student as s
         inner join (select SId, avg(score) as avg_score
                     from SC
                     group by SId
                     having avg_score >= 60) as t
                    on s.SId = t.SId;

# +---+-----+---------+
# |SId|Sname|avg_score|
# +---+-----+---------+
# |01 |赵雷   |89.66667 |
# |02 |钱电   |70.00000 |
# |03 |孙风   |80.00000 |
# |05 |周梅   |81.50000 |
# |07 |郑竹   |93.50000 |
# +---+-----+---------+
峭壁雄峰
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL设置utf8mb4编码
一个平凡的程序猿
11-25 1万+
有一个项目需要存储ios的表情(emoji表情) 这种表情虽然是utf8编码，但是一个字符需要占用4个字节，而MySQL utf8编码只能存放3字节的字符。 在MySQL 5.6中，可以设置编码为utf8mb4,这个字符集是utf8的超集。 首先，修改mysql的配置文件 character_set_server=utf8mb4 然后重启数据库和中间件. 点击两个测试的链接
MySQL学习资料PDF
02-11
这份压缩包包含的三本书籍是MySQL学习的重要资源，涵盖了从基础到高级的全方位知识。 《MySQL必知必会》这本书是MySQL入门的经典之作，适合对数据库没有基础知识的读者。它详细介绍了MySQL的基本操作，包括如何创建...
mysql使用utf8mb4经验吐血总结
热门推荐
码瘾的空间
09-26 11万+
mysql使用utf8mb4经验吐血总结 1. utf8 与 utf8mb4 异同 先看 官方手册 https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html 的说明： The character set named utf8 uses a maximum of three bytes per character an...
设置 MYSQL 数据库编码为 utf8mb4
eagle89的专栏
12-31 1万+
UTF8MB4
MYSQL: set names utf8是什么意思?
weixin_30273501的博客
06-14 1108
set names utf8 是用于设置编码，可以再在建数据库的时候设置，也可以在创建表的时候设置，或只是对部分字段进行设置，而且在设置编码的时候，这些地方最好是一致的，这样能最大程度上避免数据记录出现乱码。执行SET NAMES utf8的效果等同于同时设定如下：SET character_set_client='utf8';SET character_set_connection='utf8'...
Mybatis-快速入门
JolyouLu的博客
12-21 167
Mybatis认识 该文档对应的项目都可通过github下载：https://github.com/JolyouLu/mybatis01 传统JDBC的弊端 创建表 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20)...
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
mysql学习用数据库dbt3
05-05
在这个“mysql学习用数据库dbt3”压缩包中，包含了一个名为“dbt3_s1.sql”的文件，这通常是用来初始化或恢复数据库的SQL脚本。 在学习MySQL的过程中，理解数据库的设计和管理至关重要。DBT3提供了一种实践环境，让...
Mysql Mysql学习资料
最新发布
01-13
这份"Mysql Mysql学习资料"涵盖了关于MySQL的基础到高级的知识点，对于想要深入理解数据库管理和开发的人员来说是一份宝贵的资源。 1. **MySQL基础知识** - 数据库概念：理解数据库是什么，以及它在存储和管理数据...
MySQL学习文档
12-05
以上就是根据给定文件信息总结出来的MySQL学习文档中的核心知识点。希望这些内容能帮助初学者更好地理解和掌握MySQL的基础操作。随着对MySQL的深入学习，还可以探索更多高级功能，例如事务处理、存储过程、触发器等...
数据库初学者经典练习题
qq_35649215的博客
03-17 1459
数据库初学者经典练习题
将MySQL的默认字符集设置为utf8或utf8mb4的方法(本文基于MySQL8.0书写，不保证MySQL5.0有效)
luliancsdn的博客
05-17 1万+
MySQL 8.0.23 默认字符集设置方法 方法一：通过修改my.ini文件达到修改目的MySQL 8.0.23的my.ini配置文件位置方法1具体操作方法my.ini文件方法2通过使用MySQL 8.0 Command Line Client达到修改目的具体操作方法 方法一：通过修改my.ini文件达到修改目的 MySQL 8.0.23的my.ini配置文件位置 注意事项： 1.下列方法选择其中一种即可完成配置，但在开始配置之前需要将MySQL80服务关闭，否者可能无法修改my.ini文件 2.在使用下
SQL语句执行顺序
xzkwuli的博客
08-23 362
SQL语句执行顺序学习记录 数据表: student SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO
mysql设置字符集utf8mb4
dqchouyang的专栏
05-22 1万+
为什么要把数据库的字符集设置成utf8mb4呢？以前一直用的都是utf8啊？ 答案在这里：utf8适用于不使用移动设备的互联网交互，utf8mb4适用于当前的移动设备互联网开发，因为移动设备中常常会有表情符号（emoji）的存储，它占用4个字节的存储空间，而utf8是3个字节，这样，用3个字节去存储4个字节的东西，很明显是存不下的，会报错，所以要用utf8mb4，并且utf8mb4是兼容utf8的
数据库配置字符集为utf8mb4 ,且配置文件增加 initconnect='set names utf8mb4' 后仍然报错。解决方案 注意事项
weixin_39724194的博客
02-16 512
增加数据库配置后 initconnect=‘set names utf8mb4’ 仍报错，是因为 源码里 每次会根据 获取的数据库信息判断后， 执行 set names utf8; 参考mysql 官方文档，解决方案 升级版本： 升级后，测试 生僻字多表关联查询，不再报错； 解决后，修改字符集， 部分表 所有列 长度加起来 超过了 65535 字节，也会报错： 是因为utf8 一般一...
mysql设置了utf8mb4不生效的问题解决
Aaron_ik的博客
07-12 1090
connectionInitSqls: set names utf8mb4
mysql set names 命令和 mysql 字符编码问题
weixin_33875564的博客
02-29 172
先看下面的执行结果： (root@localhost)[(none)]mysql>show variables like 'character%'; +--------------------------+-------------------------------------------------------------+ | Variable_name ...
MySQL set names 命令_mysql set names 命令和 mysql 字符编码问题
weixin_35094438的博客
01-27 152
Reference:https://www.cnblogs.com/digdeep/p/5228199.html先看下面的执行结果：([emailprotected])[(none)]mysql>show variables like ‘character%‘;+--------------------------+------------------------------------...
超级简单的sql入门（一）
幽谷虫师
10-10 7496
前言 因为女朋友要做测试了，问我都该学些什么，考虑到循序渐进，网上的教程又不是那么通俗易懂，所以写点sql的教程给女朋友看。 准备 本次使用的数据库为mysql，管理工具为navicat。我新建了两张表，一张学生表，一张成绩表，接下来将通过这两张表来给女朋友讲一下增删改查的操作，首先我们先创建数据库和表，可以通过查询或者管理工具的方式。如果使用cmd终端连接本地mysql数据库的话执行命令 mysql -u root -p 然后会提示输入密码，输入正确密码就连接成功了，如果使用管理工具的话 就配置好直接连
MySQL学习笔记：从基础到高级
"一千行+MySQL+学习笔记.pdf" MySQL是一种广泛应用的关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性受到广大开发者的青睐。这份学习笔记涵盖了MySQL的多个关键知识点，包括基本的SQL语句、索引、数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值