带你走进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)
);

 

 

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约束。

 

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

 

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

 

在这里插入图片描述

 

posted @ 2020-04-25 13:49  轻松玩编程  阅读(256)  评论(0编辑  收藏  举报