mysql学习拓展

1.sql语句分类

2、MySQL数据库用户安全策略介绍

安装完MySQL数据库之后，默认的管理员root密码为空，这很不安全。因此需要为root用户设置一个密码。如下图查询之后为空。

1、为root用户设置密码
进入mysql数据库：use mysql;
更改root用户密码： update user set password=password(‘song123’) where user=root;
退出： exit;
注意：如上操作是不会生效的，再用“mysql -u root”登录的时候，依然是无密码状态，原因在于mysql服务为加速系统效率，会先将用户权限更新到内存中，除非使用实时生效命令会再次刷新内存权限数据。在mysql命令行中执行flush privileges,或重启服务，service mysqld restart
接下来无密码方式就不能登录，要采用以下方式登录
mysql -u root -p 回车后，会提示输入密码，输入自己设置的密码就好
2、忘记密码如何处理
进行免密登录的配置
首先停止MySQL服务，示例如下： service mysql stop
然后，/etc/my.cnf 中在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables
保存并且退出vi。 ::?
现在，无须密码即可登录MySQL，然后修改新密码，示例如下：
这时，可以将root密码修改为新密码了，示例如下：
mysql> update mysql.user set password=PASSWORD(“oldboy123”) where user=“root” and host=“localhost”;
mysql> flush privileges; #<==刷新权限使得修改密码生效。
Query OK, 0 rows affected (0.01 sec)
然后重启服务 service mysql restart
3、MySQL创建用户及授权
命令如下:
grant all on stumysql.* to test@localhost identified by ‘song123’；
grant select, insert, update, delete on . to test@"%" identified by ‘song123’;
grant all on stumysql. * to test@ ‘192. 168. 111. 118’ identified by ‘song123’
以上三条 grant语句授权参数详解如下:
1、授权 localhost主机通过test用户和song123密码访问本地的stumysql库的所有权限;
2、授权所有主机通过test用户和song123s密码访间本地的 stumysql库的查询、插入、更新、删除权限;
3、授权192.168.111.118主机通过test用户和song123密码访问本地的 stumysql库的所有权限。

3.数据库备份与恢复

对于绝大多数企业来讲，失去数据就相当于失去商机、失去产品、失去客户，甚至会造成公司倒闭，那么，在所有的数据中，最核心的数据又是哪些呢？这恐怕要属数据库中的数据了，当然，并不是说其他数据不重要，只是这一部分更具代表性。既然数据库中的数据地位这么高，那么数据库备份与恢复的重要性就不言而喻了。
1、mysql数据库备份
基本备份输入命令:
mysqldump -u root -p stumysql1>/home/bak/2019-04-11/stumysql1.sql
Enter password: song123
此时,已成功将数据库stumysql1备份到/home/bak/2019-04-11目录下的test.sql中.
加-B参数备份的实践：
下面以备份stumysql1库时加-B参数，备份的文件中会比不加-B多创建库的语句，
在备份的时候最好加上-B,以下例进行讲解。
备份命令如下
mysqldump -B -u root -p stumysql1 >/home/bak/2019-04-11/stumysql.sql
Enter password: song123
指定压缩命令gzip压缩备份stumysql1数据库，命令如下：
mysqldump -B -u root -p stumysql1|gzip>/home/mysql_bak_B.sql.gz
#<==注意压缩命令前要加管道。

Linux增量备份
2、mysql数据库恢复
使用mysql命令
mysql -u root -p test < /home/bak/2019-04-11/stumysql1.sql
Enter password:song123
使用source命令恢复数据库的说明
进入mysql数据库控制台后，切换到想恢复数据的数据库。
mysql>use 数据库
接着，使用source命令进行恢复，后面接.sql文件，即上文使用mysqldump备份的文件或者人工编辑的SQL语句文件：
mysql>source /home/stumysql.sql
这个stumysql.sql文件是系统的相对路径，默认是登录MySQL前的系统路径，也可以使用完整的路径。
解压压缩包
gzip -d /home/mysql_bak_B.sql.gz
再用以上方式还原即可

4.MySQL数据库索引案例

MySQL索引可以用来快速地寻找某些具有特定值的记录,所有 MySQL.索引都以B-树形式保存。如果 MySQL没有索引,执行 select时会从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。如果表中数据有上亿条数据,查询一条数据花费的时间会非常长,索引类似于电子书的目录与页码的对应关系,可加快数据的查找。
如果在需搜索条件的列上创建了索引, MySQL无须扫描全表记录即可快速得到相应的记录行。如果该表有100000条记录,通过索引查找记录要比全表顺序扫描至少快100倍,这就是索引在企业环境中带来的执行速度上的提升。
1、MySQL数据库常见索引类型包括:普通索引( normal)、唯一索引( unique)、全文索引( full text)、主键索引( primary key)、组合索引等,以下为每个索引的应用场景及区别,
普通索引: normal,使用最广泛。
唯一索引: unique,不允许重复的索引,允许有空值
全文索引: full text,只能用于 MyISAM表, full text主要用于大量的内容检索
主键索引: primary key又称为特殊的唯一索引,不允许有空值。
组合索引:为提高 MySQL效率可建立组合索引。
2、MySQL数据库表创建各个索引命令,以t1表为案例,操作如下
主键索引: ALTER TABLE t1 ADD PRIMARY KEY(’ column’)。
唯一索引: ALTER TABLE t1 ADD UNIQUE(’ column’)
普通索引: ALTER TABLE t1 ADD INDEX index_name(’ column’)。
全文索引: ALTER TABLE t1 ADD FULLTEXT(’ column’)
组合索引: ALTER TABLE tl ADD INDEX index_name(‘columnl’,’ column2’,’ column3’)
3、t1表的d字段创建主键索引,查看索引是否被创建,然后插入相同的id,提示报错,如图所示。
4、MySQL数据库表删除各个索引命令,以表为案例,操作如下:
DROP INDEX index_name ON t1;
ALTER TABLE tl DROP INDEX index_name;
ALTER TABLE tI DROP PRIMARY KEY;
5、MySQL数据库查看表索引,操作如下:
show index from t1;
show keys from t1;