MySQL笔记(二)

---

目录：

一、概要
二、MySQL编码
三、MySQL备份与恢复
    1. 备份
    2. 恢复
四、约束
    1. 主键约束
        1.1 创建表时创建主键
        1.2 为已创建表创建主键
        1.3 删除主键
        1.4 主键自增长
        1.5 多字段主键约束
    2. 非空约束、唯一约束
        2.1 非空约束
        2.2 唯一约束
    3. 外键约束
        3.1 一对多关系
        3.2 一对一关系
        3.3 多对多关系
五、多表查询
    1. 合计结果集
    2. 连接查询
        2.1 内连接
        2.2 外连接
            2.2.1 左外连接
            2.2.2 右外连接
            2.2.3全连接
    3. 子查询
       3.1 子查询在where后，作为条件
       3.2 子查询在from后，作为结果集

---

一、概要

上一篇文章主要介绍了MySQL的数据库/表的操作以及数据的增删改查等基本操作，本篇文章主要介绍数据库的编码、备份、主键、约束、多表查询等内容，相较于上一篇文章要难一点。

---

二、MySQL编码

查看MySQL编码命令：show variables like ‘char%’;

接下来逐条分析每个variable：

character_set_client----客户端发送的数据，mysql会当成该编码来解析，可以使用
                        set character_set_client=gbk;更改当前窗口的client编码，但关
                        闭该窗口后又恢复到了以前的值

character_set_results---数据库以该编码格式给客户端发送数据，set character_set_results 
                        可以设置该编码格式，但是也是临时设置

可以修改my.ini文件，永久修改上面的两个属性值：

这一条设置语句相当于设置了client、results、connection的值为gbk

---

三、MySQL备份与恢复

数据库的备份和恢复分为两步：

• 备份 ： 数据库—>sql脚本
• 恢复 ： sql脚本–>数据库

1.备份

mysqldump -u用户名 -p密码 数据库名>sql脚本绝对路径

eg：

备份数据库mydb

mysqldump -uroot -p123456 mydb>c:\mydb.sql

2.恢复

mysql -u用户名 -p密码 数据库名 ‘<’sql脚本绝对路径

eg:

备份数据库mydb1
mysql -uroot -p123456 mydb1’<’c:\mydb.sql

恢复数据库还可以在登录数据库后，使用source sql脚本来完成

create database mydb2;
use mydb2;
source c:\mydb.sql;

---

四、约束

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

1.主键约束

主键特性：

• 唯一
• 非空
• 可被引用

当某一列被设置成了主键，则该列的值不能为空，且唯一

1.1 创建表时创建主键

方法一：

方法二：

查看创建的主键

1.2 为已创建表创建主键

alter table table_name add primary key(列名);

eg:
alter table stu add primary key(sid);

1.3 删除主键

alter table table_name drop primary key;

eg:
alter table stu drop primary key;

1.4 主键自增长

auto_increment，这个属性大多和主键约束一起用(可以单独使用)，必须是int类型，且一张表只能有一个列使用该约束

创建表时设置自增长：
    create table table_name(
        列名1        int primary key auto_increment，
        列名2        类型2，
        ......
        列名n        类型n，
    );

    eg:
    create table stu(
        _id      int primary key auto_increment,
        name     varchar(20),
        age      int,
        gender   varchar(5)
    );

修改表时设置自增长：
    alter table table_name change 列名 新列名 int auto_increment

    eg:
    alter table stu change sid sid int auto_increment

删除自增长
   alter table table_name change 列名 新列名 int

   eg:
   alter table stu change sid sid int

1.5 多字段主键约束

创建：

create table table_name(
        列名1     类型1,
        列名1     类型2,
        ......
        列名n     类型n,
        constraint primary_key_name primary key(列名1，列名2...)
     );


 eg:
 create table stu(
     sid     char(6),
     name    varchar(20),
     age     int,
     gender  varchar(5),
     constraint my_pk primary key(sid,name)
 );

2.非空约束、唯一约束

2.1 非空约束(not null)

设置某列非空约束后，该列的值不能为空NULL

设置：
create table table_name(
            列名1          类型1 not null，
            列名2          类型2，
            ......
            列名n         类型n
);

eg:
create table stu(
    sid     int primary key auto_increment,
    name    varchar(20) not null,
    age     int,
    gender  varchar(5)
);

2.2 唯一约束(unique)

设置某列唯一约束后，该列的值不能重复

设置
create table table_name(
            列名1          类型1 unique，
            列名2          类型2，
            ......
            列名n         类型n
); 

eg:
create table stu(
    sid     int primary key auto_increment,
    name    varchar(20) not null unique,
    age     int,
    gender  varchar(5)
);

3.外键约束

外键要素：

    1. 外键必须是另一表的主键(即引用另一表的主键的值，不能超出该主键值以外的值)
    2. 可以重复
    3. 可以为空
    4. 一张表可以有多个外键

设置：
create table table_name(
    列名1       类型1，
    列名2       类型2，
    ......
    列名n      类型n，
    constraint 外键名 foreign key(列名1) references 主表名(列名)
);

表与表之间三种关系
1. 一对多关系
2. 一对一关系
3. 多对多关系

3.1 一对多关系

eg:
先创建一张主表
create table dept(
    deptno    int primary key auto_increment,
    dname    varchar(20)
);

再创建一张从表
create table emp(
    empno    int primary key auto_increment,
    ename    varchar(20),
    deptno   int,
    constraint fk_em_deptno foreign key(deptno) references dept(deptno)
);

或者在修改表时创建外键约束
alter table emp add constraint fk_em_deptno foreign key(deptno) references dept(deptno)

3.2 一对一关系

核心：从表的主键，也是外键

eg:
先创建一张主表
create table husband(
    h_id     int primary key auto_increment,
    h_name   varchar(10)
);

再创建一张从表
create table wife(
    w_id     int primary key auto_increment,
    w_name   varchar(10),
    constraint fk_wife_husband foreign key(w_id) references husband(h_id)
);

3.3 多对多关系

核心：
  两张需要关联的表之间没有直接联系，通过建立中间的关联表把这两张表建立联系，这两张表都是主表,而关联表是从表

方法:
  在关联表中建立两个外键，引用上述两个主表的主键


eg：
建立两个主表，student和teacher表
student:
    create table student(
        s_id      int primary key auto_increment,
        s_name    varchar(20)
    );

teacher:
    create table teacher(
        t_id     int primary key auto_increment,
        t_name   varchar(20)
    );

建立从表(关联表)stu_tea表
stu_tea:
    create table stu_tea(
        s_id     int,
        t_id     int,
        constraint fk_student foreign key(s_id) references student(s_id),
        constraint fk_teacher foreign key(t_id) references teacher(t_id)
    );

多对多的关系体现在关联表中！

---

五、多表查询

分类：
    1. 合并结果集
    2. 连接查询
    3. 子查询

1.合并结果集

将查询的两个结果集的记录(行数)合并显示，要求结果集的结构相同(列、类型)。

关键字：
    1. union        -------   去除结果集中完全相同的行(记录)
    2. union all   -------   不去除相同的行，全部合并

eg：
创建student表
     create table student(
         s_id     int,
         s_name   varchar(10)
     );
     insert into student values(1,'stu_zhang');
     insert into student values(2,'stu_wang');

创建teacher表
     create table teacher(
         t_id     int,
         t_name   varchar(10)
     );
     insert into teacher values(5,'tea_zhao');
     insert into teacher values(6,'tea_han');

合并结果集：
    select * from student union all select * from teacher; 

2.连接查询

分类：
    1. 内连接
    2. 外连接
       * 左外连接
       * 右外连接

2.1 内连接

别名：笛卡尔积
两(多)张表的记录组合
如果没有查询条件，则查询结果为两张表所有记录的自由组合m*n，当有查询条件时，查询结果为满足条件的记录

select 表名1.列名1 ……，表名2.列名1…… from 表名1，表名2 where condition

或者

select 表名1.列名1……，表名2.列名1…… from 表名1 inner join 表名2 on condition

eg：

emp表
    select * from emp; 

dept表
    select * from dept;

内连接查询：
select emp.ename, emp.deptno, dept.loc from emp, dept where emp.deptno=dept.deptno; 

或者 

select emp.ename, emp.deptno, dept.loc from emp inner join dept on emp.deptno=dept.deptno;


也可以给表起别名，这样便于书写 
select e.ename, e.deptno, d.loc from emp e, dept d where e.deptno=d.deptno; 

或者

select e.ename, e.deptno, d.loc from emp e inner join dept d on e.deptno=d.deptno;

查询结果：

2.2 外连接

2.2.1 左外连接(left outer join)

左边的记录全部显示出来，不满足条件时，右表使用NULL代替

eg：
select e.ename,e.deptno,d.loc from emp e left outer join dept d on e.deptno=d.deptno;

查询结果

2.2.2 右外连接(right outer join)

右边的记录全部显示出来，不满足条件时，左表使用NULL代替

eg:
select e.ename,e.deptno,d.loc from emp e right outer join dept d on e.deptno=d.deptno;

查询结果

2.2.3 全外连接

左右表的所有记录均显示出来，效果类型与左外连接和右外连接，然后合并结果集

select e.ename,e.deptno,d.loc from emp e left outer join dept d on e.deptno=d.deptno
union
select e.ename,e.deptno,d.loc from emp e right outer join dept d on e.deptno=d.deptno;

查询结果

3.子查询

查询语句中包含多个select

子查询出现的位置：
    1. where  后，作为条件存在
    2. from 后，作为结果表存在(从该结果表中再查询)

3.1 子查询在where后，作为条件

子查询结果为单行单列(一个值)：
    ---可以使用=、>、<、!=、>=、<=预算符
    eg：
    select * from emp where sal = (select max(sal) from emp);

子查询结果为多行单列(一个集合)：
    ---可以使用all、any、in关键字
    eg:
    select * from emp  where sal > all (select sal from emp where deptno =30);
    select * from emp where sal > any (select sal from emp where deptno=20);

子查询结果为单行多列(一个对象)：
    ---使用in关键字
    eg:
    select * from emp where (job,deptno) in (select job,deptno from emp where  empno='1004');

3.2 子查询在from后，作为结果集

子查询结果为多行多列：
    ---子查询结果为一个结果集(相当于一张表)，在该结果集里再查询
eg:
select e.empno,e.deptno from (select * from emp where sal>12500.00) e where e.job != '经理';

---