MySQL 基本的知识

MySQL
课程内容:
-数据库的基本概念
-sql的简介
-常用的数据库
-mysql数据库的安装，配置，和卸载
-DDL数据定义语言
-DML数据操作语言
-DQL数据查询语言（简单）
-数据的完整性
-多表设计
-DQL数据查询语言（复杂）
    --链接查询
    --子查询
-数据库的备份和恢复


--------------------------------------------
一，什么是数据库？（DataBase简称DB）
从字面意思看：数据库就是存储数据的仓库
使用数据库存储数据的优势：
  数据库是将数据存储在表中，数据和数据之间可以建立关系，还可以对数据实施
  完整性的约束，这样我们就可以保证数据库中的数据是准确的，可靠的。使用数据
  库存储数据还便于数据的查询，检索，更新及删除等操作。

官方解释：数据库指长期保存在计算机的存储设备上，按照一定规律组织起来的，可以被各种用户或应用共享的数据集合。（文件系统）

数据库管理系统（DataBase Management System简称DBMS）
操作和管理数据库的大型软件，用于建立，使用和维护数据库，对数据库进行统一管理
和控制，以保证数据的安全性和完整性（正确性和准确性）。
用户通过数据库管理系统访问数据库中的数据的。

总结：数据库软件就是数据库管理系统，数据库是通过数据库管理系统创建和操作的。

面试题：数据库？存储，维护和管理数据的集合。

数据库中真正存储数据的是数据表，一个数据库中可以包含多个数据表。

二，常用的数据库管理系统有哪些？
-Oracle：是目前比较成功的关系型数据库管理系统。
特点：运行稳定，功能齐全，性能超群，技术领先。
主要应用在大型企业数据库领域。

Oracle是Oracle（甲骨文）公司运营的数据库产品。甲骨文公司成立于1977年，
是全球最大的数据库软件公司。

-DB2：是IBM公司研发的数据库产品。

IBM研究中心Codd博士提出的关系型数据库模式，奠定了关系型数据库发展的理论基础。

-SQL server：微软（Microsoft）的产品。界面友好，易学易用。在操作性和交互性
方面比较好。

-MySQL：免费的数据库系统，被广泛的用于中小型企业。
特点：体积小，速度快，功能齐全，开发源代码。
08年被sun收购了，09年sun公司又被oracle收购了，（6.0之前是免费的）
Mysql是现在比较流行的中小型关系型数据库，开发者是瑞典的Mysql AB公司。


三，sql的简介
什么是sql？Structure Query Language 结构化查询语言。

专门用于数据存取，数据更新，及管理数据库等数据库操作。

SQL是被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来又被国际化
组织（ISO）采纳为关系型数据库语言的国际标准。
各个数据库厂商都支持ISO的SQL标准----普通话
各个数据库厂商在标准的基础上还做了一些自己的扩展----方言

优点：-它不是某个特定数据库供应商的专有语言，几乎所有的重要的数据库管理系统都
       支持SQL。
      -简单易学，它的语句都是由描述性很强的英语单词组成，而且单词数量还不多。
      -非过程化，就是用sql操作数据库时，只需要指出‘做什么’，不用指出‘怎么做’
       存取路径的选择和操作的执行由DBMS自动完成。


四，SQL的分类：
    -DDL（Data Definition Language）数据定义语言。
    作用：用来定义数据库对象：库，表，列等。
    常用的关键字：create   alter   drop


    -*DML (Data Manipulation Language)数据操作语言。
    作用：用来操作数据库表中的数据（记录）。
    常用的关键字：insert   update   delete

    -**DQL（Data Query Language）数据查询语言。
    作用：用来查询数据的。
    常用关键字：select

    -DCL(Data Control Language)数据控制语言。
    作用：用来定义访问权限和安全级别的；

注意：sql语句要以;结尾。

五，mysql的安装，配置和卸载
    -安装配置见图
    -卸载：1，停止mysql服务 在运行输入 cmd 进入命令窗口--》输入net stop mysql停          止服务。
          或者控制面板--》管理工具--》服务--》停止mysql服务。
          或者在运行中输入service.msc停止mysql服务。

       2，控制面板--》程序和功能--》找到mysql卸载
       3，找到mysql安装目录下的my.ini打开找到          datadir="C:/ProgramData/Mysql/MysqlServer5.5/Data/"将文件夹删除。

启动服务命令：net start mysql
停止服务命令：net stop mysql

登录mysql服务命令：mysql -u 用户名 -p 先回车在输入密码
          mysql -h localhost -u 用户名 -p 密码
          mysql -h 127.0.0.1 -u 用户名 -p 密码

退出命令：quit    exit

修改密码：1，停止mysql服务
      2，在cmd下输入mysqld --skip-grant-tables 启动服务器（不要关闭窗口）
      3，新打开一个cmd输入 mysql -u root -p 不需要输入密码直接回车。
      4，输入 use mysql
      5，输入 update user set password=password('修改后的密码')
         where user=‘root’;
      6, 关闭cmd窗口，在人物管理器中结束mysqld进程。
      7，重新启动mysql服务，密码修改完成。


六，操作数据库：
-创建库 create database 数据库名称；
      create database mydb2 character set gbk;
例：create database mydbone;

-修改字符集 alter database 数据库名称 character set 对应的字符集；
-删除库  drop database 数据库的名称；

-查看当前数据库服务器中的所有数据库： show databases;
-查看创建数据库的定义信息：show create database 数据库名称；
-查看当前使用的是哪个数据库：select database();
-切换数据库：use 数据库名称；

七，操作表的语法：
-创建数据表： create table 表名(
            字段1  字段类型,
            字段2  字段类型，
            字段3  字段类型，
            字段n  字段类型
        );

create table stu(
    id int,
    name varchar(10),
    gender varchar(4),
    birthday date,
    .......
    );

--------------------------------------------------------------------
-常用的数据类型：
    --int  整型
    --double 浮点型  double(5,2)表示最多5位数，其中必须有2位小数，最大值是                         999.99
    --char 固定长度的字符串类型 char(10) 'abcd      '   java中的String
    --varchar 可变长度的字符串类型 varchar(10) 'abcd'
    --text 字符串类型
    --blob 字节类型   java中的是byte
    --date 日期类型  格式 yyyy-MM-dd
    --time 时间类型  格式 hh:mm:ss
    --timestamp 时间戳类型 yyyy-MM-dd  hh:mm:ss 会自动赋值
    --datetime yyyy-MM-dd  hh:mm:ss

--------------------------------------------------------------------------
-查看当前数据库中的所有表：show tables;
-查看表的字段信息：desc 表名；
 例：desc stu;
-在表中增加列：alter table 表名 add 列名 数据类型；
 例：alter table stu add address varchar(50);
-修改列名：alter table 表名 change 原列名 新列名 数据类型；
 例：alter table stu change address addre varchar(40);
-修改列： alter table 表名 modify 列名 数据类型；
 例：alter table stu modify addre varchar(50);
-删除列：一次删一列：alter table 表名 drop 列名；
 例：alter table stu drop addre;
-修改表名： rename table 原来的表明 to 新表名；
 例：rename table stu to student;
-查看表的创建细节：show create table 表名；
 例：show create table student;
-修改表的字符集：alter table 表名 character set 字符集；
 例：alter table student character set gbk；
-删除表：drop table 表名；
 例：drop table student;

（修改默认字符集：找到my.ini，找到default-character-set 修改）

------------------------------以上都是DDL相关操作

七，DML相关操作
DML是对表中数据进行增，删，改的操作，切记不要和DDL混淆了！！！

-插入操作：insert
    语法:insert into 表名(列名1，列名2，列名3，.....)
         values(列值1，列值2，列值3，.....)；
    例：insert into employee(id,name,gender,birthday,salary,bonus)
        values(1,'baoqiang','nan','1989-05-05',6000,2000);
    例：insert into employee
        values(1,'baoqiang','nan','1989-05-05',6000,2000);
    批量插入：
    例：insert into employee
        values(5,'songzhe','nan','1989-05-05',6000,2000),
          (6,'marong','nv','1989-05-05',6000,2000),
          (7,'linqingxia','nv','1989-05-05',6000,2000),
          (8,'bingbing','nv','1989-05-05',6000,2000);    

    注意：-列名和列值的类型，个数和顺序要一致并且要一一对应。
          -值不能超出列定义的长度。
          -如果要插入空值，请使用null
          -插入的字符和日期一样，都要用引号括起来。
          -列名相当于java中的形参，值相当于实参。
----------------------------------------------------------------------
-修改操作：update 语法：update 表名 set 列名1=列值1，列名2=列值2，....
       where 列名=值；

-- 将所有人的工资修改为8000
update employee set salary=8000;
-- 将姓名为青霞的人的公司改为10000
update employee set salary=10000 where name='qingxia';
-- 将宝强的工资改为4000，性别改为男
update employee set salary=4000,gender='男' where name='baoqiang';
-- 把霆锋的工资在原来的基础上增加1000
update employee set salary=salary+1000 where name='tifeng';
update employee set name='霆锋' where id=2;
-- 将马蓉的奖金在原来的基础上增加500
update employee set bonus=bonus+500 where name='marong';
update employee set bonus=ifnull(bonus,0)+500 where name='marong';
-- 将宋哲的奖金在原来的基础上增加500
update employee set bonus=bonus+500 where name='songzhe';

null和任何数相加都为null，所以我们要用到一个虑空函数 ifnull() 就相当于
java中的if语句。ifnull（bonus,0）意思为如果bonus为null就把它变为0
--------------------------------------------------------------------
-删除操作：delete
    语法：delete from 表名 where 列名=值；

-- 删除姓名为baoqiang的记录
delete from employee where name='baoqiang';

-- 删除表中性别为女的记录
delete from employee where gender='nv';

-- 删除表中所有记录
delete from employee;

-- 使用truncate删除表中记录
truncate table employee;

delete删除表中的数据，表结构还在，删除的数据可以找回。
truncate 删除时是把表直接DROP掉了，然后创建了一个同样的新表，
删除的数据不能找回，执行速度比delete快。
----------------------------------------------------------------
八，DQL 数据查询语言
-数据库执行dql语句不会对数据进行改变，而是让数据库发送结果给客户端。
  查询返回的结果集是一张虚拟表。

-查询关键字：select
-语法：select 列名 from 表名 【where--group by--having--order by】
    查询几列就写几个列名，中间用逗号隔开。
    如果查询所有列就可以使用通配符  *   代表所有
    例：select * from 表名；

-基础查询：
     -- 查询所有列：select * from 表名；

     -- 查询指定列：select 列名1，列名2，.... from 表名；
     -- 查出emp表中 name， sal ，bonus这三列
        select name,salary,bonus from employee;
          
-条件查询：
    -什么是条件查询？就是在查询时给出where子句，将满足条件的内容显示出来。
    -在where子句中可以使用的运算符和关键字如下：
    =， ！=，<>,<,<=,>,>=
    between...and....
    in()
    is null
    is not null
    and
    or
    not

-- 查询出性别为女，并且年龄为40的人
select * from employee where gender='nv' and age=40;

-- 查询出id为5或者姓名为景甜的人的信息
select * from employee where id=5 or name='jingtian';

-- 查询出性别为女，或者年龄为50的人
select * from employee where gender='nv' or age=50;

-- 查询出编号是1,2,3,4的人的信息
select * from employee where id in(1,2,3,4);
select * from employee where id BETWEEN 1 and 4;
select * from employee where id>=1 and id<=4;
select * from employee where id<=4;
select * from employee where id=1 or id=2 or id=3 or id=4;

-- 查询出编号不是1,2,3的人信息
select * from employee where id not in(1,2,3);
select * from employee where id not BETWEEN 1 and 3;
select * from employee where id!=1 and id<>2 and id!=3;
select * from employee where id>3;

-- 查询出奖金不为null的人信息
select * from employee where bonus is not null;
select * from employee where not bonus is null;

-- 查询出奖金为null的人信息
select * from employee where bonus is null;

-- 查询出年龄在30到50岁之间的人的信息
select * from employee where age between 30 and 50;
select * from employee where age>=30 and age<=50;

-- 查询出性别非男的人的信息
select * from employee where gender='nv';
select * from employee where gender <>'nan';
select * from employee where gender !='nan';
select * from employee where gender not in('nan');
-----------------------------------------------------------------
-模糊查询：
-什么是模糊查询呢？当想查询姓名中包含某个字的人的信息时就需要用到
  模糊查询，意思就是要查询的条件不完整。

    模糊查询用到的关键字：like
    通配符：_ 代表任意一个字符
        % 代表0到n个字符（任意个字符）
    ‘张%’‘张_’  ‘%小%’

-- 查询姓名有三个子组成的人的信息
select * from employee where name like '___';

-- 查询姓名由三个字组成并且最后一个字是冰的人的信息
select * from employee where name like '__冰';

-- 查询姓林的人的信息
select * from employee where name like '林%';

-- 查询出姓名中第二个字是心的人的信息
select * from employee where name like '_心%';

-- 查询出姓名中包含心字的人的信息
select * from employee where name like '%心%';
--------------------------------------------------------
字段控制查询：
-去除重复记录：两行或两行以上记录中列上的数据都相同，比如employee表的sal
        字段有相同记录，当我们查询sal字段时，就出现相同的重复记录，
        要去除重复记录需要用到 distinct；

-- 查询emp表中sal列要求去除重复记录
select distinct salary from employee;

-查看员工的工资和奖金之和：
--因为sal和bonus两列都是数值类型，所以可以做加法运算，如果其中有一个字段
  不是数值类型，那么就会出错。

错误的写法：select salary,bonus,salary+bonus from employee;
bonus列有记录为null的，因为任何数值和null相加结果都为null，所以计算结果有
可能会出现问题，我们要使用虑空函数ifnull()将null值变为0.

正确的写法：select salary,bonus,sal+ifnull(bonus,0) from employee;

-给列添加别名：as
--在上面的查询中出现了sal+ifnull(bonus,0)列名，这样很不美观，所以我们需要给
  列起一个别名

-- 查询表中sal和bonus之和并起别名
select salary as 工资,bonus 奖金,salary+ifnull(bonus,0) as 工资总和 from employee;
由此可见起别名要用到as关键字

-- 查询出姓名，性别，生日，并起别名
select name 姓名,gender as 性别,birthday 生日 from employee;

-- 查询所有信息并且显示工资和奖金之和，起别名。
select *,salary+ifnull(bonus,0) as 工资总和 from employee;
说明给列起别名时as关键字可以省略。

---------------------------------------------------------------
排序：
    排序要用到的关键字是order by
    order by 列名 asc升序（默认） desc降序

-- 查询出所有记录，按照id升序排序
select * from employee order by id asc;
select * from employee order by id;

-- 查询出所有记录，按照id降序排序
select * from employee order by id desc;

-- 查询所有记录按工资升序排序
select * from employee order by salary asc;

-- 查询所有记录按工资升序排序，如果工资相同按奖金降序排序
select * from employee order by salary asc,bonus desc;

-- 查询所有记录，按年龄升序排序，如果年龄相同按编号降序排序
select * from employee order by age,id desc;

-- 查询id，name，salary，age，按照年龄升序排序，如果年龄相同，按工资降序排序，要求起别名
select id as 编号,name 姓名,salary as 工资,age 年龄 from employee order by age,salary desc;
----------------------------------------------------------------------------
聚合函数： sum求和   avg求平均值   max最大值   min最小值  count统计
    聚合函数是用来做纵向运算的函数。

-count()：统计指定列不为null的记录
-max()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
    （就是指对给定的字符串，按照首字母的字典值排序，如果首字母相同，则按照
     第二个字母的字典值排序，以此类推）
-min()：计算指定列的最小值。如果是字符串类型同上。
-sum()：计算指定列的数值和，如果指定列不是数值类型，那么计算结果为0
-avg()：计算指定列的平均值，同上。


count：当我们需要做纵向统计的时候可以使用count()

-- 查询emp表中的记录数
select count(*) from employee;

-- 查询emp表中有奖金的人数
select count(bonus) from employee;

因为count函数中给出的是bonus这列，那么只通知bonus列非null的行数

-- 查询emp表中的记录数
select count(*) from employee;

-- 查询emp表中有奖金的人数
select count(bonus) from employee;

-- 查询表中工资大于4000的人数
select count(salary) from employee where salary>4000;
select count(*) from employee where salary>4000;

-- 统计一下工资和奖金之和大于6000的人数
select count(*) from employee where salary+ifnull(bonus,0)>6000;

-- 查询有奖金的人数，有领导的人数
select count(bonus),count(leadernumber) from employee;

-- 查询出有奖金并且有领导的人数
select count(bonus and leadernumber) from employee;
select count(*) from employee where bonus is not null and leadernumber is not null;
---------------------------------------------------------------
sum和avg：
-当需要纵向求和时使用sum()：

-- 查询所有明星的工资之和
select sum(salary) 工资总和 from employee;

-- 查询所有明星的工资之和，以及所有明星的奖金之和
select sum(salary),sum(bonus) from employee;

-- 查询所有明星的工资加奖金之和
select sum(salary)+sum(bonus) from employee;
select sum(salary+ifnull(bonus,0)) from employee;

-- 统计所有明星的平均工资
select avg(salary) from employee;
select sum(salary)/count(salary) 平均工资 from employee;

-- 统计有奖金人的平均奖金
select avg(bonus) from employee;
select sum(bonus)/count(bonus) from employee;
---------------------------------------------------------
max和min：

-- 查询出最高工资和最低工资
select max(salary),min(salary) from employee;

---------------------------------------------------------
分组查询：
-当需要分组查询时要使用group by 子句
 -- 查询每个部门的编号和每个部门的工资总和
select deptno,sum(salary) from employee group by deptno;

-- 查询出每个部门的编号和每个部门的人数
select deptno,count(deptno) from employee group by deptno;
select deptno,count(*) from employee group by deptno;

-- 查询每个部门的编号，以及每个部门工资大于5000的人数
select deptno,count(*) from employee where salary>5000 group by deptno;

select * from employee where salary>5000;

首先走from找到表，然后走where条件对结果进行过滤，把不满足条件的过滤掉，
然后执行group by 进行分组（10，20），分完组之后进行统计人数（count（*））

注意：凡是和聚合函数同时出现的列名，一定要写在group by 之后。
----------------------------------------------------------------
having子句：
-- 查询工资总和大于10000的部门编号以及工资总和
select deptno,sum(salary) from employee group by deptno having sum(salary)>10000;

注意：having和where的区别：
    1，having是在分组后对数据进行过滤
       where实在分组前对数据进行过滤
    2，having后可以使用聚合函数
       where后不可以使用聚合函数

where是为分组前记录的条件，如果某行记录没有满足要求where子句的条件，那么
这行记录是不会参加分组的，而having是对分组后的数据的一种约束（筛选）
（where是先筛选在分组，having是先分组在筛选）

-- 查询工资大于5000的人的部门编号以及工资总和，要求各个部门工资总和大于10000
select deptno,sum(salary) from employee where salary>5000 group by deptno having sum(salary)>10000;
-----------------------------------------------------------------------------
limit 方言
     limit是用来限定查询结果的起始行，以及总行数。

-- 查询3行记录，起始行从提一条记录开始
select * from employee limit 0,3;

注意：起始行从0开始，就是从第一行开始。

-- 查询5行记录，起始行从第三行开始
select * from employee limit 2,5;

分页查询：
    如果一页记录为10条，希望查看第三页的记录怎么查？
    第一页记录起始行为0，一共查询10行
    select * from employee limit 0,10;
    第二页记录的起始行为10，一共查询10行
    select * from employee limit 10,10;
    第三页记录的起始行为20，一共查询10行
    select * from employee limit 20,10;

查询代码的书写顺序和执行顺序：
-查询语句的书写顺序：select-from-where-group by-having-order by-limit
-查询语句的执行顺序：from-where-group by-having-select-order by-limit
-------------------------------------------------------------------------
DCL:数据控制语言
-创建用户：
语法：create user 用户名@地址 identified by '密码'；
      create user momo@localhost identified by '123';

-给用户授权：
语法：grant 权限1，权限2，..... on 数据库名称.* to 用户名；
      grant select,update on mydbone.* to momo@localhost;

      grant all on mydbone.* to momo@localhost;


-撤销用户权限：
语法：revoke 权限1，权限，.... on 数据库名.* from 用户名；
      revoke select on mydbone.* from momo@localhost;
      revoke all on mydbone.* from momo@localhost;

-查看用户权限：
语法：show grants for 用户名；
      show grants for momo@localhost;

-删除用户：
语法：drop user 用户名；
      drop user momo@localhost;

-修改用户密码：
语法：use mysql;
      update user set password=password('密码') where user='用户名' and       host='IP'；
      flush privileges;

flush privileges命令在用户数据和权限修改后，希望在不重启mysql服务的
情况下直接生效，那么就需要用这个命令。
-------------------------------------------------------------------------
数据的完整性：
    作用：保证用户输入的数据保存到数据库中式正确的。
        防止了用户可能的输入错误。
    
    如何确保数据的完整性呢？就是在创建表的时候给表中添加约束。

    完整性的分类：
        --实体完整性
        --域完整性
        --引用完整性

    -实体完整性：
        --实体：表中的一行数据（一行记录）代表一个实体（entity）
        --实体完整性的作用：标识每一行数据不重复

        实体完整性是通过表的主键来实现的。

        约束类型：主键约束（primary key）
              唯一约束（unique）
              自动增长列（auto_increment）

        -主键约束的特点：数据唯一，且不能为空  null

        第一种添加方式：
            create table stu1(
                id int primary key,
                name varhcar(10)    
            );

        第二种添加方式：优势在于可以添加联合主键
            create table stu2(
                id int,
                name varchar(10),
                primary key(id)
            );

            create table stu3(
                id int,
                classid int,
                name varchar(10),
                primary key(id,classid)
            );

        第三种添加方式：
            create table stu4(
                id int,
                name varchar(10)
            );

        alter table stu4 add constraint pk_stu4_id primary key(id);
        alter table stu4 add primary key(id);

        -删除主键约束：alter table 表名 drop primary key;

    -唯一约束：（unique）
        create table stu6(
            id int primary key,
            name varchar(10) unique
        );

    -自动增长列：（auto_increment）
        给主键添加自动增长列的数值，列只能是整数类型，如果删除之前
        增长的序号后面在添加的时候序号不会重新开始，而是会接着被删除
        的那一列的序号。

        create table stu7(
            id int primary key auto_increment,
            name varhcar(10)
        );

-------------------------------------------------------------
域完整性：
    -指数据库表的列（字段）必须符合某种特点的数据类型或约束，
    比如not null
    -域完整性的作用：限制此单元格的数据正确，不对照此列的其他
    单元格比较，域代表当前单元格。
    -域完整性的约束：
        --数据类型  非空约束（not null） 默认值约束 default
          check约束（mysql不支持）
---------------------------------------------------------------
数据类型：数值类型  日期类型  字符串类型

非空约束：not null

create table stu8(
    id int primary key,
    name varchar(10) not null,
    gender varchar(2)
);
不能为空 null 可以相同    
---------------------------------------------------
默认值约束：default
create table stu9(
    id int primary key auto_increment,
    name varchar(10) not null,
    gender varchar(10) default'male'
);
insert into stu9(name) values('tom');
insert into stu9(name) values('momo');

insert into stu9(name,gender) values('xiaobao',default);

insert into stu9(name,gender) values('xiaobao','female');

insert into stu9 values(null,'bao','female');
------------------------------------------------------
引用完整性：也叫做参照完整性
    
    -外键约束：foreign key
    create table stu10(
        sid int primary key,
        name varchar(10) not null,
        gender varchar(10) default'男'
    );    

    create table score(
        cid int,
        score int,
        sid int
    );

-- 第一种添加方式：
alter table score add constraint fk_stu_score foreign key(sid) REFERENCES stu10(sid);

-- 第二种添加方式：
create table score(
    cid int,
    score int,
    sid int,
    constraint fk_stu_score foreign key(sid) references stu(sid)
);

-- 删除外键：
    语法：alter table 表名 drop forgien key 约束名
    alter table score drop forgien key fk_stu_score;

注意：要有外键必须先有主键，主键和外键的类型必须一致。
    保证一个表的外键和另一个表的主键对应。

究竟什么是引用完整性呢？引用完整性指从表外键的值引用主表中主键的值。
一旦建立了引用关系，从表外键的所有值在主表的主键中都必须存在。

总结：实体完整性描述的是行，域完整性描述的是列，引用完整性描述的是关系。
-------------------------------------------------------------------
引用完整性创建了标语表之间的关系：关系分为几种呢？
    -一对多（多对一）：主表中的一条记录对应子表中的多条记录。
    -一对一：主表中的一条记录对应子表中的一条记录。
    -多对多：主表中的多条记录对应子表中的多条记录。

多对一（一对多）
create table emp(
    eno int primary key,
    ename varchar(10) not null,
    manager int,
    salary double(8,2),
    bonus int,
    deptno int
);

create table dept(
    deptno int primary key,
    dname varchar(50),
    address varchar(100)
);
alter table emp add constraint fk_dept_emp foreign key(deptno) references dept(deptno);


多对多（学生和老师）设计三张表，其中有一张中间表。

create table tea(
    tid int primary key,
    tname varchar(10),
    age int
);

create table stu(
    sid int primary key,
    sname varchar(10),
    age int
);

create table tea_stu(
    tid int,
    sid int
);

alter table tea_stu add constraint fk_tea_stu foreign key (tid) REFERENCES teacher(tid);
alter table tea_stu add constraint fk_stu_tea foreign key (sid) REFERENCES stu(sid);

多对多通常要创建中间表来处理多对多的关系。在中间表添加2个外键约束。

注意：为什么要拆表？为了避免表中出现大量的重复（冗余）数据。
----------------------------------------------------------------------
***多表查询：
    多表查询有以下几种：
    -合并结果集 union     union all
    -连接查询：
        --内连接[inner] join on
        --外链接    outer join on
            ---左外链接left[outer]join
            ---右外链接right[outer]join
            ---全外链接（mysql 不支持）full join
        --自然链接 natural join
    -子查询

--------------------------------------------------------
合并结果集：
    合并结果集就是把2个select语句的查询结果合并到一起。

create table aaa(
    id int,
    name varchar(10)
);

create table bbb(
    id int,
    name varchar(10)
);

insert into aaa values(1,'zhangsan');
insert into aaa values(2,'lisi');
insert into aaa values(3,'wangwu');

insert into bbb values(1,'zhangsan');
insert into bbb values(2,'lisi');
insert into bbb values(4,'zhaoliu');

-合并结果集有两种方式：
    -union：去除重复记录
    select * from aaa union select * from bbb;
    -union all：不去除重复记录
    select * from aaa union all select * from bbb;

注意：被合并的两个结果，要求列数，列的类型必须相同。
-----------------------------------------------------
链接查询（非常重要）：
    -链接查询就是求出多个表的乘积，比如连接a表和b表，那么它查询
    出来的结果是a*b。
    -连接查询会产生笛卡尔积。
    -连接查询查出来的结果不是我们想要的，那么怎么去除那些不想要的记录呢？
     通过条件来过滤。
    -表之间存在着关系，可以通过关联关系来去除笛卡尔积。

-- 使用主外键关系作为条件去除无用信息。
select * from employee,dept where employee.deptno=dept.deptno;

-- 上面的查询结果查询出了所有列，我们可以指定要查询的列。
select employee.id,employee.name,dept.deptno,dept.deptname from employee,dept where employee.deptno=dept.deptno;

-- 可以给表指定别名，引用时使用别名即可。
select e.id,e.name,d.deptno,d.deptname from employee as e,dept as d where e.deptno=d.deptno;
----------------------------------------------------------------------
内连接：
    上面的链接语句就是内连接，但是它不是SQL标准中的查询方式，可以理解为方言

SQL标准的内连接：
    select * from employee e inner join dept d on e.deptno=d.deptno;
    select * from employee e join dept d on e.deptno=d.deptno;
    ---------------------inner 可以省略不写。
    内连接的特点：查询结果必须满足条件。on用于主外键关联。
---------------------------------------------------------------------
外连接：（左外和右外）
    外连接的特点：查询出的结果存在不满足条件的可能。

-左连接：
select * from employee e left outer join dept d on e.deptno=d.deptno;
select * from employee e left join dept d on e.deptno=d.deptno;、

select * from dept d left outer join employee e on e.deptno=d.deptno;
select * from dept d left join employee e on e.deptno=d.deptno;
----------------------outer可以省略不写！
左连接：先查询出左表（以左表为主）然后查询右表，把右表中满足条件的显示出来
    不满足条件的显示为null。

-右连接：
select * from employee e right outer join dept d on e.deptno=d.deptno;
select * from employee e right join dept d on e.deptno=d.deptno;

select * from dept d right outer join employee e on e.deptno=d.deptno;
select * from dept d right join employee e on e.deptno=d.deptno;
有链接把右表中的所有记录先查出来，然后查询左表满足条件的显示，不满足条件的
显示为null

查询心得：两张表的链接查询一定会有一个主外键关系。三张表的链接查询就一定有两
    个主外键关系。用主外键关系就可以去除笛卡尔积。
----------------------------------------------------------------------
自然连接：
    连接查询会产生笛卡尔积，用主外键关系作为条件可以去除笛卡尔积
    而自然连接不需要给出主外键关系等式。它会自动找到这个等式。
    
    -- 两张连接的表中名称和类型完全一致的列作为条件的。
    select * from employee natural join dept;
    select * from employee NATURAL left join dept;
    select * from employee NATURAL right join dept;

-----------------------------------------------------------------
子查询（*****）：
    一个select语句中包含另一个完整的select语句。
    子查询就是嵌套查询。就是select中包含select。
    如果一条语句中存在两个或两个以上的select，那么它就是子查询语句。

-子查询出现的位置：
    --出现在where后，作为被查询的条件的一部分。
    --出现在from后，作为表。
    如果子查询出现在where后作为条件，还可以使用一些关键字：
    --any
    --all

-子查询结果集的形式：
    --单行单列（用于条件）
    --当行多列（用于条件）
    --多行单列（用于条件）
    --多行多列（用于表）

举个栗子：
-- 查询工资大于李冰冰的人的信息
分析：第一步先要查询出李冰冰的工资
select salary from employee where name='李冰冰';
-- 第二部查询出工资大于她的人的信息
select * from employee where salary>(第一步);
select * from employee where salary>(select salary from employee where name='李冰冰');

-- 查询出与范冰冰同一个部门的人的信息
分析：第一步：先查询出范爷的部门
select deptno from employee where name='范冰冰';
第二步;查出与她部门相同的人的信息
select * from employee where deptno=(select deptno from employee where name='范冰冰');

-- 查询工资大于30号部门所有人的员工信息。
-- 分析：第一步要先查询30号部门所有人的工资
select salary from employee where deptno=30;
-- 第二步：查询出工资高于30部门所有人的信息。
-- select * from employee where salary>all(第一步);
select * from employee where salary>all(select salary from employee where deptno=30);
（子查询的形式为多行单列，当结果集形式为多行单列时，可以使用all或any关键字）
select * from employee where salary>(select max(salary) from employee where deptno=30);

-- 查询出工资和奖金与马蓉完全相同的人的信息
-- 分析：第一步先查询出马蓉的工资和奖金
select salary,bonus from employee where name='马蓉';
-- 第二步：与马蓉完全相同的人的信息
select * from employee where (salary,bonus) in (第一步);
select * from employee where (salary,bonus) in (select salary,bonus from employee where name='马蓉');

select * from employee where salary=(select salary from employee where name='马蓉') and bonus=(select bonus from employee where name='马蓉');

-- 查询有2个或2个以上直接下属的人的信息。
select leadernumber from employee group by leadernumber having count(leadernumber)>=2;

select * from employee where id in(select leadernumber from employee group by leadernumber having count(leadernumber)>=2);

-- 查询编号是5,的人的姓名，工资，部门名称，部门地址
-- 查询指定列：姓名，工资，部门名称，部门地址，要从2张表中去查。
-- 条件：编号是5的
-- 第一步：先查询一张表
select name,salary from employee where id=5;
-- 第二步：
select deptname,address from dept where deptno=(select deptno from employee where id=5);

-- 分析：不用外链接（外链接的特性：某一行或某些行会出现一半有值，一半为null的情况）
select e.name,e.salary,d.deptname,d.address from employee e,dept d where e.deptno=d.deptno and id=5;

select e.name,e.salary,d.deptname,d.address from (select id,name,salary,deptno from employee) e,(select deptno,deptname,address from dept) d
where e.deptno=d.deptno and e.id=5;

子查询做表，结果集形式是多行多列。
----------------------------------------------------------------------
自连接：自己连接自己。起别名。
-- 查询id是6的人的姓名，领导编号，和领导姓名。
select e1.name,e1.leadernumber,e2.name from employee e1,employee e2 where e1.leadernumber=e2.id and e1.id=6;

select e1.name,e1.leadernumber,e2.name from employee e1,(select id,name from employee) e2 where e1.leadernumber=e2.id and e1.id=6;

-- 求出各个部门工资最高的人的所有信息
select * from employee e,(select deptno,max(salary) maxsal from employee group by deptno) a where e.deptno=a.deptno and e.salary=a.maxsal;
-----------------------------------------------------------------------
Mysql的备份和恢复：
    1，生成脚本，导出数据。
    mysqldump -u 用户名 -p密码 数据库名>生成的脚本文件的路径
    mysqldump -uroot -pabcd mydbone>C:\mydbone.sql
    注意：只包含数据库中的内容，不包含创建数据库的语句。
    无需登录mysql，在控制台中使用。

    2，执行脚本，恢复数据。
    注意：要先创建数据库
    mysql -u用户名 -p密码 已经创建的数据库名称<要执行的脚本文件路径
    mysql -uroot -pabcd mydb8<C:\mydbone.sql