本文主要内容为MySQL的基础语句以及正则表达式等内容。
本文操作的数据库内容存在个人github:https://github.com/YuanGao-1/blog_demo.git
init_schema.sql为建表脚本
init_data.sql为测试数据
具体导入过程为:
create database (数据库名); use (数据库名) ; source init_schema.sql; //先建表 source init_data.sql; //导入测试数据
这些概念都是基本常识,总结下来可以浏览一下。
数据库(database)指保存有组织的数据的容器(通常是一个文件或一组文件)。
表(table)指某种特定类型数据的结构化清单。
表是一种结构化的文件,可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录,或者其他信息清单。表名具有唯一性,同一个数据库下,不应有两个相同名字的表。
表具有一些特性,这些特性定义了数据在表中如何存储,如可以存储什么样的数据,数据如何分解,各部分信息如何命名,等等。描述表的这组信息就是所谓的模式,模式可以用来描述数据库中特定的表以及整个数据库(和其中表的关系)。
模式(schema)指关于数据库和表的布局及特性的信息。
有些资料上的模式和数据库含义相同,需要注意
列(column)指表中的一个字段。所有表都是由一个或多个列组成的。
数据类型(datatype)指所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。
行(row)指表中的一个记录。
主键(primary key)指一列(或一组列),其值能够唯一区分表中每个行。可以着重理解key
。
表中的任何列都可以作为主键,只要它满足以下条件:
除MySQL强制实施的规则外,应该坚持的几个普遍认可的最好习惯为:
- 不更新主键列中的值;
- 不重用主键列的值;
- 不在主键列中使用可能会更改的值。(例如,如果使用一个名字作为主键以标识某个供应商,当该供应商合并和更改其名字时,必须更改这个主键。)
在CMD中键入mysql -uroot -p
进入mysql状态,接下来需要键入密码
mysql>
之后;;
或 \g
结束,换句话说,仅按 Enter
不执行命令;help
或 \h
获得帮助,也可以输入更多的文本获得特定命令的帮助(如,输入 help select
获得使用SELECT语句的帮助);quit
或 exit
退出命令行实用程序。MySQL中同样具有关键字。如:ues, show, SELECT等等(cmd中不区分大小写)
先举例一些基本的命令:
举例:
什么是自动增量? 某些表列需要唯一值。例如,订单编号、雇员ID或(如上面例子中所示的)顾客ID。在每个行添加到表中时,MySQL可以自动地为每个行分配下一个可用编号,不用在添加一行时手动分配唯一值(这样做必须记住最后一次使用的值)。这个功能就是所谓的自动增量。如果需要它,则必须在用CREATE语句创建表时把它作为表定义的组成部分。我们将在第21章中介绍CREATE语句。
此外,相关的show命令还有:
MySQL 5支持一个新的
INFORMATION_SCHEMA
命令,可用它来获得和过滤模式信息。
上述语句利用SELECT 语句从user
表中检索一个名为password
的列。所需的列名在SELECT关键字之后给出,FROM关键字指出从其中检索数据的表名。如上的一条简单SELECT语句将返回表中所有行。数据没有过滤(过滤将得出结果集的一个子集),也没有排序
SQL语句和大小写 请注意,SQL语句不区分大小写,因此SELECT与select是相同的。同样,写成Select也没有关系。许多SQL开发人员喜欢对所有SQL关键字使用大写,而对所有列和表名使用小写,这样做使代码更易于阅读和调试。不过,一定要认识到虽然SQL是不区分大小写的,但有些标识符(如数据库名、表名、列名)可能不同:在MySQL 4.1及之前的版本中,这些标识符默认是区分大小写的;在MySQL 4.1.1版本中,这些标识符默认是不区分大小写的。最佳方式是按照大小写的惯例,且使用时保持一致。MySQL8.0中不区分,但是可根据自己的情况进行调整。
唯一的不同是必须在SELECT关键字后给出多个列名,列名之间必须以逗号分隔。
SELECT * FROM 表名,为检索所有列。优点:能检索出名字未知的列。缺点:如果不是确定需要所有列的信息,使用通配符*讲会降低检索和应用程序的性能。
解决办法是使用DISTINCT关键字,顾名思义,此关键字指示MySQL只返回不同(唯一)的值。
实际上type中有多个重复的0,1,2行。
此语句使用SELECT语句检索单个列。LIMIT 5指示MySQL返回不多于5行。
其中,LIMIT
后若跟两个数字,如:5,5,则指示MySQL返回从行5开始的5行。第一个数为开始位置,第二个数为要检索的行数。
带一个值的LIMIT总是从第一行开始,给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。
值得注意的是:
此外,LIMIT还有一种表示方法: LIMIT 10,5等效于 LIMIT 5 OFFSET 10第一个数字为检索的行数,第二个数字为偏移量即开始的位置。
有一些情形需要完全限定名,例如:使用完全限定的名字来引用列(同时使用表名和列字)。表名也可以是完全限定的。(community是数据库,user为其中一个表,id为user中的一个列)
2.2部分检索出的数据并不是以纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响。因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序。关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
在这里新介绍一个概念,子句:SQL语句由子句构成,有些子句是必需的,而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句中的FROM子句。
为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字,据此对输出进行排序。请看下面的例子:
这条语句除了指示MySQL对 email
列以字母顺序排序数据的ORDER BY子句外,与前面的语句相同。也可用其他列进行排列之后显示email
字段。
如下所示,先对 type
字段进行排序。之后在 type
相同的行中,对 username
字段进行排序。若 type
均不同,则不会对 username
进行排序。
指定排序方向:数据排序不限于升序排序(从A到Z)。这只是默认的排序顺序,还可以使用ORDER BY子句以降序(从Z到A)顺序排序。为了进行降序排序,必须指定DESC
关键字。
注意:对哪个列进行降序排列,就需要在列后加 DESC
,ORDER BY默认是升序,关键字为 ASC
在对文本性的数据进行排序时,A与a相同吗?a位于B之前还是位于Z之后?这些问题不是理论问题,其答案取决于数据库如何设置。
在字典(dictionary)排序顺序中,A被视为与a相同,这是MySQL(和大多数数据库管理系统)的默认行为。但是,许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符,可能必须这样做)。
注意,使用的时候,LIMIT要放在ORDER BY之后,否则会报错。
2.3部分学习了如何用SELECT语句的ORDER BY子句对检索出的数据进行排序。这个子句必须是SELECT语句中的最后一条子句。
过滤数据,即指定数据的搜索条件,毕竟在实际应用中检索所有数据的情况是不多的。
在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名(FROM子句)之后给出。这个例子采用的是相等测试:它检查一个列是否具有指定的值,据此进行过滤。但是SQL允许做的事情不仅仅是相等测试。
在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
值得注意的是,
MySQL在执行匹配时默认不区分大小写,所以system
与SYSTEM
匹配。
为了检查某个范围的值,可使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同,因为它需要两个值,即范围的开始值和结束值。
例如,BETWEEN操作符可用来检索id
在11和22之间的所有用户名。
从这个例子中可以看到,在使用BETWEEN时,必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。
在创建表时,表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时,称其为包含空值NULL。
NULL 无值(no value),它与字段包含0、空字符串或仅仅包含空格不同
为了进行更强的过滤控制,MySQL允许给出多个WHERE子句。这些子句可以两种方式使用:以AND子句的方式或OR子句的方式使用。
操作符(operator) 用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符(logical operator)
接下来介绍几个操作符:
AND 用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行。
OR操作符与AND操作符不同,它指示MySQL检索匹配任一条件的行。
在这里值得注意的是SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符。优先级不同,需要记住。
举例如下:
这句命令的含义为:检索所有的,activation_code
不为空且id
小于22这种情况,以及type
=1这种情况的记录。
注意:在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
例:(和开发语言相同的习惯,不再赘述)
SELECT username,id,activation_code,type FROM user WHERE (activation_code IS NOT NULL AND id<22) OR type=1;
IN 操作符:圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。
其实,这句命令等效于
SELECT username,id,activation_code,type FROM user WHERE type=1 OR type =2 ;
为什么要使用IN操作符?其优点具体如下:
NOT操作符:WHERE子句中的NOT操作符有且只有一个功能,那就是否定它之后所跟的任何条件。
例:
检索的为 type
=0的记录。
前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个还是多个值,测试大于还是小于已知值,或者检查某个范围的值,共同点是过滤中使用的值都是已知的。但是,这种过滤方法并不是任何时候都好用。例如,怎样搜索产品名中包含文本anvil的所有产品?用简单的比较操作符肯定不行,必须使用通配符。利用通配符可创建比较特定数据的搜索模式。在这个例子中,如果你想找出名称包含anvil的所有产品,可构造一个通配符搜索模式,找出产品名中任何位置出现anvil的产品。
通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际是SQL的WHERE子句中有特殊含义的字符,SQL支持几种通配符。
为在搜索子句中使用通配符,必须使用LIKE操作符。LIKE指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
在搜索串中,%表示任何字符出现任意次数
举例如下:
搜索的为:username
开头是nowcoder
的所有记录。
根据MySQL的配置方式,搜索可以是区分大小写的。
通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。下面的例子使用两个通配符,它们位于模式的两端:%anvil%这样的话就可以找到符合本章开头的产品记录了。自然也可放在中间,不再赘述。
值得注意的是,除了一个或多个字符外,%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
尾空格可能会干扰通配符匹配。例如,在保存词**anvil **时, 如果它后面有一个或多个空格, 则子句WHEREprod_name LIKE '%anvil'将不会匹配它们,因为在最后的l
后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数(本系列第二篇博客将会介绍)去掉首尾空格。
此外,虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL的行。
另一个有用的通配符是下划线(_)。下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符。
如上所示,利用的是3个下划线通配符,匹配的为username
只有3个字符的记录。
使用通配符需要注意以下几点:
Java中已有正则表达式的相关描述,不再在本篇博客中解释。
如果你熟悉正则表达式,需要注意:MySQL仅支持多数正则表达式实现的一个很小的子集。本章介绍MySQL支持的大多数内容。
这里的用例可以看一下,与章节3相比,将LIKE
操作符换成了REGEXP
,表示将使用正则表达式。REGEXP
后跟的是正则表达式。
再看一个例子:
这里使用了一个特殊字符.
,含义是匹配任意一个字符。
其实,这里也可以用
LIKE
实现,区别在于如果LIKE
匹配的是整个串,而REGEXP
匹配的是子串。
MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXP BINARY 'JetPack .000'。
为搜索两个串之一(或者为这个串,或者为另一个串),使用 |
。和Java语言有些类似。
此外,OR匹配还有另一种描述:
使用中括号,[12]定义一组字符,它的意思是匹配1或2。其实是[1|2]的缩写。
使用中括号的好处在于,假定有两行命令:
SELECT username,id,activation_code,type FROM user WHERE type REGEXP '[12] ton';
SELECT username,id,activation_code,type FROM user WHERE type REGEXP '1|2 ton';
这两行命令结果是不同的,'1|2 ton'返回的是符合正则表达式1或2 ton的所有记录。
字符集合也可以被否定,即,它们将匹配除指定字符外的任何东西。为否定一个字符集,在集合的开始处放置一个即可。因此,尽管[123]匹配字符1、2或3,但中括号中若为** 123**却匹配除这些字符外的任何东西。
此外,还可进行简写,[123]可直接写为[1-3]
为了匹配特殊字符,必须用\\
为前导。\\`-表示查找
-,\\\\
.表示查找
.`。
\\也用来引用元字符(具有特殊含义的字符),如表9-1所列。
多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。
为方便工作,SQL预定义了一些字符集,成为字符类,可见下图。
接下来讲一下,匹配多个实例的情况下该怎么做?
试想,如果我们需要匹配满足正则表达式'stick'和'sticks'的实例,使用OR匹配确实可以做到,但是有更简单的方法。举原文中例子:
匹配多个实例,需要使用\\(匹配内容)
另外还有一个例子值得学习:
目前为止本博客以上的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,需要使用表9-4列出的定位符。
^的双重用途 ^有两种用法。在集合中(用[和]定义),用它来否定该集合,否则,用来指串的开始处。
举例:
检索以1结尾的记录。
LIKE和REGEXP的不同在于,LIKE匹配整个串而REGEXP匹配子串。利用定位符,通过用^开始每个表达式,用$结束每个表达式,可以使REGEXP的作用与LIKE一样。
字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子。
在上述每个例子中,存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
这就是计算字段发挥作用的所在了。与前面各章介绍过的列不同,计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
Concat()拼接串,即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串,各个串之间用逗号分隔。
从前面的输出中可以看到,SELECT语句拼接地址字段工作得很好。但此新计算列的名字是什么呢?实际上它没有名字,它只是一个值。如果仅在SQL查询工具中查看一下结果,这样没有什么不好。但是,一个未命名的列不能用于客户机应用中,因为客户机没有办法引用它。
为了解决这个问题,SQL支持列别名。别名(alias)是一个字段或值的替换名。别名用AS关键字赋予。请看下面的SELECT语句:
它指示SQL创建一个包含指定计算的名为vend_title的计算字段。从输出中可以看到,结果与以前的相同,但现在列名为vend_title,任何客户机应用都可以按名引用这个列,就像它是一个实际的表列一样。
这个就比较容易理解了,举例如下:
demo
列是执行计算id*from_id计算后的列。
本系列浏览量过低,MySQL基础部分将不再更新,后续可能更新进阶部分或进入框架阶段。
手机扫一扫
移动阅读更方便
你可能感兴趣的文章