当前位置：首页 > 数据库 > MySQL教程 > SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂 > 正文

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

来源：这里教程网 时间：2026-02-28 18:17:17 作者：

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);

教师桌

teacher_id teacher_name age subject experience
101 sk. sohana 30 mathematics 5
102 u. munisekhar 35 english 8
103 sk. nellu 40 science 10
104 a. venu 28 history 3
105 s. jagadeesh 28 telugu 3

类表

class_id class_name teacher_id
9 math 101
10 english 102
11 science 103
12 history 104
    从class表中获取数据 
select * from class;

| class_id | class_name         | teacher_id |
|----------|--------------------|------------|
| 9        | math               | 101        |
| 10       | english            | 102        |
| 11       | science            | 103        |
| 12       | history            | 104        |
    从教师表中获取数据 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    |

编辑推荐：

相关推荐

最新软件资讯

如何在 MySQL 中定义计数器
如何在 MySQL 中定义计数器

热文推荐

天极热推