MySQL：
- database：数据库 =》 文件夹
- table：表 =》excel
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 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 .插入数据
- 1.第一种方式
insert into gh.student (name,age) VALUES ("haoge",21),("bangzhang",20);
insert into gh.student (name,age) VALUES ("zuoshao",22);
- 1.第二种方式
insert into gh.student VALUES 全部指定;
insert into gh.student (name,age) VALUES ("zuoshao",22);
insert into gh.student (name,age) VALUES ("浩哥",22);
7 .查看数据
- select 语法结构： select 字段名字 from 表名字 [where]
- 表示 显示表里面所有字段
select * from gh.student;
- 模糊查询
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%' ;
- 子查询 sql 套 sql
select
from
(
select
from
) as a ; ***
select
from xx
where
column in (select id from xxxx );
- 例子
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;
8 .更新数据
- update 语法结构：update 表名 set 字段名 where 限制条件
update gh.student set age=80 where name="bangzhang";
9.删除数据
- delete 语法结构 ：delete from 表名 where 字段
delete from gh.student where name="banzhang";
- update 、delete 思考 是否加 where
- update gh.student set age=80 ;
- delete from gh.student;
4 .其他语法
1.条件过滤 where
- 例子
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 默认升序asc，降序desc
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.合并表
- 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
//结果有三条左少数据
4.null
- " '' , ' null ' , null
1.过滤：
- is
is not
select * from student where name 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 ;
5 .聚合语法 【聚合函数】
1 .聚合函数
- sum
count
max
min
avg
聚合函数:多行数据 按照一定规则【聚合函数】 聚合为一行
理论上说：
聚合后的行数 <=聚合前的行数
***********聚合之后 过滤不能用where
2 .group by 【分组】
- distinct（也可用于去重）
select distinct ssex from student ;--distinct去重数据
select avg(age) from student; //整个班 平均年龄
- 需求：
1.求name中带有zuoshao 的平均年龄
select avg(age) from student where name like "%zuoshao%";
- 需求
2.求每个name 的平均年龄
name ：zuoshaoxx
name：其他
指标：avg age
- 第一种方法，分别查询 name 为 zuoshao 和 其他学生的平均年龄，并用union all合并
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;
- 第二种方法，if判断，语法为if（1，2，3）如果1满足返回2，否则返回3
select
if(name like "%zuoshao%","zuoshao",name) as name_1,
avg(age) as avg_age
from student
group by name_1;
- 第三种方法，case when end用法
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.求每个name 的平均年龄
select name ,avg(age) as avg_age from student
group by name;
- 需求
- 查询 平均年龄 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
- 第二种写法
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;
6. join（多表联查）
- 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);
- 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;
- join
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;
- join
3.右连接 left join
以左表为主，数据是全的 ，右表来匹配，匹配不上就是 null
select a.* ,b.* from a1 as a right join b1 as b
on a.id = b.id and a.name=b.name;
- join
4.全连接 full outer join
全连接表都为主，数据是全的
左表来匹配，匹配不上就是 null
右表来匹配，匹配不上就是 null
7.指标 ： count条数
select id ,name ,age ,count(1) as cnt from student
group by id , name ,age
