06_数据库-表数据的操作

-- 数据库的操作
    -- 连接数据库 启动数据库
    mysql -uroot -p
    mysql -uroot -pxiaoye1014

    -- 退出数据库
    exit/quit/ctrl + d

    -- sql语句最后需要有分号;结尾
    -- 不区分大小写
    -- 显示数据库版本
    select version();

     -- 显示时间
    select now();

     -- 查看所有数据库
    show databases;

     -- 创建数据库
     create database python04;
     create database python04 charset=utf8;

     -- 查看创建的数据库
    show create databse python04;

     -- 查看当前使用的数据库
    select database();

     -- 使用数据库
     use pyhton04;  -- 确定当前使用数据库

     -- 删除数据库
     drop database python04
     drop table    `python-04`


-- 数据表的操作
    -- 查看当前数据库中所有表
    show tables;

    -- 创建表
    -- auto_increment 表示自动增长
    -- not null 表示不能为空
    -- primary key 表示主键
    -- default 默认值
    -- create table 数据表名字 （字段 类型 约束, ... ）

    -- 创建students表（id, name, age, heigh, gender, cls_id）
    create table students(
        id int unsigned not null auto_increment primary key,
        name varchar(30),
        age tinyint unsigned default 0,
        high decimal(5, 2),
        gender enum("男", "女", "中性", "保密") default "保密",
        cls_id int unsigned 
    );  -- 最后一个字段不能加逗号 , 

    -- 查看表的创建语句
    show create table students;

    -- 查看表结构
    desc table01;

    -- 修改表-添加字段
    alter table students add birthday datetime;

    -- 修改表-修改字段：不重名版
    alter table students modify birthday date;

    -- 修改表-修改字段：重命名版
    alter table students change birthday birth date default "2020-01-01";
    
    -- 修改表-删除字段
    alter table students drop high;

    -- 删除表
    drop table students;
    
-- 数增删改查(curd) 
    -- 增加
        -- 全列插入
        insert into classes values(0, "菜鸟班");
        -- 备注：
            -- 插入students中，由于id是主键、自动增长，所以可以
                insert into students values(0, "张三", 20, "女", 1, "2020-01-01");
                -- id 还可以用 null default
            -- 插入enum类型，不能超过范围，同时可以用1,2 代替 男 女 插入
        
        -- 部分插入
        insert into students (name, gender) values (“李四”, 2);

        -- 多行插入
        insert into students (name, gender) values ("小乔", 18), ("貂蝉", 19);
        
    -- 修改
    update students set gender="男";  -- gender =1 -- 全部都改
    update students set gender="男" where id=3  -- 改特定条件
    update students set gender="男", age=22 where id=3;  -- 改多个


    -- 删除
        -- 物理删除
        delete from students;  -- 删除整个表
        delete from studnts where id=3;  -- 删除特定条件，删除某一行。

        -- 逻辑删除
        -- 一般不推荐物理删除，数据难得
        -- 如何逻辑删除？用一个字段来表示，这条信息是否已经不能再使用了
        -- 给students表添加一个is_delete字段 bit 类型
        alter students add is_delete bit default 0;  -- 看不见，但是该怎么办酒怎么办。
        -- update students set is_delete=1 where id=6


-- 查询详细
    -- 查询所有字段
    select * from students;
    
    -- 查询指定字段
    select name, age from students;

    -- 使用 as 给字段起别名
    select name as 姓名, age as 年龄 from students

    -- 通过 as 给表写名字  多个表的字段
    select students.name, students.age from students  -- 用 .
    select s.name, s.age from students as s;  
    -- 不行 select students.name, students.age from students as s; 


    -- 消除重复行
    select distinct name from students;

    -- 条件查询
    -- 比较运算符
        -- >  <  >=  <=     =      !=  
        select .. from students where ...

    -- 逻辑运算符
        -- and  or not 

    -- 模糊查询
        -- like
        -- % 替代0个、1个或者多个
        -- _ 替换一个
        select  name from students where name like "%小%";
        select  name from students where name like "__";  -- 名字两个字

    -- rlike 正则
        select name from students where name rlike "^周.*论$";  -- 名字是以周开头，论结尾中间是.*，正则还有贪婪和非贪婪

    -- 范围查询
        -- in (1, 3, 8) 表示在一个非连续的范围内查询
        select name where students where age in (12, 18, 30);    

        -- not in 不在某个范围之内
        select name where students where age not in (12, 18, 30); 

        -- between .. and .. 在连续范围之内 首尾端包含
        select name where students where age between 18 and 34;

        -- 不在连续 not between .. and ..
        select name where students where age not between 18 and 34;

    -- 空判断
        --列判断空为 is null
        select * from students where heigh is null;

        -- 列判断非空为 is not null
        select * from students where heigh is not null;

    -- 排序
        -- order by 字段  asc 升序 desc 降序
        select * from students where (age between 18 and 34) and gender=1 order by age;  -- 默认是升序

        -- order by 多个字段 第一个相同的情况下，按照第二个 ......
        select * from students where (age between 18 and 34) and gender=1 order by age, id desc;  -- 默认是升序
            -- 如果第一个字段都不相同，则第二个排序字段就没有用。

    -- 聚合函数
        -- 总数：count()
        select count(*) as 男性人数 from students where gender=1;

        -- 最大值：max()
        select max(age) as 男性最大年龄 from students where gender=1;
            -- 最小值：min()  平均值：avg()  求和：sum() 
        
        -- 计算平均值
        select sum(age)/count(*) from students;  -- select后面可以放表达式。

        -- 四舍五入 round()
        select round(sum(age)/count(*), 2) from students; 

    --分组
        -- group by 
        select gender from students group by gender;
        select gender, count(*) from students where gender=1 group by gender;
            -- 注意先是gender，后面是count(*) * 是针对每个组的。

        -- 查看每组中的name有哪些？
        select gender, group_concat(name) from students group by gender;
        select gender, group_concat(name, "_", age, "_", id) from students group by gender;

        -- having 对分组后的每组进行过滤
        select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;
            -- having后的avg(age)是针对每个组的，count(*)中*也是针对每个组的。
            -- where Vs having：where是对原始表进行过滤，having是对查询的结果进行过滤。

    -- 分页
        -- limit
        -- 查询前5个数据
        select * from students where gender=1 limit 5;
        select * from students where gender=1 limit 1, 5;  
        -- 表示从1开始的5个，索引是0, 1, 2, 3 ...   其中limit 1， 5：就是索引1到5
        -- limit 5 如果没有5个，则实际多少个就显示多少个。
        -- limit位置在最后。
        -- select * from students where gender=1 limit 2*(6 - 1), 5;  -- 不行，这个地方不支持表达式。

    -- 连接查询
        -- 内连接：取交集
        -- 取所有
        select * from students inner join classes;  
            -- students中每一行都有classes所有记录 总记录数 n * m
        -- 取交集
        select * from students inner join classes on students.cls_id=classes.id;
            -- 上面是内部连接，输出交集结果。
        -- 取部分字段
        select sutdents.*, classes.name from students inner join classes on students.cls_id=classes.id;
        -- 取别名
        select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;

        -- 左连接：哪个表在左边，就按照该表为标准，右表取不出来的就为null。
        select s.*, c.name from students as s left join classes as c on s.cls_id=c.id;
            -- 右连接：哪个表在右边，就按照该表为标准，左表取不出来的就为null。
        -- 从查询的结果当做一个集，然后对结果进行过滤
        select s.*, c.name from students as s left join classes as c on s.cls_id=c.id having  c.id is null;

    -- 自关联，自连接
    select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle="山东省";

    -- 子查询 select 嵌套 select
    select * from students where height = (select max(height) from students);


-- 数据库设计实例
    -- 创建商品表
    create table goods(
        id int unsigned primary key auto_increment not null,
        name varchar(40) default '',
        price decimal(5,2),
        cate_id int unsigned,
        brand_id int unsigned,
        is_show bit default 1,
        is_saleoff bit default 0,
        foreign key(cate_id) references goods_cates(id),
        foreign key(brand_id) references goods_brands(id)
    );

    -- 通过goods_brands数据表来更新goods数据表
    update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand=b.id;

    -- 修改表结构 给表字段改名字和类型约束
    alter table goods
    change cate_name cate_id int unsigned not null,
    change brand_name brand_id int unsigned not null;

    -- 对于已经存在的数据表如何更新外键的约束
    alter table goods add foreign key (brand_id) references goods_brands(id);
        -- 注意goods中的cate_id 和 goods_brands中的id类型需要一致。

    -- 如何取消外键的约束
    alter table goods drop foreign key 外键名称。

    -- 在实际开发中，很少使用外键的约束，会极大的降低表的更新效率。


-- 补充
    -- 插入sql
        -- 由很多insert into ...  valus ...  组成的.sql文件 如何整体插入？
        -- 到所属数据库，然后
        source areas.sql;

    -- 一般子查询比自关联查询会慢点。

    -- 一边查询，一边创建表，用select查询结果创建表。
    create table goods_brands (
	    id int unsigned primary key auto_increment,
	    name varchar(40) not null
    ) select brand_name as name from goods group by brand_name;  -- 这里brand_name必须改名为name，以和前面创建表的对应。