MySQL学习笔记
一 介绍
数据库: 是数据的结构化集合.
数据库管理系统(DBMS): 操作数据库的软件, 如Oracle,MySQL等
配置,权限,主从复制
关键字大小写不敏感, 其他不确定, 如Linux下数据库名,表名敏感, Windows上不敏感. 很多比较表达式也是不敏感的
MySQL是一个关系型的, 开源的数据管理系统(DBMS).
特性
C,C++编写跨平台多线程提供事物和非事物引擎可嵌入式使用, 即嵌入到单个应用中MySQL8新变化
默认utf8mb4编码, 之前默认latin1编码建表默认使用InnoDB引擎(事物的), 之前默认使用MyISAM引擎(非事物的)支持角色权限管理增强了InnoDB引擎功能增强了JSON存储功能1.1 语法表示
[ 和 ]表示其中的单词或语句可选
DROP TABLE [IF EXISTS] tbl_name 1
|表示多个选项可选择一个, 可配合[]或{}使用
#与[]使用, 选择一个或零个 TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) #与{}使用, 必须选择一个 {DESCRIBE | DESC} tbl_name [col_name | wild] 1234
...表示语句省略, 或前面语法的重复
#仅表示部分语法省略 SELECT ... INTO OUTFILE #与[]使用, 表示前面的语句重复 RESET reset_option [,reset_option] ... 1234
二 安装
官方网站给出了多种安装方式, 如源码安装,压缩包安装, 二进制安装(如.deb文件). 尽管可以通过包管理器 (如apt,yum) 来安装, 可能安装的并不是最新版的. 如果使用官方仓库源, 也不能保证兼容性.
因此这里使用压缩包方式安装MySQL8, 它适用于大多数发行版. 这里本系统为Deepin.
环境预处理: 安装前, 先删除原有mysql, 和配置,数据等文件, 如/etc/my.cnf或/etc/mysql/
MySQL依赖: mysql依赖于libaio库, 安装前先检查下是否已安装, 一般情况都有
#检查是否已安装, 看是否提示已安装 apt search libaio #安装 apt install libaio1 #为啥多了个1? 因为仓库中名字就是这个.. 1234
下载: 通过浏览器下载, 地址: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 1
解压: 解压后, 可以凭自己喜好存于某个位置
tar -xJvf Downloads/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz 1
创建mysql用户: 官网建议, 以非root用户运行mysqld, 有助于安全性. 这里创建了mysql用户
# 创建一个不能登录的系统用户mysql, 默认也创建了组 useradd -r -s /bin/false mysql 12
初始化mysqld : 在这个过程中会建立root用户, 生成权限表等, 并存入数据目录中, 如果不存在会自动生成, 经测试为data目录, 而不是官方说的mysql-files(这是一个坑)! 初始化有以下两种方式:
mysqld就是mysql服务端程序, 这里就是初始化该程序.
下面我们会通过--user指定mysqld以mysql身份运行, 由于切换身份需要root权限, 所以还要加上sudo
该数据目录是mysqld存放数据库等文件的地方, 此时该文件夹的拥有者为mysql, 也就是说mysqld以mysql身份运行时只能写该目录内的数据, 大大保证了安全性
--initialize: 生成root密码, 打印在控制台
sudo bin/mysqld --initialize --user=mysql 1
-initialize-insecure: root账户无密码
bin/mysqld --initialize-insecure --user=mysql 1
运行mysqld : 同样的, 以mysql身份运行, 这里的mysqld_safe一个启动mysqld方便的工具.
sudo bin/mysqld_safe --user=mysql & 1
修改root密码: mysql是一个命令行客户端, 在bin目录下, 最好设置环境变量, 这里略.
#root有密码的登录方式 mysql -u root -p #root无密码的登录方式 mysql -u root --skip-password #进入后修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password'; 123456
修改完后请关闭msyqld, 怎么关? 思路是使用ps的到进程号, kill杀死它
增加安全性(可选) : mysql提供了mysql_secure_installation脚本来配置msyqld安全方面的配置, 功能如
设置root账户密码
阻止外部访问root账户
删除匿名用户
等等
使用后最直观感受是, 用户密码不能是简单的123456了, 必须复杂… 开发环境下不建议使用.
#直接运行即可 mysql_secure_installation 12
自启配置: 我们要自启的是mysqld, 但是我不熟悉它的配置, 但是它提供了一个mysql.server, 能够方便的开启和关闭mysqld. 该脚本被写死了, 它规定mysql必须存在于/usr/local/下, 目录名mysql, 这里我使用符号连接来解决:
ln -s $PWD/mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local/mysql 1
deepin使用systemd来管理启动程序的, 它兼容Sys V, 因此也可使用Sys V的方式来让mysql加入自启, 如:
cp support-files/mysql.server /etc/init.d/mysql.server 1
但新时代就用新方法, 这里通过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 1234567891011121314
要注意点是, PIDFile的名字一般为你电脑的域名, 如我的sidian-PC, 通过hostname命令获取.
User选项规定mysql以mysql身份运行.
接下来启动它
sudo systemctl enable mysqld.service # 加入自启 sudo systemctl start mysqld.service # 现在启动mysqld 12
大功告成!
参考
Installing and Upgrading MySQL : 简述安装的大致步骤Postinstallation Setup and Testing : 初始化设置三 基础
mysql是一个连接mysql服务端的客户程序, 提供命令行界面.3.1 连接与断开
连接到远程主机
shell> mysql -h host -u user -p Enter password: ******** 12
连接到本地
shell> mysql -u user -p 1
在mysql中退出
quit # 或 exit 123
3.2 查询常识
SQL语句后接; , 一些语句不用,如 exit,use
查询时, 语句在server上执行, client显示结果
列名通常为表列名, 也可以是表达式.
大小写不敏感
查询时可以进行表达式计算
查看当前用户
select user(); 1
查看当前被选择的数据库
select database(); 1
布尔运算以0和1作为返回值
3.2 创建和使用数据库
SHOW DATABASES; 显示你有权限查看的数据库
USE test 使用(选择)一个数据库
赋予数据库全部权限给某个用户
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host'; 1
创建数据库
mysql> CREATE DATABASE menagerie; 1
SHOW TABLES; 查看所选数据库的表
创建表
mysql> CREATE TABLE pet (name VARCHAR(20),owner VARCHAR(20), species VARCHAR(20),sex CHAR(1),birth DATE,death DATE); 12345678
describe pet; 查看表结构
load data语句可以从文件中读取固定格式的数据并插入表中, 感觉不太满意, 用法略. 当然, 以mysql身份运行时只能访问data数据目录.
3.4 查询进阶
select常用形式
SELECT what_to_select #选出什么内容,a list of columns或 * 表示所有列 FROM which_table # 表名 WHERE conditions_to_satisfy; #选择条件, 可选 123
比较
字符比较是大小写不敏感的是否为null, 只能使用操作符is not null或is null0和NULL表示false, 其他的true选择某一列时, 可能会重复, 可通过distinct去掉
SELECT DISTINCT owner FROM pet; 1
通过where限制行范围, 设置column lists选择特定列
排序order by
默认递增排序, 大小写不敏感
SELECT name, birth FROM pet ORDER BY birth; 1
可指定大小写敏感
ORDER BY BINARY col_name 1
可指定降序排序
SELECT name, birth FROM pet ORDER BY birth DESC; 1
排序多列, 不同升降序
SELECT name, species, birth FROM pet ORDER BY species, birth DESC; 12
日期计算: 提供了相关的函数操作日期
计算年龄
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet; 123
选择下一个月生日的宠物
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)); 12
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'; 123456
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; 12345
取个不同别名即可当作两张表使用
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 | | +---------+-------------+------+-----+---------+-------+ 1234567891011
其中, 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: ******** 12 加上--force, 即使部分语句错误也不会导致脚本停止-t输出内容与交互模式一致-v输出被执行的语句
mysql内读取文件
mysql> source filename; mysql> . filename 12
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; 12
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; 1
查看当前server状态
mysql> SHOW STATUS; 1
时区
系统时区: 默认使用操作系统的时区会话时区: 客户端使用的时区, 默认为系统时区. 会影响 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'; 12
账户密码存在mysql数据库的user表中, 密码也不是明文储存, 加了密的.
账户名: 由host和username组成, 形式大致如下
'user_name'@'host_name' 1 名字合法的情况下可省略引号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'; 1
角色: 略
授权: 这里直接给出授权一个数据库所有权限的例子
GRANT ALL ON db_name.* TO 'user_name'@'host_name'; 1
然后让服务器重新加载权限表
FLUSH PRIVILEGES; 1
六 数据类型
数据类型大致分类:
numeric typesdate and time typesstring (character and byte) typesspatial typesJSON data type.描述约定
M, 在数值类型中, 表示最大显示宽度; 在浮点与定点数值类型中, 表示存储时的数字个数(精度); 在字符类型中, 表示字符长度, 但M的最大值取决于数据类型和字符编码.D, 用在浮点和定点类型中, 表示小数点后数字的个数(scale). 最大值可为30, 但不应该超过M-2fsp用于TIME,DATETIME和TIMESTAMP类型中, 表示秒小数部分的位数. 选值范围0-6, 默认0, 与标准SQL默认的6相反.[和]表示类型定义中可选的部分
一些数据类型是同一个, 仅为了兼容性, 如兼容标准SQL类型.
6.1 Numeric
修饰属性
显示宽度属性, zerofill属性将被弃用允许unsigned和signed属性(默认), 修饰在浮点数上的方式将被弃用整形数值类型
类型位数描述TINYINT1SMALLINT2MEDIUMINT3INT,INTEGER4BIGINT8后接(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’utcYEAR1901 to 2155datetime与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 and SELECT ... INTO OUTFILE , 受secure_file_priv变量影响
其他的以后补充了
参考
MySQL 8.0 Reference Manual相关知识
mysql prestatement
【讨论】养猫学习笔记(02)——营养需求标准，什么是NRC、AAFCO
宠物商城+ssm框架+jsp页面+mysql数据库
谷歌云搭建wordpress centos7 + apache + mysql + php + wordpress
机器学习实战笔记3(决策树与随机森林)
【盗墓笔记主题】迷你机关相册
基于javaweb+jsp的宠物领养信息管理系统(JavaWeb MySQL JSP Bootstrap Servlet SSM SpringBoot)
优秀的萌宠类笔记应该具有哪些特点？
「职位对比」豆柴宠物 小红书内容种草（自建笔记）怎么样
【讨论】养猫学习笔记(03)——钙磷比、磷以及长期过量或缺乏对肾脏的影响
网址: MySQL学习笔记 https://m.mcbbbk.com/newsview209161.html
| 上一篇: 超级舞者宠物比赛时间是几点 |
下一篇: 猫毛过敏怎么处理 |
