MySQL資料庫管理

来源:https://www.cnblogs.com/sl08/archive/2023/09/17/17709419.html
-Advertisement-
Play Games

MySQL資料庫管理 資料庫-->數據表-->行（記錄）：用來描述一個對象的信息 列（欄位）：用來描述對象的一個屬性 常用的數據類型： int ：整型 無符號[0,2^32-1]，有符號[-2^31,2^31-1] float ：單精度浮點 4位元組32位 double ：雙精度浮點 8位元組64位 c ...


MySQL資料庫管理

資料庫-->數據表-->行（記錄）：用來描述一個對象的信息 列（欄位）：用來描述對象的一個屬性

常用的數據類型：

int ：整型 無符號[0,2^32-1]，有符號[-2^31,2^31-1] float ：單精度浮點 4位元組32位 double ：雙精度浮點 8位元組64位 char ：固定長度的字元類型 varchar ：可變長度的字元類型 text ：文本 image ：圖片 decimal(5,2) ：5個有效長度數字，小數點後面有2位

--------查看資料庫結構--------

1．查看當前伺服器中的資料庫

SHOW DATABASES;                     #大小寫不區分，分號“;”表示結束

image-20230913171353532

2．查看資料庫中包含的表

USE 資料庫名;//選擇要查看的庫，進入該庫
SHOW TABLES;//顯示當前選擇的資料庫中的所有表

image-20230913172214985

3．查看表的結構（欄位）

USE 資料庫名;
DESCRIBE [資料庫名.]表名;
可縮寫成：DESC 表名;

image-20230913173024093

image-20230913173218522

image-20230913173520144

 

--------SQL語句--------

SQL語句用於維護管理資料庫，包括數據查詢、數據更新、訪問控制、對象管理等功能。

SQL語言分類： DDL：數據定義語言，用於創建資料庫對象，如庫、表、索引等 DML：數據操縱語言，用於對錶中的數據進行管理 DQL：數據查詢語言，用於從數據表中查找符合條件的數據記錄 DCL：數據控制語言，用於設置或者更改資料庫用戶或角色許可權

----創建及刪除資料庫和表----

1．創建新的資料庫

CREATE DATABASE 資料庫名;

image-20230913174625812

2．創建新的表

CREATE TABLE 表名 (欄位1 數據類型,欄位2 數據類型[,...][,PRIMARY KEY (主鍵名)]);
#主鍵一般選擇能代表唯一性的欄位不允許取空值（NULL），一個表只能有一個主鍵。
​
例：
CREATE DATABASE fsj;
USE fsj;
create table fsj01 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));
​
#用於在資料庫中創建一個名為"fsj01"的表格。
"id"欄位，類型為整數（int），不能為空（NOT NULL）。
"name"欄位，類型為字元型（char），長度為10個字元，不能為空（NOT NULL）。
"score"欄位，類型為十進位數（decimal），總長度為5位，其中有2位小數。
"passwd"欄位，類型為字元型（char），長度為48個字元，預設值為空字元串（‘’）。
"id"欄位被定義為主鍵（PRIMARY KEY），用於唯一標識每一行數據。
​
DESC fsj01;//查看該表結構

image-20230913185415947

3．刪除指定的數據表

DROP TABLE [資料庫名.]表名;               #如不用USE進入庫中，則需加上資料庫名

image-20230914172128098

4．刪除指定的資料庫

DROP DATABASE 資料庫名;

image-20230914172440414

 

----管理表中的數據記錄----

1．向數據表中插入新的數據記錄

INSERT INTO 表名(欄位1,欄位2[,...]) VALUES(欄位1的值,欄位2的值,...);

例：

INSERT INTO fsj01(id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
#PASSWORD('123456')：查詢數據記錄時，密碼字串以加密形式顯示；若不使用PASSWORD()，查詢時以明文顯示。
​
INSERT INTO fsj01 VALUES(2,'lisi',90.5,123456);
​
SELECT * FROM fsj01;                    #查詢表的數據記錄
將會返回"fsj01"表中的所有行和所有列的數據。符號 “*” 表示選擇所有的列。

image-20230914181151211

2．查詢數據記錄

SELECT 欄位名1,欄位名2[,...] FROM 表名 [WHERE 條件表達式];

例：

SELECT * FROM fsj01;
SELECT id,name,score FROM fsj01 WHERE id=2;

select name from fsj01\G					#以列表方式豎向顯示
select * from fsj01 limit 2;				#只顯示頭2行
select * from fsj01 limit 2,3;			#顯示第2行後的前3行

image-20230914181938560

image-20230914182414797

image-20230914183049668

image-20230914183229195

3．修改、更新數據表中的數據記錄

UPDATE 表名 SET 欄位名1=欄位值1[,欄位名2=欄位值2] [WHERE 條件表達式];

例：

UPDATE fsj01 SET passwd=PASSWORD('') WHERE name='zhangsan';
UPDATE fsj01 SET name='wangxiaoer',passwd='' WHERE id=3;

image-20230914184532255

 

4．在數據表中刪除指定的數據記錄

DELETE FROM 表名 [WHERE 條件表達式];

例：

DELETE FROM fsj01 WHERE id=4;

image-20230914190406719

----修改表名和表結構----

1．修改表名

ALTER TABLE 舊表名 RENAME 新表名;

例：

ALTER TABLE fsj01 RENAME ylc;
ALTER TABLE ylc RENAME fsj;

image-20230914190922142

2．擴展表結構（增加欄位）

ALTER TABLE 表名 ADD address varchar(50) default '地址不詳';
#default '地址不詳'：表示此欄位設置預設值 地址不詳；可與 NOT NULL 配合使用

image-20230914195048442

3．修改欄位（列）名，添加唯一鍵

唯一鍵唯一但是可以為空值

ALTER TABLE 表名 CHANGE 舊列名 新列名 數據類型 [unique key];
**例：**
ALTER TABLE ylc CHANGE name user_name varchar(10) unique key;
#CHANGE可修改欄位名、數據類型、約束等所有項。

image-20230914211351003

4．刪除欄位

ALTER TABLE 表名 DROP 欄位名;

例：

ALTER TABLE ylc DROP passwd;

image-20230916153833547

案例擴展：

use fsj;
create table if not exists info ( #如果有info表就不執行，沒有info表自動創建
id int(4) zerofill primary key auto_increment,	zerofill用零填充0001 primary key #指定主鍵的第二種方式auto_increment 自增長欄位不指定每次自增一
name varchar(10) not null default '匿名',
cardid int(18) not null unique key);

image-20230916163049292

image-20230916170802518

hobby varchar(50));

**#if not exists：**表示檢測要創建的表是否已存在，如果不存在就繼續創建
**#int(4) zerofill：**表示若數值不滿4位數，則前面用“0”填充，例0001
**#auto_increment：**表示此欄位為自增長欄位，即每條記錄自動遞增1，預設從1開始遞增；自增長欄位數據不可以重覆；自增長欄位必須是主鍵；如添加的記錄數據沒有指定此欄位的值且添加失敗也會自動遞增一次
**#unique key：**表示此欄位唯一鍵約束，此欄位數據不可以重覆；一張表中只能有一個主鍵, 但是一張表中可以有多個唯一鍵

 

#not null：表示此欄位不允許為NULL

----數據表高級操作----

1．克隆表，將數據表的數據記錄生成到新的表中

方法一：

create table test01 like ylc;               #通過 LIKE 方法，複製 ylc 表結構生成 test01 表
insert into test01 select * from ylc;
​
#此方法能保證 新表的表結構、表數據 跟舊表都是一致的

image-20230917123444932

image-20230917124019619

方法二：

CREATE TABLE test02 (SELECT * from ylc);

#此方法創建的新表的表數據和舊表是一樣的，但可能會出現新表的表結構和舊表的不一致

show create table test02\G					#獲取數據表的表結構、索引等信息
SELECT * from test02;

image-20230917124704798

image-20230917124954374

image-20230917125215458

2．清空表，刪除表內的所有數據 方法一：

delete from test02;
#DELETE清空表後，返回的結果內有刪除的記錄條目；DELETE工作時是一行一行的刪除記錄數據的；如果表中有自增長欄位，使用DELETE FROM 刪除所有記錄後，再次新添加的記錄會從原來最大的記錄 ID 後面繼續自增寫入記錄。

image-20230917144921807

方法二：

truncate table test01;
#TRUNCATE 清空表後，沒有返回被刪除的條目；TRUNCATE 工作時是將表結構按原樣重新建立，因此在速度上 TRUNCATE 會比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表內數據後，ID 會從 1 開始重新記錄。

image-20230917150206757

3．創建臨時表 臨時表創建成功之後，使用SHOW TABLES命令是看不到創建的臨時表的，臨時表會在連接退出後被銷毀。 如果在退出連接之前，也可以可執行增刪改查等操作，比如使用 DROP TABLE 語句手動直接刪除臨時表。

CREATE TEMPORARY TABLE 表名 (欄位1 數據類型,欄位2 數據類型[,...][,PRIMARY KEY (主鍵名)]);

 

例：
create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
​
insert into test03 values(1,'zhangsan',123456,'running');
​
select * from test03;
show tables;
​
quit
select * from test03;

image-20230917153039841

image-20230917153320021

image-20230917153525417

 

4．創建外鍵約束，保證數據的完整性和一致性。

外鍵的定義：如果同一個屬性欄位X在表一中是主鍵，而在表二中不是主鍵，則欄位X稱為表二的外鍵。

主鍵表和外鍵表的理解： （1）以公共關鍵字作主鍵的表為主鍵表（父表、主表） （2）以公共關鍵字作外鍵的表為外鍵表（從表、外表）

註意：與外鍵關聯的主表的欄位必須設置為主鍵。要求從表不能是臨時表，主從表的欄位具備相同的數據類型、字元長度和約束。

#創建主表 profession
create table profession (pid int(4),proname varchar(50));

image-20230917154028382

#創建從表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));

image-20230917154824753

//ALTER TABLE 是用於在關係型資料庫中修改已存在的表結構的命令。通過 ALTER TABLE，可以添加、修改或刪除表的列、約束等。

#為主表 profession 添加一個主鍵約束。主鍵名建議以“PK_”開頭。
alter table profession add constraint PK_pid primary key (pid);

image-20230917155711205

#為從表 student 表添加外鍵，並將 student 表的 proid 欄位和 profession 表的 pid 欄位建立外鍵關聯。外鍵名建議以“FK_”開頭。
alter table student add constraint FK_pro foreign key (proid) references profession (pid);

desc student;

image-20230917160450887

 

#插入新的數據記錄時，要先主表再從表
insert into profession values(1,'雲計算');
insert into profession values(2,'大數據');
insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',19,1);
insert into student values(3,'wangwu',20,2);

image-20230917161012375

image-20230917161533666

#刪數數據記錄時，要先從表再主表，也就是說刪除主鍵表的記錄時必須先刪除其他與之關聯的表中的記錄。
delete from student where proid=1;
delete from profession where pid=1;

image-20230917161948149

#查看和刪除外鍵約束
show create table student;
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

image-20230917162115152

image-20230917164439532

MySQL中6種常見的約束：

主鍵約束（primary key） 外鍵約束（foreign key） 非空約束（not null） 唯一性約束（unique [key|index]） 預設值約束（default） 自增約束（auto_increment）

----資料庫用戶管理----

1．新建用戶

CREATE USER '用戶名'@'來源地址' [IDENTIFIED BY [PASSWORD] '密碼'];

'用戶名'：指定將創建的用戶名 '來源地址'：指定新創建的用戶可在哪些主機上登錄，可使用IP地址、網段、主機名的形式，本地用戶可用localhost，允許任意主機登錄可用通配符% '密碼'：若使用明文密碼，直接輸入'密碼'，插入到資料庫時由Mysql自動加密； 若使用加密密碼，需要先使用SELECT PASSWORD('密碼'); 獲取密文，再在語句中添加 PASSWORD '密文';

若省略“IDENTIFIED BY”部分，則用戶的密碼將為空（不建議使用）

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
​
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

image-20230917165715403

2．查看用戶信息

#創建後的用戶保存在 mysql 資料庫的 user 表裡
USE mysql;
SELECT User,authentication_string,Host from mysql.user;
#這將返回用戶表中的用戶名，密碼和主機信息，其中 mysql.user 是存儲用戶信息的表。

image-20230917165814312

image-20230917170111703

#查看當前登錄用戶

select user();

image-20230917170328385

3．重命名用戶

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

image-20230917170623020

4．刪除用戶

DROP USER 'lisi'@'localhost';

image-20230917170829807

5．修改當前登錄用戶密碼

SET PASSWORD = PASSWORD('abc123');

image-20230917171117510

6．修改其他用戶密碼

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123');

image-20230917171254583

7．忘記 root 密碼的解決辦法

（1）修改 /etc/my.cnf 配置文件，不使用密碼直接登錄到 

mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables                   #添加，使登錄mysql不使用授權表
​
systemctl restart mysqld
​
mysql                               #直接登錄

image-20230917171535817

image-20230917171702224

（2）使用 update 修改 root 密碼，刷新資料庫

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';
​
FLUSH PRIVILEGES;
quit
​
mysql -u root -pabc123

image-20230917172015866

註意：最後再把 /etc/my.cnf 配置文件里的 skip-grant-tables 刪除，並重啟 mysql 服務。

----資料庫用戶授權----

1．授予許可權 GRANT語句：專門用來設置資料庫用戶的訪問許可權。當指定的用戶名不存在時，GRANT語句將會創建新的用戶；當指定的用戶名存在時， GRANT 語句用於修改用戶信息。 

GRANT 許可權列表 ON 資料庫名.表名 TO '用戶名'@'來源地址' [IDENTIFIED BY '密碼'];

#許可權列表：用於列出授權使用的各種資料庫操作，以逗號進行分隔，如“select,insert,update”。使用“all”表示所有許可權，可授權執行任何操作。 #資料庫名.表名：用於指定授權操作的資料庫和表的名稱，其中可以使用通配符“”。例如，使用“kgc.”表示授權操作的對象為 kgc資料庫中的所有表。 #'用戶名@來源地址'：用於指定用戶名稱和允許訪問的客戶機地址，即誰能連接、能從哪裡連接。來源地址可以是功能變數名稱、IP地址，還可以使用“%”通配符，表示某個區域或網段內的所有地址，如“%.kgc.com”、“192.168.80.%”等。

#IDENTIFIED BY：用於設置用戶連接資料庫時所使用的密碼字元串。在新建用戶時，若省略“IDENTIFIED BY”部分，則用戶的密碼將為空。

例子：

#允許用戶 zhangsan 在本地查詢 kgc 資料庫中 所有表的數據記錄，但禁止查詢其他資料庫中的表的記錄。

GRANT select ON fsj.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';

image-20230917181050666

#允許用戶 lisi 在所有終端遠程連接 mysql ，並擁有所有許可權。

GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
​
flush privileges;
quit
​
mysql -u zhangsan -p123456
use fsj;
show tables;
select * from ylc;

2．查看許可權

SHOW GRANTS FOR 用戶名@來源地址;

例：

SHOW GRANTS FOR 'lisi'@'%';

3．撤銷許可權

REVOKE 許可權列表 ON 資料庫名.表名 FROM 用戶名@來源地址;

例：

REVOKE ALL ON *.* FROM 'lisi'@'%';
​
SHOW GRANTS FOR 'lisi'@'%';
#USAGE許可權只能用於資料庫登陸，不能執行任何操作；USAGE許可權不能被回收，即 REVOKE 不能刪除用戶。
​
flush privileges;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 學習多線程
    學習多線程 1.認識程式，進程，線程 程式 程式由指令和數據組成，是靜態的 進程 啟動一個程式，就啟動了一個進程。 進程就是程式的一次執行過程。 進程是動態的。 線程 一個進程包含多個線程。例如：播放視頻時，有動畫，有聲音，有彈幕…… 2.如何創建線程 繼承Thread類 三板斧： 繼承Thread ...
  • shardingdb：支持分片和併發讀寫的 GoLevelDB
    概述 shardingdb 是一個開源包，旨在為 GoLevelDB 增加分片和併發讀寫功能。它可以作為 LevelDB 的替代品，方便地集成到現有項目中。本博客將介紹 shardingdb 及其功能，並介紹如何在您的項目中使用它。 特點 - 分片支持：shardingdb 使您能夠將數據分佈在多個 ...
  • spring security為啥是個垃圾框架？
    古時候寫代碼，許可權這塊寫過一個庫，基本就是一個泛型介面，裡面有幾個方法： 如驗證輸入的principal和credentials，返回token和authorities和roles，role就是一堆authorities集，也就說就是返回一堆authorities。然後每次請求會拿token找到au ...
  • Python 潮流周刊第 20 期（摘要）
    在設備維修保養管理中，及時通知相關人員是確保設備得到及時維護的關鍵。API介面提供了一個方便的方式來自動發送維修保養通知，以確保工作流程的順利進行。本文將詳細介紹如何使用成熟的API介面來發送設備維修保養通知，以確保設備得到及時的維護，同時提供通俗易懂的步驟和代碼說明。 什麼是API介面？ 首先，讓 ...
  • linux常見命令（分類展示）
    一、文件管理： 1.1、ls： ​ 顯示文件/目錄屬性 常見參數： -l ：列出長數據串，包含文件的屬性與許可權數據等 -a ：列出全部的文件，連同隱藏文件（開頭為.的文件）一起列出來（常用） -d ：僅列出目錄本身，而不是列出目錄的文件數據 -h ：將文件容量以較易讀的方式（GB，kB等）列出來 - ...
  • 9-MySQL資料庫 數據的備份與恢復
    1.date文件的備份 2.mysqldump 備份 說明：mysqldump是MySQL資料庫中的一個實用程式，它主要用於轉儲（備份）資料庫。mysqldump通過生成一個SQL腳本文件，包含從頭開始重新創建資料庫所必需的（如 CREATE TABLE和INSERT等），來實現資料庫的備份和轉儲。 ...
  • 8-MySQL查詢高級用法，數據表的關聯關係
    1.分組 group by 詳情見，發佈的第七篇博客文章，7- MySQL函數 2.排序 order by 說明：在MySQL中，ORDER BY是一種用於對查詢結果進行排序的關鍵字。它可以根據一列或多列的值，以升序或降序的方式對查詢結果進行排序，使得查詢者可以更加方便 地查看、分析和處理數據。 使 ...
  • 7-MySQL函數
    1.分組group by 在MySQL中，GROUP BY的意思是“分組查詢”，它可以根據一個或多個欄位對查詢結果進行分組。 GROUP BY的作用是通過一定的規則將一個數據集劃分成若幹個小的區域，然後針對若幹個小區域進行數據處理。這可以理解為將數據按照某個欄位或者多個欄位進行分組。 使用GROUP ...
一周排行
    -Advertisement-
    Play Games
  • 實用接地氣的 .NET 微服務框架
    前言 微服務架構已經成為搭建高效、可擴展系統的關鍵技術之一，然而，現有許多微服務框架往往過於複雜，使得我們普通開發者難以快速上手並體驗到微服務帶了的便利。為瞭解決這一問題，於是作者精心打造了一款最接地氣的 .NET 微服務框架，幫助我們輕鬆構建和管理微服務應用。 本框架不僅支持 Consul 服務註 ...
  • 基於surging的木舟IOT平臺如何添加網路組件
    先看一下效果吧: 如果不會寫動畫或者懶得寫動畫，就直接交給Blend來做吧; 其實Blend操作起來很簡單，有點類似於在操作PS，我們只需要設置關鍵幀，滑鼠點來點去就可以了，Blend會自動幫我們生成我們想要的動畫效果. 第一步：要創建一個空的WPF項目 第二步：右鍵我們的項目，在最下方有一個，在B ...
  • Prism:框架介紹與安裝
    Prism:框架介紹與安裝 什麼是Prism? Prism是一個用於在 WPF、Xamarin Form、Uno 平臺和 WinUI 中構建鬆散耦合、可維護和可測試的 XAML 應用程式框架 Github https://github.com/PrismLibrary/Prism NuGet htt ...
  • 不可不知的WPF畫筆（Brush）
    在WPF中，屏幕上的所有內容，都是通過畫筆（Brush）畫上去的。如按鈕的背景色，邊框，文本框的前景和形狀填充。藉助畫筆，可以繪製頁面上的所有UI對象。不同畫筆具有不同類型的輸出（ 如：某些畫筆使用純色繪製區域，其他畫筆使用漸變、圖案、圖像或繪圖）。 ...
  • .NET 8 高併發微服務電商系統實戰
    前言 嗨，大家好！推薦一個基於 .NET 8 的高併發微服務電商系統，涵蓋了商品、訂單、會員、服務、財務等50多種實用功能。 項目不僅使用了 .NET 8 的最新特性，還集成了AutoFac、DotLiquid、HangFire、Nlog、Jwt、LayUIAdmin、SqlSugar、MySQL、 ...
  • 基於surging 如何利用peerjs進行語音視頻通話
    本文主要介紹攝像頭（相機）如何採集數據，用於類似攝像頭本地顯示軟體，以及流媒體數據傳輸場景如傳屏、視訊會議等。 攝像頭採集有多種方案，如AForge.NET、WPFMediaKit、OpenCvSharp、EmguCv、DirectShow.NET、MediaCaptre（UWP），網上一些文章以及 ...
  • [WPF]數據綁定時為何會出現StringFormat失效
    前言 Seal-Report 是一款.NET 開源報表工具，擁有 1.4K Star。它提供了一個完整的框架，使用 C# 編寫，最新的版本採用的是 .NET 8.0 。 它能夠高效地從各種資料庫或 NoSQL 數據源生成日常報表，並支持執行複雜的報表任務。 其簡單易用的安裝過程和直觀的設計界面，我們 ...
  • NET Core 多身份校驗與策略模式
    背景需求： 系統需要對接到XXX官方的API，但因此官方對接以及管理都十分嚴格。而本人部門的系統中包含諸多子系統，系統間為了穩定，程式間多數固定Token+特殊驗證進行調用，且後期還要提供給其他兄弟部門系統共同調用。 原則上：每套系統都必須單獨接入到官方，但官方的接入複雜，還要官方指定機構認證的證書 ...
  • C#自定義控制項—轉換開關
    本文介紹下電腦設備關機的情況下如何通過網路喚醒設備，之前電源S狀態 電腦Power電源狀態- 唐宋元明清2188 - 博客園 (cnblogs.com) 有介紹過遠程喚醒設備，後面這倆天瞭解多了點所以單獨加個隨筆 設備關機的情況下，使用網路喚醒的前提條件： 1. 被喚醒設備需要支持這WakeOnL ...
  • .NET 8.0 前後分離快速開發框架
    前言 大家好，推薦一個.NET 8.0 為核心，結合前端 Vue 框架，實現了前後端完全分離的設計理念。它不僅提供了強大的基礎功能支持，如許可權管理、代碼生成器等，還通過採用主流技術和最佳實踐，顯著降低了開發難度，加快了項目交付速度。 如果你需要一個高效的開發解決方案，本框架能幫助大家輕鬆應對挑戰，實 ...
所有分類