建表语句
1、create table class(id int auto_increment primary key,name varchar(20) not null, stuNum int not null);
2、create table students(id int auto_increment primary key, name varchar(20) not null, gender bit default 1, classid int not null, foreign key(classid) references class(id));
插入一些数据:
insert into class values(0,"python01",55),(0,"python02",50),(0,"python03",60),(0,"python04",80); (注意:一条语句可以插入多条数据)
insert into students values(0,"tom",1,1);
删除指定id:delete from students_1 where id=2 or id=3;
insert into students values(0,"lilei",1,10);
此处运行会报错!
insert into students values(0,"jack",1,2);
select * from students;
关联查询:
select students_1.name, class.name from class inner join students_1 on class.id=students_1.classid;
select students_1.name, class.name from class left join students_1 on class.id=students_1.classid;
分类:
1、表A inner join 表B: 表A与表B匹配的行会出现在结果集中;
2、表A left join 表B: 表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据使用null填充;
3、表A right join 表B:表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据使用null填充。