MySQL练习

-- 一:数据库
    -- 1.创建数据库 create database 数据库名 charset=utf8;   
        create database python charset=utf8

    -- 2.删除数据库 drop database 数据库名;
        drop database python

    -- 3.使用数据库 use 数据库名;
        use python

    -- 4.查看所有数据库
        show databases
  




-- 二:数据表的操作
    -- 1.增
        -- 创建学生表
        create table students(
            id int unsigned primary key auto_increment not null,
            name varchar(20) default '',
            age tinyint unsigned default 0,
            height decimal(5,2),
            gender enum('男','女','人妖','保密'),
            cls_id int unsigned default 0
        )

        -- 添加字段 add : alter table 表名 add 列名 类型;
             alter table  students add birthday datetime

    -- 2.删除
        -- 删除表 : drop table 表名;
           drop table students
        --删除字段 : alter table 表名 drop 列名;
           alter table student drop birthday

    -- 3.修改表
        
        -- 修改表-修改字段-重命名版 change : alter table 表名 change 原名 新名 类型及约束;
            alter  table students change birthday birth datetime not null 
        -- 修改表-修改字段-不重命名版 modify: alter table 表名 modify 列名 类型及约束;
            alter   table  students  modify  birth  date not null

    --4.查看
        -- 查看当前数据库中所有表
        show tables
        --  查看表结构
        desc 表名;
        -- 查看表的创建语句
        show create table classes
        






-- 三:数据增删改查(curd)
    -- 增加
        -- 全列插入：值的顺序与表中字段的顺序对应  ;
            -- insert into 表名 values(...) 
            insert into  students values (0,’郭靖‘,1,'蒙古','2016-1-2')

        -- 全列多行插入：值的顺序与给出的列顺序对应 ;
            -- insert into 表名 values(...),(...)...; 
                insert into classes valuse  (0,'python1'),(0,'python2')
            -- insert into 表名(列1,...) values(值1,...),(值1,...)...;
                insert into  students(name) values ('杨康'),('杨过'),('小龙女')
                

        -- 部分列插入：值的顺序与给出的列顺序对应 insert into 表名(列1,...) values(值1,...) values
            insert into  students(name,hometown,birthday) values ('黄蓉','桃花岛','2016-3-2')

        

    -- 删除
        --物理删除 delete from 表名 where 条件 
        delete  from students where id=5

        -- 逻辑删除，本质就是修改操作 isdelete=1 id=1
        update  student set isdelete=1 where id=1


    -- 修改
        -- update 表名 set 列1=值1,列2=值2... where 条件 id=5
            update student set gender=0,hometown='北京' where id =5

    -- 查询
        -- 查询所有列 select * from 表名;
        select * from classes;
        -- 查询指定列 select 列1,列2,... from 表名;
        select id,name from classes;

-- 四:数据备份和恢复
    -- 备份
        mysqldump –uroot –p 数据库名 > python.sql;

    -- 恢复
        mysql -uroot –p 新数据库名 < python.sql
    -- 自关联
        -- 从sql文件中导入数据
        source areas.sql:


--五:数据库设计
     第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
     第二范式（2NF）：首先是 1NF，表必须有一个主键(主键可以由多个列组成)；且包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
     第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：
    注意: 2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。


--六:Mysql的查询

    --准备
    -- 使用 as 给字段起别名 /可以通过 as 给表起别名
    -- 消除重复行 select distinct 列1,... from 表名



    --条件查询 where 字段 
        -- 比较运算符 大于/小于/等于/不等于
        -- 逻辑运算符 and/or/not
        -- 模糊查询 like:%表示任意多个任意字符/_表示一个任意字符
            -- 查询姓黄的学生
            select * from students  where name like '黄%'
        -- 范围查询 in/between ... and ...
            -- 查询编号是1或3或8的学生
                select * from students where id and 3 or 8         
            -- 查询编号为3至8的学生
                select * from students where id between 3 and 8 
            
        -- 空判断 is not null   注意：null与''是不同的 空值是不占用空间的 mysql中的NULL其实是占用空间的，
            -- 查询没有填写身高的学生
                select * from students where height is not null
           

    注意: 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符

    -- 排序where... order by 列名 asc/desc 

    -- -- 聚合函数 : 
            --              总数count /
            --             最大值max(列)/
            --             最小值min(列)/
            --              求和 sum(列)/
            --               平均值avg(列) 

    -- 分组
            -- group by
            -- group by + group_concat()   注意:group_concat()用来显示分组后每一组的某字段的值的集合
                -- 输出各个性别的人数的名字
                select gender,group_concat(name) from students group by gender
            -- group by + 集合函数
                -- 分别统计性别为男/女的人年龄平均值
                    select  gender,avg(age) from students group by gender
            -- group by + having           注意:having作用和where一样，但having只能用于group by
                    select gender,count(*) from students  group by gender  having count(*)>2

            -- group by + with rollup      注意:with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
                    select  gender,count(*) from studnents group by  gender with rollup

    -- 分页
        -- select * from 表名 limit start,count
            
    -- 连接查询
            select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
            -- 使用内连接查询班级表与学生表
                select  * from students as s inner join classes as c on s.cls_id=c.id 

            -- 使用左连接查询班级表与学生表
                select *  from students as s left join classes as c on s.cls_id=c.id 

            -- 使用右连接查询班级表与学生表
                select *  from students as s right join classes as c on s.cls_id=c.id 



    -- 子查询
    -- 标量子查询 
        -- 查询班级学生平均年龄 /查询大于平均年龄的学生
            select * from student  where  age > (select avg(age) from student)
        
    -- 列表子查询
        -- 找出学生表中所有的班级 id/找出班级表中对应的名字
            select name from student where id in (select cls_id from student )
    -- 行级子查询
        -- 查找班级年龄最大,身高最高的学生
            select * from student where (height,age)=(select max(height),max(age) from students);


    -- 总结:
        select distinct * from 表名  where ... group by ... having ... order by... limit start,count

-- 七:视图
    -- 定义:一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查
    -- 创建视图
        create view 视图名称 as select语句;
    -- 查看视图
        show tables;
    --  删除视图
        drop view v_stu_sco;

-- 八:事务
    -- 1定义:它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
    
    -- 2.事务四大特性(简称ACID)
        -- 原子性(Atomicity)
        -- 一致性(Consistency) 一个事务所做的修改在最终提交以前，对其他事务是不可见的
        -- 隔离性(Isolation)
        -- 持久性(Durability)
    
    -- 3.操作
        1.begin;
        2.1 具体操作
        2.2 rollback
        3.commit;
-- 九:索引
    -- 定义: 索引是一种特殊的文件 它们包含着对数据表里所有记录的引用指针。
    -- 1.创建索引
        create index 索引名称 on 表名(字段名称(长度))

    -- 2.删除索引：
        drop index 索引名称 on 表名;

    -- 3.查看索引
        show index from 表名;

    注意:建立太多的索引将会影响更新和插入的速度 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了