MySQL第一周

数据库: 
    MYSQL:RDBMS(Relationship Database Management System)
                关系型数据库管理系统，运行在后台，Server进程，看不见
    DB: Database 数据库
    Table: 表 由横向和纵向组成
    Record: 一条记录
    column: 字段
        多个字段组成一条记录，多条记录组成一张表，多张表组成数据库，多个数据库构成数据库管理系统
    SQL语句: Structure Query Language 结构化查询语言
    登录命令: mysql -uroot -proot不安全
            mysql -uroot -p **** 安全，支持无密码登录
    远程登录: mysql -h localhost -P 3306 -u root -p root 
    
    基础语句: show databases;显示当前数据库管理系统中的所有数据库
            use 数据库名; 使用指定的数据库
            show tables;显示当前数据库有多少表
            selection version();显示MySQL数据库的版本号
            desc 表名;显示表结构
    DDL:(Database Definition Language)数据库定义语言
        1.创建一个新的数据库: create database 数据库名; 
        2.创建一个新的表:  create table 表名(
                        字段1 数据类型(长度),
                        字段2 数据类型(长度),
                        字段3 数据类型(长度));   
        3.删除表: drop table 表名;
          删除数据库: drop database 数据库名;
        4.修改表结构: 
            (1)添加字段: alter table 表名 add [column] 字段 数据类型(长度);
            (2)删除字段: alter table 表名 drop [column] 字段;
            (3)修改字段类型: alter table 表名 modify [column] 字段 新数据类型(长度);
            (4)修改字段名: alter table 表名 change [column] 旧字段 新字段 数据类型(长度);    
            (5)修改表名: alter table oldName rename to newName;            
    DML:(Database Manipulation Language)数据库操纵语言        
        1.添加数据: insert into 表名(字段列表), values(值列表);    
            例子:(1)常规: insert into person(id,name,sex) values(1001,'张三','男');
                (2)不加字段列表，只有值列表，值列表中值的顺序必须和给定的字段列表顺序一致
                         insert into person values(1002,'李四','男');
                (3)允许字段列表乱序，值列表必须与字段列表的顺序一致
                         insert into person(name,sex,id) values('王五','男',1003);
                (4)批量插入，一般用于测试
                         insert into person(id,name,sex)
                                values(1004,'赵六','男'),
                                values(1005,'貂蝉','女'),
                                values(1006,'西施','女'),
                                values(1007,'昭君','女');
        2.删除数据: 慎用，如果真的有需要删的，尽量去备份
                (1)物理删除: delete from 表名;      (一定要备份)
                           delete from 表名    where 唯一字段 = 值;            
                ()逻辑删除:修改状态
                        更新数据库: update 表名 set 字段 = 新值 where id = ?;
                                  update 表名 set 字段1 = 新值,set 字段2 = 新值 where id = ?; 
        3.查找: select * from 表名 where id 或 status = ?;
        4.表复制: create table 新表名 as select * from 旧表名;
                 create table 新表名 as select * from 旧表名 where 1 = 2;        
=====================================================================================================            
    数据库约束： 6种
    1.非空约束: not null
    2.唯一约束: unique
    3.检查约束: check MySQL不支持
    4.默认约束: default
    5.主键约束: primary key
    6.外键约束: foreign key(自定义的段，一般与主表的主键名一样) reference 主表名(主键名)
表和表之间的关系:
            1 <--->1 
            1 <--->多
            多<--->多
----------------------------------------------------------------------------------------------------
DQL:  Database Query Language 数据库查询语言
        select [字段列表] from 表名
                        [where] 条件语句
                        [group by]  分组  
                        [having] 条件语句  先过滤在分组
                        [order by] 排序  asc 升序  desc 降序
                        [limit  开始行，总记录数] 分页
            1.where  条件
             登录：            
               (1)select * from `user` where name = '张三' and password = '123';
               查询年龄是18岁或者25岁的
               (2)select * from `uset` where age = '18' or name = '25';
               (3)select * from `user` where age in(18,25);   
            2.group by 一定是根据谁分组查询谁，要使用分组函数
                分组函数：sum(), avg(), max(), min(), count(1)
            3.having
               select * from emp group date having age > 18;
            4.order by   order by 字段名
               select * from emp order by age asc;升序
               select * from emp order by age desc;降序
            5.limit         limit 开始行，总记录数    pageNo 页码
                    startRow = (pageNo -1)*pageSize;
结果集 ResultSet        
===================================================================================================
模糊查询：
        like     % 通配符   _ 占位符
        查询名字中第三个字母是A的员工的信息
        select * from emp where ENAME like '__A%';        
表关联查询：
        sql92 版本
            内连接: 等值连接: select * from beauty,boys   查询女明星的男朋友的信息
                                    where beauty.boyfriend_id = boys.id;
                   不等值连接: select ename ,sal from emp,salgrade   查询工资在losal到hisal之间的员工的名字和工资
                                    where emp.sal between salgrade.losal and salgrade.hisal;                   
                   自连接:     
            外连接:(支持的不好，所以舍弃了)
        sql99 版本    
            内连接: 等值连接: select * from emp inner join dept on emp.deptno = dept.deptno            
                   不等值连接: select * from emp inner join salgrade on emp.sal between salgrade.losal 
                                        and salgrade.hisal;
                   自连接: select * from emp e1 inner emp e2 on e1.MGR = e2.EMPNO;
            外连接:  外连接的结果 = 内连接的结果 + 主表有 从表没有的数据
                   左外连接: select * from beauty b1 left join boys b2 on b1.boyfriend_id = boys.id;
                   右外连接: select * from beauty b1 right join boys b2 on b1.boyfriend_id = boys.id;
                   全外连接: full join  MySQL不支持
                   交叉连接cross join 故意出现笛卡尔积
                          select * from beauty b1 cross join boys  b2;
子查询: select * from emp where DEPTNO = (select DEPTNO from emp where ENAME = 'SCOTT');

distinct去重        
====================================================================================================
mysql 中的函数
    1.数学函数: abs() 绝对值   PI() 返回PI值   POW(a,b)   返回b的a次方
    2.字符串函数: 1.ACCII('');返回字符串中第一个字符的ACCII值
                2.charlegth('');返回字符的个数
                  length(''); 返回字符的个数
                3.concat('','');拼接多个字符串
                4.format(X,D) X是小数，格式化保留X的D位有效数字，四舍五入
                5.insert(str,pos,len,newstr) 替换，从pos开始，len个长度替换
                6.locate(substr,str) 返回substr在str中的位置
                7.lower(str)返回字符串的小写字母
                  upper(str)返回字符串的大写字母
                8.LTRIM(str)返回去左空格的字符串
                  RTRIM(str)返回去右空格的字符串
                  TRIM(str)返回去两边空格的字符串
                9.position(substr int str) 查询substr在str中的位置
                10.reverse(str) 反转字符串
                11.substr(str pos len) 截取 在str字符串中从pos截取len个长度的字符串
                   substring(str pos len) 截取 在str字符串中从pos截取len个长度的字符串
                12.    replace(str ,str1,str2) 替换 把str字符串中的str1替换为str2
    3.日期函数:  1.ADDDATE(date,INTERVAL 1 day);  将给定日期增加指定的日期数
                2.curdate();返回当前日期  年-月-日
                  current_date();同上
                3.now() ; 返回当前系统时间 年-月-日  时-分-秒
                4.datediff();返回两个日期的差值
                  TIMEDIFF(time1, time2);返回相差多少
                5.year(); month(); day(), hour(), minute(); second();
    4.日期格式化函数: 
                1.select STR_TO_Date('1999-09-09','%Y-%m-%d') from dual;-- 字符串转日期
                2.select DATE_FORMAT(CURDATE(),'%Y-%m-%d') from dual; -- 日期转字符串  年月日
                3.select DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%s') from dual;年月日时分秒
    5.条件语句函数: 
                1.IF(expr1,expr2,expr3)判断是否有expr1,有则返回expr2,如果为0 或null则返回expr3
                2.IFNULL(expr1,expr2) 判断expr1 是否为null ，是null则返回expr2，不是null则返回expr1的值
                3.case when then else end
                 简单语句                 
                 select productName,case color 
                   when 'white' then '白色'
                   when 'red' then '红色'
                   when 'black' then '黑色'
                   when 'yellow' then '黄色'
                                     else '不知道' end
                                     from product;
                 复杂语句: 行变列
                 select 
                   `name`,
                    sum((case when `subject`='英语' then score else 0 end)) 英语 ,
                    sum((case when `subject`='语文' then score else 0 end)) 语文,
                    sum((case when `subject`='数学' then score else 0 end)) 数学
                         from grade group by `name`;
====================================================================================================
联合查询:
     应用场景:    
        在两个表没有任何关联关系的时候，
        需要将两个表的数据 整合成 一个结果集，过程就是 联合查询
        
     前提: 表字段个数，顺序，类型 必须要一致，否则查询出来没意义
        union  去重
        union all  不去重
     案例：
        不添加过滤条件:    
            select * from chinese
             union all
            select * from american
        添加过滤条件:    
            select * from chinese where score > 80
             union all
            select * from american where score > 80 
====================================================================================================
事务:
    什么是事务?
            从现实角度: 事务 是 一件 或 多件 事件的集合,
                     这些事儿 要么全做 ，要么全不做
            从mysql数据库: 事务 是 一条 或多条 sql 语句的集合
                          这些sql 语句 要么都执行， 要么 都不执行
    mysql中的事务默认 是 自动提交的
    设置手动提交:
        show variables like 'autocommit';查看自动提交状态，一般为ON
        set autocommit = 0; -- 手动提交  修改后autocommit变为OFF
        commit; 永久提交， 永久提交后 ，不能回滚
        rollback; 回滚 回复到最初的状态
        savepoint; 保存点
        savepoint a;
        rollbacke to 保存点;
        如果回滚的是最初的保存点 那么 子保存点则 取消;
    保证事务的正常执行: 
        mysql事务的四个特性: 
            1.原子性: 一个事务是最小的执行单位，不可再分，即事务在执行时，不可过半终止，所以sql语句要么全部执行，要么全不执行
            2.一致性: 事务从开启到结束，数据库中的状态要保持一致：
                     1) 当前表中有两条数据，这两条数据在一个事务中，则这两条数据状态应保持一致
                     2) 当有主表 从表关系时，主表的主键更新了，从表的外键字段也必须更新
            3.隔离性: 体现的实在并发情况下的，在一个事务操作一条数据时，其他事务不可以访问这条数据
                     1) 读未提交数据  read uncommited
                     2) 读已提交数据  read commited Oracle数据库默认
                     3) 可重复读     repeatable read mysql数据库默认
                     4) 串行化       Serializtion
            4.持久性: 如果事务执行 commit 提交操作，那么对数据库永久有效，不可回滚
    查看MySQL的隔离级别: select @@tx_isolation;
    如果MySQL中没有隔离级别，那么在并发情况下，读取数据时会出现以下三种情况
            1.脏读:   发生在读未提交数据的情况下多
                 假设T1和T2两个事务，此时T1修改了数据库表中的某一条数据
                 但 没提交，这时T2读取了当前修改后的记录，这时T1 遇到问题
                 回滚了，这时T2读取的数据和 数据库表中的数据不一致
                 那就是 脏读
            2.不可重复读:
                假设T1 和T2 两个事务, T1不断的读取当前表中的 某条记录
                在读取 过程中 T2对当前记录进行了修改操作 ，并且提交了
                这时T1 ，在一个事务中读取的数据不一致了，
                这个现象就是 不可重复读
            3.幻读:
                T1 和 T2两个事务， 假设T1查询工资为 5000元的员工总数是10
                T2 执行 inert into 操作 ，总记录数11 ，
                T1 查询的总记录数和  数据库中记录数不一致，幻读
    读未提交数据: 
             会出现 脏读，不可重复读，幻读
    读已提交数据:
         会解决 脏读问题， 但是有可能出现 不可重复读， 和幻读
    可重复读:
         会解决 脏读，不可重复度，会出现 幻读
    串行化:
         会解决 脏读， 不可重复读， 幻读,只是事务 一个 接着一个的 执行,效率极低

    解决并发问题:
        1.事务隔离级别：
        2.悲观锁：必须修改事务隔离级别为 读未提交数据
                set session transcation isolation level read uncommited; -- 修改事务隔离级别为读未提交数据
                set @@tx_isolation ;
                select ………… for update ;  设置手动提交事务    
                MySQL5.5以前时myISAM     表锁  myISAM不支持事务
                MySQL5.5以后包含5.5INNODB 支持行锁，表锁      INNODB支持事务
        3.乐观锁：根据版本号和时间戳来判断是否出现并发问题
                即在你的表上添加一个字段