数据库 - 4 简单常用的 mysql 增删改查（简单的多表和子查询）

一、创建表两个表 插入数据

1-1、class表 （同学分数表）

create table class(id int(20) unsigned auto_increment primary KEY not null,
name varchar(16) default NULL,

subject varchar(8),

scord int(6))

default charset=utf8;

1-2、class表插入数据 (同学信息表)

INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('1','张三','语文',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('2','李四','语文',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('3','王二','语文',0);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('4','麻子','语文',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('5','李四','数学',0);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('6','王二','数学',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('7','麻子','数学',50);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('8','张三','数学',50);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('9','李四','英语',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('10','王二','英语',60);
INSERT INTO `class` (`id`, `name`, `subject`, `score`) VALUES('11','麻子','英语',60);

2-1、student表

create table student(id int(20) unsigned auto_increment primary KEY not null,
name varchar(16) default NULL,

sex varchar(8),

phone varchar(16))

default charset=utf8;

2-2、插入数据

INSERT INTO `student` (`id`, `name`, `sex`, `phone`) VALUES('1','张三','男','15601883498');
INSERT INTO `student` (`id`, `name`, `sex`, `phone`) VALUES('2','李四','男','13011225006');
INSERT INTO `student` (`id`, `name`, `sex`, `phone`) VALUES('3','王二','男','18137366791');
INSERT INTO `student` (`id`, `name`, `sex`, `phone`) VALUES('4','麻子','女','15601196116');

3、多表联查   (通过关键字段name进行)

select * from student s ,student class c where s.name = c.name;

select * from 表1 新表名1,表2 新表名2 where 新表名1.相同字段 = 新表名2.相同字段; 

 4、子查询  (查询出分数小于50同学的信息)

SELECT NAME FROM class WHERE score<50

SELECT id,NAME,sex,phone FROM student WHERE NAME IN
(SELECT NAME FROM class WHERE score<50);