MySql基础常用集合-2
一、创建数据库
1、命名规则
数据库名称可以包含字母、数字、下划线和短划线，并且必须以字母开头。通常建议使用小写字母来命名数据库，以避免在不同操作系统上的大小写问题。
2、字符集
字符集决定了数据库中可以存储的字符类型。常见的字符集包括 UTF-8、GBK、Latin1 等。UTF-8 是一种通用的字符集，支持多种语言和特殊字符。在创建数据库时，您可以指定要使用的字符集。
常用的一些字符集有：
UTF-8：最常用的字符集之一，支持多种语言和特殊字符，适合国际化应用程序。
UTF-16：与 UTF-8 类似，但使用更多的字节表示字符，适用于需要处理较大字符集的场景。
GBK：广泛用于简体中文的字符集，支持简体中文中的常用字符。
Latin1：又称为 ISO 8859-1，主要用于欧洲语言，支持英文、法文、德文等。
3、排序规则
排序规则用于定义在排序和比较文本时要遵循的规则。不同的排序规则可能会影响字符串的排序顺序。在创建数据库时，您可以指定要使用的排序规则。
常用的一些排序规则有：
utf8_general_ci：UTF-8 字符集的通用排序规则，不区分大小写。
utf8_unicode_ci：UTF-8 字符集的 Unicode 排序规则，更加严格，支持更多的语言和特殊字符。
gbk_chinese_ci：GBK 字符集的中文排序规则，用于简体中文文本的排序和比较。
latin1_swedish_ci：Latin1 字符集的瑞典语排序规则，适用于欧洲语言的排序和比较。
4、示例：
假设我们要创建一个名为 my_database 的数据库，使用 UTF-8 字符集，排序规则为 utf8_general_ci，可以使用以下命令：
CREATE DATABASE my_database
CHARACTER SET utf8
COLLATE utf8_general_ci;
在这个示例中，指定了数据库的名称为 my_database，字符集为 utf8，排序规则为 utf8_general_ci。这样就创建了一个新的数据库，可以在其中存储支持 UTF-8 编码的数据，并使用通用的排序规则进行排序和比较。
创建数据库时，除了指定数据库的名称之外，还可以根据需要指定字符集和排序规则。选择适当的字符集和排序规则可以确保数据库在存储和处理数据时能够正常工作，并且适应多种语言和文本类型。
二、创建数据表
创建数据表是 MySQL 中的一个重要操作，它定义了数据库中存储数据的结构。在创建数据表时，需要指定表的名称、各个字段的名称、数据类型以及其他属性。下面是详细介绍如何创建数据表以及常见字段类型和属性的解释：
1、基本语法
要创建一个数据表，可以使用 CREATE TABLE 语句，其基本语法如下：
CREATE TABLE table_name (
column1 datatype [attributes],
column2 datatype [attributes],
...
);
其中：
table_name：表的名称。
column1, column2, ...：表中的字段名。
datatype：字段的数据类型。
attributes：字段的属性，如是否允许为空、是否为主键等。
2、字段的数据类型和属性
（1）整数类型：
在使用时，需要选择适当的整数类型，在选择整数类型时，应根据数据的范围和精度来决定。如果数据范围很小，可以选择占用存储空间更小的整数类型，以节省存储空间。如果数据范围很大，需要选择能够容纳数据的整数类型，避免数据溢出。
INT（整数类型）：
占用 4 字节的存储空间。
可存储范围为约 -2.1 亿到 2.1 亿。
适用于大多数情况下的整数存储，例如用户 ID、数量等。
TINYINT（小整数类型）：
占用 1 字节的存储空间。
可存储范围为约 -128 到 127（有符号）或 0 到 255（无符号）。
适用于存储较小范围的整数，如性别、状态等。
SMALLINT（短整数类型）：
占用 2 字节的存储空间。
可存储范围为约 -3.2 万到 3.2 万（有符号）或 0 到 6.5 万（无符号）。
适用于中等范围的整数，如年份、计数等。
MEDIUMINT（中整数类型）：
占用 3 字节的存储空间。
可存储范围为约 -8.4 亿到 8.4 亿（有符号）或 0 到 1.6 亿（无符号）。
适用于介于 INT 和 BIGINT 之间的整数范围。
BIGINT（大整数类型）：
占用 8 字节的存储空间。
可存储范围为约 -9.2 京到 9.2 京（有符号）或 0 到 18.4 京（无符号）。
适用于非常大的整数范围，如订单号、金额等。
示例：
假设我们要设计一个存储用户信息的表，其中包含 user_id、age 和 status 三个字段。根据不同的需求，我们可以选择不同的整数类型：
CREATE TABLE users (
user_id INT PRIMARY KEY,
age TINYINT UNSIGNED,
status SMALLINT
);
在这个示例中：
user_id 使用 INT 类型存储用户 ID，因为它可能是一个唯一的标识，并且范围可能很大。
age 使用 TINYINT UNSIGNED 类型存储年龄，因为年龄范围通常很小，并且不应该是负数。
status 使用 SMALLINT 类型存储用户状态码，因为它可能有多种状态，但是状态码通常不需要非常大的整数范围。
（2）浮点数类型：
在使用时，需要选择适当的浮点数类型，如果需要存储较小范围内的小数值，并且对精度要求不是非常高，可以选择 FLOAT 类型。如果需要存储较大范围内的小数值，并且对精度要求较高，可以选择 DOUBLE 类型。如果需要进行精确计算，并且对存储空间要求不是很严格，可以选择 DECIMAL 类型。
FLOAT：
4 字节，单精度浮点数。
存储范围约为 -3.4E+38 到 3.4E+38。
精度约为 7 位小数。
DOUBLE 或 REAL：
8 字节，双精度浮点数。
存储范围约为 -1.79E+308 到 1.79E+308。
精度约为 15 位小数。
DECIMAL 或 NUMERIC：
定点数类型，用于存储精确的小数值。
可以指定精度和小数位数，适用于货币金额等需要精确计算的场景。
示例：
假设我们要设计一个存储产品价格信息的表，其中包含 product_id 和 price 两个字段。根据不同的需求，我们可以选择不同的浮点数类型：
CREATE TABLE products (
product_id INT PRIMARY KEY,
price FLOAT
);
在这个示例中：
product_id 使用 INT 类型存储产品 ID，因为它通常是一个唯一的标识，并且范围可能很大。
price 使用 FLOAT 类型存储产品价格，因为价格通常是一个小数值，并且对精度要求不是非常高。
（3）字符类型：
在选择字符类型时，应根据实际需求和数据的特点进行选择，如果数据长度较为固定，可以选择 CHAR 类型，以节省存储空间。如果数据长度不确定或有较大的波动，应选择 VARCHAR 类型。对于较大的文本数据，应选择 TEXT 或 LONGTEXT 类型。
CHAR（固定长度字符串类型）：
CHAR 类型用于存储固定长度的字符串，最多可达 255 个字符。
当存储的字符串长度小于定义的长度时，MySQL 会使用空格填充。
CHAR 类型的存储空间是固定的，因此适用于存储长度相对稳定的数据，如固定长度的代码、标识符等。
VARCHAR（可变长度字符串类型）：
VARCHAR 类型用于存储可变长度的字符串，最多可达 65535 个字符。
VARCHAR 类型的存储空间是根据实际数据长度变化的，因此适用于存储长度不固定的数据，如变长的文本、用户名等。
VARCHAR 类型最大长度需要根据存储引擎和表的最大行大小进行调整。
TEXT（大文本类型）：
TEXT 类型用于存储大段文本数据，最多可达 65535 个字符。
TEXT 类型适用于存储较大的文本数据，如文章内容、备注等。
TEXT 类型与 VARCHAR 类型的区别在于，TEXT 类型存储的数据不计入表的最大行大小限制。
LONGTEXT（超大文本类型）：
LONGTEXT 类型用于存储更大的文本数据，最多可达 4294967295 个字符。
LONGTEXT 类型适用于存储非常大的文本数据，如长篇文章、日志等。
示例
假设我们要设计一个存储用户信息的表，其中包含 user_id 和 username 两个字段。根据不同的需求，我们可以选择不同的字符类型：
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50)
);
在这个示例中：
user_id 使用 INT 类型存储用户 ID，因为它通常是一个唯一的标识，并且范围可能很大。
username 使用 VARCHAR(50) 类型存储用户名，因为用户名的长度是可变的，并且不会太长。
（4）日期和时间类型：
在选择日期和时间类型时，应根据实际需求和数据的特点进行选择，DATE、TIME、DATETIME 和 TIMESTAMP 类型的存储空间分别为 3、3、8 和 4 字节。TIMESTAMP 类型在写入时会自动转换为 UTC 时间，而在读取时会自动转换为当前时区的时间。DATETIME 类型可以存储更广泛的日期时间范围，而 TIMESTAMP 类型通常用于记录数据的创建或修改时间。
DATE（日期类型）：
DATE 类型用于存储日期，格式为 'YYYY-MM-DD'。
DATE 类型可以表示的日期范围为 '1000-01-01' 到 '9999-12-31'。
适用于存储只涉及日期的数据，如生日、注册日期等。
TIME（时间类型）：
TIME 类型用于存储时间，格式为 'HH:MM:SS'。
TIME 类型可以表示的时间范围为 '-838:59:59' 到 '838:59:59'。
适用于存储只涉及时间的数据，如开店时间、上班时间等。
DATETIME（日期时间类型）：
DATETIME 类型用于存储日期时间，格式为 'YYYY-MM-DD HH:MM:SS'。
DATETIME 类型可以表示的日期时间范围为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
适用于存储同时涉及日期和时间的数据，如订单时间、日志时间等。
TIMESTAMP（日期时间类型）：
TIMESTAMP 类型也用于存储日期时间，格式为 'YYYY-MM-DD HH:MM:SS'。
TIMESTAMP 类型可以表示的日期时间范围与 DATETIME 类型相同。
TIMESTAMP 类型与 DATETIME 类型的区别在于，TIMESTAMP 类型会自动更新为当前时间戳，通常用于记录数据的创建或修改时间。
示例
假设我们要设计一个存储文章信息的表，其中包含 article_id 和 publish_time 两个字段。根据不同的需求，我们可以选择不同的日期时间类型：
CREATE TABLE articles (
article_id INT PRIMARY KEY,
publish_time DATETIME
);
在这个示例中：
article_id 使用 INT 类型存储文章 ID，因为它通常是一个唯一的标识，并且范围可能很大。
publish_time 使用 DATETIME 类型存储文章的发布时间，因为发布时间同时涉及日期和时间。
3、字段属性
（1）主键（Primary Key）：
PRIMARY KEY 属性用于标识字段为主键，唯一标识表中的每一行数据。
主键的值必须是唯一的，且不能为 NULL。
每个表最多只能有一个主键。
（2）自增长（Auto Increment）：
AUTO_INCREMENT 属性用于指定字段为自增长字段，每次插入新数据时自动增加。
适用于用作主键的整数类型字段，可以简化插入数据的过程。
（3）唯一约束（Unique Constraint）：
UNIQUE 属性用于保证字段的值在整个表中唯一。
与主键不同，唯一约束允许字段的值为 NULL，但如果不为 NULL，则值必须唯一。
（4）非空约束（Not Null Constraint）：
NOT NULL 属性用于指定字段的值不能为空。
当插入新数据时，必须为非空约束的字段提供值，否则将引发错误。
（5）默认值（Default Value）：
DEFAULT value 属性用于指定字段的默认值，当插入新数据时如果未指定该字段的值，则使用默认值。
可以为字段指定任何合法的默认值，如常量、表达式等。
（6）存储引擎（Storage Engine）：
ENGINE 属性用于指定表的存储引擎，不同的存储引擎具有不同的特性和性能。
常见的存储引擎包括 InnoDB、MyISAM、Memory 等。
（7）注释（Comment）：
COMMENT 属性用于为字段添加注释，提供关于字段用途或说明的描述信息。
注释可以提高代码的可读性和可维护性，对于团队合作和文档生成非常有用。
（8）字符集和排序规则（Character Set and Collation）：
CHARACTER SET 和 COLLATE 属性用于指定字段的字符集和排序规则。
字符集定义了字段中允许的字符集合，排序规则定义了在排序和比较文本时要遵循的规则。
（9）存储格式（Storage Format）：
ROW_FORMAT 属性用于指定表的行存储格式，影响数据存储和检索的性能。
常见的存储格式包括 Compact、Dynamic、Compressed 等。
（10）自定义约束（Custom Constraint）：
可以使用触发器或存储过程等方式实现自定义约束，确保数据满足特定的业务逻辑或规则。
示例：
下面是一个示例表的创建语句，展示了如何使用以上提到的属性：
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department_id INT,
hire_date DATE,
salary DECIMAL(10, 2) DEFAULT 0.00,
INDEX (department_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工基本信息表';
在这个示例中：
employee_id 使用 PRIMARY KEY AUTO_INCREMENT 属性作为主键，并且自动增长。
name 使用 NOT NULL 属性确保员工姓名不能为空。
email 使用 UNIQUE 属性确保邮箱地址在整个表中唯一。
department_id 使用 INDEX 和 FOREIGN KEY 属性与另一个表的字段建立外键关系。
hire_date 使用 DATE 类型存储员工入职日期。
salary 使用 DECIMAL(10, 2) DEFAULT 0.00 属性指定了默认值为 0.00 的工资字段。
表使用 InnoDB 存储引擎，并且设置了字符集和排序规则，并添加了注释以描述表的用途。
三、增删改查操作
当操作数据库时，经常会用到增删改查（CRUD）这四种基本操作，它们分别是创建（Create）、读取（Read）、更新（Update）和删除（Delete）数据。下面详细介绍这四种操作的实现方式：
1、新增
新增操作（Create）用于向数据库中添加新的数据记录。在 MySQL 中，新增操作通常通过 INSERT INTO 语句来实现。下面是详细介绍新增操作的步骤和示例。
新增操作步骤
选择目标表：确定要向哪个表中添加新的数据记录。
指定列和值：指定要插入数据的列名和对应的值。
执行插入操作：执行 INSERT INTO 语句将数据插入到目标表中。
示例：
假设我们有一个名为 students 的表，包含 student_id、name 和 age 三个字段，下面是向该表中插入新数据的示例：
-- 插入单条数据
INSERT INTO students (name, age) VALUES ('Alice', 20);
-- 插入多条数据
INSERT INTO students (name, age) VALUES
('Bob', 22),
('Charlie', 21),
('David', 23);
在这个示例中：
第一个 INSERT INTO 语句插入了一条学生记录，指定了 name 和 age 两个列的值为 'Alice' 和 20。
第二个 INSERT INTO 语句插入了多条学生记录，每个 VALUES 子句对应一条记录，分别指定了不同学生的姓名和年龄。
注意：
在执行新增操作之前，应确保提供了正确的列名和相应的值，以避免数据插入错误的列或缺少必要的数据。
如果目标表中有自增长字段，可以省略插入语句中对应的列，数据库会自动为该字段生成新的值。
2、删除
删除操作（Delete）用于从数据库中删除现有的数据记录。在 MySQL 中，删除操作通常通过 DELETE FROM 语句来实现。下面是详细介绍删除操作的步骤和示例：
删除操作步骤
选择目标表：确定要从哪个表中删除数据记录。
指定条件：指定要删除的数据记录的条件，以确定要删除哪些数据。
执行删除操作：执行 DELETE FROM 语句将满足条件的数据记录从目标表中删除。
示例：
假设我们有一个名为 students 的表，包含 student_id、name 和 age 三个字段，下面是删除数据的示例：
-- 删除所有数据
DELETE FROM students;
-- 删除符合条件的数据
DELETE FROM students WHERE age > 25;
在这个示例中：
第一个 DELETE FROM 语句删除了 students 表中的所有数据记录。
第二个 DELETE FROM 语句删除了 students 表中年龄大于 25 岁的学生数据记录。
注意：
在执行删除操作之前，应谨慎选择删除的条件，以避免误删重要数据。
删除操作是不可逆的，一旦执行，数据将永久丢失，请在执行删除操作之前确认操作。
3、修改
修改操作（Update）用于修改数据库中现有的数据记录。在 MySQL 中，修改操作通常通过 UPDATE 语句来实现。下面是详细介绍修改操作的步骤和示例：
修改操作步骤
选择目标表：确定要修改哪个表中的数据记录。
指定更新内容：指定要修改的列和对应的新值。
指定更新条件：指定要更新的数据记录的条件，以确定要修改哪些数据。
执行更新操作：执行 UPDATE 语句将满足条件的数据记录进行修改。
示例：
假设我们有一个名为 students 的表，包含 student_id、name 和 age 三个字段，下面是修改数据的示例：
-- 更新单个字段
UPDATE students SET age = 22 WHERE name = 'Alice';
-- 更新多个字段
UPDATE students SET name = 'Bob', age = 23 WHERE student_id = 2;
在这个示例中：
第一个 UPDATE 语句将名为 'Alice' 的学生的年龄更新为 22 岁。
第二个 UPDATE 语句将学生 ID 为 2 的学生的姓名更新为 'Bob'，年龄更新为 23 岁。
注意：
在执行修改操作之前，应谨慎选择更新的条件和更新的字段，以确保修改的数据符合预期。
修改操作会直接修改数据库中的数据，务必确认修改的内容正确无误。
4、查询
查询操作（Read）用于从数据库中检索数据记录。在 MySQL 中，查询操作通常通过 SELECT 语句来实现。下面是详细介绍查询操作的步骤和示例：
查询操作步骤
选择目标表：确定要从哪个表中检索数据记录。
指定要检索的列：指定要检索的列名，可以是所有列（*）或特定列。
指定检索条件：指定检索数据的条件，以过滤出符合条件的数据记录。
执行查询操作：执行 SELECT 语句将满足条件的数据记录检索出来。
示例：
假设我们有一个名为 students 的表，包含 student_id、name 和 age 三个字段，下面是一些查询数据的示例：
-- 检索所有数据
SELECT * FROM students;
-- 检索特定列数据
SELECT name, age FROM students;
-- 检索符合条件的数据
SELECT * FROM students WHERE age > 20;
-- 检索排序后的数据
SELECT * FROM students ORDER BY age DESC;
-- 检索前 N 条数据
SELECT * FROM students LIMIT 5;
-- 检索部分数据
SELECT * FROM students WHERE age BETWEEN 20 AND 25;
在这个示例中：
第一个 SELECT 语句检索了 students 表中的所有数据记录。
第二个 SELECT 语句检索了 students 表中的姓名和年龄两列数据记录。
第三个 SELECT 语句检索了 students 表中年龄大于 20 岁的数据记录。
第四个 SELECT 语句检索了 students 表中的数据记录，并按年龄降序排序。
第五个 SELECT 语句检索了 students 表中的前 5 条数据记录。
第六个 SELECT 语句检索了 students 表中年龄在 20 到 25 岁之间的数据记录。
注意：
在执行查询操作之前，应根据实际需求选择合适的列和条件，以确保检索到的数据符合预期。
查询操作可以通过使用 WHERE 子句、ORDER BY 子句、LIMIT 子句等来进一步筛选、排序和限制检索结果。
常用的查询函数：
1、聚合函数
COUNT()：统计符合条件的行数。
SUM()：计算指定列的总和。
AVG()：计算指定列的平均值。
MAX()：获取指定列的最大值。
MIN()：获取指定列的最小值。
2、字符串函数
CONCAT()：将多个字符串连接成一个字符串。
SUBSTRING()：提取子字符串。
UPPER() 和 LOWER()：将字符串转换为大写或小写。
3、日期和时间函数
NOW()：获取当前日期和时间。
DATE_FORMAT()：格式化日期和时间。
DATEDIFF()：计算日期之间的差值。
DATE_ADD() 和 DATE_SUB()：对日期进行加减运算。
4、逻辑函数
IF() 和 CASE WHEN：条件判断函数，根据条件返回不同的值。
常用的查询关键字：
WHERE：用于指定条件，过滤满足条件的数据记录。
ORDER BY：用于对检索结果进行排序。
GROUP BY：用于对检索结果进行分组汇总。
HAVING：结合 GROUP BY 使用，用于指定条件，过滤分组后的结果。
LIMIT：用于限制检索结果的数量。
DISTINCT：用于去重，返回不重复的值。
JOIN：用于连接多个表，进行关联查询。
使用常用函数和关键字的示例：
假设我们有一个名为 employees 的表，包含员工的基本信息和薪资信息，表结构如下：
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
hire_date DATE,
salary DECIMAL(10, 2)
);
插入一些示例数据：
INSERT INTO employees (employee_id, name, department_id, hire_date, salary) VALUES
(1, 'Alice', 1, '2022-01-15', 50000.00),
(2, 'Bob', 2, '2021-09-10', 60000.00),
(3, 'Charlie', 1, '2023-03-20', 55000.00);
执行一些查询，使用常用的查询函数和关键字：
统计员工人数：
SELECT COUNT(*) AS num_of_employees FROM employees;
计算平均工资：
SELECT AVG(salary) AS avg_salary FROM employees;
查询最高工资和最低工资：
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;
按部门分组，统计每个部门的员工人数：
SELECT department_id, COUNT(*) AS num_of_employees
FROM employees
GROUP BY department_id;
查询入职日期在当前年份的员工姓名和入职日期：
SELECT name, hire_date
FROM employees
WHERE YEAR(hire_date) = YEAR(NOW());
查询入职日期在当前月份的员工姓名和入职日期：
SELECT name, hire_date
FROM employees
WHERE YEAR(hire_date) = YEAR(NOW()) AND MONTH(hire_date) = MONTH(NOW());
查询工资高于平均工资的员工信息：
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
查询工资最高的员工信息：
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
查询工资排名前三的员工信息：
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;
查询入职时间最早的员工信息：
SELECT *
FROM employees
WHERE hire_date = (SELECT MIN(hire_date) FROM employees);
查询员工姓名和部门名称，并按照部门名称排序：
SELECT e.name AS employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name;
查询每个部门的平均工资，并按照平均工资降序排序：
SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;
查询每个部门的员工人数，并按照人数降序排序：
SELECT d.department_name, COUNT(e.employee_id) AS num_of_employees
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY num_of_employees DESC;
查询员工姓名和入职日期，按照入职日期降序排序，且只显示入职日期在当前年份的员工：
SELECT name, hire_date
FROM employees
WHERE YEAR(hire_date) = YEAR(NOW())
ORDER BY hire_date DESC;
原文链接：https://blog.csdn.net/xiangxi1204/article/details/138823737
