数据库 - Tian_Cai
数据库的作用
#解决了操作文件的效率和便捷问题
#解决了多个服务同时使用数据的一致性问题
#解决了安全的问题
#解决了并发问题
数据库的分类
#关系型数据库:mysql,oracle,sqlserver,sqllite,accesse
#非关系型数据库(存储数据快):redis,mongodb,memcache(内存级别)
MYSQL数据库
MYSQL操作语言分类
1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
Mysql存储引擎(了解)
-
存储引擎的概念
1:存储引擎的概念是Mysql才里才有的,不是所有的关系型数据库都有存储引擎这个概念
2:存储引擎就是在如何存储数据、提取数据、更新数据等技术方法的实现上，底层的实现方式不同，那么就会呈现出不同存储引擎有着一些自己独有的特点和功能，对应着不同的存取机制。
3:其实MySQL支持多种存储引擎，每种引擎有着一些自己独特的功能，用户在使用的时候，可以根据自己的业务场景来使用不同的存储引擎，其中MySQL最常用的存储引擎为：MyISAM和InnoDB。 -
Mysql工作流程
-
InnoDB引擎
InnoDB引擎
介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。
InnoDB引擎特点：
1.支持事务：支持4个事务隔离界别，支持多版本读。
四大特点:原子性,隔离性,持久性,一致性*****
2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
6.所有SecondaryIndex都会保存主键信息。(了解)
7.支持分区，表空间，类似oracle数据库。
8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
小结：三个重要功能：Supports transactions，row-level locking，and foreign keys -
MyISam引擎
MyISAM引擎调优精要
1.设置合适的索引(缓存机制)(where、join后面的列建立索引，重复值比较少的建索引等)
2.调整读写优先级，根据实际需求确保重要操作更优先执行，读写的时候可以通过参数设置优先级。
3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)。
4.尽量顺序操作让insert数据都写入到尾部，较少阻塞。
5.分解大的操作，降低单个操作的阻塞时间，就像操作系统控制cpu分片一样。
6.降低并发数(减少对MySQL访问)，某些高并发场景通过应用进行排队队列机制Q队列。
7.对于相对静态(更改不频繁)的数据库数据，充分利用Query Cache(可以通过配置文件配置)或memcached缓存服务可以极大的提高访问频率。
8.MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
9.可以把主从同步的主库使用innodb，从库使用MyISAM引擎。主库写，从库读可以(不推荐，有些麻烦的地方，市场上有人这么用) -
区别和特点
MYSQL数据库安装配置文件
#在数据库安装目录下创建一个my.ini文件,写入下面的配置,然后重启服务端(注意保存,复制pycharm上修改文件路径即可)**********
[client]
#设置mysql所有客户端默认字符集
default-character-set=utf8
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
user = \'root\'
password = \'root\'
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\mysoftware\mysql-5.6.45-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysoftware\mysql-5.6.45-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#设置严格模式
sql_mode=STRICT_TRANS_TABLES
sql_mode设置
-
sql_mode的解释
sql_mode是个很容易忽视的变量,默认值是空值(5.6版本,5.7版本是严格模式),在这种模式下允许一些非法操作,比如一些非法数据的插入,在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题. -
sql_mode常用来解决这几种问题
(1) 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。
(2) 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。
(3) 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中 -
sql_mode的查询和修改方式
#方式一:
执行sql语句: select @@sql_mode可以查看当前会话框的sql_mode模式,
执行sql语句: set session sql_mode=\'STRICT_TRANS_TABLES\',就改为严格模式
此方法在次回话关闭之后,就会失效
#方式二:
执行sql语句: select @@global.sql_mode 查看当前全局的sql_mode设置
执行sql语句: set global.sql_mode = \'STRICT_TRANS_TABLES\',就回修改尾严格模式
此方法在mysql服务关闭重启之后失效,只在此次服务中有效
#方法三:
再mysql安装目录下,找到配置文件,在mysqld的配置文件下添加:
sql_mode = \'STRICT_TRANS_TABLES\',就能设置成严格模式,此方法需要重启mysql服务,
从方法永久生效.当然公司的生产环境上事禁止重启mysql服务的
查看mysql所有的编码格式
#查看字符集编码的指令:
show variables like \'%char%\';
MYSQL客户端链接服务端的完整指令
#完整版指令
mysql -h 127.0.0.1 -P 3306 -uroot -p(密码)
#平时使用的版本
mysql -uroot -p(密码)
mysql登陆密码忘记后的修改方式
#1:停掉Mysql服务端
net stop mysql
#2:在cmd中路径切换到Mysql安装目录下的bin目录,然后手动启动mysql服务端:
mysqld.exe --skip-grant-tables
#3:服务端启动成功后,重新启动一个新的cmd窗口,链接mysql服务端
#4:修改mysql这个库中的user表中的root用户记录的密码
update user set password = password(\'123\') where user=\'root\';
#5:关掉mysqld服务端:
tasklist | findstr mysqld#找到mysqld的服务
taskkill /F /PID 进程号#关闭mysqld的服务
#6:重新启动mysql服务端(正常启动)
登陆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表
1:首先登录MySQL，连接上mysql服务端。
2:mysql> use mysql; use mysql的意思是切换到mysql这个库，这个库是所有的用户表 和权限相关的表都在这个库里面，我们进入到这个库才能修改这个库里面的表。
3:mysql> update user set password=password(\'123\') where user=\'root\' and host=\'localhost\'; #其中password=password(\'123\') 前面的password是变量，后面 的password是mysql提供的给密码加密用的，我们最好不要明文的存密码，对吧，其中user是 一个表，存着所有的mysql用户的信息。
4:mysql> flush privileges; #刷新权限，让其生效，否则不生效，修改不成功
MYSQL账号操作(了解)
mysql> select user(); #查看当前用户
mysql> exit # 也可以用\q quit退出
# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
$ mysql -uroot -p # mysql5.6默认是没有密码的
#遇到password直接按回车键
mysql> set password = password(\'root\'); # 给当前账户设置密码
# 创建账号(IDENTIFIED BY \'123\',指定密码)
create user \'eva\'@\'192.168.10.%\' IDENTIFIED BY \'123\';# 指示该网段可以使用eva登陆
create user \'田彩\'@\'192.168.10.5\' IDENTIFIED BY \'123\'; # 指示ip和用户名连接
create user \'eva\'@\'%\' IDENTIFIED BY \'123\'; #指示所有机器都可以通过eva账户连接
drop user \'eva\'@\'192.168.1.1\' #删除用户
rename user \'用户名\'%\'IP地址\' to \'新用户名\'%\'IP地址\';#修改用户名
set password for \'用户名\'%\'IP地址\'=password(\'新密码\');#修改用户密码
# 远程登陆(使用创建的账号,在别人的电脑)
mysql -uroot -p123 -h 192.168.10.3;
mysql -h 192.168.10.3 -P 3306 -uroot -p123;
# 给账号授权
grant all privileges on *.* to \'eva\'@\'%\'; #给eva用户所有的权限(privileges可以不写)
grant select,insert on 数据库.表名 to \'田彩\'@\'%\';#给用户某个库中某个表的查询,增加权限
grant all privileges on db1.* to "chao"@\'%\';#chao用户对db1数据库中的文件执行任何操作
grant all privileges on *.* to "chao"@\'%\';#chao用户对所有数据库中文件有任何操作
show grants for \'eva\'@\'192.168.10.5\';#查看某个用户的权限
flush privileges; # 刷新使授权立即生效
#取消权限
revoke all on db.t1 from \'cai\'@\'%\';#取消用户cai对t1表的所有操作
revoke all on db.* from \'cai\'@\'%\';#取消用户彩对db库的所有权限
revoke all on *.* from \'cai\'@\'%\'#取消用户彩对所有数据库所有表的权限
# 创建账号并授权(*代表所有)
mysql> grant all on 数据库.表名 to \'eva\'@\'%\' identified by \'123\'
mysql