可乐笔记MySQL数据库的使用
type
status
date
slug
summary
tags
category
icon
password
数据库基础知识
先谈发音
MySQL如何发音？在国内MySQL发音有很多种，Oracle官方文档说他们念作My sequal['si:kwəl]。
数据库基本概念
- 数据：数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。
- 数据库：数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映客观事物间的本质联系。
- 数据库管理系统：数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。
- 数据库应用程序：数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序。
- 数据库管理员：数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。
数据库分类
关系型数据库
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。可以采用结构化查询语言（SQL）对数据库进行操作。
优点：
- 易于维护：都是使用表结构，格式一致；
- 使用方便：SQL语言通用，可用于复杂查询；
- 复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
- 读写性能比较差，尤其是海量数据的高效率读写；
- 固定的表结构，灵活度稍欠；
- 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
非关系型数据库
非关系型数据库也称之为NoSQL数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：
- 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
- 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
- 高扩展性；
- 成本低：nosql数据库部署简单，基本都是开源软件。
缺点：
- 不提供sql支持，学习和使用成本较高；
- 无事务处理；
- 数据结构相对复杂，复杂查询方面稍欠。
MySQL基础知识
MySQL简介
MySQL 是一个关系型数据库管理系统， 由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL特点
MySQL 是开源的。
MySQL 支持大型系统的数据库。可以处理拥有上千万条记录的大型数据库。 MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C 、C++、 Python 、Java 、Perl 、PHP 等。
MySQL 存储数据量较大，32 位系统表文件最大可支持 4GB ，64位系统支持最大的 表文件为 8TB。
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
MySQL分类
MySQL分为社区版：社区版是完全开源免费的，社区版也支持多种数据类型和标准的
SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了。
企业版：企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的 Oracle、DB2等也是有很大优势的。对数据库可靠性要求比较高的企业可以选择企业版。
下载MySQL
MySQL官网地址：mysql.com
MySQL的安装与卸载
可以查看一下教程：
连接MySQL
通过MySQL自带客户端工具
可通过MySQL自带的客户端工具链接MySQL。
通过客户端工具可对MySQL进行操作。
通过MySQL客户端工具的快捷方式连接MySQL
Navicat工具的使用
SQL语言
SQL语言简介
结构化查询语言(Structured Query Language)简称 SQL(发音：sequal['si:kwəl])，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL 能做什么？
- SQL 面向数据库执行查询
- SQL 可在数据库中插入新的记录
- SQL 可更新数据库中的数据
- SQL 可从数据库删除记录
- SQL 可创建新数据库
- SQL 可在数据库中创建新表
- SQL 可在数据库中创建存储过程
- SQL 可在数据库中创建视图
- SQL 可以设置表、存储过程和视图的权限
SQL 标准
SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了 SQL 正式国际标准。1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准，1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准。最新版本为 SQL2016 版。比较有代表性的几个版本：SQL86、SQL92、SQL99。
SQL语言分类
数据查询语言（DQL：Data Query Language）其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。
- SELECT
- FROM
- WHERE
- ORDER BY
- HAVING
数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。
- INSERT：添加数据
- UPDATE：更新数据
- DELETE：删除数据
数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。
- CREATE：创建数据库对象
- ALTER：修改数据库对象
- DROP：删除数据库对象
数据控制语言（DCL：Data Control Language）它的语句通过GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。
- GRANT：授予用户某种权限
- REVOKE：回收授予的某种权限
事务控制语言（TCL ：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。
- COMMIT：提交事务
- ROLLBACK：回滚事务
- SAVEPOINT：设置回滚点
注意：数据操纵语言DML（insert、update、delete）针对表中的数据 ；而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger；
SQL语言语法
- SQL语句不区分大小写，关键字建议大写。
- SQL语句可以单行或多行书写，以分号结尾。
创建与删除数据库
创建数据库
使用DDL语句创建数据库
示例：创建一个test 的数据库，并查看该数据库，以及该数据库的编码。
创建数据库：
查看数据库：
查看数据库编码：
使用Navicat创建数据库
示例：创建一个test2 的数据库。
删除数据库
使用DDL语言删除数据库
示例：删除 test 数据库
使用Navicat删除数据库
示例：删除test2数据库
选择数据库
在创建表时，需要先选择数据库。
示例：创建一个名称为 bjsxt 的数据库，编码为 utf8。
选择该数据库。
MySQL中的数据类型
整数类型
MySQL数据类型 | 含义（有符号） |
tinyint(m) | 1个字节 范围(-128~127) |
smallint(m) | 2个字节 范围(-32768~32767) |
mediumint(m) | 3个字节 范围(-8388608~8388607) |
int(m) | 4个字节 范围(-2147483648~2147483647) |
bigint(m) | 8个字节 范围(+-9.22*10的18次方) |
数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用。
例如： int(3) ，如果实际值是 2 ，如果列指定了 zerofill ，查询结果就是 002 ，左边用 0 来 填充。
浮点类型
MySQL数据类型 | 含义 |
float(m,d) | 单精度浮点型 8位精度(4字节) m总个数，d小数位 |
double(m,d) | 双精度浮点型 16位精度(8字节) m总个数，d小数位 |
字符类型
MySQL数据类型 | 含义 |
char(n) | 固定长度，最多255个字符 |
tinytext | 可变长度，最多255个字符 |
varchar(n) | 可变长度，最多65535个字符 |
text | 可变长度，最多65535个字符 |
mediumtext | 可变长度，最多2的24次方-1个字符 |
longtext | 可变长度，最多2的32次方-1个字符 |
char和varchar：
1.char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
2. varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3.text不设置长度， 当不知道属性的最大长度时，适合用text。
按照查询速度： char最快， varchar次之，text最慢。
字符串型使用建议：
1. 经常变化的字段用varchar
2. 知道固定长度的用char
3. 尽量用varchar
4. 超过255字符的只能用varchar或者text
5. 能用varchar的地方不用text
日期类型
MySQL数据类型 | 含义 |
date | 日期 YYYY-MM-DD |
time | 时间 HH:MM:SS |
datetime | 日期时间 YYYY-MM-DD HH:MM:SS |
timestamp | 时间戳YYYYMMDD HHMMSS |
二进制数据(BLOB)
- BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
- BLOB存储的数据只能整体读出。
- TEXT可以指定字符集，BLOB不用指定字符集。
创建表与删除表
创建表
使用DDL语句创建表
示例：
创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。
查看已创建的表。
使用Navicat创建表
示例：
创建employees2表。
删除表
使用DDL语句删除表
示例：
删除 employees 表。
使用Navicat删除表
示例：删除employees2表
修改表
使用DDL语句修改表
示例一：
创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。
示例二：
将 employees 表名修改为 emp。
使用Navicat修改表名
选择表按F2。
修改列名
使用DDL语句修改列名
示例：
将emp表中的employee_name修改为name。
使用Navicat修改列名
添加新列
使用DDL语句添加新列
示例：
在 emp 表中添加佣金列，列名为 commission_pct。
使用Navicat添加新列
删除指定列
使用DDL语句删除指定的列
示例：
删除 emp 表中的 commission_pct。
使用Navicat删除指定的列
MySQL中的约束
约束概述
数据库约束是对表中的数据进行进一步的限制，保证数据的正确
性、有效性和完整性。
主键约束(Primary Key) PK
主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。
外键约束(Foreign Key) FK
外键约束经常和主键约束一起使用，用来确保数据的一致性。
唯一性约束(Unique)
唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。
非空约束(Not Null)
非空约束用来约束表中的字段不能为空。
检查约束(Check)
检查约束也叫用户自定义约束，是用来检查数据表中，字段
值是否有效的一个手段，但目前 MySQL 数据库不支持检查
约束。
添加主键约束(Primary Key)
- 单一主键：使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。
- 联合主键：使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。
修改表添加主键约束
使用DDL语句添加主键约束
示例：
将 emp 表中的 employee_id 修改为主键。
主键自增长
MySQL中的自动增长类型要求：
- 一个表中只能有一个列为自动增长。
- 自动增长的列的类型必须是整数类型。
- 自动增长只能添加到具备主键约束与唯一性约束的列上。
- 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后在删除约束。
示例：
将 emp 表中的 employee_id 主键修改为自增。
使用Navicat添加主键约束
删除主键
使用DDL语句删除主键
注意：
删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。
示例：
删除emp表中的 employee_id 主键约束。
去掉自动增长：
删除主键：
使用Navicat删除主键
添加外键约束(Foreign Key)
修改表添加外键约束
使用DDL语句添加外键约束
示例一：
创建 departments 表包含 department_id 、department_name ,location_id。
示例二：
修改departments表，向department_id列添加主键约束与自动递增。
示例三：
修改 emp 表，添加 dept_id 列。
示例四：
向 emp 表中的 dept_id 列添加外键约束。
使用Navicat添加外键约束
删除外键约束
使用DDL语句删除外键约束。
示例：
删除 dept_id 的外键约束。
使用Navicat删除外键约束
添加唯一性约束(Unique)
修改表添加唯一性约束
使用DDL语句添加唯一性约束。
示例：
向 emp 表中的 name 添加唯一约束。
使用Navicat添加唯一性约束
Last update: 2023-11-24