2021年12月03日问题汇总

MySQL问题

如何创建联合主键

联合主键是什么

数据库的每张表只能有一个主键，不可能有多个主键。所谓的一张表多个主键，我们称之为联合主键。联合主键就是用多个字段一起作为一张表的主键。主键的主键的作用是保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度。

联合主键怎么用

{% note success %} 建表时候已经指定联合主键 {% endnote %}

create table if not exists `course`(
 `course_no` varchar(128)  comment '课程号' not null,
 `teacher_no` varchar(128)  comment '教师号' not null,
 `course_name` varchar(128) comment '课程名称' not null,
 primary key(teacher_no,course_no)
)engine=InnoDB default charset = utf8 ;

mysql插入单条，多条数据


-- 创建课程表 course
create table if not exists `course`(
 `course_no` varchar(128)  comment '课程号' not null,
 `teacher_no` varchar(128)  comment '教师号' not null,
 `course_name` varchar(128) comment '课程名称' not null,
 primary key(`teacher_no`,`course_no`)
)engine=InnoDB default charset = utf8 ;

-- 插入数据

-- 插入多条数据
insert into `course`(course_no,teacher_no,course_name) values
('0001','语文','0002'),
('0002','数学','0001'),
('0003','英语','0003');

-- 插入单条数据
insert into `course` value ('0004','编程学习','0004') ;

-- 查询数据
select * from score ;

删除数据

有三种方案用于删除数据库信息，drop,truncate,delete,三者用于不同场景
drop table table_name 删除表结构数据
truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间，存储引擎不管是 Innodb 和 MyISAM;
delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
<a class="btn" href="#" title="总结">总结</a>

当你不再需要该表时，用 drop;
当你仍要保留该表，但要删除所有记录时，用 truncate;
当你要删除部分记录时，用 delete。

distinct用法

简单说明

distinct一般是用来去除查询结果中的重复记录的，
而且这个语句在select、insert、delete和update中只可以在select中使用

具体用法

-- 这里的expressions可以是多个字段
select distinct expression[,expression...] from tables [where conditions];

实例

-- 测试表
-- SET FOREIGN_KEY_CHECKS=0;执行sql脚本时，不让外键受影响导致出错
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for test_distinct
-- ----------------------------
DROP TABLE IF EXISTS `test_distinct`;
CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `province` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
 
-- ----------------------------
-- Records of test_distinct
-- ----------------------------
INSERT INTO `test_distinct` VALUES ('1', 'BJ', 'BJ', 'houchenggong');
INSERT INTO `test_distinct` VALUES ('2', 'LN', 'DL', 'zhenhuasun');
INSERT INTO `test_distinct` VALUES ('3', 'LN', 'DL', 'yueweihua');
INSERT INTO `test_distinct` VALUES ('4', 'BJ', 'BJ', 'sunzhenhua');
INSERT INTO `test_distinct` VALUES ('5', 'LN', 'TL', 'fengwenquan');
INSERT INTO `test_distinct` VALUES ('6', 'LN', 'DL', 'renquan');
INSERT INTO `test_distinct` VALUES ('7', 'LN', 'DL', 'wuxin');

<a class="btn" href="#" title="作用于单列">作用于单列</a>

-- 测试表
-- SET FOREIGN_KEY_CHECKS=0;执行sql脚本时，不让外键受影响导致出错
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for test_distinct
-- ----------------------------
DROP TABLE IF EXISTS `test_distinct`;
CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `province` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
 
-- ----------------------------
-- Records of test_distinct
-- ----------------------------
INSERT INTO `test_distinct` VALUES ('1', 'BJ', 'BJ', 'houchenggong');
INSERT INTO `test_distinct` VALUES ('2', 'LN', 'DL', 'zhenhuasun');
INSERT INTO `test_distinct` VALUES ('3', 'LN', 'DL', 'yueweihua');
INSERT INTO `test_distinct` VALUES ('4', 'BJ', 'BJ', 'sunzhenhua');
INSERT INTO `test_distinct` VALUES ('5', 'LN', 'TL', 'fengwenquan');
INSERT INTO `test_distinct` VALUES ('6', 'LN', 'DL', 'renquan');
INSERT INTO `test_distinct` VALUES ('7', 'LN', 'DL', 'wuxin');

 -- 作用于单列 
select * from test_distinct ;
select distinct city from test_distinct;

20211204115854

<a class="btn" href="#" title="作用于多列">作用于多列:对多列操作，表示选取多列都不重复的数据，相当于多列拼接的记录的整个一条记录 , 不重复的记录。</a>

-- 测试表
-- SET FOREIGN_KEY_CHECKS=0;执行sql脚本时，不让外键受影响导致出错
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for test_distinct
-- ----------------------------
DROP TABLE IF EXISTS `test_distinct`;
CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `province` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
 
-- ----------------------------
-- Records of test_distinct
-- ----------------------------
INSERT INTO `test_distinct` VALUES ('1', 'BJ', 'BJ', 'houchenggong');
INSERT INTO `test_distinct` VALUES ('2', 'LN', 'DL', 'zhenhuasun');
INSERT INTO `test_distinct` VALUES ('3', 'LN', 'DL', 'yueweihua');
INSERT INTO `test_distinct` VALUES ('4', 'BJ', 'BJ', 'sunzhenhua');
INSERT INTO `test_distinct` VALUES ('5', 'LN', 'TL', 'fengwenquan');
INSERT INTO `test_distinct` VALUES ('6', 'LN', 'DL', 'renquan');
INSERT INTO `test_distinct` VALUES ('7', 'LN', 'DL', 'wuxin');

 -- 作用于单列 
select * from test_distinct ;
select distinct province, city from test_distinct;

20211204120419

20211204120441

注意

{% label primary @distinct 必须放在第一个参数 %}

错误实例用来说明

SELECT  province, DISTINCT city FROM test_distinct;

20211204121336

{% label primary @DISTINCT 表示对后面的所有参数的拼接取不重复的记录，相当于把 SELECT 表达式的项拼接起来选唯一值 %}

SELECT DISTINCT   province，city FROM test_distinct;
-- 期望值:  只对 第一个参数  province 取唯一值。如下图

20211204120441

解决方法

{% label primary @前提说明 %}

问：mysql出现which is not functionally dependent on columns in GROUP BY clause报错

原因：输出的结果是叫target list，就是select后面跟着的字段，还有一个地方group by column，就是 group by后面跟着的字段。由于开启了ONLY_FULL_GROUP_BY的设置，所以如果一个字段没有在target list 和group by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误。 SELECT province , city FROM test_distinct GROUP BY province;


解答：SELECT any_value(province) , any_value(city) FROM test_distinct GROUP BY province;

方法一：利用 group_concat 函数

SELECT  group_concat(distinct any_value(province)) AS province, any_value(city) FROM test_distinct GROUP BY province;

方法二：不利用DISTINCT , 而是利用group by (我认为第一种方法其实就是第二种方法, 第一种方法也就是第二种方法)

SELECT any_value(province) , any_value(city) FROM test_distinct GROUP BY province;

<a class="btn" href="#" title="count统计">count统计</a>
<a class="btn" href="#" title="count统计">注意：注意 COUNT( ) 会过滤掉为NULL 的项</a>


select count(distinct username) from test_distinct ;

20211204230120

<a class="btn" href="#" title="distinct必须放在开头">针对于null处理</a>

-- 测试表
-- SET FOREIGN_KEY_CHECKS=0;执行sql脚本时，不让外键受影响导致出错
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for test_distinct
-- ----------------------------
DROP TABLE IF EXISTS `test_distinct`;
CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `province` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
 
-- ----------------------------
-- Records of test_distinct
-- ----------------------------
INSERT INTO `test_distinct` VALUES ('1', 'BJ', 'BJ', 'houchenggong');
INSERT INTO `test_distinct` VALUES ('2', 'LN', 'DL', 'zhenhuasun');
INSERT INTO `test_distinct` VALUES ('3', 'LN', 'DL', 'yueweihua');
INSERT INTO `test_distinct` VALUES ('4', 'BJ', 'BJ', 'sunzhenhua');
INSERT INTO `test_distinct` VALUES ('5', 'LN', 'TL', 'fengwenquan');
INSERT INTO `test_distinct` VALUES ('6', 'LN', 'DL', 'renquan');
INSERT INTO `test_distinct` VALUES ('7', 'LN', 'DL', 'wuxin');

 -- 空值不过滤 
select * from test_distinct ;
select distinct username from test_distinct ;

20211204225322

<a class="btn" href="#" title="distinct必须放在开头">与all不能同时使用</a>

默认情况下，查询时返回所有的结果，此时使用的就是all语句，这是与distinct相对应的

20211204225811

搭配group by 使用

20211204230344

where和having区别

1、having在聚合(max(target),min(target),sum(target),avg(target),count(*))之后进行过滤，having在分组的时候会用，对分组结果进行过滤，通常分组里面包含聚合函数
2、where搜索条件在分组操作之前应用，having搜索条件在进行分组操作之后应用
！！！having子语句与where子语句区别：
　　where子句在分组前对记录进行过滤；
　　having子句在分组后对记录进行过滤;


select any_value(id),any_value(province),any_value(username),any_value(city) from test_distinct where id > 3 group by province having province like 'B%' ;

[1]:which is not functionally dependent on columns in GROUP BY clause; this is
[2]:distinct详细用法
[3]:个人博客

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,937评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,503评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,712评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,668评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,677评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,601评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,975评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,637评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,881评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,621评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,710评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,387评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,971评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,947评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,189评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,805评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,449评论 2赞 342