該筆記總結了：建表的七大約束、三種刪除語句區(qū)別、修改表小結、解決only_full_group_by報錯

這一塊46道題查缺補漏了我個人很多知識盲區(qū)~? ?感覺像回到了學生時代通過刷題找到不會的知識點，每次遇到不會的題 首先慶幸一下還好不是在高考上才發(fā)現的，再去找一些同類型的題目，通過刷題鞏固知識點，加深記憶！~

第三板塊

SQL1-3? 插入記錄

復制舊表部分內容帶新表：

insert into 新表(列1,列2,...) select 列1,列2,...? from 舊表? where? 篩選條件；

insert into

exam_record_before_2021(uid,exam_id,start_time,submit_time,score)

select uid,exam_id,start_time,submit_time,score

from exam_record where year(start_time)<2021 and score is not null;? ?#sql2

知識點小結：

（一）建表約束

1、主鍵約束

使某個字段不重復且不得為空，確保表內所有數據的唯一性。

create table 表名(

????id int primary key,

????name varchar(20));

2、聯合約束

聯合主鍵中的每個字段都不能為空，并且加起來不能和已設置的聯合主鍵重復。

create table 表名(

????id int ,

????name varchar(20) ,

? ? primary key(id,name)??);

3、自增約束

自增約束的主鍵由系統(tǒng)自動遞增分配。

create table 表名(

????id int primary key auto_increment,

? ? name varchar(20)?);

4、唯一約束

create table 表名(

????id int ,

????name varchar(20) ,

? ? unique (name) );

5、非空約束

約束某個字段不能為空

create table 表名(

????id int ,

????name varchar(20)? not null? );

6、默認約束

約束某個字段的默認值

create table 表名(

????id int ,

? ?name varchar(20)，

? ?age int default 10 );

7、外鍵約束

(1) 主表（父表）classes 中沒有的數據值，在副表（子表）students 中，是不可以使用的；

(2) 主表中的記錄被副表引用時，主表不可以被刪除。

SQL7? ?delete 中l(wèi)imit限制

刪除記錄（二）_?？皖}霸_?？途W

delete from exam_record where submit_time is null

or date_sub(submit_time,interval 5 minute)<start_time

order by start_time asc limit 3;

#limit 0,3 就通過不了..

評論區(qū)解答：delete?是支持?limit?關鍵字的，但僅支持單個參數

在這道題還學到了一個有用的函數：

timestampdiff (minute,? start_time,? submit_time)? <? 5

當然了，date_sub或date_add也是好理解的~

SQL8? ?刪除語句

刪除記錄（三）_?？皖}霸_?？途W

（二）drop table, truncate table, delete table　三種刪除語句的區(qū)別

1.drop table? ? 清除數據并且銷毀表，是一種數據庫定義語言(DDL Data Definition Language), 執(zhí)行后不能撤銷，被刪除表格的關系，索引，權限等等都會被永久刪除。

2.truncate table　只清除數據，保留表結構，列，權限，索引，視圖，關系等等，相當于清零數據，是一種數據庫定義語言(DDL Data Definition Language)，執(zhí)行后不能撤銷。

3.delete table　刪除（符合某些條件的）數據，是一種數據操縱語言(DML Data Manipulation Language)，執(zhí)行后通過事務回滾可以撤銷。

delete? vs? truncate

1.delete 可以加where?條件，truncate?不能加

2.truncate刪除，效率高一些

3.假如要刪除的表中有自增長列，如果用delete?刪除后，再插入數據，自增長列的值從斷點開始，而truncate刪除后，再插入數據，自增長列的值從1開始

4.truncate刪除后沒有返回值，delete?刪除有返回值

5.truncate?刪除后不能回滾，delete?刪除可以回滾

SQL9? ?comment用法：添加字段注釋

創(chuàng)建一張新表_?？皖}霸_?？途W?

create table user_info_vip(

? ? id int primary key auto_increment comment '自增ID',

? ? uid int unique not null comment '用戶ID',

? ? nick_name varchar(64) comment '昵稱',

? ? achievement int default 0 comment '成就值',

? ? level int comment '用戶等級',

? ? job varchar(32) comment '職業(yè)方向',

? ? register_time datetime default current_timestamp comment '注冊時間'

)default? charset = UTF8 ;?

comment 屬性用來添加字段注釋

SQL10? ?alter table 表名 add|modify|drop|change column 字段名 【字段類型】

修改表_?？皖}霸_?？途W

alter table user_info add column school varchar(15) after level;

alter table user_info change column job profession varchar(10);

alter table user_info modify column achievement int(11) default 0;

知識點小結：

語法：alter table 表名 add|modify|drop|change column 字段名 【字段類型】;

①修改字段名

alter table 表 change? column 原列名 現列名 char|int..;

②修改表名

alter table 原表名? rename [TO]? 新表明;

③修改字段類型和列級約束

alter table 表 modify column 列名 date|char|int.. ;

④添加字段

alter table 表? add column 列名 varchar(20) first;

⑤刪除字段

alter table 表?drop column 列名;

注：

1.默認添加一行列的語句是表示在所有列之后添加

2.如果想在指定列后面添加則要使用after xxx(指定列名)

3.如果是在最開始添加，則在最后使用first即可

SQL18? ?解決only_full_group_by報錯

月總刷題數和日均刷題數_?？皖}霸_?？途W

select date_format(submit_time,'%Y%m') as submit_month,

count(submit_time) as month_q_cnt,

any_value(round(count(submit_time)/day(last_day(submit_time)),3))? as avg_day_q_cnt

from practice_record where year(submit_time)=2021

group by date_format(submit_time,'%Y%m')

union

select '2021匯總' as submit_month,

count(*) as month_q_cnt,

any_value(round(count(*)/31,3)) as avg_day_q_cnt

from practice_record

where year(submit_time)=2021

order by submit_month;

①關于解決最新的SQL版本中ONLY_FULL_GROUP_BY報錯的辦法：

ONLY_FULL_GROUP_BY的語義就是確定select 中的所有列的值要么是來自于聚合函數（sum、avg、max等）的結果，要么是來自于group by list中的表達式的值。MySQL提供了any_value()函數來抑制ONLY_FULL_GROUP_BY值被拒絕。

所以只需要在非group by的列上加any_value()就可以了

②給一個任意的日期，查詢這個日期對應的月份有多少天

day(last_day(submit_time))

SQL19? ?group_concat函數

未完成試卷數大于1的有效用戶_?？皖}霸_?？途W

select uid,count(*)-count(submit_time) as incomplete_cnt,

count(submit_time) as complete_cnt,

group_concat(distinct concat(date_format(start_time,'%Y-%m-%d'),':',ei.tag)

? ? ? ? ? ? order by start_time separator ';') as detail

from exam_record er left join examination_info ei

on er.exam_id=ei.exam_id

where year(start_time)=2021

group by uid

having complete_cnt>=1 and incomplete_cnt<5

and incomplete_cnt>1

order by incomplete_cnt desc;

SQL23? ?union排序問題

每個題目和每份試卷被作答的人數和次數_牛客題霸_?？途W

首先明確，union可以使用任何selcet語句，但order by子句只能在最后一次使用

所以，如果想要對未union前兩個sql語句的查詢結果進行排序，分別單獨排序需要的數據，查出以后再使用union連接

select * from (select exam_id as tid,count(distinct ex.uid) as uv,count(*) as pv? from exam_record ex

group by exam_id order by uv desc,pv desc) t1

union

select * from (select question_id as tid,count(distinct pr.uid) as uv,count(*) as pv? from practice_record pr

group by question_id order by uv desc,pv desc) t2

題目來源：?？途W