Java基础入门day40

embedded/2024/11/15 3:56:31/

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/embedded/18820.html

相关文章

结构化查询语言(SQL)的作用

结构化查询语言(SQL)的作用 结构化查询语言(SQL)是一种专门用来管理关系型数据库的语言。它具有以下重要作用: 1. 数据库管理 SQL被广泛应用于数据库管理,包括创建数据库、表和视图,定义数据…

【Flutter 面试题】 Dart 当中的 .. 表示什么?

【Flutter 面试题】 Dart 当中的 … 表示什么? 文章目录 写在前面口述回答补充说明写在前面 🙋 关于我 ,小雨青年 👉 CSDN博客专家,GitChat专栏作者,阿里云社区专家博主,51CTO专家博主。2023博客之星TOP153。 👏🏻 正在学 Flutter 的同学,你好! 😊 Flutter…

SpringBoot---------整合Redis

目录 第一步:引入依赖 第二步:配置Redis信息 第三步:选择Spring Data Redis进行操作Redis数据库 ①操作String类型数据(用的少) ②操作Object类型数据(重要!!!&#x…

村集体建设用地,开发乡村旅游项目,土地如何审批?

以村集体建设用地,开发乡村旅游项目,土地如何审批? 乡村,作为承载乡村旅游产业的载体,在乡村振兴中扮演着非常重要的角色。 项目的落地,可靠的土地是必要的前提。集体建设用地如何审批?农转非又需要什么样的流程,具体…

企业微信私有化部署对接oauth2.0

1.添加依赖&#xff1a;JustAuth <dependency><groupId>me.zhyd.oauth</groupId><artifactId>JustAuth</artifactId><version>1.16.6</version> </dependency> 2.添加 ElephantAuthSource.java package com.elephant.devop…

缓存相关问题:雪崩、穿透、预热、更新、降级的深度解析

✨✨祝屏幕前的小伙伴们每天都有好运相伴左右✨✨ &#x1f388;&#x1f388;作者主页&#xff1a; 喔的嘛呀&#x1f388;&#x1f388; 目录 1. 缓存雪崩 1.1 问题描述 1.2 解决方案 1.2.1 加锁防止并发重建缓存 2. 缓存穿透 2.1 问题描述 2.2 解决方案 2.2.1 布隆过…

Laravel 6 - 第十一章 中间件

​ 文章目录 Laravel 6 - 第一章 简介 Laravel 6 - 第二章 项目搭建 Laravel 6 - 第三章 文件夹结构 Laravel 6 - 第四章 生命周期 Laravel 6 - 第五章 控制反转和依赖注入 Laravel 6 - 第六章 服务容器 Laravel 6 - 第七章 服务提供者 Laravel 6 - 第八章 门面 Laravel 6 - …

感觉眩晕就以为高血压犯了?如果伴有耳鸣,请警惕这种疾病

高血压被称为“无形杀手”&#xff0c;我国调查研究显示每3位成年人中就有1位高血压患者&#xff0c;典型表现为头痛、心悸耳鸣等&#xff0c;偶尔也存在阵发性眩晕等不典型表现。当出现耳鸣、眩晕等症状时切忌想当然以为是高血压&#xff0c;尤其是耳鸣和眩晕两者同时出现而且…