01-MySQL之基础篇【SQL语句、备份与还原、DB约束、范式、多表查询】

一、数据库基本知识

`1. 数据库的英文单词： DataBase 简称 ： DB
`2. 什么数据库？
	* 用于存储和管理数据的仓库。

`3. 数据库的特点：
	1. 持久化存储数据的。其实数据库就是一个文件系统
	2. 方便存储和管理数据
	3. 使用了统一的方式操作数据库 -- SQL

`1. 安装
   暂时省略
`2. 卸载
	1. 去mysql的安装目录找到my.ini文件
		* 复制 datadir="C:/ProgramData/MySQL/MySQL Server 8.2/Data/"
	2. 卸载MySQL
	3. 删除C:/ProgramData目录下的MySQL文件夹。
	
`3. 配置
	* MySQL服务启动
		1. 手动。
		2. cmd--> services.msc 打开服务的窗口
		3. 使用管理员打开cmd
			* net start mysql : 启动mysql的服务
			* net stop mysql:关闭mysql服务
	* MySQL登录
		1. mysql -uroot -p密码
		2. mysql -hip -uroot -p连接目标的密码
		3. mysql --host=ip --user=root --password=连接目标的密码
	* MySQL退出
		1. exit
		2. quit

	* MySQL目录结构
		1. MySQL安装目录：basedir="D:/develop/MySQL/"
			* 配置文件 my.ini
		2. MySQL数据目录：datadir="C:/ProgramData/MySQL/MySQL Server 8.2/Data/"
			* 几个概念
				* 数据库：文件夹
				* 表：文件
				* 数据：数据

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

二、SQL的常用语句

重点:练习笔记中DML和DQL 语句;

1、SQL之DDL语句

#单行注释
-- 单行注释
/*多行注释*/

---------------------------------------------------------------
一、 数据库定义
-- 1. 创建数据库
-- create database [if not exists] 数据库名称 [character set 编码集];
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET gbk;

-- 2. 查看数据库
SHOW DATABASES;
--  查看sql的创建
SHOW CREATE DATABASE test_db1;

-- 3. DDL修改,删除和切换数据库
-- 1)修改数据库: 将数据库的字符集修改,语法格式:
ALTER DATABASE test_db1 CHARACTER SET utf8;
-- 2)删除数据库
DROP DATABASE test_db1;
-- 3)查看正在使用的数据库
SELECT DATABASE();
-- 4)切换数据库
USE test_db1;

===========================================================================

二、数据表定义
-- 1. 创建学生表
CREATE TABLE student(
	id INT,
	NAME VARCHAR(32),
	gender CHAR(2);
	age INT ,
	# yuwen double(4,1), #语文成绩
	birthday DATE,
	address VARCHAR(50)
	#insert_time timestamp
);

/*
* 注意：最后一列，不需要加逗号（,）
* 数据库类型：
1. int：整数类型
	* age int,
2. double:小数类型
	* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型	包含年月日时分秒  yyyy-MM-dd HH:mm:ss	
	* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串 (可变)
	* name varchar(20):姓名最大20个字符
	* zhangsan 8个字符  张三 2个字符
   char(m): （定长），余下字节自动补全 m:0~255之间
*/


-- 2. 查看表信息
-- 2.1 查看某个数据下的所有表
SHOW TABLES;
-- 2.2 查看表结构包含字段信息 description
DESC student;
-- 2.3 查看创建表的SQL信息
SHOW CREATE TABLE student;


-- 3. 删除表信息
DROP TABLE IF EXISTS student;
/*说明:
 drop:删除关键字
 table:表示删除的对象是表类型
 if exists:表示如果存在就删除,不存在则不做操作(不会报错)
*/

-- 4. 表字段DDL操作
-- 4.1为表添加字段
ALTER TABLE student ADD 字段名 类型;
-- 4.2修改表中字段的类型
ALTER TABLE student MODIFY 字段名 新的类型;
-- 4.3修改列名
ALTER TABLE student CHANGE 旧字段名 新字段名 类型;
-- 4.4删除列
ALTER TABLE student DROP 字段名;
-- 例如：
ALTER TABLE student ADD address VARCHAR(100);


-- 5. 整表属性DDL修改
-- 1.修改表名
RENAME TABLE student TO 新表名;
-- 6.修改字符集
ALTER TABLE student CHARACTER SET 字符集;

2、SQL之DML语句

01、insert插入语句

-- 1.单条插入
INSERT INTO 表名 (字段名1, 字段名2,...) VALUES (字段值1, 字段值2,...);
-- 如果插入的是全部字段,表名后的字段名称可以忽略不写,格式如下
INSERT INTO 表名 VALUES (字段值1, 字段值2,...);
-- 2.批量插入
INSERT INTO 表名 VALUES (字段值1, 字段值2,...), (字段值1, 字段值2,...),......;
-- 例如：添加学生
INSERT INTO student(id,NAME,gender,address,birthday) 
VALUES (1,'张三','男',18,'上海','1999-9-09'); 
INSERT INTO student(id,NAME,gender,address,birthday) 
VALUES (2,'李四','男',18,'安徽','1999-9-09'); 
INSERT INTO student(id,NAME,gender,address,birthday) 
VALUES (3,'王五','女',17,'北京','1999-9-09'); 
INSERT INTO student(id,NAME,gender,address,birthday) 
VALUES (4,'赵六','男',20,'上海','1999-9-09'); 

02、update更新语句

-- 1.无条件全表更新,语法格式(开发中慎用):
UPDATE 表名 SET 字段名=新的值,字段名=新的值,..;
-- 2.带条件修改
UPDATE 表名 SET 字段名=新的值,字段名=新的值,.. WHERE 条件;
-- 例如：
UPDATE student SET gender='女';
UPDATE student SET gender=''男'' WHERE id=2;

03、delete删除语句

-- 1.无条件全表删除,语法格式(开发中慎用):
DELETE FROM 表名;

-- 2.带条件删除
DELETE FROM 表名 WHERE 条件;
-- 例如：
DELETE FROM student;
DELETE FROM student WHERE id=3;

-- truncate删除(属于DDL)
TRUNCATE TABLE 表名; 
-- 说明:truncate表示清空表的结构和数据
/*
truncate和delete的区别:
1)delete是将表中的数据一条一条删除数据,不影响表结构;（接着继续）
2)truncate是将整张表删除，然后重新创建一个新的表,
新的表结构和原来表结构一致; （从头开始）
*/

3、SQL之DQL语句

01、无条件查询

-- 查询指定列数据
SELECT 字段名1, 字段名2, ... FROM 表名;
-- 如果要查询表所有的列也可使用*匹配
SELECT * FROM 表名 ;

-- 例如：
SELECT id,NAME,age,gender,address FROM student;
-- *表示匹配表中所有字段
SELECT * FROM student;

02、别名查询(AS别名查询)

-- 1. 查询时给列或者表取别名,可使用AS关键字
SELECT 字段名1 AS 别名, 字段名2 别名... FROM 表名;
-- 给查询结果取别名意义:将查询结果区别名后作为一张表与其他表关联查询;
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;

-- 说明:AS关键字是给字段和查询结果取别名,同时as可以省略不写;
-- 例如：
SELECT NAME AS '姓名',age 年龄 FROM student;

03、去重查询 Distinct （字段的去重和运算）

-- 1. Distinct去重查询
-- 查询指定列并且结果不出现重复数据,格式:
-- 说明:distinct关键字后的所有字段值都相同,才去重
SELECT DISTINCT 字段名,字段名,.. FROM 表名;
-- 例如：
SELECT DISTINCT address FROM student;

-- 2. 查询结果参与运算
-- 添加数学\英语两个字段后,添加相关的测试数据
ALTER TABLE student ADD math INT;
ALTER TABLE student ADD english INT;

-- 需求1:查询姓名、年龄，将每个人的数学增加10分
SELECT NAME,age,math+10 AS math FROM student;
-- 查询所有列与math + english的和并使用别名”总成绩”
SELECT *,math+english AS 总成绩 FROM student;

04、 蠕虫复制、条件、运算符、in 、between and关键字查询

-- 1. 蠕虫复制：将其他表中的数据进行快速复制，并插入到对应的表的操作
/*
说明:insert into 插入表名1的数据来自select * from 表名2的查询数据
 前提:查询的数据要与插入的表中的字段类型和顺序要一致;
*/
INSERT INTO 表名1 SELECT * FROM 表名2;

-- 例如：
CREATE TABLE student2 LIKE student;
INSERT INTO student2 SELECT * FROM student;

-- 2. 条件查询
-- Demo：
-- sql准备
CREATE TABLE student3 (
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math DOUBLE(4.1),
english DOUBLE(4.1)
);
-- 插入测试数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

-- 条件查询格式:
SELECT 字段1,字段2,...... FROM 表 WHERE 条件;
SELECT NAME FROM student WHERE NAME='马云';

-- 3. 运算符
-- 3.1 比较运算法
/*
> 大于
< 小于
<= 小于等于
>= 大于等于
= 等于
<>、!= 不等于
-- 说明:比较运算符用于条件比较
*/

-- 3.2 逻辑运算符
AND(&&) 多个条件同时满足 一假即假 开发中用的最多个
OR(||)  多个条件其中一个满足 一真即真
NOT(!)  不满足 取反
举例:
-- 查询名字等于张三且性别为男的学生:
SELECT * FROM student WHERE NAME='马化腾' AND sex='男';

-- 3.3 in ,between and 关键字
-- in语法格式
SELECT * FROM 表名 WHERE 字段 IN (值1, 值2, 值3);
例如：
SELECT * FROM student3 WHERE id IN(1,3,5,7);
SELECT * FROM student3 WHERE id NOT IN(1,3,5,7);

-- between and语法格式
BETWEEN 值1 AND 值2; -- 表示从值1到值2范围(同时值1要小于值2)，包头又包尾(包含边界值)
例如：
-- 比如查询年龄在80到100的用户:
SELECT * FROM USER WHERE age BETWEEN 80 AND 100;
-- 相当于：
SELECT * FROM USER WHERE age>=80 AND age<=100;

05、 模糊查询like

五、 模糊查询like
-- 生活中模糊搜索的例子非常多,比如网上购物时,根据输入商品名称模糊匹配;
-- 语法格式
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
/*
-- 说明:like是模糊查询关键字
MySQL通配符有两个：
         %: 表示零到多个字符(任意多个字符)；
          _: 表示一个字符

例如： 
name like '张%' 所有姓张学员，如：张, 张三,张三丰,张三丰收
name like '%张%' 只要有张就可以，如：老张,张,张三
name like '张_' 所有姓张名字为两个字学员,如：张三
name like '张__' 必须三个字,且姓张
name like '_张_' 只有中间是张，前面一个字，后面一个字
*/

-- 需求1:查询姓马的学生
SELECT * FROM student3 WHERE NAME LIKE '马%';
-- 需求2:查询姓名中包含'德'字的学生
SELECT * FROM student3 WHERE NAME LIKE '%德%';
-- 需求3:查询姓马，且姓名有三个字的学生
SELECT * FROM student3 WHERE NAME LIKE '马__';

06、排序查询ORDER BY

SELECT 字段1,字段2,... FROM 表名 WHERE 条件 ORDER BY 字段 [ASC|DESC]; ascend descend
-- ASC: 升序排序(默认,可省略不写)
-- DESC: 降序排序

两种排序方式：
1）单列排序
说明:单列排序就是使用一个字段排序;
-- 格式
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC];
2）组合排序
说明:组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依
次类推。
-- 格式
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

-- 需求1:查询所有数据,使用年龄降序排序
SELECT * FROM student3 ORDER BY age DESC;
-- 需求:查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC,math DESC;

07、聚合函数

/*
count:在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。
按照列去统计有多少行数据;
sum:计算指定列的数值和，如果不是数值类型，那么计算结果为0;
max:计算指定列的最大值;
min:计算指定列的最小值;
avg:计算指定列的平均值;
*/

-- 语法格式:
SELECT 聚合函数(字段) FROM 表名;

-- 需求1:查询学生总数
SELECT COUNT(id) FROM student3;-- 8
SELECT COUNT(english) FROM student3;-- 7
# * 表示所有字段
SELECT COUNT(*) FROM student3;-- 8
-- 需求2:查询年龄大于40的总数
SELECT COUNT(*) FROM student3 WHERE age > 40;
-- 需求3:查询数学成绩总分
SELECT SUM(math) AS 数学总分 FROM student3;

-- 需求4:统计数学与英语的总和值
SELECT SUM(math+english) FROM student3;-- 1120
SELECT SUM(math)+SUM(english) FROM student3;-- 1206
/*
上述查询中差了86,原因:
任何数值与null进行数学运算那么结果都为null
解决方式:
 ifnull(v1,v2)函数作用:如果v1不为null,返回v1,如果v1为null,返回v2
*/
SELECT SUM(IFNULL(math,0)+IFNULL(english,0)) FROM student3;

-- 需求5:查询数学成绩最高分和最低分
SELECT MAX(math),MIN(math) FROM student3;

-- 需求6:查询数学成绩平均分
SELECT AVG(math) FROM student3;-- 79.5000
-- 四舍五入，保留2位小数
SELECT ROUND(3.1415926,3);-- 3.142
SELECT ROUND(AVG(math),2) FROM student3;-- 79.50
-- 不做四舍五入,单纯截取数字
SELECT TRUNCATE(AVG(math),2) FROM student3;-- 79.50

08、分组 GROUP BY

定义：分组就是按照某一列或者某几列。把相同的数据，进行合并输出。

– 注意事项：
– 1.group by后边的字段值相同才能划分为一组；
– 2**.分组后边可以关联多个字段；**

-- 定义：分组就是按照某一列或者某几列。把相同的数据，进行合并输出。
SELECT column_name, 聚合函数(column_name) FROM 表 WHERE 条件 GROUP BY column_name;
-- 注意事项：
-- 1.group by后边的字段值相同才能划分为一组；
-- 2.分组后边可以关联多个字段；

-- 1. Demo:
-- 车辆表
CREATE TABLE car(
	id INT,
	color CHAR(2),
	price FLOAT
);

-- 测试数据
INSERT INTO car(id,color,price) VALUES(1,'黄色',16);
INSERT INTO car(id,color,price) VALUES(2,'黄色',16);
INSERT INTO car(id,color,price) VALUES(3,'蓝色',5);
INSERT INTO car(id,color,price) VALUES(4,'红色',60);
INSERT INTO car(id,color,price) VALUES(5,'白色',8);
INSERT INTO car(id,color,price) VALUES(6,'红色',60);

-- 需求1：查询停车场的每种颜色车辆的总价
SELECT color,SUM(price) FROM car GROUP BY color;
SELECT color AS '颜色',SUM(price) AS "总价" FROM car GROUP BY color;

-- 2. 【分组之后数据过滤使用】having
-- 语法格式
SELECT 字段,.....from 表名 GROUP BY 字段 HAVING 条件;

-- 需求：查询停车场每种颜色车辆总价大于30W的车辆颜色，并显示总价
SELECT color,SUM(price) FROM car GROUP BY color HAVING SUM(price)>30;

09、limit分页查询

1.limit查询语法格式
-- 索引位默认从0开始,如果是0可以省略不写
SELECT 字段1,字段2,... FROM 表名 WHERE 条件 LIMIT 索引位,返回的记录数;

2.使用(WHERE)条件,(group by)分组,(order by)排序,(LIMIT)分页查询的执行顺序?
SELECT 字段1,字段2,... FROM 表名 (WHERE)条件,(group by)分组,(order by)排序,(LIMIT)分页

FROM  表
WHERE 条件
GROUP BY 字段1,字段2 
HAVING 条件 
SELECT 字段
ORDER BY 字段 
LIMIT OFFSET,ROW_COUNT;

SELECT * FROM 表名 WHERE 条件 LIMIT OFFSET ,ROW_COUNT;
/*
 说明:offset表示索引偏移量,索引位从0开始;row_count:返回的条数,
 从索引偏移量开始(包含 偏移量对应的行记录) 
 limit 0,3; -- id=1,2,3 
 limit 4,2; -- id=5,6
 */
 
 -- 创建表
CREATE TABLE student3 (
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math DOUBLE(4,1),
english DOUBLE(4,1)
)

-- 插入测试数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);


-- 需求1:查询student表信息,按照每页3条记录查询,查询第2页的数据
SELECT * FROM student3 LIMIT 3, 3;
-- 需求2:查询数学成绩的前三名
-- 思路:先根据math进行降序排序,然后limit返回前3条
SELECT * FROM student3 ORDER BY math DESC LIMIT 0,3;
-- 其实索引位为0,可以不写索引值
SELECT * FROM student3 ORDER BY math DESC LIMIT 3;

小结

`1. 语法
SELECT 字段1,字段2,... FROM 表名 (WHERE)条件,(group by)分组,(order by)排序,(LIMIT)分页
# 执行的先后顺序
FROM  表
WHERE 条件
GROUP BY 字段1,字段2 
HAVING 条件  
SELECT 字段
ORDER BY 字段 
LIMIT OFFSET,ROW_COUNT

`2. 基础查询
	1. 多个字段的查询：select 字段名1，字段名2... from 表名；
		注意：如果查询所有字段，则可以使用*来替代字段列表
			 
	2. 去除重复：distinct
		
	3. 计算列：一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
		     ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
				* 表达式1：哪个字段需要判断是否为null
				* 表达式2：如果表达式1字段为null，结果返回表达式2。
				
	4. 起别名： as：as也可以省略
		
				
`3. 条件查询
	1. where子句后跟条件
	
	2. 运算符
		* > 、< 、<= 、>= 、= 、<>
		* BETWEEN...AND  
		* IN( 集合) 查询在某个范围 
		* LIKE：模糊查询
			* 占位符：
				* _:单个任意字符
				* %：多个任意字符
		* IS NULL  
		* and  或 &&
		* or  或 || 
		* not  或 !

4、 SQL之DCL语句

01、DCL用户创建,授权,撤销授权

-- 1.创建普通用户指令
-- 主机名称为当前用户的ip或者主机名,也可使用%匹配所有地址
CREATE USER '用户名'@'主机名称' IDENTIFIED BY '密码';
-- 2.给用户授权 如果匹配所有库下的所有表,使用 *.*,如果是day01库下的所有表:day01.*
GRANT 权限1,权限2,.... ON 数据库.表 TO '用户名'@'主机名';
-- 3.撤销用户权限
REVOKE 权限1,权限2,.... ON 数据库.表名 FROM '用户名'@'主机名称';

需求1：创建u1用户，只能在localhost这个服务器登录mysql服务器，密码为1234
CREATE USER 'user01'@'localhost' IDENTIFIED BY '1234';

需求2：创建u2用户可以在任何电脑上登录mysql服务器，密码为1234
CREATE USER 'user02'@'%' IDENTIFIED BY '1234';

需求3:给用户分配对数据库操作的权限：创建表，修改表，插入记录，更新记录，查询.
GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON *.* TO  'user01'@'localhost';
GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON *.* TO  'user02'@'%';

需求4:撤销用户对数据库所有表的操作的权限;
REVOKE CREATE,ALTER,INSERT,UPDATE,SELECT ON *.* FROM 'user01'@'localhost';
REVOKE ALL ON *.* FROM 'user02'@'localhost';

02、DCL删除用户，修改用户密码

-- 1.删除普通用户命令
DROP USER '用户名'@'主机名称';
-- 2.修改超级管理员密码
mysqladmin -uroot -p旧密码 PASSWORD 新密码
-- 3.修改普通用户密码
SET PASSWORD FOR '用户名'@'主机名' =PASSWORD('新密码');

需求1:删除user02
-- 查询数据库下所有的管理员用户
SELECT * FROM mysql.user;
-- 删除用户
DROP USER 'user02'@'%';

需求2:修改管理员密码为123456
-- 修改超级管理员密码
mysqladmin -uroot -p1234 PASSWORD 123456

需求3:将'user01'@'localhost'的密码改成'666'
SET PASSWORD FOR ''user01''@''localhost'' =PASSWORD(''666'');

* DBA：数据库管理员

* DCL：管理用户，授权
	1. 管理用户
		1. 添加用户：
			* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
		2. 删除用户：
			* 语法：DROP USER '用户名'@'主机名';
		3. 修改用户密码：
			UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
			UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
			
			SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

			* mysql中忘记了root用户的密码？
				1. cmd -- > net stop mysql 停止mysql服务
					* 需要管理员运行该cmd

				2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
				3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
				4. use mysql;
				5. update user set password = password('你的新密码') where user = 'root';
				6. 关闭两个窗口
				7. 打开任务管理器，手动结束mysqld.exe 的进程
				8. 启动mysql服务
				9. 使用新密码登录。
		4. 查询用户：
			-- 1. 切换到mysql数据库
			USE myql;
			-- 2. 查询user表
			SELECT * FROM USER;
			
			* 通配符： % 表示可以在任意主机使用用户登录数据库

	2. 权限管理：
		1. 查询权限：
			-- 查询权限
			SHOW GRANTS FOR '用户名'@'主机名';
			SHOW GRANTS FOR 'lisi'@'%';

		2. 授予权限：
			-- 授予权限
			grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
			-- 给张三用户授予所有权限，在任意数据库任意表上
			
			GRANT ALL ON *.* TO 'zhangsan'@'localhost';
		3. 撤销权限：
			-- 撤销权限：
			revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
			REVOKE UPDATE ON db3.account FROM 'lisi'@'%';

小结

# 注意
-- 1、SELECT查询时，语法的执行的先后顺序：
FROM  + 表名    		从表名查字段数据
WHERE + 条件        		进行条件判断
GROUP BY  + 以某字段分组	进行分组
HAVING    			进行过滤
SELECT + 字段  			查询出得到表
ORDER BY			进行以某字段为标准进行排序
LIMIT OFFSET,ROW_COUNT;

-- 2、当select查询和【group by】一起使用时
select 关键字后面只能跟：聚合函数 或者 group by关键字后面的字段


# 总结
1.DML:主要是对表中数据的增删改的操作
-- 1)增:insert into
INSERT INTO 表名(字段1,字段,...,字段n) VALUES(v1,v2,....,vn);
-- 如果是全字段插入,那么字段名称可以不写
INSERT INTO 表名 VALUES(v1,v2,....,vn);
-- 批量插入
INSERT INTO t VALUES(v1,v2,....,vn),(v1,v2,....,vn),(v1,v2,....,vn),...;
-- 2)删:delete from
DELETE FROM 表名;-- 无条件删除导致整个表数据都被删除
DELETE FROM 表名 WHERE 条件;-- 仅删除满足条件的数据
-- 3)改:update set
UPDATE 表名 SET 字段1=v1,字段2=v2,...;-- 无条件更新,全表更新
UPDATE 表名 SET 字段1=v1,字段2=v2,...where 条件;-- 仅更新满足条件的数据

2.DQL:主要是对表中数据的查询操作
-- 1)基本语法
SELECT 字段1,字段2 FROM t;-- 无条件查询等价于全表查询
SELECT 字段1,字段2 FROM t WHERE 条件;

-- 2) 2种运算符
-- 2.1 比较运算符
< <= > >= = != <>
-- 2.2 逻辑运算符(主要用户关联多个条件)
AND(&&):条件都满足才显示;
OR(||):满足其一就显示
NOT(!):条件取反

-- 3)模糊搜索 like
SELECT * FROM t WHERE 字段 LIKE '通配符字符串';
-- 两种通配符:
%:表示匹配任意多个
_:表示匹配一个字符;

-- 4)排序 order by 字段1 asc ,字段2 desc;
SELECT * FROM t ORDER BY age ASC,math DESC;
排序规则:
1.先按照第一个字段进行排序,如果出现相同的数据,那么按照第二个字段进行排序,如果字段2也出相同的数据,以
次类推;
2.如果第一个字段都不相同,后边的字段就没有机会参与排序运算;

-- 5)大聚合函数
SELECT 聚合函数(字段) FROM t WHERE 条件;
-- 5.1 求平均值
SELECT AVG(字段) FROM t;
-- 5.2 最大值
SELECT MAX(字段) FROM t;
-- 5.3 最小值
SELECT MIN(字段) FROM t;
-- 5.4 求和
SELECT SUM(字段) FROM t;
-- 5.5 统计记录数
SELECT COUNT(*) FROM t;
-- 6)分组 group by
SELECT 分组字段,聚合函数(字段) FROM t GROUP BY 字段1,字段2 HAVING 条件;

-------------------------------------------------------------------------
查询所有员工,如果薪资没有的员工,salary列值显示:没有薪资,倒序显示(ifnull函数实现).
-- IFNULL( expr1 , expr2 )
-- 在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2
# 默认id升序
SELECT NAME,IFNULL(salary,'无薪资') 薪资 FROM users ORDER BY salary DESC;

三、数据库备份和还原

-- 备份,导出的文件后缀名必须为.sql
mysqldump -u用户名 -p密码 数据库名称 > 备份文件(sql后缀)
-- 还原,数据导入格式
mysql -uroot -p密码 数据库名称 < 数据存放的文件路径

需求1:将day01_2数据库导出到硬盘文件e:\day02.sql 中
-- 使用mysqldump手动备份时,创建库的DDL语句没有备份,还原时需要手动创建库;
mysqldump -uroot -p1234 day01_2 > e:\day02.sql
需求2:重新开启一个新的dos窗口,将day02备份的数据表和表数据 恢复到test_db1库中
mysql -uroot -p1234 test_db1 < e:\day02.sql

四、数据库约束

数据库约束的作用：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

01、主键约束

# 1).主键约束作用
主键能够唯一的标识表中的每一行数据,使用主键约束后,可使数据满足唯一且非空,方便数据管理;
总之，主键特点: 
     1.唯一标识表中每一条记录; 
     2.非空
     3.非业务相关;
     
# 2).创建表时创建主键
字段名 类型 primary key

#3).在已有表中添加主键(DDL 了解)
alter table 表名 add primary key(字段);

-- 创建学生表st5, 包含字段(id, name, age)将id做为主键,并测试唯一和非空
create table st5(
id int primary key,-- 给id添加主键月约束
name varchar(30),
age int
);

# 4).主键自增
-- `据库自己维护主键`,且实现主键的递增;
-- 语法格式
字段名 字段类型 primary key `auto_increment`
练习:创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长并测试
create table st6(
id int primary key auto_increment,
name varchar(20),
age int
);
-- 此时因为主键自增,可以输入null
insert into st6 values(null,'wangwu',19);
-- 此时主键值为10
insert into st6 values(10,'wangwu',19);
-- 下一条数据自增从11开始
insert into st6 values(null,'wangwu',19);

02、唯一约束

1.唯一约束介绍及语法格式
字段值唯一,不可重复
-- 两种语法格式：
-- 创建表时指定
create table 表名(
字段名 字段类型 unique,
 ......
);
-- DDL修改字段约束属性
alter table 表名 add unique(字段);
练习:创建学生表st7, 包含字段(id, name),name这一列设置唯一约束;
create table st7(
id int primary key auto_increment,
name varchar(20) unique 
);
-- 注意：唯一约束字段下允许存在多个null值
insert into st7(name) values(null);
insert into st7(name) values(null)

03、非空约束

-- 约束某个字段不能为空;
-- 修改字段非空约束,不常用
alter table 表名 alter 字段 set not null;

-- 创建表时指定
字段名 类型 not null
练习:创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL,并测试
create table st8(
id int,
name varchar(20) not null,
gender char(2) 
);

-- 测试非空
insert into st8 values(1,'张三','男');
如果这样为null
insert into st8 values(1,null,'男');
/*
Error Code : 1048
Column 'name' cannot be null
*/

04、默认值

-- 给字段添加默认值,如果字段没有被赋值,则使用默认值;
2种语法格式:
-- 1）创建表是指定默认值
字段名 字段类型 default 默认值
-- 2）修改表字段默认值
alter table 表名 alter 字段名 set default 默认值;

需求:创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州;
-- 默认值约束,如果是字符串类型,需要加单引号
create table st9(
id int,
name varchar(20),
address varchar(100) default '广州'
);

-- 测试默认值
insert into st9(id,name) values(1,'zhagnsan');
-- 如果自己赋值,就会覆盖默认值
insert into st9 values(1,'zhagnsan','上海');
-- 如果默认值手动插入为null,也会覆盖默认值;
insert into st9 values(1,'zhagnsan',null);

小结

05、外键约束

表与表之间是存在的关联

表关系 设计方式

多对多 通过设计一张中间表，简化多对多的业务模型，转化成主表与中间表之间一对多的关系；

一对多 一般由多的一方维护两者之间的关系；

一对一 任意一方取维护；

# 在关系型数据库中,表与表之间是可以存在关联的,常见的表结构关系如下:
 多对多: 程序员与项目、学生与选修课程等;
 一对多:班级与学生、部门与员工等等; 
 一对一:用户与身份证、玩家信息与微信信息等;

# 多表之间的关系
	# 1. 分类：
		1. 一对一(了解)：
			* 如：人和身份证
			* 分析：一个人只有一个身份证，一个身份证只能对应一个人
		2. 一对多(多对一)：
			* 如：部门和员工
			* 分析：一个部门有多个员工，一个员工只能对应一个部门
		3. 多对多：
			* 如：学生和课程
			* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
	# 2. 实现关系：
		1. 一对多(多对一)：
			* 如：部门和员工
			* 实现方式：`在多的一方建立外键，指向一的一方的主键`。
		2. 多对多：
			* 如：学生和课程
			* 实现方式：多对多关系实现需要借助`第三张中间表`。中间表`至少包含两个字段`，
			  这`两个字段作为第三张表的外键`，`分别指向两张表的主键`
		3. 一对一(了解)：
			* 如：人和身份证
			* 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

外键约束语法

-- 添加外键约束语法格式: 
-- 方式1:修改字段为外键
alter table 表名 add constraint 外键名称 foreign key(当前表中的字段) references 主表(主表主键);
/*
说明:
 constraint 外键名称 :表示约束,同时给约束取名称
 foreign key(当前表中的字段):表示给当前表字段添加外键
 references 主表(主表主键):表示关联主表中的主键
*/

-- 方式2:创建表时创建外键
create table coder_project(
coder_id int,
project_id int,
constraint c_id_fk foreign key(coder_id) references coder(id),
constraint p_id_fk foreign key(project_id) references porject(id)
);


1.练习:为中间表添加外键
-- 程序员表
create table coder(
id int primary key auto_increment,
name varchar(20) unique,
salary double not null
);
-- 项目表
create table project(
id int primary key auto_increment,
name varchar(10)
);
-- coder_project中间表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

添加外键约束
-- 1.为coder_id添加外键约束
alter table coder_project add constraint fk_coder foreign key(coder_id) references coder(id);
-- 2.再给project_id添加外键约束
alter table coder_project add constraint fk_project foreign key(project_id) references project(id);

外键约束测试：
-- ErrorInfo:Cannot add or update a child row: a foreign key constraint fails ......
insert into coder_project values(10,10);

-- ErrorInfo:删除主表中关联的数据
-- Cannot delete or update a parent row: a foreign key constraint fails ......
delete from coder where id=1;

-- ErrorInfo:更新中间表数据
-- Cannot delete or update a parent row: a foreign key constraint fails ......
update coder set id=20 where id=1;

如何在主表中删除或者更新一条被外键引用的数据？
先删除中间表关联的数据，然后再更新或者删除主表数据。是不是感觉特别麻烦?

外键的级联

级联操作就是在修改或者删除主键时可以同时对从表的外键进行修改删除。

 on udpate cascade  表示主表进行更新操作后,从表对应的数据也要进行更新操作;

 on delete cascade  表示主表进行主键删除后,从表外键对应的数据也要删除;

练习:为coder_project中间表添加级联操作
-- 添加外键的级联约束
-- 1.为coder_id添加外键约束
alter table coder_project add constraint fk_coder foreign key(coder_id) 
references coder(id) on update cascade on delete cascade;
-- 2.为project_id添加外键约束
alter table coder_project add constraint fk_project foreign key(project_id) 
references project(id) on update cascade on delete cascade;

五、数据库设计的范式

01、六种范式

• 概念：

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

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

• 分类：

1. 第一范式（1NF）：每一列都是不可分割的原子数据项
2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

			* 几个概念：
				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. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
					例如：该表中码为：（学号，课程名称）
					* 主属性：码属性组中的所有属性
					* 非主属性：除过码属性组的属性
					
		

02、反三范式

六、多表查询

同时查询多张表,获取需要的数据;

比如:我们向查询水果对应的价格,需要将水果表和价格表同时进行查询;

笛卡尔积现象查询：

同时查询多张表,获取需要的数据;

比如:我们向查询水果对应的价格,需要将水果表和价格表同时进行查询

结果 null 值的也会查询出来

01、内连接

什么是内连接：内连接查询又称为交集查询,也就是查询只显示满足条件的数据；

1、 显式内连接
显示内连接：使用INNER JOIN ... ON语句, 可以省略INNER关键字；
-- 语法格式：
select * from 表名1 inner join 表名2 on 条件;
-- 或者
select * from 表名1 join 表名2 on 条件

2、 隐式内连接
看不到join关键字,条件使用where指定；
select 列名 , 列名, .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;

02、外连接

一、左外连接
 左表的记录全部表示出来;
 右表只会显示符合搜索条件的记录;

二、右外连接
 右表的记录全部表示出来;
 左表只会显示符合搜索条件的记录;

一、左外连接
语法格式：
select * from 表1 left [outer] join 表2 on 条件;
-- 说明：
-- 1.left关键字左边的表定义为左表，left关键字右边的表定义为右表,查询的内容以左表为主，
如果左表有数据，而右表没有对应的数据，仍然会把左表数据进行显示。
-- 2.outer关键字可省略
练习:不管能否查到水果对应的价格，都要把水果显示出来；
-- 左外连接查询
select * from fruit left outer join price on fruit.price_id=price.id;

二、右外连接
语法格式：
select * from 表1 right [outer] join 表2 on 条件;
-- 说明：
-- 1.right关键字左边的表定义为左表，right关键字右边的表定义为右表,查询的内容以右表为主，
如果右表有数据，而左表没有对应的数据，仍然会把右表数据进行显示。
-- 2.outer关键字可省略
练习:不管能否查到价格对应的水果，都要把价格显示出来
select * from fruit right outer join price on fruit.price_id=price.id;

03、子查询

子查询就是一个SQL查询的结果作为另一个SQL查询语句语法的一部分

单行单列
使用比较运算符: > 、>= 、<、<=、 =等

多行单列
使用in关键字

多行多列
将查询结果使用as取别名作为一张表，与其他表进行关联查询

练习：
-- 创建部门表 1
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
-- 创建员工表 n
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
foreign key(dept_id) references dept(id)
);

INSERT INTO dept (NAME) VALUES 
('开发部'),
('市场部'),
('财务部');
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);

子查询结果是单行单列

需求1:查询工资最高的员工是谁？
-- 分析
-- 1.查询最高工资是多少?
select max(salary) from emp; -- 9000
-- 2.查询等于最高工资的员工信息
select * from emp where salary=9000;-- id=3
-- 合并,一步查询
select * from emp where salary=(select max(salary) from emp);
需求2:查询工资小于平均工资的员工有哪些？
-- 分析
-- 1.获取平均工资
select avg(salary) from emp;-- 5860
-- 2.查询工资小于平均工资的员工信息
select * from emp where salary < 5860;-- 2,4,5
-- 合并
select * from emp where salary < (select avg(salary) from emp);

子查询结果是多行单列

子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符；

需求1:查询工资大于5000的员工，来自于哪些部门的名字
-- 分析
-- 结果:查询部门name
-- 条件:员工薪资大于5000
-- 关联的表: emp,dept 关联条件: emp.dept_id=dept.id
-- 实现:
-- 子查询
-- 1.查询薪资大于5000的员工对应的部门id集合
select dept_id from emp where salary > 5000;-- dept_id:(1,2)
-- 2.查询部门表中id等于(1,2)的部门名称,使用in
select name from dept where id in(1,2);
-- 合并
select name from dept where id in(select dept_id from emp where salary > 5000);
需求2:查询开发部与财务部所有的员工信息
-- 子查询 多个单表查询的组合
-- 1.查询开发部与财务部对应的id集合
select id from dept where name in('开发部','财务部');-- id:(1,3)
-- 2.在员工表中查询部门id等于1或者3的员工信息
select * from emp where dept_id in (1,3);
-- 合并
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));

子查询的结果是多行多列

子查询如果是多行多列,一般使用AS取别名作为表进行关联查询；

需求1:查询出2011年以后入职的员工信息，包括部门名称
-- 子查询
-- 1.查询出2011年以后入职的员工信息
select * from emp where join_date >'2011-01-01';
-- 2.然后将上述查询结果使用as取别名,作为一张表与dept表关联查询
select
tmp.*,
 dept.name
from (select * from emp where join_date > '2011-01-01') as tmp,dept
where tmp.dept_id = dept.id;
-- 关联查询也可以实现查询
select
emp.*,dept.namefrom emp,dept
where emp.dept_id = dept.id and join_date > '2011-01-01';

小结

# 1.limit语句
作用:限制返回的记录行数
语法格式:
select * from t limit offset,row_cout;
-- offset:表示索引位变异量(从0开始),row_count表示查询返回的行数

# 2.约束
约束名称 	关键字 		  作用
主键约束 	primary key 	唯一+非空
唯一约束 	unique 			唯一(允许多个null存在)
非空     	 not null 		 非空
默认值约束  default 值 	   如果没有指定字段对应的值,那么就使用默认值
外键约束 	foreign key 	维护表与表之间的关系

# 3.数据库设计3范式
1)第一范式:表中字段保证原子性,不可分割;
2)第二范式:1.满足第一范式 2.消除部分依赖;
3)第三范式: 1.满足第二范式 2.消除传递依赖;

# 4.数据库备份与还原（了解即可）可视化操作要会;

# 5.DCL:了解即可(看到命令能理解) 
-- 创建用户
create user '用户名'@'%' identified by '密码';
-- 授权
grant 权限1,权限2,select,drop ,delete on 库.表 to '用户名'@'%';
-- 撤销权限
revoke 权限1,权限2,select,drop ,delete on 库.表 from '用户名'@'%';
-- 修改超级管理员密码
mysqladmin -uroot -p旧密码 password 新密码;
-- 修改普通管理员名称
set password for '用户名'@'%'=password(新密码);

# 6.多表查询
2.1 表关联查询
`内连接：
	特点：交集查询,也就是多表都满足条件的部分,才显示;
	分类-
	显示内连接：--> select * from t1 inner join t2 on 条件;
	隐式内连接：--> select * from t1,t2 where 条件;

`外连接：
	特点：以左关联为例,左表的数据都显示,右表只显示满足条件的数据;
 	分类
 	左外连接查询：--->select * from t1 left [outer] join t2 on 条件;
	右外连接查询：--->select * from t1 right[outer] join t2 on 条件;

2.2 子查询
	特点:前一个sql查询的结果作为后一个sql语句的组成部分;
	子查询3种情况:
		1.单行单列,`使用比较运算符`
		2.多行单列,`使用in关键字`
		3.多行多列，`查询结果使用as关键字区别名`,然后作为一张表与其他表进行关联查询;
	 注意事项:如果子查询中使用了函数,且在外部使用别名获取这个字段值,那么需要给函数查询取别名;
	 
	`注意事项:如果子查询中使用了函数 字段 取别名,那么在最外部取这个字段值,就要使用别名获取！！！

单行单列
使用比较运算符:   > 、>= 、<、<=、 =等

多行单列
使用in关键字

多行多列
将查询结果使用as取别名作为一张表，与其他表进行关联查询
	 
多对多  		通过设计一张中间表，**简化多对多的业务模型，转化成`主表与中间表之间一对多`的关系；

一对多     	一般由`多的一方维护`两者之间的关系；

一对一         任意一方取维护；