MySQL学习笔记

最新推荐文章于 2023-02-27 00:56:05 发布

千霜

最新推荐文章于 2023-02-27 00:56:05 发布

阅读量490

点赞数 1

分类专栏：数据库文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/jdbdh/article/details/93389811

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一介绍

数据库: 是数据的结构化集合.
数据库管理系统(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 or RLIKE 操作符, 成功匹配字符串内一段内容也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;
```
时区
- 系统时区: 默认使用操作系统的时区
- 会话时区: 客户端使用的时区, 默认为系统时区. 会影响 NOW() orCURTIME()的值, 不会影响UTC_TIMESTAMP() 和DATE, TIME, or DATETIME 字段的值, 这些字段的数据类型是与时区无关的. 但会影响TIMESTAMP 字段的值, 存储时会转化为UTC再存储, 取出时转化为会话时区再显示.
数据目录: 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!!

类型	位数	描述
`TINYINT`	1
`SMALLINT`	2
`MEDIUMINT`	3
`INT`,`INTEGER`	4
`BIGINT`	8

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个, 默认1
`varchar(M)`	可变长字符串. 字节大小不能超过2¹⁶-1, 通过一个2字节前缀记录长度. 一个例子, 假设每个utf-8字符占3字节, 则`M`最大值为21833.
`tinytext`	可变长字符串, 字节大小不超过2⁸-1, 1字节前缀记录长度
`text(M)`	可变长字符串, 字节大小不超过2¹⁶-1, 2字节前缀记录长度和`varchar(M)`一模一样??!!
`mediumtext`	可变长字符串, 字节代销不超过2²⁴-1, 3字节前缀
`longtext`	可变, 不超过2³²-1, 4字节前缀

字节串: 即存二进制数据的, 以下类型中M就是表示字节个数

类型	描述
`binary(M)`	定长字节串, `M`范围0-255, 默认1
`varbinary(M)`	可变字节串, 字节数(M)不超过2¹⁶-1, 2字节前缀
`tinyblob`	可变字节串, M不超过2⁸-1, 1字节前缀
`blob`	可变字节串, M不超过2¹⁶-1, 2字节前缀
`mediublob`	可变字节串, M不超过2²⁴-1, 3字节前缀
`longblob`	可变字节串, M不超过2³²-1, 4字节前缀

text与blob系列类型: 是专门用来存储大数据的, 会不会影响表查询效率? 我想应该不会吧, mysql应该会对该类型做相应的优化.

enum, set略

其他

对于大文件，经常访问的可以存入数据库，数据库有对应字段，应该是优化过了的，为大字段单独存一张表应该没必要。但是不经常访问，或则实在太大了，则放入文件中。
utf8 vs. utf8mb4 : UTF-8是一种可变长编码方式, 字符可由1到4个字节编码. 但在MySQL中的UTF-8实际上最多只存3个字节, 实际上是utf8mb3, 仅涵盖BMP编码集; 而utf8mb4能够存四字节编码的字符.
导入导出方法: LOAD DATA and SELECT ... INTO OUTFILE , 受secure_file_priv变量影响

其他的以后补充了

参考

MySQL 8.0 Reference Manual

千霜

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

一介绍数据库: 是数据的结构化集合.数据库管理系统(DBMS): 操作数据库的软件, 如Oracle,MySQL等配置,权限,主从复制关键字大小写不敏感, 其他不确定, 如Linux下数据库名,表名敏感, Windows上不敏感. 很多比较表达式也是不敏感的MySQL是一个关系型的, 开源的数据管理系统(DBMS).特性C,C++编写跨平台多线程提供...
复制链接

扫一扫