MySQL02_基本操作

1 操作数据库

操作数据库就是操作数据库中的表，即操作数据库中表的数据

MySQL中关键字不区分大小写

• 创建数据库

  -- 方括号内为可选项，表示如果没有此数据库时才创建
  create datebase [if not exists] `myDB`;
  

• 删除数据库

  -- 方括号内为可选项，表示存在此数据库时才删除
  drop database [if exists] `myDB`;
  

• 使用数据库

  -- 如果表名或字段名是一个特殊字符，需要用``包围
  use `user`;
  

• 查看数据库

  show databases;
  

2 数据库的列类型

• 数值

  类型	描述	大小
  tinyint	十分小的整数	1个字节
  smallint	较小的整数	2个字节
  mediumint	中等大小的整数	3个字节
  int	标准的整数	4个字节
  bigint	较大的整数	8个字节
  float	单精度浮点数	4个字节
  double	双精度浮点数	8个字节
  decimal	字符串形式的浮点数，不存在精度损失	16个字节

• 字符串

  类型	描述	范围
  char	固定大小字符串	0~255
  varchar	可变字符串	0~65535
  tinytext	微型文本	2^8-1
  text	文本串	2^16-1

• 时间日期

  类型	描述
  date	YYYY-MM-DD，日期格式
  time	HH:mm:ss，时间格式
  datetime	YY-MM-DD HH:mm:ss，最常用的时间格式
  timestamp	时间戳，从1970.1.1到现在的毫秒数
  year	年份表示

• null类型，表示空值或未知值，不要使用null进行运算

3 数据库的字段属性

• unsigned：只针对于整型，表示该列不能声明为负数
• zerofill：不足的位数使用0来填充，例如：int (3)，5→005
• auto_increment：自增，自动在上一条记录的基础上+1（默认）；通常用于设计唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长
• null和not null：设置为not null，如果不赋值会报错；设置为null，如果不赋值默认为null
• default：默认值，如果没有为字段赋值，系统会自动为这个字段插入默认值
• 拓展：每个表都必须存在以下5个字段，表示一个记录存在的意义
  • id：主键
  • version：乐观锁
  • is_delete：伪删除
  • gmt_create：创建时间
  • gmt_update：修改时间

4 创建数据库表

创建一个学生表，包括学号、姓名、密码、性别等字段

-- 创建表使用create语句，使用英文括号包围各种字段
-- 表的名称和字段尽量用``包围起来，以免产生冲突
-- 字符串使用单引号包围
-- 所有的语句后加英文逗号，最后一行不加
-- primary key表示主键，一个表通常只设置一个主键
-- 最后指定引擎和字符集
create table if not exists `student`
(
    `id`       int(4)      not null auto_increment comment '学号',
    `name`     varchar(3)  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 = utf8;

通用格式为：

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

通过show create database myDB;和show create table student;可以查看创建数据库和创建表的定义语句

5 MyISAM和InnoDB区别

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持（表级锁）	支持（行级锁）
外键约束	不支持	支持
全文索引	支持	不支持（5.6后支持）
表空间大小	较小	较大

在常规使用中：

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

MySQL中所有数据存在data目录下，两种引擎在物理文件中存在差异：

• InnoDB在数据库中只有一个*.frm文件（8.0版本后有所不同），以及上级目录中的ibdata1文件
• MyISAM中有：
  • *.frm：表结构的定义文件
  • *.MYD：数据文件（data）
  • *.MYI：索引文件（index）

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

• MySQL默认编码是Latin1，不支持中文，可以在创建表时添加charset=utf8改变编码方式

• 此外还可以在my.ini中配置默认编码方式：

  character-set-server=utf8
  

7 修改表字段

-- 修改表名
alter table `student` rename as `student1`;
-- 增加表的字段
alter table `student1`
    add `age` int(3);
-- 修改表字段约束，但不能修改字段名
alter table `student1`
    modify `age` varchar(3) not null comment '年龄';
-- 重命名表字段，也可以修改约束
alter table `student1`
    change `age` `age1` int(3);
-- 删除表字段
alter table `student1`
    drop `age1`;