连接MySQL数据库及MySQL数据类型-源于官方文档

笔记来源：MySQL数据库教程、MySQL参考手册
默认已安装MySQL数据库
在下的学习环境 mysql 8.0.33

连接MySQL后，语句间;表示终止符，下列语句有;表示终止符是必需的

基本操作

连接/断开MySQL

连接

命令	说明
mysql -u root -p	连接当前服务器下的mysql
mysql -h host -u user -p	连接指定服务器下的mysql
mysql -h host -u user -p dbname	连接指定服务器下的mysql的指定数据库

-h host 表示 连接的服务器（host 替换成 服务器地址）
-u root 表示 登录的用户root
-p 密码（默认隐式输入，也可以 -prootpassword，-p后直接接密码）
dbname 指定连接的数据库名称

退出

mysql> QUIT
mysql> exit

查看数据库信息

# 查看MySQL版本 
mysql> status
mysql> \s
mysql> select version();

# 获取warning信息（日期违法，获取告警信息）
mysql> select month('2023-10-32');show warnings;
# 列出数据库列表
mysql> show databases;
# 创建数据库
mysql> create database dbname;
# 使用数据库（进入数据库）
mysql> use dbname
# sql_mode 查看MySQL校验规则（select @@session.sql_mode;select @@global.sql_mode;）
mysql> select @@sql_mode;
# 查看当前数据库
mysql> select database();
# 列出数据库中表
mysql> show tables;
# 列出表结构（使用 desc table_name也可，desc表示describe的缩写）
mysql> describe table_name;
# 获取建标语句
mysql> show create table table_name;
# 获取表创建的索引信息
mysql> show index from table_name;
# 查看MySQL的存储引擎信息
mysql> show engines;

赋权

# 给指定用户赋予指定数据库的所有权限
mysql> GRANT ALL ON dbname.* TO 'your_mysql_name'@'your_client_host';

数据类型

数值

M表示总位数，D表示小数位位数，小数点不参与位数计算
整数类型从MySQL 8.0.17开始，显示宽度属性M已被弃用

类型	范围 （有符号位范围，无符号位范围）
BIT(M)	M范围是1 ~ 64，数值范围取决于M大小，最大64位（-9223372036854775808~9223372036854775807），使用二进制0/1进行存储
TINYINT(M)	-128 ~ 127， 0 ~ 255
BOOL/BOOLEAN	等价 TINYINT(1)，由于宽度属性被弃用，范围与TINYINT一致；可以存储判定式的结果，0表示false，1和其他表示true；例INSERT INTO A ( BOOL_COLUMN) VALUES( 1=2)，实际存储为0，INSERT INTO A ( BOOL_COLUMN) VALUES( 1=1)实际存储1
SMALLINT(M)	-32768 ~ 32767，0 ~ 65535
MEDIUMINT(M)	-8388608 ~ 8388607，0 ~ 16777215
INT(M)	-2147483648 ~ 2147483647，0 ~ 4294967295
INTEGER(M)	xx，官网没描述范围
BIGINT(M)	-9223372036854775808~9223372036854775807 ，0 ~ 18446744073709551615
DECIMAL(M,D)	DECIMAL(65, 30)总位数最大值是65，小数位最大值30；总位数默认10，小数位默认0
FLOAT(M,D)	单精度浮点数，大小受硬件和操作系统影响，且MySQL所有的计算都是按双精度计算，使用FLOAT可能会获取意料外的结果
DOUBLE(M,D)	双精度浮点数，大小受硬件和操作系统影响

整数类型默认是有符号位的（signed）；也可修改为无符号位，无符号即没有负数（ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME TINYINT UNSIGNED;）
浮点类型从MySQL 8.0.17开始，弃用UNSIGNED无符号属性
注意，浮点数据被存储的是近似值而不是精确值（高精度要求时，应使用DECIMAL，而不是FLOAT）
浮点类型，小数位超出D的大小通常采用截断（四舍五入），整数部分大于实际范围则回报Out of range value
从MySQL 8.0.17开始，数值左侧填充0的zerofill 属性也被弃用（整数可以使用lpad()达到效果，或者将格式化的数字存储char类型的列中）

时间

fsp表示秒的小数部分，默认0（没有小数部分），最大为6。

类型	范围
DATE	1000-01-01 ~ 9999-12-31
TIME(fsp)	-838:00:00.000000 ~ 838:59:59.999999
DATETIME(fsp)	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
TIMESTAMP(fsp)	UTC 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999
YEAR	1901 ~ 2155

YEAR(2)在MySQL 8.0不再支持；从 MySQL 8.0.19 开始，YEAR(4)被弃用。
两位制的年份不再使用。之前的规范0 ~ 69表示2000 ~ 2069，70 ~ 99表示1970 ~ 1999

字符

M表示列长度

类型	范围
CHAR(M)	0 ~ 255字符，默认 1，CHAR(0)是一个MySQL允许创建的特殊类型，可用场景，列不需要被获取值，或存储值为null/''空字符串
VARCHAR(M)	可变长度字符串，0 ~ 65535个字节，M不能为空；M大小受字符集影响，例utf8mb3使用3个字节表示字符，则M最大是21844，而utf8mb4使用4个字节表示字符，M最大为16383
BINARY(M)	可变长度字节字符串，M默认 1，最大255字节；存储二进制字节字符串
VARBINARY(M)	M不可为空，最大65535字节；存储二进制字节字符串
TINYBLOB	255 (28 − 1) bytes.
BLOB	65,535 (216 − 1) bytes
TINYTEXT	255 (28 − 1) characters
TEXT	65,535 (216 − 1) characters
ENUM(‘vaule1’, ‘vaule2’, …)	最多65535个字符对象，单个对象最长255个字符，最大不能超过1020个字节长度（1020的大小取决于字符集，若3betys表示一个字符，则最大765字节）
SET(‘vaule1’, ‘vaule2’, …)	最多64个字符对象，单个对象最长255个字符，最大不能超过1020个字节长度（1020的大小取决于字符集，若3betys表示一个字符，则最大765字节）

注意，MySQL表的列具有65535字节长度的限制，当创建多个列时，尽管VARBINARY最大长度是65535，但创建类型VARBINARY(65535)的列时，会报：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOB，M较大的可变长度字符推荐使用TEXT/BLOB代替，TEXT/BLOB列长只占9~10个字节

其他类型

MySQL还支持空间类型、JSON类型等。（在下没用过下列的类型，有一定的了解后会补充这部分内容的）

CREATE TABLE geom (g GEOMETRY);
CREATE TABLE t1 (jdoc JSON);

实用SQL

按A表表结构创建B表

1、WHERE 1 = 2 使创建的B表为空表

CREATE TABLE B_TABLE SELECT * FROM A_TABLE WHERE 1=2; 

2、无查询条件，创建一张与A表表结构和表数据都相同的B表

CREATE TABLE B_TABLE SELECT * FROM A_TABLE

3、根据查询条件，创建一张与A表表结构相同且复制A表中性别为男的数据到B表

CREATE TABLE B_TABLE SELECT * FROM A_TABLE  WHERE SEX = 'M'

数据插入

0、将查询结果插入指定表中（数据类型和列的数量对应即可，SELECT后可以跟复杂的SQL）

INSERT INTO A_TABLE(ID, NAME) SELECT ID , NAME FROM B_TABLE WHERE SEX = 'M'

1、INSERT INTO TABLE_NAME(...) VALUES (...), (...), ...;

# 单笔记录：
INSERT INTO STUDENT(ID, CLASS_ID, NAME, GENDER, SCORE) VALUES(1, 1, '小明', 'M', 100);
# 多笔记录：
INSERT INTO STUDENT(ID, CLASS_ID, NAME, GENDER, SCORE) VALUES(1, 1, '小明', 'M', 100), (2, 3, '小月', 'F', 99)，(3, 1, '小兰', 'F', 100);

2、若ID=1不存在，插入新记录；若ID=1的记录存在，删除原记录，插入新记录；

REPLACE INTO STUDENT(ID, CLASS_ID, NAME, GENDER, SCORE) VALUES(1, 1, '小明', 'M', 100);

3、若ID=1不存在，插入新记录；若ID=1的记录存在，更新记录，按UPDATE更新字段；

INSERT INTO STUDENT (ID, CLASS_ID, NAME, GENDER, SCORE) VALUES (1, 1, '小明', 'F', 99) 
ON DUPLICATE KEY UPDATE NAME='小明', GENDER='F', SCORE=99;

4、若ID=1不存在，插入新记录；若ID=1的记录存在，不做任何操作。

INSERT IGNORE INTO STUDENT (ID, CLASS_ID, NAME, GENDER, SCORE) VALUES (1, 1, '小明', 'F', 99);

补充

# 强制使用指定索引（SQL优化器不总是使用最优索引）
SELECT * FROM TABLE_NAME FORCE INDEX (INDEX_NAME) WHERE COLUMN_NAME = 1 ORDER BY COLUMN_NAME DESC;