mysql 笔记

0 序

　　本篇文章旨在总结MySQL基础查询知识，加深知识的记忆，同时查缺补漏和做好记录以备日后回顾。

1 数据库基础

　　数据库是一个以某种有组织的方式储存的数据集合，专门储存数据的地方。数据将以某种逻辑储存在数据库中，但是我们并不直接访问数据库，而是通过DBMS（数据库管理系统）来进行访问，其中MySQL就是DBMS。

　　数据库以多个不同表来储存数据。同个数据库中每个表名称不一，储存这某种特定类型的数据。表由行和列组成，列表示一个字段（属性），行表示一条完整的记录（实例），一条记录将由一个或者多个字段值组成。

　　标识表中的一行记录需要一个唯一主键，用于区别“记录”。一个表中只能拥有一个主键，主键由表中一列或者多列组合来表示。（注意：每行必须有主键，并且任意两行主键值必不相同，当主键由多列组成，只需每行多列组成的主键不同；主键不能为NULL）

　　SQL是一种查询语言，专门用来与数据库通信。每个DBMS都会拥有自己的SQL，但是它们无法相互通用，存在一些区别。

2 检索数据

 　　检索数据用select语句，基本的关键字有select和from。

　　如：select * from table

　　select后面接的是列名或者*（*代表所有列），其中返回多列则将列名依次列出并用逗号相隔，返回的表也将是按照列名顺序依次组合。

　　注意：除非需要所有的列，否则一般不要使用*通配符，使用*通配符会降低检索和应用程序的性能

　　select后面是将整列检索出来，当我们只需要该列其中一些行时，需要增加过滤条件where

3 关键字用法

distinct

　　select distinct column1， column2 from table

　　distinct：返回不重复的行，有多个一样的返回第一个，上面例子将返回列1、列2组合后不重复的值

where

　　select distinct column1， column2 from table where column1 = “XX”

　　where：where子句指定过滤条件

　　where子句操作符（= ，<> ，！=，<,>,<=,>=,between,is,in) 不存在操作符或者是其他操作符，一般将会是错误的语法

between...and...

　　select distinct column1， column2 from table where column1 between a and b

　　过滤a，b这个区间的值，包括等于a和b的值

in

　　select distinct column1， column2 from table where column1 in（a，b）

　　括号里的是集合，将过滤出仅等于a或者b的值

is null

　　select distinct column1， column2 from table where column1 is null

　　过滤出null值，null不是空字符串

like

　　select distinct column1， column2 from table where column1 like“X%”

　　过滤出以X开头的字符串，“%”代表0个或者多个字符，“_”代表一个字符且必须有一个字符

　　注意：能用操作符得到结果就尽量不用通配符（“%”，“_”），通配符会降低检索性能

order by

　　select distinct column1， column2 from table where order by column1 desc

　　将查询出的数据进行排序，默认升序（asc），降序用desc，顺序按acsll码的规定

limit

　　select distinct column1， column2 from table limit a,b

　　limit返回第一行或者前几行。当有两个参数时，a表示从第a行开始（不含第a行），返回后面的b行；当只有一个参数时，表示从第一行（包含第一行）开始返回多少行

　　limit 1 offset 1 表示返回1行数据，并从第2行开始返回

group by ...having

　　select distinct column1， column2 from table group by column1 having column > 0

　　group by 将数据按某个特征分组，即column1中相同的值将分成同一组，增加过滤条件用having，它的作用与where一样，having是以整组来过滤的

union

　　将多条select语句返回值组合，各语句列名需一致；默认返回不重复的行（需返回重复行使用union all），且只能按一种方式排序

format

　　格式化小数位数format（1.23242，2）保留小数点后两位

3 MySQL正则表达式

 　　正则表达式是用来匹配文本的特殊的字符集合，关键词为regexp。

　　select distinct column1， column2 from table where column1 regexp “XX”

　　例子表示找到column1中拥有“XX”的文本值，不论“XX”是在文本开头、结尾或中间，只要存在“XX”就返回。

or 匹配

　　select distinct column1， column2 from table where column1 regexp "a|b"

　　“|” 等同于 or，并且可以存在多个“|”，如 a|b|c

.

　　select distinct column1， column2 from table where column1 regexp ".XX"

　　"."表示任意一个字符

匹配多个字符中的一个

　　select distinct column1， column2 from table where column1 regexp "[253]XX"

　　匹配2XX、5XX、3XX，相互之间为or关系，[]表示or

　　注意：[^253]表示除它们外的任何东西

　　[0-9]表示0-9任一数字，[a-z]同理

匹配特殊字符

　　用"\\"来进行转义

匹配字符类

　　匹配一类字符，如纯字母、数字、符号等

　　[:alnum:] 代表任意字母和数字

　　[:alpha:] 代表任意字符（字母）

　　[:blank:] 代表空格和制表

　　[:digit:] 代表任意数字

　　[:lower:] 代表任意小写字母（upper大写）

　　[:space:] 代表任意空白字符（空格、制表符、分页符等等）

匹配多个字符

　　”*“ 0个或多个匹配

　　”+“ 1个或多个匹配

　　”？“ 0个或1个匹配

　　｛n｝ 指定数目匹配

　　｛n，m｝ 指定匹配数目范围

定位符

　　”^“ 文本的开始

　　”$“文本的结尾

　　select distinct column1， column2 from table where column1 regexp '^[0-9\\.]'

　　匹配小数 

　　注意：”^“在[]内表示否定，如[^123]表示不是1、2或者3的任意值

3 计算字段

　　通常我们需要的数据分散在多个列中格式无法满足，此时则需要将其计算或拼接得到合适的格式

拼接字段

　　select concat（ column1，rtrim（ column2 ））from table

　　concat（）将两个列名拼接至一起，输出column1column2作为新列，可以将其用as进行别名

　　rtrim()函数将将值右边的空格去掉，ltrim()去掉左边，trim()去掉左右两边

算术计算

　　select column1*column2 as product_column from table

　　可以将字段进行算术计算得到目标值

4 数据处理函数

　　数据处理常包含文本串处理、数值算术计算、日期时间处理等，下面是一些常用的函数

文本处理函数

　　left(字段，长度a) 返回字段左边长度a的字符  right()返回右边字符 

　　length() 返回长度

　　locate(aba，a，起始位置) 找出串的一个子串（找出a在aba第一次出现的位置，若有第三个参数则代表检索的起始位置）

　　lower() upper()　字母大小写转换

　　trim() 去掉空格

数值处理函数

　　abs()返回绝对值

　　mod(a,2)返回a除以2的余数

　　sqrt()返回平方根

　　pi()返回圆周率

　　rand()返回一个随机数

日期和时间处理函数

　　adddate() 同下方date_add()

　　addtime() 增加一个时间

　　curtime()返回当前时间

　　curdate()返回当前日期

　　date()返回日期部分

　　datediff(日期1，日期2) 返回两日期之间的天数

　　date_format(date，y%-m%-d%) 指定为不同格式

　　day()返回天数

　　dayofweek()返回该日期对应星期几

　　hour()

　　minute()

　　month()

　　now()返回当前日期时间

　　second()

　　time()

　　year()

　　date_add(date,interval,2,day) date 增加2day

5 聚集函数

　　avg() 忽略null值

　　count() count(*)对行数计数；count(column)对列的值计数，忽略null

　　max()返回数值或日期文本的最大值 

　　min()

　　sum()

6 联结

　　在联结是实际是各表相互之间每行对应匹配，所以需要where进行过滤

自联结

　　select * from score a where degree < (select avg(degree) from score b where b.cno=a.cno)

　　自己与自己联结

　　父子表联结，数据库引擎逐条从父查询中取记录与子查询比较进行过滤

自然联结

　　指定列进行联结

外部联结

　　left\right outer join

　　以两表的其中一表某个列为基准，另一个表联结过来，不存在等值的是null

　　注意：聚集函数计算的是整个列，所以常与group by 一起使用，用以计算各组的聚集