MySQL数据库

一,数据库
    --1,概述
        存储和管理数据的一个仓库,分为两种数据库:关系型数据库和非关系型数据库
        关系型数据库:把数据组织成行和列的类似表的结构
        非关系型数据库:数据间的关系不紧密-NoSQL
        关系型数据库产品:MySQL:免费的 / Oracle:收费的 / SQLServer
        英文是database/DB
    --2,MySQL数据库
        --安装mysql服务器端:
            --设置端口号(默认是3306) + 密码(root) + 编码(默认是latin1->utf-8)
            --用客户端工具连接:
               点击连接-输入连接名-输入密码(检查端口号)-连接测试-连接成功-确定
    --3,数据库的结构
        --数据库--表--行(一条一条的记录)和列(字段)
二,SQL语言
    --1，概述
            用来操作数据库的语言。可以对数据库里的数据进行CRUD增删改查
            分类：    DML-数据操纵语言(CRUD)
                    DDL-数据库定义语言(创建库、创建表)
                      DCL-数据库控制语言(分配具体权限)
                    DQL-数据库的查询语言(各种查的语法)
    --2，用法：CRUD
             --数据库：查询、创建、删除库(谨慎)
             --表：查询、创建、删除、修改
             --!!!记录:创建、查询、删除、修改
    --3，操作数据库的SQL
        --查询所有数据库:show databases;
        --新建数据库:create database 数据库名;
        --新建数据库:create database 数据库名 default character set utf8;将默认的字符集改为utf8;
        --删除数据库:drop database 数据库名;
    --4，操作数据表的SQL
        --使用指定数据库:use 数据库名;
        --查询所有数据表:show tables;
        --新建数据表:
                字段类型：int/long double/float char/varchar
                create table 表名(
                    字段1名字 字段类型(字段长度),
                    字段2名字 字段类型(字段长度),
                    字段3名字 字段类型(字段长度)
                )
                mysql> create table tb_door(
                    -> id int(11),
                    -> door_name varchar(100),
                    -> tel varchar(20)
                    -> );
                 
        --查看数据表结构:desc 表名
            mysql> desc tb_door;
        --修改数据表:alter table 表名 add column 字段名 字段类型(字段长度)
            mysql> alter table tb_door add column addr varchar(100);
            mysql> desc tb_door;
        --删除数据表:drop table 表名;
            mysql> drop table tb_order_detail;
    --5，!!!!操作记录的SQL
        --查询记录:select 字段名 / * from 表名
            mysql> select * from student;
        --添加记录:insert into 表名 values(字段1的值,字段2的值,字段3的值);
            mysql> set names gbk; #防止中文乱码
            mysql> insert into student values(1,'张三',18);
        --修改记录：update 表名 set 字段名 = 字段值
            mysql> update student set age=100;
            mysql> update student set name='tony'; #字段如果是字符串要加'??'单引号
        --删除记录：delete from 表名
            mysql> delete from student;        

三,字段的约束
    --1,主键约束
        --是指可以给指定的字段添加一个主键约束,来达到 主键的值是唯一的+不能位空 的效果
        --mysql> create table a(id int);                #id不是主键可以重复可以为空
        --mysql> create table b(id int primary key);    #id是主键必须唯一不能为空
        --mysql> create table c(id int primary key auto_increment);        #主键自动递增(从1开始)
    --2,非空约束        
        --是指给特定的字段,添加非空约束,达到 字段值不能为空 的效果
        --mysql> create table d(id int primary key auto_increment,
                -> name varchar(10) not null         #not null就是对name字段的非空约束
                -> );
    --3,唯一约束
        --是指给字段加上唯一约束,达到 字段的值必须唯一 的效果    
        --mysql> create table e(id int primary key auto_increment,
                -> age int not null unique             #unique是对age字段的值的唯一约束
                -> );  

        
四,基础函数
    --1,概述
        MySQL提供了很多函数,可以快速操作数据.
    --2,各种函数的使用
        --基本函数：lower \ upper \ length \ substr \ concat \ replace \ ifnull \ uuid
           --小数的函数: round \ ceil \floor
         --日期的函数: now \ year \ month \ day \ hour \ minute \ second \转义字符
    --#MySQL的函数
            desc dept;                                  #查看结构
            select * from dept;                      #查询所有字段的值-低效; 就是查看所有的记录;
            select dname from dept;                  #只查询dname字段的值-高效
            select dname,deptno from dept;             #只查询dname,deptno字段的值-高效
            select dname,lower(dname) from dept;    #lower(参数)-把参数变小写
            select dname,upper(dname) from dept    ;#upper(参数)-把参数变大写
            select dname,lower(dname) from dept;    #查3列

            --#length              (参数)-获取参数的长度 
            select length(dname),length(loc) from dept;

            --#substr(1,2)       -1是要截取谁2是从哪儿开始(从1开始) 
            select dname,substr(dname,3) from dept;

            --#substr(1,2,3)      -1是要截取谁 2是从哪儿开始(从1开始)3是截取几个
            select dname,substr(dname,2,3) from dept;

            --#concat()          -拼接字符串
            select dname,concat(dname,'hello') from dept;
            
            --#replace(1,2,3)    替换  -1是准备要替换的字段  2是要被替换的字符  3是新字符 注意不可以替换null;
            select dname,replace(dname,'c',6) from dept;
            
            --#ifnull(1,2)-1是字段名2是要被替换成的值
            --#ifnull如果是null可以替换成新值
            select comm,ifnull(comm,1) from emp;

        --#对小数的处理
            #round(四舍五入) & ceil(向上取整) & floor(全舍)
            select comm,ceil(comm),floor(comm),round(comm) from emp;
        --#对日期数据的操作
            select now() ;             #获取年月日时分秒
            select CURDATE();         #获取年月日
            select CURTIME();         #获取时分秒
            #year() month() day()
            select now(),year(now()),month(now()),day(now())
            #hour() minute() second()
            select now(),hour(now()),minute(now()),second(now())
            select uuid()             #得到32位的字符,得到一个几乎不重复的id
            select 'xi\'an'            # '是SQL里的特殊符号,想作为普通字符使用需要转义\

    --3,条件查询
        --1,distinct/where/like/order by/limit
        --#SQL的执行顺序? from > where >  order by > limit
        --2,练习
            select 'xi\'an'#'是SQL里的特殊符号,想作为普通字符使用需要加转义符\
            select distinct loc from dept;         #distinct给字段的值去重
            
            #给查询结果 加一个过滤条件
            #where后面就是一个条件语句
            #多个条件间的关系: 并且and  或者or
            #SQL执行的顺序:from > where > select 
            select * from emp where 字段名 = 字段值;  
            select * from emp where true       #判断条件是true,直接执行

            #查询sal是8000,10000,20000的记录
            select * from emp where sal in(8000,10000,20000)        #等于8000或者等于10000或者等于20000
            select * from emp where sal not in(8000,10000,20000)    #不等于8000或者等于10000或者等于20000

            #between and  过滤
            #过滤工资在[8000,20000]的
            select * from emp where sal between 8000 and 20000    #[8000,20000]

            #like 模糊查询
            select * from emp where ename like '%a%';    #中间包含着a
            select * from emp where ename like 't%';    #以t开始后面有几个都行
            select * from emp where ename like '%y';    #以y结束前面有几个都行

            #按照null过滤数据
            select * from emp where comm is null         #过滤出来是null的记录
            select * from emp where comm is not null    #过滤出来不是null的记录

            
            #limit 分页技术
            select * from emp limit 3         #只取前三条
            select * from emp limit 1,3     #从第二条开始取,取总共3条
            select * from emp limit 2,2         #从第三条开始取,取总共2条

            #order by 排序
            select * from emp order by sal             #默认升序,按数字的从小到大,null放最上面,字母按自然顺序
            select * from emp ORDER BY sal desc     #默认是升序,desc降序

            #2017年以前入职的员工信息
            select * from emp where year(hiredate) < 2017      //获取字段的年份在比较
            select * from emp where hiredate < '2017-1-1'   //直接将两个字符串比较
            
            #公司福利不错13薪,统计员工的年薪
            select ename,job,sal,comm,(sal+ifnull(comm,0))*13 from emp 
            
    --4,聚合函数
        --对一列的结果进行运算
        --count \ sum \ avg \ max \ min
        --练习
            #count \sum \avg \max \min
            select count(*) from emp     #统计emp的记录总数-低效
            select count(1) from emp     #高效
            select count(id) from emp     #高效 查询字段     //注意需要id为主键而且不能为空
            select max(sal) from emp     #max    获取sal的最大值
            select min(sal) from emp     #min    获取sal的最小值
            select avg(sal) from emp     #avg    获取sal的平均值
            select sum(sal) from emp     #sum    获取sal的求和
            #获取每个部门的,最高薪    
            #group by----分组函数
            select deptno,min(sal),max(sal) from emp group by deptno
            记住: #分组--聚合函数以外的列都需要分组,group by后面要想加条件只能是使用 having
--五.分组
        --1，概述
            把数据按照一个规则划分成一个一个的组
            对分组后的数据进行过滤就用固定搭配having
        --2，语法
            group by 字段 having 要过滤的字段
        --3,执行顺序
            #执行顺序:from > group by  > having > select
            #执行顺序:from > where > group by > select
            #group by是分组,要过滤固定搭配having
--六.事务:
    --1,概述
        为了保证多条SQL语句要么同时成功,要么就同时失败
        MySQL默认就开启了事务,一条SQL一个事务
        如果想实现多条SQL在一个事务里执行,只能手动管理事务
        开启事务:start transaction / begin
        结束事务:commit(成功就提交,失败就自动回滚)/rollback(回滚)
    --2,事务的4个特性
        ACID
        A是原子性--是指多条SQL要么都成功要么都失败
        C是一致性--总和守恒
        I是隔离性--数据库允许高并发,同时增删改查数据
        D是持久性--是指对数据的操作持久有效
    --3,事务的隔离级别
        读未提交--效率最高,但是安全性最差(多并发时数据可能出错)
        读已提交--效率较低,但是安全性较高(oracle数据库的默认级别)
        可重复读--效率较低,但是安全性较高(MySQL数据库的默认级别)
        串行 -- 效率最低,安全性最高(实现表级锁)
--七.索引
    --概述
        为了提交数据的查询效率,可以给指定的列加索引
        哪些列需要加索引?--常用来做查询条件的就加索引
        分为几种:单值索引(一个索引只有一个列)
                 唯一索引(索引列的值必须唯一)
                 复合索引(一个索引可以包含多个列)
        --查看索引，主键会自动创建索引
            SHOW INDEX FROM 表名
        --创建索引
            CREATE INDEX 索引名 ON 表名 (字段名)
        --删除索引
            ALTER TABLE 表名 DROP INDEX 索引名
        --创建唯一索引 --索引列出现的值必须唯一
            ALTER TABLE 表名 ADD UNIQUE (字段名)
        --创建复合索引  就是两个字段使用了一个索引
            ALTER TABLE teachers ADD INDEX fuhe_index(prof,depart)
--八.关联查询
    --概述
        完成多表的关联查询,要知道查哪些表,表之间有什么关系
        分为: 笛卡尔积 / inner join 关联查询 / 子查询 
        --笛卡尔积加过滤条件就是指多表的关联关系        
        --inner join 内关联/ left join 左关联/ right join 右关联
        --子查询就是相当于两句放在了一句,后面的放在括号里面
        #查询emp和dept表,过滤dname='research'的数据
            #笛卡儿积方式
            select * from dept,emp 
            where dept.deptno=emp.deptno#表间的关联关系
            and dept.dname='research';    #过滤条件
            #关联查询方式
            select * from dept 
            inner join emp 
            on dept.deptno=emp.deptno#表间的关联关系
            where dept.dname='research'# 过滤条件
            #子查询方式
            select * from dept where dname = 
            (select deptno from dept where dname = 'research')
--九,SQL优化
    --1,查询语句里,用字段名代替* 
            select id,name,age from user
    --2,过滤条件尽量不要用or,难以避免
            select id,name,age from user where id=1 or name='123'
    --3,表设计时,字段的类型,最好用varchar代替char
            --char是固定长度,可能浪费空间,varchar长度可变
    --4,模糊查询,最好先确定前面的数据
            select id,name,age from user where name like 't%'
    --5,字段的值,尽量用数字不用字符串
            --查的快,省空间,1个数字只占一个字符,但是一个汉字要占3个字符(utf8)    
    --6,查询的结果范围尽量小,数据量尽量少
            select id,name,age from user where name='123' and id=3
    --7,给字段加索引
            #如果name有索引,由于123这个值写的不规范,会导致索引失效
            select id,name,age from user where name=123 
    --8,每个列都可以加索引,但是不能太多,最好不能超过5个
    --9,对字段的值可以去重,使用distinct关键字,但是不要对太多列去重
            select distinct * from user
    --10,过滤条件里最好不要用!=  <>  
    --11,尽量给null列设置默认值
    --12,批量操作
            --需要一次性删除多条(10000)时,最好分批删除(按照500个的方式删除多次)
            --增加大量数据时,可以只发起一个insert语句,来减少事务的开启和结束
                insert into user(id) values(1),(2),(3),(4),(5),(6),(7)
    --13,group by
            --尽量把数据的范围控制到最小然后再分组        
            select id,count(*) from user group by id having id > 100 #低效
            select id,count(*) from user where id > 100 group by id   #高效
    --14,伪删除设计
            真实的开发中,很少真正的删除数据,而是把要删除的数据改变状态1 -> 0
--十,扩展三范式
    数据库设计三范式（重点内容 面试经常问）
    按照三范式设计的表不会出现数据的冗余；
    三范式都是那些
    第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分
    第二范式：建立在第一范式基础上，并且要求所有的非关键字段完全依赖主键，不能产生部分依赖
    多对多 三张表 关系表两个外键
    第三范式：建立在第二范式基础上，所有非主键字段直接依赖主键，不能产生传递依赖
    一对多 两张表 多的表加外键
    三范式就是为了不会产生数据的冗余
    在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度.
--.十一,笛卡尔积现象
    笛卡尔积现象（笛卡尔乘积现象）：当两张表进行连接查询的时候 没有任何条件进行限制 最终的查询结果条数是两张表记录的乘积
    避免了笛卡尔积现象 不会减少记录的匹配次数 只不过显示的是有效记录.