Mysql

下载

官网下载

Ubuntu 22.04 安装 mysql 8.0

// 更新 Ubuntu 本地软件仓库的包索引
apt update

// 查看可使用的安装包
apt search mysql-server
// 安装最新版本mysql
apt install mysql-server

// 启用额外的安全功能：密码强度、匿名用户、禁止远程 root 登录、删除测试数据库等
mysql_secure_installation

// 修改root密码, 创建新用户
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'securepassword';
FLUSH PRIVILEGES;

CREATE USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'asset'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

// 打开远程访问, 解除 only_full_group_by 限制
vi /etc/mysql/mysql.conf.d/mysqld.cnf 
#bind-address           = 127.0.0.1
#mysqlx-bind-address    = 127.0.0.1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

// 重启mysql
systemctl restart mysql

Windows 安装 mysql 8.0

// MySQL解压到目录 D:\Tools\mysql-8.4.5-winx64

// 创建my.ini 内容为
[mysqld]
port=3308
basedir=D:\Tools\mysql-8.4.5-winx64
datadir=D:\Tools\mysql-8.4.5-winx64\data
socket=MySQL84
shared-memory
shared-memory-base-name=MySQL84
enable-named-pipe
named-pipe

// 初始化和启动
mysqld --initialize --console   # 初始化, 生成data目录。 删除data目录后可再次初始化
mysqld --install MySQL84 --defaults-file=D:\Tools\mysql-8.4.5-winx64\my.ini   # 安装服务
net start MySQL84           # 启动服务

// 出错情况下, 涉及命令
mysqld --remove MySQL84     # 移除服务
net stop MySQL84            # 停止服务

// 修改root密码, 创建新用户
mysql -u root -P 3308 -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'securepassword';
FLUSH PRIVILEGES;

CREATE USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

数据类型

数值类型	大小(B)	范围 (有符号)	范围 (无符号)	有效位数
TINYINT	1	[-128, 127]	[0, 255]
SMALLINT	2	[-32,768, 32,767]	[0, 65,535]
MEDIUMINT	3	[-8,388,608, 8,388,607]	[0, 16,777,215]
INT, INTEGER	4	[-2,147,483,648, 2,147,483,647]	[0, 4,294,967,295]
BIGINT	8	(-9.2E+18, 9.2E+18)	(0, 1.8E+19)
FLOAT	4	(-3.4E+38，-1.2E-38)，0，(1.2E-38，3.4E+38)	0，(1.2E-38，3.4E+38)	7位, 例(7,3)
DOUBLE	8	(-1.8E+308，-2.2E-308)，0，(2.2E-308，1.8E+308)	0，(2.2E-308，1.8E+308)	16位, 例(16,3)
DECIMAL(M,D)	若M>D为M+2否则为D+2	依赖M和D值	依赖M和D值	依赖M和D值

串类型	储存大小
CHAR(M)	M字节，1 <= M <= 255
VARCHAR(M)	L+1 字节, 在此L <= M和1 <= M <= 255
TINYBLOB, TINYTEXT	L+1 字节, 在此L< 2 ^ 8
BLOB, TEXT	L+2 字节, 在此L< 2 ^ 16
MEDIUMBLOB, MEDIUMTEXT	L+3 字节, 在此L< 2 ^ 24
LONGBLOB, LONGTEXT	L+4 字节, 在此L< 2 ^ 32
ENUM('value1','value2',...)	1 或 2 个字节, 取决于枚举值的数目(最大值65535）
SET('value1','value2',...)	1，2，3，4或8个字节, 取决于集合成员的数量(最多64个成员）

日期类型	最小值	最大值	零值表示
timestamp	1970-01-01 08:00:01	2038-01-19 03:14:07.999999(准确的来讲应该是UTC范围)	0000000000000000[000000]
datetime	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00[.000000]
date	1000-01-01	9999-12-31	0000-00-00
time	-838:59:59	838:59:59	00:00:00[.000000]
year	1901	2155	0000

日期数据存储空间

INSERT 语句

INSERT IGNORE INTO dict_tbl SET title = 'knowledge', content = 'dict';
INSERT IGNORE INTO dict_tbl (title, content) VALUES ('knowledge', 'dict');
INSERT INTO dict_tbl (title, content) VALUES ('knowledge', 'dict') ON DUPLICATE KEY UPDATE title = VALUES(title), content = VALUES(content);
INSERT INTO table1 (field1, field2) SELECT field1, field2 FROM table2 WHERE condition;
INSERT INTO table1 SELECT * FROM table2 WHERE condition;
INSERT IGNORE INTO table1 SELECT * FROM table2 WHERE condition;

DELETE 语句

DELETE FROM table_name WHERE condition;

UPDTAE 语句

UPDATE table_name SET surname = 'Ambani', age = 25 WHERE cus_id = 5;
UPDATE T03_policy_info SET holderAddr=REPLACE(holderAddr,'北京市通州区北京市通州区','北京市通州区');
UPDATE community c, community_bj b SET c.house_total = b.house_amount WHERE c.community_id=b.community_id;

misc

// CLI登录
mysql -h 192.168.0.99 -u root -p
// 查看锁信息（死锁分析）
SHOW ENGINE INNODB STATUS;
// 优化表
OPTIMIZE TABLE user;