mysql02

day02
create database [if not exists] 库名 character set utf8;
create table [if not exists] 表名（）；
表记录的增删改查
1.增
insert into 表名（字段1，字段2） values(值1，值2)；
insert into 表名 values(值1，值2，值3...);
2.删
delete from 表名 where 条件；
3.改
update 表名 set 字段1=值1，字段2=值2 where 条件;
4.查
select * from 表名 where 条件；
select 字段1，字段2 from 表名 where 条件；

１．数据类型
　　　１．数值类型
　　　２．字符类型
　　　３．枚举类型
　　　４．日期时间类型
　　　　　１．date : "YYYY-MM-DD"
          ２．datetime : "YYYY-MM-DD HH:MM:SS"
          ３．timestamp : "YYYY-MM-DD HH:MM:SS"
          ４．time: "HH:MM:SS"
     注意：　
      datetime: 不给值默认返回NULL
      timestamp: 不给值默认返回系统的当前时间
　　 ５．示例：
　　　  １．创建一张表，用户充值表
　　　　　 create table t4(
           id int,
           username varchar(20),
           password varchar(20),
           money int,
           birthday date,
           cztime timestamp
           )character set utf8;
        2.插入记录
        　　insert into t4 values
        (1,"用户１","123456",500,"1995-08-19","2018-08-30 09:40:30"),
        (2,"用户２","123456",700,"1996-06-12",now()),
        (3,"用户３","123456",600,"1997-03-17",now());
2.日期时间函数
　　　１．　now() 返回服务器当前时间
　　　２．　curdate() 当前日期
　　　３．　curtime() 当前时间
　　　４．date("1999-09-09 09:09:09")  从给定时间里　提取日期　年月日
　　　５．time("...")  从给定中　提取　时分秒
　　　６．year("...")  提取　年 
　　　练习：(面试/笔试)
　　　　１．查找＂2018-08-30＂用户充值的详细信息
　　　　　　 select * from t4 where date(cztime) = "2018-08-30";＇
　　　　２．查找2018年08月所有用户充值的信息
　　　　　　　select * from t4 where 
              date(cztime) >= "2018-08-01" and 
              date(cztime) <= "2018-08-31"; 

          注：cztime 给定的类型是timestamp，需要date 进行年月日提取

        ３．查找2018年08月30日　08:00-10:00之间的用户充值信息
    　　  　select * from t4 where 
            cztime >= "2018-08-30 08:00:00" and 
            cztime <= "2018-08-30 10:00:00"; 

3.日期时间运算（重点）
   1.语法格式：
      select * from 表名；
      where 字段名 运算符（now（）- interval 时间间隔单位）；
      时间间隔单位：
       2 day | 3 hour | 1 minute | 2 year | 3 month
   2.示例：
       1.查询一天以内的充值记录
        select * from t4 where 
    cztime >= (now()-interval 1 day);
       2.查询一年以前的充值记录
        select * from t4 where
    cztime <= (now()-interval 1 year);
       3.查询一天以前、3天以内的充值记录
        select * from t4 where
    cztime <= (now()-interval 1 day) and
    cztime >= (now()-interval 3 day);
4.表字段操作
   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 新表名；
   6.修改字段名（change）
    alter table 表名 change 原字段名 新字段名 数据类型；

   7.练习
    1.在db2库中建表studb，字段有3个：
     name,age,phnumber
     create database db2;
     create table studb(
     name varchar(20),
     age tinyint unsigned,
     phnumber bigint);
    2.在表中第一列添加一个id字段
    alter table studb add id first;
    3.把phnumber的数据类型改为char(11)
    alter table studb modify phnumder char(11);
    4.在表中最后一列添加一个字段 address
    alter atble studb add address varchar(20);
    5.删除表中的age字段
    alter table studb drop age

5.表记录的删除
  1. 删除表记录（delete）
    1. delete from 表名 where 条件；
    2.注意：
       一定要加where条件，不加where条件全部删除表记录
  2. 更新表记录（update）
    1. update 表名 set 字段1=值1，字段2=值2 where 条件；
    2.注意：
       一定要加where条件，不加where条件全部修改表记录
6.运算符操作
   1.数值比较&&字符比较&&逻辑比较
     1.数值比较： =  !=  >  >=  <  <=
     2.字符比较： =  !=
     3.逻辑比较：
       1.and：两个或多个条件同时成立
       2.or ：有一个条件满足即可
         where country="蜀国" or country="魏国";
   2.范围内比较：
     1.between 值1 and 值2
       select * from sanguo where (gongji between 100 and 200) and country="蜀国";
     2. in(值1，值2)
       select * from sanguo where (country not in("蜀国","吴国")) and sex="女";
     3. not in(值1，值2)
       select * from sanguo where (id in(1,3,5) and country="蜀国") or name="貂蝉";
   3.匹配空和非空：
     1.空： is null
     2.非空： is not null
       select * from sanguo where name is null and sex = "男" and country ="蜀国";  
   4.注意：
      1.NULL ：空值，只能用is /  is not 去匹配
      2."" : 空字符串，只能用 = !=去匹配
   5.模糊查询：（Like）
      1.where 字段名 like 表达式
      2.表达式
        1. _ : 匹配单个字符
    2. % ：匹配0到多个字符
    #name中有两个字符以上的
    select * from sanguo where name like "_%_";
    #匹配所用，但不包括NULL
    select * from sanguo where name like "%";
    #匹配名字为三个字符
    select * from sanguo where name like "___";
    #匹配名字姓赵的人
    select * from sanguo where name like "赵%";

   7.表字段、表记录操作
         表字段（alter table 表名）    表记录
增       add                         insert into 表名;
删       drop                        delete from 表名 where...;
改       modify                      update 表名 set ... where...;
查       desc 表名                   select * from 表名 where...;
7.SQL高级查询(面试)（************）
   1.总结
     执行顺序
     3.select ... 聚合函数 from 表名
     1.where ... (只能操作表内实际存在的字段)
     2.group by... 分组
     4.having...   
     5.order by... 排序
     6.limit...;   显示查询条数
   2.order by ：给查询结果排序
     1. order by 字段名 ASC/DESC
     2. ASC（默认）： 升序
     3. DESC ： 降序
      select * from sanguo 
      order by fangyu desc;
   3.limit （永远放在SQL命令的最后写）
     1.显示查询记录的条数
     2.用法：
       limit n;  --->显示n条记录
       limit  m,n; ----->从第m+1 条记录开始，显示n条
          limit 2,3;  -----> 从第3条开始显示，显示第3、4、5条记录
          limit 3; -----> 显示前3条记录
   4.分页
     每页显示5条记录，显示第4页的内容
     每页显示n条记录，显示第m页的内容
     第一页：limit (1-1)*5,5  #1 2 3 4 5
     第二页：limit (2-1)*5,5  #6 7 8 9 10
     第三页：limit (3-1)*5,5 #11 12 13 14 15
     ...
     第m页： limit (m-1)*n,n 
   5.聚合函数
     1.分类
       avg(字段名) ：求该字段的平均值
       sum（字段名）： 求和
       max（字段名）： 最大值
       min（字段名）： 最小值
       count（字段名）： 统计该字段记录的个数
          select max(gongji) as max from sanguo;----> as 别名 查询显示即为别名
          select count(id),count(name) from sanguo; 
   6.group by：给查询的结果进行分组
       select country from sanguo group by country;
       注：select后写的和group by后面的一致
       select country,avg(gongji) from sanguo
       group by country;
       执行顺序：先分组、针对每组进行聚合函数处理（avg（））、最后去重
       注意：
        select 之后的字段名如果没有在group by之后出现，
    则必须要对该字段进行聚合处理（聚合函数）
       select country,count(id) from sanguo
       group by country
       order by count(id) desc
       limit 2;
    7.having语句
      1.作用：对查询的结果进行进一步的筛选
      2.练习：having age > 20
           select country,avg(gongji) from sanguo
       group by country
       having avg(gongji) > 105
       order by avg(gongji)
       limit 2;
      3.注意：
         1.having 语句通常和group by语句联合使用，
       过滤由group by语句返回记录集
         2.where 只能操作表中实际存在字段，
       having 语句可操作由聚合函数生成的显示列

练习
    1、查找2018年8月30日用户充值的详细信息
      select * from t2 where date(cztime)="2018-08-30";
    2、查找2018年8月份所有用户充值的信息
      select * from t2 where
      date(cztime)>="2018-07-01" and date(cztime)<="2018-07-31";
    3、查找2018年08月30日 08:00-10:00之间用户充值信息
      select * from t2 where
      cztime>="2018-08-30 08:00:00" and cztime<="2018-08-30

 1、查询1天以内的充值记录
      select * from t2 where
      cztime>=(now()-interval 1 day);
    2、查询1年以前的充值记录
      select * from t2 where
      cztime<(now()-interval 1 year);
    3、查询1天以前、3天以内的充值记录
      select * from t2 where
      cztime>=(now()-interval 3 day) and
      cztime<=(now()-interval 1 day);

  1、在 db2 库中创建表 stutab ,字段有3个：
      name、age、phnumber
      use db2;
      create table stutab(
      name char(20),
      age tinyint,
      phnumber bigint
      );
    2、在表中第一列添加一个 id 字段
      alter table studab add id int first;
    3、把 phnumber 的数据类型改为 char(11)
      alter table studab modify phnumber char(11);
    4、在表中最后一列添加一个字段 address
      alter table studab add address varchar(30);
    5、删除表中的 age 字段
      alter table stutab drop age;

1、查找攻击力高于150的英雄的名字和攻击值
        select name,gongji from sanguo
    where gongji>150;
2、将赵云的攻击力设置为360,防御力设置为88,名字改为赵子龙
        update sanguo set gongji=360,fangyu=88,name="赵子龙" 
 3、将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60
        update sanguo set gongji=100,fangyu=60
    where country="吴国" and gongji=110;
4、查找蜀国和魏国的英雄信息
        select * from sanguo where
    country="蜀国" or country="魏国";
5、找出攻击力高于200的蜀国英雄的名字、攻击值和国家
        select name,gongji,country from sanguo 
    where
        gongji>200 and country="蜀国";

 1、查找攻击值100-200之间的蜀国英雄信息
        select * from sanguo 
        where
        (gongji between 100 and 200) and country="蜀国";
2、查找蜀国和吴国以外的国家的女英雄信息
        select * from sanguo 
        where country not in("蜀国","吴国") and sex="女";
3、查找id为1、3或5的蜀国英雄 和 貂蝉的信息
        select * from sanguo
        where
        (id in(1,3,5) and country="蜀国") or name="貂蝉";

1、查找姓名为 NULL 的蜀国男英雄信息
        select * from sanguo
    where
        name is NULL and country="蜀国" and sex="男";
      2、查找姓名为 "" 的英雄信息
        select * from sanguo where name="";
      3、在所有蜀国英雄中查找攻击力大于150的并且名字不为NULL的英雄的姓名、攻击值和国家
        select name,gongji,country from sanguo
    where
        country="蜀国" and gongji>150 and name is not NULL;
      4、查找魏蜀两国英雄中攻击力小于200并且防御力小于80的英雄信息
        select * from sanguo
    where
        country in("魏国","蜀国") and gongji<200 and fangyu<80;

      1、将所有英雄按防御值从高到低排序
        select * from sanguo order by fangyu DESC;
      2、将蜀国英雄按攻击值从高到低排序
        select * from sanguo 
    where country="蜀国" order by gongji DESC;
      3、将魏蜀两国英雄中名字为3个字的,按防御值升序排序
        select * from sanguo where
    country in("魏国","蜀国") and name like "___"
    order by fangyu;

 1、在蜀国英雄中,查找防御值倒数第2名到倒数第4名的英雄的姓名、防御值和国家
        select name,fangyu,country from sanguo
    where country="蜀国"
    order by fangyu ASC 
    limit 1,3;
      2、在所有蜀国名字不为NULL的英雄中,查找攻击值前3名的英雄的姓名、攻击值和国家
        select name,gongji,country from sanguo
        where
    country="蜀国" and name is not NULL
    order by gongji DESC
    limit 3;    

     1、所有英雄中攻击力最大值
        select max(gongji) from sanguo;
      2、统计id、name两个字段分别有多少条记录
        select count(id),count(name) from sanguo;

    1、查询表中都有哪些国家
      select country from sanguo group by country;
    2、计算每个国家的平均攻击力
      select country,avg(gongji) from sanguo
      group by country;

 1、找出平均攻击力大于105的国家的前2名,显示国家名和平均攻击力
        1、计算每个国家的平均攻击力
    2、找到平均攻击力 > 105 的
    select country,avg(gongji) as average from sanguo
    group by country
    having average>105
    order by average DESC
    limit 2;