2022-03-22 mysql 简单命令及其练习

1.字段类型

##数值类型：
    int 整型
    long 长整型 
    bigint
    float  单精度 
    double 双精度 
    decimal 小数   跟钱有关
##字符串
    char  字符    0-255 长度   zuoshaoxxxxxxx  255 自动补齐
    varchar 字符串  变长     zuoshao
    text 文本
##日期：
    date 日期 YYYY-MM-DD
    time 时间 HH:mm:SS
    datetime 年月日时分秒 YYYY-MM-DD HH:mm:SS
    timestamp 年月日时分秒【时间戳】 YYYY-MM-DD HH:mm:SS

2.sql类型

ddl 数据定义语言：create drop  
dml 数据操作语言：select insert update delete
dcl 数据控制语言：grant

3.基本语法【sql】

1.创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
create database gh;

2.查看数据库

show databases;

3.使用数据库 [切换数据库]

default
use gh;

4.创建表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)

create_definition: {
    col_name data_type 
}

create table student(
  id int(11) not null auto_increment,
  name varchar(255),
  age int(3),
  create_user varchar(255),
  create_time timestamp not null default current_timestamp,
  update_user varchar(255),
  update_time timestamp not null default current_timestamp on update current_timestamp,
  primary key(id)
);

5.查看表

show tables;

6.插入数据

INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }

insert into gh.student (name,age) VALUES ("haoge",21),("bangzhang",20);

insert into gh.student (name,age) VALUES ("zuoshao",22);

insert into gh.student VALUES 全部指定;

insert into gh.student (name,age) VALUES ("zuoshao",22);

update gh.student set name="左少" where name="zuoshao";
insert into gh.student (name,age) VALUES ("浩哥",22);

7.查看数据

select 语法结构：
select 字段名字 from 表名字 [where]

* 表示 显示表里面所有字段

select * from gh.student;

8.更新数据

update

UPDATE table_reference
SET assignment_list
[WHERE where_condition]

update gh.student set  age=80  where name="bangzhang";

9.删除数据

delete
DELETE FROM tbl_name
[WHERE where_condition]

delete  from gh.student where name="banzhang";

update 、delete 思考 是否加 where

update gh.student set  age=80  ;
delete  from gh.student;

其他语法：

1.条件过滤 where

=
>
<
and 
or 
in 
not in 

select *  from gh.student where age <20;
select * from gh.student where age=20 and name="zuoshao" ;
select * from gh.student where age=20 or name="zuoshao" ;
select * from gh.student where age in (20,12,11,99) ;
select * from gh.student where age not in (20,12,11,99) ;

2.排序语法

order by

select *  from gh.student order by age asc;
select *  from gh.student order by age desc;
select *  from gh.student order by age desc,name desc;

3.模糊查询

like ：
1.%
2._ 占位符

regexp 【正则表达式】【课后作业】

select * from gh.student where name like 'z%' ;
select * from gh.student where name like '%z%' ;
select * from gh.student where name like '%z' ;

先思考： name 第三个字母是n

select * from gh.student where name like '__n%' ;

4.合并表

union 去重
union all 不去重

        create table a(id int ,name varchar(255));
    create table b(id int ,name varchar(255));

    insert into a VALUES(1,'左少');
    insert into b VALUES(1,'左少');
    insert into b VALUES(1,'左少');
    select  * from a 
    union 
    select  * from b;
    select  * from a 
    union  all 
    select  * from b

5.null

"" '' 'null' null

1.过滤：

    is 
    is not 

select *  from student where create_user is null;

2.数据清洗（etl）

脏数据 =》 规范的数据
函数 :ifnull ,coalesce

    null -> --

select 
id
,name    
,age 
,ifnull(create_user,"--") as elt_create_user
,create_time        
, ifnull(update_user,"--")  as elt_update_user
,update_time
from gh.student ;

select 
id
,name    
,age 
,coalesce(create_user,"--") as elt_create_user
,create_time        
, coalesce(update_user,"--")  as elt_update_user
,update_time
from gh.student ;

6.聚合语法 【聚合函数】

1.聚合函数

    sum 
    count
    max
    min
    avg

聚合函数:多行数据 按照一定规则【聚合函数】 聚合为一行
    理论上说： 
        聚合后的行数 <=聚合前的行数

2.group by 【分组】

select avg(age)  from student; //整个班 平均年龄

需求：
1.求name中带有zuoshao 的平均年龄
select avg(age) from student where name like "%zuoshao%";

    2.求每个name 的平均年龄 
        name ：zuoshaoxx 
        name：其他 
        指标：avg age 

select 
        'zuoshao' as name_1,
        avg(age) as avg_age
        from student 
        where 
        name like "%zuoshao%"
        group by name_1

        union all 

        select 
        name,
        avg(age) as avg_age
        from student 
        where 
        name not like "%zuoshao%"
        group by name;


        select 
        if(name like "%zuoshao%","zuoshao",name) as name_1,
        avg(age) as avg_age
        from student 
        group by name_1;

        select 
        case when 
            name like "%zuoshao%" then "zuoshao" 
            else name end as name_1,
        avg(age) as avg_age
        from student 
        group by name_1;

3.2求每个name 的平均年龄

            select 
            name,
            avg(age) as avg_age
            from student
            group by 
            name;

4. 查询 平均年龄 20

select 
case when 
name like "%zuoshao%" then "zuoshao" 
else name end as name_1,
avg(age) as avg_age
from student 
group by name_1

聚合之后 过滤不能用where having

select 
case when 
name like "%zuoshao%" then "zuoshao" 
else name end as name_1,
avg(age) as avg_age
from student 
group by name_1
having avg_age>20;

select 
name_1 as name ,
round(avg_age,2) as age 
from 
(
    select 
    case when 
    name like "%zuoshao%" then "zuoshao" 
    else name end as name_1,
    avg(age) as avg_age
    from student 
    group by name_1
) as tmp
where 
avg_age >20;

子查询：
sql 套sql

select 
sum(age) as sum_age, 
count(1) as  cnt, -- 统计 表中数据条数
max(age) as  max_age,
min(age) as  min_age,
avg(age) as  avg_age
from student;

1.统计表中name字段数据条数【name不能重复】

select count(distinct name) as  cnt  from student;

2.使用group by 执行效率高

select 
count(name) as cnt 
from 
(
    select 
    name 
    from student
    group by name 
) as a;

group by :分组

select 
name_1,
avg(age) as avg_age
from student 
group by name_1

group by :
x,1
y,1
z,1
x,1
=> 分组+聚合函数 =》 “拉倒一起” “去做一些事情””
“拉倒一起”： group by
x,<1,1>
y,<1>
z,<1>
“去做一些事情””avg
x,<1,1> => x,(1+1) /2
y,<1> => y, 1/1
子查询：

select 
from 
(
    select 
    from 
) as a ; ***

select 
from  xx 
where 
column in (select id from xxxx );

7.join（多表联查）

类：7大类

create table a1(id int ,name varchar(255),address varchar(255));
create table b1(id int ,name varchar(255),age int(3));

insert into a1 VALUES(1,'zuoshao','长春');
insert into a1 VALUES(2,'haoge','苏州');
insert into a1 VALUES(4,'banzhang','山东');

insert into b1 VALUES(1,'zuoshao',21);
insert into b1 VALUES(2,'haoge',21);
insert into b1 VALUES(3,'xuanxuan',29);

sql 7 join:

1.内连接 inner join (join)

select 
a.*,
b.*
from a1 as a join b1 as b 
on a.id = b.id and a.name=b.name;

select 
a.id,
a.name,
a.address,
b.age
from a1 as a join b1 as b 
on a.id = b.id and a.name=b.name;

2.左连接 left join

以左表为主，数据是全的 ，右表来匹配，匹配不上就是 null

select 
a.*,
b.*
from a1 as a left join b1 as b 
on a.id = b.id and a.name=b.name;

3.右连接 right join

以右表为主，数据是全的 ，左表来匹配，匹配不上就是 null

select 
a.*,
b.*
from a1 as a right join b1 as b 
on a.id = b.id and a.name=b.name;

4.全连接 full outer join

全连接表都为主，数据是全的 
    左表来匹配，匹配不上就是 null
    右表来匹配，匹配不上就是 null

左连接: 99.99 00.1 inner join
主表 维表 =》 事实表 维度表

主表 维表

维度组合：
通过维度去分析指标

维度：column
指标：sum max min count

三个维度：
id | name | age

指标 ： count条数

select 
id,
name,
age,
count(1) as cnt 
from student 
group by
id, 
name,
age

注意：
select 字段、指标【聚合函数】 from xx where
group by 字段

有视频=》 大数据 => 电商的进销存 erp =》 指标查询
grouping sets

java

linux shell mysql hadoop** hive***
zookeeper kafka* flume* hbase** scala spark**** flink ***
maxwell cannel sqoop* datax* clickhouse*

sql on hbase => phoniex

spark****:
sparkcore 离线
sparksql 离线
sparkstreaming 实时
structed streaming 实时 ***

flink ：

flink
flinkcdc 
flinksql ****

离线数仓： *****
实时数仓：***

实时同步***
大数据同步工具**sparksql
maxwell 底层开发 issue

t5 =>

极客时间 =》 可以
官网 =》
大厂分享博客=》
公众号=》 
问 同事
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • MySQL手记
    启动和停止MySQL服务： 在管理员权限下： （我本人的是mysql57，启动后可以在任务管理器的服务栏看到） 注...
    墨革阅读 485评论 0赞 2
  • Mysql所有语句命令
    /* Windows服务 */-- 启动MySQLnet start mysql-- 创建Windows服务sc ...
    阿登20阅读 203评论 0赞 0
  • MySQL 学习笔记（二）
    MySQL 学习笔记（一）[https://www.jianshu.com/p/dde13bbe0fd9] MyS...
    Whyn阅读 321评论 0赞 0
  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 686评论 0赞 1
  • MySQL优化系列8-MySQL的执行计划介绍
    备注:测试数据库版本为MySQL 8.0 一.使用EXPLAIN优化查询 1.1 Explain语法及概述 语法:...
    只是甲阅读 610评论 0赞 0