day31-mysql
day31-mysql
mysql
mysql配置文件详解
配置文件所在路径：C:\ProgramData\MySQL\MySQL Server 8.0
常用的配置信息：
会被客户端读取的配置信息：
[client]
port:3306
socket:/var/run/mysql/mysql.sock
数据库的备份的配置信息
[mysqldump]
max_allowed_packet=16M
以上参数会被MySql客户端读取
参数说明：
port: MySQL客户端连接服务器端时使用的端口号，默认为3306
socket: 套接字文件所在的目录
quick: 支持较大的数据库转储，导出非常巨大的表时需要此项
max_allowed_packet: 服务所能处理的请求包的最大大小以及服务所能处理的最大请求大小（当与大的BLOB字段一起工作时相当必要），每个连接独立的大小，大小动态增加
- [mysqld]
[mysqld]
# mysqld程序在启动后将在给定UNIX/Linux账户下执行。mysqld必须从root账户启动才能在启动后切换到另一个账户下执行。mysqld_safe脚本将默认使用user=mysql选项来启动mysqld程序
user=mysql
# 指定mysql安装的绝对路径
basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
# 指定mysql数据存放的绝对路径
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
# 服务端口号，默认为3306
port=3306
# mysql服务的唯一编号，每个mysql服务的id需唯一
server-id=1
# socke文件所在目录
socket=/var/run/mysql/mysql.sock
# 数据库默认字符集，主流字符集支持一些特殊表情符号（特殊表情符号占用4个字节）
character-set-server=utf8mb4
# 数据库字符集对应一些排序等规则，要和character-set-server对应
collation-server=utf8mb4_general_ci
# 设置client连接mysql 的字符集，防止乱码
init_connect='SET NAMES utf8mb4'
# 是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names=1
# 用于指定索引缓冲区的大小
key_buffer_size=16M
# 设置一次消息传输的最大值
max_allowed_packet=8M
# 仅仅允许使用键值的UPDATES和DELETES
no_auto-rehash
# 表示sql模式的参数，通过这个参数可以设置检验sql语句的严格程度
sql_mode=TRADITIONAL
常用的Mysql图形化管理工具
MySQL Workbench: 官方提供的的图形化管理工具，社区版完全免费，商业版收费
phpMyAdmin
Navicat: 有免费版本提供
SQLyong
MySQLDumper
mysql安装完成后的目录结构介绍
- mysql的安装目录结构
- mysql的配置文件目录
bin:用于放置一些可执行文件，如mysql.ext、mysqld.ext、mysqlshow.exe
docs: 存放一些文档
data: 登录数据库后，可使用 SHOW GLOBAL VARIABLES LIKE "%Datadir%"; 命令查看Data目录位置
Data目录中用于放置一些日志文件以及数据库。
include: 用于防止一些头文件，如：mysql.h
lib: 用于防止一系列库文件
share: 用于放字符集、语言等信息
my.ini：是MySQL默认使用的配置文件，一般情况下，只要修改my.ini配置文件中的内容就可以对MySQL进行配置
mysql的基本操作
对数据库进行查询和修改操作的语言叫做SQL（Structured Query Language，结构化查询语言）
sql包含4各部分
- 数据定义语言（Data Definition Language,DDL）
用来创建或删除数据库以及表等对象
drop: 删除数据库和表对象
create: 创建数据库和表等对象
alter: 修改数据库和表等对象的结构
- 数据操作语言（Data Manipulation Language,DML）
用来变更表中的记录
select: 查询
insert: 插入
update: 修改
delete: 删除
- 数据查询语言（Data Query Language,DQL）
select
- 数据控制语言（Data Control Language,DCL）
用于确认或者取消对数据库中的数据进行的变更。对数据库中的用户设定权限。
grant: 赋予用户操作权限
revoke: 取消用户的操作权限
commit: 确认对数据库中的数据进行的变更
rollback: 取消对数据库中的数据进行的变更
数据库的基本语法
- 查看数据库
show databases [like '数据库名']
like 用于匹配指定的数据库名称，like从句可以部分匹配，也可以完全匹配
- show databases
information_schema
mysql
performance_schema
sakila
sys
world
information_schema:主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等
mysql: MySQL的核心数据库，类似于SQL Server中的master表，主要负责存储数据库用户、用户访问权限等，MySQL自己需要使用的控制和管理信息。常用的比如在mysql数据库的user表中修改root用户密码
performance_schema: 主要用于收集数据库服务器性能参数
sakila: MySQL提供的样例数据库，该数据库共有16张彪，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据包
sys: sys数据库主要提供了一些视图，数据都来自performation_schema,主要是让开发者和使用者更方便地查看性能问题
world:world数据库是MySQL自动创建的数据库，该数据库只包含3张数据表，分别保存城市，国家和国家使用的语言等内容
- 创建数据库
# 创建数据库
create database database_name;
# 常见数据库的时候判断此数据库是否存在
create database if not exists database_name;
# 创建数据库并指定字符集和校对规则
create database if not exists test_db
default character set utf8
-- 因为版本原因
-- default collate utf8_chinese_ci; 显示找不到
default collate utf8_general_ci;
- 查看数据库的定义声明
show create dtabase test_db;
CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */
- 修改数据库结构
alter database 数据库名
default character set ''
default collate '校对规则'
- 删除数据库
drop database [if exists] 数据库名
注意：mysql安装后，系统会自动创建名为information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关信息，如果删除了这两个数据库，MySQL将不能正常工作。
- 选择数据库
use 数据库名
只有使用use语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作。
- mysql注释
单行注释
#注释内容
-- 注释内容
区别：#后面可以直接加注释，-- 后面需要加一个空格才能添加注释
多行注释
/*
注释内容
*/
-
Mysql命令规则
- sql关键字和函数名
sql的关键字和函数名不区分大小写- 数据库名、表明、视图名
mysql用服务器主机的底层文件系统所包含的目录和文件来表示数据库和表。因此，数据库名和表名的默认大小写取决于服务器主机的操作系统在命名方面的规定。- 存储程序的名字
存储函数、存储过程和时间的名字都不区分大小写。 触发器的名字要区分大小写- 列名和索引名
不区分大小写- 别名的名字
区分大小写 -
阿里巴巴java开发手册的MySql建表规约
强制：表名、字段名必须使用小写字母或数字
禁止出现数字开头
禁止两个下划线中间只出现数字
- 如何查看MySQL系统帮助？
help 查询内容
数据库的设计
数据库设计是根据用户的需求，在某一具体的数据库管理系统上，设计数据库结构和建立数据库的过程。
数据库设计概述
数据库设计就是根据业务系统的具体需求，结合我们所选用的数据库，建立好表结构及表与表之间的管理关系，为这个业务系统构造出最优秀的数据存储模型的过程。
- 糟糕的数据库设计
1. 访问数据效率低下
2. 存在大量的数据冗余，浪费存储空间
3. 跟新和检索数据时会出现许多问题
- 良好的数据库设计表现在以下几方面
1. 访问效率高
2. 减少数据冗余，节省存储空间，便于进一步扩展
3. 可以使应用程序的开发变得更容易
数据可设计的基本步骤
- 软件项目的开发周期
1. 需求分析
2. 概要设计
3. 逻辑设计/详细设计
4. 代码编写
5. 软件测试
6. 安装部署
- 数据库设计6个阶段
1. 需求分析
明确用户对系统的需求：数据需求和业务处理需求
2. 概要设计
综合、归纳与抽象用户需求，形成一个具体DBMS的概念模型，绘制数据库的E-R图
3. 逻辑结构设计
由E-R图转换为多张表，确定各表的主外键，应用数据库设计的三大范式进行审核，对其优化
4. 物理设计阶段
选择具体的数据库实现
5. 数据库实施阶段
编制与调试应用程序，组织数据入库，并进行测试
6. 数据库运行和维护阶段
正式运行，发现问题，解决问题。
VIsio下载及安装
绘制E-R图的软件
数据类型
mysql的数据类型有5种：
整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制类型
- 数值类型
整数类型：
tinyint 很小的整数 1byte
smallint 小的整数 2byte
mediumint 中等大小的整数 3byte
int 普通大小的整数 4byte
bigint 大整数 8byte
浮点数类型：
float 单精度浮点数 4byte
double 双精度浮点数 8byte
定点数类型：
decimal(M,D)压缩的严格定点数 M+2byte
说明
显示宽度和数据类型的取值范围是无关的。
显示宽度知识指明mysql最大可能显示的数子个数，数值的位数小于指定的宽度时会由空格填充，如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。
decimale类型
double实际上是以字符串的形式存放的
decimal可能的最大取值范围与double相同，但是有效的取值范围由M和D决定，以字符串存储
- 日期/时间类型
year 年 1byte
time 时间（HH:MM:SS） 3byte
date 日期（YYYY-MM-DD）3byte
datetime 日期时间（YYYY-MM-DD HH:MM:SS）8byte
timestamp 日期时间（YYYY-MM-DD HH:MM:SS）4byte
datetime与timestamp的区别
timestamp的取值范围小于datetime的取值范围
datetime: '1000-01-01 00：00：00'～'9999-12-3 23：59：59'
timestamp: '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC
存储的空间大小不同，timestamp：4字节 datetime：8字节
存在时间的格式不同：
datetime在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关
timestamp是以UTC(世界标准时间)格式保存，存储时对当前失去进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。l
- 字符串类型
char(M) 固定长度非二进制字符串 M字节，1<=M<=255
varchar(M) 变长非二进制字符串 L+1字节，L<=M和 1<=M<=255
tinytext 非常小的非二进制字符串 L+1字节，L<2^8
text 小的非二进制字符串 L+2字节，L<2^16
mediumtext 中等大小的非二进制字符串，L+3字节，L<2^24
longtext 大的非二进制字符串 L+4 L<2^32
enum 枚举类型，只能有一个枚举字符串值 1或2字节，取决于枚举值的数目（最大值为65535）
set 一个设置，字符串对象可以有0个或多个set成员 1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）
char和varchar类型
char(M) 固定长度字符串，当保存时，右侧填充空格以达到指定的长度
varchar(M) 长度可变的字符串，M表示最大列的长度，占用的空间为实际长度加1（字符串结束符号），
char 会自动删除插入数据的尾部空格
varchar 不会删除尾部空格
char 是固定长度，处理速度比varchar的速度块，缺点浪费存储空间
enum类型
格式：
字段名 enum('值1','值2'......)
enum类型的字段在取值时，能在指定的枚举列表种获取，而且一次只能取一个。
enum值在内部用整数表示，每个枚举值均有一个索引值
set类型
格式
set('值1','值2'......)
set是一个字符串对象，可以有零个或多个值，set最多可以有64个成员，值为表创建时规定的一列值。
- 二进制类型
bit(M) 位 大约(M+7)/8字节
binary(M) 固定长度二进制字符串 M字节
varbinary(M) 可变长度二进制字符串 M+1字节
tinyblob(M) 非常小的blob L+1字节，L<2^8
blob 小blob L+2字节，L<2^16
mediumblob 中等大小blob L+3字节，L<2^24
longblob 非常大的blob L+4字节，L<2^32
mysql数据类型的选择
为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型
mysql系统变量（查看和修改）
mysql中变量分为：系统变量和用户自定义变量
系统变量以@@开头，用户自定义变量以@开头
服务器维护着两种系统变量：全局变量（global variables）和会话变量（session variables）
全局变量：影响mysql服务的整体运行方式
会话变量：影响具体客户端连接的操作
每个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，mysql服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量的拷贝。
-
查看系统变量
- 查看所有的全局变量
show global variables;- 查看当前会话相关的所有会话变量以及全局变量
show session variables; -
设置系统变量
-
修改mysql源代码，然后对mysql源代码重新编译
-
在mysql 配置文件中修改mysql系统变量的值，需要重新启动mysql服务
-
在mysql运行期间，使用set命令重新设置系统变量的值
更改全局变量，需要具有super权限 格式 set @@global.innodb_file_per_table=default; 更改全局变量只影响更改后连接客户端的相应会话变量，不会影响目前已连接的客户端会话变量。
-
浙公网安备 33010602011771号