一、配置

1.MYSQL服务的启动

 (1). 手动
 (2). 打开终端
 (3).使用管理员打开cmd
       *  net start mysql   启动mysql的服务器
       *  net stop mysql   关闭mysql的服务器

    终端操作mysql无效则输入后面这句：PATH=”$PATH”:/usr/local/mysql/bin

2.MYSQL登录

  1. mysql  -u root -p      连接的是本机的数据库
  2.mysql -h 127.0.0.1 -u root -p    连接某个ip的数据库
  3. mysql  —host-127.0.0.1 —user=root —password=sf1005213565

3.MYSQL退出

     (1)exit
     （2）quit

4.MYSQL的目录结构

 (1).MYSQL的安装目录
 (2).MYSQL的数据目录
       *几个概念
              *数据库：文件夹
              *表：文件
              *数据

二、SQL

1.什么事SQL？

       Structured Query  Language:  结构化查询语言
        其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为”方言”

2.SQL通用语法

           (1). SQL语句可以单行或多行进行书写，以分号结尾
           (2).可使用空格或缩进提高可读性
           (3).不区分大小写，但是某些关键字建议大写
           (4).  3中注释
                   单行注释：— 我是注释（双横岗一个空格后面是注释）或者 #
                    多行注释：/*  我是注释 */

3.SQL分类

          (1) DDL (Data Definition Language) 数据定义语言
                  用来定义数据库对象：数据库、表、列等。关键字create、drop、alter等

          (2). DML(Data Manipulation Language) 数据操作语言
                  用来对数据库中表的数据进行增删改查。关键字insert、delete、update等

          (3). DQL （Data Query Language）数据查询语言
                  用来查询数据库中表的记录（数据）。关键字：select、where等
          
          (4). DCL  (Data Control Language) 数据控制语言（了解）
                   用来定义数据库的访问权限和安全级别，及创建用户。关键字：Grant、Revoke等

4.DDL：操作数据库、表

         (1).操作数据库：CRUO
               C (create): 创建
                      *创建数据库：create database shifeng;
                                               create database if not exists shifeng;
                      *创建数据库同时设置数据库的字符集
                                               create database db3 character set gbk;
               R (retrieve): 查询
                     *查询所有数据库的名称：show databases;
                     *查看某个数据的字符集:查询某个数据库的创建语句 show create database 数据库名称
              U（updata）:修改
                      *修改数据库字符集
                             alter database db3 character set utf8;
              D（delete）: 删除
                        *删除数据库
                             *drop database 数据库名称;
                             drop database if exists shi; // 存在才删除
              使用数据库
                   *查询当前正在使用的数据库名称
                         *   select database();
                    *使用数据库
                          use shifeng;
      (2). 操作表
       创建：
              Create table 表名(
                列名1    数据类型1,
                列名2    数据类型2，
                。。。
                );


             Create table stu like students; // 创建一个stu表和students表一样
              注意：最后一列不需要加逗号。
              数据库类型：
                   1.double: 小数类型
                          score double(5,2) // 为5位保留2位小数，所以最大数为999.99
                     2.date:  日期，值包含年月日，yyyy-MM-dd
                     3.datetime: 日期，包含年月日时分秒  yyyy-MM-dd HH:mm:ss
                     4.timestamp: 时间戳类型，如果不给这个字段赋值，或者给这个字段赋值为null，则它会放当前时间。 
                     5.varchar: 字符串
                          name varchar(20) 表示姓名最大20个字符，超过会报错。
                     6. text : 存放大文本  允许0到65535字节
           
              查询：
             展示所有的表：show tables;
             查询表结构：desc  表名称;
       修改：
            1.修改表名
                 Alter table 表名 rename to 新的表名
            2.修改表的字符集
                 alter table stu character set utf8;
            3.添加一列
                 Alter table 表名 add 列名 数据类型;
            4.修改列的名称  类型
                Alter table 表名 change 以前列名 现在列名 类型; // 改类型也改列名
                Alter table 表名  modify 现在列名 类型;  // 只改类型
            5.删除列
                 Alter table 表名 drop 列名;
        删除：
               drop table 表名；
                Drop table if exists 表名； 
       (3). 增删改查表中数据
         增加：
         *语法：insert into 表名(列名1，列名2…..列名n) values(值1，值2，、、、值n);
         
         删除：
         * 语法：delete from 表名  where 条件
         delete FROM stu WHERE age = 19; // 表中有多少条记录就要操作几次
         truncate from stu;      // 删除表在创建一个一模一样的表，效率比直接删除表效率高
      改：
        设置某个字段为主键并设置为自增：alter table stu change id id int not NULL PRIMARY KEY auto_increment first;

         语法:
               update 表名 set 列名1=值1；
               update stu set name = "我的神" where id = 4;
               update stu set name = "我的神", age = 34 where id = 8;
               注意：如果不加入条件，则会改该表中的所有数据

DQL：查询语句

1.排序查询

     *语法： order by 子句
              *order by 排序字段  排序方式1,  排序字段2  排序方式2;          哪个字段在前就哪个条件优先
     *排序方式：
            *ASC:  升序  默认
            *DESC:  降序
     *注意：
           *如果有多个排序条件，则当前面的条件一样时，才会判断第二个条件

2.聚合函数：将一列数据作为一个整体，进行纵向的计算。

       (1).count：计算个数       select count(name) from stu; 得到结果为一个数字
       (2).max:  计算最大值  select max(math) from stu;
       (3).min: 计算最小值   SELECT min(math) from stu;
       (4). sum:   计算和
       (5). avg:  计算平均值  select avg(score) from stu;  计算平均分的时候也不会去除以为null的成绩的个数
     注意：聚合函数的计算，排除null值。所以可以由下面方法解决select count(IFNULL(score,0)) from stu;
          解决方案：
             1.选择不包含飞空的列进行计算
             2.IFNULL解决
             3.select count(*) from stu;   表示一行只要有一个非空就会计算

3.分组查询

  1.语法：group by 分组字段;
  2.注意：
        1.分组之后查询的字段：分组字段、聚合函数
            #按照性别分组，分别查询男、女同学的平均成绩，成绩和，和对应人数
select sex, avg(math), sum(math), COUNT(id) from stu group by sex;
           ##按照性别分组，分别查询男、女同学的平均成绩，和对应人数，分数低于30分的人，不参与分组
select sex, avg(math), COUNT(id) from stu WHERE math > 30 group by sex;
          ##按照性别分组，分别查询男、女同学的平均成绩，和对应人数，分数低于30分的人，不参与分组,  分组之后人数要大于2个人的分组
select sex, avg(math), COUNT(id) from stu WHERE math > 30 group by sex having count(id) > 2;
 起了个别名进行判断
 select sex, avg(math), COUNT(id) 人数 from stu WHERE math > 30 group by sex having 人数 > 2;
        2.where和having的区别？
              1.where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
              2.where 后不可以跟聚合函数， having之后可以进行聚合函数的判断

4.分页查询

 1.语法：limit 开始的索引，每页查询的条数；
 2.公式：开始的索引 = (当前的页码 -1)*每页显示的条数
 --每页显示3条记录
       select *from stu limit 0,3;  #第一页

       select *from stu limit 3,3; #第二页

       select *from stu limit 6,3; #第三页
 3.limit 是一个MYSQL的方言

DQL:查询表中的记录

  * select  *  from 表名；
    1.语法
      select
           字段列表
      from 
           表名列表
      where
           条件列表
      group by
           分组字段
      having
           分组之后的条件
      order by
           排序
      limit 
           分页限定
     2.基础查询
         1.多个字段的查询
         2.去除重复: distinct
            select distinct name from stu;
            注意：多个字段去重的时候多个字段都相同才算重复
            select distinct name,math from stu;
         3.计算列
           计算数学和英语的分数和
           select name, math, english , math + english  from stu;
           注意：
               如果有null参与的运算，计算结果都为null
               所以使用ifnull解决
         4.起别名   as 或者不写as
             select name, math, english , math + English as 英语和数学  from stu;

     3.条件查询
         1.where子句后面跟条件
            * >、< 、<=、>=、=、<>不等号
            * between...and    
                   select * from stu where age between 11 and 25;
            * in()
                 下面两句等价
                  select * from stu where age in(18,22,23);

                  select * from stu where age=18 || age=22 || age=23;
            * like 模糊查询
                 *占位符
                     * _:  单个任意字符
                       %: 多个任意字符
                  #查询姓李的由哪些？ 
                  select * from stu where name like "李%";

                  #查询姓名第二个字是四的人
                   select * from stu where name like "_四%";

                   #查询姓名是3个字的人
                   select * from stu where name like "___";

                  #查询姓名中包含李的人
                  select * from stu where name like "%三%";
            * is null
                  注意：
                   null值不能用= null判断，需要用is null
                   is not null 判断不是null

                   select *from stu where name is null;

                   select *from stu where name is not null;
            * and 或 &&
            * or 或 ||
            * not 或 ！

约束：

   概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性。
  分类：
   1. 非空约束：not null
   2. 唯一约束：unique
   3.主键约束：primary key
   4. 外键约束：foreign key

   1.非空约束：not null
     #创建表添加约束
      create table student( id int, name varchar(20) not null);

      #删除name的非空约束
      alter table student modify name varchar(20);

      #创建表完成之后添加非空约束
      alter table student modify name varchar(20) not null;

   2.唯一约束：unique
       注意：唯一约束的字段可以多个为null

       #创建表添加唯一约束
       create table student( id int, phone_number varchar(20) unique);

       #删除唯一约束
       alter table student drop index phone_number;

      #创建表完成之后添加唯一约束
      alter table student modify phone_number varchar(20) unique;

    3.主键约束：primary key
        1.注意：
              1.含义：非空且唯一
              2.一张表只能有一个字段为主键
              3.主键就是表中记录的唯一标识。
        2.在创建表时，添加主键约束
           create table student( id int primary key, #给id添加主键约束
           name varchar(20) );

        3.删除主键约束
           alter table student drop primary key;

        4.创建表完成之后添加主键约束
         alter table student modify id int primary key;

        5.自动增长：
            1.概念：如果某一列是数值类型的，使用auto_increment 可以来完成值的自动增长
            2.创建表添加主键自增长
               create table student( id int primary key           auto_increment, #给id添加主键约束自增长
               name varchar(20) );

            3.删除自动增长约束(主键这样删除不掉的，这样会删除自动增长)
             alter table student modify id int;

            4.创建表完成之后添加自动增长
              alter table student modify id int auto_increment;

    4.外键约束：foreign key
          注意：如果某个表中的键有被其他表作为外键引用了，则此表删除会失败（表中无数据也删除不了）
          1.#在创建表时，可以添加外键
             *语法
                 create table 表名(
                  ...
                  外键列
                  constraint 外键约束名称 foreign key (外键列名称)   references 主表名称(主键，也可以是唯一键));

                  #解决方案：分成两张表
                  #创建部门表（id, dep_name, dep_location）
                  #一方，主表
                  create table department(id int primary key auto_increment,  dep_name varchar(20), dep_location varchar(20));
                  #创建员工表
                   create table employee(id int primary key auto_increment, name varchar(20), age int, dep_id int, 
                   constraint emp_dept_fk foreign key (dep_id) references department(id)
                   );

           2.#删除外键
            alter table 表名 drop foreign key 外键名称;

           3.#创建表后添加外键
            alter table 表名 add CONSTRAINT 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

           4.#级联操作(谨慎使用)
               级联更新： on update cascade
               级联删除： on delete cascade
               当关联的表的外键值改变之后同时自己表的这个字段值也会改变
               当关联的表的某一条数据删除之后，同时自己表中含有此外键值相等的某条数据也会删除掉（相当于裁撤某个部门之后，此部门下的所有人全部裁撤了）
              #创建表后添加外键，设置级联更新
               alter table employee add CONSTRAINT emp_dep_fk foreign key (dep_id) references department(id) on update cascade;

              #创建表后添加外键，设置级联更新，设置级联删除
              alter table employee add CONSTRAINT emp_dep_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;

数据库设计

一.多表之间的关系
   1.分类：
      1.一对一：
               *如：人和身份证
               *分析：一个人只有一个身份证，一个身份证只能对应一个人
       2.一对多（多对一）
               *如：部门和员工
               *分析：一个部门有多个员工，一个员工只能对应一个部门
       3.多对多
              *如：学生和课程
              *分析：一个学生可以选择很多课程，一个课程也可以被很多学生选择

     2.实现关系：
         1.一对多（多对一）
              *如：部门和员工
              *实现方式： 在多的一方建立外键，指向一得一方的主键
         2.多对多
              *如：学生和课程
              *实现方式：多对多的关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向这两张表的主键
          3.一对一：可以合成一张表
               *如：学生和身份证
               *实现方式：一对一关系实现可以在任意一方添加唯一外键来指向另一方的主键。

       3.案例：
            旅游线路分类：
            旅游线路：
            用户：

二.数据库设计的范式
     * 概念：设计数据库时，需要遵循的一些规范。要遵循后面的范式要求，必须先遵循前边的所有范式要求
            设计关系[数据库]时，遵从不同的规范[要求]，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

             目前[关系数据库]有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF)和第五范式（5NF，又称完美范式).
   
      *分类：
           1.第一范式(1NF)：每一列都是不可分割的原子数据项
           2.第二范式(2NF):  在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部门函数依赖）
                *几个概念：
                    1.函数依赖： A-->B，如果通过A属性(属性组)，可以确定B属性的值，则成B依赖于A
                     例如：学号-->姓名    （学号、课程名称）--》分数
                     2.完全函数依赖：A-->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值
                     例如：（学号、课程名称）--》分数
                    3.部分依赖：A-->B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
                      例如：（学号、课程名称）--》姓名
                    4.传递函数依赖：A--》B， B--》C
                      例如：学号--》系名，系名--》系主任
                    5.码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码
                        *主属性：码属性组中的所有属性
                        *非主属性：除过码属性组的属性
           3.第三范式(3NF): 在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF的基础上消除传递依赖）

数据库的备份和还原

   1.命令行的方式：
       *语法：
          备份：  mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
          还原：可以还原出所有数据
                     1.登录数据库
                     2.创建数据库 create database shifeng;
                     3.使用数据库 use shifeng;
                     4.执行文件。soure 文件路径
                            source /Users/fly/Desktop/a.sql;
   2.图形化工具：
        备份：选择数据库-》右键-》转存SQL-》选择路径存储。
        还原：
               1.创建一个数据库
               2.选择该数据库-》右键-》选择执行SQL脚本-》执行

多表查询

    *查询语法：
         select 
              列名列表
         from
              表名列表
         where...

    *笛卡尔积
        *有两个集合A,B，取这两个集合的所有组成结果
        *要完成多表查询，需要消除无用的数据。
    *多表查询的分类：
        1.内连接
             *查询的是两张表的交集部分
            1.隐式内连接：使用where条件消除无用的数据。
              #查询所有员工信息和对应部门信息
             select *from emp,dept where emp.dept_id = dept.id;

             #查询员工表的名称，性别，部门表的名称
             select emp.name, emp.gender, dept.name from emp,dept where emp.dept_id = dept.id;

            #查询员工表的名称，性别，部门表的名称     表取别名
            select t1.name, t1.gender, t2.name from emp as t1, dept as t2 where t1.dept_id = t2.id;
             2.显示内连接：速度比隐士快
               *语法：select 字段列表 from 表名1 inner join 表名2 on  条件
              注意：inner关键字可以省略
               #查询员工表的名称，性别，部门表的名称
               select emp.name, emp.gender, dept.name from emp inner join dept on emp.dept_id = dept.id; 

             3.内连接查询：
                  1.从哪些表中查询数据
                  2.条件是什么
                  3.查询哪些字段
        2.外链接查询：outer关键字可省略
           1.左外连接：
                *语法：
                    select 字段列表 from 表1 left outer join 表二 on 条件; 
                *查询的是左表所有数据以及其交集部分。
                #查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显示部门名称
                select emp.name, emp.gender, dept.name from emp left join dept on emp.dept_id = dept.id;
           2.右外连接：
                *语法：
                    select 字段列表 from 表1 left outer join 表二 on 条件; 
                *查询的是右表所有数据以及其交集部分。

        3.子查询  
           *概念：查询中嵌套查询，称为子查询
          #.查询员工信息，并且等价最高工资的
          select *from emp where salary = (select max(salary) from emp);

          *子查询的不同情况
               1.子查询的结果是当行单列的
                   *子查询可以作为条件，用运算符作为判断
                   #查询员工工资小于平均工资的人
                   select *from emp where emp.salary < (select avg(salary) from emp);
               2.子查询的结果是多行单列的
                   *子查询可以作为条件，使用运算符in来判断
                   #查询销售部和人事部所有员工信息
                  select id from dept where name = "销售部" or name = "人事部";
                  select *from emp where dept_id in(select id from dept where name = "销售部" or name = "人事部");
               3.子查询的结果是多行多列的
                   *子查询可以作为一张虚拟表参与查询。
                 #查询员工的入职日期是2011-11-11之后的员工信息和部门信息
                 select *from dept t1, (select *from emp where emp.join_date > "2011-11-11") t2
                where t1.id = t2.dept_id;

事务

  1.事务的基本介绍
      1.概念：
           *如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
      2.操作：
        1.开启事务：start transaction;
        2.回滚：rollback;
        3.提交：commit;
        #例子:
           #0开启事务
            start transaction
          #张三给李四转账500元
            update account set balance = 1000;
           #1.张三账户-500
             update account set balance = balance - 500 where name = "张三";
          #2.李四账户+500
          #出错了
            update account set balance = balance + 500 where name = "李四";

           #2提交事务
           commit;

         #1发现错误回滚事务
          rollback;

       4.MYSQL数据库中事务默认自动提交
         
        *事务提交的两种方式:
             *自动提交：
                   *msql是自动提交的
                   *一条DML(增删改)语句会自动提交一次事务。
             *手动提交：
                  *Oracle数据库是默认手动提交事务的
                  *需要先开启事务，再提交
         *修改事务的默认提交方式：
             *查看事务的默认提交方式：select @@autocommit; #1代表自动提交   0代表手动提交
             *修改默认提交方式：set @@autocommit = 0;
     
  2.事务的四大特征
      1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
      2.持久性：当事务提交或回滚后，数据库会持久化的保持数据
      3.隔离性：多个事务之间相互独立。
      4.一致性：事务操作前后数据总量不变。
  3.事务的隔离级别（了解）
      *概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就能解决这些问题。
     *存在问题：
         1.脏读：一个事务，读取到另一个事务中没有提交的数据
         2.不可重复读(虚读)：在同一个事务中，两次读取到的数据不一致。当一个人查询的此数据的时候，在提交事务之前后之后就会出现此问题
         3.幻读：一个事务操作（DML增删改）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
     *隔离级别：
          1.read uncommitted: 读未提交
               *产生的问题：脏读、不可重复读、幻读
          2.read commited:读已经提交（Oracle默认 ）
                *产生的问题：不可重复读、幻读
          3.repeatable read:  可重复度（MySQL默认）
                *产生的问题：幻读
          4.serializable:串行化：会把当前操作的表锁住
                *可解决所有的问题

           注意：隔离级别从小到大安全性越来越高，但是安全性效率越来越低。
           *数据库查询隔离级别：
               *select @@tx_isolation;
           *数据库设置隔离级别
               * set global transaction isolation level 级别字符串;

DCL

   *SQL分类：
      1.DDL:操作数据库和表
      2.DML：增删改表中的数据
      3.DQL：查询表中数据
      4.DCL:  管理用户，授权

   *DBA:数据库管理员 

   *DCL: 管理用户，授权
         1.管理用户
              1.添加用户：
                 create user '用户名'@'主机名' IDENTIFIED by '密码';
                 #创建任意用户都可访问的用户
                 create user 'shifeng0'@'%' IDENTIFIED by 'sf1005213565';
              2.删除用户：
                 drop user '用户名'@'主机名';
              3.修改用户密码：
                  #修改shifeng用户的密码
                  update user set password = password('新密码') where User = "用户名";
                  set password for '用户名'@'主机名' = PASSWORD("新密码");

                 *mysql中忘记了root用户的密码
                     1.在终端执行net stop mysql停止mysql服务。需要管理员运行
                     2.使用无验证启动mysql服务：mysqld --skip-grant-tables;
                     3.打开新的终端窗口，输入mysql进入mysql使用use mysql进行修改密码
                     4.关闭两个终端窗口
                     5.停止mysqld服务。
                     6.启动mysql服务
                     7.使用新密码登录
              4.查询用户：
                  #1.切换到mysql数据库
                     use mysql;

                  #2.查询user表
                    select * from user;

                  *通配符：% 表示可以在任意主机使用用户登录数据库
         

       2.权限管理：
             1.查询权限：
                 select grants for "用户名"@"主机名";
                 
                 show grants for 'root'@'localhost';
             2.授予权限：
                  grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
                 grant select,delete,update on P2P.account to 'shifeng'@'localhost';
                 #给张三用户授予所有权限，在任意数据库任意表上
                 grant all on *.* to 'shifeng'@'localhost';

             3.撤销权限：
                 revoke 权限列表  on 数据库名.表名 from  '用户名'@'主机名';
                #移除shifeng的更新权限
                revoke update on P2P.account from 'shifeng'@'localhost';

注意：

1.增加虚拟列
      select @num := @num + 1 , salary from emp, (select @num := 0 as myId) as b;

find_in_set使用

#查询表中的一个字段包含2或4的记录
 select *from route where find_in_set('2', route_pic) or find_in_set('4', route_pic);
 结果为多条记录

 # 查询到的结果按照后面的顺序排序
 select name from route_detaile_pic where find_in_set(id, '4,3,2,5') order by find_in_set(id, '4,3,2,5');
结果为多条记录


#查找路线表route中route_detaile_pic包含的图片id(2,3, 4,5)对应的图片，结果按照图片id(2,3, 4,5)的顺序

# 查询到的结果按照route.detaile_pics中的顺序排序的
 select route.id as 'route_id', route_detaile_pic.id as 'routeDetaileIntroduceImgId', route_detaile_pic.img_width, route_detaile_pic.img_height, route_detaile_pic.name as '详情图介绍'
 from route join route_detaile_pic on find_in_set(route_detaile_pic.id , route.detaile_pics)
 where route.id = 1 order by find_in_set(route_detaile_pic.id, route.detaile_pics);

结果为多条记录，顺序是按照路线表中的图片id顺序来的

group_concat使用

  #查询到的字段数据组成一个新的字符串用逗号隔开, 其中的顺序是按照主键自增的顺序的，结果为一条记录
 select group_concat(id) as routeId from user;