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
    
  • |表示多个选项可选择一个, 可配合[]{}使用

    #与[]使用, 选择一个或零个
    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.

  1. 环境预处理: 安装前, 先删除原有mysql, 和配置,数据等文件, 如/etc/my.cnf/etc/mysql/

  2. MySQL依赖: mysql依赖于libaio库, 安装前先检查下是否已安装, 一般情况都有

    #检查是否已安装, 看是否提示已安装
    apt search libaio
    #安装
    apt install libaio1 #为啥多了个1? 因为仓库中名字就是这个..
    
  3. 下载: 通过浏览器下载, 地址: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
    
  4. 解压: 解压后, 可以凭自己喜好存于某个位置

    tar -xJvf Downloads/mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
    
  5. 创建mysql用户: 官网建议, 以非root用户运行mysqld, 有助于安全性. 这里创建了mysql用户

    # 创建一个不能登录的系统用户mysql, 默认也创建了组
    useradd -r -s /bin/false mysql
    
  6. 初始化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
      
  7. 运行mysqld : 同样的, 以mysql身份运行, 这里的mysqld_safe一个启动mysqld方便的工具.

    sudo bin/mysqld_safe --user=mysql &
    
  8. 修改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杀死它

  9. 增加安全性(可选) : mysql提供了mysql_secure_installation脚本来配置msyqld安全方面的配置, 功能如

    • 设置root账户密码

    • 阻止外部访问root账户

    • 删除匿名用户

    • 等等

      使用后最直观感受是, 用户密码不能是简单的123456了, 必须复杂… 开发环境下不建议使用.

    #直接运行即可
    mysql_secure_installation
    
  10. 自启配置: 我们要自启的是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
    

    大功告成!

参考

三 基础

  • 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 nullis 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 nullis not null
    • group by中, 两个NULL被视为相等
    • order by中, NULL被当作最小来排序
  • 模式匹配: 支持标准SQL模式匹配和正则表达式, 默认大小写不敏感 ,

    • 标准SQ: 操作符使用LIKENOT 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 joinjoin(内连接)表示连接类型, 还有外连接, 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表中, 密码也不是明文储存, 加了密的.

  • 账户名: 由hostusername组成, 形式大致如下

    '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,DATETIMETIMESTAMP类型中, 表示秒小数部分的位数. 选值范围0-6, 默认0, 与标准SQL默认的6相反.
  • []表示类型定义中可选的部分

一些数据类型是同一个, 仅为了兼容性, 如兼容标准SQL类型.

6.1 Numeric

  • 修饰属性

    • 显示宽度属性, zerofill属性将被弃用
    • 允许unsignedsigned属性(默认), 修饰在浮点数上的方式将被弃用
  • 整形数值类型

    类型位数描述
    TINYINT1
    SMALLINT2
    MEDIUMINT3
    INT,INTEGER4
    BIGINT8

    后接(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. 注意关键字truefalse分别对应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
YEAR1901 to 2155

datetimetimestamp

两者都能获取和存入YYYY-MM-DD hh:mm:ss类型的时间, 但是timestamp实际上存入的是从'1970-01-01 00:00:00' UTC到现在的时间戳. 当取出时, 将时间戳根据当前会话时区转化为时间, 存入时将时间转化为UTC时区的时间戳后才存入.

因此, 改变当前时区后, 相同的timestamp值得到不同的时间, 因为时区不同.

6.3 字符串

  • 修饰属性

    • character setcharset指定编码
    • collate指定排序顺序
  • 字符串: 以下类型中M均表示字符长度. 说三遍, 字符,字符,字符!!!

    类型描述
    char(M)character(M)定长字符串. M范围0到255个, 默认1
    varchar(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, 默认1
    varbinary(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变量影响

其他的以后补充了

参考

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值