MySQL查詢&約束&多表.png
回顧
1. 數(shù)據(jù)庫(kù)介紹
MySQL軟件-->多個(gè)倉(cāng)庫(kù)-->多張表-->多條記錄
實(shí)體和表關(guān)系
一個(gè)實(shí)體（Student）對(duì)應(yīng)一張表
一個(gè)對(duì)象（new Student）對(duì)應(yīng)一條記錄
2. mysql安裝和使用
軟件有一個(gè)核心配置文件：my.ini
dos窗口操作
登錄
mysql -u用戶名 -p密碼
退出
exit
sqlyog操作
3. SQL
結(jié)構(gòu)化查詢語(yǔ)言春贸，通過它提供的sql語(yǔ)句混萝，可以實(shí)現(xiàn)對(duì)記錄的增刪改查【CRUD】
比如：MySQL特有的注釋 # （方言）
DDL：操作庫(kù)和表
DML：操作數(shù)據(jù)的增刪改
DQL：操作數(shù)據(jù)的查詢
DCL：操作用戶和權(quán)限
TCL：操作事務(wù)相關(guān)
4. 基本練習(xí)
DDL
操作庫(kù)
create database 數(shù)據(jù)庫(kù)名;
drop database 數(shù)據(jù)庫(kù)名;
use 數(shù)據(jù)庫(kù)名;
select database();
操作表
create table 表名(
列名 數(shù)據(jù)類型,
列名 數(shù)據(jù)類型,
列名 數(shù)據(jù)類型
);
varchar(長(zhǎng)度)
decimal(m,n)
show tables;
desc 表名;
drop table 表名;
DML
insert into 表名(字段1,字段2..)values(值1,值2....);
insert into 表名values(值1,值2....);
update 表名 set 字段=值,字段=值 where 條件;
delete from 表名 where 條件;
DQL
select * from 表名;
關(guān)系
邏輯
in關(guān)鍵字
between關(guān)鍵字
空值關(guān)鍵字
MySQL查詢&約束&多表
今日目標(biāo)
1. DQL高級(jí)查詢
2. 數(shù)據(jù)庫(kù)約束
3. 表關(guān)系【重點(diǎn)】
一 DQL高級(jí)查詢
準(zhǔn)備數(shù)據(jù)
-- DQL語(yǔ)句 單表查詢
create database day19;
use day19;
-- 創(chuàng)建表
CREATE TABLE student (
id int,
name varchar(20),
age int,
sex varchar(5),
address varchar(100),
math int,
english int
);
-- 插入記錄
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(1,'馬云',55,'男','杭州',66,78),
(2,'馬化騰',45,'女','深圳',98,87),
(3,'馬景濤',55,'男','香港',56,77),
(4,'柳巖',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'劉德華',57,'男','香港',99,99),
(7,'馬德',22,'女','香港',99,99),
(8,'德瑪西亞',18,'男','南京',56,65),
(9,'唐僧',25,'男','長(zhǎng)安',87,78),
(10,'孫悟空',18,'男','花果山',100,66),
(11,'豬八戒',22,'男','高老莊',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎嶺',66,66),
(14,'蜘蛛精',23,'女','盤絲洞',88,88);
1.1 排序
1. 語(yǔ)法：
select ... from 表名 order by 排序列 [asc|desc],排序列 [asc|dex]
asc：升序 （默認(rèn)值）
desc：降序
2. 注意：
多字段排序，后面的排序結(jié)果是在前面排序的基礎(chǔ)之上
# 排序
-- 查詢所有數(shù)據(jù),使用年齡降序排序
SELECT * FROM student ORDER BY age DESC;
-- 查詢所有數(shù)據(jù),在年齡降序排序的基礎(chǔ)上萍恕，如果年齡相同再以數(shù)學(xué)成績(jī)降序排序
SELECT * FROM student ORDER BY age DESC,math DESC;
1.2 聚合函數(shù)
作用：對(duì)一列數(shù)據(jù)進(jìn)行計(jì)算逸嘀，返回一個(gè)結(jié)果，忽略null值
* 語(yǔ)法：
count(列名)：統(tǒng)計(jì)一列個(gè)數(shù)
max(列名)：求出一列的最大值
min(列名)：求出一列的最小值
sum(列名)：對(duì)一列求和
avg(列名)：求出一列的平均值
# 聚合函數(shù)
-- 查詢學(xué)生總數(shù)（null值處理）
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
SELECT COUNT(*) FROM student;
-- 查詢年齡大于40的總數(shù)
-- 1.1 查詢年齡大于40
SELECT * FROM student WHERE age >40;
-- 1.2 總數(shù)
SELECT COUNT(*) FROM student WHERE age >40;
-- 查詢數(shù)學(xué)成績(jī)總分
SELECT SUM(math) FROM student;
-- 查詢數(shù)學(xué)成績(jī)平均分
SELECT AVG(math) FROM student;
-- 查詢數(shù)學(xué)成績(jī)最高分
SELECT MAX(math) FROM student;
-- 查詢數(shù)學(xué)成績(jī)最低分
SELECT MIN(math) FROM student;
1.3 分組
作用：對(duì)一列數(shù)據(jù)進(jìn)行分組允粤，相同的內(nèi)容分為一組崭倘，通常與聚合函數(shù)一起使用，完成統(tǒng)計(jì)工作
1. 語(yǔ)法：
select 分組列 from 表名 group by 分組列 having 分組后的過濾條件;
2. where和having區(qū)別
where在分組前進(jìn)行條件過濾类垫，不支持聚合函數(shù)
having在分組后今天條件過濾司光，支持聚合函數(shù)
-- 統(tǒng)計(jì)男生和女生各有多少人
-- select count(*) from student where sex ='女'; 我想一條sql語(yǔ)句實(shí)現(xiàn)
# 分組
-- 按性別分組
SELECT sex FROM student GROUP BY sex;
-- 查詢男女各多少人
SELECT sex,COUNT(*) FROM student GROUP BY sex;
-- 查詢年齡大于25歲的人,按性別分組,統(tǒng)計(jì)每組的人數(shù)
-- 1.1 查詢年齡大于25歲的人
SELECT * FROM student WHERE age >25;
-- 1.2 按性別分組
SELECT sex FROM student WHERE age >25 GROUP BY sex;
-- 1.3 統(tǒng)計(jì)每組的人數(shù)
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex;
-- 查詢年齡大于25歲的人,按性別分組,統(tǒng)計(jì)每組的人數(shù),并只顯示性別人數(shù)大于2的數(shù)據(jù)
SELECT sex,COUNT(*) FROM student WHERE age >25 AND COUNT(*) >2 GROUP BY sex; -- 錯(cuò)誤
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex HAVING COUNT(*)>2; -- 正確
1.4 分頁(yè)
1. 語(yǔ)法：
select ... from 表名 limit 開始索引,每頁(yè)顯示個(gè)數(shù);
2. 索引特點(diǎn)：
所以是從0開始，0也是默認(rèn)值悉患，可以省略
3. 分頁(yè)索引公式：
索引 = (當(dāng)前頁(yè)-1) × 每頁(yè)個(gè)數(shù)
# 分頁(yè)
-- 查詢學(xué)生表中數(shù)據(jù)残家，顯示前6條
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 查詢學(xué)生表中數(shù)據(jù)，從第三條開始顯示售躁，顯示6條
SELECT * FROM student LIMIT 2,6;
-- 模擬百度分頁(yè)坞淮，一頁(yè)顯示5條
-- 第一頁(yè)
SELECT * FROM student LIMIT 0,5;
-- 第二頁(yè)
SELECT * FROM student LIMIT 5,5;
-- 第三頁(yè)
SELECT * FROM student LIMIT 10,5;
1.5 知識(shí)小結(jié)
sql語(yǔ)句執(zhí)行順序問題
select * from 表名 where 條件 group by 分組 having 分組后條件 order by 排序 limit 分頁(yè);
二 數(shù)據(jù)庫(kù)約束
2.1 概述
作用
對(duì)表中的數(shù)據(jù)進(jìn)行限定茴晋，保證數(shù)據(jù)的正確性、有效性和完整性回窘。
分類
1. primary key:主鍵約束【掌握】 要求表中有一個(gè)字段 唯一 且 非空诺擅，通常我們使用id作為主鍵
2. unique：唯一約束
3. not null：非空約束
4. default：默認(rèn)值
5. foreign key：外鍵約束
2.2 實(shí)現(xiàn)
2.2.1 主鍵約束
作用：限定某一列的值非空且唯一， 主鍵就是表中記錄的唯一標(biāo)識(shí)毫玖。
1. 設(shè)置主鍵約束
1）創(chuàng)建表
create table 表名(
id int primary key,
...
...
);
2）已有表
alter tabe 表名 add primary key(id);
2. 特點(diǎn)：
一張表只能有一個(gè)主鍵約束，但是我們可以設(shè)置聯(lián)合主鍵（多個(gè)字段）
3. 自增器
1）創(chuàng)建表【掌握】
create table 表名(
id int priamry key auto_increment,
...
...
);
2）特點(diǎn)：自增器起始值為1凌盯，可以手動(dòng)指定
alter table 表名 auto_increment=起始值;
4. 刪除主鍵約束【忘掉....】
語(yǔ)法：
alter table 表名 drop primary key;
1）先移出自增器
alter table stu3 modify id int;
2）才能刪除主鍵約束
alter table stu3 drop primary key;
解釋：因?yàn)橹挥兄麈I約束才有意義設(shè)置自增器...（保證唯一性....）
-- 主鍵約束
-- 給student表添加主鍵約束
ALTER TABLE student ADD PRIMARY KEY(id);
-- 創(chuàng)建表時(shí)指定主鍵約束
CREATE TABLE stu1(
id INT PRIMARY KEY,
`name` VARCHAR(32)
);
-- 插入數(shù)據(jù)測(cè)試
INSERT INTO stu1 VALUES(1,'jack');
-- Duplicate entry '1' for key 'PRIMARY' 錯(cuò)誤：主鍵不能重復(fù)
INSERT INTO stu1 VALUES(1,'lucy');
-- Column 'id' cannot be null 錯(cuò)誤：主鍵不能為空
INSERT INTO stu1 VALUES(NULL,'lucy');
-- 我想讓name字段付枫，也作為主鍵使用...
-- Multiple primary key defined -- 錯(cuò)誤：主鍵被重復(fù)定義了
ALTER TABLE stu1 ADD PRIMARY KEY(`name`);
-- 聯(lián)合主鍵（主鍵字段完全相同，在進(jìn)行約束的限定）
CREATE TABLE stu2(
id INT ,
`name` VARCHAR(32),
PRIMARY KEY(id,`name`)
);
-- 插入數(shù)據(jù)測(cè)試
INSERT INTO stu2 VALUES(1,'jack');
INSERT INTO stu2 VALUES(1,'lucy');
-- Duplicate entry '1-lucy' for key 'PRIMARY' 錯(cuò)誤
INSERT INTO stu2 VALUES(1,'lucy');
-- 自增器
CREATE TABLE stu3(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
-- 插入數(shù)據(jù)測(cè)試
INSERT INTO stu3 VALUES(1,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(3,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(10,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
-- 設(shè)置自增器起始值
ALTER TABLE stu3 AUTO_INCREMENT=1000;
INSERT INTO stu3 VALUES(NULL,'jack');
-- delete（橡皮擦） 和 truncat（撕紙） 區(qū)別
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
-- 1）先移出自增器
ALTER TABLE stu3 MODIFY id INT;
-- 2）才能刪除主鍵約束
ALTER TABLE stu3 DROP PRIMARY KEY;
2.2.2 唯一約束
作用：限定某一列的值不能重復(fù)驰怎，可以出現(xiàn)多個(gè)null
1. 創(chuàng)建表時(shí)設(shè)置唯一約束
create table 表名(
列名 數(shù)據(jù)類型 unique,
...
...
);
-- 唯一約束
CREATE TABLE stu4(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) UNIQUE
);
INSERT INTO stu4 VALUES(1,'jack');
-- Duplicate entry 'jack' for key 'name' 錯(cuò)誤：名稱重復(fù)了
INSERT INTO stu4 VALUES(2,'jack');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);
2.2.3 非空約束
作用：限定某一列的值不能為null
1. 創(chuàng)建表時(shí)設(shè)置非空約束
create table 表名(
列名 數(shù)據(jù)類型 not null,-- 非空約束
列名 數(shù)據(jù)類型 unique not null,-- （唯一+非空）
);
疑問：唯一 + 非空 = 主鍵阐滩？？
回答：不等于县忌，主鍵約束一張表只能有一個(gè)掂榔，唯一+非空 設(shè)置多個(gè)
-- 唯一+非空
CREATE TABLE stu5(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) UNIQUE NOT NULL
);
INSERT INTO stu5 VALUES(1,'jack');
-- Column 'name' cannot be null 錯(cuò)誤：名稱不能為空
INSERT INTO stu5 VALUES(2,NULL);
2.2.4 默認(rèn)值
作用：限定某一列的默認(rèn)值，再?zèng)]有指定的情況下所有列的默認(rèn)值為null
1. 創(chuàng)建表設(shè)置默認(rèn)值
create table 表名(
列名 數(shù)據(jù)類型 default 默認(rèn)值,
...
...
);
-- 默認(rèn)值
CREATE TABLE stu6(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32),
sex VARCHAR(5) DEFAULT '男'
);
INSERT INTO stu6(id,`name`) VALUES(1,'小張');
INSERT INTO stu6(id,`name`,sex) VALUES(2,'小劉','女');
-- 因?yàn)槲覀冎付四J(rèn)值為男症杏，你再插入null装获，會(huì)把默認(rèn)值覆蓋...
INSERT INTO stu6 VALUES(3,'小王',NULL);
三 表關(guān)系【重中之重】
3.1 概述
現(xiàn)實(shí)生活中，（班級(jí)）實(shí)體與（學(xué)生）實(shí)體之間肯定是有關(guān)系的厉颤，那么我們?cè)谠O(shè)計(jì)表的時(shí)候穴豫，就應(yīng)該體現(xiàn)出（班級(jí)）表與（學(xué)生）表之間的這種關(guān)系！
簡(jiǎn)稱：關(guān)系型數(shù)據(jù)庫(kù)（Relation DBMS）
1. 一對(duì)多
應(yīng)用場(chǎng)景：
班級(jí)和學(xué)生逼友、部門和員工
解釋：
一個(gè)班級(jí)下面有多名同學(xué)精肃，多名同學(xué)屬于某一個(gè)班級(jí)
2. 多對(duì)多
應(yīng)用場(chǎng)景：
老師和學(xué)生、學(xué)生和課程
解釋：
一名老師可以教導(dǎo)多名學(xué)生帜乞，一名學(xué)生可以被多個(gè)老師教導(dǎo)
3. 一對(duì)一
應(yīng)用場(chǎng)景：
公民和身份證號(hào)司抱、公司和注冊(cè)地
解釋：
一個(gè)公民只能有一個(gè)身份證號(hào)，一個(gè)身份證號(hào)對(duì)應(yīng)一個(gè)公民
3.2 實(shí)現(xiàn)
3.2.1 一對(duì)多
* 舉例：班級(jí)和學(xué)生
1588132385848.png
-- 創(chuàng)建新庫(kù)
CREATE DATABASE day19_pro;
USE day19_pro;
-- 一對(duì)多
-- 班級(jí)表（主表）
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO class VALUES(1,'java一班');
INSERT INTO class VALUES(2,'java二班');
-- 學(xué)生表（從表）
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32),
class_id INT -- 外鍵字段
);
INSERT INTO student VALUES(1,'流川楓',1);
INSERT INTO student VALUES(2,'櫻木花道',1);
INSERT INTO student VALUES(3,'大猩猩',2);
INSERT INTO student VALUES(4,'赤木晴子',2);
-- 通過班級(jí)找學(xué)生
SELECT * FROM student WHERE class_id =1;
-- 通過學(xué)生找班級(jí)
SELECT * FROM class WHERE id = 2;
-- 給學(xué)生表添加外鍵約束
ALTER TABLE student ADD CONSTRAINT class_id_fk FOREIGN KEY(class_id) REFERENCES class(id);
-- 刪除學(xué)生表的外鍵約束
ALTER TABLE student DROP FOREIGN KEY class_id_fk;
3.2.2 多對(duì)多
* 舉例：學(xué)生和課程
1588142266257.png
-- 多對(duì)多
-- 課程表（主表）
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'ui');
INSERT INTO course VALUES(3,'美容美發(fā)');
INSERT INTO course VALUES(4,'挖掘機(jī)');
-- 中間表（從表）
CREATE TABLE sc(
s_id INT,
c_id INT,
PRIMARY KEY(s_id,c_id)
);
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,2);
INSERT INTO sc VALUES(2,1);
INSERT INTO sc VALUES(2,3);
-- 聯(lián)合主鍵黎烈，可以幫我們校驗(yàn)重復(fù)選修問題
INSERT INTO sc VALUES(1,1);
-- 給中間表增加外鍵約束
ALTER TABLE sc ADD CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT c_id_fk FOREIGN KEY(c_id) REFERENCES course(id);
-- 流川楓不能選修习柠，不存在的課程
INSERT INTO sc VALUES(1,6);
3.2.3 一對(duì)一
- 一對(duì)一關(guān)系在實(shí)際開發(fā)中用的并不多，因?yàn)榭梢赞k關(guān)聯(lián)字段設(shè)計(jì)在同一張表...
* 公司和注冊(cè)地
1588143207597.png
-- 一對(duì)一
-- 公司表
CREATE TABLE company(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32)
);
INSERT INTO company VALUES(1,'拼多多');
INSERT INTO company VALUES(2,'傳智播客');
-- 地址表
CREATE TABLE address(
id INT PRIMARY KEY AUTO_INCREMENT, -- 同時(shí)也作為外鍵
`name` VARCHAR(32),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES company(id)
);
INSERT INTO address VALUES(1,'上海');
INSERT INTO address VALUES(2,'江蘇沭陽(yáng)');
3.3 外鍵約束
作用：限定二張表有關(guān)系的數(shù)據(jù)照棋，保證數(shù)據(jù)的正確性津畸、有效性和完整性
1. 在從表中添加外鍵約束
1）創(chuàng)建表
create table 表名(
列名 數(shù)據(jù)類型,
[constraint] [約束名] foreign key(外鍵列) references 主表(主鍵)
);
2）已有表
alter table 表名 add [constraint] [約束名] foreign key(外鍵列) references 主表(主鍵);
2. 外鍵約束特點(diǎn)
1）主表不能刪除從表已引用的數(shù)據(jù)
2）從表不能添加主表未擁有的數(shù)據(jù)
3）先添加主表數(shù)據(jù)再添加從表數(shù)據(jù)
4）先刪除從表數(shù)據(jù)再刪除主表數(shù)據(jù)
5）外鍵約束允許為空但不能是錯(cuò)的
3. 刪除外鍵約束
alter table 表名 drop foreign key 約束名;
在企業(yè)開發(fā)過程中：傳統(tǒng)的項(xiàng)目中我們需要外鍵約束，互聯(lián)網(wǎng)項(xiàng)目絕對(duì)不用......（性能問題）
3.4 講義中有課下作業(yè)
javaWeb綜合案例黑馬旅游網(wǎng)一部分的數(shù)據(jù)庫(kù)設(shè)計(jì)必怜，放在今天去玩一玩
- 分類和線路是一對(duì)多關(guān)系
- 用戶和線路是多對(duì)多的關(guān)系（favorite 收藏表【中間表】）
