MySQL -- 基础

已于 2024-06-04 10:38:03 修改
阅读量1k 收藏 23
点赞数 16
分类专栏： 数据库 文章标签： mysql 数据库
于 2024-05-23 13:43:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Afison/article/details/139138614
版权

1.数据库相关介绍

数据库的选择通常取决于具体的应用需求，如性能、扩展性、数据一致性和易用性等因素。

1. 关系型数据库（RDBMS）

  • MySQL：

    • 广泛使用的开源数据库，支持大多数操作系统。
    • 强调易用性、灵活性和广泛的社区支持。
    • 主要用于Web应用。

  • PostgreSQL：

    • 另一种强大的开源关系型数据库，以其稳定性和高级功能（如高级索引、全文搜索、JSON支持）著称。
    • 通常被视为企业级数据库，支持更复杂的查询和更大的数据量。

  • Oracle Database：

    • 企业级商业数据库解决方案，提供全面的特性支持，包括高级数据管理、安全性和可靠性。
    • 常用于需要高事务处理和复杂查询操作的大型企业和金融机构。

  • Microsoft SQL Server：

    • 面向企业的数据库产品，集成了大量管理工具，优化了.NET环境的数据操作。
    • 常用于企业环境，与其他Microsoft产品（如Excel和Power BI）有很好的集成性。

2. 非关系型数据库（NoSQL）

  • MongoDB：

    • 文档型数据库，以其高性能、高可扩展性和易用性著称。
    • 数据以类似JSON的格式存储，使其在处理大规模数据集时非常灵活。

  • Cassandra：

    • 高度可扩展的分布式NoSQL数据库，设计用于处理大量数据 across many commodity servers.
    • 提供高可用性而不牺牲性能，适合需要大规模数据分布的应用。

  • Redis：

    • 开源的键值存储数据库，常用作数据缓存和消息队列。
    • 支持数据结构如字符串、列表、集合、散列等。

主要区别

  • 数据结构：

    • 关系型数据库使用表格和行的结构，适合需要执行复杂查询的应用。
    • 非关系型数据库（如文档、键值、宽列存储）则更灵活，适合于非结构化数据。

  • 事务性：

    • 关系型数据库支持ACID属性（原子性、一致性、隔离性、持久性），确保数据的完整性。
    • 部分非关系型数据库牺牲了事务性以换取性能和扩展性。

  • 扩展性：

    • 关系型数据库通常扩展性较差，尤其是垂直扩展。
    • 非关系型数据库设计为水平扩展，可以通过增加更多服务器来增加容量。

  • 查询能力：

    • 关系型数据库支持复杂的查询语言（如SQL），适合复杂的数据分析。
    • 非关系型数据库的查询功能比较基础，主要优化了读/写操作的速度。

选择哪种数据库取决于具体项目的需求，如数据模型、预期负载、扩展需求等。理解每种数据库的特点和局限是选择最合适数据库的关键。

2. MySQL 数据类型

数值类型
  • 整数类型：INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT。有符号范围从 -128 到 127（TINYINT），无符号范围从 0 到 255。
  • 定点数类型：DECIMAL，NUMERIC。可以精确表示。
  • 浮点数类型：FLOAT, DOUBLE。用于表示有小数点的数值。
字符串类型
  • 文本类型：CHAR, VARCHAR（可变长度字符串）。
  • 长文本类型：TEXT, BLOB，以及它们的变种 TINYTEXT, MEDIUMTEXT, LONGTEXT。
日期和时间类型
  • 日期类型：DATE（仅日期），DATETIME（日期和时间），TIMESTAMP（时间戳，具有时区支持），TIME（时间），YEAR（年份）。
ENUM 和 SET
  • ENUM：列出所有可能的值。
  • SET：允许选择多个值，类似于一个包含多个预定义值的集合。

3. MySQL 运算符

算数运算符
  • 基本运算：+, -, *, /, %（取余）
逻辑运算符
  • AND, OR, NOT，用于条件组合。
比较运算符
  • =, != 或 <>（不等于），>, <, >=, <=，BETWEEN，LIKE, IN。

4. MySQL 常用函数

字符串函数
  • CONCAT()：串联字符串。
  • LENGTH()：返回字符串的字节长度。
  • SUBSTRING()：截取部分字符串。
  • LOWER()、UPPER()：转换字母大小写。
数值函数
  • ABS()：绝对值。
  • ROUND()：四舍五入。
  • CEIL()、FLOOR()：向上/向下取整。
时间和日期函数
  • NOW()：当前的日期和时间。
  • CURDATE()：当前日期。
  • DATEDIFF()：计算两个日期之间的差。
聚合函数
  • SUM()：求和。
  • AVG()：平均值。
  • COUNT()：计数。
  • MAX()、MIN()：最大值、最小值。

5. MySQL 完整性约束

完整性约束的类型包括：
  • PRIMARY KEY：主键，唯一标识表中的每一行。
  • FOREIGN KEY：外键，引用另一个表的主键。
  • UNIQUE：唯一约束，保证数据的唯一性。
  • NOT NULL：非空约束，保证列不接受 NULL 值。
  • CHECK：检查约束（在MySQL 8.0.16及以上版本中支持），限制列中值的范围。

test：

CREATE TABLE user (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户的主键id',
    nickname VARCHAR(50) UNIQUE NOT NULL COMMENT '用户的昵称',
    age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '用户的年龄，默认为18',
    sex ENUM('male', 'female') NOT NULL COMMENT '用户的性别'
);
  1. id：一个无符号整数（INT UNSIGNED），设定为自动递增（AUTO_INCREMENT）。作为主键，用来唯一标识表中的每一行。
  2. nickname：一个长度为50的字符串，设定为唯一（UNIQUE）且不允许为空（NOT NULL）。用来存储用户的昵称。
  3. age：一个无符号的小整数（TINYINT UNSIGNED），不允许为空（NOT NULL），默认值为18。用来存储用户的年龄。
  4. sex：一个枚举类型（ENUM），只允许为'male'或'female'，不允许为空。用来表示用户的性别。

6. 关系型数据库表设计（关系模型）

关系型数据库的表设计涉及到如何合理地组织数据以及各个数据表之间的关联关系。根据关联性质，关系可以分为三种类型：

  • 一对一关系：一个表中的记录只与另一个表中的一条记录相关联。例如，用户表和用户详情表，每个用户只有一个详细信息记录，反之亦然。

  • 一对多关系：一个表中的记录可以与另一个表中的多条记录相关联。最典型的例子是，用户表和订单表，一个用户可以有多个订单，但每个订单只能属于一个用户。

  • 多对多关系：一个表中的记录可以与另一个表中的多条记录相关联，反之亦然。例如，学生表和课程表，一个学生可以注册多门课程，一门课程也可以由多个学生注册。通常通过一个中间表来实现这种关系，如学生课程关系表。

7. 关系型数据库范式

范式（Normalization）是为了减少数据库中数据冗余和改善数据结构设计的一系列规则。主要范式包括：

  • 第一范式（1NF）：表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即属性的原子性。

 假设有一个学生参与的课程表如下：

学生ID学生姓名课程
1张三数学, 物理

这个表不满足第一范式，因为课程列有多个值。为了满足1NF，需要将课程列分解为不可分割的单个项：

学生ID学生姓名课程
1张三数学
1张三物理
  • 第二范式（2NF）：在1NF的基础上，非主属性完全依赖于主键，消除了非主属性对主键的部分依赖。

例子：

假设有一个学生选课表，如下所示：

学生ID学生姓名课程ID课程名称课程教师
1张三101数学李老师
1张三102物理王老师
2李四101数学李老师
2李四103化学赵老师

在这个表中，每个课程的教师（课程教师）是由课程ID决定的，而不是由学生ID和课程ID共同决定。这意味着存在部分依赖，因为课程教师只依赖于课程ID的一部分，而不是整个复合键（学生ID和课程ID）。为了满足第二范式，需要消除这个部分依赖，将表格分解为两个表，从而确保每个非主属性只依赖于整个主键。

学生选课表：
学生ID学生姓名课程ID
1张三101
1张三102
2李四101
2李四103
课程信息表：
课程ID课程名称课程教师
101数学李老师
102物理王老师
103化学赵老师

  • 第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其他非主属性，消除了传递依赖。

假设现有订单明细表：

订单ID产品ID产品名称产品制造商
100101笔记本电脑联想
100102鼠标罗技

产品名称和产品制造商都依赖于产品ID，这是一个传递依赖。为满足3NF，需要进一步分解表：

产品表：

产品ID产品名称产品制造商
01笔记本电脑联想
02鼠标罗技

订单明细表仅保留产品ID：

订单ID产品ID
100101
100102
  • Boyce-Codd范式（BCNF）：更严格的3NF，要求表中的每一个决定因素都是候选键，处理复杂的依赖和冗余问题。

如果在上面的产品表中，产品ID和产品名称都可以唯一确定记录，并且存在如下依赖：产品ID → 产品名称，产品名称 → 产品制造商。该表不满足BCNF，因为产品名称也能决定产品制造商。我们需要进一步规范化以满足BCNF：

产品表：

产品名称产品制造商
笔记本电脑联想
鼠标罗技
  • 第四范式（4NF）：在BCNF的基础上，消除表中的多值依赖。

假设一个教师可教授多个课程，并且有多个研究领域。这些信息存在多值依赖。

教师ID课程研究领域
T01数学, 物理代数, 力学

教师课程表：

教师ID课程
T01数学
T01物理

教师研究领域表：

教师ID研究领域
T01代数
T01力学

这样的设计确保了每张表中只含有与主键相关的多值依赖，避免了非主键属性间的依赖，符合第四范式的要求。通过这种分解，我们可以更有效地管理和维护数据，同时减少数据冗余和更新异常。

8.MySQL核心SQL

结构化查询语句SQL

SQL（结构化查询语言）是用于管理关系数据库系统的标准编程语言，主要用于存储、操作和检索数据库中的数据。以下是一些基本的SQL知识点和常用命令：


1. 数据定义语言 (DDL)：

DDL允许用户定义或修改数据库结构。

  • CREATE: 创建新表或数据库。
  CREATE TABLE students (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      age INT
  );
  • ALTER: 修改现有的数据库结构，如添加、删除或修改列。
ALTER TABLE students ADD COLUMN email VARCHAR(100);
  • DROP: 删除表或数据库。
  DROP TABLE students;

2. 数据操作语言 (DML)：

DML允许用户对数据进行插入、修改、删除和查询。

  • INSERT: 向表中添加新行。
  INSERT INTO students (id, name, age) VALUES (1, 'Alice', 21);
  • UPDATE: 更新表中的数据。
  UPDATE students SET age = 22 WHERE id = 1;
  • DELETE: 从表中删除数据。
  DELETE FROM students WHERE id = 1;
  • SELECT: 查询表中的数据。
  SELECT * FROM students WHERE age &gt; 20;

3. 数据控制语言 (DCL)

DCL用于控制不同用户对数据库中数据的访问。

  • GRANT: 授予权限。
  GRANT SELECT ON students TO user1;
  • REVOKE: 撤销权限。
  REVOKE SELECT ON students FROM user1;

4. 事务控制

SQL支持事务控制，以保证数据的完整性。

  • BEGIN TRANSACTION: 开始一个事务。
  • COMMIT: 提交当前事务，使之前的操作得到永久保存。
  • ROLLBACK: 回滚当前事务，撤销之前的所有操作。

5. 约束

约束用于确保数据库中数据的准确性和可靠性。

  • PRIMARY KEY: 唯一标识数据库表中的每条记录。
  • FOREIGN KEY: 一种约束，用于与另一表的主键字段建立链接，保持数据一致性。
  • NOT NULL: 确保列不能有NULL值。
  • UNIQUE: 确保所有列中的值都是唯一的。

6. 索引

索引用于提高数据库的查询速度。

  • CREATE INDEX: 在表中的一个或多个列上创建索引，提高搜索速度。
  CREATE INDEX idx_name ON students (name);

库操作

查询数据库

show databases;

创建数据库

create database ChatDB;

删除数据库

drop database ChatDB;

选择数据库

use ChatDB;

表操作

查看表

show tables;

创建表

create table user(id int unsigned primary key not null auto_increment,
name varchar(50) not null,
age tinyint not null,
sex enum('M','W') not null)engine=INNODB default charset=utf8;
  • age tinyint not null: 创建了一个名为 age 的列，类型为一个字节大小的整数（范围为 -128 到 127），不能为空。
  • sex enum('M','W') not null: 创建了一个名为 sex 的列，类型为枚举（enum），只能取 'M' 或 'W' 两个值中的一个，不能为空。

自动递增（auto_increment）。engine=INNODB: 这指定了表的存储引擎为 InnoDB。default charset=utf8: 这指定了表的默认字符集为 UTF-8。

查看表结构

desc user;

查看建表sql

show create table user\G

删除表

drop table user;

CRUD操作

"CRUD" 是一种常用于描述基本数据库操作的术语，它代表了四个常见的操作：Create（创建）、Read（读取）、Update（更新）和Delete（删除）。

insert增加

insert into user(nickname, name, age, sex) values('fixbug', 'zhang san', 22,
'M');
insert into user(nickname, name, age, sex) values('666', 'li si', 21, 'W'),
('888', 'gao yang', 20, 'M');

 增加多行数据可用第二种方式，省了很多client和Server的握手和挥手操作，提升性能

update修改

update user set age=23 where name='zhang san';
update user set age=age+1 where id=3;

delete删除

delete from user where age=23;
delete from user where age between 20 and 22;
delete from user;

 

删除某一列，id并不会缩减，仍然保持之前的递增顺序。 

select查询

select * from user;
select id,nickname,name,age,sex from user;
select id,name from user;
select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25;
select id,nickname,name,age,sex from user where sex='M' and age between 20 and
25;
select id,nickname,name,age,sex from user where sex='W' or age>=22;

 在数据库查询中，“回表”是指执行查询时，除了使用索引定位到相应的行之外，还需要进一步访问主表或者其他索引表来获取所需的数据。

  1. 覆盖索引（Covering Index）不可用：当查询涉及的字段不在索引中，或者使用的索引不是覆盖索引时，数据库需要从主表中获取额外的数据列，从而产生了额外的访问操作。

  2. 查询条件不满足索引覆盖：有时，即使索引覆盖了查询中的某些条件，但查询还需要额外的数据，因此需要回到主表或其他索引表中进行进一步的检索。

回表操作会增加查询的开销，因为它需要额外的磁盘 I/O 或内存访问来获取所需的数据。因此，尽量避免回表操作可以提高查询性能。常见的优化方法包括创建覆盖索引、优化查询条件以满足索引覆盖、调整表结构以减少回表次数等。

  • 在 SQL 中，下划线（_）通配符代表一个单个字符的占位符。
  • 百分号（%）通配符表示零个、一个或多个字符的占位符。

 EXPLAIN 语句用于获取查询执行计划，它可以帮助你理解数据库是如何执行你的查询的。

去重distinct

select distinct name from user;

 

空值查询

is [not] null

select * from user where name is null;

union合并查询

SELECT expression1, expression2, ... expression_n FROM tables[WHERE conditions] UNION [ALL | DISTINCT] # 注意：union默认去重，不用修饰distinct，all表示显示所有重复值 SELECT expression1, expression2, ... expression_n FROM tables[WHERE conditions];

SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY
country;

带in子查询

[NOT] IN(元素1，元素2，...，元素3)

select * from user where id in(10, 20, 30, 40, 50)
select * from user where id not in(10, 20, 30, 40, 50)
select * from user where id in(select stu_id from grade where average>=60.0)

分页查询

分页查询是在处理大量数据时，为了方便用户浏览而将数据分批次显示的一种技术。在数据库管理系统中，我们通常使用LIMIT和OFFSET语句来实现分页。

假设我们有一个名为employees的表，表中存储了公司所有员工的信息。如果我们想查看第二页的数据，每页显示10条记录，我们可以使用如下的SQL查询：

SELECT * FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 10;

LIMIT 10表示每页显示10条记录，OFFSET 10表示跳过前10条记录（即第一页的数据），从第11条开始显示，这样就实现了查看第二页的数据。

SELECT id, nickname, name, age, sex FROM user LIMIT 10;

 这个查询语句将从 "user" 表中选择 id、nickname、name、age 和 sex 列，并且只返回前 10 行结果。

SELECT id, nickname, name, age, sex FROM user LIMIT 2000, 10;

 这个查询语句也从 "user" 表中选择相同的列，但它使用了 LIMIT 子句的两个参数。第一个参数 2000 表示要跳过的行数，而第二个参数 10 表示要返回的行数。因此，这个查询将从结果集的第 2001 行开始返回接下来的 10 行数据。

以下是用于向 t_user 表中插入指定数量的用户数据，用户的邮箱地址和年龄是根据循环计数器生成的。

delimiter $
Create Procedure add_t_user (IN n INT)
BEGIN
DECLARE i INT;
SET i=0;
WHILE i<n DO
INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@fixbug.com'),i+1);
SET i=i+1;
END WHILE;
END$
delimiter ;
call add_t_user(2000000);

  1. DELIMITER $: 这一行将 MySQL 的语句终止符设置为 $，以便在存储过程中使用分号 ;。

  2. CREATE PROCEDURE add_t_user (IN n INT) ... END$: 这段代码创建一个名为 add_t_user 的存储过程，它接受一个整数参数 n，该参数表示要插入的用户数量。存储过程的主体部分位于 BEGIN 和 END 之间。

  3. DECLARE i INT;: 这行声明了一个整数类型的局部变量 i，用于循环计数器。

  4. SET i=0;: 这行将变量 i 初始化为 0。

  5. WHILE i<n DO ... END WHILE;: 这是一个循环语句，它会执行循环体中的代码块，直到 i 的值小于参数 n。

  6. INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@fixbug.com'),i+1);: 这行插入了一条用户数据到 t_user 表中。其中，NULL 表示自增主键字段，CONCAT(i+1,'@fixbug.com') 用于生成用户的邮箱地址，i+1 是用户的年龄。

  7. SET i=i+1;: 这行将变量 i 的值递增，以便在下一次循环中使用。

  8. END$: 存储过程的结束标志。

  9. DELIMITER ;: 恢复 MySQL 的语句终止符为默认的分号 ;。

  10. CALL add_t_user(2000000);: 这行调用了存储过程 add_t_user，并传入参数值 2000000，表示要插入的用户数量。

 使用limit提高了查询效率，explain虽然显示基本都遍历了全部行，但limit实际上还是有优化。

 

提高效率

通过带有索引的列快速过滤数据

排序(Order By)

排序是根据一列或多列的值，将数据库表中的行进行排序。在SQL中，ORDER BY子句用于定义排序的规则。

继续使用上述employees表，如果我们想按照员工的薪水降序显示数据，可以使用如下的SQL查询：

SELECT * FROM employees
ORDER BY salary DESC;

这里，DESC(descending)表示降序排列（从高到低）。如果想按升序排列（从低到高），可以使用ASC(ascending)或者省略（默认为升序）。

select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25
order by age asc;
select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25
order by age desc;

 分组(Group By)

分组用于将返回的记录集按照一个或多个列进行分组，常与聚合函数（如COUNT, SUM, AVG等）一起使用，来汇总每个分组的数据。

number为count(age)的别名，可在count(age)后加as或不加。

如果我们想知道每个部门的员工数量，可以使用GROUP BY和COUNT函数。假设employees表中有department_id和employee_id列，SQL查询可以是：

SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id;

 这里，GROUP BY department_id表示按部门ID进行分组。COUNT(employee_id)则计算每个部门的员工数。

select sex from user group by sex;
select count(id),sex from user group by sex;
select count(id),age from user group by age having age>20;

1.这个查询语句会从 "user" 表中选择 sex 列，并按照性别（sex 列的值）进行分组。

2.这个查询语句首先会按照性别（sex 列的值）进行分组，并对每个性别组内的行数进行计数。

3.这个查询语句首先会按照年龄（age 列的值）进行分组，并对每个年龄组内的行数进行计数。然后，HAVING 子句用于过滤分组，只返回年龄大于 20 岁的分组。

Afison
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值