Mysql之表的操作

聆听逝去的流
156 阅读5分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第2天，点击查看活动详情

今天讲解的是Mysql中对数据库的基本操作、数据库的几种类型、数据库引擎和对表的操作

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

mysql的关键字不区分大小写

操作数据库

1、创建数据库

中括号表示可选项

create database [if not exists] westos;

2、删除数据库

drop database [if exists] westos;

3、使用数据库

use 'school';

4、查看所有数据库

show databases;

数据库的数据类型

1.数值

  • tinyint 1个字节
  • smallint 2个字节
  • mediumint 3个字节
  • int 4个字节 常用
  • bigint 8个字节
  • float 浮点数 4个字节
  • double 浮点数 9个字节
  • decimal 字符串形式的浮点数（金融计算）

2.字符串

  • char 字符串 0-255 只能存储固定长度的字符串
  • varchar 可变字符串 0-65535 常用 可以存储一个最大不超过限定范围长度的字符串
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^16 - 1 保存大文本

3.时间日期

  • date YYYY-MM-DD，日期格式
  • time HH: MM: SS， 时间格式
  • datetime YYYY-MM-DD HH: mm: ss 常用
  • timestamp 时间戳， 1970.1.1到现在的毫秒数 常用
  • year 年份表示

4.null

  • 没有值，未知
  • 不能使用null进行计算

数据库的字段属性

1、Unsigned

  • 无符号的整数
  • 不能声明为负数

2、zerofill

  • 0填充
  • 不足的位数，使用0填充

3、自增 (auto_increment)

  • 自动在上一条记录的基础上+1（默认）
  • 通常用来设计唯一的主键 index，必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

4、非空 (null 和 not null)

  • 设置为not null时，如果不给它赋值就会报错
  • 默认为null

5、默认

  • 设置默认的值
  • 如果不指定值，则该列的值为默认值

拓展

每个表都必须存在以下五个字段，表示一个记录存在的意义

  • id 主键
  • 'version' 乐观锁
  • is_delete 伪删除
  • gmt_create 创建时间
  • gmt_update 修改时间

数据库引擎

  • innodb 默认使用
  • myisam 早年使用
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大，约myisam的2倍

常规使用操作：

  • myisam

    • 节约空间，速度较快
    • 用于管理非事务表，提供高速检索和全文检索能力，适用于大量查询操作的表

  • innodb

    • 安全性高，支持事务的处理，支持 多表多用户操作
    • 应用于执行大量插入和修改操作的表
    • 通过间隙锁防止幻读的出现（后面会介绍幻读）

在物理空间中的位置

所有的数据库文件都存在 data 目录下，一个文件夹对应一个数据库，所以其本质还是文件的存储

MySQL 引擎在物理文件上的区别如下：

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

  • myisam对应文件

    • .frm 表结构的定义文件
    • .MYD 数据文件（data）
    • .MYI 索引文件（index）

设置数据库表的字符集编码

charset=utf8

不设置时，MySQL 的默认编码是 Latin1 ，不支持中文

可以在 my.ini 文件中配置默认的编码(不建议)，添加如下语句

character-set-server=utf8

接下来讲解的是数据库定义语言（DDL）

创建数据库表

primary key 是主键，主键是用来唯一标识该表的，一般一个表只有一个唯一的主键（主码）。用法：

`id` int(4) primary key,
--还可以有多个字段共同构成一个主键
primary key(`id`, `name`),

创建一个表的格式

字段名就是属性名，就是列名。中括号的内容为可选项

create table [if not exists] `表名` (
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置][设置];

创建一个学生表

create table if not exists `student` (
    `id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null default '123456' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '邮箱',
    primary key(`id`);
) engine=innodb default charset=utf-8; --设置引擎和默认字符集

表的名称和字段尽量使用反单引号括起来

字符串需要使用单引号括起来

所有的语句后面加逗号（英文），最后一个语句不加逗号

comment：后面是注释

default：用来添加默认值

常用命令

show create database school --查看创建数据库的语句
show create table student --查看student数据表的定义语句
desc student --显示表的结构

修改表

1、修改表名

--alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1;

2、增加表的字段

--alter table 表名 add 字段名 列类型
alter table teacher1 add age int(11);

3、修改表的字段 (重命名，修改约束)

--alter table 表名 modify 字段名 列属性
alter table teacher1 modify age varchar(11); --修改类型和约束
​
--alter table 表名 change 旧字段名  新字段名  数据类型
alter table teacher1 change age age1 int(1); --字段重命名

删除表

1、删除表的字段

--alter table 表名 drop 字段名
alter table teacher1 drop age1;

2、删除表

如果选择 restrict ，则该表必须没有被引用对象（视图，触发器，存储过程），默认

如果选择 cascade ，则会将引用对象一起删除

drop table if exists teacher1 [restrict/cascade];

3、删除引用对象

如果该列被其他对象引用，则将引用对象一起删除

alter table teacher1 drop column `tname` cascade;

4、不删除引用对象

如果该列被其他对象引用（如视图），则将拒绝删除该列

alter table teacher1 drop column `tname` restrict;

所有的创建和删除操作尽量加上判空判断，以免报错

avatar
文章
阅读
粉丝