Mysql总结

最近又重新复习了一遍SQL的基础和进阶知识，SQL可以很基础也可以很复杂，写一篇总结来梳理自己的知识脉络

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库。

SQL语句主要分为：

DQL：数据查询语言，用于对数据进行查询，如select

DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete

TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback

DCL：数据控制语言，进行授权与权限回收，如grant、revoke

DDL：数据定义语言，进行数据库、表的管理等，如create、drop

CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

数据分析必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它语言如TPL、DCL、CCL了解即可。

---

在实际操作过程中，我使用的是MYSQL，MySQL使用标准的SQL数据语言形式，同时开源免费，适用范围广。开启MYSQL可以用电脑的终端，也可以搭载可视化软件(navicat或mysqlworkbench)。

一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中。在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

MySQL支持所有标准的SQL数据类型，主要分3类:

    ·数值类型

    ·字符串类型

    ·时间日期类型

可以参考这篇文章-https://blog.csdn.net/anxpp/article/details/51284106

约束 需要了解：

·主键primary key：物理上存储的顺序

·非空not null：此字段不允许填写空值

·惟一unique：此字段的值不允许重复

·默认default：当不填写此值时会使用默认值，如果填写时以填写为准

·外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。

---

数据库的操作

-- 连接数据库

mysql -uroot -p (输入密码）

-- 退出数据库

exit/quit 

-- 查看所有数据库

show databases;

-- 查看当前使用的数据库；

select database();

-- 创建数据库

create database 数据库名 charset=utf8;

--使用数据库

use  数据库名;

-- 删除数据库

drop database 数据库名;

数据表的操作

-- 查看当前数据库所有表

show tables;

-- 查看表结构

desc 表名;

-- 创建表

create table 表名（字段 类型 约束，字段 类型 约束，···）;

create table user_info (

id int unsigned primary key auto_increment not null,

name varchar(20) not null

);

-- 查看建表语句

show create table 表名；

 -- 删除表

drop table 表名;

表中字段的操作

-- 修改字段名 重命名

alter table 表名 change 原列名 新列名 类型及约束；

alter table user_info change id user_id varchar(20) not null;

-- 修改字段 不重命名

alter table 表名 modify 列名 类型及约束;

-- 添加字段 

alter table 表名 add 列名 类型；

-- 删除字段 

alter table 表名 drop 列名；

数据的操作 增删改查（crud)

-- 增加记录

全列插入 insert into 表名 values(...) 如 insert into classes values(1,'zhangsan');

部分插入 insert into 表名(列1,...) values(值1,...) 如  insert into classes(name) values('zhangsan');

多行插入 insert into students values(0,'老刘',40,160,'男',003),(0,'老王',20,180,default,003);

注意：主键字段 可以用 0、null、default 来占位

-- 删除记录

物理删除 delete from 表名 where 条件 

逻辑删除 即用一个字段来表示这条信息已被删除 

alter table students add is_delete bit default 0; 

-- 修改记录

update 表名 set 列1=值1，列2=值2 ··· where 条件；

全部修改 update students set gender='保密'

部分按条件修改 update students set gender='女' where name='huahua'

按条件修改多个值 update students set gender='男' where cls_id=3;

-- 查询记录

全部查询 select * from 表名；

条件查询 select * from 表名 where 条件；

查询指定列 select 列名 from 表名 ；

用AS给字段起别名 select 字段[as 别名] , 字段[as 别名] from 表名

***  高级查询语句

条件查询：使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

where后面支持多种运算符，进行条件的处理

1、比较运算符（大于小于等于不等于···）

2、逻辑运算符（and\or\not)

3、模糊查询（like:%表示任意多个任意字符, _表示一个任意字符)

4、范围查询（in、 between··· and···）

5、空判断（is null、is  not null)

聚合函数：

1、总数 count(列）不会统计null值记录

count(*)=count(1) ，结果是相同的，count(列) 不会统计null值记录

2、最大值 max(列)

3、最小值 min(列)

4、求和 sum(列)

5、求平均值 avg(列)

分组查询：

1、group by

2、group by + group_concat() 

group_concat(字段名)可以作为一个输出字段来使用

select gender,group_concat(name) from students group by gender;

+--------+-----------------------------------------------------------+| 

gender | group_concat(name) |

+--------+-----------------------------------------------------------+| 

男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 || 

女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 || 

中性 | 金星 || 保密 | 凤姐 |

+--------+-----------------------------------------------------------+

3、group by + 集合函数

4、group by + having 用来分组查询后指定一些条件来输出查询结果

5、 group by + with rollup 在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;

+--------+----------+

| gender | count(*) |

+--------+----------+

| 男    |        5 |

| 女    |        7 |

| 中性  |        1 |

| 保密  |        1 |

| NULL  |      14 |

+--------+----------+

分页查询:

limit: select * from 表名 limit start,count 从start开始，获取count条数据

连接查询：

三种连接查询

子查询：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

子查询分为三类：

1、标量子查询: 子查询返回的结果是一个数据(一行一列)

如 查询大于平均年龄的学生

2、列子查询: 返回的结果是一列(一列多行)

如 查询所有学生在班的所有班级名字

3、行子查询: 返回的结果是一行(一行多列)

如 查询年龄最大，身高最高的学生具体信息

要感慨一句，子查询可以很复杂，绕人，但是有时候也很好用。。。。

---

数据查询的执行顺序

from-where-groupby-having-select-orderby-limit

1、from首先从哪张表取数

2、where筛选条件，不是所有的数据都要

3、groupby 做一个分组聚合

4、having 分组聚合后的结果也并不是都要,满足条件的才要

5、select  选择需要的指定字段

6、orderby 排序

7、limit 限制显示数量

举个例子：从订单表中查询出过去一年内，购买10次以上的用户名单，展示前10名的姓名、购物金额

select 姓名、sum(price) as 购物金额

from 订单表

where 时间=过去一年内

group by 用户ID

having count(*)>10

order by 购物金额 desc

limit 10

*having后面可以接聚合函数，但是where后不能接聚合函数，因为执行顺序的原因，where执行时还没有完成分组聚合。

---

时间函数

--获取当前时间

select now() 执行开始时的系统时间

select sysdate() 在函数执行时动态得到值

-- 获取年月日 

select current_date()

-- 获取时分秒

select current_time()

-- 转换字符串为日期格式

select str_to_date(时间字符串，字符串日期格式）

select str_to_date（'08/09/08' , '%m/%d/%y'); -- 2008-08-09

-- 转换日期为字符串格式

select date_format(now(),'%Y-%M-%d %H')

-- 提取时间的函数

常用的year/month/time/day/week/minute/hour/quarter````

dayofyear,dayofmonth,dayofweek,dayofmonth,yearweek```

-- 日期的运算

减法 date_sub(日期 ，要减少偏移的间隔)

select date_sub('1998-01-01 00:00:00', interval 1 day); --- 1997-12-31 00:00:00

加法 date_add(date,INTERVAL expr type)

select date_add('1998-01-01 00:00:00', interval 1 day); --- 1998-1-2 00:00:00

-- 两个日期的天数差

date_diff（time1，time2）：返回两个日期之间（time1-time2）的天数。

eg：

select datediff('2008-08-08','2008-08-01')

-- 两个日期的时间差

timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

--- 两个日期得到指定差

timestampdiff(unit,begin,end)

返回end-begin的结果，其中begin和end是date或datetime格式

例如：timestampdiff(year,birth,now()) 计算生日 返回两个日期之间的年份，未过生日减一

---

做题的过程中，发现窗口函数也是常考内容，补充一下

窗口函数：Mysql8.0以上版本支持窗口函数，窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。

面对一些既要分组又要排序的TOP N问题，我们可以使用窗口函数快速得出结果.

窗口函数和普通聚合函数区别如下：

··聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。

··聚合函数也可以用于窗口函数中

使用方法：配合聚合窗口函数一起使用，例如SUM/AVG/COUNT/MAX/MIN等

select * 

from(

    select*,

        row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as 排名

    from 表名) as a

where排名<=N;

ROW_NUMBER() 依次排序且不会重复,不考虑并列名次的情况

DENSE_RANK() 遇到重复值时连续排序,如果有并列名次的行，不占用下一名次的位置

RANK() 跳跃排序,如果有并列名次的行，会占用下一名次的位置

PERCENT_RANK() 百分位排序，遇到相同值的时候连续排序。

---

case when语句

"case

　　　　when 列名= 条件值1 then 选择项1

　　　　when 列名=条件值2 then 选项2.......

　　　　else 默认值 end"

·经常会结合聚合函数（sum)一起来计算人数，金额····

例如： select group, sum( case when ``` then 1 else 0 end)  as  ' 消费人数' 

·还有用于行转列的操作：

例如将图1转换为图2

select name as '姓名'

,max(case course when '语文' then score else 0 end) as '语文'

,max(case course when '数学' then score else 0 end) as '数学'

,max(case course when '英语' then score else 0 end) as '英语'

from test.course_score group by name;

---

刷题的过程中还会发现很多常见解题套路，梦回高中做数学题 _(:з」∠)_ 

到时候再来总结