mysql 字段切割并返回第2个 mysql切表

转载

mob6454cc743894 2024-07-24 04:50:27

文章标签 mysql 字段切割并返回第2个 mysql 数据类型字段名 文章分类 MySQL 数据库

MySQL表相关操作

一表相关操作基础

1 表介绍
2 创建表

2.1 语法及注意事项
2.2 代码示例：

3 查看表结构
4 修改表

4.1 修改表名
4.2 修改字段
4.3 增加字段
4.4 删除字段
4.5 代码示例

5 表字段的数据类型

5.1 介绍
5.2 数值类型

5.2.1 整数类型
5.2.2 浮点型

5.3 日期类型

5.3.1 datetime与timestamp的区别

5.4 字符串类型

5.4.1 基本情况
5.4.2 基本使用验证
5.4.3 总结

5.5 枚举类型与集合类型

6 复制表
7 删除表

二表操作之完整性约束

1 介绍

1.1 约束条件
1.2 主要分类及说明：

2 not null与default
3 unique

3.1 单列唯一
3.2 联合唯一

4 primary key

4.1 主键是什么
4.2 特点：**
4.3 代码验证

5 auto_increment
6 foreign key

6.1 什么是foreign key
6.2 如何找出两张表之间的关系
6.3 建立表之间的关系

6.3.1 一对多或称为多对一
6.3.2 多对多
6.3.3 一对一

MySQL表相关操作

一表相关操作基础

1 表介绍

表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。

id	name	age	字段：一条记录对应的标题称为一个字段，如id，name，age
1	kiessling	37	记录：一行内容称为一条记录
2	lars	30
3	leno	28

2 创建表

2.1 语法及注意事项

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

## 注意：
    1).在同一张表中，字段名是不能相同的；

    2).宽度和约束条件可选；

    3).字段名和类型是必须的；

    4).表中的最后一个字段不要加逗号！！"

2.2 代码示例：

mysql> create database db1 charset utf8;  # 创建库并指定字符编码(文件夹) 
mysql> use db1;  # 切换文件夹
mysql> create table t1(    # 创建表，并指定字段，数据类型
    -> id int,             # 一般创建表必指定的字段
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)    # 整型字段后面指定的是显示长度，不是数据实际的存储长度
    -> );
mysql> show tables; #查看db1库下所有表名
mysql> desc t1;  # 查看表的结构
mysql> select id,name,sex,age from t1;  # 查看指定字段的内容
mysql> select * from t1;  # 查看表的所有内容
mysql> insert into t1 values  # into可省略
    -> (1,'egon',18,'male'),
    -> (2,'alex',81,'female')
    -> ;
mysql> insert into t1(id) values  # 插入值全为NULL
    -> (3),
    -> (4);

3 查看表结构

MariaDB[db1]> describe t1;  # 查看表结构，可简写为desc 表名
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

MariaDB [db1]> show create table t1\G; # 查看表详细结构，可加\G

4 修改表

4.1 修改表名

alter table 表名 rename 新表名;

4.2 修改字段

alter table 表名 modify 字段名 数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];

4.3 增加字段

alter table 表名 add 字段名 数据类型 [完整性约束条件],
				add 字段名 数据类型 [完整性约束条件];
alter table 表名 add 字段名 数据类型 [完整性约束条件] first;
alter table 表名 add 字段名 数据类型 [完整性约束条件] after 字段名;

4.4 删除字段

alter table 表名 drop 字段名;

4.5 代码示例

1) 先创建库，在新建表
mysql> create database db1;
mysql> user db1
mysql> create table t1(id int,name char(4));
# mysql> alter table t1 rename tt1;  # 表名的修改

2) 修改存储引擎
mysql> alter table t1 engine=innodb;

3) 修改字段
mysql> alter table t1 modify id tinyint;  # 修改字段的数据类型
mysql> alter table t1 change id ID tinyint;  # 修改字段名及数据类型
mysql> alter table t1 change ID id tinyint,change name NAME char(4);  # 修改多个字段名及数据类型

4) 增加字段
mysql> alter table t1 add gender char(4);
mysql> desc t1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | tinyint(4) | YES  |     | NULL    |       |
| NAME   | char(4)    | YES  |     | NULL    |       |
| gender | char(4)    | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
mysql> alter table t1 add gender char(4) first;
mysql> alter table t1 add level int after ID;

5) 删除字段
alter table t1 drop gender;
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
| NAME  | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

5 表字段的数据类型

5.1 介绍

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但是宽度是可选的。

# 1.mysql常用数据类型概览：

#1. 数字：
    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：(如果用小数，则用推荐使用decimal) 
            精准
            内部原理是以字符串形式去存
#2. 字符串：
    char(10) ：简单粗暴，浪费空间，存取速度快
        root存成root000000
    varchar：精准，节省空间，存取速度慢

    sql优化：创建表时，定长的类型往前放，变长的往后放
                    比如性别           比如地址或描述信息

    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3. 时间类型：
    最常用：datetime
#4. 枚举类型与集合类型

5.2 数值类型

5.2.1 整数类型

整数类型： TINYINT SMALLINT MEDIUMINT INT BIGINT

	作用：存储年龄，等级，id，各种号码等。

	# 强调："整型的宽度是显示宽度(如tinyint(4) ) "，无需设置，存储宽度是固定死的。默认的显示宽度，都是在最大值的基础上加1。

# 常用类型详解
========================================
        tinyint[(m)] [unsigned] [zerofill]
            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
                0 ～ 255
            PS： MySQL中无布尔值，使用tinyint(1)构造。
========================================
        int[(m)][unsigned][zerofill]
            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
                    0 ～ 4294967295
========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
                    0  ～  18446744073709551615

#	代码验证：
# 1、=====================表字段类型之整型=======================
# =========有符号和无符号tinyint==========
# tinyint默认为有符号
mysql> insert t4 values(128);  
ERROR 1264 (22003): Out of range value for column 'id' at row 1  # 严格模式，超出范围会报错，若不是严格模式，则会把数字存成最近的极值。
mysql> insert t4 values(127);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+------+
| id   |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

"类型"	"大小"	"范围(有符号) "	"范围(无符号) "	"用途"
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 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-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 字节	(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	依赖于M和D的值	依赖于M和D的值	小数值

1)  int的存储宽度是4Bytes，即32个bit，即2"32；
2)  无符号最大值：4294967296-1；
3)  有符号最大值：2147483648-1；
4)  有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能完全显示，所以int类型默认的显示宽度为11是非常合理的。
5)  最后，整型类型，其实没有必要指定显示宽度，使用默认的就OK。

5.2.2 浮点型

定点数类型 DEC等同于DECIMAL

浮点类型：float double

作用：存储薪资、身高、体重、体质参数等

# 常用类型详解
======================================
float[(M,D) [unsigned] [zerofill]]
定义：
	单精度浮点数(非准确小数) ，m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。
有符号：
		-3.402823466E+38 to -1.175494351E-38,					1.175494351E-38 to 3.402823466E+38
无符号：
		1.175494351E-38 to 3.402823466E+38
精确度：
		"" 随着小数的增多，精度变得不准确 ""
======================================
double[(M,D) [unsigned] [zerofill]]
定义：
	双精度浮点数(非准确小数值) ，m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。
有符号：
		-1.7976931348623157E+308 to -2.2250738585072014E-308
		2.2250738585072014E-308 to 1.7976931348623157E+308
无符号：
		2.2250738585072014E-308 to 1.7976931348623157E+308
精确度：
		""随着小数的增多，精度比float要高，但也会变得不准确 ""
======================================
decimal[(M,D) [unsigned] [zerofill]]
定义：
		准确的小数值，m是数字总个数(符号不算) ，d是小数点后个数。m最大值为65，d最大值为30。
精确度：
		"" 随着小数的增多，精度始终准确 ""
        对于精确数值计算时需要用此类型
        decaimal能够存储精确值的原因在于其内部按照字符串存储。

# 2、=====================表字段类型之浮点类型=======================
mysql> create table t7(x float(255,30),y double(255,30),z decimal(65,30));
mysql> insert t7 values(1.11111111111111111111,1.11111111111111111111,1.11111111111111111111);
mysql> select * from t7;  # 显示的精度问题，decimal精度最高
+----------------------------------+----------------------------------+----------------------------------+
| x                                | y                                | z                                |
+----------------------------------+----------------------------------+----------------------------------+
| 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111110000000000 |
+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

5.3 日期类型

date time datetime timestamp year

	作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等等。

year(1901/2155) 

time 时:分:秒 ('-838:59:59'/'838:59:59') 

date 年:月:日 (1000-01-01/9999-12-31) 

datetime 年:月:日 时:分:秒 1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp 年:月:日 时:分:秒 1970-01-01 00:00:00/2037

# 3、=====================表字段类型之日期类型======================
# 使用函数now()
mysql> create table t8(y year,t time,d date,dt datetime,ts timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert t8 values(now(),now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t8;
+------+----------+------------+---------------------+---------------------+
| y    | t        | d          | dt                  | ts                  |
+------+----------+------------+---------------------+---------------------+
| 2020 | 11:09:30 | 2020-09-02 | 2020-09-02 11:09:30 | 2020-09-02 11:09:30 |
+------+----------+------------+---------------------+---------------------+

# 手动传时间参数
mysql> create table student(id int,name char(10),born_year year,birth date,reg_time datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> insert student values(1,"xxx","1911","1911-11-11","1911-11-11 11:11:11");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+-----------+------------+---------------------+
| id   | name | born_year | birth      | reg_time            |
+------+------+-----------+------------+---------------------+
|    1 | xxx  |      1911 | 1911-11-11 | 1911-11-11 11:11:11 |
+------+------+-----------+------------+---------------------+
1 row in set (0.00 sec)

============！！！注意！！！！！！！===========
1) 单独插入时间是，需要以字符串的显露出，按照对应的格式插入
2) 插入年份时，尽量使用4位值
3) 插入两位年份时，<=69，以20开头，比如50，结果2050      
				>=70，以19开头，比如71，结果1971

5.3.1 datetime与timestamp的区别

# 在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
1) DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2) datetime存储时间与失去无关，timestamp存储时间与失去有关，显示的值也依赖于时区。在MySQL服务器，操作系统以及客户端连接都有时区的设置。
3) datetime使用8字节存储时间，timestamp的搓出空间为4字节。因此，timestamp比datetime的空间利用率更高。
4) datetime的默认值为null，timestamp的字段默认不为空(not null) ，默认值为当前时间(current_timestamp) ，如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新至为当前时间。

# "！！！注意："针对datetime或者timestamp如果是用作注册时间，那么指定not null default now()自动填充时间即可，如果是用作更新时间那么需要额外指定on update now()，该配置timestamp自带。

# 测试1：基础性质
mysql> create table t9(reg_time datetime not null default now());
mysql> create table t10(reg_time timestamp);

mysql> insert t9 values();
mysql> insert t10 values();

mysql> select * from t9;
+---------------------+
| reg_time            |
+---------------------+
| 2020-09-02 11:28:40 |
+---------------------+

mysql> select * from t10;
+---------------------+
| reg_time            |
+---------------------+
| 2020-09-02 11:28:49 |
+---------------------+

mysql> desc t9;  # 用于记录注册时间，
+----------+----------+------+-----+-------------------+-------+
| Field    | Type     | Null | Key | Default           | Extra |
+----------+----------+------+-----+-------------------+-------+
| reg_time | datetime | NO   |     | CURRENT_TIMESTAMP |       |
+----------+----------+------+-----+-------------------+-------+

mysql> desc t10;  # 用于记录更新时间
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| reg_time | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+

# 测试2：
mysql> create table t9(
    ->     id int,
    ->     name varchar(16),
    ->     -- update_time datetime not null default now() on update now()  # --是注释的意思,
    ->     update_time timestamp,
    ->     reg_time datetime not null default now()
    -> );
mysql> insert into t9(id,name) values(1,"xxx");
# 测试效果
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | xxx  | 2020-09-02 11:37:21 | 2020-09-02 11:37:21 |
+------+------+---------------------+---------------------+

mysql> update t9 set name="XXXX" where id=1;
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | XXXX | 2020-09-02 11:39:13 | 2020-09-02 11:37:21 |
+------+------+---------------------+---------------------+

5.4 字符串类型

5.4.1 基本情况

# 注意：char和varchar括号内的参数指的都是字符的长度
1) char类型：定长，简单粗暴，浪费空间，存取速度快
	字符长度范围：0-255(一个波斯文是一个字符，是utf8编码的3个字节) 
	存储：
		存储char类型的值时，会往右填充空格来满足长度(底层逻辑，查询时查不到) 
		例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个存储字符。
	检索：
		在检索或者说查询时，查处的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式(SET sql_mode='PAD_CHAR_TO_FULL_LENGTH';)。
		
2) varchar类型：变长，精准，节省空间，存取速度慢
	字符长度范围：0-65535(如果大于21845会提示用其他类型。MySQL行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html) 
	存储
		varchar类型存储数据的真实内容，不会用空格填充，如果'ab  '，尾部空格也会被存起来；
		强调！！：varchar类型的会在真实数据前加1-2Bytes前缀，该前缀用来表示真实数据的Bytes字节数(1-2Bytes最大表示65535个数字，正好符合MySQL对roe的最大字节限制数，即已足够使用) ；
		如果真实数据<255Bytes则需要1Bytes的前缀(1Bytes=8bit 2"8最大表示的数字为255) ；
		如果真实数据>255Bytes则需要2Bytes的前缀(2Bytes=16bit 2"16最大表示的数字为65535) ；
	检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容；

"示例："以4字节为例，"对比char与varchar存储空间数量"。
"特殊情况："在接近数据存储满的时候，varchar反而更费空间。

Value	`CHAR(4)`	Storage Required	`VARCHAR(4)`	Storage Required
`''`	`' '`	4 bytes	`''`	1 byte
`'ab'`	`'ab '`	4 bytes	`'ab'`	3 bytes
`'abcd'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes
`'abcdefgh'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes

5.4.2 基本使用验证

# 函数了解
length: 查看字节数
char_length: 查看字符数

# 验证
create table t11(x char(5));
create table t12(x varchar(5));
# 实际数字后面加一个空格
insert t11 values("我擦嘞 ");  -- "我擦嘞  "
insert t12 values("我擦嘞 ");  -- "我擦嘞 "

t11=>字符个数 5  字节个数 11  # 实际的字符、字节数
t11=>字符个数 3   # char显示的字符
t12=>字符个数 4  字节个数 10  # varchar可以显示实际的字符、字节数(字节数上添加了一个位数) varchar认为末尾的空格不是数据的一部分。

# 查询当前的模式
show variables like "sql_mode"

set sql_mode="pad_char_to_full_length";  # 严格模式下可以看见实际的字符和字节个数，默认的模式下不是这样显示
select char_length(x) from t11;
select char_length(x) from t12;


select length(x) from t11;
select length(x) from t12;

# 查询时默认把末尾的空格都去掉 like模糊匹配，=匹配
select * from 表名 where 字段 like "r%"  # like模糊匹配，r开头的数据，后面字符数不限
select * from 表名 where 字段 like "r_"  # r开头的数据，后面只有一个字符。

5.4.3 总结

# InnoDB存储引擎：建议使用VARCHAR数据类型
	单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针) ，因此在本质上，使用固定长度的CHAR列不一定比使用可变长度的VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I\O是比较好的。

5.5 枚举类型与集合类型

字段的值只能"在给定范围中选择"，如单选框，多选框；

	"enum" 单选，只能在给定的范围内选一个值，如性别 sex 男male/女female

	"set"  多选，在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)

# 示例enum:
mysql> create table shirts(
    -> name varchar(40),
    -> size enum('x-small', 'small', 'medium', 'large', 'x-large')
    -> );
# 正常插入值
mysql> insert shirts(name,size) values('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
mysql> select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+
# 非正常插入值
mysql> insert shirts values("abc shirts","xxxxxx");
mysql> select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
| abc shirts  |        |
+-------------+--------+  # 如果是严格模式，则会报错，不允许传入值；

# 示例：set
mysql> create table user(
    -> name varchar(16),
    -> hobbies set("read","chou","drink","tang")
    -> );
# 正常传值
mysql> insert user values("xxx","tang,chou");
mysql> select * from user;
+------+-----------+
| name | hobbies   |
+------+-----------+
| xxx  | chou,tang |
+------+-----------+
# 非正常传值
mysql> insert user values("qqq","tangchou");
mysql> select * from user;
+------+-----------+
| name | hobbies   |
+------+-----------+
| xxx  | chou,tang |
| qqq  |           |
+------+-----------+  # 非正常传值，空

6 复制表

# 复制表结构+记录(key不会复制：主键，外键和索引) , 
# select语句查询的内容保存到表t2  全部复制将字段改为*
create table t2 select user,host,password from mysql.user;

# 只复制表结构，
# select语句查询的表结构保存到表t3  全部复制使用*
create table t3 select user,host,password from mysql.user where 1!=1;

PS：关于"虚拟表"：硬盘上不存在表，表存在于内存中，表是硬盘上数据的被select语句组织出来的。

7 删除表

drop table 表名;

二表操作之完整性约束

1 介绍

1.1 约束条件

#### 1 什么是约束条件
	约束条件与数据类型的宽度一样，都是可选参数。

#### 2 作用：
	用于保证数据的完整性和一致性。

1.2 主要分类及说明：

PRIMARY KEY(PK)     # 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY(FK)     # 标识该字段为该表的外键
NOT NULL    		# 标识该字段不能为空
UNIQUE KEY(UK)		# 标识该字段的值是唯一的
AUTO_INCREMENT    	# 标识该字段的值自动增长(整数类型，而且为主键) 
DEFAULT    			# 为该字段设置默认值

UNSIGNED    		# 无符号
ZEROFILL    		# 使用0填充

1) 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值；
2) 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
	sex enum('male','female') not null default 'male'
	age int unsigned NOT NULL default 20  # 必须为正值(无符号)  不允许为空  默认是20
3) 是否是key
主键  primary key
外键  foreign key
索引  (index,unique...)

2 not null与default

是否可空，null表示空，非字符串。"not null"——>"不可空"；"null"——>"可空"。

	default是默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

# 代码验证
==================not null====================
mysql> create table t1(x int not null);  # 设置字段id不为空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert t1 values();  # 不能插入空
"""ERROR 1364 (HY000): Field 'x' doesn't have a default value"""

==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t2(x int not null default 666);
mysql> insert t2 values();
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   |     | 666     |       |
+-------+---------+------+-----+---------+-------+
mysql> select * from t2;
+-----+
| x   |
+-----+
| 666 |
+-----+

3 unique

unique是唯一约束，可以"单列唯一"，也可以多个条件"联合唯一"。

3.1 单列唯一

mysql> create table t3(name varchar(10) unique);

mysql> insert t3 values("egon");
mysql> insert t3 values("DSB");

mysql> insert t2 values("DSB");
ERROR 1366 (HY000): Incorrect integer value: 'DSB' for column 'x' at row 1

3.2 联合唯一

mysql> create table server(
    -> id int,
    -> name varchar(10),
    -> ip varchar(15),
    -> port int,
    -> unique(ip,port),
    -> unique(name)
    -> );
mysql> desc server;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  | UNI | NULL    |       |
| ip    | varchar(15) | YES  | MUL | NULL    |       |
| port  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> insert server values (1,"web1","10.10.0.11",8080);
mysql> insert server values (2,"web2","10.10.0.11",8081);
mysql> insert server values(4,"web4","10.10.0.11",8081);
-- ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'
mysql> insert server values(3,"网页3","10.10.0.11",8082);

mysql> select * from server;
+------+---------+------------+------+
| id   | name    | ip         | port |
+------+---------+------------+------+
|    1 | web1    | 10.10.0.11 | 8080 |
|    2 | web2    | 10.10.0.11 | 8081 |
|    3 | 网页3   | 10.10.0.11 | 8082 |
+------+---------+------------+------+

4 primary key

4.1 主键是什么

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表。

4.2 特点：**

一张innodb表中必须有且只有一个主键，可以："单列做主键"，也可以"多列做主键(复合/联合主键) "。

	主键的约束效果是not null + unique。

4.3 代码验证

==================单列做主键==================
# 方法一：在某一个字段后用primary key
mysql> create table t6(
    -> id int primary key auto_increment, # 主键 # 不指定id，则自动增长
    -> name varchar(5)
    -> );
mysql> insert t6(name) values
    -> ("egon"),
    -> ("is"),
    -> ("DSB"),
    -> ("DDB");

mysql> desc t6;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(5) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

mysql> select * from t6;
+----+------+
| id | name |
+----+------+
|  1 | egon |
|  2 | is   |  # 尽量不要使用关键字作为表格的内容
|  3 | DSB  |
|  4 | DDB  |
+----+------+

# 方法二：not null+unique
mysql>  create table t6_2(
    -> id int not null unique,  # 主键
    -> name varchar(20) not null unique,
    -> comment varchar(100)
    -> );
mysql> desc t6_2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

# 方法三：在所有字段后单独定义primary key
mysql> create table t6_3(
    -> id int,
    -> name varchar(20),
    -> comment varchar(100),
    -> constraint pk_name primary key(id)  # 创建主键并为其命名pk_name
    -> );

mysql> desc t6_3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | 0       |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

==================多列做主键(了解) ==================
mysql> create table t7(
    -> id int,
    -> name varchar(5),
    -> primary key(id,name)
    -> );

mysql> desc t7;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | 0       |       |
| name  | varchar(5) | NO   | PRI |         |       |
+-------+------------+------+-----+---------+-------+

5 auto_increment

约束字段为自动增长，被约束的字段必须同时被key约束。

6 foreign key

6.1 什么是foreign key

foreign key是外键，用于关联多张表，对表进行解耦合，提高查询速度。

1) # 先创建被关联表
mysql> create table dep(
    -> id int primary key auto_increment,
    -> name varchar(6),
    -> comment varchar(60)
    -> );
    
2) # 再创建关联表
mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(10),
    -> gender varchar(5),
    -> dep_id int,
    -> foreign key(dep_id) references dep(id) on delete cascade on update cascade
    -> );
    
3) # 先往被关联表插入数据
mysql> insert dep(id,name) values
    -> (1,"技术部"),
    -> (2,"人力资源部"),
    -> (3,"销售部");
    
mysql> desc dep;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(6)  | YES  |     | NULL    |                |
| comment | varchar(60) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> select * from dep;
+----+-----------------+---------+
| id | name            | comment |
+----+-----------------+---------+
|  1 | 技术部          | NULL    |
|  2 | 人力资源部      | NULL    |
|  3 | 销售部          | NULL    |
+----+-----------------+---------+

4) # 再往关联表插入数据
mysql> insert emp(name,gender,dep_id) values
    -> ('egon',"male",1),
    -> ('alex1',"male",2),
    -> ('alex2',"male",2),
    -> ('alex3',"male",2),
    -> ('李坦克',"male",3),
    -> ('刘飞机',"male",3),
    -> ('张火箭',"male",3),
    -> ('林子弹',"male",3),
    -> ('加特林',"male",3);

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | YES  |     | NULL    |                |
| gender | varchar(5)  | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

mysql> select * from emp;
+----+-----------+--------+--------+
| id | name      | gender | dep_id |
+----+-----------+--------+--------+
|  1 | egon      | male   |      1 |
|  2 | alex1     | male   |      2 |
|  3 | alex2     | male   |      2 |
|  4 | alex3     | male   |      2 |
|  5 | 李坦克    | male   |      3 |
|  6 | 刘飞机    | male   |      3 |
|  7 | 张火箭    | male   |      3 |
|  8 | 林子弹    | male   |      3 |
|  9 | 加特林    | male   |      3 |
+----+-----------+--------+--------+

6.2 如何找出两张表之间的关系

# 分析步骤
1) 先站在左表的角度去找
	是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id) 。
    
2) 再站在右表的角度去找
	是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id) 。
    
3) 总结：
# 多对一
	如果只有步骤1成立，则是左表多对一右表；
    如果只是步骤2成立，则是右表多对一左表。
# 多对多
	如果步骤1和2同时成立，则证明这两张表同时一个双向的多对一，即多对多，需要定义一个这两张表的关系的关系表来专门存放二者的关系。
# 一对一
	如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key 右表的基础上，将左表的外键字段设置成unique即可。

6.3 建立表之间的关系

6.3.1 一对多或称为多对一

三张表：出版社，作者，书
一对多(或多对一) ：一个出版社可以出版多本书
关联方式： foreign key

=====================多对一=====================
mysql> create table press(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );

mysql> create table book(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> press_id int not null,
    -> foreign key(press_id) references press(id) on delete cascade on update cascade
    -> );

mysql> insert press(name) values
    -> ('丑国の音乐不好听出版社'),
    -> ('忒浪噗是个DSB出版社'),
    -> ('啊丑离家出版社');

mysql> insert book(name,press_id) values
    -> ('九阳神功',1),
    -> ('九阴真经',2),
    -> ('九阴白骨爪',2),
    -> ('独孤九剑',3),
    -> ('降龙十巴掌',2),
    -> ('葵花宝典',3);

mysql> select * from press;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | 丑国の音乐不好听出版社           |
|  2 | 忒浪噗是个DSB出版社              |
|  3 | 啊丑离家出版社             |
+----+--------------------------------+

mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+

6.3.2 多对多

三张表：出版社，作者信息，数
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式： foreign key + 一张新的表

=====================多对多=====================
mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );

# 这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
mysql> create table author2book(
    -> id int not null unique auto_increment,
    -> author_id int not null,
    -> book_id int not null,
    -> constraint fk_auther foreign key(author_id) references author(id) on delete cascade on update cascade,
    -> constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade,
    -> primary key(auther_id,book_id)
    -> );

# 插入四个作者，id依次排开
mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

# 每个作者与自己的代表作如下:
1 egon: 
      1 九阳神功；2 九阴真经；3 九阴白骨爪；4 独孤九剑；5 降龙十巴掌；6 葵花宝典；
2 alex: 
      1 九阳神功；6 葵花宝典；
3 yuanhao:
      4 独孤九剑；5 降龙十巴掌；6 葵花宝典；
4 wpq:
      1 九阳神功；

mysql> insert into author2book(author_id,book_id) values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (1,4),
    -> (1,5),
    -> (1,6),
    -> (2,1),
    -> (2,6),
    -> (3,4),
    -> (3,5),
    -> (3,6),
    -> (4,1);
    
mysql> desc author2book;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | UNI | NULL    | auto_increment |
| author_id | int(11) | NO   | PRI | NULL    |                |
| book_id   | int(11) | NO   | PRI | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         1 |       4 |
|  5 |         1 |       5 |
|  6 |         1 |       6 |
|  7 |         2 |       1 |
|  8 |         2 |       6 |
|  9 |         3 |       4 |
| 10 |         3 |       5 |
| 11 |         3 |       6 |
| 12 |         4 |       1 |
+----+-----------+---------+

6.3.3 一对一

两张表：学生表和客户表
一对一：一个学上是一个客户，一个客户有可能变成一个学生，即一对一的关系；
关联方式： foreign key + unique

# 一定是student来foreign key表customer，这样就保证了：
# 1 学生一定是一个客户，
# 2 客户不一定是学生，但有可能成为一个学生

mysql> create table customer(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> qq varchar(10) not null,
    -> phone char(16) not null
    -> );

mysql> create table student(
    -> id int primary key auto_increment,
    -> class_name varchar(20) not null,
    -> customer_id int unique,  # 该字段一定要是唯一的
    -> foreign key(customer_id) references customer(id) on delete cascade on update cascade  # 外键的字段一定要保证unique
    -> );
# 增加客户
mysql> insert customer(name,qq,phone) values
    -> ("x001",'100001','123123123'),
    -> ("x002",'100002','123123124'),
    -> ("x003",'100003','123123125');

# 增加学生
mysql> insert student(class_name,customer_id) values
    -> ("linux",3),
    -> ("go",1);
    
mysql> select * from customer;
+----+------+--------+-----------+
| id | name | qq     | phone     |
+----+------+--------+-----------+
|  1 | x001 | 100001 | 123123123 |
|  2 | x002 | 100002 | 123123124 |
|  3 | x003 | 100003 | 123123125 |
+----+------+--------+-----------+
mysql> select * from student;
+----+------------+-------------+
| id | class_name | customer_id |
+----+------------+-------------+
|  4 | linux      |           3 |
|  5 | go         |           1 |
+----+------------+-------------+

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：mysql 怎么验证插入性能如何测试mysql

下一篇：spark 循环使用dataframe 导致内存溢出 sparksql for循环

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql 字段切割并返回第2个 mysql切表

mysql 字段切割并返回第2个 mysql切表

MySQL表相关操作

一 表相关操作基础

1 表介绍

2 创建表

2.1 语法及注意事项

2.2 代码示例：

3 查看表结构

4 修改表

4.1 修改表名

4.2 修改字段

4.3 增加字段

4.4 删除字段

4.5 代码示例

5 表字段的数据类型

5.1 介绍

5.2 数值类型

5.2.1 整数类型

5.2.2 浮点型

5.3 日期类型

5.3.1 datetime与timestamp的区别

5.4 字符串类型

5.4.1 基本情况

5.4.2 基本使用验证

5.4.3 总结

5.5 枚举类型与集合类型

6 复制表

7 删除表

二 表操作之完整性约束

1 介绍

1.1 约束条件

1.2 主要分类及说明：

2 not null与default

3 unique

3.1 单列唯一

3.2 联合唯一

4 primary key

4.1 主键是什么

4.2 特点：**

4.3 代码验证

5 auto_increment

6 foreign key

6.1 什么是foreign key

6.2 如何找出两张表之间的关系

6.3 建立表之间的关系

6.3.1 一对多或称为多对一

6.3.2 多对多

6.3.3 一对一

51CTO博客

一表相关操作基础

二表操作之完整性约束