當前位置：首頁 > 编程资源 > 综合教程 >内容正文

综合教程

MySQL基础

發布時間：2023/12/15 综合教程 20 生活家

生活随笔收集整理的這篇文章主要介紹了 MySQL基础小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一、數據庫概述

1、SQL分類

SQL，Structure Query Language，結構化查詢語言，主要有下面三種：

一、數據定義語言 Data Defination Language (DDL)

數據庫定義語言定義了數據庫涉及的各種對象，定義數據的完整性約束、保密限制等約束。

二、數據操作語言 Data Manipulation Language (DML)

數據操作語言實現了對數據的操作。基本數據操作有兩類：檢索（查詢）和更新（插入、刪除和修改）。

三、數據控制語言 Data Control Language (DCL)

數據庫控制語言實現對數據庫的控制，包括數據完整性控制、數據安全性控制和數據庫恢復等。

2、MySQL歷史發展

1985年，David Axmark 、Allan Larsson 、 Michael Widenius 成立公司，該公司為 MySQL AB 的前身。

2000年4月，MySQL對搜索引擎進行整理，命名為MyISAM。

2001年，InnoDB集成到MySQL。

2004年10月，MySQL 4.1 版本發布。

2005年10月，MySQL5.0版本發布。5.0加入了游標、存儲過程、觸發器、視圖和事務支持。

2008年1月16日，MySQL被SUN收購。

2009年，SUN被Oracle收購。

... ...

3、MySQL優點

1、體積小

2、速度快

3、擁有成本低

4、開放源碼

4、數據庫設計三大范式

1、原子性，字段不可再分割

比如：在user中有“聯系方式”字段，存儲內容：“ 64558@163.com,15854215521 ”，就不符合原子性，要設計成：email、phone對郵箱和手機號分別進行存儲。

不符合第一范式：

符合第一范式：

2、完全依賴，沒有部分依賴

比如：學生選課表的設計：

這里可以通過（學生,課程）可以確定老師、教師、時間，可以把（學生,課程）作為主鍵。但是，教材并不完全依賴主鍵，教材可以根據課程直接確定。這就是出現了不完全依賴、部分依賴，這樣的設計就不符合第二范式。

符合第二范式的設計：

選課表:

教材表：

3、沒有傳遞依賴

每個屬性要跟主鍵直接聯系，不能有傳遞關系。

比如，學生表：

學校的地址和學校的電話，就是和college有依賴關系，和student是沒有直接依賴關系，不符合第三范式。

符合第三范式的修改如下：

學生表：

學校表：

一般來講，范式越復雜，性能會越差。我們工作中滿足以上三大范式即可。

二、MySQL安裝和配置

1、MySQL下載

1、MySQL官方網站：http://www.mysql.com/

2、MySQL版本分類：社區版 Community 和企業版 Enterprise。

社區辦支持只有下載，并且完全免費，官方不提供技術支持；

企業版不能自由下載并且收費，提供了更多的功能，享受完備的技術支持。

3、版本標識說明

GA：通用版本，官方推薦

RC：發布版

Alpha：內測版

Bean：公測版

4、社區版下載：

進入網站首頁——>Downloads——>Community——MySQL on Windows ——>MySQL Installer,進入Windows版本下載頁面。

其中 1.7 M大小的mysql-installer-web-community 為在線安裝文件；

385M大小的mysql-installer-community 為本地安裝。

2、MySQL安裝

Typical：默認安裝

Complete：完全安裝

Custom：自定義安裝

3、MySQL配置

配置文件為：C：ProgramData/MySQL/MySQL 5.6/my.ini

常用配置項：

lower_case_table_names = 0 // 0 ,表名區分大小寫；1 , 表名不區分大小寫

default_character_set = utf-8 // 客戶端字符編碼

character_set_server = utf-8 //服務端字符編碼

port = 3306 //端口號

default_storage_engin = INNODB //默認存儲引擎

三、MySQL對象操作

1、數據庫操作

（1）創建數據庫：

create database db_name;

（2）查看數據庫：

show databases ;

（3）使用數據庫：

use db_name;

（4）刪除數據庫：

drop database db_name;

注意： SQL語句可以用 ; g G 三種方式結尾，其中 ; 和 g 效果相同； G 優化結果顯示。

2、表操作

（1）創建表:

create table company ( company_id  INT , company_name  VARCHAR(20) , company_address  VARCHAR(64));

（2）查看表結構：

describe company;     --查看表定義

show create table company;      --查看表詳細定義

（3）刪除表：

drop table company;

（4）修改表：

alter table company rename mycompany; --修改表名
alter table company add  tel varchar(20); --增加字段
alter table company add  desc varchar(32) first;--在表的第一個位置增加字段
alter table company add create_time varchar(32) after company_name; --在指定位置增加字段
alter table company drop company_name;--刪除字段
alter table company modify company_name varchar(128);--修改字段類型
alter table company modify company_name first;--調整字段順序到第一位
alter table company modify company_name after desc;--調整字段順序到desc后面
alter table company change company_name new_name varchar(128);--修改字段名和字段類型

（5）操作表約束：

數據的完整性是指數據的準確性與一致性，完整性檢查是指檢查數據的準確性和一致性。MySQL提供一致機制來檢查數據庫表中的數據是否滿足規定的條件，以保證數據庫表中數據的準確性和一致性，這種機制就是約束。

MySQL支持的完整性約束：

NOT NULL（NK）：約束字段的值不能為空

DEFAULT：設置字段的默認值

UNIQUE KEY（UK）：約束字段的值唯一

PRIMARY KEY（PK）：約束字段為標的主鍵，作為表記錄的唯一標識

AUTO_INCREMENT：約束字段的值為自動增加

FOREIGN KEY（FK）：約束字段為表的外鍵

a、設置非空約束

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) NOT NULL);

如果增加的記錄中，設置NK的字段沒有值，則數據庫管理系統會報錯。

命令行錯誤信息：

ERROR 1048 (23000) : Column 'member_name' cannot be null

管理工具錯誤提示：

b、設置字段的默認值

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) DEFAULT 'huiyuan');

c、設置唯一約束（UK）

create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) UNIQUE);

對唯一字段插入相同記錄時，提示信息：

d、設置主鍵約束

主鍵字段要滿足：唯一、非空，可以是單一字段，也可以是多個字段。

單字段主鍵的兩種方法：

create table member(member_id INT(11) PRIMARY KEY, member_name VARCHAR(32) UNIQUE, create_time varchar(32));  --（1）設置主鍵
create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),
             CONSTRAINT pk_menberid PRIMARY KEY (member_id)); -- （2）設置主鍵并定義主鍵名稱

多字段主鍵：

create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),
                    CONSTRAINT pk_id_name PRIMARY KEY (member_id,member_name)); -- 設置主鍵并定義主鍵名稱

e、自增（AUTO INCREMENT）

一個數據庫只能有一個字段使用自增約束，而且該字段類型必須是整數類型。自增約束一般設置在主鍵上。

create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40));

f、外鍵（FOREIGN KEY）

描述兩個表之間的約束。子表中的某個字段的取值范圍由父表決定。比如部門和雇員表。

create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40)); --部門表
create table t_employee(employee_id INT PRIMARY KEY,e_name VARCHAR(32),create_time VARCHAR(32),
            dept_no INT(11), CONSISTENT fk_deptno FOREIGN KEY(dept_no) REFERENCES t_tept(deptno));--雇員表

3、索引操作

數據庫的索引對象和書的目錄類似，主要是為了提高從表中檢索數據的速度（其次還可以保證字段唯一性，實現數據庫表的完整性）。按索引的存儲類型分類，可以分為：B型樹索引（BTREE）和哈希索引（HASH），其中，InnoDB和MyISAM存儲引擎支持BTREE索引，MEMORY支持HASH索引。

MySQL支持的索引有6種：普通索引、唯一索引、全文索引、單列索引、多列索引、空間索引

（1）索引的適用場景

以下情況適合創建索引：

經常被查詢的字段，即在WHERE子句中出現的字段。
在分組的字段，即在 GROUP BY 子句中出現的字段。
存在依賴關系的子表和父表之間的聯合查詢，即主鍵和外鍵字段。
設置唯一完整性約束的字段。

以下情況不適合創建索引：

在查詢中很少被使用的字段。
擁有許多重復值的字段。

（2）創建索引

a、普通索引

普通索引：在創建索引時，不附加任何限制條件（唯一、非空等限制）。該類型的索引可以創建在任何數據類型的字段上。

在創建索引時，可以指定索引的長度。這是因為不用的存儲引擎定義了表的最大索引數和最大索引長度。MySQL每個表至少支持16個索引，總索引長度為256字節。

在創建索引時，可以定義索引的長度和排序。ASC 升序排列； DESC 降序排列

-- 創建表時創建普通索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
INDEX index_deptno(deptno));
-- 在已經存在的表上創建索引
create INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通過Alter table 創建索引
alter table t_dept add INDEX index_deptno(deptno);

b、唯一索引

唯一索引：創建索引時，限制索引的值必須是唯一的。唯一索引可以更快速的查詢某條記錄。

-- 創建表時創建 唯一 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
UNIQUE INDEX index_deptno(deptno));
-- 在已經存在的表上創建 唯一 索引
create UNIQUE INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通過Alter table 創建 唯一 索引
alter table t_dept add UNIQUE INDEX index_deptno(deptno);

c、全文索引

全文索引主要是關聯在數據類型為:CHAR 、VARCHAR 和 TEXT 的字段上，以便能夠更加快速的查詢數據量較大的字符串類型的字段。只能在存儲引擎為MyISAM的數據庫表上創建全文索引。默認情況下，全文索引的搜索執行方式不區分大小寫，如果全文索引所關聯的字段為二進制數據類型，則以區分大小寫的搜索方式執行。

-- 創建表時創建 全文 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
FULLTEXT INDEX index_deptno(deptno)) ENGINE = MyISAM;
-- 在已經存在的表上創建 全文 索引
create FULLTEXT INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通過Alter table 創建 全文 索引
alter table t_dept add FULLTEXT INDEX index_deptno(deptno);

d、多列索引

多列索引：創建索引時，所關聯的字段不是一個字段，而是多個字段。只有查詢條件中使用了所關聯字段中的第一個字段，多列索引才被使用。

-- 創建表時創建  多列 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
INDEX index_deptno(deptno,loc));
-- 在已經存在的表上創建 多列 索引
create INDEX index_deptno ON t_dept(deptno,loc);
-- 通過Alter table 創建 多列 索引
alter table t_dept add INDEX index_deptno(deptno,loc);

（3）刪除索引

-- 刪除索引
DROP INDEX index_dname_loc ON t_dept;

4、視圖操作

（1）視圖的特點

視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關系。
視圖是由基本表（實表）產生的表（虛表）。
視圖的建立和刪除不影響基本表。
對視圖內容的更新（添加、刪除和修改）直接影響基本表。
當視圖來自多個基本表時，不允許添加和刪除數據。

（2）創建視圖

基本表結構：

創建視圖：

CREATE VIEW view_selectproduct AS SELECT id,name FROM t_product;

注意：在SQL語句命名規范中，視圖一般用 view_xxx 或者 v_xxx 命名。

（2）使用視圖

視圖的使用和正常的表查詢一樣：

SELECT * FROM view_selectproduct;

（3）不同種類的視圖

a、常量視圖

CREATE VIEW view_pi AS SELECT 3.1415926;

b、封裝聚合函數（SUM/MIN/MAX/COUNT等）

CREATE VIEW view_count_name AS SELECT COUNT(name) FROM t_product;

c、封裝排序（order by）

CREATE VIEW view_order_by AS SELECT name FROM t_product ORDER BY id DESC;

d、封裝內連接

CREATE VIEW view_inner_join AS SELECT s.name FROM t_student AS s,t_group as g WHERE s.group_id = g.id;

e、封裝外連接

CREATE VIEW view_left_join AS SELECT s.name FROM t_student AS s LEFT JOIN t_group AS g ON s.group_id = g.id;

其他子查詢、聯合查詢的視圖創建方式相似。

（4）查看視圖

SHOW TABLES;
SHOW TABLES STATUS FROM DB_NAME; -- 查看視圖的詳細信息
SHOW CREATE VIEW viewname; -- 查看視圖的定義信息

（5）刪除視圖

DROP VIEW view_name1,view_name2; -- 可以同時刪除多個視圖

（6）視圖操作基本表數據

INSERT INTO view_product (id, name, price ,order_id) VALUES (11,"apple",6,54); -- 插入數據
DELETE FROM view_product WHERE name = "apple"; -- 刪除數據
UPDATE view_product SET price = 4 WHERE name = "apple"; -- 修改數據

5、觸發器操作

觸發器：在表發生更改時，自動進行處理。

可以觸發器執行的語句： delete 語句、insert 語句、update 語句。

-- 觸發器
CREATE TRIGGER tri_diarytime 
    BEFORE INSERT ON t_dept FOR EACH ROW
         INSERT INTO t_diary VALUES(null,"t_dept",NOW());

四、MySQL數據操作

1、增、改、刪操作

（1）插入數據

-- 插入一條完整記錄
INSERT INTO t_dept(deptno,dname,loc) VALUES (5,'開發部','程序開發'); -- 推薦使用
INSERT INTO t_dept VALUES (5,'開發部','程序開發');-- 缺省格式，不推薦使用

-- 插入一條部分記錄
INSERT INTO t_dept(dname) VALUES ('設計部');

-- 插入多條記錄
INSERT INTO t_dept(dname) VALUES ('設計部'),('產品部'),('市場部'),('行政部');

-- 插入查詢的結果
INSERT INTO t_dept(dname) SELECT dname FROM t_company;

（2）更新數據

UPDATE t_dept SET dname = '測試部門' ,loc = '' where deptno = 1;

（3）刪除

DELETE FROM t_dept WHERE deptno < 10;

2、單表查詢

原始表：

（1）簡單數據記錄查詢

SELECT item_name,brand_id FROM z_item; -- 查詢商品名稱和品牌id
SELECT * FROM z_item; -- 查詢商品表中的所有字段

a、去重 DISTINCT:

SELECT DISTINCT  item_name ,brand_id FROM z_item;  -- 去重會取并集，即  去重的 所有字段 都重復 才進行 去重。

b、四則運算：

MySQL支持的數學運算符：

+ 加法； - 減法； * 乘法； / 除法；% 取余

SELECT price,(price+0.2) AS 'price+0.2',(price -0.2) AS 'price-0.2' , (price * 2) AS 'price * 2 ' ,
(price / 2) AS 'price/2',(price % 2 ) AS 'price % 2' FROM z_item ;

c、字符串連接CONCAT()

SELECT CONCAT('商品名稱是',item_name,'的商品，價格為',price) FROM z_item;

（2）條件查詢

a、MySQL支持的比較運算符和邏輯運算符

> 大于; < 小于 ; = 等于 ; != (<>) 不等于 ; >= 大于等于; <=小于等于;

AND(&&) 邏輯與 OR(||) 邏輯或 XOR 邏輯異或 NOT(!) 邏輯非

b、簡單條件查詢

SELECT * FROM z_item WHERE item_name = '鞋子';
SELECT * FROM z_item WHERE item_id <= 10 AND price > 1000;
SELECT * FROM z_item WHERE item_id <= 10 && price > 1000;

c、BETWEEN AND 關鍵字

SELECT * FROM z_item WHERE price BETWEEN 100 AND 1000;
SELECT * FROM z_item WHERE price NOT BETWEEN 100 AND 1000;

d、NULL 和 NOT NULL

SELECT * FROM z_item WHERE item_name IS NULL;
SELECT * FROM z_item WHERE item_name IS NOT NULL;

e、IN

SELECT * FROM z_item WHERE item_name = '冰箱' OR item_name = '空調' OR item_name = '洗衣機';
SELECT * FROM z_item WHERE item_name IN('冰箱','空調','洗衣機');
SELECT * FROM z_item WHERE item_name NOT IN('冰箱','空調','洗衣機');

注意：在具體使用IN 關鍵字時，查詢的結果如果存在NULL，則不會影響查詢；如果使用關鍵字 NOT IN ，查詢的集合中如果存在NULL，則不會有任何的查詢結果。

f、LIKE

通配符：用來實現匹配部分值的特殊符號。

SELECT * FROM z_item WHERE item_name LIKE '%機';
SELECT * FROM z_item WHERE NOT item_name LIKE '%機';
SELECT * FROM z_item WHERE item_name NOT LIKE '%機';
SELECT * FROM z_item WHERE price LIKE '_9%';

g、ORDER BY

ASC 升序 DESC 降序

SELECT * FROM z_item ORDER BY price DESC , item_id ASC;

h、LIMIT

SELECT * FROM z_item LIMIT 1;
SELECT * FROM z_item LIMIT 3,2; -- 第四條記錄開始顯示，顯示2條

i、統計函數和分組函數

-- 統計函數經常和分組一起使用
-- 統計條數   COUNT(*),統計所有值，包括null值和非null值； count(price)指定之后不統計null值。
SELECT COUNT(*) FROM z_item ;
-- 平均值
SELECT AVG(price) FROM z_item;
-- 求和
SELECT SUM(price) FROM z_item;
-- 最大值、最小值
SELECT MAX(price) FROM z_item;
SELECT item_name,MIN(price) FROM z_item;

-- 分組
SELECT *,SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id;
SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete;

SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete HAVING AVG( price) > 1000;

注：資料來源為書籍《MySQL數據庫應用從入門到精通》，本文為個人學習筆記。

總結

以上是生活随笔為你收集整理的MySQL基础的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

MySQL
基础

上一篇： Mybatis源码：Executor 模
下一篇：基于机器学习的图像质量评价算法