MySQL学习笔记(3)——操作数据库中的表

MySQL学习笔记(3)——操作数据库中的表

参考视频：https://www.bilibili.com/video/BV1NJ411J79W?p=6

1.创建数据表

1.1 SQL语句建表格式

SQL语句建表/可视化建表

创建demo表，有id和name两个字段.

id字段 为int类型长度为8 id` int(8)，无符号数(unsigned)，0填充(zerofill)，非空(NOT NULL)，

自增(AUTO_INCREMENT)，备注(COMMENT)为id编号

name字段为varchar长度为30 name` varchar(30)，非空（NOT NULL），

默认值为张三(DEFAULT ‘张三’)，备注(COMMENT)为姓名

主键设置为id，存储引擎为InnoDB， 数据库默认编码为utf-8

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'id编号',
  `name` varchar(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名称` 字段类型 [字段属性] [索引] [备注/注释],
    `字段名称` 字段类型 [字段属性] [索引] [备注/注释],
    ...
    `字段名称` 字段类型 [字段属性] [索引] [备注/注释]
)[表类型:存储引擎][字符集设置][备注/注释]

注意：

[ ]中的内容代表可选, 字段用 ` 括住(tab键上面那个)，不是单引号

最后一个字段定义完不用加逗号，

tips：查看创建数据库/数据表的语句

SHOW CREATE DATABASE blog;  -- 查看创建数据库语句
SHOW CREATE TABLE blog;  -- 查看创建数据表的语句
DSC blog;  -- 显示表结构

1.2.数据表的类型(存储引擎)

就是以上的ENGINE=InnoDB,建表中通过 ENGINE 设置。

一般就是InnoDB和MYISAM。早年使用MYISAM

	MYISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持，5.6.4版本后支持(全英文)
表空间大小	较小	较大(约为MYISAM两倍)

了解即可

• MYISAM` 节约空间，速度较快
• InnoDB安全性高，支持事务处理，多表多用户操作

在物理空间存在的位置：

所有数据库文件都存下data下，本质还是文件存储!

MySQL存储引擎在物理文件上的区别(5.7版本)

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibddata1文件

• MYISAM对应的文件：

  • *.frm表结构的定义文件；

  • *.MYD 数据文件(data)

  • *.MYI 索引文件(index)

1.3 数据表的字符集编码

DEFAULT CHARSET=utf8;

不设置的话，默认的字符集编码(Lanti1)不支持中文，会导致中文乱码问题。

修改编码格式的方法：

• 建表语句里设置CHARSET=utf8（建议用这种，通用性更高）
• 配置文件my.ini中配置默认的编码格式character-set-server=utf-8

2. 修改与删除表

2.1 修改表及表中字段

• **关键字：ALTER **

修改表名：RENAME AS

ALTER TABLE 旧表名 RENAME AS 新表名;

 --把demo数据表改名为demo1
ALTER TABLE demo RENAME AS demo1;

增加字段：ADD

ALTER TABLE 表名 ADD 字段名 字段属性;

 -- 在demo1数据表中添加一个字段，名称为sge，类型INT，长度2
ALTER TABLE demo1 ADD age INT(2);

修改字段：MODIFY修改字段类型和字段约束；CHANGE重命名字段

ALTER TABLE 表名 MODIFY 字段名 字段属性;

ALTER TABLE 表名 CHANGE 旧字段名 新字段名;

 -- 修改字段名称或者约束
 
 --修改demo1表中age字段的约束，改为VARCHAR类型长度2，默认值设为18
ALTER TABLE demo1 MODIFY age VARCHAR(2) DEFAULT '18'; 

-- 字段重命名，将demo1表中的age重命名为age1
ALTER TABLE demo1 CHANGE age age1; 

2.2 删除表及表中字段

删除表：DROP

DROP TABLE IF EXISTS 表名;

DROP TABLE IF EXISTS demo1; -- 删除数据表demo1

删除表的字段：DROP

ALTER TABLE 表名 DROP 字段名;

-- 删除demo1表中的age1字段
ALTER TABLE demo1 DROP age1;

所有的创建和删除操作尽量加上存在判断，防止报错，即IF EXISTS