带你走进MySQL数据库（MySQL入门详细总结二）

导读：关于MySQL，用三篇文章带你进入MySQL的世界。文章较长，建议收藏再看！

带你走进MySQL数据库（MySQL入门详细总结一）

1.连接查询（多表查询）

1.什么是连接查询？
*在实际开发中，大部分的情况下都不是从单张表中查询数据，一般都是多张表联合查询最终的结果。

*在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。如果存储在一张表中就会出现大量的冗余。
2.连接查询的分类
*根据语法出现的年代来划分的话，包括：
SQL92（一些老的DBA可能还在使用，DBA：DataBase Administator,数据库管理员）

SQL99(比较新的语法）
*根据表的连接方式来划分，包括：

• 内连接
  等值连接，非等值连接 ，自连接
• 外连接
  左外连接（左连接）
  右外连接（右连接）
• 全连接(很少用）

3.在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）两张表连接，没有限制的话为两张表的乘积。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
select ename,dname from **emp ,dept **;
会出现56条记录（56 rows in set (0.00 sec)）。因为没有条件限制，查询结果条数为两张表记录条数的乘积。

*关于表的别名：
select e.ename ,d.dname from emp e,dept d;
表的别名有什么好处：
第一：执行效率高
第二:可读性好。

4.怎么避免笛卡尔积现象？
*1加条件进行过滤。

*2思考：加条件不能避免笛卡尔积现象，即不会减少匹配次数。只是显示的是有记录。

*3案例：找出每一个员工的部门名称，要求显示员工名和部门名。

select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;(条件）//为SQL92语法，以后不用(因为官方说不够清晰）

显示：
±-------±-----------+
| ename | dname |
±-------±-----------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
±-------±-----------+
5.内连接之等值连接：
特点：条件是等量关系。

*案例：找出每一个员工的部门名称，要求显示员工名和部门名。
SQL99：（常用的）

**

select** 
	e.ename,d.dname
**from**
	emp e
**（inner) join** （inner可以省略，可读性好。）(内连接）
	dept d
**on**
	e.deptno = d.deptno;

*还可以在on后面写where。（结构清晰）
6.内连接之非等值连接：

特点：连接条件中的关系不是等量关系。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
select
e.ename,e.sal,s.grade
from
emp e
join (inner可以省略）
salgrade s
on
e.sal between s.losal and s.hisal;（即条件不是等量关系)
7.自连接
特点：一张表看做两张表。自己连接自己。

案例：找出每个员工的上级领导，要求显示员工名和对应领导名。

员工的领导编号= 领导的员工编号。

select
a.ename as '员工名’ ，b.ename as '领导名'
from
emp a
inner join(inner可以省略）
emp b
on
a.mgr = b.empno;

8.外连接
什么是外连接和内连接有什么区别？

**内连接：**假设A和B表进行连接，使用内连接的话，凡是A表和B表能匹配的记录查询出来，这就是内连接，AB两张表没有主次之分。

**外连接：**假设A和B进行连接，使用外连接的话,AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，副表中的数据没有和主表的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类：
左外连接：（左连接）：表示左边的这张表是主表。

右外连接：（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接有左连接的写法。

案例：找出每个员工的上级领导。（所有员工必须查询出来）
select
a.ename '员工’ ， b.ename ‘领导’
from
emp a
left （outer) join(左外连接,outer可以省略）
emp b
on
a.mgr = b.empno;
改为右连接
select
a.ename '员工’ ， b.ename '领导‘
from
emp b
right （outer)join(右外连接,outer可以省略）
emp a
on
a.mg r = b.empno;
*外连接特点：主表数据无条件的全部查询出来。

案例：找出哪个部门没有员工？
select
e. *,d. *
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
全连接，两个都是主表。（很少使用）

9.三张以上的表的查询。
案例:找出每一个员工的部门名称和工资等级。
…
A
join
B
join
C
on
…
分析：A和B表先进行连接，在和c表进行连接。

select(内连接）
e.ename, d.dname,s.grade
from
emp e
join
dept d
on
e.deptno =d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
案例二：找出每一个员工的部门名称、工资等级、以及上级领导

select(外连接）
e.ename, d.dname,s.grade
from
emp e
join
dept d
on
e.deptno =d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;

2.Navicat工具

1.Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。Navicat是闻名世界、广受全球各大企业、政府机构、教育机构所信赖，更是各界从业员每天必备的工作伙伴。自2001 年以来，Navicat 已在全球被下载超过 2,000,000 次，并且已有超过 70,000 个用户的客户群。《财富》世界 500 强中有超过 100 家公司也都正在使用 Navicat。

2.它可以用来对本机或远程的 MySQL、SQL Server、SQLite、Oracle 及 PostgreSQL 数据库进行管理及开发。Navicat的功能足以符合专业开发人员的所有需求，而且对数据库服务器的新手来说又相当容易学习。有了极完备的图形用户界面 (GUI)，Navicat 让你可以以安全且简单的方法创建、组织、访问和共享信息。

3.Navicat适用于三种平台 - Microsoft Windows、Mac OS X 及Linux。它可以让用户连接到任何本机或远程服务器、提供一些实用的数据库工具如数据模型、数据传输、数据同步、结构同步、导入、导出、备份、还原、报表创建工具及计划以协助管理数据。

4.新手不建议使用Navicat，因为不用写SQL语句，耽误你的成长。

3.子查询（嵌套select)

1.select语句当中嵌套select语句，被嵌套的select语句是子查询。
*子查询可以出现的位置？
select
…(select)
from
…(select)
where
…(select)
2.where子句中使用子查询
案例：找出高于平均薪资的员工信息。
select * from emp where sal > avg(sal);//错误写法，where后面不可以直接使用分组函数。

第一步：找出平均工资
select avg(sal) from emp;

第二步：where 过滤
select * from emp where sal >2073.xxx

合并：
select * from emp where sal>(select avg(sal) from emp);
3.from后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级
第一步：找出每个部门平均薪水（按照部门分组，求sal的平均值）
select deptno ,avg(sal) as abgsal from emp group by deptno;

第二步：
*将以上的查询结果当作临时表t,让t表和salgrade表连接，条件是：t.avgsal between s.losal and s.hisal

select
t.*,s.grade
from
（select deptno,avg(sal) as abgsal from emp group by deptno )t
join
salfrade s
on
t.avgsal between s.losal and s.hisal

案例：找出每个部门平均的薪资等级（先求每个员工薪资的等级再求其平均）
第一步：找出每个员工的薪水等级

select
	e.ename,e.sal,e.deptno,s.grade
from 
  emp e 
join 
	salgrade s
on 
   e.sal between s.losal and s.hisal;

第二步：基于以上结果，继续按照deptno分组，求grade平均值。
select
e.deptno,avg（s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
4.在select后面嵌套子查询。

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

select
e.ename,e.deptno,
(select d.dname from dept d where e.deptno=d.deptno)as dname
from
emp e;
第二种方式：(不用select嵌套）

select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;

4.union

*可以将查询结果集相加。
案例：找出工作岗位是SALESMAN和MANAGER员工？
第一种：
select ename,job from emp where job =‘MANGER’ or job =‘SALESMAN’;

第二种:
select ename,job from emp where job in(‘MANGER’ ,SALESMAN’);

第三种：
select ename,job from emp where job =‘MANGER’
union
select ename,job from emp where job =‘SALESMAN’
union可以将两张不相干的表中的数据拼接在一起。
union两个部分的列数要相同。

5.limit

*1.limit是mysql中特有的，(Oracle中有个相同的机制，叫做rownum)主要用于取结果集的部分数据。

*语法机制:

• limit startIndex ,length
  startIndex 表示起始位置。
  length表示取几个。

2.案例：取出工资前5名的员工(思路：降序取前5个数据。）
select ename,sal from emp order by sal desc;
取前五个：
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;

*limit 是sql语句最后执行的一个环节。
select----->5
…
from----->1
…
where------->2
…
group by------->3
…
having--------->4
…
order by--------->6
…
limit---------> 7
…;
3.案例：找出工资排名在第4到第九名的员工。
select ename,sal from emp order by sal desc limit 3,9;
4.通用的标准分页sql:
每页显示3条记录：
第一页：0，3
第二页：3，3
第三页：6，3
第四页：9，3
第五页：12，3
每页显示pageSize条记录：
第pageNo页：(pageSize-1)*pageSize，pageSize
java代码{
int pageNo =2;//页码为二
int pageSize = 10;//每页显示10条

limit 10,10;
}

6.创建表，插入（增加）数据，删除数据，和修改(更新）数据

1.建表语句的语法格式：
create table 表名（
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
…
);

2.关于MySQL当中字段常见的数据类型：
int 整数型
bigint 长整型（long)
float 浮点型（float,double)
char 定长字符串
varchar 可变长字符串（255字符）(stringBuffer/StringBuilder)
date 日期类型(java.sql.Date)
BLOB 二进制大对象（存储图片、视频等流媒体信息）Binary large OBject(java的object)
CLOB 字符串对象 （存储较大文本，比如，可以存储4G的字符串）Character Large OBject
*BLOB和CLOB类型的使用：
电影表：t_moive
id(int) name（varchar) playtime(date/char) poster(BLOB) history(CLOB)
可以将路径放进去。一般不会将视频放到表中

*表最好以_t或者_tbl_开始。
案例：创建学生表
学号、姓名、性别、班级编号、生日
学号：bigint
性别：char
班级编号：varchar
生日：char
create table t_student(
num bigint,
name varchar(255),
sex char(1),
classnum varchar(255),
birthday char(10)
);

详细的MySQL数据类型介绍

3.insert语句插入数据
*语法格式：
insert into 表名（字段名1，字段名2…字段名n)values(值1，值2，…值n)

*要求：字段的数量和值的数量相同，并且数据类型相同。
insert into t_student(num,name,sex,classnum,birthday)values(3,‘MengYangChen’,‘m’,‘XinA1811’,‘1999-09-10’);
*只要字段和值对应即可，没有顺序可言。
*可以插入部分数据，剩余字段默认添NULL。

*drop table if exists t_student;//当存在时删除。

*default在建表时，在字段名后面使用，表示默认值。

*当insert成功后，不能用再insert修改某些字段的数据，只能用updata进行更新。
*其他写法
insert into t_student values(5,‘jack’,‘w’,‘XinA1811’,‘2000-10-18’);

一次插入多行数据：
insert into t_student (num,name,sex,classnum,birthday)
values
(6,‘Hai’,‘w’,‘XinA1811’,‘2000-10-18’),(7,‘WenChang’,‘w’,‘XinA1811’,‘2000-10-18’);
4.表的复制
语法：
create table 新表名 as select 语句；
例如：
create table t_student2 as select name,birthday from t_student;

*t_student2的部分内容。
±-------------±-----------+
| name | birthday |
±-------------±-----------+
| MengYangChen | NULL |
| Huangjiahuan | NULL |
| LiuQingDong | NULL |
其实质为将查询结果当作表创建出来。

5.将查询结果插入另一张表中。
注意：字段数要相同。
语法：insert into t_student select * from emp;

6.修改表中的数据
语法格式：
update 表名 set 字段名1 = 值1，字段名2=值2…where 条件；
注意：如果没有设置条件的话，整个字段都更新。
update t_student set num =4 where birthday =‘1999-09-10’;
可以在set后面同时更改多个字段值。

*如果要更新字段所有记录，不用加条件即可。

7.删除数据：
语法格式：
delete from 表名 where 条件；

注意：没有条件全部删除数据。

删除学号为7的记录：
delete from t_student where num =7;

删除所有记录:
delete from t_student
*delete效率差，不会释放空间，可以撤销。

怎么删除大表？（重点）
truncate table t_student;//表被截断，不可撤销，永久丢失。

8.修改表结构，用的少，在开发中表结构的修改很少，遇到建议直接用Navicat工具，其不会出现在java代码中。

CRUD操作：即增删改查，（insert,delete ,update ,select)会出现在java代码中。

Cteate（增）
Retrieve（检索）
Update（修改）
Delete(删除）

7.创建表时加入约束（Constraint)

什么是约束？
在创建表的时候，可以给表的字段添加相应的约束，目的是为了保证表中数据的合法性，有效性，完整性。
如：
id username(唯一性约束）password(非空约束）

常见的约束有哪些呢？
非空约束：（not null)约束的字段不能为NULL。
唯一约束：(unique）约束的字段不能重复。
主键约束：（primary key)约束的字段既不能为NULL,也不能重复。
外键约束：（foreign key)简称FK
检查约束：（check)
注意：Oracle数据库有check约束，但是mysql没有。

1.非空约束：
1.drop table if exists t_user；

2.create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);

3.insert into t_user(id,password) values(1,‘123’);

报错：ERROR 1364 (HY000): Field ‘username’ doesn’t have a default value
即用户名不能为空。

2.唯一性约束：
修饰的字段具有唯一性，不能重复，但可以为NULL,因为NULL不可以比较。
1.drop table if exists t_user；

2.create table t_user1(
id int,
username varchar(255) unique,
password varchar(255)
);【列级约束】

3.insert into t_user1(id,username,password) values(1,‘MengYangChen’,‘123’);

insert into t_user1(id,username,password) values(1,‘MengYangChen’,‘1r23’);
报错：ERROR 1062 (23000): Duplicate entry ‘MengYangChen’ for key ‘username’；

给多个列添加unique
create table t_user1(
id int,
username varchar(255) ,
password varchar(255) ,
unique(username,password)
);
表示多个列联合起来不一样，也就是说这种方式可以有一列或多列相同，但不能全相同。【表级约束】

3.主键约束
怎么给一张表添加主键约束？
在字段后面加
1.drop table if exists t_user；

2.create table t_user2(
id int primary key ,
username varchar(255) ,
password varchar(255)
);【列级约束】
根据以上的测试得出:id是主键，因为添加了主键约束，主键字段中的数据不能为NULL,也不能重复。

主键的特点：不能为NULL,也不能重复。
逐渐相关的术语：
主键约束：primary key
主键字段：id
主键值：插入的值
4.主键有什么用？
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
主键值不一样就认为两者是不同的记录，是记录的唯一标识。

一张表的主键约束只能有一个,可以复合主键。

主键的分类：
根据主键字段的字段数量来划分：
单一主键（常用）
复合主键（多个字段联合起来添加一个主键约束，不建议使用，因为违背了三范式）

根据主键性质来划分：
自然主键：主键值最好就是一个个和业务没有任何关系的自然数。
业务主键：主键值和系统的业务挂钩。（如拿银行卡号做主键）
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

使用表级约束方式定义主键：
1.drop table if exists t_user2；

2.create table t_user2(
id int ,
username varchar(255) ,
password varchar(255) ，
primary key(id)
);【表级约束】

复合主键：在primary key()括号中添加字段即可。

5.mysql提供主键值自增：
1.drop table if exists t_user3；

2.create table t_user3(
id int primary key auto_increment,//子段自动维护一个自增的数字，从1开始，以1自增
username varchar(255) ,
password varchar(255) ，
);
优点：可以不用写主键，比如用户注册的时候。
Oracle的递增叫做序列对象。

6.外键约束：
关于外键约束的相关术语：
外键字段：添加有外键约束的字段
外键约束：foreign key
外键值：外键字段中的每一个值。

业务背景：设计学生数据库表用来维护学生和班级的信息？
第一种方案：一张表存储所有数据。（学生表）
缺点：数据冗余

第二种方案：两张表（班级表和学生表）
t_class 班级表
cno(pk) cname

---

A1511 北京xx大学A1511班
A1311 北京xx大学A1311班

t_student学生表
sno(pk) 主键 sname classno （该字段添加外键约束fk)

---

1 xing A1511
2 Meng A1311
将以上表的建表语句写出来：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
先创建父表，再创子，先删子，再删父。
先添父，再添子。
drop table if exists t_student;
drop table if exists t_class;

create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);

create table t_student（
sno int ,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);

insert into t_class values(101,‘xx’);
insert into t_class values(102’yyy’);

insert into t_student values(1,‘dff’,101);
insert into t_student values(2,‘dfaf’,102);

如果：insert into t_student values(3,‘daaf’,103）
这个会报错，因为子表外键约束了，只能插入父表的特定数据。

*sql脚本，其实就是sql语句。

外键值可以为NULL吗？
外键可以为NULL,外键字段引用另一个字段，被引用的子段不一定要为主键字段，但需要有唯一性，即至少有unique约束。

• 关注公众号【轻松玩编程】回复，计算机资源，可以领取学习资源哦！

码字不易，点个赞再走吧。

转载：https://blog.csdn.net/jiahuan_/article/details/105642948