mysql2教程_MySql基础教程(二)

所需要的三张表在《MySql基础教程(一)》中已经建立了，不再重建。

主要内容：模糊查询(like)，联合查询(union)，计算总数(count)，合计(sum)，排序(order by)，分组(group by)，表连接(join)，正则表达式

1、like

%：表示任意个或多个字符。可匹配任意类型和长度的字符。

eg：

select * from Student where SName like '%红';select * from Student where SName like '张%';select * from Student where SName like '%花%';

另外，如果需要找出SName中既有“小”又有“花”的记录，请使用and条件

select * from Student where SName like '%小%' and SName like '%花%';

若使用 select * from ［Student］ where SName like ‘%小%花%’;

虽然能搜索出“小花”，但不能搜索出符合条件的“张花小”。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：(可以代表一个中文字符)

select * from Student where SName like '_';select * from Student where SName like '红_';select * from Student where SName like '李_红';

如果我就真的要查%或者_，怎么办？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用

eg：

select SName from Student where SName like '%红/_%' escape '/';select SName from Student where SName like '%红/%%' escape '/';

2、union

union 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。

union 是对数据进行并集操作，不包括重复行，同时进行默认排序

eg：

select column_name(s) fromtable_name1union

select column_name(s) from table_name2

union all 是对数据进行并集操作，包括重复行，不进行排序

eg：

select column_name(s) fromtable_name1union all

select column_name(s) from table_name2

3、count

count(*)函数将返回表格中所有存在的行的总数包括值为null的行的记录数

count(column_name) 函数返回指定列的值的数目(NULL 不计入)

count(distinct column_name) 函数返回指定列的不同值的数目(distinct 的作用是对查询结果去重)

eg：

SELECT COUNT(*) FROM STUDENT;

返回结果：4

SELECT COUNT(*) AS COUNT1 FROM STUDENT WHERE SAge>18;

返回结果：2

SELECT COUNT(SID) FROM SC;

返回结果：4

SELECT COUNT(distinct SID) FROM SC;

返回结果：3

总结：

一般情况下，Select Count (*)和Select Count(1)两者返回结果是一样的，都包括对NULL的统计，而count(column) 是不包括NULL的统计。

假如表沒有主键(Primary key), 那么count(1)比count(*)快

如果有主键的話，那主键作为count的条件时候count(主键)最快

如果你的表只有一个字段的话那count(*)就是最快的

在不加where限制条件的情况下，count(*)与count(col)基本可以认为是等价的；

但是在有where限制条件的情况下，count(*)会比count(col)快非常多；

不考虑Null的情况：

count(1)和count(主键) 这两个只扫描主键Index就可以得到数据，count(*)是扫描表的。所以count(1)和count(主键)这两个效率高。

还有一种写法是count(rowid)这也是只扫描Index的，效率高。

4、sum

sum(column_name) 函数返回指定列的值的求和运算

sum(case when 某字段=某值 then 1 else 0 end)满足一定条件的记录条数，相当于count，起计数作用

eg:

SELECT SUM(SScore) FROM SC;

返回结果：227

SELECT SUM(case when SID='S001' THEN 1 ELSE 0 END) FROM SC;

返回结果：2

5、order by

order by column_name desc/asc默认升序asc(从低到高)

多列混合排序：select , from order by desc, asc;

6、group by

group by column_name表示根据 column_name 的不同取值对查询结果进行分组。 column_name 有n个不同的取值,查询结果就会被分成n组。

当分组字段有多个时候group by A,B时; 会对A 和B 进行排列组合。每个排列组合的结果作为查询一个的一个分组。

如果A 的取值有 n 个，B的取值有 m 个，那么查询结果将会被分称m*n组。

当count 与 group by 连用时，count是对 group by 结果的各个分组进行计数 。

eg：

查看每个学生选了几门课

SELECT SID,COUNT(*) FROM SC GROUP BY SID;

查看每个学生的选课总成绩：

SELECT SID,SUM(SScore) FROM SC GROUP BY SID;

7、join

内联接(Inner join),满足交换律：“A inner join B” 和 “B inner join A” 是相等的。

外部联接(Outer join)进行查询。由于外部联接保存一个或两个输入表的所有行，即使无法找到匹配联接谓词的行

交叉联接(cross join)执行两个表的笛卡尔积(就是把表A和表B的数据进行一个N*M的组合)。也就是说，它匹配一个表与另一个表中的每一行；我们不能通过使     用ON子句在交叉联接指定谓词，虽然我们可以使用WHERE子句来实现相同的结果，这时交叉联接基本上是作为一个内部联接了。

交叉联接相对于内部联接使用率较低，而且两个大表不应该进行交叉联接，因为这将导致一个非常昂贵的操作和一个非常大的结果集。

下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

8、正则表达式

MySQL可以通过 LIKE...% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。正则表达式作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较。匹配上返回"1"匹配不上返回"0"，默认不加条件REGEXP相当于like '%%'。在前面加上NOT相当于NOT LIKE。

下表中的正则模式可应用于 REGEXP 操作符中。

模式描述

^

匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。

$

匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。

.

匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。

[...]

字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。

[^...]

负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。

p1|p2|p3

匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。

*

匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。

+

匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。

{n}

n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。

{n,m}

m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

(1)基本字符匹配

select SID from student where SID regexp 'S001';

返回：S001

.  表示匹配任意一个字符。

select SID from student where SID regexp '.00';

返回：S001

S002

S003

S004

LIKE和REGEXP区别

LIKE会匹配这个列，而REGEXP会在列值内进行匹配

在MySql中正则表达式不区分大小写，要区分需使用BINARY关键字

select SID from student where SID regexp binary 's001';select SID from student where SID regexp 's001';

第一个没有返回值，第二个返回：S001

(2)OR匹配

| 作为OR操作符，表示匹配其中之一。可给出两个以上的OR条件。

select SID from student where SID regexp 'S001|S002';

返回：S001

S002

(3)[ ] 匹配任何单一字符

[123]定义一组字符，意思是匹配1或2或3.

[ ]是另外一种形式的OR语句，[123] Ton  就是  [1 | 2 | 3] Ton 的缩写。

^ 否定一个字符集合，将匹配除指定字符外的任何东西。[^123]将匹配除这些字符外的任何东西。

select SID from student where SID regexp 'S00[123]';

返回：S001

S002

S003

select SID from student where SID regexp 'S00[^123]';

返回：S004

(4)匹配范围

[0-9]数字0到9

[a-z]a到z

[A-Z]A到Z [^0-9] ^表示非，即匹配不是0-9

注意，后面的必须比前面大

select SID from student where SID regexp 'S00[1-4]';

返回：S001

S002

S003

S004

(5)匹配特殊字符

在特殊字符前加“\\”进行转义，注意在一般情况下正则表达式的转义加一个“\”就可以了，在mysql中需要加两个。

\\-  表示查找 -

\\.  表示查找 .

select SID from student where SID regexp '\\.S00';

无返回

\\  也用来引用元字符(具有特殊意义的字符)

\\f 换页

\\n 换行

\\r 回车

\\t 制表

\\v 纵向制表

(6)匹配字符类

使用的时候需要外面加一层[]，例如[[:digit:]]

类 说明[:alnum:] 任意字母和数字(同[a-zA-Z0-9])[:alpha:] 任意字母(同[a-zA-Z])[:blank:] 空格和制表(同[\\t])[:cntrl:]ASCII控制字符(ASCII0到31和127)[:digit:] 任意数字(同[0-9])[:graph:] 和[[:print:]]相同，但不包含空格[:lower:] 任意小写字母(同[a-z])[:print:]任意可打印字符[:punct:] 即不在[[:alnum:]]又不在[[:cntrl:]]中的字符[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])[:upper:] 任意大写字母(同[A-Z])[:xdigit:] 任意16进制数字(同[a-fA-F0-9])

select SID from student where SID regexp 'S00[[:digit:]]';

返回：S001

S002

S003

S004

(7)匹配多个实例

元字符 说明*0个或多个匹配+1个或多个匹配(等于 {1, })

？ 0个或1个匹配(等于 {0, 1})

{n} 指定数目的匹配

{n，} 不少于指定数目的匹配

{n,m} 匹配数目的范围(m不超过255)

select SID from student where SID regexp '[A-Z]{1,3}';

返回：S001

S002

S003

S004

(8)定位符

元字符 说明^文本的开始

$ 文本的结尾[[:<:>:]] 词的结尾

select SID from student where SID regexp '^S';

返回：S001

S002

S003

S004

注意：^有两个用法，一个是非，一个是文本的开始，在[]中表示非，否则是文本的开始。