🍀七、数据行操作补充
✨7.1、增操作
向表的某一行插入数据
insert into 表名(列名1, 列名2) values('行一内容', '行一内容'), ('行二内容', '行二内容')
向表的多行插入数据
insert into 表名(列名1, 列名2) values('行一内容', '行一内容'), ('行二内容', '行二内容')
向某一张表中插入另一张表中的内容
insert into 表一(name,age) select name,age from 表二;
✨7.2、删操作
假设我创建了一张表叫【tb1】其中列名有【name】列和【id】列
# 删除表 delect from tb1 # 带条件的删除 # 把id不等于2的行删除 delete from tb1 where id !=2 delete from tb1 where id =2 delete from tb1 where id > 2 delete from tb1 where id >=2 # 把id > 2，并且name='alex'的数据行删除 delete from tb1 where id >=2 or name='alex'
✨7.3、改操作
同样的使用上面删操作的表
# 把tb1表中的id > 2，并且name='XX'的数据行，的名字设为'alex'，其他的不变 update tb1 set name='alex' where id>12 and name='xx' update tb1 set name='alex',age=19 where id>12 and name='xx'
✨7.4、查操作
基础的查操作
# 查看表中所有数据 select * from tb1; # 查看表中id，name列的数据 select id,name from tb1; select id,name from tb1 where id > 10 or name ='xxx'; # 查看表中id，name列的数据，并将name列名重新取个叫cname的别名 select id,name as cname from tb1 where id > 10 or name ='xxx'; select name,age,11 from tb1;
进阶的查操作
select * from tb1 where id != 1 # 查看id为(1,5,12)中的数的行 select * from tb1 where id in (1,5,12); select * from tb1 where id not in (1,5,12); # 查tb1表中值id为tb11中元素的行 select * from tb1 where id in (select id from tb11) # 查看id为5到12之间数的行 select * from tb1 where id between 5 and 12;
通配符的查操作
# 查询表中以ale开头的所有用户 %表示后面可以有任意多个字符，比如可以匹配到【alex，alexk】 select * from tb1 where name like "ale%" # 查询表中以ale开头的所有用户 _表示后面只能有一个字符，比如【alex】可以匹配到但是【alexxxx】就不可以匹配到 select * from tb1 where name like "ale_"
✨7.5、limit以及order by语句
将上面知识是先看下面的图：
在我们浏览器搜素想要的内容时，返回的结果通常是很多的，如果一次将结果全部显示给你，那么电脑可能会崩溃，这时浏览器就会默认返回结果的前几十条，这种对想要查询结果的条数的限制我们在数据库中也可以使用limit来实现
🎉7.5.1、limit【限制】的用法
# 查看表中的前十条数据 select * from tb1 limit 10; # 从0行开始后面取十条数据 select * from tb1 limit 0,10; select * from tb1 limit 10,10; # 从20行开始后面取十条数据 select * from tb1 limit 20,10; # 从第20行开始读取，读取10行； select * from tb1 limit 10 offset 20;
🎉7.5.2、order by【排序语句】
# 将表tb1按id列从大到小排 select * from tb1 order by id desc; 大到小 【口诀先d后c，d在c后面所以是从大到小】 select * from tb1 order by id asc; 小到大 【口诀先a后c，c在a后面所以是从小到大】 # 将表tb1按age列从大到小排，如果id数值相同就按id列大小从小到大排 select * from tb1 order by age desc,id desc;
拓展要点：取后十条数据
# 实现原理：将tb1表逆序，然后在取前十条数据，这样就相当于取了原表的最后十条数据 select * from tb1 order by id desc limit 10;
🍀八、MySQL分组操作知识点
关键语句：
group by
首先我们按如下的方式创建两张表【department表】【userinfo表】
department表 CREATE table department( id int auto_increment primary key, title varchar(32) )engine=innodb default charset=utf8; userinfo表 CREATE table userinfo( id int auto_increment primary key, name varchar(32), age int, depart_id int, CONSTRAINT fk_usrt_depart FOREIGN key (depart_id) REFERENCES department(id) )engine=innodb default charset=utf8; # 给两张表加数据 # department表 +----+-------+ | id | title | +----+-------+ | 1 | 财务 | | 2 | 公关 | | 3 | 测试 | | 4 | 运维 | +----+-------+ # userinfo表 +----+------+------+-----------+ | id | name | age | depart_id | +----+------+------+-----------+ | 1 | 小费 | 6 | 1 | | 2 | 小港 | 6 | 3 | | 3 | 小干 | 6 | 2 | | 4 | 小刚 | 6 | 4 | | 5 | 小强 | 6 | 4 | | 6 | 小美 | 6 | 4 | | 7 | 小亮 | 6 | 2 | | 8 | 小每 | 6 | 1 | +----+------+------+-----------+
对于语句我就不多解释了，主要看结果就可以了
1、将同一个部门的人放在一起，并且用户部门相同取id值大的用户
SELECT depart_id, max(id) FROM userinfo GROUP BY depart_id;
输出结果：
2、在上面操作的基础上显示各个部门的人数
select count(id),max(id),depart_id from userinfo group by depart_id;
输出结果：
3、如果对于聚合函数结果进行二次筛选时？必须使用having
select count(id),depart_id from userinfo group by depart_id having count(id) > 1;
4、上面的列名为count(id)，这是看着有点不舒服的，我们可以使用as关键字改名
5、进一步的进阶方式
select count(id),depart_id from userinfo where id > 4 group by depart_id having count(id) > 1;
🍀九、MySQL连表操作
✨9.1、连表操作概念
连表顾名思义就是将两张表连在一起查看的操作，操作大的分为两种内连接和外连接，而外连接又分为左连接、右连接和全连接。
- 内连接（inner join）：只包含匹配的记录。
- 外连接（outer join）：除了包含匹配的记录还包含不匹配的记录。{
1. 左连接（left join）：返回匹配的记录，以及表 A 多余的记录。
2. 右连接（right join）：返回匹配的记录，以及表 B 多余的记录。
3. 全连接（full join）：返回匹配的记录，以及表 A 和表 B 各自的多余记录。
}
用网上一张图比较好的图可以更加方便理解如下：
下面我们都用【department表】【user_mess表】来举例：
# department表 +----+-------+ | id | title | +----+-------+ | 1 | 财务 | | 2 | 公关 | | 3 | 测试 | | 4 | 运维 | +----+-------+ # user_mess表 +----+------+-----------+ | id | name | depart_id | +----+------+-----------+ | 1 | 小费 | 1 | | 2 | 小港 | 1 | | 3 | 小干 | 2 | | 4 | 小刚 | 4 | +----+------+-----------+
执行如下语句可以连接两张表：
select * from user_mess,department where user_mess.depart_id = department.id; 输出结果： +----+------+-----------+----+-------+ | id | name | depart_id | id | title | +----+------+-----------+----+-------+ | 1 | 小费 | 1 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | 4 | 小刚 | 4 | 4 | 运维 | +----+------+-----------+----+-------+ 4 rows in set (0.00 sec)
✨9.2、内连接
内连接 语法： a inner join b ，但是一般 inner 可以省略不写，也就是如下形式
select * from a join b ;
执行下面语句：
select * from user_mess join department; 输出结果： +----+------+-----------+----+-------+ | id | name | depart_id | id | title | +----+------+-----------+----+-------+ | 4 | 小刚 | 4 | 1 | 财务 | | 3 | 小干 | 2 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 1 | 小费 | 1 | 1 | 财务 | | 4 | 小刚 | 4 | 2 | 公关 | | 3 | 小干 | 2 | 2 | 公关 | | 2 | 小港 | 1 | 2 | 公关 | | 1 | 小费 | 1 | 2 | 公关 | | 4 | 小刚 | 4 | 3 | 测试 | | 3 | 小干 | 2 | 3 | 测试 | | 2 | 小港 | 1 | 3 | 测试 | | 1 | 小费 | 1 | 3 | 测试 | | 4 | 小刚 | 4 | 4 | 运维 | | 3 | 小干 | 2 | 4 | 运维 | | 2 | 小港 | 1 | 4 | 运维 | | 1 | 小费 | 1 | 4 | 运维 | +----+------+-----------+----+-------+
说明：像这样不加查询条件会形成 笛卡尔积 。笛卡尔积的意思是：是指包含两个集合中任意取出两个元素构成的组合的集合。// 两表分别交叉查询了一遍；也可以加上条件查询条件 on 或者 using ，两者的区别在于 都是查询出符合条件的结果集 ，但是using会优化掉相同的字段。
下面来举个栗子更好理解：
# 使用on语句添加条件 select * from user_mess join department on user_mess.depart_id = department.id; 输出结果： +----+------+-----------+----+-------+ | id | name | depart_id | id | title | +----+------+-----------+----+-------+ | 1 | 小费 | 1 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | 4 | 小刚 | 4 | 4 | 运维 | +----+------+-----------+----+-------+ 4 rows in set (0.00 sec)
这时我们可以发现这与上面的select * from user_mess,department where user_mess.depart_id = department.id;语句输出结果是相同的
✨9.3、外连接
🎉9.3.1、左连接
语法：
# 左连接既 左边 tb_left 表作为基表（主表）显示所有行， tb_right 表作为外表 条件匹配上的就显示，没匹配上的就用 Null 填充 select * from tb_left left join tb_right on tb_left.id = tb_left.id ;
栗子：
select * from user_mess left join department on user_mess.depart_id = department.id; 输出结果： +----+------+-----------+------+-------+ | id | name | depart_id | id | title | +----+------+-----------+------+-------+ | 1 | 小费 | 1 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | 4 | 小刚 | 4 | 4 | 运维 | +----+------+-----------+------+-------+ 4 rows in set (0.00 sec)
🎉9.3.1、右连接
语法：
# 右连接即 右边 tb_right 表作为基表（主表）显示所有行， tb_left 表作为外表 条件匹配上的就显示，没匹配上的就用 Null 填充； 和左连接相反。 select * from tb_left right join tb_right on tb_left.id = tb_left.id ;
栗子：
select * from user_mess right join department on user_mess.depart_id = department.id; 输出结果： +------+------+-----------+----+-------+ | id | name | depart_id | id | title | +------+------+-----------+----+-------+ | 2 | 小港 | 1 | 1 | 财务 | | 1 | 小费 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | NULL | NULL | NULL | 3 | 测试 | | 4 | 小刚 | 4 | 4 | 运维 | +------+------+-----------+----+-------+ 5 rows in set (0.00 sec)
🎉9.3.1、全外连接
语法：
# 经查找发现 MySQL 是不支持所谓 tb_left full join tb_right 语作为 全外连接查询的，想要实现全外连接查询可以通过 union 实现，union 操作符用于合并两个或多个 SELECT 语句的结果集，语句如下： select * from tb_left left join tb_right on tb_left.id = tb_right.id union select * from tb_left right join tb_right on tb_left.id = tb_right.id ;
栗子:
select * from user_mess left join department on user_mess.depart_id = department.id union select * from user_mess right join department on user_mess.depart_id = department.id; 输出结果： +------+------+-----------+------+-------+ | id | name | depart_id | id | title | +------+------+-----------+------+-------+ | 1 | 小费 | 1 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | 4 | 小刚 | 4 | 4 | 运维 | | NULL | NULL | NULL | 3 | 测试 | +------+------+-----------+------+-------+ 5 rows in set (0.00 sec)
值得注意的是：注：当 union 和 all 一起使用时（即 union all ），重复的行不会去除。
栗子：
select * from user_mess left join department on user_mess.depart_id = department.id union all select * from user_mess right join department on user_mess.depart_id = department.id; 输出结果： +------+------+-----------+------+-------+ | id | name | depart_id | id | title | +------+------+-----------+------+-------+ | 1 | 小费 | 1 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | 4 | 小刚 | 4 | 4 | 运维 | | 2 | 小港 | 1 | 1 | 财务 | | 1 | 小费 | 1 | 1 | 财务 | | 3 | 小干 | 2 | 2 | 公关 | | NULL | NULL | NULL | 3 | 测试 | | 4 | 小刚 | 4 | 4 | 运维 | +------+------+-----------+------+-------+ 9 rows in set (0.00 sec)
✨9.4、交叉连接
概念：
交错连接 语法：tb1 cross join tb2 ；交错连接可以加查询条件，也可以不加查询条件，如果不加查询条件会形成 笛卡尔积，类似内连接效果，同样可以使用 using 语句优化字段。
栗子：
select * from user_mess cross join department; 输出结果： +----+------+-----------+----+-------+ | id | name | depart_id | id | title | +----+------+-----------+----+-------+ | 4 | 小刚 | 4 | 1 | 财务 | | 3 | 小干 | 2 | 1 | 财务 | | 2 | 小港 | 1 | 1 | 财务 | | 1 | 小费 | 1 | 1 | 财务 | | 4 | 小刚 | 4 | 2 | 公关 | | 3 | 小干 | 2 | 2 | 公关 | | 2 | 小港 | 1 | 2 | 公关 | | 1 | 小费 | 1 | 2 | 公关 | | 4 | 小刚 | 4 | 3 | 测试 | | 3 | 小干 | 2 | 3 | 测试 | | 2 | 小港 | 1 | 3 | 测试 | | 1 | 小费 | 1 | 3 | 测试 | | 4 | 小刚 | 4 | 4 | 运维 | | 3 | 小干 | 2 | 4 | 运维 | | 2 | 小港 | 1 | 4 | 运维 | | 1 | 小费 | 1 | 4 | 运维 | +----+------+-----------+----+-------+ 16 rows in set (0.00 sec)
✨9.5、总结各种连表操作
1、内连接和交叉连接是十分相似的，只是语句语法有所不同，但最后查询出来的结果集的效果都是一样的，添加条件查询就只查询匹配条件的行，不添加条件查询则形成 笛卡尔积（生成重复多行） 而降低效率。
2、左连接以左边表为基础表 显示所有行 ，右边表条件匹配的行显示，不匹配的则有 Null 代替。
3、右连接以右边表为基础表 显示所有行 ，左边表条件匹配的行显示，不匹配的则有 Null 代替。
🍀十、小结
恭喜你看到了最后，现在看了这么多，不如赶快网上找些题目自己动手实践一波撒😁。
不知道在哪找？放心我帮你找好了👀。
👉 【MySQL练习题】复制链接打开阿里云盘就行了：https://www.aliyundrive.com/s/D24NKjfNpTW
