1.查询
一些和查询相关的sql内置函数:
max():求一个字段中数据的最大值。
min():求一个字段中数据的最小值。
avg():求一个字段中数据的平均值。
sum():求一个字段中所有数据的和。
count(*):统计当前表中数据有多少个。
#表中薪资的最大值
mysql> select * from person;
+------+-----------+--------+------+--------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+--------------+--------+
| 1 | 小苏苏 | 1 | 29 | 可爱得很 | 8.99 |
| 3 | heidan | 1 | 12 | 是真的狗 | 98.98 |
| 4 | 二贝 | 0 | 78 | 神经吧 | 23.40 |
| 5 | zihao | NULL | NULL | NULL | 700.00 |
| 6 | ruiqiang | NULL | NULL | NULL | 300.00 |
+------+-----------+--------+------+--------------+--------+
5 rows in set (0.04 sec)#求一个字段的最大值的
mysql> select max(salary) from person;
+-------------+
| max(salary) |
+-------------+
| 700.00 |
+-------------+
1 row in set (0.03 sec)mysql> select max(salary) as "最大值" from person;
+-----------+
| 最大值 |
+-----------+
| 700.00 |
+-----------+
#求一个字段所有的数据平均值 null 是不计入的
mysql> select avg(age) from person;
+----------+
| avg(age) |
+----------+
| 39.6667 |
+----------+
1 row in set (0.00 sec)#求一个字段的所有的数据的总和
mysql> select sum(salary) from person;
+-------------+
| sum(salary) |
+-------------+
| 1131.37 |
+-------------+
1 row in set (0.00 sec)#统计一个表中有多少条数据
mysql> select count(*) from person;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
分组统计:group by
group by语句用于结合合计函数,根据一个或多个列对结果集进行分组。
语法格式:
select * from 字段 group by 字段;
mysql> select * from person;
+------+-----------+--------+------+-------------------------------------------------+--------+
| id | name | gender | age | info | salary |
+------+-----------+--------+------+-------------------------------------------------+--------+
| 1 | 小苏苏 | 1 | 29 | 可爱得很 | 8.99 |
| 3 | heidan | 1 | 12 | 是真的狗 | 98.98 |
| 4 | 二贝 | 0 | 78 | 神经吧 | 23.40 |
| 5 | zihao | 1 | 78 | dasjhjksj nn sahjnjdsnj计算机视觉你就能 | 700.00 |
| 6 | ruiqiang | 0 | 34 | qweq | 300.00 |
+------+-----------+--------+------+-------------------------------------------------+--------+
#分组
mysql> select gender from person group by gender;
+--------+
| gender |
+--------+
| 0 |
| 1 |
+--------+
#分组以后的统计问题
mysql> select gender,count(*) from person group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 0 | 2 |
| 1 | 3 |
+--------+----------+
#按照性别分组,然后统计,然后要性别类人数 大于2的
#你们想法:是使用where
select gender,count(*) from person where count(*) > 2 group by gender;
#上面sql是错的, 因为此时 count(*) 呢
#一般使用group by的时候 需要对分组之后的数据进行筛选 使用having 不能where
#正确的写法
mysql> select gender, count(*) from person group by gender having count(*) > 2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 1 | 3 |
+--------+----------+
#找出年龄大于20岁的,然后分组统计 ,统计之后大于2 的性别类别
mysql> select gender,count(*) from person where age > 20 group by gender having count(*) >2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 1 | 3 |
+--------+----------+
2.数据约束
在创建表的时候,一定要对字段进行约束,这样在插入数据的时候才能更加合理。数据约束在创建字段的时候就开始添加了。
2.1默认值(default)
#default
#在插入数据的时候,如果没有给当前字段设置一个值的时候,会当前字段赋值一个默认值。
mysql> create table person1 (
-> id int,
-> name varchar(32),
-> country varchar(32) default "PRC" #在一个数据类型的后面 跟一个默认值
-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc person1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| country | varchar(32) | YES | | PRC | |
+---------+-------------+------+-----+---------+-------+
2.2非空(not null)
not null 如果给一个字段加上非空,在添加数据时就必须给当前字段赋值,如果不赋值就会报错。
mysql> create table person2 (
-> id int not null,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)mysql> insert into person2 (name) values("狗蛋");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into person2 (id, name) values(1, "狗蛋");
Query OK, 1 row affected (0.00 sec)
2.3唯一(unique)
unique设置字段的唯一性,添加数据的时候,如果添加数据重复的值就会报错。
mysql> create table person3 (
-> id int not null,
-> name varchar(32) unique not null
-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into person3 (id, name) values(1, "毛球");
Query OK, 1 row affected (0.00 sec)mysql> insert into person3 (id, name) values(2, "毛球");
ERROR 1062 (23000): Duplicate entry '毛球' for key 'name'
2.4主键(primary key)
主键是唯一和非空的组合;
主键是唯一的并且是非空的,每张表中都得有且只有一个主键字段;
主键字段必须和业务逻辑无关。
mysql> create table person4(
-> id int primary key ,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into person4 (id,name) values(1, "嘻嘻");
Query OK, 1 row affected (0.00 sec)mysql> insert into person4 (id,name) values(1, "哈哈");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into person4(name) values("xixi");
ERROR 1364 (HY000): Field 'id' doesn't have a default value
2.5自增长(auto_increment)
在每次插入新纪录时,自动的创建主键字段的值。
mysql> create table person5 (
-> id int primary key auto_increment,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)mysql> insert into person5 (name) values("panglong");
Query OK, 1 row affected (0.01 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
+----+----------+
1 row in set (0.00 sec)mysql> insert into person5 (name) values("xiaoxiao");
Query OK, 1 row affected (0.00 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
+----+----------+
2 rows in set (0.00 sec)mysql> insert into person5 (name) values("xixi");
Query OK, 1 row affected (0.00 sec)mysql> insert into person5 (name) values("hehe");
Query OK, 1 row affected (0.01 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
| 4 | hehe |
+----+----------+
4 rows in set (0.00 sec)java
#当删除最后一条数据的时候,再次插入数据的时候什么效果!!!
mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
| 4 | hehe |
+----+----------+
4 rows in set (0.00 sec)mysql> delete from person5 where id = 4;
Query OK, 1 row affected (0.01 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
+----+----------+
3 rows in set (0.00 sec)mysql> insert into person5(name) values("haha");
Query OK, 1 row affected (0.00 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
| 5 | haha |
+----+----------+
4 rows in set (0.00 sec)mysql> update person5 set id = 4 where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
| 4 | haha |
+----+----------+
4 rows in set (0.00 sec)mysql> insert into person5 (name) values ("jiejie");
Query OK, 1 row affected (0.00 sec)mysql> select * from person5;
+----+----------+
| id | name |
+----+----------+
| 1 | panglong |
| 2 | xiaoxiao |
| 3 | xixi |
| 4 | haha |
| 6 | jiejie |
+----+----------+
5 rows in set (0.00 sec)
扩展知识点:当表中的数据有时间的话表字段如何处置
类型 | 格式 | 用途 | 默认值 |
date(常用) | YYYY-MM-DD | 2023-04-14 | |
time | HH:MM:SS | 21:05:10 | |
year | YYYY | 2023 | |
datetime | YYYY-MM-DD hh:mm:ss | ||
timestamp(常用) | YYYY-MM-DD hh:mm:ss | 2023-04-14 21:07:34 | current_times |
mysql> create table person7 (
-> id int primary key auto_increment,
-> name varchar(32),
-> createTime timestamp default current_timestamp
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into person7 (name) values("laoxing");
Query OK, 1 row affected (0.00 sec)mysql> insert into person7 (name) values("goudan");
Query OK, 1 row affected (0.00 sec)mysql> insert into person7 (name) values("saolei");
Query OK, 1 row affected (0.00 sec)mysql> select * from person7;
+----+---------+---------------------+
| id | name | createTime |
+----+---------+---------------------+
| 1 | laoxing | 2023-04-08 16:53:08 |
| 2 | goudan | 2023-04-08 16:53:12 |
| 3 | saolei | 2023-04-08 16:53:17 |
+----+---------+---------------------+
3.外键的约束
mysql> create table emp (
-> emp_id int primary key auto_increment,
-> emp_name varchar(32) ,
-> dept_id int,
-> reg_time timestamp default current_timestamp,
#constraint 约束的意思
#fk_emp_dept 外键的名字 随意起
#foreign key(dept_id) 外键 使用本表中的一个字段作为外键去和别的表产生关系
#references 关联 引用的意思,去关联主表里面的某一个字段(外键所在的表叫副表)
-> constraint fk_emp_dept foreign key(dept_id) references dept(dept_id)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 教学部 |
| 2 | 品保部 |
+---------+-----------+
#插入数据
mysql> insert into emp (emp_name, dept_id) values("lx", 1);
Query OK, 1 row affected (0.01 sec)mysql> insert into emp (emp_name, dept_id) values("sl", 1);
Query OK, 1 row affected (0.00 sec)
#插入了一个不存在的部门 的时候结果报错课
mysql> insert into emp (emp_name, dept_id) values("gd", 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))#删除数据 删除教学部
mysql> delete from dept where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))#修改数据
mysql> update dept set dept_id = 5 where dept_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java2304`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`))
mysql>#加上外键约束以后,增删改 有的时候会报错
#主表(部门表) 副表(员工表)
#增加的时候: 先看主表 然后增加副表
#删除的时候: 先删除副表 再删除主表
#修改的时候: 先修改副表 再修改主表
#这样才能不报错
#太麻烦了,还得记这些
#级联操作!!! 级联修改 和级联删除
mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)mysql> create table emp (
-> emp_id int primary key auto_increment,
-> emp_name varchar(32) ,
-> dept_id int,
-> reg_time timestamp default current_timestamp,
-> constraint fk_emp_dept foreign key(dept_id) references dept(dept_id)
#加的两个状态
-> on delete cascade
-> on update cascade
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> delete from dept where dept_id =1;
Query OK, 1 row affected (0.01 sec)mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 2 | 2023-04-10 10:46:04 |
| 4 | erbei | 2 | 2023-04-10 10:46:08 |
| 5 | erjia | 2 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+
#修改:
mysql> update dept set dept_id = 4 where dept_id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 |
+--------+----------+---------+---------------------+真实开发的时候,特别是外键约束的表的时候,要加上级联删除和级联修改
mysql> create table emp (
-> emp_id int primary key auto_increment,
-> emp_name varchar(32) ,
-> dept_id int,
-> reg_time timestamp default current_timestamp,
-> constraint fk_emp_dept foreign key(dept_id) references dept(dept_id)
#加的两个状态
-> on delete cascade
-> on update cascade
-> );
4.联表查询(重点)
4.1常规的联表查询
#增删改和外键约束有关 查询没有关系的
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 4 | 品保部 |
| 5 | 教学部 |
| 6 | 技术部 |
+---------+-----------+
3 rows in set (0.00 sec)mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 |
+--------+----------+---------+---------------------+
#联表查询 查看 erbei 属于哪个部门
select emp.emp_name, dept.dept_name # 查什么
from emp, dept #从哪查
where emp.dept_id = dept.dept_id and emp.emp_name = "erbei" #查询的条件
mysql> select *
-> from emp, dept
-> where emp.dept_id = dept.dept_id;
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 | 4 | 品保部 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 | 4 | 品保部 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 | 4 | 品保部 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 | 5 | 教学部 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 | 5 | 教学部 |
+--------+----------+---------+---------------------+---------+-----------+
5 rows in set (0.01 sec)mysql> select *
-> from emp, dept
-> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei";
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
| 4 | erbei | 4 | 2023-04-10 10:46:08 | 4 | 品保部 |
+--------+----------+---------+---------------------+---------+-----------+
1 row in set (0.00 sec)mysql> select emp.emp_name, dept.dept_name
-> from emp, dept
-> where emp.dept_id = dept.dept_id and emp.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei | 品保部 |
+----------+-----------+
1 row in set (0.00 sec)
#对表起表名
select e.emp_name, d.dept_name
from emp e, dept d
where e.dept_id = d.dept_id and e.emp_name = "erbei";
mysql> select e.emp_name as "员工名字", d.dept_name as "部门名字"
-> from emp e, dept d
-> where e.dept_id = d.dept_id and e.emp_name = "erbei";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erbei | 品保部 |
+----------+-----------+
1 row in set (0.00 sec)#找出教学部有有哪些人
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept_id = e.dept_id and d.dept_name= "教学部";
mysql> select d.dept_name, e.emp_name
-> from dept d, emp e
-> where d.dept_id = e.dept_id and d.dept_name= "教学部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 教学部 | 东哥 |
| 教学部 | 飞哥 |
+-----------+----------+
2 rows in set (0.00 sec)
4.2内连接和外连接
内连接和外连接也牵涉多表联查,只是换了一种写法。
4.2.1内连接
select 列名 ->查什么
from 表名1 ->从哪查 表1
inner join 表名2 ->关联 表2
on 表名1.列名=表名2.列名
注意:inner join与join是相同的
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 4 | 品保部 |
| 5 | 教学部 |
| 6 | 技术部 |
+---------+-----------+
3 rows in set (0.00 sec)mysql> select * from emp;
+--------+----------+---------+---------------------+
| emp_id | emp_name | dept_id | reg_time |
+--------+----------+---------+---------------------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 |
+--------+----------+---------+---------------------+select e.emp_name, d.dept_name
from emp e
inner join dept d
on e.dept_id = d.dept_id
where e.emp_name = "erjia";
mysql> select *
-> from emp
-> inner join dept
-> on emp.dept_id = dept.dept_id
-> where emp.emp_name = "erjia";
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
| 5 | erjia | 4 | 2023-04-10 10:46:11 | 4 | 品保部 |
+--------+----------+---------+---------------------+---------+-----------+
1 row in set (0.01 sec)mysql> select emp.emp_name, dept.dept_name
-> from emp
-> inner join dept
-> on emp.dept_id = dept.dept_id
-> where emp.emp_name = "erjia";
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erjia | 品保部 |
+----------+-----------+
1 row in set (0.00 sec)mysql> select e.emp_name, d.dept_name #查什么
-> from emp e #从第一个表
-> inner join dept d #关联第二表
-> on e.dept_id = d.dept_id #表之间的连接的字段
-> where e.emp_name = "erjia"; #条件
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| erjia | 品保部 |
+----------+-----------+
1 row in set (0.00 sec)
#查 品保部的人
mysql> select d.dept_name, e.emp_name
-> from dept d
-> join emp e
-> on d.dept_id = e.dept_id
-> where d.dept_name= "品保部";
+-----------+----------+
| dept_name | emp_name |
+-----------+----------+
| 品保部 | xueke |
| 品保部 | erbei |
| 品保部 | erjia |
+-----------+----------+
4.2.2外连接(开发中用的很少)
#左外连接 和右外连接
#LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
mysql> select *
-> from dept d
-> left outer join emp e
-> on d.dept_id = e.dept_id;
+---------+-----------+--------+----------+---------+---------------------+
| dept_id | dept_name | emp_id | emp_name | dept_id | reg_time |
+---------+-----------+--------+----------+---------+---------------------+
| 4 | 品保部 | 3 | xueke | 4 | 2023-04-10 10:46:04 |
| 4 | 品保部 | 4 | erbei | 4 | 2023-04-10 10:46:08 |
| 4 | 品保部 | 5 | erjia | 4 | 2023-04-10 10:46:11 |
| 5 | 教学部 | 6 | 东哥 | 5 | 2023-04-10 11:00:59 |
| 5 | 教学部 | 7 | 飞哥 | 5 | 2023-04-10 11:01:11 |
| 6 | 技术部 | NULL | NULL | NULL | NULL |
| 7 | 总经办 | NULL | NULL | NULL | NULL |
+---------+-----------+--------+----------+---------+---------------------+
#RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。mysql> select *
-> from emp e
-> right outer join dept d
-> on e.dept_id = d.dept_id;
+--------+----------+---------+---------------------+---------+-----------+
| emp_id | emp_name | dept_id | reg_time | dept_id | dept_name |
+--------+----------+---------+---------------------+---------+-----------+
| 3 | xueke | 4 | 2023-04-10 10:46:04 | 4 | 品保部 |
| 4 | erbei | 4 | 2023-04-10 10:46:08 | 4 | 品保部 |
| 5 | erjia | 4 | 2023-04-10 10:46:11 | 4 | 品保部 |
| 6 | 东哥 | 5 | 2023-04-10 11:00:59 | 5 | 教学部 |
| 7 | 飞哥 | 5 | 2023-04-10 11:01:11 | 5 | 教学部 |
| NULL | NULL | NULL | NULL | 6 | 技术部 |
| NULL | NULL | NULL | NULL | 7 | 总经办 |
+--------+----------+---------+---------------------+---------+-----------+
7 rows in set (0.00 sec)
5.开发中如何使用多表联查
5.1一对多或多对一的查询
mysql> create table teacher (
-> t_id int primary key auto_increment,
-> t_name varchar(32)
-> );
Query OK, 0 rows affected (0.02 sec)mysql> create table student(
-> s_id int primary key auto_increment,
-> s_name varchar(32),
-> teacher_id int
-> );
Query OK, 0 rows affected (0.01 sec)
#老邢带学生有哪些?
select teacher.t_name, student.s_name
from teacher, student
where teacher.t_id = student.teacher_id and teacher.t_name = "老邢";
mysql> select teacher.t_name, student.s_name
-> from teacher, student
-> where teacher.t_id = student.teacher_id and teacher.t_name = "老邢";
+--------+--------+
| t_name | s_name |
+--------+--------+
| 老邢 | 张三 |
| 老邢 | 李四 |
| 老邢 | 王五 |
| 老邢 | 老八 |
+--------+--------+
4 rows in set (0.00 sec)mysql> select t.t_name,s.s_name
-> from teacher t
-> inner join student s
-> on t.t_id = s.teacher_id
-> where t.t_name = "老邢";
+--------+--------+
| t_name | s_name |
+--------+--------+
| 老邢 | 张三 |
| 老邢 | 李四 |
| 老邢 | 王五 |
| 老邢 | 老八 |
+--------+--------+
4 rows in set (0.00 sec)
5.2多对多的场景
场景:学生和课程的关系:学生要选课,课要被学生选
一个学生对应着多门的课程
一个课程可以被多个学生选择
mysql> create table stu (
-> s_id int primary key auto_increment,
-> s_name varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)mysql> create table course (
-> c_id int primary key auto_increment,
-> c_name varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)mysql> create table stu_course (
-> id int primary key auto_increment,
-> s_id int,
-> c_id int
-> );
Query OK, 0 rows affected (0.08 sec)
#有三张表就意味着着必须使用三表联查
#需求: 司马懿选了哪些课程
select stu.s_name, course.c_name
from stu, stu_course, course
where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
mysql> select *
-> from stu, stu_course, course
-> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
+------+-----------+----+------+------+------+--------+
| s_id | s_name | id | s_id | c_id | c_id | c_name |
+------+-----------+----+------+------+------+--------+
| 1 | 司马懿 | 1 | 1 | 1 | 1 | 物理 |
| 1 | 司马懿 | 2 | 1 | 3 | 3 | Java |
| 1 | 司马懿 | 3 | 1 | 4 | 4 | python |
+------+-----------+----+------+------+------+--------+
3 rows in set (0.01 sec)mysql> select stu.s_name, course.c_name
-> from stu, stu_course, course
-> where stu.s_id = stu_course.s_id and stu_course.c_id = course.c_id and stu.s_name = "司马懿";
+-----------+--------+
| s_name | c_name |
+-----------+--------+
| 司马懿 | 物理 |
| 司马懿 | Java |
| 司马懿 | python |
+-----------+--------+
3 rows in set (0.01 sec)#内连接的写法
select s.s_name, c.c_name
from stu s
inner join stu_course sc
on s.s_id = sc.s_id
inner join course c
on sc.c_id = c.c_id
where s.s_name = "司马懿";
mysql> select s.s_name, c.c_name
-> from stu s
-> inner join stu_course sc
-> on s.s_id = sc.s_id
-> inner join course c
-> on sc.c_id = c.c_id
-> where s.s_name = "司马懿";
+-----------+--------+
| s_name | c_name |
+-----------+--------+
| 司马懿 | 物理 |
| 司马懿 | Java |
| 司马懿 | python |
+-----------+--------+
#挖掘机被哪些人选了select c.c_name, s.s_name
from course c
inner join stu_course sc
on c.c_id = sc.c_id
inner join stu s
on sc.s_id = s.s_id
where c.c_name = "挖掘机";
mysql> select c.c_name, s.s_name
-> from course c
-> inner join stu_course sc
-> on c.c_id = sc.c_id
-> inner join stu s
-> on sc.s_id = s.s_id
-> where c.c_name = "挖掘机";
+-----------+-----------+
| c_name | s_name |
+-----------+-----------+
| 挖掘机 | 孙悟空 |
| 挖掘机 | 林黛玉 |
+-----------+-----------+