MySQL入门笔记(二)
MySQL的数据类型、数据库操作、针对单表的操作以及简单的记录操作可参考:MySQL入门笔记(一)
五、子查询
子查询可简单地理解为查询中的查询,即子查询外部必然还有一层查询,并且这里的查询并非仅仅指SELECT的查询操作,而是包括INSERT、DELETE、SET等操作在内的所有操作。
1. 使用比较运算符的子查询
operand comparison_operator [{ANY | SOME | ALL}] (subquery)
operand为操作数,即参与比较运算的数;在语句的最后为subquery子查询,子查询必须写在小括号内;ANY、SOME与ALL关键字用于子查询返回结果不唯一的情况,下面通过例子说明几个关键字的作用。
例:存在下表test
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | John | 20 |
| 2 | Kity | 24 |
| 3 | Tommy | 26 |
| 4 | Jackson | 31 |
| 5 | Pat | 19 |
| 6 | Darling | 33 |
| 7 | Tamzin | 29 |
| 8 | Hoking | 21 |
| 9 | Obama | 22 |
| 10 | Bush | 25 |
+----+---------+------+
现需要查询年龄大于平均值的的人,按照普通的方法,则需要先计算出年龄平均值SELECT AVG(age) FROM test;
(结果为25.0000),再根据输出的结果进行查询SELECT * FROM test WHERE age > 25;
;若使用子查询,则可将两步合为一步:
SELECT * FROM test WHERE age > (SELECT AVG(age) FROM test);
这就是子查询,而ANY、SOME以及ALL关键字的作用则是,当子查询返回的结果存在多条记录时,规定其比较方法,例如,现在要查询比id<=3的记录中任意一记录的age小的记录:
SELECT * FROM test WHERE age < ANY (SELECT age FROM test WHERE id <= 3);
SOME的作用与ANY相同,而ALL则是如其含义一般的“全部”,例如在上面的例子中,若将ANY改成ALL,则返回的结果的age必须大于id<=3的全部记录的age。
2. 使用[NOT] IN 的子查询
operand [NOT] IN (subquery)
[NOT] IN的作用为检测某些记录是否存在指定表中,其中IN等效于= ANY;NOT IN等效于!= ALL或者<> ALL。
例:以下两个语句结果完全一样:
SELECT * FROM test WHERE age = ANY (SELECT age FROM test WHERE id <= 3);
SELECT * FROM test WHERE age IN (SELECT age FROM test WHERE id <= 3);
3. 使用[NOT] EXISTS的子查询
若子查询返回任意行,EXISTS返回1;否则为0。
例:以下操作返回结果则为0:
SELECT EXISTS (SELECT age FROM test WHERE id < 0);
六、多表操作
1. 连接
table_reference {[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN} table_reference ON conditional_expr
连接,如其字面含义,连接多个数据表,使其产生物理上的关系(即实际存在关系,而非仅仅为逻辑上的关系)。ON关键字用于设定其后的连接条件;{[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN}为连接类型,分别是INNER JOIN内连接、LEFT [OUTER] JOIN左外连接和RIGHT [OUTER] JOIN右外连接三种:
内连接为仅返回符合连接条件的记录,如下图所示,内连接返回的结果为两表的交集部分。其中JOIN、INNER JOIN和CROSS JOIN作用相同,均表示内连接。
例:存在以下两表:
users:
+----+---------+------+----------+
| id | name | age | location |
+----+---------+------+----------+
| 1 | John | 20 | 3 |
| 2 | Kity | 24 | 3 |
| 3 | Tommy | 26 | 4 |
| 4 | Jackson | 31 | 3 |
| 5 | Pat | 19 | 4 |
| 6 | Darling | 33 | 2 |
| 7 | Tamzin | 29 | 2 |
| 8 | Hoking | 21 | 2 |
| 9 | Obama | 22 | 3 |
| 10 | Bush | 25 | 1 |
| 11 | Richard | 22 | 12 |
| 12 | Andy | 22 | 13 |
+----+---------+------+----------+
lname:
+----+-----------+
| id | name |
+----+-----------+
| 1 | 北京市 |
| 2 | 上海市 |
| 3 | 广州市 |
| 4 | 深圳市 |
| 5 | 杭州市 |
+----+-----------+
现进行以下操作:
SELECT * FROM users INNER JOIN lname ON users.location=lname.id;
结果为:
+----+---------+------+----------+----+-----------+
| id | name | age | location | id | name |
+----+---------+------+----------+----+-----------+
| 1 | John | 20 | 3 | 3 | 广州市 |
| 2 | Kity | 24 | 3 | 3 | 广州市 |
| 3 | Tommy | 26 | 4 | 4 | 深圳市 |
| 4 | Jackson | 31 | 3 | 3 | 广州市 |
| 5 | Pat | 19 | 4 | 4 | 深圳市 |
| 6 | Darling | 33 | 2 | 2 | 上海市 |
| 7 | Tamzin | 29 | 2 | 2 | 上海市 |
| 8 | Hoking | 21 | 2 | 2 | 上海市 |
| 9 | Obama | 22 | 3 | 3 | 广州市 |
| 10 | Bush | 25 | 1 | 1 | 北京市 |
+----+---------+------+----------+----+-----------+
不难看出,返回结果为users表中的第11、12条记录,以及lname表中的第5条记录以外的所有结果,原因就是这3条记录并不存在于两表的交集之中。(若觉得难以理解,往下结合左外连接与右外连接相对照,这样比较容易理解)
左外连接为返回左表中的全部以及右表中满足连接条件的记录,右表不存在的记录所对应的字段显示为NULL。
右外连接则为返回右表中的全部以及左表中满足连接条件的记录,左表不存在的记录所对应的字段显示为NULL。
例:仍为上述两表,现将上述查询操作中的内连接改为左外连接:
SELECT * FROM users LEFT JOIN lname ON users.location=lname.id;
输出结果为:
+----+---------+------+----------+------+-----------+
| id | name | age | location | id | name |
+----+---------+------+----------+------+-----------+
| 10 | Bush | 25 | 1 | 1 | 北京市 |
| 6 | Darling | 33 | 2 | 2 | 上海市 |
| 7 | Tamzin | 29 | 2 | 2 | 上海市 |
| 8 | Hoking | 21 | 2 | 2 | 上海市 |
| 1 | John | 20 | 3 | 3 | 广州市 |
| 2 | Kity | 24 | 3 | 3 | 广州市 |
| 4 | Jackson | 31 | 3 | 3 | 广州市 |
| 9 | Obama | 22 | 3 | 3 | 广州市 |
| 3 | Tommy | 26 | 4 | 4 | 深圳市 |
| 5 | Pat | 19 | 4 | 4 | 深圳市 |
| 11 | Richard | 22 | 12 | NULL | NULL |
| 12 | Andy | 22 | 13 | NULL | NULL |
+----+---------+------+----------+------+-----------+
2. 多表创建
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition, ...)] select_statement;
在创建表的同时把查询结果写入该表中。
3. 多表删除
DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition];
例:把上述例子中的表users中location字段在表lname中不存在的记录删掉(即删掉第11、12条记录):
DELETE users.* FROM users INNER JOIN lname ON users.location != ALL(SELECT lname.id FROM lname);
4. 多表更新
UPDATE tables_references SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}]... [WHERE where_condition];
例:存在以下两个数据表:
users:
+----+---------+------+-----------+
| id | name | age | location |
+----+---------+------+-----------+
| 1 | John | 20 | 广州市 |
| 2 | Kity | 24 | 广州市 |
| 3 | Tommy | 26 | 深圳市 |
| 4 | Jackson | 31 | 广州市 |
| 5 | Pat | 19 | 深圳市 |
| 6 | Darling | 33 | 北京市 |
| 7 | Tamzin | 29 | 北京市 |
| 8 | Hoking | 21 | 北京市 |
| 9 | Obama | 22 | 广州市 |
| 10 | Bush | 25 | 上海市 |
+----+---------+------+-----------+
lname:
+----+-----------+
| id | name |
+----+-----------+
| 1 | 上海市 |
| 2 | 北京市 |
| 3 | 广州市 |
| 4 | 深圳市 |
+----+-----------+
现将users表中的location用lname表中相应的id进行替代,操作为:
UPDATE users INNER JOIN lname ON users.location=lname.name SET users.location=lname.id;
七、运算符与函数
PS:MySQL提供的函数有很多,这里仅列举一些比较常用的函数,若需要用到其它函数,可查阅MySQL官方文档。
1. 字符函数
1.1 CONCAT(str1, str2, ...)
作用:连接多个字符串。例:SELECT CONCAT('John','Smith');
输出结果为:
+------------------------+
| CONCAT('John','Smith') |
+------------------------+
| JohnSmith |
+------------------------+
1.2 CONCAT_WS(connector, str1, str2, ...)
作用与CONCAT相同,不同之处在于CONCAT_WS的第一个参数为指定连接字符串所使用的连接符。例:存在表username:
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John | Smith |
| Kity | Chan |
+-----------+----------+
输入命令SELECT CONCAT_WS('-', firstname, lastname) AS fullname FROM username;
输出结果为:
+------------+
| fullname |
+------------+
| John-Smith |
| Kity-Chan |
+------------+
1.3 FORMAT(number, dec_place)
将数字格式化,返回结果为字符型。所谓格式化即将整数每3位用逗号分隔,并按照指定保留小数位数进行取舍。函数的第一个参数为进行格式化的数字,第二个参数为保留的小数位数。
例:SELECT FORMAT(156416486435.13561651,5);
的输出结果为:
+----------------------------------+
| FORMAT(156416486435.13561651, 5) |
+----------------------------------+
| 156,416,486,435.13562 |
+----------------------------------+
1.4 LOWER(str)、UPPER(str)
LOWER的作用是将字符中的字母全部转换为小写,UPPER则相反。例:SELECT LOWER('TEST大写');
输出结果为:
+---------------------+
| LOWER('TEST大写') |
+---------------------+
| test大写 |
+---------------------+
1.5 LEFT(str, len)、RIGHT(str, len)
LEFT的作用是获取字符串从左边算起指定位数的字符,RIGHT则相反。例:SELECT LEFT('MyFunction', 5);
输出结果为:
+-----------------------+
| LEFT('MyFunction', 5) |
+-----------------------+
| MyFun |
+-----------------------+
1.6 LENGTH(str)
获取字符串长度。
1.7 LTRIM(str)、RTRIM(str)、TRIM()
LTRIM的作用是删除字符串的前导空格(即字符串前面的空格),RTRIM是删除字符串的后续空格(即字符串后面的空格);
TRIM的用法有两种,一是参数仅指定一个字符串TRIM(str),则删除该字符串的前导和后续空格;还有一种用法则是可指定删除字符串的前导或后续的指定字符TRIM({LEADING | TRAILING | BOTH} del_char FROM str),LEADING为前导,TRAILING为后续,BOTH则为两者均删除。
例:SELECT LENGTH(' TEST ');
的输出结果为:
+---------------------+
| LENGTH(' TEST ') |
+---------------------+
| 9 |
+---------------------+
加上TRIM函数后:SELECT LENGTH(TRIM(' TEST '));
+---------------------------+
| LENGTH(TRIM(' TEST ')) |
+---------------------------+
| 4 |
+---------------------------+
另外,若输入命令SELECT TRIM(LEADING ',' FROM ',test,');
,输出结果为:
+---------------------------------+
| TRIM(LEADING ',' FROM ',test,') |
+---------------------------------+
| test, |
+---------------------------------+
1.8 REPLACE(str, ori_str, sub_str)
将字符串中的某段字符替换成另外一段字符。例:输入命令SELECT REPLACE('asdfasdf', 'as', 'df');
,结果为:
+---------------------------------+
| REPLACE('asdfasdf', 'as', 'df') |
+---------------------------------+
| dfdfdfdf |
+---------------------------------+
1.9 SUBSTRING(str, i[, j])
作用是截取字符串str从第i个字符开始算起长度为j的字符串,若不指定j,则从第i位截取到末尾。i可以为负值,此时则从字符串的末尾往前算,例如-3则为倒数第3个字符算起。
1.10 [NOT] LIKE
作用是在一个字符型字段中检索包含指定子字符串的字符串,需要配合通配符进行使用。MySQL中LIKE语句的通配符有:百分号(%)、下划线(_)和ESCAPE。
%表示任意字符串,例如'%ab%'表示含有'ab'的任意字符串,可以是aabc、lab、abo等等;下划线表示任意单个字符,例如'ae_a'可以表示aeba、aeoa等等;若需要查询%或下划线,则通过ESCAPE加转义字符。
例:查询表users中name字段为'李'开头的记录:
SELECT * FROM users WHERE name LIKE '李%';
查询表users中name字段为'xiao%'开头的记录:
SELECT * FROM users WHERE name LIKE '李/%%' ESCAPE '/';
紧随转义字符后面的一个%或一个下划线不作为通配符,其它的依旧为通配符。
2. 数值运算符和函数
2.1 CEIL(num)、FLOOR(num)
分别为向上取整和向下取整。
2.2 ROUND(num[, m])
四舍五入。可仅指定进行四舍五入的数字,也可以通过第二个参数同时指定取舍的小数位数。
2.3 POWER(num, m)
幂运算,计算num的m次方。
2.4 TRUNCATE(num, m)
数字截取。m为正值时,将num保留m位小数,m为负值时,则从整数位个位算起m位为0,同时保留整数。不作四舍五入,而直接截取。例如TRUNCATE(23.63, 0) = 23、TRUNCATE(459.23, -1) = 450。
2.5 DIV
整数除法,即将普通除法得到的结果保留整数位,例如5 DIV 4 = 1。
2.6 MOD
取余运算,例如5 MOD 4 = 1。
3. 比较运算符和函数
3.1 [NOT] BETWEEN...AND...
判断是否在指定范围内。例如SELECT 10 BETWEEN 0 AND 10
,结果为1,即true。
3.2 [NOT] IN()
判断是否在给定的若干个值的范围内。BETWEEN...AND...判断的是一个连续的区间,而IN则是数轴上的几个点。例如SELECT 6 IN(1, 2, 3, 4);
结果为0,即false。
3.3 IS [NOT] NULL
判断是否为空。例如SELECT '' IS NULL
,结果为0。
4. 日期时间函数
4.1 NOW()、CURDATE()、CURTIME()
分别是当前日期和时间、当前日期、当前时间。例如输入命令SELECT NOW();
,结果为:
+---------------------+
| NOW() |
+---------------------+
| 2017-03-06 10:21:17 |
+---------------------+
4.2 DATE_ADD(ori_date, INTERVAL add_date {YEAR | MONTH | WEEK | DAY |...})
日期变化,add_date可以为负值。例如SELECT DATE_ADD('2017-03-06', INTERVAL -2 MONTH);
,结果为:
+-------------------------------------------+
| DATE_ADD('2017-03-06', INTERVAL -2 MONTH) |
+-------------------------------------------+
| 2017-01-06 |
+-------------------------------------------+
4.3 DATEDIFF(date1, date2)
计算两个日期的差值。例如SELECT DATEDIFF('2016-01-01', NOW());
,结果为(今天日期为2017-03-06):
+-------------------------------+
| DATEDIFF('2016-01-01', NOW()) |
+-------------------------------+
| -430 |
+-------------------------------+
4.4 DATE_FORMAT(date, format)
将日期date按照指定格式输出。常用格式有年份的%y、%Y,月份的%m、%M,日期的%D、%d,要注意,区分大小写。
例:SELECT DATE_FORMAT(NOW(), '%d/%m/%y');
,结果为:
+--------------------------------+
| DATE_FORMAT(NOW(), '%d/%m/%y') |
+--------------------------------+
| 06/03/17 |
+--------------------------------+
SELECT DATE_FORMAT(NOW(), '%D/%M/%Y');
,结果为:
+--------------------------------+
| DATE_FORMAT(NOW(), '%D/%M/%Y') |
+--------------------------------+
| 6th/March/2017 |
+--------------------------------+
5. 信息函数
名称 | 作用 |
---|---|
CONNECTION_ID() | 返回当前线程的id |
DATABASE() | 返回当前正在使用的数据库名称 |
LAST_INSERT_ID() | 返回最后插入的记录的ID |
USER() | 返回当前用户 |
VERSION() | 返回当前数据库的版本信息 |
LAST_INSERT_IN()使用注意:使用的对象数据表必须含有一个自动编号的字段。若最新一次插入操作同时插入多条记录,则返回多条记录中的第一个id而非最后一个id。例如最后一次操作中插入了id为8和9的两条记录,则使用LAST_INSERT_ID返回的id为8。
6. 聚合函数
名称 | 作用 |
---|---|
AVG() | 计算平均值 |
COUNT() | 计数 |
MAX() | 比较得到最大值 |
MIN() | 比较得到最小值 |
SUM() | 求和 |
聚合函数使用注意:在MySQL中不允许使用聚合函数直接进行数值运算,其参数要求为数据表中的某字段名称。例如SELECT AVG(1, 2, 3);
这一命令则会报错,正确用法应为SELECT AVG(age) FROM users;
。
7. 加密函数
7.1 MD5(str)
对字符串进行散列,一般用于一些普通的不需要解密的数据加密。例:SELECT MD5('MyPassword');
,结果为:
+----------------------------------+
| MD5('MyPassword') |
+----------------------------------+
| 48503dfd58720bd5ff35c102065a52d7 |
+----------------------------------+
7.2 PASSWORD(str)
用法与MD5()基本一致,一般用于给用户密码进行加密。
8. 自定义函数
8.1 变量
(1)用户变量:以"@"开始,定义方法为@var_name
,例如定义一个变量temp并让其等于10:SET @temp = 10;
。需要注意,使用变量时需要连着"@"。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效。
(2)全局变量:定义时,以如下两种形式出现,SET GLOBAL 变量名
或者SET @@GLOBAL.var_name
。对所有客户端生效,只有具有super权限才可以设置全局变量。
(3)会话变量:只对连接的客户端有效。
(4)局部变量:用DECLARE进行定义,作用范围在begin到end语句块之间。
8.2 简单结构(函数体只有一个执行语句)
CREATE FUNCTION function_name([par_definition1] [, par2_definition2] ...)
RETURNS data_type
RETURN function_oper;
函数名后面指定参数列表,可以为空;RETURES后面指定返回值类型;RETURN后面跟具体的函数内容,注意返回类型需与RETURNS后面指定的类型一致。
例:自定义一个函数,求两个整数的平均值:
CREATE FUNCTION myAvg(a SMALLINT, b SMALLINT)
RETURNS SMALLINT
RETURN (a+b)/2;
输入命令SELECT myAvg(8,4);
,结果为:
+------------+
| myAvg(8,4) |
+------------+
| 6 |
+------------+
8.3 复合结构(函数体有多个执行语句)
DELIMITER new_terminator
CREATE FUNCTION function_name([par_definition1] [, par2_definition2] ...)
RETURNS data_type
BEGIN
function_oper1;
RETURN function_oper2;
END new_terminator
与简单结构不同的地方有二,第一是当出现多个执行语句时,前面的执行语句末尾的';'会被认为是终止符,导致出错,因此首先需要通过DELIMITER关键字指定新的终止符;第二则是简单结构中执行语句只有一句,因此该语句必须为能够直接跟在RETURN后面的、返回值符合要求的语句,而复合结构中存在多个语句,因此需要以BEGIN开始,以RETURN返回值,以END结束。
例:定义一个函数,可向users表(含有id、name两个字段)插入一条记录,并返回该记录的ID:
DELIMITER $$
CREATE FUNCTION addUser(name VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
INSERT users(name) VALUES(name);
RETURN LAST_INSERT_ID();
END$$
DELIMITER ;
将终止符重新指定为分号,并加入第一条记录:
mysql> DELIMITER ;
mysql> SELECT addUser('John');
+-----------------+
| addUser('John') |
+-----------------+
| 1 |
+-----------------+
8.4 删除自定义函数
DROP FUNCTION [IF EXISTS] function_name;
八、存储过程
CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([{IN | OUT | INOUT} proc_parameter[, ...]]) [characteristic ...] routine_body;
创建存储过程的语法结构与自定义函数非常相似,仅仅是多了[DEFINER = {user | CURRENT_USER}]这一选项,DEFINER用于指定存储过程的创建用户,可不写。IN表示该参数的值必须在调用存储过程时指定,用IN修饰的参数相当于函数中的形式参数;OUT表示该参数的值可以被存储过程改变,并且可以返回,用OUT修饰的参数相当于用于存储返回值的变量;INOUT表示该参数的调用时指定,并且可以被改变和返回。
例1. 创建一个不带参数的存储过程,用于显示数据库版本信息:
CREATE PROCEDURE showver() SELECT VERSION();
存储过程的调用方法是CALL sp_name([proc_parameter[, ...]]);
,若无参数,可省略小括号。
例2. 创建一个带有IN类型参数的存储过程,用于删除表users中的指定id的记录:
DELIMITER $$
CREATE PROCEDURE delUser(IN id SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE users.id = id;
END $$
DELIMITER ;
例3. 创建一个带有IN类型参数和OUT类型参数的存储过程,用于删除表users中的指定id的记录并返回剩余记录数量:
DELIMITER $$
CREATE PROCEDURE removeUser(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE users.id = id;
SELECT COUNT(id) FROM users INTO num;
END $$
DELIMITER ;
调用时使用命令CALL removeUser(8,@num);
。
删除存储过程的操作是DROP PROCEDURE [IF EXISTS] sp_name;
本文作学习交流用,如有错误,欢迎指正!