当前位置：首页 > news >正文

MySQL数据库（3）-SQL基础语言学习

news 2024/12/14 0:29:30

1. DDL数据定义语言

1.1 什么是DDL

DDL（Data Definition Language，数据定义语言）是SQL语言的一部分，用于定义和修改数据库结构。DDL主要包括以下三类语句：

1.CREATE：用于创建数据库对象，如数据库、表、视图、索引、存储过程、触发器等。

2. ALTER：用于修改已存在的数据库对象的结构，如添加或删除表的列、修改列的数据类型、更改表的名称等。

3. DROP：用于删除数据库对象，如删除表、视图、索引、存储过程、触发器等。

这些DDL语句允许用户定义和管理数据库的结构，而不涉及数据的增删改查操作，后者通常由DML（Data Manipulation Language）语句处理。

1.2 DDL语言应用

创建库 create database demo;
切换库 use demo
查询库列表 show databases;
删除逻辑库 drop database demo;
删除表：drop table student;
创建表：（先使用use demo，选择要操作的库）

create table student(id int unsigned primary key,   #允许存储负数、主键唯一且不为空`name` varchar(20) not null,  #name是系统关键字，所以加``sex char(1) not null,birthday date not null,tel char(11) not null,remark varchar(200)
);

2.DML数据操纵语言

2.1 什么是DML

DML（Data Manipulation Language，数据操纵语言），用于操作数据库中的数据。

DML语句允许用户对数据进行增删改等操作，但不涉及修改数据库结构。DML的主要语句包括：

1. INSERT：用于向表中插入新的数据行。

2. UPDATE：用于修改表中已存在的数据行。

3. DELETE：用于从表中删除数据行。

这些语句用于直接操作表中的数据，而不改变表的结构或定义。DML语句通常与事务控制语句（如COMMIT和ROLLBACK）一起使用，以确保数据操作的完整性和一致性。

2.2 DDL语言应用

增：INSERT INTO student (id, name, age) VALUES (1, 'John', 30);

删：DELETE FROM student WHERE id = 1;

改：UPDATE student SET age = 31 WHERE id = 1;

3.DQL数据查询语言

3.1 什么是DQL

DQL（Data Query Language，数据查询语言），专门用于查询数据库中的数据。

DQL的核心功能是从数据库中检索数据，而不修改数据结构或数据本身。

DQL的主要语句是SELECT，它允许用户执行各种查询操作，包括：

选择特定列或所有列的数据。
使用WHERE子句进行条件过滤。
使用ORDER BY子句对结果进行排序。
使用GROUP BY子句对数据进行分组。
使用聚合函数（如COUNT, SUM, AVG等）进行数据汇总。

3.2 DQL语言应用

查：SELECT * FROM student;

4. 数据库约束

4.1 什么是约束

数据库中的约束（Constraint）是一种规则，用于限制可以存储在表中的数据，确保数据的准确性和可靠性。约束帮助维护数据库的完整性，防止无效或不一致的数据被插入。以下是一些常见的数据库约束：

主键约束（PRIMARY KEY）：确保列（或列的组合）中的每个值都是唯一的，并且非空。

外键约束（FOREIGN KEY）：用于链接两个表，确保引用表中的值必须在主表的主键中存在。

唯一约束（UNIQUE）：保证列中的所有值都是唯一的，但允许为空值。

非空约束（NOT NULL）：确保列不能包含空值。

检查约束（CHECK）：用于确保列中的值满足特定的条件，例如值的范围或模式。

默认值约束（DEFAULT）：当没有为列提供值时，指定一个默认值。

4.2 约束具体应用

主键约束：CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

外键约束：CREATE TABLE orders (order_id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));

唯一约束：CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(100) UNIQUE);

非空约束：CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);

5.数据库索引

5.1 什么是索引

在MySQL数据库中，索引是一种数据结构，用于提高数据库查询的效率。

索引类似于书籍的目录，它允许数据库管理系统快速检索表中的数据，而不需要扫描整个表。

5.2 索引具体应用

1.普通索引（INDEX）：

这是最基本的索引类型，没有唯一性的要求。它可以加快数据检索速度。

2.唯一索引（UNIQUE）：

唯一索引要求索引列的值必须唯一，但可以有空值NULL。

唯一索引不仅用于提高查询效率，还用于保证数据的唯一性。

3.主键索引（PRIMARY KEY）：

主键索引是一种特殊的唯一索引，表中只能有一个主键索引。

主键索引不仅要求唯一性，还要求不能有空值。主键通常用于标识表中每一行的唯一记录。

4.外键索引（FOREIGN KEY）：

外键索引用于实现表之间的关联关系，确保数据的参照完整性。

虽然MySQL的InnoDB存储引擎支持外键，但外键本身也是一种索引。

5.复合索引（COMPOSITE INDEX）：

复合索引是指包含两个或多个列的索引。它用于优化涉及多个列的查询条件。

6.全文索引（FULLTEXT）：

全文索引用于全文搜索，支持在文本字段中进行快速搜索。它特别适用于搜索引擎功能。

7.空间索引（SPATIAL）：

空间索引用于地理空间数据类型，支持对地理数据的快速检索。

8.B-tree索引

B-tree索引是一种基于平衡树的数据结构，支持快速查找、有序存储和高效的范围查询。

它通过动态平衡机制适应数据变动，并优化磁盘I/O操作。

B-tree索引的节点可以有多个子节点，且常以B+树形式实现，进一步提升索引性能。

9.聚簇索引（Clustered Index）

聚簇索引是一种特殊的索引类型，它决定了数据在表中的物理存储顺序。

其主要特点包括：数据行按照聚簇索引键的顺序存储，表中只能有一个聚簇索引，支持高效的基于索引键的查询和范围查询，但在插入和更新时可能导致页分裂，影响性能。

聚簇索引通常用于优化数据检索，特别适合需要频繁进行范围查询的场景。

10.哈希索引

哈希索引利用哈希表实现快速等值查询，通过哈希函数将键值映射到表中的位置。

其主要特点包括：高效的等值查询能力、不支持范围查询、需要处理哈希冲突。

适用于内存存储，依赖于良好的哈希函数以减少冲突，特别适合需要快速检索特定键值的场景。

6.数据库实例操作

6.1 表结构修改Alter

添加列

alter table student
add address varchar(200) not null;

修改字段属性

# 若修改的列中包含数据，则要考虑数据的长度
alter table student
modify address char(20) not null;

删除字段

alter table student
drop address;

6.2 表创建 Create

创建表

#创建t_tept表

CREATE TABLE t_tept(
deptno int UNSIGNED PRIMARY KEY,
dname VARCHAR(20) not null UNIQUE,
tel CHAR(4) UNIQUE
);

#创建t_emp表

CREATE TABLE t_emp(
empno INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) not null,
sex ENUM("男","女") not null,
deptno INT UNSIGNED,
hiredate DATE not null,
FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);

#创建t_message表

CREATE TABLE t_message(
id INT UNSIGNED PRIMARY KEY,
content VARCHAR(200) not null,
type ENUM("公告","通报","个人通知")not null,
create_time TIMESTAMP not null,
INDEX idx_type(type)
);

6.3 数据库选择 Use

使用数据库

use datasource;

6.4 数据查询 select

查询所有列

# 题库表中的所有信息
SELECT * FROM question;

查询特定列

# 查询所有题库名称
SELECT name as "题库名称" FROM question_bank;

# 查询题库名称和图片
SELECT name,img_url "图片" FROM question_bank;

6.5 分页查询 Limit

分页显示数据

# 查询题库，分页方式显示数据，每页显示5条
SELECT * FROM question_bank LIMIT 0,5;
SELECT * FROM question_bank LIMIT 1,5;

# 查询用户信息表中，第三页的数据，每页显示5条
SELECT * FROM userinfo LIMIT 10,5;