MySQL 入门指南

  |   0 评论   |   0 浏览
介绍
MySQL是DBMS(数据库管理系统)软件之一,是"多用户共同使用一份文件夹"的单机程序。通过scoket通信连接服务端、客户端之间的交互。
  • 服务端软件:

    1. scoket服务端
    2. 本地或远程文件的操作
    3. 解析SQL指令(语句)
    4. mysqld程序启动mysql服务,通过bin目录mysqld可执行程序启动mysql服务
  • 客户端软件(各种各样,可以使用java/php/go/python/js编写各式各样的客户端)

    1. scoket客户端
    2. 发送、解析SQL指令
    3. mysql程序连接数据库以及与数据库交互,通过bin目录自带mysql可执行程序连接、交互db
  • 关系型数据库、非关系型数据库

    1. 关系型数据库:Oracle、MySQL、SQLLite、SQL Server等,都是DBMS软件

    2. 非关系型数据库:MangoDB、Redis、Memcached等,本质也是用来存储、操作数据的软件

    3. 区别:

      3.1 关系型数据库约束:数据类型的约束、数据之间的强一致性约束(如,关联数据删除的先后顺序)

      3.2 非关系型数据库:约束少、操作速度快(性能好)

  • MySql数据存储

    1. MySql的数据本质是存储在远程服务器的硬盘内,发起请求后先从硬盘中取再放到内存
    2. Redis的数据本质是存储在远程服务器的内存,发起请求后直接从内存中获取所以速度更快
安装
  • MacOS手动安装

    # 下载、解压压缩包,解压后路径
    /Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64 # replace your path
    
    # 初始化服务端, 成功后会创建data目录
    /Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64/bin/mysqld --initialise-insecure
    
    # 启动mysql服务
    /Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64/bin/mysqld
    
    # 连接mysql服务
    /Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64/bin/mysql -u root -p # -u: 指定用户名, -h: 指定远程机地址,默认localhost -P: 指定远程机mysql服务的端口号,默认3306
    
    # 添加环境变量简化操作
    echo "export PATH=/Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64/bin:$PATH" >> ~/.bash_profile
    
    # MacOS数据库服务启动、停止命令
    # 添加环境变量
    echo "export PATH=/Users/angel90324/usr/local/mysql-8.0.20-macos10.15-x86_64/support-files:$PATH" >> ~/.bash_profile
    # 启动命令
    mysql.server start
    # 停止命令
    mysql.server stop
    
  • MacOS点点点安装

    # 终端执行brew install mysql,等待安装
    brew install mysql
    
    # 终端初始化命令
    mysql_secure_installation
    # 初始化设置如下:
    # 1. VALIDATE PASSWORD PLUGIN: 设置密码长度,Y:密码需要大于8位
    # 2. New password && Re-enter password: 设置密码,确认密码
    # 3. Remove anonymous users? 是否移除不需要密码的用户,Y:是,N:否 
    # 4. Disallow root login remotely? 是否允许远程登陆,Y:是,N:否
    # 5. Remove test database and access to it? 是否删除test库,Y:是,N:否
    # 6. Reload privilege talbes now? 是否重载权限,Y:是,N:否
    
    # MacOS数据库服务启动、停止命令
    # 启动
    mysql.server start
    # 停止
    mysql.server stop
    
    # 连接本地数据库
    mysql -u ${user} -p // 需要输入user的密码
    # 连接远程数据库
    mysql -h ${host} -P ${port} -u ${user} -p
    
  • Linux

    
    
连接
DBMS本质是文件夹(数据库)、文件(数据表)、文件内数据(数据行)的操作。
  • 数据库操作

    1. 数据库管理
    -- 创建数据库,关键字:create database
    create database ${db_name} default charset utf8 collate utf8_general_ci; # utf8 e.g: create databse db_utf8 defatult charset utf8 collate utf8_general_ci;
    create database ${db_name} default character set gbk collate gbk_chinese_ci; # gbk e.g: create databse db_gbk defatult character set gbk collate gbk_chinese_ci;
    create database ${db_name} default character set utf8mb4 collate utf8mb4_unicode_ci; # utf8mb4 e.g: create database db_utf8mb4 default character set utf8mb4 collate utf8mb4_unicode_ci;
    
    PS:
    字符集:mysql >= 5.5.3使用utf8mb4实现完整的utf8, mb4表示most bytes 4 最多占用4字节。mysql >= 8.0 默认使用utf8mb4字符集
    mysql8.0+默认使用:utf8mb4_0900_ai_ci, 0900指校对算法,ai指口音不敏感,ci指大小不敏感
    推荐使用:utf8mb4_unicode_ci / utf8mb4_general_ci
    重音灵敏、大小写敏感使用:utf8mb4_0900_as_cs
    
    -- 查看数据库,关键字:show
    show databases;
    
    -- 使用数据库,关键字:use
    use ${db_name}; # e.g: use db1;
    
    -- 删除数据库,关键字:drop
    drop database ${db_name};
    
    
    1. 用户管理
    -- 默认用户
    # root, 具有数据库所有权限、包含赋予权限的权限
    
    -- DBA(database admin),数据库管理员
    # 基本上等同于root
    
    -- 创建用户
    -- 语法:create user some_username identified by password;
    create user '${username}'@'${host}' identified by '${password}'; # e.g create user 'angel'@'192.168.0.1' identified by 'xg001015'; 
    
    -- 删除用户
    -- 语法:drop user some_username;
    drop user '${username}'@'${host}';
    
    -- 修改用户
    -- 语法:update user set some_field where user=some_username
    update user set ${fields}='${new_value}' where user='${user}';
    
    -- 修改密码
    -- 语法:set password for some_username = Password(new_password);
    set password for '${username}'@'${host}' = Password(${new_password}); # mysql 5.+
    alert user '${username}'@'${host}' identified with mysql_native_password by '${new_password}' # mysql 8+ 
    
    PS: 
    '${username}'@'${host}' # ${username}表示连接数据的用户名,${host}表示可连接数据库的host地址
    'angel'@'192.168.0.1': angel用户可以在192.168.0.1的主机上连接数据库服务
    'angel'@'192.168.%':angel用户可以在192.168号段上所有主机连接数据库服务,通配符"%"表示任意
    'angel'@'%':angel用户可以在所有主机上连接数据库服务
    
    1. 权限管理
    -- 赋予用户权限,语法:grant somegrants on somedb.sometable to some_username
    grant ${grants} on ${db_name}.${table_name} to '${username}'@'${host}';
    # e.g1: grant select, insert, update on db1.tb1 to 'angel'@'%'; # 在数据库db1下数据表tb1赋予查询、插入、更新权限, 可以赋予多个权限,权限之间使用逗号分割
    # e.g2: grant all privileges on db1.* to 'angel'@'%'; # 在数据库db1下所有数据表赋予所有权限,但没有赋予权限的权限
    PS:
    ${db_name}.* 表示具体某个数据库下的所有
    ${db_name}.${table_name} 表示具体某个数据库下的某个数据表
    ${db_name}.存储过程 表示具体某个数据库下的存储过程
    *.* 表示所有数据库下的所有
    
    -- 取消用户权限,语法:revoke somegrants on somedb.sometable from some_username
    revoke ${grants} on ${db_name}.${table_name} from '${username}'@'${host}';
    
    -- 查看用户权限,语法:show grants for some_username
    show grants for '${username}'@'${host}';
    
    -- 刷新权限使权限立即生效
    flush privileges;
    
    -- 权限列表
    all privileges  除grant外的所有权限
    select          仅查权限
    select,insert   查和插入权限
    ...
    usage                   无访问权限
    alter                   可使用alter table权限
    alter routine           可使用alter procedure和drop procedure权限
    create                  使用create table权限
    create routine          使用create procedure权限
    create temporary tables 使用create temporary tables权限
    create user             使用create user、drop user、rename user和revoke、all privileges权限
    create view             使用create view权限
    delete                  使用delete权限
    drop                    使用drop table权限
    execute                 使用call和存储过程权限
    file                    使用select into outfile 和 load data infile权限
    grant option            使用grant 和 revoke权限
    index                   使用index权限
    insert                  使用insert权限
    lock tables             使用lock table权限
    process                 使用show full processlist权限
    select                  使用select权限
    show databases          使用show databases权限
    show view               使用show view权限
    update                  使用update权限
    reload                  使用flush权限
    shutdown                使用mysqladmin shutdown(关闭MySQL)权限
    super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆权限
    replication client      服务器位置的访问权限
    replication slave       由复制从属使用权限
    
  • 数据表操作

    1. 基本操作
    -- 查看所有数据表
    show tables;
    
    -- 创建数据表
    create table ${table_name}(
    	id int not null auto_increment primary key,
      name char(10)
    ) engine=innodb default charset=utf8;
    
    engine指定引擎
    charset指定字符编码
    
    列(字段)约束:
    数据类型约束,大体分为三类,数字型、字符型、时间型
    其他约束:
    auto_increment:  自增列,必须是primary key
    primary key: 主键,约束不可重复且不可为空,加速查找
    not null: 不可为空 
    null : 可为空
    default: 默认值 
    
    -- 清空数据表
    	delete from ${table_name};
    	truncate table ${table_name};
    	区别: delete清空不会清除自增值, truncate会清除自增值。truncate清除速度较快
    
    -- 删除数据表
    	drop table ${table_name}; # 删除数据表
    
    -- 修改数据表
    # 手动设置AUTO_INCREMENT自增列起始值,手动设置自增值小于当前自增值时需要先清空数据表否则无法生效。
    alter table ${table_name} AUTO_INCREMENT=${n};
    
    # 手动设置自增步长
    MySql自增步长,基于会话级别手动设置自增步长 
    # 查看会话级别变量
    show session variables like 'auto_increment%';
    set session auto_increment_increment=${n} # 设置会话级别自增列增长步长
    set session auto_increment_offset=${0} # 设置会话级别自增列的起始值
    
    MySql自增步长,基于全局级别手动设置自增步长
    # 查看全局级别变量
    show global variables like 'auto_increment%';
    set global auto_increment_increment=${n}; # 设置全局自增列增长步长
    set global auto_increment_offset=${n}; # 设置全局自增列起始值
    
    区别:设置全局自增步长,无论哪个用户登录mysql,自增列都会按照全局设置来增长
    
    
    SqlServer自增步长,基于数据表级别,创建表时可以设置自增步长
    -- 查看数据表详情
    	desc ${table_name};
    
    -- 查看数据表如何创建
    	show create table ${table_name} \G; # \G格式化
    
    
    1. 引擎
    -- 创建数据表时,使用engine指定引擎
    innodb引擎: 支持事务,原子性操作,即存储过程中任意环节发生错误时自动回滚保持数据的强一致性
    myisam引擎: 支持全局索引,存储速度快
    
    1. 基本数据类型
    1. 数字型,分为整数型,浮点型
    整数型, 
    example: 
    	id int signed # 有符号
      id int unsigned # 无符号  
    	int: 整数型
    	tinyint: 小整数型,有符号取值范围-127 - 128, 无符号取值范围 0 - 255
    	bigint: 大整数型,取值范围大
    
    浮点型:
    example: 
    	price decimal(10, 2) defalut 0;
    	float: 单精度, 
    	double: 单精度, double与float的区别在于小数点后不精准起始的位数, double比float更长
    	decimal: 双精度,更精确总位数以及小数点后位数,底层使用字符串存储
    
    2. 字符型:
    char(10), 最大长度255,存储时长度不足限制长度时按照限制长度来计算,占用空间大,查找速度快
    varchar(10),最大长度255,存储时长度不足限制长度时按照实际长度来计算,占用空间小,查找速度慢
    text、mediumtext、longtext, text最大长度65535储存长文本使用。
    PS:
    char()比varchar()查找速度快的原理:执行select语句从前往后一列查找时,char存储长度固定所以每一列直接跳过所以查询速度快,而varchar存储不固定每一列都需要计算实际长度所以查询慢
    SQL优化点:长度固定的列使用char()设计数据表时往前放,长度不固定的列使用varchar()设计数据表时往后放
    长文本存储:超长文本不建议使用字符串存储到数据表的某一列,推荐存储到某台服务器硬盘数据表中存锤访问地址,例如图片、视频、大数据。
    
    3. 时间型:
    DATE: yyyy-mm-dd 年月日
    TIME: HH:MM:SS 时分秒
    YEAR: yyyy 年份
    DATETIME: yyyy-mm-dd HH:MM:SS 年月日时分秒,区间(1000-01-01 00:00:00 -- 9999-12-31 00:00:00)
    TIMESTAMP: yyyy-mm-dd HH:MM:SS 年月日时分秒,区间(1070-01-01 00:00:00 -- 2037-12-31 00:00:00)
    PS:常用DATETIME或TIMESTAMP或存时间戳整形
    
    4. enum()枚举型:适合选项固定且长期不修改的列,且插入列时值必须为枚举值当中其一的值不可超出范围,扩展性较差
    5. set()集合型:选项固定,且插入列时值必须集合值中一个或多个组合的值不可超出集合范围
    
    6. 二进制数据类型:TinyBlob、Blob、MediumBlob、LongBlob
    
    1. 主键
    -- 主键作用
    	1. 一张表只能有一个主键,数据行唯一标示。可以使用一列或多个列组成主键,多列组成的称为联合主键
    	2. 约束作用,不可重复并且不可为空
    	3. 加速查找
    
    -- 定义主键,使用关键字primary key
    	定义主键两种方式
    	example1: 
    	id int primary key
    	example2:
    	primary key (field1, field2, ...)
    
    -- 自增列
    	1. 自增列必须是primary key
    
    1. 外键
    -- 外键作用
    	1. 一张表可以有多个外键,用于关联其他数据表。可以使用一列或多列组成外键,外键值可以重复。
    	2. 约束作用:
    	创建约束:外键列只能关联其他数据表的主键列,如果需要多个外键列则被关联数据表需要定义同等数量的主键列
    	删除约束:外键关联表某行数据没有关联数据时,可直接删除外键关联表数据;如果有关联数据则需要先删除关联数据后,再删除外键关联表某行数据。 # e.g:员工表部门id外键列 关联 部门表,部门表d_id=1没有关联数据,可直接删除。d_id=2有关联员工表的数据,需要先删除员工表内所有关联d_id=2的所有数据行后再删除部门表d_id=2这行数据
    	3. 节省空间,扩展性强
    	4. 一个外键列的值对应其他数据表中关联列的任意值
    	PS:类似使用下拉框选项展示的业务,优先使用外键。
    
    -- 约束外键
    -- 语法:constraint ${fk_name} foreign key (${foreign_key_field}) references ${table_name}(${relation_field}); # 数据表之间外键名字不可以重复
    create table department(
    	d_id bigint auto_increment primary key,
      title char(15)
    ) engine=innodb default charset=utf8mb4;
    
    create table userinfo(
    	uid bigint auto_increment primary key,
      name varchar(32) not null,
      department_id bigint not null, # 先定义外键,再约束外键
      constraint fk_user_department foreign key(department_id) references department(d_id)
    ) engine=innodb default charset=utf8mb4;
    
    -- 定义多个外键列
    create table tb1(
    	id int not null auto_increment,
      pid int not null,
      name varchar(15) not null,
      primary key(t1_id, pid)
    ) engine=innodb default charset=utf8mb4;
    
    create table tb2(
    	id int not null auto_increment,
      t1_id int not null,
      t1_pid int not null,
      name varchar(50) not null,
      -- 约束外键,使用多个列组成外键关键外键关联表多个主键列
      constraint fk_tb2_t1id_t1_id_t2_t1pid_t1_pid foreign key (t1_id, t1_pid) references tb1(t1_id, pid)
    ) engine=innodb default charset=utf8mb4;
    
    
    
  • 数据行操作

    -- 插入数据
    insert into ${table_name}(field_1, field_2, ...) values(field_1_value, field_2_value, ....);
    
    -- 删除数据
    	delete from ${table_name}; # 不会清空自增列
    	delete from ${table_name} where id<6; # 条件删除
    
    -- 更新数据
    	update ${table_name} set age=18; # 更新整张数据表
    	update ${table_name} set age=18 wherer user='angel'; # 条件更新
    
    -- 查询数据
    select * from ${table_name}; # 查询数据表所有列, 性能差
    select field_1, field_2 from ${table_name} # 查询数据表指定列,推荐使用
    
    
唯一索引
  • 唯一索引

    -- 关键字:unique
    # 单列唯一索引
    create table ${table_name}(
    	id int not null auto_increment primary key,
      name varchar(32),
      unique unique_name (name)
    ) engine=innodb default charset=utf8mb4;
    
    # 联合唯一索引
    create table ${table_name}(
    	id int not null auto_increment primary key,
      name varchar(32),
      unique unique_name(id, name)
    ) engine=innodb default charset=utf8mb4;
    
    -- 使用ALTER关键字添加、删除唯一索引
    # ALTER TABLE ${table_name} ADD UNIQUE KEY (field_name, ...); 使用ALTER关键字添加唯一索引
    # ALTER TABLE ${table_name} DROP INDEX ${unique_key_name}; 使用ALTER删除唯一索引(唯一索引名)
    
    
  • 唯一索引与主键的区别

    -- 共同点
    1. 不可重复
    2. 加速查询
    
    -- 不同点
    1. 主键不可为空
    2. 单列唯一索引可为空,但不可重复,不推荐唯一索引为空
    3. 联合唯一索引某一列可以为空,唯一索引列组合起来不可重复
    
外键的变种
  • 一对多关系

    1. 最常用的关系
    2. 数据表的数据与另外一张数据表的数据是一行对应多行数据关系
    3. 不需要在外键增加唯一索引约束,不限制外键值的唯一性
    
  • 一对一关系

    1. 数据表的数据与另外一张数据表的数据是一、一对应关系
    2. 数据表需要定义外键、约束外键
    3. 需要在外键增加唯一索引约束,限制外键值的唯一性,建立两张数据表的数据行一、一对应关系
    
  • 多对多关系

    -- 对多张数据表建立关系
    
    -- 单表多对多关系
    1. 假想有一张相同数据的数据表
    2. 建立关系表,通过关系表关联多张表之间的数据
    3. 关系表中可定义多个外键,关联同一张数据表,外键之间可增加唯一索引也可以不加
    
    -- 多表多对多关系
    1. 建立多张表之间的关系表,通过关系表关联多张表之间的数据
    2. 关系表中可定义多个外键,关联多张的数据表,外键之间可增加唯一索引也可以不加
    
    
数据行操作
  • 插入数据

    -- 一次插入单条数据
    INSERT INTO ${table_name}(field1, field2, ...) VALUES(value1, value2, ...);
    
    -- 一次插入多条数据
    INSERT INTO ${table_name}(field1, field2, ...) VALUES(value1, value2, ...), (value1, value2, ...);
    
    -- 从另外一张数据表中获取数据并插入新表
    INSERT INTO ${table1_name}(field1, field2, ...) SELECT field1, field2, ... FROM ${table2_name} WHERE ...; # 查询语句可使用SQL查询语句的任意语法规则
    
    
  • 清空数据

    -- 清空数据表全部数据
    DELETE FROM ${table_name}; # 不会清空自增列当前值
    TRUNCATE TABLE ${table_name}; # 会清空自增列当前值,并且清空速度较快
    
    -- 提交清空数据表数据,可使用SQL条件语句任意语法
    DELETE FROM ${table_name} WHERE ...;
    TRUNCATE TABLE ${table_name} WHERE ...;
    
    
  • 更新数据

    -- 更新整张数据表单列数据
    UPDATE TABLE ${table_name} SET field_name=${new_value};
    
    -- 更新整张数据表多列数据
    UPDATE TABLE ${table_name} SET field1_name=${new_value}, field2_name=${new_value}, ...;
    
    -- 条件更新数据表单列数据
    UPDATE TABLE ${table_name} SET field_name=${new_value} WHERE ...;
    
    -- 条件更新数据表多列数据
    UPDATE TABLE ${table_name} SET field1_name=${new_value}, field2_name=${new_value}, ... WHERE ...;
    
  • 查询数据

    -- 条件查询
    # 比较查询
    > 大于
    SELECT id, name FROM ${table_name} WHERE id>5;
    >= 大于等于
    SELECT id, name FROM ${table_name} WHERE id>=5;
    < 小于
    SELECT id, name FROM ${table_name} WHERE id<5;
    <= 小于等于
    SELECT id, name FROM ${table_name} WHERE id<=5;
    != 不等于,也可以写为<>
    SELECT id, name FROM ${table_name} WHERE id!=5;
    
    # 逻辑查询
    AND
    SELECT id, name FROM ${table_name} WHERE id<5 AND name="angel";
    
    OR
    SELECT id, name FROM ${table_name} WHERE id>5 OR name="angel";
    
    IN # 在取值范围内
    SELECT id, name FROM ${table_name} WHERE id IN(1, 5, 10);
    SELECT id, name FROM ${table_name} WHERE id IN(SELECT id FROM ${table2_name} WHERE ...); # 从其他数据表查询id范围
    
    NOT IN # 不在取值范围内
    SELECT id, name FROM ${table_name} WHERE id NOT IN(1, 5, 10);
    SELECT id, name FROM ${table_name} WHERE id NOT IN(SELECT id FROM ${table2_name} WHERE ...); # 从其他数据表查询id范围
    
    BETWEEN ... AND ... # 闭区间范围
    SELECT id, name FROM ${table_name} WHERE id BETWEEN 5 AND 10;
    
    # 列名别名
    AS
    SELECT id AS "用户ID", name AS "用户名" FROM user WHERE id>5;
    SELECT id AS "用户ID", name AS "用户名", other FROM user WHERE id>5;
    
    -- 通配符
    LIKE "angel%", % 通配符,通配多个字符串
    SELECT id, name FROM user WHERE name LIKE "angel%"; # 通配任意angel开头的字符串
    SELECT id, name FROM user WHERE name LIKE "%angel"; # 通配任意angelj结尾的字符串
    SELECT id, name FROM user WHERE name LIKE "%angel%"; # 通配任意包含angel字符串
    
    LIKE "angel_" _ 通配符,通配一个字符串
    SELECT id, name FROM user WHERE name LIKE "angel_"; # 通配任意angel开头长度为6的字符串
    SELECT id, name FROM user WHERE name LIKE "_angel"; # 通配任意angel结尾长度为6的字符串
    SELECT id, name FROM user WHERE name LIKE "_angel_"; # 通配任意包含angel字符串长度为7的字符串
    
    -- 分页, 必须在条件子句末尾
    LIMIT ${size} OFFSET ${page}
    SELECT id, name FROM user LIMIT 10 OFFSET 0; # 查询user表前十条数据
    SELECT id, name FROM user LIMIT 10 OFFSET 10; # 从user表第十行起,查询10条数据
    
    LIMIT ${page}, ${size}
    SELECT id, name FROM user LIMIT 0, 10; # 查询user表前十条数据, 查询数据集索引下标从0开始
    SELECT id, name FROM user LIMIT 20, 10; # 从user表第二十行起,查询10条数据
    
    -- 排序
    # 单列排序
    ORDER BY field asc;
    SELECT id, name FROM user ORDER BY id ASC;
    SELECT id, name FROM user ORDER BY id ASC LIMIT 10 OFFSET 10; # 组合使用
    
    ORDER BY field desc;
    SELECT id, name FROM user ORDER BY id DESC;
    SELECT id, name FROM user ORDER BY id DESC LIMIT 10 OFFSET 10;
    
    # 多列排序
    SELECT id, name, age FROM user ORDER BY id ASC, age ASC; # 优先按照id生序,如果相同则按照年龄再降序排序 
    
    -- 分组
    # 简单分组
    SELECT gender FROM user GROUP BY gender; # 按照性别分组
    
    # 分组 + 聚合函数
    # 聚合函数max()求列最大值, min()求列最小值, count()求列总行数, sum()求列值总和, avg()求列平均值
    SELECT gender, count(*) AS counter FROM user GROUP BY gender;
    
    # 过滤(条件)分组 *** 使用HAVING,不可使用WHERE,且WHERE子句中不可使用聚合函数操作 ***
    SELECT gender, count(*) AS counter FROM user GROUP BY gender HAVING counter > 2;
    
    # 组合使用
    SELECT gender, count(*) AS counter FROM user WHERE student_id BETWEEN 1 AND 5 GROUP BY gender ORDER BY counter DESC; # 在student_id 1-5之间,查询男女总数并倒序排列
    
    -- 连接表操作
    # 内连接表
    
    # 外连接表
    
    # 左连接表
    ${left_table_name} LEFT JOIN ${right_table_name} ON ${relation_express} # 查询数据集范围取左数据表全部数据 + 左、右数据表表交集, relation_express为关系表达式可以看作为条件
    
    # 右连接表
    ${left_table_name} RIGHT JOIN ${right_table_name} ON ${relation_express} # 查询数据集返回取右数据表全部数据 + 左、右数据表交集数据
    
    # 笛卡尔积
    select field1, field2, ... from ${table_name1}, ${table_name2}, ...;
    
  • 临时表

    -- 创建临时表
    (SELECT field1, field2, ... FROM ${other_table} WHERE ....) AS ${casual_table};
    
    -- 使用临时表
    SELECT * FROM (SELECT * FROM ${other_table} WHERE ... ) AS ${casual_table}; # 后面使用括号包裹起来的可以作为一张临时表被查询
    
    -- 利用临时表与别名进行自联结查询
    # 使用自联结查询数据列
    SELECT (SELECT name FROM table1 AS tb2 WHERE tb2.id = tb1.id) AS name FROM table1 AS tb1
    
    SELECT
    s1.sid,
    (SELECT score.number FROM score AS s2 WHERE s2.sid = s1.sid AND s2.cid = 1) AS yw_score,
    (SELECT score.number FROM score AS s2 WHERE s2.sid = s1.sid AND s2.cid = 2) AS sx_score
    FROM
    score as s1;
    
    # 使用自联结查询数据行时
    SELECT c1.custer_id, c1.custer_name, c1.custer_contact 
    FROM custmer AS c1, custmer AS c2 
    WHERE c1.custer_name = c2.custer_name AND c2.custer_contact = "angel";
    
    
    -- 动态计算列
    # 动态计算列只能是一个值
    SELECT field, field2, (SELECT AVG(field) FROM ${table_name}) FROM ${table_name}
    
    # 条件动态计算列
    CASE WHEN $[条件...] THEN ${条件成立列值} ELSE ${条件失败列值} END
    
    # 三元表达式动态计算列
    IF(ISNULL(filed), default_value, field)
    
    
  • 去重

    -- 对列去重 # 可去重复,但效率不高
    SELECT DISINCT field FROM ${table_name}
    
  • 组合

    -- 上下表组合,关键字:UNION
    1. 使用UNION上下联结两张数据表,列数必须相同;数据列名取第一张表的列名,数据行会取两张表总和。
    SELECT student.id, student.gender FROM student
    UNION
    SELECT teacher.id, teacher.name FROM teacher;
    
    2. 使用UNION上下联结数据表时,被查询列数据行相同的情况下默认会有去重效果,数据行不同则取数据行之和
    SELECT student.id, student.name FROM student
    UNION
    SELECT student.id, student.name FROM student
    
    # UNION ALL 默认不去重
    SELECT student.id, student.name FROM student
    UNION ALL
    SELECT student.id, student.name FROM student
    
    
    
  • 数据库备份

    -- 数据库导出
    # 备份数据表结构+数据
    mysqldump ${db_name} > ${path}/${file_name}.sql -u ${user} -p
    # 仅备份数据表数据
    mysqldump -d ${db_name} > ${path}/${file_name}.sql -u ${user} -p
    
    -- 数据库导入
    mysqldump ${db_name} < ${path}/${file_name}.sql -u ${user} -p
    mysqldump -d ${db_name} < ${path}/${file_name}.sql -u ${user} -p
    
视图
  • 视图概念

    1. 数据表相当于物理表,而视图表相当于虚拟表。
    2. 视图表不可以被增、删、改数据,但视图表所使用的物理表数据被更新后,视图表查询数据也会同步更新。
    3. 视图表可以被重复使用。
    
  • 创建视图

    -- 关键字: CREATE VIEW
    CREATE VIEW ${VIEW_NAME} AS ${SQL}
    
  • 修改视图

    -- 关键字: ALTER VIEW
    ALTER VIEW ${VIEW_NAME} AS ${NEW_SQL}
    
  • 删除视图

    -- 关键字:DROP VIEW
    DROP VIEW ${VIEW_NAME};
    
  • 注意事项

    1. 真实业务中,不推荐使用视图,可读性太差
    
触发器
  • 触发器概念

    1. 触发器类似于Python或者Vue当中的钩子函数,在特定时机下自动触发。仅适用于数据表更新、插入、删除数据行,不适用查询。真实业务中不常用
    2. 触发器分为两种触发状态,一种是在增、删、改操作前触发,另外一种是在增、删、改之后触发
    3. 触发器创建需要超级管理权限
    
  • 插入数据行前触发器

    -- 关键字:CREATE TRIGGER ... BEFORE ON ... FOR EACH ROW
    delimiter //
    DELIMITER //
    CREATE TRIGGER ${TRIGGER_NAME} BEFORE INSERT ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	插入数据行前触发的SQL语句代码块
    	...;
    	...;
    	...;
    END //
    delimiter ; 
    
  • 插入数据行后触发器

    -- 关键字:CREATE TRIGGER ... AFTER INSERT ON ... FOR EACH ROW
    delimiter //
    CREATE TRIGGER ${TRIGGER_NAME} AFTER INSERT ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	插入数据行后触发的SQL语句代码块
    	...;
    	...;
    	...;
    END //
    delimiter ;
    
  • 删除数据行前触发器

    -- 关键字:CREATE TRIGGER ... BEFORE DELETE ON ... FOR EACH ROW
    delimiter //
    CREATE TRIGGER ${TRIGGER_NAME} BEFORE DELETE ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	删除数据行前触发的SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
  • 删除数据行后触发器

    -- 关键字:CREATE TRIGGER ... AFTER DELETE ON ... FOR EACH ROW
    delimiter //
    CREATE TRIGGER ${TRIGGER_NAME} AFTER DELETE ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	删除数据行后触发SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
  • 更新数据行前触发器

    -- 关键字:CREATE TRIGGER ... BEFORE UPDATE ON ... FOR EACH ROW
    delimiter //
    CREATE TRIGGER ${TRIGGER_NAME} BEFORE UPDATE ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	更新数据行后触发SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
  • 更新数据行后触发器

    -- 关键字:CREATE TRIGGER ... AFTER UPDATE ON ... FOR EACH ROW
    delimiter //
    CREATE TRIGGER ${TRIGGER_NAME} AFTER UPDATE ON ${TABLE_NAME} FOR EACH ROW
    BEGIN
    	更新数据行后触发SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
  • 动态数据触发器

    -- 关键字NEW,OLD
    delimiter //
    CREATE TRIGGER trigger_student_teacher AFTER INSERT ON student FOR EACH ROW
    BEGIN
    	INSERT INTO teacher (name) values (NEW.student_name); # 将每一个新插入到student数据表的student_name字段的值同样插入到teacher数据表,表示该同学既是学生也是老师
    END //
    delimiter ;
    
    PS:
    OLD关键字只能用于更新、删除动作。
    
  • 注意事项

    1. SQL语句默认结束标示符为分号";",如果使用默认结束标示符SQL语句代码块之后将不会被执行
    2. FOR EACH ROW关键字表示为每插入一行数据,会自动触发一次
    2. delimiter关键字,在创建触发器之间将默认结束标示符改为//, 在触发器结束之后再使用delimiter修改为分号";"
    3. delimiter关键字,修改默认结束标示符需要root用户或者DBA或超级管理员权限
    
函数
  • 数据库内置函数

    -- 最常用时间格式化函数
    官方文档:
    
  • 自定义函数

    -- 关键字: CREATE FUNCTION
    delimiter //
    CREATE FUNCTION(
      	params1 INT, 
      	params2 INT, 
      	...
    	)
    returns INT
    
    BEGIN
    	自定义SQL函数代码块, 代码块内禁止使用增删查改SQL语句
    	...
    	...
    END//
    delimiter ;
    
  • 使用函数

    SELECT function() FROM ${TABLE_NAME};
    
  • 注意事项

    1. 自定义函数,必须指定参数的数据类型
    2. 自定义函数,必须有返回值并且指定返回值的数据类型
    
存储过程
  • 存储过程概念

    1. 存储过程相当于多个SQL语句的封装,可以被重复性使用
    2. 使用关键字:PROCEDURE 创建存储过程,存储过程本身不能返回结果,但可以通过定义参数来伪造"返回值"
    3. 存储过程参数分为三种类型in, out, inout
    	3.1 in类型在存储过程内部可使用不可修改,存储过程执行后可通过"SELECT"关键字查询获取
    	3.2 out类型在存储过程内部可修改不可使用,存储过程后执行后可通过"SELECT"关键字查询获取修改后结果
    	3.3 intout类型在存储过程内部即可以使用也可以修改,存储过程后执行后可通过"SELECT"关键字查询获取修改后结果
    	PS:
    	out类型通常来记录存储过程执行SQL代码块的状态,例获取out为1:全部SQL成功,2:部分SQL成功,0:全部失败
    
  • 创建存储过程

    -- 无参存储过程
    delimiter //
    CREATE PROCEDURE ${PROCEDURE_NAME} ()
    BEGIN
    	# 存储过程SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
    -- 带参存储过程
    delimiter //
    CREATE PROCEDURE ${PROCEDURE_NAME} (
    	INT params1 INT,
      OUT params2 INT
      INOUT params3 TINYINT
    )
    BEGIN
    	# 存储过程SQL语句代码块
    	...;
    	...;
    END //
    delimiter ;
    
  • 调用存储过程

    -- 关键字:CALL
    CALL get_user_proc(@uid, @username, 0); # 使用"CALL"关键字调用执行存储过程
    
  • 引用全局变量

    -- 设置全局变量、获取存储过程"返回值"
    # 使用"SET"关键字设置全局变量,可以传入存储过程
    SET @uid = 1;
    SET @username = "angel";
    SET @status = 0
    
    CALL get_user_proc(@uid, @username, 0); # 使用"CALL"关键字调用执行存储过程,并引用全局变量
    
    SELECT @_get_user_proc_2; # 使用"SELECT"关键字查询获取存储过程执行结果, OUT: 1
    
  • 事务型存储过程

    -- 原子性操作,支持回滚, 关键字:DECLARE、ROLLBACK、COMMIT
    delimiter //
    CREATE PROCEDURE ${PROCEDURE_NAME} (
    	out status tinyint
    )
    BEGIN
    	DECLARE exit HANDLER FOR SQLEXCEPTION
      BEGIN
      	# 发生异常时执行的SQL代码块
      	SET status = 0;
      	ROLLBACK; # 执行SQL异常,将状态重置为0后回滚
      END;
    
      START TRANSACTION
      	# 正常情况下执行的SQL代码块
      	DELETE FROM ${TABLE_NAME};
      	INSERT INTO user(name) values("angel");
      	SET status = 1;
      	COMMIT; # 执行SQL正常,将状态修改为1后提交
      END//
    delimiter ;
    
  • 游标

    
    
  • 注意事项

    1. 优先编写SQL语句查询
    2. 性能相关
    	2.1 SQL查询优先在代码程序中处理。性能要求不高时可使用SQL函数进行操作;
    	2.2	利用MySQL内置功能视图、函数、触发器、存储过程
    	2.3 MySQL内置函数影响性能,数据行索引将无法加速查找
    3. 使用存储过程执行SQL语句可以最多可以拿到2个结果,
    	3.1 执行查询SQL语句的查询数据集
    	3.2 执行任意SQL语句的执行状态结果(伪造的返回值)
    
索引
  • 索引分类

    1. 主键索引
    	1.1 主键索引作用:加速查找、约束数据行的唯一性,主键不可为空不可重复。通常使用自增列作为主键
    	1.2 每行数据,只能有一个主键索引;但一个主键索引可以由一列或多个列组成
    
    2. 唯一索引
    	2.1 唯一索引作用:加速查找,约束数据列的唯一性
      2.2 每行数据可以有多个唯一索引,每个唯一索引可以由一列或多个列组成
    
    3. 联合主键索引
    	3.1 由多个列组成的主键索引,称作联合主键索引,保持每行数据只能有一个主键索引的规则
    	3.2 联合主键索引,多个列数据之间组合不可重复不可为空,如 id:, name: a 与 id:1, name: b
    
    4. 联合唯一索引
    	4.1 由多个列组成的唯一索引,称作联合唯一索引,保持多列之间组合不可重复的规则,但其中一列或几列可为空
    	4.2 联合唯一索引,保持每行数据可以使用多个联合唯一索引的规则
    
    
  • 无索引与有索引区别

    -- 无索引
    1. 查询速度慢,数据表需要从前到后依次查找,扫描全表。好比博客,没有目录需要整个博客内挨个翻。
    2. 占用硬盘空间小
    
    -- 有索引
    1. 查询速度快,可以直接定位到某行数据。好比博客,增加了目录页,可以直接定位到某个具体内容体系,但需要有地方存放目录。
    2. 占用硬盘空间大,因为需要有地方存储创建的索引
    
    
  • 索引工作原理

    1. 根据定义的索引,额外创建一块存储空间"按照某种数据结构"将数据表数据保存起来,存储为数据行在数据表的位置。
    2. 当查询数据表数据时,先到"存储空间"内找到数据行的具体位置,再到数据表查询直接定位到数据行的位置。
    3. 当将定义的索引删除后,再次查询时,每次又会重新扫描数据表进行查询。
    
    -- 哈希索引存储原理
    1. 将索引转换为哈希值,对应数据行的内存地址, 可以假想有一张哈希索引表,一列是索引的哈希值,一列是数据行的内存地址
    2. 当使用索引具体值查询时,直接从哈希表内定位哈希值的位置,再取出数据行的内存地址,从内存地址直接取出对应数据行
    
    优点:
    	1. 使用索引,根据索引具体值查询,速度快。如:创建索引name,WHERE name = "xxxx";
    	2. 相对于B数索引存储来说,占用硬盘空间小一些
    	3. 个人猜测:例如查询某个商品详情页数据,使用哈希索引可能会相对较快。
    缺点:
    	1. 哈希索引表是无序的,与数据表内数据行的顺序不能保持一致。
    	2. 使用索引,查询索引范围时,速度慢。 如:创建索引id, WHERE id BETWEEN 10 AND 20;
    
    -- B树索引存储原理
    1. 基本工作原理,二分查找原理。以2为底求任意数据量的对数,对数就是B树索引表的最多查找次数。例如: 1024, 最多查找10次
    2. 将每一个父节点拆分为2个子节点,一个大于父节点(索引),一个小于父节点(索引),以此类推。
    
    优点:
    	1. B树索引应用广泛,根据索引范围查询,速度快。
    缺点:
    	1. B树索引占用硬盘空间,相对较大。
    
    -- 注意事项
    1. 创建索引的目的:查询速度快
    
    2. 创建索引的使用
    	2.1 增、删、改数据表时会速度会慢,因为要更改两个地方
    	2.2 创建索引,仅查询数据表时会起到加速作用
    	2.3 创建索引,但查询中没有命中(正确使用)索引,无法起到加速作用
    	2.4 
    3. 由于建立索引会无形中增加所需存储空间,导致硬盘存储空间要求相对较高
    
    
ORM操作
Pymysql
  • 安装

    
    
  • 防止SQL注入

    1. 禁止方法内使用字符串拼接SQL语句,也是测试点
    	理由:
    	1.1 SQL语句中,使用 "--" 符号进行注释
    	1.2 使用字符串拼接方式,输入中含有" --"可能会引起部分SQL语句被注释的风险
    	1.3 使用字符串拼接方式,业务必须使用字符串拼接方式情况时,一定要做字符串包含" --"的校验
    
    2. 规避SQL注入的两种解决方案
    	2.1 使用API提供的course.excute(query, args),"args"参数进行参数化SQL语句,args参数接受元组/list/字典/可迭代对象
      2.2 在数据库存储过程中使用动态参数化SQL语句规避SQL注入
    
    Bad Example:
    user = input("input username:")
    pwd = input("input password:")
    # 当用户输入username: angel' OR 1 = 1 --, 或任意${username}' OR 1 = 1 --, 后面passworde部分语句就被注释掉, 并且100%通过
    sql = "SELECT uid, name FROM user WHERE username='{username}' AND password=	'{password}'".format(username=user, passworde=pwd)
    cursor.execute(sql)
    
    Good Example:
    user = input("input username:")
    pwd = input("input password:")
    sql = "SELECT uid, name FROM user username={username} AND pasworde={password}"
    cursor.execute(sql, {"username": user, "password": pwd})
    
  • 基本API

    import pymysql
    
    ##### 建立数据库连接相关操作
    1. 构造数据库连接对象
    connect = pymysql.connect(
      host,  # 数据库远程机地址, 默认localhost
      port,  # 数据库服务端口号, 默认3306
      username,  # 连接数据库时的用户名
      password,  # 连接数据时的密码
      database,  # 连接数据库的数据库名
      charset,  # 指定使用字符集
      cursorclass # 指定创建游标的类,默认Cursor,返回数据类型为(嵌套)元组,DictCursor返回数据类型为(嵌套)字典
    )
    
    2. 使用with ... as: 句柄构造数据库连接对象
    with pymysql.connect(**kwargs) as cursor:
      # 使用with ... as构造数据库连接对象注意点:
      # 1. __enter__(self)方法返回的是游标对象,不是connect对象
      # 2. __exit__(self, exc_type, exc_val, exc_tb)方法,捕获到异常会自动执行回滚
    
    3. 断开数据库连接
    connect.close() # 数据库操作后关闭数据库连接,避免占用连接
    
    4. 提交
    connect.commit() # 增、删、改操作后需要调用commit()进行提交,否则无法修改数据行数据
    
    5. 回滚
    connect.rollback() # 当执行SQL语句代码块发生异常时,可在异常处理代码块中调用rollback()调用回滚保证数据一致性
    
    ##### 数据行相关操作
    1. 构造游标对象
    cursor = connect.cursor(cursor=None)  # cursor()方法内部为构造Cursor对象, 参数为指定创建游标的类
    
    2. 执行SQL
    cursor.execute(query, args) # 单次执行SQL语句,args参数为参数化语句的参数支持列表、元组、字典可迭代对象
    cursor.excutemany(query, args) # 批量执行SQL语句,args参数为嵌套元组、字典
    """单次执行example"""  
    # 1. 使用列表或元组进行参数化
    sql = "SELECT user.id FROM user WHERE user.username = %s AND age = %s"
    cursor.execute(sql, ("angel", "30"))
    # 2. 使用位置参数进行参数化
    sql = "SELECT user.id FROM user WHERE user.username = %s AND age = %s"
    cursor.execute(sql, "angel", "30")
    # 3. 使用字典参数进行参数化
    sql = "SELECT user.id FROM user WHERE user.username = %(username)s"
    cursor.execute(sql, {"username": "angel"})
    
    """批量执行example"""
    cursor.executemany(query, args), 返回结果为受影响的数据行行数
    # example批量参数sql语句,适用于插入数据
    # 1. 使用列表或元组进行参数化
    sql = "INSERT INTO user(username, age) FROM user VALUES (%s, %s)"
    cursor.executemany(sql, [("angel", "30"), ("angel01", "30")])
    # 2. 使用字典参数进行数化
    sql = "INSERT INTO user(username, age) FROM user VALUES (%(username)s, %(age)s)"
    cursor.executemany(sql, [{"username": "angel", "age": "30"}, {"username": "angel01", "age": "30"}])
    
    3. 关闭游标连接
    cursor.close() # 在执行增、删、改、查操作后需要调用游标对象close方法关闭游标连接
    
    4. 通过游标对象获取查询数据
    # 前置条件时需要调用execute()成功
    query_set = cursor.fetchone() # 获取第一条数据
    query_sets = cursor.fetchall() # 获取全部数据
    
    5. 通过游标对象获取自定义数量数据
    data = cursor.fetchmany(size)
    
    6. 使游标对象位移
    cursor.scroll(1, mode="relative") # 相对位移,相对于游标当前位置发生位移
    cursor.scroll(4, mode="absolute") # 绝对位移,相对于数据表起始位置发生位移
    
    7. 获取新插入数据的自增ID
    cur_auto_increment = cursor.lastrowid # 注意,如果是批量插入,只能获取到最后一次插入完后到自增ID
    
    ##### 存储过程相关操作
    1. 执行存储过程
    cursor.callproc("${PROC_NAME}", args=(proc_params1, proc_params_2, ...))
    2. 获取存储过程的查询数据集
    query_set =cursor.fecthone() # 获取第一行数据
    query_sets =cursor.fetchall() # 获取所有行数据
    
    3. 获取存储过程的返回结果(不是查询数据集),需要两步:
    # 查询存储过程结果
    cursor.execute("SELECT @_${PROC_NAME}_${PARAMS_INDEX}, @_${PROC_NAME}_${PARAMS_INDEX}") 
    # 获取存储过程结果数据集
    proc_status = cursor.fetchall()
    PS:工作原理, pymysql内部帮助生成了下面这一段SQL代码
    # SQL设置存储过程参数初始值的代码
    SET @_proc_0 = 1; 
    SET @_proc_1 = 0;
    # SQL执行存储过程的代码
    CALL proc(@_proc_0, @_proc_1);
    # SQL查询存储过程的返回结果代码,只能查询out类型参数
    SELECT @_proc_0, @_proc_1;