学生信息表mysql表的语句，关联查询

说明：我最近在弄一个sql的数据，然后简单弄点增删改查的东西，然后关联表的查询，然后拿到list数据，生成json，进行展示

查询表长度

select count(*) from tv_student;

删除表的数据

 truncate table tv_student;

1.查询学生总数 16

select count(*) from tv_student;

2.查询学生总数里面的已经接送 7

select count(*) from tv_student where ready=0;

3.查询学生总数里面的待接送 4

select count(*) from tv_student where ready=1; 

4.查询学生总数里面的未接送 5

 select count(*) from tv_student where ready=2;

5.查询待接送 list 列表

 select * from tv_student where ready=1;

查询班级列表

 select * from tv_class  

查询班级列表里面的学生总数 查询班级列表里面的学生接送状态 和数量

总共三个班 101 207 303

查询三个班级里面的学生列表

select * from tv_student where class_id=101
 
 select * from tv_student where class_id=303 && ready=0
 
 select * from tv_student where class_id=207 && ready=0 

多个条件查询

查出来了

每个班级学生的列表 都能查出来

查询 辉志的学生，他的班主任的名字和电话号码

查一个叫辉志的人

select * from tv_student where student_id=13332145236

查到辉志的班级id

select class_id from tv_student where student_id=13332145236

根据班级id 查出老师的电话号码

查这个人班主任的电话号码

select tv_class.teacher_id,tv_class.name,teacher_name from tv_class where class_id=(select class_id from tv_student where student_id=15852145236)

select tv_class.teacher_id from tv_class where class_id=303

然后用java手段 把数据存到list里面就行，最后输出json，在前端展示

下面是完整的sql，创建表和插入数据

-- auto-generated definition
create table tv_class
(
    class_id     bigint       not null comment '班级id'
        primary key,
    school_id    bigint       not null comment '学校id',
    name         varchar(255) null comment '班级名称',
    teacher_name varchar(255) null comment '教师名称',
    teacher_id   bigint       not null comment '教师id',
    status0      varchar(255) null comment '备用字段0',
    status1      varchar(255) null comment '备用字1',
    status2      varchar(255) null comment '备用字2'
);



-- auto-generated definition
create table tv_school
(
    school_id bigint       not null comment '学校id'
        primary key,
    name      varchar(255) null comment '学校名称',
    address   varchar(255) null comment '学校地址',
    status0   varchar(255) null comment '备用字段0',
    status1   varchar(255) null comment '备用字段1',
    status2   varchar(255) null comment '备用字段2'
);

-- auto-generated definition
create table tv_student
(
    student_id bigint       not null comment '学生id',
    school_id  bigint       not null comment '学校id',
    class_id   bigint       not null comment '班级id',
    room_id    bigint       not null comment '宿舍号id',
    name       varchar(255) null comment '班级名称',
    ready      bigint       not null comment '接送状态，全部，已经接送，待接送，未接送，0，1，2',
    status0    varchar(255) null comment '备用字段0',
    status1    varchar(255) null comment '备用字1',
    status2    varchar(255) null comment '备用字2'
);

INSERT INTO school.tv_school (school_id, name, address, status0, status1, status2) VALUES (24610001, '昆明七中', '云南楚雄', '', '', '');
INSERT INTO school.tv_school (school_id, name, address, status0, status1, status2) VALUES (24610002, '河北理工学院', '河北正定', '', '', '');


INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13952145236, 24610002, 303, 208, 'tom', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13052145236, 24610002, 303, 208, 'bob', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13122145236, 24610002, 303, 208, 'smail', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13222145236, 24610002, 303, 208, 'ailise', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13332145236, 24610002, 303, 208, 'lida', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13442145236, 24610002, 303, 208, 'linny', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13552145236, 24610002, 303, 212, 'luyisi', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13662145236, 24610002, 303, 209, 'jhon', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13772145236, 24610002, 303, 208, 'kennidi', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13882145236, 24610001, 207, 208, 'trump', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (13992145236, 24610001, 207, 208, 'baiden', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15752145236, 24610001, 207, 208, 'shuersi', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15852145236, 24610001, 207, 208, 'puting', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15952145236, 24610001, 101, 208, 'meidefu', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15352145236, 24610001, 101, 208, 'weilianmusi', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15252145236, 24610001, 101, 208, 'azike', 0, '', '', '');
INSERT INTO school.tv_student (student_id, school_id, class_id, room_id, name, ready, status0, status1, status2) VALUES (15152145236, 24610001, 101, 208, 'shanmu', 0, '', '', '');


INSERT INTO school.tv_class (class_id, school_id, name, teacher_name, teacher_id, status0, status1, status2) VALUES (101, 24610001, '一年级理科1班', 'opeike', 15555684581, '', '', '');
INSERT INTO school.tv_class (class_id, school_id, name, teacher_name, teacher_id, status0, status1, status2) VALUES (207, 24610001, '二年级文科7班', 'sudan', 13952144123, '', '', '');
INSERT INTO school.tv_class (class_id, school_id, name, teacher_name, teacher_id, status0, status1, status2) VALUES (303, 24610002, '三年级移动3班', 'fenlan', 15752362145, '', '', '');