Mysql 常用命令 转
备份
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> ? updateName: '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]-->
