MySQL数据库基础
MySQL数据库
概念：数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
1.分类：
RDBMS关系型数据库：
MySQL，Oracle，MarIDB，SQL server
NoSQL非关系型数据库：
redis mencache，MongoDB，Cassandra，ES
NewSQL新型分布式数据库：
spaner（谷歌） TiDB（淘宝）polarDB（阿里）
2.MySQL安装方式：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用程序包管理器管理的程序包：yum安装，apt安装
3.MySQL5.7版本设置数据库简单密码方法如下：
#设置参数
set global validate_password_policy=LOW; #关闭检查密码复杂性
set global validate_password_length=6; #设置管理员密码长度
#设置密码
mysql> select user() ;
ALTER USER USER() IDENTIFIED BY '123456';
或者写成
alter user root@localhost identified by "123456";
4.MySQL8.0版本设置数据库简单密码方法如下：
set global validate_password.policy=LOW;
set global validate_password.length=6;
ALTER USER USER() IDENTIFIED BY '123456';
数据库用户身份分为三类：管理员、系统用户、普通用户
5.MySQL创建用户：
- 创建MySQL用户，设置登录密码 (关键字:create)
create user xiaom@'10.0.0.%' identified by '123';
- MySQL8.0以前，可以自动创建用户并授以权限 (关键字:grant)
grant all on *.* to xiaom@'10.0.0.%' identified by '123';
- 查询用户 (关键字:select)
select user,host from mysql.user;
- 删除用户 (关键字:drop)
drop user xiaom@'10.0.0.%';
6.修改密码：
- 新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中， 如果mysql.user表的authentication_string和password字段都保存密码，authentication_string优先生效
方法1
#MySQL 8 版本不支持此方法
SET PASSWORD FOR 'user'@hostlocal = PASSWORD('password');
#MySQL 8 版本修改密码
alter user test@'%' IDENTIFIED BY 'centos';
方法2
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where user='mage';
#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;
7.MySQL权限列表
MySQL普通用户授权如下
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX,
ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL # 对用户授权于以上权限，一般普通管理员拥有此权限
with grant option # 可以对其他用户授权的权限，一般本地超级管理员拥有此权限，此权限只能写在授权命令grant最后
8.用户授权命令格式
MySQL 7.0+ 版本:grant命令既可以授予权限,又可以创建用户设置密码
grant 权限列表 on 作用目标 to 授权用户 identified by 密码;
# MySQL 8.0 版本:建用户和授权分开了，grant 不再支持自动创建用户了，不支持改密码，授权之前，必须要提前创建用户。
grant 权限列表 on 作用目标 to 授权用户;
# 作用目标格式
1.MySQL所有数据库下所有数据表授权给用户:
2.针对MySQL一个数据库下所有表授权给用户: 数据库
3.只对MySQL下单独的某张数据表授权给用户: 数据库.数据表
9.查看用户授权情况
- 查看用户权限: show grants for 用户;
mysql> show grants for wordpress@'10.0.0.%';
10.回收用户权限
# 回收用户权限: revoke 权限 on 授权的数据库/数据表 from 用户;
# 回收wordpress用户对wordpress数据库的delete权限
mysql> revoke delete on wordpress.* from wordpress@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for wordpress@'10.0.0.%';
11.回收用户的授权的权限 grant option
mysql> GRANT ALL ON *.* TO xiaom@'10.0.0.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> show grants for xiaom@'10.0.0.%';
mysql> REVOKE GRANT OPTION ON *.* from xiaom@'10.0.0.%';
12.MySQL5.7基本初始化配置文件模板
- 服务端
[mysqld]
# 指定MySQL管理用户
user=mysql
# 指定MySQL安装路径
basedir=/app/mysql
# 指定MySQL数据存放路径
datadir=/data/mysql/data
# 指定MySQL的socket文件存放在服务端路径
socket=/tmp/mysql.socket
# 配置MySQL主从复制，数值 1 ~ 65535
server_id=6
# MySQL服务端口
port=3306
- 客户端
[mysql]
# 指定MySQL的socket文件存放在客户端路径
socket=/tmp/mysql.socket
13.TCP/IP 连接数据库
- 数据库授权远程连接：
grant all privileges on db.* to root@'localhost' identified by '123456' with grant option;
- mysql连接数据库参数
-u # 指定登录用户
-h # 指定登录数据库服务器IP地址或者本地登录localhost
-p # 指定登录用户密码
-P # 指定连接数据库端口，-P参数必须配合-h
-e # 免交互执行SQL语句
-S # 本地登录指定socket文件
< # 导入数据，常常用于恢复数据
- 示例
# TCP/IP登录数据库
[root @db01 ~ ]# mysql -uxiaom -h10.0.0.51 -p123456 -P3306
# TCP/IP连接数据库，只是用-P指定端口，默认以本地localhost连接
[root @db01 ~ ]# mysql -uxiaom -p123456 -P3306
# 免交互执行SQL语句
[root @db01 ~ ]# mysql -uxiaom -h10.0.0.51 -p123456 -P3306 -e 'select user,host from mysql.user;'
14.创建数据库 - 语法
CREATE { DATABASE|SCHEMA } [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
#创建数据库
CREATE DATABASE <库名> CHARSET <字符集> COLLATE <排序规则>;
#判断数据库是否存在，如果数据库已存在不会创建,如果数据库不存在则会创建
CREATE DATABASE IF NOT EXISTS <库名> CHARSET <字符集> COLLATE <排序规则>;
15.范例：使用mysql数据库默认字符集创建数据库
mysql> create database db01;
mysql> show create database db01;
#创建数据库字符集信息会存放到数据库对系统目录下 db.opt文件中
[root@centos7 ~ ]# cat /data/mysql/data/db01/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
#当db01数据库已经创建,再次执行创建db01数据库SQL语句会报错
mysql> create database db01;
ERROR 1007 (HY000): Can't create database 'db01'; database exists
#创建数据库判断
mysql> create database if not exists db01;
Query OK, 1 row affected, 1 warning (0.00 sec) #1条警告信息
#如果创建数据库时判断其数据库是否存在，如果存在会产生一条警告信息，查看数据库警告信息
mysql> show warnings\G
*************************** 1. row ***************************
Level: Error
Code: 1064
Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
16.范例：指定字符集创建新数据库
mysql> create database IF NOT EXISTS db02 CHARACTER SET 'utf8';
[root@centos7 ~ ]# cat /data/mysql/data/db02/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
17.修改数据库语法
- 修改字符集
ALTER DATABASE <库名> character set <字符集> collate <排序规则>;
18.范例：修改db01数据库的字符集
mysql> alter database db01 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db01;
删除数据库语法删除数据库区分大小写
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
mysql> show databases;
mysql> drop database DB2;
ERROR 1008 (HY000): Can't drop database 'DB2'; database doesn't exist
mysql> drop database db2;
Query OK, 0 rows affected (0.03 sec)
19.MySQL中的系统数据库
数据类型：数字，字符串，时间，二进制
20.创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名(
字段1 属性(数据类型、约束、其他属性),
字段2 属性(数据类型、约束、其他属性),
字段3 属性(数据类型、约束、其他属性)
)ENGINE 存储引擎 CHARSET 排序规则 COMMENT 注释;
21.数据库常用指令：
查看数据库：SHOW DATABASES;
创建数据库：CREATE DATABASE 数据库名；
删除数据库：DROP DATABASE 数据库名；
切换数据库：USE 数据库名；
查看数据表：SHOW TABLES;
查看数据表结构：DESC 表名；
创建数据表：CREATE TABLE 表名 ( 列1 数据类型, 列2 数据类型, ... );
删除数据表：DROP TABLES 表名;
修改表名称：ALTER TABLE 表名 RENAME 新表名；
修改表中的字段类型：alter table 表名 modify 要修改的字段名 要修改的类型 ;
修改表中的字段类型和字段名称：alter table 表名 change 原字段名 新字段名 新字段类型;
在表中添加字段：alter table 表名 add 字段名 字段类型；
创建用户：CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
删除用户：DROP USER '用户名'@'主机';
阅读建议
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果