MySQL学习之路--Day01-优快云博客

MySQL学习之路--Day01

序章
学习之路Day01-04
- MySQL环境搭建与基础入门

序章

从零到熟悉MySQL，虽然一路上磕磕绊绊，但终有收获~

学习之路Day01-04

MySQL环境搭建与基础入门

学习目标

熟悉数据库的作用和分类
熟悉关系型数据库保存数据的基本形式：数据库、数据表
熟悉 DataGrip软件的基本使用：连接数据库、编写SQL执行
掌握 DDL数据定义语言：建表、建库、表结构
掌握 DML数据操作语言：表记录增、删、改
熟悉数据库的常见约束及其特点：主键、非空、唯一、默认值

一：MySQL数据库简介

数据库介绍：作用和分类

作用：管理和存储数据的系统。
分类：关系型数据库和非关系型数据库
- 关系型数据库–>RDBMS
  - 数据库、数据表
  - 数据存储形式：数据以行列式表格（二维表）的形式进行存储
  - 常见产品：MySQL、Oracle、DB2、SQL Server等
- 非关系型数据库（NoSQL数据库）
  - 数据存储形式：键值对存储、文档存储、列式存储、图式存储
  - 常见产品：Redis、MongoDB、Hbase、Neo4j
拓展
- 数据按照一定格式进行存储，用户可以对数据库中的数据进行增加、删除、修改、查询 (CDUR操作)
  - CREATE --> 新增
  - DELETE --> 删除
  - UPDATE --> 修改
  - READ --> 查询

MySQL数据库软件简介

Question：关系型数据库那么多，为什么用MySQL？
- 全世界最流行的开源数据库
- 免费，中小企业首选
- 简单易懂
MySQL发展史
- 拓展
  - 2015-10：MySQL5.7正式版发布
  - 2018-04：MySQL8.0正式版发布（新增窗口函数等）
软件特点
- MySQL数据库是 C/S 软件：Client[客户端]–>Server[服务端]
- 免费开源，不需要额外支付费用（MySQL5以上企业版开始收费，提供技术支持）
- 使用标准的SQL数据语言形式(增、删、改、查)
- 支持不同操作系统，并提供多种编程语言的操作接口（包括C、C++、Python、Java等）
- 支持多种存储引擎
软件版本
- 注意： 下文使用的是MySQL 8.0.12版本，具体安装过程参考MySQL与DataGrip安装
- 在命令行查看MySQL版本
  - win键+R
- 输入cmd，鼠标点击确定或者按电脑上的Enter键(即回车键)
输入mysql -V 或者 mysql -version，显示如下图所示即成功

MySQL客户端连接服务端

Question：如果你在上海，公司MySQL服务在北京，该怎样连接？
IP地址和PORT端口
- IP 地址：标识网络上唯一的一台设备（电脑）
- PORT端口：标识一台设备上的一个网络程序
- 拓展
  - 浏览器默认端口号：80
  - MySQL端口号：3306
  - HiveSQL端口号：10000
  - Oracle端口号：1251
MySQL 客户端连接服务器
- 命令行方式
  - 连接（命令格式）
    - mysql -h数据库服务端IP -P数据库服务端口 -u用户名 -p密码
  - 说明
    - 数据库服务段IP默认是：127.0.0.1 或者 localhost
    - 数据库服务端口默认是：3306
    - -u 后面是登录的用户名，MySQL安装之后，自带一个root用户
    - -p 后面是登录用户密码，如果不填写，回车后会提示输入密码（注意：密码填写时不显示出来，输完直接回车登录即可）— 一般在企业中，都不填写，回车后再输入
    - 拓展如果是在本地电脑上连接MySQL，则可省略-h 和 -p；即mysql -u用户名 -p密码
  - 退出
    - 进入mysql后输入quit 或者 exit，退出mysql
- 使用DataGrip软件
  - DataGrip是JetBrains公司推出的管理数据库的产品（数据库客户端软件），功能非常强大，可以兼容各种数据库；设计符合数据库管理员、开发人员需要；用户体验非常不错
  - 连接
  - 注意：第一次使用连接时，根据提示点击下载MySQL连接驱动文件
  - 具体可以参考MySQL与DataGrip安装进行配置连接
DataGrip软件使用简介
- 软件界面介绍
- 附加文件夹到项目中[重要]
  - 右键选择添加文件夹到项目中
- 找到要附加到项目中的文件夹，点击ok确定
- 右键点击该文件夹，选中New中SQL File进行新建SQL文件
- 输入你想要取的SQL文件名
- 附加之后，则可看见刚创建的SQL文件
- 给将要编写SQL语句的文件建立会话
DataGrip设置
拓展
- SQL中快速执行操作的快捷键：Ctrl+Enter
SQL语句介绍：DDL、DML、DQL、DCL
- Question：什么是SQL语句？
  - SQL全称是结构化查询语言(Structured Query Language)，是关系型数据库管理系统都需要遵循的规范，是操作关系型数据库的语言，只要是关系型数据库，都支持SQL。
  - 虽然所有的关系型数据库都支持SQL，但是不同的关系型数据库都会有一些特殊的东西(特有的函数或语法)。
- SQL语句的作用
  - SQL语句的主要作用就是对数据库中的数据进行相应的管理操作（增、删、改、查）。
    - 数据库的增、删、改、查
    - 数据表的增、删、改、查
    - 表记录的增、删、改、查
    - …
- SQL语句的分类（根据功能分为4大类）
  - DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：数据库，表，列等。
    注意：DDL并未涉及表中记录(数据)的操作！
  - DML (Data Manipulation Language)：数据操作语言，用来对数据库中的表记录进行增、删、改。
  - DQL (Data Query Language)：数据查询语言，用来查询数据库中表的记录。[重点学习]
  - DCL (Data Control Language)：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。
- SQL通用语法
  - 一条SQL语句可以单行或多行书写，以分号;结尾
  - MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
  - 可以使用/* */、–、#的方式完成注释
    - DataGrip快速注释/取消注释的快捷键：Ctrl + /
  - 可使用空格和缩进来增强语句的可读性
    - DataGrip软件sql格式化快捷键： Ctrl + Alt + L
  - Question：以下两个SQL语句，哪个可读性更好？
    - 结果：自然是第一个！

二：SQL-DDL数据定义语言

场景说明
- DDL：主要是负责数据库及数据表的结构设置，也就是搭建保存数据的容器，并定义存储规则的语言。
数据库操作
- 创建数据库

# 创建数据库
CREATE DATABASE 数据库名称;

# 当数据库不存在时，创建数据库
-- 对于IF NOT EXISTS 如果数据库不存在则创建，存在，则忽略
CREATE DATABASE IF NOT EXISTS 数据库名称;

# 创建数据库并指定utf8编码格式
-- 指定数据库的编码字符集utf8，防止表数据存储中文时出错或乱码
CREATE DATABASE 数据库名称 DEFAULT CHARSET=utf8;

# 如果数据库不存在，则创建数据库，并指定utf8编码格式
CREATE DATABASE IF NOT EXISTS 数据库名称 DEFAULT CHARSET=utf8;

- 查看、切换数据库

# 显示当前项目下已有的所有数据库
-- 注意：DATABASE后多了个s，表示复数
SHOW DATABASES;

# 查看数据库创建的语句
SHOW CREATE DATABASE 数据库名称;

# 查看当前切换的数据库
SELECT DATABASE();

# 切换指定数据库
USE 数据库名称;

在这里插入图片描述

- 删除数据库

# 删除指定数据库
-- 注意：实际工作不要删除数据库！！！
-- 注意：实际工作不要删除数据库！！！
-- 注意：实际工作不要删除数据库！！！
DROP DATABASE 数据库名称;

- 练习小题–答案均在文章末尾处
数据表操作
- 创建数据表

# 语法
-- 字段名即列名 数据类型就是类型限制 约束即类型之外的限制，是可选也可省略的，主要限制的是表中数据
CREATE TABLE 表名(
	字段名1 数据类型 [约束],
	字段名2 数据类型 [约束],
	字段名3 数据类型 [约束]
)DEFAULT CHARSET=utf8;

# 创建表举例
-- 创建一个商品种类表，其中有id(整型且为主键)、username(不定长字符型100)
CREATE TABLE category(
	-- 字段名1为id，数据类型为整型，添加了主键约束
	id INT PRIMARY KEY,
	-- 字段名2为username，数据类型为不定长字符串型即VARCHAR，最大长度为100，没用约束
	username VARCHAR(100)
)DEFAULT CHARSET=utf8;

- - 注意：不用管类型和约束是什么，现在只需知道创建表的格式即可
  - 注意：后期只要是中括号即[]，包围的都是可以省略不写的！！！
- 查看数据表和表结构

# 查看当前数据库中已有的所有数据表
-- 注意：TABLE多了个S表示复数
SHOW TABLES;

# 查看创建表的语句
SHOW CREATE TABLE 表名;

# 查看指定数据表的结构
DESC 表名;

- 修改表名、删除数据表

# 修改指定表名称
RENAME TABLE 旧表名 TO 新表名;

# 删除指定的数据表
DROP TABLE 表名;

- 练习小题–答案均在文章末尾处
数据类型和约束
- Question：什么是数据类型？约束又是什么？
  - 存储商品数据时，价格是数字，名称是文本，如何让价格列只能存数字，名称列只存文本？
  - 添加一个商品数据时，如果名称为NULL，数据有没有问题？怎么限制不能为NULL？
- 数据类型
  - 整数：TINYINT、SMALLINT、 MEDIUMINT、INT、BIGINT
    - 参考文档： https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
    - 小数：DECIMAL，DOUBLE，FLOAT
      - DECIMAL(M, N)，如DECIMAL(5，2)表示共存5位数，小数占2位，存储小数要求没有误差时使用，比如：金钱
    - 字符串：VARCHAR，CHAR，TEXT
      - CHAR表示固定长度的字符串，如CHAR(3)，如果填充’ab’时会补一个空格为’ab '，3表示字符数
      - VARCHAR表示可变长度的字符串，如VARCHAR(3)，填充’ab’时就会存储’ab’，3表示字符数
    - 日期时间：DATE， TIME， DATETIME
- 如何选择数据类型
  - 根据存储数据值的不同，选择合适的数据类型
    - 如：姓名、年龄、身高、手机号
  - 同样的数据类型，在满足后续需求的前提下，尽量选择取值范围小的，够用就行，节省存储空间
    - 如：存储年龄，TINYINT和INT哪个更合适？当然是TINYINT啦~
- 数据约束
  - 主键约束
    - 关键词
      - PRIMARY KEY
    - 作用
      - 唯一标识表中的一行记录。 MySQL 建议所有表的主键字段都叫 id，类型为 int unsigned。
      - 非空
      - 一张表中只能有一个

# 方式1：创建表时设置主键
-- 直接在字段中添加
CREATE TABLE students(
	id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(20),
    age TINYINT,
    mobile CHAR(11)
)DEFAULT CHARSET=utf8;

-- 在所有字段设置完后，添加
CREATE TABLE students(
	id INT UNSIGNED,
    name VARCHAR(20),
    age TINYINT,
    mobile CHAR(11),
    -- 将 id 设置为主键
    PRIMARY KEY(id)
)DEFAULT CHARSET=utf8;

# 方式2：创建表后设置主键(了解)
CREATE TABLE students(
	id INT UNSIGNED,
    name VARCHAR(20),
    age TINYINT,
    mobile CHAR(11)
)DEFAULT CHARSET=utf8;

-- 创建表后，使用ALTER TABLE关键字添加主键
ALTER TABLE students ADD PRIMARY KEY(id);

-- 删除主键约束
ALTER TABLE students DROP PRIMARY KEY;

- - - 拓展：主键自增
      - 关键词
        AUTO_INCREMENT
      - 作用
        在设置主键自增之后，insert数据时，如果不设置主键，或主键设置为0或NULL，数据库会自动维护主键的值

# 在创建表时添加主键自增
CREATE TABLE students(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    age TINYINT,
    mobile CHAR(11)
)DEFAULT CHARSET=utf8;

- - 非空约束
    - 关键词
      - NOT NULL
    - 作用
      - 此字段不允许填写空值。NULL表示空
      - 一张表可以有多个
  - 惟一约束
    - 关键词
      - UNIQUE
    - 作用
      - 此字段的值不允许重复，唯一
      - 一张表可以有多个
  - 默认值约束
  - 关键词
    - DEFAULT
  - 作用
    - 当不填写字段对应的值会使用默认值，如果填写时以填写为准。
    - 一张表可以有多个
  - 外键约束
    - 关键词
      - FOREIGN KEY
    - 作用
      - 对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
表字段操作
- Question：为什么创建表之后？又要对表的字段进行操作？
  - 在实际开发中，我们创建的数据库一般只满足第一版需求，随着日后的需求不断增加，数据维度不断扩展，还有更多的需求需要添加进来，此时，数据存储的结构就需要随之变化
- 添加表字段

/* 注意：
1. 约束按需求选择添加，可以没有约束，类型必须填写，并且只能填写一个类型
2. 表字段名称不能重复，否则会出现 Duplicate column 错误
3. 如果列名和关键字相同，需要在列名两边加``（反撇）
*/
ALTER TABLE 表名 ADD 列名 类型(长度) [约束];

# 拓展
-- 如果创建了一个students表，字段依次为id name age
# 如果要在students表中增加一个学号num_id（定长字符型，长度为11，非空约束），且要在第一个
ALTER TABLE students ADD num_id CHAR(11) NOT NULL FIRST;
# 如果要在students表中增加一个学号num_id（定长字符型，长度为11，非空约束），且要在name后面
ALTER TABLE students ADD num_id CHAR(11) NOT NULL AFTER name;

- 修改、删除表字段

# 修改表字段
-- 方式一：可以修改字段名，类型以及约束
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型(长度) [约束];

-- 方式二：只能修改类型和约束
ALTER TABLE 表名 MODIFY 列名 新类型 [新约束];

# 删除表字段
ALTER TABLE 表名 DROP 列名;

- 练习小题–答案均在文章末尾处

三：SQL-DML数据操作语言

场景说明
- DML：数据操作语言，用来对数据库中的表记录进行增、删、改
表记录的增、删、改
- 增加表记录

# 不指定字段插入，一次插入一行，并且指定所有列
INSERT INTO 表名 VALUES(值1, 值2, 值3,...);

# 指定字段插入，一次插入一行，并且指定插入列
INSERT INTO 表名(字段1, 字段2, 字段3,...) VALUES(值1, 值2, 值3,...);

# 不指定字段插入，一次性添加多条记录
INSERT INTO 表名 VALUES(值1, 值2, 值3,...), (值1, 值2, 值3,...), ...;

- - 练习小题–答案均在文章末尾处
- 更新表记录

# 更新所有行
-- 注意：更新所有行，执行代码后，需要点击弹出来的继续执行的提示才能生效
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,...;

# 更新满足条件的行
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,... WHERE 条件;

在这里插入图片描述

- - 练习小题–答案均在文章末尾处
- 删除表记录

# 删除表中满足条件的数据
DELETE FROM 表名 WHERE 条件;

# 删除表中的所有数据
DELETE FROM 表名;

# 清空表数据
TRUNCATE [TABLE] 表名;

- - 练习小题–答案均在文章末尾处
- 拓展
  - 面试题：DELETE 与 TRUNCATE的区别是什么？
    - DELETE，属于DML语句，会清空数据表，但如果表中有自增，则不会清零自增，下次插入数据时从清空表中的最大序列号+1
    - TRUNCATE，属于DDL语句，会清空数据表，如果表中有自增，也会清零，相当于删除表之后再进行重建

小题答案

# 第一个练习小题
-- 示例1：创建一个名为 bigdata_db 的数据库
CREATE DATABASE bigdata_db;

-- 示例2：数据库不存在时，才创建数据库
CREATE DATABASE IF NOT EXISTS bigdata_db;

-- 示例3：创建数据库并指定 utf8 编码字符集
CREATE DATABASE bigdata_db2;

-- 示例4：查看当前有哪些数据库
SHOW DATABASES;

-- 示例5：使用 bigdata_db 数据库
USE bigdata_db;

-- 示例6：使用 bigdata_db2 数据库
USE bigdata_db2;

-- 示例7：查看当前使用的是哪个数据库
SELECT DATABASE();

-- 示例8：删除 bigdata_db 数据库
DROP DATABASE bigdata_db;

-- 示例9：删除 bigdata_db2 数据库
DROP DATABASE bigdata_db2;

# 第二个练习小题
-- 练习准备
# 创建数据库
CREATE DATABASE test_2;

# 切换数据库
USE test_2;

-- 示例1：创建一个 users 数据表
CREATE TABLE users(
	id INT,
	name VARCHAR(50)
)DEFAULT CHARSET=utf8;

-- 示例2：查看当前数据库中有哪些表
SHOW TABLES;

-- 示例3：查看 users 数据表的结构
DESC users;

-- 示例4：将 users 表重命名为 categories
RENAME TABLE users TO categories;


# 第三个练习小题
-- 练习准备
# 创建数据库
CREATE DATABASE test_3;

# 切换数据库
USE test_3;

# 创建数据表
CREATE TABLE category(
	id INT,
	name VARCHAR(50)
)DEFAULT CHARSET=utf8;

-- 示例1：给 category 表添加一个 num 字段，整数类型并且不能为空
ALTER TABLE category ADD num INT NOT NULL;

-- 示例2：给 category 表添加一个 desc 字段，最大长度为100个字符
ALTER TABLE category ADD `desc` VARCHAR(100);

-- 示例3：将 category 表的 desc 字段修改为 description 字段
ALTER TABLE category CHANGE `desc` description  VARCHAR(100);

-- 示例4：删除 category 表的 num 字段
ALTER TABLE category DROP num;


# 第四个练习小题
# 创建数据库
CREATE DATABASE test_4;

# 切换数据库
USE test_4;

# 创建数据表
CREATE TABLE category(
	id INT,
	name VARCHAR(50)
)DEFAULT CHARSET=utf8;

-- 示例1：在 category 表中插入1条记录：不指定字段
INSERT INTO category VALUES(1, '手机');

-- 示例2：在 category 表中插入1条记录：指定字段
INSERT INTO category(name) VALUES('电脑');

-- 示例3：在 category 中一次插入2行记录：不指定字段
INSERT INTO category VALUES(2, '化妆品'), (3, '家具');

-- 示例4：在 category 中一次插入3行记录：指定字段
INSERT INTO category(id) VALUES(4), (5), (6);

# 第五个练习小题
# 创建数据库
CREATE DATABASE test_5;

# 切换数据库
USE test_5;

# 创建数据表
CREATE TABLE category(
	cid INT,
	cname VARCHAR(50)
)DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO category 
	VALUES
(1, '手机'), 
(2, '电脑'), 
(3, '化妆品');

-- 示例1：将 category 表中所有行的 cname 改为 '家电'
UPDATE category SET cname = '家电';

-- 示例2：将 category 表中 cid 为 1 的记录的 cname 改为 '服装'
UPDATE category SET cname = '服装' WHERE cid = 1;

# 第六个练习小题
# 创建数据库
CREATE DATABASE test_6;

# 切换数据库
USE test_6;

# 创建数据表
CREATE TABLE category(
	cid INT AUTO_INCREMENT PRIMARY KEY,
	cname VARCHAR(50)
)DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO category 
	VALUES
(1, '手机'), 
(2, '电脑'), 
(3, '化妆品'),
(NULL, '家具'), 
(NULL, '厨具'), 
(6, '服装');

-- 示例1：删除 category 表中 cid 为 5 的记录
DELETE FROM category WHERE cid = 5;

-- 示例2：删除 category 表中的所有记录
-- 方法一：删除所有数据，自增序列不清零
DELETE FROM category;

-- 方法二：清空数据表，自增序列清零
TRUNCATE category;