一、MySQL基础回顾

MySQL基础概念

• 数据库（DB）：是一个按数据结构来存储和管理数据的计算机软件系统

• 数据库管理系统（DBMS）：是专门用于管理数据库的计算机系统软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能

  • 1，数据定义语言（DataDescriptionLanguage，简称DDL）和相应的建库机制。用户利用DDL可以方便地进行数据库建立、修改、变更及扩充功能；

  • 2，数据操纵语言（DataManipulationlanguage，简称DML），实现数据的插入、修改、删除、查询、统计等数据存取操作的功能称为数据操纵功能；

  • 3，数据库维护功能：数据的载入、转储、重组织功能及数据库的恢复功能；

  • 4，数据库运行管理：并发控制、数据的存取控制、数据完整性条件的检查和执行、数据库内部的维护；

• 数据库应用系统（DAS）：就是使用数据库技术的系统数据库应用系统有很多，基本上所有的信息系统都是数据库应用系统。它通常由软件、数据库和数据管理员组成

• 数据管理员（DBA）：负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。数据库管理员一般是由业务水平较高，资历较深的人员担任。

2、数据库分类

• 网状型数据库：处理以记录类型为结点的网状数据模型的数据库

• 层次型数据库：用树形结构表示实体及其联系的模型称为层次模型

• 关系型数据库：在关系模型中，数据的逻辑结构为满足一定条件的二维表，表具有固定的列数和任意的行数，在数学上称为“关系”；

  • 1，关系型数据库的三范式；
    第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
    第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。
    第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

  • 2，常见的关系型数据库：
    1、Oracle：大型企业数据库，支持数据量，速度较快，安全性非常好，提供完善的存储过程支持；新的版本提供了众多新功能；
    2、DB2（IBM）：大型企业数据库，支持数据量，速度较快，安全性较好；
    3、SQL Server（MS）：大型企业数据库，支持数据量，速度较快，安全性较好；
    4、MySQL（Oracle）：性能不错，使用方便，体积小，易扩展；是目前使用最广的关系型数据库；
    5、PostgreSQL（postgres）：实现更完整，功能更强大；

  • NOSQL：泛指非关系型的数据库；

  • 1、NoSQL分类：
    1，键值(Key-Value)存储数据库：这一类数据库主要会使用到一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据；
    2，列存储数据库：这部分数据库通常是用来应对分布式存储的海量数据。键仍然存在，但是它们的特点是指向了多个列；
    3，文档型数据库：同第一种键值存储相类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储；
    4，图形(Graph)数据库：使用图模型存储；

  • 2、常见NoSQL数据库：
    1，MongoDB：文档型数据库；功能丰富；查询语言强大；并发效率不高，查询效率不算高；
    2，Hbase：高可靠性、高性能、面向列、可伸缩的分布式存储系统；
    3，Redis：key-value存储系统（主要面对内存数据库，也可持久化）；安全性；主从复制；运行异常快，支持事务，丰富类型的key类型；

3、MySQL文件目录

• bin：MySQL相关二进制文件存储目录，存放了很多MySQL提供的系统工具；ex：mysql；mysqld；mysqldump；
• data：MySQL默认的数据存放目录，一般不会存放在这里；
• include：包含一些头文件，提供其他程序连接mysql的API接口；
• lib：MySQL依赖的库；
• share：存放几个初始表文件和每种语言的errmsg.sys错误日志；
• mysql-test：mysql测试套件；
• scripts：这个Perl脚本的作用是创建mysql最初需要的几个系统表，它的建表sql文件在share目录中；
• sql-bench：对数据库进行压力测试的工具；
• my.ini：MySQL的基础配置文件；

4、MySQL配置文件

• my.ini或者my.conf是MySQL服务的配置文件，MySQL的启动可以通过配置文件，也可以直接通过命令行参数；

  • 1、配置项中的[client]和[mysqld]：针对MySQL服务的配置是放在[mysqld]后面的；

  • 2、一些比较常见的配置项：

    • 1，port：启动端口；
    • 2，basedir：MySQL的主目录；
    • 3，datadir：MySQL数据文件存放的位置，默认就是在MySQL主目录的data文件夹下；一般会进行调整；
    • 4，character-set-server：默认的字符编码；
    • 5，default-storage-engine：默认的存储引擎；
    • 6，max_connections：最大允许连接数；

5、MySQL数据文件

• 1、数据文件存放格式，注意MyISAM和InnoDB的文件不同格式；
• 2、常见的文件类型：
  • .frm：与表相关的元数据（meta）信息，比如表结构定义；所有表都需要这个文件；
  • .MYD：MyISAM 存储引擎专用，存放MyISAM 表的数据；
  • .MYI：MyISAM 存储引擎专用，主要存放MyISAM 表的索引相关信息；
  • .ibd和ibdata：存放InnoDB数据；
    • ibdata：共享存储空间，相当于所有InnoDB的数据都存放在公共的这个ibdata文件中；
    • .ibd：独享存储空间，相当于每一个表的数据还是保存在自己独立的文件中，可以通过innodb_file_per_table=1参数来设置，但是独立表空间和共享表空间不能同时存在；
  • db.opt：该数据库的默认配置；

6、权限的相关命令

• MySQL中权限的设置也是存储在数据库中的：

  • mysql.user：用户表，包括用户名，密码，主机，全局权限，性能限制；

  • mysql.db：数据库权限设置；

  • mysql.table_priv：表权限设置；

  • mysql.column_priv：列权限设置；

• GRANT命令

  • MySQL中用于赋权限的命令是GRANT，语法为：完整语法：GRANT权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码" WITH GRANT OPTION

• 例子：

  GRANT ALL PRIVILEGES ON *.* TO admin@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;

创建一个超级管理员，用户名为admin，密码为1234，只能在localhost登陆；可以给别人授权；

• 1、ALL PRIVILEGES即代表所有权限；

• 2、.：所有数据库对象。普通格式为db.table，比如test.*；

• 3、admin@'localhost'：admin用户，只能在本机访问。

  • 1，用户账户包括user@host；
  • 2，host为
    • %：从任意地址访问；
    • 10.250.7.%：只能从指定网段访问；
    • 192.168.1.2：只能从某个IP访问；

• 4、WITH GRANT OPTION：是否允许用户继续授权（注意，这个用户能够授权只能是该用户的权限子集）

• 5、注意，在授权操作之后，使用flush privileges命令刷新权限；

• SHOW GRANTS

  • 查看当前用户的权限： show grants

  • 查看特定用户的权限： show grants for root@localhost

• REVOKE命令

  • REVOKE 权限 ON 数据库对象 FROM 用户；
  • 注意：使用REVOKE撤销全部权限,操作者必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限；

• DROP USER

• 删除用户：DROP USER user

• 注意，user必须包括user@host；

1，创建一个新的数据库priv_test;
create database priv_test;

2，创建一个表user，包括id,name;
use priv_test;
create table user(id bigint(20) primary key auto_increment,name varchar(255));

3，创建一个表book，包括id,isbn;
create table book(id bigint(20) primary key auto_increment,isbn varchar(255));

4，创建一个超级管理员boss,密码为123456，拥有所有权限，并能继续授予权限；
grant ALL PRIVILEGES on *.* TO boss@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

5，使用boss用户登录MySQL；
exit
mysql -uboss -p 123456

6，创建一个项目管理员privadmin，密码1234，拥有对priv_test库所有的权限；
grant ALL PRIVILEGES on priv_test.* TO privadmin@localhost IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges;

7，创建一个普通用户privuser,密码1234，能对priv_test数据库所有表做CRUD；
grant insert,update,select,delete on priv_test.* TO privuser@localhost IDENTIFIED BY '1234' 
flush privileges;

8，创建一个HR用户privhr,密码1234，只能对user表进行CRUD；
grant insert,update,select,delete on priv_test.user TO privhr@localhost IDENTIFIED BY '1234';
flush privileges;