一些SQL操作语句(3)
返回
<![CDATA[
price1和price2不对应
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数量对应的数据。否则置'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)
以上三个结果都不符合条件,想找的是恰好"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)
这个排比语句行。把各种情况列出来,唯一一个;复数个的最后一个;复数个的中间一个;复数个的第一个。
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)
可以执行下列语句,可以含有"_"!
mysql> select enq1.hullnoid, hull_no.name from enq1, hull_no where hull_no.id =1
;
是否数据表格名不能含有"_"(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
]]>
返回