1. 在使用用户名和密码登陆MySQL数据库之后,首先需要指定你将要操作的数据库
USE $数据库名称
2. 使用SHOW 命令可以查看数据库和表中的信息
SHOW DATABASES; #列出可用数据库名称列表 SHOW TABLES; # 列出当前选择数据库内可用表的列表
SHOW COLUMNS FROM $表名 #返回指定表中所有列字段的详细信息,DESCRIBE $表名,也可以实现相同的功能
SHOW STATUS; #用于显示广泛的服务器状态信息 SHOW CREATE DATABASE 和 SHOW CREATE TABLE, 分别用来显示创建特定数据库或表的MySQL语句 #没有实现,不知道是什么意思 SHOW GRANTS; #用于显示授权用户的安全权限 SHOW ERRORS 和 SHOW WARNINGS # 用来显示服务器错误或警告消息
可以用HLEP SHOW
,进一步查看允许的SHOW 语句
3. 使用SELECT语句检索数据
SELECT+想选择什么+FROM+从哪里选择
1> 从指定表中检索单个列:SELECT prod_name FROM products;#从products 表中检索一个名为prod_name的列
2> SELECT语句可以一次性检索多个列,多个列名之间用逗号分隔SELECT prod_name, prod_id, prod_price FROM products;
3> SELECT 还可以使用通配符*检索表中所有列:SELECT * FROM products;
SELECT语句会返回表中所有行,如果在检索语句中没有指明数据顺序,则返回的数据顺序没有特殊意义
4> SELECT可以使用DISTINCT关键字返回不同的行SELECT vend_id, prod_price FROM products; #返回所有行,有重复
SELECT DISTINCT vend_id FROM products; # 返回值不同的行
SELECT DISTINCT vend_id, prod_price FROM products; #DISTINCT 应用于随后的所有列,而不仅仅是紧跟的那一列
5> SELECT 语句可以用LIMIT字句 返回指定行号和指定数目的行SELECT prod_name FROM products LIMIT 5; #返回前五行
SELECT prod_name FROM products LIMIT 5,5; # 返回从行五开始的五行,第一个数为开始的行号,第二个数为检索的行数,MySQL中的行号是从0开始的
LIMIT字句还有另外一种代替语法,LIMIT 3, 4 #从行3开始的4行
,可以使用LIMIT 4 OFFSET 3
来实现
6> 完全限定的表名
迄今为止使用的SQL例子只通过列名引用列。也可以使用完全限定的名字来引用列(同时使用表名和列字)SELECT products.prod_name FROM products; # 使用完全限定的列名
SELECT products.prod_name FROM learning.products; # 表名也可以完全限定
4. 排序数据
1>排序检索数据
可以用ORDER BY 子句对SELECT检索的数据进行排序SELECT prod_name FROM products ORDER BY prod_name; #按照prod_name对数据进行排序,这里的顺序是默认的字母升序
通常,ORDER BY子句中使用的列将是为显示所选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的。
2>按多个列排序
为了按多个列排序,只要指定列名,列名之间用逗号分开即可SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
3>指定排序方向
在使用ORDER BY子句对检索结果进行排序时,如果不指定排序方向,那么默认的返回结果就是按照升序排列。为了进行降序排列,必须指定DESC关键字。
```SELECT prod_name,prod_price FROM products
ORDER BY prod_price DESC; #按价格以降序排序产品(最贵的排在最前面)
如果是对多个列进行排序,可以逐个对列指定顺序,DESC 关键字只应用到直接位于其前面的列名,如果想对多个列降序,必须对每个列使用DESC关键字,与DESC 相反的关键字是ASC(ASCENDING)SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC, prod_name; # 以降序排序产品(最贵的在最前面),然后再对产品名排序
使用ORDER BY 和LIMIT 字句的结合可以找出一个列中最高或者最低的值SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC LIMIT 1;#找出最昂贵物品的值
5. 过滤数据
1> 使用WHERE子句检查单个值
在SELECT语句中,数据可以根据WHERE子句中指定的搜索条件进行过滤。SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50; #这个例子采用了简单的相等测试:它检查一个列是否具有指定的值,据此进行过滤
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'; # MySQL在执行匹配时默认不区分大小写
单引号用来限定字符串,如果将值与串类型的列进行比较,则需要限定引号。用来与数值列进行比较的值不用引号SELECT prod_name, prod_price FROM products WHERE prod_price <10;
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003; #不匹配检查,检索不是由供应商1003制造的所有产品
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
在同时使用ORDER BY 和WHERE 子句时,ORDER BY 子句应该放在WHERE 子句之后
2> WHERE 子句的操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
3> 范围值检查
为了检索某个范围内的值,可使用BETWEEN AND 操作符。需要两个值,即范围的开始值和结束值。
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; #它检索价格在5美元和10美元之间的所有产品,BETWEEN匹配范围中所有的值,包括指定的开端指和结束值。
4> 空值检查
在创建表时,表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时,称其为包含空值NULL。
NULL 无值(no value),它与字段包含0、空字符串或仅仅包含空格不同。
SELECT prod_name FROM products WHERE prod_name IS NULL; # 这里返回没有价格的所有产品,没有这样的行,所以没有返回数据
SELECT cust_id FROM customers WHERE cust_email IS NULL;
5> 多个WHERE 子句组合
为了进行更强的过滤控制,MySQL允许给出多个WHERE子句。这些子句可以两种方式使用:以AND子句的方式或OR子句的方式使用。
AND 用在WHERE子句的关键字,用来检索满足所有给定条件的行。
SELECT prod_id,prod_price, prod_name FROM products WHERE vend_id =1003 AND prod_price <=10; #为了通过不止一个列进行过滤,可使用AND操作符给WHERE子句附加条件。此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。
OR 操作符指示MySQL检索匹配任一条件的行SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id =1003; #此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。
6> 计算次序
WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。。SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符。SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
返回的行未按预期的进行过滤。由于AND在计算次序中优先级更高,操作符被错误地组合了。此问题的解决方法是使用圆括号明确地分组相应的操作符。SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; # 任何时候使用具有AND 和OR 操作符的WHERE子句,都应该使用圆括号明确的分组操作符
7> IN 操作符
圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。功能与OR相当。
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name; #IN操作符后跟由逗号分隔的合法值清单,整个清单必须包括在圆括号中
8> NOT 操作符
WHERE 子句的NOT 操作符有且只有一个功能,那就是否定它之后所跟的任何条件SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
mysql 支持使用NOT 对IN,BETWEEN 和 EXISTS 子句取反,这与多数其他DBMS允许使用NOT 对各种条件取反有很大的差别。
6. 用通配符进行过滤
利用通配符可创建比较特定数据的搜索模式。如果你想找出名称包含anvil的所有产品,可构造一个通配符搜索模式,找出产品名中任何位置出现anvil的产品。通配符(wildcard)是用来匹配值的一部分的特殊字符
搜索模式是由字面值、通配符或两者组合构成的搜索条件。为在搜索子句中使用通配符,必须使用LIKE操作符
LIKE 操作符指示MySQL后跟的搜索模式利用通配符而不是直接相等匹配进行比较。
1> 百分号%通配符
%表示任何字符出现任意次数SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; #找出任意以及jet开头的词,不管jet后有多少字符
通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。
```SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE '%anvil%'; #%anvil%表示匹配任何位置包含文本anvil的值,不论它之前或之后出现什么字符
SELECT prod_name FROM products WHERE prod_name LIKE 's%e'; #通配符也可以出现在搜索模式的中间,虽然这样做不太有用。该例子找出以s起头以e结尾的所有产品
尾空格可能会干扰通配符匹配。例如,在保存词
anvil 时, 如果它后面有一个或多个空格, 则子句WHEREprod_name LIKE '%anvil'将不会匹配它们,因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模
式最后附加一个%。一个更好的办法是使用函数(第11章将会介绍)去掉首尾空格。
虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL。即使是WHERE rod_name LIKE '%'也不能匹配用值NULL作为产品名的行。
2> 下划线_通配符
下划线只能匹配单个字符而不是多个字符
SELECT prod_name FROM products WHERE prod_name LIKE '% ton anvil';
SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; # 与%能匹配0个字符不一样,_总是匹配一个字符,不能多也不能少。
通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。不要过度使用通配符。把通配符置于搜索模式的开始处,搜索起来是最慢的。
7. 用正则表达式进行搜索
正则表达式是用来匹配文本的特殊的串(字符集合)
1> 基本字符匹配SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; # 检索列prod_name包含文本1000的所有行,REGEXP 后面跟的东西作为正则表达式处理
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name; #这里的 . 是正则表达式语言中的一个特殊的字符,它表示匹配任意一个字符,因此1000和2000都匹配
使用通配符也能实现该过滤SELECT prod_name FROM products WHERE prod_name LIKE '%000';
在LIKE和REGEXP之间有一个重要的差别。LIEK匹配整个列,如果被匹配的文本在列值中出现,LIKE 将不会找到它,相应的行也不被返回(除非使用通配符)。而REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP 将会找到它,相应的行将会被返回。看下面这个例子:
SELECT vend_id, prod_name FROM products WHERE vend_id = 1002;
SELECT vend_id, prod_name FROM products WHERE vend_id LIKE '002';
SELECT vend_id ,prod_name FROM products WHERE vend_id REGEXP '002';
MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXP BINARY 'JetPack .000'。
2>进行 OR 匹配
为搜索两个串之一(或者为这个串,或者为另一个串),使用|SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'; #|为正则表达式的OR操作符。它表示匹配其中之一,因此1000和2000都匹配并返回。两个以上的OR条件 可以给出两个以上的OR条件。例如,'1000 | 2000 | 3000'将匹配1000或2000或3000。
3> 匹配几个字符之一
[]是另一种形式的OR语句。。事实上,正则表达式[123]Ton为[1|2|3]Ton的缩写,也可以使用后者。但是,#需要用[]来定义OR语句查找什么#。SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'; #这里,使用了正则表达式[123] Ton。[123]定义一组字符,它的意思是匹配1或2或3,因此,1 ton和2 ton都匹配且返回(没有3 ton)。
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'; #这并不是期望的输出。两个要求的行被检索出来,但还检索出了另外3行。之所以这样是由于MySQL假定你的意思是'1'或'2'或'3 ton'。除非把字符|括在一个集合中,否则它将应用于整个串。
字符集合也可以被否定。在集合开始的地方放一个^,例如[^123]匹配除这些字符以外的任何东西
4> 匹配范围
集合可用来定义要匹配的一个或多个字符。[0-9]可以匹配数字0-9,[a-z]可以匹配任意字母字符。范围不限于完整的集合,[1-3]和[6-9]也是合法的范围。SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9] Ton';
5> 匹配特殊字符
正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、[]、|和-等,还有其他一些字符。为了匹配特殊字符,必须用\为前导。\-表示查找-,\.表示查找.。这种处理就是所谓的转义(escaping),正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.';
\也用来引用元字符(具有特殊含义的字符)。
元字符 | 说明 |
---|---|
\f | 换页 |
\n | 换行 |
\r | 回车 |
\t | 制表 |
\v | 纵向指标 |
为了匹配 字符本身,需要使用\
5> 匹配字符类
存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作,可以使用预定义的字符集,称为字符类(character class)。
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同[\t]) |
[:cntrl:] | ASCII控制字符(ASCII 0到31和127) |
[:digit:] | 任意数字(同[0-9]) |
[:graph:] | 与[:print:]相同,但不包括空格 |
[:lower:] | 任意小写字母(同[a-z]) |
[:print:] | 任意可打印字符 |
[:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
[:space:] | 包括空格在内的任意空白字符(同[\f\n\r\t\v]) |
[:upper:] | 任意大写字母(同[A-Z]) |
[:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |
6> 匹配多个实例
目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配,该行被检索出来,如果不存在,检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如,你可能需要寻找所有的数,不管数中包含多少数字,或者你可能想寻找一个单词并且还能够适应一个尾随的s(如果存在),等等。
这可以正则表达式重复元字符来完成。
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配(等于{1,}) |
? | 0个或1个匹配(等于{0,1}) |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围(m不超过255) |
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'; #\\(匹配( [0-9]匹配任意数字(这个例子中为1和5),sticks?匹配stick和sticks(s后的?使s可选,因为?匹配它前面的任何字符的0次或1次出现),\\)匹配)
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'; #,[:digit:]匹配任意数字,因而它为数字的一个集合。{4}确切地要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4}匹配连在一起的任意4位数字。
需要注意的是,在使用正则表达式时,编写某个特殊的表达式几乎总是有不止一种方法。SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]';
7> 定位符
为了匹配特定位置的文本,需要使用定位符:
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
SELECT prod_name FROM products WHERE prod_name REGEXP '^[[:digit:]\\.]'; #找出以一个数(包括以小数点开始的数)开始的所有产品,^匹配串的开始。因此,^[:digit:]\\.只在.或任意数字为串中第一个字符时才匹配它们。没有^,则还要多检索出4个别的行(那些中间有数字的行)。
^有两种用法。在集合中(用[和]定义),用它来否定该集合,否则,用来指串的开始处。
利用定位符,通过用^开始每个表达式,用$结束每个表达式,可以使REGEXP的作用与LIKE一样。
8> 简单的正则表达式测试
可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(没有匹配)或1(匹配)。可以用带文字串的REGEXP来测试表达式,并试验它们。相应的语法如下:SELECT 'hello' REGEXP '[0-9]';
#这个例子显然将返回0(因为文本hello中没有数字)。
8. 创建计算字段
存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据;物品订单表存储物品的价格和数量,但不需要存储每个物品的总价格(用价格乘以数量即可)。为打印发票,需要物品的总价格。
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段(filed)基本上与列(column)的意思相同。
只有数据库知道SELECT语句中哪些列是实际的
表列,哪些列是计算字段。从客户机(如应用程序)的角度来看,计算字段的数据是以与其他列的数据相同的方式返回的。
1>拼接字段
拼接(concatenate)将值联结到一起构成单个值,在MySQL的SELECT语句中,可使用
Concat()函数来拼接两个列。多数DBMS使用+或||来实现拼接,MySQL则使用Concat()函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。
Concat()拼接串,即把多个串连接起来形成一个较长的串。
Concat()需要一个或多个指定的串,各个串之间用逗号分隔。SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name; # ,SELECT语句返回包含上述4个元素的单个列(计算字段)。
通过删除数据右侧多余的空格来整理数据,这可以使用MySQL的RTrim()函数来完成SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') FROM vendors ;
MySQL除了支持RTrim()(正如刚才所见,它去掉串右边的空格),还支持LTrim()(去掉串左边的空格)以及Trim()(去掉串左右两边的空格)。
2> 使用别名
别名(alias)是一个字段或值的替换名。别名用AS关键字赋予。SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors; #这里的语句中计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算的名为vend_title的计算字段。现在列名为vend_title,任何客户机应用都可以按名引用这个列,就像它是一个实际的表列一样。
别名还有其他用途。常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它,等等。
3> 执行算术计算
计算字段的另一常见用途是对检索出的数据进行算术计算。
SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20005; # 检索订单号20005中的所有物品
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
MySQL支持以下列出的基本算术操作符。此外,圆括号可用来区分优先顺序
操作符 | 说明 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
4> SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据,但可以省略FROM子句以便简单地访问和处理表达式。例如,SELECT3*2;将返回6,SELECT Trim('abc');将返回abc,而SELECTNow()利用Now()函数返回当前日期和时间。通过这些例子,可以明白如何根据需要使用SELECT进行试验。