MySql-环境安装与SQL基本语言以及SQL相关概念

Mysql安装网址：

安装mariadb官网：https://downloads.mariadb.org/mariadb/10.5.9/
检查是否安装好：window+R,输入mysql --version；如果显示ok证明已经安装配置好，否则安装或配置有问题。
mariadb这个相对于mysql更加小巧一些，安装也比较方便，可以用mariadb代替mysql使用。
注意下载安装包选择windows系统，64位的，选择mis类型的下载，zip还需要配置，比较麻烦，mis可以安装后直接运行。
配置环境变量，将其bin目录配置到path路径，方便以后使用。

数据库相关概念

1. 什么是数据库：按照数据结构用来组织、存储和管理数据的仓库。是一个长期存储在计算机内的一个仓库。
2. 数据库存储数据的优点：方便、安全、专业，可以实现数据共享、备份，避免用户数据各自建立存储，减少数据冗余
3. 数据库类型有哪些：层次式数据库、网络型数据库；关系型数据库-底层以二维表保存数据（大多数）、非关系型数据库（键值对形式保存数据）
4. 常见的关系型数据库：Oracle（大型）、SQL Server（大型）、Mysql（适用于中小型,集群可实现大型或超大型数据库）
5. 数据库服务器：其实就是安装的mysql软件后，计算机就能实现数据的存取服务
6. 一个数据库服务器可以用于管理多个数据库

SQL 语言

SQL语言是一种操作关系型数据库的通用语言（学会了SQL可以操作所有的关系型数据库）
SQL语言可以做什么：
（1）创建数据库
（2）创建表、删除表、修改表、查看表
（3）新增表记录、删除表记录、查看表记录、修改表记录
（4）存储过程、视图、索引也可以操作

如何连接MYSQL

方式一：mysql -u用户名 -p密码
方式二：mysql -u用户名 -p 下一行键入密码
方式三：mysql -u用户名 -p -h主机名或者ip地址 -p端口
-h：后面跟的是主机名，默认是127.0.0.1
-p： 后面跟的是端口3306是默认端口
退出：quit退出，表示退出程序，exit也可以退出

SQL操作

• show databases 表示展示所有的目前数据库有哪些
• use mysql --进入mysql数据库
• ues test —进入test数据库
• drop database+库名 --删除库
• create databases 库名 charest 编码 --设置指定的编码
• show create +库名 —查看建库语句
• drop table+表名—删除表
• create table 表名+（）；
• select * from + 表名

SQL语句函数

• curdate（）–获取当前时间+日期
• year（date） --返回date中的年份
• month（date）–返回date中的月份
• day（date）–返回date中的天数
• hour（date） --返回时间中的小时
• minute（date） --返回时间中的分钟
• second（date） --返回时间中的秒
• CONCAT（s1,s2…) --将s1,s2等多个字符串合并为一个字符串
• CONCAT _WS（X,s1,s2…) 同CONCAT（s1,s2）一样，但是每个字符之间加上一个x,x是分隔符

Mysql的数据类型

1.1 数值类型:mysql中提供了多种数值类型,其中包括tinyint\smallint\bigint\float\double\decimal其中较为常用的就是int和double

1.2字符串类型
char类型:定长字符串,char(n),n的范围是0~255;
varchar类型:变长字符串,varchar(n),n的范围是:0~65535个字节

1.21.laitin编码中,1个字符对应一个字节,n的最大值是65535/1;
		测试表:create table t2(id int,name varchar(65532))charest latin1;
					create table t2(id int,name varchar(60000)charest latin1;
					
1.2.2. bgk编码中,1个字符对应2个字节,n的最大值小于65535/2;
		  测试表:create table t3(id int ,name varchar(37767)charest gbk);报错
		  测试表:create table t3(id int ,name varchar(32767)charest gbk);报错
		  测试表:create table t3(id int ,name varchar(30000)charest gbk);
1.2.3 utf8编码中,1个字符对应3个字节,n的最大值小于65535/3;
		  测试表:create table t4(id int ,name varchar(30000)charest gbk);报错
		  测试表:create table t4(id int ,name varchar(20000)charest gbk);
		  
char类型之所以被称为定长字符串,是因为一旦确定了n的最大字符数,不管cun的数据是多少,该数据占用的空间就是n个字符.例如:name char(10),存入'张三丰',存入3个字符,剩余空间会用空格不全,不会给别的字符串.使用.因此char类型可能会浪费空间
char类型适合用于存储长度固定的数据:例如:
student_id char(11):利用这个列存储所有学生的编号
idcard char(18),用这个列存储所有人的身份证号码
		  
char类型相比于varchar类型速度快一些,因为char类型只需要判断一个数据能否存入该列中,而不需要将空间给别的数据使用.
varchar类型之所以叫变长字符串,是因为即使确定了n,n只是限制该列中最多能存的字符数,如果实际存的字符数据量小于n剩余的空间还可以留给别的字符串使用.例如: name char (10),存入'小老鼠',存入了3个字符,剩余的7个空间会留给别的数据使用,因为varchar类型不会浪费空间. varchar类型适合用于长度不固定的数据.

1.2.4char和varchar有什么区别?
(1)char最多能存255个字符,varcahr类型最多可以存65535个字符;
(2)char类型如果存的数据量小于最大长度,剩余的空间会用空格填充,因此可能会浪费空间,因为varchar类型适合存储长度固定的数据,这样既不会浪费空间,效率还比varchar类型略高;
(3)varchar类型如果存的数据量小于最大长度,剩余空间会留给别的数据使用,所以varchar适合存储长度不固定的数据,这样虽然没有char存储效率高,但不会浪费空间

1.3:日期类型
date: 日期类型,格式是:年月日
time:时间类型,格式是:时分秒
datetime:日期+时间,格式是:年月日 时分秒
timestamp:时间戳,格式是和datetime格式一样,也是:年月日 时分秒
(1)范围上:datetime范围是:1000~9999(年份)
timestamp范围是:1970到2038年
(2)实际上存的数据:
datetime实际上存的就是一个年月日 时分秒 格式的日期+时间
timestamp实际上存储的是从1970年1月1日到now这个日期+时间的时间毫秒值
(3)在使用上,timestamp可以设置自动获取当前时间作为值插入到表中,而datetime不可以

Mysql的字段约束

mysql -uroot -proot
use mydb1;
drop table if exists stu;
create table stu(
id int primary key auto_increment,
name varchar(50),
gender varchar(10),
birthday date,
score double
);

insert into stu value(1,'小龙女','小姐姐','1995-09-1',66);

insert into stu value(null,'小燕子','小姐姐','1996-02-03',77);

insert into stu value(null,'紫薇','小姐姐','1997-09-08',88);

insert into stu value(null,'尔康','小哥哥','1998-07-06',99);

insert into stu value(5,'永琪','小哥哥','1999-9-9',86);

select * from stu;

2.1 主键约束
如果一个列添加了主键约束,那么这个列的值必须是非空的且不能重复,一张表里面往往只能有一个主键,因为主键常用来表示唯一的一行记录,就像人的身份证一样,添加主键约束的格式:

create table stu(
	id int primary key,
	...
);

如果id是数值类型,为了维护方便,可以设置主键自增策略,设置方法:

create table stu(
	id int primary key auto_increment,
	...
);

 auto_incrementde 值增加1

2.3: 唯一约束
如果一个列添加了唯一约束,那么这列的值就不能为重复,但是可以为空(null)
比如:网站绑定的邮箱,前期可以不绑定为空,但是一旦绑定,这个邮箱是不能和其他账号的邮箱重复的.
添加唯一约束的格式:

create table stu(
		...
		email varchar(50)unique,
		...
 	);

 	create table stu(
		...
		username varchar(50)unique not null, -- 用户名既不能为空也不能重复
		...
 	);

	主键约束 和 (非空+唯一约束)有什么区别?
	(1)主键约束和(非空+唯一约束)的特点是相同的,都是既不能为空也不能重复;
	(2)主键约束出了非空且不能重复以外,还可以表示唯一记录,即作为表记录的唯一标识

Mysql的外键约束

外键约束不同于主键\非空\唯一约束,外键约束用于标识两张表之间的对应关系,思考一下:
(1)如何保存部门dept的员工emp两个表之间的对应关系?
可以在员工表中添加一个列,如dept_id保存部门的员工部门关系;可以将dept_id设置为主键,当然也可以不设置

(2)添加为外键和不添加外键有什么区别?
(2.1)如果不添加外键:
对于数据库来说dept_id这个列就是一个普通的列,数据库不会知道dept和empt两站表之间的关系,自然也不会帮我们去维护这层关系;
假如现在要删除dept表中某一个部门,如4号部门,删除之后4号部门里面的对应员工就会找不到部门;
而对应员工后面的dept_id也会变为冗余数据,这样就会破坏数据库中数据的完整性和一致性.
(2.2)如果将dept_id设置为外键,相当于通知数据库,dept和emp这两张表存在对应关系,emp的id列严格对应dept中的id列,这样数据库就会帮我们维护这两张表之间的关系.
假如添加了外键约束后,想要删除dept表中的某一个部门(4号部门),数据库会检查这个4号部门在emp表格中还有没有对应的员工,如果有,数据库就会阻止我们删除,这样就保证了数据的完整性.
如果非要删除,可以将4号部门的员工记录先转移到其他部门或者删除,只要保证删除部门中没有员工即可.

表关系

(1)1对多的关系或者多对1的关系:在这种关系中往往会在多的一方添加列,保存1的一方为主键(可以设置为外键,也可以不设置为外键看需求)
部门表(1)员工表(*)

(2)1对1的关系:例如班级和教室,在这种关系中,在奶房添加关系都可以保存另一方的主键(可以设置为外键,也可以不设置为外键)
班级表(1),教室表(1),在班级表中添加列(1)添加(room_id)来保存教室的编号
班级表(1),教室表(1),在教室表中添加(1)添加列(class_id)来保存班级的编号
(3)多对多的关系:一个学生有多个老师,一个老师有多个学生

 	create table stu(
		stu_id int primary key auto_increment,
		name varchar(20)
 	);

 	create table teacher(
		...
 	);

	在这种关系中,在任何一方添加保存另一方的主键都不合适
	此时可以载创建一张表,在这种表中分别添加两个列(stuid,teaid),分别用于保存学生表的主键
	和教师表的主键,以此来保存学生和教室的关系.

多表查询

(1)连接查询:准备数据进入对应库db30;
(2)查询部门和部门对应的员工信息:

select * from dept,emp;

得到结果如下:
以上查询救过为笛卡尔积查询

笛卡尔积查询:其实就是同事查询两张表,其中一张表有m条记录,另外一张表有n条记录,可以查询m*n条数据
一般不会直接使用这种查询,会在这个查询的基础上剔除错误数据,保留正确数据
这个剔除错误数据可以通过where字句来实现,这个就是连接查询
select * from dept,emp where emp.dept_id = dept.id;
内连接查询:查询结果和上面查询结果是一样的
select * from dept inner join emp on emp.dept_id = dept.id;
查询结果如下图:

(3)连接查询
查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null
如果两张表在连接查询时,要求查询出其中一张表的所有数据,此时可以使用左外连接查询或者右外连接查询
select * from dept left join emp on emp.dept_id = dept.id;
相同效果的有下面语句:
select * from emp right join dept on emp.dept_id = dept.id;
– 如果要查询部门表中的所有数据,而部门表在左边,此时可以选择使用左外连接查询,可查询所有部门信息,员工信息只显示和部门对应的,因为有筛选条件保证把错误的都剔除过滤
【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

(4)查询[所有员工]所属部门,如果某个员工没有所属部门,部门显示为null
[右外连接查询]:可以将右边表中的所有记录都查询出来,左边表知识显示和右边相对应的数据,如果右边表中某些数据左边表中没有记录,则可以显示为null.
select * from dept right join emp on emp.dept_id = dept.id;
相同效果的有下面的语句:
select * from emp left join dept on emp.dept_id = dept.id;
上面两个sql语句的执行效果如下:
查询所有部门以及所有员工,如果部门对应没有员工可以为null.如果员工没有对应部门,对应部门可以为null

这种应该使用全外查询,但是mysql不支持全外查询,可以通过union模拟这种查询.

select * from dept left join emp on emp.dept_id = dept.id;

select * from dept right join emp on emp.dept_id = dept.id;

执行结果如下图显示(但是这两个结果都不符合我们的需求,要用union连接):
测试 union连接后的效果:

select * from dept left join emp on emp.dept_id = dept.id
union
select * from dept right join emp on emp.dept_id = dept.id;
执行效果如下图:
测试一下:union 后面加一个all有何区别?
select * from dept left join emp on emp.dept_id = dept.id
union all
select * from dept right join emp on emp.dept_id = dept.id;

执行效果如下示意图:
由此可以推出:
union加上all是不去重,不加all是有去重功能

能够使用union和union all合并结果的前提是:
(1)两条sql语句查询的结果列数必须相等;
(2)两条sql语句查询的结果列名必须相等(低版本mysql要求列名相等)

子查询练习

先用db40库中的表记录,先进入db40库;
(1)求出王老师的薪资?

select name,sal from emp where name='王老师';

(2)求出比王老师薪资高的薪资?

select name,sal from emp where sal>(select sal from emp where name='王老师');

上面两个sql语句执行后的结果如下图:
虽然第二条和第三条的两条sql语句的执行效果是一样的,但是不能使用第二条sql语句的写法.因为第二条sql语句把程序写死了,以后如果万一王老师的工资数据有调整,整个表格的查询结果都会有问题,破坏了程序的拓展性和前后一致性原则.应该采用第三条sql语句的方式,这样之后如果王老师的sal数据有调整,也能够保证数据的准确性.

像上面这种将一个sql语句的执行结果作为另外一个sql语句的条件来执行,这就是子查询的用法.

(3)列出与刘老师从事相同职位的所有员工,显示姓名\职位:

select name,job from emp where job = (select job from emp where name='刘老师');

执行结果显示如下图:

多表查询练习

(1)列出在’培优部’任职的员工,假定不知道’培优部’的部门编号,显示部门名称,员工名称.
所用的sql语句如下:

select dept.name,emp.name from emp,dept where emp.dept_id=dept.id;

执行结果如下:
这里在书写sql语句的时候,需要关注sql语句的顺序:

1. 查询的列:select dept_name,emp_name;
2. 查询的表:from emp,dept – 这里注意因为员工对部门,是多对1的关系,所以员工方前面,部门放后面比较合适;
3. 筛选条件:where emp.dept_id = dept.id and dept.name=‘培优部’;

加上部门名称的筛选条件怎么写:
select dept.name,emp.name from emp,dept where emp.dept_id=dept.id and dept.name=‘培优部’;
执行结果如下图:
如果采用内连接方式怎么写:

select dept.name,emp.name from dept inner join emp on emp.dept_id=dept.id where dept.name=‘培优部’;

执行的结果如下图所示:
查询的列: select dept.name,emp.name
查询的表:from dept inner join emp
筛选条件:on emp.dept_id = dept.id and dept.name = ‘培优部’;

(2)查询所有员工及其直接上级,显示员工姓名\上级编号\上级姓名

(自查询:指的是把一张表拆成两张表来用)

/* 查询表:emp e1 -- 表示员工表; emp e2 -- 表示上级表
		员工上级的编号 = 上级编号;
from emp e1,emp e2;
查询列:emp e1.name ,select emp e2.name
筛选条件:from emp.e1,emp.e2
筛选条件:where e1.topid =e2.id;
*/
select e1.name '员工姓名',e2.name '上级姓名',e2.id '上级ID'  from emp e1,emp e2 where e1.topid=e2.id;
执行结果显示如下图:

(3)列出最低薪资大于1500的各种职位,显示该职位的最低薪资
select job,min(sal) from emp,dept where sal>1500 group by job;
执行结果如下图:
(4)求出有那些职位的最低薪资是大于1500的
错误原因:

• where这里容易出现在from前面的错误,当然上面这个sql语句中没有出现
• where中不能出现使用多行函数(列别名也不能在where中)
• where是在分组之前执行,先过滤掉一些记录,在用剩下的进行分组,而在这个题当中是要先分组再过滤,所以不能用where应该使用having,这里的逻辑需要重点理解一下.

所以,正确的sql语句应该是:
select job,min(sal) from emp group by job having min(sal)>1500;
执行结果如下:
这里注意理解where和having的区别:

• where和having都是用于对表中的内容进行筛选过滤
• where用于在分组过滤之前对记录进行筛选过滤,而having用于分组之后的记录进行筛选过滤
• where子句中不能使用多行函数和列别名,但是可以使用表别名
  select name as’姓名’, sal as ‘薪资’ from emp ‘e’;
  其中上面的sql语句中,姓名和薪资都是列别名,e是表别名
• having子句中可以使用多行函数,以及表别名\列别名

(4)列出在每个部门就职的员工数量\平均工资.显示部门编号\员工数量\平均薪资
具体sql语句如下:
select dept_id ‘部门编号’,count() ‘员工数量’,avg(sal) ‘平均薪资’
from dept,emp
where dept.id=emp.dept_id group by dept_id;
执行结果如下:
(5)列出受雇日期早于直接上级的所有员工,显示员工编号\员工姓名\部门名称.
/ emp e1表示员工表 emp e2表示上级表/
查询的表:from emp e1’员工表’,emp e2’上级表’,dept
查询的列:e1.hdate,e2.hdate
筛选条件:e1.hdate<e2.hdate
连接条件:e1.topid = e2.id
具体的sql语句如下:
select e1.id ‘员工编号’,e1.name ‘员工姓名’,e2.id’上级编号’,e2.name ‘上级姓名’ ,dept.name
from emp e1 ,emp e2 ,dept
where e1.topid = e2.id and e1.dept_id=dept.id and e1.hdate<e2.hdate;
上面sql语句的执行结果如下图:
最后可以验证一下:
select * from emp;
select * from dept;

经过验证上面的数据是没有问题的.