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（排序规则）

它们的主要区别在于如何对字符进行排序和比较。

  1. utf8mb4_general_ci:

    • 这是一种较为宽松的排序规则，适合一般的文本排序和比较。
    • 在排序时，不会区分大小写和重音符号（diacritics）。例如，"A"和"a"会被认为是相同的，"é"和"e"也会被认为是相同的。
    • 由于不区分大小写和重音符号，所以在排序时可能会产生一些不符合某些语言特性的结果。

  2. 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 添加字段时，需要注意：

  1. 数据备份：在执行 ALTER TABLE 之前，建议对表进行备份。这样在操作过程中出现问题时，可以恢复到之前的状态。

  2. 性能影响：ALTER TABLE 操作可能会锁定表，导致其他查询或操作受到影响。在表数据量较大时，该操作可能需要更长的时间。因此，建议在业务低峰期进行操作，或考虑使用在线架构更改工具，如 pt-online-schema-change ↗。

  3. 字段类型和默认值：在添加新字段时，请确保选择合适的数据类型，并为其设置默认值（如适用）。这有助于避免数据类型不匹配或空值等问题。

  4. 约束和索引：在添加新字段时，可能需要考虑添加约束（例如 UNIQUE、NOT NULL 等）。此外，根据查询需求，可能需要为新字段创建索引。

  5. 兼容性：如果在生产环境中更改表结构，需要确保已有的应用程序和查询能够正常工作。在执行 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支持多个存储引擎，每个存储引擎都有自己独特的特点和适用场景。

  1. InnoDB：MySQL5.6以后默认的事务性存储引擎。它提供了ACID（原子性、一致性、隔离性和持久性）事务支持和行级锁定。InnoDB适用于处理大量并发操作和保证数据完整性的应用程序。

  2. MyISAM：MySQL早期的存储引擎之一。它对于读密集型应用程序具有较好的性能，但不支持事务和行级锁定。MyISAM适用于非事务性的应用程序，例如博客、新闻网站等。

  3. Memory：也称为Heap存储引擎，将数据存储在内存中，提供了快速的数据访问速度。然而，数据在服务器重启后会丢失，因此适用于临时数据存储或缓存等场景。

  4. Archive：这个存储引擎用于存储大量的归档数据，对于插入和查询效率很高，但不支持更新和删除操作。Archive适用于日志记录、数据归档等场景。

  5. 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 可以显式地开始一个事务，并手动控制事务的提交或回滚。

 

posted @ 2023-07-11 19:50  凡人半睁眼  阅读(21)  评论(0)  编辑  收藏  举报