MySQL（2）

---

MySQL（2）

编码

1、查看MySQL数据库编码

show variables like 'char%';  # 通配符%代表任意多个字符

2、编码解释

character_set_client：MySQL使用该编码来解读客户端发过来的数据，例如编码为utf8，如果客户端发送过来的数据不是utf8，就会发生乱码。

如何解决乱码呢？

#方法一
set character_set_client = gbk; # 仅在当前窗口有效
#方法二：在my.ini(windows中的总配置文件，linux中是my.cnf)

character_set_results：MySQL会把数据转换成该编码后再发送给客户端，如果客户端不使用该编码进行解读，就会乱码。

#解决方法
set character_set_results = gbk; # 让服务器发送gbk的数据

3、备份与恢复

备份：将数据库转换成SQL语句的形式（外部文件）存储下来。

#方法一：命令
mysql dump -u root -p <密码> <数据库名> > <外部文件>;
#方法二：直接用workbench导出

恢复：通过SQL语句（外部文件）创建数据库

#方法一：命令
mysql -u root -p <密码> <数据库名> < <外部文件>;
#方法二：直接用workbench导入

约束

约束就是对表的一列或多列加以限制，从而保证数据库的一致性以及易用性，一致性是指若多表之间存在关联，改动表A则也需要对B进行相应的修改。

约束的种类

1、主键约束（PRIMARY KEY）
标志一列或多列，并保证其值在表内的唯一性。

特点：主键非空且唯一，被其他表的外键引用。

#创建时
create table <表名>(id int primary key auto_increment); #指定主键为id，auto_increment设置主键自增长
create table <表名>(id int,primary key(id));
#修改时
alter table <表名> add primary key(id); #指定主键为id
alter table <表名> drop primary key; #删除主键

2、外键约束（FOREIGN KEY）
限制一列或多列的值必须被包含在另一张表的外键列中，并且在级联更新或级联删除规则建立后也能限制其他表中的可用值，以满足数据库一致性的要求。

特点：外键必须是另一张表的主键值（外键引用主键），外键可以重复，可以为空，一张表中可以有多个外键。

外键所在的表称为从表，主键所在的表称为主表。

#创建时
create table <表名>(id int, login_date date, constraint <约束名称> foreign key(列名，指定为外键的列) references <表名>(列名，被引用的主键));
#修改时
alter table <表名> add constraint <约束名称> foreign key(列名) references <表名>(列名);
alter table <表名> drop foreign key <约束名称>; # 删除外键约束

定义外键的注意点：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。
• 在主表的表名后面指定列名或列名的组合。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

3、唯一约束（UNIQUE）

限制一列或多列的值，保证其在表内的唯一性。

#创建时
create table <表名>(id int primary key auto_increment, name varchar(20) unique);
#在类型后面加上unique修饰即可
#修改时
alter table <表名> add unique(列名);
alter table <表名> drop index <列名>; #删除唯一约束

4、非空约束（NOT NULL）

限制一列或多列的值非空，强制列不接受 NULL 值。

#创建时
create table <表名>(id int primary key auto_increment, name varchar(20) not null);
#修改时
alter table <表名> modify name varchar(20) not null;
alter table <表名> modify name varchar(20) null; 

5、检查约束（mysql没有）

限制一列的可用范围值。

级联约束

避免出现当从表中插入了新行、修改了行，导致主表中的外键列无可匹配值。保证数据库的一致性和完整性。

级联更新：当主表中的一行被更新时，服务器将更新从表中的子行。在删除存在的外键和添加新的外键时包含on update cascade语句。

级联删除：如果主表中的一行被删除，那么级联删除就会删除从表中的行。使用on delete cascade语句

alter table <表名> add constraint <约束名> foreign key(列名) references <表名>(列名)
on update cascade
on delete cascade;

多表查询

合并结果集

要求：被合并的表的列数和类型相同。

方法：

select from <表1> 
union
select from <表2>;
#union all:不去除重复值，union:去除重复值

连接查询

1、交叉连接（CROSS JOIN）

在不指定任何连接条件的情况下产生的结果就是交叉连接，数据服务器会产生笛卡尔积，即两张表的所有置换。

select a.name b.age from <表1> a, <表2> b;  # 未指定连接
select a.name b.age from <表1> a cross join <表2> b; #指定交叉连接
#a和b分别是表1和表2的别名

2、内连接（INNER JOIN）

查询出的所有记录都满足条件。

#mysql特有语法
select * from <表1> a, <表2> b where a.xx=b.xx;
#SQL标准语法
select * from <表1> a inner join <表2> b on a.xx=b.xx;

3、外连接（OUTER JOIN）

外连接有一主一次，左外连接即以左表为主。左表决定结果集的行数，而右表只负责提供与之匹配的列值，右表数据不足使用NULL来补位。

select * from <表1> a left outer join <表2> b on a.xx=b.xx;  #outer可以省略

右外连接以右表为主，右表决定结果集的行数，而左表负责提供与之匹配的列值，左表数据不足使用NULL来补位。

select * from <表1> a right outer join <表2> b on a.xx=b.xx;  

注意，right和left只是告诉数据库服务器哪个表的数据可以不足，<表1> a left outer join <表2> b和<表2> b right outer join <表1> a效果一样。

全连接（mysql中没有FULL OUTER JOIN的语法）,mysql中使用UNION将左外和右外连接结果集合并以实现全连接。

select * from <表1> a full outer join <表2> b on a.xx=b.xx;  
#mysql中实现
select * from <表1> a left outer join <表2> b on a.xx=b.xx
union
select * from <表1> a right outer join <表2> b on a.xx=b.xx;  

4、自然连接（NATURAL JOIN）

数据库服务器会依赖多表交叉时的相同列名来推断正确的连接条件，而不需要人为指定相同的列。当两表没有相同名称的列时会出问题，最终会变成交叉连接，产生笛卡尔积，所以应尽量避免这种方法。

select * from <表1> a natural join <表2> b;

记忆——7种join理论

select <select_list> from <表A> A inner join <表B> B on A.key=B=key; # 内连接

select <select_list> from <表A> A left join <表B> B on A.key=B=key; # 左连接

select <select_list> from <表A> A right join <表B> B on A.key=B=key; # 右连接

select <select_list> from <表A> A left join <表B> B on A.key=B=key where B.key is null;

select <select_list> from <表A> A right join <表B> B on A.key=B=key where A.key is null;

select <select_list> from <表A> A full outer join <表B> B on A.key=B=key;#mysql用union实现

select <select_list> from <表A> A full outer join <表B> B on A.key=B=key where A.key is null or B.key is null;

子查询

子查询是指包含在另一个SQL语句内部的查询，它通常在包含语句之前执行。任何子查询返回的数据在包含语句执行完成后都会被丢弃，也就是说服务器在SQL语句执行结束后将清空子查询结果所占的内存。形式如下：

#利用子查询进行过滤
select * 
from <表名> 
where <列> in (select <列>
               from <表名>
               where <条件>);                                   

Tips：由于包含子查询的select语句难以阅读和调试，可以分解为多行并适当地缩进，以提高可读性。

用子查询建立查询的可靠方法：

step 1：建立和测试最内层的查询；

step 2：用硬编码数据建立和测试外层查询，确认它正常后再嵌入子查询；

step 3：再测试查询语句；

step 4：重复上述步骤，直至必须的子查询嵌入完毕。