飞道的博客

Part 3 数据库应用（占比 17%）——上

2023-02-09 12:06 351人阅读  评论(0)

文章目录

【后续会持续更新CDA Level I&II备考相关内容，敬请期待】

【考试大纲】

【考试内容】







【备考资料】

1、数据库相关概念

1.1、数据库概述及分类

关系型数据库（Relational Database Management System：RDBMS）是数据库应用的主流，关系型数据库以行和列的形式存储数据，这一系列的行和列被称为表，一组表组成了数据库。当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、MySQL等。

数据库是存储、调用、分析数据的仓库。数据库通常分为层次式数据库、网络式数据库和关系式数据库三种。而不同的数据库是按不同的数据结构来联系和组织的。而在当今的互联网中，最常见的数据库模型主要是两种，即关系型数据库和非关系型数据库。

操作关系型数据库使用的语言叫结构化查询语言(Structure Query Language)，简称SQL。

1.2、SQL语言的功能

1.2.1、数据查询语言（DQL：Data Query Language）

也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

1.2.2、数据操作语言（DML：Data Manipulation Language）

其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

1.2.3、数据定义语言（DDL：Data Definition Language）

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

1.2.4、事务处理语言（TPL：Transaction Process Language）

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

1.2.5、数据控制语言（DCL：Data Control Language）

它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

1.2.6、指针控制语言（CCL）

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

1.3、数据库、数据库管理系统与 SQL 之间的关系

  • 数据库（DataBase，简称DB）：长期存储在计算机内、有组织的、统一管理的相关数据的集合，可供各种用户共享，具有最小冗余度和较高的数据独立性。
  • 数据库管理系统（DataBase Management System，简称DBMS）：
    ① 用于管理数据库的软件，对数据库进行统一的管理和控制，以保证数据库的安全性和完整性；
    ② 由DBA对DB进行查询、更新、删除、修改操作的；
    ③ 用来操作和管理DB的软件，用于建立、使用和维护DB；
    ④ 用户可以通过DBMS访问DB中的数据；
    ⑤ 可使多个应用程序和用户拥有不同的方法在同时或不同时刻去建立、修改和询问DB。
  • 关系型数据库管理系统（Relational DataBase Management System，简称RDBMS）。
  • SQL（Structure Query Language，简称SQL）：
    ① 是一种结构化查询，是国际标准化组织（ISO）采纳的标准数据库语音；
    ② 是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；
    ③ 从功能上可以分为三个部分——数据定义、数据操纵和数据控制。
  • 数据库DB存储数据；数据库管理系统DBMS用于管理数据库DB的软件
  • SQL结构化查询语言；SQL就是DBMS进行数据管理的方法
  • 用户使用RDBMS的系统结构（客户端/服务器），通过客户端向服务器发送SQL语句来实现数据库的读写操作
  • MySQL是最流行的关系型数据库管理系统之一；
  • 数据库：数据库是表的集合，带有相关的数据；数据库负责数据收集、数据整合、数据调用等工作；
  • 表：一个表是多个字段的集合；
  • 字段：一个字段是一列数据，由字段名和记录组成

2、DDL数据定义语言(DDL：Data Definition Language)

2.1、数据库基本结构

数据库的基本结构分三个层次，反映了观察数据库的三种不同角度。

(1)物理数据层。它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。这些数据是原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。
(2)概念数据层。它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。它所涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。
(3)逻辑数据层。它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。

2.2、数据类型

详细可参阅以下链接，讲的很详细：

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。



这些整数类型拥有额外的选项UNSIGNED。通常，整数可以是正数或负数。如果添加UNSIGNED属性，那么范围将从0开始，而不是某个负数。

即便DATETIME和TIMESTAMP返回相同的格式，他们的工作方式很不同。在INSERT或UPDATE查询中，TIMESTAMP自动把自身设置为当前的日期和时间。TIMESTAMP也接受不同的格式，比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYYYMMDD。

2.3约束条件

数据库中的约束条件，在数据库中使用约束条件可以定义规则使得数据能够朝我们希望的方向发展，也就是可以保证数据的完整性。在数据库中有五大约束条件，分别为主键约束，非空约束，唯一约束，检查性约束，外键约束。

  • 主键约束：保证表中每行记录都不重复，通常都是在id字段上使用。主键列的数据必须唯一，且不允许为空；主键分为两种类型：单字段主键，多字段联合主键。
  • 非空约束：就是保证一个字段不能为空，不能有空值存在，如果插入空值，则会报错。

    语法：字段名 字段类型 not null；
    示例：pro_name varchar(20) not null

  • 唯一约束：要求该列的值必须是唯一的。如果想让某个字段唯一，则为它加上unique唯一约束，当插入了相同的值时会报错。

    语法：字段名 数据类型 unique
    示例：pro_name varchar(20) unique

    • 允许为空，但只能出现一个空值；
    • 一个表中可以有多个字段声明为唯一的；
    • 唯一约束确保数据表的一列或几列不出现重复值。
  • 检查性约束：检查性约束（check (字段名 in (一个合法范围))），使用检查性约束可以用来约束某个字段值的合法范围。例如用1表示男，2表示女。
    create table tablename(
	gender number(1) check (gender in(1,2)),
	//检查约束也可以自己指定
	constraint  check_name check(字段 in(1,2))
);
  • 外键约束：用于两张表之间，用来保证关联数据的完整性。外键关联时要注意的是：
    • 外键一定是主表的主键；
    • 删除表时要先删子表在删主表。

    主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表；
    从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

  • 默认约束：指定某个字段的默认值。当插入记录时如果没有明确为字段赋值时，那么系统就会自动为这个字段赋值为默认约束设定的值；

    语法：字段名 数据类型 default默认值；
    示例：pro_name varchar(10) default ‘unknown’

  • 自增字段：一个表只能有一个自增字段，自增字段必须为主键的一部分，默认情况下从1开始自增；
  • 删除约束：可以强制删除，而不用管约束条件：drop table orders cascade constraint。也可以使用级联更新和级联删除，这样在主表中进行更新或者删除操作，子表中的数据也会保证同步更新或者删除。on delete cascade|on update cascade。

2.4、创建、选用、删除数据库

--创建数据库
create database db_name;
--查看创建好的数据库
show create database db_name;
--查看所有数据库列表
show databases db_name;
--使用数据库
use db_name;
--删除数据库
drop database db_name;

2.5、创建、修改、删除表

数据库是由多个数据表构成的；每张数据表存储多个字段；每个字段由不同的字段名及记录构成，每个字段有自己的数据结构及约束条件；

  • 创建表
    CREATE TABLE table_name( 
	属性名1,数据类型, 
	属性名2,数据类型, 
	属性名n,数据类型
);
  • 修改表。在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。
    --在users表添加一个字段
alter table users add assets varchar(100); 
--修改name，将其长度改成60
alter table users modify name varchar(60); 
--删除password列
alter table users drop password;
--修改表名为employee
alter table users rename to employee;
--将name列修改为xingming
alter table employee change name xingming varchar(60); --新字段需要完整定义 
--修改字段的排列位置：modify ...first排序改为第一位
alter table employee modify name first;
--modify...after 排列顺序改到某个字段之后
alter table employee modify name varchar(20) after age;
  • 查看/删除表
    --查看该数据库中的所有表
show tables;
--删除表
drop table table_name;

3、DML数据操作语言(DML：Data Manipulation Language)

数据操作语言（DML）是对表中记录进行添加（INSERT）、更新（UPDATE）、删除（DELETE）等操作的语言，与DDL语言相同，在使用DML语言对记录信息进行操作时，也需要提前拥有相应的操作权限；

3.1、添加数据的步骤

  • 检查表结构：desc table_name；
  • 添加数据
    • 插入数据：insert into 表名（字段1,字段2,…）values(字段值11,字段值12,…),(字段值21,字段值22,…),…
    • 从外部文件导入：load data local infile “文件绝对路径” into table table_name [fields terminated by:指定分隔符]；
  • 检查数据
    • 检查导入内容：select * from 表名；
    • 检查导入数据总行数：select count（*） from 表名；

3.2、添加、修改、删除数据的语法规则

3.2.1、添加数据

插⼊数据分为插⼊⼀条数据和插⼊多条数据。插⼊数据⽤的关键字是insert into。

--插入一条数据
insert into emp(f_id,s_id,f_name,f_price) values
('a1',101,'apple',5.2);

--插入多条数据
insert into emp(f_id,s_id,f_name,f_price) values
('a1',101,'apple',5.2), 
('b1',101,'blackberry',10.2), 
('bs1',102,'orange',11.2), 
('bs2',105,'melon',8.2), 
('b5',107,'xxxx',3.6);

--外部文件导入数据
load data local infile "文件绝对路径" into table table_name [fields terminated by:指定分隔符]；
--例1,test1.txt文件中分隔符为,
load data local infile "/home/user/test1.txt" into table emp fileds terminated by ',';
--例2,test2.txt文件中分隔符为\t,导入时默认分隔符就是\t
load data local infile "/home/user/test2.txt" into table emp;

3.2.2、更新数据

更新数据记录时数据库操作最常⻅的操作，该操作可以更新表中已经存在数据记录中的值。主要包括：更新特定数据记录；更新已有数据记录。

更新table_name表中的数据，field表示表中所要更新数值的字段名字，value表示更新后的数值，CONDITION指定更新满⾜条件的特定数据记录。

--更新满足指定条件的记录，如果去掉where条件，则是更新表中所有记录
UPDATE table_name 
SET field1=value1, 
	field2=value2, 
 	field3=value3, 
WHERE CONDITION;

3.3.3、删除数据

当不需要该表时，用DROP；当仍要保留该表，但要删除所有记录时，用TRUNCATE；当要删除部分记录时，用DELETE。

删除数据记录时数据库操作最常⻅的操作，该操作可以删除表中已经存在的数据记录的值。常⽤的SQL语句是“DELETE FROM"。主要包括：删除特定数据记录；删除已有数据记录。

table_name是所要删除数据记录的表名，CONDITION指定删除满⾜条件的特定数据记录。

DELETE FROM table_name WHERE CONDITION;
TRUNCATE [TABLE] 表名;

DELETE与TRUNCATE的区别

  • DELETE关键词是属于DML语句；TRUNCATE关键词属于DDL语句。但是它们的作用都用来清空数据表中的数据；
  • DELETE关键词是逐条记录一条一条的删除记录；而TRUNCATE是直接删除原来的表，然后再重新创建一个字段结果完全一样的新表格，执行数据比DELETE快；
  • DELETE可以通过WHERE子句指定条件来删除部分数据；而TRUNCATE只能删除整体；
  • DELETE会返回删除数据的行数，但是TRUNCATE只会返回 0，没有任何意义。

转载：https://blog.csdn.net/qq_43146264/article/details/128822983
查看评论
* 以上用户言论只代表其个人观点，不代表本网站的观点或立场