[大数据]3万字mysql一篇过（有图，有案例）

sql语句分类

DDL 数据定义语句 create 表库
DML 数据操作语句 增加insert 修改 update 删除 delete
DQL 查询语句 select
DCL 数据控制语句 管理数据库 grand revoke

Mysql的三层数据结构

DBMS数据库管理系统
数据库DB1 data文件下
表1 data中的 db.opt user.frm users.ibd

增删简单案例

创建数据库

1、character set:指定数据库采用的字符集 如果不指定字符集 默认时utf-8
2、collate:指定数据库字符集的校验规则 utf-8_bin 区分大小写
默认都是utf-8 general-ci 不区分大小写 注意默认utf-8_general_ci 区
分大小写

创建一个名称为db01的数据库

创建一个使用utf8字符集db02的数据库

创建一个使用utf8字符集 并带校对规则db03 数据库

*的表示含义

*代表所有的字段
from 是从哪个表

查看删除数据库

显示数据库语句
show databases；

显示数据库创建语句

show create database db_name;

删除数据库语句

drop database if exists db_name

为了规避关键字 我们用反引号 解决

create table_name table_name;

备份恢复数据库

mysqldump -u 用户名 -p -B 数据库01 数据库2 数据库4 > 文件名.sql

恢复数据库

Source 文件名.sql

创建表

create table table_name(
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校队规则 engine 引擎
field 指定列名 datatype 指定列类型 字段类型
character set 如不指定则为所在数据库字符集
collate 如不指定则为所在数据库校对规则
engine

创建指令集
id 整形
name 字符串
password 字符串
birthday 日期

mysql列类型就是mysql数据类型

mysql常用数据类型
1、说明使用规范能够满足需求的情况下 尽量选择占用空间小的类型

数据类型bit 的使用

1、基本使用
create table
只有 0，1位

数值型小数的基本使用

1、 FLOAT/double[unsigned]

Float 单精度 double 双精度

2、deimal【M，D】

可以支持更加精确的小数 M是小数位数的总数 D是小数点后面的标数

添加数据：

decimal 位数不够继续补
可以放置很大的数

字符串的基本使用

char（size） 0-255 字符
固定长度的字符串 最大255 字符
varchar（size） 0~65535） 65535 -23字节 用来节字 utf-8 /3 21844 字符 使用gbk 65532/2
可变长度字符串 最大65532 字节 【utf-8 编码最大218844字符 1-3】
字节可变用户记录大小

最多使用 255个字符
失败案例

成功案例

新版本 对数据进行无限制扩大

字符串使用的细节

1、char（4）代表的是字符 不是字节 不是中文还是英文字母都是放四个 按字符来算
varchar（4） 表示字符数 不管是字母还是汉字都是以定义好的表的编码来存放数据

当四个字符使用加入5个字符时 会在 4个字符时进行截取

varchar 也会自动截取

2、 char（4） 是定长的 就是插入少于 4个也会分配 4个字符的长度空概念
varcha人（4） 是变长 如果分配的不是4个字符 这就会 随机进行空间的分配
如果使用老版本的则使用 则varchar 一般使用

3、 什么时候使用char 什么时候使用varchar
如果数据是定长 这会使用char

4、 在存放文本的时候也可以使用text数据类型 可以将text列视为varchar 列 注意text 不能有默认值 大小 2-2^16;
如果哦不够用可以考虑使用mediumtext 2^24 或者longtext 2^32;

日期类型的基本使用

stamp 时间戳的意思
timestamp 在insert和update时 自动更新
创建一张表
date datetime timestamp

例题、创建一个表使用适当的数据类型从创建一个员工表 emp

id整形 name 字符型 sex 字符型 birthday 日期型 （date） entry_time 日期型（date） job字符型

修改***敲黑板 （重点）

1、使用alter table 语句进行追加修改 或者删除列操作
2、rename
3、傻瓜式修改 图形化界面

修改上·emp·中的操作

、1、员工表emp的上增加一个image 列varchar 类型 要求在resume后面
alter table emp add image varchar(32) not null default ’ ’ after resume;

desc emp 显示 表结构 可以查看表的所有列


2、修改job列 使其长度为60
alter table emp modify job varchar(60) not null default ’ ';

3、alter table emp drop sex;

4、表名改为employee
rename table emp to employee;

5、修改表的字符集为utf8
alter table employee character set utf8;

6、列表名name 修改为 user_name
alter table employee change name user_name ` varchar(32) not null default ’ ';

插入 数据 insert

细节注意：

1、插入的数据与字段的数据类型相同

insert into goods (id,goods_name,price) values(‘abc’,‘小米手机’，2000);

2、数据的长度应该在列出的规定范围之内 不能将一个长度为80的字符串加入到长度为40的列中

3、在values中列出的数据位置必须与被加入的列的排列位置相对应
4、字符和日期数据应包含在单引号中
5、列可以插入空值 前提是字段允许为空 insert
6、insert、 into tab name values （）（）（） 形式添加多条记录
7、如果是给表中的所以肉的字段添加数据 可以不写前面的字段名称
8、默人值得使用 当不给某个字段值是 如果默认就会添加则就会报错

更改 update

使用细节

1、update语法可以用新值更新原有的表行中各列
2、set子句 只是要修改的哪些列要哪些值
3、如果有where 就更新 所在的哪一行数据 如果没有where 就更细所在行的数据
4、如果需要修改多个字段可以通过 set 字段1 =值1 ，字段2 =值2

delete语句

使用delete语句删除表中的数据
delete from tbl_name where where_definition

依旧使用goods表快速入门

distinct：独特的 作用去重

查询 select 敲黑边重点

创建表

查出的完全相同会 进行distinct 进行去重
select 【distinct】 *【col1，col2，col3】 from table

column 指定列 * 代表查询的列
from 查询的哪张表 distinct 显示结果 去掉重复的数据

查询指定的列

使用表达式对查询的列进行运算

select *【Colum】 expression,cloum expression from table name;

可以取别名
as

select 语句中进行比较运算
比较运算
1、> < <= !=
2、 between and
3、in set
4、like 模糊查询
not like
is null 判断是否为空
逻辑运算符
and 多个条件同时成立
or 多个条件 任意成立
not 不成立

查询学生是马云的同学

查询成绩大于90

查询总成绩大于 200的同学

查询数学大于60 id大于1的同学

查询语文大于数学成绩的

查询总分大于200分 并且数学成绩小于语文成绩姓‘马’的

order by 查询语句 排序 查询到的结果按照
order by 既可以是指定排序的列名 也可以指定排列的别名
2、asc 升序排列、 desc 降序排列
3、order by子句 应位于 select 语句的结尾

按照数学成绩进行升序排列

按照总成绩 进行升序排列

降序

按照
select * from student where name like ‘李%’ order by math;

count函数

统计班级里面的数据

统计班里数学大于30的同学

统计分数大于250的同学的人数

count（*） 和count列的区别
count返回满足所有条件记录的行数
count列 统计满足条件的某列有多个但是会排排除为null的条件




列为null的不统计

合计函数sum

sum函数返回满足where 条件的行和 一般使用在数值列

统计一个班的 总成绩
select sum

统计一个班的数学 英语 语文总成绩
select sum（math） as math_total_sore from student2;


求每个学生平均学习时间

Avg合计函数

数学的平均分

使用max 和min的使用

group by子句 对列进行分组

使用 group by 子句 对列进行分组

select col1，col2，col3 from table group by col
创建表

查看历史命令的方法

前期工作都已就绪 下面有我给大家解说

按照部分来进行分组查询
1、根据部门来进行分组查询 显示平均工资和最高工资

2、查找每个部门的平均工资 和最低工资

2、显示平均工资低于2000部门号的他的平均工资 （比较复杂但是可以逐个击破）
（1） 显示各个部门的平均工资和部门号
（2）在1的基础上 进行过滤 保留 avg(sal)<2000

先进性分组再进行过滤

使用having 子句对分组后的结果进行过滤

字符串函数

dual 可以作为默认的测试表 不用进行船舰可以直接进行测试 如下 可能会翻车 如果翻车 请看 数字函数

字符串的字符集 charset

字符串连接 将多列拼接在一块 concat 【string2 【，】】

字符串的某个字符在字符串中的位置

字符串的-ucase 转换成大写

字符串的lcase 转换成小写

字符串的left 从左边起取几个字符

字符串的length 字符串的长度

字符串的replace 将第几个字符串转换成 几个字符串

字符串的strcmp 逐字符串比较大小

字符串的substring（str，postition，[,length]） 从str的position开始比较

字符串的ltrim （string2） rtrimstring trim 除去前端空格或后端空格

数学相关函数

标题abs（） 绝对值

将十进制转换成二进制 bin(decimal number)

celling就近的整数

conv（number2，from base，to base）转换进制 由几进制转换成几进制

floor（number2）向下取整 取得比num2小的最大整数

保留小数位数 format

least 求最小值

求余 mod（numerator，denominator） 求余

rand（【seed】） 返回随机数 其范围为0《= v《=1.0 如果seed不变 所获得随机数也不变

时间日期相关

current_date() 当前日期

current_time() 当前时间

current_timestamp() 当前时间戳

date返回datetime的日期部分

先创建一个信息表

date_add(date,interval d_value,d_type) 在date2中加上日期或者时间（后加）

date_sub(date2,interval d_value d_type) 在date2上减去一个时间（后加）

datediff（date1，date2） 两个日期差 结果是天（后加）

timediff（date1，date2） 两个时间差 多少小时 多少分钟多少秒（后加）

now（） 当前时间

year|month|date（datetime） from_unixtime() 年月日

案例 显示所有的新闻信息 发布的日期只显示日期不显示时间

查询在20分钟内发布的新闻

加密和系统函数

user（） 查询用户

可以查看登录到MySQL的有哪些用户 以及登录的IP

database 数据库名称 查询当前是哪个数据库名称

md5（str） 为字符串算出一个md5 32的字符串用户密码加密

root密码是hsp md5加密 从数据库中存放的是加密后的密码

查看一下是多少位

用户表存放密码 是md5

但是如果想查到该用户的密码呢？

这样显然是查不到的
只能靠这样查
但是会有一种 消耗大毕竟32位 比较长

加密函数password（）

加密函数 mysql数据库的用户密码就是password函数加密

select * from mysql.user \G 从原文密码str 计算并返回密码字符串
mysql.user 表示 数据库表的权限

流程控制函数

查询emp表 如果comm是null 则显示 0.0

if（expr，expr2，expr3） 如果expr1 为 true 则返回expr2 否则 返回expr3
ifnull（expr1，expr2） 如果expr1 如果不为null 则返回expr1 则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end； 如果expr1 为true 则返回expr2，如果expr2为t 返回expr4，否则返回expr5

如果表是job则是clerk 则显示职员 如果是manager则表示经历 入宫时salesman 则显示销售人员 其他正常显示

mysql表查询增强

使用where 子句 查找某某时间段后入职的员工

如何使用like操作符
%：表示0到多个字符 _:表示单个字符
？如何显示首字符为s的员工姓名和工资

？如果显示第三个字符为大写rillen的所有的员工的姓名和工资

使用order by 子句按照工资从低到高的顺序 显示雇员的信息

降序的排序

分页查询

分页查询
1、按雇员的id号 升序去除 煤业显示3条数据 请分贝额显示第一页第二页第三页
2、基本语法 select …… limit start，row 表示start+1行开始取 去除row行 start 从0开始计算
从第几行开始截取 进行

从序列号为0 开始进行两个行的读取

分页查询公式

select * from emp order by empno limit 每页显示记录 第几页到第几页 每页显示记录数

增强group by的使用

（1）、显示每种岗位的雇员总数和平均工资

（2）、显示雇员的总数 以及获得补助的员工数

统计没有获得补助的雇员数
select count（*），count（if（comm is null，1，null）） from emp;

显示管理这的总人数

显示最大差额

应用 案例统计各个部门group by的平均工资 avg 并且是大于1000的having 并且按照平均工资从高到低进行排序 limit 0 ，2

如果select 语句中同时 包含多个 group by，having，order by limit，那么他们的语句顺序是 group by，having，order by，limit

应用案例

统计各个部分group by 的平均工资 avg 并且大于1000的having 并且按照平均工资 从高到低排序 order by 取出前两行记录

多表查询（多表笛卡尔集）★★★（评论功能）

显示雇员名字 雇员的工资 以及所在的部门的名字 来自 emp表
部门的名字来自 dept表

**笛卡尔积

默认： 当两张表查询时 规则
1、从第一张表中 取出一行 和第二张表 的每一行 进行组合
2、一共返回 的记录数时第一张表和第二张表相乘的总行数
返回的所所有的结果就是笛卡尔积

使用 当两张表中 相同的属性相同的时候才会进行取得

这种情况下会出错

找不到相关deptno所在的表 因为两个表都有deptno

如何解决

#显示各个员工的姓名 工资 以及其 工资级别

思路 姓名，工资来自emp 13
工资级别 salgrade 5
写sql的方法 先写一个简单 然后加入过滤条件

自连接 多表查询

显示公司员工和他的上级的名字
同表查询应当使用别名 alias

管理部门使用 之间使用mysql 需要进行关系的确认

自连接特点:
1、把同一张表当作两张表使用
2、需要给表名取别名 表名 表别名
3、列名不明确 可以指定列的别名 列名 as 列的别名

mysql 表子查询

子表每次都会生成一个 临时表

什么是子查询：
子查询 嵌入在其他sql 语句中的select语句 也叫嵌套查询
单行子查询：
查询是指只返回一行数据的子查询语句
多行子查询
是指返回多行数据的子查询 使用关键字in

eg 子查询演示 如果显示与smith同一部门的所有员工
1、先查询到smith 部门号 得到
2、把上面的select 语句当作一个子查询来使用
子查询语句

根据子查询语句找到所对应的语句

练习：
查找和部门10的工作相同的雇员 名字 岗位工资^ 但不含 10号部门自己的雇员

首先 先查找到 10 号部门有哪些雇员

mysql 表子查询 all操作符

多行子查询中使用all操作符

eg. 显示工资比部门30的所有员工的工资高的员工的姓名 工资和部门号

在多行子查询中 使用any 操作符

显示工资比部门30的其中任意一个员工的工资高的员工的姓名 工资和部门号

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

查询与smith 的部门和岗位完全相同的所有雇员并且不含smith本人

（1）先查询smith的部门和岗位

（2）上面查询当作子查询来使用 并且使用多列子查询的语法进行匹配

查找到和自己相同的语句 就是没有的意思

子查询练习

查找每个部门工资高于本部门平均的人的资料
这里要用到数据查询的小技巧 把一个子查询当作一个临时表使用

（1）先得到每个部门的 部门号和对应的平均工资

再将上面得到信息 当作子表来查询

查找每个部门工资最高的人的信息

（1）先找到部门工资最高的人

（2）再找出人的全部信息

查询每个部门的信息 包括部门名编号 地址 和人员的数量
（1） 部门名 编号 地址 来自dept 表

(2)、各个部门的人员数量 构建一个临时表