[大数据]MySQL知识总结（一）

MySQL 服务

启动/关闭mysql服务

net start mysql
net stop mysql

以管理员方式进入mysql服务
密码一般为 root 或者 1234 ； 管理员用户名一般默认为 root

mysql -uroot -p1234

这种方式默认连接本地的mysql服务，如果想连接其他主机的 mysql服务，可以

mysql -h(IP地址) -u(用户名) -p(密码)

eg
mysql -h127.0.0.1 -uroot -p1234
其实是以下内容的简写
mysql --host=127.0.0.1 --user=root --password=1234

退出mysql

exit

MySQL 的安装目录

• bin 目录
  里面放的是一些二进制（binary）的可执行文件，对应了很多的exe命令

• data目录放的数据

• include 目录
  由于mysql 是 C语言写的，这里放的是一些头文件

• lib 目录
  里边放的一些依赖文件

• share
  方式错误信息

• my,ini 文件
  里面记录了MySQL的配置文件

MySQL的数据格式

• MySQL服务器
  指的是安装了MySQL服务的设备
• MySQL 服务
  MySQL是一种数据库管理系统，提供的是管理关系型数据库的服务
• MySQL 数据库
  对应着上述 data文件的文件夹
• 表
  打开上面的data文件，表以frm文件的形式进行存储
• 数据
  表中记录着数据库

SQL

• SQL （Structure Query Language）操作所有关系型数据库的规则，语言。 所有的关系型数据库都要支持这个语言。虽然每一种数据库 可能有略微差距，但是大体上都差不多。

• SQL 语言分为4类

  • DDL 操作数据库和表
  • DML 增删改 表中的数据
  • DQL 查询表中的数据
  • DCL 授权语句

操作数据库、表 – DDL （data definition language）

用于操作数据库，表

操作数据库（database）：CRUD

C（create）创建库

create database （if not exists） [数据库名称] (character set [设定的字符集] ) ; -- 创建数据库

# eg
create database if not exists test character set gbk;

R（Retrieve）查询库

show databases; -- 展示所有数据库 

第一次使用show databases;时（未创建任何表时）可以看到四个数据库

Q: 但是，你在data文件夹里面只能看到三个文件夹（数据库），这是怎么回事？
A:
- information_schema 不是真正的数据表，而是“视图”，没有指定的物理文件
- mysql数据库，对应mysql的一些核心文件
- performance_schema 数据库 对应的一些性能提升相关
- sys文件 ，系统相关

show create database [数据库名称] ; # 查询某个数据库的字符集；查询某个数据库的创建语句 
# eg
show create database mysql;

U（Update）修改库

alter database [数据库名] character set [字符集];
#eg
alter database [数据库名] character set utf8;

D （delete） 删除库

drop database （if exists） [数据库名]

使用数据库、进入数据库

select database(); -- 查询正在使用的数据库的名称，没有的话返回 null

use [数据库名称]； -- 使用（进入数据库）

操作表（table）： CRUD

C 创建表

表有表名
表头(列名)

create table [表名](
		[列名1] [数据类型1],
		[列名2] [数据类型2],
		……
		[列名n] [数据类型n]
	);
/* 常用数据类型：
	int 
	double(5,2) -- 表示此小数最多 一共 5 位，最多保留2位；
	date -- 只包含年月日 yyyy--MM--dd
	datetime -- yyyy--MM--dd HH:mm:ss
	timestamp -- 同上,不赋值时 默认 添加的 系统时间
	varchar(20): 字符串 , 表示最大字符个数
*/
# eg
create table student(
		id int,
		name varchar(32),
		age int,
		score double(4,1),
		birthday date,
		insert_time timestamp
);

create table student2 like student; -- 复制表

R 查询表

show tables;

desc [表名]; -- desc 是describe 的缩写

show create table [表名]; --查看表的字符集

U 修改表

alter table [旧表名] rename to [新表名]; -- 修改表名

alter table [表名] character set [字符集]; -- 修改表数据字符集等信息

alter table [表名] add  [列名] [数据类型] ; -- 增加表的列（属性）

alter table [表名] change [旧列名] [新列名]（[数据类型]）; -- 改变列名（可以包括数据类型）
alter table [表名] modify [列名] （[数据类型]）; -- 改变列的数据类型

alter table [表名] drop [列名]; -- 删除列

D 删除表

drop table （if exists）[表名];

增删改表中数据 DML

增数据

# 下面这个语句用来填写表中部分数据
insert into [表名]([列名1],[列名2],……,[列名n]) values([值1],[值2],……,[值n]) （可增加多条）;
# eg
insert into student(id,name,age) values (1,'小明',18)(4,'小美',17);;

#下面这个语句是用来填写所有数据
insert into [表名] values([值1],[值2],……,[值n]);
insert into student values(3,'小刚',17,99.9,'1998-12-22','2022-10-27 21:22:22',NULL);

删数据

delete from [表名] （where [条件]）;

注意，

• 如果不加条件，则会删除表中所有数据 – 等价于 下面这条语句，但是下面这种方式效率跟高，上面的相当于一条一条删除，效率低

truncate table stu; --删除整个表，然后再创建一个一摸一样的空表

改数据

update [表名] set [列名1] = [值1] , [列名2] = [值2] ,……,[列名3]= [值3] （where [条件]）;

# 
update student set age = 22 where id = 3;

查询表中数据 DQL

查询语法

select * from [表名]; -- 查询表中所有数据

完整语法

select 
	[字段列表]
from 
	[表名]
（where
	[条件语句]
）
（group by
	[分组字段]）
（having 
	[分组之后的条件]）
（order by
	[排序]）
（limit
	[分页限定]）;

展示结果

# eg
select name,age from student;

# eg 打印全部属性时，不建议用 select * 方法，而建议 使用下面这种方式把所有属性全部写一遍，因为方便注释，更加规范
select 
	name , -- 姓名
	age , -- 年龄
from
	student; --学生表

# eg 结果去重复
select distinct name , adress from student;

# 计算并展示一些不存在的属性(但是可以从其他属性简单计算得到)
select mathscore, englishscore, mathscore + englishscore from   student;
-- 如果有NULL参与的运算，结果默认为NULL，可以用下面的方法 “解决”
select mathscore, englishscore, ifnull(mathscore,0) + ifnull(englishscore,0) from   student;
-- 起别名 （在属性后面 加一个 as 接 别名，as 可以省略）
select mathscore 数学, englishscore 英语, ifnull(mathscore,0) + ifnull(englishscore,0)  总分 from   student;

条件语句

条件运算符

1、 > 、 < 、 >=、 <= 、 = 、 <>或 !=
2、between …… and
3、in （集合）
4、like
5、is null
6、and 或 && 、 or 或 || 、not 或 !

# eg
select  * from student age between 20 and 30;-- 包含两边
select  * from student address in (北京,上海); 

# 注意 NULL 不能用整数来判断 要用IS NULL
select  * from student englishscore is null; 
select  * from student englishscore is not null; 

like 也叫 模糊查询，有点类似正则表达式

• 占位符：
  • _:单个任意字符
  • %:多个任意字符
  • select * from student where name like ‘马%’
  • select * from student where name like ‘%马%’

排序语句

select * from student order by math;
# 排序方式如果省略,默认升序

• ASC： 升序，默认的
• DESC ： 降序

select * from student order by math ASC, by english DESC; 

如果第一属性一样才会按照第二属性排序规则进行排序，多个条件时，以此类推

聚合函数

将一列数据作为一个整体进行计算

1、count  计算个数
2、 max  min
3、sum
4、avg

注意所有的聚合函数都会排除了空元素；

select count(name) from student;  -- 返回所有学生的数量（如果名字都为非空）
select count(ifnull(english,0)) from student; -- ifnull 函数
# 一般计数还是采用主键进行计算

分组查询

按照性别分组，分别查询男女 同学 的平均分

# eg
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 address, avg(math), count(id) from student where math>70 group by address having count(id)>2 ;

where 和 having 的区别？

• where 在分组之前进行限定，不满足条件不参与分组，having 在分组之后进行分组，不满足条件，不会被显示
• where 后面不能跟聚合函数的判断，having可以

分页查询

语法：
limit 开始的索引，每页显示条数

#eg
select * from student limit 0,3; # 第1页
select * from student limit 3,3; # 第2页
select * from student limit 6,3; # 第3页

公式 ：开始的索引 =（当前页码 -1 ）* 每页条数

limit 是一个mysql 的 分页“方言”

约束

对数据进行约束
分类：
主键约束 primary key
非空约束 not null
唯一约束 unique
外键约束 foreign key

非空约束

1、创建表时添加约束

create table stu(
		id int,
		name varchar(20) not null  -- not null 即对name属性 进行非空约束，后续添加时，name不能为空
); 

删除 name 的非空约束

alter table stu modify name varchar(20);

2、创建表后添加非空约束

alter table stu modify name varchar(20) not null;

编译时，sql语句会检查表中是否有空，有空会报错

唯一约束

create table stu(
		id int,
		name varchar(20) unique
); 

alter table stu modify name varchar(20) unique;

取消唯一约束

alter table stu drop index name;

注意，唯一约束 有且是能有一个null值

主键约束

主键约束
1、 非空且唯一
2、 一张表只能由一个字段为主键
3、 主键是表数据的唯一标识

# 创建时添加
create table stu(
		id int primary key,-- 主键约束
		name varchar(20)
); 

# 联合主键
create table tab_fav(
		rid int,
		uid int,
		primary key(rid,uid) --联合主键
); 

alter table stu modify id int  primary key;

取消主键！

alter table stu drop primary key; -- 不用指定属性，因为主键只有一个

自动增长

如果某一个属性时数值类型的，使用 auto_increment 可以完成值的自动增长

# 创建时添加
create table stu(
		id int primary key auto_increment,-- 主键约束,并实现自动增长
		name varchar(20)
); 

这样添加数据时可以不指定 id 的值， 会实现自动增长

insert into stu values(null , '小美');

添加时 也可以指定 id ，但是下一条记录会根据最后的数据增加

删除自动增长

alter table stu modify id int ; 

自动增长一般都和主键一起

外键约束

一般要配合多张表的操作

语法

create table 表名(
		列名 数据类型,
		...
		constraint 外键名称 foreign key （外键列名称） references 主表名称(主表（主键）列名);
		)

创建表时 增加外键约束
假设有 empolyee 和 department 两张表，empolyee 存储员工信息，dept存储 部门信息
employee 存储 员工 的 id 、年龄 、部门编号（外键）、
department 对应 部门编号（主键），部门名称，地址

create table department(
		id int primary key auto_increment,
		dep_name varchar(20),
		dep_location varchar(20)
);

create table employee (
		id int primary key auto_increment,
		name varchar(20),
		age int,
		dept_id int , -- 外键约束
		constraint emp_dept_fk foreign key (dept_id ) references department(id)
		/* 这一句表示增加了 从employee(dept_id) 到 父表 department(id) 的外键依赖 */
) ;

创建表后添加外键

alter table employee add constraint emp_dept_fk foreign key (dept_id ) references department(id);

alter table employee add constraint emp_dept_fk foreign key (dept_id ) references department(id) on update cascade ; --设置级联更新之后，附表的数据进行改动时，子表会自动跟新进行改动

alter table employee add constraint emp_dept_fk foreign key (dept_id ) references department(id) on delete cascade on update cascade; --设置级联更新/删除之后，附表的数据进行改动时，子表会自动跟新进行改动

删除外键

alter table employee drop foreign key emp_dept_fk;

注意
增加外键约束的子表可以删除，但是附表不能随意删除，只有子表被删除了，或者子表对父表的外键依赖删除了，才能删除父表

子表 外键依赖的属性数据不能随意添加，必须父表中 存在，才能添加

子表 外键依赖的属性数据 可以为 null 但是不能为 父表 不存在的值

多表

多表之间的关系

• 1、一对一
  人和身份证
  一对一关系实现可以在任意一方添加唯一外键直向另一方的主键
• 2、一对多，多对一
  部门和员工
  在多的一方建立外键，指向一的一方的主键，如上面外键的例子。
• 3、多对多
  学生和课程
  建立一个中间表，如 至少有两个字段，这两个字段作为第三张表的外键，要设置成联合主键，分别指向两张表的主键

数据库设计范式

要遵循后面范式时，必须遵循前面的所有范式

• 第一范式（1NF）
  每一列都是不可分可的原子数据项 （属性不能分割为子属性，即符合列）
  缺点：存在数据冗余，很多依赖项
• 第二范式（2NF）
  在第一范式的基础上，非码属性必须完全依赖于候选码
  PS：
  依赖，当一个属性A确定时，能完全确定属性B，称属性 B 依赖于 A，简写为A–>B
  如（学号，课程名称）-> 分数 ，可以称 分数依赖于属性组（学号，课程名称）
  完全函数依赖，想知道 被依赖属性 需要 依赖属性组的所有属性
  部分函数依赖，想知道 被依赖属性 需要 依赖属性组的部分属性
  传递函数依赖， A -->B, B–>C 则称属性C传递依赖于属性A
  码：一张表中一个属性或属性组呗其他属性所完全依赖，可以称这个属性为该表的码（有点类似于主键的意思，）
  问题，如一个表中把有属性 学号，课程名 ， 系名，系主任， 分数
  显然（学号，课程名）作为码，叫做主属性，其他为非码属性，但是系名和系主任只依赖于学号，不依赖于课程名，所以 这两个属性对于码来说，只是不分依赖 不是完全函数依赖。
  解决方案，将表拆分 表一 （学号，课程名，分数） 表二（学号，姓名，系名，系主任）
• 第三范式（3NF）
  在第二范式的基础上,任何非主不依赖于其他非主属性（在2NF的基础上消除传递依赖）
  如上面更改后的表，系主任与系名之间有函数依赖
  表一 （学号，课程名，分数） 表二（学号，姓名，系名） 表三（系名，系主任）

数据库备份与还原

1、命令行：

• 语法：
  • 备份：mysqldump -uXXX -pXXX 数据库名称 > 保存路径

# 备份
mysqldump -uroot -p1234  test > e://a.sql

执行完发现，e盘根目录多了两个文件

还原：

• 1、登录数据库
• 2、创建数据库
• 3、使用数据库
• 4、执行文件 source

# 进入mysql
# 删除原来的数据库
drop database test;

# 创建数据库
create database if not exists  test;
use test;
# 执行备份的source文件
source e://a.sql

2、图形化工具的备份和还原
备份，右键数据库直接有备份和导出等操作
导入，右键服务器，有一个执行sql脚本的操作，选中备份的文件执行

多表查询

select 
	[字段列表]
from 
	[表名1,表名2,...,表名n]
（where
	[条件语句]
）
（group by
	[分组字段]）
（having 
	[分组之后的条件]）
（order by
	[排序]）
（limit
	[分页限定]）;

如果不加任何条件的多表查询，一般称结果为这多张表的笛卡尔积（考虑了记录的所有组合情况，没有什么实际意义），要完成多表查询需要消除无用的数据

多表查询的分类：
内连接查询 外连接查询

内连接查询

隐式内连接 -用where 限定

   # 
	select employee.name,department.name,employee.salary from employee, department where employee.'dept_id' = department.'id';


	# 企业中更规范的写法为, t1,t2 是别名
	select 
			t1.name,
			t2.name,
			t1.salary
	from
			employee t1,
			department t2
	where 
			t1.'dept_id' = t2.'id' ;

显式内连接

select * 
from 
		employee 
inner join department 
on 
		employee.'dept_id'=department.'id';  --注意 inner 可以省略

# 省略 inner
select * 
from 
		employee 
join department 
on 
		employee.'dept_id'=department.'id';

外连接查询

左外连接

select 
		t1.*,
		t2.'name' 
from employee t1 
left （outer） join department t2 
on 
	t1.'dept_id' = t2.'id';

注意，这与上面的内连接的区别是： 生成的表会保留 employee 表 的 所有记录，即使对应的 dept_id 为空 或者，在department 表中没有对应的属性，也会保留

左外连接查询的是 左表的所有数据以及交集部分

右外连接

select 
		t1.*,
		t2.'name' 
from employee t1 
right （outer） join department t2 
on 
		t1.'dept_id' = t2.'id';

注意，这与上面的内连接的区别是： 生成的表会保留 department 表 的 所有记录，即使 对应的 id 为空 或者，在employee 表中没有对应的属性，也会保留

右外连接查询的是右表的所有数据以及交集部分

子查询：

查询中嵌套查询，被嵌套的查询叫做子查询。

3.1 子查询的查询结果是单行单列的
举例: 查询工资最高的员工的信息

# 查询最高的工资是多少  假设为 9000
select max(salary) from employee;

# 查询员工信息，并且工资等于 9000
select * from employee where employee.'salary'= 9000 ;


# 合并以上操作
select * from employee where employee.'salary'= (select max(salary) from employee) ;

# 查询工资小于 平均工资的人
select * from employee where employee.salary < (select avg(salary) from employee);

3.2 子查询的查询结果是多行单列的

-- 查询财务部 和市场部 的所有员工信息
select * from employee where dept_id in (select id from department where name = '财务部' or name ='市场部')；

使用运算符 in

3.2 子查询的查询结果是多行单列的

-- 查询员工的入职日期是2011-11-11 之后入职的员工 员工信息 和部门信息

# 子查询, 相当于把子查询结果当作一个虚拟表
select
		 * 
from 
		department t1 , 
		(select * from employee where employee.'join_date'>'2011-11-11') t2
where 
		t1.id = t2.dept_id;

# 普通内连接
select * from employee t1, department t2 where t1.'dept_id'=t2.'id' and t1.'join_date'>'2011-11-11';

事务

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

如 张三给李四转账500： 数据库的操作如下

• 查询张三的账户余额是否大于500？
• 张三账户 -500
• 李四账户 +500

上面任何一个操作失败都视为该事务失败，需要将业务进行回滚，回滚到事务之前

操作：
- 开始事务：start transaction;
- 回滚： rollback;
- 提交： commit；

假设有一个账户表
account （id，name， balance）

start transaction; -- 开始事务，手动开启事务
update acconut set balance = balance - 500 where name='zhangsan';
update acconut set balance = balance + 500 where name='lisi';

/* 以上代码 产生的结果只是临时数据
 如果不进行回滚或者提交不会写到最终的数据里面 */

commit; -- 如果发现正常，没有出错就可以提交，手动提交
rollback; -- 如果发现错误，回滚事务，回滚到 start transaction ;

在MySQL 中， 执行一句 DML 语句，会自动开启事务 如果没有报错 ， 执行完毕会自动提交事务。

修改事务的默认提交方式：
- 查看/修改事务的默认提交方式

	select @@autocommit; -- 1 代表自动提交 ， 0代表手动提交

	set @@autocommit = 0 ;  # 这样写了 DML 语句，只会子宫开始事务，不会自动提交
	# 要想使得 DML 语句生效 需要 在最后 commit 一下

事务的四大特征（面试常问）
1、原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2、持久性： 当事务提交之后， 数据库会持久化保存数据
3、隔离性： 多个事务之间，相互独立
4、一致性： 实务操作的前后，数据的总量不变

事务的隔离级别（了解）

• 多个事务之间彼此独立，当多个事务同时处理一批数据，可以能会引发一些问题，需要设置隔离级别
• 存在问题
  • 脏读： 一个事务读取到另一个事务没有提交的数据
  • 不可重复读（虚读），在同一个事务中，两次读取到的数据不一样
  • 幻读：一个事务操作（DML） 数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查不到自己的修改
• 隔离级别
  • read uncommitted：读未提交
    • 即在一个进程中 进行修改 但是未提交， 这个数据被另一个进程读取 ， 这个数据虽然没有被提交 依然会被读取，这时如果第一个进程回滚 。。。
    • 产生问题，脏读、不可重复读、幻读
  • read committed :读已提交 （Oracle 默认）
    • 设置成 读已提交，如果第一个进程的事务不提交，第二个进程读取的结果始终是 进程开启之前的。 这样可以避免脏读的问题，但是可能会导致 第二个进程在 自己的同一事务内 读取到的结果不一样。
    • 产生的问题： 不可重复读、幻读
  • repeatable read : 可重复读 （MySQL 默认）
    • 设置成 可重复读，即使第一个进程的事务已提交，的第二个进程的事务是要没有提交，他读取到的信息始终一样，不会改变。
    • 产生问题 ： 幻读
  • serializable : 串行化
    • 相当于给操作的数据加了锁，只有第一个进程的数据处理完毕提交之后，第二个进程的数据处理请求才会被处理，否则一直处于等待状态。
    • 可以解决所有问题

隔离级别从小到大安全性越高，但是效率越来越差
查询/设置隔离级别

select @@tx_isolation; -- 查询隔离级别
set global transaction isolation level 级别字符串; -- 修改隔离界别

DCL 管理用户，授权

一般使用该语言的是 ：DBA（ 数据库管理员）

管理用户

查询用户
先切换到mysql数据库（最开始提及到的 安装mysql 的 默认的四个数据库之一）

use mysql
select * from  user;

可以看到，表中虽然只有一个用户 root 但是却有两条记录
其实第一条的意思是通过本机登录 的 root 用户 和密码，密码是加密过后的
第二条的意思是 通过其他 任意主机（% 表示 任意主机） 登录 的root 用户 和密码

创建用户

create user '用户名' @'主机名' identified by '密码';

# eg
create user '张三' @'localhost' identified by '1234';
create user 'lissi' @'%' identified by '1234'; # % 表示任意主机

删除用户

drop user '用户名' @'主机名';

修改用户密码

# 法一
update user set password= 	password('新密码') where user = '用户名';

# 法二
 set password for '用户名'@'主机名' =password('新密码') ;

如果在mysql中忘记了 root 用户的密码该怎么办？

• （管理员）cmd： net stop mysql
• 使用无验证 登录 ：mysqld --skip-grant-tables
• 上述命令光标会不动，打开一个新的cmd窗口 输入 mysql 即可登录
• 改密码
• 管理两个窗口
• 打开任务管理器手动结束 mysql.exe 进程
• 重新打开登录

用户权限
查询权限

show grants for '用户名'@'主机名' ;

root 权限

授予权限

grant 权限列表 on 数据库名.表名 to  '用户名'@'主机名' ;

# 授予 次管理员权限
grant all on *.* to  '用户名'@'主机名' ;

撤销权限

revoke 权限列表 on 数据库名.表名 from  '用户名'@'主机名' ;