说明:我最近在弄一个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, '', '', '');