数据管理的演变

1. 人工档案管理
2. 管理文件系统
3. 数据库管理

数据库管理系统

1. 层次数据库
2. 网格数据库
3. 关系数据库
4. 对象型数据库
5. 非关系数据库

概述 数据库是一存储在计算机中，有组织、共享和统一管理. 我们可以以二维表格的形式理解数据库中的数据，例如 表中一列的标题称为字段 表中的一行数据称为记录 

数据库由三部分组成 数据库：存储数据 数据库管理系统：用户管理数据库的软件 数据库应用程序：补充数据库管理，以提高数据库系统的管理能力  

[外链图片存储失败，源站可能有防盗链机制，建议保存图片直接上传(img-EyGefmnw-1656138158734)(./img/

查询和修改数据库的语言称为SQL 结构化查询语言(Structured Query Language)简称SQL(发音:///es kju? ?el/ "S-Q-L")，编程语言是一种用于访问数据、查询、更新和管理关系数据库系统的数据库查询和程序设计语言；它也是数据库脚本文件的扩展名称。 标准：SQL-92、SQL-99、SQL-2003方言 关系数据库常用 Oracle、MySQL、SQLServer... 对象数据库 PostgreSQL... 常用的非关系数据库(在中国) Redis、MongoDB、HBase... 按照SQL-92标准SQL包含4部分 1据库定义语言(DDL)：create、drop、alter... 2.数据库操作语言(DML)：insert、update、delete... 3.数据库查询语言(DQL)：select 4.数据库控制语言(DCL)：commit、rollback、grant、revoke... 

sudo yum -y update 

sudo yum -y install vim net-tools wget gcc make cmake lrzsz 

sudo yum -y install https://repo.mysql.com//mysql80-community-release-el8-4.noarch.rpm 

sudo yum repolist enabled | grep mysql 

sudo yum -y install mysql-community-server 

使用以下命令启动 MySQL 服务器：

sudo systemctl start mysqld 

您可以使用以下命令进行检查 MySQL 服务器状态：

sudo systemctl status mysqld 

使用以下命令开始自启动 MySQL 服务器：

sudo systemctl enable mysqld 

假设服务器的数据目录是空的，服务器初始启动时会发生以下情况：

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; FLUSH PRIVILEGES; 

笔记

validate_password 默认安装。实现的默认密码策略validate_password要求密码至少包含1个大写字母、1个小写字母、1个数字和1个特殊字符，密码总长度至少为8个字符。

validate_password检查语句中的明文密码。在要求密码长度至少为 8 个字符的默认密码策略下，密码很弱并且语句会产生错误：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Lihaozhe!!@@1122';

不检查指定为散列值的密码，因为原始密码值不可用于检查：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@1122';
FLUSH PRIVILEGES;

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
FLUSH PRIVILEGES;

exit;

sudo systemctl restart mysqld

# 使用新密码连接
mysql -h 你自己的IP地址 -uroot -p

–zone #作用域

–add-port=1935/tcp #添加端口，格式为：端口/通讯协议

–permanent #永久生效，没有此参数重启后失效

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload 

sudo apt update
sudo apt -y dist-upgrade 

sudo apt -y install vim net-tools wget gcc make cmake lrzsz
sudo apt -y install libmecab2 libjson-perl 

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-server_8.0.29-1debian10_amd64.deb-bundle.tar

mkdir mysql & tar -xvf mysql-server_8.0.29-1debian10_amd64.deb-bundle.tar -C mysql cd mysql

cd mysql 
sudo dpkg -i mysql-{ 
        common,community-client,community-client-core,community-client-plugins,client,community-server,community-server-core,server}_*.deb

select host , user , plugin ,authentication_string from mysql.user;

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
flush privileges;

quit;

sudo systemctl restart mysql

-- 创建密码为 Lihaozhe!!@@6633 的用户 lhz 可以通过 本机访问 
create user 'lhz'@'localhost' identified by 'Lihaozhe!!@@6633';
-- 授权 lhz 用户 可以通过本机 访问 所有数据中所有表 
grant all privileges on *.* to 'lhz'@'localhost' with grant option;
-- 刷新权限
flush privileges;

-- 创建密码为 Lihaozhe!!@@6633 的用户 lhz 可以通过 任意地址访问 密码策略为 mysql_native_password
create user 'lhz'@'%' identified with mysql_native_password by 'Lihaozhe!!@@6633';
-- 授权 lhz 用户 可以通过本机 访问 所有数据中所有表 
grant all privileges on *.* to 'lhz'@'%' with grant option;
-- 刷新权限
flush privileges;

-- 如果遇到 ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
-- 原因是由于root用户没有SYSTEM_USER权限，把权限加入后即可解决：
grant system_user on *.* to 'root';

# openEuler
sudo vim /etc/my.cnf

# Deepin UOS 
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

openEuler配置如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

skip-grant-tables

Deepin UOS 配置如下：

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log

skip-grant-tables

# openEuler
sudo systemctl restart mysqld

# Deepin UOS
sudo systemctl restart mysql

sudo mysql

FLUSH PRIVILEGES;

select user,host from mysql.user where user = 'root';

-- 如果查询结果host的值为 localhost 执行
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@6633';

-- 如果查询结果host的值为 % 执行
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@6633';

-- 如果遇到 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
-- 先执行 FLUSH PRIVILEGES; 再修改密码

-- 如果遇到 ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
-- 原因是由于root用户没有SYSTEM_USER权限，把权限加入后即可解决：
grant system_user on *.* to 'root';

FLUSH PRIVILEGES;

exit;

sudo vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# skip-grant-tables

sudo systemctl restart mysqld

sudo mysql -uroot -p

mysql -uroot -p

提示信息如下：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

show databases;

提示信息如下：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

create database `day01`;

提示信息如下：

Query OK, 1 row affected (0.00 sec)

show databases;

+--------------------+
| Database           |
+--------------------+
| day01              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

show create database `day01`;

提示信息如下：

或者：

show create database `day01`\G

提示信息如下：

CREATE DATABASE `day011` DEFAULT CHARACTER SET gbk;

SHOW CREATE DATABASE `day011`;

提示信息如下：

drop database `day011`;

提示信息如下：

Query OK, 0 rows affected (0.00 sec)

select database();

提示信息如下：

+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

use `day01`;

提示信息如下：

Database changed

select database();

+------------+
| database() |
+------------+
| day01      |
+------------+
1 row in set (0.00 sec)

use `day01`;

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
);

提示信息如下：

Query OK, 0 rows affected, 1 warning (0.01 sec)

show tables;

提示信息如下：

+-----------------+
| Tables_in_day01 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

show create table `user`;

show create table `user`\G

show engines;

drop table `user`;

提示信息如下：

Query OK, 0 rows affected (0.01 sec)

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
) engine=myisam default charset=gbk;

describe `user`;

提示信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

或者

describe `user`\G

*************************** 1. row ***************************
  Field: id
   Type: int
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: account
   Type: varchar(11)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 3. row ***************************
  Field: password
   Type: varchar(64)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 4. row ***************************
  Field: nickname
   Type: varchar(50)
   Null: YES
    Key: 
Default: NULL
  Extra: 
4 rows in set (0.00 sec)

insert into `user` (`id`,`account`,`password`,`nickname`) value (1,'a1','123456','u1');

提示信息如下：

`day01` Query OK, 1 row affected (0.01 sec)

检查插入数据是内容

select * from `user`;

查询结果如下：

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | 123456   | u1       |
+------+---------+----------+----------+
1 row in set (0.00 sec)