mysql数据库基础
mysql数据库基础
目录
常见的数据库有哪些？
关系型数据库：
mysql，mariadb，MSsql，oracle，sqlserver
数据一般存放在硬盘
字段：每一列的第一行就是字段
记录：每一行代表一条记录
非关系型数据库nosql：
mongodb，redis，memcache
数据存放在内存当中，以变量形式存放 key=value
sqlite ：数据存放在文件当中的，数据文件只能用sql语句去读取
...
关系型数据库介绍
1.数据库结构模型
数据库结构模型主要有：
- 层次模型
- 网状结构
- 关系模型
关系模型：
二维关系：row，column
数据库管理系统：DBMS (DataBase Managment System)
关系：Relational，RDBMS(关系型数据库管理系统)
2.RDBMS专业名词
常见的关系型数据库管理系统：
- MySQL：MySQL，MariaDB，Percona-Server
- PostgreSQL：简称为pgsql
- Oracle
- MSSQL
SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制
-
主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
- 一个表只能存在一个
-
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
- 一个表可以存在多个
-
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
-
检查性约束
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
3.关系型数据库的常见组件
关系型数据库的常见组件有：
- 数据库：database
- 表：table，由行（row）和列（column）组成
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler
4.SQL语句
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：移除授权 |
mysql安装与配置
mysql安装
mysql安装方式有三种：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
- 程序包管理器管理的程序包：
- rpm：有两种
- OS Vendor：操作系统发行商提供的
- 项目官方提供的
- deb
- rpm：有两种
第一种方法：yum安装mysql
//下载mysql的yum源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# ls
anaconda-ks.cfg mysql57-community-release-el7-11.noarch.rpm
//更新mysql的yum源
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# ls /etc/yum.repos.d/
...省略
mysql-community-source.repo
mysql-community.repo
//禁用mysql，默认推荐8.0版本，现在我们要安装5.7版本，所以先禁用一下。
[root@localhost ~]# yum module disable mysql
//安装mysql5.7
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
//为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@localhost ~]# rpm -e mysql57-community-release
第二种方法：yum安装mariadb
//安装mariadb
[root@localhost ~]# dnf install -y mariadb*
//开机自启动
[root@136 ~]# systemctl enable --now mariadb
//mariadb默认没有密码，直接输入mysql进入
[root@136 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password =password('123456'); //修改密码
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit //退出验证密码是否修改成功
Bye
[root@136 ~]#
[root@136 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
mysql配置
//启动mysql并设置开机自启动
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; ven>
Active: active (running) since Mon 2022-07-25 14:37:45 CST; 17s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 66751 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var>
Process: 66586 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited,>
Main PID: 66753 (mysqld)
Tasks: 27 (limit: 23457)
Memory: 303.7M
CGroup: /system.slice/mysqld.service
└─66753 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mys>
Jul 25 14:37:43 localhost.localdomain systemd[1]: Starting MySQL Server>
Jul 25 14:37:45 localhost.localdomain systemd[1]: Started MySQL Server.
lines 1-15/15 (END)
//确保3306端口已经监听起来
[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 ~]# grep "password" /var/log/mysqld.log
2022-07-25T06:55:44.273550Z 1 [Note] A temporary password is generated for root@localhost: MXX.QJq-t7op
//使用获取到的临时密码登录mysql
[root@localhost ~]# mysql -uroot -pMXX.QJq-t7op
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 2
Server version: 5.7.38
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> //看到有这样的标识符则表示成功登录了
**//修改mysql登录密码**
//设置密码安全强度为0，0比较低的安全强度
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
//定义数据库里的算法的长度为1，不是我们理解的一个字符
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
//修改密码
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//退出后试试用新密码登录。
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456
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.38 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> //登录成功
//另一种改密码方法
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxx';
mysql的程序组成
- 客户端
- mysql：CLI交互式客户端程序(纯命令行交互界面)
- mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
- mysqldump：mysql备份工具
- mysqladmin
- 服务器端
- mysqld
[root@localhost ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: //输入数据库的root密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y //是否更改root的密码？
New password:
Re-enter new password:
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y //是否要继续使用提供的密码？
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y //删除匿名用户？
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n //禁止远程root登录？
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y //删除测试数据库并访问它？
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y //是否立即重新加载权限表？
Success.
All done!
[root@localhost ~]#
mysql工具使用
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost(127.0.0.1)，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1
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 16
Server version: 5.7.38 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>
//指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307，默认3306
[root@localhost ~]# mysql -uroot -p123456 -P3306
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 17
Server version: 5.7.38 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>
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapper
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
服务器监听的两种socket地址
| socket类型 | 说明 |
|---|---|
| ip socket | 默认监听在tcp的3306端口，支持远程通信 |
| unix sock | 监听在sock文件上(/tmp/mysql.sock，/var/lib/mysql/mysql.sock) 仅支持本地通信 server地址只能是：localhost，127.0.0.1 |
//socket登录
[root@localhost ~]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock
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 20
Server version: 5.7.38 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>
mysql数据库操作
DDL操作
1.数据库操作
//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
[IF NOT EXISTS]
用于判断数据库存不存在，若存在就退出，若不存在就创建
平常可以不加，若数据库太多了，可以使用这个判断
//查看当前实例有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//创建数据库xiaodada
mysql> create database if not exists xiaodada;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xiaodada |
+--------------------+
5 rows in set (0.00 sec)
//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
[IF EXISTS]
若不存在就退出，若存在就删除
mysql> drop database xiaodada;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.表操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
mysql> create database if not exists xiaodada; //创建数据库xiaodada
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xiaodada |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xiaodada; //进入数据库
Database changed
mysql> create table linux (id int not null,name varchar(10),age tinyint(3)); //创建linux表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------------+
| Tables_in_xiaodada |
+--------------------+
| linux |
+--------------------+
1 row in set (0.00 sec)
mysql> desc linux; //查看表的结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
mysql> drop table linux;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql数据类型
MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。
下面的表显示了需要的每个整数类型的存储和范围
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
3.用户操作
mysql用户帐号由两部分组成，如'USERNAME'@'HOST'
表示此USERNAME只能从此HOST上远程登录
这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
- IP地址，如：172.16.12.129
- 通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建tom用户
mysql> create user 'tom'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user\G //查看详细用户表
mysql> exit
Bye
[root@localhost ~]# mysql -utom -p123456
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 22
Server version: 5.7.38 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>
//删除数据库用户
//语法：DROP USER 'username'@'host';
mysql> drop user 'tom'@'localhost';
Query OK, 0 rows affected (0.02 sec)
4.查看命令show
mysql> show character set; //查看支持的所有字符集
mysql> show engines; //查看当前数据库支持的所有存储引擎
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xiaodada |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from xiaodada; //不进入某数据库而列出其包含的所有表
+--------------------+
| Tables_in_xiaodada |
+--------------------+
| linux |
+--------------------+
1 row in set (0.00 sec)
//查看表结构
//语法：DESC [db_name.]table_name;
mysql> desc xiaodada.linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
//查看某表的创建命令
//语法：SHOW CREATE TABLE table_name;
mysql> show create table xiaodada.linux;
+-------+--------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------+
| linux | CREATE TABLE `linux` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------+
1 row in set (0.00 sec)
//查看某表的状态
//语法：SHOW TABLE STATUS LIKE 'table_name'\G
mysql> use xiaodada;
Database changed
mysql> show table status like 'linux'\G
*************************** 1. row ***************************
Name: linux
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-07-25 16:18:01
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
5.获取帮助
//获取命令使用帮助
//语法：HELP keyword;
