Java基础入门day40

news/2024/11/7 22:49:24/

day40

DQL

分组补充

create table student(sid int,name varchar(20),sex char(6),score double,cid int
);
​
insert into student values(100, 'wukong', 'male', 99, 1);
insert into student values(101, 'wuneng', 'male', 59, 1);
insert into student values(102, 'wujing', 'male', 60, 1);
insert into student values(103, 'tangtang', 'male', 100, 1);
​
insert into student values(104, 'baoyu', 'male', 17, 2);
insert into student values(105, 'daiyu', 'female', 16, 2);
insert into student values(106, 'baichai', 'female', 16, 2);
insert into student values(107, 'xifeng', 'female', 27, 2);
​
insert into student values(108, 'liubei', 'male', 20, 3);
insert into student values(109, 'sunquan', 'male', 20, 3);
insert into student values(110, 'caocao', 'male', 20, 3);
​
insert into student values(111, 'songjiang', 'male', 20, 4);
insert into student values(112, 'likui', 'male', 22, 4);
insert into student values(113, 'sunerniang', 'female', 20, 4);
select sex, max(score) from student group by sex;           //  查询不同性别下的最高分
select name, sex, max(score) from student group by sex;     //  有些版本的mysql执行报错,有些版本不报错,但是name值对不上
​
select cid, max(score) from student group by cid;           //  根据班级来查询不同班级的最高分
select cid, name, max(score) from student group by cid;     //  有些版本的MySQL执行报错,有些不报错,但是name值对不上
​
//  查询列中的字段的选取,要么直接是聚合函数本身,要么是分组列的值,不能包含分组列之外的值
mysql> create table student(->  sid int,->  name varchar(20),->  sex char(6),->  score double,->  cid int-> );
Query OK, 0 rows affected (0.01 sec)
​
mysql> show tables;
+----------------+
| Tables_in_saas |
+----------------+
| student        |
| tb_stu         |
+----------------+
2 rows in set (0.00 sec)
​
mysql> ^C
mysql> insert into student values(100, 'wukong', 'male', 99, 1);
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into student values(101, 'wuneng', 'male', 59, 1);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(102, 'wujing', 'male', 60, 1);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(103, 'tangtang', 'male', 100, 1);
Query OK, 1 row affected (0.00 sec)
​
mysql>
mysql> insert into student values(104, 'baoyu', 'male', 17, 2);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(105, 'daiyu', 'female', 16, 2);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(106, 'baichai', 'female', 16, 2);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(107, 'xifeng', 'female', 27, 2);
Query OK, 1 row affected (0.00 sec)
​
mysql>
mysql> insert into student values(108, 'liubei', 'male', 20, 3);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(109, 'sunquan', 'male', 20, 3);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(110, 'caocao', 'male', 20, 3);
Query OK, 1 row affected (0.00 sec)
​
mysql>
mysql> insert into student values(111, 'songjiang', 'male', 20, 4);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(112, 'likui', 'male', 22, 4);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values(113, 'sunerniang', 'female', 20, 4);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from student;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  104 | baoyu      | male   |    17 |    2 |
|  105 | daiyu      | female |    16 |    2 |
|  106 | baichai    | female |    16 |    2 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
14 rows in set (0.00 sec)
​
mysql> select sum(score) from student;
+------------+
| sum(score) |
+------------+
|        516 |
+------------+
1 row in set (0.00 sec)
​
mysql> select avg(score) from student;
+--------------------+
| avg(score)         |
+--------------------+
| 36.857142857142854 |
+--------------------+
1 row in set (0.00 sec)
​
mysql> select max(score) from student;
+------------+
| max(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)
​
mysql> select min(score) from student;
+------------+
| min(score) |
+------------+
|         16 |
+------------+
1 row in set (0.00 sec)
​
mysql> select count(score) from student;
+--------------+
| count(score) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)
​
mysql> select max(score) from student group by sex;
+------------+
| max(score) |
+------------+
|         27 |
|        100 |
+------------+
2 rows in set (0.00 sec)
​
mysql> select sex, max(score) from student group by sex;
+--------+------------+
| sex    | max(score) |
+--------+------------+
| female |         27 |
| male   |        100 |
+--------+------------+
2 rows in set (0.00 sec)
​
mysql> select name, sex, max(score) from student group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'saas.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select sex, max(score) from student group by sex;
+--------+------------+
| sex    | max(score) |
+--------+------------+
| female |         27 |
| male   |        100 |
+--------+------------+
2 rows in set (0.00 sec)
​
mysql> select max(score) from student group by cid;
+------------+
| max(score) |
+------------+
|        100 |
|         27 |
|         20 |
|         22 |
+------------+
4 rows in set (0.00 sec)
​
mysql> select cid, max(score) from student group by cid;
+------+------------+
| cid  | max(score) |
+------+------------+
|    1 |        100 |
|    2 |         27 |
|    3 |         20 |
|    4 |         22 |
+------+------------+
4 rows in set (0.00 sec)
​
mysql> select cid, name, max(score) from student group by cid;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'saas.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

注意:分组查询中,select显示的列只能是分组依据列,或者聚合函数,不能出现其他列

分组过滤查询

语法:
select 列名 from 表名 where 条件 group by 分组列 having 过滤规则
关键字描述
having 过滤规则过滤规则定义对于分组后的数据进行过滤

统计不同班级的最高成绩:

select cid, max(score) from student group by cid;       //  查询各个班级中最高成绩
​
+------+------------+
| cid  | max(score) |
+------+------------+
|    1 |        100 |
|    2 |         27 |
|    3 |         20 |
|    4 |         22 |
+------+------------+
​
在这个结果之上查询最高成绩大于60分的结果
select cid, max(score) from student where max(score) > 60 group by cid; //报错,where不能对于聚合函数做进一步的判断
​
select cid, max(score) from student group by cid having max(score) > 60;    //  having后面可以跟过滤规则,这个过滤规则可以使用聚合函数

限定查询

select 列名 from 表名 limit 起始行,查询行数

关键字描述
limit offset_start, row_count限定查询结果的起始行和总行数
select * from student limit 0, 5;
select * from student limit 5, 5;
select * from student limit 10, 5;

注意:在分页中,起始行是变化的,但是每一页显示的条目数是固定不变的

mysql> select * from student;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  104 | baoyu      | male   |    17 |    2 |
|  105 | daiyu      | female |    16 |    2 |
|  106 | baichai    | female |    16 |    2 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
14 rows in set (0.00 sec)
​
mysql> select * from student limit 0, 5;
+------+----------+------+-------+------+
| sid  | name     | sex  | score | cid  |
+------+----------+------+-------+------+
|  100 | wukong   | male |    99 |    1 |
|  101 | wuneng   | male |    59 |    1 |
|  102 | wujing   | male |    60 |    1 |
|  103 | tangtang | male |   100 |    1 |
|  104 | baoyu    | male |    17 |    2 |
+------+----------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from student limit 5, 5;
+------+---------+--------+-------+------+
| sid  | name    | sex    | score | cid  |
+------+---------+--------+-------+------+
|  105 | daiyu   | female |    16 |    2 |
|  106 | baichai | female |    16 |    2 |
|  107 | xifeng  | female |    27 |    2 |
|  108 | liubei  | male   |    20 |    3 |
|  109 | sunquan | male   |    20 |    3 |
+------+---------+--------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from student limit 10, 5;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
4 rows in set (0.00 sec)
​
mysql> select * from student limit 3, 5;
+------+----------+--------+-------+------+
| sid  | name     | sex    | score | cid  |
+------+----------+--------+-------+------+
|  103 | tangtang | male   |   100 |    1 |
|  104 | baoyu    | male   |    17 |    2 |
|  105 | daiyu    | female |    16 |    2 |
|  106 | baichai  | female |    16 |    2 |
|  107 | xifeng   | female |    27 |    2 |
+------+----------+--------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from student limit 0, 6;
+------+----------+--------+-------+------+
| sid  | name     | sex    | score | cid  |
+------+----------+--------+-------+------+
|  100 | wukong   | male   |    99 |    1 |
|  101 | wuneng   | male   |    59 |    1 |
|  102 | wujing   | male   |    60 |    1 |
|  103 | tangtang | male   |   100 |    1 |
|  104 | baoyu    | male   |    17 |    2 |
|  105 | daiyu    | female |    16 |    2 |
+------+----------+--------+-------+------+
6 rows in set (0.00 sec)
​
mysql> select * from student limit 6, 6;
+------+-----------+--------+-------+------+
| sid  | name      | sex    | score | cid  |
+------+-----------+--------+-------+------+
|  106 | baichai   | female |    16 |    2 |
|  107 | xifeng    | female |    27 |    2 |
|  108 | liubei    | male   |    20 |    3 |
|  109 | sunquan   | male   |    20 |    3 |
|  110 | caocao    | male   |    20 |    3 |
|  111 | songjiang | male   |    20 |    4 |
+------+-----------+--------+-------+------+
6 rows in set (0.00 sec)
​
mysql> select * from student limit 12, 6;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
2 rows in set (0.00 sec)

查询总结

完整的SQL语句编写顺序:

select 列名 from 表名 where 条件 group by 分组 having 过滤条件 order by 排序列 [asc|desc] limit 起始行,总条目数

执行顺序

  1. from: 指定数据来源表

  2. where:对于查询数据做第一次过滤

  3. group by: 分组

  4. having:对于分组后的数据进行二次过滤

  5. select:查询各个字段的值

  6. order by:排序

  7. limit: 限定查询结果

子查询

将一个查询结果作为一条条件判断

select 列名 from 表名 where 条件(子查询结果)

查询比baoyu成绩更高的所有同学

mysql> select score from student where name = 'baoyu';
+-------+
| score |
+-------+
|    17 |
+-------+
1 row in set (0.00 sec)mysql> select * from student where score > 17;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
11 rows in set (0.00 sec)

现在用子查询将两条SQL语句合并

mysql> select * from student where score > (select score from student where name = 'baoyu');
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
11 rows in set (0.01 sec)

在查询中嵌套查询,内部的查询就被称之为子查询

将子查询的“一行一列”的结果作为外部条件,进行二次查询

子查询得到一行一列的结果才能作为外部查询的等值判断或者不等值条件判断

将查询结果作为枚举查询

mysql> select * from student;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  104 | baoyu      | male   |    17 |    2 |
|  105 | daiyu      | female |    16 |    2 |
|  106 | baichai    | female |    16 |    2 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
14 rows in set (0.00 sec)mysql> select cid from student where name = 'baoyu';
+------+
| cid  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
mysql> select * from student where cid in (2);
+------+---------+--------+-------+------+
| sid  | name    | sex    | score | cid  |
+------+---------+--------+-------+------+
|  104 | baoyu   | male   |    17 |    2 |
|  105 | daiyu   | female |    16 |    2 |
|  106 | baichai | female |    16 |    2 |
|  107 | xifeng  | female |    27 |    2 |
+------+---------+--------+-------+------+
4 rows in set (0.01 sec)mysql> select cid from student;
+------+
| cid  |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    2 |
|    2 |
|    2 |
|    2 |
|    3 |
|    3 |
|    3 |
|    4 |
|    4 |
|    4 |
+------+
14 rows in set (0.00 sec)mysql> select distinct cid from student;
+------+
| cid  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)mysql> select * from student where cid in (select distinct cid from student);
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  100 | wukong     | male   |    99 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  103 | tangtang   | male   |   100 |    1 |
|  104 | baoyu      | male   |    17 |    2 |
|  105 | daiyu      | female |    16 |    2 |
|  106 | baichai    | female |    16 |    2 |
|  107 | xifeng     | female |    27 |    2 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  112 | likui      | male   |    22 |    4 |
|  113 | sunerniang | female |    20 |    4 |
+------+------------+--------+-------+------+
14 rows in set (0.00 sec)

将子查询的“多行一列”的结果作为外部查询的枚举查询条件,做二次查询

案例:

查询比4班同学成绩高的同学

mysql> select score from student where cid = 4;
+-------+
| score |
+-------+
|    20 |
|    22 |
|    20 |
+-------+
3 rows in set (0.00 sec)mysql> select * from student where score > all(select score from student where cid = 4);
+------+----------+--------+-------+------+
| sid  | name     | sex    | score | cid  |
+------+----------+--------+-------+------+
|  100 | wukong   | male   |    99 |    1 |
|  101 | wuneng   | male   |    59 |    1 |
|  102 | wujing   | male   |    60 |    1 |
|  103 | tangtang | male   |   100 |    1 |
|  107 | xifeng   | female |    27 |    2 |
+------+----------+--------+-------+------+
5 rows in set (0.00 sec)mysql> select * from student where score > any(select score from student where cid = 4);
+------+----------+--------+-------+------+
| sid  | name     | sex    | score | cid  |
+------+----------+--------+-------+------+
|  100 | wukong   | male   |    99 |    1 |
|  101 | wuneng   | male   |    59 |    1 |
|  102 | wujing   | male   |    60 |    1 |
|  103 | tangtang | male   |   100 |    1 |
|  107 | xifeng   | female |    27 |    2 |
|  112 | likui    | male   |    22 |    4 |
+------+----------+--------+-------+------+
6 rows in set (0.00 sec)

注意:当子查询结果集形式为多行单列时可以使用any或者all关键字

将子查询作为一张表

select 列名 from 表名 from(子查询结果) where 条件

案例

查询成绩排名前五的所有同学

先查出来所有的同学的信息,按照成绩从高到底的排列顺序

mysql> select * from student order by score desc;
+------+------------+--------+-------+------+
| sid  | name       | sex    | score | cid  |
+------+------------+--------+-------+------+
|  103 | tangtang   | male   |   100 |    1 |
|  100 | wukong     | male   |    99 |    1 |
|  102 | wujing     | male   |    60 |    1 |
|  101 | wuneng     | male   |    59 |    1 |
|  107 | xifeng     | female |    27 |    2 |
|  112 | likui      | male   |    22 |    4 |
|  108 | liubei     | male   |    20 |    3 |
|  109 | sunquan    | male   |    20 |    3 |
|  110 | caocao     | male   |    20 |    3 |
|  111 | songjiang  | male   |    20 |    4 |
|  113 | sunerniang | female |    20 |    4 |
|  104 | baoyu      | male   |    17 |    2 |
|  105 | daiyu      | female |    16 |    2 |
|  106 | baichai    | female |    16 |    2 |
+------+------------+--------+-------+------+
14 rows in set (0.00 sec)
mysql> select * from (select * from student order by score desc) orderScore limit 0, 5;
+------+----------+--------+-------+------+
| sid  | name     | sex    | score | cid  |
+------+----------+--------+-------+------+
|  103 | tangtang | male   |   100 |    1 |
|  100 | wukong   | male   |    99 |    1 |
|  102 | wujing   | male   |    60 |    1 |
|  101 | wuneng   | male   |    59 |    1 |
|  107 | xifeng   | female |    27 |    2 |
+------+----------+--------+-------+------+
5 rows in set (0.00 sec)

注意:将子查询的“多行多列”的结果作为外查询的一张虚拟表,做二次查询

子查询作为临时表,要为其赋予一个临时表表名

合并查询

语法:

select * from 表1 union select * from 表2
select * from 表1 union all select * from 表2

合并两张表的结果

注意:合并结果的两张表,列数必须相同,列的数据类型可以不同

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)mysql> select * from student;
+------+------------+--------+-------+------+------+
| sid  | name       | sex    | score | cid  | age  |
+------+------------+--------+-------+------+------+
|  100 | wukong     | male   |    99 |    1 | NULL |
|  101 | wuneng     | male   |    59 |    1 | NULL |
|  102 | wujing     | male   |    60 |    1 | NULL |
|  103 | tangtang   | male   |   100 |    1 | NULL |
|  104 | baoyu      | male   |    17 |    2 | NULL |
|  105 | daiyu      | female |    16 |    2 | NULL |
|  106 | baichai    | female |    16 |    2 | NULL |
|  107 | xifeng     | female |    27 |    2 | NULL |
|  108 | liubei     | male   |    20 |    3 | NULL |
|  109 | sunquan    | male   |    20 |    3 | NULL |
|  110 | caocao     | male   |    20 |    3 | NULL |
|  111 | songjiang  | male   |    20 |    4 | NULL |
|  112 | likui      | male   |    22 |    4 | NULL |
|  113 | sunerniang | female |    20 |    4 | NULL |
+------+------------+--------+-------+------+------+
14 rows in set (0.00 sec)mysql> select * from tb_stu union select * from student;
+------+--------------+--------+------+-------+------+
| sid  | name         | sex    | tel  | score | age  |
+------+--------------+--------+------+-------+------+
| 9527 | zhouxingxing | boy    | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl   | 110  |    80 |   18 |
| 9529 | shiliu       | girl   | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl   | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy    | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL   | NULL |  NULL | NULL |
|  100 | wukong       | male   | 99   |     1 | NULL |
|  101 | wuneng       | male   | 59   |     1 | NULL |
|  102 | wujing       | male   | 60   |     1 | NULL |
|  103 | tangtang     | male   | 100  |     1 | NULL |
|  104 | baoyu        | male   | 17   |     2 | NULL |
|  105 | daiyu        | female | 16   |     2 | NULL |
|  106 | baichai      | female | 16   |     2 | NULL |
|  107 | xifeng       | female | 27   |     2 | NULL |
|  108 | liubei       | male   | 20   |     3 | NULL |
|  109 | sunquan      | male   | 20   |     3 | NULL |
|  110 | caocao       | male   | 20   |     3 | NULL |
|  111 | songjiang    | male   | 20   |     4 | NULL |
|  112 | likui        | male   | 22   |     4 | NULL |
|  113 | sunerniang   | female | 20   |     4 | NULL |
+------+--------------+--------+------+-------+------+
20 rows in set (0.00 sec)mysql> desc tb_stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(4)     | YES  |     | NULL    |       |
| tel   | char(11)    | YES  |     | NULL    |       |
| score | double      | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(6)     | YES  |     | NULL    |       |
| score | double      | YES  |     | NULL    |       |
| cid   | int(11)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> select * from student union select * from tb_stu;
+------+--------------+--------+-------+------+------+
| sid  | name         | sex    | score | cid  | age  |
+------+--------------+--------+-------+------+------+
|  100 | wukong       | male   | 99    |    1 | NULL |
|  101 | wuneng       | male   | 59    |    1 | NULL |
|  102 | wujing       | male   | 60    |    1 | NULL |
|  103 | tangtang     | male   | 100   |    1 | NULL |
|  104 | baoyu        | male   | 17    |    2 | NULL |
|  105 | daiyu        | female | 16    |    2 | NULL |
|  106 | baichai      | female | 16    |    2 | NULL |
|  107 | xifeng       | female | 27    |    2 | NULL |
|  108 | liubei       | male   | 20    |    3 | NULL |
|  109 | sunquan      | male   | 20    |    3 | NULL |
|  110 | caocao       | male   | 20    |    3 | NULL |
|  111 | songjiang    | male   | 20    |    4 | NULL |
|  112 | likui        | male   | 22    |    4 | NULL |
|  113 | sunerniang   | female | 20    |    4 | NULL |
| 9527 | zhouxingxing | boy    | 119   |   99 |   20 |
| 9528 | qiuxiang     | girl   | 110   |   80 |   18 |
| 9529 | shiliu       | girl   | 114   |   59 |   30 |
| 9530 | qiuxiang     | girl   | 110   |   80 |   18 |
| 9531 | zhuzhishan   | boy    | 112   |   77 |   22 |
| 9532 | chunxiang    | NULL   | NULL  | NULL | NULL |
+------+--------------+--------+-------+------+------+
20 rows in set (0.00 sec)mysql> select * from student union select * from student;
+------+------------+--------+-------+------+------+
| sid  | name       | sex    | score | cid  | age  |
+------+------------+--------+-------+------+------+
|  100 | wukong     | male   |    99 |    1 | NULL |
|  101 | wuneng     | male   |    59 |    1 | NULL |
|  102 | wujing     | male   |    60 |    1 | NULL |
|  103 | tangtang   | male   |   100 |    1 | NULL |
|  104 | baoyu      | male   |    17 |    2 | NULL |
|  105 | daiyu      | female |    16 |    2 | NULL |
|  106 | baichai    | female |    16 |    2 | NULL |
|  107 | xifeng     | female |    27 |    2 | NULL |
|  108 | liubei     | male   |    20 |    3 | NULL |
|  109 | sunquan    | male   |    20 |    3 | NULL |
|  110 | caocao     | male   |    20 |    3 | NULL |
|  111 | songjiang  | male   |    20 |    4 | NULL |
|  112 | likui      | male   |    22 |    4 | NULL |
|  113 | sunerniang | female |    20 |    4 | NULL |
+------+------------+--------+-------+------+------+
14 rows in set (0.00 sec)mysql> select * from student union all select * from student;
+------+------------+--------+-------+------+------+
| sid  | name       | sex    | score | cid  | age  |
+------+------------+--------+-------+------+------+
|  100 | wukong     | male   |    99 |    1 | NULL |
|  101 | wuneng     | male   |    59 |    1 | NULL |
|  102 | wujing     | male   |    60 |    1 | NULL |
|  103 | tangtang   | male   |   100 |    1 | NULL |
|  104 | baoyu      | male   |    17 |    2 | NULL |
|  105 | daiyu      | female |    16 |    2 | NULL |
|  106 | baichai    | female |    16 |    2 | NULL |
|  107 | xifeng     | female |    27 |    2 | NULL |
|  108 | liubei     | male   |    20 |    3 | NULL |
|  109 | sunquan    | male   |    20 |    3 | NULL |
|  110 | caocao     | male   |    20 |    3 | NULL |
|  111 | songjiang  | male   |    20 |    4 | NULL |
|  112 | likui      | male   |    22 |    4 | NULL |
|  113 | sunerniang | female |    20 |    4 | NULL |
|  100 | wukong     | male   |    99 |    1 | NULL |
|  101 | wuneng     | male   |    59 |    1 | NULL |
|  102 | wujing     | male   |    60 |    1 | NULL |
|  103 | tangtang   | male   |   100 |    1 | NULL |
|  104 | baoyu      | male   |    17 |    2 | NULL |
|  105 | daiyu      | female |    16 |    2 | NULL |
|  106 | baichai    | female |    16 |    2 | NULL |
|  107 | xifeng     | female |    27 |    2 | NULL |
|  108 | liubei     | male   |    20 |    3 | NULL |
|  109 | sunquan    | male   |    20 |    3 | NULL |
|  110 | caocao     | male   |    20 |    3 | NULL |
|  111 | songjiang  | male   |    20 |    4 | NULL |
|  112 | likui      | male   |    22 |    4 | NULL |
|  113 | sunerniang | female |    20 |    4 | NULL |
+------+------------+--------+-------+------+------+
28 rows in set (0.00 sec)mysql> select * from tb_stu union all select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
12 rows in set (0.00 sec)mysql> select * from tb_stu union select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

经验:union合并结果集,会去除掉两张表中重复的数据

表连接

准备:

create table clazz(cid int,cname varchar(20),cdesc varchar(20)
);insert into clazz values(1, 'computer', 'computer desc');
insert into clazz values(2, 'software', 'software desc');
insert into clazz values(3, 'math', 'math desc');
insert into clazz values(4, 'biological', 'biological desc');
insert into clazz values(5, 'education', 'education desc');

语法:

select 列名 from 表1 连接方式 表2 on 连接条件

内连接

select * from student s inner join clazz c on s.cid = c.cid
mysql> select * from student s inner join clazz c on s.cid = c.cid;
+------+------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name       | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong     | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan    | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang  | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui      | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang | female |    20 |    4 | NULL |    4 | biological | biological desc |
+------+------------+--------+-------+------+------+------+------------+-----------------+
14 rows in set (0.00 sec)

左外连接

select * from student s left outer join clazz c on s.cid = c.cid
mysql> select * from student s left outer join clazz c on s.cid = c.cid;
+------+--------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name         | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+--------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong       | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng       | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing       | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang     | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu        | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu        | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng       | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei       | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan      | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao       | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang    | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui        | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang   | female |    20 |    4 | NULL |    4 | biological | biological desc |
|  114 | zhouhuaqiang | male   |    50 |    9 |   30 | NULL | NULL       | NULL            |
+------+--------------+--------+-------+------+------+------+------------+-----------------+
15 rows in set (0.00 sec)mysql> select * from student s left join clazz c on s.cid = c.cid;
+------+--------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name         | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+--------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong       | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng       | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing       | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang     | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu        | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu        | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng       | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei       | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan      | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao       | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang    | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui        | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang   | female |    20 |    4 | NULL |    4 | biological | biological desc |
|  114 | zhouhuaqiang | male   |    50 |    9 |   30 | NULL | NULL       | NULL            |
+------+--------------+--------+-------+------+------+------+------------+-----------------+
15 rows in set (0.00 sec)	

左外连接left outer join,也可以简写为left join

以左表为基准,查找右表,找到了则显示右表,没找到右表里面的所有信息为null

mysql> select * from student s right outer join clazz c on s.cid = c.cid;
+------+------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name       | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong     | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan    | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang  | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui      | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang | female |    20 |    4 | NULL |    4 | biological | biological desc |
| NULL | NULL       | NULL   |  NULL | NULL | NULL |    5 | education  | education desc  |
+------+------------+--------+-------+------+------+------+------------+-----------------+
15 rows in set (0.00 sec)mysql> select * from student s right join clazz c on s.cid = c.cid;
+------+------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name       | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong     | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan    | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang  | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui      | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang | female |    20 |    4 | NULL |    4 | biological | biological desc |
| NULL | NULL       | NULL   |  NULL | NULL | NULL |    5 | education  | education desc  |
+------+------------+--------+-------+------+------+------+------------+-----------------+
15 rows in set (0.00 sec)

6*-右外连接right outer join, 也可以简写为right join

以右表为基准,查找坐标,找到了则显示左表,没找到左表里面的所有信息为null

笛卡尔积:

笛卡尔积是两张表所有的记录的乘积

mysql> select * from clazz;
+------+------------+-----------------+
| cid  | cname      | cdesc           |
+------+------------+-----------------+
|    1 | computer   | computer desc   |
|    2 | software   | software desc   |
|    3 | math       | math desc       |
|    4 | biological | biological desc |
|    5 | education  | education desc  |
+------+------------+-----------------+
5 rows in set (0.00 sec)
​
mysql> select * from student;
+------+------------+--------+-------+------+------+
| sid  | name       | sex    | score | cid  | age  |
+------+------------+--------+-------+------+------+
|  100 | wukong     | male   |    99 |    1 | NULL |
|  101 | wuneng     | male   |    59 |    1 | NULL |
|  102 | wujing     | male   |    60 |    1 | NULL |
|  103 | tangtang   | male   |   100 |    1 | NULL |
|  104 | baoyu      | male   |    17 |    2 | NULL |
|  105 | daiyu      | female |    16 |    2 | NULL |
|  106 | baichai    | female |    16 |    2 | NULL |
|  107 | xifeng     | female |    27 |    2 | NULL |
|  108 | liubei     | male   |    20 |    3 | NULL |
|  109 | sunquan    | male   |    20 |    3 | NULL |
|  110 | caocao     | male   |    20 |    3 | NULL |
|  111 | songjiang  | male   |    20 |    4 | NULL |
|  112 | likui      | male   |    22 |    4 | NULL |
|  113 | sunerniang | female |    20 |    4 | NULL |
+------+------------+--------+-------+------+------+
14 rows in set (0.00 sec)
mysql> select * from student s, clazz c;
+------+------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name       | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong     | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  100 | wukong     | male   |    99 |    1 | NULL |    2 | software   | software desc   |
|  100 | wukong     | male   |    99 |    1 | NULL |    3 | math       | math desc       |
|  100 | wukong     | male   |    99 |    1 | NULL |    4 | biological | biological desc |
|  100 | wukong     | male   |    99 |    1 | NULL |    5 | education  | education desc  |
|  101 | wuneng     | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    2 | software   | software desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    3 | math       | math desc       |
|  101 | wuneng     | male   |    59 |    1 | NULL |    4 | biological | biological desc |
|  101 | wuneng     | male   |    59 |    1 | NULL |    5 | education  | education desc  |
|  102 | wujing     | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    2 | software   | software desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    3 | math       | math desc       |
|  102 | wujing     | male   |    60 |    1 | NULL |    4 | biological | biological desc |
|  102 | wujing     | male   |    60 |    1 | NULL |    5 | education  | education desc  |
|  103 | tangtang   | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    2 | software   | software desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    3 | math       | math desc       |
|  103 | tangtang   | male   |   100 |    1 | NULL |    4 | biological | biological desc |
|  103 | tangtang   | male   |   100 |    1 | NULL |    5 | education  | education desc  |
|  104 | baoyu      | male   |    17 |    2 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    3 | math       | math desc       |
|  104 | baoyu      | male   |    17 |    2 | NULL |    4 | biological | biological desc |
|  104 | baoyu      | male   |    17 |    2 | NULL |    5 | education  | education desc  |
|  105 | daiyu      | female |    16 |    2 | NULL |    1 | computer   | computer desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    3 | math       | math desc       |
|  105 | daiyu      | female |    16 |    2 | NULL |    4 | biological | biological desc |
|  105 | daiyu      | female |    16 |    2 | NULL |    5 | education  | education desc  |
|  106 | baichai    | female |    16 |    2 | NULL |    1 | computer   | computer desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    3 | math       | math desc       |
|  106 | baichai    | female |    16 |    2 | NULL |    4 | biological | biological desc |
|  106 | baichai    | female |    16 |    2 | NULL |    5 | education  | education desc  |
|  107 | xifeng     | female |    27 |    2 | NULL |    1 | computer   | computer desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    3 | math       | math desc       |
|  107 | xifeng     | female |    27 |    2 | NULL |    4 | biological | biological desc |
|  107 | xifeng     | female |    27 |    2 | NULL |    5 | education  | education desc  |
|  108 | liubei     | male   |    20 |    3 | NULL |    1 | computer   | computer desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    2 | software   | software desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  108 | liubei     | male   |    20 |    3 | NULL |    4 | biological | biological desc |
|  108 | liubei     | male   |    20 |    3 | NULL |    5 | education  | education desc  |
|  109 | sunquan    | male   |    20 |    3 | NULL |    1 | computer   | computer desc   |
|  109 | sunquan    | male   |    20 |    3 | NULL |    2 | software   | software desc   |
|  109 | sunquan    | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan    | male   |    20 |    3 | NULL |    4 | biological | biological desc |
|  109 | sunquan    | male   |    20 |    3 | NULL |    5 | education  | education desc  |
|  110 | caocao     | male   |    20 |    3 | NULL |    1 | computer   | computer desc   |
|  110 | caocao     | male   |    20 |    3 | NULL |    2 | software   | software desc   |
|  110 | caocao     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao     | male   |    20 |    3 | NULL |    4 | biological | biological desc |
|  110 | caocao     | male   |    20 |    3 | NULL |    5 | education  | education desc  |
|  111 | songjiang  | male   |    20 |    4 | NULL |    1 | computer   | computer desc   |
|  111 | songjiang  | male   |    20 |    4 | NULL |    2 | software   | software desc   |
|  111 | songjiang  | male   |    20 |    4 | NULL |    3 | math       | math desc       |
|  111 | songjiang  | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  111 | songjiang  | male   |    20 |    4 | NULL |    5 | education  | education desc  |
|  112 | likui      | male   |    22 |    4 | NULL |    1 | computer   | computer desc   |
|  112 | likui      | male   |    22 |    4 | NULL |    2 | software   | software desc   |
|  112 | likui      | male   |    22 |    4 | NULL |    3 | math       | math desc       |
|  112 | likui      | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui      | male   |    22 |    4 | NULL |    5 | education  | education desc  |
|  113 | sunerniang | female |    20 |    4 | NULL |    1 | computer   | computer desc   |
|  113 | sunerniang | female |    20 |    4 | NULL |    2 | software   | software desc   |
|  113 | sunerniang | female |    20 |    4 | NULL |    3 | math       | math desc       |
|  113 | sunerniang | female |    20 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang | female |    20 |    4 | NULL |    5 | education  | education desc  |
+------+------------+--------+-------+------+------+------+------------+-----------------+
70 rows in set (0.00 sec)

第一张表14条记录,第二表5条记录,最终笛卡尔积的总条目是14*5 = 70.

通过运行结果我们发现,笛卡尔积是第一张表的每一条记录都与第二张表分别关联

等值查询

mysql> select * from student;
+------+--------------+--------+-------+------+------+
| sid  | name         | sex    | score | cid  | age  |
+------+--------------+--------+-------+------+------+
|  100 | wukong       | male   |    99 |    1 | NULL |
|  101 | wuneng       | male   |    59 |    1 | NULL |
|  102 | wujing       | male   |    60 |    1 | NULL |
|  103 | tangtang     | male   |   100 |    1 | NULL |
|  104 | baoyu        | male   |    17 |    2 | NULL |
|  105 | daiyu        | female |    16 |    2 | NULL |
|  106 | baichai      | female |    16 |    2 | NULL |
|  107 | xifeng       | female |    27 |    2 | NULL |
|  108 | liubei       | male   |    20 |    3 | NULL |
|  109 | sunquan      | male   |    20 |    3 | NULL |
|  110 | caocao       | male   |    20 |    3 | NULL |
|  111 | songjiang    | male   |    20 |    4 | NULL |
|  112 | likui        | male   |    22 |    4 | NULL |
|  113 | sunerniang   | female |    20 |    4 | NULL |
|  114 | zhouhuaqiang | male   |    50 |    9 |   30 |
+------+--------------+--------+-------+------+------+
15 rows in set (0.00 sec)
mysql> select * from clazz;
+------+------------+-----------------+
| cid  | cname      | cdesc           |
+------+------------+-----------------+
|    1 | computer   | computer desc   |
|    2 | software   | software desc   |
|    3 | math       | math desc       |
|    4 | biological | biological desc |
|    5 | education  | education desc  |
+------+------------+-----------------+
5 rows in set (0.00 sec)
mysql> select * from student s, clazz c where c.cid = s.cid;
+------+------------+--------+-------+------+------+------+------------+-----------------+
| sid  | name       | sex    | score | cid  | age  | cid  | cname      | cdesc           |
+------+------------+--------+-------+------+------+------+------------+-----------------+
|  100 | wukong     | male   |    99 |    1 | NULL |    1 | computer   | computer desc   |
|  101 | wuneng     | male   |    59 |    1 | NULL |    1 | computer   | computer desc   |
|  102 | wujing     | male   |    60 |    1 | NULL |    1 | computer   | computer desc   |
|  103 | tangtang   | male   |   100 |    1 | NULL |    1 | computer   | computer desc   |
|  104 | baoyu      | male   |    17 |    2 | NULL |    2 | software   | software desc   |
|  105 | daiyu      | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  106 | baichai    | female |    16 |    2 | NULL |    2 | software   | software desc   |
|  107 | xifeng     | female |    27 |    2 | NULL |    2 | software   | software desc   |
|  108 | liubei     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  109 | sunquan    | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  110 | caocao     | male   |    20 |    3 | NULL |    3 | math       | math desc       |
|  111 | songjiang  | male   |    20 |    4 | NULL |    4 | biological | biological desc |
|  112 | likui      | male   |    22 |    4 | NULL |    4 | biological | biological desc |
|  113 | sunerniang | female |    20 |    4 | NULL |    4 | biological | biological desc |
+------+------------+--------+-------+------+------+------+------------+-----------------+
14 rows in set (0.00 sec)

等值查询:条件中分别指定了两张表的关联关系

第一张表中114学生的cid是9

第二张表中5号班级在学生表中没有记录

所以最终等值查询只能得到14条记录

其中114的学生不会出现,以及cid为5的班级不会出现

约束

问题:在往数据库中创建好的表里面添加数据时,可不可以新增两行相同列值的数据

如果可行,会有什么弊端

  • 实体完整性约束

    • 表中的一行数据代表一个实体,实体完整性约束的作用时标志每一行的数据时不重复的,实体唯一

    • 主键约束:primary key,唯一,标识表中的一行数据,此列数据的值不可重复,且不能为null

      • create table tb_stu2(sid int primary key,name varchar(20),sex varchar(6),score double,age int
        );insert into tb_stu2 values(9527, 'huaan', 'male', 99, 20);
        insert into tb_stu2 values(9528, 'qiuxiang', 'female', 88, 18);
        insert into tb_stu2 values(9528, 'qiuxiang', 'female', 88, 18);			//X,sid为主键,不能重复
        insert into tb_stu2 values(null, 'chunxiang', 'female', 88, 18);		//X,sid为主键,不能为空null
    • 唯一约束:unique,唯一,标识表中的 一行数据,不能重复,可以为null

      • create table subject(sid int primary key,subName varchar(20) unique,subHour	int
        );insert into subject values(1, "java", 100);
        insert into subject values(2, "python", 50);
        insert into subject values(3, "java", 100);					//	X,不能添加相同的值给uniqueu约束的字段
        insert into subject values(4, , 100);
      • mysql中唯一约束时值互不相同,可以添加null作为其值,而且可以添加多个null

    • 自动增长列

      • auto_increment, 自动增长,给主键数值列添加自动增长,从1开始,每次加1,不能单独使用

      • create table subject02(sid int primary key auto_increment,subName varchar(20) unique,subHour	int
        );insert into subject02 values(1, "java", 100);
        insert into subject02 values(2, "pytyon", 90);
        insert into subject02 values(null, "ui", 80);
        insert into subject02(subname, subhour) values("bigdata", 80);
        insert into subject02(subname, subhour) values("bigdata99999999999999", 80);			//	X
        insert into subject02(subname, subhour) values("chat", 80);								//	有些版本会出现跳格

http://www.ppmy.cn/news/1439826.html

相关文章

Python编程----递归求解兔子的数量

描述 兔子的数量以这样的方式增长:每个月的兔子数量等于它前一个月的兔子数量加它前两个月的兔子数量,即f(n)f(n-1)f(n-2)。假设第1个月的兔子有2只,第2个月的兔子有3只,你能使用递归的方法求得第n个月的兔子有多少只吗&#xff…

SN75107BDR 总线接收器 中文资料_PDF中文资料_参数_引脚图

SN75107BDR 规格信息: 制造商:Texas Instruments 产品种类:总线接收器 RoHS:是 接收机数量:2 Receiver 接收机信号类型:Differential 电源电压-最小:/- 4.75 V 电源电压-最大:/- 5.25 V 工作电源电流:30 mA 最小工作温度:0 C 最大工作温度: 70 C 封装 / 箱…

【深度学习】烟雾和火焰数据集,野外数据集,超大量数据集,目标检测,YOLOv5

标注了2w张数据集,是目标检测yolo格式的,有火焰、烟雾两个目标,下图是训练时候的样子: 训练方法看这里: https://qq742971636.blog.csdn.net/article/details/138097481 数据集介绍 都是博主辛苦整理和标注的&…

Esp8266 - USB开关分享(开源)

文章目录 简介推广自己gitee项目地址:嘉立创项目地址:联系我们 功能演示视频原理图嘉立创PCB开源地址原理图PCB预览 固件烧录代码编译烧录1. 软件和驱动安装2. 代码编译1. 安装所需要的依赖库文件2. 下载源代码3. 烧录代码 使用说明1. 设备配网2. 打开设备操作页面3…

深入OceanBase内部机制:分区机制构建高可用、高性能的分布式数据库基石

码到三十五 : 个人主页 在数据库技术的发展历程中,随着数据量的不断增长和业务需求的日益复杂,如何高效地存储、查询和处理数据成为了关键挑战。OceanBase作为一款高性能、高可用的分布式关系数据库,通过其独特的分区机制&#xf…

03 后端入参校验:自定义注解实现

03 后端入参校验:自定义注解实现 一、前言二、实现1、新建Spring Boot项目2、引入依赖3、新建注解类4、新建校验器5、全局异常处理器6、编写Controller7、新建实体类8、启动并测试 一、前言 在 Java 后端开发中,为了实现入参校验,常常会使用…

Python中的多点坐标

Python中的多点坐标 在Python中,多点坐标通常表示为一组元组或列表的列表,其中每个内部列表或元组表示一个点的坐标。这些坐标可以是二维的(x, y),三维的(x, y, z),或者更高维度的&a…

【学习】软件测试自动化,是未来的趋势还是当前的必需

在当今快速迭代的软件开发周期中,速度和质量成为了企业生存的关键。随着DevOps实践的普及和持续集成/持续部署(CI/CD)流程的标准化,软件测试自动化已经从未来的趋势转变为当前的必要性。本文将探讨自动化测试的现状、必要性以及其…