select高级用法

select高级用法

传统连接

# 多表查询
# 创建表
[zhang3,li4,wang5]
[50,70,80]

t1:student
sid sname
001 zhang3
002 li4
003 wang5

create table student(
sid int(3) zerofill,
sname varchar(10));

insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');

root@localhost:wc>select * from student;
+------+--------+
| sid  | sname  |
+------+--------+
|  001 | zhang3 |
|  002 | li4    |
|  003 | wang5  |
+------+--------+
3 rows in set (0.00 sec)

# 创建score表
t2:score
sid smark
001 50
002 70
003 80

create table score(
sid int(3) zerofill,
smark float(3,1));

insert into score value(1,50),(2,70),(3,80);

root@localhost:wc>select * from score;
+-----+-------+
| sid | smark |
+-----+-------+
| 001 |    50 |
| 002 |    70 |
| 003 |    80 |
+-----+-------+
3 rows in set (0.02 sec)

# 查询wang5的成绩

# 传统连接（连表查询的前提：两张表，必须有关联的字段）
select student.sname,score.smark from student,score where student.sid=score.sid and student.sname='wang5';

# 世界上小于100人的人口城市是哪个国家？
root@localhost:world>select city.Population,country.name,city.name 
from city,country where 
city.CountryCode=country.Code 
and city.Population < 100;

# 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
select country.name,city.name,city.populati
on,countrylanguage.language
from country,city,countrylanguage
where country.code=city.countrycode
and country.code=countrylanguage.countrycode
and city.population > 10000000;

自连接（NATURAL JOIN）

自动找到等价条件，前提：两张表的等价条件字段名，必须一样
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode

SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000;

# 说english的城市有哪些，他们的国家代码是什么？
root@localhost:world>select countrylanguage.Language,city.CountryCode,city.Name from city NATURAL JOIN countrylanguage and where countrylanguage.Language='English';

# 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.languageas 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode order by;

# 每个国家有几个城市说英语，他们的国家代码是什么？ 按城市数量排序
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.languageas 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode order by 城市数量;

内连接 (join on)

select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;

# 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.languageas 语言
from city join countrylanguage
on city.countrycode=countrylanguage.countrycode
where countrylanguage.language='english'
group by city.countrycode;

# 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
select country.name,city.name,city.population,countrylanguage.language
from country
join city
on country.code=city.countrycode
join countrylanguage
on country.code=countrylanguage.countrycode
where city.population > 10000000;

外连接

## 左外连接 left join
select city.name,city.countrycode,country.name from city left join country on city.countrycode=country.code and city.population<100;

## 右外连接 right join
select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code and city.population<100;

联合查询

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';

#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');

# 替换为：
mysql> select * from city where countrycode='CHN' union all select * from city where
countrycode='USA';
union：去重复合并
union all ：不去重复
使用情况：union < union all
视图：view
触发器：trigger

练习题

库名：linux50 字符集：utf8 校验规则：utf8_general_ci

create database linu50 charset utf8 collate utf8_general_ci;

表一

表名：student（学生表）

字段 数据类型要求 是否为空 注释
sno 最多20位 否 学号（主键）
sname 可变长 否 学生姓名
sage 最小整数，非负数 否 学生年龄
ssex 0，1 否 学生性别（1是男，0是女s）默认为男）
sbirthday 时间类型 默认为空 学生生日
class 可变长 否 学生班级

create table student(
    sno bigint(20) primary key auto_increment comment '学号(主键)',
    sname varchar(10) not null comment '学生姓名',
    sage tinyint unsigned not null comment '学生年龄',
    ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女s）默认为男）',
    sbirthday datetime default null comment '学生生日',
    class varchar(5) not null comment '学生班级'
);



insert into student(sname,sage,ssex,sbirthday,class) value
('徐导',20,'1',now(),'1'),
('曾导',18,'1',now(),'1'),
('李导',25,'1',now(),'2');

表二
表名：course（课程表）

字段 数据类型要求 是否为空 注释
cno 最多20位 否 课程号（主键）
cname 可变长 否 课程名称
tno 可变长 否 教师编号

create table course(
    cno bigint(20) primary key auto_increment comment '课程号(主键)',
    cname varchar(10) not null comment '课程名称',
    tno varchar(3) not null comment '教师编号'
);


insert into course(cno,cname,tno) value(1,'英语','001'),(2,'语文','002'),(3,'数学','003');

表三

表名：score（成绩表）

字段 数据类型要求 是否为空 注释
sno 最多20位 否 学号（主键）
cno 最多20位 否 课程号（主键）
mark 浮点数(4,1) 否 成绩

create table score(
    sno bigint(20) not null comment '学号（主键）',
    cno bigint(20) not null comment '课程号（主键）',
    mark float(4,1) not null comment '成绩'
);


CREATE TABLE `test4` (
  `sno` bigint(20) NOT NULL AUTO_INCREMENT,
  `cno` bigint(20) NOT NULL,
  `mark` float(4,1) NOT NULL,
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



mysql[zls]> insert into score(sno,cno,mark) value(1,1,90.0),
(2,1,10.0),
(3,1,60.0),
(1,2,90.0),
(2,2,99.5),
(3,2,80.0),
(1,3,80.5),
(2,3,60.0),
(3,3,88.0);

表四

表名：teacher（教师表）

字段 数据类型要求 是否为空 注释
tno 最多20位 否 教师编号（主键）
tname 可变长 否 教师姓名
tage 最小整数，非负数 否 教师年龄
tsex 0，1 否 教师性别（1是男，0是女）默认为男）
prof 可变长 是 教师职称
depart 可变长 否 教师部门

create table teacher(
tno bigint(3) zerofill primary key auto_increment comment '教师编号（主键）',
tname varchar(10) not null comment '教师编号（主键）',
tage tinyint unsigned not null comment '教师年龄',
tsex enum('0','1') not null comment '教师性别（1是男，0是女）默认为男）',
prof varchar(10) null comment '教师职称',
depart varchar(10) not null comment '教师部门'
);


zerofill 零填充


insert into teacher(tname,tage,tsex,prof,depart) 
value('曾志高翔',18,'1','教学总监','语言系'),
('徐亮伟',50,'1','讲师','文学系'),
('李永义',80,'1','助教','科学系');

作业

查询练习：

1.查询student表中的所有记录的sname、ssex和class列。
mysql[zls]> select sname,ssex,class from student;

2.查询教师所有的单位即不重复的depart列。
select distinct(depart) from teacher;


3.查询student表的所有记录。
select * from student;


4.查询score表中成绩在60到80之间的所有记录。
select * from score where mark>=60 and mark<=80;


5.查询score表中成绩为85，86或88的记录。
select * from score where mark in (85,86,88);
select * from score where mark=85 or mark=86 or mark=88;


6.查询student表中1班或性别为“女”的同学记录。
select * from student where class='1' or ssex='0';


7.以class降序查询Student表的所有记录。
select * from student order by class desc;

8.以cno升序、mark降序查询Score表的所有记录
select * from score order by cno , mark desc;

9.查询2班的学生人数。
select count(class) from student where class='2' group by class;

10.查询”曾志高翔“教师任课的学生成绩
select teacher.tname,course.cname,score.mark,student.sname
from teacher
join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on score.sno=student.sno
where score.cno=1;

11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
select student.sname as '名字',student.ssex'性别',course.cname'课程',score.mark'成绩',teacher.tname'教师名',teacher.prof'教师职称',teacher.depart'教师部门'
from teacher
join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on score.sno=student.sno
where score.cno=2 and student.ssex='1' order by score.mark desc;

12.把11题查出的成绩按照降序排序
select student.sname as '名字',student.ssex'性别',course.cname'课程',score.mark'成绩',teacher.tname'教师名',teacher.prof'教师职称',teacher.depart'教师部门'
from teacher
join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on score.sno=student.sno
where score.cno=2 and student.ssex='1' order by score.mark desc;
（不加desc是顺序，加了的是倒序）