案例2-创建多表,可以描述出表于表之间的关系
常见关系:
一对多. 多对多. 一对一. ER图可以描述实体于实体之间的关系 实体用矩形表示 属性用椭圆表示 关系用菱形表示一对多:用户和订单 create table userinfoinfo( uid int primary key auto_increment, userinfoname varchar(20));create table orders( oid int primary key auto_increment, price double, userinfoinfo_id int);
为了保证数据的有效性和完整性,在多表的一方添加外键约束
格式: alter table 多表名称 add foreign key(外键名称) references 一表名称(主键); 例如: alter table orders add foreign key (userinfoinfo_id) references userinfoinfo(uid); 添加了外键约束之后有如下特点:★ 1.主表中不能删除从表中已引用的数据 2.从表中不能添加主表中不存在的数据 开发中处理一对多:★ 在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致, 为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.
多对多例子:商品和订单-- 创建商品表create table product( pid int primary key auto_increment, pname varchar(20), price double);-- 创建中间表create table orderitem( orders_id int, product_id int);-- 添加外键约束alter table orderitem add foreign key(orders_id) references orders(oid); alter table orderitem add foreign key(product_id) references product(pid);
笛卡尔积:了解
多张表无条件的联合查询.没有任何意思 select a.*,b.* from a,b;内连接:★
格式1:显式的内连接（推荐） select a.*,b.* from a join b on ab的连接条件 格式2:隐式的内连接 select a.*,b.* from a,b where ab的连接条件外连接:★（记住一个就行了） 左外连接:★ select a.*,b.* from a left join b on 连接条件; 意思: 先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示. 右外连接: select a.*,b.* from b right [outer] join a on 连接条件; 意思: 先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b), 符合条件则展示出来,不符合以null值展示.子查询:★ 一个查询依赖另一个查询.练习: 查询用户的订单,没有订单的用户不显示 隐式内连接: select userinfo.*,orders.* from userinfo ,orders where userinfo.id=orders.userinfo_id; 显示内连接 select userinfo.*,orders.* from userinfo join orders on userinfo.id=orders.userinfo_id; 查询所有用户的订单详情 左外连接: userinfo在左 select userinfo.*,orders.* from userinfo left join orders on userinfo.id=orders.userinfo_id; 查询所有订单的用户详情 右外连接:orders 在右 select orders.*,userinfo.* from userinfo right join orders on userinfo.id=orders.userinfo_id;练习: 查看用户为张三的订单详情 1.先查询张三的id select id from userinfo where username = '张三';// 3 2.select * from orders where userinfo_id = ?; 两个合二为一 select * from orders where userinfo_id = (select id from userinfo where username = '张三'); 查询出订单的价格大于300的所有用户信息。 1.先查询出订单价格>300的用户的id select userinfo_id from orders where price >300;//(3,3,5,null) 2.select * from userinfo where id in(3,3,5,null); 两个合二为一: select * from userinfo where id in(select userinfo_id from orders where price >300); 查询订单价格大于300的订单信息及相关用户的信息。 内连接: select orders.*,userinfo.* from orders,userinfo where userinfo.id=orders.userinfo_id and orders.price>300 ; 子查询: 是将一个查询的结果作为一张临时表 select userinfo.*,tmp.* from userinfo,(select * from orders where price>300) as tmp where userinfo.id=tmp.userinfo_id; 给表起别名 格式: 表 [as] 别名//
-- 向userinfo表中添加数据
insert into userinfo values(3,'张三'); insert into userinfo values(4,'李四'); insert into userinfo values(5,'王五'); insert into userinfo values(6,'赵六');-- 向orders 表中插入数据
insert into orders values(1,1314,3); insert into orders values(2,1314,3); insert into orders values(3,15,4); insert into orders values(4,315,5); insert into orders values(5,1014,null);