标题: mysql常用命令 [打印本页] 作者: admin 时间: 2012-9-13 16:32 标题: mysql常用命令 启动:net start mySql; 8 E t4 ~) X: M; z$ |1 K 进入:mysql -u root -p/mysql -h localhost -u root -p databaseName;8 J5 a8 ?" G. N* o( O0 h. n
列出数据库:show databases;8 |+ V0 y0 S+ L. N Q8 u+ s6 C' L
选择数据库:use databaseName; ( [0 u) }! g- m$ k0 j, a 列出表格:show tables;( E% X5 y3 A+ k
显示表格列的属性:show columns from tableName; & V2 d/ Z9 Y8 b1 N( y& O2 ^ h 建立数据库:source fileName.txt; / Z& r0 E$ R$ W4 }: b 匹配字符:可以用通配符_代表任何一个字符,%代表任何字符串;. D( ^ [# G: J
增加一个字段:alter table tabelName add column fieldName dateType; * S# E! R5 y0 y; ^; G$ h6 d 增加多个字段:alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;5 N5 r1 T8 C' E, R2 j. A
多行命令输入:注意不能将单词断开;当插入或更改数据时,不能将字段的字符串展开到多行里,否则硬回车将被储存到数据中;, ~9 Z3 a. I2 X) O3 [) J! A- g
增加一个管理员帐户:grant all on *.* to user@localhost identified by "password"; & Z' t* J/ e$ T0 \ 每条语句输入完毕后要在末尾填加分号';',或者填加'\g'也可以; g0 p5 Y6 U) F5 x$ @& }. @! ?( A z
查询时间:select now();9 W6 {% G; O; F* L3 {! v; J
查询当前用户:select user();' G* V" g+ o# @! ]# W7 N
查询数据库版本:select version(); ; S7 c8 L) v: _ 查询当前使用的数据库:select database();+ K2 p7 J, O$ a! v
/ N$ R" W) u' h. q5 i 1、删除student_course数据库中的students数据表:0 h: k; C8 U |. C ^1 u; M c
rm -f student_course/students.*, l+ |+ @! ?$ Z/ B, |' O" i
% \" _% b0 u1 z2 O4 Y( b; u+ F& Y 2、备份数据库:(将数据库test备份) " l0 a, Q7 z a mysqldump -u root -p test>c:\test.txt 8 M& [4 O5 _# I1 G4 D5 b' x8 c1 s, B 备份表格:(备份test数据库下的mytable表格)1 h8 J( L( A8 ]3 X
mysqldump -u root -p test mytable>c:\test.txt6 E1 _5 z1 R6 {+ \# r z
将备份数据导入到数据库:(导回test数据库)( S q. R- {0 T3 W
mysql -u root -p test<c:\test.txt0 U; [! s* K# F( a, m
) p- y1 n1 c/ n2 m6 m5 }* w
3、创建临时表:(建立临时表zengchao)/ ^1 I+ Q& x" y
create temporary table zengchao(name varchar(10)); 0 o. s8 G' _1 X 2 V8 @$ z3 {6 q0 u( e I
4、创建表是先判断表是否存在 : F$ e; E/ l8 b( W create table if not exists students(……); $ e, z6 [( R: N" }+ `3 E7 U - L5 K9 z) G$ U! p" G
5、从已经有的表中复制表的结构$ b, |9 J+ k' Q& V; `* _
create table table2 select * from table1 where 1<>1; 0 g8 k, O6 C6 v/ }. g 9 K4 s: v9 m$ ^9 c! i+ a, S
6、复制表 3 u) u( [8 |0 n7 d. d# t create table table2 select * from table1;- C& \0 L- z5 h. _8 K# j8 ^' F
7 D1 p, b7 ?; F; k+ n! m$ W' W. M% _7 X' @* K 7、对表重新命名) v( l$ K/ _: E( l: G
alter table table1 rename as table2; ( R C5 d" }" L" o% T) Q ( G2 \" E7 f \6 ~& } 8、修改列的类型! ?& b; i6 l1 p" k
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned! a- z; P7 `( J) U, h- S8 p9 j: W
alter table table1 change id sid int unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned ?; W/ C, F6 w! Y $ l6 y* i9 R" K. z1 y5 O, Z 9、创建索引+ v9 W! R& c/ q% M& ^5 c+ {
alter table table1 add index ind_id (id); # T3 X/ W- n9 s. C create index ind_id on table1 (id); 1 }# \# `7 _& [3 a create unique index ind_id on table1 (id);//建立唯一性索引 8 r9 @3 L, N; l9 i9 r3 D4 x & t5 ?9 P: h7 K' D( d 10、删除索引/ i% M+ l* U7 |% F0 C2 ^9 Q+ Y
drop index idx_id on table1;1 t5 H4 T6 b( Z2 ?' N
alter table table1 drop index ind_id; 9 f0 f7 G9 } [' [/ P5 T7 f7 F6 i % T) K* X @+ |& o& [ \7 Z
11、联合字符或者多个列(将列id与":"和列name和"="连接): C. E" k8 M% ^3 X% I. {3 a/ H
select concat(id,':',name,'=') from students; 5 L: I$ i# a, {; { % ?* L6 y4 P: p 12、limit(选出10到20条)<第一个记录集的编号是0> : X& X- N( `6 c( Z select * from students order by id limit 9,10;! x) |& q3 r1 y
3 w. s% Z) ~! W! Y W 13、MySQL不支持的功能 $ Q+ {3 F; P z# d: s 事务,视图,外键和引用完整性,存储过程和触发器 2 M- H7 \7 Y1 l% M$ u# |6 x & W# z1 y9 ?/ @( s( A
3 e1 V' l3 F: a0 A& u2 w
14、MySQL会使用索引的操作符号) o8 m. q! G( F8 o
<,<=,>=,>,=,between,in,不带%或者_开头的like , x$ A; W. Q: `4 Q$ m- h5 Y ! A) [+ k0 B r7 Q
15、使用索引的缺点 + A0 C1 F7 l. e1 P8 r( Q& P% h 1)减慢增删改数据的速度; % R( ?8 b& N; z+ F6 h0 t 2)占用磁盘空间; & h+ g. S0 U' x, I 3)增加查询优化器的负担;9 x0 M+ {0 I$ w. c. p, |" q& Y4 p# h
当查询优化器生成执行计划时,会考虑索引,太多的索引会给查询优化器增加工作量,导致无法选择最优的查询方案; * R9 w. J! H j3 a# V$ g2 n 6 [3 v3 J! ]/ ]" J 16、分析索引效率 7 p3 [; Q6 H& Y2 x% R" m 方法:在一般的SQL语句前加上explain;8 J' t7 _6 b& }1 ^4 R
分析结果的含义: + u [+ |9 v# q6 n1 K8 q0 J2 _ 1)table:表名;0 V- A2 h$ [7 p; Y# O" q
2)type:连接的类型,(ALL/Range/Ref)。其中ref是最理想的; * X5 b( B+ y @$ Y7 j, W( N' s5 e! X7 d 3)possible_keys:查询可以利用的索引名;* D& i) l$ I$ g/ B" E
4)key:实际使用的索引;. C0 R. b ^- S0 i
5)key_len:索引中被使用部分的长度(字节); # d) G& J y' L9 z 6)ref:显示列名字或者"const"(不明白什么意思); , R7 x0 F7 s$ U' ^. P/ a6 H$ W 7)rows:显示MySQL认为在找到正确结果之前必须扫描的行数; $ _" O) z$ |3 v( g 8)extra:MySQL的建议; + n5 K. w$ v: D) \% _( Y, P 0 _. T2 W( w; L
17、使用较短的定长列 " q A6 B- t6 B) C9 _2 X 1)尽可能使用较短的数据类型;. S( n/ o u5 f+ n$ r' }: v( b
2)尽可能使用定长数据类型; + E B) P; N0 ]* z- j a)用char代替varchar,固定长度的数据处理比变长的快些;& m) l G. J0 U6 ]$ s1 K2 O& ~3 j
b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能; * P; N, B; c4 m6 V7 D c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了; & ]; J0 ~' k+ Y+ |3 i4 @5 T+ _ d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大;, l' ^! \; w/ E
7 G" T4 ^1 L! Z5 c 18、使用not null和enum 1 m4 Q% m6 @2 f& O2 ~ 尽量将列定义为not null,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询;% c7 R! m# v# I; ]0 f
如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的; + `2 Q5 @3 }9 ]) {7 L # y. c$ Q- e8 X& N
19、使用optimize table ( v' K: d2 C- r 对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表; 9 |4 S6 D! `& _9 S ( b9 U$ y- v5 H3 y* c& T
20、使用procedure analyse() 5 U' d& `+ U9 F; X% b/ ~/ f: G' b& u 可以使用procedure analyse()显示最佳类型的建议,使用很简单,在select语句后面加上procedure analyse()就可以了;例如:: W: N9 T1 D0 h o/ i' o' V- ?+ H: o
select * from students procedure analyse();5 j9 ]7 C$ D" J5 `, T
select * from students procedure analyse(16,256); # G1 d; c ~9 H5 x8 U 第二条语句要求procedure analyse()不要建议含有多于16个值,或者含有多于256字节的enum类型,如果没有限制,输出可能会很长;. X' F! d9 s, ]; ]
) t8 c, ?7 ]6 q; S7 S O3 w
21、使用查询缓存 6 X6 x J1 f c6 ] u 1)查询缓存的工作方式: $ F/ k% W3 Q3 f6 F( z# _; q4 M4 k+ d 第一次执行某条select语句时,服务器记住该查询的文本内容和查询结果,存储在缓存中,下次碰到这个语句时,直接从缓存中返回结果;当更新数据表后,该数据表的任何缓存查询都变成无效的,并且会被丢弃。8 Y7 E3 [5 K$ }, z: w0 ~# E0 b- e0 p) `- a
2)配置缓存参数:# C: a7 X6 v! ~3 d0 H
变量:query_cache _type,查询缓存的操作模式。有3中模式,0:不缓存;1:缓存查询,除非与select sql_no_cache开头;2:根据需要只缓存那些以select sql_cache开头的查询;query_cache_size:设置查询缓存的最大结果集的大小,比这个值大的不会被缓存。 8 \# Q; S4 x: @% F4 O / k1 W V+ ?: _$ h0 _0 v 22、调整硬件 % O7 [" z' O# N4 d1 |# F1 i1 k: L 1)在机器上装更多的内存; + S( _6 O, r. Q. f% G5 I, d 2)增加更快的硬盘以减少I/O等待时间; 7 c2 w! B: g4 f 寻道时间是决定性能的主要因素,逐字地移动磁头是最慢的,一旦磁头定位,从磁道读则很快; 2 z0 C# J' a1 u/ p; k 3)在不同的物理硬盘设备上重新分配磁盘活动;. M* s3 ? e2 D% n9 O7 B, o1 g
如果可能,应将最繁忙的数据库存放在不同的物理设备上,这跟使用同一物理设备的不同分区是不同的,因为它们将争用相同的物理资源(磁头)。 , ?5 u: X9 ~4 p