mysql_02

1.查看表中描述
   desc tablename;

2.创建表
   create 建表规范：
   1.表名字 一定是英文 不要写中文  汉语拼音
   2.建表风格
      1.主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义
   3.字段的注释
业务字段
非业务字段:
   1.表创建表用户 vs 更新表用户
   2.主键

create table user_info2(
   id int(3) not null  auto_increment,
   name varchar(10) COMMENT '用户名称',
   age int(3),
   create_user varchar(10),
   create_time timestamp not null default current_timestamp,
   update_user varchar(10),
   update_time  timestamp not null default current_timestamp on update current_timestamp
   ,primary key(id)
   );

3.插入数据
   INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

   insert into user_info (name,age) VALUES('zs',18),('ls',20);


   insert into user_info (name,age) VALUES('zs01',18),('ls01',20);

   NULL: 空值 的三种表示方式'' 'null' 'NULL'

4.更新数据
   UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
   where 过滤
   update user_info set age='20'; =》 对整张表
   update user_info set age='20' where name='zs' ; 对某条数据
   update 语句 注意： where
5.删除一条数据
   delete
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
   delete from user_info where id=1;
注意：
   delete 也要考虑 是否加where
   insert into user_info (name,age) VALUES('zs',18),('ls',20);
   insert into user_info (name,age) VALUES('zihang01',10),('子航01',10);
   insert into user_info (name,age) VALUES('zihang02',11),('子航02',20);
   insert into user_info (name,age) VALUES('zihang03',12),('子航03',30);
   insert into user_info (name,age) VALUES('zihang04',13),('子航04',21);
   insert into user_info (name,age) VALUES('zs',19),('zs',21);
表的字符集：
   udf8
   Incorrect string value: '\xE5\xAD\x90\xE8\x88\xAA...' for column 'name
   mysql 5.7版本 默认建表字符集 ：latin1
   解决：修改字符集 、建表直接指定字符集 utf8
6.删除表
   drop table user_info;
   CREATE TABLE `user_info` (
   `id` int(3) NOT NULL AUTO_INCREMENT,
   `name` varchar(10) DEFAULT NULL,
   `age` int(3) DEFAULT NULL,
   `create_user` varchar(10) DEFAULT NULL,
   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `update_user` varchar(10) DEFAULT NULL,
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

7.其他语法
1.where  过滤条件
   1. >
   2.<
   3. =
   4. and or in not in
   mysql> select * from user_info where age > 18;
   select * from user_info where name='子航01';
   mysql> select *  from user_info where name='zs' and age>18;
   mysql> select *  from user_info where age>20 or name='zs';

   产品给你 1000个数据值  in
   select *  from user_info where name='zs' or name = 'ls' or name ="ww"
   select *  from user_info where name in ('zs','ls','ww');
      not in
   select *  from user_info where name not in ('zs','ls','ww');

2.order by 排序语法
   mysql> select *  from user_info order by age;

   order by column [asc | desc] ,...

   1.默认是升序
   2. asc desc 降序
   mysql> select *  from user_info order by age ,name desc;

3.like语法 模糊查询
   1.like  rlike regexp  正则表达式【了解】
      1.% 模糊
      2.占位符 _
      mysql> select *  from user_info where name like '%z%';
      mysql> select *  from user_info where name like "_s%";
      nsert into user_info (name) VALUES('zihsssg04');
      nsert into user_info (name) VALUES('zshsssg04');
      nsert into user_info (name) VALUES('zshsssg04');
      nsert into user_info (name) VALUES('zshsssg04');

    需求：
       1.name 字母开头是y
       select  *  from user_info where name like "y%";
       2.name 字母结尾是1
       select  *  from user_info where name like "%1";
       3.name 含有字母h
       select  *  from user_info where name like "%h%";
       4.name查询第3个字母是h的数据
       select  *  from user_info where name like "__h%";

4.合并表
   1.union  去重
   2.union all  不去重
      create table a(id int(3),name varchar(4));
      create table b(id int(3),name varchar(4));
      insert into a values(1,'zs');
      insert into b values(1,'zs');
      insert into b values(2,'ls');
   1.union
      select *  from a
      union
      select *  from b;
   2.union all
      select *  from a
      union  all
      select *  from b;
5.null
   数据清洗
      1.过滤null
         is null
         is not null
      select *  from user_info where age is null;
      2.null数据进行 【数据转换】
         update

   数据分析时候 ：
      delte 、update
   函数：
      处理 null的函数：
         1.coalesce()
         2.ifnull
      select
      id
      ,name
      ,coalesce(age,0) as age_alias
      ,create_user
      ,create_time
      ,update_user
      ,update_time
      from user_info

      select
      id
      ,name
      ,ifnull(age,0) as age_alias
      ,create_user
      ,create_time
      ,update_user
      ,update_time
      from user_info
6.聚合函数： 指标
   多行数据按照一定规则 进行聚合为一行
   sum max min avg count
   理论上：
      聚合后的行数 <= 聚合前的行数
      insert into user_info (name,age) values("zs",10);
      insert into user_info (name,age) values("zs",11);
      insert into user_info (name,age) values("zs",12);
      insert into user_info (name,age) values("ls",10);
      insert into user_info (name,age) values("ls",20);
      insert into user_info (name,age) values("ls",30);
      insert into user_info (name,age) values("ww",30);
   1.聚合函数
      select
      sum(age) as age_sum,
      max(age) as age_max
      ,min(age) as age_min
      ,avg(age) as age_avg
      ,count(age) as cnt
      from user_info
   2.分组语法
   词频统计：
      wordcount
   word,1
   =>  对每个单词进行分组  分组
      x,<1>
      y,<1,1>
      z,<1>
   => 聚合 sum
      x,1
      y,1+1 =2
      z,1
   group by  column...
需求：
   按照name进行分组，求每组的平均年龄
分组聚合
      select
      name,
      avg(age) as age_avg
      from user_info
      group by name
   需求：
   user_info 各个name的最大年龄、最小年龄、以及人数？

      select
      name,
      max(age) as age_max,
      min(age) as age_min,
      count(age) as cnt
      from user_info
      group by name
   分组聚合:注意：
   1.select 字段 和  group by 字段 要对应 【非聚合函数字段】
      select
      name,
      id,
      max(age) as age_max,
      min(age) as age_min,
      count(age) as cnt
      from user_info
      group by name ,id

   维度不一样：
      1.
         维度： name
         指标：最大年龄、最小年龄、以及人数
      2.
          维度： name、id
          指标：最大年龄、最小年龄、以及人数


      select
      name,
      max(age) as age_max,
      avg(age) as age_avg,
      count(age) as cnt
      from user_info
      group by name

求：
   上面的结果 求： age_avg 大于 18岁的 信息？
   1.分组聚合 + having
      select
      name,
      max(age) as age_max,
      avg(age) as age_avg,
      count(age) as cnt
      from user_info
      group by name
      having age_avg > 18
   总结：
      条件过滤：
         1.where 写在 from 后面
         2.having 写在 group by 后面

   上面的结果 求： age_avg 大于 18岁的 信息？

1. 子查询：
   查询里面 嵌套查询
      select
      name,
      age_max,
      age_avg,
      cnt
      from
      (
      select
      name,
      max(age) as age_max,
      avg(age) as age_avg,
      count(age) as cnt
      from user_info
      group by name
      ) as res
      where
      age_avg >18;

7.join 多表联查
   种类：
   4种 广义上
      内连接、左连接、右连接、全连接
      内连接 **
      左连接 **
      全连接 *
      create table a1(id int(3),name varchar(10),address varchar(20));
      create table b1(id int(3),name varchar(10),age int(3));
      insert into a1 values(1,'aa',"dalian");
      insert into a1 values(2,'bb',"shenyang");
      insert into a1 values(4,'dd',"beijing");
      insert into b1 values(1,'aa',10);
      insert into b1 values(2,'bb',20);
      insert into b1 values(3,'cc',21);

   1.内连接
      join
      select
      *
      from a1 join b1
      on a1.id = b1.id

      select
      *
      from a1 inner join b1
      on a1.id = b1.id

   2.左连接
      left join
         以左表为主 数据是全的 右表来匹配 匹配不上就是null

      select
      *
      from a1 left join b1
      on a1.id =b1.id;
   3.右连接
      right join
         以右表为主 数据是全的 左表来匹配 匹配不上就是null
      select
      *
      from a1 right join b1
      on a1.id =b1.id;

   4.全连接 -- mysql 不支持全连接
      左表右表数据是全的，而且没有重复数据
      full join
      select
      *
      from a1 left join b1
      on a1.id =b1.id
      union
      select
      *
      from a1 right join b1
      on a1.id =b1.id
8. 查询数据条数 显示限制
      limit