💕7、Mysql的连表查询

mysql的连表查询

字符集的介绍

字符编码：现在能够看到的字符串 数字 特殊符号，都是字符编码表提供的

字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。


磁盘存储数据是二进制的数据，在系统的最底层，二进制的数据就是0 1，二进制的我们看不懂，最底层有一个ACS码表，经过他的转换，二进制的数据就变成我们能够看懂的字符串
ACS码表多大呢，就一页，不指定字符集，就会乱码

如果软件想要对国外提供服务，开发语言把接收到的数据转化为json,叫序列化
json转化为其他语言，叫反序列化
如果用万国编码开发，开发出来的程序特别大

2.MySQL数据库的字符集
     1）字符集（CHARACTER）
     2）校对规则（COLLATION）

3.MySQL中常见的字符集
     1）UTF8
     2）LATIN1
     3）GBK
     
4.可以使用以下命令查看
     mysql> show charset;
     mysql> show collation：

5.常见校对规则
     Collation 的列  ci结尾：大小写不敏感
                    cs或bin结尾：大小写敏感

字符集的设置

#字符集需要统一
1、需要设置远程连接工具的字符集

2、查看操作系统设置字符集
[root@db01 ~]# echo SLANG
SLANG

## 临时修改系统字符集
[root@db04 ~]# export LANG=en_US.UTF-8

## 永久修改 CentOS7
[root@db04 ~]# vim /etc/locale.conf
LANG="en_US.UTF-8"

## 永久修改 CentOS6
[root@db04 ~]# vim /etc/sysconfig/i18n
LANG="en_US.UTF-8"

3、mysql服务字符集的修改
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8

#想知道我们库里面哪些表不是utf8
root@localhost:(none) > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | #这个库存元数据，在磁盘上看不见

select * from information_schema.tables where table_collation not like '%utf8%';

库表修改字符集

root@localhost:(none) > show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

1、#修改字符集
mysql> alter database mysql charset utf8;

2、#查看所有库里面哪些表的字符集不是UTF8的
select * from information_schema.tables where table_collation not like '%utf8%';

3、# 修改单张表字符集
mysql> alter table zls.test111 charset utf8;

#批量修改表的字符集
可以把表名取出来，放到文件里面，循环文件里面的内容去修改
## 方法一：
[root@db04 ~]# for n in `seq 7`;do mysql -uroot -p123 -e "alter table
zabbix.zabbix$n charset utf8" ;done

导出数据库，编辑导出的数据文件，编辑数据文件，把字符集修改，再导入数据
# 方法二：
[root@db04 ~]# mysqldump -uroot -p123 -B zabbix > /tmp/zabbix.sql
[root@db04 ~]# vim /tmp/zabbix.sql
:%s#CHARSET=latin1#CHARSET=utf8#g
[root@db04 ~]# sed -i 's#CHARSET=latin1#CHARSET=utf8#g' /tmp/zabbix.sql
[root@db04 ~]# mysql -uroot -p123 < /tmp/zabbix.sql

字符集不够用就改，把小的字符集往大改

select 连表查询 （多表连查）

1     2      3
[li4,zhang3,wang5]  #学生表

 1   2  3
[80,90,100]    #成绩表

 2    1   3
[语文,数学,英语]  #学科表


root@localhost:(none) > use aaa
root@localhost:aaa > create table stu(id int,name varchar(10));
root@localhost:aaa > insert into stu values(1,'lis4'),(2,'zhang3'),(3,'wang5');
root@localhost:aaa > select * from stu;
+------+--------+
| id   | name   |
+------+--------+
|    1 | lis4   |
|    2 | zhang3 |
|    3 | wang5  |
+------+--------+

root@localhost:aaa > create table mark(id int,mark tinyint unsigned);
root@localhost:aaa > insert into mark values(1,80),(2,90),(3,100);
root@localhost:aaa > select * from mark;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+

#查询张三的成绩
名字：stu  name
分数：mark mark

root@localhost:aaa > select stu.name,mark.mark from stu,mark where stu.id=mark.id and stu.name='zhang3';   #这种方式叫传统连接
+--------+------+
| name   | mark |
+--------+------+
| zhang3 |   90 |
+--------+------+     

select 传统连接

1、先查看表结构
root@localhost:world > desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

root@localhost:world > desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       |
| Population     | int(11)                                                                               | NO   |     | 0       |       |
| LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |       |
| GNP            | float(10,2)                                                                           | YES  |     | NULL    |       |
| GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int(11)                                                                               | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+

root@localhost:world > desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+


2、#查询世界上小于100人的人口城市是哪个国家的？
找到你要的字段
国家                   城市            城市人口数          
country.name         city.name        city.population

select country.name,city.name,city.population
from country,city
where city.countrycode=country.code
and city.population<100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+

#name都是一样的，可以写别名
select country.name as 国家名,city.name as 城市名,city.population as 城市人口数量
from country,city
where city.countrycode=country.code
and city.population<100;
+-----------+-----------+--------------------+
| 国家名    | 城市名    | 城市人口数量       |
+-----------+-----------+--------------------+
| Pitcairn  | Adamstown |                 42 |
+-----------+-----------+--------------------+


3、#查询世界上小于100人的人口城市是哪个国家的说的什么语言？
找到你要的字段
国家               城市         城市人口数         语言 
country.name    city.name     city.population    countrylanguage.language

select country.name,city.name,city.population,countrylanguage.language
from city,countrylanguage,country
where city.countrycode=country.code and country.code=countrylanguage.countrycode
and city.population<100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+

select 自连接

select 自连接natural join    #自连接用的少
#自动关联多张表的数据(自动帮我们找到等价条件)字段必须相同
root@localhost:aaa > select stu.name,mark.mark from stu natural join mark where stu.name='zhang3';
+--------+------+
| name   | mark |
+--------+------+
| zhang3 |   90 |
+--------+------+


1、#查询世界上小于100人的人口城市是哪个国家的？

#传统连接查询
select country.name,city.name,city.population
from country,city
where city.countrycode=country.code
and city.population<100;

#自连接查询  无法实现，等价的条件有，但是字段名不相同
找到你要的字段
国家                   城市            城市人口数          
country.name         city.name        city.population

select country.name,city.name,city.population
from country natural join city
where city.population<100;

2、#查询世界上小于100人的人口城市是哪个国家的说的什么语言？
找到你要的字段
国家               城市         城市人口数         语言 
country.name    city.name     city.population    countrylanguage.language
#传统连接查询
select country.name,city.name,city.population,countrylanguage.language
from city,countrylanguage,country
where city.countrycode=country.code and country.code=countrylanguage.countrycode
and city.population<100;


city   countrylanguage
#自连接查询
select  city.countrycode,city.name,city.population,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
+-------------+-----------+------------+-------------+
| countrycode | name      | population | language    |
+-------------+-----------+------------+-------------+
| PCN         | Adamstown |         42 | Pitcairnese |
+-------------+-----------+------------+-------------+

select 内连接（inner join）

tb1 join tb2 on 等价条件 where
tb1 join tb2 on 等价条件 join tb3 on 等价条件  where

1、#查询世界上小于100人的人口城市是哪个国家的？

#传统连接查询
select country.name,city.name,city.population
from country,city
where city.countrycode=country.code
and city.population<100;

#内连接查询
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+
     

2、#查询世界上小于100人的人口城市是哪个国家的说的什么语言？
找到你要的字段
国家               城市         城市人口数         语言 
country.name    city.name     city.population    countrylanguage.language

#传统连接查询
select country.name,city.name,city.population,countrylanguage.language
from city,countrylanguage,country
where city.countrycode=country.code and country.code=countrylanguage.countrycode
and city.population<100;

#内连接查询
select country.name,city.name,city.population,countrylanguage.language
from city join countrylanguage 
on  city.countrycode=countrylanguage.countrycode
join country
on country.code=city.countrycode
where city.population<100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+


#为了性能
join on 内连接查询，必须小表在前，大表在后，内连接的性能比传统连接的性能高
谁的数据量多谁属于大表 select * from city

select 外连接

• 左连接

没充钱  看不到数据 就用左 外  右外  
充钱 ，就变成传统连接
#left outer join       left join
select city.name,city.countrycode,country.name
from city left join country on city.countrycode=country.code and
city.population<100 limit 10;
+----------------+-------------+------+
| name           | countrycode | name |
+----------------+-------------+------+
| Kabul          | AFG         | NULL |
| Qandahar       | AFG         | NULL |
| Herat          | AFG         | NULL |
| Mazar-e-Sharif | AFG         | NULL |
| Amsterdam      | NLD         | NULL |
| Rotterdam      | NLD         | NULL |
| Haag           | NLD         | NULL |
| Utrecht        | NLD         | NULL |
| Eindhoven      | NLD         | NULL |
| Tilburg        | NLD         | NULL |
+----------------+-------------+------+

• 右连接

#right outer join             right join
select city.name,city.countrycode,country.name
from city right join country on city.countrycode=country.code and
city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name                 |
+------+-------------+----------------------+
| NULL | NULL        | Aruba                |
| NULL | NULL        | Afghanistan          |
| NULL | NULL        | Angola               |
| NULL | NULL        | Anguilla             |
| NULL | NULL        | Albania              |
| NULL | NULL        | Andorra              |
| NULL | NULL        | Netherlands Antilles |
| NULL | NULL        | United Arab Emirates |
| NULL | NULL        | Argentina            |
| NULL | NULL        | Armenia              |
+------+-------------+----------------------+