MySQL数据库之库、表、用户管理一、MySQL数据库的概述什么是MySQL？一款深受欢迎的开源关系型数据库 Ora

一、MySQL数据库的概述

什么是MySQL？

一款深受欢迎的开源关系型数据库

Oracle旗下的产品

遵守GPL协议，可以免费使用与修改

2. MySQL的特点

● 性能卓越、服务稳定

● 开源、无版权限制、成本低

● 多线程、多用户

● 基于C/S（客户端/服务器）架构

● 安全可靠

二、常用的数据类型

MySQL支持多种内置数据类型

数值类型
日期/时间类型
字符串(字符)类型

1. 数值型

数值型分为：整数型和小数型（浮点型、定点型）

2. 整数型

tinyint(m) 1个字节范围(-128~127) 有一个正或负的表示符
smallint(m) 2个字节范围(-32768~32767)
mediumint(m) 3个字节范围(-8388608~8388607)
int(m) 4个字节范围(-2147483648~2147483647)
bigint(m) 8个字节范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍。如：tinyint unsigned的取值范围为(0~255)

3. 浮点型

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位。注意: 小数点不占用总个数
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位。注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

4. 定点型

在数据库中存放的是精确值,存为十进制 decimal(5.2) 100.01 - 999.99。

格式 decimal(m,d) 表示最多m位数字,其中d个小数,小数点不算在长度内。

5. 字符型

char(n):固定长度，最多255个字符,注意不是字节
varchar(n):可变长度，最多65535个字符
text:可变长度，最多65535个字符
tinytext:可变长度，最多255个字符
mediumtext:可变长度，最多2的24次方-1个字符
longtext:可变长度，最多2的32次方-1个字符
BINARY(M):固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M):可变长度，可存二进制或字符，允许长度为0-M字节

char和varchar的比较:

char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
char类型的字符串检索速度要比varchar类型的快

6. 修饰符

作用：修饰数据

适用所有类型的修饰符：

名称	含义
NULL	数据列可包含NULL值，默认值
NOT NULL	数据列不允许包含NULL值，`*`为必填选项
DEFAULT	默认值
PRIMARY KEY	主键，所有记录中此字段的值不能重复，且不能为NULL ，一张表中只有一个主键
UNIQUE KEY	唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET	name 指定一个字符集

适用数值型的修饰符：

名称	作用
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号

三、查看数据库结构

mysql中的命令默认不区分大小写。表名和库名区分大小写。

在linux系统中的客户端，命令后一定要加分号。

1. 查看帮助信息

mysql> help create
//help 后面跟上具体命令可以查看帮助

2. 查看当前服务器中的数据库

 SHOW DATABASES;     #大小写不区分，分号“;"表示结束

3. 查看数据库中包含的表

 方法一：
 USE  数据库名;      #切换库
 SHOW TABLES;      #查看库中的表

 方法二：
 show tables from 数据库名；    #直接查看某个库中的表
 #例如：show tables from mysql；

4. 查看表的结构（字段）

 方法一：
 USE 数据库名;       #切换库
 DESCRIBE 表名;     #查看指定表的结构

 方法二：
 DESCRIBE [数据库名.]表名;
 可缩写成：DESC 数据库名.表名;
 #例如：desc mysql.user;

四、SQL语句

关系型数据库，都是使用SQL语句来管理数据库中的数据。

SQL，即结构化查询语言(Structured Query Language) 。

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

1. SQL中的名词介绍

数据库：database
表：table，行：row 列：column
索引：index
视图：view
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
用户：user
权限：privilege

2. SQL语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写。
SQL语句可单行或多行书写，默认以 " ; " 结尾。
关键词不能跨多行或简写。
用空格和TAB 缩进来提高语句的可读性。
子句通常位于独立行，便于编辑，提高可读性。

3. 数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。

命名规则：

必须以字母开头，后续可以包括字母，数字和三个特殊字符（# _ $）
不要使用MySQL的保留字，如table、select、show、databases。
数据库名、表名、用户名区分大小写

4. SQL语言分类

1. DDL：数据定义语言（Data Definition Language），用于创建数据库对象，如库、表、索引等。

例如：CREATE，DROP，ALTER 等。

2. DML：数据操纵语言（Data Manipulation Language），用于对表中的数据进行管理。

例如： SELECT、UPDATE、INSERT、DELETE 等。

3. DQL：数据查询语言（ Data Query Languag ），用于从数据表中查找符合条件的数据记录。

例如： SELECT

4. DCL：数据控制语言（Data Control Language），用于设置或者更改数据库用户或角色权限。

例如： GRANT，REVOKE

5. TCL：事务控制语言（Transaction Control Language），用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。

例如： COMMIT，ROLLBACK，SAVEPOINT

五、管理数据库

1. 创建数据库

create database 数据库名称;

例： create database db1;

2. 修改数据库

当建立好数据库后可以使用 ALTER 命令修改。（一般不会进行修改）

3. 删除数据库

drop database 数据库名称;

例： drop database db1;

4. 查看数据库

查看目前所有数据库

show databases;

查看当前数据库

select database ();

查看数据库的详细信息

show create database 数据库名字;   

例：show create database db1;

5. 切换数据库

use 数据库名称;

例： use db1;

六、表操作

1. 新建表

新建表，先要指定数据库
use  数据库名
create table 表名 （字段1 数据类型 [修饰符]，字段2 数据类型 [修饰符]， .... ）

示例1：

use db1;
create table student (id int,name varchar(10),gender char(2));

示例2：

use db2;
create table student (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
//两种写法，可写在一行，也可分行写

unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M

2. 查看表结构(字段)

查看表结构

desc 表名;

显示当前数据库下的所有表

show tables；

查看表中的所有数据

select * from 表名;

3. 修改表结构（一般不轻易修改）

3.1 alter 添加字段

示例：

mysql [db1]> alter  table  student  add   phone  vachar(11) not null ;
            命令字  命令字    表名   关键字 字段名称     字段属性

3.2 change 修改字段名称

示例：

alter table student change phone mobile varchar(11) not null;

3.3 drop 删除字段

示例：

alter table student drop mobile;

七、用户管理

1. 存放用户信息的表

mysql的用户，存放在mysql数据库中的user表中

select user,host,authentication_string from mysql.user;

//root@localhost  默认存在的，并且只能本地登录无法远程登录

2. 查看当前使用用户

select user();

3. 新建用户

语法：

create user  '用户名'@'主机地址'  identified by '密码';

'用户名'：指定将创建的用户名
'主机地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%

示例：

4. 修改用户名称

语法：

rename user '旧名字' to '新名字';

示例：

rename user 'lhey'@'localhost' to 'lv'@'localhost';

5. 删除用户

语法：

drop  user '用户名'@'主机地址';

示例：

drop user 'test'@'192.168.100.%';

6. 修改用户密码

密码有安全性策略可以修改取消

set global validate_password_policy=0;
set global validate_password_length=1;
//修改密码策略

语法：

set password = '密码';             //给当前用户修改密码
set password for '用户' = '密码';  //给其他用户修改密码

示例：

set password = '123123';          //把当前用户密码修改为123123
set password for 'lv'@'localhost' = 'abc123';  //把用户lv密码修改为abc123

7. 破解密码

修改配置文件

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
//数据库的单用户模式   此模式下权限受到限制，很多功能无法使用， 除了破解密码不要加此项
skip-networking  
//MySQL8.0不需要

//然后清空密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
//注意刷新后生效
flush privileges;

8. 远程登录

mysql -utest -h192.168.100.10   -p'密码'  -P端口号


vim /etc/my.cnf
[mysqld]
port = 9527

systemctl restart   mysqld

客户机
mysql -utest -h192.168.100.10   -p'Admin@123'  -P9527

八、用户权限管理

权限类别：

管理类
程序类
数据库级别
表级别
字段级别

库和表级别：针对 DATABASE、TABLE

ALTER
CREATE
CREATE VIEW
DROP INDEX
SHOW VIEW
WITH GRANT OPTION：能将自己获得的权限转赠给其他用户

数据操作

SELECT
INSERT
DELETE
UPDATE

1. 查看权限

show grants for '用户名'@'主机地址';
//USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。

2. 授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

语法：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

----------------------------------------------------------------------------------------------------------
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，
  表示某个区域或网段内的所有地址，如“%.kgc.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。
----------------------------------------------------------------------------------------------------------

示例：

grant all on hellodb.students to 'lkk'@'localhost';
//给用户lkk授权，可对hellodb数据库中的students表执行任何操作

GRANT ALL  ON *.* TO 'cxk'@'%' IDENTIFIED BY '123123';
//一步完成建立用户并授权
//注意！8.0版本取消此操作，必须先建用户，再授权

3. 撤销权限

语法：

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

示例：

revoke all on *.* from 'cxk'@'%';

4. 远程连接图形界面

grant all privileges on *.* to test@'192.168.%.%' identified by '123123';
flush privileges;

九、总结

库
创建数据库	create database 数据库名称;
删除数据库	drop database 数据库名称;
查看目前所有数据库	show databases;
查看数据库的详细信息	show create database 数据库名称;
切换数据库	use 数据库名称;

表
新建表	①use 数据库名 ②create table 表名（字段1 数据类型，字段2 数据类型， .... ）
显示当前数据库下的所有表	show tables;
查看表结构	desc 表名;
查看表中的所有数据	select * from 表名;
添加表的字段	alter table 表名 add 字段名数据类型 [修饰符];
在表中插入数据	insert 表名（字段1，字段2...） values （字段1的值，字段2的值...）
更新数据	update 表名 set 字段='修改的值' [,字段2='修改的值2'] where 指定条件;
删除数据	delete from 表名 where 指定条件;

用户管理
查看当前使用用户	select user();
新建用户	create user '用户名'@'主机地址' identified by '密码';
修改用户名称	rename user '旧名字' to '新名字';
删除用户	drop user '用户名'@'主机名';
查看权限	show grants for '用户名'@'主机地址';
授予权限	grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];
撤销权限	revoke 权限列表 on 数据库名.表名 from '用户名'@'来源地址';
远程连接mysql	mysql -u 用户名 -p[密码] -h 目标IP/主机名 -P 端口号

MySQL数据库之库、表、用户管理