【简单易懂】MySQL增删改查(进阶)
目錄
1.數(shù)據(jù)庫約束
1.1 約束類型
1.2 NULL約束
1.3 UNIQE:唯一約束
1.4 DEFAULT:默認(rèn)值約束
1.5 PRIMARY KEY:主鍵約束
?1.6 FOREIGN KEY:外鍵約束
2. 表的設(shè)計(jì)
3. 新增
4. 查詢
4.1 聚合查詢
4.1.2 GROUP BY子句
4.1.3 HAVING
4.2 聯(lián)合查詢
4.2.1 內(nèi)連接
4.2.2 外連接
4.2.3 自連接
4.2.4 子查詢
1.數(shù)據(jù)庫約束
1.1 約束類型
NOT NULL - 指示某列不能存儲 NULL 值。 UNIQUE - 保證某列的每行必須有唯一的值。 DEFAULT - 規(guī)定沒有給列賦值時(shí)的默認(rèn)值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的結(jié)合。確保某列(或兩個(gè)列多個(gè)列的結(jié)合)有唯一標(biāo) 識,有助于更容易更快速地找到表中的一個(gè)特定的記錄。 FOREIGN KEY - 保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性。 CHECK - 保證列中的值符合指定的條件。對于 MySQL 數(shù)據(jù)庫,對 CHECK 子句進(jìn)行分析,但是忽略 CHECK 子句。1.2 NULL約束
創(chuàng)建表時(shí),可以指定某列不為空: create table student (id int not null,name varchar(30) );?如果往 id 列插入空值,就直接報(bào)錯(cuò)
1.3 UNIQE:唯一約束
uniqe就是指定列的每一個(gè)數(shù)值為唯一值,不能有重復(fù)。如果往指定的列中插入重復(fù)值也會報(bào)錯(cuò)。
create table student (id int unique,name varchar(30) );?如下列表信息中已經(jīng)表明
1.4 DEFAULT:默認(rèn)值約束
default 就是給插入空數(shù)據(jù)時(shí)指定默認(rèn)值,也可以手動默認(rèn)
create table student (id int,name varchar(30) default,age int );?往表中增加信息
?將name 默認(rèn)為 ‘guangming’
create table student (id int,name varchar(20) default 'guangming' );?
1.5 PRIMARY KEY:主鍵約束
對于一個(gè)表來說,只能有一個(gè)列被指定為主鍵,并且 primary key 是 not null 和 unique 的結(jié)合,確保某列有唯一標(biāo)識,有助于更容易更快速的找到表中的一個(gè)特定的記錄。
確認(rèn)了主鍵,該列既不能為空又不能為重復(fù)值。
如下插入數(shù)據(jù),將 id 列為空,最終顯示結(jié)果為錯(cuò)誤
?插入重復(fù)值,為錯(cuò)誤
?對于主鍵的關(guān)鍵用法就是使用在數(shù)字自增中,auto_ncrement 是主要句子
創(chuàng)建表
create table student (id int primary key auto_increment,name varchar(20) );?插入數(shù)據(jù)
insert into into student (id, name) values (null, 'lisi'), (null, 'zahngsan'), (null,'wnagwu');?在插入數(shù)據(jù)中,并沒有插入 id ,但是表中可以自動生成
?自增主鍵也可以手動指定 id, 但是后序的自增是從指定的 id 開始的
?1.6 FOREIGN KEY:外鍵約束
外鍵用于關(guān)聯(lián)其他表的主鍵或唯一建
?foreign key (字段名) references 主表 (列)
?例如創(chuàng)建一張學(xué)生表和班級表,學(xué)生表就依賴于班級表,學(xué)生表就為‘字表’,班級表就為‘父表’
?先創(chuàng)建一個(gè)班級表
?
?classId 既是 class 表的主鍵,又是? student 表的外鍵
?然后構(gòu)造一個(gè)學(xué)生表,來指定一下 外鍵約束
?往表中插入數(shù)據(jù),其中 classId 為1,在class 表中存在,所以插入成功
?如果我們將classId 為10,在class 表中不存在,所以插入不成功
insert into student values (null, 'wangwu', 10);?
?外鍵約束,同樣也約束著父表,當(dāng)父表中的某個(gè)記錄被子表中依賴著的時(shí)候,進(jìn)行修改或者刪除都會失敗
delete from class where class where classId = 1;?
?外鍵約束工作原理
在子表中插入新的記錄的時(shí)候,就會根據(jù)對應(yīng)的值,在父表中先查詢,查詢到之后,才能夠執(zhí)行后序的插入。
查詢操作就是根據(jù)父表中被依賴的這一列,必須要有索引,即得到 primary key 或者unique?
2. 表的設(shè)計(jì)
2.1 一對一
就是唯一性,例如每個(gè)人只有一個(gè)身份證號
2.2 一對多
以班級為例,一個(gè)班級可以有多個(gè)學(xué)生。
2.3 多對多
以學(xué)生和課程為例,一個(gè)學(xué)生可以選多們課,一門課可以被多個(gè)學(xué)生選
3. 新增
?將查詢到的結(jié)果插入新的表中,但是需要保證查詢到的結(jié)果與插入到新的表中類型匹配,列的順序可以不一樣。
創(chuàng)建 A?表? 、 B表
create table A (id int ,name varchar(20), age int); create table B (id int ,name varchar(20), age int);?往表A中插入數(shù)據(jù)
insert into A values(1, 'lisi', 13), (2, 'wangwu', 15);?將查詢A的結(jié)果插入表B中
insert into B select * from A4. 查詢
4.1 聚合查詢
4.1.1 聚合函數(shù)
常見的統(tǒng)計(jì)總數(shù),計(jì)算平均值等,可以使用聚合函數(shù)來實(shí)現(xiàn),常見的聚合函數(shù)有:
| 函數(shù) | 說明 |
| COUNT([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的數(shù)量 |
| SUM([DISTINCT] expr | 返回查詢到的數(shù)據(jù)的總和,不是數(shù)字沒有意義 |
| AVG([DISTINCT] expr | 返回查詢到的數(shù)據(jù)的平均值,不是數(shù)字沒有意義 |
| MAX([DISTINCT] expr | 返回查詢到的數(shù)據(jù)的最大值,不是數(shù)字沒有意義 |
| MIN([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的最小值,不是數(shù)字沒有意義 |
count 函數(shù) 的用法
查詢? exam 表的人數(shù), count(*)
select * from count(*) from exam;?在 count 時(shí),也可以指定某個(gè)列,如,指定math 列,count(math)
select count(math) from exam;?
?注意,如果表中存在 null ,則是不會被記錄在當(dāng)中的
?sum 求和函數(shù)
將列的若干個(gè)數(shù)值相加
例如,求 math 列的總成績, sum(math)
select sum(math) from exam;?
?sum 函數(shù)只能針對數(shù)字進(jìn)行運(yùn)算,不能針對字符串來進(jìn)行
?例如對name進(jìn)行計(jì)算,sum(name)
select sum(name) from exam;?一個(gè)聚合函數(shù)里面的參數(shù),也可以通過表達(dá)式的方式進(jìn)行運(yùn)算
select sum(chinese) + sum(math) from exam; select sum(chinese + math + english) from exam;?還可以用 as 起別名來顯示;
select sum(chinese + math + english) as total from exam;?聚合函數(shù)還可以搭配 where 字句來使用,可以基于條件進(jìn)行篩選,把篩選結(jié)果再進(jìn)行聚合
?例如,先篩選出 english 大于 85 的,然后將大于 85 的求和
select sum(english) from eaxm where english > 85;4.1.2 GROUP BY子句
SELECT 中使用 GROUP BY 子句可以對指定列進(jìn)行分組查詢。需要滿足:使用 GROUP BY 進(jìn)行分組查詢時(shí),SELECT 指定的字段必須是 “ 分組依據(jù)字段 ” ,其他字段若想出現(xiàn)在 SELECT 中則必須包含在聚合函數(shù)中?例如對 emp 表進(jìn)行分組在聚合函數(shù)查詢
select role, max(salary), min(salary), avg(salary) from emp group by role;?group by 是可以使用 where, 只不過是在分組之前執(zhí)行
?如下,先去掉 name 是 liliu 的,在進(jìn)行對 role 分組
select role, avg(salary) from emp where name != 'liliu' group by role;?
4.1.3 HAVING
GROUP BY 子句進(jìn)行分組以后,需要對分組結(jié)果再進(jìn)行條件過濾時(shí),不能使用 WHERE 語句,而需要用HAVING?如下,先對role 進(jìn)行分組,求avg, 然后執(zhí)行篩選條件 having, 最終將篩選之后的顯示出來。
select role, avg(salary) from emp group by role having avg(salary) < 30;4.2 聯(lián)合查詢
聯(lián)合查詢就是把多個(gè)表的記錄往一起合并,一起進(jìn)行查詢,稱為多表查詢,使用的方法是“笛卡爾積”
運(yùn)算過程: 先拿第A表的第一行記錄與第B表的每行記錄分別組合,得到一行新的記錄,
再繼續(xù)拿A表的記錄和B的記錄重復(fù)執(zhí)行上述的操作。
得到的列數(shù)就是A 的列數(shù) + B的列數(shù)
?例如先單獨(dú)查詢 表 a 和表 student ,
select * from a; select * from student;?進(jìn)行聯(lián)合查詢
語句就是直接 select * from 表名,表名;
表名與表名之間是有逗號隔開
select * from a, student;?加上條件,可以單獨(dú)列出一行的信息,
select * from a, student where a.id = student.studentId;?只保留名字和成績,student.name = exam.name 是兩個(gè)表中名字一樣的
select student. name, exam.math from student,exam where student.name = exam.name and student.classId = 1;?
4.2.1 內(nèi)連接
語法
select 字段 from 表 1 別名 1 [inner] join 表 2 別名 2 on 連接條件 and 其他條件 ; select 字段 from 表 1 別名 1, 表 2 別名 2 where 連接條件 and 其他條件 ; select student. name, exam.math from student join exam on student.name = exam.name and student.classId = 1;?
4.2.2 外連接
外連接分為左外連接和右外連接。如果聯(lián)合查詢,左側(cè)的表完全顯示我們就說是左外連接;右側(cè)的表完全顯示我們就說是右外連接。語法
-- 左外連接,表 1 完全顯示 select 字段名 ? from 表名 1 left join 表名 2 on 連接條件 ; -- 右外連接,表 2 完全顯示 select 字段 from 表名 1 right join 表名 2 on連接條件4.2.3 自連接
自連接是指在同一張表連接自身進(jìn)行查詢。4.2.4 子查詢
子查詢是指嵌入在其他 sql 語句中的 select 語句,也叫嵌套查詢 單行子查詢:返回一行記錄的子查詢 查詢與 “ 不想畢業(yè) ” 同學(xué)的同班同學(xué): select * from student where classes_id=(select classes_id from student where name='不想畢業(yè)'); 多行子查詢:返回多行記錄的子查詢 案例:查詢 “ 語文 ” 或 “ 英文 ” 課程的成績信息 -- 使用IN select * from score where course_id in (select id from course where name='語文'or name='英文');4.2.5 合并查詢
在實(shí)際應(yīng)用中,為了合并多個(gè) select 的執(zhí)行結(jié)果,可以使用集合操作符 union , union all 。使用 UNION 和 UNION ALL 時(shí),前后查詢的結(jié)果集中,字段需要一致。 union 該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí),會自動去掉結(jié)果集中的重復(fù)行。 案例:查詢 id 小于 3 ,或者名字為 “ 英文 ” 的課程: select * from course where id<3 union select * from course where name='英文'; -- 或者使用or來實(shí)現(xiàn) select * from course where id<3or name='英文'; union all 該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí),不會去掉結(jié)果集中的重復(fù)行。 案例:查詢 id 小于 3 ,或者名字為 “Java” 的課程 -- 可以看到結(jié)果集中出現(xiàn)重復(fù)數(shù)據(jù) select * from course where id<3 union all select * from course where name='英文';總結(jié)
以上是生活随笔為你收集整理的【简单易懂】MySQL增删改查(进阶)的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 朴素贝叶斯—印第安人
- 下一篇: 如何设置企业微信中的微盘中的分享链接的有