数据库学习（MySQL）

SQL

1. 什么是SQL：
   SQL事结构化查询语言，就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2. SQL通用语法

   1. SQL语句可以单行或多行书写，以分号结尾。

   2. 使用空格和缩进来增强可读性。

   3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

   4. 注释：

      单行注释： 
      1. -- 注释内容 
      	show databases; -- 查询所有的数据库名称
      			
      2. # 注释内容（MySQL特有）
      	show databases；#查询所有的数据库名称
      	show databases；# 查询所有的数据库名称
      			
      3. 多行注释：
      	/*注释内容*/
      

3. SQL的分类：

   1. DDL（Definition）数据定义语言：用于定义数据库对象：数据库，表，列等。
   	关键字：create，drop，alter等
   	
   2. DML（Manipulation）数据操作语言：用于对数据库中的表的数据进行增删改。
   	关键字：insert，delete。update等
   	
   3. DQL（Query）数据查询语言：用于查询数据库中表的记录(数据)。
   	关键字：select，where等
   	
   4. DCL（Control）数据控制语言（了解）：用于定义数据库的访问权限和安全级别，及创建用户。
   	关键字：GRANT，REVOKE等
   

DDL： 操作数据库、表

• 操作数据库：CRUD

1. C（Create）：创建

   基础创建:
   create database 数据库名;
   
   判断数据库是否已存在，存在则不创建:
   create database if not exists 数据库名;
   		
   设置数据库字符集:	
   create database 数据库名 character set  字符集名;
   	
   判断并设置字符集:		
   create database if not exists 数据库名 character set 字符集名;
   

2. R（Retrieve）：查询

   查询所有数据库的名称：
   show databases;
   			
   查询某个数据库的字符集：/查询某个数据库的创建语句
   show craeate database 数据库名称;
   

3. U（Update）：修改

   修改数据库的字符集
   alter database 数据库名 character set 字符集名称;
   

4. D（Delet）：删除

   删除数据库
   drop database 数据库名称；
   drop database if exists 数据库名称;
   

5. 使用数据库

   查询当前正在使用的数据库名称：
   		select database();
   		使用数据库
   		use 数据库名称;
   

• 操作表：

1. C（Create）：创建

   创建表：
   create table 表名(
   	列名1 数据类型1,
   	列名2 数据类型2,
   	......
   	列名n 数据类型n
   );
   注意：最后一列不需要加逗号“,”
   
   数据类型：
   	1. int:整数类型
   		age int;
   	2. double：小数类型
   		score double(2,5);
   	3. date：日期，只包含年月日，yy-MM-dd
   	4. datetime：日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
   	5. timestamp：时间戳类型 包含年月日时分秒 yyy-MM-dd HH:mm:ss
   	如果将来不给这个字段赋值，或者赋值为null，则默认使用当前系统时间来自动赋值。
   	6. varchar：字符串
   	name varchar(20);
   	姓名最大20字符
   
   复制表: create table 表名 like 被复制表名;
   

   更多：https://www.runoob.com/mysql/mysql-data-types.html

   例子：
   	create table student(
   		id int,
   		name varchar(50),
   		age int ,
   		score double(4,1),
   		birthday date,
   		insert_time timestamp
   	);
   

2. R（Retrieve）：查询

   查询某个数据库中所有表的名称
   	show tables;
   查询表结构：
   	desc 表名称;
   

3. U（Update）：修改

   修改表名:
   	alter table 表名 rename to 新表名;
   修改表的字符集:
   	查看: show create table 表名;
   	修改：show table 表名 character set 字符集名称;
   添加列:
   	alter table 表名 add 列名 数据类型;
   修改列名称，类型:
   	alter table 表名 change 列名 新列名 新数据类型;
   	alter table 表名 modify 列名 新数据类型;
   删除列:
   	alter table 表名 drop 列名;
   

4. D（Delet）：删除

   drop table 表名;
   drop table if exists 表名
   

DML：增删改表中数据

1.添加数据：

insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);

注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值。
	insert into 表名 values(值1,值2,...,值n);
3.除了数字类型，其他类型需要使用引号（单双都可以）引起来

2.删除数据：

delete  from 表名 [where 条件]

注意：
如果不加条件，则删除表中所有数据。
如果要删除所有记录
1. delete from 表名;   #不推荐使用。因为有多少条记录就会执行多少次删除操作。
2. TRUNCATE TABLE 表名;  #推荐使用，删除表然后再创建一个一摸一样的空表。

3.修改数据：

update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件]

注意：
如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

select * from 表名; #查询表中所有数据

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

2.基础查询
	1.多个字段的查询
		select 字段名1,字段名2....from 表名;
		00 select name,id,address from student; #查询字段名字，编号，地址在学生表中。

		如果要查询所有字段，则可以使用*来代替字段列表。
		-- select * from student;
		
	2.去重
		distinct
		-- select distinct address from student; #唯一地址
	3.计算列
		一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
		-- select english + meth from student;
		
		ifnull(表达式1,表达式2): null参与的计算，计算结构都为null
		-- select ifnull(english,0) + ifnull(meth,0) from student;
		表达式1: 那个字段需要判断是否为null
		表达式2: 如果该字段为null后的替换值。
	4.起别名:
		as: as也可以省略
		-- select ifnull(english,0) + ifnull(meth,0) as 总分 from student;
		-- select ifnull(english,0) + ifnull(meth,0) 总分 from student;
	
3.条件查询:
	1.where子句后跟条件
	2.运算符
		>、<、<=、>=、=、< >
		between ... and
		in(集合)
		like -- 模糊查询
			占位符：
			_ : 单个任意字符
			% : 多个任意字符
		is null
		and 或 &&
		or 或 ||
		not 或 !
	
	例子:
	-- 查询年龄大于等于20岁
	select * from student where age >= 20;
	
	--查询年龄不等于20岁
	select * from student where age <> 20;
								(age != 20);
	
	--大于等于20，小于等于30
	select * from student where age>=20 && age <=30;
										(and)
	select * from student where between 20 and 30;
	
	--22岁，19岁，25岁
	select * from student where age = 22 or age = 19 or age = 25; 
	select * from student where age in(22,19,25);
	
	-- 查询英语为null
	select * from student where english = null; 
	-- 查询失败，原因为null值不能使用 =(!=) 判断
	select * from student where english is null; 
	
	-- 查询英语不为null
	select * from student where english is not null; 

	-- 查询姓马的有哪些？ like
	select * from student where name like '马%';
	-- 查询第二个字是华的人
	select * from student where name like '_华%';
	-- 查询三个字的人
	select * from student where name like '___';
	# 当出现其他结果时，可能时数据有问题里面有空字符
	
	-- 查询姓名中包含马的人
	select * from student where name like '%马%';
	

DQL：查询语句

1.排序查询
	语法: order by 子句
		order by 排序字段1 排序方式1,排序字段2 排序方式2,....
		
		select from student order by math; 
	
		第二排序条件只有在第一排序条件一样时，才会启用。
		
		-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩查询
		select * from student order by math ASC,english ASC;
		
		排序方式:
			ASC:升序,默认
			select from student order by math ASC; 
			
			DESC:降序
			select from student order by math DESC; 
			
			
2.聚合函数:将一列数据作为一个整体，进行纵向的运算
	1.count: 计算个数
		1.一般选择非空的列:主键
		2.count(*): 有一列不为NULL就算一条数据，但不推荐使用。
	2.max: 计算最大值
	3.min: 计算最小值
	4.sum: 求和
	5.avg: 计算平均值
	
	注意: 聚合函数的计算会排除null值。
		解决: 
			1.选择非空列
			2.IFNULL函数
	
	select count(IFNULL(english,0)) from student;
	select count(english) from student;
3.分组查询:
	语法: group by 分组字段;
	注意: 
		1.分组之后查询的字段:分组字段、聚合函数
		2.where 和 having 的区别:
			1.where在分组前进行限定，如果不满足条件，则不参与分组; having在分组后进行限定，如果不满足结果，则不会被查询出来
			2.where后不可以跟聚合函数，having可以进行聚合函数的判断。
		
	-- 按照性别分组、分组查询男、女同学的平均分
	SELECT sex,AVG(math) FROM student GROUP BY sex;
	-- 按照性别分组、分组查询男、女同学的平均分、人数
	SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
	-- 按照性别分组、分组查询男、女同学的平均分、人数，要求分数低于70分的人不参与分组
	SELECT sex,AVG(math),COUNT(id) FROM student where math>70 GROUP BY sex;
	-- 按照性别分组、分组查询男、女同学的平均分、人数，要求分数低于70分的人不参与分组,分组后人数大于2个人
	SELECT sex,AVG(math),COUNT(id) FROM student where math>70 GROUP BY sex HAVING COUNT(id)>2;

	SELECT sex,AVG(math),COUNT(id) as 人数 FROM student where math>70 GROUP BY sex HAVING 人数>2;sex;

4.分页查询:
	1.语法: limit 开始的索引,每页查询的条数;
	2.公式: 开始的索引 = （当前的页码 - 1）* 每页显示的条数	
		-- 每页显示3条记录
		SELECT * FROM student LIMIT 0,3; -- 第一页
		SELECT * FROM student LIMIT 3,3; -- 第二页
		SELECT * FROM student LIMIT 6,3; -- 第三页
	3.limit 是一个MySQL“方言”
	

约束：

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：
	1.主键约束：primary key
	2.非空约束：not null
	3.唯一约束：unique
	4.外键约束：foreign key

非空约束：not null
	1.在创建表时添加约束
	create table stu(
		id int,
		name VARCHAR(10) not null
	);
	
	2.创建表后添加非空约束
	ALTER TABLE stu change NAME NAME VARCHAR(20) not NULL;

	3.删除name的非空约束
	ALTER TABLE stu change name NAME VARCHAR(20);

唯一约束: unique,某一列的值不能重复
	1.注意:
		唯一约束可以有null值，但是只能有一条记录为null。
	2.创建表时，添加唯一约束
		CREATE TABLE stu2(
			id int,
			phone_number VARCHAR(20) UNIQUE-- 手机号
		);
	3.在表创建完后，添加唯一约束
		ALTER TABLE stu2 modify phone_number VARCHAR(20) UNIQUE;


	4. 删除唯一约束:
		ALTER TABLE stu2 DROP INDEX phone_number;

主键约束: primary key
	1.注意：
		1.含义：非空且唯一
		2.一张表只能有一个字段为主键
		3.主键就是表中记录的唯一标识
	
	2.在创建表时，添加主键约束
	create table stu(
		id int primary key, -- 给id添加主键约束
		name varchar(20)
	);
	

	3.删除主键
	alter TABLE stu3 DROP PRIMARY KEY;
	
	4.创建表后，添加主键
	ALTER TABLE stu3 MODIFY id int PRIMARY key;
	
	5.自动增长
		1.概念: 如果某一列时数值类型的，使用auto_increment可以来完成值的自动增长。
		2.在创建表时，添加主键约束，并且完成主键自增长
		create table stu(
			id int primary key AUTO_INCREMENT, -- 给id添加主键约束
			name varchar(20)
		);
		3.删除自动增长
		ALTER TABLE stu MODIFY id int;
		
		4.添加自动增长
		ALTER TABLE stu MODIFY id int auto_increment;

外键约束: foreign key,让表于表产生关系,从而保证数据的正确性
	1.在添加表时，可以添加外键
	语法：
		create table 表名(
			....
			外键列
			constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) 
			-- 简化 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 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
		2.分类
			1.级联更新: ON UPDATE CASCADE
			2.级联删除: ON DELETE CASCADE

数据库的设计：

多表之间的关系

1. 分类：

   1. 一对一：
      如：人和身份证
      分析：一个人只有一个身份证，一个身份证只能对应一个人

   2. 一对多(多对一):
      如：部门和员工
      分析：一个部门有多个员工，一个员工只能对应一个部门

   3. 多对多：
      如：学生与课程
      分析： 一个学生可以选多门课程，一个课程也可以被很多学生选择

2. 实现关系:
   1.一对多(多对一)：
   实现方式：在多的一方建立外键，指向一的一方的主键。
   

   2.多对多：
   实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
   

   3.一对一
   实现方式：可以在任意一方添加唯一外键指向另一方的主键
   

3. 案例：
   

CREATE TABLE tab_category(
	cid int PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

CREATE table tab_route(
	rid int PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate Date,
	cid int,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

CREATE TABLE tab_user(
	uid int PRIMARY key auto_increment,
	username VARCHAR(100) UNIQUE not null,
	password  VARCHAR(100)not null,
	name VARCHAR(100),
	birthday date,
	sex char(1) DEFAULT '男',
	telephone VARCHAR(11),
	email VARCHAR(100)
);


CREATE TABLE tab_favorite(
	rid int, -- 线路id
	date datetime,
	uid int, -- 用户id
	-- 创建复合主键
	PRIMARY KEY(rid,uid), -- 联合主键
	FOREIGN KEY(rid) REFERENCES tab_route(rid),
	FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

数据库设计的范式

1. 概念：在设计数据库时，需要遵循的一些规范。要遵循后边的范式，必须先遵循前边的所有范式要求
   1. 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
   2. 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
2. 分类：

   1. 第一范式（1NF）：每一列都是不可分割的原子数据项
      

   2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
      

       几个概念：
       	1.函数依赖：A ➡ B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称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密码 数据库名称 > 保存的路径
	还原：
		1.登录数据库
		2.创建数据库
		3.使用数据库
		4.执行文件。source 文件路径

2.图形化工具：
备份：右键数据库 ➡ 转储为sql文件

还原：用户区域右键 ➡ 运行sql文件

多表查询：

查询语法：
	select 
		列名列表
	form
		表名列表
	where
		....
# 创建部门表
create table dept(
	id int primary key AUTO_INCREMENT,
	NAME varchar()
);

insert into dept(NAME) values ('开发部'),('市场部'),('财务部');

# 创建员工表
CREATE TABLE emp(
  id int primary key AUTO_INCREMENT,
  NAME varchar(10),
  gender char(1), -- 性别
  salary double, -- 工资
  join_data date, -- 入职时间
  dept_id int L,
  FOREIGN KEY (`dept_id`) REFERENCES dept (id) -- 外键，关联部门表
);
# 员工信息
INSERT INTO emp(NAME,gender,salary,join_data,dept_id) VALUES ('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_data,dept_id) VALUES ('猪八戒','男',3600,'2021-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_data,dept_id) VALUES ('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_data,dept_id) VALUES ('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_data,dept_id) VALUES ('蜘蛛女','女',4500,'2011-03-14',1);

笛卡尔积：
	* 有两个集合A，B，取这两个集合的所有组合情况。
	* 要完成多表查询，需要消除无用的数据。

多表查询的分类：
	1. 内连接查询：
		1. 隐式内链接：使用where条件消除无用数据
			* 例子：
			-- 查询所有的员工信息和对应的部门信息
			SELECT * from emp,dept where emp.dept_id = dept.id;
			-- 查询员工表的名称，性别，部门的名称
			SELECT
				t1.name,
				t1.gender,
				t2.name
			FROM
				emp t1,dept t2
			where 
				t1.dept_id = t2.id;
		2. 显式内连接：
			* 语法：select 字段列表 from 表名 inner join 表名2 on where;
			* 例子：
				* SELECT * FROM emp [INNER] JOIN dept ON emp.dept_id = dept.id;
				* SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
		3. 内连接查询：
			1. 从哪些表中查询数据
			2. 查询条件
			3. 查询哪些字段
	2. 外连接查询：
		1.左外连接：
			* 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
			* 查询的时左表所有数据以及其交集部分。
		2.右外连接
			* 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
			* 查询的时右表所有数据以及其交集部分。
	3. 子查询：
		* 查询中嵌套查询，称嵌套查询为子查询
		-- 查询工资最高的员工信息
		-- 1.查询最高的工资是多少	
		SELECT MAX(salary) FROM emp;
		-- 2.查询员工信息，并且工资等于9000的
		SELECT * FROM emp where emp.salary = 9000;
		-- 3.一条sql完成操作
		SELECT * FROM emp where emp.salary = (SELECT MAX(salary) FROM emp);
		* 子查询的不同情况
			1.子查询的结果是单行单列的：
				*子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =
				-- 查询员工工资小于平均工资的人
				SELECT * FROM emp where emp.salary < (SELECT AVG(salary) FROM emp);
			
			2.子查询的结果是多行单列的：
				* 子查询可以作为条件，使用运算符in来判断
			-- 查询‘财务部’所有员工信息
			SELECT id FROM dept WHERE `NAME` = '财务部' OR `NAME` = '市场部';
			SELECT*FROM emp WHERE dept_id=3 OR dept_id=2;
			-- 子查询
			SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE `NAME` = '财务部' OR `NAME` = '市场部');
			
			3.子查询的结果是多行多列的：
			* 子查询可以作为一张虚拟表。
			-- 查询员工的入职日期是2011-11-11之后的员工信息和部门信息
			SELECT*FROM dept t1,(SELECT*FROM emp WHERE emp.join_data > '2011-11-11' )as t2 WHERE t1.id = t2.dept_id;
			
			-- 普通内连接
			SELECT *FROM emp t1,dept t2 WHERE t1.dept_id = t2.id and t1.join_data > '2011-11-11';

事务：

1. 事务的基本介绍

   1. 概念：
      • 如果一个包含多个步骤的业务操作，被事务管理，要么这些操作同时成功，要么同时失败。
   2. 操作：

      1. 开启事务： start transaction;
      2. 回滚：rollback;
      3. 提交：commit;
      
      -- 张三给李四转帐500元
      -- 0.开启事务
      	start TRANSACTION;
      -- 1.张三账户 -500
      	UPDATE account set balance = balance -500 WHERE name = 'zhangsan';
      -- 2. 李四账户 +500
      	-- 出错了。。。。。。
      	UPDATE account set balance = balance +500 WHERE name = 'lisi';
      	-- 发现事务没有问题，提交事务
      	COMMIT;
      	-- 发现问题，回滚事务
      	ROLLBACK;
      
      4. 在MySQL中事务默认自动提交 
      	* 一条DML（增删改）语句会自动提交一次事务
      	*  事务提交的两种方式：
      		* 自动提交
      			* MySQL就是自动提交
      		* 手动提交：
      			* Oracle 数据库默认是手动提交事务
      			* 需要先开启事务，在提交 
      	* 修改事务的默认提交方式：
      		* 查看事务的默认提交方式：
      		 	SELECT @@autocommit; -- 1 代表自动提交	0 代表手动提交
      		* 修改事务提交方式：set @@autocommit = 0;
      

2. 事务的四大特征

   • 原子性：是不可分割的最小操作单位。要么同时成功，要么同时失败。
   • 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
   • 隔离性：多个事务之间相互独立。
   • 一致性：事务操作前后，数据总量不变。

3. 事务的隔离级别(了解)

   * 概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
   * 存在问题：
   	1. 脏读：一个事务，读取到另一个事务中没有提交的数据
   	2. 不可重复读（虚读）：在同一个事务中，两次读到的数据不一样。
   	3. 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
   * 隔离级别：
   	* read uncommitted：读未提交
   		* 产生的问题：脏读，虚读、幻读
   	* read committed：读已提交（Oracle）
   		* 产生的问题：虚读、幻读
   	* repeatable read：虚读（MySQL默认）
   		* 产生的问题：幻读
   	* serializable：串行化
   		* 可以解决所有的问题 
   	
   	* 注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
   	* 数据库查询隔离级别：
   		* select @@tx_isolation;
   	* 数据库设置global隔离级别：
   		* set global transaction isolation level 级别字符串;
   
   	* 演示：
   		set global transaction isolation level read uncommitted;
   		start transaction;
   		-- 转账操作
   		UPDATE account set balance = balance - 500 WHERE id = 1;
   		UPDATE account set balance = balance + 500 WHERE id = 2;
   		
   

DCL:

• SQL分类：

  • DDL：操作数据库和表
  • DML：增删改表中数据
  • DQL：查询表中数据
  • DCL：管理用户，授权

• DBA：数据库管理员

• DCL：管理用户，授权

1. 管理用户
	1.	添加用户：
		* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
	2.	删除用户：
		DROP USER '用户名'@'主机名';
	3.	修改用户密码：
		 --update语句实践后无法修改，建议使用alter语句
		* update mysql.user set authentication_string=password('新密码') where user='用户名' ;
		* update user SET password=PASSWORD('新密码') WHERE USER='用户名' and host='主机名'; 
		* set PASSWORD for '用户名'@'主机名' = PASSWORD('新密码'); -- 无法使用
		 
		ALTER USER 'lishi'@'localhost' IDENTIFIED BY 'abc';

		* MySQL中忘记root密码，
		

		>https://cloud.tencent.com/developer/article/1855931

	4.	查询用户：
		-- 1. 切换到MySQL数据库
		user mysql;
		-- 2.查询user表
		SELECT * FROM `USER`;
		
		* 通配符：%表示可以在任意主机使用用户登录数据库
2. 授权管理：
	1.查询权限
		show grants for '用户名'@'主机名';
		show grants for 'root'@'localhost';
	2.授予权限
		grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
		grant SELECT,DELETE,UPDATE on db1.account to 'lisi'@'localhost';
		
		-- 给张三用户授予所有权限，在任意数据库任意表上
		grant ALL on *.* to 'zs'@'localhost';
	3.撤销权限
		语法：revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
	

MySQL（主要内容为该部分以上）

前期准备

navicat快速运行： ctrl+r;
navicat自动增加快捷键：‘shift+insert’;

启动mysql服务：net start mysql

关闭mysql服务：net stop mysql

MySQL登录：mysql -u用户名 -p密码（密码选填，填了之后就是外界可见）
mysql --host=ip --user= --password=

访问远程数据库： mysql -hip -u -p（连接目标的ip、用户名与密码）

MySQL退出 exit 或 quit

配置my.ini：my.ini位于MySQL server中。

mysql目录结构

1.安装目录：
- 配置文件 my.ini
2. 数据目录：
- 数据库：文件夹
- 表：文件
- 数据：文件存储数据

客户端：

[client]
no-beep
port=3306 //端口
[mysql]
default-character-set=utf-8  //默认字符集

服务器：

[mysqld]
default-time_zone='+08:00'
#skip-grant-tables

#skip-name-resolve
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\ProgrammingRelated\\Mysql
# 设置mysql数据库的数据的存放目录
datadir=D:\\ProgrammingRelated\\Mysql\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

数据库

数据库是一个统一管理的数据集合，由DBMS统一管理。

创建数据库：

Create { Database | Schema } [ if not exists ] 
 <数据库名称>
[ create_specification , … ]
其中create_specification的可选项如下：
[ Default ] Character Set <字符集名称> 
| [ Default ] Collate <排序规则名称>

例如：
create database  demo;
create schema  demo;

【说明】：

• 中括号[ ]中的内容为可选项，其余为必须书写的项；二者选其一的选项使用“|”分隔；多个选项或参数列出前面1个选项或多个选项，使用“…”表示可有多个选项或参数。

• Create Database为创建数据库的必须项，不能省略。

• 由于MySQL的数据存储区将以文件夹方式表示MySQL数据库。因此，命令中的数据库名称必须符合操作系统文件夹命名规则。MySQL中不区分大小写。

• if not exists：为可选项，在创建数据库之前，判断即将创建的数据库名是否存在。如果不存在，则创建该数据库。如果数据库中已经存在同名的数据库，则不创建任何数据库。但是，如果存在同名数据库，并且没有指定if not exists，则会出现错误提示。

• create_specification：用于指定数据库的特性。数据库特性存储在数据库文件夹中的db.opt文件中。Default指定默认值，Character Set子句用于指定默认的数据库字符集，Collate子句用于指定默认的数据库排序规则。

• MySQL中，每一条SQL语句都以 “ ; ” 作为结束标志。

ASCII对照表

• 字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。
• ASCII字符集中四个字母：‘A’、‘B’、‘a’、‘b’，每个字母赋予一个数值：‘A’=65，‘B’= 66，‘a’= 113，‘b’= 114。字母‘A’是一个符号，数字65是‘A’的编码，字母和它们的编码组合在一起是一个字符集。
• 假设我们希望比较两个字符串的值：‘A’和‘b’。比较的最简单的方法是查找编码：‘A’为65，‘b’为114。因为65 小于114，我们可以说‘A’小于‘b’。我们做的仅仅是在我们的字符集上应用了一个校对规则。
• MySQL支持30多种字符集的70多种校对规则。在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串。
  • 两个不同的字符集不能有相同的校对规则。
  • 每个字符集有一个默认校对规则。
    ——例如，latin1默认校对规则是latin1_swedish_ci，而gb2312默认校对规则是gb2312_chinese_ci。
• ASCII 码字符集：使用7 位二进制数来表示所有的大写和小写字母，数字0 到9、标点符号， 以及在美式英语中使用的特殊控制字符
• GB2312是中国国家标准的简体中文字符集。 GB2312收录简化汉字及一般符号、序号、数字、拉丁字母、日文假名、希腊字母、俄文字母、汉语拼音符号、汉语注音字母，共 7445 个图形字符。

查看数据库：

show batabases;

选择数据库：

use 数据库名;

删除数据库：

drop database 数据库名;

查看MySQL支持储存引擎类型：

show engines;

查看默认存储引擎：

show variables like '%storage_engine%';

备份数据库：

（1）备份单个数据库中所有的数据表
	使用“mysqldump”命令备份单个数据库中所有数据表的基本语法格式如下：		
		mysqldump –u 用户名 –p 数据库名>备份文件名 
	
	也可以写成以下形式：
		mysqldump –u 用户名 –p –-databases 数据库名>备份文件名
（2）备份单个数据库中指定的数据表

	使用“mysqldump”命令备份一个数据库或数据表的基本语法格式如下：	
		mysqldump –u 用户名 –p 数据库名 数据表名>备份文件名
		
	如果需要指定多个数据表，则在数据库名的后面列出多个数据表名，并使用空格分隔。
	
（3）备份多个数据库

	使用“mysqldump”命令备份多个数据库的基本语法格式如下：	
		mysqldump –u 用户名 –p –-databases 数据库名1 数据库名2 …>备份文件名
		
	多个数据库名之间使用空格分隔。备份完成后，备份文件中将会存储多个数据库的信息。
（4）备份所有的数据库
	使用“mysqldump”命令备份MySQL服务器中所有数据库的基本语法格式如下：
		mysqldump –u 用户名 –p --all-databases>备份文件名

	备份完成后，备份文件中将会存储全部数据库的信息。
	

还原数据库：

通过使用“mysqldump”命令将数据库中的数据备份成一个文本文件。

备份文件中通常包含“Create”语句和“Insert”语句。

通过使用“mysql”命令来还原备份的数据，

“mysql”命令可以执行备份文件中的“Create”语句和“Insert”语句。

通过“Create”语句来创建数据库和数据表，通过“Insert”语句来插入备份的数据。

“mysql”命令基本语法如下：
	mysql –u root –p [ 数据库名 ]<备份文件名

MySQL数据类型

在MySQL中，系统数据类型主要分为数值类型、字符串类型、日期类型和特殊类型4种。

数值类型

用于存放数字数据类型的，包括整数与小数。具有数学含义，能够直接参加数值运算（例如求和，求平均数等）的数据。还例如数量、单价、金额、比例等方面的数据。

整数类型

小数类型

浮点数：
FLoat —— 单精度浮点类型
Double —— 双精度浮点类型
定点数：
Decimal —— 定点类型

浮点类型和顶点类型都可以用（M,N）来表示，M表示有效位数，也称精度；
N表示小数位数。

Float和Double在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定）存储，Decimal如不指定精度默认值为（10，0)。

如果不需要小数部分，则使用整数；如果需要表示小数部分，则使用浮点数类型。对于浮点数据，存入的数值会按字段定义的小数位进行四舍五入。浮点类型包括Float和Double类型，Double类型精度比Float类型要高，因此，如果要求存储精度较高时，应使用Double类型，如果精度较低的小数，则使用Float类型。

字符串类型

主要用于存储字符串或文本信息。
主要包含Char、Varchar、Binary、Varbinary、Text等类型

日期类型

在程序中给日期时间类型字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合相应类型的格式即可。

枚举类型

所谓枚举类型，就是指定数据只能取指定范围内的值。语法格式如下：

<字段名> Enum(<'值1'> , <'值2'> , <'值n'> )

其中“字段名”指将要定义的字段，值n指枚举列表中的第n个值。
Enum类型的字段在取值时，只能在指定枚举列表中取，一次只能取一个。
如果创建的成员中有空格时，其尾部的空格将自动被删除。
例如：Sex Enum(‘男’ , ‘女’ )

Set类型

Set类型也称为集合类型，是一个字符串对象，可以有零个或多个值，Set字段最多可以有64个成员，其值为表创建时规定的一列值。
语法格式如下：

Set(<'值1'> , <'值2'> , <'值n'> )

例如，Set(‘春’ , ‘夏’ , ‘秋’ , ‘冬’)

bit类型

bit类型主要用来定义一个指定位数的数据，其取值范围为1～64，它所占用的字节数是根据它的位数决定的。

Blob类型

Blob类型是一个二进制大对象，用来存储可变数量的二进制字符串。
Blob类型分为4种：TinyBlob、Blob、MediumBlob和LongBlob，它们可容纳的最大长度不同，分别为255个字符、65535个字符、16777215个字符、4294967295个字符。

类型使用

• 整数类型和浮点类型

  如果不需要小数部分，则使用整数；如果需要表示小数部分，则使用浮点数类型。对于浮点数据，存入的数值会按字段定义的小数位进行四舍五入。浮点类型包括Float和Double类型，Double类型精度比Float类型要高，因此，如果要求存储精度较高时，应使用Double类型，如果精度较低的小数，则使用Float类型。

• 浮点类型和定点类型

  浮点类型（Float和Double）相对于定点类型Decimal的优势是，在长度一定的情况下，浮点类型比定点类型能表示更大的数据范围，其缺点是容易产生计算误差。Decimal在MySQL中是以字符串形式存储的，用于存储精度相对要求较高的数据(如货币、科学数据等)。两个浮点数据进行减法或比较运算时容易出现问题，如果进行数值比较，最好使用Decimal类型。

• 日期类型和时间类型

  MySQL对于不同种类的日期和时间有很多种数据类型，例如Year和Time。只需要存储年份，则使用Year类型即可；如果只记录时间，只须使用Time类型即可。
  如果同时需要存储日期和时间，则可以使用DateTime或TimeStamp类型。存储范围较大的日期最好使用DateTime类型。

  TimeStamp类型也有DateTime类型不具备的属性，默认情况下，当插入一条记录但并没有给TimeStamp类型字段指定具体的值时，MySQL会把TimeStamp字段设置为当前的时间。因此当需要插入记录同时插入当前时间时，使用TimeStamp类型更方便。

• Char类型和Varchar类型

  Char类型是固定长度，Varchar类型是可变长度，Char类型可能会浪费一些存储空间，Varchar类型则是按实际长度存储，比较节省空间。
  对于Char(n)如果存入字符数小于n，则会自动以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格。而Varchar类型查询时不会删除尾部空格。

  Char类型数据的检索速度要比Varchar类型的快。Char(n)是固定长度，例如，Char(4)不管是存入几个字符，都将占用4个字节。Varchar是存入的“实际字符数+1”个字节（n<=255）或实际字符数+2”个字节(n>255)，所以Varchar(4)，存入3个字符将占用4个字节。对于存储的字符串长度较小，但在速度上有要求的可以使用Char类型，反之则可以使用Varchar类型。

  对于MyISAM存储引擎，最好使用固定长度的类型代替可变长度的类型，这样可以使整个数据表静态化，从而使数据检索更快，用空间换时间。

• Varchar类型和Text类型

  Varchar类型可以指定长度n，Text类型则不能指定，存储Varchar类型数据占用“实际字符数+1”个字节（n<=255）或“实际字符数+2个”字节(n>255)，存储Text类型数据占用“实际字符数+2”个字节。Text类型不能有默认值。
  Varchar查询速度快于Text，因为Varchar可直接创建索引，Text创建索引要指定前多少个字符。当保存或查询Text字段的值时，不删除尾部空格。

• Enum类型和Set类型

  Enum类型和Set类型的值都是以字符串形式出现的，但在数据库中存储的是数值。
  Enum类型只能取单值，它的数据列表是一个枚举集合，它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用Enum类型，例如，性别字段适合定义为Enum类型，只能从“男”或“女”中取一个值。

  Set可取多值，它的合法取值列表最多允许有64个成员。空字符串也是一个合法的Set值。在需要取多个值的时候，适合使用Set类型，例如，要存储一个人的兴趣爱好，最好使用Set类型。

• Blob类型和Text类型

  Blob类型存储的二进制字符串，Text类型是非二进制字符串，两者均可存放大容量的信息。Blob类型主要存储图片、音频信息等，而Text只能存储纯文本内容。