sql语句查询

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!