多表关联

1. 多表关联

2. 列约束 • 格式 • col_name type column_details • 这里column_details可以取 • NULL • NOT NULL • DEFAULT defaule_value • AUTO_INCREMENT • PRIMARY KEY(不能为 NULL) • UNIQUE(可以为NULL,但最多只能有一个NULL)

3. Create table customer( id int primary key auto_increment, name varchar(10) not null, address varchar(50) not null, phone_number varchar(10) not null) 例如 insert into customer(name,address,phone_number) values('dxw','gongzhufen','63983477');

4. 外键约束 Create table fee( fee_id int primary key auto_increment, customer_id int not null, fee float(10), foreign key(customer_id) references customer (id)); 例如insert into fee(customer_id,fee) values(1,1.5);

5. 如果外键约束存在，在子表中插入数据时，主表中必须有相应的纪录如果外键约束存在，在子表中插入数据时，主表中必须有相应的纪录 • insert into fee(customer_id,fee) values(100,1.5);

6. 多表查询 • 基本的多表查询 • select * from customer,fee; • 选定列 • select name,fee from customer,fee; • 关联两个表 • select name,fee from customer,fee where customer.id=fee.customer_id; • 关联两个表并附加条件 • select name,fee from customer,fee where customer.id=fee.customer_id and customer.name='dxw';

7. 聚合 • select sum(fee) from customer,fee where customer.id=fee.customer_id; • select sum(fee) from customer,fee where customer.id=fee.customer_id and customer.name='dxw'; • 分组 • select name,sum(fee) from customer,fee where customer.id=fee.customer_id group by name; • select name,sum(fee) from customer,fee where customer.id=fee.customer_id and customer.name='dxw' group by name;

8. 左连接 create table employee( id int primary key, name varchar(10)); Create table fine( id int, money float, foreign key(id) references employee(id));

9. 如果要列出整个公司的罚款单 • select name,money from employee left join fine on employee.id= fine.id; • select name,sum(money) from employee left join fine on employee.id= fine.id group by name; • 列出左表，并与右表匹配

10. 右连接 • select name,money from employee rightjoin fine on employee.id= fine.id; • 列出右表，并与左表匹配