MYSQL基础学习笔记

SQL

1. 什么是SQL？
	Structured Query Language:结构化查询语言
	其实就是定义了操作所有就关系类型数据库的规则。每种数据库存在不一样的地方我们叫做“方言”
2. SQL通用语法
	1. SQL 语句可以一单行或多行书写，以分号结尾。
	2. 可以使用空格和缩进来增强语句的可读性。
	3. MySql 数据库的 SQL 语句不区分大小写，关键词建议使用大写。
	4. 3种注释方式
		*当行注释：--注释内容 或 # 注释类容（mysql 特有）
		*多行注释：/*z注释*/
3. SQL分类
	1) DDL(Data Definition Language) 数据定义语言
		用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等
	2）MDL(Data Query Language)数据操作语言
   		用来对数据中表的数据进行增删改。关键字：insert,delete,updata 等
	3）DQL(Data Query Language)数据查询语言
		用来查询数据库中的记录（数据）。关键字:select,where 等
	4）DCL(Data Control Language)数据控制语言（了解）
		用来点定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE 等

DDL:操作数据库、表

1. 操作数据库:CRUD
	1. C(Create): 创建
		*创建数据库：
			*create database 数据库名称;
		*创建数据库db3，判断是否存在，并设置字符集为gbk
			*create database if not exists db3 character set gbk;
	2. R(Retrieve):查询
		*查询所有数据库的名称:
			*show databases;
		*查看某个数据库的字符集：查询某个数据库的创建语句
			*show create databaase 数据库名称；
	3. U(Updata):修改
		*修改数据库的字符集:
			*alter database 数据库名称 character set utf8
	4. D(Delete):删除
		*删除数据库：
			*drop database 数据库名称
	5. 使用数据库
		*查看当前使用使用数据库：
			*select database()
		*使用某个数据库：
      		*use 数据库名称

2. 操作表
	1. C(Create): 创建
		1. 语法：
			create table 表名(
				字段名1 数据类型1,
				字段名2 数据类型2,
				字段名3 数据类型3,
				字段名n 数据类型n
				);
			*注意最后一个字段的后面不要加逗号（,）、
			*常用的数据类型:
				1. int:整型
					*age int,
				2. double:小数类型
					*score double(5,2) -- 第一 参数表示一共多少为数字，第二个参数表示小数有多少位。
				3. date：日期，自包含年月日 yyyy-MM-dd
				4. datatiem:日期，包含年月日时分秒 yyyy-MM-dd  HH-mm-ss
				5. timestamp:时间戳类型 包含年月日时分秒
					*如果将来不给这个字段赋值或者赋值为null，侧使用当前的系统时间来自动赋值
				6. varchar:字符串类型
					*name varchar(32) -- 姓名最大32个字符
					*zhangsan 8个字符 张三 2个字符
			*创建表
				create table student(
					id int,
					name varchar(32),
					age int,
					score double(4,1),
					birthday data,
					insert_time timestamp
					);
			*复制表
				*create table 新表名 like 需要复制的表;

	2. R(Retrieve):查询
		*查看某个数据库中所有的表名称：
			*show tables;
		*查看某个表的结构关键字desc(description)的缩写：
			*desc 某个表名;
		*查看表的字符集：实质上是查看表的创建sql语句
			*show create table 表名;
	3. U(Updata):修改
		1. 修该表名
			*alter table 表名 rename to 新名字;
		2. 修改字符集
   			*alter table 表名 character set 字符集名称;
		3. 添加字段
			*alter table 表名 add 添加字段 字段类型;
		4. 修改字段名称和类型
			*同时修改字段名称和字段数据类型
				*alter table 表名 change 旧字段名 新字段名 新字段数据类型;
			*只修改数据类型
				*alter table 表名 modfiy 字段名 新数据类型;
		5. 删除指定的字段
			*alter table 表名 drop 需要删除的字段;
	4. D(Delete):删除
		*直接删除
			drop table 表名;
		*判断是否存在后再删除
			drop table if exists 表名;

*客服端图形化工具:SQLYog;

DML:增删改表中的数据

1. 添加数据：
	*语法：
		* insert into 表名 (字段名1,字段名2,...字段名n) values (值1,值2,...值n);
	*注意：
		1. 列名和字段名一一对应。
		2. 如果表名后不定义列名则默认是给所有就列添加数据
			insert into 表名 values(值2,值2,...值n);
		3. 除了数字不用引号引起来其他的都要用引号引起来双引号或单引号都可以
2. 删除数据
	*语法：
		*delete from 表名 [where 条件]
	*注意：
		1. 如果不加条件，则删除有所记录
		2. 如果要删除所有记录
			1. delete from 表名；-- 不推荐使用。表中有多少条记录就会执行都少次删除语句。
			2. truncate table 表名;-- 先删除表，然后再创建一张一模一样的表。
3. 修改数据
	*语法：
		*update 表名 set 字段1=值1,字段2=值2,...字段n=值n [where 条件];
	*注意：
		*如果不添加任何条件，则会将表中的所有记录全部修改。

DQL: 查询表中的数据

1. 语法：
	select 
		字段列表
	from 
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定


2. 基础查询
	1. 多字段查询
		select 字段名1,字段名2... from 表名
		*注意
			*如果查询所有字段则可以使用*号来替代
	2. 去除重复
		*distinct
		*注意
			*只有查询的结果集中数据完全一样才可以去重复
	3. 计算列
		*一般可以使用四则运算计算一些列的值。（一般是对数字进行计算）
		*ifnull(表达式1,表达式2)：null参与运算，计算结果为null
			*表达式1：哪个字段需要判断为null
			*表达式2：为null后所替换的值
	4. 起别名
		*as:as也可省略使用空额替代

3. 条件查询
	1. where字句后边跟条件
	2. 运算符
		* > , < , <= , >= , = , <>
		* betweet .... and
		* like
			*占位符：（注意这里不能使用?和*号）
				* _:单个任意字符
				* %：多个任意字符
		* is null
		* and 或 &&
		* or 或 ||
		* not 或 ！
4. 排序查询
	*语法：order by 句子
		*order by 排序字段1 排序方式1，排序字段2 排序方式2,排序字段n 排序方式n;
		*排序方式
			*asc:升序（默认
			*desc:降序
		*注意：
			*如果有多个排序字段，则当前边的条件一样时才会判断后面对的条件
5. 聚合函数:将一列作为一个整体，进行纵向计算
	1. count:计算个数
		1. 一般选择非空字段
	2. max:计算最大值
	3. min:计算最小值
	4. sum:计算和
	5. avg:计算平均值
	*注意：使用聚合函数进行计算会排除null值
		*解决方法：
			1. 使用非空字段
			2. ifnull函数
6. 分组查询：
	1. 语法：group by 分组字段
	2. 注意:
		1. 分组之后查询的字段：分组字段，聚合函数
		2. where 和 having的区别
			1. where 在分组之前进行限定，如果不满足条件，这不参与分组。having在分组之后进行限定，不满足条件的，则不会被查询出来。
			2. where 后面不可以跟聚合函数，having后面可以进行聚合函数的判断。
7. 分页查询
	1. select * from student3 limit 开始的索引，每页查询的条数；
	2. 公式：开始的索引=（当前页索引-1）*每页显示的条数
	3. 分页操作是一个“方言”;
*--------------*

约束

*概念：对表中的数据进行限定，保证数据的的正确性、有效性和完整性。
*分类：
	1. 主键约束：primary key
	2. 非空约束：not null
	3. 唯一约束：unique
	4. 外键约束：foreign key
*非空约束：not null
	1. 创建表时添加约束
		create table stu(
		id int,
		name varchar(20) not null -- 定义不为空
		);
	2. 创建表完后添加
		alter table stu modify name varchar(20) not null;
	3. 删除name的非空约束
		alter table modify name varchar(20);

*唯一约束:unique,某一列的数据不能重复
	1. 注意：
		*唯一约束可以有null值，但是只有一列一条记录为null
	2. create table stu(
			id int,
			phone_number varchar(20) unique -- 唯一约束
		);
	3. 删除唯一索引
		*alter table 表名 drop index 字段名
	4. 添加唯一约束
		*alter table stu modify phone_number varchar(20) unique;
*主键约束：primary key
	1. 注意：
		1. 非空且唯一
		2. 一张表只能有一个字段为主键
		3. 主键就是表中记录的唯一标识
	2. 在创建表时,添加主键约束
		create table stu(
			id int primary key, -- 设置主键
			name varchar(20)
		);
	3. 删除主键
		alter table stu drop primary key;

	4. 创建表后添加主键
		alter table stu modify id int primary key;

	5. 自动增长
		1. 概念:如果某一列是数字类型的，使用 auto_increment 可以完成自动增长
		
		2. 创建表时，添加主键，并完成自动增长
			create table stu(
				id int primary key auto_increment,
				nmae varchar(20)
			);
		3. 删除自动增长
			alter table stu modify id int;
		4. 添加自动增长
			ALTER TABLE stu MODIFY id AUTO_INCREMENT;

	6. 外键约束：foreign key
		1. *创建表时创建外键
		create table 表名(
			....
			-- 外键列
			constraint 外键名称 foreign key (外键列名) references 主表名称(主表列名);
		);
		2. 删除外键
			*alter table 表名 drop foreign key 外键名称;
		3. 创建表后添加外键
			*alter table 表名 add constraint 外键名称 foreign key (外键列名) references 主表名称(主表列名);
		4. 级联操作
			1. 添加级联更新相关字段
				语法：alter table 表名 add constraint 外键名称 foreign key (外键列名) references 主表名称(主表列名) 级联操作(可以同时加多个操作用空格分隔);
			2. 级联操作分类
				1. on update cascade -- 级联更新相关字段
				2. on delete cascade --级联删除相关记录

数据库设计

1. 多表之间的关系
	1. 一对一（了解）
	2. 一对多:
		*实现方式：在多的一方建立外键，指向一的一方主键。
	3. 多对多:
		*实现方式：通过创建中间表。


2. 数据库设计的范式
	*概念：设计数据库时，需要遵循的一些规范。要遵循后边范式的要求，必须先遵循前边的所有范式要求。
		
		设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，越高范式数据库冗余越小。
		目前关系数据库有六种范式：第一范式（1NF），第二方式（3NF），第三范式（3NF），巴斯-科德范式（BCNF）,第四方式和第五范式（5NF，又称完美范式）。
	*分类:
		1. 第一范式（1NF）：每一列都是不可分割的原子数据项
		2. 第二范式（2NF）:在1NF的基础上，非主码属性必须完全依赖于码）（在1NF基础上消除非主属性对主码的部分函数依赖）
			*几个概念：
				1. 函数依赖：A-->B,如果通过A属性（属性组）的值，可以确定唯一B属性的值。则可以依赖于A
				2. 完全函数依赖：A-->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有的属性值。
				3. 部分函数依赖：A-->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组的某一些值即可。
				4. 传递函数依赖：A-->B,B-->C,如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性的值可以确定唯一C属性的值，侧称C传递函数依赖于A。
				5. 码：如果在一张表中，一个属性或属性组，被其他所有属性完全或部分依赖，则称这个属性（属性组）为该表的码。
					*主码：码属性组中的所有属性
					*非主码：除主码属性组的有所属性
		3. 第三方式（3NF）：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

数据库的备份和还原

1. 命令行
	*语法：mysqldump -u用户名 -p用户密码 数据名 > 保存路径\文件名.sql;
	*还原：
		1. 登录数据库
		2. 创建数据库
		3. 使用数据库
		4. 执行文件。source 文件路径

多表查询：

* 笛卡尔积：
	*有两个集合A,B 取这连个集合的所有组成情况
	*要完成多表查询，需要消除无用的数据
* 多表查询的分类：
	1. 内连接查询
		1. 隐式内连接
			语法：select 字段列表 from 表名1,表名2 where 表名1.链接字段=表名2.链接字段；
		2. 显示内连接
			语法：select 字段列表 from 表名1 inner(可省略) join 表名2 on 表1.链接字段=表2.链接字段；
	2. 外链接查询
		1. 左外链接：
			*语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
			*查询的是左表所有的数据及其交集部分。
		2. 右外链接：
			* 语法：select 字段列表 from 表1 right  [outer]  join 表2 on 条件;
		
	3. 子查询：
		*概念：查询中嵌套查询，称嵌套查询为子查询。
		*子查询不同情况
			1. 子查询的结果是单行单列:
				*子查询可以作为条件、使用运算符去判断。运算符：> >= < <= =
				*
				-- 查询工资小于平均工资
				select * from emp where salary<(select avg(salary) from emp);
			2. 子查询的结果是多行单列:
				*子查询可以作为条件，使用运算符in进行判断
				*
				--查询财务部和市场部所有的员工信息
				select * from emp where dept_id in(select id from dept where name="财务部" or name="市场部");
			3. 子查询的结果是多行多列：
				*子查询作为一张虚拟的表。
				 select * from dept t1,(select * from emp where join_date>"2015-1-1") t2 where t1.id=t2.dept_id;

事务

1. 事务的基本简介
	1. 概念：
		*如果一个包含多个步骤的业务被操作、被事务管理、那么这些操作要么同时成功、要么同时失败。
	2. 操作：
		1. 开启事务：start transaction;
		2. 回滚：rollback;
		3. 提交：commit;
	3. MYSQL数据库中事务默认自动提交
	
		*事务提交的两种方式：
			*自动提交：
				*mysql就是自动提交的
				*一条DML（增删改）语句会自动提交一次事务
			*手动提交：
				*Oracle 数据库时手动提交
				*需要先开启事务，再提交
		*修改事务的默认提交方式：
			*查看事务的默认提交方式：select @@autocommit;-- 1 代表自动提交  0 --代表手动提交。
			*修改默认提交方式：set @@autocommit=0;
2. 事务的四大特征：
	1. 原子性：是不可分割的最小操作单位、要么同时成功、要么同时失败。
	2. 持久性：当事务提交或回滚后、数据库会持久保存数据。
	3. 隔离性：多个事务之间。相互独立。
	4. 一致性：事务操作之后、数据总量不变。
	
3. 事务的隔离级别（了解）
	* 概念：多个事务之间隔离的、相互独立的。但是如果多个事务操作同一批数据、则会引发一些问题、设置不同的隔离级别就可以解决这些问题。
	* 存在的3问题：
		1. 脏读：一个事务、读到另一个事务中没有提交的数据
		2. 不可重复读（虚读）：在同一个事务中、两次读到的数据不一样。
		3. 幻读：一个事务操作（DML）数据表中的所有记录、另一个事务添加了一条数据、则第一个事务查询不到自己的修改。
	* 隔离级别：
																1. read uncommitted:读未提交
			*产生的问题：脏读、不可重复读、幻读
		2. read committed:读已提交(oracle默认)
			*产生的问题：不可重复读、幻读
		3. repeatable read：可重复读（MYSQL默认）
			*产生的问题：幻读
		4. serializable:创行化
			*可以解决所有问题

		* 注意：隔离级别从小到大安全性越来越高、但是效率越来越低
		*数据库查询隔离级别：
			*select @@tx_isolation;
		*数据库设置隔离级别：
			*set global transaction isolation level 级别字符串；

DCL:

*DCL:管理用户、授权
1. 用户管理
	1. 添加用户
		*语法：create user "用户名"@"主机名" identified by 密码;
	2. 删除用户
		*语法：drop user "用户名"@"主机名";
	3. 修改用户密码
		*方法一：set password for "用户名"@"密码" ="需要设置的新密码"
		*方法二：使用mysql数据库中的user表
				*update user set password=password("新密码") where user="需要修改的用户名"；

		*mysql中忘记了root的密码？
			*使用无验证登录：
				1. cmd-->net stop mysql 停止mysql服务
				2. mysqld –skip-grant-tables（在8.0版本中弃用）
				2. 8.0以上mysqld --defaults-file="指定配置文件" --console --skip-grant-tables --shared-memory
				3. 打开新cmd窗口，直接输入mysql命令。就可以登录成功
				4. 使用设置密码语句
				5. 关闭mysqld进程
				6. 使用新密码登录
	4. 查询用户
		--1. 切换到mysql数据库
			use mysql;
		--2. 查询user表
			select * from user;
		*通配符：% 表示可以任何主机使用，localhost表示只能在本地使用。	

2. 权限管理：
	1. 查询管理
		-- 查询权限
		show grants for "用户名"@"主机地址";
	2. 授权管理
		-- 授予权限
		grant 权限列表[all] on 数据库名[*].表名[*] to "用户名"@:"主机名";
	3. 撤销权限
		-- 撤销权限
		revoke 权限列表[all] on 数据库[*].数据表[*] for "用户名"@"主机名";