sql语句中的一些查询
mysql是一个功能强大且方面操作的一个关系型数据库
所以我们做后端开发的会经常用到mysql数据库,那么我们也会常用到sql语句了,下面我就给大家做些简单的sql查询,闲来无事随便弄一下=.=;
咱们首先可以来从一个例子下手
数据库有三个表 teacher表, student表, tea_stu关系表
teacher表 有teaID name age ,
student 表 有stuID name age ,
teacher_student表 有 teaID stuID,
要求用一条SQL查询出这样的结果:
1.显示的字段要有老师 id age 每个老师所带的学生人数
2.只列出老师age为40 以下, 学生age为12以上的记录
1>创建三个表
create table teacher(
teaid int auto_increment primary key comment "主键自增",
name varchar(100) comment "老师名称",
age int comment "年龄"
);
create table student(
stuid int auto_increment primary key comment "主键自增",
name varchar(100) comment "学生名称",
age int comment "年龄"
);
create table teacher_student(
teaid int comment "老师id",
stuid int comment "学生id"
);
2>做一下外键关系
ALTER TABLE `teacher_student` ADD CONSTRAINT `tea_stu_teaid_fk` FOREIGN KEY (`teaid`) REFERENCES `teacher` (`teaid`);
ALTER TABLE `teacher_student` ADD CONSTRAINT `tea_stu_stuid_fk` FOREIGN KEY (`stuid`) REFERENCES `student` (`stuid`);
3>分别往表里面根据字段添加数据
insert into teacher(teaid, name, age) values(1, "小慧", 30),(2, "小宇", 28),(3, "小任", 45);
insert into student(stuid, name, age) values(1, "小阳", 19),(2, "小忠", 20),(3, "小旭", 20),(4, "小凡", 18);
insert into student(stuid, name, age) values(5, "小薛", 27),(6, "小李", 27),(7, "小赵", 24),(8, "小胡", 12);
insert into student(stuid, name, age) values(9, "小琦", 28),(10, "小杨", 27);
- 查看teacher表内数据
select * from teacher;
±------±-----±-----+
| teaid | name | age |
±------±-----±-----+
| 1 | 小慧 | 30 |
| 2 | 小宇 | 28 |
| 3 | 小任 | 45 |
±------±-----±-----+
- 查看student表内数据
select * from student;
±------±-----±-----+
| stuid | name | age |
±------±-----±-----+
| 1 | 小阳 | 19 |
| 2 | 小忠 | 20 |
| 3 | 小旭 | 20 |
| 4 | 小凡 | 18 |
| 5 | 小薛 | 27 |
| 6 | 小李 | 27 |
| 7 | 小赵 | 24 |
| 8 | 小胡 | 12 |
| 9 | 小琦 | 28 |
| 10 | 小杨 | 27 |
- 查看外键表teacher_student内数据
select * from teacher_student;
±------±------+
| teaid | stuid |
±------±------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
| 3 | 9 |
| 3 | 10 |
±------±------+
4>在生成的外键表中进行关系关联
insert into teacher_student(teaid, stuid) values(1,1),(1, 2),(1,3),(1,4);
insert into teacher_student(teaid, stuid) values(2,5),(2, 6),(2,7),(2,8);
insert into teacher_student(teaid, stuid) values(3,9),(3, 10);
1.显示的字段要有老师 id age 每个老师所带的学生人数
select teacher.teaid, teacher.age, count(teacher_student.stuid) as student_count from teacher
left join teacher_student on teacher.teaid = teacher_student.teaid
group by teacher_student.teaid;
±------±-----±--------------+
| teaid | age | student_count |
±------±-----±--------------+
| 1 | 30 | 4 |
| 2 | 28 | 4 |
| 3 | 45 | 2 |
±------±-----±--------------+
2.只列出老师age为40 以下, 学生age为12以上的记录
select teacher.teaid, teacher.name, teacher.age, count(teacher_student.stuid) as student_count from teacher
left join teacher_student on teacher.teaid = teacher_student.teaid
left join student on student.stuid = teacher_student.stuid
where teacher.age<40 and student.age>12
group by teacher_student.teaid;
±------±-----±-----±--------------+
| teaid | name | age | student_count |
±------±-----±-----±--------------+
| 1 | 小慧 | 30 | 4 |
| 2 | 小宇 | 28 | 3 |
±------±-----±-----±--------------+
5>然后我们就可以直观的看出来来结果
补充:如果添加数据报错,请看我上篇sql解决
部分数据展示
- 查看表结构
desc student;
±------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±---------------+
| stuid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
±------±-------------±-----±----±--------±---------------+
- 查看创建数据库的语句
show create database day01;
±---------±---------------------------------------------------------------+
| Database | Create Database |
±---------±---------------------------------------------------------------+
| day01 | CREATE DATABASE day01
/*!40100 DEFAULT CHARACTER SET utf8 */ |
±---------±---------------------------------------------------------------+
nothing is impossible!