Python全栈 MySQL 数据库 （表字段增、删、改、查、函数）

查询SQL变量 show variables

1.表字段的操作
　　1.语法：alter table 表名 执行动作；
　　2.添加字段（add）
　　　　alter table  表名 add 字段名 数据类型；(尾插)
　　　　alter table 表名 add 字段名 数据类型 first；（头插）
　　　　alter table 表名 add 字段名 数据类型 after 字段名；（指定插入）
　　3.删除字段（drop）
　　　　alter table 表名 drop 字段名；
　　4.修改数据类型（modify）
　　　　alter table 表名 modify 字段名 新数据类型；
　　5.重命名（rename）
　　　　alter table 表名 rename 表名；
2.字符类型
　　1.字符类型宽度与数值类型宽度的区别
　　　　1.数值类型宽度为显示宽度，只用于select查询显示
　　　　占用储存无关，可用zerofill查看效果
　　　　2.枚举类型
　　　　　　1.单选（enum）：字段名 enum（值1，值2...）；
　　　　　　2.多选（set）：字段名 set（值1，值2...）；
　　　　      （多项放在一个字符串内用，号隔开）
　　3.日期时间类型
　　　　1.date：“YYYY-MM-DD”
　　　　2.time：“HH：MM：SS”
　　　　3.datetime：“YYYY-MM-DD HH：MM：SS”
　　　　4.timestamp：“YYYY-MM-DD HH：MM：SS”
　　　　5.datetime：不给值默认返回Null
　　　　6.timestamp：不给值默认返回系统时间

3. 日期时间函数
　　1.now（） 返回服务器当前的时间
　　2.curdate（） 返回当前时期
　　3.curtime（） 返回当前日期
　　4.year（date） 返回指定时间的年份
　　5.date（date） 返回指定时间的日期
　　6.time（date） 返回指定时间的时间
4.日期时间运算
　　1.语法格式
　　　　select * from 表名
　　　　where 字段名 运算符（时间 -interval 时间间隔单位）；
　　　　时间间隔单位：
　　　　1 day | 2hour | 1 minute | 2year | month

5.表记录管理
　　1.删除表记录
　　　　1.delete from 表名 where 条件；
　　　　注意：
　　　　如果不加where条件，所有记录全部清空
　　2.更改表记录
　　　　1.update 表名 set 字段1=值1，字段名2=值2，... where 条件
　　　　注意：
　　　　如果不加where条件，所有记录全部更改
　　3.运算符操作
　　　　1.数值比较/字符比较
　　　　　　1.数值比较： = != > >= < <=
　　　　　　2.字符比较： = !=
　　　　2.逻辑比较
　　　　　　1.and
　　　　　　2.or
　　　　3.范围内比较
　　　　　　1.where 字段名 between 值1 and 值2
　　　　　　2.where 字段名 in（值1，值2，....）
　　　　　　3.where 字段名 not in （值1，值2，...）

　　　　4.匹配空、非空
　　　　　　1.空：where name is null
　　　　　　2.非空：where name is not null
　　               3.注意
　　　　　　　　1.NILL：空值，只能用is或is not取匹配
　　　　　　　　2.“ ” ： 空字符串，用 = 或 != 去匹配
　　　　　　4.模糊比较
　　　　　　　　1.where 字段名 like 表达式
　　　　　　　　2.表达式
　　　　　　　　　　1._ : 匹配单个字符
　　　　　　　　　　2.% ：匹配0到多个字符
　　　　　　　　　　NULL不会被统计
6.SQL查询：

　　1语法顺序：

　　　　3.select ... 聚合函数 from 表名
　　　　1.where
　　　　2.group by...
　　　　4.having ...
　　　　5.order by ...
　　　　6.limit ...；

　　2.order by
　　　　1.给出查询结果进行排序
　　　　2...order by 字段名 升序/降序
　　　　升序：ASC（默认排序方式）
　　　　降序：DESC
　　3.limit（永远放在SQL语句的最后）
　　　　1.作用：显示查询记录的个数
　　　　2.用法
　　　　　　limit n     显示n条记录
　　　　　　limit m，n
　　　　　　m表示 从m+1条记录开始显示 显示n条记录
　　　　　　limit 2,3 显示第3,4，5条记录
　　　　3.分页
　　　　　　每页显示5条记录，显示第4页内容
　　　　　　第1页：limit 0,5 #1,2,3,4,5
　　　　　　第2页：limit 5,5
　　　　　　第3页：limit 10,5
　　　　　　第4页：limit 15,5
　　　　　　每页显示n条记录，显示第m页：
　　　　　　limit（m-1）*n，n
　　4.聚合函数
　　　　avg（字段名）：求该字段的平均值
　　　　sum（字段名）：求和
　　　　max（字段名）：最大值
　　　　min（字段名）：最小值
　　　　count（字段名）：统计该字段的个数

练习库：

create database MOSHOU;
use MOSHOU;
create table hero(
id int,
name char(15),
sex enum("男","女"),
country char(10)
)default charset=utf8;
insert into hero values
(1,"曹操","男","魏国"),
(2,"小乔","女","吴国"),
(3,"诸葛亮","男","蜀国"),
(4,"貂蝉","女","东汉"),
(5,"赵子龙","男","蜀国"),
(6,"魏延","男","蜀国");

use MOSHOU;
create table sanguo(
id int,
name char(20),
gongji int,
fangyu tinyint unsigned,
sex enum("男","女"),
country varchar(20)
)default charset=utf8;
insert into sanguo values
(1,'诸葛亮',120,20,'男','蜀国'),
(2,'司马懿',119,25,'男','魏国'),
(3,'关羽',188,60,'男','蜀国'),
(4,'赵云',200,66,'男','魏国'),
(5,'孙权',110,20,'男','吴国'),
(6,'貂蝉',666,10,'女','魏国'),
(7,null,1000,99,'男','蜀国'),
(8,'',1005,88,'女','蜀国');

练习

    1、创建库 studb2

    2、在库中创建表 t1 ,字段有3个：name、age、phnumber

    3、查看表结构

    4、在表中第一列添加一个 id 字段

    5、把 phnumber 的数据类型改为 bigint

    6、在表中最后一列添加一个字段 address

    7、删除表中的 age 字段

    8、查看表结构

答案：

      use studb2;
      create table t1(
      name char(20),
      age tinyint unsigned,
      phnumber char(11)
      );
      desc t1;
      alter table t1 add id int first;
      alter table t1 modify phnumber bigint;
      alter table t1 add address varchar(50);
      alter table t1 drop age;
      desc t1;

练习

    1、在表中插入3条记录

    2、查找2018年7月2日有哪些用户充值了

    3、查找2018年7月份充值的信息

    4、查找7月30日10:00-12:00充值的信息

答案：

      insert into t7 values
      (3,"小昭",19000520,3000,20180630000000),
      (4,"赵敏",19000521,4000,20180702000000),
      (5,"周芷若",19010522,3500,20180702100000);
      select * from t7 where date(shijian)="2018-07-02";
      select * from t7 
      where 
      date(shijian)>="2018-07-01" and date(shijian)<="2018-07-31";
      select * from t7 
      where
      date(shijian)="2018-07-31" and 
      time(shijian)>="10:00:00" and 
      time(shijian)<="12:00:00";

练习

    1、查询1天以内的记录

    2、查询1年以前的记录

    3、查询1天以前,3天以内的记录

答案：

      select * from t7 
      where shijian > (now()-interval 1 day);
      select * from t7
      where shijian < (now()-interval 1 year);
      select * from t7
      where
      shijian < (now()-interval 1 day) and
      shijian > (now()-interval 3 day);

练习(表hero)

    1、查找所有蜀国人的信息

    2、查找所有女英雄的姓名、性别和国家

    3、把id为2的记录改为典韦,性别男,国家魏国

    4、删除所有蜀国英雄

    5、把貂蝉的国籍改为魏国

    6、删除所有表记录

答案：

      select * from hero where country="蜀国";
      select name,sex,country from hero 
      where sex="女";
      update hero set name="典韦",sex="男",country="魏国" where id=2;
      delete from hero where country="蜀国";
      update hero set country="魏国" 
      where name="貂蝉";
      delete from hero;

练习

      1、找出攻击值高于200的蜀国英雄的名字、攻击力

      2、将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60

      3、查找蜀国和魏国的英雄信息

答案：

        select name as n,gongji as g from sanguo
    where gongji>200 and country="蜀国";
        update sanguo set gongji=100,fangyu=60
    where country="吴国" and gongji=110;
        select * from sanguo 
    where country="蜀国" or country="魏国";

练习

      1、查找攻击值100-200的蜀国英雄信息

      2、找到蜀国和吴国以外的国家的女英雄信息

      3、找到id为1、3或5的蜀国英雄 和 貂蝉的信息

答案：

        select * from sanguo
    where gongji between 100 and 200 and
    country="蜀国";
        select * from sanguo
    where country not in("蜀国","吴国") 
    and sex="女";
        select * from sanguo
    where 
    (id in(1,3,5) and country="蜀国") or name="貂蝉";

      1、在蜀国英雄中,查找防御值倒数第二名至倒数第四名的英雄的记录

      2、在蜀国英雄中,查找攻击值前3名且名字不为 NULL 的英雄的姓名、攻击值和国家

答案：

        select * from sanguo
        where country="蜀国"
        order by fangyu asc
        limit 1,3;
        select name,gongji,country from sanguo
        where 
        country="蜀国" and name is not NULL
        order by gongji DESC
        limit 3;

      1、攻击力最强值是多少

      2、统计id 、name 两个字段分别有几条记录

## 空值 NULL 不会被统计,""会被统计

      3、计算蜀国英雄的总攻击力

      4、统计蜀国英雄中攻击值大于200的英雄的数量

答案：

        select max(gongji) from MOSHOU.sanguo;
        select count(id),count(name) from sanguo;
        select sum(gongji) from MOSHOU.sanguo
    where country="蜀国";
        select count(*) from MOSHOU.sanguo
    where gongji>200 and country="蜀国";