一.创建数据库

1. 直接创建数据库（默认字符集和排序规则）

1
create database 数据库名;

2. 判断数据库是否存在，不存在则创建

1
create database if not exists 数据库名;

3. 指定字符集创建数据库

1
create database 数据库名 character set 字符集;

4. 指定字符集和排序规则创建数据库

1
create database 数据库名 character set 字符集 collate 排序规则;

字符集种类

  • utf8mb3（支持中文，过时）
  • utf8mb4（支持emoji表情，mysql8.0默认字符集）

排序规则种类

  • utf8mb3_general_ci（支持中文，过时）
  • utf8mb4_general_ci（支持emoji表情，大小写敏感排序）
  • utf8mb4_ai_ci（支持emoji表情，unicode排序,大小写和重音敏感排序,mysql8.0默认排序规则）
  • utf8mb4_as_ci（支持emoji表情，unicode排序，大小写和重音敏感排序）

注意:创建数据库时，字符集和排序规则必须一致(utf8mb3对utf8mb3_general_ci，utf8mb4对utf8mb4_general_ci)

查看数据库字符集和排序规则

1
2
show variables like 'character_set_database';
show variables like 'collation_database';

二.库的查看与切换

1. 查看当前所有库

1
show databases;

2. 查看当前使用库

1
select database();

3. 查看指定库下的所有表

1
show tables from 数据库名;

4. 查看创建库的信息

1
show create database 数据库名;

5. 切换到指定库

1
use 数据库名;

注意:切换库后，当前会话库会改变，后续操作会默认在切换后的库下进行。所以要操作指定库下的表和数据前，需要先切换到该库。

三.库的删除与修改

1. 删除数据库

1
drop database 数据库名;

2. 判断数据库是否存在，不存在则删除

1
drop database if exists 数据库名;

3. 修改数据库字符集

1
alter database 数据库名 character set 字符集;

4. 修改数据库排序规则

1
alter database 数据库名 collate 排序规则;

5. 同时修改数据库字符集和排序规则

1
alter database 数据库名 character set 字符集 collate 排序规则;

四.表的创建

1.基本格式

1
2
3
4
5
6
7
8
create table [if not exists] 表名
(
列名 类型 [列可选约束][COMMENT'列可选注释'],
列名 类型 [列可选约束][COMMENT‘列可选注释'],
列名 类型 [列可选约束][COMMENT'列可选注释’],
列名 类型 [列可选约束][COMMENT‘列可选注释'],#列之间使用,分割
)
[表可选约束][COMMENT‘表可选注释’];

2. 实例

1
2
3
4
5
6
create table if not exists book_lib(
    book_id int primary key comment '图书id',
    book_name varchar(20) comment '图书名称',
    book_author varchar(20) comment '图书作者',
    book_price double(4,1) comment '图书价格'
)charset=utf8mb4 coll=utf8mb4_ai_ci;

介绍:

创建了一个名为book_lib的表，包含图书id，图书名称，图书作者，图书价格四列，图书id为主键，图书价格为双精度浮点数，图书名称和图书作者均为varchar类型（最大长度为20个字符）。表的字符集为utf8mb4，排序规则为utf8mb4_ai_ci。

五.表的数据类型

1. 整数类型

类型 存储（字节） 范围
tinyint（mysql独有） 1 -128~127
smallint（sql通用） 2 -32768~32767
mediumint (mysql独有) 3 -16777216~16777215
bigint（mysql独有） 8 -2^63~2^63-1
int（sql通用） 4 -2^31~2^31-1

注意：加上unsigned后，对应列数据变成无负号整数，范围大小不变，但最小值从0开始。

例：

1
2
stu_age tinyint unsigned COMMENT‘年龄字段,tinyint类型,无符号值从0开始’,
stu_age tinyint COMMENT‘年龄字段,tinyint类型,无符号值从-128开始',

2. 浮点/定点数类型

类型 存储（字节） 范围
float 4 -3^23~3^23
double 8 -3^64~3^64 
1
2
stu_height float(4,1) unsigned COMMENT‘身高,保留一位小数,多位会四舍五入,无符号值从0开始’
-- 比如180.66会被四舍五入为180.7

注意：从MySQL 8.0.17 开始,不推荐使用非标准FLOAT(M,D)语法DOUBLE(M,D),未来版本中可能删除对它的支持。（支持unsigned修饰,添加修饰,只保留正值范围负值不会迁移到正值!）

类型 存储(字节) M(小数+整数位数) D(小位数)
decimal(M,D) 动态计算 M最大为65 D最大为30

注意:DECIMAL类型的存储空间是可变的,它的存储大小受DECIMAL数据类型定义时指定的精度和规模影响。如果D小数位数为0,DECIMAL则值不包含小数点或小数部分。

例：

1
emp_salary DECIMAL(8,1) COMMENT ‘工资,小数加整数一共8位,保留一位小数,多位会四舍五入’

3. 字符类型

类型 特点 长度范围（字符）
char(n) 固定长度 0=<n<=255
varchar(n) 可变长度 0=<n<=65535
text 长文本 0~65535
tinytext 微短文本 0~255
tinyblob 微短二进制文本 0~255
blob 二进制文本 0~65535

注意:

  • varchar定义时，必须指定最大长度，否则会报错。char定义时，如果不指定最大长度，默认会指定最大长度为1。
  • 字符类型在存储时，如果数据长度小于最大长度，会在末尾填充空格。
  • blob类型是一个二进制大对象，可以容纳可变数量的数据。

4. 日期和时间类型

类型 大小（bytes） 格式 范围 用途
date 3 YYYY-MM-DD 1000-01-01~9999-12-31 日期值
time 3 HH:mm:ss -838:59:59~838:59:59 时间值
datetime 8 YYYY-MM-DD HH:mm:ss 1000-01-01 00:00:00 到 9999-12-31 23:59:59 日期时间值混合
year 1 YYYY 1901~2155 年分值
timestamp 4 YYYY-DD HH:mm:ss 1970-01-01 00:00:00 到 2038-01-19 03:14:07 年分值

注意:

  • year类型有两位或者四位的表达形式,两位 00-69 =2000-2069 70-99=1970-1999

  • 时间类型就是一个特殊格式的字符串,插入数据的时候!,时间类型需要自动赋值,需要手动添加相关的设置

扩展自动填写时间:

  1. 插入默认添加时间
1
datetime default current_timestamp ;
  1. 修改默认更改时间(插入的默认时间)
1
datetime default current_timestamp on update current_timestamp;

实例：

1
2
3
4
ACREATE TABLE t2(
NAME VARCHAR (20),
register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT'插入自动维护时间',
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入维护时间,修改数据自动维护时间')

创建t2表:

注册日期 字段插入自动添加时间,更新数据不变(插入数据的时候,自动维护时间,后续修改数据,时间不变!)

更新日期 字段插入自动添加时间,更新数据时间改变(插入数据需要自动维护时间,修改数据也需要维护时间!)

六. 表的修改和删除

1. 添加列

1
alter table 表名 add 列名 类型 [列可选约束][COMMENT '列可选注释'][first | after 原列名];

2. 删除列

1
alter table 表名 drop 列名;

3. 修改列名

1
alter table 表名 change 原列名 新列名 新类型 [列可选约束][COMMENT '列可选注释'][first | after 原列名];

4. 修改列类型

1
alter table 表名 modify 列名 新类型 [列可选约束][COMMENT '列可选注释'][first | after 原列名];

5. 修改表名

1
alter table 表名 rename 新表名;

6. 删除表

1
drop table [if exists] 表名;

7. 清除表数据

1
truncate table 表名;