数据库(MySQL)从0到精通笔记

MySQL服务启用、停用、登录、退出

1、MySql服务的启动和停止：

	启动：mysql>net start mysql
	停止：mysql>net stop mysql

2、登录MySql

	mysql> mysql -u root -p
	然后输入你的密码

2、退出mysql：

	mysql>quit

MySQL数据模型：

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

SQL分类：

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

DDL–操作数据库：

（1）查询：Show DataBases;
（2）创建：
	2.1）创建数据库：Create DataBase 数据库名称;
	2.2）创建数据库（判断，如果不存在则创建）：Create DataBase If Not Exists 数据库名称;
（3）删除：
	3.1）删除数据库：Drop DataBase 数据库名称;
	3.2）删除数据库（判断，如果存在则删除）：Drop DataBase If Exists 数据库名称;
（4）使用数据库：
	4.1）查看当前使用的数据库：Select DataBase();
	4.2）使用数据库：Use 数据库名称;

DDL–操作表：

（1）创建：
	1.1）创建表：
	Create Table 表名(
		字段名1 数据类型1,
		字段名2 数据类型2,
		...
		字段名n 数据类型n	--最后一行末尾，不能加逗号
	);
（2）查询：
	2.1）查询当前数据库下所有表名称：Show Tables;
	2.2）查询表结构：Desc 表名称;
（3）修改：
	3.1）修改表名：Alter Table 表名 Rename To 新的表名;
	3.2）添加一列：Alter Table 表名 Add 列名 数据类型;
	3.3）修改数据类型：Alter Table 表名 Modify 列名 新数据类型;
	3.4）修改列名和数据类型：Alter Table 表名 Change 列名 新列名 新数据类型;
	3.5）删除列：Alter Table 表名 Drop 列名;
（4）删除：
	4.1）删除表：Drop Table 表名;
	4.2）删除表时判断表是否存在：Drop Table If Exists 表名;

DML–表的数据进行增删改：

（1）添加：
	1.1）给指定列添加数据：Insert Into 表名(列名1,列名2,...) Values(值1,值2,...);
	1.2）给全部列添加数据：Insert Into 表名 Values(值1,值2,...);
	1.3）批量添加数据：
		Insert Into 表名(列名1,列名2,...) Values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
		Insert Into 表名 Values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
（2）修改：
	2.1）修改表数据：Update 表名 Set 列名1=值1,列名2=值2,... [Where 条件] ;	-- 修改语句中如果不加条件，则将所有数据都修改
（3）删除：
	3.1）删除数据：Delete From 表名 [Where 条件] ;	-- 删除语句中如果不加条件，则将所有数据都删除！

DQL–查询数据库中表的记录： 基本语法：

	Select
		字段列表
	From
		表名列表
	Where
		条件列表
	Group By
		分组字段
	Having
		分组后条件
	Order By
		排序字段
	Limit
		分页限定
（1）基础查询：
	1.1）查询多个字段：
		Select 字段列表 From 表名;
		Select * From 表名;	-- 查询所有数据
	1.2）去除重复记录：
		Select Distinct 字段列表 From 表名;
	1.3）起别名：
		As: as 也可以省略
（2）条件查询：
	2.1）条件查询语法：
		Select 字段列名 From 表名 Where 条件列表;
		模糊查询（like）："_"表示单个任意字符，"%"表示多个任意字符
（3）排序查询：
	3.1）排序查询语法：
		Select 字段列表 From表名 Order By 排序字段名1 [排序方式1],排序字段名2 [排序方式2] ...;
		排序方式：
			ASD：升序排列（默认值）
			DESC：降序排列

		-- 如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序
（4）分组查询：
	4.1）聚合函数：
		4.1.1）概念：将一列数据作为一个整体，进行纵向计算。
		4.1.2）聚合函数分类：
			count(列名)：统计数量（一般选用不为null的列）
			max(列名)：最大值
			min(列名)：最小值
			sum(列名)：求和
			avg(列名)：平均值
		4.1.3）聚合函数语法：
			Select 聚合函数名(列名) From 表;	-- null值不参与所有聚合函数运算
	4.2）分组查询语法：
		Select 字段列表 From 表名 [Where 分组前条件限定] Group By 分组字段名 [Having 分组后条件过滤];
		
			注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。
			Where和Having的区别：
				执行时机不一样：Where是分组之前进行限定，不满足Where条件，则不参与分组，而Having是分组之后对结果进行过滤。
				可判断的条件不一样：Where不能对聚合函数进行判断，Having可以
			执行顺序：Where > 聚合函数 > Having
（5）分页查询：
	5.1）分页查询语法：
		Select 字段列表 From 表名 Limit 起始索引 , 查询条目数;
			起始索引：从0开始
			计算公式：起始索引=（当前页码数-1）*每页显示的条目数
		tips：
			分页查询limit是MySQL数据库的方言
			Oracle分页查询使用rownumber
			SQL Server分页查询使用top

数据库约束：

（1）约束：
	1.1）约束的概念：
		约束是作用于表中列上的规则，用于限制加入表的数据
		约束的存在保证了数据库中数据的正确性、有效性和完整性
	1.2）约束的分类：
		非空约束：保证列中所有数据不能有null值（NOT NULL）
			1.2.1）添加约束：
				-- 创建表时添加非空约束
				Create Table 表名(
					列名 数据类型 NOT NULL,
					...
				);
				-- 建完表后添加非空约束
				Alter Table 表名 Modify 字段名 数据类型 NOT NULL;
			1.2.2）删除约束：
				Alter Table 表名 Modify 字段名 数据类型;
		唯一约束：保证列中所有数据各不相同（UNIQUE）
			1.2.3）删除约束：
				Alter Table 表名 Drop Index 字段名;
		主键约束：主键是一行数据的唯一标识，要求非空且唯一（PRIMARY KEY）
			1.2.4）建完表后添加主键约束：
				Alter Table 表名 Add Primary Key(字段名);
			1.2.5）删除约束：
				Alter Table 表名 Drop PrimaryKey;
		检查约束：保证列中的值满足某一条件（CHECK）
		默认约束：保存数据时，未指定值则采用默认值（DEFAULT）
			1.2.6）建完表后添加默认约束：
				Alter Table 表名 Alter 列名 Set DEFAULT 默认值;
			1.2.7）删除约束：
				Alter Table 表名 Alter 列名 Drop DEFAULT;
		外键约束：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性（FOREIGN KEY）
			1.2.8）添加约束：
				-- 创建表时添加外键约束
				Create Table 表名(
					列名 数据类型,
					...
					[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
				);
				-- 建完表后添加外键约束
				Alter Table 表名 Add CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
			1.2.9）删除约束：
				Alter Table 表名 Drop FOREIGN KEY 外键名称;
		-- MySQL不支持检查约束
	1.3）自动增长：当列是数字类型并且唯一约束（auto_increament）

数据库设计：

（1）软件的研发步骤：
	 |	1.1）需求分析：产品原型（墨刀软件等）
	 |	1.2）设计：软件结构设计（功能、模块、模块之间的关系等）、数据库设计（数据、表、表之间的关系等）、接口设计（功能的出参与入参是什么等问题）、过程设计（如何开发）
	 |	1.3）编码
	 |	1.4）测试
	 V	1.5）安装部署
（2）数据库设计概念：
	2.1）数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
	2.2）建立数据库中的表结构以及表与表之间的关联关系的过程。
	2.3）有哪些表？表里有哪些字段？表和表之间有什么关系？
（3）数据库设计的步骤：
	3.1）需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么）
	3.2）逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
	3.3）物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
	3.4）维护设计（1.对新的需求进行建表；2.表优化）
（4）表关系的实现：
	4.1）一对多：在“多”的一方建立外键，指向“一”的一方的主键
	4.2）多对多：建立第三张中间表，中间表至少包括两个外键，分别关联两方主键
	4.3）一对一：
		一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。
		实现：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

多表查询：

（1）笛卡尔积：取A，B集合所有组合情况
（2）多表查询：从多张表查询数据
	2.1）连接查询：
		2.1.1）内连接：相对于查询A、B交集数据
		2.1.2）外连接：
			2.1.2.1）左外连接：相对于查询A表所有数据和交集部分数据
			2.1.2.2）右外连接：相对于查询B表所有数据和交集部分数据
	2.2）子查询
（3）内连接：相对于查询A、B交集数据
	3.1）内连接查询语法：
		-- 隐式内连接
		Select 字段列表 From 表1,表2... Where 条件;

		-- 显示内连接
		Select 字段列表 From 表1 [Inner] Join 表2 On 条件;
（4）外连接：
	4.1）外连接查询语法：
		-- 左外连接
		Select 字段列表 From 表1 Left [Outer] Join 表2 On 条件;
		
		-- 右外连接
		Select 字段列表 From 表1 Right [Outer] Join 表2 On 条件;
（5）子查询：
	5.1）子查询概念：查询中嵌套查询，称嵌套查询为子查询
	5.2）子查询根据查询结果不同，作用不同：
		5.2.1）单行单列：作为条件值，使用=、!=、>、<等进行条件判断
			Select 字段列表 From 表 Where 字段名 = (子查询);
		5.2.2）多行单列：作为条件值，使用in等关键字进行条件判断
			Select 字段列表 From 表 Where 字段名 in (子查询);
		5.2.3）多行多列：作为虚拟表
			Select 字段列表 From (子查询) Where 条件;

事务：

（1）事务简介：
	1.1）数据库的事务是一种机制、一个操作序列，包含了一组数据库操作命令
	1.2）事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
	1.3）事务是一个不可分割的工作逻辑单元
（2）事务3个操作：
	-- 开启事务
	Start Transaction;
	或者 Begin;
	-- 提交事务
	Commit;
	-- 回滚事务
	RollBack;
（3）事务四大特性：（*面试经常被问）ACID
	3.1）原子性(A)：事务时不可分割的最小操作单位，要么同时成功，要么同时失败
	3.2）一致性(C)：事务完成时，必须使所有的数据都保持一致状态
	3.3）隔离性( I)：多个事务之间，操作的可见性
		隔离性越强，操作越不可见，性能就越低
		隔离性越弱，操作是可见的，性能就越高
	3.4）持久性(D)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
（4）MySQL事务默认自动提交
	-- 查看事务的默认提交方式
	Select @@autocommit;
	-- 1 自动提交  0 手动提交
	-- 修改事务提交方式
	Set @@autocommit = 0;