MySQL-表相关

目录

1. 存储引擎
2. 严格模式
3. 表的语法
4. 增删查改
5. 表关联
6. 约束条件

1、存储引擎

存储引擎就是针对不同的数据的有不同的处理机制
查看所有引擎：show engines;
4种存储引擎：
	innodb：5.5版本之后的默认存储引擎，数据更加安全，速度较慢。生产2个文件：结构、数据
	myisam：5.5版本之前的默认存储引擎，数据较不安全，速度较快。生产3个文件：结构、数据、索引
	memory：内存存储引擎，不往硬盘上写，只存在内存，断电丢失，速度很快。生产1个文件：结构
	blackhole：黑洞存储引擎，存入及消失。生产1个文件：结构

2、严格模式

严格模式：数据存储异常就会报错

1、模糊查找：查看以mode结尾的信息，里面的sql_mode可以配置严格模式
show variables like '%mode';

2、配置严格模式
方法1：
set session sql_mode = 'STRICT_TRANS_TABLES'
方法2：
set global sql_mode = 'STRICT_TRANS_TABLES'

模式：
显示填充字符串：PAD_CHAR_TO_FULL_LENGTH
严格模式：STRICT_TRANS_TABLES

3、表的语法

3.1 创建语法

语法：
create table 表名(
				字段名1 字段类型(宽度) 约束条件,
				字段名2 字段类型(宽度) 约束条件,
				字段名3 字段类型(宽度) 约束条件
				)
"""
宽度：一般情况下存储数据的限制
约束条件：额外的的限制，可以有多个

1、在同一张表内字段名不能重复
2、字段名和字段类型必须参数，宽度和约束条件是可选参数
3、最后一个字段后不能加逗号
"""

3.2 修改语法

语法：
1. 修改表名
      ALTER TABLE 表名 RENAME 新表名;
      alter table t1 rename tt1;

2. 增加字段
      # 最后面插入
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…];
      alter table t1 add name char(16) not null ...
      # 最前面插入
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      alter table t1 add name char(16) not null ... first
      # 某个字段后插入
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
      alter table t1 add name char(16) not null ... after id

3. 删除字段
      ALTER TABLE 表名 DROP 字段名;
      alter table t1 drop id

4. 修改字段
	  # modify一般修改字段类型
      ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
      alter table t1 modify id char(16) not null ...
      # change 修改字段名
      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      alter table t1 change id ip int unsigned ...
      # change 修改字段类型
      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
      alter table t1 change id ip char not null ...

5、复制表
	sql语句查询出的结果也可以看成一张表（虚拟表）
	言外之意 就是针对这个查询功能还可以继续用查询表的语法继续操作该虚拟表
	# 把查询的表复制到新表里
	create table new_t1 select * from t1;
	

4、增删查改

1、 操作文件夹（库）
        增：	create database db1 charset utf8;				# charset utf8 默认是utf-8
        查：	show databases;									# 查看所有库
        	show create database db1 						# 查看指定库
        改：alter database db1 charset gbk;					# 修改表
        删除: drop database db1;								# 删除表

2、 操作文件（表）
		查看当前文件位置：select database();					# 查看当前位置
    	切换到文件夹下：use db1								# 切换目录
        增：	create table t1(id int,name char);				# 创建一个表，括号里为表头
        查：	show tables	;									# 查看所有表
        	show create table t1;							# 查看指定表
        	desc t1;										# 美观查看指定表
        改：	alter table t1 modify name char(3);				# 修改表的字段
           	alter table t1 change name name1 char(2);		# 重命名表头
        删：	drop table t1;									# 删除表


3、 操作文件中的内容（记录）
        增：	insert into t1 values(1,'1'),(2,'2'),(3,'3');	# 增加记录多个之间用逗号（,）隔开，into可以不写
        查：	select * from t1;								# 查看所有记录
        	select name from t1;							# 查看单个表头下记录
        改：	update t1 set name='6' where id=2;				# 根据条件修改记录
        删：	delete from t1 where id=1;						# 根据条件删除记录
        	delete from t1 									# 删除所有记录
        	truncate table t1;								# 数据量大，删除速度比上一条快，且直接从零开始，

5、约束条件

5.1 分类

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

"""
PRIMARY KEY (PK)    	标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    	标识该字段为该表的外键
NOT NULL    			标识该字段不能为空
UNIQUE KEY (UK)    		标识该字段的值是唯一的
AUTO_INCREMENT    		标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    				为该字段设置默认值
UNSIGNED 				无符号
ZEROFILL 				使用0填充
"""

5.2 功能

1、 NULL、NOT NULL、DEFAULT
"""
作用：用来限制数据能否为空，默认为 null
null：表示空，设置则字段允许为空
not null：表示非空，设置则字段不允许为空
default：默认值，如果插入记录时不给字段赋值，此字段使用默认值null,默认值可配置。
"""
1.1 null
mysql> create table t1(id int);								# 约束条件为默认null 
mysql> insert into t1 values(null);							# 成功，可以插入空，字段允许为空
Query OK, 1 row affected (0.14 sec)

1.2 not null
mysql> create table t1(id int not null);					# 约束条件为not null
mysql> insert into t1 values(null);							# 报错，不可以插入空，字段不允许为空
ERROR 1048 (23000): Column 'id' cannot be null

1.3 default
mysql> create table t1(id int);								# 不指定默认值，默认值为null
mysql> insert into t1 values();								# 插入空，使用默认值null
mysql> select * from t1;									# 查看所有记录
+------+
| id   |
+------+
| NULL |
+------+

mysql> create table t1(id int default 1);					# 指定默认值1
mysql> insert into t1 values();								# 插入空，使用设置的默认值1
mysql> select * from t1;									# 查看所有记录
+------+
| id   |
+------+
|    1 |
+------+

2、UNIQUE KEY (UK)
"""
作用：用在限制数据是否唯一,默认为开启
unique:开启设置输入唯一，重复报错
"""
2.1 单列唯一
mysql> create table t1(id int);								# 默认为未设置unique
mysql> insert into t1 values(1),(1);						# 可以插入相同数据
Query OK, 2 rows affected (0.14 sec)

mysql> create table t1(id int unique);						# 设置unique
mysql> insert into t1 values(1),(1);						# 插入相同数据报错
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

2.2 联合唯一
mysql> create table t1(										# 创建表
					id int,
					ip char(16),
					port int,
					unique(ip,port)							# 联合唯一，约束条件在最下面写
					);
mysql> insert into t1 values(								# 插入数据
	->	1,'192.168.1.2',80),								
    -> (2,'192.168.1.2',81),								# 相同ip可以插入
    -> (3,'192.168.1.3',80);								# 相同port可以插入
mysql> insert into t1 values(1,'192.168.1.2',80);			# 相同ip+相同port的时候报错
ERROR 1062 (23000): Duplicate entry '192.168.1.2-80' for key 'ip'

3、PRIMARY KEY (PK)
"""
作用：主键，主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
primary key:字段的值不为空且唯一
没设置主键的情况下，not null + unique 晋升为主键
"""
3.1 单个主键
mysql> create table t1(id int not null unique);				# not null + unique 自动晋升主键
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |			# pri 主键
+-------+---------+------+-----+---------+-------+

mysql> create table t1(id int primary key);					# 设置primary key设置主键
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |			# pri主键
+-------+---------+------+-----+---------+-------+

3.2 联合多键
mysql> create table t1 (
					id int,
					ip char(16),
					port int,
					primary key(ip,port)
					);
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| ip    | char(16) | NO   | PRI |         |       |			# 主键
| port  | int(11)  | NO   | PRI | 0       |       |			# 主键
+-------+----------+------+-----+---------+-------+

4、AUTO_INCREMENT
"""
作用：自动增长，被约束的字段必须是key
auto_increment：自动增长
"""
mysql> create table t1(										# 创建表
					id int primary key auto_increment,
					ip char(16) default '192.168.111.111',
					port int);
mysql> insert into t1(port) values(80),(81),(82);			# 表后面指定字段，按指定字段插入
mysql> select * from t1;									# 查看所有记录
+----+-----------------+------+
| id | ip              | port |
+----+-----------------+------+
|  1 | 192.168.111.111 |   80 |								# id自动增长 
|  2 | 192.168.111.111 |   81 |
|  3 | 192.168.111.111 |   82 |
+----+-----------------+------+

5、UNSIGNED
"""
作用：用于限制是否显示符号
unsigned: 默认显示符号，设置后不显示符号
"""
mysql> create table t1(id int);								# 默认显示符号
mysql> insert into t1 values(-1),(1);
mysql> select * from t1;
+------+
| id   |
+------+
|   -1 |													# 显示符号
|    1 |
+------+

mysql> create table t1(id int unsigned);					# 设置后不显示符号
mysql> insert into t1 values(-1),(1);
mysql> select * from t1;
+------+
| id   |
+------+
|    0 |													# 填充不显示符号的最小值
|    1 |
+------+

6、ZEROFILL
"""
作用：用于设置填充，填充为0
zerofill：默认用空字符填充，设置后用0填充
"""
mysql> create table t1(id int zerofill);					# 设置填充为0
mysql> insert into t1 values(1);
mysql> select * from t1;
+------------+
| id         |
+------------+
| 0000000001 |												# 用0填充
+------------+

7、FOREIGN KEY (FK)
"""
作用：外键，用于建立表与表的关系
foreign key：表与表进行关联
"""
mysql> create table t1(										# 先创建被关联表
					id int primary key auto_increment,
					ip char(16) default '192.168.111.111',
					port int);
mysql> create table t2(										# 在创建关联表
					id int primary key auto_increment,
					t1_id int,
					foreign key(t1_id) references t1(id) 	# 关联表
					on delete cascade 						# 同步删除
					on update cascade);						# 同步更新
mysql> insert into t1(port) values(80),(81),(82);			# 数据先插入被关联表
mysql> insert into t2(t1_id) values(1),(2),(3);				# 再插入关联表
mysql> select * from t1;
+----+-----------------+------+
| id | ip              | port |
+----+-----------------+------+
|  1 | 192.168.111.111 |   80 |
|  2 | 192.168.111.111 |   81 |
|  3 | 192.168.111.111 |   82 |
+----+-----------------+------+
mysql> select * from t2;
+----+-------+
| id | t1_id |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
mysql> update t1 set id=222 where id=2;						# 更新被关联表，关联表的数据同步更新
mysql> select * from t1;
+-----+-----------------+------+
| id  | ip              | port |
+-----+-----------------+------+
|   1 | 192.168.111.111 |   80 |
|   3 | 192.168.111.111 |   82 |
| 222 | 192.168.111.111 |   81 |							# 修改id=2 为id=222
+-----+-----------------+------+
mysql> select * from t2;
+----+-------+
| id | t1_id |
+----+-------+
|  1 |     1 |
|  3 |     3 |
|  2 |   222 |												# 同步更改
+----+-------+

ps：同步删除同理

6、表关联

#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

6.1 一对多

1、一对多表关系，外键字段设置在多的一方
2、创建表时，先创建被关联表
3、录入数据时，先录入被关联表

例子：
图书和出版社
	先站在图书表
		一本书能否被多个出版社出版（版权）  不能
	再站在出版社表
		一个出版社能否出版多本书           能
结论：单项的一对多成立，表示表关系为一对多，图书表是多的一方，外键设置在图书表，出版社为被关联表
create table publish(								# 先建立关联表
				id ...);
create table book(									# 再创建被关联表
				id ...
				publish_id int,						# 设置外键
				foreign key(publish_id) references publish(id)
				on update cascade 
				on delete cascade);

6.2 多对多

1、必须单独开设一张表，用于存放两张表数据之间的关系，外键设置在这张表上
2、必须先创建关系表，再创建两张表
3、必须先录入两张表，再录入关系表

例子：
图书与作者
	先站在图书表
		一本书能否有多个作者		能
	再站在作者表
		一个作者能否写多本书		能
结论：图书和作者是双向的一对多，那么表示表关系为多对多，一对要单独开设一张表存放表关系，外键设置在这张表上
create table book(								# 先创建图书表和作者表
				id ...);
create table author(
				id ...);
create table relationship(						# 再创建关系表
				id ...,
				book_id int,
				author_id int,					# 设置外键
				foreign key(book_id) references book(id)
				on update cascade 
				on delete cascade,
				foreign key(author_id) references author(id)
				on update cascade 
				on delete cascade);

6.3 一对一

1、外键可以设置在任意一张表上，通常设置在查询频率高的那张表上
2、外键字段必须唯一

例子：
作者与作者详情
	无论站在哪一方都不能成立一对多
结论：双方都不能成立一对多，那么表示表关系为一对一，外键可以设置在任意表上,外键字段必须唯一
create table author(								# 外键通常设置在查询频率高的表上
				id ...,
				authordetail_id int unique,			# 外键字段必须唯一
				foreign key(authordetail_id) references authordetail(id)
				on update cascade 
				on delete cascade);
create table authordetail(
				id ...);