MySQL-有解無憂

一資料庫簡介

存盤資料的倉庫

本質上是一個檔案系統以檔案的方式存在服務器電腦上

所有關系型資料庫都可以使用通用的SQL陳述句進行管理

常見的資料庫軟體有 MySQL Oracle PostgreSQL...

二 MySQL簡介

開源免費的資料庫小型資料庫功能強大已經被Oracle收購 MySQL6.x版本也開始收費

1. MySQL安裝

　　Linux 安裝MySQL(使用Mac遠程訪問)

2. MySQL登錄

　　mysql -u用戶名 -p密碼

　　mysql -hIP地址 -u用戶名 -p密碼

3. MySQL退出

　　exit

　　quit

4. MySQL常見資料型別

　　INT 整數型別

　　DOUBLE(X,Y) 小數型別最多有X位小數點后面保留Y位

　　DATE 日期只包含年月日 yyyy-MM-dd

　　DATETIME 日期包含年月日時分秒 yyyy-MM-dd HH:mm:ss

　　TIMESTAMP 時間戳默認使用當前的系統時間來自動賦值

　　VARCHAR(X) 字串最多有X個字符

三 SQL簡介

Structured Query Language 結構化查詢語言

定義了操作所有關系型資料庫的規則

通用的資料庫操作語言可以用在不同的資料庫中不同的資料庫SQL陳述句有一些區別

四 SQL陳述句分類

1. DDL: Data Definition Language 資料定義語言比如建庫建表

2. DML: Data Manipulation Language 資料操作語言比如對表資料的增刪改

3. DQL: Data Query Language 資料查詢語言比如對表資料的查詢

4. DCL: Data Control Language 資料控制語言比如對用戶權限的設定

五庫操作

創建資料庫

CREATE DATABASE 資料庫名稱;

創建資料庫判斷不存在再創建

CREATE DATABASE IF NOT EXISTS 資料庫名稱;

創建資料庫并指定默認編碼

CREATE DATABASE 資料庫名稱 CHARACTER SET utf8;

創建資料庫判斷不存在再創建并指定默認編碼

CREATE DATABASE IF NOT EXISTS 資料庫名稱 CHARACTER SET utf8;

洗掉資料庫

DROP DATABASE 資料庫名稱;

洗掉資料庫判斷存在再洗掉

DROP DATABASE IF EXISTS 資料庫名稱;

修改當前使用的資料庫

USE 資料庫名稱;

修改資料庫的字符集 utf8mb4支持Emoji表情??

ALTER DATABASE 資料庫名稱 CHARACTER SET utf8mb4;

查看當前使用的資料庫

SELECT DATABASE();

查看所有資料庫

SHOW DATABASES;

查看指定資料庫的定義資訊

SHOW CREATE DATABASE 資料庫名稱;

六單表操作

創建表

CREATE TABLE account (
    aid INT,
    aname VARCHAR(100),
    amoney DOUBLE
);

創建表指定主鍵主鍵特點是非空且唯一

CREATE TABLE account (
    aid INT PRIMARY KEY,
    aname VARCHAR(100),
    amoney DOUBLE
);

創建表指定主鍵且主鍵自動增長

CREATE TABLE account (
    aid INT PRIMARY KEY AUTO_INCREMENT,
    aname VARCHAR(100),
    amoney DOUBLE
);

創建表復制

CREATE TABLE account_new LIKE account;

洗掉表

DROP TABLE account;

洗掉表判斷存在再洗掉

DROP TABLE IF EXISTS account;

修改表名

ALTER TABLE account RENAME TO account_new;

修改表的字符集

ALTER TABLE account CHARACTER SET utf8;

修改表添加列

ALTER TABLE account ADD asex INT;

修改表修改列的型別長度約束

ALTER TABLE account MODIFY asex VARCHAR(1) NOT NULL;

修改表修改列的名稱型別長度約束

ALTER TABLE account CHANGE asex asex_new INT;

修改表洗掉列

ALTER TABLE account DROP asex_new;

查看資料庫中的所有表

SHOW TABLES;

查看表結構

DESC account;

查看指定表的定義資訊

SHOW CREATE TABLE account;

七單表資料操作

插入表紀錄

INSERT INTO account VALUES (1, '吃飯支出', 247);
INSERT INTO account (aid, aname, amoney) VALUES (2, '工資收入', 12345);
INSERT INTO account (aid, aname, amoney) VALUES (3, '服裝支出', 1000);
INSERT INTO account (aid, aname, amoney) VALUES (4, '吃飯支出', 325);
INSERT INTO account (aid, aname, amoney) VALUES (5, '股票收入', 8000);
INSERT INTO account (aid, aname, amoney) VALUES (6, '打麻將支出', 8000);
INSERT INTO account (aid, aname, amoney) VALUES (7, NULL, 5000);
INSERT INTO account (aid, aname, amoney) VALUES (8, '打麻將支出', 2000);

洗掉所有記錄一條一條洗掉不清空AUTO_INCREMENT記錄數

DELETE FROM account;

洗掉所有記錄直接將表洗掉重新建表 AUTO_INCREMENT歸零效率更高

TRUNCATE TABLE account;

洗掉指定記錄一條一條洗掉不清空AUTO_INCREMENT記錄數

DELETE FROM account WHERE aid = 1;

修改所有記錄

UPDATE account SET aname = '錢都用光了';

修改指定記錄

UPDATE account SET aname = '撿到一袋子錢', amoney = 88888 WHERE aid = 1;

查詢語法

SELECT 欄位串列 FROM 表名串列 WHERE 條件串列 GROUP BY 分組欄位 HAVING 分組之后的條件 ORDER BY 排序 LIMIT 分頁

查詢所有欄位

SELECT * FROM account;

查詢指定欄位

SELECT aname, amoney FROM account;

查詢去除重復記錄

SELECT DISTINCT amoney FROM account;

查詢別名

SELECT * FROM account AS a;
SELECT * FROM account a;
SELECT aname, amoney AS money FROM account;
SELECT aname, amoney money FROM account;

查詢運算

SELECT aname, amoney + 100 FROM account;

查詢不等于

SELECT * FROM account WHERE aname <> '吃飯支出';

查詢大于

SELECT * FROM account WHERE amoney > 1000;

查詢之間

SELECT * FROM account WHERE amoney >= 2000 AND amoney <= 5000;
SELECT * FROM account WHERE amoney BETWEEN 2000 AND 5000;

查詢或

SELECT * FROM account WHERE amoney = 1000 OR amoney = 3500 OR amoney = 5000;
SELECT * FROM account WHERE amoney IN (1000, 3500, 5000);

查詢包含

SELECT * FROM account WHERE aname LIKE '%支出%';

查詢字符長度匹配

SELECT * FROM account WHERE aname LIKE '_____';

查詢不為NULL

SELECT * FROM account WHERE aname IS NOT NULL;
SELECT * FROM account WHERE NOT (aname IS NULL);

查詢升序+降序

SELECT * FROM account ORDER BY amoney ASC, aid DESC;

查詢條件+升序排序必須放在最后

SELECT * FROM account WHERE aname LIKE '%支出%' ORDER BY amoney ASC;

查詢求和對表中資料條數求和

SELECT COUNT(aid) FROM account;
SELECT COUNT(IFNULL(aname, 0)) FROM account;

查詢求和對表中資料內容求和一般是數值型

SELECT SUM(amoney) FROM account WHERE aname LIKE '%收入%';

查詢最大值一般是數值型

SELECT MAX(amoney) FROM account;

查詢最小值一般是數值型

SELECT MIN(amoney) FROM account;

查詢平均值一般是數值型

SELECT AVG(amoney) FROM account;

查詢分組必須跟隨聚合函式

SELECT aname, SUM(amoney) FROM account WHERE aname LIKE '%支出%' GROUP BY aname HAVING SUM(amoney) > 888 ORDER BY SUM(amoney) ASC;

查詢分頁引數一=開始索引引數二=每頁顯示個數 pageSize

SELECT * FROM account LIMIT (N - 1) * pageSize, pageSize;
SELECT * FROM account LIMIT 0, 3;
SELECT * FROM account WHERE aname LIKE '%支出%' ORDER BY amoney ASC LIMIT 2, 2;

八約束

對表中的資料進行限定保證資料的正確性有效性和完整性

1. 主鍵約束 primary key

非空且唯一一張表只能有一個欄位為主鍵主鍵就是表中記錄的唯一標識

a. 在創建表時添加主鍵約束

CREATE TABLE demo_primary_key (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

b. 洗掉主鍵約束洗掉不了自動增長的主鍵

ALTER TABLE demo_primary_key DROP PRIMARY KEY;

c. 創建表后添加主鍵約束

ALTER TABLE demo_primary_key MODIFY id INT PRIMARY KEY;

2. 非空約束 not null

值不能為null

a. 在創建表時添加非空約束

CREATE TABLE demo_not_null (
    id INT,
    name VARCHAR(20) NOT NULL
);

b. 洗掉非空約束

ALTER TABLE demo_not_null MODIFY name VARCHAR(20);

c. 創建表后添加非空約束

ALTER TABLE demo_not_null MODIFY name VARCHAR(20) NOT NULL;

3. 唯一約束 unique

值不能重復

a. 在創建表時添加唯一約束

CREATE TABLE demo_unique (
    id INT,
    name VARCHAR(20) UNIQUE
);

b. 洗掉唯一約束

ALTER TABLE demo_unique DROP INDEX name;

c. 創建表后添加唯一約束

ALTER TABLE demo_unique MODIFY name VARCHAR(20) UNIQUE;

4. 外鍵約束 foreign key

讓表于表產生關系從而保證資料的正確性

a. 在創建表時添加外鍵約束

CREATE TABLE 表名 (
    ...
    外鍵列
    CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名稱) REFERENCES 主表名稱 (主表欄位名稱)
);

b. 洗掉外鍵約束

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

c. 創建表后添加外鍵約束

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名稱) REFERENCES 主表名稱 (主表欄位名稱);

九多表操作(一對多)

1. 表操作

創建表一

CREATE TABLE category (
    cid VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100)
);

創建表多

CREATE TABLE product (
    pid VARCHAR(32) PRIMARY KEY,
    pname VARCHAR(100),
    pprice DOUBLE,
    pcid VARCHAR(32)
);

添加外鍵約束

ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY (pcid) REFERENCES category (cid);

洗掉外鍵約束

ALTER TABLE product DROP FOREIGN KEY product_fk;

2. 資料操作

插入表紀錄

INSERT INTO category VALUES ('c001', '家電');
INSERT INTO category VALUES ('c002', '服飾');
INSERT INTO category VALUES ('c003', '化妝品');
INSERT INTO product VALUES ('p001', '聯想', 5000, 'c001');
INSERT INTO product VALUES ('p002', '海爾', 3000, 'c001');
INSERT INTO product VALUES ('p003', '雷神', 5000, 'c001');
INSERT INTO product VALUES ('p004', 'JACK JONES', 800, 'c002');
INSERT INTO product VALUES ('p005', '真維斯', 200, 'c002');
INSERT INTO product VALUES ('p006', '花花公子', 440, 'c002');
INSERT INTO product VALUES ('p007', '勁霸', 2000, 'c002');
INSERT INTO product VALUES ('p008', '香奈兒', 800, 'c003');
INSERT INTO product VALUES ('p009', '相宜本草', 200, 'c003');

查詢隱式內連接兩表交集

SELECT * FROM category, product WHERE cid = pcid;

查詢顯示內連接兩表交集

SELECT * FROM category INNER JOIN product ON cid = pcid;

查詢左外連接左表全部和兩表交集

SELECT * FROM category LEFT JOIN product ON cid = pcid;

查詢右外連接右表全部和兩表交集

SELECT * FROM category RIGHT JOIN product ON cid = pcid;

查詢子查詢一條SELECT陳述句結果作為另一條SELECT陳述句一部分

SELECT * FROM product WHERE pcid = (SELECT cid FROM category WHERE cname = '化妝品');

十多表操作(多對多)

創建表多

CREATE TABLE goods (
    gid VARCHAR(32) PRIMARY KEY,
    gname VARCHAR(100),
    gprice DOUBLE
);

創建表多

CREATE TABLE orders (
    oid VARCHAR(32) PRIMARY KEY,
    ototal_price DOUBLE
);

創建表中間表

CREATE TABLE item (
    gid VARCHAR(32),
    oid VARCHAR(32)
);

添加外鍵約束

ALTER TABLE item ADD CONSTRAINT item_goods_fk FOREIGN KEY (gid) REFERENCES goods (gid);
ALTER TABLE item ADD CONSTRAINT item_orders_fk FOREIGN KEY (oid) REFERENCES orders (oid);

洗掉外鍵約束

ALTER TABLE item DROP FOREIGN KEY item_goods_fk;
ALTER TABLE item DROP FOREIGN KEY item_orders_fk;

十一資料庫設計范式

設計資料庫時需要遵循的一些規范要遵循后邊的范式要求必須先遵循前邊的所有范式要求

1. 第一范式(1NF) 原子性

每一列都是不可分割的原子資料項

表中的每一列不可再拆分

2. 第二范式(2NF) 不產生區域依賴

在1NF的基礎上非碼屬性必須完全依賴于碼

表中的每一列都完全依賴于主鍵

3. 第三范式(3NF) 不產生傳遞依賴

在2NF的基礎上任何非碼屬性不依賴于其它非碼屬性

表中的每一列都直接依賴于主鍵而不是通過其它列來間接依賴于主鍵資料不能存在傳遞關系

十二資料庫命名規范

1. 采用26個英文字母(區分大小寫)和0-9的自然數(經常不需要)加上下劃線'_'組成命名簡潔明確多個單詞用下劃線'_'分隔

2. 全部小寫命名禁止出現大寫

3. 禁止單獨使用資料庫關鍵字比如name time password等等

4. 表名稱不應該取得太長(一般不超過三個英文單詞)

5. 用單數形式表示名稱比如使用user而不是users

6. 表名業務表前綴business 系統表前綴system

7. 如果該表主鍵需要作為別的多個表的外鍵需要有前綴區分并且命名一致比如產品型別表business_product_type 表的主鍵會被產品表business_product和價格表business_price等多張表設為外鍵那么business_product_type表的主鍵不能是id 應該是product_type_id 產品表business_product和價格表business_price的外鍵命名也應該是product_type_id

十三事務

1. 簡介

如果一個包含多個步驟的業務操作被事務管理那么這些操作要么同時成功要么同時失敗

2. 自動提交事務

MySQL默認每一條DML(增刪改)陳述句都是一個單獨的事務每條陳述句都會自動開啟一個事務陳述句執行完畢自動提交事務 MySQL默認開啟自動提交事務

3. 手動提交事務

執行成功的情況

　　開啟事務 -> 執行多條SQL陳述句 -> 提交事務

　　START TRANSACTION; -> SQL... -> COMMIT;

執行失敗的情況

　　開啟事務 -> 執行多條SQL陳述句 -> 回滾事務

　　START TRANSACTION; -> SQL... -> ROLLBACK;

4. 修改事務提交方式

查看事務提交方式

　　SELECT @@autocommit; //0=手動提交 1=自動提交

修改事務提交方式

　　set @@autocommit = 0; //0=手動提交 1=自動提交

5. 事務的四大特征

原子性: 是不可分割的最小操作單位要么同時成功要么同時失敗

持久性: 當事務提交或回滾后資料庫會持久化的保存資料

隔離性: 多個事務之間相互獨立

一致性: 事務操作前后資料總量不變

6. 事務的隔離級別

多個事務之間保持隔離互不影響如果多個事務操作同一批資料則會引發一些問題設定不同的隔離級別就可以解決這些問題

a. 存在問題

1> 臟讀: 一個事務讀取到另一個事務中沒有提交的資料

2> 不可重復讀(虛讀): 在一個事務中兩次讀取到的資料內容不一樣這是update時引發的問題

3> 幻讀: 在一個事務中兩次讀取到的資料數量不一樣這是insert或delete時引發的問題

b. 隔離級別

隔離級別越高性能越差安全性越高

1> read uncommitted 讀未提交

　　產生的問題: 臟讀不可重復讀幻讀

2> read committed 讀已提交 Oracle默認

　　產生的問題: 不可重復讀幻讀

3> repeatable read 可重復讀 MySQL默認

　　產生的問題: 幻讀

4> serializable 串行化

　　可以解決所有的問題

c. 查看資料庫隔離級別

查看資料庫隔離級別

　　select @@tx_isolation;

修改資料庫隔離級別重新連接才會生效

　　set global transaction isolation level 隔離級別字串;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4581.html

標籤：MySQL

上一篇：Mysql資料庫意外崩潰導致表資料檔案損壞無法啟動的問題解決

下一篇：MySQL學習筆記（21）：優化磁盤IO