MySQL---数据库从入门走向大神系列(四)-子查询、表与表之间的关系

本篇博客讲解(子查询)非相关子查询/相关子查询，一对一，一对多，多对一，多对的的关系！

准备：

首先我们创建一列sex。再为部分行设置好值0(女)或者1(男)；

delete from stud where age=26;
删除年龄为26的行。

 update stud set sex='1' where saddress like '湖南%';

将地址中湖南开头的人的的sex修改为1。

update stud set sex='0' where age>=30 and sex is null;

将年龄大于等于30的，且性别为null的人的sex设置为0.

case-when then else end 语句.

现在要做这样一件事，查询显示的时候sex不显示0,1和null，我们让它0的就显示女，1的就显示男，null就显示未知。
这时我们需要用到:
case-when then else end 语句.

修改 * 当 * 时候修改成 * 否则修改成 * END
when then 可以写多句

select sno,sname,age,saddress,(case sex when '0' then '女' when '1' then '男' else '未知' end) as 性别 from stud;

有人可能会有疑问，不是定义了sex只能有一个字符嘛，为什么还能用‘未知’啊，因为这里只是显示的，并不是存储到数据库中的数据，只是相当于输出字符。

子查询：

子查询：嵌套在其它查询中的查询语句。（又称为内部查询）

主查询：包含其它子查询的查询称为主查询。（又称外部查询）

非相关子查询：

在主查询中，子查询只需要执行一次，子查询结果不再变化，供主查询使用，这种查询方式称为非相关子查询。
也可以这么理解：
非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

现在我们来对这个表做一个查询：
查询具有年龄相同的人在2人及以上的，他们的年龄分别是多少。

select age from stud group by age having count(age)>=2;

count(age)在这里只是作为供主查询使用的条件。

表与表之间的关系：

一对一：

需要两个表。当然做项目时为了省空间，通常只建一个表，如果要实现一对一的查询，可以建立两个视图。示例如下:
1)先建物理表，初始化数据、

create table person(
    id int,
    name varchar(10),
    sex char(1),
    wife int,
    husband int
);
insert into person values(1,'小花','0',1,3);
insert into person values(2,'玉芳','0',0,4);
insert into person values(3,'张三','1',1,0);
insert into person values(4,'李四','1',2,0);
insert into person values(5,'王五','1',0,0);

2)建立两个视图

create view women as select * from person where sex='0';
create view men as select * from person where sex='1';

3)查询夫妻信息
92年以前是这样查询的：

select women.name as 妻子,men.name as 丈夫 from women,men where women.husband=men.id and women.id=men.wife;

一对多/多对一：

数据库设计分析：
案例：一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。

方案一：(差的设计-用一张表存储–数据冗余太严重)

编号   姓名  性别   年龄   汽车编号   车型  排量  价格
P001  Jack   男     25     C001     BMW    12L   80w
P001  Jack   男     25     C002     Benz   12L   100w
P001  Jack   男     25     C003     Benz   12L   100w
P002  Tom    男     25     C004     BMW    12L   80w
P002  Tom    男     25     C005     Benz   12L   100w
P003  Rose   女     25     C006     Benz   12L   100w

方案二(好的设计)：
1）把一方单独建个表

编号   姓名  性别   年龄
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25

2）把多方也建个表(依赖一方,通过外键–补一个字段)
外键:位于依赖一方，它是被依赖一方是主键

汽车编号   车型  排量  价格    车主
 C001     BMW    12L   80w    P001
 C002     Benz   12L   100w   P001
 C003     Benz   12L   120w   P001
 C004     BMW    12L   80w    P002
 C005     Benz   12L   100w   P002
 C006     Benz   12L   100w   P003

3)代码实现：

create table person2(
    id varchar(32) primary key,
    sname varchar(30),
    sex char(1),
    age int
);
insert into person2 values('P1001','Jack','0',25);
insert into person2 values('P1002','Tom','1',22);
insert into person2 values('P1003','Rose','1',24);
insert into person2 values('P1004','张三','0',25);

create table car(
    id varchar(32) primary key,
    sname varchar(30),
    price numeric(10,2),
    /*numeric(a,b),a表示一共多少位数，b表示带有几位小数*/
    pid varchar(32),
    /*为字段pid定义一个外键约束(来自person2表的字段id)*/
    constraint car_fk foreign key(pid) references person2(id)
);

insert into car values('C001','BMW',80.5,'P1001');
insert into car values('C002','Benz',100,'P1001');
insert into car values('C003','BMW',120.05,'P1001');
insert into car values('C004','Benz',88.5,'P1002');
insert into car values('C005','QQ',8.5,'P1002');
insert into car values('C006','BIKE',0.5,'P1003');

查询每个人拥有的车辆：
92年之前的写法：

select person2.sname as 车主,car.sname as 车辆 from person2,car where car.pid=person2.id;

现在的内关联写法:

select person2.sname as 车主 ,car.sname as 车辆 from person2 join car on car.pid=person2.id;

一对多和多对一是一样的！反过来理解就是了。

多对多：

数据库设计分析
案例：一个人可以选择多门课程，一门课程又可以被多人选择。

方案一：（差的设计–数据冗余太严重）
1)学生表

编号   姓名  性别   年龄  电话 ...
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25

2)课程表

编号   名称   教材  学分...  学生
S001   Java   ...  ......   P001
S001   Java   ...  ......   P002
S001   Java   ...  ......   ...
S002   数据库  ... ......   P001
S002   数据库  ... ......   P002

pass。

方案二(好的设计:两个实体表+一个关系表)：
1)学生表(独立)—实体

编号   姓名  性别   年龄  电话 ...
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25

2)课程表(独立)—实体

编号   名称   教材  学分...
S001   Java   ...  ......
S002   数据库  ... ......
S003   XML   ... ......

3)选课表(专为体现多对多的关系而新增的表)–关系

课程编号  学生编号
S001      P001
S001      P002
...
S002      P002
S002      P003
...
S003      P001
...
--------------
外键      外键
 |         |
 |         |
  ---------
      |
   联合主键

4) 代码实现

create table stud(
    id varchar(32) primary key,
    sname varchar(30),
    age int
);
create table ject(
    id varchar(32) primary key,
    sname varchar(30)
);
create table sj(
    studid varchar(32),
    jectid varchar(32)
);
/*单独添加约束，必须先添加联合主键，再添加外键*/
/*创建联合主键*/
alter table sj add constraint pk_sj primary key(studid,jectid);
/*创建两个外键*/
alter table sj add constraint fk_stud foreign key(studid) references stud(id);
alter table sj add constraint fk_ject foreign key(jectid) references ject(id);

如果需要删除外键

alter table sj drop foreign key fk_stud;
alter table sj drop foreign key fk_ject;

初始化stud表数据

insert into stud values('P001','小花',25);
insert into stud values('P002','Jack',23);
insert into stud values('P003','Tom',24);
insert into stud values('P004','张三',24);
insert into stud values('P005','赵子龙',26);

初始化ject表：

insert into ject values('S001','Java');
insert into ject values('S002','JavaEE');
insert into ject values('S003','XML');
insert into ject values('S004','数据库');
insert into ject values('S005','JQuery');

初始化sj表：

insert into sj values('P001','S001');
insert into sj values('P001','S003');
insert into sj values('P002','S001');
insert into sj values('P002','S002');
insert into sj values('P002','S003');
insert into sj values('P003','S001');
insert into sj values('P004','S002');
insert into sj values('P004','S003');

关联（也称为连接）:
左关联(left join) 右关联(right join) 内关联(inner join)
mysql不支持: 全关联(full join) 外关联(outter join)

可以把关联理解成：几个表合成一个新的表，然后在新表中进行查询！

查询哪些人选了哪些课：

92年之前是这样查询的：(没有用关联)

select stud.sname,ject.sname from stud,sj,ject where stud.id=sj.studid and ject.id=sj.jectid;

用内关联来：

select stud.sname,ject.sname from
    stud inner join sj on stud.id=sj.studid
         inner join ject on ject.id=sj.jectid;

内关联可以这样来看，以中间的sj表为主表，来合另外2个表。

查询哪些人没有选课：

不用关联的写法:

select stud.sname from stud where stud.id not in(select studid from sj);

用左关联的写法:

select stud.sname from
    stud left join sj on stud.id=sj.studid
         left join ject on ject.id=sj.jectid
    where ject.sname is null;
/*下面这句也可以查询出*/
select stud.sname from
    stud left join sj on stud.id=sj.studid
         left join ject on ject.id=sj.jectid
    where ject.id is null;

左关联就是把左边的表作为主表，也就是说，stud必须是完整的，可以增加，但不能减少，再按照sj表的关系，来添加ject表的数据。

查询哪些课程没人选:

不用关联的写法:

select ject.sname from ject where ject.id not in(select jectid from sj);

用左关联的写法：

select ject.sname from
    ject left join sj on ject.id=sj.jectid
         left join stud on stud.id=sj.studid
    where stud.id is null;

最后的那里也可以用stud.sname is null来判断。
但要注意，需要原来的stud表中的那个属性定义了not null。
否则会出现bug。

用右关联写：

select ject.sname from
    stud right join sj on stud.id=sj.studid
         right join ject on ject.id=sj.jectid
    where stud.sname is null;

左关联和右关联实质上是差不多的。认真的看下上面的左关联和右关联就可以看出来了。

时间： 2024-10-24 18:08:42

MySQL---数据库从入门走向大神系列(四)-子查询、表与表之间的关系的相关文章

MySQL---数据库从入门走向大神系列(三)-修改数据库编码/DOS窗口编码

如何查看与修改数据库的编码,如何修改dos窗口的显示编码,都在本篇详细讲解. 查看当前数据库的编码: show variables where variable_name like 'character%'; 如果出现了中文乱码,我们只要看: character_set_client -客户端的编码 character_set_connection -连接的编码(传输时的编码) character_set_results - 最后的输出编码只要保证这三个编码是相同的,且编码集有中文,中文就不会

MySQL---数据库从入门走向大神系列(九)-用Java向数据库读写大文本/二进制文件数据

介绍MySQL的文本和图形数据类型: Text 类型: 数据类型:描述 ------------------------------------------------------ char(size):保存固定长度的字符串(可包含字母.数字以及特殊字符).在括号中指定字符串的长度.最多 255 个字符. varchar(size):保存可变长度的字符串(可包含字母.数字以及特殊字符).在括号中指定字符串的最大长度.最多 255 个字符. 注释:如果值的长度大于 255,则被转换为 text类型

MySQL---数据库从入门走向大神系列(七)-Java访问数据库配置及简单使用方法execute

从操作配置文件properties中读取连接字符串,通过该字符串进行数据连接,需要写三个文件其中,两个是java类,一个是后缀名为.properties的文件,该文件放在src工作目录下. 需要准备的包: https://github.com/chenhaoxiang/Java 后缀为.properties的文件此处为其取名为jdbc.properties,其中的代码如下: ##MySQL driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0

MySQL---数据库从入门走向大神系列(二)-用Java对MySQL进行增删改查

上节已经学会对MySQL进行简单的增删改查了,那么,我们如何实现用Java来对数据库操作增删改呢. 本节将用Java演示对MySQL进行增删改查. 简单的来说,分为4个步骤: 1.加载连接器(驱动) 通过Driver类 (最好用类反射来加载,更加灵活) 2.建立与数据库的连接 3.获取语句对象 4.对数据库进行操作(增删改查) 其实第一步现在可以不用写了,高版本的MySQL已经在内部帮我们写好了第一步,但是,为了兼容性更好(兼容低版本的MySQL)我们最好还是写上第一步. 我们先看一下原数据

MySQL---数据库从入门走向大神系列(八)-在java中执行MySQL的存储过程

http://blog.csdn.net/qq_26525215/article/details/52143733 在上面链接的博客中,写了如何用MySQL语句定义和执行存储过程 Java执行存储过程: 准备表stud: 列类型分别为: varchar,varchar,int 定义的存储过程分别为: p1:-无参 delimiter && create procedure p1() begin insert into stud values('P100','小李',43); select

MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔离(锁机制)

MySQL 事务处理简单介绍事务处理: MySQL 事务主要用于处理操作量大,复杂度高的数据. 比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务! 一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行.换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的.如果在事务的任何操作失败,则整个事务将失败. 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全

MySQL---数据库从入门走向大神系列(五)-存储过程

本篇博客讲解: 自动增长列.字段值唯一性约束.存储过程.区分大小写的查询! 自动增长列.字段值唯一性约束 create table aa( id int auto_increment primary key, sname varchar(32) unique ); insert into aa values(5,'abc'); 创建一个自动增长的id属性(最开始不设置就从0开始增长) (后面的id如果有值了,如果添加数据时没有设置id,MySQL就会用最大的id加1做为最新的id) 注意:aut

MySQL---数据库从入门走向大神系列(十三)-BasicDataSource创建DataSource(DBCP连接池配置)

DBCP(DataBase connection pool),数据库连接池.是 apache 上的一个 java 连接池项目,也是 tomcat 使用的连接池组件.单独使用dbcp需要2个包:commons-dbcp.jar,commons-pool.jar由于建立数据库连接是一个非常耗时耗资源的行为,所以通过连接池预先同数据库建立一些连接,放在内存中,应用程序需要建立数据库连接时直接到连接池中申请一个就行,用完后再放回去. 首先,下载必须的jar包 dbcp包,目前版本是2.1.1 : htt

MySQL---数据库从入门走向大神系列(十六)-JavaWeb分页技术实例演示1

分页,是一种将所有数据分段展示给用户的技术.用户每次看到的不是全部数据,而是其中的一部分,如果在其中没有找到自己想要的内容,用户可以通过指定页码或是点上/下一页的方式进行翻页. 本例演示静态分页,也就是先设置好每页显示10行,再根据总行数,来算出总页数,将所有页数的页号都显示出来. 相关算法(技术): 总行数(num): select count(1) from stud; 每页显示的行数(n): 固定值---已知的一个常量页数: pageSize= num/n +( (num%n==0)?