當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

MySQL（2）数据库管理

發(fā)布時(shí)間：2024/9/3 数据库 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL（2）数据库管理小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

文章目錄

一、MySQL數(shù)據(jù)庫(kù)管理
- 1.1庫(kù)和表
二、查看數(shù)據(jù)庫(kù)結(jié)構(gòu)
- 2.1 查看數(shù)據(jù)庫(kù)結(jié)構(gòu)
- - 1．查看當(dāng)前服務(wù)器中的數(shù)據(jù)庫(kù)
  - 2．查看數(shù)據(jù)庫(kù)中包含的表
  - 3．查看表的結(jié)構(gòu)（字段）
  - 4. 查看mysql版本
- 2.2 SQL語(yǔ)句
- 2.3 創(chuàng)建及刪除數(shù)據(jù)庫(kù)和表
- - 1）創(chuàng)建新的數(shù)據(jù)庫(kù)
  - 2）創(chuàng)建新的表
  - 3）刪除指定的數(shù)據(jù)表
  - 4）刪除指定的數(shù)據(jù)庫(kù)
- 2.4 管理表中的數(shù)據(jù)記錄
- - 1）向數(shù)據(jù)表中插入新的數(shù)據(jù)記錄
  - 2）查詢數(shù)據(jù)記錄
  - 3）修改、更新數(shù)據(jù)表中的數(shù)據(jù)記錄
  - 4）在數(shù)據(jù)表中刪除指定的數(shù)據(jù)記錄
- 2.5 修改表名和表結(jié)構(gòu)
- - 1）修改表名
  - 2）擴(kuò)展表結(jié)構(gòu)（增加字段）
  - 3）修改字段（列）名，添加唯一鍵
  - 4）刪除字段
- 2.6 使用 if 判斷創(chuàng)建表并測(cè)試自增和填充
三、數(shù)據(jù)表高級(jí)操作
- 3.1 克隆表——將數(shù)據(jù)表的數(shù)據(jù)記錄生成到新的表中
- - 3.1.1 方法一：先創(chuàng)建再導(dǎo)入
  - 3.1.2 方法二：創(chuàng)建的時(shí)候同時(shí)導(dǎo)入
- 3.2 清空表——?jiǎng)h除表內(nèi)的所有數(shù)據(jù)
- - 3.2.1 方法一：deldte刪除
  - 3.2.2 方法二：truncate刪除
  - 3.2.2 方法三：創(chuàng)建臨時(shí)表
- 3.3 創(chuàng)建外鍵約束

一、MySQL數(shù)據(jù)庫(kù)管理

1.1庫(kù)和表

數(shù)據(jù)庫(kù)–>數(shù)據(jù)表–>行（記錄）：用來(lái)描述一個(gè)對(duì)象的信息
_______________ 列（字段）：用來(lái)描述對(duì)象的一個(gè)屬性
常用的數(shù)據(jù)類型

int ：整型
float ：單精度浮點(diǎn) 4字節(jié)32位
double ：雙精度浮點(diǎn) 8字節(jié)64位
char ：固定長(zhǎng)度的字符類型
varchar ：可變長(zhǎng)度的字符類型
text ：文本
image ：圖片
decimal(5,2) ：5個(gè)有效長(zhǎng)度數(shù)字，小數(shù)點(diǎn)后面有2位

二、查看數(shù)據(jù)庫(kù)結(jié)構(gòu)

2.1 查看數(shù)據(jù)庫(kù)結(jié)構(gòu)

1．查看當(dāng)前服務(wù)器中的數(shù)據(jù)庫(kù)

SHOW DATABASES; #大小寫(xiě)不區(qū)分，分號(hào)“;”表示結(jié)束

2．查看數(shù)據(jù)庫(kù)中包含的表

USE 數(shù)據(jù)庫(kù)名;
SHOW TABLES;
查看mysql數(shù)據(jù)庫(kù)中的表

3．查看表的結(jié)構(gòu)（字段）

USE 數(shù)據(jù)庫(kù)名;
DESCRIBE [數(shù)據(jù)庫(kù)名.]表名;
可縮寫(xiě)成：DESC 表名;

相對(duì)路徑查看：

絕對(duì)路徑查看

4. 查看mysql版本

select version(); #在mysql內(nèi)查看mysql版本

mysql -V #在linux系統(tǒng)中查看mysql版本

2.2 SQL語(yǔ)句

SQL語(yǔ)句用于維護(hù)管理數(shù)據(jù)庫(kù)，包括數(shù)據(jù)查詢、數(shù)據(jù)更新、訪問(wèn)控制、對(duì)象管理等功能。

SQL語(yǔ)句分類：
語(yǔ)句代表的意思
DDL 數(shù)據(jù)定義語(yǔ)言，用于創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象，如庫(kù)、表、索引等
DML 數(shù)據(jù)操縱語(yǔ)言，用于對(duì)表中的數(shù)據(jù)進(jìn)行管理
DQL 數(shù)據(jù)查詢語(yǔ)言，用于從數(shù)據(jù)表中查找符合條件的數(shù)據(jù)記錄
DCL 數(shù)據(jù)控制語(yǔ)言，用于設(shè)置或者更改數(shù)據(jù)庫(kù)用戶或角色權(quán)限

2.3 創(chuàng)建及刪除數(shù)據(jù)庫(kù)和表

1）創(chuàng)建新的數(shù)據(jù)庫(kù)

CREATE DATABASE 數(shù)據(jù)庫(kù)名;

2）創(chuàng)建新的表

CREATE TABLE 表名 (字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,…][,PRIMARY KEY (主鍵名)]);
#主鍵一般選擇能代表唯一性的字段不允許取空值（NULL），一個(gè)表只能有一個(gè)主鍵。

例：

CREATE DATABASE ali;
USE ali;
create table market(id int(4) NOT NULL, name char(10), age int(3), PRIMARY KEY(id));
DESC market;

3）刪除指定的數(shù)據(jù)表

DROP TABLE [數(shù)據(jù)庫(kù)名.]表名; #如不用USE進(jìn)入庫(kù)中，則需加上數(shù)據(jù)庫(kù)名

刪除方法一：進(jìn)入目標(biāo)庫(kù)

刪除方法二：在要?jiǎng)h除的表前面加上庫(kù)名

4）刪除指定的數(shù)據(jù)庫(kù)

DROP DATABASE 數(shù)據(jù)庫(kù)名;

2.4 管理表中的數(shù)據(jù)記錄

1）向數(shù)據(jù)表中插入新的數(shù)據(jù)記錄

INSERT INTO 表名(字段1,字段2[,…]) VALUES(字段1的值,字段2的值,…);
例：
insert into market(id,name,age,passwd) values(2,‘lisi’,20,PASSWORD(‘112233’));
#PASSWORD(‘112233’)：查詢數(shù)據(jù)記錄時(shí)，密碼字串以加密形式顯示；若不使用PASSWORD()，查詢時(shí)以明文顯示。

INSERT INTO KY08 VALUES(2,‘lisi’,90.5,654321);

2）查詢數(shù)據(jù)記錄

SELECT 字段名1,字段名2[,…] FROM 表名 [WHERE 條件表達(dá)式];

例：查詢?nèi)繑?shù)據(jù)

SELECT * FROM market;

例：查詢指定數(shù)據(jù)

select id,name from market where id=4;

例：以列表方式豎向顯示

select from market\G;

例：只顯示頭2行
select * from market limit 2;

例：顯示第2行后的前2行
select * from market limit 2,2;

3）修改、更新數(shù)據(jù)表中的數(shù)據(jù)記錄

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 條件表達(dá)式];
例：修改age

update market set age=19 where id=4;
update market set age=20,passwd=‘111111’ where id=4; #同時(shí)修改age和passwd

4）在數(shù)據(jù)表中刪除指定的數(shù)據(jù)記錄

DELETE FROM 表名 [WHERE 條件表達(dá)式];

例：刪除當(dāng)前表中id為4的數(shù)據(jù)
delete from market where id=4;

2.5 修改表名和表結(jié)構(gòu)

1）修改表名

ALTER TABLE 舊表名 RENAME 新表名;

例：
alter table market rename sales;

2）擴(kuò)展表結(jié)構(gòu)（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default ‘地址未填寫(xiě)！’;
#default ‘地址未填寫(xiě)！’：表示此字段設(shè)置默認(rèn)值地址未填寫(xiě)！；可與 NOT NULL 配合使用

3）修改字段（列）名，添加唯一鍵

ALTER TABLE 表名 CHANGE 舊列名新列名數(shù)據(jù)類型 [unique key];

例：修改字段并添加唯一鍵，然后創(chuàng)建數(shù)據(jù)測(cè)試。

alter table market change name user_name char(30) unique key;
#CHANGE可修改字段名、數(shù)據(jù)類型、約束等所有項(xiàng)。

4）刪除字段

ALTER TABLE 表名 drop 字段名;
例：刪除表中 passwd字段
ALTER TABLE market drop passwd;

2.6 使用 if 判斷創(chuàng)建表并測(cè)試自增和填充

use ali; #先進(jìn)入指定的庫(kù)

create table if not exists yy (
id int(4) zerofill primary key auto_increment,
name char(20) not null,
sid int(18) not null unique key,
age int(3) not null,
address char(100));

分析：
#if not exists：表示檢測(cè)要?jiǎng)?chuàng)建的表是否已存在，如果不存在就繼續(xù)創(chuàng)建
#int(4) zerofill：表示若數(shù)值不滿4位數(shù)，則前面用“0”填充，例0001
#auto_increment：表示此字段為自增長(zhǎng)字段，即每條記錄自動(dòng)遞增1，默認(rèn)從1開(kāi)始遞增；自增長(zhǎng)字段數(shù)據(jù)不可以重復(fù)；自增長(zhǎng)字段必須是主鍵；如添加的記錄數(shù)據(jù)沒(méi)有指定此字段的值且添加失敗也會(huì)自動(dòng)遞增一次
#unique key：表示此字段唯一鍵約束，此字段數(shù)據(jù)不可以重復(fù)；一張表中只能有一個(gè)主鍵, 但是一張表中可以有多個(gè)唯一鍵
#not null：表示此字段不允許為NULL

測(cè)試：創(chuàng)建數(shù)據(jù)測(cè)試自動(dòng)補(bǔ)齊和自增長(zhǎng)。

測(cè)試：數(shù)據(jù)創(chuàng)建失敗，id的補(bǔ)齊和自增情況

三、數(shù)據(jù)表高級(jí)操作

3.1 克隆表——將數(shù)據(jù)表的數(shù)據(jù)記錄生成到新的表中

3.1.1 方法一：先創(chuàng)建再導(dǎo)入

create table test01 like info; #通過(guò) LIKE 方法，復(fù)制 info 表結(jié)構(gòu)生成 test01 表
insert into test01 select * from info; #導(dǎo)入數(shù)據(jù)

3.1.2 方法二：創(chuàng)建的時(shí)候同時(shí)導(dǎo)入

create table test02 (select * from info);
show create table test02\G; #獲取數(shù)據(jù)表的表結(jié)構(gòu)、索引等信息

3.2 清空表——?jiǎng)h除表內(nèi)的所有數(shù)據(jù)

3.2.1 方法一：deldte刪除

DELETE清空表后，返回的結(jié)果內(nèi)有刪除的記錄條目；DELETE工作時(shí)是一行一行的刪除記錄數(shù)據(jù)的；如果表中有自增長(zhǎng)字段，使用DELETE FROM 刪除所有記錄后，再次新添加的記錄會(huì)從原來(lái)最大的記錄 ID 后面繼續(xù)自增寫(xiě)入記錄。

delete from info; #delete from 表名；

3.2.2 方法二：truncate刪除

TRUNCATE 清空表后，沒(méi)有返回被刪除的條目；TRUNCATE 工作時(shí)是將表結(jié)構(gòu)按原樣重新建立，因此在速度上 TRUNCATE 會(huì)比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表內(nèi)數(shù)據(jù)后，ID 會(huì)從 1 開(kāi)始重新記錄

truncate table info;

3.2.2 方法三：創(chuàng)建臨時(shí)表

臨時(shí)表創(chuàng)建成功之后，使用SHOW TABLES命令是看不到創(chuàng)建的臨時(shí)表的，臨時(shí)表會(huì)在連接退出后被銷毀。如果在退出連接之前，也可以可執(zhí)行增刪改查等操作，比如使用 DROP TABLE 語(yǔ)句手動(dòng)直接刪除臨時(shí)表。

##添加臨時(shí)表test03
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));

show tables; ## 查看當(dāng)前庫(kù)中所有表
insert into test03 values(1,‘zhangsan’,123456789,‘watch a film’); ##在臨時(shí)表中添加數(shù)據(jù)
select * from test03; ##查看當(dāng)前表中所有數(shù)據(jù)
quit ##退出數(shù)據(jù)庫(kù)

mysql -u root -p ##重新登錄后進(jìn)行查看
select * from test03; ##查看之前創(chuàng)建的臨時(shí)表中所有數(shù)據(jù)，發(fā)現(xiàn)已經(jīng)被自動(dòng)銷毀

3.3 創(chuàng)建外鍵約束

create table pro (pid int, pname char(20));
create table student (id int, name char(10), age int, proid int);
alter table pro add constraint PK_pid primary key (pid);
alter table student add constraint FK_proid foreign key (proid) references pro (pid);
pro表結(jié)構(gòu)如下：

student表結(jié)構(gòu)如下：

注意：與外鍵關(guān)聯(lián)的子表的字段必須設(shè)置為主鍵。要求主表外鍵字段和子表的字段具備相同的數(shù)據(jù)類型、字符長(zhǎng)度和約束。

#為子表添加一個(gè)主鍵約束。主鍵名建議以“PK_”開(kāi)頭。

alter table pro add constraint PK_pid primary key (pid);

#為 branch 表添加外鍵，并將 student表的 proid 字段和 pro 表的 pid 字段建立外鍵關(guān)聯(lián)。外鍵名建議以“FK_”開(kāi)頭。

alter table student add constraint FK_proid foreign key (proid) references pro (pid);

測(cè)試刪除：從表的數(shù)據(jù)未刪除，主表的數(shù)據(jù)無(wú)法刪除

查看和刪除外鍵約束：

desc test03;
show create table student\G #查看表約束

alter table student drop foreign key FK_proid; 解除外鍵約束
alter table student drop key FK_proid; 刪除外鍵
desc student;

總結(jié)

以上是生活随笔為你收集整理的MySQL（2）数据库管理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： MySQL（1）数据库介绍，配置MySQ
下一篇： MySQL(3)数据库用户管理