MySQL 的字符集

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

image-20240814143839002

字符集的校验规则

show collation；

ci -- 不区分大小写
cs或者bin -- 大小写敏感

字符集的设置

## 终端 liunx 修改字符集
#### LANG
echo $LANG

## 临时修改系统字符集
export LANG=en_US.UTF_8

## 永久生效 CentOS7
[root@db03 ~]# vim /etc/locale.conf 

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

## 应用修改字符集
[root@db04 ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8

### 库修改字符集
show create database mysql;
alter database mysql charset utf8;

## 查询字符集应用的表
select * from information_schema.tables where table_collation not like '%utf8%';
show create table zls.test111;
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


sed -i 's#CHARSET=latin1#CHARSET=utf8#g' /tmp/zabbix.sql

mysql -uroot -p123 < /tmp/zabbix.sql

select连表查询

root@localhost:a1111 02:54:53>select * from stu;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang3 |
|    2 | li4    |
|    3 | wang5  |
+------+--------+

root@localhost:a1111 02:55:06>select * from mark;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+

## 查找zhang3的成绩
select stu.name,mark.mark from stu,mark where stu.id=mark.id and name='zhang3';
+--------+------+
| name   | mark |
+--------+------+
| zhang3 |   80 |
+--------+------+

select传统连接

# 世界上小于100人的人口城市是哪个国家的？
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 |
+-----------+-----------+--------------------+

## 世界上小于100人的人口城市是哪个国家的说的什么语言？
select country.name as 国家,city.name as '城市名',city.population as '人口数',countrylanguage.language as '讲的语言'
from country,city,countrylanguage
where city.countrycode=country.code
and country.code=countrylanguage.countrycode
and city.population<100;

+----------+-----------+-----------+--------------+
| 国家     | 城市名     | 人口数     | 讲的语言     |
+----------+-----------+-----------+--------------+
| Pitcairn | Adamstown |        42 | Pitcairnese  |
+----------+-----------+-----------+--------------+

select 自连接 natural join

## 自动关联多张表的数据（自动帮我们找到等价条件）等价条件的字段名必须相同
root@localhost:a1111 03:35:30>select * from stu;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang3 |
|    2 | li4    |
|    3 | wang5  |
+------+--------+

root@localhost:a1111 03:35:20>select * from mark;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+

select stu.name as 姓名,mark.mark as 成绩
from stu natural join mark
where stu.name='zhang3';
+--------+--------+
| 姓名   | 成绩    |
+--------+--------+
| zhang3 |     80 |
+--------+--------+


# 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from country,city
where city.countrycode=country.code
and city.population<100;

select 内连接（inner join）（join）

## 语法：tb1 join tb2 on 等价条件
## 语法：tb1 join tb2 on 等价条件1 join tb3 on 等价条件2


# 世界上小于100人的人口城市是哪个国家的？
#传统连接
select country.name,city.name,city.population
from city,country
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;



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

## 内连接
select country.name,city.name,city.population,countrylanguage.language
from country join city
on city.countrycode=country.code
join countrylanguage
on country.code=countrylanguage.countrycode
where city.population<100;

# join on 内连接查询
小表在前，大表在后 性能

select外连接（outer join） （left join） （right join）

### 左外连接 只显示 left join 左边的
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;

select city.name,city.countrycode,country.name
from country left join city on city.countrycode=country.code and
city.population<100 limit 10;

### 右外连接  只显示 right join 右边的 
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;