2021年12月03日問題匯總

MySQL問題

如何創(chuàng)建聯(lián)合主鍵

聯(lián)合主鍵是什么

數(shù)據(jù)庫的每張表只能有一個主鍵，不可能有多個主鍵。所謂的一張表多個主鍵，我們稱之為聯(lián)合主鍵。聯(lián)合主鍵就是用多個字段一起作為一張表的主鍵。主鍵的主鍵的作用是保證數(shù)據(jù)的唯一性和完整性，同時通過主鍵檢索表能夠增加檢索速度。

聯(lián)合主鍵怎么用

{% note success %} 建表時候已經(jīng)指定聯(lián)合主鍵 {% 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插入單條，多條數(shù)據(jù)


-- 創(chuàng)建課程表 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 ;

-- 插入數(shù)據(jù)

-- 插入多條數(shù)據(jù)
insert into `course`(course_no,teacher_no,course_name) values
('0001','語文','0002'),
('0002','數(shù)學(xué)','0001'),
('0003','英語','0003');

-- 插入單條數(shù)據(jù)
insert into `course` value ('0004','編程學(xué)習(xí)','0004') ;

-- 查詢數(shù)據(jù)
select * from score ;

刪除數(shù)據(jù)

有三種方案用于刪除數(shù)據(jù)庫信息，drop,truncate,delete,三者用于不同場景
drop table table_name 刪除表結(jié)構(gòu)數(shù)據(jù)
truncate table table_name : 刪除表全部數(shù)據(jù)，保留表結(jié)構(gòu)，立刻釋放磁盤空間 ，存儲引擎不管是 Innodb 和 MyISAM;
delete from table_name : 刪除表全部數(shù)據(jù)，表結(jié)構(gòu)不變，對于 MyISAM 會立刻釋放磁盤空間，InnoDB 不會釋放磁盤空間;
<a class="btn" href="#" title="總結(jié)">總結(jié)</a>

  1. 當(dāng)你不再需要該表時， 用 drop;
  2. 當(dāng)你仍要保留該表，但要刪除所有記錄時， 用 truncate;
  3. 當(dāng)你要刪除部分記錄時， 用 delete。

distinct用法

簡單說明

distinct一般是用來去除查詢結(jié)果中的重復(fù)記錄的，
而且這個語句在select、insert、delete和update中只可以在select中使用

具體用法

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

實例

-- 測試表
-- SET FOREIGN_KEY_CHECKS=0;執(zhí)行sql腳本時，不讓外鍵受影響導(dǎo)致出錯
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;執(zhí)行sql腳本時，不讓外鍵受影響導(dǎo)致出錯
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="作用于多列">作用于多列:對多列操作，表示選取 多列都不重復(fù)的數(shù)據(jù)，相當(dāng)于 多列拼接的記錄 的整個一條記錄 , 不重復(fù)的記錄。</a>

-- 測試表
-- SET FOREIGN_KEY_CHECKS=0;執(zhí)行sql腳本時，不讓外鍵受影響導(dǎo)致出錯
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 必須放在第一個參數(shù) %}

錯誤實例用來說明

SELECT  province, DISTINCT city FROM test_distinct;
20211204121336

{% label primary @DISTINCT 表示對后面的所有參數(shù)的拼接取 不重復(fù)的記錄，相當(dāng)于 把 SELECT 表達式的項 拼接起來選唯一值 %}

SELECT DISTINCT   province，city FROM test_distinct;
-- 期望值:  只對 第一個參數(shù)  province 取唯一值。如下圖
20211204120441

解決方法

{% label primary @前提說明 %}

問：mysql出現(xiàn)which is not functionally dependent on columns in GROUP BY clause報錯

原因：輸出的結(jié)果是叫target list，就是select后面跟著的字段，還有一個地方group by column，就是 group by后面跟著的字段。由于開啟了ONLY_FULL_GROUP_BY的設(shè)置，所以如果一個字段沒有在target list 和group by字段中同時出現(xiàn)，或者是聚合函數(shù)的值的話，那么這條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 函數(shù)

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統(tǒng)計">count統(tǒng)計</a>
<a class="btn" href="#" title="count統(tǒng)計">注意：注意 COUNT( ) 會過濾掉為NULL 的項</a>


select count(distinct username) from test_distinct ;
20211204230120

<a class="btn" href="#" title="distinct必須放在開頭">針對于null處理</a>

-- 測試表
-- SET FOREIGN_KEY_CHECKS=0;執(zhí)行sql腳本時，不讓外鍵受影響導(dǎo)致出錯
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>

默認情況下，查詢時返回所有的結(jié)果，此時使用的就是all語句，這是與distinct相對應(yīng)的


20211204225811

搭配group by 使用

20211204230344

where和having區(qū)別

1、having在聚合(max(target),min(target),sum(target),avg(target),count(*))之后進行過濾，having在分組的時候會用，對分組結(jié)果進行過濾，通常分組里面包含聚合函數(shù)
2、where搜索條件在分組操作之前應(yīng)用，having搜索條件在進行分組操作之后應(yīng)用
?。?！having子語句與where子語句區(qū)別：
　　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]:個人博客

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容