MySQL基础之登录,权限,数据库

目录

• 1 MySQL认识
  • 1.1 MySQL历史和认识
  • 1.2 数据库基本知识
• 2 MySQL安装配置
• 3 MySQL登录和权限
  • 3.1 用户登录
    • 3.1.1 登录命令
    • 3.1.2 登录报错处理
  • 3.2 创建用户
    • 3.2.1 创建用户
    • 3.2.3 删除用户
    • 3.2.4 重命名用户
  • 3.3 赋予并查看权限
  • 3.4 忘记并修改密码
    • 3.4.1 忘记密码解决方法
    • 3.4.2 各个版本修改密码方式
    • 3.4.3 权限赋值语句修改
    • 3.4.4 修改简易密码报错
  • 3.5 回收权限
• 4 数据库和查看帮助文档
  • 4.1 查看帮助文档
  • 4.2 数据库操作
• 5 表操作
• 6 存储引擎

1 MySQL认识

1.1 MySQL历史和认识

MySQL是一款免费开源、小型、关系型数据库管理系统。随着该数据库功能的不断完善、性能的不断提高，可靠性不断增强
2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM
2001年，支持事务处理和行级锁存储引擎InnoDB被集成到MySQL发行版中，该版本集成了MyISAM与InnoDB存储引擎，MySQL与InnoDB的正式结合版本是4.0。
2004年10月，发布了经典的4.1版本。
2005年10月，发布了里程碑的一个版本，MySQL 5.0，在5.0中加入了游标，存储过程，触发器，视图和事务的支持。在5.0 之后的版本里，MySQL明确地表现出迈向高性能数据库的发展步伐
MySQL公司于2008年1月16号被SUN公司收购，而在2009年SUN又被Oracal收购

在MySQL的5.6版本中，数据库的可扩展性、集成度以及查询性能都得到提升。新增功能包括实现全文搜索，开发者可以通过InnoDB存储引擎列表进行索引和搜索基于文本的信息；InnoDB重写日志文件容量也增至2TB，能够提升写密集型应用程序的负载性能；加速MySQL复制；提供新的编程接口，使用户可以将MySQL与新的和原有的应用程序以及数据存储无缝集成

为什么要使用MySQL数据库
在许多数据库管理系统提供的功能特性，只有40%的功能被使用。而MySQL在性能与标准的取舍上，一直坚持性能优先的原则，成为了互联网行业非常流行的数据库软件之一

1.2 数据库基本知识

数据库管理系统提供的功能
数据库管理系统提供许多功能，可以通过SQL（结构化查询语言）来定义和操作数据，维护数据的完整性和安全性，以及进行各种数据库的管理等。那么数据库管理系统所支持的SQL提供哪些功能呢？

1. 数据定义语言（Data Definition Language，DDL）
   数据库管理系统提供了数据定义语言定义数据库涉及各种对象，定义数据的完整性约束、保密限制等约束
2. 数据操作语言（Data Manipulation Language，DML）
   数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作有两类：检索（查询）和更新（插入、删除和更新）。
3. 数据控制语言（Data Control Language，DCL）
   数据库管理系统提供了数据控制语言实现对数据库的控制，包含数据完整性控制、数据安全性控制和数据库的恢复等

2 MySQL安装配置

Linux系统下安装MySQL
Windows系统下zip包安装MySQL

3 MySQL登录和权限

注意：如下所讲的登录都是通过命令行登录的

3.1 用户登录

3.1.1 登录命令

MySQL登录

mysql -h 127.0.0.1 -u root -proot company
mysql -h 127.0.0.1 -u root -p company -e "SELECT * FROM t_dept";

参数说明如下：

• -h：表示主机 -u：表示用户名
• -p：表示密码 -P：表示端口，此处后面跟的company是表示数据库
• -e：表示要执行的SQL

3.1.2 登录报错处理

连接数过多，无法连接MySQL

解决问题的思路：

1. 首先先要考虑在我们 MySQL 数据库参数文件里面，对应的 max_connections 这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。
   该值默认大小是151，我们可以根据实际情况进行调整。
   对应解决办法：set global max_connections=500
   但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃 10 个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。
   所以这又反应出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。
2. 其次可以限制Innodb的并发处理数量 ，如果 innodb_thread_concurrency = 0（这种代表不受限制） 可以先改成 16或是64 看服务器压力。如果非常大，可以先改的小一点让服务器的压力下来之后,然后再慢慢增大,根据自己的业务而定

3.2 创建用户

3.2.1 创建用户

MySQL账户管理
创建用户语法：

CREATE USER username[IDENTIFIED BY [PASSWORD] 'password']
	    [,username[IDENTIFIED BY [PASSWORD] 'password']]
	    ……
	    [,username[IDENTIFIED BY [PASSWORD] 'password']]

创建用户示例：
CREATE USER 'cjgong'@'localhost' IDENTIFIED BY '123456';

3.2.3 删除用户

DROP USER 'cjgong'@'localhost';

3.2.4 重命名用户

rename user 'test3'@'%' to 'test1'@'%';

3.3 赋予并查看权限

MySQL权限管理
赋予权限：

GRANT priv_type ON databasename.tablename
	TO username[IDENTIFIED BY [PASSWORD] 'password'] 
	WITH GRANT OPTION

参数说明：

• databasename.tablename:数据库中的表一般是.表示所有数据库所有表
• priv_type：表示select,delete,update,insert，usage(是连接权限)等等
• WITH GRANT OPTION :是否允许用户继续授权给其他用户

给创建的用户赋予权限并刷新权限示例：

GRANT SELECT ON company.t_dept TO 'cjogng2'@'localhost' 
IDENTIFIED BY '123456';

授权之后，不要忘记更新权限表
flush privileges   

查看权限：

SHOW GRANTS FOR 'cjgong'@'localhost' \G

3.4 忘记并修改密码

3.4.1 忘记密码解决方法

在MySQL中，若密码丢失则无法直接找回，只能通过特殊方式来修改密码。
在配置文件my.cnf中添加如下一行，重启 MySQL 登录则不需要密码。

在[mysqld]区域添加配置,并保存my.cnf文件
skip-grant-tables

3.4.2 各个版本修改密码方式

update语句修改
mysql5.7之前

UPDATE user SET password=PASSWORD("new_password")
WHERE user="root" AND host="localhost";

mysql5.7修改密码

update user set authentication_string=password('123456') where user='root';

MySQL8修改密码

alter user root@'localhost' identified with mysql_native_password by 'root1'

3.4.3 权限赋值语句修改

GRANT priv_type ON database.table
	TO user [IDENTIFIED BY [PASSWORD] 'new_password']

3.4.4 修改简易密码报错

报错现象

alter user root@‘localhost’ identified with mysql_native_password by ‘root’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

处理过程

1. 查询密码策略

mysql> SHOW VARIABLES LIKE ‘validate_password%’;

2. 去除密码验证策略
   默认关闭，设置为ON时可以将密码设置成当前用户名

set global validate_password.check_user_name=OFF;

3. 密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。默认是1，即MEDIUM

set global validate_password_policy=0;
set global validate_password.length=4;

4. 特殊字符

set global validate_password.mixed_case_count=0;
set global validate_password.number_count=0;

flush privileges;

5. 密码验证策略
   0/LOW：只检查长度
   1/MEDIUM：检查长度、数字、大小写、特殊字符
   2/STRONG：检查长度、数字、大小写、特殊字符字典文件

3.5 回收权限

REVOKE ALL PRIVILEGES,GRANT OPTION
	FROM user1 [IDENTIFIED BY[PASSWORD] ‘password’]
	…
	[,user2 [IDENTIFIED BY[PASSWORD] ‘password’]]

参数说明：

• ALL PRIVILEGES：表示select,delete,update,insert,usage(是连接权限)等等
• GRANT OPTION ：表示回收授权权限

示例：

REVOKE SELECT ON *.* FROM 'cjgong'@'localhost'
回收权限之后，不要忘记更新权限表
flush privileges      

4 数据库和查看帮助文档

4.1 查看帮助文档

MySQL帮助文档：http://dev.mysql.com/doc/然后根据自己MySQL版本选择对应版本号查询帮助文档
在dos窗口内通过mysql -u root -p 登录，然后help contents，就可以查看对应帮助文档，比如：help create database;：查看创建数据库创建文档

4.2 数据库操作

创建数据库：

create database if not exists testdb;
带字符创建数据库
create database if not exists testdb default character set utf8;
单独地修改字符集
alter database testdb default character set latin1;

查看数据库：

show databases;

选择数据库并查询：

use testdb;
select * from db.table;

删除数据库：

drop database if exists testdb;

5 表操作

表的创建

create table if not exists testTable(列名 列类型 comment '字段的注释'...) comment='表的注释';

使用如下命令只是在原表基础上复制了个表
create table 表名 as select * from other_table; 

表定义查看

show create table TableName;
或者使用如下查看表的详情信息
desc TableName;

表的重命名

alter table old_table_name rename [to] new_table_name;
或者使用如下命令
rename table old_table_name to new_table_name;

表的注释修改

alter table TableName comment '修改后的表的注释';

表结构修改，增加字段

alter table TableName add 属性名 属性类型;

在表的指定字段之后增加字段

alter table TableName add 属性名 属性类型 after 属性名;

表字段删除：

alter table TableName drop 属性名;

修改表字段属性：

alter table TableName modify 属性名 新属性类型 comment '修改后的字段注释';

修改表字段名字：

alter table TableName change 旧属性名 新属性名 旧属性类型;

表的删除

drop table TableName;

6 存储引擎

查看数据库支持的存储引擎类型：show engines
或者使用命令show variables like 'storage_engine%'
修改默认存储引擎my.ini
default-storage-engine=INNODB
注意需重启MySQL服务器