2022-08-15 - 初識MySQL

• 2022 年 8 月 21 日
• 筆記
• 資料庫初識

MySQL資料庫

資料庫

資料庫,又稱為Database，簡稱DB。資料庫就是一個文件集合。
顧名思義：是一個存儲數據的倉庫，實際上就是一堆文件，這些文件中存儲了具有特定格式的數據，可以很方便的對裡面的數據進行增刪改查等操作。

資料庫管理系統

DataBase Management System，簡稱DBMS。
資料庫管理系統是專門用來管理資料庫中的數據的，可以對資料庫當中的數據進行增刪改查。
常見的DBMS有：MySQL、Oracle、DB2、SQLite 、SqlServer。

SQL:結構化查詢語言

程式設計師主要學習SQL語句，通過編寫SQL語句，然後DBMS負責執行SQL語句，最終來完成資料庫中數據的增刪改查操作。

三者之間的關係？？

DBMS--執行-->SQL--操作-->DB

基本操作

MySQL服務啟停

啟動服務：net start mysql
停止服務：net stop mysql

登錄MySQL

第一種：
mysql -h 127.0.0.1 -p3306 -uroot -p
第二種：
mysql -uroot -p

退出資料庫：exit

創建一個資料庫

create database 資料庫名;
create schema 資料庫名;

查看所有的資料庫

show databases;
MySQL默認自帶了4個資料庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pythonweb |
| questionnaire |
| sys |
+--------------------+

使用資料庫

use 資料庫名

表

資料庫中最基本的單元是：表(table)
用來存儲數據的對象，是有結構的數據的集合。
數據表是一個臨時保存數據的網格虛擬表(表示記憶體中數據的一個表)。

數據表是由表名、欄位、欄位類型、欄位長度、數據記錄組成。數據表之間的關聯通過「鍵」來實現的，鍵分為主鍵和外鍵兩種。
任何一張表都有行和列。

• 行：一行即為一條數據，資料庫一共有多少條數據，實際上就是有幾行數據。
• 列：一列即為一個欄位，資料庫一共有多少個欄位，實際上就是有幾列數據。

SQL語言

SQL是一種特殊目的的程式語言，是一種資料庫查詢和程式設計語言，用於存儲數據以及查詢、更新和管理關係型資料庫系統。

SQL分類

根據功能主要分為四類：DDL、DML、DQL、DCL

• DCL（Data Control Language）：數據控制語言，用來創建資料庫用戶、定義訪問許可權和安全級別。
• DDL（Data Definition Language）：數據定義語言，用來定義資料庫對象：庫，表，欄位（列）。功能：創建、刪除、修改庫和表結構。
• DML（Data Manipulation Language）：數據操作語言，用來定義數據的增刪改記錄。
• DQL（Data Query Language）：資料庫查詢語言，用來查詢資料庫中表的記錄。
• TCL（Transition Control Language）：事務控制語言，用來管理事務。

DDL（數據定義語言）

DDL主要是用在定義或改變表的結構。

• 查詢
  查詢所有資料庫SHOW DATABASES;
  查詢當前資料庫SELECT DATABASE();
• 創建
  create table 表名(
  欄位名1(列名) 類型(長度) 約束條件,
  欄位名2(列名) 類型(長度) 約束條件,
  欄位名3(列名) 類型(長度) 約束條件,
  .......
  );
  在關係型資料庫中，我們需要這頂表名和列名，同時設定。

數據類型

整型

在整型中，我們默認使用的是【有符號】的，我們可以使用unsigned關鍵字，定義成無符號類型，tinyint unsigned的取值範圍0~255。

如果長度需要配合zerofill：

int(4) unsigned zerofill;

說明：上述的int長度為4，如果設置了zerofill，如果數據是1，最終存到表格中的數據格式為0001，0010。

浮點型

比如定義一個float(5,3)：

• 插入123.45678，最後查詢得到的結果就是99.999；
• 插入12.3456789，最後查詢得到的結果就是12.346；

所以，在使用浮點型的時候，要以插入到資料庫中的實際結果為準。

字元串類型

（1）char和vachar的區別：

char
char表示定長字元串，長度是固定的；
如果插入數據的長度小於char的固定長度時，則用空格填充；
因為長度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因為其長度固定，所以會佔據多餘的空間，是空間換時間的做法；
對於char來說，最多能存放的字元個數為255，和編碼無關
varchar
varchar表示可變長字元串，長度是可變的；
插入的數據是多長，就按照多長來存儲；
varchar在存取方面與char相反，它存取慢，因為長度不固定，但正因如此，不佔據多餘的空間，是時間換空間的做法；
對於varchar來說，最多能存放的字元個數為65532
日常的設計，對於長度相對固定的字元串，可以使用char，對於長度不確定的，使用varchar更合適一些。
（2）varchar和text區別:

• text不能設置默認值，varchar可以設置默認值。
• text類型，由於單表的最大行寬的限制，支援溢出存儲，只會存放768位元組在數據頁中，剩餘的數據存儲在溢出段中。
• 一般我們都是用varchar。

日期類型

建表約束

因為一張表要有多個列，資料庫中的表不止有一張，建表約束說的就是我們 應該如何規範表中的數據以及表之間的關係。

MySQL約束類型：

（1）NOT NULL約束

CREATE TABLE `student` (
	`stu_id` int,
	`stu_name` VARCHAR(50) NOT NULL,
	`gender` char(1) DEFAULT '男',
	`brithday` datetime,
	PRIMARY KEY(stu_id)
);

（2）UNIQUE約束

create table `book` (
	`id` int PRIMARY KEY auto_increment,
	`name` varchar(50) not null,
	`bar_code` VARCHAR(30) not null,
	`aut_id` int not null,
	UNIQUE(bar_code)
);

（3）主鍵約束

用多個列來共同當主鍵：

create table `author`(
	`aut_id` int,
	`aut_name` varchar(50) not null,
	`gender` char(1) default '男',
	`country` varchar(50),
	`birthday` datetime,
	PRIMARY KEY(aut_id,aut_name)
);

（4）外鍵約束

推薦配合主鍵去使用。有了這個約束，我們在向表中插入數據時，來源於另外一張表的主鍵

外鍵會產生的效果：

1. 刪除表的時候，如果不刪除引用外鍵的表，被引用的表是不能直接刪除。
2. 外鍵的值必須來源於引用的表的主鍵字元。

create table `author`(
	`aut_id` int,
	`aut_name` varchar(50) not null,
	`gender` char(1) default '男',
	`country` varchar(50),
	`birthday` datetime,
	PRIMARY KEY(aut_id)
);

create table `book` (
	`id` int PRIMARY KEY auto_increment,
	`name` varchar(50) not null,
	`bar_code` VARCHAR(30) not null UNIQUE,
	`aut_id` int not null,
	FOREIGN KEY(aut_id) REFERENCES author(aut_id)
);

在創建表的時候，建議欄位名使用著重符。

對錶的修改操作

查看當前庫中的所有表：

show tables;

查看錶結構：

desc 表名;

修改表有5個操作，但是前綴都是一樣的alter table 表名 ....。

• 添加列

  ALTER table author add (hobby varchar(20),address varchar(50));
  

• 修改列數據類型

  ALTER table author MODIFY address varchar(100);
  

• 修改列名稱和數據類型

  alter table author change address addr VARCHAR(60);
  

• 刪除列

  alter table author drop addr;
  

• 修改表名

  ALTER TABLE author RENAME `authors`;
  

• 刪除表

  drop table if EXISTS `user`;
  

• 添加主鍵

  alter table 表名 ADD CONSTRAINT 主鍵名(pk_表名) primary key 表名(欄位名);
  

  ALTER TABLE `authors` ADD CONSTRAINT pk_authors PRIMARY KEY `authors` ( aut_id );
  

• 添加外鍵

  alter table 從表 add constraint 外鍵名(fk_從表_主表) foreign key 從表(外鍵欄位) REFERENCES 主表(主鍵欄位;)
  

  ALTER TABLE book ADD CONSTRAINT fk_book_authors FOREIGN KEY book ( aut_id ) REFERENCES `authors` ( aut_id );
  

DML（數據操作語言）

該語言來對錶記錄進行操作（增、刪、改），不包含查詢。

插入數據

INSERT INTO `authors` ( aut_id, aut_name, gender, country, birthday, hobby ) VALUES (4,'羅曼羅蘭','女','漂亮國','1945-8-15','寫字');

如果插入的是全欄位，欄位名可以省略。

INSERT INTO `authors` VALUES (5,'韓寒','男','中國','1984-8-15','賽車');

說明：

1. 在資料庫中所有的字元串類型，必須使用引號。
2. 如果部分欄位插入，必須列名和值要匹配。如果全欄位插入，則列名可以省略。

批量插入。

INSERT INTO `authors` VALUES 
(7,"李誕",'男','中國','1985-8-15','脫口秀'),
(8,"史鐵生",'男','中國','1967-8-15','繪畫');

修改數據

修改某列的全部的值：

update `authors` set aut_name = '郭小四',country='中國';

修改特定行的數據：

update `authors` set aut_name = '金庸',country='中國' where aut_id = 1;

where是一個關鍵字，我們可以使用where關鍵字實現豐富的篩選，它很像我們的if語句，可以使用各種複雜的條件運算：

• =（沒有==，也沒有equals）
• !=
• >
  • where aut_id > 1
• <
• >=
• <=
• <> 不等於
• between...and
  • where aut_id between 1 and 4
  • where aut_id > 1 and aut_name='xxx'
• in(....)
  • where aut_id in(1,3,5)
• is null
  • where name is null
• not
  • where name is not null
• or
• and

刪除數據

全部刪除：

delete from `student`;

根據條件刪除：

delete from `authors` where aut_id = 8;

說明：通過delete這種刪除方式刪除的數據，主鍵如果是自動遞增，會斷檔。

截斷（清空表）：

TRUNCATE student;

說明：

truncate實際上應該屬於DDL語言，操作立即生效，不能撤回。

• truncate和delete都是刪除數據，drop刪除整個表。
• truncate速度快，效率高，可以理解為直接刪除整個表，再重新建立。
• truncate和delete都不會是表結構及其列、約束、索引的發生改變。