mysql中自带数据库表介绍
- mysql.user 全局 用户信息表 有Host、User、Password等字段
- mysql.db 数据库 存储用户对一个数据库的所有的操作权限。
- tables_priv 数据表 记录对一个表的授权记录
- columns_priv 列级表 记录对表的某一列的授权记录。
数据库的账户管理
- 介绍：
- 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud。
- MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：
- 1.服务实例级账号：启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
- 2.数据库级别账号：对特定数据库执行增删改查的所有操作
- 3.数据表级别账号：对特定表执行增删改查等所有操作
- 4.字段级别的权限：对某些表的特定字段进行操作
- 5.存储程序级别的账号：对存储程序进行增删改查的操作
- 账户管理常用操作：
- 查看账户 （需要实例级别账户）
- 查看user表的结构
- desc user\G;
- 查看所有用户的信息
- select host,user,authentication_string from user;
- 备注：
- host表示允许访问的主机（可以是主机名也可以是ip地址）
- user表示用户名
- authentication_string表示密码，为加密后的值
- 创建账户并授予权限 （需要实例级别账户）
- 创建用户并授予权限
- grant （权限1，权限2.....） on （数据库/表） to （'用户名'@'访问主机'） identified by '密码';
- 直接授予用户权限
- grant （权限1，权限2.....） on （数据库/表） to（ 账户1,账户2,...） with grant option;
- 授权备注：
- 常用权限主要包括：select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
- 如果分配所有权限，可以使用all privileges
- 当想赋予给某个用户部分字段的权限要用权限名（字段名）来限制；
- 当数据库被 *.*代替， 代表所有数据库中的所有表
- 实例：
- grant all privileges on python.* to 'py'@'%' identified by '123';
- 备注：
- 操作python数据库的所有表python.*
- 访问主机通常使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库
- 访问主机可以设置成localhost或具体的ip，表示只允许本机或特定主机访问
- 权限刷新
- flush privileges
- 查看用户有哪些权限 （需要实例级别账户）
- show grants for py;
- 回收权限 （需要实例级别账户）
- revoke 权限列表 on 数据库名.* from '用户名'@'主机';
- 修改密码
- 语法1：（不需要登录）
- mysqladmin -u 用户名 -p password '新密码'
- 语法2：使用root登录，修改mysql数据库的user表、
- update user set authentication_string=password('???') where user='用户名’;
- 修改密码后需要刷新权限
- 删除账户 （需要实例级别账户）
- 语法1：
- drop user '用户名'@'主机'；
- 语法2：
- delete from user where user='用户名';
- 推荐使用语法1删除用户
- 如果主机的字母大写时，使用语法1删除失败，采用语法2方式
Mysql函数
- 创建函数
- 作用：将经常用到的处理方法和功能同意的功能集成为一个函数，可以在SQL语句中直接中调用
- 函数定义的作用范围：函数是定义在目前正在使用的数据库中，作用域也是本数据库；
- 创建函数的语法：
- delimiter $$ “因为函数中会利用； 所以要重新定义分隔符”
- create function 函数名称(参数列表) returns 返回类型 “定义函数名称、参数及数值类型、返回值及数值类型”
- begin
- sql语句
- end
- $$ “函数定义结束”
- delimiter ; “定义函数后恢复分隔符”
- 实例：
- delimiter $$
- create function py_trim(str varchar(100)) returns varchar(100)
- begin
- declare x varchar(100);
- set x=ltrim(rtrim(str));
- return x;
- end
- $$
- delimiter ；
- 查看函数
- 解释：所有函数存储在mysql数据库下的proc表中，存储过程与函数都存储在proc表中，区别在type字段，func表中无数据。
- 示例：select name,type from mysql.proc where db='python';
- 调用函数
- select 函数名称(参数列表)
- 调用非本数据库的函数，应该使用数据库名.函数名称 来使用函数；
- 删除函数
- drop function 函数名称;
- 变量 （函数中的临时变量）
- 临时变量（函数中的临时变量）
- 作用：在定义函数的时候使用
- declare 变量名 类型 default 默认值; ------ 声明临时变量，作用只在begin ..end 之间
- set 变量名=值 ; --------设置变量值；
- 用户变量/会话变量（作用全局的变量）
- 作用：只有在当前连接用户有效,其他连接用户无法访问，退出后将不能访问；
- set @maxage = 0; -------设置用户变量
- select @maxage; --------- 查看用户变量
- select @maxage = max(age) from students; ------在select语句中给用户变量赋值 （注意在select中=代表判断，需用：=来代替）
- 系统变量（作用全局的变量）
- 作用：任何一个用户都可以访问，使用 @@来标识系统变量。
- 查询所有系统变量
- show variables;
- 查看用户变量
- select @@max_connections;
- 当前用户/会话中修改系统变量；
- set max_connections = 1000;
- if 判断
- 语法
- if 条件1 then
- 语句1;
- elseif 条件2 then
- 语句2;
- else
- 语句
- end if;
- while循环
- 语法
- while 条件 do
- 语句;
- end while;
- 注意：
- 退出循环：leave，相当于break
- 退出本次循环：iterate，相当于continue
Mysql存储过程
- 存储过程与自定义函数的相同点与区别：
- 相同点：
- 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
- 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中
- 已经编译好的 sql, 不需要重复编译
- 减少网络交互,减少网络访问流量
- 区别：
- 标识符不同,函数的标识符是 function, 过程:procedure
- 函数中有返回值,且必须有返回值,而过程没有返回值,但是可以通过它来设置参数
- 类型(in,out)来实现多个参数或者返回值
- 函数使用 select 调用,存储过程需要使用 call 调用
- select 语句可以在存储过中调用,但是除了 select ... into 之外的 select 语句都不能再函数中调用
- 通过 in out 参数,过程相关函数更加灵活,可以返回多个结果
- 创建存储过程
- 格式
- delimiter //
- create procedure 存储过程名称(参数列表)
- begin
- sql语句；
- end
- //
- delimiter ;
- 查看存储过程
- select name,type,body from mysql.proc where db='python';
- 删除存储过程
- 删除
- drop procedure 存储过程名称;
Mysql视图
- 视图：
- 对于复杂的关联查询等，在多个地方被使用，这时候我们可以对基表的操作进行封装来定义一个视图；
- 创建视图：
- 语法：
- create view 视图名称 as select语句;
- 示例：
- create view v_stu_sco as
- select students.*,scores.score from scores
- inner join students on scores.stuid=students.id;
- 查看视图：
- show tables;
- 删除视图：
- drop view 视图名称;
- 使用：
- select * from v_stu_score; “视图的用途就是查询”
- 视图的应用场景：
- 安全原因， 视图可以隐藏一些数据。 如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数；
- 可使复杂的查询易于理解和使用
Mysql的事物
- 什么事物
- MySQL 事务主要用于处理操作量大，复杂度高的数据，也可能是一条非常简单的SQL语句。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。这是事务模型区别于简单文件系统的重要特征。
- 在 MySQL中Innodb 数据库引擎的数据库或表支持事务
- 举例在哪种场景中需要使用事物
- 查询用户余额
- 检测用户密码
- 开始转账操作 锁定用户账户
- 输入转账金额 判断是否超出转账额度限制和余额限制
- 事务的特性
- 事物的条件：
- 一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性）
- 原子性：一组事物，要么全部成功，要么全部撤回；
- 一致性； 即在执行事务之前和执行事务之后，必须都处于一致性的状态，比如：有非法数据（外键约束之类），系统可以自动该撤销事物执行并使其自身返回到事物执行之前的初始状态；不允许有中间状态产生；
- 隔离性：要求每个事物对象与其他事物的操作能够相互独立运行，不可见。实现事物隔离，需要牺牲速度。相当于在一个事物在运行的时候会在响应修改过的资源中获取互斥锁资源，其他事物中不可见到当前事物提交前的结果，也不能对表进行操作；
- 永久性：一旦事物提交，结果是永久的
- 事物的使用
- 开启事物 （开启事物对一个数据进行操作，将会对该资源上锁，其他事物不能对该资源的数据进行修改）
- 要求：
- 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句，后就会马上执行 COMMIT 操作，因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
- 语法：
- BEGIN； （roll back/commit操作后会自动取消事物的记录）
- START TRANSACTION； （roll back/commit操作后会自动取消事物的记录）
- SET AUTOCOMMIT=0；（需要手动修改AUTOCOMMIT的属性）
- 提交事物：
- 要求：
- 当任务中所有流程全部执行完成，使用COMMIT进行提交确认；如果有流程失败则回滚，表示之前执行的流程全部取消 ， 提交后边不能进行回滚操作
- 语法： commit;
- 回滚：
- 语法： rollback； 对事物进行回滚，将begin 后的操作全部回滚
- 隐式提交的SQL语句 （事物不起作用的操作）
- 有一种情况 不管在任何情况下(即使设置不自动提交也会提交的)都会自动提交的SQL语句就是隐式的COMMIT操作
- DDL语句 ALTER DATABASE/TABLE/PROCEDURE/VIEW ；CREATE DATABASE/TABLE/PROCEDURE/VIEW ；DROP DATABASE/TABLE/PROCEDURE/VIEW 等和表结构相关操作都属于这类
- 修改MySqL架构的操作CREATE USER/DROP USER/GRANT / RENAME USER/REVOKE/SETPASSWORD
Mysql的索引
- 什么是索引
- 当数据库中数据量很大时，查找数据会变得很慢，这时候可以通过索引来优化搜索，记录存储数据的位置。当插入数据的时候和更改数据的时候都会更新对应的索引信息；
- 主键和唯一索引，都是索引，可以提高查询速度
- 主键和唯一索引都是字段内容不重复的索引；
- 主键本身就具有唯一性约束，会记录数据的物理存储位置，所以主键也具有索引功能，且一个表中只能有一个主键；
- 索引会单独创建一个目录，对应数据的位置
- 一个表中只允许有一个主键，但是允许有多个唯一索引
- 备注：
- 当创建外键和主键的时候，数据库会默认创建一个索引，用来记录数据存储的物理位置；
- 索引的分类
- 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
- 组合索引，即一个索引包含多个列
- 语法
- 查看索引
- show index from 表名;
- 创建索引（四种方法）
- CREATE INDEX 索引名称 ON 表名(字段名称(长度))
- ALTER TABLE table_name ADD INDEX index_name (column_list) ;
- ALTER TABLE table_name ADD UNIQUE index_name(column_list)
- ALTER TABLE table_name ADD PRIMARY KEY (column_list)
- 备注：
- 当创建组合索引的时候，需要传递两个字段参数，表示创建两个字段的符合索引
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分
- 删除索引 （两种方法）
- drop index 索引名称 on 表名;
- alter table table_name drop index index_name ;
- 索引的缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要更新索引数据
- 建立索引会占用磁盘空间
- 综上应该根据实际情况创建合适的索引
- 如何使用索引
- 分析select语句的结构，针对在where中出现次数较多的字段可以建立索引；
- 对于使用筛选较少的字段，要进行考虑，因为创建越多的索引对于磁盘占用、索引维护、CURD的时间都是一种损失；
备注：
- 开启运行时间监控：set profiling = 1;
- 查看执行时间： show profiles;
- 导入文件的SQL命令： source file_name;
