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

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 ||作者：穆尼塞卡·乌达瓦拉帕蒂的详细内容，更多请关注php中文网其它相关文章！