mysql中查看函数定义

常用命令

1创建用户test,密码为password该用户没有任何权限

create user test@localhost identified by 'password';

2指定用户拥有创建表的权限远程 %可以换成ip

grant all on database.* to 'test'@'%' identified by 'password';
grant select,insert,update,delete,create,drop on database.* to test@localhost identified by "test";

3.刷新数据库

flush privileges;

4.查看用户信息

select host,user from mysql.user;

cmd mysql的bin目录下,使用mysql命令备份，还原能避免一些报错，解决兼容问题

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
mysqldump -h192.168.1.240 -uroot -ppassword testa>"F:\test.sql"

备份MySQL数据库为带删除表的格式 

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

导入数据

mysql>use abc;
mysql>set names utf8;
mysql>source /home/abc/abc.sql;

不存在就创建

CREATE TABLE IF NOT EXISTS `basegraphics` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL COMMENT '文本内容',
  `src` varchar(100) NOT NULL COMMENT '图片地址',
  `width` varchar(30) NOT NULL COMMENT '图片宽度',
  `height` varchar(30) NOT NULL COMMENT '图片高度',
  `createtime` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

如果表存在则删除

DROP TABLE IF EXISTS orders

DISTINCT 替换GROUP by

select count(1) from (select order_id FROM `sales_flat_order_item` GROUP by order_id)a;
SELECT count(DISTINCT order_id) FROM `sales_flat_order_item`;

#即可查看建表sql语句
SHOW CREATE TABLE test; 
#表的字段数
SHOW COLUMNS FROM test;

上一条

SELECT * FROM A WHERE id < $id ORDER BY id DESC LIMIT  1

下一条

SELECT * FROM A WHERE id > $id ORDER BY id ASC LIMIT  1

如果有字段自增和默认值可写成：

INSERT INTO Persons VALUES ('Bill', 'Xuanwumen 10');

常用函数

ABS(x) 返回x的绝对值

SELECT ABS(-1);
---- 返回1

CEIL(x)返回大于或等于 x 的最小整数

SELECT CEIL(1.5); 
---- 返回2

--判断是否是整数
SELECT 10<>ceil(10.23),10<>ceil(10.0)

FLOOR(x) 返回小于或等于 x 的最大整数

SELECT FLOOR(1.5); 
---- 返回1

ROUND(x)返回离 x 最近的数 

SELECT ROUND(1.23456, 2);

TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入

SELECT TRUNCATE(1.23456,3);
---- 返回1.234

LPAD (str,len,padstr)

返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

mysql> SELECT LPAD('hi',4,'?');-> '??hi'
mysql> SELECT LPAD('hi',1,'?');-> 'h'

RPAD(str,len,padstr)

返回字符串str, 其右边被字符串 padstr填补至len 字符长度。假如字符串str 的长度大于 len,则返回值被缩短到与 len 字符相同长度。

FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

SELECT FORMAT(250500.5634, 2); 
---- 返回250,500.56

LTRIM/RTRIM/TRIM 去掉字符串两端的空格

SELECT TRIM('    RUNOOB    ');
---- 返回RUNOOB

INSERT(str,pos,len,newstr)

返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。 如果pos 超过字符串长度，则返回值为原始字符串。 假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');-> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');-> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');-> 'QuWhat'

LENGTH(str)

返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。

mysql> SELECT LENGTH('text');-> 4

REPEAT(str,count)

返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。

mysql> SELECT REPEAT('MySQL', 3);-> 'MySQLMySQLMySQL'

测试表数据 

GREATEST（求最大值）和LEAST（求最小值）

mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where
 num1 != "" and num2 != "";
+------+------+------+------+
| c_id | max  | num1 | num2 |
+------+------+------+------+
|    1 |   21 |   21 |   12 |
|    2 |  219 |  133 |  219 |
|    3 |   67 |   67 |   16 |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu
m1 != "" and num2 != "";
+------+------+------+------+
| c_id | max  | num1 | num2 |
+------+------+------+------+
|    1 |   12 |   21 |   12 |
|    2 |  133 |  133 |  219 |
|    3 |   16 |   67 |   16 |
+------+------+------+------+
3 rows in set (0.00 sec)

REGEXP函数

mysql> SELECT * FROM `comment` WHERE content REGEXP '[0-9]+';
+------+------+------+------------+---------------------+------+------+
| c_id | u_id | name | content    | datetime            | num1 | num2 |
+------+------+------+------------+---------------------+------+------+
|    1 |    1 | test | 2222222211 | 2010-11-10 15:16:00 |   21 |   12 |
+------+------+------+------------+---------------------+------+------+
1 row in set (0.00 sec)

IFNULL(expr1,expr2)

如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。 IFNULL()返回一个数字或字符 串值，取决于它被使用的上下文环境 。

mysql > select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,'yes');
-> 'yes'

判断字段值是否为null或者空串''

SELECT * FROM `table_name' WHERE ifnull(col_name,'')='';

IF(expr1,expr2,expr3) 

如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。 IF()返回一个数字或字符串值，取决于它被使用的上下文。

mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
-> 'no'

expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，你应该使用一个比较操作来做。

mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1

在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。

select * from stock_moves where  abs (qty)=1140

CONCAT

可以连接一个或者多个字符串,MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

mysql> SELECT CONCAT(2,' test');
-> '2 test'

CONCAT_WS

即有分隔符的字符串连接如连接后以逗号分隔。优先使用

mysql> SELECT CONCAT_WS(';',2,' test');
-> '2;test'
SELECT CONCAT_WS(';', null,' test');
-> 'test'

COALESCE()

函数表示可以返回参数中的第一个非空表达式，当你有N个参数时选取第一个非空值（从左到右）。

mysql->select coalesce(null,"carrot","apple")
->carrot
mysql-> select coalesce(1,"carrot","apple")
->1

select coalesce(a,b,c);
如果a==null,则选择b；

如果b==null,则选择c；

如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

INSTR(str,substr)

返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒。

mysql> SELECT INSTR('foobarbar', 'bar');
-> 4

LOCATE(substr,str) 包含

返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

select substr('1分29秒',1,LOCATE('分','1分29秒')-1)*60+substr('1分29秒',LOCATE('分','1分29秒')+1,2)

SELECT LOCATE("0316", "03162774952")

SUBSTRING

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) ,SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'

SUBSTRING_INDEX(str,delim,count) 

在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

MAKE_SET(bits,str1,str2,...)

返回一个设定值 (一个包含被‘,’号分开的字字符串的字符串) ，由在bits 组中具有相应的比特的字符串组成。str1 对应比特 0, str2 对应比特1,以此类推。str1, str2, ...中的 NULL值不会被添加到结果中。

mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
-> 'hello'

LEFT(str,len) 返回从字符串str 开始的len 最左字符。

mysql> SELECT LEFT('foobarbar', 5);-> 'fooba'

RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符

ELT(N,str1,str2,str3,...)   

如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。

select   ELT(1,   'ej ',   'Heja ',   'hej ',   'foo ');  //->   'ej '

REPLACE(str,from_str,to_str)

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

update user set created =replace(replace (replace(created,   "年 ",   "- "),   "月 ",   "- "),   "日 ",   " ");

GROUP_CONCAT()

是MySQL数据库提供的一个函数，通常跟GROUP BY一起用,
1.例如:

select student_id,group_concat(courses_id order by courses_id desc) as courses_id from student_courses group by student_id;
　　+------------+-------------+
　　| student_id | courses_id  |
　　+------------+-------------+
　　|          2 | 5,4,3            |

2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：

SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+
| student_id | courses |
+------------+---------+
|   2        | 3|||4|||5    |
 +------------+---------+

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我用 UNION 这 个指令时，我们只会看到不同的资料值 (类似 SELECT DISTINCT )。

UNION 的语法如下：

[SQL 语句 1]
UNION
[SQL 语句 2]

而我们要找 出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句：

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

trim函数可以过滤指定的字符串：

mysql> SELECT TRIM(' bar '); //默认删除前后空格 
-> 'bar' 
mysql> SELECT TRIM(LEADING '，' FROM '，，barxxx'); //删除指定首字符 如'，‘ 
-> 'barxxx' 
mysql> SELECT TRIM(BOTH '，' FROM '，，bar，，，'); //删除指定首尾字符 
-> 'bar' 
mysql> SELECT TRIM(TRAILING '，' FROM 'barxxyz，，'); 
-> 'barxxyz'