mysql相关内容详细总结

MySQL总结

---

零、MySQL环境检测及安装
1、检测本机上的mysql环境是否OK
打开cmd，输入:mysql -uroot -proot

1）如果回车后显示：Welcome to the MariaDB/mysql...表示本机上已经安装了mysql
	并且也配置了环境变量。(不用再安装以及任何配置)
2）如果回车后显示：mysql不是内部或外部命令，也不是可执行程序...，原因可能是：
	a）可能是电脑上安装了mysql，只是没有配制环境变量	（仅需配置环境变量！）
	b）也可以是电脑上既没有安装mysql，也没有配置环境变量 （需先安装，再配置环境变量）
3）如何判断，本机是否安装了mysql？
	由于mysql默认安装位置是：
		C:/Program Files/MariaDB 10.3
		C:/Program Files/mysql
		C:/Program Files/mysql5.5
		C:/Program Files/mysql5.7
		...
	如果有，点进去查看目录结构是否完整
4）如果已经安装，只需要配置环境变量即可
5）如果没有安装，先安装mysql，再配置环境变量

2、安装mysql软件
mariadb10.37版本

3、配置mysql环境变量
将mysql的安装目录下的bin目录配置到path环境变量中

一、数据库概述

1、什么是数据库
数据库: 存储和管理数据的仓库
早期：层次式数据库、网络型数据库
现在市场上流行的是关系型数据库和非关系型数据库

2、什么是关系型数据库
底层以二维表的形式来保存数据的库叫做关系型数据库

常见的关系型数据库有哪些:
SqlServer：微软，收费，适用于一些中型、大型的项目，在Java中的市场占比并不高。
Oracle：甲骨文公司，收费，适用于一些大型、超大型的项目（功能强大、性能优异），50%
MySQL：瑞典MySQLAB公司，免费开源，小巧灵活，学习成本低，20%
	MySQL被甲骨文公司收购
	开发成员：mariadb，永远开源免费，和mysql用法完全相同
DB2：IBM，在银行、金融行业使用的较多，在Java中的市场占比不高。
Sqlite：体积小，迷你数据库，用于智能家居，手机，pad等
...

3、数据库相关概念
数据库服务器: 比如我们安装的mysql软件,安装在电脑上之后,可以对外提供存取数据的服务/能力
在一个数据库服务器中可以创建多个数据库.

数据库:每一个数据库都是存取数据的仓库. 通常一个网站中的所有数据会存放在一个数据库中
	比如:
	www.jd.com		db_jd
	www.baidu.com	db_baidu
	...
表:每一个数据库中可以创建多张表,每张表用于存放一类数据,例如:
	用户信息	tb_user
	商品信息	tb_product
	订单信息	tb_order
表记录:每张表中又可以插入若干表记录,每一条表记录用于保存一个具体的事物

4、SQL语言
SQL: 是用于操作关系型数据库的通用的语言(学会了SQL语句,所有的关系型数据库都可以操作)
使用SQL可以实现(数据库/表/标记的)如下操作:
创建库、查看库、删除库、修改库
创建表、查看表、删除表、修改表
添加表记录、删除表记录、修改表记录、查询表记录
创建存储过程、视图、索引等
SQL语言是通用的，但每一个数据库厂商为了增强自己数据库的能力，都提供了方言。

5、如何连接mysql服务器（cmd）
打开cmd窗口,输入: mysql -uroot -proot
打开cmd窗口,输入: mysql -uroot -p
换行之后再输入密码
打开cmd窗口,输入: mysql -uroot -p -h主机名/ip地址 -P端口
换行之后再输入密码

退出连接mysql服务器: quit、exit、\q

注释符号: #注释内容
		-- 注释内容
		/* 注释内容 */
取消当前SQL语句的执行：\c

二、操作数据库、操作表（SQL）

创建数据库: create database 库名 charset utf8;
		create database if not exists 库名 charset utf8;
查看所有数据库: show databases;
删除数据库: drop database 库名;
		  drop database if exists 库名;
进入/选择数据库: use 库名
--------------------------------------------
创建stu表: 
	use mydb1;
	create table stu(
		id int,
		name varchar(50),
		gender varchar(10),
		birthday date,
		score double
	);
删除表: drop table if exists 表名;
查看表: show tables;
查看表结构: desc 表名;

三、新增、修改、删除表记录

具体参考讲义中的课堂练习
新增记录：
	insert into stu(id,name,gender,birthday,score) value(1,'张三','男','1985-9-1',86);
	-- 插入时，给所有列赋值，列名可以省略
	insert into stu value(1,'张三','男','1985-9-1',86);
修改记录：
	update stu set score=score+10;
	-- 如果要修改id大于1的所有学生的成绩
	update stu set score=score+10 where id>1;
	
删除记录：
	delete from stu; -- 删除stu表中的所有记录
	delete from stu where id>1; -- 删除id大于1的所有学生信息

	drop database 库名; -- 删除指定的库
	drop table 表名;    -- 删除指定的表

四、查询表记录（单表查询）

1、基础查询
	select * | 列名列表 from 表名
2、where子句查询
	select * | 列名列表 from 表名 where 列 运算符 值
3、模糊查询
	select * | 列名列表 from 表名 where 列 like 值
	值中要包含 % 或 _
	%: 匹配0个/1个/多个任意字符
	_: 匹配1个任意字符
4、多行函数查询
	count( * | 列名 ): 用于统计查询结果有多少行
	max( 列名 ): 求当前这一列中的最大值
	min( 列名 ): 求当前这一列中的最小值
	sum( 列名 ): 对当前这一列中的所有值求和
	avg( 列名 ): 对当前这一列中的所有值求平均值
	
5、分组查询
	select * | 列名列表 from 表名 [where子句] group by 列

6、排序查询
	select * | 列名列表 from 表名 [where子句] [group by 列] 
	order by 列 ASC|DESC
	ASC: 升序(默认值,可以省略)
	DESC: 降序
	
7、分页查询
	select * | 列名列表 from 表名 [where子句] [group by 列] 
	[order by 列 ASC|DESC] 
	limit (页码-1)*每页显示的记录数,每页显示的记录数

8、其它函数查询
	curdate() -- 获取日期:年月日
	curtime() -- 获取时间:时分秒
	sysdate()/now() -- 获取日期+时间, 年月日 时分秒
	year()/month()/day()/hour()/minute()/second() -- 从日期/时间中分别获取年、月、日、时、分、秒
	concat(s1,s2,...sn) -- 用于拼接字符串,s1~sn都是字符串
	concat_ws(opt,s1,s2,...sn) -- 用于拼接字符串,第一个参数是一个分隔符,在后面的s1~sn在拼接时,都需要在中间拼上这个分隔符

五、mysql的数据类型

1、数值类型
tinyint --> java中的byte
smallint --> java中的short
int --> java中的int
bigint --> java中的long
float --> java中的float
double --> java中的double
2、字符串类型
2.1.char类型: 定长字符串
char类型的存储范围是: 0~255个字符
name char(10): 声明一个name列,是字符串类型,长度不能超过10个字符
如果实际存储的数据长度小于10,剩余的空间会用空格补全,因此会造成空间浪费!
char类型在存储数据的效率略高于varchar类型

因此,char类型适合存储长度固定的数据(比如:学生证号,身份证号,员工编号等)
这样既不会浪费空间,存储数据的效率还较高!

2.2.varchar类型: 不定长字符串
varchar类型的存储范围是: 0~65535个字节
iso8859-1编码: 1个字符对应1个字节
GBK编码: 1个字符对应2个字节
UTF-8编码: 1个字符对应3个字节
name varchar(10): 声明一个name列,是字符串类型,长度不能超过10个字符
如果实际存储的数据长度小于10,剩余的空间还可以留给被的数据使用,不会造成空间浪费!
varchar类型适合存储长度不固定的数据,不会浪费空间!

3、日期类型：
date：年月日
time：时分秒
datetime：年月日 时分秒 2020-10-1 11:10:09
timestamp: 时间戳,格式和datetime相同,但实际存储的是从1970-1-1到指定日期的毫秒值

六、mysql的字段约束

1、主键约束（primary key）
如果为一个列添加了主键约束，那么这列的值就必须得是唯一且不为空的！
主键的作用：作为一行表记录的唯一标识（类似于人的身份证号码）。
如何添加主键约束：创建表时
create table stu(
id int primary key,
…
);

主键自增策略: 如果一个列是主键,并且类型是数值,可以为主键添加自增策略。添加了自增策略后，以后再插入数据时，可以不为主键赋值，数据库会自己维护一个变量（AUTO_INCREMENT）,该变量的值从1开始,每次用完后会自动加1,当插入数据时,如果没有给主键赋值,数据库就会从AUTO_INCREMENT变量上获取一个值,作为主键值插入到表中。
如何添加主键自增策略:
	create table user(
		id int primary key auto_increment,
		...
	);

主键约束补充: 如果表已经建好了,如何为id添加主键约束:
	例如:将mydb1.stu表中的id设置为主键自增:
	alter table stu modify id int primary key auto_increment;
	再插入数据,就可以不用给id赋值了
	insert into stu values(null,'张三','male','1988-1-2',83);
	insert into stu values(null,'李四','male','1989-3-4',85);

2、唯一约束（unique）
如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的,但可以为空(null)
如何添加唯一约束:
create table user(
…
username varchar(50) unique not null,
…
);
主键: 唯一且不为空(主键可以唯一的标识一条表记录,类似于身份证号/学生编号/员工工号)
唯一+非空: 唯一且不为空

3、非空约束（not null）
如果为一个列添加了非空约束,那么这个列的值就不能是空的,但可以重复!
如何添加非空约束:
create table user(
…
password varchar(50) not null,
…
);

4、外键约束（foreign key）
问题1: 如何保存两张表数据之间的对应关系?
在其中的一张表中添加列,用于保存另外一张表的主键,以此来保存两张表数据之间的对应关系。
例如：在emp表中添加一个dept_id列,用于保存dept表中的id列,表示员工所属的部门编号

问题2: 如何避免在删除部门后,员工表中出现冗余数据?
	方式一: 在删除每一个部门之前,先检查部门下还有没有对应的员工,如果有,先将员工删除或者移到别的部门,再删除部门即可
	方式二: 通知数据库员工表和部门表之间存在对应关系,员工表中的dept_id列是要严格参考部门表中的id列。即设置dept_id这个列为外键。通知完后，数据库会一直帮我们盯着，如果再删除部门，部门下有员工，数据库就会阻止我们删除！

问题3: 什么是外键?
	外键就是用于通知数据库两张表(比如部门和员工表)数据之间存在对应关系的这么一个列。
	
问题4: 如何添加外键?
	create table dept(
		id int primary key auto_increment,
		name varchar(50)
	);
	create table emp(
		id int primary key auto_increment,
		name varchar(50),
		dept_id int,
		foreign key(dept_id) references dept(id)
	);

练习: 在没有将dept_id设置为外键的情况下去删除一个部门,查看是否能删除?
		可以删除，数据库不知道两张表存在对应关系，所以不会阻止我们删除！
	如果将dept_id设置为外键的情况下去删除一个部门,查看是否能删除?
		如果部门下有员工，删除会失败，此时数据库知道两张表存在对应关系
		并且会帮我们维护这个关系，所以会阻止我们删除有员工的部门！

		如果加了级联删除,则在删除某一个部门的同时,先删除该部门下的所有员工,再删除部门本身!

七、表关系

1、一对多（多对一）
例如: 部门 对 员工
班级 对 学生
在保存一对多或者多对一的表关系时,是在多的一方添加列,来保存一的一方的主键,从而来保存两张表数据之间的对应关系

2、一对一
例如:班级 对 教室
在保存一对一的表关系时,可以在任意一方添加列,来保存另一方的主键,从而来保存两张表数据之间的对应关系

3、多对多
例如:学生 对 教师
在保存多对多的表关系时,在任何一方添加列都不合适,此时可以再创建一张表专门用于保存两张表（学生表和教师表）的对应关系,再创建的这个表中,至少有两个列,分别用于保存两张表（学生表和教师表）的主键，并且将这两个列设置为联合主键。

八、多表查询

1、连接查询
查询部门和部门对应的员工信息
select * from dept,emp;
笛卡尔积查询: 指同时查询两张表,其中一张表的记录有m条,另一张表的记录有n条,笛卡尔积查询的结果就是 m*n条。
由于笛卡尔积查询的结果中存在大量错误的数据,因此我们不会直接使用这种查询!
可以在笛卡尔积查询的结果上,添加where子句,通过where子句将结果中的错误数据剔除,保留下的就是正确数据!
– 连接查询
select * from dept,emp
where emp.dept_id=dept.id;
– 内连接查询
select * from dept inner join emp
on emp.dept_id=dept.id;

2、左外连接查询和右外连接查询
左外连接查询：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。例如：
– 查询部门表中的所有部门,以及部门对应的员工
select * from dept left join emp
on emp.dept_id=dept.id;

右外连接查询：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。
-- 查询所有员工及员工对应的部门
select * from dept right join emp
on emp.dept_id=dept.id;

3、多表查询练习