##mysql 进阶(二)
###多表联合查询
- 什么是多表联合查询
- 前面所讲的查询语句都是针对一个表的,但是在关系型数据库中,表与表之间是有联系的,所以在实际应用中,经常使用多表查询。多表查询就是同时查询两个或两个以上的表。
- 在 MySQL 中,多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。
###交叉连接
- ####笛卡尔积
- 交叉连接(CROSS JOIN):有两种,显式的和隐式的2种,一般用来返回连接表的笛卡尔积。
- 笛卡尔积(笛卡尔积)是指两个集合 X 和 Y 的乘积。
例如,有 A 和 B 两个集合,它们的值如下:
A = {1,2}
B = {3,4,5}
集合 A×B 和 B×A 的结果集分别表示为:
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
并且,从以上结果我们可以看出:
- 两个集合相乘,不满足交换率,即 A×B≠B×A。
- A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积,表与表之间的连接可以看成是在做乘法运算。在实际应用中,应避免使用笛卡尔积,因为笛卡尔积中容易存在大量的不合理数据,简单来说就是容易导致查询结果重复、混乱。
- 查看数据库 创建数据库 进入创建的数据库 数据库内创建表
mysql> show databases; //查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database WWW; //创建一个WWW数据库
Query OK, 1 row affected (0.01 sec)
mysql> show databases; //成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| WWW |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use WWW //进入WWW数据库
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(40),age tinyint,sex varchar(4),height float,course_id int) DEFAULT CHARACTER SET utf8 COLLATE utfe8_general_ci; //创建tb_students_info表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_WWW |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
//创建tb_students_info表
然后查看字符集 修改字符集为utf8
mysql> create table tb_students_info; //查看字符集
ERROR 1113 (42000): A table must have at least 1 column
mysql> show create table tb_students_info;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_students_info | CREATE TABLE `tb_students_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(40) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` varchar(4) DEFAULT NULL,
`height` float DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table tb_students_info CHARSET = 'utf8'; //修改字符集为utf8
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tb_students_info;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_students_info | CREATE TABLE `tb_students_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(40) CHARACTER SET latin1 DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` varchar(4) CHARACTER SET latin1 DEFAULT NULL,
`height` float DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
- 查看表结构
mysql> desc tb_students_info; //查看tb_students_info 表结构
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| height | float | YES | | NULL | |
| course_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
插入表数据并查看
mysql> insert tb_students_info (name,age,sex,height,course_id) values('hjd',25,'男',160,1),('zsll',23,'男',158,2),('laj',23,'女',185,1),('lxy',22,'男',162,3),('kym',24,'女',175,2),('lx',21,'男',172,4),('ljl',22,'男',165,4),('zm',23,'男',170,5),('yjq',22,'女',178,5),('yc',23,'女',165,5)
);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+------+------+------+--------+-----------+
| 1 | hjd | 25 | 男 | 160 | 1 |
| 2 | zsl | 23 | 男 | 158 | 2 |
| 3 | laj | 23 | 女 | 185 | 1 |
| 4 | lxy | 22 | 男 | 162 | 3 |
| 5 | kym | 24 | 女 | 175 | 2 |
| 6 | lx | 21 | 男 | 172 | 4 |
| 7 | ljl | 22 | 男 | 165 | 4 |
| 8 | zm | 23 | 男 | 170 | 5 |
| 9 | yjq | 22 | 女 | 178 | 5 |
| 10 | yc | 23 | 女 | 165 | 5 |
+----+------+------+------+--------+-----------+
10 rows in set (0.00 sec)
创建第二个表
mysql> show tables;
+------------------+
| Tables_in_WWW |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50))); //创建tb_course表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_WWW |
+------------------+
| tb_course |
| tb_students_info |
+------------------&