mysqlÏà¹ØÃüÁî
Ò»¡¢ÊÂÎñ´¦Àí£¨myisamÒýÇæ²»Ö§³ÖÊÂÎñ£¬innodbÒýÇæÖ§³ÖÊÂÎñ£©
£¨1£©start transaction
£¨2£©commit
£¨3£©rollback£¬rollback to [savepoint name]
£¨4£©savepoint [savepoint name]
£¨5£©set autocommit=0 or 1
£¨6£©release savepoint [savepoint name]
×¢Ò⣺create¡¢dropµÈ²Ù×÷£¬²»ÄÜ»ØÍË
¶þ¡¢×Ö·û¼¯
1.×Ö·û±àÂ룺×Ö·ûµÄÄÚ²¿±íʾ
2.У¶Ô£º×Ö·ûÈçºÎ±È½Ï
3.²é¿´ÃüÁ
show character set; --²é¿´×Ö·û¼¯
show collation; --²é¿´Ð£¶Ô
show variables like 'character%' --²é¿´ÏµÍ³×Ö·û¼¯
show variables like 'collaction%' --²é¿´ÏµÍ³µÄУ¶Ô¹æÔò
4.collation¿ÉÒÔÓÃÔÚÊý¾Ý¿â¼¶¡¢±í¼¶¡¢×ֶμ¶£¬ÉõÖÁʱ²éѯʱָ¶¨
È磺
select * from table_name order by column_name collate utf8_general_ci;
Èý¡¢°²È«
1.´´½¨Óû§
create user username identified by 'pwd'
2.ɾ³ýÓû§
drop user username
3.ÏÔʾÓû§È¨ÏÞ
show grants for username
4.grant
grant ȨÏÞ on dbname.tablename to username
5.revoke
revoke ȨÏÞ on dbname.tablename from username
ËÄ¡¢
£¨1£©ÒÔµÍÓÅÏȼ¶ÔËÐÐ
INSERT|UPDATE|DELETE LOW_PRIORITY ......
£¨2£©ºöÂÔ´íÎó
UPDATE IGNORE table_name ......
Î塢ά»¤
1.±¸·ÝºÍ»Ö¸´
£¨1£©mysqldump
£¨2£©mysqlhotcopy£¨Ö»Ö§³Ömyisam±íÀàÐÍ£©
£¨3£©BACKUP TABLE tbl_name1,tbl_name2 to 'dirpath' Ö»Ö§³Ömyisam±íÀàÐÍ£¨¿ÉÒÔÓÃmysqlhotcopy´úÌæ£©£¬²»Ö§³Öinnodb
£¨4£©RESTORE TABLE tbl_name1,tbl_name2 from 'dirpath' »Ö¸´±í£¨myisamÀàÐÍ£©
£¨5£©SELECT INTO OUTFILE
ÀýÈ磺select * into outfile 'c:\\a.txt' from tbl_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
£¨6£©LOAD DATA INFILE
ÀýÈ磺load data infile 'c:\\a.txt' into table tbl_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
2.ά»¤ÃüÁî
£¨1£©·ÖÎö±í£¬·ÖÎö±í¼üÊÇ·ñÕýÈ·
ANALYZE TABLE tbl_name
£¨2£©¼ì²é±í£¬¼ì²é±í´æÔÚµÄÎÊÌâ
CHECK TABLE tbl_name
£¨3£©ÐÞÀí±í
REPAIR TABLE tbl_name
£¨4£©ÓÅ»¯±í£¬µ±´óÁ¿É¾³ýÊý¾Ýºó£¬¿ÉÒÔÓôËÊÕ»ØËùÓõĿռ䣬ÒÔÓÅ»¯ÐÔÄÜ
OPTIMIZE TABLE tbl_name
Ïà¹ØÎĵµ£º
Êý¾Ý¿âÌØµã
££Ê¹ÓÃpartition£¬´æÔÚÉϰٸö·ÖÇø
££½¨±íʱָ¶¨ÁËdata_dirºÍindex_dir£¬Êý¾Ý²»ÊÇ´æ´¢ÔÚĬÈÏλÖ㬶øÊÇÔÚmysqldµÄÊý¾ÝĿ¼ÏÂlinkµ½ÕæÕýµÄÊý¾ÝÎļþ
±¸·Ý»Ö¸´ÒªÇó
££±¸·Ý³öÀ´µÄÊý¾Ý»Ö¸´Ê±Òª»Ö¸´³É²»Í¬µÄ±íÃû
££»Ö¸´³öÀ´µÄÊý¾Ýʵ¼Ê´æ´¢Î»ÖÃÒ²Òª´æ´¢ÔÚÓëÔ±í²»Í¬µÄλÖÃ
ÎÊÌâ
Èç¹ûÖ±½Ómysqldump£sourc ......
1.mysqlµ¼³öÊý¾Ý¿âÃüÁî
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû (±íÃû) > c:\\ÎļþÃû.sql
2.mysqlµ¼ÈëÊý¾Ý¿âÃüÁî
£¨1£©½â¾öÖÐÎÄÂÒÂëÎÊÌ⣺ִÐÐÈçÏÂÃüÁîÔÙµ¼È룺set names utf8
£¨2£©µÇ¼mysql£¬Ö´ÐÐÈçÏÂÃüÁ
mysql> source c:\\ÎļþÃû.sql;
3½â¾öÖÐÎÄÂÒÂëÎÊÌ⣺ִР......
1. Á¬½Ómysql:
mysql -h Ö÷»úµØÖ· -u Óû§Ãû £p
2.Í˳ömysql:exit
3. ÐÞ¸ÄÃÜÂë:
mysqlbinmysqladmin -uroot -p(oldpassword) password newpassword
4.Ôö¼ÓÓû§£º
Ìí¼ÓÒ»¸öÓû§test1 ÃÜÂëΪABC£»ÈÃËû¿ÉÒÔÔÚÈκÎÖ÷»úÉϵǼ,²¢¶ÔËùÓÐÊý¾Ý¿âÓвéѯ¡¢²åÈë¡¢Ð޸ġ¢É¾³ýµÄȨÏÞ¡£Ê×ÏÈÓÃÒÔrootÓû§Á¬Èë
mysql,È»ºó¼üÈëÒÔÏÂÃü ......
MySQL´æ´¢¹ý³ÌµÄ²ÎÊýÓÃÔÚ´æ´¢¹ý³ÌµÄ¶¨Ò壬¹²ÓÐÈýÖÖ²ÎÊýÀàÐÍ,IN,OUT,INOUT,ÐÎʽÈ磺
CREATE PROCEDURE([[IN |OUT |INOUT ] ²ÎÊýÃû Êý¾ÝÀàÐÎ...])
1¡¢IN ÊäÈë²ÎÊý:±íʾ¸Ã²ÎÊýµÄÖµ±ØÐëÔÚµ÷Óô洢¹ý³Ìʱָ¶¨£¬ÔÚ´æ´¢¹ý³ÌÖÐÐ޸ĸòÎÊýµÄÖµ²»Äܱ»·µ»Ø£¬ÎªÄ¬ÈÏÖµ
2¡¢OUT Êä³ö²ÎÊý:¸ÃÖµ¿ÉÔÚ´æ´¢¹ý³ÌÄÚ²¿±»¸Ä±ä£¬²¢¿É·µ»Ø
3¡¢I ......
񡜧
CREATE TABLE `user` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`password` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
ÆäÖÐÓÐÊý¾ÝΪ£º
1 name1 123456
2&nbs ......