关系型数据库基础

最新推荐文章于 2022-11-21 20:30:04 发布

后背的书包

最新推荐文章于 2022-11-21 20:30:04 发布

阅读量584

点赞数 1

文章标签：数据库管理

本文链接：https://blog.csdn.net/clingingfiy/article/details/50460777

版权

关系型数据库管理系统简介
1.1为什么使用数据库

数据库：就是在计算机上集中存放数据的地方

数据库的集中控制的优点：降低存储数据的冗余度、更高的数据一致性，存储的数据可以分享、可以建立数据库所遵循的标准、便于维护数据的完整性，能够实现数据的安全性

1.2数据在数据库中的存储形式

层次模型（树状图）：父子之间的关系，一个父记录多个自己录

网状模型（食物链）：允许记录多个父子关系

关系模型：独立于应用程序，”表“示数据库的核心

对象模型：出现晚，维护费用高

NoSQL：

1.3关系型数据库的基本概念

实体具有的某一特性成为属性

表中的行代表实体，列代表实体的属性

·主键：用一个唯一的标识符来识别每一个行

外键：用来表达表与表之间的关系，表示表中的一到多个列到另一个表中的一到多个列的值

表与表之间的关系：一对一、一对多（以边为主表，多边为从表）、多对多

1.4关系型数据库管理系统（Relational Database Management System 简称RDBMS）

1）.本地数据库管路系统（又称为桌面型管理系统）：

2）.数据库服务器管路系统：

1.5结构化查询语言SQL

执行一个或多个操作的编码指令

SQL非过程性质（只关注其结果）：

SQL语句：数据定义语句（DDL）、数据查询语句（DQL）、数据操作语句（DML）、数据控制语句（DCL）
管理数据库和表
2.1管理数据库

创建和使用数据库：create database 数据库名;

连接到数据库：use 数据库;

删除数据库：drop database 数据库名;

2.2数据类型

整数：只能存在这个范围内的，多了会自动转换成相应的最大值

tinyint 1字节 0~255

snallint 2字节 -2^15~2^15-1

mediumint3字节

int4字节-2^31~2^31-1

bigint·8字节-2^63~2^63-1

浮点数：real可精确到第七位小数

float可精确到第十位小数

decimal可用2~17个字节来储存-10^38-1~10^38-1之间的值

numeric与decimal数据类型相同

字符串：char（固定空间）：

Varchar（根据你输入确定占用空间）：

二进制：tinyblob2550~255

blob6K0~6K

mediumblob16M 0~16M

longblob4G0~4G

逻辑：bit：存储逻辑真与假数据用0或1表示除0以为全为1

日期和时间：

year1字节YYYY1901~2155

date3字节YYYY-MM-DD1000-01-01~9999-12-31

timestamp4字节YYYY-MM-DD HH：MM：SS1970-01-01 00：00：01~2038-12-31 23：59：59

datetime8字节YYYY-MM-DD HH：MM：SS1000-01-01 00：00：01~9999-12-31 23：59：59

管理表

创建表：create table 表名(

列名 1数据类型约束条件,

列名 2数据类型约束条件

);

删除表：drop table 表名;#(有外键约束连接在一起的表不能删除。删除前必须先删除约束。)

复制表：create table 自定义表名 like 表名;（复制表的同时约束不能复制过来）

select * from 自定义表名;

修改表：alter table 表名 add 列名 varchar;

增加一列数据：alter table 表名 add 列名数据类型;

删除一列数据：alter table 表名 drop 列名 phoneno;

修改列名：alter table 表名 change colvmn 修改前的列名修改后的列名数据类型;

改编数据类型：alter table 表名 change 列名修改前的数据类型修改后的数据类型;

管理索引

修改表名：alter table 表名 rename 修改后的表名;

初建索引：create index 索引名 on 表名（列名）;

删除索引：alter table 表名 drop index 索引名;
保证数据完整性
3.1数据完整性概述（防止垃圾数据产生，程序有效执行）
实体完整性：保证每一行数据有效实现：主键约束、唯一约束、标识列属性
域完整性：保证每一列数据有效，指定列的输入有效实现：外键约束、检查约束、默认值定义、非空约束
引用完整性：保证引用的编号有效实现：外键约束
用户自定义完整性：保证自定义规则实现：触发器、存储过程
3.2数据完整性的实现
非空约束：直接再列后面加not null
主键约束：在列后面加primary key（一个表中只有唯一一个主键）
唯一约束：在列后面加unique
自动编号列：在列后面加auto_increment（根据最大的编号加‘1’。通常与主键约束配合使用）
外键约束：constraint 自定义名 foreign key(从键) references (主键);
增加主键：alter table 表名 add primary key (列名);
增加外键：alter table 主表名 add constraint forekey foreign key (主表列名) references 从表名(列名);
删除外键：alter table 主表名 drop foreign key forekey;
修改列的约束：
alter table 表名 add unique 列名;
alter table 表名 modefy 列名 not null unique;
alter table 表名 modefy 列名 value 值;
使用DML语句更改数据
4.1添加新数据
插入单行记录：insert into 表名（列名） values （列值）;(into可省略)
insert into 表名 values（列值）；
插入多行记录：insert into 表名 values （列值1,列值2）;
表数据的复制：create table 复制表 like 加入数据的表;
insert into 复制表 select *from 计入数据的表;
4.2更改已有数据
updete 表名 set 新的列表值 where 过滤条件;
软删除：update set 列名=null where 过滤条件;
4.3删除数据
delete from 表名 where 过滤条件;
删除多行：delete from 表名 where 列名1 or 列名2;(当or换成and，不删除，不报错)
使用truncate语句：truncate table 表名;(drop 删除一切结构与数据，有日志。truncate只删除数据，不删结构，不更新日志，慎用)
简单的数据查询
5.1投影操作
select 列名 from 表名;
表名前缀：select 表名.列名 from 表名;
列别名：select 列名 as a from 表名 as b;(as可省略)
select concat（列名1,'-',列名2） as ‘更改的名称’ from 表名;
排除重复数据：select distinct 列名 from 表名;
返回限定函数的查询：select 列1，列2 from 表名 limit 开始序号，返回行数;(只写一个表示多少行，从0开始)
select *from 表名 where 列值1 and 列值2;
5.2选择操作
select 列名 from 表名 where 过滤条件;
select *from 表名 where 列名='值';
select *from 表名 where is null; （或is not null）
多条件选择操作：select 列名 from 表名 where 条件1 like 条件2;
执行范围测试：select 列名 from 表名 where 列名 between 下限 and 上限;
定义集合关系：select *from 表名 where 列名=in（值集合）;
模糊查询：select *from 表名 where 列名 like '%值';(或‘值%’、如BD%n_)
处理空值数据：select *from 表名 where 列名 is not null;(或‘is null’)
排序操作：select *from 表名 order by 列名 asc;(或‘desc’)
聚合函数于分组

6.1聚合函数

     执行行和列计数（count）

             select count （*） from 表名；返回全部值，包括null值

            select count （all 列名0 from 表名；排除空值

            select count （distinct 列名） from 表名；去重

    返回列合计算（sum）

            select sum （列名） from 表名；

    获取平均值（avg）

              select avg（列名） from 表名；自动过滤null值，返回值错误。

              select avg （列名） from 表名 where 列名 is not null；

              select sum（列名）/count（liem） as 列名 from 表名；

返回最值没有MAX/MIN（all distinct   *这些规范）

                  SELECT MAX(s_grade) FROM t_stu;

                  SELECT MIN(s_grade) FROM t_stu;

数据分组（行分组）

      1 过滤分组数据：

  select 列名，集合函数 from 表名（where 条件） grounp by 列名；

  select 列名，列名 from 表名 group by 列名，列名；

    group by 后两个条件，若第一个分组完成，第二个在第一个的基础上分组。

      使用后，投影的列有两种：1.group by 后面的列

      2 使用having语句：

select 列名 from 表名 group by 列名 having 条件；

   HAVING和WHERE区别：二者都是过滤条件，WHERE运行在分组前，因此不能执行任何聚合函数。

       SQL的执行顺序
第一步：执行FROM
       第二步：WHERE条件过滤
       第三步：GROUP BY分组
       第四步：执行SELECT投影列
       第五步：HAVING条件过滤

                  HAVING是运行在分组后，只能用做聚合函数的过滤。

       第六步：执行ORDER BY 排序

       第七步：LIMITI 限定返回行
组合查询
1）.子查询
         含义：以某种方式嵌入在另一个查询中的查询（嵌套在主查询中的查询）

         如何实现完整的子查询：子查询的使用。

        查询的位置：（任何位置都可以使用）

        select :  select 列，（子查询） as 别名 from 表    ；               查询的值只能是单行单例
         例子
                   select 列1，列2 ，（select classname from class where course.id= class.id ）from class;

        from ： select 列 as 别名 from （子查询） as 别名；              查询的值可以是多行多例

       where：  select 列 from 表 where 列运算符 (子查询) ；
         在子查询中使用运算符    （运算符：1.> < = >= <= != <> （not）in ）
     1 exists：     select 列名 from 表名 where 列名 exists （select 列名 from 表名）；

                     exists是一个相关子查询，是用来测试子查询是否返回结果（结果不只是特定的结果）。

      2 all：     select 列名 from 表名 where 列名 all = （select 列名from 表名）；

                     all与比较运算符一起使用（=、<、>），测试子查询的结果集的所有行是否满足指定条件。

      3 any：    select 列名 from 表名 where 列名 = （select 列名 from 表名）；

                     any测试子查询的结果集是否有一行或多行满足条件。

       group by ：select 列 from 表 where 过滤条件 group by （子查询）；

       having ：select 列 from 表 where 过滤条件 group by （子查询）having (子查询)；

                           类似于where的用法，但只能用到聚合函数（不推荐直接使用列的比较）

      order by：select 列 from 表 where 过滤条件 group by （子查询）having (子查询)；          与select相似，只能返回单行单例

2）.分类

1.相关子查询：执行依赖于外部查询的数据，外部查询返回一行，子查询就执行一次。（select）

                         （劣势：消耗执行时间更长、效率低，数据量越多时，执行时间就会越多，成正比）；

2.非相关子查询：独立于外部查询的子查询，子查询总共执行一次，执行后将值传给外部查询（from）


3.组合查询 union（列相同，属性相同）自动去重

    含义：一种联合两条或以上查询的运算符，类似多条查询结果相组合的效果。

    使用union运算符： select 列名 from 表名 union （select 列名 from 表名）；

                                         union是纵向联合，自动去重。
     union all不去重： select 列名 from 表名 union all （select 列名 from 表名）；
联接
  内联接

      1 select * from 表名1 join 表名2；                                            输出左右两表所有数据，不管相互间是否匹配。

      2 select * from 表1 join 表2 on 表1.列名 = 表2.列名；输出两表匹配的数据。

   外联接

     1 左外联接：返回结果包括左表所有行，不匹配则为空值。

                            select * from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名；

     2 右外联接：返回结果包括右表所有行，不匹配则为空值。

                            select * from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名；

      3 全外联接：返回左右表所有行，如果一个表没数据匹配，则为空值。

                             select * from 表名1 full join 表名2 on 表名1.列名 = 表名2.列名；

                           *MY SQL 不支持full join 语法。