MySQL數據庫

MySQL Gitee筆記

小皮面板(PHP Study)

安裝路徑不能包含“中文”或者“空格”，否則會報錯。

點擊設置-文件位置-MySQL，打開文件路徑，復制路徑到Windows設置環境變量

設置環境變量：右鍵此電腦-屬性-高級系統設置-高級-環境變量-上邊是用戶環境變量，下面是系統環境變量。

在系統變量中找到Path-雙擊或者點編輯

點擊新建，把X:\phpstudy_pro\Extensions\MySQLX.X.X路徑復制上去。然后一路的確定即可。

把X:\phpstudy_pro\Extensions\MySQLX.X.X\bin文件配置到用戶變量中

點擊新建，把X:\phpstudy_pro\Extensions\MySQLX.X.X\bin路徑復制上去，然后一路確定即可。

打開CMD終端，輸入：mysql -uroot -proot

出現Welcome to the MySQL monitor表示配置成功。

數據庫：

1.初識MySQL語句：

每條命令以；或 \g 或 \G 結束

注釋:

單行：# 或 -- ... --

多行：/* ... */

增：create database 數據庫名稱 charset utf-8;

推薦：create database 數據庫名稱 default character set utf8 collate utf8_general_ci;

查看數據庫的創建細節：show create database 數據庫名稱;

查看所有數據庫：show databases;

切換數據庫：use 數據庫名稱;

查看當前所在數據庫：select database();

改：alter database 數據庫名稱 charset gbk;

刪：drop database 數據庫名稱;

數據表：

增：create table 數據表名稱(字段數據類型(寬度),約束,功能);

查：show create table 數據表名稱;

查看當前數據庫所有表：show tables;

數據表的結構形式查看：desc 數據表名稱;

改：alter table 數據表名稱 modify 字段數據類型(寬度),約束,功能;

alter table 數據表名稱 change 原字段改后字段數據類型(寬度);

刪：drop table 數據表名稱;

數據表數據：

增：insert 數據表名稱(字段1,字段2) values(數據1,'數據2'),(數據3,'數據4');

或insert into 數據表名稱(字段1,字段2) values(數據1,'數據2'),(數據3,'數據4');

查：select *from 數據表名稱;

select *from 數據庫名稱.數據表名稱;

select 字段1,字段2…… from 數據庫名稱.數據表;

改：update 數據庫名稱.數據表名稱 set 字段=數據;

更改一條數據：UPDATA 數據庫名稱.數據表名稱 SET 字段=數據 WHERE 字段=數據;

更改一條數據：update 數據庫名稱.數據表名稱 set 字段=數據 where 字段=數據;

刪除一條數據：delete 數據表 where 字段=數據;

復制：表結構+記錄：create table 新數據表名稱 select 字段 from 原數據表名稱;

只復制表結構不復制記錄：create table 新數據表名稱 select 字段 from 原數據表名稱 where條件不成立;

like:只復制表結構不復制記錄 create table 新數據表名稱 like 原數據表名稱;

刪：delete from 數據表名稱;

2.庫操作：

①.系統數據庫

information_schema：MySQL 等數據庫管理系統里的一個特殊系統數據庫。虛擬庫，不占用磁盤空間，存儲的是數據庫啟動后的一些參數。它的用途是存儲數據庫元數據，也就是有關數據庫本身的數據，像數據庫、表、列、索引等的定義和信息。如：用戶表信息，列信息，權限信息，字符信息等。

mysql：授權庫，主要存儲系統用戶的權限信息。

performance_schema：MySQL5.5開始新增的一個數據庫;主要用于收集數據庫服務器性能參數，記錄處理查詢請求時發生的各種事件， SQL 語句的執行、鎖的等待、線程的活動等。

sys:MySQL 5.7 版本引入的一個系統數據庫，它包含了一系列的視圖和存儲過程，用于提供關于數據庫服務器性能、配置、查詢執行等方面的詳細信息。這些視圖和存儲過程的數據是基于底層的系統表和性能架構表進行整合和處理的。通過查詢sys數據庫中的視圖，管理員和開發人員可以方便地獲取到數據庫運行狀態的各種指標，例如查詢執行時間、數據庫鎖信息、系統資源使用情況等，從而更好地進行性能優化、故障排查和系統管理。

test:MySQL數據庫系統自動創建的測試數據庫。

②.help:查看命令的使用方法。

③.表的操作：

存儲引擎：就是表的數據類型。

查看MySQL支持的存儲引擎：show engines;

MySQL自帶的庫：show create table mysql.user\G

顯示方式：show create table 數據表名稱\G

數據類型：

1. 整數類型

類型	字節大小	有符號范圍(Signed)	無符號-范圍(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

2. 字符串

類型	說明	使用場景
CHAR	固定長度，小型數據	身份證號、手機號、電話、密碼
VARCHAR	可變長度，小型數據	姓名、地址、品牌、型號
TEXT	可變長度，字符個數大于 4000	存儲小型文章或者新聞
LONGTEXT	可變長度，極大型文本數據	存儲極大型文本數據

3. 時間類型

類型	字節大小	示例
DATE	4	'2022-01-01'
TIME	3	'12:29:59'
DATETIME	8	'2020-01-01 12:29:59'
YEAR	1	'2017'
TIMESTAMP	4	'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

查看字符串的長度：select char_length(字段) from 數據表;

設置幾個字節長度就顯示幾個字節長度：SET sql_mode ='PAD_CHAR_TO_FULL_LENGTH';

枚舉類型enum:單選。只能在給定的范圍內選擇一個值。

集合類型set:多選。在給定的范圍內一個或一個以上的值。

UNIQUE 是一個重要的約束條件，主要用于保證表中某一列或者多列組合的值具有唯一性，即表中不會存在重復的記錄。

單列唯一：

-- 方式一
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50)
);    
-- 方式二
CREATE TABLE department (
    id INT,
    name CHAR(10),
    UNIQUE(id),
    UNIQUE(name)
);

多列&聯合唯一：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    UNIQUE (customer_id, product_id, order_date)
);

primary key主鍵：是一個極為關鍵的約束條件，不為空且唯一，它的作用是唯一標識表中的每一行記錄。

單列主鍵：當表中某一列的值能夠保證唯一性時，可將該列設為主鍵。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

多列&聯合主鍵：若單靠一列無法保證記錄的唯一性，可將多列組合起來設為主鍵。

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

修改數據表時添加主鍵：

-- 單列主鍵：
ALTER TABLE 數據表
ADD PRIMARY KEY (字段); 
-- 多列主鍵：
ALTER TABLE 數據表
ADD PRIMARY KEY (字段1, 字段2);

刪除主鍵：

ALTER TABLE 數據表
DROP PRIMARY KEY;

AUTO_INCREMENT 是一個非常實用的約束條件，主要用于為表中的某一列自動生成唯一的整數值，通常與 PRIMARY KEY 約束一起使用，為表中的每一行記錄生成一個唯一的標識符。

CREATE TABLE 數據表 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

除了在創建表時使用 AUTO_INCREMENT，它在修改表結構、數據插入、重置自增序列等場景中也有重要應用。

可以使用 ALTER TABLE 語句來實現。

-- 創建一個不含 AUTO_INCREMENT 的表
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50)
);

-- 為 product_id 列添加 AUTO_INCREMENT 屬性
ALTER TABLE products
MODIFY COLUMN product_id INT AUTO_INCREMENT PRIMARY KEY;
-- 移除 product_id 列的 AUTO_INCREMENT 屬性
ALTER TABLE products
MODIFY COLUMN product_id INT;

-- 清空表數據
DELETE FROM products;

-- 重置自增序列從 1 開始
ALTER TABLE products AUTO_INCREMENT = 1;

-- 復制 products 表的結構到 new_products 表
CREATE TABLE new_products LIKE products;

查詢變量名稱：show variables like 'auto_inc%';

設置auto_increment步長：set global auto_increment_increment=3;

設置auto_increment起始偏移量：set global auto_increment_offset=2;

強調：auto_increment_offset要<=auto_increment_increment.否則無效！

更改后必須先退出MySQL再登錄才能看到效果。

清空數據恢復默認：truncate 數據表;

foreign key外鍵:是一個表中的一列或多列，其值與另一個表的主鍵（Primary Key）相匹配。它創建了兩個表之間的關系，保證了引用完整性。

數據完整性：外鍵能夠防止在一個表中插入無效的數據，保證了數據的一致性。
建立關系：在數據庫中定義表與表之間的關系，讓數據之間的關聯更清晰。

先建被關聯的數據表，并且保證其不為空且唯一性：

-- 創建被關聯的department數據表

create table department(
id int,
name char(16),
commnet char(50)
);

-- 先在被關聯的department數據表里插入數據

insert into department values
(1,'IT','技術能力有限部門'),
(2,'銷售','銷售能力部門'),
(3,'財務','花錢特別多部門');

再建關聯的表：

-- 創建 staff 關聯表，并使用外鍵關聯到 department 表

create table staff(
id int primary key,
name char(10),
sex enum('male','female'),
department_id int,
foreign key(department_id) references department(id)
);

-- 再在關聯的staff數據表里插入數據

insert into staff values
(1,'勇哥','male',1),
(2,'白娘子','female',2),
(3,'小青','female',3),
(4,'許仙','male',2);

在這個例子中，staff表中的department列是外鍵，它引用了department表中的id列（主鍵）。這意味著在staff表中插入數據時，department_id的值必須在department表的id列中存在。

外鍵約束的操作：

級聯刪除（CASCADE DELETE）：當刪除父表中的記錄時，相關的子表記錄也會被自動刪除。
級聯更新（CASCADE UPDATE）：當更新父表中的主鍵值時，相關的子表記錄的外鍵值也會被自動更新。

-- 創建 departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 創建 employees 表，并設置外鍵約束，同時啟用級聯刪除和級聯更新
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 插入示例數據
INSERT INTO departments (id, name) VALUES (1, '技術部');
INSERT INTO employees (id, name, department_id) VALUES (1, '張三', 1);

-- 查看插入的數據
SELECT * FROM departments;
SELECT * FROM employees;

-- 嘗試刪除 departments 表中的記錄，觀察 employees 表的變化
DELETE FROM departments WHERE id = 1;

-- 再次查看 employees 表，確認相關記錄已被刪除
SELECT * FROM employees;

-- 重新插入數據
INSERT INTO departments (id, name) VALUES (1, '技術部');
INSERT INTO employees (id, name, department_id) VALUES (1, '張三', 1);

-- 嘗試更新 departments 表中的主鍵值，觀察 employees 表的變化
UPDATE departments SET id = 2 WHERE id = 1;

-- 再次查看 employees 表，確認外鍵值已被更新
SELECT * FROM employees;

一對多關系：是指在兩個表中，一個被關聯表（通常稱為主表或父表）中的一條記錄可以與另一個關聯表（通常稱為從表或子表）中的多條記錄相關聯。

多對一關系：指的是在一個關聯表（子表）里的多條記錄能夠和另一個被關聯表（父表）中的同一條記錄相對應。

一對多數據表或稱為多對一數據表：

/* 一對多或（多對一）：一個出版社可以出版多本書
關聯方式：foreign key
一對多或是多對一表 */

create table press(
id int primary key auto_increment,
name varchar(20));

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade);

insert into press(name) values
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識產權沒有用出版社');
insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨狐九劍',3),
('降龍十八掌',2),
('葵花寶典',3);

解釋：在 book 表中設置的外鍵約束 foreign key(press_id) references press(id) on delete cascade on update cascade，其作用是維護數據的一致性。

foreign key(press_id)：這部分表明在 book 表中，press_id 列被指定為外鍵。外鍵是一種特殊的字段，其作用是建立不同表之間的關聯。

references press(id)：此部分說明 press_id 列引用了 press 表的 id 列。也就是說，book 表中的 press_id 列的值必須是 press 表中 id 列已經存在的值。

on delete cascade 表示當刪除 press 表中的某條出版社記錄時，book 表中所有關聯該出版社的書籍記錄會被自動刪除；

on update cascade 表示當更新 press 表中某條記錄的 id 時，book 表中關聯該出版社的書籍記錄的 press_id 會自動更新。

多對多關系：多對多關系指的是兩個表之間，一個表中的一條記錄能夠和另一個表中的多條記錄相關聯，反之亦然。

在上述數據庫設計中，press 表和 book 表之間是一對多關系，如果要實現多對多關系，通常需要引入一個中間表。

-- 創建出版社表
CREATE TABLE press (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 創建圖書表
CREATE TABLE book (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 創建中間表，用于建立多對多關系
CREATE TABLE book_press_relation (
    book_id INT,
    press_id INT,
    PRIMARY KEY (book_id, press_id),
    FOREIGN KEY (book_id) REFERENCES book(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (press_id) REFERENCES press(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 向出版社表插入數據
INSERT INTO press (name) VALUES
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識產權沒有用出版社');

-- 向圖書表插入數據
INSERT INTO book (name) VALUES
('九陽神功'),
('九陰真經'),
('九陰白骨爪'),
('獨狐九劍'),
('降龍十八掌'),
('葵花寶典');

-- 向中間表插入數據，建立圖書與出版社的多對多關系
INSERT INTO book_press_relation (book_id, press_id) VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 3),
(5, 2),
(6, 3),
-- 假設《九陽神功》同時由人民音樂不好聽出版社出版
(1, 2);

一對一關系：指的是被關聯表（主表）中的一條記錄僅能和關聯表（從表）中的一條記錄關聯，反之亦然。

-- 創建客戶表
create table customer(
    id int primary key auto_increment,
    name varchar(20) not null,
    qq varchar(11) not null,
    phone char(16) not null
);

-- 創建學生表
create table student(
    id int primary key auto_increment,
    class_name varchar(20) not null,
    customer_id int unique, -- 該字段一定要是唯一的
    foreign key(customer_id) references customer(id) -- 外鍵的字段一定要保證 unique
    on delete cascade
    on update cascade
);

-- 增加客戶
insert into customer(name,qq,phone) values
('李飛機','1234567','13011223344'),
('王大炮','7654321','13155667788'),
('守榴彈','11223344','13011223344'),
('吳坦克','55667788','13300112233'),
('嬴火箭','6688668','15533445567'),
('戰地雷','56445787','15665487987');

-- 增加學生
insert into student(class_name,customer_id) values
('起飛3班',3),
('陸地18班',4),
('陸地18班',5);

posted @ 2025-03-15 16:26 自學小天才閱讀(136) 評論(0) 收藏舉報

刷新頁面返回頂部

xxy002

MySQL數據庫

1. 整數類型

2. 字符串

3. 時間類型

公告