前言
数据库简介
- 数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
- 数据库是一个按数据结构来存储和管理数据的计算机软件系统。数据库的概念实际包括两层意思：
（1）数据库是一个实体，它是能够合理保管数据的“仓库”，用户在该“仓库”中存放要管理的事务数据，“数据”和“库”两个概念结合成为数据库。
（2）数据库是数据管理的新方法和技术，它能更合适的组织数据、更方便的维护数据、更严密的控制数据和更有效的利用数据。
- 我们常用的数据存储方式，比如内存和文件，数据保存在内存中时，存取速度快，但是数据不能永久保存 ；数据保存在文件时，数据永久，但是速度比内存操作慢，频繁的IO操作，并且查询数据不方便。而数据库的存储方式，数据持久化保存，高可靠，高可用，数据的快速提取。所以在企业中得以广泛使用。 数据库管理系统
数据库管理系统（DBMS）
-
数据库管理系统（Database Management System，DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。数据库管理系统可以依据它所支持的数据库模型来作分类，例如关系式、XML；或依据所支持的计算机类型来作分类，例如服务器群集、移动电话；或依据所用查询语言来作分类，例如SQL、XQuery；或依据性能冲量重点来作分类，例如最大规模、最高运行速度；亦或其他的分类方式。不论使用哪种分类方式，一些DBMS能够跨类别，例如，同时支持多种查询语言。
-
数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操纵与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。它的安全直接关系到整个数据库系统的安全，其防护手段主要有：
（1）使用正版数据库管理系统并及时安装相关补丁。
（2）做好用户账户管理，禁用默认超级管理员账户或者为超级管理员账户设置复杂密码；为应用程序分别分配专用账户进行访问；设置用户登录时间及登录失败次数限制， 防止暴力破解用户密码。
（3）分配用户访问权限时，坚持最小权限分配原则，并限制用户只能访问特定数据库，不能同时访问其他数据库。
（4）修改数据库默认访问端口，使用防火墙屏蔽掉对 外开放的其他端口，禁止一切外部的端口探测行为。
（5）对数据库内存储的重要数据、敏感数据进行加密存储，防止数据库备份或数据文件被盗而造成数据泄露。
（6）设置好数据库的备份策略，保证数据库被破坏后能迅速恢复。
（7）对数据库内的系统存储过程进行合理管理，禁用掉不必要的存储过程，防止利用存储过程进行数据库探测与攻击。
（8）启用数据库审核功能，对数据库进行全面的事件跟踪和日志记录。
数据库的类型
关系数据库 关系型数据库
存储的格式可以直观地反映实体间的关系。关系型数据库和常见的表格比较相似，关系型数据库中表与表之间是有很多复杂的关联关系的。 常见的关系型数据库有Mysql，SqlServer等。在轻量或者小型的应用中，使用不同的关系型数据库对系统的性能影响不大，但是在构建大型应用时，则需要根据应用的业务需求和性能需求，选择合适的关系型数据库。
非关系型数据库（NoSQL）
随着近些年技术方向的不断拓展，大量的NoSql数据库如MongoDB、Redis、Memcache出于简化数据库结构、避免冗余、影响性能的表连接、摒弃复杂分布式的目的被设计。 指的是分布式的、非关系型的、不保证遵循ACID原则的数据存储系统。NoSQL数据库技术与CAP理论、一致性哈希算法有密切关系。所谓CAP理论，简单来说就是一个分布式系统不可能满足可用性、一致性与分区容错性这三个要求，一次性满足两种要求是该系统的上限。而一致性哈希算法则指的是NoSQL数据库在应用过程中，为满足工作需求而在通常情况下产生的一种数据算法，该算法能有效解决工作方面的诸多问题但也存在弊端，即工作完成质量会随着节点的变化而产生波动，当节点过多时，相关工作结果就无法那么准确。这一问题使整个系统的工作效率受到影响，导致整个数据库系统的数据乱码与出错率大大提高，甚至会出现数据节点的内容迁移，产生错误的代码信息。但尽管如此，NoSQL数据库技术还是具有非常明显的应用优势，如数据库结构相对简单，在大数据量下的读写性能好；能满足随时存储自定义数据格式需求，非常适用于大数据处理工作。 NoSQL数据库适合追求速度和可扩展性、业务多变的应用场景。对于非结构化数据的处理更合适，如文章、评论，这些数据如全文搜索、机器学习通常只用于模糊处理，并不需要像结构化数据一样，进行精确查询，而且这类数据的数据规模往往是海量的，数据规模的增长往往也是不可能预期的，而NoSQL数据库的扩展能力几乎也是无限的，所以NoSQL数据库可以很好的满足这一类数据的存储。NoSQL数据库利用key-value可以大量的获取大量的非结构化数据，并且数据的获取效率很高，但用它查询结构化数据效果就比较差。
NoSQL数据库分类
（1）键值对存储（key-value）：代表软件Redis，它的优点能够进行数据的快速查询，而缺点是需要存储数据之间的关系。
（2）列存储：代表软件Hbase，它的优点是对数据能快速查询，数据存储的扩展性强。而缺点是数据库的功能有局限性。
（3）文档数据库存储：代表软件MongoDB，它的优点是对数据结构要求不特别的严格。而缺点是查询性的性能不好，同时缺少一种统一查询语言。
（4）图形数据库存储：代表软件InfoGrid，它的优点可以方便的利用图结构相关算法进行计算。而缺点是要想得到结果必须进行整个图的计算，而且遇到不适合的数据模型时，图形数据库很难使用。
NoSQL与关系型数据库的区别
存储方式
传统的关系型数据库采取表格的存储方式，数据以行和列的方式进行存储，要读取和查询都十分方便，而非关系新数据库不适合这样的传统表格存储方式，通常以数据集的方式，大量的数据集中存储在一起，类似于键值对，图结构或者文档。
存储结构
关系型数据库按照结构化的方法存储数据， 每个数据表都必须对各个字段定义好（也就是先定义好表的结构），再根据表的结构存入数据，这样做的好处就是由于数据的形式和内容在存入数据之前就已经定义好了，所以整个数据表的可靠性和稳定性都比较高，但带来的问题就是一旦存入数据后，如果需要修改数据表的结构就会十分困难。而NoSQL数据库由于面对的是大量非结构化的数据的存储，它采用的是动态结构，对于数据类型和结构的改变非常的适应，可以根据数据存储的需要灵活的改变数据库的结构。
存储规范
关系型数据库为了避免重复、规范化数据以及充分利用好存储空间，把数据按照最小关系表的形式进行存储，这样数据管理的就可以变得很清晰、一目了然，当然这主要是一张数据表的情况。如果是多张表情况就不一样了，由于数据涉及到多张数据表，数据表之间存在着复杂的关系，随着数据表数量的增加，数据管理会越来越复杂。而NoSQL数据库的数据存储方式是用平面数据集的方式集中存放，虽然会存在数据被重复存储，从而造成存储空间被浪费的问题（从当前的计算机硬件的发展来看，这样的存储空间浪费的问题微不足道），但是由于基本上单个数据库都是采用单独存放的形式，很少采用分割存放的方式，所以这样数据往往能存成一个整体，这对于数据的读写提供了极大的方便。
扩展方式
当前社会和科学飞速发展，要支持日益增长的数据库存储需求当然要求数据库有良好的扩展性能，并且要求数据库支持更多数据并发量，扩展方式是NoSQL数据库与关系型数据库差别最大的地方，由于关系型数据库将数据存储在数据表中，数据操作的瓶颈出现在多张数据表的操作中，而且数据表越多这个问题越严重，如果要缓解这个问题，只能提高处理能力，也就是选择速度更快性能更高的计算机，这样的方法虽然可以一定的拓展空间，但这样的拓展空间一定有非常有限的，也就是关系型数据库只具备纵向扩展能力。而NoSQL数据库由于使用的是数据集的存储方式，它的存储方式一定是分布式的，它可以采用横向的方式来开展数据库，也就是可以添加更多数据库服务器到资源池，然后由这些增加的服务器来负担数据量增加的开销。
查询方式
关系型数据库采用结构化查询语言（即SQL）来对数据库进行查询，SQL早已获得了各个数据库厂商的支持，成为数据库行业的标准，它能够支持数据库的CRUD（增加，查询，更新，删除）操作，具有非常强大的功能，SQL可以采用类似索引的方法来加快查询操作。NoSQL数据库使用的是非结构化查询语言（UnQL），它以数据集（像文档）为单位来管理和操作数据，由于它没有一个统一的标准，所以每个数据库厂商提供产品标准是不一样的，NoSQL中的文档Id与关系型表中主键的概念类似，NoSQL数据库采用的数据访问模式相对SQL更简单而精确。
规范化
在数据库的设计开发过程中开发人员通常会面对同时需要对一个或者多个数据实体（包括数组、列表和嵌套数据）进行操作，这样在关系型数据库中，一个数据实体一般首先要分割成多个部分，然后再对分割的部分进行规范化，规范化以后再分别存入到多张关系型数据表中，这是一个复杂的过程。好消息是随着软件技术的发展，相当多的软件开发平台都提供一些简单的解决方法，例如，可以利用ORM层（也就是对象关系映射）来将数据库中对象模型映射到基于SQL的关系型数据库中去以及进行不同类型系统的数据之间的转换。对于NoSQL数据库则没有这方面的问题，它不需要规范化数据，它通常是在一个单独的存储单元中存入一个复杂的数据实体。
事务性
关系型数据库强调ACID规则（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）），可以满足对事务性要求较高或者需要进行复杂数据查询的数据操作，而且可以充分满足数据库操作的高性能和操作稳定性的要求。并且关系型数据库十分强调数据的强一致性，对于事务的操作有很好的支持。关系型数据库可以控制事务原子性细粒度，并且一旦操作有误或者有需要，可以马上回滚事务。而NoSQL数据库强调BASE原则（基本可用（Basically Available）、软状态（Soft-state）、最终一致性（Eventual Consistency）），它减少了对数据的强一致性支持，从而获得了基本一致性和柔性可靠性，并且利用以上的特性达到了高可靠性和高性能，最终达到了数据的最终一致性。NoSQL数据库虽然对于事务操作也可以使用，但由于它是一种基于节点的分布式数据库，对于事务的操作不能很好的支持，也很难满足其全部的需求，所以NoSQL数据库的性能和优点更多的体现在大数据的处理和数据库的扩展方面。
读写性能
关系型数据库十分强调数据的一致性，并为此降低读写性能付出了巨大的代价，虽然关系型数据库存储数据和处理数据的可靠性很不错，但一旦面对海量数据的处理的时候效率就会变得很差，特别是遇到高并发读写的时候性能就会下降的非常厉害。而NoSQL数据库相对关系型数据库优势最大的恰恰是应对大数据方面，也就是对于大量的每天都产生非结构化的数据能够高性能的读写，这是因为NoSQL数据库是按key-value类型进行存储的，以数据集的方式存储的，因此无论是扩展还是读写都非常容易，并且NoSQL数据库不需要关系型数据库繁琐的解析，所以NoSQL数据库大数据管理、检索、读写、分析以及可视化方面具有关系型数据库不可比拟的优势。
授权方式
关系型数据库常见的有Oracle，SQLServer，DB2，Mysql，除了Mysql大多数的关系型数据库如果要使用都需要支付一笔价格高昂的费用，即使是免费的Mysql性能也受到了诸多的限制。而对于NoSQL数据库，比较主流的有redis，HBase，MongoDb，memcache等产品，通常都采用开源的方式，不需要像关系型数据库那样，需要一笔高昂的花费。
MySQL安装及基础
MySQL 组成
-
客户端程序
-
mysql: 交互式的CLI工具
-
mysqladmin：基于mysql协议管理mysqld
-
mysqlimport：数据导入工具
-
MyISAM存储引擎的管理工具：
-
myisamchk：检查MyISAM库
-
myisampack：打包MyISAM表，只读
-
服务器端程序
-
mysqld_safe
-
mysqld
-
mysqld_multi 多实例 ，示例：mysqld_multi
[root@localhost ~]# yum info mariadb
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.cn99.com
* extras: mirrors.njupt.edu.cn
* updates: mirrors.cn99.com
可安装的软件包
名称 ：mariadb
架构 ：x86_64
时期 ：1
版本 ：5.5.68
发布 ：1.el7
大小 ：8.8 M
源 ：base/7/x86_64
简介 ： A community developed branch of MySQL
网址 ：http://mariadb.org
协议 ： GPLv2 with exceptions and LGPLv2 and BSD
描述 ： MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server
: daemon (mysqld) and many different client programs and
: libraries. The base package contains the standard MariaDB/MySQL
: client programs and generic MySQL files.
MySQL被Sun收购后，搞了个过渡的6.0版本，没多久就下线了,后来被Oracle收购后，终于迎来了像样的5.6版本，之后就是5.7、8.0版本。由于6.0版本号已被用过，7.x系列版本专用于NDB Cluster，因而新版本号从8.0开始。
MySQL安装
方法一：yum安装
1. #创建mysql源
cd /etc/yum.repos.d/
vim mysql.repo
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
2. #安装服务mysql-community-server
yum -y install mysql-community-server
3. #启动服务
systemctl start mysqld
ss -ntap |grep 3306
4. #登入数据库，但是密码是默认的，需要查看暗码
mysql -u root -p
grep password /var/log/mysqld.log
mysql -u root -p'+8saq%5+ljzG'
#特殊符号用单引号引起来
5. #登入数据库后必须修改密码(密码需要复杂，大小写，数字混合，标点符号)
alter user root@'localhost' identified by 'MiMaShi@9527';
#可以使用mysql> \h查看帮助
示例
- 创建yum源
- 安装服务
- 启动服务，查看端口号，mysql默认端口号为3306
- 登入数据库，但是密码是默认的，需要查看暗码
- 登入数据库后必须修改密码（注意修改密码复杂性，太简单会报错）
方法二：编译安装
--------编译安装mysqld 服务--------
1.将安装mysql 所需软件包传到/opt目录下
mysql-5.7.17.tar.gz
boost_1_59_0.tar.gz #支持c++的运行库
2.安装环境依赖包
yum -y install \
gcc \
gcc-c++ \
ncurses \ #字符终端下图形互动功能的动态库
ncurses-devel \ #ncurses开发包
bison \ #语法分析器
cmake #mysql需要用cmake编译安装
----------------------------------------------------------------------------------------------------------
yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
3.配置软件模块
tar zxvf mysql-5.7.17.tar.gz
tar zxvf boost_1_59_0.tar.gz
cd /opt
mv boost_1_59_0 /usr/local/boost #重命名
cd /opt/mysql-5.7.17/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ #指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \ #指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \ #指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8 \ #指定默认使用的字符集编码，如 utf8
-DDEFAULT_COLLATION=utf8_general_ci \ #指定默认使用的字符集校对规则
-DWITH_EXTRA_CHARSETS=all \ #指定支持其他字符集编码
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \ #安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #安装BLACKHOLE存储引擎
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ #安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \ #指定数据库文件的存储路径
-DWITH_BOOST=/usr/local/boost \ #指定boost的路径，若使用mysql-boost集成包安装则-DWITH_BOOST=boost
-DWITH_SYSTEMD=1 #生成便于systemctl管理的文件
存储引擎选项：
MYISAM，MERGE，MEMORY，和CSV引擎是默认编译到服务器中，并不需要明确地安装。
静态编译一个存储引擎到服务器，使用-DWITH_engine_STORAGE_ENGINE= 1
可用的存储引擎值有：ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), 和PERFSCHEMA (Performance Schema)
----------------------------------------------------------------------------------------------------------
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
注意：如果在CMAKE的过程中有报错，当报错解决后，需要把源码目录中的CMakeCache.txt文件删除，然后再重新CMAKE，否则错误依旧
4.编译及安装
make && make install
5.创建mysql用户
useradd -M -s /sbin/nologin mysql
6.修改mysql 配置文件
vim /etc/my.cnf #删除原配置项，再重新添加下面内容
[client] #客户端设置
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysql] #服务端设置
port = 3306
socket = /usr/local/mysql/mysql.sock
auto-rehash #开启自动补全功能
[mysqld] #服务全局设置
user = mysql #设置管理用户
basedir=/usr/local/mysql #指定数据库的安装目录
datadir=/usr/local/mysql/data #指定数据库文件的存储路径
port = 3306 #指定端口
character-set-server=utf8 #设置服务器字符集编码格式为utf8
pid-file = /usr/local/mysql/mysqld.pid #指定pid 进程文件路径
socket=/usr/local/mysql/mysql.sock #指定数据库连接文件
bind-address = 0.0.0.0 #设置监听地址，0.0.0.0代表允许所有，如允许多个IP需空格隔开
skip-name-resolve #禁用DNS解析
max_connections=2048 #设置mysql的最大连接数
default-storage-engine=INNODB #指定默认存储引擎
max_allowed_packet=16M #设置数据库接收的数据包大小的最大值
server-id = 1 #指定服务ID号
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
----------------------------------------------------------------------------------------------------------
sql_mode常用值如下:
NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
STRICT_TRANS_TABLES
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_AUTO_VALUE_ON_ZERO
mysql中的自增长列可以从0开始。默认情况下自增长列是从1开始的，如果你插入值为0的数据会报错
NO_ZERO_IN_DATE
不允许日期和月份为零
NO_ZERO_DATE
mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。默认情况下数据被零除时MySQL返回NULL
PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
----------------------------------------------------------------------------------------------------------
[client]
port = 3306
socket=/usr/local/mysql/mysql.sock
[mysql]
port = 3306
socket = /usr/local/mysql/mysql.sock
auto-rehash
[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
7.更改mysql安装目录和配置文件的属主属组
chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf
8.设置路径环境变量
echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
source /etc/profile
9.初始化数据库
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \ #生成初始化密码为空
--user=mysql \ #指定管理用户
--basedir=/usr/local/mysql \ #指定数据库的安装目录
--datadir=/usr/local/mysql/data #指定数据库文件的存储路径
----------------------------------------------------------------------------------------------------------
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
10.添加mysqld系统服务
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ #用于systemctl服务管理
systemctl daemon-reload #刷新识别
systemctl start mysqld.service #开启服务
systemctl enable mysqld #开机自启动
netstat -anpt | grep 3306 #查看端口
11.修改mysql 的登录密码
mysqladmin -u root -p password "abc123" #给root账号设置密码为abc123，提示输入的是原始密码（为空）
12.授权远程登录
mysql -u root -p
grant all privileges on *.* to 'root'@'%' identified by 'abc123';
#授予root用户可以在所有终端远程登录，使用的密码是abc123，并对所有数据库和所有表有操作权限
show databases; #查看当前已有的数据库
1.将安装mysql 所需软件包传到/opt目录下
2.安装环境依赖包
3.配置软件模块
4.编译及安装
5.创建mysql用户
6.修改mysql 配置文件
7.更改mysql安装目录和配置文件的属主属组
8.设置路径环境变量
9.初始化数据库
10.添加mysqld系统服务
11.修改mysql 的登录密码
12.授权远程登录
MySQL基础
数据库结构
1.1 查看已存在数据库信息
查看已存在的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql默认4个数据库
information_schema： 定义访问数据库元数据的方式。数据库名和表名，列的数据类型、访问权限等。
mysql：核心数据库，负责存储数据库用户、权限、关键字等用户自己需要使用的控制和管理信息。
performance_schema：数据库的性能参数，存储引擎等。
sys：sys系统库下包含许多视图，它们以各种方式对preformance_schema表进行聚合计算展示。
1.2 查看数据库表信息
mysql> use mysql; #use后跟数据库名
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
......
Mysql数据库的数据文件都存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于储存数据表文件。每一个数据表对应为三个文件，后缀名分别为’’.frm “.myd” 和“.myi"。当然也有少数以opt、csm、csv、ibd结尾的。
1.3 显示数据表的结构（字段）
mysql> use mysql;#先使用数据库
mysql> desc user;#再查看表
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
或者
mysql> desc mysql.user;#用 数据库名.表名 的格式查看
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
二、SQL基本语句
SQL分类
- DDL:数据定义语言
- DML:数据操纵语
- DQL:数据查询语言（select）
- DCL:数据控制语言
2.1 DDL语句
DDL语句用于创建数据库对象，如库、表、索引等。
1、新建库、表
mysql> create database student;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;#可查看到
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#建表
mysql> use student;
Database changed
mysql> create table info (id int(3) not null primary key,name char(10) not null,address varchar(50) default 'nj');
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info |
+-------------------+
1 row in set (0.00 sec)
#查看表结构
mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(3) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | nj | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
create table 表名（字段01名称 字段01类型 字段01约束，字段02名 字段02类型 字段02约束）存储引擎 字符集
多个字段用逗号加空格隔开。
字段01名称：属性名称，自定义
字段01类型：
int （5）: 整型 00000-99999
double: 浮点型
decimal（5,2）: 有效数字是5位，小数点后面保留2位
fioat: 单精度浮点 4字节
varchar（50）: 可变长度字符串
char（10）: 固定长度字符串\
字段01约束：
非空约束：内容不允许为空
主键约束：非空且唯一 典型的标识
默认值：假如没有填数据，默认预先设定的值
自增值：自动排序1、2、3、4…
存数引擎：myisam innodb
字符集：UTF-8
2、删除库、删除表
mysql> drop database 后面跟库名或者 库名.表名;
Query OK, 0 rows affected (0.02 sec)
2.2 DML语句
DML语句用于对表中的数据进行管理
INSERT:插入新数据
UPDATE：更新原有数据
DELETE：删除不不需要的数据
1.表中插入数据
#方法1：
insert into info (id,name,address) values (1,'lisi','上海'),(3,'wangwu','北京');#对应各个字段
#方法2：
insert into info values (4,'yiyi','北京');
查看：
mysql> select * from info;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 上海 |
| 3 | wangwu | 北京 |
| 4 | yiyi | 北京 |
+----+--------+---------+
3 rows in set (0.00 sec)
2.表中修改、更新数据记录
UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE条件表达式
#将info表中lisi的地址替换为苏州
mysql> update tom set address=‘苏州’ where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看
mysql> select * from info;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 苏州 |
| 3 | wangwu | 北京 |
| 4 | yiyi | 北京 |
+----+--------+---------+
3 rows in set (0.00 sec)
3.修改用户登录数据库密码
mysql> update mysql.user set authentication_string=password('123123') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
4.在数据库中删除指定的数据记录
delete from 表名 where 条件表达式（不带where代表删除表中所有记录）
mysql> select * from student.info;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 苏州 |
| 3 | wangwu | 北京 |
| 4 | yiyi | 北京 |
+----+--------+---------+
3 rows in set (0.00 sec)
mysql> delete from student.info where name='yiyi';
Query OK, 1 row affected (0.01 sec)
mysql> select * from student.info;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 苏州 |
| 3 | wangwu | 北京 |
+----+--------+---------+
2 rows in set (0.00 sec)
5.修改表名与表结构
alter table 旧表名 rename 新表名；
例：alter table zzz rename yyy；\
扩展表结构（增加字段）
alter table 表名 add address varchar（50） default ‘地址不详’；
#default ‘地址不详’：表示此字段设置默认值为地址不详，可与not null配合使用\
mysql> alter table info add address varchar(50) not null default '地址不详';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from info;
Empty set (0.00 sec)
mysql> desc info;
+---------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+--------------+-------+
| id | int(3) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+-------------+------+-----+--------------+-------+
3 rows in set (0.00 sec)
6.修改字段（列）名，添加唯一键
alter table 表名 change 旧列名 新列名 数据类型 [unique key];
#change 可修改字段名、数据类型、约束等所有项。\
删除字段/主键
格式：
alter table 表名 drop 字段名/主键；
##扩展
use student;
create table if not exists info (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
#if not exists:表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4)zerofill:表示若数值不满4位数，则前面用"0"填充，例0001
#auto increment:表示此字段为自增长字段，即每条记录自动递增1,默认从1开始递增；
自增长字段数据不可以重复；
自增长字段必须是主键；
如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key:表示此字段唯一键约束，此字段数据不可以重复：一张表中只能有一个主键，但是一张表可有多个唯一键
#not null:表示此字段不允许为NULL
show create table test02\G #获取数据库的表结构、索引等信息
select * from test02;
2.3 数据库高级操作
1、清空表
delete from info；
# delete清空表后，返回的结果内有删除的记录条目；
delete工作是一行一行的删除记录数据的;如果表中有自增长字段，使用delete from 删除所有记录，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。
truncate table info；
# truncate清空表数据，表还在，没有返回被删除的条目；
truncate工作时是将表结构按原样重新建立，因此在速度上truncate会比delete清空表快；
使用truncate table清空表内数据后，id会从1开始重新记录
drop是删除数据和表；
速度
drop > truncate > delete
安全性
delete 最好
2、临时表
临时建立的表，用于保存一些临时数据，不会长期存在
创建成功后，使用shiwtables命令看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用drop table语句手动直接删除临时表。
PS：无法创建外键
mysql> create temporary table cdc (id int(3) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> describe cdc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into cdc (name,hobby) values ('boy','dog');
Query OK, 1 row affected (0.00 sec)
mysql> select * from cdc;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | boy | dog |
+----+------+-------+
1 row in set (0.00 sec)
mysql> show tables;#并没有cdc表
+-------------------+
| Tables_in_student |
+-------------------+
| info |
| jerry |
| tom |
+-------------------+
3 rows in set (0.00 sec)
#不在硬盘上，在内存上
3、克隆表
like方法：从info表完整复制结构生成test表
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table test like info;
Query OK, 0 rows affected (0.02 sec)
导入数据
mysql> insert into test select * from info;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 苏州 |
| 3 | wangwu | 北京 |
+----+--------+---------+
2 rows in set (0.00 sec)
或者用as
mysql> create table zf as select * from info;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from zf;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | lisi | 苏州 |
| 3 | wangwu | 北京 |
+----+--------+---------+
2 rows in set (0.00 sec)
2.4 DCL数据库用户授权
1、DCL语句设置用户权限（用户不存在时；则会新建用户）
- ‘用户名’:指定将创建的用户名
- 来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost,允许任意主机登录
- 可用通配符：
- 密码’:若使用明文密码，直接输入密码，插入到数据库时由Mysq1自动加密；
- 若使用加密密码，需要先使用SELECT PASSWORD(‘密码’);获取密文，再在语句中添PASSWORD ‘密文’;
- 若省略"IDENTIFIED BY"部分，则用户的密码将为空（不建议使用）
mysql> create user 'lisi'@'%' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
#设置登录密码为abc123的lisi用户，可以从任意终端（%）登录
#若要限制只能本地登录，%换为localhost，（主机名或终端IP地址）
mysql> grant all on *.* to 'lisi'@'%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
#对所有库和所有表（*.*）有全部权限
#若只允许对mysql库中user表有select权限
mysql> grant select on mysql.user to 'lisi'@'%' identified by '123123'；
2.查看当前用户的权限
flush privileges；#刷新权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
3.查看从本地用户登录的权限
mysql> show grants for 'lisi'@'localhost';
+------------------------------------------------------+
| Grants for lisi@localhost |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'localhost' |
| GRANT SELECT ON `mysql`.`user` TO 'lisi'@'localhost' |
+------------------------------------------------------+
4.撤销用户的权限
mysql> revoke select on mysql.user from 'lisi'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'lisi'@'localhost';
+------------------------------------------+
| Grants for lisi@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
5.修改当前密码
#修改当前密码
set password = password('123456');
#修改其他用户密码
set password for 'user1'@'localhost' password('abc123');
忘记密码的解决方法
#忘记root密码的解决办法
修改/etc/my.cnf配置文件，免密登陆mysq1
vim / etc / my . cnf
[mysqld]
skip-grant-tables #添加，使登录mysq1不使用授权表
systemctl restart mysqld
mysq1 #直接登录
然后使用SQL语句修改密码
UPDATE mysql . user SET AUTHENTICATION _ STRING = PASSWORD ( ' abc123 ' ) where user = ' root ' ;
FLUSH PRIVILEGES;
quit
mysql-u root-pabc123
Ps:最后再把/etc/my.cnf配置文件里的skip-grant-tables删除，并重启mysq1服务
Mysql中的约束
-
主键约束（primary key）
-
外键约束（foreign key）
-
非空约束（not null）
-
唯一性约束（unique [key|index]）
-
默认值约束（default）
-
自增约束（auto_increment） 外键的定义：外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，
则字段x称为表二的外键。
创建外键约束作用（误删，修改）,保证数据的完整性和一致性。 主键表和外键表的理解
(1)以公共关键字作主键的表为主键表（父表、主表）
(2)以公共关键字作外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表， 主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。
#创建主表test04
create table test04 (hobid int (4), hobname varchar(50));
#创建从表test05
create table test05 (id int (4) primary key auto_increment,
#可以使用查询表语句结构命令查看外键关联
show create table test05;
desc test04 ;
desc test05;
#插入新的数据记录时，要先主表再从表
insert into test04 values (1,'runing');
insert into test05 values (1,'zhangsan',18,1);
#删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表。
drop tables test05;
drop tables test04;
#查看和删除外键约束
#如果要删除外键约束字段先删除外键约束，再删除外键名
show create table test05;
alter table test05 drop foreign key FK_hob;
alter table test05 drop key FK hob ;
desc test05;
