MySQL创建数据库、表、以及增删改查数据

目录

1 MySQL基本概念

2 建库

3 建表

4 增删改查

5 复杂查询

1 MySQL基本概念

MySQL是一种开源的关系型数据库管理系统（RDBMS），它是目前最流行的数据库之一。下面是MySQL数据库的一些基本概念：

1. 数据库（Database）：数据库是一个用于存储和组织数据的容器。它由一组表（Tables）和其他相关对象组成。
2. 表（Table）：表是MySQL数据库中的主要对象，用于存储数据。它由一组行（Rows）和列（Columns）组成。每个表都有一个唯一的名称，并且包含一个定义了各列名称、数据类型和约束的结构。
3. 列（Column）：列是表中的一个数据字段，用于存储特定类型的数据。每个列都有一个名称和数据类型，例如整数、字符、日期等。
4. 行（Row）：行是表中的一个记录，它包含了表中每个列的具体值。每行都有一个唯一的标识符，称为主键（Primary Key），用于唯一标识该行。
5. 主键（Primary Key）：主键是表中用于唯一标识每行的列或列的组合。它确保了表中的每个行都有唯一的标识符。
6. 外键（Foreign Key）：外键是一个表中的列，它与另一个表的主键相关联。通过外键，可以在多个表之间建立关联，实现数据的引用完整性和一致性。
7. 索引（Index）：索引是一种数据结构，用于提高查询效率。它可以根据一个或多个列的值快速定位到表中的特定行。
8. 查询（Query）：查询是从数据库中检索数据的操作。使用结构化查询语言（SQL），可以编写查询语句来执行各种操作，如插入、更新、删除和查询数据。
9. 视图（View）：视图是基于一个或多个表的查询结果构建的虚拟表。它可以简化复杂的查询操作，并提供了一个更简洁、易读的数据访问接口。
10. 触发器（Trigger）：触发器是与表相关联的特殊类型的存储过程。它在特定的数据库操作（如插入、更新、删除）前后自动执行一系列定义的操作。
11. 函数（Function）：函数是一段可重用的代码块，接受输入参数并返回一个值。MySQL提供了许多内置函数，用于处理和操作数据，例如数学函数（如SUM、AVG、MAX、MIN）、字符串函数（如CONCAT、SUBSTRING、UPPER）和日期函数（如NOW、DATE_FORMAT）等。
12. 存储过程（Stored Procedure）：存储过程是一组预编译的SQL语句和控制结构，作为一个单元存储在数据库中。存储过程可以接受输入参数，并可以执行复杂的逻辑和数据操作。它们提供了一种将常用操作封装起来并在需要时进行调用的方法，可以提高性能和代码重用性。

2 建库

在MySQL中，创建数据库的语法使用CREATE DATABASE语句。以下是创建数据库的基本语法：

CREATE DATABASE database_name;

其中，database_name是要创建的数据库的名称。请注意，数据库名称通常要遵循命名规范，如只使用字母、数字和下划线，且以字母开头。

除了基本语法之外，还可以使用一些可选参数来自定义数据库的属性。以下是创建数据库时可用的一些常见选项：

• 字符集（Character Set）：指定数据库中存储的字符编码方式，默认为服务器的默认字符集。常见的字符集包括UTF8、UTF8mb4等。

CREATE DATABASE database_name CHARACTER SET utf8;

• 排序规则（Collation）：指定字符排序规则，用于比较和排序字符串。默认为字符集的默认排序规则。

CREATE DATABASE database_name COLLATE utf8_general_ci;

• 数据库选项（Database Options）：其他可选的数据库选项，如设置访问权限、存储引擎等。

CREATE DATABASE database_name
  CHARACTER SET utf8
  COLLATE utf8_general_ci
  /* other options */;

创建数据库后，可以使用USE语句切换到新创建的数据库，开始在其中创建表和插入数据：

USE database_name;

当数据库不再使用时，可以使用DROP DATABASSE语句删除数据库，此操作需慎重：

DROP DATABASE database_name;

3 建表

在MySQL中，创建表的语法使用CREATE TABLE语句。以下是创建表的基本语法：

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

其中，table_name是要创建的表的名称。括号内的部分定义了表的列以及每个列的数据类型。

在列定义中，需要指定列名和数据类型。数据类型定义了该列可以存储的数据类型，如整数、字符、日期等。常见的数据类型包括：

• 整数类型（Integer Types）：如INT、BIGINT、TINYINT等。
• 字符串类型（String Types）：如VARCHAR、CHAR、TEXT等。
• 小数类型（Decimal Types）：如DECIMAL、FLOAT、DOUBLE等。
• 日期和时间类型（Date and Time Types）：如DATE、DATETIME、TIMESTAMP等。

以下是一个简单的示例，演示如何创建一个包含几个常见列的表：

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
);

show create table employees;

上述示例创建了一个名为employees的表，包含了id、name、department、salary和hire_date这几个列。

在定义列时，还可以添加其他约束条件，如主键约束、唯一约束、非空约束等。例如，以下示例在创建表时指定了id列作为主键：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
);

除了列定义外，还可以在CREATE TABLE语句中指定其他选项，如存储引擎、字符集、排序规则等。这些选项可以根据具体需求进行自定义设置。

当数据表不再使用时，可以使用DROP TABLE语句删除数据表：

DROP TABLE table_name;

4 增删改查

基于上述示例的建表语句，以下是MySQL中常用的增删改查（CRUD）语法示例：

1.插入数据（Insert）：

插入一行数据到employees表中：

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES 
  (1, 'John Doe', 'Sales', 5000.00, '2022-01-01'),
  (2, 'Jane Smith', 'Marketing', 6000.00, '2022-02-01'),
  (3, 'Mike Johnson', 'IT', 5500.00, '2022-03-01'),
  (4, 'Sarah Davis', 'Sales', 4500.00, '2022-04-01'),
  (5, 'Tom Wilson', 'IT', 5200.00, '2022-05-01');

2.查询数据（Select）：

查询所有员工的信息：

SELECT * FROM employees;

查询特定部门的员工信息：

SELECT * FROM employees WHERE department = 'Sales';

3.更新数据（Update）：

更新员工的薪资：

UPDATE employees SET salary = 5500.00 WHERE id = 1;

4.删除数据（Delete）：

删除特定员工的记录：

DELETE FROM employees WHERE id = 1;

删除全部记录：

DELETE FROM employees ;

或

TRUNCATE TABLE employees;

5 复杂查询

为了提供更复杂的查询示例，需要扩展示例数据库，添加一个新的表departments来演示聚合函数、排序、分组以及不同类型的连接（inner join、left join、right join）的使用。

以下是扩展的建表语句：

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  location VARCHAR(100),
  manager VARCHAR(50)
);

插入示例数据：

INSERT INTO departments (id, name, location, manager)
VALUES
  (1, 'Sales', 'New York', 'John Smith'),
  (2, 'Marketing', 'London', 'Jane Doe'),
  (3, 'IT', 'San Francisco', 'Mike Johnson');

下面是一些复杂查询的示例：

1.使用聚合函数（Aggregate Functions）：

• 计算员工表中的平均薪资：

SELECT AVG(salary) AS average_salary FROM employees;

2.排序（Sorting）：

• 按照薪资从高到低对员工表进行排序：

SELECT * FROM employees ORDER BY salary DESC;

注：ASC是升序。

3.分组（Grouping）：

• 按照部门对员工表进行分组，并计算每个部门的平均薪资：

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

4.内连接（Inner Join）：

• 使用内连接获取员工表和部门表中相关联的数据：

SELECT e.*,d.*
FROM employees e
INNER JOIN departments d ON e.department = d.name;

5.左连接（Left Join）：

• 使用左连接获取员工表中的所有记录和关联的部门信息（如果有）：

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (6, 'Alex Johnson', 5, 4800, '2022-06-01');


SELECT e.*,d.*
FROM employees e
LEFT JOIN departments d ON e.department = d.name;

6.右连接（Right Join）：

• 使用右连接获取部门表中的所有记录和关联的员工信息（如果有）：

INSERT INTO departments (id, name, location, manager)
VALUES (4, 'Finance', 'Chicago', 'David Brown');

SELECT e.*,d.*
FROM employees e
RIGHT JOIN departments d ON e.department = d.name;