一、數(shù)據(jù)庫管理
創(chuàng)建庫時指定字符集(utf-8, gb2312)
utf-8
CREATE DATABASE 數(shù)據(jù)庫名稱 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
gbk
CREATE DATABASE 數(shù)據(jù)庫名稱 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Example
mysql> create database teacher default charset utf8mb4 collate utf8mb4_general_ci;
使用數(shù)據(jù)庫
USE 數(shù)據(jù)庫名字;
Example
use teacher;
查看當前庫
select database();
/*或*/
status;
刪除數(shù)據(jù)庫
drop database 數(shù)據(jù)庫名稱;
Example
drop database teacher;
二、 表的管理
- MySQL 表的基本概念
MySQL 的表存放了實際的數(shù)據(jù)。
表大體可以分為表頭和表體， 一張表只有一個表頭，也就是一行數(shù)據(jù)，
這行數(shù)據(jù)的每一列的值成為字段。
表體可以有多行數(shù)據(jù)組成，每一行代表了一個要表現(xiàn)的實際對象。
每個字段代表了一條數(shù)據(jù)都有那些屬性，比如存一個學生的信息，需要有姓名、年齡、學號等。
對于這些屬性，你會發(fā)現(xiàn)它們是不同類型的數(shù)據(jù)，比如 姓名 是普通的文本，就是字符串，而年齡就是數(shù)字，也就是整型。
為了保證這些數(shù)據(jù)存放到數(shù)據(jù)庫時，不會出現(xiàn)類型的錯誤而導致以后影響使用，在創(chuàng)建表時，必須對每個字段指定要存放數(shù)據(jù)的數(shù)據(jù)類型。
image.png
2. MySQL 中的數(shù)據(jù)類型
bit[(M)]
二進制位（101001），m表示二進制位的長度（1-64），默認m＝1
tinyint[(m)] [unsigned] [zerofill]
小整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-128 ～ 127.
無符號：
0 ～ 255
特別的： MySQL中無布爾值，使用tinyint(1)構(gòu)造。
int[(m)][unsigned][zerofill]
整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-2147483648 ～ 2147483647
無符號：
0 ～ 4294967295
特別的：整數(shù)類型中的m僅用于顯示，對存儲范圍無限制。例如： int(5),當插入數(shù)據(jù)2時，select 時數(shù)據(jù)顯示為： 00002
bigint[(m)][unsigned][zerofill]
大整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-9223372036854775808 ～ 9223372036854775807
無符號：
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
準確的小數(shù)值，m是數(shù)字總個數(shù)（負號不算），d是小數(shù)點后個數(shù)。 m最大值為65，d最大值為保留3個。
特別的：對于精確數(shù)值計算時需要用此類型
decaimal能夠存儲精確值的原因在于其內(nèi)部按照字符串存儲。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
單精度浮點數(shù)（非準確小數(shù)值），m是數(shù)字總個數(shù)，d是小數(shù)點后個數(shù)。
無符號：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符號：
0
1.175494351E-38 to 3.402823466E+38
**** 數(shù)值越大，越不準確 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
雙精度浮點數(shù)（非準確小數(shù)值），m是數(shù)字總個數(shù)，d是小數(shù)點后個數(shù)。
無符號：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符號：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 數(shù)值越大，越不準確 ****
char (m)
char數(shù)據(jù)類型用于表示固定長度的字符串，可以包含最多達255個字符。其中m代表字符串的長度。
PS: 即使數(shù)據(jù)小于m長度，也會占用m長度
varchar(m)
varchars數(shù)據(jù)類型用于變長的字符串，可以包含最多達255個字符。其中m代表該數(shù)據(jù)類型所允許保存的字符串的最大長度，只要長度小于該最大值的字符串都可以被保存在該數(shù)據(jù)類型中。
注：雖然varchar使用起來較為靈活，但是從整個系統(tǒng)的性能角度來說，char數(shù)據(jù)類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，用戶在設(shè)計數(shù)據(jù)庫時應當綜合考慮各方面的因素，以求達到最佳的平衡
text
text數(shù)據(jù)類型用于保存變長的大字符串，可以組多到65535 (2**16 ? 1)個字符。
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.
/* 定義數(shù)據(jù)庫列時，可以使用Enum（Enumeration，枚舉）和SET（集合）類型：變通的實現(xiàn)CHECK約束
兩者的區(qū)別是：
使用Enum，只能選一個值；
使用SET，可以選多個值；
Enum和SET中的值都必須是字符串類型。
注意：
在內(nèi)部存儲Enum值時，MYSQL給Enum中的每個值一個順序號碼：第一個值的順序號碼是1，第二個值的順序號碼是2，以此類推。當排序或比較Enum的時候，使用這些順序號碼進行。
*/
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');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）
3. MySQL 表的操作 DDL
DDL（Data Definition Language 數(shù)據(jù)定義語言）
用在定義或改變表（TABLE）的結(jié)構(gòu)，數(shù)據(jù)類型，表之間的鏈接和約束等初始化工作上，他們大多在建立表時使用。
DDL不需要commit（DDL都是被隱式提交，不能對DDL語句使用ROLLBACK命令）。
CREATE 創(chuàng)建表,在數(shù)據(jù)庫創(chuàng)建對象
ALTER 修改表,修改數(shù)據(jù)庫結(jié)構(gòu)
DROP 刪除表,從數(shù)據(jù)庫中刪除對象
TRUNCATE 刪除表中所有行（無法回退）
COMMENT 注釋（為數(shù)據(jù)字典添加備注）
RENAME 重命名表
a. 創(chuàng)建表
create table 表名(
列名 類型 是否可以為空,
列名 類型 是否可以為空
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
創(chuàng)建表時可以單獨指定其使用的存儲引擎和默認字符編碼。
ENGINE=InnoDB DEFAULT CHARSET=utf8
是否可空，null表示空，非字符串
not null - 不可空
null - 可空(這是非主鍵的默認值)
默認值，創(chuàng)建列時可以指定默認值，當插入數(shù)據(jù)時如果未主動設(shè)置，則自動添加默認值
create table tb1(
id int not null ,
age int not null defalut 2
)
主鍵，一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。
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è)置此列的值，默認將自增（表中只能有一個自增列）
create table tb1(
id int auto_increment primary key,
age int not null
)
Example
create table student(
id int auto_increment primary key,
name varchar(12) not null,
age int not null,
phone char(11)
)
創(chuàng)建表時，定義外鍵,一對多關(guān)系
create table student2(
id int auto_increment primary key,
class_id int,
foreign key(class_id) REFERENCES class(id)
);
創(chuàng)建表時，定義多個外鍵，多對多關(guān)系
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. 查看當前數(shù)據(jù)庫中所有表
mysql> show tables;
+--------------------+
| Tables_in_shark_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
c. 查看表結(jié)構(gòu)
/查看當前庫中指定表的表結(jié)構(gòu)/
mysql> desc 表名;
/查看指定庫中指定表的表結(jié)構(gòu)/
mysql> desc 庫名.表名;
d. 清空表的內(nèi)容，表本身還在。
delete from 表名 /*自增列的值繼續(xù)遞增，可以加 where 子句*/
truncate table 表名 /*自增列的值重新從 1 開始*/
/刪除表中的所有數(shù)據(jù)，自增列的值繼續(xù)遞增/
delete from tb1;
/刪除表中的某些數(shù)據(jù)，被刪除的數(shù)據(jù)的自增列的值將不會再次出現(xiàn)，自增列的值繼續(xù)遞增/
delete from tb1 where id < 20;
e. 刪除表，表將消失
drop table 表名 /*整個表將不復存在*/
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;
添加一個列的唯一屬性
alter table student2 modify column email varchar(32) unique;
修改自增列
修改自增屬性的列，必須具備主鍵的屬性。
alter table 表名 modify column id int AUTO_INCREMENT;
外鍵的添加和刪除
向從表對一個字段增加外鍵屬性時，從表中的這個字段必須已經(jīng)存在，且不能有數(shù)據(jù)。
添加外鍵：
alter table 從表 add constraint 外鍵名稱（形如：FK_從表_主表） foreign key 從表(從表的列名) references 主表(主鍵列名);
刪除外鍵：
alter table 表名 drop foreign key 外鍵名稱
注意：
從表的的外鍵列的屬性必須主表的主鍵列名屬性一致。
默認值的修改和刪除
修改默認值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
刪除默認值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
修改字符集(編碼)
修改數(shù)據(jù)庫字符集：
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
把表默認的字符集和所有字符列（CHAR,VARCHAR,TEXT）改為新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
只是修改表的默認字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：
ALTER TABLE student DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
查看數(shù)據(jù)庫字符集：
SHOW CREATE DATABASE db_name;
查看表字符集：
SHOW CREATE TABLE tbl_name;
作者：xiguatian
鏈接：http://www.itdecent.cn/p/67442db0e1e8
來源：簡書
簡書著作權(quán)歸作者所有，任何形式的轉(zhuǎn)載都請聯(lián)系作者獲得授權(quán)并注明出處。
