Mysql自增字段设置+中间表实现联合查询+左外、右外与内连接区别实例讲解

文章目录

一、Mysql自增字段设置

创建好表字段之后修改字段id为自增

alter table role modify id int(12) auto_increment;

创建好表之后没有字段id,但是又想新增id字段为主键自增。

alter table role add id int(12) primary key not null auto_increment;

注意如果需要设置主键，但是表中有主键，需要用drop命令将原来的主键删掉才能设置。

alter table [表名] drop [主键字段];

删除表

drop table if exists [表名];

删除表字段

alter table  [表名] drop column [字段名]

修改表名

alter table [旧表名] rename to [新表名]

查看表结构

desc [表名]

当删除最后记录之后再插入数据设置自增字段id连续(比如我数据库中有5条数据，被我删掉最后3条，剩下两条，那么下次我插入数据期望自增字段从3开始)

ALTER TABLE [表名] AUTO_INCREMENT=3;

二、中间表实现联合查询

mysql中多对多实现需要借助中间表存储其余两张表的主键，并设置外键约束。
下面我们通过一个例子来说明
说明：生活中每个人都有不同的角色，同时一个角色又可以赋予多个人，这样我们建立起一个用户对应多个角色，一个角色对应多个用户的关系。
要求：
1、当我们查询用户信息时候同时期望查询他们对应的角色信息。
2、当我们查询角色信息时候我们期望查询相应的用户信息。

首先创建两张基础表mybatis_user表和mybatis_role表。创建表mybatis_user 表结构如下：
在这里插入图片描述
创建mybatis_role表结构如下：

其中我们可以看到，这两张表并没有任何关联，因此，当我们需要对这两张表进行联合查询的时候，我们就需要借助中间表。创建mybatis_user_role表代码如下：

create table mybatis_user_role(
    uid int(12) not null comment '用户编号',
    rid int(12) not null  comment '角色编号',
    primary key(uid,rid),
    constraint foreign key(rid) references role(id),
    constraint foreign key(uid) references mybatis_user(id),
    engine=InnoDB default charset=utf8);

表结构如下：
在这里插入图片描述
因此，我们要完成第一个问题，也就是要求
sql语句如下：

SELECT mr.id AS rid,mr.role_name,mr.role_desc,mu.* FROM mybatis_user mu
LEFT OUTER JOIN mybatis_user_role mur ON mu.id=mur.uid
LEFT OUTER JOIN mybatis_role mr ON mur.rid=mr.id;

而对于第二个问题实际上就是我们左连接时候的表变换一下位置即可。

SELECT mr.id AS rid,mr.role_name,mr.role_desc ,mu.* FROM mybatis_role mr
LEFT OUTER JOIN mybatis_user_role mur ON mr.id=mur.rid
LEFT OUTER JOIN mybatis_user mu ON mur.uid=mu.id;

当创建好表之后才想起设置外键约束：
alter table 子表的数据表名 add foreign key [子表的外键名称] references 父表的数据表名称 [父表的主键名称];

三、Mysql的左外连接丶右外连接与内连接的区别

此部分内容来自：Mysql的左外连接丶右外连接与内连接的区别

MYSQL中可以通过内外键链接,将有关系的表中数据合并到一起进行条件筛选:首先创建两个新表,数据如下:
student 表：
在这里插入图片描述
score 表：

可以看到students表中stu_id为16048008的记录对应score表没有数据;

1.当进行内连接时,系统会自动忽略两个表中对应不起来的数据:

-- 显示内连接所有数据:
SELECT * FROM students st INNER JOIN score sc ON st.sid=sc.stu_id;

数据太多,只截图最末尾的:
在这里插入图片描述

可以看到,数据只显示到16048007,16048008的并没有显示,所以内连接只显示所有有关联的数据.
外连接分为左连接和右连接：
2.左链接,显示关键词left左边表中的所有数据,右边表数据数据少了补NULL值,数据多了不显示;

-- 左外链接 left
SELECT * FROM students st LEFT JOIN score sc ON st.sid=sc.stu_id;

在这里插入图片描述
　　可以看到,16048008在右边score表中没有数据,系统用NUll补齐,

SELECT * FROM score sc LEFT JOIN students st ON st.sid=sc.stu_id;

在这里插入图片描述
　　上图是我们将students表和score表换了一下位置的运行结果,可以看出,本来右边表16048008记录,因为在左边表中没有数据对应,所以被删除了,可以看出,左链接是以左边表中数据为参照,显示左边表中所有数据,右边表,只显示与左边表对应的数据,少了补NULl,多了删除;
　　同理,右链接是以右边为参照,左边少了补NULL,多了删除,这里就不多说了;
　　以上可以得出:

　　1.内连接,显示两个表中有联系的所有数据;
　　2.左链接,以左表为参照,显示所有数据;
　　3.右链接,以右表为参照显示数据;

四、mysql 新版本出现group by 语句不兼容问题

此部分来自：https://blog.csdn.net/Dream____Fly/article/details/88779630
在mysql8.0以上的版本中，对于 group by 的这种聚合操作，如果在select 中的列，没有在group by 中出现，那么这个SQL是不合法的，因为列不在group by的从句中，所以对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX()，SUM()，ANT_VALUE()的这种聚合函数，才能完成GROUP BY 的聚合操作，那么话说回来了，如何关闭呢？
1.具体出错提示：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1.1 查看sql_mode

select @@global.sql_mode;

1.2 查询出来的值为：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.去掉ONLY_FULL_GROUP_BY，重新设置值。

2.1 上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

set sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

3.修改my.cnf（windows下是my.ini）配置文件，删掉only_full_group_by这一项
若我们项目的mysql安装在ubuntu上面，找到这个文件打开一看，里面并没有sql_mode这一配置项，想删都没得删。

当然，还有别的办法，打开mysql命令行，执行命令
如果 [mysqld] 这行被注释掉的话记得要打开注释。

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后重重启mysql服务