MySQL数据库操作-EW帮帮网

一. MySQL 数据库介绍

在熟悉安装及访问 MySQL 数据库以后，接下来将学习使用 MySQL 数据库的基本操作，这也是在服务器运维工作中不可或缺的知识。本节中的所有数据库语句均在“mysq1>”操作环境中执行，并且每一条操作语句都是以分号(;)结束的
数据库目前标准的指令集是 SQL。SQL是 Structured Query Language 的缩写，即结构化查询语言。它是1974年由 Boyce 和Chamberlin 提出来的，1975~1979 年 IBM 公司研制的关系数据库管理系统原型 System R 实现了这种语言。经过多年的发展，SL语言得到了广泛的应用。
SQL 语言主要由以下几部分组成。
DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP。
DML(Data Manipulation Language，数据操纵语言):用来插入、删除和修改数据库中的数据，如 INSERT、UPDATE、DELETE。
DQL(Data Query Language数据查询语言):用来查询数据库中的数据,如 SELECT.
DCL(Data Control Language,数据控制语言):用来控制数据库组件的存取许可、存取权限等，如COMMIT、FROLLBACK、GRANT、REVOKE.

二. MySQL 库操作

1.系统数据库

MySQL 自带了几个系统数据库，这些数据库的功能分别如下：

information_schema：用于存储数据库的元数据，像表结构、列信息等都包含在内。
mysql：存储着 MySQL 服务器的用户权限、配置信息等。
performance_schema：主要负责收集数据库服务器的性能数据。
sys：借助视图的方式，简化了对 performance_schema 的访问。

2.数据库操作

2.1创建数据库

CREATE DATABASE IF NOT EXISTS db_name;

这里的IF NOT EXISTS是一个可选参数，其作用是避免因重复创建数据库而产生错误

2.2数据库命名规则

命名可以使用字母、数字、下划线以及美元符号。
数据库名不能和已有的数据库名重复，并且要避开 MySQL 的保留字。
命名长度不能超过 64 个字符。
数据库名在某些操作系统中是区分大小写的。

2.3选择数据库

USE db_name;

执行这条语句后，后续的操作都会在指定的数据库中进行。

2.4查看数据库

SHOW DATABASES; -- 查看所有数据库
SHOW CREATE DATABASE db_name; -- 查看数据库的创建语句

2.5删除数据库

DROP DATABASE IF EXISTS db_name;

使用这条语句要格外谨慎，因为它会将数据库中的所有数据都删除。

三.MySQL表操作

1.表介绍

表是数据库中用于存储数据的基本单位，它由行和列组成。每一列代表一个字段，并且每个字段都有其特定的数据类型；每一行则代表一条记录。

2.查看表

SHOW TABLES; -- 查看当前数据库中的所有表
SHOW TABLES IN db_name; -- 查看指定数据库中的所有表

3.创建表

3.1语法

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
    PRIMARY KEY (pk_column)
);

3.2类型介绍

数字类型

整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
浮点类型：FLOAT、DOUBLE
定点类型：DECIMAL

日期类型

DATE（格式：YYYY - MM - DD）
TIME（格式：HH:MM:SS）
DATETIME（格式：YYYY - MM - DD HH:MM:SS）
TIMESTAMP（自动存储记录的修改时间）
YEAR

字符串类型

CHAR（固定长度字符串）
VARCHAR（可变长度字符串）
TEXT（用于存储大文本数据）
BLOB（用于存储二进制数据）

3.3约束条件

NOT NULL：字段值不能为空。
UNIQUE：字段值在表中必须是唯一的。
PRIMARY KEY：主键约束，用于唯一标识表中的每一行，相当于 NOT NULL 和 UNIQUE 的组合。
FOREIGN KEY：外键约束，用于建立表与表之间的关联。
DEFAULT：为字段设置默认值。
CHECK：确保字段值满足特定的条件（MySQL 8.0.16 及以上版本支持）。

3.4创建表示例

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.查看表结构

DESCRIBE table_name; -- 查看表的基本结构
SHOW CREATE TABLE table_name; -- 查看表的创建语句

5.修改表

5.1修改表名

RENAME TABLE old_name TO new_name;

5.2增加字段

ALTER TABLE table_name ADD COLUMN new_column datatype constraint;

5.3删除字段

ALTER TABLE table_name DROP COLUMN column_name;

5.4修改字段

-- 修改字段类型
ALTER TABLE table_name MODIFY column_name new_datatype;

-- 修改字段名和类型
ALTER TABLE table_name CHANGE old_column new_column new_datatype;

6.复制表

-- 只复制表结构
CREATE TABLE new_table LIKE old_table;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM old_table;

7.删除表

DROP TABLE IF EXISTS table_name;

四.MySQL 数据操作

1.介绍

数据操作主要包含增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）这四种基本操作，也就是常说的 CRUD 操作。

2.插入数据INSERT

INSERT INTO table_name VALUES (value1, value2, ...);

顺序插入

INSERT INTO table_name VALUES (value1, value2, ...);

指定字段插入数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

插入多条记录

INSERT INTO table_name (column1, column2) 
VALUES (value1_1, value1_2), (value2_1, value2_2), ...;

3.删除数据INSERT

DELETE FROM table_name WHERE condition;

4.更新数据INSERT

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

5.查询数据INSERT

1.单表查询

1.1单表查询语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT offset, count];

1.2关键字执行的优先级

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

1.3准备数据

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock INT
);

INSERT INTO products VALUES
(1, 'iPhone', 'Electronics', 999.99, 100),
(2, 'iPad', 'Electronics', 599.99, 50),
(3, 'Book', 'Books', 29.99, 200),
(4, 'Laptop', 'Electronics', 1499.99, 30);

1.4简单查询

SELECT * FROM products; -- 查询所有字段
SELECT name, price FROM products; -- 查询指定字段
SELECT DISTINCT category FROM products; -- 查询不同的值

1.5where条件

SELECT * FROM products WHERE price > 500;
SELECT * FROM products WHERE category = 'Electronics' AND stock > 50;
SELECT * FROM products WHERE category IN ('Electronics', 'Books');
SELECT * FROM products WHERE name LIKE '%Phone%';

1.6group by 分组

（1）什么是分组？为什么要分组？

分组是将数据按照指定的字段进行分类，其目的是为了对每一组的数据进行聚合计算。

（2）聚合函数：count（）avg() max() min() sun()

SELECT category, COUNT(*) AS count, AVG(price) AS avg_price 
FROM products 
GROUP BY category;

1.7having 过滤

SELECT category, COUNT(*) 
FROM products 
GROUP BY category 
HAVING COUNT(*) > 1;

1.8order by 排序

SELECT * FROM products ORDER BY price DESC; -- 降序排列
SELECT * FROM products ORDER BY category ASC, price DESC; -- 多字段排序

1.9limit 限制结果条目

SELECT * FROM products LIMIT 2; -- 取前两条记录
SELECT * FROM products LIMIT 1, 2; -- 从第2条记录开始，取2条记录

1.10正则匹配

SELECT * FROM products WHERE name REGEXP '^i'; -- 以i开头

2.多表查询

2.1子查询

SELECT * FROM products 
WHERE category = (SELECT category FROM products WHERE id = 1);

2.2多表连接查询

假设有两个表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO orders VALUES (101, 1, 2), (102, 3, 5);

1.内连接

SELECT orders.order_id, products.name, orders.quantity
FROM orders
INNER JOIN products ON orders.product_id = products.id;

2.左连接

SELECT products.name, orders.order_id
FROM products
LEFT JOIN orders ON products.id = orders.product_id;

3.右查询

SELECT products.name, orders.order_id
FROM products
RIGHT JOIN orders ON products.id = orders.product_id;

五。MySQL数据用户授权

1.创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'

2.授权操作

GRANT privilege_type ON database.table TO 'username'@'host';

-- 示例：授予用户对所有数据库的所有表的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 示例：授予用户对test数据库中users表的SELECT和INSERT权限
GRANT SELECT, INSERT ON test.users TO 'user'@'%';

3.查看权限

SHOW GRANTS FOR 'username'@'host';

4.撤销权限

REVOKE privilege_type ON database.table FROM 'username'@'host';

使用完毕后，记得刷新权限：

FLUSH PRIVILEGES;

MySQL数据库操作