MySQL基础

伪装成塔的小兵

已于 2023-09-05 23:31:17 修改

阅读量49

点赞数 1

文章标签： mysql 数据库 centos

于 2023-09-05 21:50:00 首次发布

本文链接：https://blog.csdn.net/qq_63153457/article/details/132701117

版权

MySQL基础

一、关系型数据库介绍

1.数据库结构模型

数据结构模型主要有：

层次模型
网状结构
关系模型

模型	概念
层次模型	层次模型是一种以树状结构组织数据的模型。它使用父子关系将数据组织成层次结构，其中每个节点都有一个父节点和零个或多个子节点。这种模型适用于表示具有明确定义的层次结构的数据，例如组织结构或文件系统。
网状结构	网状结构是一种以网状连接方式组织数据的模型。在网状结构中，每个数据元素可以与多个其他数据元素直接连接，形成一个复杂的网络。这种模型适用于表示具有复杂关系的数据，例如图形数据或网络拓扑。
关系模型	关系模型是一种使用表格和关系来组织数据的模型。在关系模型中，数据被组织成多个表格，每个表格包含多个行和列。表格之间通过关系建立连接，这些关系可以是一对一、一对多或多对多的关系。这种模型适用于表示结构化数据，并支持强大的查询和数据操作功能。

这些概念在数据库设计和数据管理中起着重要的作用，根据具体的需求和数据结构，选择适合的模型可以提高数据的组织和查询效率。

2.关系型数据库管理系统（RDBMS）

RDBMS relational database management system

常见的关系型数据库管理系统：

MySQL：MySQL，MariaDB，Percona-Server
PostgreSQL (pgsql)
Oracle
Microsoft SQL Server

SQL: Structure Query Language，结构化查询语言

约束：constraint，向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL），一个表只能存在一个。
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL），一个表可以存在多个。
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束
**索引：**将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

3.关系型数据库的常见组件

组件	作用
数据库：database	数据库是一个独立的数据存储单元，用于存储一组相关的数据表和其他对象。它提供了一个逻辑和物理上的隔离环境，使得不同的用户和应用程序可以安全地访问和管理各自的数据。
表：table	表格是关系型数据库的基本组成单位，用于存储数据。每个表格由一系列具有相同属性的列和多个行组成。列定义了每个数据项的类型，行包含了实际的数据。
索引：index	索引是为了提高数据检索效率而创建的数据结构。它可以基于一个或多个列的值创建，并按照特定的排序方式存储数据，以便快速定位和访问数据。
视图：view	视图是一个虚拟的表，它基于一个或多个实际表的查询结果而创建。视图可以过滤、变换和组合数据，并提供了一个简化和安全的访问接口。用户可以像访问表一样使用视图，而不必直接访问基础表。
用户：user	用户是数据库中的一个标识，用于识别和控制不同的数据库操作者。每个用户有自己的用户名和密码，可以被授予不同的权限和角色，以限制其对数据库的访问和操作。
权限：privilege	权限是指用户对数据库对象的操作权限。数据库管理系统可以将不同的权限授予用户，例如查询、插入、更新、删除等操作权限。通过权限管理，可以确保只有授权的用户能够操作和访问相应的数据。
存储过程：procedure	存储过程是一组预编译的数据库操作语句，存储在数据库中并具有一个名称。它可以像程序一样接受参数、执行复杂的数据库操作和逻辑，并返回结果。存储过程可以被重复使用，提高了数据库的性能和可维护性。
存储函数：function	存储函数类似于存储过程，但是它返回一个值，可以作为一个表达式的一部分使用。存储函数可以接受参数，执行一系列的数据库操作和计算，然后返回一个值。
触发器：trigger	触发器是与表相关联的一段数据库代码，它在特定的数据库操作（如插入、更新、删除）发生时自动触发执行。触发器可以用于执行额外的业务逻辑、维护数据一致性和实现复杂的数据约束。
事件调度器：event scheduler	事件调度器是一个数据库的任务调度器，可以自动执行一些计划性的工作。通过事件调度器，可以定期执行一些数据库维护、数据处理和清理等任务，提高数据库的自动化和效率。

4.SQL语句

DDL：Data Defination Language，数据定义语言
DML：Data Manipulation Language，数据操纵语言
DCL：Data Control Language，数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

二、CentOS安装与配置MySQL

编译安装
二进制格式的程序包
程序包管理器管理的程序包

这里使用二进制安装

//安装依赖包
[root@localhost ~]# yum -y install ncurses-devel openssl-devel openssl cmake
mariadb-devel

//创建用户与组
[root@localhost ~]# groupadd -r -g 306 mysql
[root@localhost ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql

//下载源码包
[root@localhost src]# wget
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-
x86_64.tar.gz
[root@localhost src]# tar -zxf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ls /usr/local/
apache apr-util etc include lib64 mysql-5.7.42-el7-x86_64 share
apr bin games lib libexec sbin src

//创建目录链接
[root@localhost local]# ln -sv mysql-5.7.42-el7-x86_64/ mysql
'mysql' -> 'mysql-5.7.42-el7-x86_64/'
[root@localhost local]# ls
apache apr-util etc include lib64 mysql sbin src
apr bin games lib libexec mysql-5.7.42-el7-x86_64 share
[root@localhost local]# ll
total 0
drwxr-xr-x. 13 root root 152 Aug 28 15:42 apache
drwxr-xr-x. 6 root root 58 Aug 28 15:23 apr
drwxr-xr-x. 5 root root 43 Aug 28 15:26 apr-util

//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 24 Aug 28 16:27 /usr/local/mysql -> mysql-5.7.42-el7-
x86_64/

//添加环境变量
[root@localhost ~]# ls /usr/local/mysql
bin docs include lib LICENSE man README share support-files
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >
/etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/s
bin:/usr/bin:/root/bin


//建立数据存放目录
[root@localhost mysql]# mkdir /opt/data
[root@localhost mysql]# chown -R mysql.mysql /opt/data/
[root@localhost mysql]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 28 16:42 data

//初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --
datadir=/opt/data/
2023-08-28T08:44:05.054802Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2023-08-28T08:44:05.295926Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-08-28T08:44:05.337107Z 0 [Warning] InnoDB: Creating foreign key constraint
system tables.
2023-08-28T08:44:05.394016Z 0 [Warning] No existing UUID has been found, so we
assume that this is the first time that this server has been started. Generating
a new UUID: 0baa5705-457f-11ee-97f3-000c29502aaf.
2023-08-28T08:44:05.394847Z 0 [Warning] Gtid table is not ready to be used.
Table 'mysql.gtid_executed' cannot be opened.
2023-08-28T08:44:05.666127Z 0 [Warning] A deprecated TLS version TLSv1 is
enabled. Please use TLSv1.2 or higher.
2023-08-28T08:44:05.666159Z 0 [Warning] A deprecated TLS version TLSv1.1 is
enabled. Please use TLSv1.2 or higher.
2023-08-28T08:44:05.666685Z 0 [Warning] CA certificate ca.pem is self signed.
2023-08-28T08:44:05.699672Z 1 [Note] A temporary password is generated for
root@localhost: 5iwkL<ksXSo.
//生成一个临时密码，此处密码是5iwkL<ksXSo.
//生成的密码是随机的


//配置mysql
[root@localhost ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
'/usr/local/include/mysql' -> '/usr/local/mysql/include/'
[root@localhost ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@localhost ~]# ldconfig

//生成配置文件
[root@localhost ~]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF


//链接依赖和库
[root@localhost bin]# ln -s /usr/lib64/libncurses.so.6
/usr/lib64/libncurses.so.5
[root@localhost bin]# ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5
mysql启动时报错：error while loading shared libraries: libncurses.so.5: cannot open shared object file时做此操作
报错原因是因为在启动mysql的时候加载了libtinfo.so.5库文件，将对应的高版本创建一个低版本的软连接。


//配置服务启动脚本
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server
/etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g'
/etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld



//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ps -ef|grep mysql
root 69233 1 0 16:56 pts/0 00:00:00 /bin/sh
/usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pidfile=/opt/data/mysql.pid
mysql 69421 69233 2 16:56 pts/0 00:00:00 /usr/local/mysql/bin/mysqld
--basedir=/usr/local/mysql --datadir=/opt/data --plugindir=/usr/local/mysql/lib/plugin --user=mysql --logerror=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --
socket=/tmp/mysql.sock --port=3306
root 69488 1198 0 16:56 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    Process     
LISTEN     0          128                  0.0.0.0:22                0.0.0.0:*                   
LISTEN     0          80                         *:3306                    *:*                   
LISTEN     0          128                     [::]:22                   [::]:*


//修改密码
[root@localhost bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.42
Copyright (c) 2000, 2023, 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> set password = password('yourpassword');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

三、使用MySQL

客户端：

mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
mysqldump：mysql备份工具
mysqladmin

服务器端：

mysqld

1.mysql工具使用

语法：mysql [OPTIONS] [database]

//常用的OPTIONS： 
    -uUSERNAME       //指定用户名，默认为root
    -hHOST           //指定mysql服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD       //指定用户的密码
    -P (大写)		//指定数据库监听的端口，如-P3307
    -V (大写)         //查看当前使用的mysql版本
    -e			 //不登录mysql执行sql语句后退出，常用于脚本。

示例

[root@localhost ~]# mysql -uroot -payachinene
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> exit
Bye

查看mysql版本

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.42, for el7 (x86_64) using  EditLine wrapper

使用-e选项执行语句

[root@localhost ~]# mysql -uroot -payachinene -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2.服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信 server地址只能是：localhost，127.0.0.1

四、操作MySQL数据库

1.DDL操作

CREATE：创建
DROP：删除
ALTER：修改

数据库操作

创建数据库

根据项目需求创建一个存储数据的仓库

使用create database 数据库名字创建
- 数据库层面可以指定字符集：charset / character set
- 数据库层面可以指定校对集：collate
创建数据库会在磁盘指定存放处产生一个文件夹
创建语法

create database 数据库名字 [数据库选项];

示例

1、创建一个指定名字的数据库

mysql> create database mysqld;
Query OK, 1 row affected (0.00 sec)

2、创建一个指定字符集的数据库

mysql> create database db_2 charset utf8;
Query OK, 1 row affected (0.00 sec)

显示数据库

1、显示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_2               |
| mysql              |
| mysqld             |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

2、显示数据库创建指令

mysql> show create database db_2;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| db_2     | CREATE DATABASE `db_2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库

用法

alter database 数据库名字 库选项

示例

修改数据库字符集

mysql> alter database db_2 charset gbk;
Query OK, 1 row affected (0.00 sec)

删除数据库

用法

drop database 数据库名字;
mysql> drop database db_2;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqld             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

表操作

创建数据表
显示数据表
查看表结构
更改数据表
更改字段
删除数据表

创建表

表创建语法

create table [数据库名.]表名(
	字段名 字段类型,
	...
    字段名 字段类型
)[表选项];

示例

1.创建数据表

mysql> create table mndyy(
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

2.创建多字段表

mysql> create table nene(
    -> name varchar(20),
    -> age varchar(20),
    -> id int
    -> );
Query OK, 0 rows affected (0.00 sec)

显示表

1、显示所有数据表(当前数据库下)

show tables;

2、显示所有数据表(指定数据库)

mysql> show tables from mysqld;
+------------------+
| Tables_in_mysqld |
+------------------+
| mndyy            |
| nene             |
+------------------+
2 rows in set (0.00 sec)

3、显示数据表的创建指令

mysql> show create table mysqld.mndyy;
+-------+-------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                    |
+-------+-------------------------------------------------------------------------------------------------+
| mndyy | CREATE TABLE `mndyy` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表结构

通常是查看字段信息
详细的显示字段的各项信息
查看语法有三种（效果一样）

desc 表名；
describe 表名;
show columns from 表名;

示例

mysql> desc mysqld.mndyy;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

更改表选项

修改表名：rename table 表名 to 新表名
修改表选项：alter table 表名

示例

1.改表名

mysql> rename table mysqld.nene to ayachinene;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables from mysqld;
+------------------+
| Tables_in_mysqld |
+------------------+
| ayachinene       |
| mndyy            |
+------------------+
2 rows in set (0.00 sec)

2、修改表选项

mysql> alter table mysqld.mndyy charset utf8;

修改字段

语法

alter table 表名 modify 字段名 字段类型 [字段属性] [位置]；

修改age字段类型为char(18),并放在id字段后面

mysql> desc ayachinene;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | varchar(20) | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table ayachinene modify age char(18) after id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ayachinene;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| age   | char(18)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除字段

语法

alter table 表名 drop 字段名;

示例

mysql> alter table ayachinene drop name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ayachinene;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| age   | char(18) | YES  |     | NULL    |       |
+-------+----------+----

删除表

语法

drop table 表名；

示例

mysql> drop table mndyy;
mysql> show tables from mysqld;
+------------------+
| Tables_in_mysqld |
+------------------+
| ayachinene       |
+------------------+
1 row in set (0.00 sec)

SQL数据操作

新增数据
查看数据
更新数据
删除数据

新增数据

全字段插入：insert into 表名 values(字段列表顺序对应的所有值);
部分字段插入：insert into 表名 (字段列表) values(字段列表对应的值顺序列表);

示例

1.全字段插入

mysql> insert into ayachinene values(1,'f');
Query OK, 1 row affected (0.01 sec)

2.部分字段插入

mysql> insert into ayachinene(age) values('m');
Query OK, 1 row affected (0.00 sec)

查看数据

语法

select *|字段列表 from 表名;

示例

1.查看表里所有数据

mysql> select * from ayachinene;
+------+------+
| id   | age  |
+------+------+
|    1 | f    |
|    2 | m    |
| NULL | m    |
+------+------+
3 rows in set (0.00 sec)

2.查看表里某个字段的数据

mysql> select age from ayachinene;
+------+
| age  |
+------+
| f    |
| m    |
| m    |
+------+
3 rows in set (0.00 sec)

3、查看表中id值为1的信息且只显示age字段

mysql> select age from ayachinene where id = 1;
+------+
| age  |
+------+
| f    |
+------+
1 row in set (0.00 sec)

更新数据

语法

update 表名 set 字段 = 新值[,字段 = 新值] [where条件筛选];

更新最后一个人的年龄为22岁

mysql> select * from xin;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | lc     |   21 |
|  2 | yys    |   18 |
|  3 | lxy    |   21 |
|  4 | kazisa | NULL |
+----+--------+------+
4 rows in set (0.01 sec)

mysql> update xin set age = 22 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from xin;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | lc     |   21 |
|  2 | yys    |   18 |
|  3 | lxy    |   21 |
|  4 | kazisa |   22 |
+----+--------+------+
4 rows in set (0.00 sec)

删除数据

语法

delete from 表名 [where条件];

删除表中id为2的数据

mysql> delete from xin where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from xin;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | lc     |   21 |
|  3 | lxy    |   21 |
|  4 | kazisa |   22 |
+----+--------+------+
3 rows in set (0.00 sec)