{"id":226,"title":"sql\u7ec3\u4e60","good":0,"bad":0,"hit":2152,"created_at":"2016-05-06 17:57:36","content":"

mysql\u590d\u4e60\u4e00:\u590d\u4e60\u524d\u7684\u51c6\u59071:\u786e\u8ba4\u4f60\u5df2\u5b89\u88c5wamp2:\u786e\u8ba4\u4f60\u5df2\u5b89\u88c5ecshop,\u5e76\u4e14ecshop\u7684\u6570\u636e\u5e93\u540d\u4e3ashop\u4e8c\t\u57fa\u7840\u77e5\u8bc6:1.\u6570\u636e\u5e93\u7684\u8fde\u63a5mysql -u -p -h-u \u7528\u6237\u540d-p \u5bc6\u7801-h host\u4e3b\u673a2:\u5e93\u7ea7\u77e5\u8bc62.1 \u663e\u793a\u6570\u636e\u5e93: show databases;2.2 \u9009\u62e9\u6570\u636e\u5e93: use dbname;2.3 \u521b\u5efa\u6570\u636e\u5e93: create database dbname charset utf8;2.3 \u5220\u9664\u6570\u636e\u5e93: drop database dbname;3: \u8868\u7ea7\u64cd\u4f5c:3.1 \u663e\u793a\u5e93\u4e0b\u9762\u7684\u8868show tables;3.2 \u67e5\u770b\u8868\u7684\u7ed3\u6784: desc tableName;3.3 \u67e5\u770b\u8868\u7684\u521b\u5efa\u8fc7\u7a0b: show create table  tableName;3.4 \u521b\u5efa\u8868:\u3000create table tbName (\u5217\u540d\u79f01\u3000\u5217\u7c7b\u578b\u3000[\u5217\u53c2\u6570]\u3000[not null default ],....\u52172.......\u5217\u540d\u79f0N\u3000\u5217\u7c7b\u578b\u3000[\u5217\u53c2\u6570]\u3000[not null default ])engine myisam\/innodb charset utf8\/gbk3.4\u7684\u4f8b\u5b50:create table user (    id int auto_increment,    name varchar(20) not null default '',    age tinyint unsigned not null default 0,   index id (id)   )engine=innodb charset=utf8;\u6ce8:innodb\u662f\u8868\u5f15\u64ce,\u4e5f\u53ef\u4ee5\u662fmyisam\u6216\u5176\u4ed6,\u4f46\u6700\u5e38\u7528\u7684\u662fmyisam\u548cinnodb,charset \u5e38\u7528\u7684\u6709utf8,gbk;3.5 \u4fee\u6539\u88683.5.1\t\u4fee\u6539\u8868\u4e4b\u589e\u52a0\u5217:alter table tbName add \u5217\u540d\u79f0\uff11\u3000\u5217\u7c7b\u578b\u3000[\u5217\u53c2\u6570]\u3000[not null default ]\u3000#(add\u4e4b\u540e\u7684\u65e7\u5217\u540d\u4e4b\u540e\u7684\u8bed\u6cd5\u548c\u521b\u5efa\u8868\u65f6\u7684\u5217\u58f0\u660e\u4e00\u6837)3.5.2\t\u4fee\u6539\u8868\u4e4b\u4fee\u6539\u5217alter table tbNamechange \u65e7\u5217\u540d  \u65b0\u5217\u540d  \u5217\u7c7b\u578b\u3000[\u5217\u53c2\u6570]\u3000[not null default ](\u6ce8:\u65e7\u5217\u540d\u4e4b\u540e\u7684\u8bed\u6cd5\u548c\u521b\u5efa\u8868\u65f6\u7684\u5217\u58f0\u660e\u4e00\u6837)3.5.3\t\u4fee\u6539\u8868\u4e4b\u51cf\u5c11\u5217:alter table tbName drop \u5217\u540d\u79f0;3.5.4\t\u4fee\u6539\u8868\u4e4b\u589e\u52a0\u4e3b\u952ealter table tbName add primary key(\u4e3b\u952e\u6240\u5728\u5217\u540d);\u4f8b:alter table goods add primary key(id)\u8be5\u4f8b\u662f\u628a\u4e3b\u952e\u5efa\u7acb\u5728id\u5217\u4e0a3.5.5\t\u4fee\u6539\u8868\u4e4b\u5220\u9664\u4e3b\u952ealter table tbName\u3000drop primary key;3.5.6\t\u4fee\u6539\u8868\u4e4b\u589e\u52a0\u7d22\u5f15alter table tbName add [unique|fulltext] index \u7d22\u5f15\u540d(\u5217\u540d);3.5.7\t\u4fee\u6539\u8868\u4e4b\u5220\u9664\u7d22\u5f15alter table tbName drop index \u7d22\u5f15\u540d;3.5.8\t\u6e05\u7a7a\u8868\u7684\u6570\u636etruncate tableName;4:\u5217\u7c7b\u578b\u8bb2\u89e3\u5217\u7c7b\u578b:        \u6574\u578b:tinyint (0~255\/-128~127) smallint (0~65535\/-32768~32767) mediumint int bigint (\u53c2\u8003\u624b\u518c11.2)        \u53c2\u6570\u89e3\u91ca:        unsigned \u65e0\u7b26\u53f7(\u4e0d\u80fd\u4e3a\u8d1f)  zerofill 0\u586b\u5145  M \u586b\u5145\u540e\u7684\u5bbd\u5ea6        \u4e3e\u4f8b:tinyint unsigned;             tinyint(6) zerofill;   \u6570\u503c\u578b        \u6d6e\u70b9\u578b:float double        \u683c\u5f0f:float(M,D)  unsigned\\zerofill;\u5b57\u7b26\u578b        char(m) \u5b9a\u957f        varchar(m)\u53d8\u957f        text\u5217          \u5b9e\u5b58\u5b57\u7b26i        \u5b9e\u5360\u7a7a\u95f4            \u5229\u7528\u7387char(M)      0<=i<=M            M                i\/m<=100%varchar(M)    0<=i<=M          i+1,2             i\/i+1\/2<100%                   year       YYYY\t\u8303\u56f4:1901~2155. \u53ef\u8f93\u5165\u503c2\u4f4d\u548c4\u4f4d(\u598298,2012)\u65e5\u671f\u65f6\u95f4\u7c7b\u578b   date       YYYY-MM-DD \u5982:2010-03-14               time       HH:MM:SS\t\u5982:19:26:32               datetime   YYYY-MM-DD  HH:MM:SS \u5982:2010-03-14 19:26:32               timestamp  YYYY-MM-DD  HH:MM:SS \u7279\u6027:\u4e0d\u7528\u8d4b\u503c,\u8be5\u5217\u4f1a\u4e3a\u81ea\u5df1\u8d4b\u5f53\u524d\u7684\u5177\u4f53\u65f6\u95f4 5:\u589e\u5220\u6539\u67e5\u57fa\u672c\u64cd\u4f5c5.1 \u63d2\u5165\u6570\u636e \tinsert into \u8868\u540d(col1,col2,\u2026\u2026) values(val1,val2\u2026\u2026); -- \u63d2\u5165\u6307\u5b9a\u5217\tinsert into \u8868\u540d values (,,,,); -- \u63d2\u5165\u6240\u6709\u5217\tinsert into \u8868\u540d values\t-- \u4e00\u6b21\u63d2\u5165\u591a\u884c \t(val1,val2\u2026\u2026),\t(val1,val2\u2026\u2026),\t(val1,val2\u2026\u2026);5.3\u4fee\u6539\u6570\u636e\tupdate tablename \tset \tcol1=newval1,  \tcol2=newval2,\t...\t...\tcolN=newvalN\twhere \u6761\u4ef6;5.4\uff0c\u5220\u9664\u6570\u636e    delete from tablenaeme where \u6761\u4ef6;5.5\uff0c    select     \u67e5\u8be2  \uff081\uff09  \u6761\u4ef6\u67e5\u8be2   where  a. \u6761\u4ef6\u8868\u8fbe\u5f0f\u7684\u610f\u4e49\uff0c\u8868\u8fbe\u5f0f\u4e3a\u771f\uff0c\u5219\u8be5\u884c\u53d6\u51fa\t\t\t   b.  \u6bd4\u8f83\u8fd0\u7b97\u7b26  = \uff0c!=\uff0c< > <=  >=                           c.  like , not like ('%'\u5339\u914d\u4efb\u610f\u591a\u4e2a\u5b57\u7b26,'_'\u5339\u914d\u4efb\u610f\u5355\u4e2a\u5b57\u7b26) \t\t\t\tin , not in , between and                           d. is null , is not null\t\t\t  \uff082\uff09  \u5206\u7ec4       group by \t\t\t\u4e00\u822c\u8981\u914d\u54085\u4e2a\u805a\u5408\u51fd\u6570\u4f7f\u7528:max,min,sum,avg,count  \uff083\uff09  \u7b5b\u9009       having  \uff084\uff09  \u6392\u5e8f       order by  \uff085\uff09  \u9650\u5236       limit6:\t\u8fde\u63a5\u67e5\u8be26.1\uff0c \u5de6\u8fde\u63a5\t.. left join .. on\ttable A left join table B on tableA.col1 = tableB.col2 ;  \u4f8b\u53e5:  select \u5217\u540d from table A left join table B on tableA.col1 = tableB.col22.  \u53f3\u94fe\u63a5: right join3.  \u5185\u8fde\u63a5:  inner join\u5de6\u53f3\u8fde\u63a5\u90fd\u662f\u4ee5\u5728\u5de6\u8fb9\u7684\u8868\u7684\u6570\u636e\u4e3a\u51c6,\u6cbf\u7740\u5de6\u8868\u67e5\u53f3\u8868.\u5185\u8fde\u63a5\u662f\u4ee5\u4e24\u5f20\u8868\u90fd\u6709\u7684\u5171\u540c\u90e8\u5206\u6570\u636e\u4e3a\u51c6,\u4e5f\u5c31\u662f\u5de6\u53f3\u8fde\u63a5\u7684\u6570\u636e\u4e4b\u4ea4\u96c6.7\t\u5b50\u67e5\u8be2  where \u578b\u5b50\u67e5\u8be2:\u5185\u5c42sql\u7684\u8fd4\u56de\u503c\u5728where\u540e\u4f5c\u4e3a\u6761\u4ef6\u8868\u8fbe\u5f0f\u7684\u4e00\u90e8\u5206  \u4f8b\u53e5: select * from tableA where colA = (select colB from tableB where ...);    from \u578b\u5b50\u67e5\u8be2:\u5185\u5c42sql\u67e5\u8be2\u7ed3\u679c,\u4f5c\u4e3a\u4e00\u5f20\u8868,\u4f9b\u5916\u5c42\u7684sql\u8bed\u53e5\u518d\u6b21\u67e5\u8be2  \u4f8b\u53e5:select * from (select * from ...) as tableName where ....  8: \u5b57\u7b26\u96c6  \u5ba2\u670d\u7aefsql\u7f16\u7801 character_set_client  \u670d\u52a1\u5668\u8f6c\u5316\u540e\u7684sql\u7f16\u7801 character_set_connection  \u670d\u52a1\u5668\u8fd4\u56de\u7ed9\u5ba2\u6237\u7aef\u7684\u7ed3\u679c\u96c6\u7f16\u7801     character_set_results  \u5feb\u901f\u628a\u4ee5\u4e0a3\u4e2a\u53d8\u91cf\u8bbe\u4e3a\u76f8\u540c\u503c: set names \u5b57\u7b26\u96c6   \u5b58\u50a8\u5f15\u64ce engine=1\\2  1 Myisam  \u901f\u5ea6\u5feb \u4e0d\u652f\u6301\u4e8b\u52a1 \u56de\u6eda  2 Innodb  \u901f\u5ea6\u6162 \u652f\u6301\u4e8b\u52a1,\u56de\u6eda    \u2460\u5f00\u542f\u4e8b\u52a1          start transaction  \u2461\u8fd0\u884csql;            \u2462\u63d0\u4ea4,\u540c\u65f6\u751f\u6548\\\u56de\u6eda commit\\rollback  \u89e6\u53d1\u5668 trigger  \u76d1\u89c6\u5730\u70b9:\u8868  \u76d1\u89c6\u884c\u4e3a:\u589e \u5220 \u6539  \u89e6\u53d1\u65f6\u95f4:after\\before  \u89e6\u53d1\u4e8b\u4ef6:\u589e \u5220 \u6539  \u521b\u5efa\u89e6\u53d1\u5668\u8bed\u6cd5\tcreate trigger tgName\tafter\/before insert\/delete\/update \ton tableName\tfor each row\tsql; -- \u89e6\u53d1\u8bed\u53e5\t  \u5220\u9664\u89e6\u53d1\u5668:drop trigger tgName; \u7d22\u5f15 \u63d0\u9ad8\u67e5\u8be2\u901f\u5ea6,\u4f46\u662f\u964d\u4f4e\u4e86\u589e\u5220\u6539\u7684\u901f\u5ea6,\u6240\u4ee5\u4f7f\u7528\u7d22\u5f15\u65f6,\u8981\u7efc\u5408\u8003\u8651. \u7d22\u5f15\u4e0d\u662f\u8d8a\u591a\u8d8a\u597d,\u4e00\u822c\u6211\u4eec\u5728\u5e38\u51fa\u73b0\u4e8e\u6761\u4ef6\u8868\u8fbe\u5f0f\u4e2d\u7684\u5217\u52a0\u7d22\u5f15. \u503c\u8d8a\u5206\u6563\u7684\u5217\uff0c\u7d22\u5f15\u7684\u6548\u679c\u8d8a\u597d \u7d22\u5f15\u7c7b\u578b primary key\u4e3b\u952e\u7d22\u5f15 index \u666e\u901a\u7d22\u5f15 unique index \u552f\u4e00\u6027\u7d22\u5f15 fulltext index \u5168\u6587\u7d22\u5f15\u7efc\u5408\u7ec3\u4e60:\u8fde\u63a5\u4e0a\u6570\u636e\u5e93\u670d\u52a1\u5668\u521b\u5efa\u4e00\u4e2agbk\u7f16\u7801\u7684\u6570\u636e\u5e93\u5efa\u7acb\u5546\u54c1\u8868\u548c\u680f\u76ee\u8868,\u5b57\u6bb5\u5982\u4e0b:\u5546\u54c1\u8868:goodsgoods_id\u3000--\u4e3b\u952e,goods_name -- \u5546\u54c1\u540d\u79f0cat_id  -- \u680f\u76eeidbrand_id -- \u54c1\u724cidgoods_sn -- \u8d27\u53f7goods_number -- \u5e93\u5b58\u91cfshop_price  -- \u4ef7\u683cgoods_desc\u3000--\u5546\u54c1\u8be6\u7ec6\u63cf\u8ff0\u680f\u76ee\u8868:categorycat_id --\u4e3b\u952e cat_name -- \u680f\u76ee\u540d\u79f0parent_id -- \u680f\u76ee\u7684\u7236id\u5efa\u8868\u5b8c\u6210\u540e,\u4f5c\u4ee5\u4e0b\u64cd\u4f5c:\u5220\u9664goods\u8868\u7684goods_desc \u5b57\u6bb5,\u53ca\u8d27\u53f7\u5b57\u6bb5\u5e76\u589e\u52a0\u5b57\u6bb5:click_count  -- \u70b9\u51fb\u91cf\u5728goods_name\u5217\u4e0a\u52a0\u552f\u4e00\u6027\u7d22\u5f15\u5728shop_price\u5217\u4e0a\u52a0\u666e\u901a\u7d22\u5f15\u5728clcik_count\u5217\u4e0a\u52a0\u666e\u901a\u7d22\u5f15\u5220\u9664click_count\u5217\u4e0a\u7684\u7d22\u5f15\u5bf9goods\u8868\u63d2\u5165\u4ee5\u4e0b\u6570\u636e:+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+| goods_id | goods_name                   | cat_id | brand_id | goods_sn  | goods_number | shop_price | click_count |+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+|        1 | KD876                        |      4 |        8 | ECS000000 |           10 |    1388.00 |           7 ||        4 | \u8bfa\u57fa\u4e9aN85\u539f\u88c5\u5145\u7535\u5668          |      8 |        1 | ECS000004 |           17 |      58.00 |           0 ||        3 | \u8bfa\u57fa\u4e9a\u539f\u88c55800\u8033\u673a           |      8 |        1 | ECS000002 |           24 |      68.00 |           3 ||        5 | \u7d22\u7231\u539f\u88c5M2\u5361\u8bfb\u5361\u5668           |     11 |        7 | ECS000005 |            8 |      20.00 |           3 ||        6 | \u80dc\u521bKINGMAX\u5185\u5b58\u5361            |     11 |        0 | ECS000006 |           15 |      42.00 |           0 ||        7 | \u8bfa\u57fa\u4e9aN85\u539f\u88c5\u7acb\u4f53\u58f0\u8033\u673aHS-82 |      8 |        1 | ECS000007 |           20 |     100.00 |           0 ||        8 | \u98de\u5229\u6d669@9v                   |      3 |        4 | ECS000008 |           17 |     399.00 |           9 ||        9 | \u8bfa\u57fa\u4e9aE66                    |      3 |        1 | ECS000009 |           13 |    2298.00 |          20 ||       10 | \u7d22\u7231C702c                    |      3 |        7 | ECS000010 |            7 |    1328.00 |          11 ||       11 | \u7d22\u7231C702c                    |      3 |        7 | ECS000011 |            1 |    1300.00 |           0 ||       12 | \u6469\u6258\u7f57\u62c9A810                 |      3 |        2 | ECS000012 |            8 |     983.00 |          14 ||       13 | \u8bfa\u57fa\u4e9a5320 XpressMusic       |      3 |        1 | ECS000013 |            8 |    1311.00 |          13 ||       14 | \u8bfa\u57fa\u4e9a5800XM                 |      4 |        1 | ECS000014 |            4 |    2625.00 |           6 ||       15 | \u6469\u6258\u7f57\u62c9A810                 |      3 |        2 | ECS000015 |            3 |     788.00 |           8 ||       16 | \u6052\u57fa\u4f1f\u4e1aG101                 |      2 |       11 | ECS000016 |            0 |     823.33 |           3 ||       17 | \u590f\u65b0N7                       |      3 |        5 | ECS000017 |            1 |    2300.00 |           2 ||       18 | \u590f\u65b0T5                       |      4 |        5 | ECS000018 |            1 |    2878.00 |           0 ||       19 | \u4e09\u661fSGH-F258                 |      3 |        6 | ECS000019 |            0 |     858.00 |           7 ||       20 | \u4e09\u661fBC01                     |      3 |        6 | ECS000020 |           13 |     280.00 |          14 ||       21 | \u91d1\u7acb A30                     |      3 |       10 | ECS000021 |           40 |    2000.00 |           4 ||       22 | \u591a\u666e\u8fbeTouch HD               |      3 |        3 | ECS000022 |            0 |    5999.00 |          15 ||       23 | \u8bfa\u57fa\u4e9aN96                    |      5 |        1 | ECS000023 |            8 |    3700.00 |          17 ||       24 | P806                         |      3 |        9 | ECS000024 |          148 |    2000.00 |          36 ||       25 | \u5c0f\u7075\u901a\/\u56fa\u8bdd50\u5143\u5145\u503c\u5361        |     13 |        0 | ECS000025 |            2 |      48.00 |           0 ||       26 | \u5c0f\u7075\u901a\/\u56fa\u8bdd20\u5143\u5145\u503c\u5361        |     13 |        0 | ECS000026 |            2 |      19.00 |           0 ||       27 | \u8054\u901a100\u5143\u5145\u503c\u5361              |     15 |        0 | ECS000027 |            2 |      95.00 |           0 ||       28 | \u8054\u901a50\u5143\u5145\u503c\u5361               |     15 |        0 | ECS000028 |            0 |      45.00 |           0 ||       29 | \u79fb\u52a8100\u5143\u5145\u503c\u5361              |     14 |        0 | ECS000029 |            0 |      90.00 |           0 ||       30 | \u79fb\u52a820\u5143\u5145\u503c\u5361               |     14 |        0 | ECS000030 |            9 |      18.00 |           1 ||       31 | \u6469\u6258\u7f57\u62c9E8                   |      3 |        2 | ECS000031 |            1 |    1337.00 |           5 ||       32 | \u8bfa\u57fa\u4e9aN85                    |      3 |        1 | ECS000032 |            1 |    3010.00 |           9 |+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+\u4e09\t\u67e5\u8be2\u77e5\u8bc6\u6ce8:\u4ee5\u4e0b\u67e5\u8be2\u57fa\u4e8eecshop\u7f51\u7ad9\u7684\u5546\u54c1\u8868(ecs_goods)\u5728\u7ec3\u4e60\u65f6\u53ef\u4ee5\u53ea\u53d6\u90e8\u5206\u5217,\u65b9\u4fbf\u67e5\u770b.1: \u57fa\u7840\u67e5\u8be2 where\u7684\u7ec3\u4e60:\u67e5\u51fa\u6ee1\u8db3\u4ee5\u4e0b\u6761\u4ef6\u7684\u5546\u54c11.1:\u4e3b\u952e\u4e3a32\u7684\u5546\u54c1select goods_id,goods_name,shop_price     from ecs_goods     where goods_id=32;1.2:\u4e0d\u5c5e\u7b2c3\u680f\u76ee\u7684\u6240\u6709\u5546\u54c1select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id!=3;1.3:\u672c\u5e97\u4ef7\u683c\u9ad8\u4e8e3000\u5143\u7684\u5546\u54c1 select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where shop_price >3000;1.4:\u672c\u5e97\u4ef7\u683c\u4f4e\u4e8e\u6216\u7b49\u4e8e100\u5143\u7684\u5546\u54c1select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price <=100;1.5:\u53d6\u51fa\u7b2c4\u680f\u76ee\u6216\u7b2c11\u680f\u76ee\u7684\u5546\u54c1(\u4e0d\u8bb8\u7528or)select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id in (4,11);1.6:\u53d6\u51fa100<=\u4ef7\u683c<=500\u7684\u5546\u54c1(\u4e0d\u8bb8\u7528and)select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where shop_price between 100 and 500;1.7:\u53d6\u51fa\u4e0d\u5c5e\u4e8e\u7b2c3\u680f\u76ee\u4e14\u4e0d\u5c5e\u4e8e\u7b2c11\u680f\u76ee\u7684\u5546\u54c1(and,\u6216not in\u5206\u522b\u5b9e\u73b0)select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id!=3 and cat_id!=11;select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id not in (3,11);1.8:\u53d6\u51fa\u4ef7\u683c\u5927\u4e8e100\u4e14\u5c0f\u4e8e300,\u6216\u8005\u5927\u4e8e4000\u4e14\u5c0f\u4e8e5000\u7684\u5546\u54c1()select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;1.9:\u53d6\u51fa\u7b2c3\u4e2a\u680f\u76ee\u4e0b\u9762\u4ef7\u683c<1000\u6216>3000,\u5e76\u4e14\u70b9\u51fb\u91cf>5\u7684\u7cfb\u5217\u5546\u54c1select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods wherecat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;1.10:\u53d6\u51fa\u7b2c1\u4e2a\u680f\u76ee\u4e0b\u9762\u7684\u5546\u54c1(\u6ce8\u610f:1\u680f\u76ee\u4e0b\u9762\u6ca1\u5546\u54c1,\u4f46\u5176\u5b50\u680f\u76ee\u4e0b\u6709)select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods     where cat_id in (2,3,4,5);1.11:\u53d6\u51fa\u540d\u5b57\u4ee5"\u8bfa\u57fa\u4e9a"\u5f00\u5934\u7684\u5546\u54c1select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where goods_name like '\u8bfa\u57fa\u4e9a%';1.12:\u53d6\u51fa\u540d\u5b57\u4e3a"\u8bfa\u57fa\u4e9aNxx"\u7684\u624b\u673aselect goods_id,cat_id,goods_name,shop_price  from ecs_goods     where goods_name like '\u8bfa\u57fa\u4e9aN__';1.13:\u53d6\u51fa\u540d\u5b57\u4e0d\u4ee5"\u8bfa\u57fa\u4e9a"\u5f00\u5934\u7684\u5546\u54c1select goods_id,cat_id,goods_name,shop_price from ecs_goos     where goods_name not like '\u8bfa\u57fa\u4e9a%';1.14:\u53d6\u51fa\u7b2c3\u4e2a\u680f\u76ee\u4e0b\u9762\u4ef7\u683c\u57281000\u52303000\u4e4b\u95f4,\u5e76\u4e14\u70b9\u51fb\u91cf>5 "\u8bfa\u57fa\u4e9a"\u5f00\u5934\u7684\u7cfb\u5217\u5546\u54c1select goods_id,cat_id,goods_name,shop_price  from ecs_goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '\u8bfa\u57fa\u4e9a%';select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price between 1000 and 3000 and cat_id=3  and click_count>5 and goods_name like '\u8bfa\u57fa\u4e9a%';\u4e00\u9053\u9762\u8bd5\u9898\u6709\u5982\u4e0b\u8868\u548c\u6570\u7ec4\u628anum\u503c\u5904\u4e8e[20,29]\u4e4b\u95f4,\u6539\u4e3a20num\u503c\u5904\u4e8e[30,39]\u4e4b\u95f4\u7684,\u6539\u4e3a30mian\u8868+------+| num  |+------+|    3 ||   12 ||   15 ||   25 ||   23 ||   29 ||   34 ||   37 ||   32 ||   45 ||   48 ||   52 |+------+\u7ec3\u4e60\u9898:\u628agood\u8868\u4e2d\u5546\u54c1\u540d\u4e3a'\u8bfa\u57fa\u4e9axxxx'\u7684\u5546\u54c1,\u6539\u4e3a'HTCxxxx',\u63d0\u793a:\u5927\u80c6\u7684\u628a\u5217\u770b\u6210\u53d8\u91cf,\u53c2\u4e0e\u8fd0\u7b97,\u751a\u81f3\u8c03\u7528\u51fd\u6570\u6765\u5904\u7406 .substring(),concat()2\t\u5206\u7ec4\u67e5\u8be2group:2.1:\u67e5\u51fa\u6700\u8d35\u7684\u5546\u54c1\u7684\u4ef7\u683cselect max(shop_price) from ecs_goods;2.2:\u67e5\u51fa\u6700\u5927(\u6700\u65b0)\u7684\u5546\u54c1\u7f16\u53f7select max(goods_id) from ecs_goods;2.3:\u67e5\u51fa\u6700\u4fbf\u5b9c\u7684\u5546\u54c1\u7684\u4ef7\u683cselect min(shop_price) from ecs_goods;2.4:\u67e5\u51fa\u6700\u65e7(\u6700\u5c0f)\u7684\u5546\u54c1\u7f16\u53f7select min(goods_id) from ecs_goods;2.5:\u67e5\u8be2\u8be5\u5e97\u6240\u6709\u5546\u54c1\u7684\u5e93\u5b58\u603b\u91cfselect sum(goods_number) from ecs_goods;2.6:\u67e5\u8be2\u6240\u6709\u5546\u54c1\u7684\u5e73\u5747\u4ef7 select avg(shop_price) from ecs_goods;2.7:\u67e5\u8be2\u8be5\u5e97\u4e00\u5171\u6709\u591a\u5c11\u79cd\u5546\u54c1 select count(*) from ecs_goods;2.8:\u67e5\u8be2\u6bcf\u4e2a\u680f\u76ee\u4e0b\u9762\u6700\u8d35\u5546\u54c1\u4ef7\u683c\u6700\u4f4e\u5546\u54c1\u4ef7\u683c\u5546\u54c1\u5e73\u5747\u4ef7\u683c\u5546\u54c1\u5e93\u5b58\u91cf\u5546\u54c1\u79cd\u7c7b\u63d0\u793a:(5\u4e2a\u805a\u5408\u51fd\u6570,sum,avg,max,min,count\u4e0egroup\u7efc\u5408\u8fd0\u7528)select cat_id,max(shop_price) from ecs_goods  group by cat_id;3 having\u4e0egroup\u7efc\u5408\u8fd0\u7528\u67e5\u8be2:3.1:\u67e5\u8be2\u8be5\u5e97\u7684\u5546\u54c1\u6bd4\u5e02\u573a\u4ef7\u6240\u8282\u7701\u7684\u4ef7\u683cselect goods_id,goods_name,market_price-shop_price as j     from ecs_goods ;3.2:\u67e5\u8be2\u6bcf\u4e2a\u5546\u54c1\u6240\u79ef\u538b\u7684\u8d27\u6b3e(\u63d0\u793a:\u5e93\u5b58*\u5355\u4ef7)select goods_id,goods_name,goods_number*shop_price  from ecs_goods3.3:\u67e5\u8be2\u8be5\u5e97\u79ef\u538b\u7684\u603b\u8d27\u6b3eselect sum(goods_number*shop_price) from ecs_goods;3.4:\u67e5\u8be2\u8be5\u5e97\u6bcf\u4e2a\u680f\u76ee\u4e0b\u9762\u79ef\u538b\u7684\u8d27\u6b3e.select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;3.5:\u67e5\u8be2\u6bd4\u5e02\u573a\u4ef7\u7701\u94b1200\u5143\u4ee5\u4e0a\u7684\u5546\u54c1\u53ca\u8be5\u5546\u54c1\u6240\u7701\u7684\u94b1(where\u548chaving\u5206\u522b\u5b9e\u73b0)select goods_id,goods_name,market_price-shop_price  as k from ecs_goodswhere market_price-shop_price >200;select goods_id,goods_name,market_price-shop_price  as k from ecs_goodshaving k >200;3.6:\u67e5\u8be2\u79ef\u538b\u8d27\u6b3e\u8d85\u8fc72W\u5143\u7684\u680f\u76ee,\u4ee5\u53ca\u8be5\u680f\u76ee\u79ef\u538b\u7684\u8d27\u6b3eselect cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_idhaving k>200003.7:where-having-group\u7efc\u5408\u7ec3\u4e60\u9898\u6709\u5982\u4e0b\u8868\u53ca\u6570\u636e+------+---------+-------+| name | subject | score |+------+---------+-------+| \u5f20\u4e09 | \u6570\u5b66    |    90 || \u5f20\u4e09 | \u8bed\u6587    |    50 || \u5f20\u4e09 | \u5730\u7406    |    40 || \u674e\u56db | \u8bed\u6587    |    55 || \u674e\u56db | \u653f\u6cbb    |    45 || \u738b\u4e94 | \u653f\u6cbb    |    30 |+------+---------+-------+\u8981\u6c42:\u67e5\u8be2\u51fa2\u95e8\u53ca2\u95e8\u4ee5\u4e0a\u4e0d\u53ca\u683c\u8005\u7684\u5e73\u5747\u6210\u7ee9## \u4e00\u79cd\u9519\u8bef\u505a\u6cd5mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;+------+---+------------+| name | k | avg(score) |+------+---+------------+| \u5f20\u4e09     | 3 |    60.0000 || \u674e\u56db     | 2 |    50.0000 |+------+---+------------+2 rows in set (0.00 sec)mysql> select name,count(score<60) as k,avg(score) from stu group by name;+------+---+------------+| name | k | avg(score) |+------+---+------------+| \u5f20\u4e09     | 3 |    60.0000 || \u674e\u56db     | 2 |    50.0000 || \u738b\u4e94     | 1 |    30.0000 |+------+---+------------+3 rows in set (0.00 sec)mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;+------+---+------------+| name | k | avg(score) |+------+---+------------+| \u5f20\u4e09     | 3 |    60.0000 || \u674e\u56db     | 2 |    50.0000 |+------+---+------------+2 rows in set (0.00 sec)#\u52a0\u4e0a\u8d75\u516d\u540e\u9519\u8bef\u66b4\u9732mysql> insert into stu    -> values    -> ('\u8d75\u516d','A',100),    -> ('\u8d75\u516d','B',99),    -> ('\u8d75\u516d','C',98);Query OK, 3 rows affected (0.05 sec)Records: 3  Duplicates: 0  Warnings: 0#\u9519\u8bef\u663e\u73b0mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;+------+---+------------+| name | k | avg(score) |+------+---+------------+| \u5f20\u4e09 | 3 |    60.0000 || \u674e\u56db | 2 |    50.0000 || \u8d75\u516d | 3 |    99.0000 |+------+---+------------+3 rows in set (0.00 sec)#\u6b63\u786e\u601d\u8def,\u5148\u67e5\u770b\u6bcf\u4e2a\u4eba\u7684\u5e73\u5747\u6210\u7ee9mysql> select name,avg(score) from stu group by name;+------+------------+| name | avg(score) |+------+------------+| \u5f20\u4e09 |    60.0000 || \u674e\u56db |    50.0000 || \u738b\u4e94 |    30.0000 || \u8d75\u516d |    99.0000 |+------+------------+4 rows in set (0.00 sec)mysql> # \u770b\u6bcf\u4e2a\u4eba\u6302\u79d1\u60c5\u51b5mysql> select name,score < 60 from stu;+------+------------+| name | score < 60 |+------+------------+| \u5f20\u4e09 |          0 || \u5f20\u4e09 |          1 || \u5f20\u4e09 |          1 || \u674e\u56db |          1 || \u674e\u56db |          1 || \u738b\u4e94 |          1 || \u8d75\u516d |          0 || \u8d75\u516d |          0 || \u8d75\u516d |          0 |+------+------------+9 rows in set (0.00 sec)mysql> #\u8ba1\u7b97\u6bcf\u4e2a\u4eba\u7684\u6302\u79d1\u79d1\u76eemysql> select name,sum(score < 60) from stu group by name;+------+-----------------+| name | sum(score < 60) |+------+-----------------+| \u5f20\u4e09 |               2 || \u674e\u56db |               2 || \u738b\u4e94 |               1 || \u8d75\u516d |               0 |+------+-----------------+4 rows in set (0.00 sec)#\u540c\u65f6\u8ba1\u7b97\u6bcf\u4eba\u7684\u5e73\u5747\u5206mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;+------+-----------------+---------+| name | sum(score < 60) | pj      |+------+-----------------+---------+| \u5f20\u4e09 |               2 | 60.0000 || \u674e\u56db |               2 | 50.0000 || \u738b\u4e94 |               1 | 30.0000 || \u8d75\u516d |               0 | 99.0000 |+------+-----------------+---------+4 rows in set (0.00 sec)#\u5229\u7528having\u7b5b\u9009\u6302\u79d12\u95e8\u4ee5\u4e0a\u7684.mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2; +------+------+---------+| name | gk   | pj      |+------+------+---------+| \u5f20\u4e09 |    2 | 60.0000 || \u674e\u56db |    2 | 50.0000 |+------+------+---------+2 rows in set (0.00 sec)4:\torder by \u4e0e limit\u67e5\u8be24.1:\u6309\u4ef7\u683c\u7531\u9ad8\u5230\u4f4e\u6392\u5e8fselect goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;4.2:\u6309\u53d1\u5e03\u65f6\u95f4\u7531\u65e9\u5230\u665a\u6392\u5e8fselect goods_id,goods_name,add_time from ecs_goods order by add_time;4.3:\u63a5\u680f\u76ee\u7531\u4f4e\u5230\u9ad8\u6392\u5e8f,\u680f\u76ee\u5185\u90e8\u6309\u4ef7\u683c\u7531\u9ad8\u5230\u4f4e\u6392\u5e8fselect goods_id,cat_id,goods_name,shop_price from ecs_goods     order by cat_id ,shop_price desc;4.4:\u53d6\u51fa\u4ef7\u683c\u6700\u9ad8\u7684\u524d\u4e09\u540d\u5546\u54c1select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;4.5:\u53d6\u51fa\u70b9\u51fb\u91cf\u524d\u4e09\u540d\u5230\u524d5\u540d\u7684\u5546\u54c1select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;5\t\u8fde\u63a5\u67e5\u8be25.1:\u53d6\u51fa\u6240\u6709\u5546\u54c1\u7684\u5546\u54c1\u540d,\u680f\u76ee\u540d,\u4ef7\u683cselect goods_name,cat_name,shop_price from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_id;5.2:\u53d6\u51fa\u7b2c4\u4e2a\u680f\u76ee\u4e0b\u7684\u5546\u54c1\u7684\u5546\u54c1\u540d,\u680f\u76ee\u540d,\u4ef7\u683cselect goods_name,cat_name,shop_price from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_idwhere ecs_goods.cat_id = 4;5.3:\u53d6\u51fa\u7b2c4\u4e2a\u680f\u76ee\u4e0b\u7684\u5546\u54c1\u7684\u5546\u54c1\u540d,\u680f\u76ee\u540d,\u4e0e\u54c1\u724c\u540dselect goods_name,cat_name,brand_name from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_idleft join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_idwhere ecs_goods.cat_id = 4;5.4: \u7528\u53cb\u9762\u8bd5\u9898\u6839\u636e\u7ed9\u51fa\u7684\u8868\u7ed3\u6784\u6309\u8981\u6c42\u5199\u51faSQL\u8bed\u53e5\u3002Match \u8d5b\u7a0b\u8868\u5b57\u6bb5\u540d\u79f0\u5b57\u6bb5\u7c7b\u578b\u63cf\u8ff0matchIDint\u4e3b\u952ehostTeamIDint\u4e3b\u961f\u7684IDguestTeamIDint\u5ba2\u961f\u7684IDmatchResultvarchar(20)\u6bd4\u8d5b\u7ed3\u679c\uff0c\u5982\uff082:0\uff09matchTimedate\u6bd4\u8d5b\u5f00\u59cb\u65f6\u95f4Team \u53c2\u8d5b\u961f\u4f0d\u8868\u5b57\u6bb5\u540d\u79f0\u5b57\u6bb5\u7c7b\u578b\u63cf\u8ff0teamIDint\u4e3b\u952eteamNamevarchar(20)\u961f\u4f0d\u540d\u79f0Match\u7684hostTeamID\u4e0eguestTeamID\u90fd\u4e0eTeam\u4e2d\u7684teamID\u5173\u8054\u67e5\u51fa 2006-6-1 \u52302006-7-1\u4e4b\u95f4\u4e3e\u884c\u7684\u6240\u6709\u6bd4\u8d5b\uff0c\u5e76\u4e14\u7528\u4ee5\u4e0b\u5f62\u5f0f\u5217\u51fa\uff1a\u62dc\u4ec1  2\uff1a0 \u4e0d\u6765\u6885 2006-6-21mysql> select * from m;+-----+------+------+------+------------+| mid | hid  | gid  | mres | matime     |+-----+------+------+------+------------+|   1 |    1 |    2 | 2:0  | 2006-05-21 ||   2 |    2 |    3 | 1:2  | 2006-06-21 ||   3 |    3 |    1 | 2:5  | 2006-06-25 ||   4 |    2 |    1 | 3:2  | 2006-07-21 |+-----+------+------+------+------------+4 rows in set (0.00 sec)mysql> select * from t;+------+----------+| tid  | tname    |+------+----------+|    1 | \u56fd\u5b89     ||    2 | \u7533\u82b1     ||    3 | \u4f20\u667a\u8054\u961f |+------+----------+3 rows in set (0.00 sec)mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime    -> from    -> m left join t as t1    -> on m.hid = t1.tid    -> left join t as t2    -> on m.gid = t2.tid;+------+----------+------+------+----------+------------+| hid  | hname    | mres | gid  | gname    | matime     |+------+----------+------+------+----------+------------+|    1 | \u56fd\u5b89     | 2:0  |    2 | \u7533\u82b1     | 2006-05-21 ||    2 | \u7533\u82b1     | 1:2  |    3 | \u4f20\u667a\u8054\u961f | 2006-06-21 ||    3 | \u4f20\u667a\u8054\u961f | 2:5  |    1 | \u56fd\u5b89     | 2006-06-25 ||    2 | \u7533\u82b1     | 3:2  |    1 | \u56fd\u5b89     | 2006-07-21 |+------+----------+------+------+----------+------------+4 rows in set (0.00 sec)6\tunion\u67e5\u8be26.1:\u628aecs_comment,ecs_feedback\u4e24\u4e2a\u8868\u4e2d\u7684\u6570\u636e,\u5404\u53d6\u51fa4\u5217,\u5e76\u628a\u7ed3\u679c\u96c6union\u6210\u4e00\u4e2a\u7ed3\u679c\u96c6.6.2:3\u671f\u5b66\u5458\u78b0\u5230\u7684\u4e00\u9053\u9762\u8bd5\u9898A\u8868:+------+------+| id   | num  |+------+------+| a    |    5 || b    |   10 || c    |   15 || d    |   10 |+------+------+B\u8868:+------+------+| id   | num  |+------+------+| b    |    5 || c    |   15 || d    |   20 || e    |   99 |+------+------+mysql> # \u5408\u5e76 ,\u6ce8\u610fall\u7684\u4f5c\u7528mysql> select * from ta    -> union all    -> select * from tb;+------+------+| id   | num  |+------+------+| a    |    5 || b    |   10 || c    |   15 || d    |   10 || b    |    5 || c    |   15 || d    |   20 || e    |   99 |+------+------+\u8981\u6c42\u67e5\u8be2\u51fa\u4ee5\u4e0b\u6548\u679c:+------+----------+| id   | sum(num) |+------+----------+| a    |        5 || b    |       15 || c    |       30 || d    |       30 || e    |       99 |+------+----------+\u53c2\u8003\u7b54\u6848:mysql> # sum,group\u6c42\u548cmysql> select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id; +------+----------+| id   | sum(num) |+------+----------+| a    |        5 || b    |       15 || c    |       25 || d    |       30 || e    |       99 |+------+----------+5 rows in set (0.00 sec)7: \u5b50\u67e5\u8be2:7.1:\u67e5\u8be2\u51fa\u6700\u65b0\u4e00\u884c\u5546\u54c1(\u4ee5\u5546\u54c1\u7f16\u53f7\u6700\u5927\u4e3a\u6700\u65b0,\u7528\u5b50\u67e5\u8be2\u5b9e\u73b0)select goods_id,goods_name from     ecs_goods where goods_id =(select max(goods_id) from ecs_goods);7.2:\u67e5\u8be2\u51fa\u7f16\u53f7\u4e3a19\u7684\u5546\u54c1\u7684\u680f\u76ee\u540d\u79f0(\u7528\u5de6\u8fde\u63a5\u67e5\u8be2\u548c\u5b50\u67e5\u8be2\u5206\u522b)7.3:\u7528where\u578b\u5b50\u67e5\u8be2\u628aecs_goods\u8868\u4e2d\u7684\u6bcf\u4e2a\u680f\u76ee\u4e0b\u9762\u6700\u65b0\u7684\u5546\u54c1\u53d6\u51fa\u6765select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);7.4:\u7528from\u578b\u5b50\u67e5\u8be2\u628aecs_goods\u8868\u4e2d\u7684\u6bcf\u4e2a\u680f\u76ee\u4e0b\u9762\u6700\u65b0\u7684\u5546\u54c1\u53d6\u51fa\u6765select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;\u521b\u5efa\u89e6\u53d1\u5668: CREATE  trigger tg2after insert on ordfor each rowupdate goods set goods_number=goods_number-new.num where id=new.gidCREATE trigger tg3after delete on ordfor each rowupdate goods set goods_number=good_number+old.num where id=old.gidCREATE  trigger tg4after update on ordfor each rowupdate goods set goods_number=goods_number+old.num-new.num where id=old.gid<\/p>"}