1 数据库（中）：DDL（数据库设计）、DML（增删改表中数据）、DQL（查询表中数据）单表基本语法

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

在开始本节前先看看一个项目中哪些部分会和数据库交互？

一些在设计数据库表时的小tip：

• （1）对于一些离散型的字段，如男、女，一般不直接使用字符串，而是存一个标签，如：0-男，1-女
  这么做的原因是：直接存男、女就定死了，如果用标签我们可以在外部修改映射表，更加灵活，0- boy,1-girl;就像这样，更加灵活。
• （2）对于图片、或者一些大文件数据，我们不会直接存在数据库的表中，数据库中的表中只要存其url访问路径、或者硬盘路径.
  很显然这种设计很合理
• （3）对于后台管理表来说，有两个通用字段是最好加上，一个是数据创建时间，另一个是最后一次的修改时间
  create_time：用来记录这条数据产生的时间，也就是插入这条记录的时间
  update_time：修改时间就是，每一次对这条数据进行了修改，我都要更新这个修改的时间

一、DDL-数据库设计

DDL部分的SQL语句虽然基本都已经被图形化界面替代了，但是我们还是是要学，面试可不管这些。

1 操作数据库（数据库的创建、查询、修改、删除、使用）

• 操作数据库: CRUD
  • 1.C(create):创建
  • 2.R(Retrieve):查询
  • 3.U(update):修改
  • 4.D(Delete):删除
  • 5.使用数据库

（1）数据库的查询（show）

show databases; ---- 查询所有数据库名称

其中，information schema、mysql、performance schema、sys是四个MySQL的内置数据库，这几个数据库不要动。
【注】：db01是我自己create的一个数据库，不用管。

show create database 数据库名称; ---- 查询某个数据库的字符集:查询某个数据库的创建语句

（2）数据库的创建(create)

create database 数据库名； ---- 创建一个数据库（如果数据库已经存在会提示错误信息）

【注】：可以用schema来替换database
create schema数据库名；也是创建数据库，后面看到SQL语句中schema和database是可以等价替换的。

create database if not exists 数据库名； ---- 创建一个数据库，但是创建前会先检查数据库是否已经存在

【注】：更加安全，就算存在也不会报错，也不会重复创建

create database 数据库名 character set 编码类型； ---- 创建指定编码类型数据库

MySQL中可以用SQL语句指定创建的数据库的字符类型，但是里面的表有可以修改表的字符类型，这就很奇怪了？
在 MySQL 中，数据库和表的字符集可以独立指定，这是设计上的灵活性，而非冲突或矛盾之处。以下是一些理解要点：
（1）数据库字符集：指定数据库默认使用的字符集。这意味着在这个数据库中创建的新表如果没有指定字符集，则默认使用数据库的字符集。
（2）表字符集：你可以在创建表时指定表的字符集。这允许在同一个数据库中具有不同字符集的表，以满足具体应用需求。
（3）列字符集：甚至可以为表中的各个列指定字符集，为每列提供不同的字符存储方式。
这种设计允许开发者根据数据和应用要求灵活选择合适的字符集和排序规则。例如，一个多语言应用可能需要在数据库中存储不同语言的数据，因此需要这种灵活性来处理不同的字符集。

create database if not exists 数据库名 character set 编码类型； ---- 创建一个指定编码方式的数据库（会先判断数据库是否已经存在）

（3）数据库的修改(alter)

alter database 数据库名称 character set 字符集类型； ---- 修改数据库的字符集

（4）数据库的删除(drop)

drop database 数据库名称； ---- 删除指定数据库（数据库不存在会报错）

drop database if exists 数据库名称； ---- 删除指定数据库（删除前先判断数据库是否存在在删除，不会抛出错误）

（5）数据库的使用

select database(); ---- 查询当前正在使用的数据库名称

什么数据库都没使用就是返回null

use 数据库名称； ---- 使用指定数据库

（6）IDEA图形界面也可以直接点击就可以创建、删除、使用数据库了（后面的表也一样）

这里就不演示了，那个界面很简单摸索一下。一般就是 新建–>架构（数据库schema），实在自己摸索不会，看下面这个几分钟的视频就可以了，图形化傻瓜操作
参考视频

2 操作表

（1）表的创建

SQL中的常见数据类型

参考视频

---- int: 整数

---- double(m,n): 小数类型(m表示这个小数一共多少位，n表示小数点后面有几位)

---- date:日期，只包含年月日，yyyy-MM-dd

---- datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss

---- timestamp: 时间戳类型，包含年月日时分秒yyyy-MM-dd HH:mm:ss

---- varchar(m): 变长字符串(m表示最大字符数)

---- char(m): 定长字符串（m表示占用的字符数），注意的是在MySQL中char是字符串喔，和Java不一样

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
我们挑选一些常见的详细讲一下：

• 常见数据类型：
  • int: 整数
    — e.g. age int
  • double(m,n): 小数类型(m表示这个小数一共多少位，n表示小数点后面有几位)
    — e.g. score double(5,2) 小数一共5位，小数点后有2位数字
  • date:日期，只包含年月日，yyyy-MM-dd
  • datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
  • timestamp: 时间戳类型，包含年月日时分秒yyyy-MM-dd HH:mm:ss
    【注】如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
  • varchar(m): 字符串(m表示最大字符数)
    e.g. name varchar(20): 姓名最大20个字符
    e.g. zhangsan 8个字符 张三 2个字符
  • char(m):定长字符串（m表示占用的字符数），注意的是在MySQL中char是字符串喔，和Java不一样
    e.g. char(2) 就可以表示两个字符，ab，男生等等

【注1】：char和varchar的选择与区别
char(10): 最多只能存10个字符, 不足10个字符, 占用10个字符空间 性能高 浪费空间
varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储 性能低 节省空间
空间不值钱啊，以空间换时间现在是很值得的。

【注2】：
上面这些就是常用的，另外下面还有一些二进制数据类型，比如说视频、图像这些数据都是二进制数据类型，但是我们今后开发时并不会选择讲这类数据直接放在数据库中，我们只用在数据库中存这类二进制数据的磁盘路径即可，这样使用io流会更加快，不然多数据库这一层只会更加的慢。

SQL中的数据类型（完整版）

下面给出MySQL里面的详细具体有哪些数据类型（了解一下即可，真要用再查）：
数值类型

字符串类型

日期时间类型

create table 表名(…); ---- 创建一张表的语法

参考视频

• 语法：
  • 其中 [ ] 内的是可选择性的内容，可与加可以不加
  • 约束（约束是作用在表中字段上的）：就是对这个字段（列）做一些约束，例如唯一标识，添加数据要求id必须唯一这些，下面demo案例中会讲约束一共有哪些
  • comment：这个后面可以加一些你对这个字段的解释说明

下面通过几个demo演示就基本知道是干什么了这里在，

---- 无约束无注释创建一张表

create table tb_user1(
    id int,
    username varchar(20),
    name varchar(10),
    age int,
    gender char(1)
);

---- 无约束有注释创建一张表

create table tb_user2(
     id int comment 'ID,唯一标识',
     username varchar(20) comment '用户名',
     name varchar(10) comment '姓名',
     age int comment '年龄',
     gender char(1) comment '性别'
) comment '用户表';

---- 有约束有注释创建一张表

------ 非空约束：not null

------ 唯一约束：unique

------ 主键约束：primary key(auto_increment自增)

------ 默认约束：default

------ 外键约束：foreign key

• 约束：
  • 概念: 约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  • 目的: 保证数据库中数据的正确性、有效性和完整性。
• MySQL中一共给我们提供了五种字段约束

这里先简单演示一下，像外键约束需要多张表后面学了多表操作才能演示。
下面我们以：

• 这张表来演示前四个约束怎么使用
  • id : 采用主键约束
  • username：采用非空约束+唯一约束
    同一个字段可以同时指定多个约束。其实这里的 非空+唯一就已经等价与主键约束了，但是主键约束只能有一个，所以这里我们采用这个组合的写法
  • name: 采用非空约束
  • gender：采用默认约束，如果添加数据没有指定这个字段的值，用 “男”默认填充

create table tb_user3(
     id int primary key comment 'ID,唯一标识', -- id int primary key auto_increment comment 'ID,唯一标识',  换成这个我们添加数据时就算不传id也会自动添加且自增方式
     username varchar(20) not null unique comment '用户名',
     name varchar(10) not null comment '姓名',
     age int comment '年龄',
     gender char(1) default '男' comment '性别'
) comment '用户表';

【注】：可以看到id的上面多了一个黄色小钥匙，这就是主键约束。

---- 图形化界面创建表（现在企业开发中喜欢用图形化界面创建表，但高手还是自己写代码）

参考视频，由于是图形化界面演示，所以还是直接去看视频就好了

还是很方便的，还能生成代码。

• 需求：根据下面字段设计并创建一张员工表（tb_emp）
  
  且每个员工初始默认密码是：123456
  还需要一个create_time（本条数据创建时间）和update_time（本条数据最后修改时间）
  id作为唯一标识当主键是必不可少的。

create table tb_emp(
     id int primary key auto_increment comment '主键ID,唯一标识',
     username varchar(20) not null unique comment '用户名',
     password varchar(32) default '123456' comment '密码',
     name varchar(10) not null comment '姓名',
     gender int not null comment '性别, 1:男  2:女',   -- 1:男  2:女
     image varchar(300) comment '头像的URL',     -- http://www.baidu.com/a.jpg
     job int comment '职位, 1:班主任  2:讲师  3:学工主管 4：教研主管',
     entry_date date comment '入职日期',  -- 2020-01-01 年月日
     create_time datetime not null comment '创建时间', -- 2020-01-01 10:10:10 年月日时分秒
     update_time datetime not null comment '更新时间'  -- 2020-01-01 10:10:10 年月日时分秒
) comment '用户表';

【注】：注意到，username 、name 、gender 、create_time、update_time 这几个字段是非空，后面往里面添加数据要注意

create table 表名 like 被复制的表名； — 复制表

（2）表的查询

show tables; ---- 查询某个数据库中所有表的名字

show create table 表名; ---- 查询某个表的字符集类型

desc 表名；---- 查询表的结构（表的信息）

（3）表的删除

drop table 表名；

drop table if exists 表名；

（4）表的修改

alter table 表名 rename to 新的表名；---- 修改表名

alter table 表名 character set 字符集名称; ---- 修改表的字符集

MySQL中可以用SQL语句指定创建的数据库的字符类型，但是里面的表有可以修改表的字符类型，这就很奇怪了？
在 MySQL 中，数据库和表的字符集可以独立指定，这是设计上的灵活性，而非冲突或矛盾之处。以下是一些理解要点：
（1）数据库字符集：指定数据库默认使用的字符集。这意味着在这个数据库中创建的新表如果没有指定字符集，则默认使用数据库的字符集。
（2）表字符集：你可以在创建表时指定表的字符集。这允许在同一个数据库中具有不同字符集的表，以满足具体应用需求。
（3）列字符集：甚至可以为表中的各个列指定字符集，为每列提供不同的字符存储方式。
这种设计允许开发者根据数据和应用要求灵活选择合适的字符集和排序规则。例如，一个多语言应用可能需要在数据库中存储不同语言的数据，因此需要这种灵活性来处理不同的字符集。

alter table 表名 add 列名 数据类型; ---- 添加一列

alter table 表名 change 列名 新列名 新数据类型; ---- 修改列名称和类型

alter table 表名 modify 列名 新数据类型; ---- 修改指定列的数据类型

alter table 表名 drop 列名; ---- 删除列

（5）表创建完以后怎么修改字段的约束？== 表的修改的应用

前面学习了，创建表的时候给字段添加约束，但是如果表已经创建完成，怎么修改已有字段的约束呢？
其实这里就要用到表的修改里面 在 alter 语法了。

非空约束的删除、添加（表创建后）

-- 创建表时添加约束
create table demo_table(
    id int,
    name varchar