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);
教师桌
| 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 |
以上就是SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂的详细内容，更多请关注编程网其它相关文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
历年真题答案解析
备考技巧名师总结
高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容猜你喜欢
AI推送时光机SQL || MySQL ||作者：穆尼塞卡·乌达瓦拉帕蒂
数据库2024-10-13
咦！没有更多了？去看看其它编程学习网 内容吧
