Mysql基础知识学习总结

一.数据库的相关概念

关系型数据库是一种广泛使用的数据库类型，它基于关系模型，这个模型由埃德加·科德（Edgar F. Codd）在1970年代初期提出。在这种模型中，数据被存储在表格中，表格之间可以通过共享的数据项（称为键）相互关联。

二.关系模型的核心概念

1. 表（Table）：

   • 表是关系型数据库中的基本存储单位，也称为关系。
   • 表由行（记录）和列（字段）组成，每行表示一个数据项（例如一个客户或一个交易），每列代表某种数据类型的属性（例如客户的姓名或交易的日期）。

2. 列（Column）：

   • 列定义了表中存储的数据类型和可存储的数据形式（例如整数、字符串、日期等）。

3. 行（Row）：

   • 行在数据库中代表一个数据记录，它包含了表中每个列声明的数据项。

4. 键（Key）：

   • 键是一种特殊的列（或列的组合），用于唯一标识表中的每条记录。最常见的是主键（Primary Key），每个表可以有一个主键，用于唯一确定表中的每一行。
   • 外键（Foreign Key）则是另一种重要的键，它用于链接两个表，通过外键，一个表中的记录可以引用另一个表中的记录。

三.SQL分类

四.SQL语句

注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

命令行连接Mysql客户端

source d:\mysqldb.sql

desc

在SQL中，DESCRIBE命令（在某些数据库系统中也称为DESC）用于获取有关表结构的详细信息。这个命令是数据库设计和调试时的一个有用工具，它可以快速显示表的列信息，包括列的名称、数据类型、是否允许空值、键信息以及其他可能的属性。

基本用法

DESCRIBE table_name;

或

DESC table_name;

输出解释

当你运行DESCRIBE命令时，你会得到以下列的信息：

• Field: 列的名称。
• Type: 列的数据类型，例如varchar(255), int, date等。
• Null: 如果列可以存储NULL值，这里会显示YES；如果不可以，则显示NO。
• Key: 如果列是表的一部分键，这里会显示键的类型。例如，PRI表示主键，UNI表示具有唯一约束的键，MUL表示该列可能在多个行中出现多次，通常与外键相关。
• Default: 列的默认值，如果设定了默认值的话。
• Extra: 其他信息，如自动递增属性auto_increment。

示例

假设有一个名为users的表，包含id, name, 和email字段，运行以下命令：

DESCRIBE users;

可能会得到如下输出：

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

show

在SQL中，SHOW命令是一个强大的工具，主要用于MySQL和类似的数据库管理系统。它用来显示各种数据库对象（如数据库、表、索引）的信息，以及数据库服务器的状态和变量。SHOW命令非常有用于数据库的管理、监控和优化。

基本用法

以下是一些常用的SHOW命令的例子：

显示数据库列表

SHOW DATABASES;

这个命令列出服务器上所有数据库的名称。

显示当前数据库中的表

SHOW TABLES;

如果你想要指定数据库，可以使用：

SHOW TABLES FROM database_name;

显示表的列信息

SHOW COLUMNS FROM table_name;

或者在指定数据库中：

SHOW COLUMNS FROM table_name FROM database_name;

这与DESCRIBE table_name;命令功能相似。

显示表的索引信息

SHOW INDEX FROM table_name;

这个命令显示指定表的所有索引的详细信息。

显示服务器状态和系统变量

SHOW STATUS;

这个命令显示关于服务器状态的系统变量，如连接数、运行时间等。

SHOW VARIABLES;

这个命令显示所有MySQL系统变量的设置，这些变量可以影响服务器的操作。

SHOW VARIABLES LIKE 'query_cache%';

这个命令显示与查询缓存相关的所有系统变量。

显示创建表的SQL语句

SHOW CREATE TABLE table_name;

这个命令返回创建指定表的完整SQL语句，非常有用于复制表结构或进行数据库迁移。

显示当前的权限

SHOW GRANTS;

这个命令显示当前用户的所有权限。

select

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

distinct 工作原理

1. 解析和优化查询：

   • 当查询被提交时，首先进行解析，DISTINCT关键字被识别。
   • 查询优化器决定如何执行去重操作最为高效，这可能涉及选择使用临时表、哈希表或排序。

2. 执行查询：

   • 根据所选的策略，数据库执行实际的查询并应用DISTINCT。

3. 数据去重处理：

   • 排序方法：数据库通过对结果集中的数据进行排序来去重。排序后，数据库扫描排序的结果，并过滤掉连续的重复行。这要求所有选定的列都参与排序操作。
   • 哈希方法：数据库可能使用哈希表来实现去重。这涉及计算每一行的哈希值，并在哈希表中存储这些值。如果发现已经存在相同的哈希值，则该行被认为是重复的，不会被包含在最终结果中。

4. 返回结果：

   • 去重后的数据作为查询结果返回给用户。

五.常见函数

按照功能分类进行介绍：

字符串函数

1. CONCAT()：连接两个或多个字符串。

   SELECT CONCAT('Hello', ' ', 'World');
   

2. LENGTH()：返回字符串的长度（以字节为单位）。

   SELECT LENGTH('text');
   

3. SUBSTRING()：从字符串中提取子字符串。

   SELECT SUBSTRING('Hello World', 1, 5);
   

4. LOWER() 和 UPPER()：将字符串转换为全小写或全大写。

   SELECT LOWER('Hello'), UPPER('World');
   

5. TRIM()：去除字符串两端的空白字符。

   SELECT TRIM('  hello  ');
   

6. REPLACE()：在字符串中替换子字符串。

   SELECT REPLACE('Hello World', 'World', 'MySQL');
   

数值函数

1. ROUND()：对数值进行四舍五入。

   SELECT ROUND(123.456, 2);
   

2. FLOOR() 和 CEIL()：分别返回不大于或不小于给定数值的最大或最小整数。

   SELECT FLOOR(123.456), CEIL(123.456);
   

3. ABS()：返回数值的绝对值。

   SELECT ABS(-123.456);
   

4. RAND()：生成一个随机数。

   SELECT RAND();
   

在MySQL中，如果你想保留小数位而不进行四舍五入，可以使用TRUNCATE()函数。这个函数可以截断数字到你指定的小数位，而不进行四舍五入。

TRUNCATE() 函数

TRUNCATE() 函数用来截断一个数字到指定的小数位数。它的语法如下：

TRUNCATE(number, decimals);

• number 是要截断的数值。
• decimals 指定要保留的小数位数。如果这个值是0，函数返回的是一个没有小数的整数。如果这个值是正数，数字就被截断到相应的小数位数。如果这个值是负数，数字则在小数点左侧被截断。

日期和时间函数

1. NOW()：返回当前的日期和时间。

   SELECT NOW();
   

2. CURDATE()：返回当前的日期。

   SELECT CURDATE();
   

3. CURTIME()：返回当前的时间。

   SELECT CURTIME();
   

4. DATEDIFF()：计算两个日期之间的天数差。

   SELECT DATEDIFF('2022-01-01', '2022-01-10');
   

5. DATE_ADD() 和 DATE_SUB()：日期的加法和减法。

   SELECT DATE_ADD('2022-01-01', INTERVAL 1 MONTH);
   SELECT DATE_SUB('2022-01-01', INTERVAL 10 DAY);
   

聚合函数

1. SUM()：计算数值列的总和。

   SELECT SUM(salary) FROM employees;
   

2. AVG()：计算数值列的平均值。

   SELECT AVG(salary) FROM employees;
   

3. COUNT()：计算行数或非NULL值的数量。

   SELECT COUNT(*) FROM employees;
   SELECT COUNT(column_name) FROM employees;
   

4. MAX() 和 MIN()：找出列中的最大值和最小值。

   SELECT MAX(salary), MIN(salary) FROM employees;
   

5. GROUP_CONCAT()：将组内的值连接成一个字符串。

   SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM employees GROUP BY department_id;
   

六.常见数据类型

七.增删改基本模板

在SQL中，增加（INSERT）、删除（DELETE）和修改（UPDATE）是数据库操作中最基本的数据操作语句。下面是这些操作的基本语句模板，适用于大多数SQL数据库系统，如MySQL、PostgreSQL、SQL Server等。

1. 插入数据（INSERT）

INSERT语句用于向数据库表中添加新的行。

基本模板

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

示例

假设有一个名为 students 的表，其中包含 id、name 和 age 三个列：

INSERT INTO students (name, age)
VALUES ('John Doe', 22);

这条语句在students表中插入一行，其中name列的值为John Doe，age列的值为22。

2. 更新数据（UPDATE）

UPDATE语句用于修改表中现有行的数据。

基本模板

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

示例

继续使用上面的students表，如果我们想更新John Doe的年龄：

UPDATE students
SET age = 23
WHERE name = 'John Doe';

这条语句将students表中所有名为John Doe的记录的age列更新为23。

3. 删除数据（DELETE）

DELETE语句用于从表中删除行。

基本模板

DELETE FROM table_name
WHERE condition;

示例

如果我们想从students表中删除John Doe的记录：

DELETE FROM students
WHERE name = 'John Doe';

这条语句删除students表中所有名为John Doe的记录。