数据库相关概念

1. 数据库：存储和管理数据的仓库，数据有组织进行存储（Data Base)
   特点：
   （1）持久化存储数据，数据库本质上是一个文件系统。
   （2）方便存储和管理数据
   （3）使用同一方式操作数据库

2. 数据库管理系统：管理数据库的大型软件（DataBase Management System)

Oracle,MySQL,SQLServer,PostgreSQL,DB2,SQLite,MariaDB

3. SQL:结构化查询语言(操作关系型数据库的编程语言）
   定义操作所有关系型数据库的统一标准
   方言：对于同一个需求，每一种数据库的操作方式出现不一样的地方。

MySQL数据模型

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，是由多张能够相互连接的二维表组成的数据库。
优点：
（1）都是使用表结构，格式一致，易于维护。
（2）通用SQL语言操作，使用方便。
（3）数据存储在磁盘中，安全。

SQL通用语法

1.SQL语句可以单行或者多行书写，并且以分号结尾。
2.不区分大小写，关键字建议用大写
3.注释：单行注释 – 注释内容 或者#注释内容（MySQL特有）
多行注释：/* */
4.分类：

DDL：数据定义语言	定义数据库对象：数据库，表，列
DML：数据操作语言	对数据库中表的数据进行增删改
DQL：数据查询语言	查询数据库中表的记录（数据）
DCL：数据控制语言	定义数据库的访问权限和安全级别，创建对象

SQL语句全解

DDL

DDL操作数据库：

查询	show databases;
创建数据库	create database 数据库名称；
创建数据库，如果不存在则创建	create database if not exists 数据库名称；
删除数据库	drop database 数据库名称；
删除数据库，如果存在	drop database if exists 数据库名称;
使用数据库	use 数据库名称;
查看当前使用的数据库	select database();

DDL操作表：

查询当前数据库下表的名称	show tables
查询表的结构	desc 表的名称
创建表	create table 表名(字段名1 数据类型，字段2 数据类型2)；
删除表	drop table 表名； drop table if exists 表名；
修改表名	alter table 表名rename to 新表名；
添加一列	alter table 表名 add 列名 数据类型；
修改数据类型	alter table 表名 modify 列名 新数据类型；
添加列名和数据类型	alter table 表名 change 列名 新列名 新数据类型
删除列	alter table 表名 drop 列名

score double(总长度，小数点后保留的位数）；

保存0-100 2位小数
score double(5,2);

data只可显示出年月日

name char(10);
name varchar(10);
//当张三同时存入俩个name中的时候，
//第一个占十个字符空间，后面按零补齐。
//第二个占据俩个字符存储空间。

(1）char的存储性能高于varchar
(2) char 浪费空间

图形化客户端工具：Navicat这套全面的前端工具为数据库的管理，开发和维护提供了一款直观而且强大的图形界面。

DML

添加数据：

给指定的列添加数据	Insert into 表名（列名1，列名2） values(值1，值2);
所有的列添加数据	insert into 表名 values(值1，值2）；
批量添加数据	insert into 表名（列名1，列名2） values(值1，值2），（值1，值2）；
修改表数据	update 表名 set 列名1=值1，列名2=值2 where 条件;
删除表数据	delete from 表名 where 条件；
如果updata语句没有加where条件，会把表中数据全部修改。

DQL

select	字段列表
from	表名列表
where	条件列表
group by	分组字段
having	分组后条件
order by	排序字段
limit	分页设定

基础查询

select name,age from stu;
//查询多个字段
select *from stu;
//列名的列表可以代替所有列的数据，建议写上所有列名
select distinct address from stu;
//去除重复记录
select math as 数学成绩 from stu;
//as起别名，当前as也可以省略，但是需要有空格

条件查询

select 字段列表 from 表名 where 条件列表;

符号	功能
>	大于
=	等于
<> !=	不等于
between …and	在某个范围内都包括
in()	多选一
like 占位符	模糊查询 _单个任意字符 %多个任意字符
is null	是null
is not null	不是null
and &&	并且
or	或者

注意：null的值不能用= ！= ,需要使用is 或者 is not

//查询入学日期在'1998-09-01'到'1999-09-08'的学员信息
select *from stu while date between '1998-09-01' to '1999-09-08';
//查询姓'马'的学员信息
select *from stu where name like '马%';
//查询第二个字'化'的成员信息
select *from stu where name like '_化%';
//查询名字中包含'德'
select *from stu where name like '%德%';

排序查询

select 字段列表 from 表名 order by 排序字段名1[排序顺序1],排序字段名2[排序顺序2];

ASC:升序排列（默认）
DESC：降序排列
如果有多个排序条件，只有当前面条件值一样的时候，才会根据第二条件进行排序。

分组查询

聚合函数：将一列数据作为一个整体，进行纵向计算。

函数名	功能
count(列名）	统计数量（一般选用不为null的列）
max(列名）	最大值
min(列名）	最小值
sum(列名）	求和
avg(列名）	平均值

select 聚合函数名（列名） from 表；

null不参与所有聚合函数的运算
count的取值：
（1）主键：非空且唯一
（2）（*）

select 字段列表 from 表名[where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

note:
（1)分组之后，查询的字段是聚合函数和分组字段，查询其他字段没有意义。

（2）where和having的区别：

• 执行时机不一样：where在分组之前进行限定，不满足where条件，则不参与分组，而having是在分组之后对结果进行过滤
• 可判断的条件不一样：where不能对聚合函数进行判断，having可以

(3)执行顺序：where>聚合函数>having

//查询男同学和女同学的各自数学平均分
select sex,avg(math) from stu group by sex;
//查询男同学和女同学的各自数学平均分和各自人数
select sex,avg(math),count(*) from stu group by sex;
//查询男同学和女同学的各自数学平均分和各自人数,要求分数低于70不参与分组
select sex,avg(math),count(*) from stu where math>70 group by sex;
//查询男同学和女同学的各自数学平均分和各自人数,要求分数低于70不参与分组,要求分组之后的人数大于2个
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>2;

分页查询

select 字段列表 from 表名 limit 起始索引，查询条目数；

起始索引：从0开始
计算公式：起始索引=（当前页码-1）*每页显示的条数

NOTE:
（1）limit 是mysql的方言
（2）Oracle分页用rewnumber
（3）SQL service分页查询用top

//从0开始查询，查询3条
select *from stu limit 0,3;
//每页显示三条数据，查询第一页数据
select *from stu limit 0,3;
//每页显示三条数据，查询第一页数据
select *from stu limit 3,3;
//每页显示三条数据，查询第一页数据
select *from stu limit 6,3;

约束

约束是作用于表中列上的规则，用于限制加入表中的数据
约束的存在保证了数据库数据的正确性，有效性和完整性

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null	not null
唯一约束	保证列中的数据各不相同	unque
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
检查约束	保证列中的值完成某一个条件	check
默认约束	保存数据是，没指定值则使用默认值0（null也是一个值）	default
外键约束	外键用来让俩个表之间的数据建立链接，保证数据一致性和完善性	foreign key

create table emp(id int primary key,//员工id,主键并且自增长ename varchar(50) not null unque,//非空并且唯一bouns double(7,2) default 0//如果没有奖金默认为0); //建完表之后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;
//删除约束
alter table modify 字段名 数据类型;

演示自动增长：auto-increment(当列是数字类型并且是唯一约束）

insert into emp(ename) value('李四');
insert into emp(id,ename)values(null,'赵六');
//自动实现id

外键约束

外键用来让俩个表的数据之间进行链接，保证数据的一致性和完整性。
添加约束：(先创建主表，再创建从表）

//创建表的时候添加
create table 表名（列名 数据类型，constraint 外键名称 foreign key (外键列名) references (主表);constraint fk_emp_dep foreign key (dep_id) references dept(id);）;//建立表之后添加约束
alter table 表名 add constraint 外键名称 foreign key 外键字段名称 references 主表名称（主表列名称）;

删除约束：

alter table 表名 drop foreign key 外键名称;

数据库设计

软件研发步骤

需求分析（产品经理）–>制作产品原型
设计（架构师+开发工程师）—>软件结构设计，数据库设计，接口设计，过程设计
编码（开发工程师）
测试（测试工程师）
安装部署（运维工程师）

数据库设计理念和步骤

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
建立数据库中的表结构和表与表之间的关联关系的过程。

数据库的设计步骤：
（1）需求分析：数据是什么？数据具有哪些属性？数据于属性的特点.
（2）逻辑分析：通过ER图对数据库进行逻辑建模，不需要考虑物理逻辑
（3）物理设计：根据数据库自身的特点把逻辑转换为物理设计
（4）维护设计：对表的需求见表 表优化

表关系

一对一（多用于表的拆分，将一个实体经常使用的字段放在一张表里面，不经常使用的字段放在另外一张表，用于提高查询性能）
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一（uniquk)

一对多
实现：在多的一方建立外键，指向一的一方的主键

多对多
实现：建立第三张中间表，中间表至少包含俩个外键，分别关联俩方主键

多表查询

笛卡尔积：有A和B俩种集合，取A和B中所有的组合情况
多表查询的目的：消除无效数据

• 连接查询：
  内连接：相当于查询A B交集的数据

//隐式内连接
select 字段列表 from 表1，表2…where 条件;
//查询emp的name和dept的dname数据，使得emp.dep_id==dept.id
select emp.name,dept.dname from emp dept where emp.dep_id=dept.did;
//给表起别名
select t1.name,t2.dname from emp t1 dept t2 where t1.dep_id=t2.did;//显式内连接
select 字段列表 from 表1 inner  join 表2 on 条件
select *from emp inner join dept on emp.dep_id=dept.did;

外连接：
（1）左外连接：相当于查询A表所有的数据和交集部分的数据

select 字段列表 from 表1 left[outer] join 表2 on 条件；

（2）右外连接：相当于查询B表所有数据和交集部分数据

select 字段列表 from 表1 right[outer] join 表2 on 条件;

• 子查询

事务

数据库的事务是一种机制，一个操作序列，包含了一组数据库的操作命令。
事务把所有的命令作为一个整体一起向系统提交或者撤销操作请求，也就是这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。（开启事务——>提交事务——>回滚事务）

//开启事务
start transaction;
begin;
//提交事务
commit;
//回滚事务
rollback;

begin;
update account set money=money-500 where name="李四";
update account set money=money+500 where name="张三";
commit;//事务的数据会被持久化更改
rollback;

事务的四大特征：
（1）A（原子性）：事务是一个不可再分的最小单位，要么同时成功，要么同时失败。
（2）C（一致性）: 事务完成的时候，必须使得所有的数据必须保持一致
（3）L（可见性）：多个事务之间，操作的可见性
（4）D（持久性）：事务一旦提交或者回滚，它对数据库的数据改变就是永久的。

Mysql的事务默认自动提交：

//查看事务的默认提交方式 1--自动提交 0--手动提交
select @@autocommit;
//修改事务的提交方式(手动提交）
set  @@autocommit=0;