mysql第四天,连接查询

1、现任意数据库中存在表testu、tetea、tema：

1.1、建表如下要求，写出建表及其数据插入语句：

testu学生表：

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| sno   | int         | NO   | PRI | NULL    | auto_increment |

| name  | varchar(10) | YES  |     | NULL    |                |

| tid   | int         | YES  | MUL | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

+-----+------+------+

| sno | name | tid  |

+-----+------+------+

|   1 | 李四 |    1 |

|   2 | 王五 |    2 |

|   3 | 张三 |    3 |

|   4 | 赵码 | NULL |

|   5 | xxxx |    1 |

|   6 | 孙彦 |    2 |

|   7 | 江城 |    1 |

|   8 | 王旭 |    2 |

|   9 | 李四 |    1 |

+-----+------+------+

tetea教师表

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| tid   | int         | NO   | PRI | NULL    | auto_increment |

| name  | varchar(10) | YES  |     | NULL    |                |

| major | varchar(10) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

+-----+--------+----------+

| tid | name   | major    |

+-----+--------+----------+

|   1 | 张老师 | English  |

|   2 | 王老师 | English  |

|   3 | 李老师 | Computer |

+-----+--------+----------+

tema专业表

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| major   | varchar(10) | YES  |     | NULL    |       |

| tuition | mediumtext  | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

+----------+---------+

| major    | tuition |

+----------+---------+

| English  | 4688    |

| Computer | 16888   |

+----------+---------+

备注：学生表id=5的name为自己的名字

#testu:
#创建表：
 create table testu(
 sno int(100) not null auto_increment primary key,
 name varchar(10),
 tid int);
 alter table testu add index(tid);
#添加数据：
 insert into testu values
 (1,'李四',1),
 (2,'王五',2),
 (3,'张三',3),
 (4,'赵码',null),
 (5,'魏睿',1),
 (6,'孙彦',2),
 (7,'江城',1),
 (8,'王旭',2),
 (9,'李四',1);

#tetea:
#创建表：
create table tetea(
    tid int(100) not null auto_increment primary key,
    name varchar(10),
    major varchar(10));
#添加数据：
insert into tetea values(1,'张老师','English'),
                        (2,'王老师','English'),
                        (3,'李老师','Computer');

#tema:
#创建表：
create table tema(
    major varchar(10),
    tuotion mediumtext);
#添加数据：
insert into tema values('English','4688'),
                       ('Computer','16888');

1.2、请写出：找出所有研究方向为“Computer”的老师所指导的学生的学号姓名的语句:

select testu.sno '学号',testu.name '姓名' 
from testu 
where tid = (select tetea.tid from tetea where major = 'Computer');

1.3、请写出：找出每个指导老师的姓名、研究方向和有多少名学生的语句:

select tid,name,major,(select count(*) 
from testu 
where testu.tid = tetea.tid)num from tetea;

1.4、请写出：找出学费（tuition）为4688的课程一共有多少名老师教授，检索出对应专业名字和老师人数，老师人数列命名为“自己姓名_num”

select major,(select count(*) 
from tetea 
where tetea.major = tema.major)'魏睿_num' from tema;

1.5、请写出：检索出各位学生姓名和对应指导老师的major，使用至少两种方法：

考察连接查询

SELECT  * FROM 表1 [连接方式|inner|left|right] JOIN 表2 [ON 连接条件] WHERE 过滤条件

#自然连接
select sno,testu.name as '学生',tetea.name as '老师',major 
from testu,tetea 
where testu.tid = tetea.tid;
#内连接
select sno,testu.name as '学生',tetea.name as '老师',major 
from testu 
inner join tetea 
on testu.tid = tetea.tid;

1.6、请写出：检索出各位学生名字、对应老师名字、专业名字和学费：

select testu.name as '学生姓名',tetea.name as '老师名字',tema.major as '专业名称',tuotion as'学费' 
from testu,tetea,tema 
where testu.tid = tetea.tid and tetea.major = tema.major;