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 MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類