MYSQL数据库基本操作
1.登录数据库
mysql -u root -p
2.显示数据库
show Databases;
3.选择数据库
use 数据库名称；
4.创建数据库
CREATE DATABASE 数据库名；
5.创建表
CREATE TABLE 表名称（
****
****
）；
6.显示选择数据库中的表
show tables;
7.显示数据库表的属性，名称，是否为null，
show columns from 表名称；
8.显示数据库是否存在主键，约束
show index from 表名称；
9.读取数据表
select * from 表名称；
10.初始化表，为表添加数据
INSERT INTO 表名称（字段，字段，字段...） values （值，值，值....）；
11.退出数据库
quit exit;
12.MySQL删除数据库
使用drop 命令删除数据库
drop 命令格式：
drop database 数据库名称；
MySQL 数据类型
1.数值类型
2.日期和时间类型
3.字符串类型
浮点型（float和double）
MySQL 创建数据表
语法
CREATE TABLE table_name(column_name column_type);
MySQL删除数据表
语法
DROP TABLE table_name;
MSQ 插入数据
语法
INSERT INTO table_name (field1,filed2.....filedN) VALUES (value1,value2....valueN);
如果数据是字符型，必须使用单引号或者双引号，如：“value”;
MySQL查询数据
MySQL WHERE 字句
我们知道从MySQL表中使用SQL SELECT语句来读取数据
如需有条件地从表中读取数据，可将WHERE字句添加到SELECT语句中
语法：
SELECT field1，field2,...fieldN FROM table_name1,table)name2...
MySQL UPTATE 查询
如果我们需要修改或更新MySQL中的数据，我们可以使用SQL UPDARE来操作。
语法
实例：
MySQL DELETE 语句
MySQL LIKE子句
MySQL UNION 操作符
MySQL 排序
MySQL 分组
MySQL连接的使用
使用MySQL 的JOIN在两个或多个表中查询数据
你可以在select，update和dalete语句中使用Mysql的JOIN来联合多表查询。
JOIN按功能大致分为如下三类：
INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录；
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的纪录；
RIGTH JOIN（右连接）：与LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应的匹配的记录。
MySQL NULL 值处理
我们已经知道MySQL使用SQLSELECT命令及WHERE字句来读取数据表中的数据，但是当提供的查询条件字段为NULL时，该命令可能就无法正常工作。
为了处理这种情况。MySQL提供了三大运算符：
IS NULL ：当列的值是NULL，此运算符返回true
IS NOT NULL：当列的值不为NULL，运算符返回true
<=>：比较操作符（不同于=运算符），当比较的两个值为NULL时返回true
关于NULL的条件比较运算符是比较特殊的，你不能使用=NULL或!=NULL 在列中查找NULL值。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false 即NULL = NULL 返回false。
MySQL中处理NULL使用IS NULL 和IS NOT NULL 运算符。
MySQL正则表达式
在前面我们了解到MySQL可以通你通过LIKE ...%来进行模糊匹配。
MySQL同样也支持其它正则表达式的匹配，MySQL中使用REGEXP操作符来进行正则表达式匹配。MySQL的正则表达式匹配与这些脚本的类似，下表中的正则模式可应用于REGEXP操作符中。
MySQL事务
mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from runoob_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>
MySQL ALTER 命令
当我们需要修改数据表明或修改数据字段，就需要使用到MySQL命令。
MySQL索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
这是最基本的所有，它没有任何限制，它有一下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构（添加索引）
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName](username(length)) );
删除索引的语法
DROP INDEX [indexName] ON mytable;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一，它有一下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName](username(length))
创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username BARCHAR(16) NOT NULL, UNIQUE [indexName](username(length));
MySQL临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
创建临时表和删除临时表
mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> (\'cucumber\', 100.25, 90, 2); mysql> SELECT * FROM SalesSummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SalesSummary; mysql> SELECT * FROM SalesSummary; ERROR 1146: Table \'RUNOOB.SalesSummary\' doesn\'t exist
MySQL复制表
如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。
本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
- 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。
实例
尝试以下实例来复制表 runoob_tbl 。
步骤一：
获取数据表的完整结构。
mysql> SHOW CREATE TABLE runoob_tbl \G; *************************** 1. row *************************** Table: runoob_tbl Create Table: CREATE TABLE `runoob_tbl` ( `runoob_id` int(11) NOT NULL auto_increment, `runoob_title` varchar(100) NOT NULL default \'\', `runoob_author` varchar(40) NOT NULL default \'\', `submission_date` date default NULL, PRIMARY KEY (`runoob_id`), UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`) ) ENGINE=InnoDB 1 row in set (0.00 sec) ERROR: No query specified
步骤二：
修改SQL语句的数据表名，并执行SQL语句
mysql> CREATE TABLE `clone_tbl` ( -> `runoob_id` int(11) NOT NULL auto_increment, -> `runoob_title` varchar(100) NOT NULL default \'\', -> `runoob_author` varchar(40) NOT NULL default \'\', -> `submission_date` date default NULL, -> PRIMARY KEY (`runoob_id`), -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (1.80 sec)
步骤三：
执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id, -> runoob_title, -> runoob_author, -> submission_date) -> SELECT runoob_id,runoob_title, -> runoob_author,submission_date -> FROM runoob_tbl; Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0
MySQL元数据
你可能想知道MySQL以下三种信息：
- 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
- 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
- MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。
在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。 但如果使用Perl或PHP等脚本语言，你就需要调用特定的接口函数来获取。 接下来我们会详细介绍。
MySQL序列使用
MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
本章我们将介绍如何使用MySQL的序列。
使用 AUTO_INCREMENT
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
实例
以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES -> (NULL,\'housefly\',\'2001-09-10\',\'kitchen\'), -> (NULL,\'millipede\',\'2001-09-10\',\'driveway\'), -> (NULL,\'grasshopper\',\'2001-09-10\',\'front yard\'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM insect ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec)
重置序列
如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：
mysql> ALTER TABLE insect DROP id; mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> ADD PRIMARY KEY (id);
设置序列的开始值
一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL, -> origin VARCHAR(30) NOT NULL )engine=innodb auto_increment=100 charset=utf8;
或者你也可以在表创建成功后，通过以下语句来实现：
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL处理重复数据
有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。
本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。
防止表中出现重复数据
你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );
如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：
1 mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) 2 -> VALUES( \'Jay\', \'Thomas\'); 3 Query OK, 1 row affected (0.00 sec) 4 mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) 5 -> VALUES( \'Jay\', \'Thomas\'); 6 Query OK, 0 rows affected (0.00 sec)
NSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：
1 CREATE TABLE person_tbl 2 ( 3 first_name CHAR(20) NOT NULL, 4 last_name CHAR(20) NOT NULL, 5 sex CHAR(10), 6 UNIQUE (last_name, first_name) 7 );
统计重复数据
以下我们将统计表中 first_name 和 last_name的重复记录数：
1 mysql> SELECT COUNT(*) as repetitions, last_name, first_name 2 -> FROM person_tbl 3 -> GROUP BY last_name, first_name 4 -> HAVING repetitions > 1;
以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：
- 确定哪一列包含的值可能会重复。
- 在列选择列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句设置重复数大于1。
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
1 mysql> SELECT DISTINCT last_name, first_name 2 -> FROM person_tbl;
删除重复数据
如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
MySQL导出导入数据
-
一、将数据导入到指定的数据库
第一种导入方式：
-
（linux下和Windows 下语法是一样的，只是路劲的书写方式不同而已）
1、创建一个空数据库
2、进入MySQL安装目录bin下
3、导入命令 mysql -uroot –p密码 数据库名 < 要导入的表数据（d:\bbs.sql）(将选定的表导入到指定的数据库)
mysql -uroot –padmin databaseName <d:\dump.sql （window）
-
mysql -uroot –padmin databaseName < /dump.sql （Linux）
- （将备份的整个数据库导入）
4、导入命令： mysql -u用户名 -p密码 < 要导入的数据库数据
mysql -uroot -padmin < d:\dump.sql (window)
mysql -uroot -padmin < /dump.sql (linux)
(将备份出来的数据库导入，这里没有写数据库名，也可以写但要求是要写同样的名称)
二、从数据库导出数据
第二种方式 使用 ： select into outfile ：SQL命令如下
(这种格式也是在linux下，window修改路劲书写方式)
// 将你选中的数据导出到这个指定的目录下 select * into outfile \'/tmp/bump.txt\' // 属性使用 逗号分开 fields terminated by \',\' // optionally enclosed by \' " \' // 一行结束的标志位换行符 lines terminated by \'\n\' // 这是你要导出的数据库表 from linuxtest.test2 ;
MySQL运算符
MySQL的运算符及运算符的优先级，MySQL主要有以下几种运算符：
算术运算符
比较运算符
逻辑运算符
位运算符
算术运算符
MySQL 支持的算术运算符包括:
| 运算符 | 作用 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / 或 DIV | 除法 |
| % 或 MOD | 取余 |
在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL
1、加
mysql> select 1+2; +-----+ | 1+2 | +-----+ | 3 | +-----+
2、减
mysql> select 1-2; +-----+ | 1-2 | +-----+ | -1 | +-----+
3.乘
mysql> select 2*3; +-----+ | 2*3 | +-----+ | 6 | +-----+
4.除
mysql> select 2/3; +--------+ | 2/3 | +--------+ | 0.6667 | +--------+
5、商
mysql> select 10 DIV 4; +----------+ | 10 DIV 4 | +----------+ | 2 | +----------+
6、取余
mysql> select 10 MOD 4; +----------+ | 10 MOD 4 | +----------+ | 2 | +----------+
比较运算符
SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| <>, != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | >=min&&<=max |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
1.等于
mysql> select 2=3; +-----+ | 2=3 | +-----+ | 0 | +-----+ mysql> select NULL = NULL; +-------------+ | NULL = NULL | +-------------+ | NULL | +-------------+
2.不等于
mysql> select 2<>3; +------+ | 2<>3 | +------+ | 1 | +------+
3.安全等于
与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。
mysql> select 2<=>3; +-------+ | 2<=>3 | +-------+ | 0 | +-------+ mysql> select null=null; +-----------+ | null=null | +-----------+ | NULL | +-----------+ mysql> select null<=>null; +-------------+ | null<=>null | +-------------+ | 1 | +-------------+
4.小于
mysql> select 2<3; +-----+ | 2<3 | +-----+ | 1 | +-----+
5.小于等于
mysql> select 2<=3; +------+ | 2<=3 | +------+ | 1 | +------+
6.大于
mysql> select 2>3; +-----+ | 2>3 | +-----+ | 0 | +-----+
7.大于等于
mysql> select 2>=3; +------+ | 2>=3 | +------+ | 0 | +------+
8.BETWEEN
mysql> select 5 between 1 and 10; +--------------------+ | 5 between 1 and 10 | +--------------------+ | 1 | +--------------------+
9.IN
mysql> select 5 in (1,2,3,4,5); +------------------+ | 5 in (1,2,3,4,5) | +------------------+ | 1 | +------------------+
10.NOT IN
mysql> select 5 not in (1,2,3,4,5); +----------------------+ | 5 not in (1,2,3,4,5) | +----------------------+ | 0 | +----------------------+
11. IS NULL
mysql> select null is NULL; +--------------+ | null is NULL | +--------------+ | 1 | +--------------+ mysql> select \'a\' is NULL; +-------------+ | \'a\' is NULL | +-------------+ | 0 | +-------------+
12.IS NOT NULL
mysql> select null IS NOT NULL; +------------------+ | null IS NOT NULL | +------------------+ | 0 | +------------------+ mysql> select \'a\' IS NOT NULL; +-----------------+ | \'a\' IS NOT NULL | +-----------------+ | 1 | +-----------------+
13.LIKE
mysql> select \'12345\' like \'12%\'; +--------------------+ | \'12345\' like \'12%\' | +--------------------+ | 1 | +--------------------+ mysql> select \'12345\' like \'12_\'; +--------------------+ | \'12345\' like \'12_\' | +--------------------+ | 0 | +--------------------+
14.REGEXP
mysql> select \'beijing\' REGEXP \'jing\'; +-------------------------+ | \'beijing\' REGEXP \'jing\' | +-------------------------+ | 1 | +-------------------------+ mysql> select \'beijing\' REGEXP \'xi\'; +-----------------------+ | \'beijing\' REGEXP \'xi\' | +-----------------------+ | 0 | +-----------------------+
逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
| 运算符号 | 作用 |
|---|---|
| NOT 或 ! | 逻辑非 |
| AND | 逻辑与 |
| OR | 逻辑或 |
| XOR | 逻辑异或 |
1.与
mysql> select 2 and 0; +---------+ | 2 and 0 | +---------+ | 0 | +---------+ mysql> select 2 and 1; +---------+ | 2 and 1 | +---------+ | 1 | +---------+
2.或
mysql> select 2 or 0; +--------+ | 2 or 0 | +--------+ | 1 | +--------+ mysql> select 2 or 1; +--------+ | 2 or 1 | +--------+ | 1 | +--------+ mysql> select 0 or 0; +--------+ | 0 or 0 | +--------+ | 0 | +--------+ mysql> select 1 || 0; +--------+ | 1 || 0 | +--------+ | 1 | +--------+
3.非
mysql> select not 1; +-------+ | not 1 | +-------+ | 0 | +-------+ mysql> select !0; +----+ | !0 | +----+ | 1 | +----+
4.异或
mysql> select 1 xor 1; +---------+ | 1 xor 1 | +---------+ | 0 | +---------+ mysql> select 0 xor 0; +---------+ | 0 xor 0 | +---------+ | 0 | +---------+ mysql> select 1 xor 0; +---------+ | 1 xor 0 | +---------+ | 1 | +---------+ mysql> select null or 1; +-----------+ | null or 1 | +-----------+ | 1 | +-----------+ mysql> select 1 ^ 0; +-------+ | 1 ^ 0 | +-------+ | 1 | +-------+
位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。
| 运算符号 | 作用 |
|---|---|
| & | 按位与 |
| | | 按位或 |
| ^ | 按位异或 |
| ! | 取反 |
| << | 左移 |
| >> | 右移 |
1.按位与
mysql> select 3&5; +-----+ | 3&5 | +-----+ | 1 | +-----+
2.按位或
mysql> select 3|5; +-----+ | 3|5 | +-----+ | 7 | +-----+
3.按位异或
mysql> select 3^5; +-----+ | 3^5 | +-----+ | 6 | +-----+
4.按位取反
mysql> select ~18446744073709551612; +-----------------------+ | ~18446744073709551612 | +-----------------------+ | 3 | +-----------------------+
5.按位右移
mysql> select 3>>1; +------+ | 3>>1 | +------+ | 1 | +------+
6.按位左移
mysql> select 3<<1; +------+ | 3<<1 | +------+ | 6 | +------+
MySQL 函数
MySQL 有很多内置的函数，以下列出了这些函数的说明。
MySQL 字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 |
返回 CustomerName 字段第一个字母的 ASCII 码： SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
|
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 |
返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
|
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数 |
返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
|
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 |
合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
|
| CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符 |
合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
|
| FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 |
返回字符串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e");
|
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 |
返回字符串 c 在指定字符串中的位置： SELECT FIND_IN_SET("c", "a,b,c,d,e");
|
| FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 |
格式化数字 "#,###.##" 形式： SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
|
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 |
从字符串第一个位置开始的 6 个字符替换为 runoob： SELECT INSERT("google.com", 1, 6, "runnob"); -- 输出：runoob.com
|
| LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 |
获取 b 在字符串 abc 中的位置： SELECT LOCATE(\'st\',\'myteststring\'); -- 5
返回字符串 abc 中 b 的位置： SELECT LOCATE(\'b\', \'abc\') -- 2
|
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 RUNOOB 转换为小写： SELECT LOWER(\'RUNOOB\') -- runoob
|
| LEFT(s,n) | 返回字符串 s 的前 n 个字符 |
返回字符串 runoob 中的前两个字符： SELECT LEFT(\'runoob\',2) -- ru
|
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 RUNOOB 转换为小写： SELECT LOWER(\'RUNOOB\') -- runoob
|
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len |
将字符串 xx 填充到 abc 字符串的开始处： SELECT LPAD(\'abc\',5,\'xx\') -- xxabc
|
| LTRIM(s) | 去掉字符串 s 开始处的空格 |
去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
|
| MID(s,n,len) | 从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len) |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
|
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 |
返回字符串 abc 中 b 的位置： SELECT POSITION(\'b\' in \'abc\') -- 2
|
| REPEAT(s,n) | 将字符串 s 重复 n 次 |
将字符串 runoob 重复三次： SELECT REPEAT(\'runoob\',3) -- runoobrunoobrunoob
|
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 |
将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE(\'abc\',\'a\',\'x\') --xbc
|
| REVERSE(s) | 将字符串s的顺序反过来 |
将字符串 abc 的顺序反过来： SELECT REVERSE(\'abc\') -- cba
|
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 |
返回字符串 runoob 的后两个字符： SELECT RIGHT(\'runoob\',2) -- ob
|
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len |
将字符串 xx 填充到 abc 字符串的结尾处： SELECT RPAD(\'abc\',5,\'xx\') -- abcxx
|
| RTRIM(s) | 去掉字符串 s 结尾处的空格 |
去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
|
| SPACE(n) | 返回 n 个空格 |
返回 10 个空格： SELECT SPACE(10);
|
| STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 |
比较字符串： SELECT STRCMP("runoob", "runoob"); -- 0
|
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
|
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
|
| SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 |
SELECT SUBSTRING_INDEX(\'a*b\',\'*\',1) -- a
SELECT SUBSTRING_INDEX(\'a*b\',\'*\',-1) -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(\'a*b*c*d*e\',\'*\',3),\'*\',-1) -- c
|
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 |
去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(\' RUNOOB \') AS TrimmedString;
|
| UCASE(s) | 将字符串转换为大写 |
将字符串 runoob 转换为大写： SELECT UCASE("runoob"); -- RUNOOB
|
| UPPER(s) | 将字符串转换为大写 |
将字符串 runoob 转换为大写： SELECT UPPER("runoob"); -- RUNOOB
|
MySQL 数字函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) | 返回 x 的绝对值 |
返回 -1 的绝对值： SELECT ABS(-1) -- 返回1
|
| ACOS(x) | 求 x 的反余弦值(参数是弧度) |
SELECT ACOS(0.25);
|
| ASIN(x) | 求反正弦值(参数是弧度) |
SELECT ASIN(0.25);
|
| ATAN(x) | 求反正切值(参数是弧度) |
SELECT ATAN(2.5);
|
| ATAN2(n, m) | 求反正切值(参数是弧度) |
SELECT ATAN2(-0.8, 2);
|
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 |
返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products;
|
| CEIL(x) | 返回大于或等于 x 的最小整数 |
SELECT CEIL(1.5) -- 返回2
|
| CEILING(x) | 返回大于或等于 x 的最小整数 |
SELECT CEIL(1.5) -- 返回2
|
| COS(x) | 求余弦值(参数是弧度) |
SELECT COS(2);
|
| COT(x) | 求余切值(参数是弧度) |
SELECT COT(6);
|
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 |
返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
|
| DEGREES(x) | 将弧度转换为角度 |
SELECT DEGREES(3.1415926535898) -- 180
|
| n DIV m | 整除，n 为被除数，m 为除数 |
计算 10 除于 5： SELECT 10 DIV 5; -- 2
|
| EXP(x) | 返回 e 的 x 次方 |
计算 e 的三次方： SELECT EXP(3) -- 20.085536923188
|
| FLOOR(x) | 返回小于或等于 x 的最大整数 |
小于或等于 1.5 的整数： SELECT FLOOR(1.5) -- 返回1
|
| GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 |
返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); -- 34
返回以下字符串列表中的最大值： SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
|
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 |
返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); -- 3
返回以下字符串列表中的最小值： SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
|
| LN | 返回数字的自然对数 |
返回 2 的自然对数： SELECT LN(2); -- 0.6931471805599453
|
| LOG(x) | 返回自然对数(以 e 为底的对数) |
SELECT LOG(20.085536923188) -- 3
|
| LOG10(x) | 返回以 10 为底的对数 |
SELECT LOG10(100) -- 2
|
| LOG2(x) | 返回以 2 为底的对数 |
返回以 2 为底 6 的对数： SELECT LOG2(6); -- 2.584962500721156
|
| MAX(expression) | 返回字段 expression 中的最大值 |
返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
|
| MIN(expression) | 返回字段 expression 中的最小值 |
返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS LargestPrice FROM Products;
|
| MOD(x,y) | 返回 x 除以 y 以后的余数 |
5 除于 2 的余数： SELECT MOD(5,2) -- 1
|
| PI() | 返回圆周率(3.141593） |
SELECT PI() --3.141593
|
| POW(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： SELECT POW(2,3) -- 8
|
| POWER(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： SELECT POWER(2,3) -- 8
|
| RADIANS(x) | 将角度转换为弧度 |
180 度转换为弧度： SELECT RADIANS(180) -- 3.1415926535898
|
| RAND() | 返回 0 到 1 的随机数 |
SELECT RAND() --0.93099315644334
|
| ROUND(x) | 返回离 x 最近的整数 |
SELECT ROUND(1.23456) --1
|
| SIGN(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 |
SELECT SIGN(-10) -- (-1)
|
| SIN(x) | 求正弦值(参数是弧度) |
SELECT SIN(RADIANS(30)) -- 0.5
|
| SQRT(x) | 返回x的平方根 |
25 的平方根： SELECT SQRT(25) -- 5
|
| SUM(expression) | 返回指定字段的总和 |
计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
|
| TAN(x) | 求正切值(参数是弧度) |
SELECT TAN(1.75); -- -5.52037992250933
|
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） |
SELECT TRUNCATE(1.23456,3) -- 1.234
|
MySQL 日期函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 |
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
|
| ADDTIME(t,n) | 时间 t 加上 n 秒的时间 |
SELECT ADDTIME(\'2011-11-11 11:11:11\', 5)
->2011-11-11 11:11:16 (秒)
|
| CURDATE() | 返回当前日期 |
SELECT CURDATE();
-> 2018-09-19
|
| CURRENT_DATE() | 返回当前日期 |
SELECT CURRENT_DATE();
-> 2018-09-19
|
| CURRENT_TIME | 返回当前时间 |
SELECT CURRENT_TIME();
-> 19:59:02
|
| CURRENT_TIMESTAMP() | 返回当前日期和时间 |
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
|
| CURTIME() | 返回当前时间 |
SELECT CURTIME();
-> 19:59:02
|
| DATE() | 从日期或日期时间表达式中提取日期值 |
SELECT DATE("2017-06-15");
-> 2017-06-15
|
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 |
SELECT DATEDIFF(\'2001-01-01\',\'2001-02-02\')
-> -32
|
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期 |
SELECT ADDDATE(\'2011-11-11 11:11:11\',1)
-> 2011-11-12 11:11:11 (默认是天)
SELECT ADDDATE(\'2011-11-11 11:11:11\', INTERVAL 5 MINUTE)
-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
|
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d |
SELECT DATE_FORMAT(\'2011-11-11 11:11:11\',\'%Y-%m-%d %r\')
-> 2011-11-11 11:11:11 AM
|
| DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 |
Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
|
| DAY(d) | 返回日期值 d 的日期部分 |
SELECT DAY("2017-06-15");
-> 15
|
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday |
SELECT DAYNAME(\'2011-11-11 11:11:11\')
->Friday
|
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 |
SELECT DAYOFMONTH(\'2011-11-11 11:11:11\')
->11
|
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 |
SELECT DAYOFWEEK(\'2011-11-11 11:11:11\')
->6
|
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 |
SELECT DAYOFYEAR(\'2011-11-11 11:11:11\')
->315
|
| EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为：
|
SELECT EXTRACT(MINUTE FROM \'2011-11-11 11:11:11\')
-> 11
|
| FROM_DAYS(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 |
SELECT FROM_DAYS(1111)
-> 0003-01-16
|
| HOUR(t) | 返回 t 中的小时值 |
SELECT HOUR(\'1:2:3\')
-> 1
|
| LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 |
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
|
| LOCALTIME() | 返回当前日期和时间 |
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
|
| LOCALTIMESTAMP() | 返回当前日期和时间 |
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
|
| MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 |
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
|
| MAKETIME(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 |
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
|
| MICROSECOND(date) | 返回日期参数所对应的毫秒数 |
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
|
| MINUTE(t) | 返回 t 中的分钟值 |
SELECT MINUTE(\'1:2:3\')
-> 2
|
| MONTHNAME(d) | 返回日期当中的月份名称，如 Janyary |
SELECT MONTHNAME(\'2011-11-11 11:11:11\')
-> November
|
| MONTH(d) | 返回日期d中的月份值，1 到 12 |
SELECT MONTH(\'2011-11-11 11:11:11\')
->11
|
| NOW() | 返回当前日期和时间 |
SELECT NOW()
-> 2018-09-19 20:57:43
|
| PERIOD_ADD(period, number) | 为 年-月 组合日期添加一个时段 |
SELECT PERIOD_ADD(201703, 5);
-> 201708
|
| PERIOD_DIFF(period1, period2) | 返回两个时段之间的月份差值 |
SELECT PERIOD_DIFF(201710, 201703);
-> 7
|
| QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 |
SELECT QUARTER(\'2011-11-11 11:11:11\')
-> 4
|
| SECOND(t) | 返回 t 中的秒钟值 |
SELECT SECOND(\'1:2:3\')
-> 3
|
| SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 |
SELECT SEC_TO_TIME(4320)
-> 01:12:00
|
| STR_TO_DATE(string, format_mask) | 将字符串转变为日期 |
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
|
| SUBDATE(d,n) | 日期 d 减去 n 天后的日期 |
SELECT SUBDATE(\'2011-11-11 11:11:11\', 1)
->2011-11-10 11:11:11 (默认是天)
|
| SUBTIME(t,n) | 时间 t 减去 n 秒的时间 |
SELECT SUBTIME(\'2011-11-11 11:11:11\', 5)
->2011-11-11 11:11:06 (秒)
|
| SYSDATE() | 返回当前日期和时间 |
SELECT SYSDATE()
-> 2018-09-19 20:57:43
|
| TIME(expression) | 提取传入表达式的时间部分 |
SELECT TIME("19:30:10");
-> 19:30:10
|
| TIME_FORMAT(t,f) | 按表达式 f 的要求显示时间 t |
SELECT TIME_FORMAT(\'11:11:11\',\'%r\')
11:11:11 AM
|
| TIME_TO_SEC(t) | 将时间 t 转换为秒 |
SELECT TIME_TO_SEC(\'1:12:00\')
-> 4320
|
| TIMEDIFF(time1, time2) | 计算时间差值 |
SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
|
| TIMESTAMP(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 |
SELECT TIMESTAMP("2017-07-23", "13:10:11");
-> 2017-07-23 13:10:11
|
| TO_DAYS(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 |
SELECT TO_DAYS(\'0001-01-01 01:01:01\')
-> 366
|
| WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
SELECT WEEK(\'2011-11-11 11:11:11\')
-> 45
|
| WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 |
SELECT WEEKDAY("2017-06-15");
-> 3
|
| WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
SELECT WEEKOFYEAR(\'2011-11-11 11:11:11\')
-> 45
|
| YEAR(d) | 返回年份 |
SELECT YEAR("2017-06-15");
-> 2017
|
| YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 |
SELECT YEARWEEK("2017-06-15");
-> 201724
|
MySQL 高级函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) | 返回 x 的二进制编码 |
15 的 2 进制编码: SELECT BIN(15); -- 1111
|
| BINARY(s) | 将字符串 s 转换为二进制字符串 |
SELECT BINARY "RUNOOB";
-> RUNOOB
|
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END
|
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 |
SELECT CASE
WHEN 1 > 0
THEN \'1 > 0\'
WHEN 2 > 0
THEN \'2 > 0\'
ELSE \'3 > 0\'
END
->1 > 0
|
| CAST(x AS type) | 转换数据类型 |
字符串日期转换为日期： SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
|
| COALESCE(expr1, expr2, ...., expr_n) | 返回参数中的第一个非空表达式（从左向右） |
SELECT COALESCE(NULL, NULL, NULL, \'runoob.com\', NULL, \'google.com\');
-> runoob.com
|
| CONNECTION_ID() | 返回服务器的连接数 |
SELECT CONNECTION_ID();
-> 4292835
|
| CONV(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 |
SELECT CONV(15, 10, 2);
-> 1111
|
| CONVERT(s USING cs) | 函数将字符串 s 的字符集变成 cs |
SELECT CHARSET(\'ABC\')
->utf-8
SELECT CHARSET(CONVERT(\'ABC\' USING gbk))
->gbk
|
| CURRENT_USER() | 返回当前用户 |
SELECT CURRENT_USER();
-> guest@%
|
| DATABASE() | 返回当前数据库名 |
SELECT DATABASE();
-> runoob
|
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 |
SELECT IF(1 > 0,\'正确\',\'错误\')
->正确
|
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
SELECT IFNULL(null,\'Hello Word\')
->Hello Word
|
| ISNULL(expression) | 判断表达式是否为 NULL |
SELECT ISNULL(NULL);
->1
|
| LAST_INSERT_ID() | 返回最近生成的 AUTO_INCREMENT 值 |
SELECT LAST_INSERT_ID();
->6
|
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 |
SELECT NULLIF(25, 25);
->
|
| SESSION_USER() | 返回当前用户 |
SELECT SESSION_USER();
-> guest@%
|
| SYSTEM_USER() | 返回当前用户 |
SELECT SYSTEM_USER();
-> guest@%
|
| USER() | 返回当前用户 |
SELECT USER();
-> guest@%
|
| VERSION() | 返回数据库的版本号 |
SELECT VERSION()
-> 5.6.34
|
需求：将数据库中每分钟一条的数据表，从 9:30 取到 22:00 ，以半小时为单位汇总，并输出 Excel。
数据表字段：id（序号）、incount（计数）、cdate（数据时间）
表名：m_temp
难点：时间处理
解决办法：使用 DATE_FORMAT、CONCAT、Date、Hour、Minute、Floor 函数将时间处理成半小时，在将这段时间的数据查询时 输出 cdate 统一变为 09:30，然后在 group by cdate ，用 sum（incount）求和。
1.处理时间：
示例：2018-08-21 09:30:00 至 2018-08-21 22:00:00
第一个半小时 09:30 至 10:00
将这段时间的数据查询时 输出 cdate 统一变为 09:30，然后在 group by cdate ，sum（incount）
将分钟取出 /30 , 0-29 分结果为 0 ，30-59分结果为 1 再乘以 30 分钟即变为 00 或者 30
date(cdate)，取出年月日；
hour(cdate)，取出小时；
minute(cdate)，取出分钟；
计算（minute(cdate)）/30 )*30，结果为 0 或30；
floor( （minute(cdate)）/30 )*30)， 处理成整数；
concat(date(cdate),\' \',hour(cdate),\':\',floor( （minute(cdate)）/30 )*30) ，按照日期格式进行拼接
DATE_FORMAT( concat(date(cdate),\' \',hour(cdate),\':\',floor( minute(cdate)/30 )*30+12) ,\'%Y-%m-%d %H:%i\') ，转换成date类型
完整 SQL：
select sum(incount),dataStartTime from ( select incount, DATE_FORMAT( concat(date(cdate),\' \',hour(cdate),\':\',floor( minute(cdate)/30 )*30+12) ,\'%Y-%m-%d %H:%i\') as dataStartTime from m_temp WHERE cdate>=\'2018-08-21 09:30\' and cdate<=\'2018-08-21 22:00\' ORDER BY dataStartTime ) a group by DATE_FORMAT( dataStartTime ,\'%Y-%m-%d %H:%i\') into outfile(\'c:/2018-08-21Data.xls\');