🍀七、数据行操作补充

✨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

【MySQL速通篇001】MySQL主键，自增列，各类索引，外键及变种，分组，连表，数据行操作等知识点 2

🍀七、数据行操作补充

✨7.1、增操作

✨7.2、删操作

✨7.3、改操作

✨7.4、查操作

✨7.5、limit以及order by语句

🎉7.5.1、limit【限制】的用法

🎉7.5.2、order by【排序语句】

🍀八、MySQL分组操作知识点

🍀九、MySQL连表操作

✨9.1、连表操作概念

✨9.2、内连接

✨9.3、外连接

🎉9.3.1、左连接

🎉9.3.1、右连接

🎉9.3.1、全外连接

✨9.4、交叉连接

✨9.5、总结各种连表操作

🍀十、小结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像