数据类型、数据查询和常用函数

---

一、数据类型

1.1 概念

在表的创建阶段，对表每一列设置数据类型，用于在数据添加时进行简单的约束，不同的类型存储方式不同，在进行查询时对于查询的效率也不同，所以需要在创建表时，根据存储的数据对列设置不同的数据类型。

在使用保存数据时，保存整数使用int，小数使用double，字符串使用varchar，日期类型使用datetime

1.2 分类

1.2.1 整型(int)

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用,所以可以省略

1.2.2 浮点型(float、double)

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

1.2.3 定点型(decimal)

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

1.2.4 字符串型(char、varchar、text)

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

• char 和 varchar：

1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3. char类型的字符串检索速度要比varchar类型的快。

• varchar 和 text ：

1. varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2. text类型不能有默认值。
3. varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

1.2.5 二进制数据(blob)

1. BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2. BLOB存储的数据只能整体读出。
3. TEXT可以指定字符集，_BLOB不用指定字符集。

1.2.6 日期时间型(date、time、daatime、timestamp)

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

若定义一个字段为 timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间

二、数据查询

2.1 查询SQL的执行顺序

select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

1. from
2. join
3. on
4. where
5. group by（开始使用select中的别名，后面的语句中都可以使用）
6. avg,sum… （聚合函数）
7. having
8. select
9. distinct
10. order by
11. limit

2.2 别名书写

通过使用 SQL，可以为表名称或列名称指定别名。在检索过程中，使用as关键字(或者用空格将列名/表名与别名隔开)创建别名，使表名或列名更具可读性。一个别名只存在于查询期间。

• 表别名

当指定了表别名之后，必须使用"表别名.列名"来查询列。
表的别名一般用单个英文单词并且与原表名相似，明白其命名的含义。

#使用as关键字为表名创建别名
select 表别名.列名 from 表名 as 表别名;
#用空格将表名与别名隔开
select 表别名.列名 from 表名 表别名;

• 列别名

列别名可以自定义查询数据返回的字段名。
当进行多表查询，导致返回的字段名称相同时，可以通过定义别名来避免冲突
列的别名一般为中文名称，用双引号 或 单引号 或 as 或 空格 来创建。

# 使用双引号为列名创建别名
select 列名 "列别名" from 表名;
# 使用单引号为列名创建别名
select 列名 '列别名' from 表名;
#用空格将列名与别名隔开
select 列名 列别名 from 表名;
#使用as关键字为列名创建别名
select 列名 as "列别名" from 表名;

2.3 基本查询语法

• 查询所有数据

select * from 表名 where 条件

• 查询指定列

select 列名1，列名2 from 表名 where 条件

2.4 分页查询 limit

对已查询的结果进行二次分割，返回数据

在查询结束后，因为返回数据过多，所以需要进行分页查询

select * from 表名 limit 起始索引,截取长度;

• 查询顺序

1. from emp
2. select 列
3. limit

2.5 排序操作 order by

order by取一个或多个列名，对输出进行排序。
如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

2.5.1书写位置

1. 在给出order by子句时，应该保证它位于from子句之后
2. 如果使用 limit，它必须位于order by之后。
3. 必须是SELECT语句中的最后一条子句。

2.5.2 执行顺序

1. 执行 from
2. 执行 where
3. 执行 select
4. 执行 order by

2.5.3 语法

asc是默认的排序顺序—升序(从A到Z)。
desc设置降序排序(从Z到A)。

# 默认升序   []表示可省略
select 列名1，列名2 from 表名 order by 列名1，列名2 [asc]
#desc降序
select 列名1，列名2 from 表名 order by 列名1，列名2 desc

字母大小写和排序顺序：对于文本数据进行排序的时候，字母大小写的排序顺序完全取决于数据库是如何设置的。MySQL 默认大小写字母相同。

2.6 分组 group by

把数据分为多个逻辑组，以便能对每个组进行聚集计算。

2.6.1 语法

select 列名1，列名2 from 表名 group by 列名1

2.6.2 with rollup关键字

使用 with rollup 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值

select 列名1，列名2 from 表名 group by 列名1 with rollup

2.6.3 使用规则

1. group by子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
3. group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数） 。如果在select中使用表达式，则必须在 group by 子句中指定相同的表达式。不能使用别名。
4. 除聚集计算语句外，select语句中的每个列都必须在group by子句中给出。
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
6. group by子句必须出现在where子句之后 （如果有 where 子句的话 ），order by子句之前。

2.6.4 过滤分组 having

having是对分组进行操作的，书写在group by之后
最重要的是 ： having 语句中可以使用函数

select 列名1,列名2  from 表名 group by 列名1 having 列名2 > 25;

• 执行顺序

1. from emp
2. group by
3. having

• HAVING和WHERE的差别

where在数据分组前进行过滤，having在数据分组后进行过滤。
where 后不可以跟聚合函数，having可以进行聚合函数的判断。
where和having唯一的差别是where过滤行，而having过滤分组。
where 和 having 可以同时使用

#年龄大于25岁的雇员进行分组，每个性别 多于 2人的数据
select  sex , max(age) from  emp  where age > 25 group by sex having count(*) > 2

2.6.5 分组与排序的区别

虽然group by和order by经常完成相同的工作，但它们是非常不同的。见下表

order by	group by
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

一般在使用group by子句时，应该也给出order by子句。这是保证数据正确排序的唯一方法。千万不要仅依赖group by排序数据。

2.7 去重 distinct

distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤，只返回其中的一条数据给用户，distinct只可以在select中使用 。

• 对单个或多个字段进行去重操作

select distinct 列名1,列名2 from 表名;

• 对所有字段进行去重操作

select distinct * from 表名

• using 会自动去掉重复的字段

多个表合并，去掉相同的列

using(列名)

2.8 数据过滤 where

2.8.1 语法

select 列名1,列名2 from 表名 where 条件语句

• 查询顺序

1. from emp
2. where 条件约束
3. select 列名

where 后面的语句都是 布尔表达式，即符合条件的数据留下，不符合条件的数据过滤掉

2.8.2 运算操作符

符号	描述	备注
and 或 &&	且	没添加一个过滤条件，都添加一个and
or 或 | |	或	检索匹配任一给定条件
=	等于
<> 或 !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
between	在两值之间	>= 最小值 && <=最大值
not between	不在两值之间
in	在集合中
not in	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
like	模糊匹配
is null	为空
is not null	不为空

2.8.2.1 between 范围判断

使用and 将数据范围标识 查找包含指定数据范围内的数据

select * from student where id between 4 and 12

2.8.2.2 not 取反

将已有操作符返回数据相反

select * from student where id  not between 4 and 12

2.8.2.3 in 集合判断

使用()书写数据集合，在结果中查找符合集合数据的数据

select * from 表名 where 列名 in(集合)

2.8.2.4 null值判断

在数据库中不区分大小写，所以字母大写小写认为是相同的，并且在创建列时如果该列数据可以为null，那么在添加数据时无需设置数据会使用null这种特殊的数据进行填写，但是注意与null字符串还是存在本质的区别，在数据库中如果需要判断数据时未添加数据默认的null 那么必须使用is null 进行判断

2.8.2.5 like模糊查询

在进行列数据搜索时，有时只拥有数据的一部分，这个时候就需要使用like模糊查询，like通常与通配符一同使用，实现相应效果数据的查询

select 列名 from 表名 where 列名 like 部分数据

• %通配符

代表任意个任意字符

#查询包含字母l的对应数据
select * from student where studentname like '%l%'
#查询以字母l结尾的数据
select * from student where studentname like '%l'
#查询以字母l开头的数据
select * from student where studentname like 'l%'
#查询以字母j开头m结尾的数据
select * from student where studentname like 'j%m'

• _通配符

代表一个任意字符

#查询第二个字母为l并且长度为3的对应数据
select * from student where studentname like '_l_'
#查询以字母l结尾并且长度为2的数据
select * from student where studentname like '_l'
#查询以字母l开头并且长度为2的数据
select * from student where studentname like 'l_'
#查询以字母j开头m结尾的并且长度为4数据
select * from student where studentname like 'j__m'

三、常用函数

3.1 概念

由mysql数据库创建时内部定义的方法，我们可以直接通过方法名参数的形式直接调用，在查询结果返回相应的结果数据。

3.2 优点

• 为了简化操作，mysql提供了大量的函数给程序员使用（比如输入当前时间，可以调用now()函数）
• 函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中

3.3 分类

• 聚合函数
• 字符串函数
• 数值函数
• 日期函数

3.4 聚合函数

用于计算当前数据相应的信息，将信息进行整合，返回相应数据（一条）

3.4.1 avg平均数(AVG)

在select后书写，用于获取结果指定列的平均值

select avg(列名) as 平均分 from 表名

3.4.2 count数据条数(COUNT)

用于获取当前结果集中对应字段条数（关系型数据库取出数据每列条数相同，通常使用主键字段，如果数据为空可能导致不符，也可以使用*）

• 返回指定字段的数据的行数（记录的数量）
• 字段可以为"",为时代表所有记录数，与字段数不同的时，记录数包括某些字段为null的记录，而字段数不包括为null的记录。

select count(列名) as 条数 from 表名

3.4.3 max/min求最值(MAX、MIN)

用于获取当前列数据中最大/最小值

#最大值
select max(列名) as 最大值 from 表名
#最小值
select min(列名) as 最小值 from 表名

3.4.4 sum总和(SUM)

用于计算当前数据列所有数据之和（一般用于数值类型列的计算）

select sum(列名) as 总和 from 表名

3.5 字符串函数

3.5.1 concat字符串拼接

• 用于将多个字符串合并成一个字符串，如果传入的值中有null，那么最终结果是null

select concat(列名1,自定义字符串) from 表名

如果想要在多个字符串合并结果中将每个字符串都分隔一下，可以使用concat_ws(分隔符,str1,str2,str3…),如果传入的分隔符为null，那么最终结果是null（不过这时候如果str有为null不影响结果）

select concat_ws(分隔符,列名1,concat(列名2,自定义字符串)) from 表名

3.5.2 length字节数

用于获取字符串字节长度(返回字节数，因此要注意字符集）

select length(列名1) from 表名

3.5.3 char_length字符数

用于获取字符长度

select char_length(列名1) from 表名

3.5.4 字母大小写转换

大写：upper(x),ucase(x)；

select upper('a');		---: A
select upper('abc'); 	---: ABC

小写：lower(x),lcase(x)

select lower('A');		---: a
select lower('Jack,你好'); 	---: jack,你好

3.5.5 字符串查找

• locate 查找字符串

查找指定字符串第一次出现的位置（从1开始没有返回0）

select locate(指定字符串,列名1) from 表名

• position 定位字符串

查找指定字符串第一次出现的位置（从1开始没有返回0）

select position(指定字符串 in 列名1) from 表名

• instr 查找字符串

查找指定字符串第一次出现的位置（从1开始没有返回0）

select instr(列名1,指定字符串) from 表名

3.5.6 字符串截取

• left 截取字符串左侧开始

select left(列名,2) from 表名

• right截取字符串右侧开始

select right(列名,3) from 表名

• substring截取字符串指定索引开始

select substring(列名,2,3) from 表名

3.5.7 字符串去空函数

• ltrim(str) 去除字符串str左边的空格

select ltrim('  hello jack'); ----: hello jack

• rtrim(str)去除字符串str右边的空格

select rtrim('  hello jack  '); ----:   hello jack

• trim(str)去除字符串str两边的空格

select trim('  hello jack  '); ----: hello jack

3.5.8 字符串替换函数

• insert(str1,index,len,str2)使用str2从str1的index位置替换str1的len个元素

#将指定字符串第7为开始长度为5替换为amy
select insert('hello world' , 7 , 5 , 'amy'); ---: hello amy

• replace(str,str1,str2) 将str中的子串str1全部替换成str2

#将123 admin 456admin中 admin替换为jack
select replace('123 admin 456admin' , 'admin' , 'jack'); ---: 123 jack 456jack

3.6 数值函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期—时间处理函数的使用那么频繁。但在主要DBMS的函数中，数值函数是最一致最统一的函数。

3.6.1 绝对值函数：abs(x)

返回x的绝对值

select abs(-3.1415927); ---: 3.1415927

3.6.2 向上取整函数：ceil(x)

返回x的向上取整的整数

select ceil(3.14); ---: 4

3.6.3 向下取整函数:floor(x)

返回x的向下取整的整数

select floor(3.14); ---: 3

3.6.4 随机数函数：rand()

返回0-1内的随机数

如果想对某种情况都使用同一随机值，可以使用rand(x),x相同时返回同样的随机结果

select rand(); ---: 0.15522042769493574

3.7 日期函数

3.7.1 获取日期相关函数

select year(  now() );
select month( now() );
select day(   now() );
select Hour(  now() );
select Minute(now() );
select Second(now() );
select weekday( curdate() ); ---: 2  从0 开始计算

3.7.2 日期格式化函数

Date_Format( date , format )

date 参数是合法的日期。format 规定日期/时间的输出格式

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

select DATE_FORMAT(now() , '%Y-%m-%d %H:%i:%S'); ---: 2019-08-21 09:06:18

---

每日一点点进步
不进则退