数据库笔记

1.1数据库基础知识

1.数据库概述

数据(Data)：不仅包括普通意义上的数字，还包括文字、图像、声音等。也就是说，凡是在计算机中用来描述事物的信息都可称作数据。
数据库(Database,DB)：是按照数据结构来组织、存储和管理数据的仓库。
数据库管理系统(Database Management System,DBMS)：专门用于创建和管理数据库的一套软件，介于应用程序和操作系统之间，如MySQL、Oracle等。
数据库应用程序：在很多情况下，DBMS无法满足用户对数据库的管理。此时，就需要使用数据库应用程序与DBMS进行通信、访问和管理DBMS中存储的数据。
数据库系统(DataBase System，DBS)：是指在计算机系统中引入数据库后的系统，除了数据库，还包括数据库管理系统、数据库应用程序等。
2. 数据模型
数据建模的三个阶段 数据建模的产物
概念建模阶段 概念模型
逻辑建模阶段 逻辑模型
物理建模阶段 物理模型
数据建模过程
现实世界 客观对象 现实世界中的客观对象(如:学生，班级，课程)
信息世界 概念模型 对现实世界进行抽象(如:绘制E-R图)
机器世界 逻辑模型 对概念进一步分解和细化(如:将E-R图转换为关系模型)
物理模型 描述数据如何进行实际存储(如:创建SQL脚本)
实体（Entity)是指客观存在并可相互区分的事物，例如，学生、班
级课程都是实体。
属性(Attribute)是指实体所具有的某一特性，一个实体可由若干个
属性来描述
例如，学生实体的属性有学号、学生姓名和学生性别。
属性由两部分组成，分别是属性名和属性值。
例如，学号和学生姓名是属性名，而“1、张三”这些具体值是属性
值，
联系(Relationship)是指实体与实体之间的联系，有一对一，一对多，
多对多三种情况
键(Key)：在二维表中，唯一标识某一条记录，又称为关键字、码。
字段名：是数据表的列名
字段类型：字段中保存的数据类型，如时间类型等
元组(Tuple)：二维表中的每一行数据称为一个元组，
城(Domain)：域是指属性的取值范围
**一、启动数据库服务：**net start mysql

二、登录数据库：
1、mysql -u root -p
2、mysql -u root -p <密码>
3、……

密码：123456

三、查看数据库：show databases；

查看数据库是否存在 create database if not exists 数据库名称
四、创建数据库：create database 数据库名称；

查看指定数据库show create database 数据库名称;


五、删除数据库drop database 数据库名称;

表操作

一、

1. 语法结构
   create table 表名 (
   列名1 数据类型1，
   列名2 数据类型2，
   列名3 数据类型3，

……
列名n 数据类型n
);
#最后一行不需要加逗号
Int 整数
Double 小数(指定一共多少位，小数点后多少位)，double(5,2)
Date 日期类型：只包含年月日，yyy-MM-dd
Datetime 时间类型：年月日时分秒，yyy-MM-dd HH:mm:ss
Varchar 字符串类型(指定长度)：neme
Varchar (20)

2.实例
创建一个商品表(goods)包含四个字段
(name、num、price、des):
代码：

二 、查看表
1.Show tables;

注意：show tables使用之前必须选择数据库，如果直接查看某库的表，应该使用：show tables from 库名
2.按照条件查看表
Show table[like匹配模式];
匹配模式符 “%”匹配一个或多个字符，代表任意长度的字符串。
匹配模式符 “_”仅可以匹配一个字符。

3.查看表结构
语法结构: show create table 表名；

4.删除表drop table 表名;

1.修改表名
#语法格式1
Alter table旧表名rename新表名;
#语法格式2
Rename table旧表名1 to 新表名[,旧表名2 to新表名2]…;
例如：
将表goods修改为regoods:
(1)

(2)
2.修改表选项
语法结构：Alter table 表名 表选项 = 值;
例如：把class表的表选项改为gbk格式

四、表结构的操作

注释 default null comment” ”
1.查看表内信息
#语法结构一:
Describe 表名;
#语法结构二:
Desc 表名；
#语法结构三:
Show columns from 表名;

2.修改字段名
语法结构
Alter table 数据表名change[column]旧字段名 新字段名 字段类型[字段属性];
例如：将表myclass3中的num字段名修改为id

3.修改字段类型
语法结构：
Alter table 数据表名 modify 字段名 新类型 [字段属性];
例如：将表myclassa的major字段类型改为varchar(20)

4.修改字段位置
语法结构:
Alter table 数据表名 modify 字段名1数据类型 [字段属性][first|after 字段名2];
例如:将表myclass中的id字段调整到第一个字段

5.新增字段
#语法结构一:新增一个字段，并可指定其位置
Alter table 数据表名 add 新字段名 字段类型[ first|after字段名];
#语法结构二:同时新增多个字段
Alter table数据表名 add (新增字段名1字段类型1,新增字段名2字段类型2…);
注意：同时新增多个字段的时候不可以指定位置
例如:在表myclass中增加一个人数字段(count)到第一列

在表myclass中增加一个专业，字段(major)到最后一列

6.删除字段
语法结构:
Alter table 数据表名 drop 字段名；

E-R图：
1、E-R图 称为 实体-联系图
2、画法：
实体：用矩形框表示，实体名写在框内。
属性：用椭圆框表示，属性名写在框内，用连线将实体与属性连接。
联系：用菱形框表示，将联系名写在框内，用连线将相关的实体连接
E-R图
1、E-R图称为实体-联系图
2 画法：
实体：用矩形框表示，实体名写在框内。
属性：用椭圆框表示，属性名写在框内，用连线将属性与实体连接。
联系：用菱形框表示，将联系名写在框内，用连线将相关实体联系起来。
关键字
数据库：database
数据表：table
显示：show
创建：create
删除：drop
修改操作：alter
重命名：rename
列：column
修改字段名：change
修改字段类型：modify
位置：首位:first
之后：after
添加：add
五，数据操作
<一>添加数据
1.为所有字段添加数据
语法结构：
insert [into] 数据表名 values[值1，值2，值3]….）；
例如：在表goods中添加一条数据：
1001，向日葵，50，送给好朋友
注意：数据信息严格按照数据表结构（字段的位置）插入对应的值.

值列表“值1[,值2]…”中多个值之间使用逗号分隔。
例如:在表goods中再添加一条数据：
2.为部分字段添加数据
语法结构1
Insert 数据表名(字段名1[,字段名2]…)values(值1[,值2]…);
语法结构2
Insert 数据表名set 字段名1 = 值1[,字段名2=值2]…;
例如:
(1)编号为3，名字为白百合(格式1)

(2)编号为4，名字为满天星，价格66(格式2)

注意:语法结构2一次只能增加一条数据
3.一次添加多行数据
Insert 数据表名 ([字段列表])values([值列表]);
<二> 查询数据
1.查看全部字段数据
语法结构：
select*from 数据表名；
注意： *是一个通配符，表示全部字段
例如:

2，查看部分字段数据
Select{字段名1，字段名2，…}from 数据表名;
例如:查看表goods中的商品名称和描述

3，简单条件查询
语法结构:
Select * | [字段名1，字段名2…] from 数据表名 where 字段名=值
例如:查询表goods中价格为50元的商品所有信息

查询表goods中价格为50元的商品名称及描述

三、修改数据
语法格式:
Update 数据表名 set 字段名1 = 值1 [,字段名2= 值2,…] [where字段名=值];
例如:
将表goods中的白百合改成百合
Update goods set name = 百合 where name = 白百合;

四、删除数据
语法结构:
Delete from 数据表名[where 字段名=值];
例如:删除id的第五条

数据类型:
<一>数字类型
1.整数类型

无符号数据类型:使用unsigned关键字修饰.

示例:
“int”有符号的int类型;
“int unsigned”无符号的int类型
例如:
1.建1表myint(有符号,无符号,有符号,无符号)
2.插入数据
正确示范

错误示范

2.浮点数类型:
float保留六到七位小数，double保留十四到十五位
建表myfloat,插入数据

3.定点数类型
通过decimal(m,d)设置位数和精度
M 代表数字总位数(不包括‘.’和‘-’)，不设置默认十位
D 代表小数点后的位数，默认取零
建表mydecimal,插入数据

Deciimal(5,2)------ (-999.99,999.99)
<二>字符串类型
转义字符:在字符前加‘\’

“\”
Bull
null
<三>时间和日期类型

建表myyear,插入2021，插入89，插入19

Date系统获取当天的date
#设置d字段的数据类型为DATE
CREATE TABLE my_date (d DATE);
#插入日期数据
INSERT INTO my_date VALUES(2020-01-21)
#插入当前系统日期
INSERT INTO my_date VALUES(CURRENT_DATE);
#插入当前系统日期
INSERT INTO my_date VALUES(NOW();
time类型:表示时间值
表达方式1:HHMMSS(几小时几分几秒)
表达方式2:D HHMMSS(几天 几小时几分几秒)
表达方式3: now () create_time
1D = 24HH

约束

默认约束:
默认值:某一种数据会经常性的出现某个具体的值,可以在一开始就指定好,在需要真实数据的时候，用户就可以选择性使用默认值
默认值关键字:default
默认值的生效:使用,在数据进行插入的时候,不给改字段赋值
想要使用默认值,可以不一定去指定列表,故意不使用字段列表:可以
使用default关键字代替值
非空约束
列属性:真正约束字段的数据类型,但是数据类型的约束很单一,需要有一些额外的约束
列属性有很多;null/not null,default,primary key, unique key,auto_increment,comment
空属性
两个值:null(默认的)和not null(不为空)
虽然默认的数据库基本都是字段为空,但是实际上在真实开发的时候尽可能的要保证所用的数据都不应该为空:空数据没有意义;空数据没有办法参与运算
列描述:comment,没有实际含义:是专门用来描述字段,会根据表创建语句保存:用来给程序员(数据库管理员)来进行了解的
唯一约束
唯一键:一张表往往有很多字段需要具有唯一性数据不能重复，但一张表中只能有一个主键(uniquekey)就可以解决表中有多个字段需要唯一性约束的问题.
唯一键的本质与主键差不多:唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)
增加唯一键，基本与主键差不多(三种方案)
1.在创建表的时候，字段之后直接跟unique/inique key
2.在所有的字段之后增加 unique key(字段列表);–复合唯一键
3.在创建表之后增加唯一键
唯一键约束
唯一键与主键本质相同:唯一的区别就是唯一键默认允许为空,而且是多个为空.
如果唯一键也不允许为空:与主键的约束作用是一致的
更新唯一键&删除唯一键
更新唯一键:先删除后新增(唯一键可以有多个,可以不删除).
删除唯一键
Alter table 表名 drop index 索引名字; (唯一键默认的使用字段名作为索引名字)
主键约束:主键对应的字段中的数据不允许重复,一旦重复数据操作失败(只限于增和改)
字段属性:主键，唯一键和自增长
主键(primary key):一张表中只能有一个字段可以使用对应的键用来唯一的约束该字段里面的的数据，不能重复
一张表只能有最多一个主键
增加主键
SQL操作中有很多方式可以给表增加主键:大体分为三种:
1.在创建表的时候,直接在字段之后跟primary key关键字(主键本身不允许为空)
优点:非常直接:缺点 只能使用一个字段作为主键
2.在创建表的时候,在所有的字段之后,使用primary key(主键字段列表)来创建主键(如果有多个字段做主键,可以是复合主键)

3.当表已经创建好之后,额外追加主键:可以通过修改表字段属性,也可以直接追加
Alter table 表名 add primary key(字段列表);
前提:表中字段对应的数据本身是独立的(不重复)
主键约束:主键对应的字段中的数据不允许重复,一旦重复数据操作失败(只限于增和改)
更新主键&删除主键
没用办法更新主键:主键必须先删除才能增加
删除主键:Alter table 表名 drop primary key;
主键分类:
在实际创建表的过程中,很少使用真实业务数据作为主键字段(业务主键,如学号,课程号);大部分的时候是使用逻辑性的字段(字段没有业务含义,值是什么都没有关系),将这种主键称之为逻辑主键

数据库范式
范式是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题：保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储：终极目标是为了减少数据的冗余
范式：是一种分层结构的规范，分为六层：每一次层都比上一层更加严格：若要满足下一层范式,前提是满足上一层范式
六层范式：1NF2NF3NE. 6NF，1NF是最底层，要求最低；6NF最高层，最严格
Mysql属于关系型数据库：有空间浪费：也是致力于节省存储空间：与范式所有解决的问题不谋而合：在设计数据库的时候，会利用到范式来指导设计.
但是数据库不单是要解决空间问题，要保证效率问题：范式只为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现：一般情况下，只有前三种范式需要满足
范式在数据库的设计当中是有指导意义：但是不是强制规范为了避免不规范的数据库出现数据冗余,造成插入，删除，更新，操作异常等情况，就要满足一定的规范化要求,这就是范式:normal format
1NF:
第一范式:在设计表储存数据的时候,如果表中设计的字段储存的数据,在取出来使用之前还需要额外的处理,那么说表的设计不满足第一范式,第一范式要求字段的数据具有原子性:不可再分
2NF:
在数据表设计的过程中,如果有复合主键(多字段主键),且表中有字段并不是由整个主键来确定,而是依赖主键中的某个字段(主键的部分):存在字段依赖主键的部分的问题称之为部分依赖:第二范式就是解决表设计不允许出现部分依赖
3NF
要满足第三范式，必须满足第二范式第三范式：理论上讲，应该一张表中的所有字段都应该直接依赖主键(逻辑主键：代表的是业务主键)，如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键：把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖.第三范式就是要解决传递依赖的问题
数据库设计
一、 数据库设计概述：
1，在需求分析阶段，数据库设计人员需要分析用户的需求， 将分析结果记录下来，形成需求分析报告。
口在这个阶段中，双方需要进行深入的沟通，以避免理解不准确导致后续的工作出现问题。
口在需求分析中有许多琐碎、耗时的工作，常见的工作有：收集数据、解决冲突、为数据形成标准。
收集数据：
一个企业内的数据可能分散、零碎，由不同人员负责管理的。
为了使用数据库系统管理这些数据，需要尽可能多地收集数据，并理解企业的业务过程和数据处理流程理解数据处理的性能需求。
可以利用数据流图等工具辅助分析与理解。
解决冲突：
解决冲突，包括命名冲突(同名异义、异名同义)、属性冲突、结构冲突。例如，商品库存数量是否包含已下订单未出库数量；到货数量和入库数量以哪一个为淮；用户名和昵称、真实姓名如何区分；性别使用男、女还是0、1或f、m来表示。
为数据形成标准：
为数据形成一些标准，如商品编号一共有多少位，未来是否会增加位数，每一位的含义是什么。
订单编号按照什么规则生成，如何避免编号重复，编号中包含哪些信息，是否加入一些随机数防止被推测等。
2，在概念数据库设计阶段，将对用户的需求进行综合、归纳、 抽象，形成概念模型。
口概念模型使设计人员摆脱数据库系统的具体技术问题，将精力集中在分析数据及数据之间联系等方面。
口一般通过绘制E-R图，直观呈现数据库设计人员对用户需求的理解。
3，逻辑数据库设计面向数据库系统，在概念数据库设计中完成E-R图等成果后，将其转换为DBMS支持的数据模型(如关系模型)，完成实体、属性 联系的转换。
口在进行逻辑数据库设计时，应遵循一些规范化理论，如范式
口不规范的设计可能会导致数据库出现大量冗余、插入异常、删除异常等问题。
4，物理数据库设计阶段需要确定数据库的存储结构、 文件类型等。
口通常DBMS为了保证其独立性与可移植性，承担了大部分任务。
口数据库设计人员只需要考虑硬件、操作系统的特性，为数据表选择合适的存储引擎，为字段选择合适的数据类型等，以及评估磁盘空间需求等工作。
5，数据库实施就是将前面那些工作的成果实施起来。
口比如使用SQL语句创建数据库、数据表，编写与调试应用程序等。
6，数据库运行和维护就是将数据库系统正式投入运行。
口 在运行后进行一些维护、调整、备份、升级等工作。
二、 数据建模工具：
在数据库设计过程中，对于业务复杂、修改频繁的场合，手工绘制E-R图显得非常低效，这时可以利用数据建模工具提高效率。

一、数据操作

1. 复制表结构
语法结构:
create table 新表名 like 旧表名;
例:在mydb数据库中创建一个和sh_goods相同的表my_goods

注意:带有数据库的数据表名的写法:shop.sh_goods
2. 复制数据:
语法结构:
Insert 数据表名1 select*|字段列表 from 数据表名2;
例如:将shop中sh_goods中的数据复制到mydb中my_goods
Insert mydb.my_goods selectfrom shop.sh_goods;

3. 去除重复数据:
语法结构:
Select [select选项]|字段列表 from 数据表;
select选项包含all和distinct,其中默认为all,当设置为distinct时，表示去重复记录。
补充:使用distinct去计数:count(distinct字段名)
Select count(distinct字段名)*|字段列表 from 数据表;

例如:查询sh_goods表中的keyword信息,不显示重复信息。

二、排序与限量
1.排序
语法结构:
Select [select选项] *|[字段列表] from 数据表 order by 字段名 [asc|desc];
注意:asc表示升序, desc表示降序,order by默认值为 asc
例如:查询sh_goods表中所有商品信息,价格从大到小排列

2.多字段排序
语法结构:
Select [select选项] *|[字段列表] from 数据表 order by 字段名1[,字段名2…][asc|desc];
例如:按照category_id和价格从小到大排序

3.限量(数据记录数)
A语法结构:
Select[select选项] *|字段列表 from 数据表名 [where条件表达式] [order by 字段 ase|desc] limit记录数;
例如:查询sh_goods表中价格低于999元中最贵的前三件商品的编号商品名称以及价格

B：限制范围:limit 参数1 ,参数2
参数1:限制数据的起始位置索引;
参数2:限制数据的条数
例如:显示第三条到第九条数据: limit 2,7
注意:一般是综合排序使用
C：修改和删除数据的限量:
Update
delete
例如 将最便宜的前两条商品的库存(stock)更新为1000
update sh_goods set stock = 1000 order by price limit 2;

分组

A 单组统计
语法结构:
Select[select 选项] 字段列表 from 数据表名 [where条件表达式] group by;
注意:select查询的字段列表只能是group by分组的字段或使用了聚合函数的非分组字段
聚合函数

例如:查询每个分类下商品分类ID和最高价格()

C多分组统计
语法结构
Select[select 选项] 字段列表 from 数据表名 [where条件表达式] group by 字段名1,[字段名2…] order by 字段名1,[ASC|DESC]],[字段名2[ASC|DESC]…];
例如:以评分score降序分组后，再以评论数comment_count升序排序分组

例:查询表sh_goods里以评分score降序分组并返回同一分组中的所有商品名称
select score,group_concat(name) from sh_goods group by score order by score desc

D回溯统计(with rollup)
在根据指定字段分组后,系统又自动对分组的字段向上进行了一次新的统计并产生一个新的统计数据,且该数据对应的分组字段值为null
语法结构
Select[select 选项] 字段列表 from 数据表名 [where条件表达式] group by 字段名1,[ASC|DESC]],[字段名2[ASC|DESC]] with rollup;
E 条件筛选:
语法结构
Select[select 选项] 字段列表 from 数据表名 [where条件表达式] group by 字段名[with rollup] having条件表达式;
例 获取评分和评论数不同的情况下，含有两件商品的对应商品ID

F 使用别名
语法结构
#字段设置别名
Select 字段1[as] 别名1,字段2[AS] 别名2 [,…] from 表名
#表设置别名
Select 表别名.字段 [,…] from 表名 [AS] 表别名
例:
Select category_id as cid,MAX(price) max_price from sh_goods group by cid having cid = 3 or cid = 6;

运算符

1.算数运算符适用于数值类型的数据,通常应用在SELECT查询结果的字段中使用,在where条件表达式中应用较少

算术运算符的注意事项
1.无符号的加减乘除运算
运算符+.-和*的操作数都是无符号整型,则运算结果也是无符号整型
2.有符号的减法运算结果

3.含有精度的运算
浮点数进行加减运算时,运算结果中的精度(小数点后的位数)等于参与运算的操作数的最大精度
4.‘/’运算
/ 在mysql中用于除法操作,且运算结果使用浮点数表示
浮点数的精度等于被除数的精度加上系统变量div_precision_increment设置的除法精度增长值(默认值为4)
除法运算中除数如果为0,则系统显示的执行结果为null
5.NULL参与算数运算
在算数运算中,null是一个特殊的值,它参与的算数运算结果均为null
6.DIV与MOD运算符
在mysql中,运算符DIV与/都能实现除法运算,区别在于前者的除法运算结果会去掉小数部分,只返回整数部分
Mysql中的运算符MOD与%功能相同,都用于取模运算
数学运算的函数

比较运算符
%表示任意多个字符
_表示一个任意字符
范围查询
In 表示在一个连续的范围内
Between…and…表示在一个连续的范围内
空判断
注意:null与”是不同的
逻辑运算符
And,or,not
赋值运算符与位运算符
赋值运算符
Mysql中’=’是一个比较特殊的运算符,既可以用于比较数据是否相等,又可以表示赋值.因此,mysql为了避免系统分不清楚运算符’=’表示赋值还是比较的含义,特意增加一个符号,=用于表示赋值运算

在mysql中insert和update…set中出现的运算符=都会被认为是赋值运算符
位运算符

位运算的函数

运算符优先级