一些SQL操作語句(3)
返回
<![CDATA[
price1和price2不對應(yīng)
mysql> select * from a001284 where id = 2\G
*************************** 1. row ***************************
id: 2
name: 'RUBBER HOSE''
code: WP35-50x3600L,5KF
dwg_id: 1
Nuid: 1
weight: 1
price1: 60280=100=1=2008-10-17=8102302=268=1854==60280=100=1=2008-10-17=8101302=
268=1689
price2: 78364=130=1=2008-10-17=13==78364=130=1=2008-10-17=13==78364=130=1=2008-1
0-17=13==78364=130=1=2008-10-17=13
memo: NULL
1 row in set (0.00 sec)
discount或是'D',或是與partsid數(shù)量對應(yīng)的數(shù)據(jù)。否則置'D'。
mysql> update enq2 set discount = 'D' where id = 1196;
按時間的倒序排列
mysql> select id,time from enq2 order by time desc;
取出enq2的所有type1id不等于'B'同時price等于'P'的項目。
mysql> select * from enq2 where price = 'P' and type1id != 'B'\G
取出enq2的所有price不等于'P'的項目
mysql> select id,enq1id from enq2 where price != 'P';
從enq1取出id=1371時的ourref編號
mysql> select id,ourref from eqn1 where id = 1371;
取出enq2的所有price不等于'P'同時discount等于'D'的項目
mysql> select id,enq1id from enq2 where price != 'P' and discount = 'D';
mysql> select id,type1id from enq2 where type1id like "%138%";
+------+------------------+
| id | type1id |
+------+------------------+
| 1164 | 1138 |
| 1271 | 1138 |
| 1686 | 1380==1381==1382 |
+------+------------------+
3 rows in set (0.00 sec)
以上三個結(jié)果都不符合條件,想找的是恰好"138"
mysql> select id,type1id from enq2 where type1id like "138";
Empty set (0.02 sec)
這個可以,但是可能會漏掉"==138","138==","==138=="等三種情況
mysql> select id,type1id from enq2 where type1id like "707" or type1id like "%==707" or type1id like "%==707==%" or type1id like "707==%";
+------+---------------------------------+
| id | type1id |
+------+---------------------------------+
| 459 | 704==705==557==706==707 |
| 1606 | 1346==707==1347==703==1348==468 |
+------+---------------------------------+
2 rows in set (0.00 sec)
這個排比語句行。把各種情況列出來,唯一一個;復(fù)數(shù)個的最后一個;復(fù)數(shù)個的中間一個;復(fù)數(shù)個的第一個。
mysql> select id,type1id from enq2 where type1id like "138" or type1id like "%==138" or type1id like "%==138==%" or type1id like "138==%";
Empty set (0.00 sec)
1138沒有被錯誤抽出!
抽出price不是'P'的項目
mysql> select id,price,discount from enq2 where price <> 'P'\G
抽出price不是'P'和discount不是'D'的項目
mysql> select id,price,discount from enq2 where price <> 'P' and discount <> 'D'
\G
mysql> select enq1.id from enq1,hull_no where hull_no.name like "%DRAGON%" and hull_no.id = enq1.hullnoid;
mysql> select enq1.id,enq1.ourref,hull_no.name from enq1,hull_no where hull_no.n ame like "%DRAGON%" and enq1.hullnoid = hull_no.id;
+------+---------+--------------+
| id | ourref | name |
+------+---------+--------------+
| 765 | 8073010 | OCEAN DRAGON |
| 766 | 8073011 | OCEAN DRAGON |
| 767 | 8073012 | OCEAN DRAGON |
| 774 | 8073107 | OCEAN DRAGON |
| 819 | 8080510 | OCEAN DRAGON |
| 1042 | 8082505 | OCEAN DRAGON |
| 1218 | 8090801 | OCEAN DRAGON |
| 1219 | 8090802 | OCEAN DRAGON |
| 1220 | 8090803 | OCEAN DRAGON |
| 1221 | 8090804 | OCEAN DRAGON |
| 1222 | 8090805 | OCEAN DRAGON |
| 1223 | 8090806 | OCEAN DRAGON |
| 1224 | 8090807 | OCEAN DRAGON |
| 1225 | 8090808 | OCEAN DRAGON |
| 1269 | 8091109 | OCEAN DRAGON |
| 1423 | 8093011 | OCEAN DRAGON |
| 1455 | 8100402 | OCEAN DRAGON |
+------+---------+--------------+
17 rows in set (0.02 sec)
可以執(zhí)行下列語句,可以含有"_"!
mysql> select enq1.hullnoid, hull_no.name from enq1, hull_no where hull_no.id =1
;
是否數(shù)據(jù)表格名不能含有"_"(hull_no)?
mysql> select enq1.hullnoid, hull_no.id hull_no.name from enq1, hull_no where enq1.hullnoid = hull_no.id
and hull_no.name like "%DRAGON%";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'.name from enq1, hull_no where enq1.hullnoid = hull_no.id and hull_no.name like ' at line 1
注意要用前后兩個"%"!
mysql> select id,ourref,ownerno from enq1 where ourref like "%YTP%" or ownerno like "%YTP%";
+------+---------+----------------+
| id | ourref | ownerno |
+------+---------+----------------+
| 17 | 8052310 | 080523-YTP3991 |
| 34 | 8052704 | 080527-YTP4067 |
| 387 | 8070304 | YTP4849 |
| 428 | 8070806 | 080708-YTP4987 |
| 941 | 8081321 | 080813-YTP5768 |
| 942 | 8081322 | 808013-YTP5778 |
| 944 | 8081401 | 080814-YTP5789 |
| 954 | 8081501 | 080815-YTP5818 |
| 1244 | 8091003 | 080910-YTP6460 |
| 1245 | 8091004 | 080910-YTP6461 |
| 1247 | 8091006 | 080910-YTP6463 |
| 1288 | 8091504 | 080912-YTP6532 |
| 1289 | 8091505 | 080912-YTP6531 |
| 1290 | 8091506 | 080912-YTP6530 |
| 1291 | 8091507 | 080912-YTP6529 |
| 1292 | 8091508 | 080912-YTP6528 |
| 1298 | 8091605 | 080916-YTP6590 |
| 1450 | 8100210 | YTP6935 |
+------+---------+----------------+
mysql> select id,ourref,ownerno from enq1 where ourref like "YTP%" or ownerno like "YTP%";
+------+---------+---------+
| id | ourref | ownerno |
+------+---------+---------+
| 387 | 8070304 | YTP4849 |
| 1450 | 8100210 | YTP6935 |
+------+---------+---------+
2 rows in set (0.00 sec)
mysql> select id,ourref from enq1 where ourref like "81002%";
+------+---------+
| id | ourref |
+------+---------+
| 1441 | 8100201 |
| 1442 | 8100202 |
| 1443 | 8100203 |
| 1444 | 8100204 |
| 1445 | 8100205 |
| 1446 | 8100206 |
| 1447 | 8100207 |
| 1448 | 8100208 |
| 1449 | 8100209 |
| 1450 | 8100210 |
| 1451 | 8100211 |
+------+---------+
11 rows in set (0.00 sec)
mysql> select name from main_maker1 where name like "A%" order by name;
+--------------------------------+
| name |
+--------------------------------+
| AALBORG INDUSTRIES KK |
| AALBORG SUNROD KK. |
| ABB |
| ABB TURBO SYSTEM |
| AKASAKA |
| AKASAKA DIESEL |
| ALFA LAVAL |
| ALUP KOMPRESSOREN GMBH |
| AMEROID JAPAN |
| AMEROID JAPAN SERVICE CO.,LTD. |
| AMRI KSB |
| AQUAMASTER-RAUMA LTD. |
| AUTRONICA |
+--------------------------------+
13 rows in set (0.02 sec)
DBD::mysql::db do failed: Unknown column 'seriesid' in 'field list' at insert_ta
bles.pl line 65.
mysql> alter table enq1 add seriesid text after enq2s;
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
INSERT INTO main_type1 (id,name,series,gname_id,maker_id,GR,DWG,memo) VALUES("11
24","6HC582SEB-KYE-X1","XXXSERIES","86","","","XXXDWG=6HC582EC-FKYE","")
DBD::mysql::db do failed: Incorrect integer value: '' for column 'gname_id' at r
ow 1 at insert_tables.pl line 65.
mysql> alter table enq1 change gname_id gname_id text;
ERROR 1054 (42S22): Unknown column 'gname_id' in 'enq1'
mysql> alter table main_type1 change gname_id gname_id text;
Query OK, 948 rows affected (0.30 sec)
Records: 948 Duplicates: 0 Warnings: 0
mysql> show columns from main_type1;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| series | text | YES | | NULL | |
| gname_id | text | YES | | NULL | |
| maker_id | int(11) | YES | | NULL | |
| GR | text | YES | | NULL | |
| DWG | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> alter table enq1 change maker_id maker_id text;
ERROR 1054 (42S22): Unknown column 'maker_id' in 'enq1'
mysql> alter table main_type1 change maker_id maker_id text;
Query OK, 714 rows affected (0.22 sec)
Records: 714 Duplicates: 0 Warnings: 0
mysql> show columns from main_type1;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| series | text | YES | | NULL | |
| gname_id | text | YES | | NULL | |
| maker_id | text | YES | | NULL | |
| GR | text | YES | | NULL | |
| DWG | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.03 sec)
mysql> select id,enq1id from enq2 where enq1id = '0';
+------+--------+
| id | enq1id |
+------+--------+
| 1025 | 0 |
| 1090 | 0 |
| 1276 | 0 |
| 1277 | 0 |
| 1279 | 0 |
| 1280 | 0 |
| 1359 | 0 |
+------+--------+
7 rows in set (0.00 sec)
mysql> update enq1 set enq2s = '1025' where id = 4;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update enq2 set enq1id = '4' where id = 1025;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from enq1 where enq2s like "1336"\G
*************************** 1. row ***************************
id: 1224
time: 2008-09-08
ourref: 8090807
owner: 11
ownerno: 08M047
hullnoid: 462
type1id: 1226
partsid: 1
QTY: 1
memo:
LANGUAGEid: 1
makerid: 20
enq2s: 1336
seriesid: C
*************************** 2. row ***************************
id: 1225
time: 2008-09-08
ourref: 8090808
owner: 11
ownerno: 08M048,49
hullnoid: 462
type1id: 1185
partsid: 17=18=19=20=21=22
QTY: 10=10=10=10=10=10
memo:
LANGUAGEid: 1
makerid: 78
enq2s: 1336
seriesid: C
2 rows in set (0.02 sec)
mysql> update enq2 set enq1id = '1225' where id = 1090;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update enq1 set enq2s = '1090' where id = 1225;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
]]>
返回
阜康市|
禄劝|
抚宁县|
苏州市|
新宁县|
汽车|
鹿泉市|
桦南县|
张家界市|
盐源县|
环江|
务川|
朝阳区|
两当县|
道孚县|
峡江县|
车致|
金秀|
长春市|
康马县|
宜州市|
英超|
滦南县|
石林|
唐海县|
南通市|
永新县|
吴旗县|
丽水市|
麻栗坡县|
东辽县|
渝北区|
邻水|
建瓯市|
科技|
海城市|
秦安县|
邢台市|
公主岭市|
泗水县|
吴川市|