mysql基础(三)
来源:互联网 发布:ug编程自学能学会吗 编辑:程序博客网 时间:2024/06/05 03:53
MySQL实用函数:
这里是所有重要的 MySQL 函数的列表。每个函数都用合适的例子来说明如下。
MySQL Group By 子句 - MySQL的GROUP BY语句以及SQL聚合函数,用于类似SUM提供某些数据库表的列来分组结果数据集
MySQL IN 子句 - 这是一个子句,它可以用来连同任何MySQL查询语句以指定条件
MySQL BETWEEN 子句 - 这是一个子句,它可以用来与任何MySQL查询来指定条件
MySQL UNION关键字 - 使用UNION操作多个结果集组合成一个结果集
MySQL COUNT()函数 - MySQL的COUNT聚合函数用于计算一个数据库表中的行数
MySQL MAX() 函数 - MySQL的MAX聚合函数允许我们选择某些列的最高(最大)值
MySQL MIN()函数 - MySQL的MIN聚合函数允许我们选择某些列的最低(最小)值
MySQL AVG()函数 - MySQL的AVG聚合函数是用来对某些表的列求它的平均值
MySQL SUM()函数 - MySQL的SUM聚合函数允许选择某列的总和
MySQL SQRT函数 - 这是用来生成给定数的平方根
MySQL RAND()函数 - 使用MySQL命令产生一个随机数
MySQL CONCAT()函数 - 这是用来连接MySQL命令中的任何字符串
MySQL DATE 和 Time 时间日期函数 - MySQL日期和时间相关的函数完整列表
MySQL数字函数 - 在MySQL中操作数字的MySQL函数完整列表
MySQL字符串函数 - 在MySQL中的字符串操作的MySQL函数的完整列表
1.MySQL Group By 子句:
可以使用GROUP BY按列的值进行分组,并且,如果愿意的话,也可对列进行计算。可以使用COUNT, SUM, AVG等函数,在上页进行列的分组计算。
要了解GROUP BY子句,我们先考虑有一个 employee_tbl表,这具有以下记录:
mysql> create table employee_tabl( id int(10) not null primary key auto_increment, name varchar(32) not null default '', work_date date, daily_typing_pages int(10) default 0);Query OK, 0 rows affected (0.28 sec)INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('John', '2015-01-24', 150);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Ram', '2015-07-27', 220);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-05-06', 170);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-01-24', 100);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jill', '2015-01-24', 220);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-01-26', 300);INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-02-24', 350);
查询结果:
mysql> SELECT * FROM employee_tabl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2015-01-24 | 150 || 2 | Ram | 2015-05-27 | 220 || 3 | Jack | 2015-05-06 | 170 || 3 | Jack | 2015-04-06 | 100 || 4 | Jill | 2015-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在假定在上表的基础上,要算天每个员工做的工作数量。
如果我们如写一个SQL查询,那么我们将得到以下结果:
mysql> SELECT COUNT(*) FROM employee_tbl;+---------------------------+| COUNT(*) |+---------------------------+| 7 |+---------------------------+
但是,这不是我们的服务目的,我们要分页显示打印每个人的总数。这是通过使用联聚合函数GROUP BY子句如下:
mysql> SELECT name, COUNT(*) -> FROM employee_tbl -> GROUP BY name;+------+----------+| name | COUNT(*) |+------+----------+| Jack | 2 || Jill | 1 || John | 1 || Ram | 1 || Zara | 2 |+------+----------+5 rows in set (0.04 sec)
我们会看到更多相关 GROUP BY 的函数,如SUM,AVG等
2.MySQL IN 子句:
可以使用IN子句来取代许多OR条件
要了解IN子句,考虑下面的 employee_tbl 表,它有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在上面的基础上,假设要在上表中显示 daily_typing_pages 等于 250,220 和 170 的记录。这可以通过使用OR条件进行查询如下:
mysql>SELECT * FROM employee_tbl ->WHERE daily_typing_pages= 250 OR ->daily_typing_pages= 220 OR daily_typing_pages= 170; +------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 4 | Jill | 2007-04-06 | 220 |+------+------+------------+--------------------+4 rows in set (0.02 sec)
同样可以使用 IN 子句来实现如下:
mysql> SELECT * FROM employee_tbl -> WHERE daily_typing_pages IN ( 250, 220, 170 );+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 4 | Jill | 2007-04-06 | 220 |+------+------+------------+--------------------+4 rows in set (0.02 sec)
3.MySQL BETWEEN 子句:
可以使用BETWEEN子句来代替组合“大于等于小于等于”条件。
要理解子句,考虑一个employee_tbl表有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在假定上表的基础上,想获取条件为daily_typing_pages大于等于170并小于等于300记录。这可以使用>=和<=条件如下:
mysql>SELECT * FROM employee_tbl ->WHERE daily_typing_pages >= 170 AND ->daily_typing_pages <= 300;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 |+------+------+------------+--------------------+5 rows in set (0.03 sec)
同样可以使用 BETWEEN 子句来实现如下:
mysql> SELECT * FROM employee_tbl -> WHERE daily_typing_pages BETWEEN 170 AND 300; +------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 |+------+------+------------+--------------------+5 rows in set (0.03 sec)
4.MySQL UNION关键字:
使用UNION,如果想从几个表选择行一前一后的所有作为一个单一的结果集,或几个集合行在单一的表中。
UNION是从MySQL4.0开始使用。本节说明如何使用它。
假设有两个表,列出潜在和实际的客户,第三个表,列出供应商购买耗材,并且希望通过从所有三个表合并名称和地址,以创建一个单一的邮件列表。UNION提供了一种方法来做到这一点。假设这三个表具有以下内容:
mysql> SELECT * FROM prospect;+---------+-------+------------------------+| fname | lname | addr |+---------+-------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. |+---------+-------+------------------------+mysql> SELECT * FROM customer;+-----------+------------+---------------------+| last_name | first_name | address |+-----------+------------+---------------------+| Peterson | Grace | 16055 Seminole Ave. || Smith | Bernice | 916 Maple Dr. || Brown | Walter | 8602 1st St. |+-----------+------------+---------------------+mysql> SELECT * FROM vendor;+-------------------+---------------------+| company | street |+-------------------+---------------------+| ReddyParts, Inc. | 38 Industrial Blvd. || Parts-to-go, Ltd. | 213B Commerce Park. |+-------------------+---------------------+
这不要紧,如果所有的三个表都是不同的列名称。 以下查询说明如何从三个表一次全部选择名称和地址:
mysql> SELECT fname, lname, addr FROM prospect-> UNION-> SELECT first_name, last_name, address FROM customer-> UNION-> SELECT company, '', street FROM vendor;+-------------------+----------+------------------------+| fname | lname | addr |+-------------------+----------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. || Grace | Peterson | 16055 Seminole Ave. || Walter | Brown | 8602 1st St. || ReddyParts, Inc. | | 38 Industrial Blvd. || Parts-to-go, Ltd. | | 213B Commerce Park. |+-------------------+----------+------------------------+
如果想选择所有记录,包括重复的记录,请使用UNION关键字后面接一个 ALL 关键字:
mysql> SELECT fname, lname, addr FROM prospect-> UNION ALL-> SELECT first_name, last_name, address FROM customer-> UNION-> SELECT company, '', street FROM vendor;+-------------------+----------+------------------------+| fname | lname | addr |+-------------------+----------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. || Grace | Peterson | 16055 Seminole Ave. || Bernice | Smith | 916 Maple Dr. || Walter | Brown | 8602 1st St. || ReddyParts, Inc. | | 38 Industrial Blvd. || Parts-to-go, Ltd. | | 213B Commerce Park. |+-------------------+----------+------------------------+
5.MySQL COUNT()函数:
MySQL Count()函数是用于统计记录数的数量的最简单的函数, 这是由 SELECT 语句返回。
理解 Count() 函数,考虑一个 employee_tbl 表,它是具有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在假定在上表的基础上计算这个表中的行总数,那么如下所示:
mysql>SELECT COUNT(*) FROM employee_tbl ;+----------+| COUNT(*) |+----------+| 7 |+----------+1 row in set (0.01 sec)
同样,如果要计算 Zara 记录数,那么如下:
mysql>SELECT COUNT(*) FROM employee_tbl -> WHERE name="Zara";+----------+| COUNT(*) |+----------+| 2 |+----------+1 row in set (0.04 sec)
注:所有的SQL查询是不区分大小写,所以它没有任何区别,如果在WHERE条件给定ZARA 或 Zara。
6.MySQL Max()函数:
MySQL Max() 函数是用来找出记录集中最大值的记录。
要理解 Max() 函数,考虑 employee_tbl 表,它是具有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在,假设想获取上表中daily_typing_pages的字段的最大值对应的记录, 那么可以简单地使用下面的命令:
mysql> SELECT MAX(daily_typing_pages) -> FROM employee_tbl;+-------------------------+| MAX(daily_typing_pages) |+-------------------------+| 350 |+-------------------------+1 row in set (0.00 sec)
可以使用GROUP BY子句找到所有记录中最大值对应的名字如下:
mysql> SELECT id, name, MAX(daily_typing_pages) -> FROM employee_tbl GROUP BY name;+------+------+-------------------------+| id | name | MAX(daily_typing_pages) |+------+------+-------------------------+| 3 | Jack | 170 || 4 | Jill | 220 || 1 | John | 250 || 2 | Ram | 220 || 5 | Zara | 350 |+------+------+-------------------------+5 rows in set (0.00 sec)
可以使用 Min() 函数和 Max() 函数一起,找出最小值和最大值。试试下面的例子:
mysql> SELECT MIN(daily_typing_pages) least, MAX(daily_typing_pages) max -> FROM employee_tbl;+-------+------+| least | max |+-------+------+| 100 | 350 |+-------+------+1 row in set (0.01 sec)
7.MySQL MIN()函数:
MySQL的Min()函数用来找出记录集的最小值纪录。
理解Min()函数,考虑一个employee_tbl表,它是具有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在假设根据上表中,想获取daily_typing_pages的最小值,那么可以简单地使用下面的命令:
mysql> SELECT MIN(daily_typing_pages) -> FROM employee_tbl;+-------------------------+| MIN(daily_typing_pages) |+-------------------------+| 100 |+-------------------------+1 row in set (0.00 sec)
使用GROUP BY子句找到所有每个名称最小值的记录如下:
mysql>SELECT id, name, MIN(daily_typing_pages) -> FROM employee_tbl GROUP BY name;+------+------+-------------------------+| id | name | MIN(daily_typing_pages) |+------+------+-------------------------+| 3 | Jack | 100 || 4 | Jill | 220 || 1 | John | 250 || 2 | Ram | 220 || 5 | Zara | 300 |+------+------+-------------------------+5 rows in set (0.00 sec)
可以使用Min()函数和Max()函数一起,找出最小值和最大值。试试下面的例子:
mysql> SELECT MIN(daily_typing_pages) least, MAX(daily_typing_pages) max -> FROM employee_tbl;+-------+------+| least | max |+-------+------+| 100 | 350 |+-------+------+1 row in set (0.01 sec)
8.MySQL AVG()函数:
MySQL的AVG函数用来查找各种记录的一个字段的平均值。
为了理解AVG函数,考虑有一个employee_tbl表,其具有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在,假设在上述表的基础上计算所有dialy_typing_pages的平均值,那么可以使用下面的命令:
mysql> SELECT AVG(daily_typing_pages) -> FROM employee_tbl;+-------------------------+| AVG(daily_typing_pages) |+-------------------------+| 230.0000 |+-------------------------+1 row in set (0.03 sec)
使用 GROUP BY 子句设置各种记录的平均值。下面的例子将涉及到单个人的记录将有每个人的平均值打印出来。
mysql> SELECT name, AVG(daily_typing_pages) -> FROM employee_tbl GROUP BY name;+------+-------------------------+| name | AVG(daily_typing_pages) |+------+-------------------------+| Jack | 135.0000 || Jill | 220.0000 || John | 250.0000 || Ram | 220.0000 || Zara | 325.0000 |+------+-------------------------+5 rows in set (0.20 sec)
9.MySQL SUM()函数:
MySQL的Sum()函数是用来找出所有记录的一个字段的总和。
要理解SUM()函数,考虑一个employee_tbl表,它有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在,假设要在上述表折基础上计算所有dialy_typing_pages字段值的总和,那么可以使用下面的命令:
mysql> SELECT SUM(daily_typing_pages) -> FROM employee_tbl;+-------------------------+| SUM(daily_typing_pages) |+-------------------------+| 1610 |+-------------------------+1 row in set (0.00 sec)
也可以使用GROUP BY子句设置各个记录总和。下面的例子将计算所有涉及到一个人的记录的总和,并打印每个人总和信息。
mysql> SELECT name, SUM(daily_typing_pages) -> FROM employee_tbl GROUP BY name;+------+-------------------------+| name | SUM(daily_typing_pages) |+------+-------------------------+| Jack | 270 || Jill | 220 || John | 250 || Ram | 220 || Zara | 650 |+------+-------------------------+5 rows in set (0.17 sec)
10.MySQL SQRT()函数:
MySQL的SQRT()函数是用来找出任何数的平方根。使用SELECT语句来查找出任何数的平方根如下:
mysql> select SQRT(16);+----------+| SQRT(16) |+----------+| 4.000000 |+----------+1 row in set (0.00 sec)
在这里所看到的浮点值,是MySQL的内部操作平方根以浮点数据类型返回。
也可以使用Sqrt()函数找出各种记录平方根。要理解更详细Sqrt()函数,考虑anemployee_tbl表,其具有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
假设在上表基础上,要计算所有dialy_typing_pages列的值的平方根,那么可以使用下面的命令:
mysql> SELECT name, SQRT(daily_typing_pages) -> FROM employee_tbl;+------+--------------------------+| name | SQRT(daily_typing_pages) |+------+--------------------------+| John | 15.811388 || Ram | 14.832397 || Jack | 13.038405 || Jack | 10.000000 || Jill | 14.832397 || Zara | 17.320508 || Zara | 18.708287 |+------+--------------------------+7 rows in set (0.00 sec)
11.MySQL RAND()函数:
MySQL Rand() 函数可以被调用,以产生一个在 0 和 1 之间的随机数:
mysql> SELECT RAND( ), RAND( ), RAND( );+------------------+-----------------+------------------+| RAND( ) | RAND( ) | RAND( ) |+------------------+-----------------+------------------+| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |+------------------+-----------------+------------------+1 row in set (0.00 sec)
当使用整数作为参数调用时,RAND()使用该值作为随机数的种子发生器。每次种子使用给定值生成,RAND()将产生一个可重复的系列数字:
mysql> SELECT RAND(1), RAND( ), RAND( );+------------------+------------------+------------------+| RAND(1 ) | RAND( ) | RAND( ) |+------------------+------------------+------------------+| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |+------------------+------------------+------------------+1 row in set (0.00 sec)
可以使用ORDER BY RAND()进行随机行集或值,如下:
要理解ORDER BY RAND()函数,考虑一个employee_tbl表,有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在,使用下面的命令:
mysql> SELECT * FROM employee_tbl ORDER BY RAND();+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 5 | Zara | 2007-06-06 | 300 || 3 | Jack | 2007-04-06 | 100 || 3 | Jack | 2007-05-06 | 170 || 2 | Ram | 2007-05-27 | 220 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-02-06 | 350 || 1 | John | 2007-01-24 | 250 |+------+------+------------+--------------------+7 rows in set (0.01 sec)mysql> SELECT * FROM employee_tbl ORDER BY RAND();+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 5 | Zara | 2007-02-06 | 350 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-04-06 | 100 || 1 | John | 2007-01-24 | 250 || 4 | Jill | 2007-04-06 | 220 || 3 | Jack | 2007-05-06 | 170 || 5 | Zara | 2007-06-06 | 300 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
12.MySQL CONCAT()函数:
MySQL的Concat()函数是用来连接两个字符串,形成一个字符串。试试下面的例子:
mysql> SELECT CONCAT('FIRST ', 'SECOND');+----------------------------+| CONCAT('FIRST ', 'SECOND') |+----------------------------+| FIRST SECOND |+----------------------------+1 row in set (0.00 sec)
要理解 CONCAT() 函数更详细信息,考虑employee_tbl表,它有以下记录:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
现在假设在上表的基础上,要连接所有员工的名字,id 和 work_date,那么可以使用以下命令做到这一点:
mysql> SELECT CONCAT(id, name, work_date) -> FROM employee_tbl;+-----------------------------+| CONCAT(id, name, work_date) |+-----------------------------+| 1John2007-01-24 || 2Ram2007-05-27 || 3Jack2007-05-06 || 3Jack2007-04-06 || 4Jill2007-04-06 || 5Zara2007-06-06 || 5Zara2007-02-06 |+-----------------------------+7 rows in set (0.00 sec)
13.MySQL DATE和Time的时间日期函数:
函数名称 描述
14.MySQL数字函数:
15.MySQL字符串函数:
- mysql基础(三)
- mysql基础(三)
- MySQL基础(三)
- MySQL基础(三)
- mysql基础篇(三)
- Mysql基础技能(三)
- MySQL基础语法(三)
- MySQL基础笔记(三) 复杂查询
- mysql面试必会基础(三)
- mysql基础---字段管理(三)
- MySQL基础笔记(三) 复杂查询
- MySQL总结—基础篇(三)
- mysql基础条件查询(三)
- (三)MySql基础之视图
- mysql基础(三) Mysql锁机制简介
- mysql基础---数据库设计(三大范式)(八)
- 在linux下使用mysql的基础操作(三)
- MYSQL基础上机练习题(三) 数据查询
- Xilinx中的xapp1052理解
- C语言 位运算
- OpenCV第一课
- hdu 1076 An Easy Task
- 两种创建对象的方法
- mysql基础(三)
- Educational Codeforces Round 12 D. Simple Subset
- 观察者模式
- 机器学习之单变量线性回归(Linear Regression with One Variable)
- /*外接矩形部分*/
- 诡异的Android开发中EditText键盘弹出后被遮盖的bug
- 【Linux C 王者归来】【第四章】【指针与字符串】
- hdu 1058 丑数?
- android 语音合成的简单实现