数据库基础

数值类型

在数据库管理中，DML（数据操作语言）、DDL（数据定义语言）和DQL（数据查询语言）是三种重要的语言类别，各自承担不同的功能。以下是它们的详细说明：

1. DML（Data Manipulation Language，数据操作语言）：
   • 功能：用于操作数据库中的数据，包括插入、更新和删除数据。
   • 常见语句：
     • INSERT：插入新数据。
     • UPDATE：更新现有数据。
     • DELETE：删除数据。
   • 特点：这些语句直接影响数据库中的数据，而不改变表的结构。
2. DDL（Data Definition Language，数据定义语言）：
   • 功能：用于定义和修改数据库的结构，如表、索引等数据库对象的创建、修改和删除。
   • 常见语句：
     • CREATE：创建数据库对象（如表）。
     • ALTER：修改现有对象的结构。
     • DROP：删除数据库对象。
   • 特点：DDL语句用于管理数据库的结构，而非数据内容。例如，TRUNCATE TABLE虽然删除数据，但属于DDL，因为它影响表的结构（如重置自增计数器）。
3. DQL（Data Query Language，数据查询语言）DML：
   • 功能：用于从数据库中查询和检索数据。
   • 常见语句：
     • SELECT：用于从数据库中选择数据，支持各种子句如WHERE、JOIN、GROUP BY等。
   • 特点：DQL语句主要用于数据检索，允许用户根据特定条件筛选和组织数据。
     总结来说，DML负责数据操作，DDL管理数据库结构，DQL用于数据查询。这些语言共同构成了数据库管理的核心工具，帮助用户高效地管理和操作数据。

1. 数值类型

用于存储数值数据，根据数值范围和精度需求选择合适的数据类型。

• 整数类型：
  • TINYINT：1字节
  • SMALLINT：2字节
  • MEDIUMINT：3字节
  • INT：4字节
  • BIGINT：8字节
• 浮点数：
  • FLOAT：4字节，单精度浮点数
  • DOUBLE：8字节，双精度浮点数
• 定点数：
• DECIMAL：精确可变字节，支持精确小数运算

2. 日期和时间

用于存储日期和时间信息，根据精度需求选择合适的数据类型。

• DATE：存储日期（如’年-月-日’），3字节。
• TIME：存储时间（如’时:分:秒’），3字节。
• DATETIME：存储日期和时间（如’YYYY-MM-DD HH:MM:SS’），8字节。
• TIMESTAMP：时间戳 4字节，精度到秒，适合记录事件时间。

❓****时间戳可以进行运算。

3. 字符串类型

用于存储文本数据，根据字符串长度和存储需求选择合适的数据类型。

• 固定长度字符串：CHAR(n)：固定长度，n为字符数
• 可变长度字符串：VARCHAR(n)：可变长度，最长是n。n最大取值为255
• 文本类型：
  • TEXT：存储较大的文本数据（如文章、评论，网页）
  • BLOB：用于存储二进制数据（如图片、歌曲）
• Unicode字符集：
  • NCHAR、NVARCHAR、NTEXT：用于存储Unicode字符，支持多语言。

---

//mysql正常安装并启动以后，会以守护进程的方式运行服务端进程，其名字为mysqld
//连接mysql服务端
mysql -u root -p

//查看所有数据库
show databases;
//新建一个叫lyy的数据库
create database lyy;

//创建一张表
create table student (name varchar(20),sex char(1) brith date);
//删除表
drop table student;
//改表的名字
rename table pet to prt;

//获取表的结构信息(两种）
describe student;
desc student;

//展示创建表的命令   \G可以替换掉；
show create table studet\G

//获取表中内容（获取整张表）
select * from <表名>;

//插入一行（知道该行所有信息）
insert into <表名> values（列1值，列2值，....);

//插入1行（不知道所有信息，没填的是默认)  列名和列值需要对应
insert into <表名> （列1名，列2名,...) values(列1值，列2值，....));

//选择语句
select * from <表名> where 条件
select * from student where sex = 'f';
select * from student where sex <> 'f';

//判断_
//NULL是一个特殊值，既不是=，也不是不等
select NULL = NULL;
select 1 = 1；
select 1 <> 1;
select NULL is null,NULL is not null;

//组合多个条件and or not，优先级非>与>或
//列筛选
//from ---> where --->select
//where行筛选，select列选
select 表达式1，表达式2... from where 条件

//排序
select expr from where condition order by col desc;
//order by默认升序 后面加desc降序
select * from student order by name;
select * from student order by name desc;
//多个排序条件
select * from student order by name，id;
select * from student order by name desc，id;

//limit
//限制长度 几行
select col1, col2, ...from table_name limit number;
select * from student order by name limit 3;
//顺序：----select -- order by -- limit

//模糊匹配 like
//通配符 wildcard
//第一种：%  ---任何长度的字符串
select * from student where name like '%';
select * from student where name like '%fy'
//第二种：_  ---长度为1的任意字符 几个下划线匹配几个字符
select * from student where name like '_____'; 


//获取一些原始数据中不存在的统计信息
//聚集函数有：count，max，min
//select 聚集函数(表达式) from 表名 where 筛选
//顺序：from -- where --表达式 -- 聚集 -- select
//null不参与任何分类
select count(*) from student;
select max(birth) from student;
select min(id) from student;
//分组聚集
//只要select 后面有两列，就必须用分组group by
select count(*) from student group by 分组条件;
//顺序：from -- where --表达式--group by --聚集 -- select
select specis,count(specis) from pet group by specis; //3个specis处填入的内容必须是一样的
//非聚集查询：where ，一定是表中有的
//聚集以后的查询 having --聚集参数会产生原来表中没有的数据
//having：对聚集后产生的数据进行筛选
select specis,count(specis) from pet group by specis having count(specis) > 1;

--------------------------------------------------------------------------------
//from
//两张表的运算
//两张没有关系的表可以做笛卡尔积，但是不能乱用！
//先笛卡尔积再筛选：每个表都有一个列或者多个列，描述的信息是一样的
select * from pet,event where pet.name = event.name;
//内连接
select * from pet inner join event on pet.name = event.name;
//右外连接
select * from pet right outer join event on pet.name = event.name;
//全外连接,只能靠并集实现
select * from pet left outer join event on pet.name = event.name union select * from pet right outer join event on pet.name = event.name;
//pet表和自身做内连接
//没有as会报错，因为pet的名字可能存在二义性
select * from pet as t1 inner join pet as t2 on ti.name = t2.name;

在SQL中，笛卡尔积是指两个表中所有行的组合。当两个表在没有WHERE子句或ON条件的情况下进行JOIN操作时，会生成笛卡尔积的结果集。每个行都会与另一个表的每一行配对，产生新的行。

1. 理解笛卡尔积概念
   笛卡尔积是数学中的概念，指两个集合所有元素的有序对组合。在SQL中，指的是两个表中所有行的组合。
2. 生成笛卡尔积的方法
   • 使用无条件的JOIN：SELECT * FROM TableA, TableB;
   • 使用CROSS JOIN：SELECT * FROM TableA CROSS JOIN TableB;
3. 注意事项
   • 结果集的大小是两个表行数的乘积，可能导致数据量剧增。
   • 使用别名或明确指定列名以避免混淆。
   • 避免在大数据集上使用，以免影响性能。
4. 优化建议
   • 添加JOIN条件或WHERE子句以减少结果集。
   • 有条件join：使用INNER JOIN(内连接）、LEFT JOIN等带条件的连接方式。

笛卡尔积有条件连接的两个例子

1. 学生与课程的有条件连接
   • 学生集合：S = {Alice, Bob, Charlie}
   • 课程集合：K = {数学, 物理}
   • 笛卡尔积：S×K = {(Alice, 数学), (Alice, 物理), (Bob, 数学), (Bob, 物理), (Charlie, 数学), (Charlie, 物理)}
   • 条件连接（只选修数学的学生）：
     结果为：{(Alice, 数学), (Bob, 数学), (Charlie, 数学)}
2. 城市与年龄的有条件连接
   • 城市集合：C = {北京, 上海, 广州}
   • 年龄集合：A = {20, 30, 40}
   • 笛卡尔积：C×A = {(北京, 20), (北京, 30), (北京, 40), (上海, 20), (上海, 30), (上海, 40), (广州, 20), (广州, 30), (广州, 40)}
   • 条件连接（年龄 >= 30）：
     结果为：{(北京, 30), (北京, 40), (上海, 30), (上海, 40), (广州, 30), (广州, 40)}
     通过这两个例子，可以清楚地看到，条件连接如何根据特定条件筛选出符合要求的有序对。

• 左外连接和右外连接

:::

//修改表中数据
update 表名 set sex = 'm' where name = 'xx';
//在sql中两个完全一样的行是没办法区分的，两个行一样时，就都会修改
//如果两个行实在都一样，那么就引入一个与业务无关的序号进行区分


//物理删除数据
delete from 表名 where 筛选条件
delete from pet where name = 'xx';
//物理删除性能很差，尽量不要做

//逻辑删除---本质是update
//增加一列is_delete，1表示已删掉
//使用时筛选条件必须增加 where is_delete = 0

以上都是DML

---

以下调整表的结构

//增加一列
alter table event add (info1 varchar(16) info2 char(255))
//减少列
alter table 表名 drop 列名
//修改一列
alter table event change info1 info varchar

---

//实体约束----主键
create table aaa (id int,name varchar(30),works varchar(30),primary key(id));
create table aaa (id int,name varchar(30),works varchar(30),primary key(id,name));
//主键的选择尽量选取与业务无关的，
//主键尽量不要被愚蠢的产品经理发现！！！
//最合适的主键是自动增长的整数
//自动增长的一定是主键

//唯一键约束

create table aaa (id int,name varchar(30),works varchar(30),primary key(id,name),unique key(name));

域约束、实体约束和参照约束是数据库中三种主要的约束类型，它们各自有不同的应用场景和作用：

1. 域约束（Domain Constraints）：
   • 定义：域约束限制单个字段的取值范围，确保数据的合理性。
   • 例子：
     • 年龄字段只能在18到100岁之间。
     • 性别字段只能是“男”或“女”。
   • 作用：确保字段值符合预期的范围和格式，提高数据质量。
2. 实体约束（Entity Constraints）：
   • 定义：实体约束涉及主键和唯一值约束，确保表中数据的唯一性和完整性。
   • 主键：表中的一列or多列的组合，所有行在主键上的取值必须是不同的
   • 一张表最多只能有一个主键，推荐有主键。主键会影响到数据的存储结构（排序）按主键的大小进行排序。
   • 主键的选择尽量选取与业务无关的，最合适的主键是自动增长的整数 ，自动增长的一定是主键。
   • uuid vs 主键：uuid是随机生成一个不重复的id，它的问题是插入比较麻烦，而主键只需要末尾追加即可。所以自动增长的主键性能会更好。
   • 例子：
     • 学生表中学号为唯一主键，确保每个学生只有一个唯一的学号。
     • 用户表中电子邮件地址字段必须唯一，避免重复。
   • 作用：防止数据重复和无效的主键，维护表内数据的唯一性。
   • 唯一键约束：一张表可以有多个唯一键。
3. 参照约束（Referential Constraints）：
   • 定义：参照约束确保不同表之间的数据引用有效，通常通过外键实现。
   • 例子：
     • 订单表中的客户ID必须存在客户表中。
     • 选课表中的学号和课程ID分别引用学生表和课程表的主键。
   • 作用：维护数据库的完整性和一致性，防止孤立数据的出现。
     总结：

• 域约束：关注单个字段的合理性。
• 实体约束：确保表内数据的唯一性和完整性。
• 参照约束：维护表间数据的一致性和相关性。

:::