mysql8 增删改查、约束条件
补充：建库设定字符集和排序规则
utf8就是utf8mb3的别称，是unicode字符集使用UTF-8编码用3个字节（bytes）标识一个字符（character）的方式输出的字符集；、
utf8mb4是unicode字符集使用UTF-8编码用4个字节（bytes）标识一个字符（character）的方式输出的字符集；在保存内容中有表情符号emoji时,使用utf8会有缺失情况(emoji表情 4 btyes)，而utf8mb4天然支持。
utf8mb4_general_ci vs utf8mb4_unicode_ci（排序规则）
它们的主要区别在于如何对字符进行排序和比较。
-
utf8mb4_general_ci:- 这是一种较为宽松的排序规则，适合一般的文本排序和比较。
- 在排序时，不会区分大小写和重音符号（diacritics）。例如，"A"和"a"会被认为是相同的，"é"和"e"也会被认为是相同的。
- 由于不区分大小写和重音符号，所以在排序时可能会产生一些不符合某些语言特性的结果。
-
utf8mb4_unicode_ci:- 这是一种较为严格的排序规则，基于Unicode字符集，适用于多语言和复杂文本的排序和比较。
- 在排序时，会区分大小写和重音符号。这意味着 "A" 和 "a" 被视为不同的，"é" 和 "e" 也被视为不同的。
- 使用Unicode规则排序，更准确地反映了不同语言的特性，能够正确地处理特殊字符、变音符号和多种语言的排序。
一、表操作
1、查看当前所在的库
> select database(): +------------+ | database() | +------------+ | oldboy | +------------+
2、use database 选择库
3、查看表
> show tables 查看有几个表
> show create table student; 查看创建表的过程
+---------+--------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( | | | `id` int NOT NULL AUTO_INCREMENT, | | | `name` varchar(20) NOT NULL, | | | `sex` varchar(2) NOT NULL, | | | `hobby` varchar(50) DEFAULT NULL, | | | PRIMARY KEY (`id`) | | | ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+--------------------------------------------------------------------------------------+
> desc 查看表结构
4、创建表
create table t1(id int(11), name varchar(20), age int);
5、修改表结构 alter
添加字段：
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], > alter table t8 add column age int; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 添加在第一行 > alter table t8 add column gender char(5) first; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 指定在哪个字段后添加 > alter table t8 add column hobby varchar(16) after gender;
生产环境 MySQL 已有数据的表中使用 ALTER TABLE 添加字段时，需要注意：
-
数据备份：在执行
ALTER TABLE之前，建议对表进行备份。这样在操作过程中出现问题时，可以恢复到之前的状态。 -
性能影响：
ALTER TABLE操作可能会锁定表，导致其他查询或操作受到影响。在表数据量较大时，该操作可能需要更长的时间。因此，建议在业务低峰期进行操作，或考虑使用在线架构更改工具，如 pt-online-schema-change ↗。 -
字段类型和默认值：在添加新字段时，请确保选择合适的数据类型，并为其设置默认值（如适用）。这有助于避免数据类型不匹配或空值等问题。
-
约束和索引：在添加新字段时，可能需要考虑添加约束（例如
UNIQUE、NOT NULL等）。此外，根据查询需求，可能需要为新字段创建索引。 -
兼容性：如果在生产环境中更改表结构，需要确保已有的应用程序和查询能够正常工作。在执行
ALTER TABLE之前，评估更改对应用程序的影响，并进行相应的调整。
删除列：
ALTER TABLE 表名 DROP 字段名; # 实例 > alter table t10 drop column age;
修改列数据类型：
modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; # 实例 > alter table t10 modify column my_name char(10);
修改列名：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; # 实例 alter table t10 change column name my_name varchar(10);
修改表名
alter table t1 rename tt1;
修改用户的认证方式和密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
在 MySQL 8 中，ALTER USER 命令可以用于修改用户的属性，包括身份验证方式和密码。使用了 IDENTIFIED WITH mysql_native_password 子句来指定使用 MySQL 原生密码验证方式。然后，使用 BY '密码' 子句来设置新的密码。
需要注意的是，在 MySQL 8 之前的版本中，默认使用的是旧的密码验证方式（旧版加密），而在 MySQL 8 中，推荐使用 mysql_native_password 认证插件和更安全的密码加密方法。因此，通过使用 ALTER USER 命令并指定 mysql_native_password，你可以成功
6、添加主键
alter table table_name add primary key(id);
7、删除表
drop table tt1；
8、重命名库
rename方式：它不能直接用于重命名数据库。不过，可以间接使用 RENAME TABLE 语句逐个重命名数据库中的所有表，从而实现数据库重命名的效果。
新建test2库，对于老库table1 的每个表，使用 RENAME TABLE 语句来移动它们到新数据库 test2。
每个表都需要执行一次，执行完，旧表变为新表
RENAME TABLE test1.table1 TO test2.table1;
9、清空表的两种方式
方式一：delete
delete 不会把主键的id值重置，会把数据清空
> delete from t8 where id =2;
> insert into t8( name) VALUES('tanke'),('kev');
> select * from t8;
+----+-------+
| id | name |
+----+-------+
| 1 | zjz |
| 3 | tanke |
| 4 | kev |
+----+-------+
方式2: truncate
truncate 不仅能清空数据表，而且主键的值也会重置
> truncate t8;
> insert into t8( name) VALUES('tanke'),('kev');
> select * from t8;
+----+-------+
| id | name |
+----+-------+
| 1 | tanke |
| 2 | kev |
+----+-------+
二、表记录操作
select 查看 insert 插入 update 更新 delete 删除
1、查看
select * from t1; select id, name from t1;
2、增加数据
insert into tt1 values(1, 'kevin', 18),(2, 'tanke', 20), (3, 'json', 21); # 只增加部分字段 > insert into tt1(id, name) values(4, 'malin'), (6, 'sd');
3、修改数据
update 表名 set 字段名='字段值' where 条件;
update tt1 set age='88' where name='kevin';
update tt1 set age='89' where name='kevin' and id=1;
update tt1 set name='aaa'; # 全表改，一定不能使用
# 更新用户密码，authentication_string 字段更新为密码的加密值
PASSWORD 函数在 MySQL 8 中已被弃用，且在使用过程中可能会产生警告。建议使用更安全的方法来处理用户密码，例如使用 SHA2 函数。
UPDATE user SET authentication_string = SHA2('Zjz5740', 256) WHERE Host = 'localhost' AND User = 'root';
4、删除数据
> delete from tt1 where id=4; >delete from t1 where id =1 and name='';
三、存储引擎
1、存储引擎
MySQL的存储引擎是指MySQL数据库系统用于管理和组织数据存储的组件或模块。存储引擎负责处理数据的存储、检索、索引和事务处理等功能。MySQL支持多个存储引擎，每个存储引擎都有自己独特的特点和适用场景。
-
InnoDB：MySQL5.6以后默认的事务性存储引擎。它提供了ACID（原子性、一致性、隔离性和持久性）事务支持和行级锁定。InnoDB适用于处理大量并发操作和保证数据完整性的应用程序。
-
MyISAM：MySQL早期的存储引擎之一。它对于读密集型应用程序具有较好的性能，但不支持事务和行级锁定。MyISAM适用于非事务性的应用程序，例如博客、新闻网站等。
-
Memory：也称为Heap存储引擎，将数据存储在内存中，提供了快速的数据访问速度。然而，数据在服务器重启后会丢失，因此适用于临时数据存储或缓存等场景。
-
Archive：这个存储引擎用于存储大量的归档数据，对于插入和查询效率很高，但不支持更新和删除操作。Archive适用于日志记录、数据归档等场景。
-
NDB Cluster：也称为MySQL Cluster存储引擎，提供了高可用性和分布式数据库功能。它适用于需要水平扩展和高可靠性的应用程序，如大规模的Web应用和实时系统。
show engines; # 在mysql8中查看支持的11种引擎：
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+ | ndbcluster | NO | Clustered, fault-tolerant tables | <null> | <null> | <null> | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | ndbinfo | NO | MySQL Cluster system information storage engine | <null> | <null> | <null> | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
2、验证前三种存储引擎产生的文件
create table t2(id int) engine=MyISAM;
mysql8中产生三个文件
- t2.MYD ：这是MyISAM表的数据文件，包含表中的实际数据记录。
- t2.MYI ：这是MyISAM表的索引文件，包含用于加快数据检索的索引结构。
- t2_381.sdi：这是MyISAM表的源数据文件，它包含了表的结构定义和其他相关信息。
在mysql5.6 中产生三个文件
- .frm: 这个文件存表结构
- .MYD：这个文件存数据 data
- .MYI: 这个文件存数据的索引 index >>> 类似于是书的目录 >>> 加快查询速度的
create table t3(id int) engine=InnoDB;
mysql8中产生一个文件
- t3.ibd ：表示InnoDB表的数据文件，InnoDB将表的数据和索引存储在单独的.ibd文件中。
在mysql5.6 中产生两个文件
- .frm: 这个文件存表结构
- .ibd: 这个文件存数据的索引和数据
create table t4(id int) engine=MEMORY;
mysql8中产生一个文件
- t4_383.sdi
在mysql5.6 中产生一个文件
- .frm: 这个文件存表结构
四、数据类型
查看mysql8支持的所有数据类型
SELECT DISTINCT DATA_TYPE FROM information_schema.COLUMNS;
+------------+ | DATA_TYPE | +------------+ | bigint | | varchar | | timestamp | | int | | longtext | | enum | | text | | mediumtext | | json | | datetime | | set | | binary | | char | | varbinary | | tinyint | | blob | | double | | decimal | | longblob | | smallint | | mediumblob | | time | | float | | year | | date | +------------+
1、整型
tinyint smallint mediumint int bigint
# 不同的类型存储的范围不一样
存储范围比较：
tinyint：1个字节------>8位------>2 **8----> 256----->0-255----->-128-127
smallint: 2个字节存储----->16位---->2 ** 16 ----> 65536----->0-65535---->-32768-32767
mediumint： 3个字节存储----->24位----->2 ** 24--->16777216-----> 0-16777215 ----> -8388608 - 8388607
int: 4个字节------>32位----->2 ** 32----> 21...---->
bigint： 8个字节----->64为----->2 ** 64---->
验证：
create table t5 (id tinyint);
> insert into t5 values(999); (1264, "Out of range value for column 'id' at row 1") # 在mysql8中会直接报错
create table t5 (id smallint);
> insert into t5 values(6666666); (1264, "Out of range value for column 'id' at row 1")
结论：在mysql8中指定int的类型之后，数据超过范围会直接报错，而mysql5.6中会存储最大的范围数字
2、浮点型
float double decimal
float(255, 30); # 255表示的是存储的位数，30代表的是小数位数
double(255,30); # 255表示的是存储的位数，30代表的是小数位数
decimal(65, 30); # 65表示的是存储的位数，30代表的是小数位数。 decimal最大只能存65位
decimal(8, 2) # 表示的最大范围是：999999.99
三者的区别
create table t6(id float(255, 30)); create table t7(id double(255, 30)); create table t8(id decimal(65, 30));
插入数据
insert into t6 values(1.111111111111111111111111);
+--------------------+ | id | +--------------------+ | 1.1111111640930176 | +--------------------+
insert into t7 values(1.111111111111111111111111);
+--------------------+ | id | +--------------------+ | 1.1111111111111112 | +--------------------+
insert into t8 values(1.111111111111111111111111);
+----------------------------------+ | id | +----------------------------------+ | 1.111111111111111111111111000000 | +----------------------------------+
"""得出结论：精确度不一样, 生产环境推荐使用decimal """
decimal >>> double >>> float
3、字符串
char 和 varchar
char(4): 它是定长，那么他就存4位，如果没有超出4位，空格填充到4位，超出4位，报错或者，最大存4位
varchar(4)：可变长的，不超出4位，有几位存几位，不会使用空格填充，超出4位，报错或者，最大存4位
验证
create table t9(id int, name char(4)); insert into t9 values(1, 'kevin'); create table t10(id int, name varchar(4)); insert into t10 values(1, 'kevin');
在mysql8中超出范围会直接报错
mysql5.6中设置：
------------------------------------------------------
如何查看严格模式
1. select @@sql_mode;
2. show variables like '%sql_mode%' # 模糊查询，变量中带有mode的
# 设置严格模式
1. 永久修改：需要改配置文件
2. 临时修改：
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
------------------------------------------------------
在mysql8 中查看name字段的长度与mysql5.6有区别，虽然看不到填充位，但是空位填充上了
> SELECT @@SESSION.sql_mode; # 查看模式 > SET SESSION sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH'; # 临时设置严格模式 > SELECT CHAR_LENGTH(name) FROM t1; 查看表 > SELECT CHAR_LENGTH(name) FROM t2;
4、五种日期类型
DATE：用于存储日期值，格式为 'YYYY-MM-DD'。
TIME：用于存储时间值，格式为 'HH:MM:SS'。
DATETIME：用于存储日期和时间值，格式为 'YYYY-MM-DD HH:MM:SS'。
TIMESTAMP：也用于存储日期和时间值，格式为 'YYYY-MM-DD HH:MM:SS'。但是，TIMESTAMP 在MySQL中有特殊的行 为，会自动转换为当前时区的时间，并在插入或更新时自动记录时间戳。
YEAR：用于存储年份值，格式为 'YYYY'。在MySQL 8中，YEAR 数据类型仅存储年份，不包含月份和日期。
创建表：
> create table t11(id int,
-> reg_time datetime,
-> update_time date,
-> delete_time time,
-> birth year,
-> timetamp timestamp);
插入数据：
INSERT INTO t11 VALUES (1, '2023-07-11 10:30:00', '2023-07-11', '10:30:00', 1990, CURRENT_TIMESTAMP);
CURRENT_TIMESTAMP 换成 NOW()也行
5、枚举类型
enum:多选一
create table t15 (
id int,
hobby enum('tangtou', 'hejiu', 'xijio', 'chouyan'));
insert into t15 values(1, 'tangtou');
+----+---------+ | id | hobby | +----+---------+ | 1 | tangtou | +----+---------+
性别二选一：
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
set：
多选多
create table t16 (id int, hobby set('tangtou', 'hejiu', 'xijio', 'chouyan'));
INSERT INTO t16 VALUES (1, 'tangtou,hejiu');
+----+---------------+ | id | hobby | +----+---------------+ | 1 | tangtou,hejiu | +----+---------------+
五、整型中的数字含义
char(4)
varchar(4) # 括号里的数字代表的就是存储的长度
# 整型的存储范围跟括号中的数字有没有关系：没有任何关系
整型的存储范围完全是跟关键字相关，int, smallint
int(3)表示展示的时候要是位数不够，或者超出都不影响，原样输出
create table t11 (id int(3)); insert into t11 values(99999);
整型中括号中的数字 + zerofill。超出3位的原样输出，不足的使用0进行填充。
这个效果在mysql8.0.23中没看到填充效果, 查看char_length(id) 可以看出填充后的位数。
注意zerofill是针对整型
create table t12 (id int(3) zerofill); insert into t12 values(9); create table t13 (id int(11) zerofill); insert into t13 values(9);
六、约束条件
1、unsigned 去除符号（去负号）
> create table t1(id int unsigned); Query OK, 0 rows affected Time: 0.006s > insert into t1 VALUES(-19); (1264, "Out of range value for column 'id' at row 1") > insert into t1 VALUES(19); Query OK, 1 row affected Time: 0.002s
2、zerofill
> create table t2(id int(4) zerofill);
3、not null 非空
> create table t3(id int, name varchar(8) not null); > insert into t3 VALUES(1, ''); # mysql中' '不为空 > insert into t3 VALUES(1, null); (1048, "Column 'name' cannot be null")
4、unique 唯一
单列唯一: unique
> create table t4(id int, name varchar(8) unique); > insert into t4 VALUES(1, 'zjz'); > insert into t4 VALUES(2, 'zjz'); # 重复插入即报错 (1062, "Duplicate entry 'zjz' for key 't4.name'")
联合唯一: unique(x,x)
> create table t5(id int,
-> ip varchar(20),
-> port int,
-> unique(ip,port)
-> );
> insert into t5 VALUES(1, '127.0.0.1', 3306),(2,'192.168.20.2', 8888);
> insert into t5 VALUES(3,'192.168.20.2', 8888);
(1062, "Duplicate entry '192.168.20.2-8888' for key 't5.ip'")
5、设置默认值： default
> create table t6(id int, name varchar(16), age int default 18); > insert into t6(id, name) VALUES(1,'zjz'); > select * from t6; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | zjz | 18 | +----+------+-----+
6、主键: primary key
单纯的从约束条件上来看，主键就相当于是not null + unique，非空且唯一
> create table t7(id int primary key, name varchar(16));
InnoDB存储引擎要求每一张表必须要有一个主键，InnoDB存储引擎内部隐藏的有一个主键字段，但是这个隐藏的字段我们看不到。
7、自增： auto_increment
让主键每次自动增加1
> create table t8(id int primary key auto_increment, name char(10));
> insert into t8(name) VALUES('zjz'),('kevin');
> select * from t8;
+----+-------+
| id | name |
+----+-------+
| 1 | zjz |
| 2 | kevin |
+----+-------+
关于TRANSACTION 事务：（留到后续补充）
START TRANSACTION; -- 执行一系列的SQL语句（增删改操作） COMMIT;
START TRANSACTION 开始一个事务，并将其标记为事务的起始点。然后，你可以执行一系列的SQL语句，这些语句可以包括插入、更新或删除数据的操作。最后，使用 COMMIT 提交事务，将所有的操作永久性地应用到数据库中。
如果在事务过程中出现错误或需要回滚操作，可以使用 ROLLBACK 语句来取消事务并回滚到事务开始的状态。
请注意，在默认情况下，MySQL 8 中的自动提交（autocommit）是启用的，这意味着每个语句都会自动成为一个单独的事务。使用 START TRANSACTION 可以显式地开始一个事务，并手动控制事务的提交或回滚。
