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;

posted @ 2017-03-06 18:09  jyau  阅读(437)  评论(0编辑  收藏  举报