mysql实战

准备一个数据库

• win/linux安装数据库

创建表

• 这里我们创建四张表,为了满足一对一,一对多,多对多

-- 课程表
CREATE TABLE `course` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 身份证表
CREATE TABLE `id_card` (
  `id` int(11) NOT NULL,
  `id_card` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `room` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 学生课程映射表
CREATE TABLE `student_course` (
  `sid` int(11) NOT NULL,
  `cid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

-- 课程表
INSERT INTO `course`(`id`, `name`) VALUES (1, '语文');
INSERT INTO `course`(`id`, `name`) VALUES (2, '数学');
INSERT INTO `course`(`id`, `name`) VALUES (3, '英语');
--身份证表
INSERT INTO `id_card`(`id`, `id_card`) VALUES (1, '110');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (2, '220');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (3, '330');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (4, '440');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (5, '550');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (6, '660');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (7, '770');
INSERT INTO `id_card`(`id`, `id_card`) VALUES (8, '880');
-- 学生表
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (1, '小一', 1, '1');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (2, '小二', 2, '1');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (3, '小三', 3, '1');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (4, '小四', 4, '2');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (5, '小五', 5, '2');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (6, '小六', 6, '3');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (7, '小七', 7, '3');
INSERT INTO `student`(`id`, `name`, `age`, `room`) VALUES (8, '王虎', 88, '4');
-- 学生课程映射表
INSERT INTO `student_course`(`sid`, `cid`) VALUES (1, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (1, 2);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (1, 3);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (2, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (2, 2);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (3, 3);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (4, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (5, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (6, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (7, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (8, 1);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (4, 2);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (5, 3);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (6, 2);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (6, 3);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (7, 2);
INSERT INTO `student_course`(`sid`, `cid`) VALUES (8, 3);

需求

1. 根据 学生id 查询 学生信息 和 身份证(一对一)
2. 根据班级id 查询学生信息(一对多)
3. 根据学生id查询他所有的课程信息(多对多)
4. 根据课程名字查询他所有的学生信息(多对多)
5. 输入名字/不输入名字查学生信息(模糊查询)
6. 输入学生名字/年龄查找学生信息(多条件查询)
7. 输入多个班级获取所属学生信息(多条件查询)

编写sql语句

-- 根据 学生id 查询 学生信息 和 身份证
select student.id,student.name,student.age,id_card.id_card,student.room from student,id_card where student.id = id_card.id;
-- 根据班级id 查询学生信息
select * from student where room = '1';
-- 根据学生id查询他所有的课程信息
select student.id,student.name,student.age,student.room,(select course.name from course where  course.id = student_course.cid ) as course from student_course,student where student.id = student_course.sid and student.id = '2';
-- 根据课程名字查询他所有的学生信息
select course.name as coursename,(select student.name from student where student_course.sid = student.id) as studentname from student_course,course where course.id= student_course.cid and course.name = '数学';
-- 输入名字/不输入名字查学生信息
select * from student where name like '%小%';
-- 输入学生名字/年龄查找学生信息
select * from student where name like '%小%' and age > 3;
-- 输入多个班级获取所属学生信息
select * from student where room in ('1','2');