MySQL数据库初识
1.数据库概述
什么是数据?
- 描述事物的符号记录称为数据.描述事物的符号既可以是数字,也可以是文字,图片,声音,图像等,数据有多种表现形式,他们都可以经过数字化后存入计算机.
什么是数据库(DataBase,简称DB)?
- 数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。
- 数据库的优势:
- 1.解决了操作文件的效率和便捷问题
- 2.解决了多个程序同时使用数据的一致性问题
- 3.解决了安全问题
- 4.解决了并发问题
什么是数据库管理系统(DataBaseManagementSystem,简称DBMS)?
- 数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。
什么是SQL?
- SQL是用于访问和处理数据库的标准的计算机语言,是结构化查询语言
- SQL使我们有能力访问数据库
- SQL是一种ANSI(美国国家标准化组织)的标准计算机语言
- 注:不同数据库系统之间的SQL不能完全相互通用
目前的数据库可以分为两个大类:关系型数据库和非关系型数据库.
- 关系型数据库(RDBMS)模型是把复杂的数据结构归结为简单的二元关系.(即二维表格形式)
- 常见软件:Oracle,MySQL,SQLserver,Access,MariaDB等
- 非关系型数据库(NoSQL)是作为传统关系型数据库的一个有效补充,是为了高性能,高并发而生.
- 常见软件:Redis,MongoDB,memcached等
数据库服务器,数据库管理系统,数据库,表与记录的关系
- 数据库服务器:运行数据库管理软件
- 数据库管理系统(软件):管理数据库
- 数据库:即文件夹,用来组织文件/表
- 表:即文件,用来存放多行内容/多条记录
2.数据库的基本使用
mysql server端:
net start mysql #启动mysql服务
net stop mysql #停止mysql服务
mysql启动了客户端,客户端会自动连接本地的3306端口
mysql客户端登陆指令:
mysql>mysql -uroot #表示要用root用户登陆,默认密码为空
mysql>set password = password('123') #设置密码为123
mysql>mysql -uroot -p 回车
Enter password:123 #使用密码登陆
mysql>mysql -uroot -p123 回车 #直接登陆成功(不安全)
mysql客户端连接服务端的完整指令:
mysql -h 127.0.0.1 -P 3306 -u root -p 回车后输入密码
#-h后输入ip地址,-P后输入端口号,-u后输入用户名,-p后输入密码
查看字符集编码的指令:
mysql>show variables like "%char%";
mysql的帐号操作
#进入mysql客户端
mysql
mysql> select user(); #查看当前用户
mysql> exit # 也可以用q quit退出
# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
mysql -uroot -p # mysql 5.6默认是没有密码的
#遇到password直接按回车键
mysql> set password = password('root'); # 给当前数据库设置密码
# 创建账号
mysql> create user 'eva'@'192.168.10.%' IDENTIFIED BY '123';# 指示网段
mysql> create user 'eva'@'192.168.10.5' # 指示某机器可以连接
mysql> create user 'eva'@'%' #指示所有机器都可以连接
mysql> show grants for 'eva'@'192.168.10.5';查看某个用户的权限
# 远程登陆
mysql -uroot -p123 -h 192.168.10.3
# 给账号授权
mysql> grant all on *.* to 'eva'@'%';
mysql> flush privileges; # 刷新使授权立即生效
# 创建账号并授权
mysql> grant all on *.* to 'eva'@'%' identified by '123'
忘记密码以及修改密码的操作
忘记密码:
1 在命令提示符窗口停掉MySQL服务端(net stop mysql)
2 切换到MySQL安装目录下的bin目录下,然后手动指定启动程序来启动mysql服务端,指令: mysqld.exe --skip-grant-tables
3 重新启动一个命令提示符窗口,连接mysql服务端,
4 修改mysql库里面的user表里面的root用户记录的密码:
update user set password = password('666') where user='root';
5 关掉mysqld服务端,指令:
tasklist|findstr mysqld
taskkill /F /PID 进程号
6 正常启动服务端(net start mysql)
修改密码的三种方式:
方法1： 用SET PASSWORD命令
首先登录MySQL，使用mysql自带的那个客户端连接上mysql。
格式：mysql> set password for 用户名@localhost = password('新密码');
例子：mysql> set password for root@localhost = password('123');
方法2：用mysqladmin （因为我们将bin已经添加到环境变量了，这个mysqladmin也在bin目录下，所以可以直接使用这个mysqladmin功能，使用它来修改密码）
关于mysqladmin的介绍：是一个执行管理操作的客户端程序。它可以用来检查服务器的配置和当前状态、创建和删除数据库、修改用户密码等等的功能，虽然mysqladmin的很多功能通过使用MySQL自带的mysql客户端可以搞定，但是有时候使用mysqladmin操作会比较简单。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123
方法3：用UPDATE直接编辑那个自动的mysql库中的user表
首先登录MySQL，连接上mysql服务端。
mysql> use mysql; #use mysql的意思是切换到mysql这个库，这个库是所有的用户表和权限相关的表都在这个库里面，我们进入到这个库才能修改这个库里面的表。
mysql> update user set password=password('123') where user='root' and host='localhost'; #其中password=password('123') 前面的password是变量，后面的password是mysql提供的给密码加密用的，我们最好不要明文的存密码，对吧，其中user是一个表，存着所有的mysql用户的信息。
mysql> flush privileges; #刷新权限，让其生效，否则不生效，修改不成功。
3.存储引擎
什么是存储引擎?
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
MySQL支持哪些存储引擎?
mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
什么是事务?
事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
例如：你给我转账5块钱，流程如下
a.从你银行卡取出5块钱，剩余计算money-5
b.把上面5块钱打入我的账户上，我收到5块，剩余计算money+5.
上述转账的过程，对应的sql语句为：
update 你_account set money=money-5 where name='你'；
update 我_account set money=money+5 where name='我'；
上述的两条SQL操作，在事务中的操作就是要么都执行，要么都不执行，不然钱就对不上了。
常用存储引擎及应用场景
InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
存储引擎相关sql语句
查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";
查询当前数据库支持的存储引擎:
mysql> show engines;
查看正在使用的搜索引擎:
mysql>show variables like "storage_engine%";
指定存储引擎建表
在建表时指定:
mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;
在配置文件中指定:
#my.ini文件
[mysqld]
default-storage-engine=INNODB
4.MySQL的工作流程
MySQL架构总共四层，在上图中以虚线作为划分。
首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。
第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。
第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。
5.初识SQL语句
1. 操作文件夹（库）
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;
2. 操作文件（表）
先切换到文件夹下：use db1
增：create table t1(id int,name char(10));
查：show tables; #查看所有表
show create table t1; #查看表结构
desc t1; #查看表结构
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;
3. 操作文件中的内容（记录）
增：insert into t1 values(1,'egon1'),(2,'egon2');
insert into t1(id,name) values(3,'egon3');
查：select * from t1;
select * from t1 where id = 1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，
*auto_increment 表示：自增
*primary key 表示：约束（不能重复且不能为空）；加速查找
6.数据库文件夹中各文件的作用
1.db.opt文件：用来记录该库的默认字符集编码和字符集排序规则用的。也就是说如果你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。
2.后缀名为.frm的文件：这个文件主要是用来描述数据表结构(id,name字段等)和字段长度等信息
3.后缀名为.ibd的文件：这个文件主要储存的是采用独立表储存模式时储存数据库的数据信息和索引信息；
4.后缀名为.MYD（MYData）的文件：从名字可以看出，这个是存储数据库数据信息的文件，主要是存储采用独立表储存模式时存储的数据信息；
5.后缀名为.MYI的文件：这个文件主要储存的是数据库的索引信息；
6.ibdata1文件:主要作用也是储存数据信息和索引信息，这个文件在mysql安装目录的data文件夹下。
从上面可以看出，.ibd储存的是数据信息和索引信息，ibdata1文件也是存储数据信息和索引信息，.MYD和.MYI也是分别储存数据信息和索引信息，那他们之间有什么区别呢？
主要区别是再于数据库的存储引擎不一样，如果储存引擎采用的是MyISAM，则生成的数据文件为表名.frm、表名.MYD、表名.MYI;而储存引擎如果是innoDB，开启了innodb_file_per_table＝1,也就是采用独立储存的模式，生成的文件是表名.frm、表名.ibd，如果采用共存储模式的，数据信息和索引信息都存储在ibdata1(在里面进行分类，从外面看是一个文件)中;
在进行数据恢复的时候，如果用的是MYISAM数据引擎，那么数据很好恢复，只要将相应.frm, .MYD, .MYI文件拷贝过去即可。但是如果是innodb的话，则每一个数据表都是一个单独的文件，只将相应的.frm和.ibd文件拷贝过去是不够的，必须在你的ibd文件的tablespace id和ibdata1文件中的元信息的tablespace id一致才可以。
msyql设定的规则就是这样存储表的.