MySQL基礎(chǔ)之?dāng)?shù)據(jù)庫(kù)和表管理
數(shù)據(jù)庫(kù)中的默認(rèn)庫(kù)
INFORMATION_SCHEMA提供對(duì)數(shù)據(jù)庫(kù)元數(shù)據(jù)的訪問 褥蚯，有關(guān)MySQL服務(wù)器的信息译秦，例如數(shù)據(jù)庫(kù)或表的名稱，列的數(shù)據(jù)類型或訪問權(quán)限茂洒。有時(shí)用于此信息的其他術(shù)語是 數(shù)據(jù)字典和 系統(tǒng)目錄威根。
官方網(wǎng)頁(yè)
一、數(shù)據(jù)庫(kù)管理
常用操作語句
-- 查看當(dāng)前數(shù)據(jù)庫(kù)
SELECT DATABASE();
-- 顯示當(dāng)前時(shí)間嘴脾、用戶名男摧、數(shù)據(jù)庫(kù)版本
SELECT now(), user(), version();
-- 創(chuàng)建庫(kù)
CREATE DATABASE[ IF NOT EXISTS] 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)選項(xiàng)
數(shù)據(jù)庫(kù)選項(xiàng)：
DEFAULT CHARACTER SET charset_name
COLLATE collation_name
-- 查看已有庫(kù)
SHOW DATABASES[ LIKE 'PATTERN']
-- 查看當(dāng)前庫(kù)信息
SHOW CREATE DATABASE 數(shù)據(jù)庫(kù)名
-- 修改庫(kù)的選項(xiàng)信息
ALTER DATABASE 庫(kù)名 選項(xiàng)信息
-- 刪除庫(kù),同時(shí)刪除該數(shù)據(jù)庫(kù)相關(guān)的目錄及其目錄內(nèi)容
DROP DATABASE[ IF EXISTS] 數(shù)據(jù)庫(kù)名
創(chuàng)建庫(kù)時(shí)指定字符集(utf-8, gb2312)
charset_name就是字符集，比如：
gbkutf8utf8mb4
collation_name排序規(guī)則译打，比如:
gbk_chinese_ciutf8_general_ciutf8mb4_general_ci
# utf-8
CREATE DATABASE 數(shù)據(jù)庫(kù)名稱 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# gbk
CREATE DATABASE 數(shù)據(jù)庫(kù)名稱 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
例如：
mysql> create database teacher default charset utf8mb4 collate utf8mb4_general_ci;
使用數(shù)據(jù)庫(kù)
USE 數(shù)據(jù)庫(kù)名字;
查看當(dāng)前庫(kù)
select database();
/*或*/
status;
查看庫(kù)的默認(rèn)字符集
mysql> show create database teacher\G
刪除數(shù)據(jù)庫(kù)
drop database 數(shù)據(jù)庫(kù)名稱;
二耗拓、 表的管理
1、MySQL 中的數(shù)據(jù)類型
int(M)
[UNSIGNED][ZEROFILL]
UNSIGNED 表示符號(hào)
ZEROFILL 表示不夠的位使用 0 填充
number int(2) UNSIGNED ZEROFILL
number 的值假如你存1奏司，顯示的時(shí)候不夠 2 位乔询，就會(huì)使用 0 填充，變成 02
整數(shù)韵洋，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-2147483648 ～ 2147483647
無符號(hào)：
0 ～ 4294967295
FLOAT[(M.D) [UNSIGNED] [ZEROFILL]
單精度浮點(diǎn)數(shù)（非準(zhǔn)確小數(shù)值）竿刁，m是數(shù)字總個(gè)數(shù)黄锤，d是小數(shù)點(diǎn)后個(gè)數(shù)。
無符號(hào)：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符號(hào)：
0
1.175494351E-38 to 3.402823466E+38
** ** 數(shù)值越大食拜，越不準(zhǔn)確 ** **
char (m)
char數(shù)據(jù)類型用于表示固定長(zhǎng)度的字符串鸵熟，可以包含最多達(dá)255個(gè)字符。其中m代表字符串的長(zhǎng)度负甸。
PS: 即使數(shù)據(jù)小于m長(zhǎng)度流强，也會(huì)占用m長(zhǎng)度
varchar(m)
varchars數(shù)據(jù)類型用于變長(zhǎng)的字符串，
可以包含最多達(dá)255個(gè)字符呻待。
其中m代表該數(shù)據(jù)類型所允許保存的字符串的最大長(zhǎng)度
只要長(zhǎng)度小于該最大值的字符串都可以被保存在該數(shù)據(jù)類型中打月。
注：
雖然varchar使用起來較為靈活，但是從整個(gè)系統(tǒng)的性能角度來說蚕捉，char數(shù)據(jù)類型的處理速度更快奏篙，有時(shí)甚至可以超出varchar處理速度的50%。因此迫淹，用戶在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)應(yīng)當(dāng)綜合考慮各方面的因素报破，以求達(dá)到最佳的平衡
text
text數(shù)據(jù)類型用于保存變長(zhǎng)的大字符串，可以組多到65535 (2* *16 ? 1)個(gè)字符千绪。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2* *24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.
Enum
枚舉類型 An Enum column can have a maximum of 65,535 distinct >elements. (The practical limit is less than 3000.) 示例： CREATE TABLE shirts ( name VARCHAR(40), size Enum('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合類型 A SET column can have a maximum of 64 distinct members. 示例： CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
定義數(shù)據(jù)庫(kù)列時(shí)，可以使用Enum（Enumeration荸型，枚舉）和SET（集合）類型：變通的實(shí)現(xiàn)CHECK約束
兩者的區(qū)別是：
- 使用Enum盹靴，只能選一個(gè)值；
- 使用SET瑞妇，可以選多個(gè)值稿静；
- Enum和SET中的值都必須是字符串類型。
注意：
在內(nèi)部存儲(chǔ)Enum值時(shí)辕狰，MYSQL給Enum中的每個(gè)值一個(gè)順序號(hào)碼：第一個(gè)值的順序號(hào)碼是1改备，第二個(gè)值的順序號(hào)碼是2，以此類推蔓倍。當(dāng)排序或比較Enum的時(shí)候悬钳，使用這些順序號(hào)碼進(jìn)行。
關(guān)于日期和時(shí)間類型的字段
字段類型: DATE
表現(xiàn)形式: YYYY-MM-DD
取值范圍: 1000-01-01/9999-12-31
創(chuàng)建方法: 字段名 DATE
添加數(shù)據(jù):values("20191010")
查詢到的結(jié)果:2019-10-10
2偶翅、MySQL 表的操作
CREATE 創(chuàng)建表,在數(shù)據(jù)庫(kù)創(chuàng)建對(duì)象
ALTER 修改表,修改數(shù)據(jù)庫(kù)結(jié)構(gòu)(擴(kuò)展自修)
DROP 刪除表,從數(shù)據(jù)庫(kù)中刪除對(duì)象
TRUNCATE 刪除表中所有行（無法回退）
COMMENT 注釋（為數(shù)據(jù)字典添加備注）
RENAME 重命名表(擴(kuò)展自修)
關(guān)于存儲(chǔ)引擎
-- 存儲(chǔ)引擎
ENGINE = engine_name
表在管理數(shù)據(jù)時(shí)采用的不同的數(shù)據(jù)結(jié)構(gòu)默勾，結(jié)構(gòu)不同會(huì)導(dǎo)致處理方式、提供的特性操作等不同
常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時(shí)采用不同的方式
MyISAM表文件含義：.frm表定義聚谁，.MYD表數(shù)據(jù)母剥，.MYI表索引
InnoDB表文件含義：.frm表定義，表空間數(shù)據(jù)和日志文件
SHOW ENGINES -- 顯示存儲(chǔ)引擎的狀態(tài)信息
SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示存儲(chǔ)引擎的日志或狀態(tài)信息
-- 自增起始數(shù)
AUTO_INCREMENT = 行數(shù)
-- 數(shù)據(jù)文件目錄
DATA DIRECTORY = '目錄'
-- 索引文件目錄
INDEX DIRECTORY = '目錄'
-- 表注釋
COMMENT = 'string'
-- 分區(qū)選項(xiàng)
PARTITION BY ... (詳細(xì)見手冊(cè))
a. 創(chuàng)建表
create table 表名[ IF NOT EXISTS](
列名 類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'],
列名 類型
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
創(chuàng)建表時(shí)可以單獨(dú)指定其使用的存儲(chǔ)引擎和默認(rèn)字符編碼。
ENGINE=InnoDB DEFAULT CHARSET=utf8
默認(rèn)值环疼，創(chuàng)建列時(shí)可以指定默認(rèn)值习霹，當(dāng)插入數(shù)據(jù)時(shí)如果未主動(dòng)設(shè)置，則自動(dòng)添加默認(rèn)值
create table tb1(
id int not null ,
age int not null default 20
)
是否可空炫隶，null表示空序愚，并不是字符串類型
not null - 不可空，在添加數(shù)據(jù)時(shí)必須給值
null - 可空(這是非主鍵的默認(rèn)屬性)
主鍵等限，一種特殊的唯一索引，不允許有空值芬膝，如果主鍵使用單個(gè)列望门，則它的值必須唯一，如果是多列锰霜，則其組合必須唯一筹误。
create table tb1(
id int primary key,
age int null
)
或
create table tb1(
id int not null,
age int not null,
primary key(id,age)
)
自增，如果為某列設(shè)置自增列癣缅，插入數(shù)據(jù)時(shí)無需設(shè)置此列的值厨剪，默認(rèn)將自增（表中只能有一個(gè)自增列）
create table 表名(
id int auto_increment primary key,
age int not null
)
創(chuàng)建日期和時(shí)間類型和存值
mysql> create table tb_datas(
-> date1 date,
-> time2 time,
-> yyyy year,
-> yyyy_mm_ddThh datetime,
-> yyyymmddhh timestamp);
插入數(shù)據(jù)
insert into tb_datas(date1,time2,yyyy,yyyy_mm_ddThh,yyyymmddhh) values( 20191010,"20:00:00",2019,"2019-10-10 20:00:00","2019-10-11 20:00:00");
查詢到的數(shù)據(jù)
mysql> select * from tb_datas\G
*************************** 1. row ***************************
date1: 2019-10-10
time2: 20:00:00
yyyy: 2019
yyyy_mm_ddThh: 2019-10-10 20:00:00
yyyymmddhh: 2019-10-11 20:00:00
1 row in set (0.00 sec)
- 創(chuàng)建表時(shí)，定義外鍵,一對(duì)多關(guān)系
首先創(chuàng)建主表友存，主表就是祷膳，表中的一條數(shù)據(jù)會(huì)對(duì)應(yīng)到從表中的多條數(shù)據(jù)，比如班級(jí)表中的一條數(shù)據(jù)就代表了一個(gè)班級(jí)屡立，一個(gè)班級(jí)中可以有多個(gè)學(xué)生直晨，所以班級(jí)表就是主表，學(xué)生表就是從表膨俐。
那需要先創(chuàng)建一個(gè)主表： class
# 首先切換到一個(gè)庫(kù)中再創(chuàng)建表
mysql> use db1;
# 接著創(chuàng)建表
create table class(
id int auto_increment primary key,
name varchar(12)
)
接下來可以創(chuàng)建從表了勇皇，并在創(chuàng)建從表的時(shí)候設(shè)置外鍵
create table student2(
id int auto_increment primary key,
class_id int,
foreign key(class_id) REFERENCES class(id)
);
創(chuàng)建表時(shí)，定義多個(gè)外鍵焚刺，多對(duì)多關(guān)系(擴(kuò)展)
create table class2teacher(
id int auto_increment primary key,
class_id int, teacher_id int,
foreign key(class_id) REFERENCES class(id),
foreign key(teacher_id) references teacher(id)
);
b. 查看當(dāng)前數(shù)據(jù)庫(kù)中所有表
mysql> show tables;
+--------------------+
| Tables_in_shark_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
c. 查看表結(jié)構(gòu)
/*查看當(dāng)前庫(kù)中指定表的表結(jié)構(gòu)*/
mysql> desc 表名;
/*查看指定庫(kù)中指定表的表結(jié)構(gòu)*/
mysql> desc 庫(kù)名.表名;
d. 清空表的內(nèi)容敛摘，表本身還在。
delete from 表名 /*自增列的值繼續(xù)遞增乳愉，可以加 where 子句*/
truncate table 表名 /*自增列的值重新從 1 開始*/
/*刪除表中的所有數(shù)據(jù)兄淫，自增列的值繼續(xù)遞增*/
delete from tb1;
/*刪除表中的某些數(shù)據(jù)，被刪除的數(shù)據(jù)的自增列的值將不會(huì)再次出現(xiàn)蔓姚，自增列的值繼續(xù)遞增*/
delete from tb1 where id < 20;
e. 刪除表拖叙，表將消失
drop table 表名 /*整個(gè)表將不復(fù)存在*/
f. 修改表結(jié)構(gòu)（以下自修）
- 添加列
alter table 表名 add 列名 類型
示例：
alter table stadent add gender Enum("男", "女");
alter table stadent add hobby set("girl","car","yacht");
update student set hobby = "girl,car";
- 刪除列
alter table 表名 drop column 列名
- 修改列
修改類型
alter table 表名 modify column 列名 類型;
修改列名和類型
alter table 表名 change 原列名 新列名 類型;
- 添加主鍵
alter table 表名 add primary key(列名;
alter table students add id int not null auto_increment, add primary key (id);
- 刪除主鍵
alter table 表名 drop primary key;
- 刪除主鍵屬性，保留原值和列
alter table 表名 modify 列名 int, drop primary key;
- 添加一個(gè)列的唯一屬性
alter table student2 modify column email varchar(32) unique;
- 修改自增列
修改自增屬性的列赂乐，必須具備主鍵的屬性薯鳍。
alter table 表名 modify column id int AUTO_INCREMENT;
- 外鍵的添加和刪除
向從表對(duì)一個(gè)字段增加外鍵屬性時(shí)，從表中的這個(gè)字段必須已經(jīng)存在，且不能有數(shù)據(jù)挖滤。
注意：
從表的的外鍵列的屬性必須主表的主鍵列名屬性一致崩溪。
添加外鍵：
alter table 從表 add constraint 外鍵名稱（形如：FK_從表_主表） foreign key 從表(從表的列名) references 主表(主鍵列名);
刪除外鍵：
alter table 表名 drop foreign key 外鍵名稱
- 默認(rèn)值的修改和刪除
修改默認(rèn)值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
刪除默認(rèn)值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
