MySQL

lzhdlw

已于 2024-04-06 21:40:02 修改

阅读量517

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-10-05 16:47:20 首次发布

本文链接：https://blog.csdn.net/lzhdlw/article/details/127099950

版权

这篇博客详细介绍了MySQL数据库的基本概念、安装步骤、服务管理及安全策略。重点讲解了SQL语句，包括DDL（数据库和表的操作）、DML（数据的增删改）、DQL（数据查询）和DCL（账户与权限管理），并涵盖了各种数据类型、查询方式、函数应用及约束定义。此外，还详细阐述了SQL执行顺序和常用函数，如聚合函数、日期函数等。

摘要由CSDN通过智能技术生成

基本概念

数据库：存储数据的仓库。DataBase，简称DB
数据库管理系统：操纵和管理数据库的软件。DataBase Management System，简称DBMS
SQL：操作关系型数据库的语言。Structured Query Language，简称SQL

数据库管理系统操作数据库，数据库管理系统对外暴露一些接口，用SQL语言操作这些接口。
即：SQL操作数据库管理系统，数据库管理系统操作数据库。
在这里插入图片描述

安装mysql

环境：ubuntu20.04

安装mysql

# 同步软件包版本
root@ubuntu:~# apt-get update
# 查看软件源提供的mysql-server版本
root@ubuntu:~# apt-cache madison mysql-server
mysql-server | 8.0.30-0ubuntu0.20.04.2 | http://mirrors.aliyun.com/ubuntu focal-updates/main amd64 Packages
mysql-server | 8.0.30-0ubuntu0.20.04.2 | http://mirrors.aliyun.com/ubuntu focal-security/main amd64 Packages
mysql-server | 8.0.19-0ubuntu5 | http://mirrors.aliyun.com/ubuntu focal/main amd64 Packages
# mysql-client版本
root@ubuntu:~# apt-cache madison mysql-client
mysql-client | 8.0.30-0ubuntu0.20.04.2 | http://mirrors.aliyun.com/ubuntu focal-updates/main amd64 Packages
mysql-client | 8.0.30-0ubuntu0.20.04.2 | http://mirrors.aliyun.com/ubuntu focal-security/main amd64 Packages
mysql-client | 8.0.19-0ubuntu5 | http://mirrors.aliyun.com/ubuntu focal/main amd64 Packages
# 安装mysql-server
apt-get -y install mysql-server
# 安装mysql-client
apt-get -y install mysql-client
# 查看版本
root@ubuntu:~# mysql --version
mysql  Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

查看服务状态

安装完成后，mysql-server会自动运行。查看mysql-server服务当前状态

root@ubuntu:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2022-09-29 12:59:45 UTC; 17min ago
   Main PID: 4834 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4575)
     Memory: 361.7M
     CGroup: /system.slice/mysql.service
             └─4834 /usr/sbin/mysqld

启/停服务

# 启动
root@ubuntu:~# systemctl start mysql
# 停止
root@ubuntu:~# systemctl stop mysql.service
# 停止之后查看服务状态
root@ubuntu:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Thu 2022-09-29 13:31:41 UTC; 5s ago
    Process: 7047 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, st>
    Process: 7075 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 7075 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
# 重启
root@ubuntu:~# systemctl restart mysql.service

安全策略

root@ubuntu:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0    # 根据实际情况选择，此处选0
Please set the password for root here.

New password:

Re-enter new password:

# 报错
Estimated strength of the password: 0
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
# 使用ALTER USER命令更改密码：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';
root@ubuntu:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu)

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> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

# 重新配置安全策略，所有选项都选y即可
root@ubuntu:~# mysql_secure_installation

连接数据库

root@ubuntu:~# mysql -h localhost -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu)

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>

SQL语句

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库、表、字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来管理数据库用户、控制数据库的访问权限

DDL

Data Definition Language，数据定义语言，用来定义数据库对象（数据库、表、字段）

数据库操作

查询

查询所有数据库： SHOW DATABASES;
查询当前数据库：SELECT DATABASE();

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

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

创建

CREATE DATABASE [IF NOT EXISTS] <数据库名> [DEFAULT CHARSET 字符集] [COLLATE 排序规则] ;

# 创建数据库test
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 创建已存在的数据库
mysql> CREATE DATABASE test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.00 sec)

使用

USE <数据库名>;

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

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

删除

DROP DATABASE [IF EXISTS] <数据库名>;

mysql> DROP DATEBASE test;
ERROR 1064 (42000): 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 'DATEBASE test' at line 1
mysql> DROP DATABASE test;
Query OK, 0 rows affected (