一、表格设计

多对多

（1）需要创建第三张表，并且公共字段可以放入到第三张表中

create table teacher11(

id int PRIMARY key auto_increment,

name VARCHAR(20) not null,

sub VARCHAR(20) not null

);

create table student11(

id int PRIMARY KEY auto_increment,

name VARCHAR(20) not null,

sex VARCHAR(5) not null

);

create table tea_std11(

id int PRIMARY KEY auto_increment,

t_id int,

s_id int,

score int,

CONSTRAINT teacher_fk FOREIGN KEY (t_id) REFERENCES teacher11(id),

CONSTRAINT student_fk FOREIGN KEY (s_id) REFERENCES student11(id)

);

查询id为1的老师教过的所有学生

步骤一 ，从第三张表中查出老师教过的学生

select * from tea_std11 where t_id = 1;

步骤二 根据上面id取查询学生的信息,(1,2)代表学生id

select * from student11 where id in (1,2);

（2）设置外键对应其它表中的主键

一对一

一般情况一张表即可

也可以设计成两张表格

第二张表格中的创建字段作为外键（设置成唯一 unique）对应第一张表格中的主键

直接使用第二张表格的主键作为外键，对应第一张表格中的主键

create table users(

id int primary key auto_increment,

name varchar(20),

age int

);

create table card(

id int PRIMARY key,

num VARCHAR(20) not null,

address VARCHAR(100) not null,

CONSTRAINT user_card_fk FOREIGN KEY(id) REFERENCES users(id)

)

一对多

示例如下：

创建客户表:

CREATE TABLE customers(

       id int,

       name varchar(100),

       address varchar(255),

       PRIMARY KEY(id)

);

创建订单表:

CREATE TABLE orders(

       order_num int primary key,

       price float(8,2),

       status int,

       customer_id int,

       注：constraint: 约束的意思。foreign key: 外键。references: 参照

CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)

);

二、多表查询

1.交叉连接 笛卡尔积（查询结果是错误的）

2.内连接

（1）语法：

隐式：select * from 表1，表2 where 条件；

例：select * from users,card;

显示：select * from 表1 inner join 表2 ON 条件；

例：select * from users CROSS JOIN card;

查询出每个用户及身份证信息，显式出来

select * from users,card where users.id =card.id;

使用别名

select * from users as u,card as c where u.id =c.id;

u.*表示users表格中的所有字段，c.num表示c中的字段num

select u.*,c.num,c.address from users as u,card as c where u.id =c.id;

3.外连接

（1）左外链接

语法：select * from 表1 LEFT JOIN 表2 ON 条件

连接规则：没有拼接上就显示为空

select * from card as c LEFT JOIN student as s ON c.id = s.id;

（2）右外连接

语法：select * from 表1 RIGHT JOIN 表2 ON 条件

连接规则：没有拼接上就显示为空

select * from card as c RIGHT JOIN student as s ON c.id = s.id;

4.子查询

嵌套子查询

执行流程：先执行内部，后执行外部查询

特点：内部查询可以单独运行

使用嵌套子查询

select * from student11 where id in (select s_id from tea_std11 where t_id=1);

相关子查询

执行流程：先执行外部查询，将记录传递给内部

特点：内部查询需要外部查询的字段

create table teacher11(

id int PRIMARY key auto_increment,

name VARCHAR(20) not null,

sub VARCHAR(20) not null

);

create table student11(

id int PRIMARY KEY auto_increment,

name VARCHAR(20) not null,

sex VARCHAR(5) not null

);

create table tea_std11(

id int PRIMARY KEY auto_increment,

t_id int,

s_id int,

score int,

CONSTRAINT teacher_fk FOREIGN KEY (t_id) REFERENCES teacher11(id),

CONSTRAINT student_fk FOREIGN KEY (s_id) REFERENCES student11(id)

);

查询id为1的老师教过的所有学生

步骤一 ，从第三张表中查出老师教过的学生

select * from tea_std11 where t_id = 1;

步骤二 根据上面id取查询学生的信息,(1,2)代表学生id

select * from student11 where id in (1,2);

使用嵌套子查询

select * from student11 where id in (select s_id from tea_std11 where t_id=1);

相关子查询

求t_id=1的平均分

select avg(score) from tea_std11 where t_id=1;

求：每一科考试成绩大于平均分的学生分数

select * from tea_std11 as t1 where t1.score>(select avg(score) from tea_std11 t2 WHERE t1.t_id=t2.t_id);

select * from tea_std11;

select avg(score) from tea_std11 where t_id=1;

select * from tea_std11 as t1 where t1.score>(select avg(score) from tea_std11 t2 WHERE t1.t_id=t2.t_id);

常用函数

聚合函数

Count()计数

（1）统计一个班多少学生

select count(id) from student;

select count(*) from student;

（2）统计ui分数大于60的学生个数

select count(*) from score where ui>60;

统计总分大于200的人数

select count(*) from score where (python+ui+mysql)>200;

Sum()  求和

(1)统计一个班mysql总成绩

select sum(mysql) from score;

(2)统计各科的总成绩

select sum(python),sum(mysql),sum(ui) from score;

（3）统计python,mysql,ui总成绩之和

select sum(python+mysql+ui) from score;

Avg()   求平均数

统计一个班python成绩的平均分

select avg(python) from score;

select sum(python)/count(python) from score;

select avg(python+mysql+ui) from score;

Max(),min() 最大最小

select min(ui),max(ui) from score;

Group by分组

create table t_order(id int primary key,product varchar(20),price float(8,2));

insert into t_order values(1,'xiaomi', 1000);

insert into t_order values(2,'xiaomi',1100);

insert into t_order values(3,'huawei',2200);

insert into t_order values(4,'apple',8200);

1.对订单表中商品归类后，显示每一类商品的总价

select product,sum(price) from t_order GROUP BY product;

2.查询每一类商品总价格大于3000的商品

select product,sum(price) as total from t_order GROUP BY product HAVING total>3000; 

having注意事项

WHERE是在分组(group by)前进行条件过滤，

HAVING子句是在分组(group by)后进行条件过滤，

WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。