透析mysql查询语句 - 快车库

在Mysql中,查询语句可以直接的使用SELECT语句. 关于SELECT,大概有一下几个子语句:

定义别名

提取列的时候,如果你觉得这个列名起的有点辣鸡. 那么你可以自己定义一个新的别名. 基本格式为:

SELECT id AS name FROM employee;

并且,你还可以对列进行简单的处理, 生成新的列.

SELECT id, id*2 AS dual_id FROM employee;

当然,你也可以选择不使用AS语句. 这也是可行的.

SELECT id name FROM employee;

去除重复行

在mysql中去除重复行,基本流程为,先对数据进行排序,然后删除重复的行. 最后显示结果,over. 所以, 显示不重复的数据,所耗费的性能和时间有一丁点长. 我们具体看一下格式吧:

SELECT DISTINCT col_name FROM db_table;

使用关键字DISTINCT 来对指定列进行简单的排序.

使用嵌套子表

子表

该方式相当于嵌套查询, 举一个简单的例子。

SELECT stu.score,stu.grade,stu.class FROM (SELECT score,grade,class FROM student) stu;

使用点操作符进行子表的查询.

视图

还可以使用视图的方式,创建一个virtue table. 所谓的virtue 指代的就是视图创建的时候,并不附带数据, 可以理解为一层过滤器.将原来表中的敏感字段信息给隐藏掉.
看一个demo:

CREATE VIEW limit_stu AS SELECT grade,class FROM student;

当上述命令执行后, 并未产生任何数据输出. 数据库会保存该表, 以供以后的查询,相当于, 创建一个新表一样.

SELECT * FROM limit_stu;

这样, 表就只会显示grade和class 字段,而不会显示其他的内容, 想score.

FROM定义表的别名

这个feature 一般是懒人必备. 因为当在数据库联合查询的时候, 不用再带上长长的表名.
like:

SELECT stu.score,stu.grade 
FROM student AS stu INNER JOIN teacher AS tc 
ON stu.teacher = tc.name;

sql中的排序

在sql中可以对指定字段进行相关排序,需要使用ORDER BY 关键字.

SELECT name FROM student
ORDER BY score;

另外, 在sql中,我们还可以进行升序和降序的相关操作.默认是升序操作, 这点需要注意一下.
即DESC(降序),ASC(升序)

SELECT name FROM student
ORDER BY score DESC,gender;

并且,排序还可以根据其他的内容进行处理。我们可以使用表达式,或者指定列来进行排序.

SELECT name FROM student
ORDER BY RIGHT(phone,3);

我们还可以根据已经给出的列来进行排序.

SELECT name,score FROM student
ORDER BY 2;
# 将第二列的内容进行相关排序.

sql中的条件判断

sql中的条件判断和WHERE子句是相关的. 通过添加某些条件,来实现数据的筛选功能.
like:

SELECT name FROM student 
WHERE score>90;

如果是多条件, 我们还可以使用AND,OR,NOT逻辑运算符来进行连接.

SELECT name FROM student
WHERE score>90 
AND gener='男';

更复杂的情况是, 嵌套条件的使用. 这时就需要小括号来帮忙了.

SELECT name FROM student
WHERE NOT (score>60 AND score<90);

在sql中,还存在一些关于条件查询的trick,我们接下来一起来看看.

BETWEEN 上下限

当想设置一个范围查询的时候,第一反应应该就是<,>,>=,<=这基本的运算. 但如果你想设置的是一个范围,like: >=20,<=30. 那么就需要使用两个字句,来进行连接. 但在sql中,我们可以直接使用BETWEEN low AND up这样的格式来进行设置[low,up]的范围.

SELECT name FROM student WHERE score BETWEEN 60 AND 90
# 找到[60,90]之间的人数

IN 包含

sql提供的IN关键字,相当于,判定是否存在枚举值中的任意一个值.

SELECT age FROM company
WHERE name IN ('Tencent','Alibaba','Baidu','美团');

并且,IN还可以用于子表的查询.即,将另外一个表的结果作为枚举集合.

SELECT age FROM company 
WHERE name IN (SELECT name FROM enterprise)

当然,那实现不包括有没有办法呢？
有的, sql给出了和IN相对应的NOT IN操作符,来判断给出的值在枚举集合中,不存在的状态.

SELECT age FROM company
WHERE name NOT IN(SELECT name FROM enterprise)

内置函数的使用

前面我们了解了一个字符串的右部截取(RIGHT), 这里还有一个左部截取,他和RIGHT差不多.基本格式为:

LEFT(field,num); //截取指定字段[0,num]的子字符

另外还有一个STRCMP(sting comparison) 方法. 用来比较两个字符串是否一致.他的作用也很简单.基本格式为:

STRCMP(str1,str2)

如果str1比str2短，或者两者长度一样,但内容不同, 则返回-1
如果str1等于str2,则返回0;
如果str1比str2长,则返回1;

当你在插入字符串的时候,有没有遇见下列这样的情况呢？

'I don't like it'

当插入上述的字符串时, 会明显的爆出一个bug出来. 因为你插入的字符串格式 is wrong. so, 那应该怎么办呢？
sql提供了一个过滤函数quote(str) 来帮助我们免除这个烦恼. 他会把str中的quote 进行转义,避免出现上述问题.

quote('I don't like it')
# 得到:
I don\'t like it

当你需要将连接字符插入的话,那应该使用什么方法呢？
the answer is : CONCAT(xxx)
CONCAT 方法其实和需要programe language里面的方法是一致的,用来连接多个字符串,并返回一个连接后的新字符串. CONCAT 也可以直接使用+进行代替.

CONCAT("a","b","c")
# 得到:
abc

# 等价于
'a' + 'b' + 'c'

接下来的问题是, 我们应该怎样判断一个字符串的长度呢？
使用LENGTH或者LEN即可.
另外,还有更多的函数,到时候需要时,看一下列表就行了.
这就不赘述了.Functions

正则和通配符

如果你嫌使用一些固定的方法不爽的话, 你可以使用wildcard(通配符)进行灵活匹配.
涉及到通配符, 需要使用sql提供的两个关键字LIKE,NOT LIKE.
他俩的效果和IN,NOT IN是类似的.
在sql中,有两个WC(通配符)_,%

_: 只能用来匹配一个字符,当然,也可以用来匹配中文字符.
%: 可以用来匹配多个字符.

SELECT name FROM student WHERE name LIKE '田%';

如果你想匹配_和%就可以直接使用\_和\%进行相关匹配.
关于NOT LIKE我就不想多说了. 他和LIKE的内容是相反的,记住这一点就没什么问题了。

我们接着来看一下,sql中的正则匹配.
sql中的正则需要使用到REGEXP和NOT REGEXP 关键字.
他使用的正则模式是Perl mode. 基本上,正则的使用方法,应该不差什么. 这里有一个地方需要提及一下,就是,'|'的匹配.
在js中,如果你在Regexp Object 使用像这样的str sam|jimmy,他代表的就是完全匹配sam|jimmy.
即

/sam|jimmy/g.test('sam|jimmy');  //返回true

但在sql中,这里代表的就是一个或的操作.

SELECT 'sam' REGEXP 'sam|jimmy';  //返回1

其余的,我就不啰嗦了.

NULL

NULL值和其他所有非正式数据类型来说,都是一个非常特别,但又特别坑的值.

NULL != NULL

所以,在mysql中,判断一个值是否为null, 需要使用IS NULL关键字来进行判断. 如果判断其不是NULL, 同样只需要使用IS NOT NULL.

SELECT name FROM student WHERE score IS NOT NULL;

sql中的连表

在sql中,我们可以使用JOIN,来进行跨表的连接. 在通常的mysql库中, JOIN,,,INNER JOIN 这三者是相等的.

SELECT stu.name,tea.name FROM student stu,teacher tea;
// 可以替换为
SELECT stu.name,tea.name FROM student stu JOIN teacher tea;
// 可以替换为
SELECT stu.name,tea.name FROM student stu INNER JOIN teacher tea;

不过,从标准上来看,我们使用 INNER JOIN 时, 还可以使用ON 语句进行简单的条件判断

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

有童鞋可能会问到,ON和WHERE是什么关系嘞?
实际上, 两者完全是可以相互替换的.
啥?
就是:ON === WHERE
如果认真细分起来,ON和WHERE 还是有很大的区别的.

ON: 一般常用在JOIN等,用于表的连接的条件判断中.比如:person.id = phone.person
WHERE: 一般用在字段的筛选中.

另外,这里补充一个trick,当你使用ON进行条件判断时,如果两个表明是一致的话,那么就可以直接使用USING(field1)进行判断

FROM a INNER JOIN b USING (id);
// 等价于
// ON a.id = b.id

USING的格式实际为USING(column_list) 用来表示, 指定的列在所列出的表中都存在,并且表名一致.

FROM a INNER JOIN b USING (c1,c2,c3)

但有时候,两个表已经不能满足,多表间查询。我们可能会用到3个表,或者4个表来进行连接. 这里, 我们需要注意一下,在sql中怎样进行多表连接

多表连接

一般而言,大于3 的数量都可以被称为多,我们这里,就依照3个表的连接来说明. 通常来说, 2个表的连接很简单.

FROM a INNER JOIN b USING(id);

如果遇到3个表呢？
一样简单

FROM a INNER JOIN b ON a.id = b.id
INNER JOIN c ON (b.id=c.id)
WHERE c.name LIKE '田%';

不过,这里需要注意一下,如果你引用表的时候,使用的是直接性的a,b. 那么在和JOIN 一起使用的时候,就需要注意一下,优先级顺序。因为JOIN的优先级是大于顺序的.

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

这个例子就会报出: Unknown column 't1.i1' in 'on clause
实际上他的执行方式是:

SELECT * FROM t1, (t2 JOIN t3) ON (t1.i1 = t3.i3);

如果你想正确的执行的话,需要改为:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

强制顺序

通常情况下,sql对于表引用顺序是没有多大限制的.即:

SELECT a.account,b.name,c.gender
FROM A a INNER JOIN B b USING(id)
INNER JOIN C c USING(id)

当然,写为下面的语句两者也是相等的.

FROM B b INNER JOIN A a USING(id)
INNER JOIN C c USING(id)

因为sql,不是一个描述过程性的语言,所以引用表的顺序如果,都不会影响最后的结果.
如果, 你想强制顺序查询,即, 先将a 和 b 连表的结果,再和c进行连接. 最后输出结果. 可以使用STRAIGHT_JOIN来限制顺序.

SELECT STRAIGHT_JOIN a.account,b.name,c.gender
FROM B b INNER JOIN A a USING(id)
INNER JOIN C c USING(id)

交叉连接

sql中的交叉连接,代表的是无条件连接. 相当于一个1->all的映射关系. 即, 以第一个表的一条记录,搭配第二个表的所有记录. 按照该规律,一次遍历完第一个表.
相当于笛卡尔积: (n×m)

外连接

sql中的外连接,可以分为LEFT和RIGHT连接. 这里,我们以LEFT为例:

LEFT外连接具体指, 显示左边连接表的所有记录, 而右边只显示匹配的row, 如果出现右边不能匹配的记录, 则自动补充NULL 来进行填充.

SELECT column_name
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

其中,LEFT OUTER JOIN可以简写为LEFT JOIN.
同样, RIGHT的连接方式和LEFT差不多, 只是他依据的表是在右边.

如果你想连接多个表的话, 则可以使用多次LEFT JOIN

SELECT  * FROM A LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON C.id = A.id

或者可以使用parenthese(小括号) 来进行多表连接

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

如果他们都是应用t1的同一个外键的话, 则可以使用

SELECT * FROM T1 LEFT JOIN (t2,t3,t4) USING (id);

自然连接

自然连接没什么说的,就是一个NATURAL关键字进行连接. 他主要的作用就是,如果两个表存在相同的column, 可以省略不写,因为NATURAL会自动甄别出相同列, 然后依据该条件进行连接.

// 假设,A,B表都存在id的列,则NATURAL 连接可以写为:
SELECT * FROM TableA NATURAL JOIN TableB
// 等价于
SELECT * FROM TableA A INNER JOIN TableB B WHERE A.id = B.id

Ps: 实际上, 上面两者并未相等. 因为使用INNER JOIN的话, 两者重复的id 列还是会显示出来.
如果你使用自然连接,连接两个表,但是两个表中并没有相同的列, 那么就会默认使用CROSS JOIN进行连接.

分组

分组有时候会和DISTINCT弄混. 看起来两者的效果是一致的, 都是找出不重复的值.

SELECT DISTINCT name FROM student;
// 等价于
SELECT name FROM student 
GROUP BY name;

单看结果来说,没有太大的区别, 但DISTINCT 会根据排序结果只取出第一条数据, 他是不能够进行任何的其他操作的.
但,如果使用GROUP BY的话, 则可以进行聚合操作.

SELECT grade, COUNT(*) number FROM school
GROUP BY grade;

但,当使用GROUP时, 需要注意一下,涉及到GROUP的条件判断,不能和WHERE语句一起使用. 因为, 当在使用WHERE语句时, 分组实际上还没有形成. 如果想使用条件判断的话只能使用HAVING语句使用.

SELECT name,COUNT(*) people  FROM school
HAVING COUNT(*)>200;

当涉及到多列分组时, GROUP BY 的逻辑是怎样的呢？
like:

SELECT A,B,SUM(C) FROM alphabet
GROUP BY A,B

他表达的逻辑是, 先合并A, 然后将A合并的记录, 以B来合并, 最后通过A和B合并的结果统计C。
具体看一个demo:

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance
FROM acount
GROUP BY product_cd,open_branch_id;

可以得到以下数据:

可以看出, BUS出现了两次, 照理说应该出现一次,但这是多列分组, 如果在第一列分组里,存在多个第二列的分组, 那么最终显示的记录应该提出来. 相当于 A×B 的分组结果.

顺便在这里补充一些常用的聚合函数.

常用聚合函数

当然, 你使用聚合函数的时候, 不必和GROUP BY一起使用. 因为聚合相当于已经内置进行判断了.
有:

MAX(): 得到列的最大值
MIN(): 得到列的最小值
AVG(): 算出列的平均值
SUM(): 得出列的和
COUNT(): 计算指定列的行数

SELECT AVG(score) 平均分, MAX(score) 最高分, MIN(score) 最低分 FROM student

这里还有一个trick, 使用COUNT + DISTINCT 能够计算出, 某列值一共出现多少个不同的值.

SELECT COUNT(DISTINCT score) FROM student