数据库的基本知识

向云端UP

已于 2025-03-24 07:52:35 修改

阅读量1.5k

点赞数 14

分类专栏：数据库文章标签：数据库 oracle sql

于 2025-03-15 18:08:59 首次发布

本文链接：https://blog.youkuaiyun.com/u012121721/article/details/146276460

版权

数据库专栏收录该内容

8 篇文章

订阅专栏

数据库的基本知识
 数据库的高阶知识

一、创建数据库和数据表

1.1 创建数据库相关代码

#创建数据库相关代码
CREATE DATABASE test;  -- 创建名为test的数据库
show CREATE DATABASE test; -- 查看创建好的数据库
show DATABASES;   -- 查看所有的数据库列表
use test;  -- 使用创建好的数据库
drop DATABASE test; -- 删除数据库

1.2 创建数据表

数据库是由多个数据表构成的，每张数据表中存储着不同的字段，每个字段由不同的字段名和记录构成，并且每个字段有自己的数据结构和约束条件。
在之前创建的test数据库中，创建一个员工信息表emp。

部门ID	部门名称	员工数
p01	财务部	20
p02	销售部	100
p03	运营部	40

-- 使用test数据库
use test;
-- 创建员工信息表
CREATE TABLE emp(
			depid char(3),
			depname varchar(20),
			peoplecount int);			
-- 查看表是否创建成功
show tables
-- 删除数据表
drop table emp

1.3 约束条件

约束是在表上强制执行的数据检验规则，用来保证创建的表的数据完整和正确。
mysql数据库常用的约束条件：

约束条件	说明	语法
PRIMARY KEY	主键约束	字段名数据类型 PRIMARY KEY
NOT NULL	非空约束	字段名数据类型 NOT NULL
UNIQUE	唯一约束	字段名数据类型 UNIQUE
AUTO_INCREMENT	自增字段	字段名数据类型 AUTO_INCREMENT
DEFAULT	默认值	字段名数据类型 DEFAULT 默认值

1.3.1 主键约束

主键，一列（或一组列），其值能够唯一区分表中每个行。主键约束：保证表中每行记录都不重复。
主键，又称为"主码”，是数据表中一列或多列的组合。主键的作用就是用来表示一个特定的行，没有主键，更新或者删除表中特定的行会比较困难，因为没有安全的方法保证只涉及相关的行。使用主键，能够惟一地标识表中的一条记录，并且可以结合外键来定义不同数据表之间的关系，还可以加快数据库查询的速度。
原则上，表中的任何列都可以作为主键，只需要满足以下条件：

任何两行都不具有相同的主键值（唯一）
每个行都必须具有一个主键值，且主键值不允许NULL值（非空）

主键分为两种类型:单字段主键和多字段主键。对于单字段主键，主键通常定义在表的一列上，比如:对于下面的员工表来说部门ID就可以作为主键。对于多字段主键，必须保证构成主键的所有列值的组合是唯一的(单个列值可以不唯一)。

# 主键约束
-- 单字段主键
CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20),
			peoplecount int);
-- drop table emp		
-- 多字段主键
CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20),
			peoplecount int,
			primary key (depid,depname));

1.3.2 非空约束

非空约束，指的是字段的值不能为空。语法：字段名字段类型 not null

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null ,
			peoplecount int);

1.3.3 唯一性约束

唯一性约束，要求该列的值必须是唯一的，语法：字段名数据类型 unique；

允许为空，但只能出现一个空值；
一个表中可以有多个字段声明为唯一值；
唯一约束确保数据表的一列或几列不出现重复值

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null ,
			peoplecount int unique
			);

异同点	主键	唯一键
不同点	设置为主键的字段不能为NULL	可以为NULL
不同点	一张表有且仅有一个主键	可以有多个唯一键
相同点	设置为主键的字段值不能重复	唯一键字段值不能重复
相同点	构造为主键的字段可以是多个	唯一键字段值可以是多个

1.3.4 默认约束

默认约束，指定某个字段的默认值。如果新插入一条记录时没有为默认约束字段赋值，那么系统就回自动为这个字段赋值为默认约束设定的值。语法：字段名数据类型 default 默认值

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null DEFAULT '-' ,
			peoplecount int unique
			);

1.3.5 自增字段

自增字段：一个表只能有一个自增字段，自增字段必须为主键的一部分，默认情况下从1开始自增。
创建含有各种约束条件的数据表

# 创建一个含有各种约束条件的数据表
create table exp (id int primary key auto_increment,
				  fname varchar(4) not null,
				  math int default 0,
				  minmax float unique);

1.4 手工建表

create table fruits(
					f_id char(10) not null,
					s_id int not null default 0,
					f_name char(255),
					f_price decimal(8,2) not null,
					primary key(f_id));
insert into fruits(f_id,s_id,f_name,f_price) values 
				("a1",101,"apple",5.2),
				("a2",102,"orange",10.2);

在这里插入图片描述

二、数据查询功能

2.1 sql 查询的7个关键词

在实际工作中，使用最多的就是数据库的查询功能了，在数据查询过程中，通常会用到以下7个核心关键词：

select <select_list>
from <table_list>
[where <where_condition>]
[group by <group_by_list>]
[having <having_condition>]
[order by <order_by_list> asc|desc]
[limit <limit_number>]

其中，select 和 from 两个关键词时必选项，也就是说，只要是数据查询功能，都需要使用这两个关键词，其他的关键词，可以根据查询实际情况有针对性的选择和使用。二手房数据链接 ：https://pan.quark.cn/s/586cf3750a24

create table second_hand_houses(
							frame char(10) not null,
							ftype char(255),
							size int,
							region char(10) not null,
							floor int,
							drection char(10) not null,
							bulit_data char(10) not null,
							price_unit int)

2.1.1 select

select主要作用是告诉数据库，在提取数据时需要选择的字段名称。这里的字段名称可以是数据表已经有的名称，也可以是依据已有名称衍生出来的名称。

2.1.2 from

from主要作用就是告诉数据库，在提取数据时需要选择的数据源（数据表）名称，通常select和from两个关键词一起使用

2.1.3 where

#查询北京所有2室1厅的二手房信息
select * from second_hand_houses where ftype = '2室1厅'

#查询朝阳区房价单价在10000以上的二手房名称，户型，面积，朝向，楼层
select frame,ftype,size,drection,floor
from second_hand_houses
where region ='朝阳区'
and price_unit>50000

2.1.4 group by

主要用于统计时的分组操作，通常和聚合函数一起搭配使用，主要有以下五种：

聚合函数	功能	说明
count	用于统计记录数	函数内传递*，表示统计数据表的记录数；如果是某个字段，则统计字段的记录数，如果需要去重，需在字段前加关键字distinct
sum	用于数值型数据的求和	需要注意的是变量求和之后，仍然具有业务含义
avg	用于数值型数据的均值计算	同上
min	用于计算数值型数据的最小值	-
max	用于计算数值型数据的最大值	-

#查询各地区下二手房数量、总的可居住面积、最大单价
select region,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region

#查询各地区下每年二手房数量、总的可居住面积、最大单价
select region,bulit_data,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region,bulit_data

2.1.5 having

HAVING 用于对分组（GROUP BY）后的结果进行过滤。它通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN 等）一起使用，用于筛选满足条件的分组。
与 WHERE 的区别：

WHERE：在分组之前过滤数据，作用于单行记录。
HAVING：在分组之后过滤数据，作用于分组结果。

#按照地区、户型、楼层和阳台朝向分组统计东城区、朝阳区二手房的平均单价和总数量
select region,ftype,floor,drection,avg(price_unit) as avg_price,count(*) as counts
from second_hand_houses
where region in ("东城区","朝阳区")
group by region,ftype,floor,drection
having avg_price >50000;

2.1.6 order by

用于对查询结果进行排序。你可以指定一个或多个字段，并选择升序（ASC，默认）或降序（DESC）排列。

#按面积降序，单价升序的方式查询所有2室1厅的二手房信息
select * from second_hand_houses
where ftype ="2室1厅"
order by size desc,price_unit

2.1.7 limit

用于限定查询返回的记录行数，可以选择返回前几行，中间几行，或者是末尾几行，需要注意的是这个关键词后面最多可以写两个整数型的值：
limit n：表示返回查询结果的前n行
limit m,n：表示从第m+1行开始，连续返回n行的数据

#查询建筑时间最悠久的前5套二手房
select * from second_hand_houses
order by bulit_data
limit 5

#查询出东城区，1980年建的二手房，并且单价在2-3名
select * from second_hand_houses
where region ="丰台区" and bulit_data = "2008"
order by price_unit desc
limit 1,2

2.2 with语句

with语句是 SQL 中用于定义临时结果集的一种语法。它可以让复杂的查询变得更清晰、更易读，并且可以重复使用这些临时结果集。WITH 语句通常用于简化嵌套子查询，或者在递归查询中处理层次结构数据。

WITH cte_name (column1, column2, ...) AS (
    -- 子查询
    SELECT ...
)
SELECT ... FROM cte_name;

cte_name: CTE 的名称，可以在后续查询中引用。
(column1, column2, ...): 可选项，为 CTE 的结果集指定列名。如果省略，列名将来自子查询的 SELECT 语句。
AS (...): 定义 CTE 的子查询。

使用场景
1、简化复杂查询：
当查询中有多个嵌套子查询时，可以使用 WITH 语句将每个子查询提取为 CTE，使查询更易读。
2、递归查询：
WITH 语句支持递归查询，常用于处理层次结构数据（如组织结构、树形结构等）。
3、重复使用结果集：
如果某个子查询在多个地方被使用，可以将其定义为 CTE，避免重复编写相同的子查询。

WITH order_totals AS (
    SELECT 
        customer_id, 
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.customer_name, 
    ot.total_amount
FROM customers c
LEFT JOIN order_totals ot ON c.customer_id = ot.customer_id;

三、数据库的增删改

3.1 数据库的增操作

数据库的增操作主要涉及数据库的增加、数据表的增加、表记录增加以及表字段增加等。数据库的增加非常简单，就是新创建一个数据库;表记录的增加指的就是新增表的数据行，可以是在已有表的基础上增加记录，也可以是将查询结果保存为新的表;字段的增加是指在原有表的基础上新增一些字段。
（1）新建数据库

create database <database_name>

（2）新建表

create table table_name (column_name column_type)

（3）在原有表的基础上增加记录
在表中增加记录的前提是保证原数据表一定存在。

#创建学生信息表
CREATE TABLE stu_info (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键
    iname VARCHAR(20),                 -- 学生姓名
    gender CHAR(1),                    -- 性别（M: 男, F: 女）
    department VARCHAR(10),            -- 所属院系
    age INT,                           -- 年龄
    province VARCHAR(10),              -- 省份
    email VARCHAR(50),                 -- 邮箱
    mobilephone CHAR(11)               -- 手机号（11位）
);
# create table stu_order like stu_info 创建相似的表
INSERT INTO stu_info (iname, gender, department, age, province, email, mobilephone)
VALUES
('周八', 'M', '计算机', 21, '北京', 'zhouba@example.com', '13800138005'),
('武勇', 'M', '生物', 21, '浙江', 'wuyong@example.com', '13800138049');

（4）增加列

alter table stu_info add column hobby char(20) default 0

3.2 数据库的删操作

数据库的删操作主要包含数据库的删除、数据表的删除、表记录删除、字段删除类型。数据库的删除就是直接删掉整个数据库;表记录的删除是指按照某些条件删除数据表中的记录，或者直接清空数据表的所有记录;字段的删除与字段的增加恰好相反，就是根据实际情况将表中某些字段删掉。
（1）数据库删除

drop database <databsae_name>

（2）数据表删除

drop table <table_name>

（3）按条件删除表记录

#删除学生表在，名为“张三“和”李四”的记录
delete from stu_info  #保留自增字段记录；如果需要删除部分数据或需要事务支持，使用 DELETE。
where iname in("张三","李四") 

#删除学生表中email 字段
alter table stu_info drop email

#truncate #彻底清除字段表；如果需要快速清空整个表，使用 TRUNCATE
truncate table stu_info

3.3 数据库的改操作

数据库的改操作主要是指修改表中错误记录、修改字段类型以及修改表名称、字段名称等。
(1) 修改表名

alter table stu_info rename to stu_summary

（2）修改字段

alter table stu_info rename to stu_summary

（3）修改字段类型

alter table stu_summary change age age varchar(10)
alter table stu_summary change gender sex varchar(10)

alter table stu_summary modify column age varchar(10)
desc stu_summary #查看表字段类型

（4）修改单个记录

update stu_summary set email='zhangyong@163.com' where iname ='张勇'