数据库、mysql数据库详解

数据库

数据库就是数据的仓库。在数据库中提供了专门的管理系统，对数据库中的数据进行集中的控制和管理。能高效的对数据进行存储、检索。

关系型数据库：关系型数据库起源于关系模型。关系模型认为，世界是由实体和联系组成的。而关系型数据库，是一种以表作为实体，以主键和外键作为联系的数据库结构。

---

主键：在关系型数据库中，用于标识每一行唯一性的列，称为主键列。

主键有两个特点：不能重复，不能为空。

外键：在关系型数据中，用于表达两个表之间联系的列，称为外键列。

在关系型数据库中，引用方称为从表，被引用方称为主表。从表利用外键，引用主表的主键，从而建立两个表之间的联系。所以，外键在从表中定义。

---

关系型数据库的关系主要有三种：一对一，一对多，多对多

一对一：一条主表记录对应一条从表记录，同时一条从表记录对应一条主表记录。

一对多：一条主表记录对应多条从表记录，同时一条从表记录对应一条主表记录。

多对多：一条主表记录对应多条从表记录，同时一条从表记录对应多条主表记录，依靠中间表维护多对多的联系。

多对多模型
老师表			中间表			学生表
老师编号	姓名		老师编号	学生编号		学生编号	姓名
1	李老师		1	1		1	张1
2	王老师		1	2		2	张2
3	胡老师		1	3		3	张3
			2	4		4	张4
			2	1
			2	2
			2	3
			3	4
			3	1
			3	2
一般情况下多方从表一方主表

---

DBMS

关系型数据库只是一个保存数据的容器，大多数数据库依靠一个称为数据库管理系统（Database Management System，简称DBMS）的软件来管理数据库中的数据。

数据库引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据库引擎进行创建、查询、更新和删除数据。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

InnoDB 是事务型数据库的首选引擎，提供提交、回滚、崩溃恢复能力的事务安全能力，并可以实现并发控制。InnoDB 是默认的MySQL引擎。

MyISAM 拥有较高的插入、查询速度，但不支持事务。

MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。主要用于数据量不大的临时数据。

结构化查询语言

SQL是结构化查询语言（Structured Query Language）的英文缩写，是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言。

SQL 与 RDBMS 协同工作，来定义数据库的结构、存储数据、操纵数据、获取数据、控制对数据的访问以及确保数据的完整性。

SQL 分类：

数据定义语言（DDL）--- 创建、修改、删除数据库的内部数据结构 （define 定义）

数据查询语言（DQL）--- 用于数据库中的数据查询 （query 查询）

数据操作语言（DML）--- 用于数据库中数据的修改，包括添加。删除、修改等 （manipulation 操纵）

数据控制语言（DCL）--- 用于控制数据的访问权限 （control 控制）

SQL执行顺序

后执行的子句，可以使用前执行子句的计算结果、定义的别名等
执行顺序：
1.执行from 查询表
2.执行where 进条件筛选
3.执行group by 分组
4.执行select 查询列
5.执行having 对分组结果进行筛选
6.执行order by 排序
7.执行limit 返回限定行

数据完整性

数据完整性=数据准确性+数据可靠性

为了防止垃圾数据的产生，从而影响数据库的执行效率

数据完整性分为：实体完整性、域完整性、引用完整性、自定义完整性

实体完整性：

保证每行所代表的实体能互相区别，不能存在两条一模一样的记录。
实体完整性实现方式：1、主键约束 2、唯一约束

主键（primary key）是表中的一到多个列，主键列不能为空，也不能重复。一个表中只能有一个主键。
适合做主键的列，需要满足四个条件：

1、值必须唯一

2、值不能为空

3、不能带业务含义

4、值不能变化

唯一约束 是指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。它和主键约束的区别在于该列可以为空。并且可以在一张表中给多个列设置唯一的约束。

create table 表名（
   phone VARCHAR（50） unique
 ）

域完整性：

保证指定列的数据的有效性（mysql对域完整性支持不好）

实现方式：非空约束、默认值、检查约束

非空约束：制定列的值,不能插入null值。not null
默认值：添加记录时，如果该列的值不做插入，那么以默认值插入。 default
检查约束：检查插入列数据的有效值。check（mysql不支持）

CREATE TABLE t_em(
	pk_emle INT PRIMARY KEY AUTO_INCREMENT;
    e_name VARCHAR(20) NOT NULL, -- 非空约束
    e_status ENUM('在职','离职') DEFAULT '在职' -- 默认值
    e_money INT CHECK(e_money>1000 AND e_money<10000) -- 检查约束
);

引用完整性：

从表当中外键出现的数据，必须在主键列出现
实现方式：外键约束

外键和外键约束：
外键是描述表与表之间联系的一个列。是表中的列
外键约束是指在外键列上加上一个约束，强制外键列引用的数据是正确的。如果违反该约束，则不允许该条数据修改或插入。
注意：没有外键约束，不表示没有外键

-- 添加外键约束
ALTER TABLE t_car ADD CONSTRAINT fk_1 FOREIGN KEY(fk_manId)
REFERENCES t_man(pk_manId);

数据库三范式

第一范式：行不能重复（每条记录不能完全一样），列不可再分（一条记录的列只能有一个值）。
第二范式：非主独立。非主键列必须依赖于主键列。二范式要求一个表只描述一个实体。
第三范式：非主独立。非主键列之间不能有依赖关系。

数据库数据类型

数据库数据类型决定了表中 列 存储数据的类型。分为五种：

整型（int）

浮点型（float、double、decimal(m、d)）

字符串型（char（定长字符串，如果内容不满以空格填充）、Varchar（可变长字符串，以内容的长度作为数据的长度））

日期型（date（年月日）、time（时分秒）、datetime（年月日时分秒））

二进制型（BLOB二进制型的长文本数据）

MYSQL 操作

创建数据库

CREATE DATABASE mydb;--mydb为数据库名

删除数据库

DROP DATABASE mydb;

创建表

CREATE TABLE t_student(
	-- PRIMARY KEY 标识该列为主键列
	-- pk表示主键列的缩写
	-- AUTO_INCREMENT 表示自动增长列，该列的内容由DBMS分配
	pk_studentId INT PRIMARY KEY AUTO_INCREMENT,
	s_name VARCHAR(20),-- 姓名
	s_birthday DATE,   -- 生日
	s_money INT, 	   -- 金额
	-- 性别 enum称为枚举，表示该列的值只能是选项中的值
	s_gender ENUM('男','女'),
	s_phone VARCHAR(20)-- 电话`db``db`
);

删除表

DROP TABLE t_student;

查询数据

SELECT * FROM t_student;--*表示所有列
SELECT s_name,s_phone FROM t_student;-- 查询指定列

添加记录

INSERT INTO t_student(s_name,s_birthday,s_money,s_gender,s_phone)
VALUES('张无忌','1997-8-21',3000,'男','13272934889');

--添加多条记录，只对mysql有效
INSERT INTO t_student(s_name,s_birthday,s_money,s_gender,s_phone)
VALUES('赵敏','1998-8-8',12000,'女','15288889999'),
('岳不群','1998-8-8',12000,'男','15287789889');

修改记录

-- 将张无忌的工资修改为4000，电话修改为13566664444
UPDATE t_student SET s_money=4000,s_phone='13566664444' WHERE s_name='张无忌';

删除记录

DELETE FROM t_student;
TRUNCATE TABLE t_student;

区别：
1、TRUNCATE TABLE 会清空表内所有数据，而DELETE FROM 可以按条件删除；
2、DELETE 会记录日志，意味着删除后的数据还可以恢复，但是效率低。TRUNCATE 不会记录日志，
删除后数据不能恢复，但是效率高。

条件删除：

-- 删除编号是2的学生
DELETE FROM t_student WHERE pk_studentId=2;

-- 删除编号大于3的学生
DELETE FROM t_student WHERE pk_studentId>3;

操作列

-- 添加学历列
ALTER TABLE t_student ADD s_edu VARCHAR(20);

-- 修改列
ALTER TABLE t_student CHANGE s_edu s_eduInfo VARCHAR(20);

-- 删除列
ALTER TABLE t_student DROP COLUMN s_eduInfo;

条件查询

if 函数 类似于三目运算符 会显示符合条件的查询结果列

注意：该列只做查询显示，并没有在表中实际插入生成

-- 显示所有用户，并且显示该用户是否万元户
SELECT stu.*,IF(s_money>=10000,'万元户','千元户') rank  FROM t_student stu;

distinct 去除重复记录 相同记录只显示一次

SELECT DISTINCT s_gender FROM t_student;

limit 返回限定行 第一个参数为起始记录数（第一条为0），第二个参数为显示记录数

--显示前三条记录
SELECT * FROM t_student LIMIT 0,3;

单条件查询   “ = ” 放在where 后面，表示比较；放在set 后面，表示赋值。
多条件查询   and （并且，两个条件都为真，结果为真，有一个为假，结果都为假）  
                       or （或者，两个条件有一个为真，结果都为真，只有两个条件都为假，结果才是假）

-- 查询90后的人 
SELECT * FROM t_student WHERE s_birthday>='1990-1-1' AND s_birthday<'2000-1-1';

-- 查询张无忌和雷震子的信息
SELECT * FROM t_student WHERE s_name='张无忌' OR s_name='雷震子';
--即使没有‘雷震子’，也会显示张无忌的信息

定义集合范围： in 或 not in 

-- 查询张无忌和雷震子的信息
SELECT * FROM t_student WHERE s_name IN('张无忌','雷震子');

-- 查询 除了 张无忌和雷震子的信息
SELECT * FROM t_student WHERE s_name NOT IN('张无忌','雷震子');

查询null 值不能用 = 、！= 只能用 is 、 is not

-- 将张无忌和雷震子电话号码设置为空
UPDATE t_student SET s_phone=NULL WHERE s_name IN('张无忌','雷震子');

-- 查询没有电话号码的人
SELECT * FROM t_student WHERE s_phone IS NULL;

-- 查询有电话号码的人
SELECT * FROM t_student WHERE s_phone IS NOT NULL;

模糊查询 
-- “ _ ” 通配符，匹配任何单个字符
-- “ % ” 通配符，匹配零个或多个任意字符

-- 查询所有姓张的人
SELECT * FROM t_student WHERE s_name LIKE '张%'

-- 查询姓张，姓名是两个字的人
SELECT * FROM t_student WHERE s_name LIKE '张_';

排序

将表中的记录按某个列进行排序排列，可以指定升序（asc）或降序（desc），如果不做指定，默认为升序。

-- 单列排序
-- 查询所有人，并且按工资降序排列
SELECT * FROM t_student ORDER BY s_money DESC;

-- 多列排序：先按某个列进行排序，如果两条记录中，该列的值相同，再按第二个列进行排序
-- 查询所有人，并且按工资排序，如果工资相同，按成绩排序
SELECT * FROM t_student ORDER BY s_money DESC,s_grade DESC;

条件判断语句

-- 在mysql中，允许根据查询结果值的不同，返回不同的内容。
-- 双分支语句，相当于三目运算符，条件满足，返回一个结果。条件不满足，返回另一个结果。

-- 药品类商品上涨5元，食品类商品上涨2元，酒类商品上涨10元
UPDATE t_product SET s_price=s_price+(CASE WHEN s_productType='食品类' THEN 2
WHEN s_productType='药品类' THEN 5
ELSE 10 END);

聚合函数

count：统计行的数量

count（*）：统计所有符合条件的记录。

-- 统计有几种商品
SELECT COUNT(*) FROM t_product;

-- count（列名）：只统计非空列
-- 统计参考学生数量
SELECT COUNT(S_grade) FROM t_student;

-- cont（distinct 列名）只统计非空、不重复列
-- 统计有几类商品
SELECT COUNT(DISTINCT s_productType) FROM t_product;

sum（列名）：统计某个列的总和

-- 统计商品的总价格
SELECT SUM(s_price) FROM t_product;

avg(列名) ：统计某个列的平均值，只统计非空

-- 统计学生平均分
SELECT AVG(s_grade),SUM(s_grade)/COUNT(*) FROM t_student;
--前者表示参考学生平均分，后者表示所有学生平均分

max（列名）：统计某个列最大值

min（列名）：统计某个列最小值，不统计空值

-- 统计最高分和最低分
SELECT MAX(s_grade),MIN(s_grade) FROM t_student;

分组查询（group by）

-- 分组就是将指定规则中相同的数据进行“小区域”（组）的划分，然后针对若干个小区域进行数据处理
-- 分组执行在where 以后，是对where条件筛选的结果进行分组

-- 统计每一类商品的数量
SELECT s_productType,COUNT(*) '数量' FROM t_product GROUP BY s_productType;

-- 统计价格在50元以上，每类商品的数量
SELECT s_productType,COUNT(*) FROM t_product WHERE s_price>=50 
GROUP BY s_productType;

-- 多列分组：在group by 后面可以跟多个列，多个列的值完全相同算一个组
-- 统计每个厂家生产不同类别商品数量
SELECT s_factory,s_productType,COUNT(*) FROM t_product 
GROUP BY s_factory,s_productType;

having

having 是对分组以后的结果进行筛选，执行在分组之后

-- 查看商品类别数量在3个以上的类别
SELECT s_productType,COUNT(*) FROM t_product GROUP BY s_productType 
HAVING COUNT(*)>3;

注意：where和having的区别。where执行在分组之前，不能跟聚合函数。having执行在分组之后，是对分组结果进行筛选，一般使用聚合函数进行筛选。

子查询

在一个查询语句中再嵌套一个查询语句，称为子查询

-- 子查询的分类
-- 相关子查询 和 非相关子查询
-- 相关子查询：在外查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询。相关子查询的执行，依赖于外部查询的数据，外部查询返回一行，子查询就执行一次。所以，相关子查询效率很低。
-- 非相关子查询：在主查询中，子查询只需要执行一次，子查询结果不再变化。子查询结果供帐户查询使用，这种查询方式称为非相关子查询。
-- 区分相关子查询，还是非相关子查询。主要看子查询是否能够独自运行，子查询是否依赖外查询的查询结果。如果子查询需要依赖外查询查询结果，不能单独运行，就是相关子查询。如果子查询不需要依赖外查询查询结果，可以单独运行，就是非相关子查询。

-- 查询比参苓白术散价格更高的单品
SELECT * FROM t_product WHERE s_price>
(SELECT s_price FROM t_product WHERE s_name='参苓白术散');

-- 子查询嵌在from之后，相当于临时表。也就是将子查询查询的结果，当作表来用。
-- 注意，子查询的结果必须加别名
SELECT * FROM
(SELECT e_name,(SELECT d_info FROM t_dept WHERE pk_deptId=fk_deptId) 
FROM t_employee)
eminfo WHERE e_name='张勇';

-- 在where后嵌入子查询。如果子查询的结果是单行单列（查询结果只有一个值），
-- 外查询才可以使用=、！=、< 、> 、>=、<=

-- 查询和刘波一个部门的员工
SELECT * FROM t_employee WHERE fk_deptId=
(SELECT fk_deptId FROM t_employee WHERE e_name='刘波')

-- 如果子查询返回的值是多行多列，外查询只能用in 或 not in

-- 查询张勇和刘波同一个部门的员工
SELECT * FROM t_employee WHERE fk_deptId IN
(SELECT fk_deptId FROM t_employee WHERE e_name IN('张勇','刘波'))

-- all：和子查询的结果逐一比较，必须全部满足时表达式的值才为真

-- 查询所有比食品类商品价格更高的商品
SELECT * FROM t_product WHERE s_price>
ALL(SELECT s_price FROM t_product WHERE s_productType='食品类');

-- 可替换
SELECT * FROM t_product WHERE s_price>
(SELECT MAX(s_price) FROM t_product WHERE s_productType='食品类');

-- any：和子查询的结果逐一比较，其中一条记录满足条件则表达式就为真

-- 查询比任意食品类商品价格高的商品
SELECT * FROM t_product WHERE s_price>
ANY(SELECT s_price FROM t_product WHERE s_productType='食品类')AND
s_productType!='食品类';

-- 可替换
SELECT * FROM t_product WHERE s_price>
(SELECT MIN(s_price) FROM t_product WHERE s_productType='食品类')AND
s_productType!='食品类';

-- exists 判断子查询是否存在数据，如果存在表达式则为真，反之为假
-- not exists 相反

删除主表记录

删除主表记录时，如果该主表记录有从表记录引用。由于有外键约束存在，则无法删除主表记录。
删除主表记录有三种方式
1、级联删除。先将主表记录中关联的从表记录全部删除，再删除从表记录
2、外键置空。先将主表记录中关联的从表记录外键设置为null，然后再删除主表记录
3、改变状态。将主表记录添中标识符（有效/无效）。删除主表记录时，将状态修改为无效

联表查询

如果数据来自多个表，那么可以采用联接查询的方式来实现。表联接就是指将多个表联合在一起实现查询效果。

笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

表联接分类：内联接、外联接、自联接

内联接：只能查询两个表之间有关联记录的数据。

-- 查询所有汽车，以及该汽车的主人姓名
SELECT c.*,m.`m_name` FROM t_man m JOIN t_car c ON m.`pk_manId`=c.`pk_carId`;
SELECT c.*,m.`m_name` FROM t_man m,t_car c WHERE m.`pk_manId`=c.`fk_manId`; 
-- 如果有汽车没有主人，则不在查询结果中。内联接只能查询有主人的汽车。

外联接：可以查询出一个表中，所有符合条件的记录，无论该记录是否有关联数据。

left 或 right 实现

-- 显示所有公民，以及公民拥有的汽车数量
SELECT m.*,COUNT(c.`c_type`) FROM t_car c LEFT JOIN t_man m ON 
m.`pk_manId`=c.`fk_manId` GROUP BY c.`fk_manId`;

视图

视图可以看作是sql语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。视图只提供查询功能，不提供数据修改功能。

CREATE VIEW v_partyNum AS
SELECT p.`partyName`,COUNT(m.`mspName`) FROM msp m RIGHT JOIN party p 
ON m.`party`=p.`partyCode` GROUP BY p.`partyCode`;

SELECT * FROM v_partyNum;