SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

1.sql第2部分

1.创建类表

create table class(
    class_id int primary key,
    class_name varchar(50),
    foreign key (teacher_id) references teacher(teacher_id)
);

2.创建教师表

create table teacher (
    teacher_id int primary key,
    teacher_name varchar(100),
    age int,
    subject varchar(50),
    experience int
);

3.将教师数据插入表

insert into teacher(teacher_id,teacher_name,age,subject,experience)
values
(101, 'sk. sohana', 30, 'mathematics', 5),
(102, 'u. munisekhar', 35, 'english', 8),
(103, 'sk. nellu', 40, 'science', 10),
(104, 'a. venu', 28, 'history', 3);

4.将类数据插入表

insert into class(class_id,class_name,teacher_id)
(9, 'math', 101),
(10, 'english', 102),
(11, 'science', 103),
(12, 'history', 104);

教师桌

类表

1. 从class表中获取数据

select * from class;

| class_id | class_name         | teacher_id |
|----------|--------------------|------------|
| 9        | math               | 101        |
| 10       | english            | 102        |
| 11       | science            | 103        |
| 12       | history            | 104        |

1. 从教师表中获取数据 5年经验教师

select * from teacher whare experience >5

| teacher_id | teacher_name       | age | subject       | experience |
|------------|--------------------|-----|---------------|------------|
| 102        | u. munisekhar      | 35  | english       | 8          |
| 103        | sk. nellu          | 40  | science       | 10         |

7.查找munisekhar老师详细信息

select * from teacher where teacher_name='u. munisekhar'

| teacher_id | teacher_name       | age | subject       | experience |
|------------|--------------------|-----|---------------|------------|
| 102        | u. munisekhar      | 35  | english       | 8          |

8.找到 sk. sohana老师的经验？

select experience from teacher where teacher_name='sk. sohana';

| experience |
|------------|
|     8      |

9.查找老师的姓名和年龄，其中年龄为 29 至 39

select name,age from teacher where age between 29 and 39;

| teacher_name       | age |
|--------------------|-----|
| sk. sohana         | 30  | 
| u. munisekhar      | 35  | 

10.查找班级名称和老师姓名以使用左连接

select class.class_name, teacher.teacher_name
from class
right join teacher on class.teacher_id=teacher.teacher_id;

| class_name | teacher_name       |
|------------|--------------------|
| math       | sk. sohana         |
| english    | u. munisekhar      |
| science    | sk. nellu          |
| history    | a. venu            |

11.查找班级名称和所有教师姓名以使用右连接

select class.class_name, teacher.teacher_name
from class
right join teacher on class.teacher_id=teacher.teacher_id;

| class_name | teacher_name       |
|------------|--------------------|
| math       | sk. sohana         |
| english    | u. munisekhar      |
| science    | sk. nellu          |
| history    | a. venu            |
| null       | s. jagadeesh       |

12.查找班级名称和教师姓名以使用内连接

select class.class_name, teacher.teacher_name
from class
inner join teacher on class.teacher_id=teacher.teacher_id;

| class_name | teacher_name       |
|------------|--------------------|
| math       | sk. sohana         |
| english    | u. munisekhar      |
| science    | sk. nellu          |
| history    | a. venu            |

13.查找munisekhar班级显示他的姓名和班级

select teacher.teacher.name, class.class_name
from teacher 
right join class on teacher.teacher_id=class.teacher_id
where teacher.teacher_name = 'u. munisekhar';

| teacher_name       | class_name |
|--------------------|------------|
| U. Munisekhar      | English    |

理论要掌握，实操不能落！以上关于《SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！