MySQL2

按照官网上的介绍来操作
1. 查看表头的相关信息
desc tablename
Field：字段、Type：类型、NULL：判断字段是否可以为空、Default：默认值、EXtra：备注扩展
2.创建表：creat
建表规范：
 1.表名字：一定是英文，不要写中文，实在不行用汉语拼音
 2.建表风格：主键一般使用表中第一个字段使用自增主键，本身没有任何业务意义，为了查询，提高查询性能
 3.字段的注释（注释在DDL里能看到，用desc看不到）

业务字段（存储信息的字段）
非业务字段（表由谁创建的，建表时间，更新，主键）表创建表用户 vs 更新表用户、主键

create table user_info(
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’：都表示字符串为空，但是该字段不是空值

插入的数据含有中文报错

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);

解决：修改字符集 、建表直接指定字符集 utf8
表的字符集：
  udf8
    Incorrect string value: ‘\xE5\xAD\x90\xE8\x88\xAA…’ for column 'name
  mysql 5.7版本 默认建表字符集 ：latin1
用dbearver，刷新表，看DDL，把latin1换成utf8（最开始建表的时候就要加上）

ENGINE=InnoDB使用InnoDB引擎。
DEFAULT CHARSET=latin1 数据库默认编码为latin1
AUTO_INCREMENT=1 自增键的起始序号为1，AUTO_INCREMENT会在新记录插入表中时生成一个唯一的数字。希望在每次插入新记录时，自动地创建主键字段的值，可以在表中创建一个 auto-increment 字段。

修改字符集 ：ALTER TABLE bigdata.user_info DEFAULT CHARSET=utf8;
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’ ; 对某条数据（更新时间也会变化）

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;
注意：delect要考虑是否加where

6.删除表

drop table user_info;

案例：建一个表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、>、<

select * from user_info where age > 18;

   2、=

select * from user_info where name=‘子航01’;

   3. and：多个条件一起

select * from user_info where name=‘zs’ and age>18;

   4.or：条件1或者条件2

select * from user_info where age>20 or name=‘zs’;

   5.in：多个或者的条件
产品给你 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’);

   6.not in：不要多个或者的条件

select * from user_info where name not in (‘zs’,‘ls’,‘ww’);

 2.order by 排序语法
order by column [asc | desc] ,…
  1.默认是升序
  2. asc desc 降序

select * from user_info order by age;

添加一条数据：insert into user_info (name,age) VALUES(‘yihang01’,10);
select * from user_info order by age ,name desc;（按照年龄升序排序，如果年龄相等按照名字降序排序）

  3.like语法：模糊查询
    1.% 模糊
    2.占位符 _

select * from user_info where name like ‘%z%’;（名字里带有z）
添加数据:
insert into user_info (name) VALUES(‘zihsssg04’);
insert into user_info (name) VALUES(‘zshsssg04’);
insert into user_info (name) VALUES(‘zshsssg04’);
insert into user_info (name) VALUES(‘zshsssg04’);
select * from user_info where name like “_s%”;（名字里第二个字母是s的）

练习：
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.合并表（关联是横向的，合并是纵向的）（上下表的字段和数据类型一定是一样的）

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
注意：工作中不能删除数据，只是查询出来，
做数据分析时候：delte 、update都不可以（源头数据不可以动）
is null（拿出来）、is not null（过滤掉）

select * from user_info where age is null;
select * from user_info where age is not null;

  2.null数据进行数据转换
需要处理 null的函数：1.coalesce()、2.ifnull
   ifnull(),两参数。若第一个参数不为空，返回该字段，若为空，返回第二个参数
   coalesce(),多参数。返回第一个非空值，select coalesce(null,null,4,null,3,1)会返回4

select id ,name ,
ifnull(age,0) as age_alias,（age字段里有空值，就把函数放在这里，age为空就给一个值为0，还要给一个别名，别名一定要起）
create_user ,create_time ,update_user ,update_time
from user_info

DBeaver远程登录执行:
 点数据库，点上方的加号，在上方选择数据库bigdata，
 写完代码之后，全选代码，
 然后点左边的箭头执行，数据就会在下方显现

6.聚合函数： 指标（聚合函数的结果）
  多行数据按照一定规则 进行聚合为一行
  sum max min avg count（count（）里写啥都行并不影响）
 s 理论上：聚合后的行数 <= 聚合前的行数

清空表里的数据：delete from user_info;
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.分组语法：group by column…
案例：词频统计：wordcount
         x
         y
         y
         z
 word,1 把每个单词赋值为1
 => 对每个单词进行分组
   x,<1>
   y,<1,1>
   z,<1>
 => 聚合 sum
   x,1
   y,1+1 =2
   z,1
分组聚合：
注意：1.select字段和group by字段要对应【非聚合函数字段】

需求：按照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

需求：user_info 各个name、id的最大年龄、最小年龄、以及人数？

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
    指标：最大年龄、最小年龄、以及人数

求： age_avg 大于 18岁的 信息？

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

    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 后面

    2. 子查询：查询里面嵌套查询（基于结果表进行查询）

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.内连接：inner join （inner工作中不可省）

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.全连接：full join-- mysql不支持全连接，左表右表数据是全的，而且没有重复数据

select *
from a1 left join b1
on a1.id =b1.id
union all
select *
from a1 right join b1
on a1.id =b1.id
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
  场景： 1.显示 避免发生 滚屏效果
        2.小表 select * from table ; 几千条数据
  table： 100M 1PB 1TB
  mysql: 极限 =》 TB级别 相应速度 s级别 ms级别【添加索引】
  前提：取决于公司的数据量
    hive,hbase →不要求响应速度
    presto s级别
    clickhouse s级别 ms级别

select * from a1 limit 2;