Mysql 常用命令 转-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Mysql 常用命令 转

简介: 备份 mysqldump --opt dbname > dbname.20060307 恢复 mysql dbname < backup.20060307 得到表格的创建 SQL 语句 SHOW CREATE TABLE tbl_name 删除行尾的 "\r" MySQL 导入的数据必须是 unix 的文件。

备份

mysqldump --opt dbname > dbname.20060307<?XML:NAMESPACE PREFIX = O />

恢复

mysql dbname < backup.20060307

得到表格的创建 SQL 语句

SHOW CREATE TABLE tbl_name

删除行尾的 "\r"

MySQL 导入的数据必须是 unix 的文件。如果你的表中一些记录的行尾是"\r",可以用这个 SQL 语句删去:

UPDATE table_name as t SET t.column_name=SUBSTRING_INDEX(t.column_name, "\r", 1)

System error 1067 has occurred.

如果使用 no-installed 的 MySQL,而且安装的位置不在 c:,则需要设置 my.ini。在 MySQL\Doc 里的文档有详细描述。一般 winxp 是在 C:\WINDOWS 目录中,新建一个 my.ini,加入这样几行:

basedir=d:\\Programs\\mysql
datadir=d:\\Programs\\mysql\\data

修改默认的 engine

MySQL Query Brower 默认的 engine 是 InnoDB,其实对于单用户来说,MyISAM 更好。只要修改 Application/MySQL/mysqlx_common_options.xml 中这一行就好了。

<property name="EditorTableDefaultStorageEngine" value="MyISAM" />

SQL 历史

Query Brower 中不能复制 history,其实这些都在 Application/MySQL/mysqlqb_history.xml 中。

常用命令

# 修改表的名字
ALTER TABLE `biblio`.`bibrefkey` RENAME TO `biblio`.`bibrefKey`;

删除重复数据

CREATE TEMPORARY TABLE temp AS SELECT DISTINCT * FROM table_name;
DELETE FROM table_name;
INSERT INTO table_name SELECT * FROM temp;

插入时忽略重复

INSERT IGNORE table_name VALUES (...);

CONCAT_WS 函数

这个函数类似于 join 函数。

CONCAT_WS(separator, str1, str2,...)

找出重复的纪录

CREATE TEMPORARY TABLE temp AS SELECT num, id FROM table_name GROUP BY CONCAT_WS("-", num, id)  HAVING COUNT(*)>1;
SELECT t.* FROM table_name AS t, temp WHERE t.id=temp.id and t.num=temp.num;

修改表格列类型:

一般只要这样就行了:

ALTER TABLE table_name CHANGE column_name column_defination

对于 auto_increment 类型,要这样:

ALTER TABLE table_name ADD PRIMARY KEY (Id);
ALTER TABLE table_name CHANGE `Id` `Id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

修改表格的默认值

ALTER TABLE table_name ALTER `id` SET DEFAULT 0;

得到表格的 auto_increment 值

SHOW TABLE STATUS FROM database like "pattern";

从文件中导入数据

load data infile "file" into table db.table;

字符串替换的例子

Custom5 字段中 hda5 替换成 sda5。也可以用 REPLACE 函数。

UPDATE bibref set Custom5=concat('/media/sda5', substring(Custom5 from 12)) where custom5 like "/media/hda5%";

字符串函数如下:

函数

功能

ASCII(str)

返回字符串第一个字符的 ascii 编码

BIN(N)

返回数字N的二进制字符串

BIT_LENGTH

返回字符串的二进制长度

CHAR(N,...[USING charset])

返回N对应的字符串

CHAR_LENGTH(str)

字符串的字符长度

CHARACTER_LENGTH(str)

CHAR_LENGTH 的别名

COMPRESS(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

CONCAT(str1, str2,...)

<!--[if !supportEmptyParas]--> <!--[endif]-->

CONCAT_WS(separator, str1, str2,...)

<!--[if !supportEmptyParas]--> <!--[endif]-->

CONV(N, from_base, to_base)

数字不同基数的转换

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

<!--[if !supportEmptyParas]--> <!--[endif]-->

EXPORT_SET

<!--[if !supportEmptyParas]--> <!--[endif]-->

FIELD

<!--[if !supportEmptyParas]--> <!--[endif]-->

FORMAT(X, D)

<!--[if !supportEmptyParas]--> <!--[endif]-->

HEX(N_or_S)

<!--[if !supportEmptyParas]--> <!--[endif]-->

INSERT(str, pos, len, newstr)

<!--[if !supportEmptyParas]--> <!--[endif]-->

INSTR(str, substr)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LCASE(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LEFT(str, len)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LENGTH(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LOAD_FILE(file_name)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LOWER(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LPAD(str, len, padstr)

<!--[if !supportEmptyParas]--> <!--[endif]-->

LTRIM(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

MAKE_SET(bits, str, str2,...)

<!--[if !supportEmptyParas]--> <!--[endif]-->

MID(str, pos, len)

<!--[if !supportEmptyParas]--> <!--[endif]-->

OCT(N)

<!--[if !supportEmptyParas]--> <!--[endif]-->

OCTET_LENGTH(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

ORD(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

QUOTE(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

REPEAT(str, count)

<!--[if !supportEmptyParas]--> <!--[endif]-->

REPLACE(str, from_str, to_str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

REVERSE(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

RIGHT(str, len)

<!--[if !supportEmptyParas]--> <!--[endif]-->

RPAD(str, len, padstr)

<!--[if !supportEmptyParas]--> <!--[endif]-->

RTRIM(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

SOUNDEX(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

SPACE(N)

<!--[if !supportEmptyParas]--> <!--[endif]-->

SUBSTRING(str, pos, len)

<!--[if !supportEmptyParas]--> <!--[endif]-->

SUBSTRING_INDEX(str, delim, count)

<!--[if !supportEmptyParas]--> <!--[endif]-->

TRIM([{BOTH, LEADING, TRAILING} [remstr] FROM] str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

UCASE(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

UNCOMPRESS(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

UNCOMPRESSED_LENGTH(compressed_str)

返回压缩字符串压缩前的长度

UNHEX(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

UPPER(str)

<!--[if !supportEmptyParas]--> <!--[endif]-->

修改密码

转载自《MySQL中修改密码方法总结

首先要说明一点的是:一般情况下,修改MySQL密码是需要有mysql里的root权限的,这样一般用户是无法更改密码的,除非请求管理员帮助修改。

方法一: 使用phpMyAdmin

(图形化管理 MySQL 数据库的工具),这是最简单的,直接用 SQL 语句修改 mysql 数据库库的 user 表,不过别忘了使用 PASSWORD 函数,插入用户用 Insert命令,修改用户用Update命令,删除用Delete命令。在本节后面有数据表 user字段的详细介绍。www.xker.com(小新技术网)

方法二: 使用mysqladmin。

mysqladmin -u root -p oldpassword newpasswd

执行这个命令后,需要输入root的原密码,这样root的密码将改为newpasswd。同样,把命令里的root改为你的用户名,你就可以改你自己的密码了。

当然如果你的mysqladmin连接不上mysql server,或者你没有办法执行 mysqladmin,那么这种方法就是无效的,而且mysqladmin无法把密码清空。

下面的方法都在mysql提示符下使用,且必须有mysql的root权限:

方法三

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES ('%','system', PASSWORD('manager'));
mysql> FLUSH PRIVILEGES

确切地说这是在增加一个用户,用户名为system,密码为manager。注意要使用 PASSWORD函数,然后还要使用FLUSH PRIVILEGES来执行确认。

方法四

和方法三一样,只是使用了REPLACE语句

mysql> REPLACE INTO mysql.user (Host,User,Password) VALUES('%','system',PASSWORD('manager'));
mysql> FLUSH PRIVILEGES

方法五

使用SET PASSWORD语句

mysql> SET PASSWORD FOR system@"%" = PASSWORD('manager');

你也必须使用PASSWORD()函数,但是不需要使用FLUSH PRIVILEGES来执行确认。

方法六

使用GRANT ... IDENTIFIED BY语句,来进行授权。

mysql> GRANT USAGE ON *.* TO system@"%" IDENTIFIED BY 'manager';

这里 PASSWORD()函数是不必要的,也不需要使用FLUSH PRIVILEGES来执行确认。

注:PASSWORD()函数作用是为口令字加密,在程序中MySQL自动解释。

如果要使用 LOAD DATA INFILE 语句,需要提供 FILE 权限:

mysql> GRANT FILE ON *.* TO system@"%" IDENTIFIED BY 'manager';

也可以直接提供所有的权限:ALL PRIVILEGES 。

SHOW VARIABLES 查看 MySQL 支持的功能

SHOW VARIABLES LIKE "have%";

查找文档

在命令行中可以使用 ? 或 help 命令来查找 SQL 语句的文档。比如:

mysql> ? update
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
<!--[if !supportEmptyParas]--> <!--[endif]-->

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
<!--[if !supportEmptyParas]--> <!--[endif]-->

Multiple-table syntax:
<!--[if !supportEmptyParas]--> <!--[endif]-->

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]
....

<!--[if !supportEmptyParas]--> <!--[endif]-->

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章