MySQL-1

前言

对MySQL概念常用命令的总结:
数据库和SQL的介绍
MySQL
检索
排序
过滤数据
创建计算字段
函数
汇总数据

数据库和SQL的介绍

数据库是保存有组织的数据的容器,通常是一个或一组文件。数据库软件称为DBMS(数据库管理系统),用户并不访问数据库,而是通过DBMS对数据库进行操作。

数据库中的文件称为表,是某种特定类型数据的结构化清单。数据库中表的名字是唯一的。数据库和表的布局及特性称为模式。表由列组成,列也称为字段。每个列都需要确定数据类型。表中的数据是按行存储的,也称为一个记录。

表中可以设定每一个记录的唯一标识——主键,其可以是一列或是一组列。主键不可为NULL,且不去更改。

SQL是一种结构化查询语言,是一种用来与数据库通信的机器语言,多数DBMS都支持SQL。

MySQL

MySQL是一种DBMS,是开源的。

DBMS多数是客户机——服务器模式工作的。数据库服务器上的软件(DBMS)是真正与数据文件打交道的,客户机是与用户打交道的软件,客户不能直接访问数据文件,只能通过客户机软件用SQL语言提出请求,通过网络发送给DBMS,由DBMS根据请求去操作数据。

服务器软件为MySQL DBMS。
客户机软件可以是MySQL命令行、脚本语言、C++、JAVA等。

客户机是必不可少的,没有它就无法与DBMS发出请求。MySQL有三种常用客户机工具:

  1. mysql命令行。
    安装DBMS时自带的软件。采用命令 mysql -u 用户名 -p -h 服务器主机名 -P 端口号,登录mysql指定服务器端口进程的指定用户(获取权限)。注意服务器对应端口必须打开。命令行中exit退出登录。命令用;\g结束。可用help 指令查询用法。

  2. MySQL Administrator
    图形交互客户机,来简化MySQL服务器对用户的管理(新增用户、访问权限),查看系统状态。

  3. MySQL Query Browser
    图形交互客户机,用来编写和执行MySQL命令,并查看数据结果。

使用MySQL前必须登录以获得各种权限。 登录之后用

  • USE 数据库名称;选择数据库。
  • SHOW DATABASES;返回可用数据库列表,包括系统自带的。
  • SHOW TABLES;获得打开的数据库内表的列表。
  • SHOW COLUMNS FROM 表名;DESCRIBE 表名返回表的所有字段的信息。

检索

SELECT关键字负责检索。

  • SELECT 列名 FROM 表名;检索单个列所有行
  • SELECT 列名1,列名2 FROM 表名;检索多个列
  • SELECT * FROM 表名;检索所有列
  • SELECT DISTINCT 列名 FROM 表名;检索出指定列不同的行
  • SELECT 列名 FROM 表名 LIMIT num;只显示前num行
  • SELECT 列名 FROM 表名 LIMIT startnum,num;显示从startnum行开始下面的前num行(第一行为行0)

SQL语句不区分大小写,包括关键字和参数名。但是习惯将关键字用大写,列和表名用小写。SQL语句所有空格会被忽略。多条SQL语句用;分隔。
完全限定名,指定数据库和表中的一列:数据库名.表名.列名

排序

  • SELECT 列名 FROM 表名 ORDER BY 列名根据指定列名排序检索出的数据行。

列名可给出多个,逗号分隔,按照给出的顺序依次排序。

  • SELECT 列名 FROM 表名 ORDER BY 列名1 DESC,列名2 LIMIT 5;列名后给出DESC关键字指明降序排序。LIMIT关键字必须在ORDER BY 之后。

过滤数据

1
2
3
4
5
SELECT 列名 
FROM 表名
WHERE 过滤条件
ORDER BY 列名
LIMIT num;

根据指定条件检索出的数据行。字符串要用单引号括起来。<>表示不等于,BETWEEN A AND B表示在范围内,包括端点的值。WHERE 列名 IS NULL检索出值为NULL的行。

逻辑操作符

AND逻辑操作符表示过滤出左右两边都满足条件的行,OR检索出匹配任一条件的行,IN (匹配项)只要在匹配值清单中的行都会被检索出来。NOT否定之后跟的任何条件,可以跟IN,BETWEEN,LIKE,REGEXP等条件。多个条件存在时有计算次序:NOT>AND>OR,最好用括号消除歧义。

LIKE和通配符

使用LIKE和通配符进行过滤。有通配符就必须用LIKE。通配符一般用于字符串的查找匹配,因此LIKE后往往需要单引号。%通配符表示任意多的任意字符(NULL除外),_匹配单个任意字符。LIKE相当于=,必须完全匹配。在不使用通配符情况下就没必要使用LIKE

正则表达式

WHERE 列名 REGEXP '正则表达式'
WHERE子句中用正则表达式进行匹配文本字符串,表示只要部分匹配表达式的文本都会被检索。REGEXP BINARY表示区分大小写。
正则表达式中:
.匹配任意一个字符,'字串1|字串2|字串3'表示匹配任意一个。
'[123]'表示匹配任意一个,'[a-z]'表示匹配范围中的任意一个,'[^1-9]'表示匹配范围之外的任意一个。
\\表示转义字符,能让特殊字符失去意义参与匹配。
预定义的匹配字符集。
重复元字符:*:0个或多个,+:一个或多个,?:0个或一个,{n}:指定数目重复,{n,}n个以上,{n,m}范围内匹配。
定位字符元:^文本开始,$文本结尾。
SELECT 字符串 REGEXP '正则表达式'来测试正则表达式。返回0、1看是否匹配。

创建计算字段

数据库检索出来的数据经服务器进一步计算之后再返回给客户端。

拼接字段

SELECT Concat(列名1,'(',列名2')') AS 别名 FROM 表名把同一行的给定不同字段的数据字符串连接起来形成一个长串,并把返回的计算列命名为一个别名。别名不影响原始数据库中的列名。可以用Rtrim(列名)去除值右边的所有空格Ltrim去除左边,Trim(列名)去除左右所有空格。

计算字段

可以使用+-/的计算字段。SELECT语句可以测试计算字段,如:`SELECT 列名num`。

函数

文本处理函数

  • Left(列名,长度):返回串左边的字符
  • Right(列名,长度):右边
  • Length(列名):返回串的长度
  • Locate(‘字串’,列名):找出字串,返回字串开始位置
  • Lower():转为小写
  • Upper():大写
  • RTrim():去空格
  • LTrim()
  • Trim()
  • Soundex():发音相近的值匹配

日期和时间处理函数

日期(date):’yyy-mm-dd’,
日期时间(datetime):’yyy-mm-dd hh:mm:ss’,
时间(time):’hh:mm:ss’,
是三种新的数据格式,不同于字符型或是数值型。
DATE()返回日期(date格式,注意单引号),Day()返回天数,Month()返回月份,Year()返回年份,上述三个返回的都是数值型。Now()返回日期时间(datetime),日期可用BETWEEN 日期一 AND 日期二 进行过滤。也可只用年份,月份过滤。

数值处理函数

Abs()绝对值
Cos()三角函数
Mod()取余
Pi()返回圆周率
Rand()返回一个随机数

汇总数据

聚集函数,把一列的所有行计算返回一个值。

  1. AVG()
    求特定列的品均值,只能用于数值列,可用WHERE子句过滤出特定的行再平均。忽略NULL行。
  2. COUNT()
    COUNT(*)对表中所有行计数,包括有的列是NULL的行。COUNT(列名)只对特定列不为NULL的行计数。同样可用WHERE子句过滤。
  3. MAX() MIN()
    返回给定列的最大、最小值,忽略NULL,可对数值、字符、日期列操作。可用WHERE。
  4. SUM()
    返回指定列的和,必须对数值列,忽略NULL行,可用WHERE。

聚集函数括号内列名前DISTINCT关键字可指定只对不重复的行值运算。一般用于SUM和AVG和COUNT,不能用于COUNT(*)。

数据分组

聚集函数只返回一个值,希望对特定字段的记录进行聚集计算汇总出各个分组的结果。
GROUP BY 列名按指定列排序并分组数据,相同的字段值在一组,可以对每个组而不是对整个结果进行聚集。GROUP BY可以提供多个组合列作为分组依据,但不能是聚集函数,所有NULL值被分到一组内,GROUP BYWHERE之后ORDER BY之前。另外,SELECT语句中的每个列(聚集函数除外)都必须在ORDER BY 子句中给出,否则每个记录就对应不上。
HAVING过滤分组,是对分组之后计算结果的过滤,通常过滤条件是聚集函数(多是SELECT后指定的)加上逻辑判断。WHERE是行过滤,是在分组前的过滤,HAVING也能进行行过滤。`GROUP BY分组之后虽然能进行排序,但还是应该明确ORDER BY来指定排序规则,排序只能根据分组的列名或者是相应聚集函数

SELECT 子句顺序

SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT。

您的支持是我创造源源不断地动力