MySQL单表查询与多表查询

2017-02-14 本文已影响0人 joshul

参考与：http://codingxiaxw.cn/

1.解决中文乱码问题

1.1查看MySQL数据库编码

在命令行中输入:SHOW VARIABLES LIKE 'CHAR%'; 回车可看到如下内容:

Paste_Image.png

1.2编码解释

character_set_client: mysql使用该编码来解读客户端发送过来的数据，例如该编码为utf，那么如果客户端发送过来的数据不是utf，就会出现乱码。
character_set_results：mysql会把数据转换成该编码后，再发送给客户端，例如该编码为utf，那么如果客户端不使用utf来解读，那么就会出现乱码，其它编码只要支持中文即可。

除了这两个编码格式外我们还需要考虑一个工具(用于编写sql语句的工具)的编码格式，即控制台(用命令行写sql语句)或者用于写sql语句的可视化工具，为什么要用”或”呢，因为二者就是一体，修改一个即一起修改了两个的编码格式。下面1.3节我在分析乱码原因后会告诉你如何保持client、results以及控制台与可视化工具这四者编码格式的一致，以解决中文乱码问题。

1.3控制台乱码问题

windows系统中:

插入或修改时出现乱码：因为控制台默认使用gbk，而character_set_client不是gbk的原因。
查询出的数据为乱码，因为character_set_results不是gbk而控制台默认使用gbk。
解决方法:通过在命令行中输入代码set character_set_client=gbk;和set character_set_results=gbk;来设置编码。

通过将client和results的编码格式改成gbk后使得它们二者和控制台的编码格式一致，这样便可以实现中文乱码问题解决了编码的问题。

注意：通过上述方式我们将client、results、控制台这三者编码方式设置一致，设置编码只对当前连接有效，当退出mysql后再次登陆mysql时又回到utf了。解决方法:找到my.ini配置文件，在配置文件中设置set default_character=gbk即可。

或是保持client和results的编码格式继续为utf-8，然后将控制台默认的的gbk编码方式改为utf-8格式，这种方法我没试过，毕竟我用的是mac，估计也是在控制台的my.ini配置文件中进行设置。

而对于我的mac系统:由于mac的控制台默认编码不是gbk(我也不知道mac系统默认是什么编码格式)，我为什么知道它的默认编码不是gbk呢，因为我修改了client和results的编码格式为gbk后，在控制台中输入中文仍显示乱码，所以我才知道它的默认编码格式不是gbk。针对mac用户我也提供如下两种方法:

1.在命令行中输入set character_set_client=gbk;和set character_set_results=gbk;来设置client和results的编码格式为gbk，然后再修改控制台的编码格式为gbk。那么如何修改控制台的编码格式呢?mac的控制台编码格式是与编写sql语句的可视化工具连在一起的，修改可视化工具的编码格式便可以修改控制台的编码格式。而我用的可视化工具为MySQL Workbench，所以我在MySQL Workbench中创建数据库时指定创建数据库的编码格式为gbk并在创建表时也设置表的编码格式为gbk,这样便保持了它们编码格式的一致，成功解决中文乱码问题。
2.保持client和results的编码格式继续为utf-8,然后修改可视化工具创建数据库时的编码格式为utf-8并在创建表时也指定表的编码格式为utf-8,通过这种方式也保持了它们三者编码格式的一致，成功解决编码乱码的问题。

2.备份数据库与恢复数据库

2.1备份数据库

备份就是将数据库导出为sql脚本。在命令行中输入:mysqldump -u用户名 -p密码数据库名>导出文件路径

注意:1.末尾不要打分号。2.执行此语句前应该先退出mysql客户端。3.导出的内容不包括创建数据库的语句只包含数据库里面的内容。

2.2恢复数据库

就是将导出的sql脚本插入到数据库中。有如下两种实现方式:

第一种方式:1.登录mysql:mysql -u用户名 -p密码。2.创建数据库:create database 数据库名。3.输入命令:mysql -uroot -p密码数据库名<备份的数据路径并回车。
第二种方式:1.删除数据库:drop database 数据库名。2.重新创建数据库:create database 数据库名。3.切换到数据库:use 数据库名。4.输入命令:source sql脚本路径并回车。

3.约束

约束是添加在列上用来约束列的。

3.1主键约束(primary key)

特点:1.非空。2.唯一。3.可被引用。当表的某一列被指定为主键后，该列的值就不能为空，也不能有重复值出现。

创建表时指定主键的两种方式:

1.create table emp(
  empno int primary key,
  ename varchar(50)
);
2.create table emp(
  empno int,
  ename varchar(50),
  primary key(empno)
 );

修改表时指定主键的方式:

alter table emp
and primary key;

修改表时删除主键的方式:

alter table emp
drop primary key;

3.2主键自增长(auto_increment)

create table student(
id int primary key auto_increment,
name varchar(50)
);

注意:auto_increment必须添加在int类型后，指定主键自增长后，插入数据时便可以给该主键设置null值。

限制:主键自增长在群集环境下不好使，所以大部分情况下我们使用UUID来作主键。

3.3非空约束(not null)

因为某些列不能设置为null值，所以可以对列添加非空约束。

create table student(
id int primary key auto_increment,
name varchar(50) not null
);

3.4唯一约束(unique)

create table student(
id int primary key auto_increment,
name varchar(50) not null unique
);

3.5概念模型

在java中表现为对象模型：在java中是domain。例如：User、Student。
在数据库中表现为关系模型：在数据库中表现为表。
当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。例如部门、员工都是系统中的实体。概念模型中的实体最终会成为java中的类、数据库中的表。实体之间还存在着关系，关系有三种:
1对多:例如员工和部门的关系
1对1:例如老公和老婆的关系
多对1:例如老师与学生的关系
对象模型：可以双向关联，而且引用的是对象，而不是一个主键。

关系模型：只能多方引用一方，而且引用的是主键，而不是一整行记录。

3.6外键约束

1.外键必须是另一表(或自己表)的主键的值(即外键要引用主键的值)。
2.外键可以为空。
3.外键可重复。
4.一张表可以有多个外键。

create table dept(
deptno int primary key auto_increment,
name varchar(50)
);
create table emp(
empno int primary key auto_increment,
name varchar(50), 
dno int,
constraint fk_emp_dept  foreign key(dno)  references dept (deptno)
);

最后一行就是给emp表添加外键约束，添加外键约束后，在emp表中对dno列进行赋值时就应该考虑外键约束的三个条件了。(上图创建的两张表演示的也是数据库中1对多的关系。)

3.7数据库中1对1的关系

create table husband (
hid int primary key auto_increment,
hname varchar(50)
);

insert into husband values(null,’刘备’);
insert into husband values(null,’张飞’);
insert into husband values(null,’关羽’);

create table wife(
wid int primary key auto_increment,
wname varchar(50),
constraint fk_wife_husband foreign key (wid)  references husband(hid)
);

特点:外键引用自身表的主键。

3.8数据库中多对多的关系

在表中建立多对多关系需要使用中间表(关联表)，即需要三张表，在中间表中使用两个外键，分别引用其它两个表的主键。

create table student(
sid int primary key auto_increment,
sname varchar(50)
);

create table teacher (
tid int primary key auto_increment,
name varchar(50)
);

create table stu_tea(
sid int,
tid int,
constraint fk_student foreign key(sid) references student(sid),
constraint fk_teacher foreign key(tid) references student(tid)
);

insert into student values(null,’刘德华’);
insert into student values(null,’梁朝伟);
insert into student values(null,’黄日华’);
insert into student values(null,’苗侨伟’);
insert into student values(null,’汤镇业’);

insert into teacher values(null,’崔老师’);
insert into teacher values(null,’刘老师’);
insert into teacher values(null,’石老师’);

insert into stu_tea values(1,1);
insert into stu_tea values(2,1);
insert into stu_tea values(3,1);
insert into stu_tea values(4,1);
insert into stu_tea values(5,1);
insert into stu_tea values(1,2);
insert into stu_tea values(2,2);
insert into stu_tea values(3,2);
insert into stu_tea values(3,3);
insert into stu_tea values(4,3);
insert into stu_tea values(5,3);

select * from stu_tea;

4.多表查询

4.1分类

合并结果集(了解)
连接查询
子查询

4.2合并结果集

要求两个结果集(注意这里强调的是结果集，而不是两张表)的列数、列类型完全相同。关键字union:去除重复行;关键字union all:不去除重复行。

create table ab(
a int, 
b,varchar(50)
);

insert into ab values(1,’1’);
insert into ab values(2,’2’);
insert into ab values(3,’3’);

create table cd(
c int,
d varchar(50)
);

insert into cd values (3,’3’);
insert into cd values (5,’5’);
insert into cd values (5,’5’);

合并操作为:

select * from ab
union (all)
select * from cd;

4.3连接查询

特点:外连接有一主一次。

左外连接左表为主，那么左表中所有的记录无论满足不满足条件，都打印出来。不满足条件的值用null填补。语法为:select * from emp left outer join dept on emp.deptno=dept.deptno;
右外连接右表为主，那么右表中所有的记录无论满足不满足条件，都打印出来。不满足条件的值用null填补。语法为::select * from amp right outer join dept on emp.deptno=dept.deptno;
全外连接：左右表都为主，左表和右表中的记录都要打印出来，不满足条件的值用null填补。使用union将左外连接和右外连接的结果集合并起来就是全外连接。

4.4子查询

子查询通俗来讲，就是查询中有查询。

见例子:

/*查询本公司工资最高的员工的详细信息*/

select * 
from amp
where sal=max(sal);

此种写法错误，因为where条件中不能有聚合函数。所以想到要用子查询。

思路:首先查出最高工资:select max(sal) from amp;然后查询该工资的员工:select * from amp where sal=刚刚的查询结果。所以合并起来为：select * from amp where sal=(select max(sal) from amp);

4.4.1子查询能出现的位置

where后作为条件(上述例子)
from后作为二次查询(下面例子)select e.empno,e.ename from (select * from amp where deptno=30) as e where 条件;

4.4.2子查询的结果集

单行单列:select * from 表1 where 列1 [=、>、<、>=、<=、!=] （select 列 from 表2 where 条件);
多行单列::select * from 表1 where 列1 [=、>、<、>=、<=、!=] ［any、all、in］（select 列 from 表2 where 条件;
单行多列:select * from 表1 where （列1，列2） in (select 列1，列2 from 表2 where 条件);
多行多列:该结果集用在from后作为二次查询。select * from 表1,(select …) 别名 where 条件;