数据库基础
一、数据库的基本概念
1.什么是数据库(DataBase)
用于存储和管理数据的仓库,简称DB
2.数据库的特点***
1)持久化存储数据的(硬盘).实际上数据库就是一个文件系统
2)方便存储和管理数据(对数据进行增删改查)
  -DBMS(数据库管理系统)
      MySQL--->很多数据库--->很多表--->很多列--->具体数据
3)使用了统一的方式操作数据库--SQL
  多种DBMS通用的语法--SQL
3.常见的数据库软件
   MySQL 默认端口是3306
   Oracle
   db2
二、MySQL数据库-端口号3306
1.MySQL数据库的安装
  详见操作手册
2.MySQL建议用360卸载
  详见操作手册
3.配置
  *查看状态
    1.win+R services.msc
    2.netstat -ano | findStr"3306"
  *开启服务(管理员身份运行cmd)
    net start mysql
  *关闭服务(管理员身份运行cmd)
    net stop mysql
4.MySQL的登陆和退出
1)本地mysql
  *MySQL数据库的登陆(本地)
  -mysql -u+用户名 -p
   输入密码(此时密码是密文显示)
  -或者mysqul -u+用户名 -p+密码
  -当前用户密码 mysql -uroot -proot
  *MySQL数据库的退出
   -exit/quit
2)远程登陆
  *MySQL数据库的登陆(远程)
    mysql -h+ip -u目标的用户名 -p目标的密码
  *退出
   -exit/quit
5.MySQL的目录结构(了解)
1)MySQL安装目录
  my.ini是配置文件,可以修改
2)MySQL数据目录
  在my.ini可以查看到
  #Path to the database root
  datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
3)几个概念:
  数据库-文件夹
  表-文件
  列-表的一部分,相当于表头
  数据-存储的数据
6.SQL语法*****
1)什么是SQL?--Structure Query Language:结构化查询语言
它定义了操作所有关系型数据库(Relational DataBase)的规则
2)SQL的通用语法
  *SQL语句可以单行或多行书写,以分号结尾
  *可以使用空格和缩进来增强语句的可读性
  *MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
  *3种注释
     -单行注释: -- 注释 或 # 注释
     -多行注释: /* 注释 */
     注意:空格一定要打, #注释是MySQL的特有语法,可以不用空格,建议使用通用语法
3)SQL的分类*****面试可能会用到
  *DDL 数据定义语言-Data Definition Language
     操作数据库的语言:对数据库和表的增删改查
  *DML 数据操纵语言-Data Manipulation Language
     增删改表中的数据
  *DQL 数据查询语言-Data Query Language
     查询表中的数据
  *DCL 数据控制语言-Data Control Language
     对用户权限的设置
7.DDL:操作数据库、表 - 以文件夹SQL语句内容为主
1)操作数据库:CRUD
  *C(Create):创建  CREATE
     SELECT DATABASE();
  *R(Retrieve):查询 SHOW
  *U(Update):修改  ALTER
  *D(Delete):删除  DROP
  *使用数据库:USE 数据库名称;   不是USE DATABASE 数据库名称
2)操作表:CRUD
  *C(Create):创建  CREATE
     -CREATE TABLE 表名(
            列名1 数据类型1,
                列名2 数据类型2,
                ...
                列名N 数据类型N
                )
           注意:最后一列,不需要加逗号.
     -数据库类型:
            **int 整数类型
                **double 小数类型 double(5,2)表示长度5,小数点后保留两位
                **date 日期,只包含年月日, yyyy-MM-dd;
                  datetime 日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
                  timestamp:时间戳类型:包含年月日时分秒 *****和datatime的区别,面试
                  如果不给这个字段赋值,默认为当前系统时间
                **字符串*****面试题
                  varchar:字符串  varchar(20) 表示最大20个字符
                  char:char(长度)
                  text:定义不需要给长度,保存超大文本/和文本文件
                  注意:char和varchar在定义的时候必须给长度 varchar(30),char(20)
                  char和varchar的区别:如果存入长度小于给定长度,char用空格补满
                                     而varchar的长度根据输入字符串内容改变
    -复制表 CREATE TABLE stu LIKE student;
  *R(Retrieve):查询 SHOW TABLES/DESC(descraption) 表名
                    SHOW CREATE TABLE
  *U(Update):修改  ALTER
      -修改表名 ALTER TABLE 表名 RENAME TO 新的表名
                    RENAME TABLE 表名 TO 新表名
      -修改表的字符集 ALTER TABLE 表名 CHARACTER SET 字符集名
      -添加一列 ALTER TABLE 表名 ADD 列名 数据类型;
      -修改一列 ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
                    ALTER TABLE 表名 MODIFY 列名 新数据类型;
      -删除列:  ALTER TABLE DROP 列名;
  *D(Delete):删除  DROP
8.DML:增删改表中的数据
1)添加数据:
    *语法:
          INSERT INTO 表名(列名1,列名2,...,列名N) VALUES(值1,值2,...,值N),(值1,值2,...,值N)
    *注意:
          列名和数据要一一对应
          如果表名后不写内容,则给所有列添加值
      除了数字类型,其他类型需要使用引号引起来,单双引号都可以
2)删除数据
    *语法
          DELETE FROM 表名 WHERE 条件;
          TRUNCATE TABLE 表名;
    *注意
          如果不加条件,则删除表中所有记录,如果要删除所有数据,建议使用TRUNCATE TABLE,效率高
          truncate先删除表,再创建一张一模一样的表,效率高,推荐使用
    *TRUNCATE 和 DELETE FROM 的区别*****面试题
         --DELETE属于DML(可以通过事务进行还原),TRUNCATE属于DDL(不能通过事务进行欢颜)
3)修改数据
    *语法
          UPDATE 表名 SET 列名 = 值,列名2=值2 WHERE 条件
    *注意:
          如果不加条件,表内的所有数据都会被修改
9.DQL:查询表中的记录
    *SELECT * from 表名;


SQL查询语句
一、DQL查询语句
1.基本查询
   *IFNULL(字段,替换的值) 字段的值为NULL则改为的替换的值
   * 字段 AS 别名 可以给字段写别名
       AS可以省略,直接谢伟字段 别名
   *公式!******
   SELECT 列名 FROM 表名
   WHERE 条件
   GROUP BY 列名 HAVING 条件
   ORDER BY ASC/DEDC
   LIMIT 索引,条数
2.条件查询
   *运算符
      -<>不等,=等于,!=不等(不建议)
      -BETWEEN 值 AND 值 是闭区间
      -IN(集合)
      -LIKE 模糊查询  %任意多个字符,_一个字符
      -&& || ! 建议用AND 和 OR NOT
3.排序查询
  *语法:
     ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2
  *排序方式:
     ASC:升序,默认的 Ascending
         DESC:降序   Descending
  *注意:
     如果有多个排序规则,只有当前一个规则生效后后一个规则才会生效
4.聚合函数->将一列数据作为一个整体,进行纵向计算
1)COUNT:计算个数
    -一般选择非空的列来计算(主键)
        -COUNT(*) 也可以避免自动排除NULL的问题,但不推荐使用
2)MAX:计算最大值  数字/日期
3)MIN:计算最小值  数字/日期
4)SUM:求和        数字
5)AVG:计算平均值  数字
6)语法:
   SELECT 聚合函数(字段) FROM 表名称
7)注意:聚合函数一般都是配合分组使用
注:聚合函数的计算会排除NULL值
5.分组查询
  *语法:
    GROUP BY 分组字段
  *注意:
    分组之后查询的字段必须为分组字段或聚合函数
  *WHERE和HAVING的区别-******面试题可能考
     -WHERE在分组之前进行限定,如果不满足条件,则不参与分组;HAVING在分组之后进行限定,如果不满足条件,则不会被查询出来
     -WHERE后不可以跟聚合函数,HAVING可以进行聚合函数的判断
6.分页查询
  *语法:
     LIMIT 开始的索引,每页查询的条数
  *公式:
     开始的索引 = (页码-1)*每页显示的条数
  *注意:
     LIMIT语法是一个"方言",只能在MySQL中使用
二、约束
1.约束的概念
   对表中的数据进行限定,保证数据的准确性、有效性和完整性
2.约束的分类
1)主键约束:PRIMARY KEY  [AUTO_INCREMENT]
2)非空约束:NOT NULL
3)唯一约束:UNIQUE
4)外键约束:FOREIGN KEY
3.非空约束:NIT NULL
  *语法-创建约束
    CREATE TABLE 表名(字段 值 约束);
    ALTER TABLE 表名 MODIFY 字段 数据类型 约束
    当后面的约束为空时,可以认为改为没有约束
4.唯一约束:UNIQUE
   *注意:
      唯一约束可以有NULL值,但只能有一个
      唯一约束必须用DROP INDEX字段来删除
        -ALTER TABLE 表名 DROP INDEX 列名
          如果当前表中有唯一约束,那么添加不了唯一约束
5.主键约束:PRIMARY KEY*****
   *语法:PRIMARY KEY AUTO_INCREMENT;
   *注意:
        -主键约束要求主键非空且唯一
                -一张表中只有一个字段为主键
                -主键就是表中记录的唯一标识
   *AUTO_INCREMENT-自动增长
      如果某一列是数值类型的,使用AUTO_INCREMENT可以来完成值的自动增长
   *主键的删除:
      ALTER TABLE 表名称 DROP PRIMARY KEY;
6.外键约束:FOREIGN KEY*****
1)在创建表时,可以添加外键
   *语法:
    CREATE TABLE 表名 (
        ...
        CONSTRAINT 外键名称 FOREIGN KEY (从表列名) REFERENCES 主表(主表主键) -- CONSTRAINT 外键名称可以省略
        );
        -主表:被依托的表
        -从表:谁有外键谁就是从表
   *删除外键
    ALTER TABLE 从表名 DROP FOREIGN KEY 外键名
   *创建表之后添加外键
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名称)
    ALTER TABLE 表名 ADD FOREIGNKEY (外键列名) REFERENCES 主表名称(主表列名称)
           CONSTRAINT 外键名称可以省略
2)级联操作(了解)-记住关键字CASCADE即可
   *添加级联操作
     语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
         FOREIGN KEY(外键字段名称) REFERENCES 主表(主表列名称)
         ON UPDATE CASE ON DELETE CASE;
   *级联操作:
         ON UPDATE CASCADE
         ON DELETE CASCADE;
   *级联操作比较危险,效率也不高,尽量慎重使用
三、数据库的设计
1.多表之间的关系
1)一对一(了解)
2)一对多(多对一)
   如:部门和员工
   分析:一个部门又多个员工,一个员工只能对应一个部门
3)多对多
   如:学生和课程
   分析:一个学生可以有多个课程,一个课程也可以有很多学生
2.多表关系的实现
1)一对一
   在其中一方创建一个外键列(值不能重复),指向另一方的主键列
2)一对多(多对一)
   在多的一方创建外键列,指向一的一方主键列
3)多对多
   创建一个中间表,至少有两列,创建外键分别指向2个主表的主键
   *联合主键:中间表中的主键对不重复
        PRIMARY KEY(主键1,主键2)      
3.数据库设计的范式(当前了解即可)-以下所有笔记应结合视频理解
1)概念:设计数据库时,要尊徐一些规范.
       要遵循后边的范式要求,必须先遵循前边的所有范式要求
2)分类-第1/2/3/巴斯-科德/4/5范式,当前只学习123范式
   *第一范式(1NF)
     每一列都是不可分割的原子数据项
   *第二范式(2NF)
     在1NF的基础上,非码属性必须完全依赖于候选码
         (在1NF基础上消除非主属性对主码的部分函数依赖)
   *第三范式(3NF)
     在2NF的基础上,任何非主属性不依赖于其他非主属性
         (在2NF基础上消除传递依赖)
3)第一范式:1NF
   *原子数据项:仅有一列,没有子列
    数据库创建表时必然满足第一范式
   *第一范式存在的问题:
    -存在非常严重的数据冗余
    -数据添加存在问题
    -数据删除存在问题
4)第二范式:2NF
   *几个概念:
      -函数依赖: A-->B,如果通过A的属性(或属性组)的值可以确定唯一B属性的值
      -完全函数依赖:A-->B,如果A是一个属性组,则B属性值的确定需要依赖A属性组中所有的属性值
      -部分函数依赖:A-->B,如果A是一个属性组,则B属性值的确定需要依赖A属性组中部分的属性值
      -传递函数依赖:A-->B, B-->C
            如果通过A的属性(或属性组)的值可以确定唯一B属性的值,再通过B的属性可以确定C属性的值
      -码:如果在一张表中,如果一个属性或属性组被其他所有属性组完全依赖,则称此属性或属性组为该表的码
           **主属性:码属性组中的所有属性
           **非主属性:除了码属性组的属性
   *第二范式存在的问题:
      -数据添加存在问题
      -数据删除存在问题
5)第三范式:3NF
   解决了传递依赖
   不存在主要问题
4.数据库的备份和还原
  详见 MYSQL语句-重置密码&备份还原数据库,一般用不着
5.总结公式:
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 条件 ORDER BY ASC/DESC LIMIT index,rows
CONSTRAINT 外键名称 FOREIGN KEY (从表列名) REFERENCES 主表(主表主键)


多表
一、多表查询
1.多表查询的分类
  *内连接查询
  *外连接查询
  *子查询
  *笛卡尔积:
     集合A,集合B,区多表中数据的所有组合情况
     要完成多表查询,需要消除笛卡尔积中无用的数据
2.内连接查询:
1)隐式内连接:使用WHERE消除无用数据
   *语法:
      SELECT 字段 FROM 表1,表2 WHERE 关联条件 AND 普通条件
   *例子:
    SELECT t1.name, -- 注释
          t1.gender,-- 注释
          t2.name -- 注释
    FROM
          emp t1, -- 注释
          dept t2 -- 注释
    WHERE
       t1.dept_id = t2.id;
2)显式内连接
   *语法:SELECT 字段 FROM 表1
    [INNER] JOIN 表2 ON 关联条件
    [INNER] JOIN 表3 ON 关联条件
         WHERE 普通条件
          -INNER 是可选操作,可以不写
   *例子:
          SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
3)内连接查询的思维逻辑
   *从哪些表中查询数据
   *查询条件是什么
   *查询哪些字段
4)内连接查询的是左右两表的交集部分****
3.外链接查询
1)左外连接
   *语法:SELECT 字段 FROM 表1
         LEFT [OUTER] JOIN 表2 ON 关联条件
         LEFT [OUTER] JOIN 表3 ON 关联条件
                 WHERE 普通条件
   *左外连接查询的是左表所有数据及左右表交集部分****
2)右外连接
   *语法:SELECT 字段 FROM 表1
   RIGHT [OUTER] JOIN 表2 ON 条件
   RIGHT [OUTER] JOIN 表3 ON 条件
   WHERE 普通条件
   *左外连接查询的是右表所有数据及左右表交集部分****
3)内连接与外链接的区别****
   -内连接查询的是连接的表的交集
   -外链接查询的是左(右)表的所有数据及与连接的表的交集
4.子查询
   *概念:查询中嵌套查询,称嵌套的查询为子查询
   *例:
       SELECT *FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
   *子查询的不同情况
     -标量子查询:
           子查询可以作为条件,使用运算符去判断
           例:查询工资小于平均工资的人
           SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
     -列子查询:子查询的结果是N行1列
           子查询可以作为条件,使用操作符去判断
           例:查询部门是财务部或市场部的人
           SELECT * FROM emp  WHERE dept_id IN (SELECT id FROM  dept WHERE NAME IN('财务部','市场部'));
     -行子查询:子查询的结果是1行N列
     -子查询的结果是多行多列的:
           子查询可以作为虚拟表,使用内/外连接去查询
           例:查询员工的入职日期是2011-11-11日之后的员工信息
           SELECT *
           FROM (SELECT * FROM emp WHERE join_date > '2011-11-11') t1, dept t2
           WHERE t1.dept_id = t2.id;
二、事务****面试用
1.事务的基本介绍
1)概念:
    如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败
2)操作
    *开启事务 START TRANSACTION
        *回滚 ROLL BACK  --如果操作的结果和预期不符,可以使用该语句回滚
        *提交 COMMIT  --如果操作的结果和预期相符,可以用该语句提交事务
3)MySQL数据库中事务默认自动提交
    *如果没有手动开启事务,一条增删改语句会默认开启并提交一次事务
        *提交方式
            -自动提交:
                   *MySQL数据库默认是自动提交事务
           -手动提交:
                   *Oracle 数据库默认是手动提交事务
                   *手动开启事务,手动提交事务
           -修改事务的默认提交方式
                 --查看:SELECT @@autocommit
                 --修改:SET @@autocommit = 0;
2.事务的四大特征*****面试题
1)原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2)持久性:当事务提交或回滚后,数据库会持久化的保持数据
3)隔离性:多个事务之间相互独立
4)一致性:事务操作前后,数据的总量不变
3.事务的隔离级别
1)概念:多个事务之间是隔离的,相互独立的,但是如果多个事务操作同一批数据,则会引发一些问题
2)存在问题
  *脏读:一个事务,读取到另一个事务中没有提交的数据
  *虚读:指一个的事务读取到了另外一个事务中提交的insert的数据
  *不可重复读:指一个的事务读取到了另外一个事务中提交的UPDATE的数据
  *幻读:一个事务操作(DML)数据表中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
3)隔离级别****面试题
  *read uncommitted:读未提交
    -产生的问题:脏读,虚读,不可重复读,幻读
  *read committed:读已提交 (Oracle默认)
    -产生问题:不可重复读,幻读
  *repeatable read:可重复读(MySQL默认)
    -产生的问题:幻读
  *serializable:串行化
    -可以解决所有问题
  *注意:隔离级别从小到大安全性越来越高,但是效率越来越低
  *设置隔离级别
    -SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别