MySQL(3)：数据的增删改查(多种查询)+数据库映射关系

一、MySQL对表中的数据进行操作

上篇文章我们创建好了表，那么就要对数据进行操作了，新表创建完数据是空的，那么首先我们就要对数据进行添加

1.添加数据

向学生表插入新数据

方法1：（字段名和值名顺序一一对应）

insert into student(id,name,age,gender,email,class) values(1,'小李',19,'男','123@qq.com','java');

insert into student(id,name,age,gender,email,class) values(2,'李白',22,'男','555@qq.com','python');
1
2
3

方法2： （不指定字段，但是需要把所有值和创表的字段名顺序一一对应）

 insert into student values(3,'庄周',33,'1','121@','python');
1

方法3：一次插入多条数据，可以使用前两张的任意一种方法

insert into student  values(3,'ada',12,'1','121@','2'),(4,'ada',12,'1','121@','2');
1

2.查询数据

查询数据知识比较多，这里只展示一下全部查询

 select * from student;
1

3.修改数据

原数据

修改数据语法： update 表名 set 字段=值 where 条件;

改一条数据：

 update student set age=100 where name='小李';  # 叫小李的年龄设置为100岁
1

改多个信息：

update student set gender='女' where gender='男'; # 男生性别都改为女生
1

4.删除数据

删除数据语法 delete from 表名 where 条件;**

delete from student where id=2;
1

5.修改自增值

如果数据超过自增值，那么只能往大改，不可以往小改

alter table students auto_increment=20;
1

二、数据查询

查询的基本格式

select 要查的内容 from 查找的对象

[where 查找的条件]
[group by 按什么分组]
[having 使用到函数的条件]
[order by 按照什么条件排序]
1
2
3
4
5
6

1.条件查询——聚合函数

max最大值:select max（age） from 表名;
min最小值:select min（age） from 表名;
avg平均值:select avg（age） from 表名;
count计数:select age,count(age) from 表名 group by age;
1
2
3
4

2.条件查询——group by分组查询

1.group by分组

1.查询表中有哪些班级
select class from student group by class;

2.统计当前表中男生和女生的人数各多少人
select gender,count(*) from student group by gender;

3.统计每个班级的人数
select class,count(*) from student group by class;

4.统计每个班，男生和女生个多少人
select class,gender,count(*) from student group by class,gender;
1
2
3
4
5
6
7
8
9
10
11

count(*)是求数量

2.Having 子句查询

只能在分组后面去使用 ，类似于where 但是having筛选的是分组后的数据

1.统计每个班级的人数，并查询出班级人数大于等于2的班级
select class,count(*) from student group by class having count(*)>=2;

2.查询每个班级男生人数大于等于2的班级
select class,gender,count(*) from student group by class,gender having gender='1' and count(*)>=2;

先查询出所有的男生 在按照班级分组 然后在用having过滤
select class,count(*) from student where gender='1' group by class having count(*)>=2;
1
2
3
4
5
6
7
8

3.条件查询——逻辑运算符和算数运算符

1.逻辑运算符

①and:select * from 表名 where 条件一 and 条件二;
②of :select * from 表名 where 条件一 and 条件二;
③in :select * from 表名 where 字段 in （选项1，选项2，选项3..）;
④not:select * from 表名 where 字段 not in （选项1，选项2，选项3..）;

1
2
3
4
5

2.算数运算符

=  :select * from 表名 where 字段=某值;
<  :select * from 表名 where 字段<某值;
<=  :select * from 表名 where 字段<=某值;
>  :select * from 表名 where 字段>某值;
>=  :select * from 表名 where 字段>=某值;
1
2
3
4
5

4.条件查询——排序查询 order by

order by 排序（desc降序，asc升序）,默认升序

语法格式：select * from 表名 order by 字段名 desc/asc;

举例：

查询所有学生信息 并按照年龄进行降序排序
select * from students order by age desc;

查询班级为3年1班的男生信息 并按照年龄进行降序排序
select * from students where class='3年1班' and gender='男' order by age desc; 
1
2
3
4
5

5.条件查询——分页查询 limit

格式：

limit n ; 从前面获取前n条数据
limit m,n; 跳过第m条数据取n条数据
1
2

举例：

1.获取前三个学生的数据

    select * from students limit 3;

2.获取第5个学生的信息

    select * from students limit 4,1;

3.获取3年1班年龄最大的2个学生
	 select * from students where class='3年1班' order by age desc limit 2;
1
2
3
4
5
6
7
8
9
10

6.条件查询——嵌套查询

将一条查询一句 嵌入到另一条查询语句当中去

使用嵌套查询举例：

# 查询年龄大于18岁的男生
select * from (select * from students where age>18) as newtable  where newtable.gender='1';
1
2

7.条件查询——外键查询

外键有两种形式：

• 逻辑外键： 又叫事物外键，不使用foreignkey，使用语法（代码）上产生逻辑关联而产生的外键
• 物理外键：某张表的字段使用foreignkey作为外键关联另外一张表、字段。并且限定引擎为inno DB； foreign key(当前表的外键字段) references 关联的表名(关联表的字段)

物理外键举例：

1.已有一张表为Persons表，表结构为
P_Id LastName  FirstName Address City;

2 创建一个带有外键的Order表
CREATE TABLE Orders
(
	O_Id int NOT NULL,
	OrderNo int NOT NULL,
	P_Id int,
	PRIMARY KEY (O_Id),
	FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
1
2
3
4
5
6
7
8
9
10
11
12

此时使用navicat查看时，会更明显看到有个foreignkey的标识，
查询起来可以更方便的联表查询，通过外键可以直接调用另一张表的查询。

查询举例

select * from Persons p,Orders o where p.P_Id=o.P_Id;
1

逻辑外键举例

1 已有一张表为Persons表，表结构为
P_Id LastName  FirstName Address City;

2 创建一个带有外键的Order表
CREATE TABLE Orders
(
	O_Id int NOT NULL,
	OrderNo int NOT NULL,
	P_Id int
)
1
2
3
4
5
6
7
8
9
10

此时使用navicat查看时，不会看到foreignkey的标识，此时两张表从数据库的角度看是毫无关联的。
其中Orders表的P_Id 与 Persions表的 P_Id 是有关联关系，增删改查上都是有关系的，而这种关系是通过代码逻辑层面进行控制。所以叫逻辑外键。

8.条件查询——内连接查询和外连接查询

①内连接： inner join 内连接，结果和where关联查询结果一样

 select * from 表1 inner join 表2 on 关联条件
1

举例：

1.查看每个学生的宿舍信息
 select * from student s inner join dormitory d on s.d_id=d.id; 

2.查询小红住在哪个宿舍
select s.id,s.name,d.num from student s inner join dormitory d on s.d_id=d.id and s.name='小红';
1
2
3
4
5

②外左连接：left join,以左表为准，将左表中所有的数据查询出来，去右表匹配信息，如果没有对应的信息以null占位

举例使用：

1.查看每个学生的宿舍信息
select * from student s left join dormitory d on s.d_id=d.id;

2.哪些学员没有宿舍
select * from student s left join dormitory d on s.d_id=d.id where d.num is null;

3.哪些宿舍是空宿舍
select * from dormitory d left join student s on s.d_id=d.id where s.id is null;
1
2
3
4
5
6
7
8

③外右链接：right join ，以右表为准，将右表中所有的数据查询出来，去左表匹配信息

举例：

查看每个学生的宿舍信息
select * from dormitory d right join student s on d.id=s.d_id;
1
2

注意——内连接，左连接，右连接的区别：

举例：

1.left join查询

select * from A left join B on A.aID = B.bID
1

left join是以左边的A表的记录为基础的,也就是说,左表(A)的记录会全部展示出来,而右表(B)只会展示符合搜索条件的记录。B表记录不足的地方均为NULL.

2.right join查询

select * from A right join B  on A.aID = B.bID
1

right join是以右边边的B表的记录为基础的,也就是说,右表(B)的记录会全部展示出来,而左表(A)只会展示符合搜索条件的记录。和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join查询

select * from A innerjoin B  on A.aID = B.bID
1

这里只展示了A.aID = B.bID的记录.说明inner join并不以谁为基础,它只显示符合条件的记录.

三、数据库映射关系

数据库建模就是用数据库关系字段和表关系来描述业务逻辑的过程，创建数据表来描述业务的主体，创建表关系来描述业务主体间的关系，常用数据库映射有三种：一对一，一对多，多对多

1.一对一

一张表中唯一一条数据对应另一张表中唯一一条数据（主键约束）

举例：

我们创建两张表，一张存放用户名字，另一张存用户具体信息

create table user(
	id int unsigned not null auto_increment primary key,
		uname char(32)
	);

我们在第二张表存入用户在第一张表的id，记为u_id	
create table uinfo(
	id int unsigned not null auto_increment primary key, 
	phone varchar(11),
	age int,
	email varchar(32),
	gender varchar(10),
	address varchar(255) default '北京',
	u_id int
);

# 存放几条数据
insert into user values(null,'老张'),(null,'老牛'),(null,'老刘'),(null,'老钟');
insert into uinfo values(null,'110',18,'laozhang@qq.com','1','北京昌平',1),
				(null,'120',28,'laoniu@qq.com','1','北京朝阳',2),
				(null,'119',38,'laoliu@qq.com','1','黑龙江',3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查询

根据用户名老刘 查询老张的详细信息
select * from uinfo where u_id=(select id from user where uname='老刘');

已知手机号为120 查询手机号对应的用户
select * from user where id=(select u_id from uinfo where phone='120');
1
2
3
4
5

2.一对多

一张表中的一条数据 对应另一张表中的多条数据

举例：

# 创建两张表，一张存放孩子，一张存放父亲，父亲会有多个孩子
create table parent(
			id int not null auto_increment primary key,
			name varchar(10)
		);
create table child(
	id int not null auto_increment primary key,
	name varchar(10),
	p_id int
);

# 插数据
insert into parent values(null,'老张'),(numm,'老李');
insert into child values(null,'张一',1),(null,'张二',1),(null,'李1',2),(null,'李2',2);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询

根据父亲找儿子：查找老张所有的孩子
 select * from child where p_id in(select id from parent where name='老张');

据儿子找父亲:查询张一的父亲
 select * from parent where id in(select p_id from child where name='张1' );
1
2
3
4
5

3.多对多

一张表中的多条数据 对应另一张表中的多条数据

举例

# 创建三张表，一张存放学生信息，一张存放老师信息，一张存放学生和老师的id，并且是按照老师和学生对应的师生关系，并且添加若干数据
create table teacher(
id int unsigned not null auto_increment primary key, 
name varchar(10)
);

create table teacher_student(
id int unsigned not null auto_increment primary key, 
t_id int,
s_id int 
);

create table student(
	id int unsigned not null auto_increment primary key,
	name char(20),
	age int,
	gender char(2),
	email varchar(32),
	class varchar(32)
)charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

查询

1.查张老师带过的所有学生

 1.查张老师的id
select id from teacher where name='张老师';
 2.根据老师id查找关系表中学生id
 select s_id from teacher_student where t_id in(select id from teacher where name='张老师');
 3.根据关系表的学生id查找学生
 select * from students where id in(select s_id from teacher_student where t_id in(select id from teacher where name='张老师'));
1
2
3
4
5
6

2.查庄周的所有老师

1.查庄周的学生id
select id from students where name='庄周';
2.根据庄周id查询关系表中老的id
select t_id from teacher_student where s_id in(select id from student where name='庄周');
3.根据老师id查老师信息
select * from teacher where id in(select t_id from teacher_student where s_id in(select id from students where name='庄周'));
1
2
3
4
5
6