©2020-2024 ioDraw All rights reserved
MYSQL
<>1、mysql字符集
（1）简介：
MySQL字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念：
* 字符集（CHARACTER）是一套编码
* 校对规则（COLLATION）是在字符集内用于比较字符的一套规则。 mysql字符集： latin1支持西欧字符、希腊字符等 gbk支持中文简体字符
big5支持中文繁体字符 utf8几乎支持世界所有国家的字符。 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> 修改默认字符集
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的区别
MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes
4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。
utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是
0xffff，也就是 Unicode 中的基本多文种平面(BMP)。
2、数据库对象
命名规则：
* 必须以字母开头
* 可包括数字和特殊字符（_和$）
* 不要使用MySQL的保留字
* 同一Schema下的对象不能同名
3、表的基本操作
数据表的每行称为一条记录（record）；每一列称为一个字段（field）
（1）数据类型
MYSQL中，有三种主要的类型：文本、数值和日期/时间类型
常用数据类型：
（2）创建表
语法：
CREATE TABLE 表名( 列名 列数据类型， 列名 列数据类型 );
例：
mysql8.0 [student]>create table t1(id int,name char(20)); -- 单行创建 mysql8.0
[student]>create table t1( -> id int,name char(20) -> ,address char(50)); /*
多行创建 */ mysql8.0 [student]>show tables; +-------------------+ |
Tables_in_student | +-------------------+ | t1 | | t2 | +-------------------+
（2）查看表
<1> 查看数据库中的所有表
语法：SHOW TABLES[FROM 数据库名][LIKE wild];
例：
mysql8.0 [student]>show tables from mysql like '%server%'; --
查看mysql数据库中含server字段的tables +----------------------------+ | Tables_in_mysql
(%server%) | +----------------------------+ | server_cost | | servers |
+----------------------------+
<2> 显示当前数据库中已有的数据表信息
[1] 语法：{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] 语法：show columns from 表名称;
mysql8.0 [student]>show columns from student.t1;
+-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key
| Default | Extra | +-------+----------+------+-----+---------+-------+ | id |
int | YES | | NULL | | | name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
（3）删除表
语法：DROP TABLE [IF EXISTS] 表名;
mysql8.0 [student]>drop table t1; -- 当前处在数据库中，不指定数据库默认删除本数据库内的表 mysql8.0
[student]>drop table student.t2; mysql8.0 [student]>show tables;
（4）修改表结构 --- ALTER
mysql8.0 [student]>desc t1;
+-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key
| Default | Extra | +-------+----------+------+-----+---------+-------+ | id |
int | YES | | NULL | | | name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+ #修改列类型：ALTER TABLE 表名
MODIFY 列名 列类型; 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
列名 列类型; 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 旧列名 新列名 列类型; 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）复制表结构
<1> 语法：create table 新表名 like 源表
-- 将源表的表结构复制到新表 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> 语法：create table 新表名 select * from 源表
-- 实现表结构的复制，甚至可以将源表的表记录拷贝到新表中 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> 语法：insert into 表名 select * from 原表;
仅复制数据 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数据库中的aaa表的表结构复制到stuinfo数据库中的mm表中
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中创建msd表，将student的sss表结构和数据复制过去
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 | +-------+---------+