01.数据库编程-MySQL基础理论

1.数据库（DBS）

        就是数据的仓库，它是长期存储在计算机内，有组织的、可共享的数据的集合。

        列：也称字段，与属性、数据项、成员同义

        行：也称元组、记录

1.1数据库设计

1.需求分析	分析数据、功能和性能等需求 了解业务流程、数据的来源和去向、数据的关联性以及需要支持的查询和操作等。
2.概念设计	画E-R图（描述数据库中实体与实体之间的关系）
3.逻辑设计	将E-R图转换成表，实现从E-R模型到关系模型的转换 确定表结构、字段定义、关联关系以及定义完整性约束。
4.物理设计	将数据库模型映射到具体的物理数据库管理系统 选择适当的数据类型、创建物理表、索引设计以及确定数据存储和访问路径。
5.数据库的实施	编程测试和运行
6.数据库优化	进行性能优化和调整，提高数据库的性能和响应速度 包括索引优化、查询优化、数据归档和分区等
7.数据库维护和监控	定期维护和监控，包括备份和恢复、数据完整性检查、性能监控和优化等，以确保数据库的稳定性和可靠性

1.2关系数据库的范式

用于规范化数据库模式，减少数据冗余和提高数据的一致性和完整性。

第一范式（1NF）	要求数据库中每个表必须具有原子性，即每个列的值都是不可再分的
第二范式（2NF）	要求数据库中的每个非主键列都必须完全依赖于主键
第三范式（3NF）	要求数据库中的每个非主键列都不能传递依赖于主键，即必须直接依赖主键
巴斯-科德范式（BCNF）*	要求关系模式中的每个非平凡函数依赖都必须由候选键完全决定，而不能存在冗余的部分依赖关系（平凡函数是指依赖关系中的被依赖属性已经包含在依赖属性中，没有提供额外的信息）
第四范式（4NF）*	消除多值依赖（多值依赖指的是在一个关系模式中，存在一组属性与另一组属性之间的多对多关系，即其中一组属性的值可能对应多个另一组属性的值）
注意：过度追求范式可能导致表之间的关联操作复杂化，对查询性能产生负面影响

2.数据库管理系统（DBMS）

        用来对数据进行存储、管理等操作

3.数据库的分类

        数据库通常分为层次式数据库、网络式数据库和关系式数据库三种。而不同的数据库是按不同的数据结构来联系和组织的。

        在当今的互联网中，最常见的数据库模型主要是两种，即关系型数据库（SQL）和非关系型数据库（NoSQL，Not Only SQL）

3.4.关系型数据库

        关系型数据库是依据关系模型来创建的数据库，使用表格结构来组织和表示数据。使用SQL语言进行数据的操作和管理，可以保证数据的完整性和一致性，并且支持事务处理和ACID特性。关系型数据库通常采用客户端-服务器架构，支持多用户并发访问。

        常见的关系型数据库：Oracle(甲骨文)、DB2(IBM)、PostgreSQL、SQL Server(微软)、MySQL(甲骨文)

3.5非关系型数据库

        非关系型数据库（NoSQL）是相对于关系型数据库（SQL）而言的，它们使用不同的数据结构和数据模型来组织和存储数据。与关系型数据库不同，非关系型数据库不需要使用固定的表格结构，可以使用键值对、文档、图形或者列族的形式来存储数据，可以更好地适应非结构化或半结构化数据的存储和查询需求。

        同时，非关系型数据库可以实现水平扩展，能够处理海量数据和高并发请求。

        非关系型数据库的代表：Redis、MongoDB、Cassandra、Couchbase等。

4.MySQL介绍

        MySQL最初是由“MySQL AB”公司开发的一套关系型数据库管理系统（RDBMS-Relational Database Mangerment System）。

        MySQL不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过7万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。

        MySQL AB是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson和Michael “Monty” Widenius在瑞典创办的。

        在2008年初，Sun Microsystems收购了MySQL AB公司。在2009年，Oracle收购了Sun公司，使MySQL并入Oracle的数据库产品线。

        MySQL支持多种存储引擎，每个存储引擎都有其特定的特性和适用场景。最常用的是InnoDB

5.SQL简介

        SQL，一般发音为sequel，SQL的全称(Structured Query Language)，SQL用来和数据库打交道，完成和数据库的通信，

        SQL是一套标准。但是每一个数据库都有自己的特性别的数据库没有,当使用这个数据库特性相关的功能,这时SQL语句可能就不是标准了.(90%以上的SQL都是通用的)

5.1、SQL标准

        由国际化标准组织ISO制定的，对RDBMS(数据库服务器)的统一的操作方式。

5.2、SQL方言

        某种RDBMS不只会支持SQL标准，还会有一些自己独有的语法，这个称为方言，limit语句就只能在mysql使用

5.3、SQL的语法

        1、SQL语句可以在单行或者多行书写，以分号结尾

        2、可以使用空格和缩进增强语句的可读性

        3、MYSQL是不区分大小写，建议MYSQL的关键字大写

6、SQL的分类

6.1 DDL(data Definition language):

数据库定义语言，用来定义数据库对象：库、表、列等,包括CREATE、TRUNCATE、ALTER和DROP等命令。

1、查看mysql中的所有数据库	show databases;
	insert into 表名 values(值1,值2,.....);
2、创建数据库	create database 数据名称;
3、删除数据库	Drop database 数据库名;
4、使用数据	use 数据名称;
5、创建数据表	create table 表名(列名1  列类型1,列名2  列类型2,...);
6、查看数据库中的所有表	Show tables;
7、查看指定表的结构	Desc 表名;
8、修改表结构	Alter table 表名 ..........；
8.1、修改表名称	Alter table 表名 rename to 新表名;
8.2、向表中添加一个新的列(字段)	Alter table 表名 add [column] 列名 列类型;
8.3、新增多个列:	Alter table 表名 add  列名1 列类型1，add  列名2 列类型2,.....;
8.4、修改列名称	Alter table 表名 change 原列名 新列名 列类型;
8.5、修改列类型	Alter table 表名 modify [column] 列名 类型;
8.7、修改多列类型或者长度:	Alter table 表名 modify 列名1 类型1,modify 列名1 类型1,....;
8.8、删除指定的列	Alter table 表名 drop [column]列名;
8.9、删除多列:	Alter table 表名 drop列名1,drop列名2,....;
9、删除表	Drop table 表名;
10、删除用户	Drop user ‘用户名’@’IP地址’;

6.2 DCL(Data Control language):

数据控制语言，用来定义访问的权限和安全级别查询语言,包括GRANT和REVOKE等命令。

1.给新用户权限	Grant 权限1,权限2,..... on 数据库名.* to ‘用户名’@’IP地址’;
	Grant all on 数据名.* to ‘用户名’@’IP地址’;
2.剥夺指定用户的权限	Revoke 权限名称,... on 数据库.*  from ‘用户名’@’IP地址’; //撤销一部分权限
	Revoke all on 数据库.*  from ‘用户名’@’IP地址’; //撤销所有权限

6.3 DML(data Manipulation language):

数据操作语言，用来定义数据库记录(数据),包括INSERT、SELECT、UPDATE和DELETE等命令。

1.插入一条记录	insert into 表名(列名1,列名2,....) values(值1,值2,...);
	insert into 表名 values(值1,值2,.....);
2.查看表中所有数据	Select * from 表名;
3.修改表数据	update 表名 set 列名1=值1,列名2=值2,.... where [条件];
4.删除表数据	delete from 表名 where [条件]....
	truncate table 表名      比 DELETE 语句更快，他会重置表结构，删除表中的所有数据，无法恢复。 不支持条件删除

6.4 DQL(Data Query language):

数据查询语言，用来查询记录(数据),常用的DQL语句有：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等。

1.查询表中的所有数据	select * from 表名;
2.查询表中的部分信息	select 列名1,列名2,.. from 表名;
3.distinct列重复内容	select distinct(列名) from 表名;
4.列运算(+、-、*、/) 注意：如果列中出现null，使用null做算术元素最后的结果都是null	select 列名1*列名2 from 表名;
5.ifnull函数对null进行转换 IFNULL(列名,值);	select IFNULL(列名,值) from 表名;
6.mysql中拼接字符串函数为concat()	select CONCAT(str1,str2,...) from 表名;
7.给查询列取别名 使用as关键字	select 列名1 as 别名1,列名2 as 别名2,... from 表名;
	select 列名1 别名1,列名2 别名2,... from 表名;
8.条件查询 条件运算符:  =、>、< >= <= != <> and or between..and..,in(...) && ||等等	select * from 表名 where [条件]....
9.模糊查询   使用like关键字: like '模糊条件';  like '刘%';表示查询字符为刘员工信息 _：表示单个字符 %:表示可以是任意个字符(0-n个字符)	select * from 表名 where 列名 like '条件';
10、排序  order by 列名 升序(asc)/倒序(desc) asc:表示升序排序 desc:表示降序排序	select * from 表名 ORDER BY 列名 升序(asc)/倒序(desc);
	select * from emp order by sal desc limit 0,5; //找出员工中工资最高的五人(从零开始五个)
11、截取字符串 SUBSTRING	select SUBSTRING(列名,开始的位置,截取的长度) from 表名; //数据库下标从1开始

6.4.1聚合函数

 最终得到的结果为单行单列

count()	统计表中记录数据	select count(*) from emp where sal > 10000;        //找出工资大于10000的员工数
max()	找最大值	select * from emp where sal = (select max(sal) from emp);        //找出工资最高的是谁，运用了子查询
min()	找最小值	select * from emp where sal = (select min(sal) from emp);        //找出工资最低的是谁，运用了子查询
sum()	求和	select sum(sal) from emp;       //计算当月所有员工工资总和
avg()	求平均值	select avg(sal) from emp where deptno = 40; //计算40部门员工工资的平均值

6.4.2分组查询

group by	分组	select sum(sal),deptno from emp group by deptno; //根据员工部门进行分组,并给出每个组工资的和
		select deptno from emp group by deptno having deptno > 20 and deptno<40; //根据员工部门进行分组，只显示部门编号大于20小于40的
		select count(*),deptno from emp group by deptno; //统计每个部门人数
		select count(*),deptno from work where sal >10000 group by deptno; //统计每个部门下员工工资大于10000的人数

 6.4.3多表查询(连接查询)

join...on (没有连接上的数据不会显示)	select * from 表1 join 表2 on 条件;
	select * from emp e,dept d where e.deptno = d.deptno;        //方言 //查询所有员工信息及员工对应部门信息 select * from emp e join dept d on e.deptno = d.deptno;        //标准
left join...on (左外连接查询)	select * from 表1 left join 表2 on 条件;
	作用：把左边表中没有连接上的数据也显示出来
right join...on (右外连接查询)	select * from 表1 right join 表2 on 条件;
	作用：把右边表中没有连接上的数据也显示出来
full join...on (全外连接查询)        //MySQL无法使用

6.4.4关系表的实现

一对一	create table husband(hid int PRIMARY KEY,wid int);        //夫妻 create table wife(wid int PRIMARY KEY,hid int);
一对多	create table city(cid int PRIMARY KEY,zid int);        //城市与市民 create table citizen(zid int PRIMARY KEY,cid int);
多对多	create table good(gid int PRIMARY KEY,gname VARCHAR(10));        //商品与订单 create table order(oid int PRIMARY KEY,price int); create table gooder(gid int ,oid int);

7、数据库的数据类型

整数类型：BIT、BOOL、TINYINT、SMALLINT、MEDIUMINT、 INT、 BIGINT

浮点数类型：FLOAT、DOUBLE、DECIMAL  //money double(7,2)  99999.99

字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

char:不可变字符串 name char(10)：‘张三’

varchar:可变字符串  name varchar(10)  ‘张三’

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

类型	字节数	取值范围	备注
INT	4个字节	范围(-2147483648~2147483647)
BIGINT	8个字节	范围(+-9.22*10的18次方)
DOUBLE(m,d)双精度浮点型	8个 字节	16位精度() m总个数，d小数位	double(8,2)表示最多8位，其中有2为必须是小数
DECIMAL(m,d)定点数	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	参数m<65 是总个数，d<30且 d<m 是小数位
CHAR(N)	0-255字节	定长字符串
VARCHAR(N)	0-65535 字节	变长字符串
TEXT	0-65 535字节	长文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
BLOB	0-65 535字节	二进制形式的长文本数据	存储二进制数据,如：视频、图片等
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
Date	3	1000-01-01/9999-12-31	yyyy-MM-dd 日起值
DateTime	8	1000-01-01 00:00:00 /9999-12-31 23:59:59	yyyy-MM-dd HH:mm:ss 混合日期和时间值(基本上是原样进行存储)
TimeStamp	8	1970-01-01 00:00:00/2037 年某时	yyyyMMdd HHmmss 混合日期和时间值，时间戳(存储时按国际UTC时间格式插入数据)