/ 中存储网

MySQL数据库常用命令分享

2014-07-13 16:17:14 来源:中存储网

查询数据库中的存储过程和函数

方法一:

       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'   //存储过程

       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION'   //函数

方法二:

         show procedure status; //存储过程

         show function status;     //函数

查看存储过程或函数的创建代码

show create procedure proc_name;

show create function func_name;

查看视图

SELECT * from information_schema.VIEWS   //视图

SELECT * from information_schema.TABLES   //表

查看触发器

方法一:

语法:SHOW TRIGGERS [FROM db_name] [LIKE expr]

实例:SHOW TRIGGERSG     //触发器

方法二:

对INFORMATION_SCHEMA数据库中的TRIGGERS表查询

mysql>SELECT * FROM triggers T WHERE trigger_name=”mytrigger” G

关键字: 数据库 

常用MySQL命令 

  启动:net start mySql; 

  进入:mysql -u root -p/mysql -h localhost -u root -p databaseName; 

  列出数据库:show databases; 

  选择数据库:use databaseName; 

  列出表格:show tables; 

  显示表格列的属性:show columns from tableName; 

  建立数据库:source fileName.txt; 

  匹配字符:可以用通配符_代表任何一个字符,%代表任何字符串; 

  增加一个字段:alter table tabelName add column fieldName dateType; 

  增加多个字段:alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType; 

  多行命令输入:注意不能将单词断开;当插入或更改数据时,不能将字段的字符串展开到多行里,否则硬回车将被储存到数据中; 

  增加一个管理员帐户:grant all on *.* to user@localhost identified by "password"; 

  每条语句输入完毕后要在末尾填加分号';',或者填加'g'也可以; 

  查询时间:select now(); 

  查询当前用户:select user(); 

  查询数据库版本:select version(); 

  查询当前使用的数据库:select database(); 

  查询当前使用的数据库编码  show create database mysql;

    导入数据库脚本:

    >mysql -h localhost -uroot -pcanada --character-set=gbk mig<mig.sql

    导出数据库[表结构+数据]脚本:

    >mysqldump -h localhost -uroot -pcanada --character-set=gbk mig>mig.sql

    导出数据库[表结构]脚本:

    >mysqldump -d  -h localhost -uroot  -pcanada cmwap >cmwap-database.sql

        

导出数据成xml格式:

sudo /var/sync/mysql/bin/mysql -uroot -pcanada cef_push -X -e "select domain_id, domain_name  from domain" > nnn.xml

导出数据成csv格式: -B

小技巧

-----------------------------------------------------------------------------------------------

1. start/stop mysql service at windows : >[net start/stop] mysql

2. del/add database at console :>mysqladmin -uroot -p123456 [drop/create] database_name

-----------------------------------------------------------------------------------------------

  1、删除student_course数据库中的students数据表: 

  rm -f student_course/students.* 

   

  2、备份数据库:(将数据库test备份) 

  mysqldump -u root -p test>c:test.txt 

  备份表格:(备份test数据库下的mytable表格) 

  mysqldump -u root -p test mytable>c:test.txt 

  将备份数据导入到数据库:(导回test数据库) 

  mysql -u root -p test<c:test.txt 

   

  3、创建临时表:(建立临时表zengchao) 

  create temporary table zengchao(name varchar(10)); 

   

  4、创建表是先判断表是否存在 

  create table if not exists students(……); 

   

  5、从已经有的表中复制表的结构 

  create table table2 select * from table1 where 1<>1; 

   

  6、复制表 

  create table table2 select * from table1; 

   

  7、对表重新命名 

  alter table table1 rename as table2; 

   

  8、修改列的类型 

  alter table table1 modify id int unsigned;//修改列id的类型为int unsigned 

  alter table table1 change id sid int unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned 

   

  9、创建索引 

  alter table table1 add index ind_id (id); 

  create index ind_id on table1 (id); 

  create unique index ind_id on table1 (id);//建立唯一性索引 

   

  10、删除索引 

  drop index idx_id on table1; 

  alter table table1 drop index ind_id; 

   

  11、联合字符或者多个列(将列id与":"和列name和"="连接) 

  select concat(id,':',name,'=') from students; 

   

  12、limit(选出10到20条)<第一个记录集的编号是0> 

  select * from students order by id limit 9,10; 

   

  13、MySQL不支持的功能 

  事务,视图,外键和引用完整性,存储过程和触发器 

   

   

  14、MySQL会使用索引的操作符号 

  <,<=,>=,>,=,between,in,不带%或者_开头的like 

   

  15、使用索引的缺点 

  1)减慢增删改数据的速度; 

  2)占用磁盘空间; 

  3)增加查询优化器的负担; 

  当查询优化器生成执行计划时,会考虑索引,太多的索引会给查询优化器增加工作量,导致无法选择最优的查询方案; 

   

  16、分析索引效率 

  方法:在一般的SQL语句前加上explain; 

  分析结果的含义: 

  1)table:表名; 

  2)type:连接的类型,(ALL/Range/Ref)。其中ref是最理想的; 

  3)possible_keys:查询可以利用的索引名; 

  4)key:实际使用的索引; 

  5)key_len:索引中被使用部分的长度(字节); 

  6)ref:显示列名字或者"const"(不明白什么意思); 

  7)rows:显示MySQL认为在找到正确结果之前必须扫描的行数; 

  8)extra:MySQL的建议; 

   

  17、使用较短的定长列 

  1)尽可能使用较短的数据类型; 

  2)尽可能使用定长数据类型; 

  a)用char代替varchar,固定长度的数据处理比变长的快些; 

  b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能; 

  c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了; 

  d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大; 

   

  18、使用not null和enum 

  尽量将列定义为not null,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询; 

  如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的; 

   

  19、使用optimize table 

  对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表; 

   

  20、使用procedure analyse() 

  可以使用procedure analyse()显示最佳类型的建议,使用很简单,在select语句后面加上procedure analyse()就可以了;例如: 

  select * from students procedure analyse(); 

  select * from students procedure analyse(16,256); 

  第二条语句要求procedure analyse()不要建议含有多于16个值,或者含有多于256字节的enum类型,如果没有限制,输出可能会很长; 

   

  21、使用查询缓存 

  1)查询缓存的工作方式: 

  第一次执行某条select语句时,服务器记住该查询的文本内容和查询结果,存储在缓存中,下次碰到这个语句时,直接从缓存中返回结果;当更新数据表后,该数据表的任何缓存查询都变成无效的,并且会被丢弃。 

  2)配置缓存参数: 

  变量:query_cache _type,查询缓存的操作模式。有3中模式,0:不缓存;1:缓存查询,除非与select sql_no_cache开头;2:根据需要只缓存那些以select sql_cache开头的查询;query_cache_size:设置查询缓存的最大结果集的大小,比这个值大的不会被缓存。 

   

  22、调整硬件 

  1)在机器上装更多的内存; 

  2)增加更快的硬盘以减少I/O等待时间; 

  寻道时间是决定性能的主要因素,逐字地移动磁头是最慢的,一旦磁头定位,从磁道读则很快; 

  3)在不同的物理硬盘设备上重新分配磁盘活动; 

  如果可能,应将最繁忙的数据库存放在不同的物理设备上,这跟使用同一物理设备的不同分区是不同的,因为它们将争用相同的物理资源(磁头)。 

------------------------------------------------

************************************************

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.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.导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u root -p dataname >dataname.sql

这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中.

2.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

mysqldump -u root -p dataname users> dataname_users.sql

3.导出一个数据库结构

mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql

-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库

常用source 命令

进入mysql数据库控制台,

如mysql -u root -p

mysql>use 数据库

然后使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

mysqldump支持下列选项:

–add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。

–add-drop-table

在每个create语句之前增加一个drop table。

–allow-keywords

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

-c, –complete-insert

使用完整的insert语句(用列名字)。

-C, –compress

如果客户和服务器均支持压缩,压缩两者间所有的信息。

–delayed

用INSERT DELAYED命令插入行。

-e, –extended-insert

使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)

-#, –debug[=option_string]

跟踪程序的使用(为了调试)。

–help

显示一条帮助消息并且退出。

–fields-terminated-by=…

–fields-enclosed-by=…

–fields-optionally-enclosed-by=…

–fields-escaped-by=…

–fields-terminated-by=…

这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。

LOAD DATA INFILE语法。

-F, –flush-logs

在开始导出前,洗掉在MySQL服务器中的日志文件。

-f, –force,

即使我们在一个表导出期间得到一个SQL错误,继续。

-h, –host=..

从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。

-l, –lock-tables.

为开始导出锁定所有表。

-t, –no-create-info

不写入表创建信息(CREATE TABLE语句)

-d, –no-data

不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!

–opt

同–quick –add-drop-table –add-locks –extended-insert –lock-tables。

应该给你为读入一个MySQL服务器的尽可能最快的导出。

-pyour_pass, –password[=your_pass]

与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。

-P port_num, –port=port_num

与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)

-q, –quick

不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。

-S /path/to/socket, –socket=/path/to/socket

与localhost连接时(它是缺省主机)使用的套接字文件。

-T, –tab=path-to-some-directory

对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和 –lines–xxx选项来定。

-u user_name, –user=user_name

与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。

-O var=option, –set-variable var=option

设置一个变量的值。可能的变量被列在下面。

-v, –verbose

冗长模式。打印出程序所做的更多的信息。

-V, –version

打印版本信息并且退出。

-w, –where=’where-condition’

只导出被选择了的记录;注意引号是强制的!

“–where=user=’jimf’” “-wuserid>1″ “-wuserid<1″

最常见的mysqldump使用可能制作整个数据库的一个备份:

mysqldump –opt database > backup-file.sql

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:

mysqldump –opt database | mysql –host=remote-host -C database

由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:

shell> mysqladmin create target_db_name

shell> mysql target_db_name < backup-file.sql

就是

shell> mysql 库名 < 文件名

****************************************************************

在win2003下MySQL数据库每天自动备份

****************************************************************

需求:

-------------------------------------------------------------------------------------

终于让服务器每天早上备份一次 MySQL 数据库并自动打包,同时删除 5 天前的备份文件. 

分享如下.

-------------------------------------------------------------------------------------

1. 环境: windows server 2003 + Apache 2.0 + PHP5 + MySQL 4.0.26 .

2. 假设 PHP 安装目录为 D:/php ,MySQL 安装目录为 D:/mysql.

3. 在 D:/php 下建立目录 WinRAR, 把你 winrar 安装目录下的 WinRAR.exe 和 RARReg.KEY 复制到 D:/php/WinRAR .

4. D:/php 下建立文件 mysql_backup.php:

   

//保存目录,路径要用反斜杠.您需要手动建立它. 

$store_folder = 'D:databse_backup'; 

//用户名和密码 

//该帐号须有操作[所有]的数据库及FILE的权限 

//否则有些数据库不能备份. 

$db_username = "root"; 

$db_password = ""; 

$time=time(); 

$nowdir = "$store_folder\".date("Ymd",$time).""; 

if(file_exists("$nowdir.rar")) die("File exists.n"); 

@mkdir($nowdir); 

mysql_connect("localhost","$db_username","$db_password"); 

$query=mysql_list_dbs(); 

while($result=mysql_fetch_array($query)){ 

system (dirname(__FILE__).'..mysqlbinmysqldump --opt '."$result[Database]

-u{$db_username} ".($db_password?"-p{$db_password}":"")." >

$nowdir\$result[Database].sql"); 

echo "dumping database `$result[Database]`...n"; 

echo "nWinrar loading...n"; 

system( dirname(__FILE__)."\WinRAR\WinRAR.exe a -ep1 -r -o+ -m5 -df "$nowdir.rar" 

"$nowdir" " ); 

//删除 5 天前的文件 

@unlink("$store_folder\".date("Ymd",$time-86400*5).".rar"); 

echo "nOK!n"; 

>

5. D:/php 下建立文件 mysql_backup.bat,内容只有一句:

php.exe mysql_backup.php

6. 双击该 bat 文件运行,如果能备份了,OK,下一步添加任务计划.

7. 把 D:/php/mysql_backup 添加到任务计划,时间选每天. 根据服务器的监测结果,每天早上 5-8 时为流量低峰期. 由于 5-7 时有些数据库的清理工作,可以把时间定在了早上 8 点整.

 

******************************************************

mysql 查看日志

******************************************************

1.查看是否启用了日志

mysql>show variables like 'log_bin';

2.怎样知道当前的日志

mysql>show master status;

3.看二进制日志文件用mysqlbinlog

shell>mysqlbinlog mail-bin.000001(要写绝对问题路径 d://)

或者shell>mysqlbinlog mail-bin.000001 | tail 

Windows 下用类似的命令。

mysql有以下几种日志:   

   

   错误日志:     -log-err  

   查询日志:     -log  

   慢查询日志:   -log-slow-queries  

   更新日志:     -log-update  

   二进制日志: -log-bin  

   

   在mysql的安装目录下,打开my.ini,在后面加上上面的参数,保存后重启mysql服务就行了。  

   例如:   

   #Enter a name for the error log file.   Otherwise a default name will be used.

   log-error=err.log

#Enter a name for the query log file. Otherwise a default name will be used.

   #log=

#Enter a name for the slow query log file. Otherwise a default name will be used.

   #log-slow-queries=

   #Enter a name for the update log file. Otherwise a default name will be used.

   #log-update=

   #Enter a name for the binary log. Otherwise a default name will be used.

   #log-bin=      

   上面只开启了错误日志,要开其他的日志就把前面的“#”去掉。

long_query_time =1 --是指执行超过多久的sql会被log下来,这里是1秒

   log-slow-queries=slowqueris.log --将查询返回较慢的语句进行记录

   log-queries-not-using-indexes = nouseindex.log --就是字面意思,log下来没有使用索引的query

   log=mylog.log --对所有执行语句进行记录

   log-error=log-error.log

   log=log-query.log

   log-queries-not-using-indexes

   log-warnings=2

   log-slow-queries=log-slow-query.log

   log-update=log-update.log

   long_query_time=2

 

***********************

Mysql死锁

***********************

先执行show  processlist找到死锁线程号.然后Kill processNo

当然主要解决还是需要去看一下具体的操作.可能产生死锁

Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁

然后就是解决了.

**********************************************************************************

1.PRIMARY KEY(主键索引)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (

`column`) 

3.INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )