子乌笔记-MySQL-数据定义DDL篇

前言

此篇章为本人在学习 MySQL 过程中送总结的笔记，总体的会全部收集到一个专栏当中，大伙们无论是单纯学习 MySQL，还是需要从事大数据行业的，都可以去我的主页去翻一翻哦！接下里我们来开始 MySQL 中数据定义篇的学习吧！

• MySQL-数据操作DML篇
• MySQL-数据查询DQL篇
• MySQL-权限操纵DCL篇
• MySQL 面试题-概念
• MySQL 面试题-习题

---

 

一、数据库概述

1. 什么是数据库？

【官方解释】：数据库（database）是按照数据结构来组织、储存和管理数据的仓库，是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

通俗的说，数据库就是存储数据的仓库，其本质是一个文件系统，按照特定的格式将数据存储起来，用户可以对数据库进行增删查改操作【CRUD】，也为事务的特性，后面我们会专门开一章讲解事务。

【了解】数据库的发展趋势
随着时代的发展，大量数据在不断的产生，伴随而来的是如何高效安全的存储数据和处理数据，而这一问题成为了信息时代非常大的问题。

1. web1.0时代：用企业提供数据，资讯类的网站、新闻类网站出现等，搜狐、头条、腾讯新闻、新浪……
2. web2.0时代：由用户产生数据，企业要去管理这些数据，抖音、淘宝、京东（个人信息、浏览信息订单）

 

2. 数据库分类

关系型数据库（SQL）

关系型数据库，采用了关系模型来组织数据的数据库，简单来说，关系模型指的就是二维表格模型，如同Excel文件中的表格，强调使用表格的方式存储数据库。

这些数据库通常支持ACID（Atomicity、Consistency、Isolation、Durability）事务，能够保证数据的完整性和一致性。

【记忆】常见的关系型数据库

数据库名称	地位
Oracle	最早的商品化的收费大型数据库，Oracle公司的产品旗下产品：Oracle数据库、MySQL数据库、Java语言[SUN公司]
Mysql	开源免费的数据库，中型的数据库。已经被Oracle收购了
DB2	IBM公司收费的数据库产品，常运用在银行系统中
SQLserver	MicroSoft公司收费的中型的数据库，长运用于C#，.net等语言
SQLite	轻型数据库，也是关系型数据库。它包含在一个小的C库中，常常应用在嵌入式产品中，例如手机端、安卓、苹果手机。
PostgreSQL	功能最大的开源数据库PG 成为 专业开发者最常使用的数据库!(Used)、PG 成为 开发者最为喜爱的数据库!(Loved)、PG 成为开发者最想要用的数据库!(Wanted)

非关系型数据库（NoSQL)

非关系型数据库：又被称为NoSQL（Not Only SQL )，意为不仅仅是SQL，对NoSQL 最普遍的定义是“非关联型的”，强调 Key-Value 的方式存储数据；此外还有、文档存储、列族存储等。
Key-Value结构存储：Key-Value数据库是一种以键值对存储数据的一种数据库，类似Python中的字典dict。
可以将整个数据库理解为一个大的字典dict，每个键都会对应一个唯一的值。存储在内存中，访问内存效率高！

【记忆】常见非关系型数据库

数据库名称	地位
Redis	Redis一个小而美的数据库，主要用在key-value的内存缓存，读写性能极佳。 后台开发！
MongoDB	最好的文档型数据库，非常接近关系型数据库的。
Elasticearch	最好的搜索服务
Cassandra	最好的列式数据库
Hbase	优秀的分布式、列式数据库，HBase是列式数据库，目标是高效存储大量数据。

【拓展】例如，一起来看看在搜索引擎、求职网站等关键词排名中使用数据库产品排行榜（点击跳转）

 

3、MySQL的介绍

MySQL数据库系统是由瑞士的DataKonsultAB公司研发，该公司被Sun收购，现今又被Oracle公司收购，因此MySQL目前是 Oracle 旗下的产品。
 

①MySQL的特点：

1. MySQL是开源的，所以你不需要支付额外的费用；
2. MySQL，中型，支持大型的数据库。可以处理拥有上千万条记录的大型数据库；
3. MySQL使用标准的SQL数据语言形式； SQL语法
4. MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口【pymysql】。这些编程语言包括Python、C、C++、Java、Ruby等等；
5. 支持多种存储引擎。大数据：InnoDB引擎

②MySQL的版本

针对不同的用户，MySQL分为两种不同的版本:

• MySQL Community Server🔥
  社区版本，免费，但是Mysql不提供官方技术支持，我们学习使用的就是此模板。
• MySQL Enterprise Edition
  商业版，该版本是收费版本，可以试用30天，官方提供技术支持。
• MySQL Cluster
  集群版，开源免费，可将几个MySQL Server封装成一个server。
• MysQL Cluster CGE
  高级集群版，需付费。
• MysQL Workbench (GUI TOOL)
  款专为MySQL设计的ER/数据库建模工具。MysQL Workbench又分为两个版本，分别是社区版(MySQLWorkbench oss)商用版 (MysQL Workbenc

---

 

二、 Mysql的安装、登录和退出

2.1 Mysql的安装

为了将精力尽可能放在学习SQL语法上，子乌推荐利用小皮面板快速安装捏！
对于电脑中没有安装过Mysql数据库的推荐用XP小皮面板安装Mysql，可以傻瓜式安装，前期我们使用win来学习MySQL基础语法，后期我们要在Centos上远程连接MySQL进行数据开发！

如果想要在Centos上安装，可以查看：Centos7 下 MySQL8.0 的安装

【了解】数据库管理工具（DataGirp）
数据库管理工具是指用来管理和操作数据库的软件工具，它们充当了数据库系统管理员和开发人员的工具，可以帮助用户创建、维护、备份和恢复数据库，执行SQL查询和管理数据库对象。以下是一些数据库管理工具的优点：

1. 提高效率：数据库管理工具能够自动化完成许多数据库管理任务，如备份、恢复、监控等。这大大提高了数据库管理员的工作效率。

2. 减少错误：人为操作数据库时容易犯错，例如错误地输入命令或误删数据。使用数据库管理工具可以减少这些人为错误的发生，降低操作数据库的风险。

3. 提高安全性：数据库管理工具可以提供强大的安全性功能，如访问控制、日志记录和审计等。这些功能能够帮助数据库管理员保护数据库的安全性，防止未经授权访问、数据泄漏等安全问题的发生。

4. 提供可视化界面：数据库管理工具一般都提供图形化用户界面，使得数据库管理员可以更直观地完成数据库管理工作。这大大降低了数据库管理员的学习成本，提高了工作效率。

5. 提供多种功能：数据库管理工具有许多不同的功能，如数据分析、查询优化等。这些功能可以使得数据库管理员更好地管理数据库和优化查询性能。

在本篇学习我们使用 DataGrip 进行MySQL的程序的编写。

---

 

2.2 Mysql登录

【掌握】登录方法有两种不同的格式，分别分为：本地登录、远程登录

• 2.2.1本地连接Mysql的语法：

mysql -u用户名 -p
密码

• 2.2.2 远程连接

mysql -h IP地址 -P 3306 -u 用户名 -p
密码

2.3 Mysql退出

方式1：exit
方式2：quit
*方式3：快捷键ctrl+D(只能在Linux系统中使用)
*方式4：关闭掉窗口

---

 

三、SQL 基本语句-DDL

 
我们在了解SQL语句前可以先了解几个名词：

• 数据库管理系统
  【MySQL】数据库管理系统（DataBase Management System，DBMS）指的是一种操作和管理数据库
  的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和
  完整性，用户通过数据库管理系统访问数据库中表内的数据。
• 数据库【database】
  数据库就是存储数据的仓库。 在MySQL软件中，创建的可以用于存储数据的仓库！
• 数据表【table】
  据表（或称表）是数据库最重要的组成部分之一。数据库只是一个库或框架，数据表是其实质内容
  【可以存放数据内容】。
  例如在"教学管理系统"中，"教学管理"数据库包含了特定主题的几个数据表：教师表、课程表、成绩表、
  学生表和班级表，用来管理教学过程中学生、教师、课程等信息。

在进行实际的SQL代码前，我们注意一下 SQL通用语法注意事项：

• SQL 对关键字的大小写不敏感
• SQL 语句可以单行或多行书写，以分号结束【重要】

• MySQL 中的注释：
  单行注释：-- 注释 或 #注释内容(MySQL特有)（快捷键：ctrl+/）
  多行注释：/* 注释 */（快捷键：ctrl+shift+/）

SQL 语句分类

SQL是用来存取关系数据库的语言，具有定义、操作、查询和控制关系型数据库的四方面功能。

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（库、表、字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

本章我们主要解决 DDL 中的语法！

---

 

DDL 数据定义语言

我们针对表和库的定义语句，我们统称为 DDL 语句

内容	用法
用来定义数据库和数据表对象	例如，定义数据库、表、列等
常用关键字	例如，创建create、修改alter、删除drop等。

3.1 库的创建、删除

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名字 [CHARSET 字符集];

• 【IF NOT EXISTS】：此语句为：如果数据库中如果不存在该名字数据库，则创建
• 【CHARSET 字符集】：设置字符集，常见的 utf8、gbk
• 【】方括号的为补充语句，非必要填写，在后文如此。

• 删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

• 【IF EXISTS】：此语句为：如果数据库中如果存在该名字数据库，则删除

例题：
【1】创建名为db_ziwu的数据库
【2】删除名为db_ziwu的数据库

# 创建名为db_ziwu的数据库
CREATE DATABASE db_ziwu;
# 删除名为db_ziwu的数据库
DROP DATABASE db_ziwu;

---

 

3.2 库的查看

• 查询所有数据库

SHOW DATABASES;

• 查询数据库创建语句

SHOW CREATE DATABASE 库名;

例题：
【1】查询所有的数据库
【2】查询mysql数据库的建库代码

# 查询当前所有数据库
SHOW DATABASES;
# 查询mysql数据库创库代码
SHOW DATABASE mysql;

结果：

---

 

3.3 使用 库

• 使用数据库

USE 数据库名;

• 查询当前正在使用的数据库

SELECT DATABASE();

• 在操纵数据库内数据表之前，必须提前声明需要操作的数据库，此时需要USE 数据库名
• 使用SELECT DATABASE()可以得知当前在哪个数据库工作区下。

---

 

字段类型

在学习创建数据表语句之前，我们应该先学习字段类型 ，在数据表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表时可以给字段设定数据类型。
使用数据类型的原则是：够用就行，并且尽量使用取值范围小的，这样可以节省更多存储空间。
My
SQL 中定义的数据字段的类型对数据库优化是非常重要的，主要分为三种类型：数值、日期/时间和字符串类型

MySQL 支持所有标准的 SQL 数值数据类型
 
①数值类型
数值类型辣么多，但是我们实际使用最多的是：INT、DOUBLE

 
②日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

 
③字符串类型
字符类型辣么多，但是我们常用的是：VARCHAR、CHAR

char(n) 和 varchar(n)的区别：一般来说，对于长度不太固定的字符串，应该使用varchar(n)。而对于长度固定的字符串，可以使用char(n)来提高查询效率。

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
但是在实际情况，一般图片等部署在服务器上，只存储路径。

 

3.4 数据表创建

在操作数据表前，先要确认要在哪个数据库下进行操作。

USE 数据库名;

在创建数据表时，需要使用CREATE TABLE关键字，还要指定字段信息，语法如下：

CREATE TABLE [IF NOT EXISTS] 数据表名(
	字段名 字段类型 [自增] [主键]
);

说明：

1. 常见类型：int、vatchar(255);
2. PRIMARY KEY,主键，使用逐渐修饰的字段必须保证唯一非空
3. AUTO_INCREMENT,自动增长，通常为int型的值自动增长（从1开始）
4. 【判断语句】内可以填写IF NOT EXISTS 保证代码稳定性

练习-使用创建数据表命令来完成：
（1）创建一个新的查询脚本和新数据库班级db_user，并在该库中完成创建数据表的操作；
（2）创建字段时，不添加任何[约束]条件；
（3）新建一个用户表user，字段有编号、用户名、昵称；
（4）思考：若再使用相同命令创建一个已有的数据表：user，能成功吗？

-- （1）创建一个新的查询脚本和新数据库`班级`db_user，并在该库中完成创建数据表的操作；
CREATE DATABASE IF NOT EXISTS db_user CHARSET utf8;
USE db_user;

-- （2）创建字段时，不添加任何[约束]条件；
-- （3）新建一个用户表user，字段有编号、用户名、昵称；
CREATE TABLE IF NOT EXISTS user(
    id INT,
    user_name VARCHAR(20),
    `name` VARCHAR(20)
);

-- （4）思考：若再使用相同命令创建一个已有的数据表：user，能成功吗？
-- 否，会报错，因此要加入条件判断是否存在以保证程序的稳定性[IF NOT EXISTS]

3.5 查看数据表

• 查询当前数据库中所有表

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查询指定表的创建表语句

SHOW CREATE TABLE 表名;

例如，使用命令查看数据表：
（1）在上述班级db_user数据库中查看有哪些数据表；
（2）查看创建数据表user的创建语句；
（3）查看数据表user的表结构。

# （1）在上述`班级`db_user数据库中查看有哪些数据表；
USE db_user;
SHOW TABLES;
# （2）查看创建数据表user的创建语句；
SHOW CREATE TABLE user;
# （3）查看数据表user的表结构。
DESC user;

3.6 删除数据表

• 删除数据表

DROP TABLE 数据表名;

• 说明：看到drop关键字，要清楚是使用SQL语句分类的DDL。
• 特别注意：在删除数据表前，先要选择使用哪个数据库,以免删除不同数据库的同名表！

例如，使用命令删除数据表：

（1）在班级db_user数据库中，继续新建一个只有姓名字段的学生表，然后查看库中有哪些数据表；
（2）删除学生表后，再查看有哪些数据表。

# （1）在`班级`db_user数据库中，继续新建一个只有姓名字段的学生表，然后查看库中有哪些数据表；
USE db_user;
CREATE TABLE student(
    `name` VARCHAR(20)
);
SHOW TABLES;
# （2）删除学生表后，再查看有哪些数据表。
DROP TABLE student;
SHOW TABLES;

3.7修改数据表

• 添加字段

ALTER TABLE 表名 ADD 字段名 字段类型 [FIRST]

修改字段中的约束：

FIRST:把新添加字段放在首位
AFTER 其他字段名称:把新添加字段放在指定字段的后
提示：约束跟在所有字段后，可以写入FIRST,将字段添加至开头.

例如，使用命令完成：

（1）创建一个新数据库班级db_user2，设定默认编码为utf8，并在该库中完成修改数据表的操作；
（2）添加判断语句，新建一张用户表table_user，有用户名、密码这2个字段；
（3）给用户表添加一列信息展示information字段，长度为140；
（4）思考1：在用户表的最开始处，添加一列最初名称first_name，该怎么做呢？
（5）思考2：在用户表的密码字段后，添加一列年龄age字段，该怎么做？

# （1）创建一个新数据库`班级`db_user2，设定默认编码为utf8，并在该库中完成修改数据表的操作；
CREATE DATABASE IF NOT EXISTS db_user2 CHARSET utf8;
# （2）添加判断语句，新建一张用户表table_user，有用户名、密码这2个字段；
USE db_user2;
CREATE TABLE IF NOT EXISTS table_user(
    `user_name` VARCHAR(20),
    `password` VARCHAR(20)
);
# （3）给用户表添加一列信息展示information字段，长度为140；
ALTER TABLE table_user ADD information VARCHAR(140);
# （4）思考1：在用户表的最开始处，添加一列最初名称first_name，该怎么做呢？
ALTER TABLE table_user ADD first_name VARCHAR(20) FIRST;
# （5）思考2：在用户表的密码字段后，添加一列年龄age字段，该怎么做？
ALTER TABLE table_user ADD age INT AFTER `password`;
DESC table_user;

• 修改字段类型

ALTER TABLE 表名 MODIFY 字段名 新字段类型 [对字段的约束];

例如，使用命令完成：
（1）给上述用户表的信息字段类型修改为char，长度修改为60；
（2）将用户名字段修改长度为16，且添加非空约束。

非空约束可写成not null，表示不能为空值，必须进行设定数据。

# （1）给用户表的信息字段类型修改为char，长度修改为60；
ALTER TABLE table_user MODIFY information char(60);
# （2）将用户名字段修改长度为16，且添加非空约束。
ALTER TABLE table_user MODIFY user_name VARCHAR(16) NOT NULL;

• 修改字段名

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [约束];

例如，使用命令完成：
（1）把用户表的用户名修改为name，字符串类型、长度为18；
（2）把用户表的信息字段列名修改为info，类型修改为varchar，长度为100。

# （1）把用户表的用户名修改为name，字符串类型、长度为18；
ALTER TABLE table_user CHANGE user_name name VARCHAR(18);
# （2）把用户表的信息字段列名修改为info，类型修改为varchar，长度为100。
ALTER TABLE table_user CHANGE information info VARCHAR(100);

CHANGE保持字段名一致可以替代MODIFY进行修改字段类型操作。

 

3.8 数据表操作-字段删除

删除字段：

ALTER TABLE 表名 DROP 字段名;

例如，使用命令完成：
（1）删除上述用户表中的年龄这一列；
（2）使用命令查看表结构。

# （1）删除上述用户表中的年龄这一列；
ALTER TABLE table_user DROP age;
# （2）使用命令查看表结构。
DESC table_user;

 

3.8 数据表操作-修改表名

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名;
RENAME TABLE 表名 TO 新表名

例如，使用命令完成：
（1）把用户表的表名更改为table_user；
（2）使用命令查看库中所有数据表信息。

# （1）把用户表的表名更改为tb_user；
RENAME TABLE table_user TO tb_user;
# （2）使用命令查看库中所有数据表信息。
SHOW TABLES;

在DATEGIRP中，较为规范化的写法是RENAME TABLE 表名 TO 新表名

 

3.10 数据表操作-删除

• 删除表

DROP TABLE[IF EXISTS] 表名;

说明：
看到drop关键字，要清楚是使用SQL语句分类的DDL。

• 删除再创建
  删除表，再根据创表语句创建一个一模一样的表，因此这个表是新表！

truncate table 表名