MYSQL
1、mysql字符集
（1）簡(jiǎn)介：
MySQL字符集包括字符集（CHARACTER）和校對(duì)規(guī)則（COLLATION）兩個(gè)概念：
- 字符集（CHARACTER）是一套編碼
- 校對(duì)規(guī)則（COLLATION）是在字符集內(nèi)用于比較字符的一套規(guī)則。
mysql字符集：
latin1支持西歐字符、希臘字符等
gbk支持中文簡(jiǎn)體字符
big5支持中文繁體字符
utf8幾乎支持世界所有國(guó)家的字符。
utf8mb4是真正意義上的utf-8（2）命令：
<1> 查看字符集
mysql8.0 [(none)]>show variables like 'character%';
-- 查看所有像character..的變量
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | gbk |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)<2> 修改默認(rèn)字符集
mysql8.0 [(none)]>set character_set_server = utf8mb4;
mysql8.0 [(none)]>set character_set_database = utf8mb4;
mysql8.0 [(none)]>show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+附：utf8和utf8mb4的區(qū)別
MySQL在5.5.3之后增加了這個(gè)utf8mb4的編碼，mb4就是most bytes 4的意思，專(zhuān)門(mén)用來(lái)兼容四字節(jié)的unicode。好在utf8mb4是utf8的超集，除了將編碼改為utf8mb4外不需要做其他轉(zhuǎn)換。
utf8 編碼最大字符長(zhǎng)度為 3 字節(jié)，如果遇到 4 字節(jié)的寬字符就會(huì)插入異常了。三個(gè)字節(jié)的 UTF-8 最大能編碼的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文種平面(BMP)。
2、數(shù)據(jù)庫(kù)對(duì)象
命名規(guī)則：
- 必須以字母開(kāi)頭
- 可包括數(shù)字和特殊字符（_和$）
- 不要使用MySQL的保留字
- 同一Schema下的對(duì)象不能同名
3、表的基本操作
數(shù)據(jù)表的每行稱(chēng)為一條記錄（record）；每一列稱(chēng)為一個(gè)字段（field）
（1）數(shù)據(jù)類(lèi)型
MYSQL中，有三種主要的類(lèi)型：文本、數(shù)值和日期/時(shí)間類(lèi)型
常用數(shù)據(jù)類(lèi)型：
（2）創(chuàng)建表
語(yǔ)法：
CREATE TABLE 表名(
列名 列數(shù)據(jù)類(lèi)型，
列名 列數(shù)據(jù)類(lèi)型
);例：
mysql8.0 [student]>create table t1(id int,name char(20)); -- 單行創(chuàng)建
mysql8.0 [student]>create table t1(
-> id int,name char(20)
-> ,address char(50));
/* 多行創(chuàng)建 */
mysql8.0 [student]>show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| t1 |
| t2 |
+-------------------+
（2）查看表
<1> 查看數(shù)據(jù)庫(kù)中的所有表
語(yǔ)法：SHOW TABLES[FROM 數(shù)據(jù)庫(kù)名][LIKE wild];
例：
mysql8.0 [student]>show tables from mysql like '%server%'; -- 查看mysql數(shù)據(jù)庫(kù)中含server字段的tables
+----------------------------+
| Tables_in_mysql (%server%) |
+----------------------------+
| server_cost |
| servers |
+----------------------------+
<2> 顯示當(dāng)前數(shù)據(jù)庫(kù)中已有的數(shù)據(jù)表信息
[1]?語(yǔ)法：{DESCRIBE|DESC}? 表名 [列名];
mysql8.0 [student]>describe student.t2;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| address | char(50) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+[2] 語(yǔ)法：show columns from 表名稱(chēng);
mysql8.0 [student]>show columns from student.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
（3）刪除表
語(yǔ)法：DROP TABLE [IF EXISTS] 表名;
mysql8.0 [student]>drop table t1; -- 當(dāng)前處在數(shù)據(jù)庫(kù)中，不指定數(shù)據(jù)庫(kù)默認(rèn)刪除本數(shù)據(jù)庫(kù)內(nèi)的表
mysql8.0 [student]>drop table student.t2;
mysql8.0 [student]>show tables;
（4）修改表結(jié)構(gòu) --- ALTER
mysql8.0 [student]>desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
#修改列類(lèi)型：ALTER TABLE 表名 MODIFY 列名 列類(lèi)型;
mysql8.0 [student]>alter table t1 modify name varchar(30);
mysql8.0 [student]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#增加列：ALTER TABLE 表名 ADD 列名 列類(lèi)型;
mysql8.0 [student]>alter table t1 add birthdar date;
mysql8.0 [student]>desc t1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| birthdar | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
#刪除列：ALTER TABLE 表名 DROP 列名;
mysql8.0 [student]>alter table t1 drop birthdar;
mysql8.0 [student]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#修改列名：ALTER TABLE 表名 CHANGE 舊列名 新列名 列類(lèi)型;
mysql8.0 [student]>alter table t1 change id score int;
mysql8.0 [student]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#修改表名
方式1：ALTER TABLE 表名 RENAME 新表名;
方式2：RENAME TABLE 表名 TO 新表名;
mysql8.0 [student]>alter table t1 rename sss;
mysql8.0 [student]>rename table t2 to aaa;
mysql8.0 [student]>show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| aaa |
| sss |
+-------------------+（5）復(fù)制表結(jié)構(gòu)
<1> 語(yǔ)法：create table 新表名 like 源表
-- 將源表的表結(jié)構(gòu)復(fù)制到新表
mysql8.0 [student]>create table qqq like aaa;
mysql8.0 [student]>desc qqq;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| address | char(50) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+<2>?語(yǔ)法：create table 新表名 select * from 源表
-- 實(shí)現(xiàn)表結(jié)構(gòu)的復(fù)制，甚至可以將源表的表記錄拷貝到新表中
mysql8.0 [student]>select * from sss;
+-------+---------+
| score | name |
+-------+---------+
| 34 | mmm |
| 35 | wangwei |
+-------+---------+
mysql8.0 [student]>create table ttt select * from sss;
mysql8.0 [student]>desc ttt;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql8.0 [student]>select * from ttt;
+-------+---------+
| score | name |
+-------+---------+
| 34 | mmm |
| 35 | wangwei |
+-------+---------+
<3>?語(yǔ)法：insert into 表名 select * from 原表;
僅復(fù)制數(shù)據(jù)
mysql8.0 [student]>select * from qqq;
mysql8.0 [student]>insert into qqq select * from aaa;
mysql8.0 [student]>select * from qqq;
+------+-------+----------+
| id | name | address |
+------+-------+----------+
| 11 | smith | nework |
| 22 | wang | shanghai |
+------+-------+----------+例1：將student數(shù)據(jù)庫(kù)中的aaa表的表結(jié)構(gòu)復(fù)制到stuinfo數(shù)據(jù)庫(kù)中的mm表中文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-569097.html
mysql8.0 [student]>create database stuinfo;
mysql8.0 [stuinfo]>create table mm like student.aaa;
mysql8.0 [stuinfo]>desc mm;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| address | char(50) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+例2：在stuinfo中創(chuàng)建msd表，將student的sss表結(jié)構(gòu)和數(shù)據(jù)復(fù)制過(guò)去文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-569097.html
mysql8.0 [(none)]>create table stuinfo.msd select * from student.sss;
mysql8.0 [(none)]>use stuinfo;
mysql8.0 [stuinfo]>desc msd;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql8.0 [stuinfo]>select * from msd;
+-------+---------+
| score | name |
+-------+---------+
| 34 | mmm |
| 35 | wangwei |
+-------+---------+
到了這里，關(guān)于數(shù)據(jù)庫(kù) --- mysql（03）-- mysql字符集、表操作（01）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
