【Mysql】耗时7200秒整理的mysql笔记！常用API汇总！包教包会！

[mysqld]
# mysql 端口
port=3306
# mysql 目录
# 更改为自己安装的目录
basedir=D:\MySql\mysql
# mysql data 目录
# 更改为自己安装的目录
datadir=D:\MySql\mysql\data
# 最大连接数
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8mb4
[client]
port=3306
# 默认字符集
default-character-set=utf8mb4

	在bin目录下，输入：<font color=red>mysqld --initialize --console </font>命令进行mysql初始化，并得到初始密码（需要记录初始密码，后续会用到）

	输入<font color=red>mysqld --install mysql</font>命令进行mysql服务的安装，出现successfully字样，则代表安装成功！

	使用<font color=red>net start mysql</font>命令启动mysql服务，显示服务启动成功则表示成功。（使用net stop mysql可以关闭mysql服务）

# 示例
mysql -u(root) -p(密码)
mysql -h(IP) -u(root) -p(密码)
mysql --host=(IP) --user=(root) --password=(密码)

1. SQL语句可以单行或多行书写，以分号结尾。
2．可使用空格和缩进来增强语句的可读性。
3. MysQL数据库的sQL语句不区分大小写，关键字建议使用大写。
4. 3种注释
	-- 注释内容
	# 注释内容
	/*注释内容*/

-- 1.切换到Mysql数据库
USE mysql;
-- 2.查询user表
SELECT * FROM user; # user表中管理用户信息

# PASSWORD('') Mysql中的函数 对密码进行加密  

# 给与一个用户 所有表 所有数据库的全部权限
GRANT ALL ON *.* TO 'root'@'rootPassword';

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

-- DISTINCT关键字 去除重复结果
SELECT (DISTINCT) <字段列表名1>,<字段列表名2>... FROM <表名>

-- 计算 数字型数据结构 使用 + - * /
SELECT name,math,english,math + english from student1
-- 如果有null参与的计算 默认计算结果均为 null 将null替换为一个值
-- IFNULL(可能为NULL的值,如果为NULL要替换的值)
SELECT name,math,english,math + IFNULL(english,0) from student1

-- 起别名 AS
SELECT name,math,english,math + english AS 总分 from student1 -- AS 可以以空格替代

# Where 后跟句子条件
# 运算符
-- > < <= >= = <>
-- BETWEEN...AND
-- IN(集合)
-- LIKE 模糊查询
	-- 占位符 _：单个任意字符  %：多个任意字符
-- IS NULL
-- and 或 &&
-- or 或 ||
-- not 或 !  

-- <> 为大于小于 与 != 一致

# 示例
SELECT * FROM student WHERE age!=20;
SELECT * FROM student WHERE age=20;

SELECT * FROM student WHERE age>20 and age<30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

SELECT * FROM student WHERE age=20 or age=29 or age=17;
SELECT * FROM student WHERE age IN(20,29,17);

SELECT * FROM student WHERE age IS NULL;
SELECT * FROM student WHERE age IS NOT NULL;

-- 姓 马 的有哪些
SELECT * FROM student WHERE name LIKE '马%';
-- 三个字的名字
SELECT * FROM student WHERE name LIKE '___';
-- 姓名中有马
SELECT * FROM student WHERE name LIKE '%马%';

-- 语法 order by 子句
	# order by 排序字段1 排序方式1 , 排序字段2 排序方式2...
-- 排序方式 有多个排序条件 第一个排序条件一致时 才会判断第二个条件
	# ASC：升序
	# DESC：降序

-- 聚合函数：将一列数据作为整体进行计算
(计算时默认排除 NULL 值)
	# count：统计个数
		# 一般选择非空的列：主键
		# count(*) 统计所有列 有一列不为null 就统计在内
	# max：计算最大值
	# min：计算最小值
	# sum：计算和
	# avg：计算平均值
	
SELECT COUNT(math) FROM student;
SELECT COUNT(IFNULL(math,0)) FROM student;

SELECT MAX(math) FROM student;
SELECT MIN(math) FROM student;
SELECT SUM(math) FROM student;

# 语法： GROUP BY 分组字段
 -- 注意
 	-- 分组之后查询的字段：分组字段，聚合字段 其他字段无意义
 	-- where 和 having 的区别
 		-- where 在分组之前进行限定 (如果不满足条件 不参与分组) 对每一条的判断
 		-- having 在分组之后进行限定 (如果不满足结果 不会被查询) 对一组的判断
 		-- where 不可使用聚合函数 而 having可以
SELECT sex,AVG(math) FROM student GROUP BY sex WHERE math > 70;
SELECT sex,AVG(math) FROM student GROUP BY sex WHERE math > 70;

# 语法： limit 开始的索引，每页显示几条记录
	-- 分页操作是一个 "方言"

SELECT * FROM student LIMIT 0,3; -- 第一页
SELECT * FROM student LIMIT 3,3; -- 第二页
SELECT * FROM student LIMIT 6,3; -- 第三页
...
SELECT * FROM student LIMIT n(x-1),n; -- 第x页

SELECT * FROM table1, table2;
-- 笛卡尔积 A B
	# 有两个集合 A 和 B 取这两个集合的所有组成情况
	# 要完成多表查询 需要消除无用数据

-- 消除无用数据方法
	# 内连接查询
    # 外连接查询 
    # 子查询

-- 内连接
	# 从哪些表中查询数据
	# 查询条件是什么
	# 查询哪些字段
	

-- 隐式内连接 使用 WHERE 消除 无用数据
# 多表筛选
SELECT * FROM emp,dept WHERE emp.'dept_id' = dept.'id';
# 使用 表名.列名 进行条件筛选
SELECT emp.'id',emp.'name',emp.'gender' FROM emp,dept WHERE emp.'dept_id' = dept.'id';

-- 显式内连接 INNER 可以省略
SELECT 字段列表 FROM 表名1 INNER JOIN 表明2 ON 条件
SELECT * FROM emp INNER JOIN dept ON emp.'dept_id' = dept.'id';

-- 外连接查询

-- 左外连接 OUTER 可以省略
SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件
# 查询的是 左表中所有的数据 以及其交集部分


-- 右外连接 OUTER 可以省略
SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件
# 查询的是 右表中所有的数据 以及其交集部分

-- SELECT 中嵌套 SELECT

# 案例：查询工资最高的员工的信息
SELECT MAX(salary) FROM emp; -- 查询到最高工资 9000
SELECT * FROM emp WHERE emp.'salary' = 9000;

# 子查询简化案例语句
SELECT * FROM emp WHERE emp.'salary' = (SELECT MAX(salary) FROM emp);
# 查询工资小于平均工资的员工
SELECT * FROM emp WHERE emp.'salary' < (SELECT AVG(salary) FROM emp);

# 多行单列使用：
SELECT id  FROM dept WHERE NAME = '财务部' or NAME = '市场部'; -- 查询出 id 为 1 , 2 , 3 ... 多行单列
SELECT * FROM emp WHERE dept_id IN (3,2)
-- 合并
SELECT * FROM emp WHERE dept_id IN (SELECT id  FROM dept WHERE NAME = '财务部' or NAME = '市场部';)

# 案例：查询员工入职日期是 2011-11-11 日之后的员工信息和部门信息
SELECT * FROM dept t1, (SELECT * FROM emp WHERE emp.'join_date' > '2011-11-11';) t2
WHERE t1.id = t2.dept_id;

# 在创建表时添加 NOT NULL 添加后字段不得为空
CREATE TABLE <表名> (<字段名> <字段类型>(最大字节长度) NOT NULL,
                   <字段名> <字段类型>(最大字节长度));
                   
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(20);
-- 不加 NOT NULL 直接用新语句覆盖旧语句

-- 创建表后添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL;

# 在创建表时添加 UNIQUE 添加后字段不得重复
	-- 可以存在 NULL 但只能有一条为NULL
CREATE TABLE <表名> (<字段名> <字段类型>(最大字节长度) UNIQUE,
                   <字段名> <字段类型>(最大字节长度));
                   
-- 删除
ALTER TABLE student MODIFY phone VARCHAR(20);

-- 创建表后添加
ALTER TABLE student MODIFY phone VARCHAR(20) UNIQUE;

# 在创建表时添加 PRIMARY KEY
CREATE TABLE <表名> (<字段名> <字段类型>(最大字节长度) PRIMARY KEY,
                   <字段名> <字段类型>(最大字节长度));
                   
-- 删除
ALTER TABLE student MODIFY phone VARCHAR(20);

-- 创建表后添加
ALTER TABLE student MODIFY phone VARCHAR(20) PRIMARY KEY;

# 在创建表时添加 PRIMARY KEY AUTO_INCREMENT
CREATE TABLE <表名> (<字段名> <字段类型>(最大字节长度) PRIMARY KEY AUTO_INCREMENT,<字段名> <字段类型>(最大字节长度));
# 可以手动插入 正常值
# 自增值根据上衣条记录加1

# 在创建表时添加 FOREIGN KEY
# 外键必须为其他表的主键
CREATE TABLE <表名> (...
				  CONSTRAINT <外键名称> FOREIGN KEY (<外键列名称>) REFERENCES <主表名称>(<主表列名称>),
                   <字段名> <字段类型>(最大字节长度)) ON UPDATE CASCADE;
-- 删除外键
ALTER TABLE <表名称> DROP FOREIGN KEY <外键名称>;

-- 添加外键
ALTER TABLE <表名称> ADD CONSTRAINT <外键名称> FOREIGN KEY (<外键列名称>) REFERENCES <主表名称>(<主表列名称>);

-- 级联操作
	# 一个数据需要修改主键 如果其他表联动此表作为外键 修改后会发生错误 需要添加级联 添加后修改主键外键会自动更改
-- 修改时添加
ALTER TABLE <表名称> ADD CONSTRAINT <外键名称> FOREIGN KEY (<外键列名称>) REFERENCES <主表名称>(<主表列名称>) ON UPDATE CASCADE;
-- or 创建时添加



# 语法：
-- 级联更新 ON UPDATE CASCADE
-- 级联删除 ON DELETE CASCADE (很危险 一不小心就删记录删除很多)

# 语法
	-- 备份： mysqldump -u用户名 -p密码 需要备份的数据库名称 > 保存路径
	-- 还原：
		1.登录数据库
		2.创建数据库
		3.使用数据库 -- 使用创建的数据库
		4.执行文件 -- 执行命令： source 文件路径

mysqldump -u root -p test > E:\桌面\Note\data.sql

create database databaseone;
use databaseone;
source E:\data.sql
-- OK！

START TRANSACTION; -- 开启事务

# 事务操作代码
# ...

COMMIT; -- 执行代码没有问题 提交事务

ROLLBACK; -- 出现问题 回滚事务
-- 还原数据库到 开启事务之前的状态

# 修改事务的默认提交方式
	-- 查看事务的默认提交方式 SELECT @@autocommit;
	-- 1 代表自动提交  0 代表手动提交
# 修改： SET @@autocommit = 0;

# Mysql 默认自动提交
# Oracle 默认手动提交

# 数据库 隔离级别 语法
# 查询隔离级别
SELECT @@tx_isolation;
# 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

# 脏读：
	-- READ UNCOMMITTED  事件中未COMMIT 但是数据库已经更改
# 不可重复读：两次SELECT数据不一致

-- 1.cmd --> net stop mysql 停止mysql服务

-- 2.使用无验证方式启动Mysql服务
# mysqld --skip-grant-tables

-- 3.修改ROOT用户密码
USE mysql;
UPDATE user SET password = PASSWORD('新密码') WHERE user='root';