目录
一 介绍
-
数据库: 是数据的结构化集合.
-
数据库管理系统(DBMS): 操作数据库的软件, 如Oracle,MySQL等
-
配置,权限,主从复制
-
关键字大小写不敏感, 其他不确定, 如Linux下数据库名,表名敏感, Windows上不敏感. 很多比较表达式也是不敏感的
-
MySQL是一个关系型的, 开源的数据管理系统(DBMS).
-
特性
- C,C++编写
- 跨平台
- 多线程
- 提供事物和非事物引擎
- 可嵌入式使用, 即嵌入到单个应用中
-
MySQL8新变化
- 默认
utf8mb4
编码, 之前默认latin1
编码 - 建表默认使用
InnoDB
引擎(事物的), 之前默认使用MyISAM
引擎(非事物的) - 支持角色权限管理
- 增强了
InnoDB
引擎功能 - 增强了
JSON
存储功能
- 默认
1.1 语法表示
-
[
和]
表示其中的单词或语句可选DROP TABLE [IF EXISTS] tbl_name
-
|
表示多个选项可选择一个, 可配合[]
或{}
使用#与[]使用, 选择一个或零个 TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) #与{}使用, 必须选择一个 {DESCRIBE | DESC} tbl_name [col_name | wild]
-
...
表示语句省略, 或前面语法的重复#仅表示部分语法省略 SELECT ... INTO OUTFILE #与[]使用, 表示前面的语句重复 RESET reset_option [,reset_option] ...
二 安装
官方网站给出了多种安装方式, 如源码安装,压缩包安装, 二进制安装(如.deb
文件). 尽管可以通过包管理器 (如apt
,yum
) 来安装, 可能安装的并不是最新版的. 如果使用官方仓库源, 也不能保证兼容性.
因此这里使用压缩包方式安装MySQL8, 它适用于大多数发行版. 这里本系统为Deepin.
-
环境预处理: 安装前, 先删除原有mysql, 和配置,数据等文件, 如
/etc/my.cnf
或/etc/mysql/
-
MySQL依赖: mysql依赖于
libaio
库, 安装前先检查下是否已安装, 一般情况都有#检查是否已安装, 看是否提示已安装 apt search libaio #安装 apt install libaio1 #为啥多了个1? 因为仓库中名字就是这个..
-
下载: 通过浏览器下载, 地址:Download MySQL Community Server; 或使用curl下载, 如下所示
curl -L https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz -O
-
解压: 解压后, 可以凭自己喜好存于某个位置
tar -xJvf Downloads/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
-
创建mysql用户: 官网建议, 以非root用户运行mysqld, 有助于安全性. 这里创建了
mysql
用户# 创建一个不能登录的系统用户mysql, 默认也创建了组 useradd -r -s /bin/false mysql
-
初始化mysqld : 在这个过程中会建立root用户, 生成权限表等, 并存入数据目录中, 如果不存在会自动生成, 经测试为
data
目录, 而不是官方说的mysql-files
(这是一个坑)! 初始化有以下两种方式:mysqld就是mysql服务端程序, 这里就是初始化该程序.
下面我们会通过
--user
指定mysqld以mysql
身份运行, 由于切换身份需要root权限, 所以还要加上sudo
该数据目录是mysqld存放数据库等文件的地方, 此时该文件夹的拥有者为mysql, 也就是说mysqld以mysql身份运行时只能写该目录内的数据, 大大保证了安全性
-
--initialize
: 生成root密码, 打印在控制台sudo bin/mysqld --initialize --user=mysql
-
-initialize-insecure
: root账户无密码bin/mysqld --initialize-insecure --user=mysql
-
-
运行mysqld : 同样的, 以
mysql
身份运行, 这里的mysqld_safe一个启动mysqld方便的工具.sudo bin/mysqld_safe --user=mysql &
-
修改root密码:
mysql
是一个命令行客户端, 在bin目录下, 最好设置环境变量, 这里略.#root有密码的登录方式 mysql -u root -p #root无密码的登录方式 mysql -u root --skip-password #进入后修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';
修改完后请关闭msyqld, 怎么关? 思路是使用
ps
的到进程号,kill
杀死它 -
增加安全性(可选) : mysql提供了
mysql_secure_installation
脚本来配置msyqld安全方面的配置, 功能如-
设置root账户密码
-
阻止外部访问root账户
-
删除匿名用户
-
等等
使用后最直观感受是, 用户密码不能是简单的123456了, 必须复杂… 开发环境下不建议使用.
#直接运行即可 mysql_secure_installation
-
-
自启配置: 我们要自启的是mysqld, 但是我不熟悉它的配置, 但是它提供了一个
mysql.server
, 能够方便的开启和关闭mysqld. 该脚本被写死了, 它规定mysql必须存在于/usr/local/
下, 目录名mysql
, 这里我使用符号连接来解决:ln -s $PWD/mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local/mysql
deepin使用systemd来管理启动程序的, 它兼容Sys V, 因此也可使用Sys V的方式来让mysql加入自启, 如:
cp support-files/mysql.server /etc/init.d/mysql.server
但新时代就用新方法, 这里通过Systemd的方式加入自启, 在
/usr/lib/systemd/system/
目录下创建配置文件mysqld.service
[Unit] Description=MySQL Server After=network.target local-fs.target remote-fs.target [Service] Type=forking PIDFile=/usr/local/mysql/data/sidian-PC.pid ExecStart=/usr/local/mysql/support-files/mysql.server start ExecStop=/usr/local/mysql/support-files/mysql.server stop ExecReload=/usr/local/mysql/support-files/mysql.server restart User=mysql [Install] WantedBy=multi-user.target
要注意点是, PIDFile的名字一般为你电脑的域名, 如我的
sidian-PC
, 通过hostname
命令获取.User
选项规定mysql以mysql身份运行.接下来启动它
sudo systemctl enable mysqld.service # 加入自启 sudo systemctl start mysqld.service # 现在启动mysqld
大功告成!
参考
- Installing and Upgrading MySQL : 简述安装的大致步骤
- Postinstallation Setup and Testing : 初始化设置
三 基础
mysql
是一个连接mysql服务端的客户程序, 提供命令行界面.
3.1 连接与断开
-
连接到远程主机
shell> mysql -h host -u user -p Enter password: ********
-
连接到本地
shell> mysql -u user -p
-
在mysql中退出
-
quit # 或 exit
3.2 查询常识
-
SQL语句后接
;
, 一些语句不用,如exit
,use
-
查询时, 语句在server上执行, client显示结果
-
列名通常为表列名, 也可以是表达式.
-
大小写不敏感
-
查询时可以进行表达式计算
-
查看当前用户
select user();
-
查看当前被选择的数据库
select database();
-
布尔运算以0和1作为返回值
3.2 创建和使用数据库
-
SHOW DATABASES;
显示你有权限查看的数据库 -
USE test
使用(选择)一个数据库 -
赋予数据库全部权限给某个用户
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
-
创建数据库
mysql> CREATE DATABASE menagerie;
-
SHOW TABLES;
查看所选数据库的表 -
创建表
mysql> CREATE TABLE pet ( name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE );
-
describe pet;
查看表结构 -
load data
语句可以从文件中读取固定格式的数据并插入表中, 感觉不太满意, 用法略. 当然, 以mysql
身份运行时只能访问data
数据目录.
3.4 查询进阶
-
select
常用形式SELECT what_to_select #选出什么内容,a list of columns或 * 表示所有列 FROM which_table # 表名 WHERE conditions_to_satisfy; #选择条件, 可选
-
比较
- 字符比较是大小写不敏感的
- 是否为
null
, 只能使用操作符is not null
或is null
- 0和NULL表示false, 其他的true
-
选择某一列时, 可能会重复, 可通过
distinct
去掉SELECT DISTINCT owner FROM pet;
-
通过
where
限制行范围, 设置column lists选择特定列 -
排序
order by
-
默认递增排序, 大小写不敏感
SELECT name, birth FROM pet ORDER BY birth;
-
可指定大小写敏感
ORDER BY BINARY col_name
-
可指定降序排序
SELECT name, birth FROM pet ORDER BY birth DESC;
-
排序多列, 不同升降序
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
-
-
日期计算: 提供了相关的函数操作日期
-
计算年龄
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
-
选择下一个月生日的宠物
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
-
-
NULL值: 表示没有值, 注意,
0
和''
是有值的(not null)- NULL参与的算术比较总是NULL
- 判断是否为NULL, 使用操作数
is null
和is not null
- 在
group by
中, 两个NULL被视为相等 - 在
order by
中, NULL被当作最小来排序
-
模式匹配: 支持标准SQL模式匹配和正则表达式, 默认大小写不敏感 ,
-
标准SQ: 操作符使用
LIKE
或NOT LIKE
, 仅当模式匹配整个字符串时true_
匹配单个字符%
匹配0到多个任意字符
-
正则: 使用
REGEXP_LIKE()
函数或REGEXP
orRLIKE
操作符, 成功匹配字符串内一段内容也true, 如果想匹配整个字符串, 使用边界定位符, 如^
,$
注意大小写也不敏感, 有多种方法解决, 见Pattern Matching
-
-
count(*)
计算组的个数, 通过group by
分组, 没有时视整个表为一组. -
多表连接
mysql> SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
from
语句上指定多个表连接成一张表,inner join
或join
(内连接)表示连接类型, 还有外连接,on
指定连接条件. 内连接表示两张表中的记录必须满足条件才能放入结果表中.除此之外, 还可与自身连接
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL;
取个不同别名即可当作两张表使用
3.5 获取数据库,表信息
-
显示所有数据库
SHOW DATABASES
-
当前被选择的数据库
SELECT DATABASE();
-
显示被选择数据库的所有表
SHOW TABLES;
-
显示表结构
mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
其中,
Field
表示字段名(列名),Type
表示字段类型,Null
表示字段是否允许为空,key
表示该字段是否被索引, 如主键PRI
,Default
表示字段默认值,Extra
表示额外信息, 如auto_increment
等, 详细见show columns -
显示建表语句
SHOW CREATE TABLE
-
显示表的索引
SHOW INDEX FROM tbl_name
3.6 批处理模式
即一次执行文件中的大量sql语句
-
从文件中读取sql语句, 结果输出到文件中(默认到控制台上)
shell> mysql -h host -u user -p < batch-file >mysql.out Enter password: ********
- 加上
--force
, 即使部分语句错误也不会导致脚本停止 -t
输出内容与交互模式一致-v
输出被执行的语句
- 加上
-
mysql内读取文件
mysql> source filename; mysql> \. filename
3.7 通用查询例子
-
select
,from
,where
语句中都可使用子查询, 子查询又分为相关子查询和非相关子查询, 相关子查询使用到了外层查询的内容. 通常情况下子查询可转化为多表连接来解决, 并且效率更高 -
limit
限制查询个数 -
将查询结果存入变量中, 然后使用, 如
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
-
InnoDB支持外键约束, 其他引擎无作用, 仅用作注释
-
auto_increment
列, 在没有被赋值或赋null时, 会以递增的方式自动设置 ,从1开始. 产生的值可通过LAST_INSERT_ID()
获取.
四 MySQL Server管理
-
server系统变量: 系统变量用来控制server行为, 每个系统变量都有默认值.
select
语句查看,set
语句态修改. 具体使用暂时不会. -
mysqld是mysql服务端程序, 默认配置见
mysqld --verbose --help
. 配置文件读取顺序如下/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf
-
查看当前server系统变量
mysql> SHOW VARIABLES;
-
查看当前server状态
mysql> SHOW STATUS;
-
时区
-
数据目录: mysql server管理的数据都会被存入数据目录中, 在这里为
mysql-x.x.x/data
目录, 该目录下的大致内容如下所示- 所有的数据库文件
- 日记
- SSL和RSA证书及秘钥
- server进程id文件(
.pid
)
五 权限控制
-
用户的标识(identity)由
host
(主机)和username
(用户名)决定, 而不是username
决定, 然后授权也是根据这个标识授权的. 简而言之, 即使使用同一个用户名在不同地方访问mysql server可能会有不同的权限, 可通过以下命令打印该标识的权限:SHOW GRANTS FOR 'joe'@'office.example.com'; SHOW GRANTS FOR 'joe'@'home.example.com';
-
账户密码存在
mysql
数据库的user
表中, 密码也不是明文储存, 加了密的. -
账户名: 由
host
和username
组成, 形式大致如下'user_name'@'host_name'
- 名字合法的情况下可省略引号
host_name
可以使用sql的通配符- 仅给出用户名时, 如
user_name
, 相当于'user_name'@'%'
即允许任何主机访问 - 用户名为空时, 如
''@'localhost'
是匿名用户 - 允许部分主机访问可使用通配符, 如
'user_name'@'198.51.100.%'
或者使用子网掩码, 如'david'@'198.51.100.0/255.255.255.0'
-
创建账户: 同时给出账户名和密码
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'your_password';
-
角色: 略
-
授权: 这里直接给出授权一个数据库所有权限的例子
GRANT ALL ON db_name.* TO 'user_name'@'host_name';
然后让服务器重新加载权限表
FLUSH PRIVILEGES;
六 数据类型
-
数据类型大致分类:
- numeric types
- date and time types
- string (character and byte) types
- spatial types
JSON
data type.
-
描述约定
M
, 在数值类型中, 表示最大显示宽度; 在浮点与定点数值类型中, 表示存储时的数字个数(精度); 在字符类型中, 表示字符长度, 但M的最大值取决于数据类型和字符编码.D
, 用在浮点和定点类型中, 表示小数点后数字的个数(scale). 最大值可为30, 但不应该超过M-2
fsp
用于TIME
,DATETIME
和TIMESTAMP
类型中, 表示秒小数部分的位数. 选值范围0-6, 默认0, 与标准SQL默认的6相反.
-
[
和]
表示类型定义中可选的部分
一些数据类型是同一个, 仅为了兼容性, 如兼容标准SQL类型.
6.1 Numeric
-
修饰属性
- 显示宽度属性,
zerofill
属性将被弃用 - 允许
unsigned
和signed
属性(默认), 修饰在浮点数上的方式将被弃用
- 显示宽度属性,
-
整形数值类型
类型 位数 描述 TINYINT
1 SMALLINT
2 MEDIUMINT
3 INT
,INTEGER
4 BIGINT
8 后接
(M)
控制显示宽度, 无用且弃用. -
定点数:
DECIMAL[(M[,D])]
,NUMERIC[(M[,D])]
两者无区别, 存精确的值, 适用于保存货币数据. 默认M
为10,D
为0 -
浮点数:
FLOAT
表示4字节单精度浮点数,DOUBLE
表示8字节双精度浮点数. 浮点数是近似值, 受限于平台实现. 一般进度分别为7,15位, 也可通过(M,D)
限制它(一般不限制). 未来将被弃用. -
位值(
BIT(M)
): 存二进制数据, 略 -
BOOL
,BOOLEAN
: 与TINYTIN(1)
无区别, 但非0视作true, 0视为false. 注意关键字true
与false
分别对应0和1, 2不等于true
!!
6.2 时间
有的类型后接(fsp)
, 表示秒的小数位数, 可选值0-6, 默认0.
类型如下
类型 | 范围 |
---|---|
DATE | ‘1000-01-01’ to ‘9999-12-31’ |
TIME | ‘-838:59:59.000000’ to ‘838:59:59.000000’ |
DATETIME | ‘1000-01-01 00:00:00.00000’ to ‘9999-12-31 23:59:59.999999’ |
TIMESTAMP | ‘1970-01-01 00:00:01.000000’ UTC to '2038-01-19 03:14:07.999999’utc |
YEAR | 1901 to 2155 |
datetime
与timestamp
两者都能获取和存入
YYYY-MM-DD hh:mm:ss
类型的时间, 但是timestamp
实际上存入的是从'1970-01-01 00:00:00' UTC
到现在的时间戳. 当取出时, 将时间戳根据当前会话时区转化为时间, 存入时将时间转化为UTC时区的时间戳后才存入.因此, 改变当前时区后, 相同的
timestamp
值得到不同的时间, 因为时区不同.
6.3 字符串
-
修饰属性
character set
或charset
指定编码collate
指定排序顺序
-
字符串: 以下类型中
M
均表示字符长度. 说三遍, 字符,字符,字符!!!类型 描述 char(M)
或character(M)
定长字符串. M
范围0到255个, 默认1varchar(M)
可变长字符串. 字节大小不能超过216-1,
通过一个2字节前缀记录长度.
一个例子, 假设每个utf-8字符占3字节, 则M
最大值为21833.tinytext
可变长字符串, 字节大小不超过28-1, 1字节前缀记录长度 text(M)
可变长字符串, 字节大小不超过216-1, 2字节前缀记录长度
和varchar(M)
一模一样??!!mediumtext
可变长字符串, 字节代销不超过224-1, 3字节前缀 longtext
可变, 不超过232-1, 4字节前缀 -
字节串: 即存二进制数据的, 以下类型中
M
就是表示字节个数类型 描述 binary(M)
定长字节串, M
范围0-255, 默认1varbinary(M)
可变字节串, 字节数(M)不超过216-1, 2字节前缀 tinyblob
可变字节串, M不超过28-1, 1字节前缀 blob
可变字节串, M不超过216-1, 2字节前缀 mediublob
可变字节串, M不超过224-1, 3字节前缀 longblob
可变字节串, M不超过232-1, 4字节前缀
text与blob系列类型: 是专门用来存储大数据的, 会不会影响表查询效率? 我想应该不会吧, mysql应该会对该类型做相应的优化.
enum
,set
略
其他
-
对于大文件,经常访问的可以存入数据库,数据库有对应字段,应该是优化过了的,为大字段单独存一张表应该没必要。但是不经常访问,或则实在太大了,则放入文件中。
-
utf8 vs. utf8mb4 : UTF-8是一种可变长编码方式, 字符可由1到4个字节编码. 但在MySQL中的UTF-8实际上最多只存3个字节, 实际上是utf8mb3, 仅涵盖BMP编码集; 而utf8mb4能够存四字节编码的字符.
-
导入导出方法:
LOAD DATA
andSELECT ... INTO OUTFILE
, 受secure_file_priv变量影响
其他的以后补充了