天天看點
傳回

資料庫--mysql

資料庫

    • 1. 資料庫是什麼
      • 1.1資料庫的相關概念
      • 1.2. 為什麼學習資料庫
      • 1.3 資料庫分類
    • 2. Mysql
      • 2.1 文法規範
      • 2.2 注釋
    • 3. DDL
      • 3.1資料庫存儲資料的特點
      • 3.2 表概述
      • 3.4 設計資料庫表
    • 4. DML
    • 5. DQL
      • 5.1DQL
      • 5.2 基礎查詢
    • 6. 多表關聯
      • 關聯的目的
      • 資料庫設計範式
      • 外鍵
      • 關聯查詢
        • 笛卡爾乘積現象
        • 内連接配接(INNER JOIN)
          • 等值連接配接
          • 非等值連接配接
          • 自連接配接
        • 外連接配接
          • 左外連接配接 (LEFT JOIN)
          • 右外連接配接(RIGHT JOIN)

資料庫（DataBase）為了友善資料的存儲和管理，它将資料按照特定的 規則存儲在磁盤上,就是一個存儲資料的倉庫。通過資料庫管理系統，可 以有效的組織和管理存儲在資料庫中的資料

1. 資料庫是什麼

  1. 為了友善資料存儲和管理,将資料存儲到電腦硬碟
  2. 通過DBMS(資料庫管理系統)管理資料

1.1資料庫的相關概念

DB：資料庫（DataBase） 存儲資料的容器，它儲存了一系列有組織的資料。

DBMS：資料庫管理系統（DataBase Management System） 又稱為資料庫軟體或資料庫産品，用于建立或管理DB。

SQL：結構化查詢語言（Structure Query Language） 是一種特殊 目的的程式設計語言，是一種資料庫查詢和程式設計語言，用于存取資料以 及查詢、更新和管理關系資料庫系統

1.2. 為什麼學習資料庫

實作運作時 資料持久化到本地

實作友善資料管理

1.3 資料庫分類

關系型資料庫(RDBMS Relation DataBase Management System )

Oracle (Oracle)

MySQL (Oracle)

SQL Server(微軟)

DB2 (IBM)

非關系型資料庫

Mongdo DB

Redis

2. Mysql

腳本語言,不需要編譯,直接通過某種解釋器解釋執行

2.1 文法規範

  • 不區分大小寫
  • 語句以分号";"結尾
  • 關鍵字大寫
  • 表名/列名小寫

2.2 注釋

單行注釋:

#注釋文字

– 注釋文字(注意-- 與注釋文字之間有空格)

多行注釋:

3. DDL

DDL 資料定義語言(Data Definition Language) 是 用于建立和修改資料庫表結構的語言

常用語句:

  • create 建立
  • drop 删除
  • alter 修改
  • rename 重命名

注意:mysql不能修改資料庫名稱,隻能修改表名稱

3.1資料庫存儲資料的特點

● 将資料放到表中，表再放到庫中

● 一個資料庫中可以有多個表，每個表都有一個名字，用來辨別自己。表名具有 唯一性。

● 表具有一些特性，這些特性定義了資料在表中如何存儲，類似Java中“類”的 設計。

● 表由列組成，我們也稱列為字段。所有表都是由一個或多個列組成的，每一列 類似java中的”屬性”

● 表中的資料是按行存儲的，每一行類似于Java中的“對象”

3.2 表概述

在關系資料庫中，資料庫表是一系列二維數組的集合，用來代表和儲存資料對象之間的關系。它由縱向的列和橫向的行組成

表是資料庫基本存儲機關

記錄: 一行資訊

字段: (列),表示存儲何種資訊

3.4 設計資料庫表

存儲資料之前,要先建立資料庫,再建立表

/*
	建立資料庫
CREATE DATABASE [IF NOT EXISTS] 資料庫名 [CHARSET 字元集名]
	
   删除資料庫
   DROP DATABASE 資料庫名;
   DROP DATABASE  IF EXISTS 資料庫名;
   
   修改資料庫字元集
   ALTER DATABASE 資料庫名 CHARSET 字元集名;
*/
           
-- 不存在就建立資料庫
CREATE DATABASE IF NOT EXISTS school_db; 
-- 不存在就建立指定字元集的資料庫
CREATE DATABASE IF NOT EXISTS sqlname3 CHARSET utf8;

-- 将要執行的語句全部選中,點選執行查詢(F9  SQLyog)
資料庫--mysql
-- 修改資料庫字元集
ALTER DATABASE school_db CHARSET gbk;
-- 删除資料庫
DROP DATABASE sqlname3;
資料庫--mysql

在給表添加元素時,我們先了解一下mysql中的資料類型

  • char(n) 長度為n的定長字元串 (如n=4,即時存儲一個字元,也占4個長度)
  • varchar(n) 長度為0~n的長度可變字元串
  • date 日期 年月日
  • datetime 時間 年月日,時分秒

浮點型

float(M,D) 4位元組

double(M,D) 8位元組

M:資料總長度

D:小數點後長度

eg . 10.15 >> (4,2)

整型

TINYINT: 1位元組

SMALLINT: 2位元組

MEDIUMINT: 3位元組

INT: 4位元組

BIGINT: 8位元組

BLOB :大文本,存儲圖檔,新聞

資料庫已經建立好,現在建立表

/*
	建立表
	CREATE TABLE 表名(列名 資料類型 [限制] [預設值] [ 注釋]...)
*/
-- 建立基礎表
CREATE TABLE student(
	-- 學号
	xuehao INT,
	-- 姓名	
	NAME VARCHAR(4),
	-- 性别
	sex CHAR(1),
	-- 生日
	birthday DATE,
	-- 身高(175.5)
	height FLOAT(4,1),
	-- 電話()
	phone INT(11),
	-- 登記時間
	register_time DATETIME
)

點選執行,運作無錯誤時,得到一個表檔案

資料庫--mysql
資料庫--mysql
-- 删除表
DROP TABLE student;
資料庫--mysql

可以看到表從資料庫school_db裡已經删除

-- 修改表名
RENAME TABLE student TO student1;
RENAME TABLE student1 TO student;
資料庫--mysql
-- 複制表結構
CREATE TABLE t_student1 LIKE student;
資料庫--mysql

PRIMARY KEY AUTO_INCREMENT主鍵自增

限制

PRIMARY KEY 主鍵限制

主鍵限制 不能為空,不能重複

check() 檢查限制

NOT NULL 不為空限制

UNIQUE 唯一性

DEFAULT ‘預設值’

字段注釋 comment’注釋’

#修改表結構
-- 添加主鍵限制
ALTER TABLE t_student1 ADD PRIMARY KEY(xuehao);
-- 删除主鍵限制(删除不寫主鍵列名,表中隻有一個主鍵)
ALTER TABLE t_student1 DROP PRIMARY KEY;

-- 設定主鍵自增
ALTER TABLE t_student1 MODIFY xuehao INT AUTO_INCREMENT;
-- 删除主鍵自增
ALTER TABLE t_student1 MODIFY xuehao INT;

-- 設定不能為空or可以為空
ALTER TABLE t_student1 MODIFY NAME VARCHAR NOT NULL;
ALTER TABLE t_student1 MODIFY sex CHAR NOT NULL;

-- 添加唯一限制
ALTER TABLE t_student1 ADD CONSTRAINT unique_phone UNIQUE(phone);
-- 删除唯一限制
ALTER TABLE t_student1 DROP INDEX unique_phone;

-- 添加/删除檢查限制
ALTER TABLE t_student1 ADD CONSTRAINT check_height CHECK(height>100);
ALTER TABLE t_student1 DROP CHECK  check_height;

為phone添加唯一限制後,輸入相同的字元串,會報錯

資料庫--mysql

檢查限制: CHECK(height>100),隻有輸入資料大于100,才不會報錯

資料庫--mysql
-- 添加列
ALTER TABLE t_student ADD weight FLOAT(4,1) FIRST; -- 添加到最前面
ALTER TABLE t_student ADD weight FLOAT(4,1) AFTER height; -- 向指定元素後加 
ALTER TABLE t_student ADD weight FLOAT(4,1); -- 向末尾加

-- 删除列
ALTER TABLE t_student DROP weight;

-- 修改列名
ALTER TABLE t_student CHANGE phone mobile_phone VARCHAR(11);

-- 修改列的資料類型
ALTER TABLE t_student MODIFY xuehao VARCHAR(10);
資料庫--mysql

4. DML

DML資料操作語言 （Data Manipulation Languag )

insert插入 delete 删除 update 修改

now() : 擷取資料庫所在系統的目前時間

-- 插入方式1
INSERT INTO t_student1(xuehao,NAME,sex,birthday,height,phone,register_time)
		VALUES(1001,'張一','男','2010-1-1',185.3,'1234567891',NOW());
		    
-- 插入方式2
INSERT INTO t_student1(xuehao,NAME,sex,birthday,height,phone,register_time)
		VALUES(1002,'張三','男','2001-1-1',175.3,'1234567892',NOW()),
		      (1003,'李四','女','2002-1-1',165.3,'1234567893',NOW());

-- 插入方式3
INSERT INTO t_student1 SET xuehao=1004,NAME='王五',sex='男',birthday='1999-1-1',
height=165.2,phone='12345678902',register_time=NOW();
資料庫--mysql
-- 複制表結構
CREATE TABLE stdi LIKE t_student1;

-- 複制表資料
INSERT INTO stdi(xuehao,NAME,sex,birthday,height,phone,register_time)
		SELECT xuehao,NAME,sex,birthday,height,phone,register_time FROM t_student1

INSERT INTO stdi SET xuehao=1005,NAME='趙六',sex='男',birthday='1999-5-9',
height=178.2,phone='1234567894',register_time=NOW();


-- 修改語句
UPDATE stdi SET NAME='Tom',sex='女' WHERE xuehao = 1001 -- 修改時要注意條件

-- 删除語句
DELETE FROM stdi WHERE xuehao = 0;
DELETE FROM stdi WHERE xuehao = 1;
-- 清空整張表
TRUNCATE TABLE stdi;

與上一張被複制的表比較,新增了一行,删除了學号為0,1的兩行,将學号1001的資訊改變

資料庫--mysql

5. DQL

5.1DQL

（Data Query Language） 資料查詢語言

基礎查詢,使用頻率最高的操作

文法:

select 查詢清單(結果) from 表名

select 結果 form 表名 where 條件 排序 分組 行數限制 子查詢

-- 查詢常量
SELECT 1001 FROM stdi;
SELECT 10*10 FROM stdi;
SELECT height-5 FROM stdi;

-- 查詢版本号
SELECT VERSION();
-- 查詢name列的長度
SELECT CHAR_LENGTH(NAME) FROM stdi;

-- 查詢所有列
SELECT * FROM stdi;

-- 查詢特定列
SELECT NAME FROM stdi;

-- 去除重複資料
SELECT DISTINCT * FROM stdi;
-- 去除指定資料的重複資料
SELECT DISTINCT sex FROM stdi;

​ 查詢軟體版本

資料庫--mysql

​ 查詢姓名列的長度

資料庫--mysql

​ 查詢所有列

資料庫--mysql

​ 查詢姓名列

資料庫--mysql

​ 去除重複資料

資料庫--mysql
  • 單行函數–字元函數 
length()：擷取參數值的位元組個數
char_length()擷取參數值的字元個數
concat(str1,str2,.....)：拼接字元串
upper()/lower()：将字元串變成大寫/小寫
substring(str,pos,length)：截取字元串 位置從1開始
instr(str,指定字元)：傳回子串第一次出現的索引，如果找不到傳回0
trim(str)：去掉字元串前後的空格或子串,trim(指定子串 from 字元串)
lpad(str,length,填充字元)：用指定的字元實作左填充将str填充為指定長度
rpad(str,length,填充字元)：用指定的字元實作右填充将str填充為指定長度
replace(str,old,new)：替換，替換所有的子串
           
/*
	使用函數對查詢的結果進行處理
	函數: 
	單行函數 :對查詢的每行資料進行操作
	分組函數(聚合函數): 多行轉為一行
	
*/

-- 查詢位元組數     漢字3位元組,英文字元一位元組
SELECT LENGTH(NAME) FROM stdi;

-- 查詢字元數     漢字=字元=1
SELECT CHAR_LENGTH(NAME) FROM stdi;

-- 拼接字元串   as 設定别名
SELECT CONCAT(NAME,':',sex,':',height) FROM stdi;
SELECT CONCAT(NAME,':',sex,':',height) NAME  FROM stdi;
SELECT CONCAT(NAME,':',sex,':',height) AS NAME  FROM stdi;


-- 大寫upper小寫lower
SELECT UPPER(NAME) FROM stdi;
SELECT LOWER(NAME) FROM stdi;


-- 截取字元串  substring(列名,開始位置(1),截取長度)
SELECT SUBSTRING(NAME,1,1) FROM stdi;

-- instr(str,子串) 傳回子串第一次出現的索引，如果找不到傳回0  類似java中的indexOf()
SELECT INSTR(NAME,'1') FROM stdi

-- trim 去掉字元串前後空格或子串
-- trim(列名)  trim(子串 from 列名)
SELECT xuehao CHAR_LENGTH(TRIM (NAME)) FROM stdi;
SELECT xuehao TRIM ('4' FROM NAME) FROM stdi;

-- lpad(列名,指定長度,填充的子串) 左填充 rpad 右填充
SELECT LPAD(NAME,4,'0') FROM stdi;
SELECT RPAD(NAME,4,'~') FROM stdi;

-- replace(列,old,new) 替換所有子串
SELECT REPLACE(sex,'男','女') FROM stdi;
  • 單行函數–邏輯函數:

case when 條件 then 結果1 else 結果2 end; 可以有多個when

if null

if (條件,結果1,結果2)

以上表為例

SELECT 
	NAME,
	(CASE WHEN YEAR(birthday)>1990 AND YEAR(birthday)<2000 THEN '90後' 
		WHEN YEAR(birthday)>2000 AND YEAR(birthday)<2010 THEN '00後'
		ELSE '10後'
		END)年齡段
FROM stdi;
資料庫--mysql
  • 單行函數–數學函數 
round(數值)：四舍五入
ceil(數值)：向上取整，傳回>=該參數的最小整數
floor(數值)：向下取整，傳回<=該參數的最大整數
truncate(數值,保留小數的位數)：截斷，小數點後截斷到幾位
mod(被除數,除數)：取餘，被除數為正，則為正；被除數為負，則為負
rand()：擷取随機數，傳回0-1之間的小數
           
-- 四舍五入
SELECT ROUND(height) FROM stdi;
-- 向上取整 ，傳回>=該參數的最小整數
SELECT CEIL(5.5) FROM stdi;
-- 向下取整 , 傳回<=該參數的最大整數
SELECT FLOOR(5.5) FROM stdi;
-- 截斷(小數點後截斷幾位)
SELECT TRUNCATE(5.1234,2) FROM stdi;
-- 取餘，被除數為正，則為正；被除數為負，則為負
SELECT MOD(5,2) FROM stdi;
-- 擷取随機數，傳回0-1之間的小數
SELECT RAND() FROM stdi;
  • 單行函數–日期函數 
/*
	日期函數
*/
-- now 目前日期加時間
SELECT NOW()
SELECT CURDATE()
SELECT CURTIME()
-- 年月日時分秒,必須有列名參數
SELECT YEAR(birthday) FROM stdi;
SELECT MONTH(birthday) FROM stdi;
SELECT DAY(birthday) FROM stdi;
SELECT HOUR(register_time) FROM stdi;
SELECT MINUTE(register_time) FROM stdi;
SELECT SECOND(register_time) FROM stdi;

-- str_to_date(str,格式)字元串轉為指定格式的日期
SELECT STR_TO_DATE('2021-7-6','%Y-%m-%d')

-- date_format(列,格式)格式化
SELECT DATE_FORMAT(birthday,'%Y-%m')FROM stdi;`t_student1`
SELECT COUNT(*),DATE_FORMAT(birthday,'%Y-%m')birth FROM stdi GROUP BY birth -- count分組

-- datediff(big,small) 計算兩個時間相差天數
SELECT DATEDIFF(CURDATE(),birthday) FROM stdi-- 計算從出生到現在的天數

日期格式

格式 含義
%Y 年,四位
%m 月，數值(00-12)
%d 月的天，數值(00-31)
%H 小時 (00-23
%i 分鐘，數值(00-59)
%s 秒(00-59)
%f 微秒
%T 時間, 24-小時 (hh:mm:ss)
%j 年的天 (001-366
%w 周的天 （0=星期日,6=星期六)

分組函數

/*
	分組函數
	sum(數值類型的列)  	求和
	avg(數值類型的列)   	求平均值
	max(列)		   	求最大值
	min(列)		    	求最小值
	count(* / 主鍵/ 指定列)	    計數
	和分組函數一同查詢的字段要求是group by 後的字段
*/

-- 全部求和
SELECT SUM(height) FROM stdi;

-- 分組求和
SELECT SUM(height),sex FROM stdi GROUP BY sex;

-- 平均值
SELECT AVG(height) FROM stdi;

-- 最大值
SELECT MAX(height) FROM stdi;
-- 最小值
SELECT MIN(height) FROM stdi;

-- 統計數量
SELECT COUNT(*) FROM stdi;

5.2 基礎查詢 

/*
	條件查詢
	文法:  select 結果 from 表名 where 條件
	比較:
	等于= 
	不等于!=,<>
	其他> < >= <=
	
*/

#               比較
-- 等于
SELECT NAME FROM stdi WHERE sex='男' 

-- 不等于
SELECT NAME FROM stdi WHERE sex!='男' 
SELECT NAME FROM stdi WHERE sex<>'女' 

-- > < >= <=
SELECT NAME FROM stdi WHERE height>170
SELECT NAME FROM stdi WHERE height<170
SELECT NAME FROM stdi WHERE weight>=100
SELECT NAME FROM stdi WHERE weight<=100


#             邏輯:  and(與) or(或) not(非) 

--  and   and可以連接配接多個條件,多個條件必須同時成立
SELECT NAME FROM stdi WHERE sex='男' AND weight<100

-- or 
SELECT NAME FROM stdi WHERE sex='男' AND weight>100 OR sex='女'
SELECT NAME FROM stdi WHERE height=185.3 OR height=165.3

-- not 
SELECT NAME FROM stdi WHERE  NOT weight=100

模糊查詢

/*
	模糊查詢
	LIKE ：是否比對于一個模式 一般和通配符搭配使用，可以判斷字元型數值或數值型
	通配符:%任意多個字元,_任意單個字元
	in   判斷某字段是否屬于in清單的某一列
	is null   為空
	is not null  不為空
	between A and B   A~B之間
	
*/			
-- is null    is not null
SELECT NAME FROM stdi WHERE weight IS NULL 
SELECT NAME FROM stdi WHERE weight IS NOT NULL 

-- in(1,2,3) 是1 or 2 or 3    not in(1,2,3) 不是1 and 2 and 3
SELECT NAME FROM stdi WHERE height IN(185.3,165.3)
SELECT * FROM stdi WHERE height NOT IN(185.3,165.3)

-- between A and B 兩者之間
SELECT NAME FROM stdi WHERE height BETWEEN 170 AND 180

-- 通配符 % 任意    _ 一個        
SELECT NAME FROM stdi WHERE NAME LIKE '張%'
SELECT NAME FROM stdi WHERE NAME LIKE '_T__'

union & union all

#             union 和  union all

-- union 将多條查詢結果合并,去掉重複行
SELECT NAME FROM stdi WHERE sex='男'
	UNION
SELECT NAME FROM stdi WHERE height>170

-- union all 不會去掉重複資料
SELECT NAME FROM stdi WHERE sex='男'
	UNION ALL
SELECT NAME FROM stdi WHERE height>170

排序 order by

/*
	排序
	order by 子句 [desc/asc]
	desc 降序
	acs  升序
	不寫預設升序
	
	子句可以是單個或多個字段,表達式,函數,别名
	
*/
-- 預設升序
SELECT * FROM stdi ORDER BY birthday;
-- 有條件時,排序在條件之後,對篩選後的結果排序
SELECT * FROM stdi WHERE sex='男' ORDER BY birthday DESC;
-- 多個字段進行排序,第一個相同時,使用第二個字段排序
SELECT * FROM stdi ORDER BY CHAR_LENGTH(NAME),height;

limit 限制數量

/*
	數量限制
	放在sql語句末尾
	limit(開始位置,查詢數量)
*/
SELECT * FROM stdi WHERE sex='男' ORDER BY birthday DESC LIMIT 0,2

分組統計

/*
	分組統計
	文法:
    select 分組函數，列（要求出現在group by的後面）
    from 表
    [where 篩選條件]
    group by 分組的清單
    [having 分組後的篩選]
    [order by 子句]
*/
-- 查詢男生女生人數
SELECT sex,COUNT(*) FROM stdi GROUP BY sex;
SELECT sex,SUM(height) FROM stdi GROUP BY sex;
SELECT sex,AVG(height) FROM stdi GROUP BY sex;

-- 查詢男女生人數哪個多
 SELECT
  sex,
  COUNT (*)
FROM
  stdi
WHERE height > 100 	-- 對原始表資料進行條件篩選
GROUP BY sex 		-- 按條件分組
HAVING COUNT (*) > 3	-- 對分組後的資料進行條件篩選
ORDER BY COUNT (*) DESC -- 對處理完的資料排序
LIMIT 0, 2		-- 限制查詢結果的顯示行數

子查詢

含義：出現在其他語句中的select語句，稱為子查詢或内查詢；外部的查詢語句，稱為主查詢或 外查詢.

分類： 按子查詢出現的位置：

  • select後面：僅僅支援标量子查詢
  • from後面：支援表子查詢
  • where或having後面：支援标量子查詢，列子查詢，行子查詢

按功能、結果集的行列數不同：

  • 标量子查詢（結果集隻有一行一列）
  • 列子查詢（結果集隻有一列多行）
  • 行子查詢（結果集有一行多列）（較少）
  • 表子查詢（結果集一般為多行多列） 
-- 在修改語句中使用子查詢,子查詢不能是目前正在操作的表
# UPDATE stdi SET NAME='匿名' WHERE xuehao=(SELECT xuehao FROM stdi WHERE height=180)
# DELETE  FROM stdi WHERE NAME=(SELECT NAME FROM stdi WHERE sex='女' AND height=169.6)

 -- 重點 在查詢語句中使用子查詢
 -- 在一條SQL語句中出現2個以上的表名時,可以為表名定義别名
 -- 在select後面寫子查詢  用外面查詢
 
 SELECT st.name ,
	(SELECT s.name FROM stdi s WHERE s.name = st.name)
 FROM stdi st;
 
 -- where 後面的子查詢
 -- 标量子查詢
 SELECT * FROM stdi WHERE height=(SELECT MAX(height) FROM stdi) 
 
 -- 列子查詢  一列多行
 SELECT * FROM stdi WHERE height IN (SELECT height FROM stdi WHERE height>=170)
 
 -- 行子查詢 一行多列
 SELECT * FROM stdi WHERE (height,weight)=(SELECT MAX(height),MAX(weight) FROM stdi)
 
 -- from後面 使用表子查詢 傳回多行多列
 -- 查詢男女人數哪個多
 
 SELECT * FROM
	(SELECT sex,COUNT(*)c FROM stdi GROUP BY sex)t
	WHERE t.c>3
 
 INSERT INTO stdi(NAME,sex,score)
		VALUES('Dim','男',50)

6. 多表關聯

關聯的目的

  • 資料庫設計範式
  • 建立備援較小,結構合理的資料庫,設計時必須遵循一定的規則(這種規則在關系型資料庫中稱為範式,範式是符合某一種設計要求的總結)
  • 六種範式:
    • 第一範式(1NF)
    • 第二範式(2NF)
    • 第三範式(3NF)
    • 巴斯-科德範式(BCNF)
    • 第四範式(4NF)
    • 第五範式(5NF)
  • 第一範式是最基本範式,在其基礎上滿足更多規定要求則為第二範式,其餘範式以此類推
  • 一般資料庫滿足第三範式就OK了

資料庫設計範式

第一範式(確定每列原子性)

第一範式是關系型資料庫最基本的範式,若表中字段都是不可分解的原子值,則該資料庫滿足第一範式

也就是每列隻能有一個屬性值,若下表中第一行的dept_desc的值包含了開發軟體和銷售軟體兩個值,則不滿足第一範式

資料庫--mysql

如圖表,每列都是不能再分的原子值,滿足第一範式

第二範式(有主鍵,其他字段依賴主鍵)

主鍵的作用:

  • 設定主鍵,每一組(行)資料就有了唯一性,通過主鍵直接定位到所需要的這組資料
  • 主鍵與其他字段組成的一組資料表示同一個東西,主鍵是唯一的,它們依賴主鍵,也就成了唯一的

第二範式就是在第一範式的基礎上使其他屬性都依賴于主鍵

-- 添加主鍵               
-- 方式1   建立表時添加列:  列名 資料類型 PRIMARY KEY
-- 方式2   建立表後:  ALTER TABLE 表名 ADD PRIMARY KEY(列名)

第三範式

消除傳遞依賴,友善了解,看作"消除備援"

非第三範式:

資料庫--mysql

第三範式:

資料庫--mysql
資料庫--mysql

外鍵 

-- 弱關聯   人為定義的關系 沒有實際的限制  删除關聯資料沒有任何限制
ALTER TABLE stdi ADD grade_id INT AFTER NAME;

-- 删除 grade_id
 ALTER TABLE stdi DROP grade_id

外鍵:引用另一條表的某條記錄

添加外鍵限制

-- 添加外鍵限制
-- ALTER TABLE 表名 ADD [CONSTRAINT 限制名] FOREIGN KEY(外鍵列) REFERENCES 關聯表(主鍵);
-- 删除外鍵
-- ALTER TABLE 表名 DROP FOREIGN KEY

資料庫中的表與表之間的關聯關系靠主鍵(primary key)與外鍵(foreign key)建立

-- 添加外鍵限制
-- ALTER TABLE 表名 ADD [CONSTRAINT 限制名] FOREIGN KEY(外鍵列) REFERENCES 關聯表(主鍵);
   -- 強關聯        添加外鍵限制,      外鍵關聯另一個表的主鍵     與t_grade中的主鍵id關聯
-- 添加外鍵限制
ALTER TABLE employee ADD CONSTRAINT fk_id FOREIGN KEY(dept_id) REFERENCES department(id)

如圖,employee表的dept_id與department表的id關聯了起來

資料庫--mysql
  • 主表中沒有對應的記錄時,不能将記錄添加到從表
  • 不能更改主表中的值而導緻從表中的記錄孤立
  • 從表存在與主表對應的記錄，不能從主表中删除該行
  • 删除主表前，先删從表

關聯查詢

資料庫--mysql

也叫多表查詢，當查詢的字段來自于多個表時，就會用到連接配接查詢

建立一個學生表

-- 建立表
CREATE TABLE stdi (
	xuehao INT,
    NAME varchar(10)
)
資料庫--mysql

建立一個課程表

-- 建立課程表
       CREATE TABLE t_course (
        id INT PRIMARY KEY AUTO_INCREMENT,
        SUBJECT VARCHAR (10)
      )
      
      -- 添加值
      INSERT INTO t_course (id, SUBJECT)
      VALUES
        (1, 'java'),
        (2, 'C#'),
        (3, 'Python'),
        (4, 'Go'),
        (5, '作業系統')
資料庫--mysql

建立一個表,關聯學生表與課程表

-- 學生選課表  (學生課程關聯表)
    CREATE TABLE stdi_course (
    stdi_xuehao VARCHAR (10),
    course_id INT,
    CONSTRAINT fk_sid FOREIGN KEY (stdi_xuehao) REFERENCES stdi (xuehao),
    CONSTRAINT fk_cid FOREIGN KEY (course_id) REFERENCES t_course (id)
)
資料庫--mysql
資料庫--mysql

笛卡爾乘積現象

查詢表stdi與t_grade

資料庫--mysql
資料庫--mysql

笛卡爾乘積—表1有m行,表2有n行,查詢結果有m*n行

-- 笛卡爾乘積現象   表1有m行，表2有n行，結果=m*n
SELECT
*
FROM
stdi,
t_grade

執行此查詢

資料庫--mysql

發生原因:

沒有有效的連接配接條件

解決方法:

添加有效的連接配接條件

-- 添加條件   多表時為表定義别名,通過别名調用表中的列
SELECT
*	
FROM
stdi s,
t_grade g                       -- 先合并表,後篩選
WHERE s.grade_id=g.id

執行此查詢:

資料庫--mysql

内連接配接(INNER JOIN)

把滿足了條件的兩張表中的交集資料查詢出來

-- 文法
Select 
結果 
from 表1,表2 
where 表1.column1 = 表2.column2
等值連接配接 
-- 内關聯 
-- 等值連接配接  作用與上面相同,内關聯效率高
SELECT 
* 
FROM
stdi s INNER JOIN t_grade g 
ON s.grade_id = g.id
非等值連接配接 
-- 向學生表中添加分數列
ALTER TABLE stdi ADD score INT AFTER NAME


-- 分數等級表
CREATE TABLE score_level(
level_name VARCHAR(5),
lower_score INT,
upper_score INT
)

查詢

-- 查詢 學生分數等級
SELECT 
s.xuehao,
s.name,
s.score,
sl.level_name
FROM stdi s INNER JOIN score_level sl
ON s.score BETWEEN sl.lower_score AND sl.upper_score

執行此查詢:

資料庫--mysql
自連接配接 
-- 省市縣(區)
 CREATE TABLE t_area(
 id INT PRIMARY KEY COMMENT'子id',
 NAME VARCHAR(10),
 pid INT COMMENT'父id'
 )

手動添加值

資料庫--mysql
-- 查詢
SELECT 
ta.name,tp.name pname 
FROM t_area ta INNER JOIN t_area tp
ON ta.pid = tp.id
WHERE ta.pid=2

執行此查詢

資料庫--mysql

外連接配接

左外連接配接 (LEFT JOIN) 
-- 文法
SELECT 結果 FROM
表1 LEFT JOIN 表2 ON
表1.column1 = 表2.column2

查詢

-- 左外關聯
-- 無論關聯條件是否成立,都會将左邊表的資料全部查詢出來(成績單,無論有無成績,名字學号必須顯示)
SELECT 
s.name,
s.xuehao,
g.grade
FROM stdi s
LEFT JOIN t_grade g
ON s.grade_id=g.id;

執行

資料庫--mysql
右外連接配接(RIGHT JOIN) 
-- 文法
SELECT 結果 FROM
表1 RIGHT JOIN 表2 ON
表1.column1 = 表2.column2

查詢

-- 右外關聯
-- 統計每個年級的人數
SELECT 
COUNT(*),
g.grade
FROM stdi s 
RIGHT JOIN t_grade g
ON s.grade_id = g.id
GROUP BY g.id
資料庫--mysql
sql 資料庫 mysql
上一篇: ARC工程中添加非ARC檔案
下一篇: spring系列(二):依賴注入

繼續閱讀