MySQL|MySQL数据库基础（一） mysql|database|数据库

MySQL数据库基础

一、数据库的基本概念
- 1.1数据的时代
- 1.2 数据库的发展史
- - 1.2.1 文件管理系统的缺点
- 1.3 DBMS 数据库管理系统
- 1.4 数据库管理系统的优点
- 1.5数据库管理系统的基本功能
- 1.6数据库系统的架构
- 1.7 各种数据库管理系统
- - 1.7.1 关系统型数据库相关概念
  - - 1.7.1.1 实体联系模型E-R
    - 1.7.1.2 联系类型
    - 1.7.1.3 数据的操作
    - 1.7.1.4 数据库规划流程
  - 1.7.2 常用关系数据库
  - 1.7.3 数据库的分类
  - - 1.7.3.1 关系型数据库与非关系型数据库
- 1.8 使用数据库的必要性
- 1.9 数据库基本概念
- - 1.9.1 数据（Data）
  - 1.9.2 表
  - 1.9.3 数据库
  - 1.9.4 数据库管理系统（DBMS）
  - 1.9.5 数据库系统
  - 1.9.6 访问数据库的流程
二、一键安装mysql脚本
三、数据库基本操作
- 3.1 SQL分类
- 3.2 SQL 语言规范
- 3.3 查看帮助信息
- 3.4 查看支持的字符集
- 3.5 查看表，使用表
- 3.6 管理数据库
- - 3.6.1 创建数据库
  - 3.6.2 删除数据库
  - 3.6.3 DML管理表中的数据记录：insert 、update、delete
  - 3.6.4 DQL查询数据记录
  - 扩展
  - 3.6.5 数据表高级操作
  - - 3.6.5.1 复制、克隆表
    - 3.6.5.2 清空、删除表
    - 3.6.5.3 创建临时表
  - 3.6.7 MySQL中6种常见的约束
  - - 3.6.7.1 什么是外键？
  - 3.6.8 数据库用户管理
  - - 3.6.8.1 新建用户
    - 3.6.8.2 查看用户信息
    - 3.6.8.3 重命名指定
    - 3.6.8.4 删除用户
    - 3.6.8.5 修改当前密码
    - 3.6.8.6 修改其他用户密码
    - 3.6.8.7 忘记root密码的解决办法
    - 3.6.8.8 数据库用户授权
    - 3.6.8.9 查看权限
    - 3.6.8.10 撤销权限
总结

一、数据库的基本概念 1.1数据的时代

涉及的数据量大
数据不随程序的结束而消失
数据被多个应用程序共享
大数据

数据的分类

结构化的数据：即有固定格式和有限长度的数据。例如填的表格就是结构化的数据，国籍：中华人民共和国，民族：汉，性别：男，这都叫结构化数据
非结构化的数据：非结构化的数据越来越多，就是不定长、无固定格式的数据，例如网页，有时候
非常长，有时候几句话就没了；例如语音，视频都是非结构化的数据
半结构化数据：比如：XML或者HTML的格式的数据

1.2 数据库的发展史

萌芽阶段：文件系统
- 使用磁盘文件来存储数据
初级阶段：第一代数据库
- 出现了网状模型、层次模型的数据库
中级阶段：第二代数据库
- 关系型数据库和结构化查询语言
高级阶段：新一代数据库
- “关系-对象”型数据库

1.2.1 文件管理系统的缺点

编写应用程序不方便
数据冗余不可避免
应用程序依赖性
不支持对文件的并发访问
数据间联系弱
难以按用户视图表示数据
无安全控制功能

1.3 DBMS 数据库管理系统

Database：数据库是数据的汇集，它以一定的组织形式存于存储介质上
DBMS：是管理数据库的系统软件，它实现数据库系统的各种功能。是**数据库系统的核心**。
DBA：负责数据库的规划、设计、协调、维护和管理等工作。
应用程序：指以数据库为基础的应用程序。

1.4 数据库管理系统的优点

相互关联的数据的集合
较少的数据冗余
程序与数据相互独立
保证数据的安全、可靠
最大限度地保证数据的正确性
数据可以并发使用并能同时保证一致性

1.5数据库管理系统的基本功能

数据定义
数据处理
数据安全
数据备份

1.6数据库系统的架构

单机架构
大型主机/终端架构
主从式架构（C/S）
分布式架构

1.7 各种数据库管理系统 1.7.1 关系统型数据库相关概念

关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK , 用于惟一确定一个记录的字段，一张表只有一个主键
域domain：属性的取值范围，如，性别只能是’男’和’女’两个值，人类的年龄只能0-150

1.7.1.1 实体联系模型E-R 实体Entity： 客观存在并可以相互区分的客观事物或抽象事件称为实体,在E-R图中用矩形框表示实体，把实体名写在框内。
属性： 实体所具有的特征或性质，用 椭圆形 表示属性。
联系： 联系是数据之间的关联集合，是客观存在的应用语义链。
? 实体内部的联系：指组成实体的各属性之间的联系。如职工实体中，职工号和部门经理号之间有一种关联关系
? 实体之间的联系：指不同实体之间联系。例：学生选课实体和学生基本信息实体之间
? 实体之间的联系用菱形框表示

文章图片

1.7.1.2 联系类型一对一联系(1:1)
一对多联系(1:n)：外键
多对多联系(m:n)：增加第三张表
1.7.1.3 数据的操作

数据提取：在数据集合中提取感兴趣的内容。SELECT
数据更新：变更数据库中的数据。INSERT、DELETE、UPDATE

1.7.1.4 数据库规划流程 1.收集数据，得到字段
2.收集必要且完整的数据项
3.转换成数据表的字段
1.7.2 常用关系数据库

MySQL: MySQL, MariaDB, Percona ServerPostgreSQL: 简称为pgsql，EnterpriseDBOracleMSSQLDB2（IBM公司）SQL server（微软) access(微软公司office产品）数据库排名： DB-Engines Ranking - popularity ranking of database management systems

1.7.3 数据库的分类
① 关系型数据库: 存储的往往是字符、字符串、数值、布尔值等
② 非关系型数据库：存储的往往是图片、视频、语音等
非关系型数据库（NoSQL）：
MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对
redis-memecache对比：

相同点：存储高热数据(在内存中高速运行）不同点：redis可以做持久化保存，可以存储对象

1.7.3.1 关系型数据库与非关系型数据库 1）描述主流的数据库系统关系型数据库： Mysql（Oracle公司）、 SQL server（微软) 、access(微软公司office产品） Oracle 、DB2（IBM公司），sybase（sybase）等等
2）关系型数据库和非关系型数据库的区别关系数据库：
关系数据库结构是二维数据库表，二维表当中每个字段（列）用来描述对象的一个属性，每个记录（行）用来描述一个对象的信息（完整信息），关系数据库写到哪里也就是存储在硬盘当中读写系统就会受到的IO限制或者瓶颈。
其他关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询； 3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
缓存加速软件非关系型数据库（NoSQL）： MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对 redis-memecache对比：相同点：存储高热数据(在内存中高速运行）不同点：redis可以做持久化保存，可以存储对象
非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。 2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘； 3、高扩展性与高可用性； 4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1、不提供sql支持，学习和使用成本较高； 2、无事务处理； 3、数据结构相对复杂，复杂查询方面稍欠。

1.8 使用数据库的必要性使用数据库可以高效且条理分明地存储数据，使人们能够更加迅速、方便地管理数据。数据库具有以下特点。

- 可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。- 可以有效地保持数据信息的一致性、完整性，降低数据冗余。- 可以满足应用的共享和安全方面的要求。- 数据库技术是计算机科学的核心技术之一，具有完备的理论基础。对数据库基本概念的掌握，将有助于对数据库的理解。

1.9 数据库基本概念 1.9.1 数据（Data）

描述事物的符号记录
包括数字，文字、图形、图像、声音、档案记录等
以“记录”形式按统一的格式进行存储

1.9.2 表

将不同的记录组织在一起
用来存储具体数据

1.9.3 数据库

表的集合，是存储数据的仓库
以一定的组织方式存储的相互有关的数据集合

1.9.4 数据库管理系统（DBMS）
数据库管理系统（Database Management System，DBMS）是实现对数据库资源有效组织、管理和存取的系统软件。它在操作系统的支持下，支持用户对数据库的各项操作。
DBMS 主要包括以下功能:

数据库的建立和维护功能： 包括建立数据库的结构和数据的录入与转换、数据库的转储与恢复、数据库的重组与性能监视等功能。
数据定义功能： 包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合语义的错误数据被输入或输出。
数据操纵功能： 包括数据查询统计和数据更新两个方面。
数据库的运行管理功能： 这是数据库管理系统的核心部分，包括并发控制、存取控制、数据库内部维护等功能。
通信功能： DBMS 与其他软件系统之间的通信,如 Access 能与其他 Office 组件进行数据交换。

1.9.5 数据库系统

是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
用户可以通过DBMS或应用程序操作数据库

1.9.6 访问数据库的流程

文章图片

DBMS的工作模式： 1.接收应用程序的数据请求和处理请求 2.将用户的数据请求（高级指令）转换为复杂机器代码（底层指令） 3.实现对数据库的操作 4.从数据库的操作中接受查询结果 5.对查询结果进行处理（格式转换） 6.将处理结果返回给用户

二、一键安装mysql脚本

#!/bin/bash #编译安装Mysql服务 #使用脚本前注意事项： #1、请设置好yum源仓库 #2、请在/opt目录下放入mysql-boost-5.7.20.tar.gz安装包 #编写安装mysql的函数 MySQL() { #关闭系统防火墙和安全机制 systemctl stop firewalld systemctl disable firewalld setenforce 0 #安装环境依赖包 yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake #创建运行用户 useradd -M -s /sbin/nologin mysql #编译安装 cd /opt tar xzvf mysql-boost-5.7.20.tar.gz cd /opt/mysql-5.7.20/ cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DSYSCONFDIR=/etc \ -DSYSTEMD_PID_DIR=/usr/local/mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DWITH_BOOST=boost \ -DWITH_SYSTEMD=1 #编译及安装 make && make install #修改mysql 配置文件 echo '[client] port = 3306 socket=/usr/local/mysql/mysql.sock [mysqld] user = mysql basedir=/usr/local/mysql datadir=/usr/local/mysql/data port = 3306 character-set-server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket=/usr/local/mysql/mysql.sock bind-address = 0.0.0.0 skip-name-resolve max_connections=2048 default-storage-engine=INNODB max_allowed_packet=16M server-id = 1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES' > /etc/my.cnf #更改mysql安装目录和配置文件的属主属组 chown -R mysql:mysql /usr/local/mysql/ chown mysql:mysql /etc/my.cnf #设置路径环境变量 echo "export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib">> /etc/profile source /etc/profile #初始化数据库 cd /usr/local/mysql/bin/ ./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data #添加mysqld系统服务 cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ systemctl daemon-reload systemctl start mysqld.service systemctl enable mysqld } #主体代码 ls /opt | egrep "^mysql-boost-5.7.20.tar.gz$" if [ $? -eq 0 ]; then #调用函数安装mysql MySQL echo "数据库安装完毕，请设定root用户密码。" echo "例如：输入“mysqladmin -u root -p password "123456" ” + 回车；回车" ln -s /usr/local/mysql/bin/* /usr/local/bin else echo -e "请将安装包mysql-boost-5.7.20.tar.gz放入/opt目录下" #如果没有就终止 break fi

三、数据库基本操作 3.1 SQL分类

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

3.2 SQL 语言规范

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

数据库对象和命名
数据库的组件(对象)：

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

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）不要使用MySQL的保留字,如tabble select show databases

SQL语句分类

DDL: Data Defination Language 数据定义语言，用于创建数据库对象，如库、表、索引等。eg：create、drop、alter。
DML: Data Manipulation Language 数据操纵语言，用于对表中的数据进行管理，用来插入、删除、修改数据库中的数据。eg：insert、delete、update。
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录。eg：select。
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，如commit、follback、grant、revoke）

#DDL: Data Defination Language 数据定义语言 CREATE，DROP，ALTER #DML: Data Manipulation Language 数据操纵语言 INSERT，DELETE，UPDATE #DQL：Data Query Language 数据查询语言 SELECT #DCL：Data Control Language 数据控制语言 GRANT，REVOKE，COMMIT，ROLLBACK SELECT *#SELECT子句 FROM products#FROM子句 WHERE price>666#WHERE子句

3.3 查看帮助信息

mysql> help create #help 后面跟上具体命令可以查看帮助 Many help items for your request exist. To make a more specific request, please type 'help ', where is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SHOW CREATE USER SPATIAL ##也可以进一步 mysql> help create DATABASE \G

3.4 查看支持的字符集

show charset; #查看支持字符集默认拉丁文字 utf8| UTF-8 Unicode #阉割版的 utf8mb4| UTF-8 Unicode #真实的版本我们编译安装已经修改过了

3.5 查看表，使用表

mysql> show databases; #查看当前数据库 mysql> use mysql； #使用 mysql表MySQL root@localhost:school> select * formky19; #查看ky19表格的

文章图片

文章图片

3.6 管理数据库 3.6.1 创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME' CHARACTER SET 'character set name' COLLATE 'collate name'; #创建数据库 create database a1; #创建的时候指定语言 create database db3 charset=utf8; #查看创建的数据库 show createdatabase a1; #加的数据位置 cd /usr/local/mysql/data/test/

文章图片

文章图片

3.6.2 删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 例如 drop database zzh;

文章图片

3.6.3 DML管理表中的数据记录：insert 、update、delete

格式： INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...); 示例: INSERT INTO zzz (id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456')); #PASSWORD('123456'):查询数据记录时，密码字串以加密形式显示:若不使用PASSWORD()，查询时以明文显示。INSERT INTO zzz VALUES(2,'lisi',90.5,654321) ; SELECT * FROM zzz ; #查询表的数据记录#修改、更新数据表中的数据记录格式: UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式]; 示例: UPDATE zzz SET passwd=PASSWORD('') WHERE name='zhangsan'; UPDATE zzz SET name='wangxiaoer',passwd='' WHERE id=3; 在数据表中删除指定的数据记录格式: DELETE FROM 表名 [WHERE 条件表达式]; 示例: DELETE FROM zzz WHERE id=4;

文章图片

文章图片

3.6.4 DQL查询数据记录

select格式： SELECT 字段名1,字段名2[,...] FROM 表名[WHERE 条件表达式]; 示例： SELECT * FROM zzz; SELECT id,name,score FROM zzz WHERE id=2; select name from zzz\G#以列表方式竖向显示 select * from info limit 2; #只显示头2行 select * from info limit 2,3; #显示第2行后的前3行#修改、更新数据表中的数据记录格式: UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式]; 示例: UPDATE zzz SET passwd=PASSWORD('') WHERE name='zhangsan'; UPDATE zzz SET name='wangxiaoer',passwd='' WHERE id=3; 在数据表中删除指定的数据记录格式: DELETE FROM 表名 [WHERE 条件表达式]; 示例: DELETE FROM zzz WHERE id=4; 修改表名和表结构 ALTER TABLE 旧表名 RENAME 新表名; 例： ALTER TABLE zzz RENAME yyy; ALTER TABLE yyy RENAME zzz; 扩展表结构(增加字段) ALTER TABLE 表名 ADD address varchar(50) default '地址不详'; #default '地址不详'：表示此字段设置默认值为地址不详，可与NOT NULL配合使用ALTER TABLE yyy ADD address varchar(50) NOT NULL default '地址不详'; 修改字段(列)名，添加唯一键 ALTER TABLE 表名 CHANGE 旧列名新列名数据类型 [unique key]; ALTER TABLE yyy CHANGE name user_name varchar(10) unique key; #CHANGE可修改字段名、数据类型、约束等所有项。删除字段格式： ALTER TABLE 表名 DROP 字段名; 示例： ALTER TABLE yyy DROP passwd;

文章图片

文章图片

扩展

use school; create table if not exists info ( id int(4) zerofill primary key auto_increment,#指定主键的第二种方式 name varchar(10) not null, cardid int(18) not null unique key, hobby varchar(50)); #if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建 #int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001 #auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增; 自增长字段数据不可以重复; 自增长字段必须是主键; 如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次 #unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键 #not null：表示此字段不允许为NULL

高级操作 use school; create table if not exists info ( id int(4) zerofill primary key auto_increment,#指定主键的第二种方式 name varchar(10) not null, cardid int(18) not null unique key, hobby varchar(50)); #if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建 #int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001 #auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增; 自增长字段数据不可以重复; 自增长字段必须是主键; 如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次 #unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键 #not null：表示此字段不允许为NULL

文章图片

文章图片

3.6.5 数据表高级操作
3.6.5.1 复制、克隆表

create table yyy2 like yyy; #复制格式，通过LIKE方法，复制yyy表结构生成yyy2表 insert into yyy2 select * from yyy; #备份内容克隆表，将数据表的数据记录生成到新的表中 CREATE TABLE test02 (SELECT * from test); #复制test 表数据到test02中show create table test02\G#获取数据表的表结构、索引等信息 SELECT * from test02;

文章图片

文章图片

3.6.5.2 清空、删除表

方法一： delete from yyy3; #DELETE清空表后，返回的结果内有删除的记录条目; DELETE 工作时是一行一行的删除记录数据的; 如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。方法二： truncate table test01; #TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上TRUNCATE会比DELETE清空表快; 使用TRUNCATE TABLE 清空表内数据后， ID会从1开始重新记录#删除速度 drop> truncate > delete 安全性 delete 最好

3.6.5.3 创建临时表

临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。 PS：无法创建外键 CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[, ...] [, PRIMARY KEY (主键名)]);

示例: create temporary table xxx ( id int(4) zerofill primary key auto_ increment, name varchar(10) not null, cardid int(18) not null unique key, hobby varchar(50)); insert into xxx values (1,'zhangsan',123456,'running') ; select * from test03; show tables; quit select * from test03;

3.6.7 MySQL中6种常见的约束

主键约束(primary key)
外键约束(foreign key)
非空约束(not null)
唯一性约束(unique [key|index])
默认值约束(default)
自增约束(auto_increment)

3.6.7.1 什么是外键？

外键的定义:如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。

创建外键约束作用(误删，修改)，保证数据的完整性和一致性。
主键表和外键表的理解：

(1)以公共关键字作主键的表为主键表(父表、主表) (2)以公共关键字作外键的表为外键表(从表、外表)

注意: 与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。

#创建主表test04 create table test04 (hobid int(4),hobname varchar(50)); #创建从表test05 create table test05 (id int(4) primary key auto_increment, name varchar(10),age int(3),hobid int(4)) ; #为主表test04添加一个主键约束。主键名建议以"PK_”开头。 alter table test04 add constraint PK_hobid primary key (hobid); #为从表test05表添加外键，并将test05表的hobid字段和test04表的hobid字段建立外键关联。外键名建议以"FK_”开头。 alter table test05 add constraint FK_hob foreign key (hobid) references test04 (hobid); #可以使用查询表语句结构命令查看外键关联 show create table test05; desc test04; desc test05; #插入新的数据记录时，要先主表再从表 insert into test04 values(1,'runing'); insert into test05 values(1,'zhangsan',18,1); #删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表。 drop tables test05; drop tables test04; #查看和删除外键约束 #如果要删除外键约束字段先删除外键约束，再删除外键名 show create table test05; alter table test05 drop foreign key FK_hob; alter table test05 drop key FK_hob; desc test05;

3.6.8 数据库用户管理
3.6.8.1 新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码']; '用户名'：指定将创建的用户名. '来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符% '密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；若使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；若省略“IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456'; SELECT PASSWORD('abc123'); CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

文章图片

3.6.8.2 查看用户信息

创建后的用户保存在mysql 数据库的user表里 USE mysql; SELECT User,authentication_string,Host from user;

文章图片

文章图片

3.6.8.3 重命名指定

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

3.6.8.4 删除用户

DROP USER 'lisi'@'localhost' ;

文章图片

3.6.8.5 修改当前密码

SET PASSWORD = PASSWORD('abc123');

3.6.8.6 修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T);

3.6.8.7 忘记root密码的解决办法

修改/etc/my.cnf 配置文件，免密登陆mysql vim /etc/my.cnf [mysqld] skip-grant-tables#添加，使登录mysql不使用授权表systemctl restart mysqld mysql#直接登录然后使用SQL语句修改密码 UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root'; FLUSH PRIVILEGES; quit mysql -u root -pabc123 PS:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除，并重启mysql服务

【MySQL|MySQL数据库基础（一）】

文章图片

文章图片

3.6.8.8 数据库用户授权

授予权限 grant 提权 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码']; #权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, update”。使用"all"表示所有权限，可授权执行任何操作。#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用“kgc.*"表示授权操作的对象为school数据库中的所有表。#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192. 168.80.%”等。#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。#允许用户zhangsan在本地查询school数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。 GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123'; #允许用户lisi在所有终端远程连接mysql，并拥有所有权限。 GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456'; flush privileges; #刷新权限 quitmysql -u zhangsan -pabc123 use xyw; show tables; . select * from yyy;

3.6.8.9 查看权限

mysql -u root -pabc123 SHOW GRANTS FOR 用户名@来源地址; SHOW GRANTS FOR 'lisi'@'%';

3.6.8.10 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址; REVOKE ALL ON *.* FROM 'lisi'@'%'; SHOW GRANTS FOR 'lisi'@'%'; #USAGE权限只能用于数据库登陆，不能执行任何操作; USAGE权限不能被回收，即REVOKE不能删除用户。 flush privileges;

总结