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 | +-------+---------+

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:[email protected]
QQ群:766591547
关注微信