devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql常用命令和函数_mysql 修改函数

作者：IT小白 | 2024-07-19 21:16:48

赞

踩

mysql 修改函数

常用命令

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

create user test@localhost identified by 'password';

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

  1. grant all on database.* to 'test'@'%' identified by 'password';
  2. 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数据库的命令

  1. mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
  2. 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

导入数据

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

不存在就创建

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

如果表存在则删除

DROP TABLE IF EXISTS orders

DISTINCT 替换GROUP by

  1. select count(1) from (select order_id FROM `sales_flat_order_item` GROUP by order_id)a;
  2. SELECT count(DISTINCT order_id) FROM `sales_flat_order_item`;
  1. #即可查看建表sql语句
  2. SHOW CREATE TABLE test; 
  3. #表的字段数
  4. 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的绝对值

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

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

  1. SELECT CEIL(1.5); 
  2. ---- 返回2
  3.  
  4. --判断是否是整数
  5. SELECT 10<>ceil(10.23),10<>ceil(10.0)

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

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

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

SELECT ROUND(1.23456, 2);

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

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

LPAD (str,len,padstr)

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

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

RPAD(str,len,padstr)

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

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

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

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

  1. SELECT TRIM('    RUNOOB    ');
  2. ---- 返回RUNOOB

INSERT(str,pos,len,newstr)

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

  1. mysql> SELECT INSERT('Quadratic', 3, 4, 'What');-> 'QuWhattic'
  2. mysql> SELECT INSERT('Quadratic', -1, 4, 'What');-> 'Quadratic'
  3. 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（求最小值）

  1. mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where
  2.  num1 != "" and num2 != "";
  3. +------+------+------+------+
  4. | c_id | max  | num1 | num2 |
  5. +------+------+------+------+
  6. |    1 |   21 |   21 |   12 |
  7. |    2 |  219 |  133 |  219 |
  8. |    3 |   67 |   67 |   16 |
  9. +------+------+------+------+
  10. 3 rows in set (0.00 sec)
  11.  
  12. mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu
  13. m1 != "" and num2 != "";
  14. +------+------+------+------+
  15. | c_id | max  | num1 | num2 |
  16. +------+------+------+------+
  17. |    1 |   12 |   21 |   12 |
  18. |    2 |  133 |  133 |  219 |
  19. |    3 |   16 |   67 |   16 |
  20. +------+------+------+------+
  21. 3 rows in set (0.00 sec)

REGEXP函数

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

IFNULL(expr1,expr2) 

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

  1. mysql > select IFNULL(1,0);
  2. -> 1
  3. mysql> select IFNULL(0,10);
  4. -> 0
  5. mysql> select IFNULL(1/0,10);
  6. -> 10
  7. mysql> select IFNULL(1/0,'yes');
  8. -> 'yes'

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

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

IF(expr1,expr2,expr3) 

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

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

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

  1. mysql> select IF(0.1,1,0);
  2. -> 0
  3. mysql> select IF(0.1<>0,1,0);
  4. -> 1

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

select * from stock_moves where  abs (qty)=1140

CONCAT

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

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

CONCAT_WS

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

  1. mysql> SELECT CONCAT_WS(';',2,' test');
  2. -> '2;test'
  3. SELECT CONCAT_WS(';', null,' test');
  4. -> 'test'

COALESCE()

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

  1. mysql->select coalesce(null,"carrot","apple")
  2. ->carrot
  3. mysql-> select coalesce(1,"carrot","apple")
  4. ->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()的双参数形式相同，除非参数的顺序被颠倒。

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

LOCATE(substr,str) 包含

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

  1. select substr('1分29秒',1,LOCATE('分','1分29秒')-1)*60+substr('1分29秒',LOCATE('分','1分29秒')+1,2)
  2.  
  3. 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 使用一个负值。

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

SUBSTRING_INDEX(str,delim,count) 

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

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

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

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

  1. mysql> SELECT MAKE_SET(1,'a','b','c');
  2. -> 'a'
  3. mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
  4. -> 'hello,world'
  5. mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
  6. -> '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 。

  1. mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
  2. -> 'WwWwWw.mysql.com' 
update user set created =replace(replace (replace(created,   "年 ",   "- "),   "月 ",   "- "),   "日 ",   " ");

GROUP_CONCAT()

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

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

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

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

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函数可以过滤指定的字符串：

  1. mysql> SELECT TRIM(' bar '); //默认删除前后空格 
  2. -> 'bar' 
  3. mysql> SELECT TRIM(LEADING '，' FROM '，，barxxx'); //删除指定首字符 如'，‘ 
  4. -> 'barxxx' 
  5. mysql> SELECT TRIM(BOTH '，' FROM '，，bar，，，'); //删除指定首尾字符 
  6. -> 'bar' 
  7. mysql> SELECT TRIM(TRAILING '，' FROM 'barxxyz，，'); 
  8. -> 'barxxyz'

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/853521
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号