MySQL数据库之初识数据库

MySQL数据库

MYSQL操作语言分类

1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

My存储引擎（了解）

• 存储引擎的概念

  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 = '123'

[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=E:mysql-5.6.45-winx64 
# 设置mysql数据库的数据的存放目录
datadir=E:mysql-5.6.45-winx64data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

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用户记录的密码
updata 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'); # 给当前账户设置密码

# 创建账号
mysql> create user 'eva'@'192.168.10.%' IDENTIFIED BY '123';# 指示网段
mysql> create user '田彩'@'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'
mysql> grant select on *.* to 'eva'@'%' identified by '123'

MYSQL数据基本操作

'''库操作'''
show databases;  #查询所有的数据库
create database student;  #创建一个student的库
create database db1 charset utf8,  #创建一个db1的库',并指定编码
use student;  #进去student这个库,(然后才能操作表)
drop database student; #删除student这个库(慎用)
=============================================
'''表操作'''
create table t1(id int,name char(16))engine=innodb; #创建一个叫t1的表,申明引擎为innoDB
show tables; #查看库下面的所有表
desc t1; #查看t1的表结构(desc全称describe)
show create table t1; #查看创建表的详细详细(创建语句,编码等)
drop table t1; #删除t1这个表
alter table t1 rename student;#修改表的名字
alter table t1 modify name char(16);#修改表的数据类型
alter table t1 change name name1 char(15);#修改表的字段名和数据类型
alter table t1 add name char(10);#给t1这个表增加一个字段
alter table t1 add unique(bname)#给t1这个表中的字段bname添加一个约束条件
alter table t1 add drop primary key#把t1这个表中的主键取消
alter table t1 add drop index 'bname';#把t1这个表中bname字段的unique这个约束条件取消
*auto_increment #表示：自增(创建表时字段约束)
*primary key #表示：约束（不能重复且不能为空)加速查找((创建表时字段约束))
==============================================
'''数据操作'''
select * from student;#查看student这个表中的所有数据
insert into student values(1,'田彩'),(2,'李杰');#往student表中添加两条数据
update student set name='田少岗' where id=1;#将id为1的name改成田少岗
update student set name='田少岗' id=2 where id=1 #将id为1的name改成田少岗,id改成2
delete from student;#把student这个表的数据清空(慎用)
delete from student where id = 1;#把student这个表中id为1的数据删掉(如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。)
truncate table t1;#数据量大，删除速度比上一条快，且id直接从零开始,