{"id":2909,"date":"2014-04-28T16:15:08","date_gmt":"2014-04-28T08:15:08","guid":{"rendered":"https:\/\/sdeno.com\/?p=2909"},"modified":"2015-11-17T16:13:30","modified_gmt":"2015-11-17T08:13:30","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93","status":"publish","type":"post","link":"https:\/\/sdeno.com\/?p=2909","title":{"rendered":"mysql\u6570\u636e\u5e93\u7b14\u8bb0"},"content":{"rendered":"

mysql\u7b14\u8bb0<\/p>\n

sql\u7c7b\u578b<\/p>\n

TEXT\uff1a\u5907\u6ce8\u5927\u6587\u7ae0\uff0c\u5e16\u5b50\uff0c\u65b0\u95fb\u5185\u5bb9<\/p>\n

VARCHAR\uff1a\u7528\u6237\u540d\uff0c\u6587\u7ae0\u6807\u9898<\/p>\n

CHAR\uff1a\u5bc6\u7801\uff0c\u6027\u522b\uff08\u8bbf\u95ee\u901f\u5ea6\u5feb\uff09<\/p>\n

SHOW DATABASES; \/\/\u67e5\u770b\u5168\u90e8\u6570\u636e\u5e93<\/p>\n

USE \u6570\u636e\u5e93; \/\/\u9009\u62e9\u64cd\u4f5c\u7684\u6570\u636e\u5e93\uff0c\u8fd9\u6837\u624d\u80fd\u5bf9\u6570\u636e\u5e93\u7684\u8868\u8fdb\u884c\u64cd\u4f5c<\/p>\n

SELECT DATABASE(); \/\/\u67e5\u770b\u5f53\u524d\u9009\u4e2d\u7684\u54ea\u4e2a\u6570\u636e\u5e93<\/p>\n

SHOW TABLES; \/\/\u67e5\u770b\u5f53\u524d\u9009\u4e2d\u6570\u636e\u5e93\u7684\u8868<\/p>\n

SELECT * FROM \u8868\u540d; \/\/\u67e5\u770b\u8868\u540d\u5185\u5bb9<\/p>\n

SET NAMES GBK;\/\/\u8bbe\u7f6e\u4e2d\u6587\u7f16\u7801<\/p>\n

CREATE DATABASE \u5e93\u540d;\/\/\u521b\u5efa\u4e00\u4e2a\u5e93
\nCREATE TABLE \u8868\u540d( \/\/\u540c\u65f6\u521b\u5efa\u8868\uff0c\u5b57\u6bb5\u540d
\n\u5b57\u6bb5\u540d VARCHAR(20),
\n\u5b57\u6bb5\u540d CHAR(1),
\n\u5b57\u6bb5\u540d DATETIME);
\nDESCRIBE \u8868\u540d\/\/\u67e5\u770b\u8868\u7ed3\u6784<\/p>\n

SELECT * FROM \u8868\u540d WHERE \u5b57\u6bb5\u540d=’\u7b5b\u9009\u5185\u5bb9’;\/\/\u7b5b\u9009\u6307\u5b9a\u6761\u4ef6\u7684\u6240\u6709\u6570\u636e<\/p>\n

DELETE * FROM \u8868\u540d WHERE \u5b57\u6bb5\u540d=’\u7b5b\u9009\u5185\u5bb9’;\/\/\u5220\u9664\u7b5b\u9009\u6307\u5b9a\u6761\u4ef6\u7684\u6240\u6709\u6570\u636e<\/p>\n

SELECT * FROM \u8868\u540d ORDER BY \u65f6\u95f4\u7684\u5b57\u6bb5 DESC; \/\/\u6309\u7167\u65f6\u95f4\u5012\u5e8f\u6392\u5217\u6570\u636e\uff08\u6b63\u5e8fASC\uff09<\/p>\n

DROP TABLE \u8868\u540d; \/\/\u5220\u9664\u8868\u540d<\/p>\n

DROP DATABASE \u5e93\u540d; \/\/\u5220\u9664\u5e93<\/p>\n

UPDATE \u8868\u540d SET \u5b57\u6bb5\u540d =’\u4fee\u6539\u7684\u5185\u5bb9’ WHERE \u4e3b\u952e\u5b57\u6bb5=\u5e8f\u53f7;\/\/\u4fee\u6539\u6570\u636e<\/p>\n

\u63d2\u5165\u6570\u636e
\ninsert into \u8868 valus(‘\u503c1′,’\u503c2′,’\u503c3’,..);\/\/\u63d2\u5165\u7684\u503c\u987a\u5e8f\u662f\u8ddf\u5b57\u6bb5\u7684\u987a\u5e8f\u4e00\u6837\u3002<\/p>\n

insert into \u8868(\u5b57\u6bb51,\u5b57\u6bb52,\u5b57\u6bb53,..) values (‘\u5b57\u6bb51\u503c’,’\u5b57\u6bb52\u503c’,’\u5b57\u6bb53\u503c’,..)\/\/\u5bf9\u5e94<\/p>\n

\u66f4\u65b0
\nupdate \u8868 set \u5b57\u6bb5=’\u4fee\u6539\u5185\u5bb9’,\u5b57\u6bb5=’\u4fee\u6539\u5185\u5bb9’ where id=’\u6839\u636eID\u627e\u5230\u8981\u4fee\u6539\u7684\u90a3\u6761\u6570\u636e’;<\/p>\n

\u5220\u9664
\ndelete from \u8868 where id=2;
\nmySQL\u51fd\u6570<\/p>\n

mySQL\u5e38\u7528\u51fd\u6570\u53ef\u4ee5\u8ddfPHP\u914d\u7f6e\u64cd\u4f5c\u6570\u636e\u5e93
\n\u6587\u672c\u51fd\u6570
\nCONCAT() \/\/ SELECT CONCAT(‘Mrs.’,’chen’); \u8fd4\u56de Mrs.chen \u5b57\u7b26\u7a9c\u76f8\u8fde\u529f\u80fd<\/p>\n

LENGTH() \/\/ SELECT LENGTH(‘chen’); \u8fd4\u56de3 \u68c0\u6d4b\u5b57\u7b26\u4e32\u957f\u5ea6<\/p>\n

LEFT() \/\/ SELECT LEFT(‘Lee’,2) \u8fd4\u56deLe ,\u8fd4\u56de\u5b57\u7b26\u4e32\u524d\u4e24\u4f4d\u5185\u5bb9<\/p>\n

RIGHT() \/\/\u540c\u4e0a<\/p>\n

TRIM() \/\/ SELECT TRIM(‘ 123 ‘) AS TRIM; \u5220\u9664\u591a\u4f59\u7684\u7a7a\u683c<\/p>\n

SUBSTRING() \/\/SELECT SUBSTRING(‘nihao 12’,2,5); \u8fd4\u56deihao ,\u7d22\u5f15\u662f1\uff0c\u4ece\u7b2c\u4e8c\u4e2a\u4f4d\u7f6e\u53d65\u4e2a\u5305\u62ec\u7b2c\u4e8c\u4e2a\u3002<\/p>\n

MD5() \/\/ MD5(‘123’)<\/p>\n

<\/div>
<\/div><\/div><\/div>\"sql_fun\"<\/figure><\/a>
\n———————————————<\/p>\n

\u6570\u5b57\u51fd\u6570
\nABS() \/\/ SELECT ABS(-5) \u8d1f5\u7edd\u5bf9\u503c\u8fd4\u56de5
\nROUND() \/\/ ROUND(4.66) \u8fd4\u56de5 \u56db\u820d\u4e94\u5165
\nFLOOR() \/\/FLOOR(4,66) \u8fd4\u56de4 \u53ea\u53d6\u6574\u6570
\nCEILING() \/\/ CEILING(4.01) \u8fd4\u56de5 \u8fd4\u56de\u6700\u5927\u6574\u6570
\nRAND() \/\/RAND()*10 0\u523010\u7684\u968f\u673a\u6570
\n———————————————<\/p>\n

\u65e5\u671f\u51fd\u6570<\/p>\n

\u6700\u5e38\u7528\u7684\u662fNOW()<\/p>\n

<\/div>
<\/div><\/div><\/div>\"sql_time\"<\/figure><\/a>
\n———————————————<\/p>\n

\u65e5\u671f\u683c\u5f0f
\nDATE_FORMAT()<\/p>\n

SELECT DATE_FORMAT(NOW(),’%A:%D:%C’)<\/p>\n

<\/div>
<\/div><\/div><\/div>\"sql_time_style\"<\/figure><\/a>\n

 <\/p>\n

<\/div>
<\/div><\/div><\/div>\"sql_time_style2\"<\/figure><\/a>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\u4ee5\u4e0b\u662f\u5144\u5f1f\u8fdePHP\u6559\u7a0b\u8bb0\u5f55<\/p>\n

1\uff0c\u6570\u5b57\u578b
\n\u6574\u5f62
\nTINYINT 1\u5b57\u8282 (-128\uff0c127) (0\uff0c255) \u5c0f\u6574\u6570\u503c<\/p>\n

create table \u8868\u540d(\u5b57\u6bb5\u540d TINYINT);
\ncreate table \u8868\u540d(\u5b57\u6bb5\u540d TINYINT UNSIGNED); \/\/\u521b\u5efa\u65e0\u7b26\u53f7\u3002<\/p>\n

SMALLINT 2 \u5b57\u8282 (-32 768\uff0c32 767) (0\uff0c65 535) \u5927\u6574\u6570\u503c<\/p>\n

MEDIUMINT 3 \u5b57\u8282 (-8 388 608\uff0c8 388 607) (0\uff0c16 777 215) \u5927\u6574\u6570\u503c<\/p>\n

INT\u6216INTEGER 4 \u5b57\u8282 (-2 147 483 648\uff0c2 147 483 647) (0\uff0c4 294 967 295) \u5927\u6574\u6570\u503c<\/p>\n

BIGINT 8 \u5b57\u8282 (-9 233 372 036 854 775 808\uff0c9 223 372 036 854 775 807) (0\uff0c18 446 744 073 709 551 615) \u6781\u5927\u6574\u6570\u503c
\n\u6d6e\u70b9\uff08\u5c0f\u6570\uff09
\nfloat(\u4e00\u5171\u51e0\u4f4d,\u4fdd\u7559\u51e0\u4f4d\u5c0f\u6570) 4\u5b57\u8282<\/p>\n

create table \u8868\u540d(\u5b57\u6bb5\u540d float(5,2)); \/\/ \u4f8b\u5982\u8f93\u51fa123.12<\/p>\n

double()<\/p>\n

decimal()
\n2\uff0c\u5b57\u7b26\u4e32\u7c7b\u578b<\/p>\n

char(\u53ef\u81ea\u5b9a\u4e49\u957f\u5ea6) \u6700\u957f255 \u56fa\u5b9a\u957f\u5ea6 \/\/\u9002\u7528 \u6027\u522b\uff0c\u4e0d\u7ba1\u7528\u4e0d\u7528\u90fd\u5360\u7528\u7a7a\u95f4\uff0c\u4f46\u662f\u8fd0\u884c\u901f\u5ea6\u5feb<\/p>\n

varchar(\u53ef\u81ea\u5b9a\u4e49\u957f\u5ea6) \u6700\u957f255 \u53ef\u53d8\u957f\u5ea6 \/\/\u9002\u7528\u6587\u7ae0\u6807\u9898\u4e0d\u7b97\u957f\u800c\u53c8\u53d8\u5316\u5927\u7684\uff0c\u667a\u80fd\u5220\u9664\u672b\u5c3e\u7684\u7a7a\u683c\u8282\u7701\u7a7a\u95f4\uff0c\u4f46\u964d\u4f4e\u6027\u80fd<\/p>\n

mediumtext<\/p>\n

longtext \/\/\u9002\u7528\u4e8e\u6587\u7ae0\u65e5\u8bb0\u3002<\/p>\n

enum(“\u503c1″,”\u503c2″,”\u503c3”); \/\/\u679a\u4e3e \u4e00\u4e2a\u503c\u53ea\u80fd\u7528\u4e00\u6b21 \u9002\u7528\u4e8e\u56fa\u5b9a\u503c\uff0c\u4f8b\u5982\u661f\u671f\u65e5\u671f\uff0c\u7537\uff0c\u5973\uff0c\u4fdd\u5bc6<\/p>\n

set(“\u503c1″,”\u503c2″,”\u503c3″); \/\/\u96c6\u5408 \u4e00\u4e2a\u503c\u80fd\u591a\u6b21
\n\u4f8b\u5982
\nvalues(‘a,b’,’a,b’) \/\/\u679a\u4e3e\u53ea\u80fd\u663e\u793aa \u96c6\u5408\u80fd\u663e\u793aa,b
\n3\uff0c\u65e5\u671f\u578b(\u521b\u5efa\u8868\u65f6\u6700\u597d\u4e0d\u8981\u7528\u8fd9\u4e9b\u65f6\u95f4\u683c\u5f0f\uff0c\u5728php\u7a0b\u5e8f\u4e2d\u6700\u597d\u4f7f\u7528php\u81ea\u5e26\u7684\u65f6\u95f4\u51fd\u6570)<\/p>\n

date 0000-00-00<\/p>\n

time 00:00:00<\/p>\n

datetime 0000-00-00 00:00:00<\/p>\n

timestamp 0000-00-00 00:00:00<\/p>\n

year 0000<\/p>\n

 <\/p>\n

 
\n\u5b57\u6bb5\u9644\u52a0\u5c5e\u6027<\/p>\n

1, unsigned \u65e0\u7b26\u53f7 \u53ea\u80fd\u7528\u5728\u6570\u503c\u578b<\/p>\n

2, zerofill \u53ea\u80fd\u7528\u5728\u6570\u503c\u578b \u4f8b\u5982 (a int(5) zerofill) values(88) \/\/\u8f93\u51fa\u662f00088 \uff0c\u4f1a\u81ea\u52a8\u52a0unsigned<\/p>\n

3, auto_increment \u53ea\u80fd\u662f\u6574\u6570 \u6bcf\u589e\u52a0\u4e00\u5929\u6570\u636e \u5c31\u81ea\u52a8\u52a01 ,\u5373\u4f7f\u539f\u6765\u6700\u5927\u503c\u7684\u5220\u9664\u4e86\u7ee7\u7eed\u5f80\u540e\u9762\u8ffd\u52a0\uff0c\u4f8b\u5982100\u5220\u9664\u5982\u679c\u8fd8\u8981\u7ee7\u7eed\u52a0\u5c31\u662f101\uff0c\u6bcf\u4e2a\u8868\u6700\u597d\u6709\u4e00\u4e2aID\u81ea\u52a8\u589e\u957f\u7684\u3002<\/p>\n

4, null\u548cnot null \u5b57\u6bb5\u9ed8\u8ba4\u662fnull\uff0c\u5982\u679c\u4e0d\u8bbe\u7f6e\u503c\u5b57\u6bb5\u5c31\u663e\u793a\u7a7a\uff1b\u5982\u679c\u8bbe\u7f6enot null\u4e0d\u7ed9\u53ea\u5c31\u81ea\u52a8\u8d4b\u503c0\uff0c\uff08\u63a8\u8350\u7ed9\u6bcf\u4e2a\u5b57\u6bb5\u8bbe\u7f6enot null\uff09<\/p>\n

5, default \u8bbe\u7f6e\u9ed8\u8ba4\u503c\uff0c\u6ca1\u7ed9\u5b57\u6bb5\u8bbe\u7f6e\u503c\u7684\u65f6\u5019\u5c31\u663e\u793a\u9ed8\u8ba4\u503c\uff08\u63a8\u8350\u548cnot null\u914d\u5408\u4f7f\u7528\uff09<\/p>\n

\u4f8b\u5b50\uff1a
\ncreate table users(
\nid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(30) NOT NULL DEFAULT ”,
\nheight DOUBLE(10,2) NOT NULL DEFAULT 0.00,
\nage INT NOT NULL DEFAULT 0,
\nsex CHAR(4) NOT NULL DEFAULT ‘\u7537’
\n);<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\u521b\u5efa\u7d22\u5f15<\/p>\n

1,\u4e3b\u952e\u7d22\u5f15 primary key
\n\u6700\u597d\u6bcf\u5f20\u8868\u90fd\u6709\u4e00\u4e2a\u4e3b\u952e\u7d22\u5f15,\u8fd9\u6837\u80fd\u901a\u8fc7\u4e3b\u952e\u7d22\u5f15\u7684\u552f\u4e00\u6027\u627e\u5230\u8868\u91cc\u7684\u6570\u636e(\u4e3b\u952e\u7d22\u5f15\u7684\u503c\u4e0d\u80fd\u91cd\u590d)
\n\u4e00\u5f20\u8868\u53ea\u80fd\u4e00\u4e2a\u4e3b\u952e
\n\u4e3b\u952e\u503c\u4e0d\u80fd\u4e3a\u7a7a<\/p>\n

2,\u552f\u4e00\u7d22\u5f15 unique
\n\u53ef\u4ee5\u5728\u591a\u4e2a\u5b57\u6bb5\u63d2\u5165unique\uff0c\u662f\u4e3a\u4e86\u9632\u6b62\u6570\u636e\u7684\u91cd\u590d\uff0c\u4f8b\u5982\u9632\u6b62\u7528\u6237\u540d\u6ce8\u518c\u7684\u91cd\u590d\u3002
\n\u552f\u4e00\u7d22\u5f15\u9632\u6b62\u6570\u636e\u7684\u91cd\u590d
\n3,\u5e38\u89c4\u7d22\u5f15
\n\u63d0\u9ad8\u67e5\u8be2\u529f\u80fd\u7684\u6027\u80fd,\u4f46\u662f\u964d\u4f4e\u4e86\u63d2\u5165,\u5220\u9664,\u4fee\u6539\u7684\u6027\u80fd,\u53ef\u4ee5\u5728\u591a\u4e2a\u5b57\u6bb5\u521b\u5efa\u6b64\u7d22\u5f15,
\n\u9700\u8981\u7528\u4e8e\u67e5\u8be2\u7684\u5b57\u6bb5\u624d\u5efa\u7acb\u6b64\u7d22\u5f15.
\n\u8ffd\u52a0\u521b\u5efa\u4f8b\u5b501,
\nindex \u7d22\u5f15\u540d(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2)<\/p>\n

\u5355\u72ec\u521b\u5efa\u4f8b\u5b502,
\ncreate index \u7d22\u5f15\u540d on \u8868\u540d(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2); \/\/\u591a\u4e2a\u5b57\u6bb5\u521b\u5efa\u4e00\u4e2a\u7d22\u5f15
\n\u5220\u9664\u4f8b\u5b50\uff1a
\ndrop index \u7d22\u5f15\u540d on \u8868\u540d; \/\/\u4e00\u4e2a\u5b57\u6bb5\u5355\u72ec\u521b\u5efa\u7d22\u5f15
\n4,\u5168\u6587\u7d22\u5f15
\n?\u7528\u7684\u65f6\u5019\u5728\u770b<\/p>\n

 <\/p>\n

 
\n\u6570\u636e\u5e93\u8868\u7c7b\u578b\u53ca\u5b58\u50a8\u4f4d\u7f6e<\/p>\n

\u8868\u7c7b\u578b\u5e38\u7528\u7684\u662f MyISAM \u548c InnoDB ,\u521b\u5efa\u8868\u9ed8\u8ba4\u662f MyISAM\u7c7b\u578b
\n\u4e8b\u52a1\u5904\u7406 \u4e0d\u652f\u6301 \u652f\u6301
\n\u6570\u636e\u884c\u9501\u5b9a \u4e0d\u652f\u6301 \u652f\u6301
\n\u5916\u952e\u7ea6\u675f \u4e0d\u652f\u6301 \u652f\u6301
\n\u8868\u7a7a\u95f4\u5360\u7528 \u76f8\u5bf9\u5c0f \u76f8\u5bf9\u5927
\n\u5168\u6587\u7d22\u5f15 \u652f\u6301 \u4e0d\u652f\u6301
\n\u4f8b\u5b50
\ncreate table \u8868\u540d(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2) type = InnoDB;<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

mysql\u9ed8\u8ba4\u5b57\u7b26\u96c6<\/p>\n

???\u6ca1\u61c2
\n\u4fee\u6539\u8868
\nalter table \u8868\u540d add \u5b57\u6bb5\u540d varchar(30) not null;\/\/ \u6dfb\u52a0\u8868
\nalter table \u8868\u540d add \u5b57\u6bb5\u540d1 varchar(30) not null after \u5b57\u6bb5\u540d2;\/\/\u5728\u5b57\u6bb5\u540d2\u7684\u540e\u9762\u63d2\u5165\u5b57\u6bb5\u540d1
\nalter table \u8868\u540d add \u5b57\u6bb5\u540d varchar(30) first;\/\/\u6700\u524d\u9762\u63d2\u5165<\/p>\n

alter table \u8868\u540d modify \u5b57\u6bb5\u540d char(3); \/\/ \u4fee\u6539\u5b57\u6bb5\u7c7b\u578b\u4e3achar(3) modify\u7528\u4e8e\u6539\u5b57\u6bb5\u7c7b\u578b
\nalter table \u8868\u540d change \u539f\u5b57\u6bb5\u540d \u6539\u5b57\u6bb5\u540d varchar(30); \/\/\u4fee\u6539\u8868\u540d<\/p>\n

alter table \u539f\u8868\u540d rename as \u6539\u8868\u540d;\/\/\u4fee\u6539\u8868\u540d<\/p>\n

alter table \u8868\u540d drop \u5220\u9664\u7684\u5b57\u6bb5\u540d; \/\/\u5220\u9664\u5b57\u6bb5\u540d
\ndrop table \u8868\u540d\/\/\u5220\u9664\u8868\u540d<\/p>\n

mysql<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

mysql\u7b14\u8bb0 sql\u7c7b\u578b TEXT\uff1a\u5907\u6ce8\u5927\u6587\u7ae0\uff0c\u5e16\u5b50\uff0c\u65b0\u95fb\u5185\u5bb9 VARCHAR\uff1a\u7528\u6237\u540d\uff0c\u6587\u7ae0\u6807\u9898 CHAR\uff1a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-2909","post","type-post","status-publish","format-standard","hentry","category-9"],"_links":{"self":[{"href":"https:\/\/sdeno.com\/index.php?rest_route=\/wp\/v2\/posts\/2909","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdeno.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdeno.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdeno.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sdeno.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2909"}],"version-history":[{"count":0,"href":"https:\/\/sdeno.com\/index.php?rest_route=\/wp\/v2\/posts\/2909\/revisions"}],"wp:attachment":[{"href":"https:\/\/sdeno.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2909"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdeno.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2909"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdeno.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2909"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}