Mysql——表操作

重要，重要，重要：

创建表

创建时需要考虑点有：

1. 需要哪些字段

  • 字段名称
  • 字段结构
  • 主键是哪个
  • 设置自增，唯一，复选，不能为空

2. 需不需要创建关联

  • 有没有重复字段
  • 关联的方式是一对一，一对多还是多对多

3. 需不需要创建索引

  • 经常需要查询的字段有哪些
  • 区分度高不高
  • 需不需要设置联合查

创建表

create table t1(id1 int,id2 int);

删除表

drop table t1;

写入数据

insert into t1 values(1,2);

如果有自增字段，就无需写入改字段的值，那么就需要进行字段和值的一一对应。

value与vlaues的区别：

一次性写入一行数据，和一次性写入多行数据。

更新数值

1. 级联无法使用，需要使用级联更新。

update t1 set id = 2 where id = 8;

删除数值

1. 级联无法使用，需要使用级联删除。

delete from t1 where id1 = 2;

delete from t1 where id1 is  null;

 

 只给一个值写入数据。

insert into t1(id1) values(8);

 

拼接字段：

select concat(id1,'---'),concat(id2,'===')from t1; 

表结构操作

数值类型

整数：

  1. 创建时，创建长度不生效（只是显示长度）
  2. 不能超过其范围值，超过就只保存最后一位（127为规定最长长度看，写129,也会成为127）
  3. 默认是有符号的，unsigned进行负值的取消。

小数：

  1. FLOAT和DOUBLE不会非常准确，会存在2进制与十进制之前转换的错误可能性。DECIMAL为非常准确。
  2. 在设置完小数点位数后，加入多的数值将会进行四舍五入。
  3. 单精度默认为小数点后五位，双精度默认为小数点后16位
  4. DECIMAL默认为只存整数，需要指定位数。

 

类型

大小

范围（有符号）

范围（无符号）unsigned约束

特点

TINYINT

1 字节

(-128，127)

(0，255)

小整数值

常用语年龄等确认不超过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 233 372 036 854 775 808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

极大整数值

FLOAT

4 字节

float(255,30)

255表示一共多少位数值，30表示占用255内小数点后多少位。

(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)

0，(1.175 494 351 E-38，3.402 823 466 E+38)

单精度
浮点数值

最常用

DOUBLE

8 字节

double(255,30)，同上

(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

双精度
浮点数值

DECIMAL

对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2

double(65,30)

依赖于M和D的值

依赖于M和D的值

小数值

非常精准

长度略有不足

默认只存整数

使用字符类进行存储

 

日期与时间类型

  1. 写入数据时，使用now（）为写入当前的数据库时间。
  2. NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，在datetime后加上后，可以使值在不赋值使自动生成当前时间进行插入。
  3. 可以以数字形态：20191230210000 也可以以字符串形式：'2019-12-30 21:00:00'书写
  4.  
类型 大小
(字节)		 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 年月日
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时分秒
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 年月日时分秒
TIMESTAMP 4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

 YYYYMMDD HHMMSS

混合日期和时间值，时间戳

默认不写也会进行传值。

 

字符串类型

  1. char设置长度后，不足将会以空格形式自动补齐
 
类型 大小 用途
CHAR 0-255字节

定长字符串

常用

定长单位

浪费空间

存取效率相对较高

长度较为固定，幅度不会太多（手机，身份证号等）
VARCHAR 0-65535 字节

变长字符串

常用

变长单位

节省空间

存储效率相对低

长度不一，幅度较大（评论，说说等）
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
 
 

修改表名

alter table 表名 rename 新表名；

添加字段

alter table 表名 add 

alter table 表名 add 字段名 数据类型（宽度）约束。。。

删除字段

alter table 表名 drop

alter table 表名 drop 字段名；

修改已经存在的字段

（类型，宽度，约束，不能更改字段名）

alter table 表名 modify

alter table 表名 modify 字段名 数据类型（宽度）约束。。。

移动表结构

原始表：

id name age

使用该命令后：

alter table 表名 modify age int not null after id;
id age name
alter table 表名 modify age int not null after id;
age id name

设置约束

修改约束

去掉null约束
alter table t modify name char(10) null;

添加null约束
alter table t modify name char(10) not null;

去掉unique约束
alter table t drop index id;

添加unique约束
alter table t modify id int unique;

联合唯一

alter table t add unique index(aa,bb);

外键

为book表添加外键

alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键

alter table book drop foreign key fk_id;

not null

设置某一个字段不能为空。

设置严格模式：

1. 不支持对not null字段插入null值

2. 不支持对自增长字段插入”值

3. 不支持text字段有默认值

直接在mysql中生效(重启失效):

mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件(my.ini)添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

default

设置默认值，可单独使用，通常和not null一起使用。

当写入值时，由于系统不知道值何键的对应关系，所以需要进行指定。

 

unique

设置某一个字段不能重复，但可以设置多个字段为null，可以使用not null来进行配合。

联合唯一，将两个字段联合在一起将是唯一的。

 

auto_increment 

自增字段：

1. 必须是数字且必须是唯一的

2. 自带非空值

3. 删除字段不会插入已经删除字段内，只会在后面进行增加。

4. 可以单独设置自增字段的值，系统将会以设置字段值后再进行插入。

5. 如果设置小于之前已经设置的值时，插入将成功，但不会回到插入值后。查看目前以使用的值使用show create table t8来查看。

 

 

 

primary key 主键

1. 设置某一个字段非空且唯一

2. 一张表只能设置一个主键，且最好每张表都设置一个主键。

3. 当表中没有设置主键是，第一个设置的非空且唯一将成为主键。

联合主键

设置多个字段不为空，且联合不能重复。和联合唯一配置一样，命令改成primary key即可。

 

foreign key

将多个表进行关联，将大量相同内容进行分开处理，大大提高数据库的读取，修改速度。

1. 数据类型必须要一致

2. 被关联表值，最好关联主键

3. 先创建需要关联的表，在创建关联表。
4. 先创建关联的表内容，否则关联表中无法写入关联系统，因为被关联内容不存在。

5. 外键关联的表中字段必须是unique

创建表的最后放：foreign key （本表关联字段）  references 外表表名 （表值）;

表与表之间的关联使用注意事项：

1. 一对一关联时，关联表需要有被关联表的ID字段并设置成唯一，再进行关联即可。

2. 一对多关联时，通常在关联表（多）创建外键，来关联被关联表（一）的情况。取消上面的唯一字段即可。关联时按照谁后出现就使用unique。

3. 多对多关联时，两张表无需额为增加关联字段，需要使用第三章表，设置本表的ID字段，关联表ID和被关联表字段ID。并进行关联。

 

 

级联其他用法：

创建表的最后放：foreign key （本表关联字段）  references 外表表名 （表值）

updata cascade on delete cascade;
cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 

set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null要注意子表的外键列不能为not null  

No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

Restrict方式
同no action, 都是立即检查外键约束

Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

事务锁

即将几个命令合在一起执行，避免在其中因其他问题导致的没有完成，而在中途挂掉。像转钱，这边扣了结果那边还没加进去就挂了。

在update后，该列将被锁定，其他人无法进行更改。直到退出为止。

实例：


mysql> desc staff_info;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 11 )               | YES  |     | NULL    |       |
| name  | varchar( 50 )           | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 表重命名
mysql> alter table staff_info rename staff;
Query OK,  0 rows affected ( 0.00 sec)
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 11 )               | YES  |     | NULL    |       |
| name  | varchar( 50 )           | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 删除sex列
mysql> alter table staff drop sex;
Query OK,  0 rows affected ( 0.02 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 11 )     | YES  |     | NULL    |       |
| name  | varchar( 50 ) | YES  |     | NULL    |       |
| age   |  int ( 3 )      | YES  |     | NULL    |       |
| phone | bigint( 11 )  | YES  |     | NULL    |       |
| job   | varchar( 11 ) | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
5 rows  in set ( 0.01 sec)
 
# 添加列
mysql> alter table staff add sex enum( 'male' , 'female' );
Query OK,  0 rows affected ( 0.03 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
# 修改id的宽度
mysql> alter table staff modify  id int ( 4 );
Query OK,  0 rows affected ( 0.02 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 4 )                | YES  |     | NULL    |       |
| name  | varchar( 50 )           | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.01 sec)
 
# 修改name列的字段名
mysql> alter table staff change name sname varchar( 20 );
Query OK,  4 rows affected ( 0.03 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 4 )                | YES  |     | NULL    |       |
| sname | varchar( 20 )           | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 修改sex列的位置
mysql> alter table staff modify sex enum( 'male' , 'female' ) after sname;
Query OK,  0 rows affected ( 0.02 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 4 )                | YES  |     | NULL    |       |
| sname | varchar( 20 )           | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 创建自增id主键
mysql> alter table staff modify  id int ( 4 ) primary key auto_increment;
Query OK,  4 rows affected ( 0.02 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| Field |  Type | Null | Key | Default | Extra          |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
|  id |  int ( 4 )                | NO   | PRI | NULL    | auto_increment |
| sname | varchar( 20 )           | YES  |     | NULL    |                |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |                |
| age   |  int ( 3 )                | YES  |     | NULL    |                |
| phone | bigint( 11 )            | YES  |     | NULL    |                |
| job   | varchar( 11 )           | YES  |     | NULL    |                |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR  1075 ( 42000 ): Incorrect table definition; there can be only one auto column  and it must be defined as a key
 
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify  id int ( 11 );
Query OK,  4 rows affected ( 0.02 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 11 )               | NO   | PRI |  0 |       |
| sname | varchar( 20 )           | YES  |     | NULL    |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
| age   |  int ( 3 )                | YES  |     | NULL    |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.01 sec)
 
mysql> alter table staff drop primary key;
Query OK,  4 rows affected ( 0.06 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
# 添加联合主键
mysql> alter table staff add primary key (sname,age);
Query OK,  0 rows affected ( 0.02 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
# 删除主键
mysql> alter table staff drop primary key;
Query OK,  4 rows affected ( 0.02 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
# 创建主键id
mysql> alter table staff add primary key ( id );
Query OK,  0 rows affected ( 0.02 sec)
Records:  0 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
| Field |  Type | Null | Key | Default | Extra |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
|  id |  int ( 11 )               | NO   | PRI |  0 |       |
| sname | varchar( 20 )           | NO   |     |         |       |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |       |
| age   |  int ( 3 )                | NO   |     |  0 |       |
| phone | bigint( 11 )            | YES  |     | NULL    |       |
| job   | varchar( 11 )           | YES  |     | NULL    |       |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - +
6 rows  in set ( 0.00 sec)
 
# 为主键添加自增属性
mysql> alter table staff modify  id int ( 4 ) auto_increment;
Query OK,  4 rows affected ( 0.02 sec)
Records:  4 Duplicates:  0 Warnings:  0
 
mysql> desc staff;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
| Field |  Type | Null | Key | Default | Extra          |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
|  id |  int ( 4 )                | NO   | PRI | NULL    | auto_increment |
| sname | varchar( 20 )           | NO   |     |         |                |
| sex   | enum( 'male' , 'female' ) | YES  |     | NULL    |                |
| age   |  int ( 3 )                | NO   |     |  0 |                |
| phone | bigint( 11 )            | YES  |     | NULL    |                |
| job   | varchar( 11 )           | YES  |     | NULL    |                |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - +
6 rows  in set ( 0.00 sec)

创建索引实例

准备工作

#1. 准备表
create table s1(
id int ,
name varchar(  20 ),
gender char(  6 ),
email varchar(  50 )
);
 
#2. 创建存储过程，实现批量插入记录
delimiter $$   #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i   int default   1 ;
    while (i<  3000000 )do
        insert into s1 values(i,  'eva' ,  'female' ,concat(  'eva' ,i,  '@oldboy' ));
        set i  = i  + 1 ;
    end   while ;
END$$   #$$结束
delimiter ;   #重新声明分号为结束符号
 
#3. 查看存储过程
show create procedure auto_insert1\G
 
#4. 调用存储过程
call auto_insert1();

　　

 

posted @   新兵蛋Z  阅读(37)  评论(0)  编辑  收藏  举报
相关博文：
· ORM——类&对象操作
· Mysql——文字类
· 初识MySQL
· MySQL笔记（二）
· MySQL表操作
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛