Linux 和數(shù)據(jù)庫 day06 隨堂筆記

數(shù)據(jù)庫設(shè)計(jì)

ER模型

定義: E 代表實(shí)體(數(shù)據(jù)表), R 代表聯(lián)系(數(shù)據(jù)表之間對應(yīng)的字段)

關(guān)系常見分類

一對一

一對多

多對多

外鍵

如果一個(gè)實(shí)體的某個(gè)字段指向另一個(gè)實(shí)體的主鍵滔驾，就稱為外鍵。

被指向的實(shí)體，稱之為主實(shí)體（主表）厦滤，也叫父實(shí)體（父表）次兆。

負(fù)責(zé)指向的實(shí)體稿茉，稱之為從實(shí)體（從表），也叫子實(shí)體（子表）

作用: 對關(guān)系字段進(jìn)行約束芥炭，當(dāng)為從表中的關(guān)系字段填寫值時(shí)漓库，會(huì)到關(guān)聯(lián)的主表中查詢此值是否存在，如果存在則填寫成功园蝠，如果不存在則填寫失敗并報(bào)錯(cuò)

-- 主表

drop table if exists class;

create table class(

id int unsigned primary key auto_increment,

name varchar(10)

);

-- 從表

drop table if exists stu;

create table stu(

name varchar(10),

class_id int unsigned,

-- stu 表的 class_id 指向 class 表的 id, class_id 是

stu 表的外鍵

-- foreign key(自己的字段名) references 目標(biāo)表名(目標(biāo)表的

主鍵)

foreign key(class_id) references class(id)

);

擴(kuò)展1 : 對應(yīng)存在表添加外鍵

-- 擴(kuò)展1 : 對于已經(jīng)存在的表添加外鍵

-- alter table 從表名 add foreign key (從表字段)

references 主表名(主表主鍵);

alter table stu add foreign key (class_id)

references class(id);

擴(kuò)展2 : 查看和刪除外鍵

-- 擴(kuò)展2 : 查看外鍵和刪除外鍵

-- 查看外鍵

-- show create table 表名

show create table stu;

-- CREATE TABLE `stu` (

-- ? `name` varchar(10) DEFAULT NULL,

-- ? `class_id` int(10) unsigned DEFAULT NULL,

-- ? KEY `class_id` (`class_id`),

-- ? CONSTRAINT `stu_ibfk_1` FOREIGN KEY

(`class_id`) REFERENCES `class` (`id`)

-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 刪除外鍵

-- alter table stu drop foreign key 外鍵名稱

alter table stu drop foreign key stu_ibfk_1;

利用 Navicat 查看外鍵

索引

定義: 類似于圖書中的目錄, 能夠起到快速檢索數(shù)據(jù)的作用

作用: 對于大量數(shù)據(jù)進(jìn)行查詢效率優(yōu)化時(shí), 可以采取添加索引的策略

-- 索引應(yīng)用檢驗(yàn)案例

-- 開啟時(shí)間監(jiān)測

set profiling=1;

-- 查詢示例數(shù)據(jù) num = 10000 的值

select * from test_index where num = 10000;

-- 查看運(yùn)行時(shí)間

show profiles;

-- 添加索引

-- create index 索引名稱 on 表名(目標(biāo)字段)

create index num_index on test_index(num);

-- 再次執(zhí)行查詢數(shù)據(jù)操作

select * from test_index where num = 10000;

-- 再次查看運(yùn)行時(shí)間

show profiles;

擴(kuò)展1: 查看索引

-- 擴(kuò)展1 : 查看索引

-- show index from 表名

show index from test_index;

擴(kuò)展2 : 創(chuàng)表時(shí)添加

-- 擴(kuò)展2 : 創(chuàng)表時(shí)添加

create table create_index(

id int primary key,

name varchar(10) unique, ?-- unique : 設(shè)置端唯一值

age int,

key(age) -- 指定添加索引方法

);

-- 查看索引

show index from create_index;

擴(kuò)展3 : 刪除索引

-- 擴(kuò)展3 : 刪除索引

-- drop index 索引名稱 on 表名;

drop index age on create_index;

結(jié)論 : 索引在使用的時(shí)候能提高查詢效率, 但是在進(jìn)行插入/更新/刪

除時(shí), 索引會(huì)造成障礙, 因此需要在執(zhí)行這些操作前, 先移除索引, 操作結(jié)束后, 重新添加索引即可

命令行中操作 MySQ

前提: 要進(jìn)行操作系統(tǒng)環(huán)境中, 存在 MySQL 環(huán)境

登錄 MySQL

mysql -u數(shù)據(jù)庫用戶名 -p數(shù)據(jù)庫密碼

例如:

mysql -uroot -p123456

數(shù)據(jù)庫操作

查看所有數(shù)據(jù)庫 : show databases;

使用數(shù)據(jù)庫 : use 數(shù)據(jù)庫名稱;

查看當(dāng)前使用的數(shù)據(jù)庫 : select database();

創(chuàng)建數(shù)據(jù)庫 : create database 數(shù)據(jù)庫名稱 charset=utf8;

刪除數(shù)據(jù)庫 : drop database 數(shù)據(jù)庫名稱;

查看所有數(shù)據(jù)庫

使用數(shù)據(jù)庫

查看當(dāng)前使用的數(shù)據(jù)庫名稱

創(chuàng)建數(shù)據(jù)庫

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

數(shù)據(jù)表操作

查看數(shù)據(jù)庫中的所有數(shù)據(jù)表 : show tables;

查看表結(jié)構(gòu) : desc 表名;

查看創(chuàng)表語句 : show create table 表名;

注意 : 進(jìn)入到數(shù)據(jù)庫之后, 所有的 SQL 查詢語句, 均可以正常使用!

查看所有數(shù)據(jù)表

查看表字段信息

查看創(chuàng)表語句

命令行和圖形化頁面操作步驟對比

連接數(shù)據(jù)庫

查看數(shù)據(jù)庫

選擇數(shù)據(jù)庫

新建查詢

查看所有表

查看表結(jié)構(gòu)

編寫 SQL 查詢語句

結(jié)論 : 命令行使用是為了在沒有圖形化工具的情況下, 應(yīng)急使用操作數(shù)據(jù)庫的手段

存儲(chǔ)過程

定義 : 可以叫存儲(chǔ)程序, 通過一定的代碼邏輯, 將一句或多久SQL

語句進(jìn)行封裝, 通過調(diào)用存儲(chǔ)過程, 快速實(shí)現(xiàn)其內(nèi)部封裝SQL語句操作

用途 : 可以用來向數(shù)據(jù)庫中, 快速插入大量測試數(shù)據(jù)時(shí)使用

基本語法格式

delimiter // -- 取消默認(rèn)結(jié)尾標(biāo)識(shí)符 ; 的作用

create procedure 存儲(chǔ)過程名(參數(shù)列表)

begin -- 代碼邏輯的開始

SQL語句

end //

delimiter ; -- 還原默認(rèn)結(jié)尾標(biāo)識(shí)符 ; 的作用

調(diào)用方法

-- 在查詢窗口中

call 存儲(chǔ)過程名();

語法實(shí)現(xiàn)

-- 修改句尾標(biāo)識(shí)符為'//'

delimiter //

-- 如果存在 test 存儲(chǔ)過程則刪除

drop procedure if exists test;

-- 創(chuàng)建無參數(shù)的存儲(chǔ)過程 test

create procedure test() ? ? ? ? ? ? ? ?

begin

-- 聲明變量 i

declare i int;

-- 變量初始化賦值為 0

? ?set i = 0;

-- 設(shè)置循環(huán)條件: 當(dāng) i 大于 10 時(shí)跳出 while 循環(huán)

? while i < 10000 do

-- 往 datatest 表插入數(shù)據(jù)

? ? ? ?insert into datatest values (null, i);

-- 循環(huán)一次, i 加一

? ? ? ?set i = i + 1;

-- 結(jié)束 while 循環(huán)

? end while;

-- 查看 datatest 表數(shù)據(jù)

? ?select * from datatest;

-- 結(jié)束存儲(chǔ)過程定義語句

end//

-- 恢復(fù)句尾標(biāo)識(shí)符為';'

delimiter ;

事務(wù)

定義 : 所謂事務(wù)可以稱之一個(gè)操作序列, 一系列操作要么都執(zhí)行,要么就不執(zhí)行. 對于數(shù)據(jù)庫來講, 對于數(shù)據(jù)的操作行為, 要么都實(shí)現(xiàn), 要么都不實(shí)現(xiàn), 最終需要確保寫入到數(shù)據(jù)庫的數(shù)據(jù)的一致性

(原子性)!

事務(wù)實(shí)現(xiàn)案例

前提: 數(shù)據(jù)表的數(shù)據(jù)引擎類型必須是 InnoDB (可以通過查看創(chuàng)表語句來確認(rèn))

-- 注意 : 出發(fā)事務(wù)操作一般是由修改數(shù)據(jù)操作產(chǎn)生(插入數(shù)據(jù)insert/更新數(shù)據(jù)update/刪除數(shù)據(jù)delete)

-- 開啟事務(wù)

begin;

-- 提交事務(wù)

commit;

-- 回滾事務(wù)

rollback;

視圖

場景 : 能夠封裝 SQL 語句, 以類似于表的形式存在

-- 創(chuàng)建視圖語法

-- 注意: 視圖命名一般以 v_視圖名稱 形式實(shí)現(xiàn)

-- create view 視圖名稱 as select 語句;

create view v_goods as select goodsName 商品名稱,

price 價(jià)格, num 數(shù)量, company 公司 from goods;

-- 視圖的用法: 當(dāng)成表查詢使用即可

select * from v_goods;

-- 復(fù)雜 SQL 語句視圖封裝

select go.goodsName, ca.cateName from goods go inner

join category ca on go.typeId = ca.typeId;

-- 封裝連接查詢語句時(shí), 如果存在重名字段名稱, 需要通過別名進(jìn)行

修改

create view v_goods_cate as select go.*, ca.id 序號,

ca.typeId 類型, ca.cateName from goods go inner join

category ca on go.typeId = ca.typeId;

select * from v_goods_cate;

-- 刪除視圖語句

-- drop view 視圖名稱

drop view v_goods_cate;

注意 : 視圖可以起到隱藏真實(shí)數(shù)據(jù)表內(nèi)容的作用

視圖名稱以 v 開頭的原因

修改 MySQL 數(shù)據(jù)庫密碼

場景 : 遺忘數(shù)據(jù)庫密碼時(shí)使用

1. 修改數(shù)據(jù)庫配置文件, 使之登錄不需要密碼

此操作需要具備服務(wù)器 root 賬戶權(quán)限

查找配置文件并修改

切換 root 用戶

su -

定位配置文件位置

locate my.cnf

使用 vi 工具打開配置文件

vi /etc/my.cnf

在文件內(nèi)容 [mysqld] 下方添加此內(nèi)容, 保存后退出

skip-grant-tables

查看和獲取 MySQL 配置文件

添加內(nèi)容 跳過密碼登錄

保存文件修改內(nèi)容退出

確認(rèn)配置文件內(nèi)容已經(jīng)修改

重新啟動(dòng) MySQL 服務(wù)

systemctl restart mysqld

重啟完成可以通過查看狀態(tài)命令進(jìn)行驗(yàn)證

systemctl status mysqld

重啟和查看 mysqld 服務(wù)

重新登錄 MySQL 查看是否需要密碼進(jìn)行登錄

確認(rèn)不需要密碼可以登錄 MySQL

2. 登錄數(shù)據(jù)庫, 修改數(shù)據(jù)庫賬戶密碼

注意 : 本步驟為 SQL 語句, 需要在 mysql > 狀態(tài)下執(zhí)行

use mysql; -- 選擇 mysql 數(shù)據(jù)庫

-- 更新密碼

-- 注意 : authentication_string 字段名需要根據(jù) MySQL 版

本就行對應(yīng)修改

-- update user set

authentication_string=password('新密碼') where user =

'用戶名';

update user set

authentication_string=password('123') where user =

'root';

-- 刷新權(quán)限

flush privileges;

3. 還原配置文件設(shè)置, 使之登錄需要密碼

還原配置文件設(shè)置與添加設(shè)置步驟基本相同

查找配置文件并修改

切換 root 用戶

su -

定位配置文件位置

locate my.cnf

使用 vi 工具打開配置文件

vi /etc/my.cnf

使用 # 注釋 [mysqld] 下方內(nèi)容, 保存后退出

# skip-grant-tables

重新啟動(dòng) MySQL 服務(wù)

systemctl restart mysqld

重啟完成可以通過查看狀態(tài)命令進(jìn)行驗(yàn)證

systemctl status mysqld

重新使用修改后的密碼登錄 MySQL 即可

MySQL 日志的獲取

注意 : MySQL 自帶日志功能, 但是開啟日志功能, 極其消耗數(shù)據(jù)庫

性能, 因此默認(rèn)情況下是不開啟的

-- 查看日志功能是否開啟

show variables like 'general%';

-- 開啟操作

set global general_log = 1;

-- 關(guān)閉操作

set global general_log = 0;

-- 注意: 日志功能使用完成, 需要記得馬上關(guān)閉, 避免影響數(shù)據(jù)庫性

能!!!!!!

開啟日志功能

關(guān)閉日志功能