mysql_基础

默认：数据库python3
      表：students
      
    -----------------------------------创建库表

    1.登录Mysql数据库
        mysql -uroot -p
        
    2.创建数据库
        create database 数据库名;
        
    3.创建表
        create table students(id int auto_increment primary key not null,
                              name varchar(10) not null,gender bit default 1,birthday datetime);
        
    4.使用数据库
        use 数据库;
        
    5.查看使用的数据库
        select database();
        
    6.查看所有的数据库/表
        show databases/tables;
        
    7.查看表结构
        desc 表名;
        
    8.修改表
        alter table students add isDelete bit default 0;
        
    9.删除表
        drop table 表明;(物理删除)
        
    10.查看表的创建语句
        show create table students;
        
    11.更改表名称
        rename table 原表名 to 新表明;
        
    -----------------------------------操作数据库
    1.查询
        select * from students;
        select * from students where 1;   # 永真查询
        
    2.增加
        # 方法一：这种插入方法需要按顺序插入值，一一对应
        insert into students values(0,'alex',1,'1990-12-12',0);
        
        # 方法二：指定某些字段的值，注意有些字段为not null，需要给其添加值，否则报错
        insert into students(name) values('eric');
        
        # 同时插入多条数据
        insert into students(name,birthday) values('wusir','1990-1-1'),('james','1991-1-1');
        
    3.修改
        update students set birthday='1999-12-12' where id=2;
        update students set gender=0,birthday='2000-12-12' where id=4;
        
    4.删除(物理删除)
        delete from students where id=4;
        # 逻辑删除(本质是修改)
        update students set isDelete=1 where id=3;
        # 筛选：将isDelete=0未删除的筛选出来
        select * from students where isDelete=0;
        
        
    -----------------------------------数据备份与恢复
    # 备份
    1.进入超级管理员
    su - root
    
    2.进入mysql库目录
    # 一般linux下：cd /var/lib/mysql
    # 一般windows下：cd C:\ProgramData\MySQL\MySQL Server 5.5\data    # 目录下显示数据库的名字
    
    3.运行mysqldump命令
    mysqldump -uroot -proot 数据库名 > ~/Desktop/备份文件.sql;
    mysqldump -uroot -p python3 > D:\bak.sql
    
    # 恢复：需要自己创建数据库
    到备份的sql目录下：D:\    
    D:\>mysql -uroot -p python3new < bak.sql    即可实现数据恢复
    
    
    
    -----------------------------------数据库表字段操作
    
    1.增加一个字段
    - alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一个字段，默认为空
    - alter table user add COLUMN new2 VARCHAR(20) NOT NULL;  //增加一个字段，默认不能为空  
 
    2.删除一个字段
    - alter table user DROP COLUMN new2; 　 //删除一个字段
    
    3.修改一个字段
    - alter table user MODIFY new1 VARCHAR(10); 　//修改一个字段的类型
    - alter table user CHANGE new1 new4 int;　 //修改一个字段的名称，此时一定要重新指定该字段的类型
        
        

 

# 查询_where

 -----------------------------查询

1.查询指定表中所需要的字段
    select id,name from students;
    
2.消除重复行
    # 消除birthday字段重复数据
    select distinct birthday from students;
    # 注：select distinct id,gender from students;
    #      比较的是distinct后面所有字段是否重复，有id不可能重复
    
    
3.条件查询 -->  where后面
    # 比较运算符
    select * from students where id>3;
    select * from students where name != 'eric';
    select * from students where isdelete=0;
    
    # 逻辑运算符
    and：同时满足           select * from students where id>3 and gender=1;
    or：满足左侧或右侧        
    not：加个否定
    
    # 模糊查询
    1.%匹配任意多个字符;    select * from students where name like 'L%';
    2._匹配一个任意字符;    select * from students where name like 'L_li%';
    模糊查询多个条件：        select * from students where name like 'L%' or name like 'C%';
    
    # 范围查询
    1.in表示在一个非连续的范围内
        - select * from students where id in(1,3,5,8,10);    # 在范围取出，不在范围不取
    2.between表示在一个连续的范围内
        - select * from students where id between 1 and 10;  # 在范围取出，不在范围不取
        # 再多加条件：
            - select * from students where id between 1 and 10 and gender=1;  # 两个条件
            
    # 空判断
    1.null与“”是不同的：null不指向内存，所以不占用内存;而""表示的是空字符串，在内存中占用空间;
    2.is null：判空条件             select * from students where birthday is null;
    3.is not null：非空判断条件     select * from students where birthday is not null;
    
    # 优先级
    1.小括号 > not > 比较运算符 > 逻辑运算符
    2.and比or先运算，如果同时出现并希望先算or，需要结合()使用

 

# 查询_聚合

    
    
    --------------------------------------聚合
    
    1.count(*)  表示计算总行数，括号中写星与列名字，结果是相同的
        - select count(*) from students where isdelete=0;
        
    2.max(列)   表示求此列的最大值
        - select max(id) from students where gender=1;
        
    3.min(列)    表示求此列的最小值
        - select * from students where id=(select min(id) from students where isdelete=0); 子查询
    
    4.sum(列)    表示求此列的和(数字类型)
        - select sum(id) from students where gender=1;
        
    5.avg(列)    表示求此列的平均值(数字类型)
        select avg(id) from students where gender=0 and isdelete=0;

 

# 查询_分组

    
    
    ----------------------------------------分组
    分组：按照字段分组，表示此字段相同的数据会被放到一个组中;
          分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中;
          可以对分组后的数据进行统计，做聚合运算;
          # 语法：select 列1，列2，聚合... from 表名 group by 列1，列2，列3...
          
    1.分组后统计
        - select gender,count(*) from students group by gender;
        
    2.筛选having
        where和having的区别--面向的数据集不同：
            - where:是对from后面指定的表进行数据筛选，属于对原始数据的筛选，且where中不能使用聚合函数;
            - having:是对group by的结果进行筛选，在having中可以使用聚合函数;
        执行顺序:
            - 原始集 --> where --> 结果集 --> 分组 --> 分组后的结果集(group by) --> having条件
            - where... order by... having...   - where... order by...
        
        - select gender,count(*) from students group by gender having count(*) > 2;
        - select gender,count(*) as res from students group by gender having res > 2;(拟定一个res名)
        
    4.分组、排序和受限的顺序？以及什么时候用where和having？
    - 三者的使用顺序是：  分组 > 排序 > 受限
    - 在group by分组语句和order by排序语句中，后面不能用where，但是在group by后面可以使用having代替where

 

# 查询_排序

    ------------------------------------------排序
    1.排序：为了方便查看数据，可以对数据进行排序
    
    2.顺序：
        - 降序:desc
        - 顺序：asc
        
    3.操作
        # 根据两个并列的条件进行降序排列
        - select * from students where isdelete=0 and gender=1 order by id desc;

 

# 查询_分页

    ----------------------------------------分页
    1.分页：当数据量过大时，在一页中查看数据是一件非常麻烦的事情
    2.语法：select * from 表名 limit start，count
          (start:从哪个下标(页)开始拿，count:拿多少个)
          
    3.操作：
        - select * from students limit 2,1;
        - select * from students limit 1,3;
        
    4.示例：
        - 已知每页显示m条数据，当前页是第n页，求总页数？请写出limit条件
        - select * from students where isdelete=0 limit (n-1)*m,m;   ---> n从1开始
        - select * from students where isdelete=0 limit n*m,m;        ---> n从0开始

 

# 外键约束

    ---------------------------------------------外键约束
    
    外键作用：
        1.为了一张表记录的数据不要过于冗余;
        2.保持数据的一致性、完整性;
        注：
            - 表的外键可以有重复的，可以是NULL;
            - 是用来和其他表建立联系使用的;
            - 一个表可以有多个外键;
            
        创建：
            - create table scores(
            id int auto_increment primary key not null,
            score decimal(5,2),
            stuid int,
            subid int,
            foreign key(stuid) references students(id),
            foreign key(subid) references subjects(id)
            );
            
        外键的级联操作：
            - 在删除students表的数据时，如果这个id值在scores中已经存在，则会抛出异常;
            - 推荐使用逻辑删除，可以解决这个问题;
            - 可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作;
            - 语法：
                ...
            
            - 级联操作的类型包括：
                - restrict(限制)：默认值，抛异常;
                - cascade(级联)：如果主表的记录删除，则从表中相关的记录都将被删除;
                - set null：将外键设置为空;
                - no action：什么都不做;
                - 推荐使用逻辑删除;

 

# 连接查询

    --------------------------------------------连接查询
    
    连接查询：当需要对有关系的多张表进行查询时，需要使用连接join
    语法：
        - select students.name,subjects.title,scores.score
          from scores
          inner join students on scores.stuid=students.id
          inner join subjects on scores.subid=subjects.id;
          
        - select students.name,subjects.title,scores.score
          from students
          inner join scores on scores.stuid=students.id
          inner join subjects on scores.subid=subjects.id;
          
    注：1.mysql左连接(left join)、右连接(right join)和内连接(inner join)的区别：
        - 内连接：显示两个表中有联系的所有数据;
            - select * from scores inner join students on students.id=scores.stuid;
        - 左连接：以左表为参照，显示所有数据，右表中没有则以null显示;
            - select * from students left join scores on students.id=scores.stuid;
        - 右连接：以右表为参照，显示所有数据，左表中没有则以null显示;
            - select * from scores right join students on students.id=scores.stuid;
        
        2.inner join时，from 表(这个表先写谁后写谁没有关系，但要注意的是，on条件后面的表要和join后面的表有关系)
        
        3.select name,avg(score) from students inner join scores on scores.stuid=students.id;
            - +------+------------+
              | name | avg(score) |
              +------+------------+
              | alex |   87.41667 |
              +------+------------+
          name没有显示完全，分数显示的是对应条件的平均分;
          

 

# 关联查询

    --------------------------------------------关联查询
    问：查询男生的姓名、总分
    思路：
        - 男生：students.gender=1
        - 姓名：students.name        # 性别和姓名都在students表中
        
        - 总分：sum(scores.score)     # 分数在scores表中
        建立关联：scores表中有外键关联着students表
        使用sum --> 就先分组(姓名：每个人的总分，对每个人进行分组)
        
        语句：
            - select name,sum(score)
              from students
              inner join scores on scores.stuid=students.id
              where gender=1
              group by students.name;
              

    问1：查询科目的名称、平均分
    思路：
        - 科目的名称：subjects.title
        - 平均分：avg(score)
        建立连接：scores表中有外键关联着subjects表
        avg(score) --> group by subjects.title
        
        语句：
            - select subjects.title,avg(score)
              from scores
              inner join subjects on scores.subid=subjects.id
              group by subjects.title;
        
        
    问2：查询未删除的名称、最高分、平均分
    思路：
        - 未删除：isdelete=0          # students表
            - where subjects.isdelete=0
        - 科目的名称：subjects.title  # subjects表
        - 最高分：max(score)          # scores表 --> 关联students表
        - 平均分：avg(score)          # scores表 --> 关联subjects表
        建立连接：max、avg --> group by subjects.title
        
        语句：
            - select subjects.title,max(score),avg(score)
              from scores
              inner join students on scores.stuid=students.id
              inner join subjects on scores.subid=subjects.id
              where subjects.isdelete=0
              group by subjects.title;
              
              
              
    ----------------------------------------------自关联
    
    事例：省、市、区县同时创建三张表会造成表资源的浪费时
            - 因为一张表如果可以存储很大的资源时，就没有必要创建其他的表;想办法合在一张表中;
          
          - 因为省没有所属的省份，所以可以填写为null;
          - 城市所属的省份pid，填写省所对应的编号id;
          -    区县所属的城市pid，填写城市所对应的编号id;
    
    自关联：以上举例为自关联，即表中的某一列，关联了这个表中的另外一列，但是他们的业务逻辑含义是不一样的;
            城市信息的pid引用的是省信息的id，区县的pid引用的是城市的id等。
            - 在这个表中，结构不变，可以添加区县、乡镇街道、村社区信息等。
            
    创建areas表的语句如下：
        # e.g:  1  江苏  null
        #        2  南京    1
        
        # 准备数据
        - create table areas(
        aid int primary key,
        atitle varchar(20),
        pid int);
        
        - insert into areas 
        values ('130000', '河北省', NULL),
        ('130100', '石家庄市', '130000'),
        ('130400', '邯郸市', '130000'),
        ('130600', '保定市', '130000'),
        ('130700', '张家口市', '130000'),
        ('130800', '承德市', '130000'),
        ('410000', '河南省', NULL),
        ('410100', '郑州市', '410000'),
        ('410300', '洛阳市', '410000'),
        ('410500', '安阳市', '410000'),
        ('410700', '新乡市', '410000'),
        ('410800', '焦作市', '410000');
        
        - 查询河南省所有城市
          - select * from areas as sheng
            inner join areas as shi on sheng.pid=shi.pid
            where sheng.atitle='河北省';
        
        - 添加区县数据
          - insert into areas values
            ('410101', '中原区', '410100'),
            ('410102', '二七区', '410100'),
            ('410103', '金水区', '410100');
            
        - 查询郑州市的所有区县
          - select * from areas as shi
           inner join areas as quxian on quxian.pid=shi.aid
           where shi.atitle='郑州市';
           
        - 查询河南省的所有区县
          - select * from areas sheng
            left join areas as shi on shi.pid=sheng.aid
            left join areas as quxian on quxian.pid=shi.aid
            where sheng。atitle='河南省';
            
    # 总结：自关联相当于公用一张表，将表定义成不同命名，作为不同的"表"来使用。

 

# 视图

    ---------------------------------------------视图
    
    - 对于复杂的查询，在多次使用后，维护时一件非常麻烦的事情;
    - 解决：定义视图;
    - 视图本质就是对查询的一个封装;
    
    - 定义试图
        - create view v_1 as 
        select stu.*,sco.score,sub.title from scores as sco
        inner join students as stu on sco.stuid=stu.id
        inner join subjects as sub on sco.subid=sub.id;
        
        - 如果修改视图，可使用alter命令;
        - alter view v_1 as 
        select stu.*,sco.score,sub.title from scores as sco
        inner join students as stu on sco.stuid=stu.id
        inner join subjects as sub on sco.subid=sub.id
        where stu.isdelete=0 and sub.isdelete=0;
        
    
    - 视图的用途就是查询
        - select * from v_1;
        
    - 逻辑上是两张表，物理上是一张表

 

# 事务

    -----------------------------------------事务
    
    - 当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回;
    - 使用事务可以完成退回的功能，保证业务逻辑的正确性;
    - 事务的四大特性(简称ACID)
        - 原子性(Atomicity)：事务中的全部操作在数据库中时不可分割的，要么全部完成，要么均不执行;
        - 一致性(Consistency)：几个并行执行的事务，其执行结果必须域按某一顺序串行执行的结果一致;
        - 隔离性(Isolation)：事物的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的;
        - 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障;
    - 要求：
        - 表的引擎必须是innodb或bdb类型，才可以对此表使用事务;(默认使用的是innodb)
    - 查看表的创建语句
        - show create table students;
        
    - 使用事务的条件时:
        - 当数据被更改时，包括insert、update、delete等
    - 事务语句
        - 开启事务: begin
        - 提交事务: commit
        - 回滚事务: rollback
        
    - 事务的意义：保证代码业务逻辑的完整性;
    

 

# 索引

    -------------------------------------索引
    索引优点：
        - 索引能提高数据访问性能;
        - 主键和唯一索引，都是索引，可以提高查询速度;
        - 使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间;
        - 创建唯一索引，能够保证数据库表中每一行数据的唯一性;
        - 在实现数据的参考完整性方面，可以加速表和表之间的连接;
    索引缺点：
        - 索引的创建会占据磁盘空间，增加物理开销;
        - 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加;
        - 对数据表中的数据进行增加、修改、删除时，索引也要动态的维护，降低了维护的速度;
    
    - 选择列的数据类型
        - 更新频繁的列不应设置索引;
        - 重复数据多的字段不应设为索引;
        - 首先应该考虑where和order by设计的列上建立索引;
        
        - 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快;
        - 简单的数据类型更好：整型书比起字符，处理开销更小，因为字符串的比较更复杂;
        - 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL，在mysql中，含有空值的列很难进行查询优化，因为
          它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
          
    - 操作索引
        - 索引分为单列索引和组合索引
            - 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引;
            - 组合索引：即一个索引包含多个列;
            
        - 查看索引：
            - show index from 表名;
        - 创建索引：
            - create index 索引名 on 表名(字段名(length));
                - 如果字段名是字符串，则指定长度;如果是数字，不需要指定长度;
        - 删除索引
            - drop index 字段名 on 表名;
            
            
    ----------------------------------------索引优化，减少时间
    步骤：
        - 开启运行时间检测：
            - set profiling=1;
        - 执行查询语句：
            - select * from areas where atitle='河北省';
        - 查看执行的时间：
            - show profiles;
            
        - 为表areas的atitle列创建索引：
            - create index titleIndex on areas(atitle(20));
        - 执行查询语句：    
            - select * from areas where atitle='河北省';
        - 再次查看执行的时间：
            - show profiles;