MySql学习笔记

[color=red]建表且插入数据[/color]
drop table if exists customers;
create table customers(
id int primary key auto_increment not null,
name varchar(10),
age varchar(3)
);

insert into customers (name,age) values ('tom','21');
insert into customers (name,age) values ('Mike','24');
insert into customers (name,age) values ('Jack','30');
insert into customers (name,age) values ('Linda','25');
insert into customers (name,age) values ('Tom','NULL');

[color=red]建表且插入数据[/color]
drop table if exists orders;
create table orders(
id int primary key auto_increment not null,
order_number varchar(20),
price varchar(6),
customer_id int
);

insert into orders (order_number,price,customer_id) values ('Tom_Order001','100',1);
insert into orders (order_number,price,customer_id) values ('Tom_Order002','200',1);
insert into orders (order_number,price,customer_id) values ('Tom_Order003','300',1);
insert into orders (order_number,price,customer_id) values ('Mike_Order001','100',3);
insert into orders (order_number,price,customer_id) values ('Jack_Order001','200',4);
insert into orders (order_number,price,customer_id) values ('Linda_Order001','100',5);
insert into orders (order_number,price,customer_id) values ('UnknowOrder','200',NULL);

[color=red]向原表中插入字段[/color]
alter table orders add sex varchar(10) not null after price;

[color=red]命令行下执行mysql.sql文件[/color]
\. mysql.sql


[color=red]以customers为左表,orders为右表示例[/color]
[color=red]左外链接[/color]
如果用左外连接的话，指查询出来的是在右表中没有对应左表中的记录以及符合条件（c.id=o.customer_id）的数据,这样的结果显示的是在左表中的某些用户在orders表中没有对应的订单
也就是说左边中的数据会全部列出,而右表中列出的只是符合c.id=o.customer_id的数据,而没有符合条件的则以NULL形式列出
select * from customers c left join orders o on c.id=o.customer_id;

[color=red]右外链接[/color]
跟左外链接正好相反
select * from customers c right join orders o on c.id=o.customer_id;