MySQL学习之路3

六.子查询,联结和组合检索

0.创建新表

首先创建个新表grade(成绩表):

CREATE TABLE `grade` (
  `no` int(10) NOT NULL,
  `subject` char(32) DEFAULT NULL COMMENT '科目',
  `personid` int(10) DEFAULT NULL COMMENT '人员id',
  `grade` int(10) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后插入数据:

INSERT INTO `grade` VALUES (1, 'math', 1, 80),(2, 'math', 2, 90),(3, 'math', 3, 70),(4, 'chinese', 1, 75),(5, 'chinese', 2, 95),(6, 'chinese', 3, 85),(7, 'english', 1, 70),(8, 'chinese', 2, 80),(9, 'chinese', 3, 95);

插入后验证插入的数据,查询如下:

1.子查询

select name from person where id in (select personid from grade where subject = "math" and grade > 70);

查找数学成绩大于70的人员名

select name, (select SUM(grade) from grade where personid = person.id) as 'all_grade' from person;

2.联结检索

select name, subject, grade from person, grade where person.id = grade.personid; 或

select name, subject, grade from person join grade where person.id = grade.personid;

3.组合检索

select * from person where age > 20 union select * from person where sex = 0;

注:

1)union必须有两条以上select语句组成

2)union每个查询的列,表达式或聚集函数必须相同

3)union默认去掉重复的行,若想要全部则可以用union all

4)可以与order by同时使用