一些SQL操作语句(6),同时使用复数表格
返回
连接一张表的列和另一张的列
<![CDATA[
mysql> SELECT * FROM parts_nu,percent;
+----+------------+----+------+
| id | parts_Unit | id | Rate |
+----+------------+----+------+
| 1 | PCS | 1 | 1.05 |
| 2 | SET | 1 | 1.05 |
| 3 | BOX | 1 | 1.05 |
| 4 | ROLL | 1 | 1.05 |
| 5 | PAIR | 1 | 1.05 |
| 6 | CAN | 1 | 1.05 |
| 7 | DOZ | 1 | 1.05 |
| 1 | PCS | 2 | 1.10 |
| 2 | SET | 2 | 1.10 |
| 3 | BOX | 2 | 1.10 |
| 4 | ROLL | 2 | 1.10 |
| 5 | PAIR | 2 | 1.10 |
| 6 | CAN | 2 | 1.10 |
| 7 | DOZ | 2 | 1.10 |
| 1 | PCS | 3 | 1.20 |
| 2 | SET | 3 | 1.20 |
| 3 | BOX | 3 | 1.20 |
| 4 | ROLL | 3 | 1.20 |
| 5 | PAIR | 3 | 1.20 |
| 6 | CAN | 3 | 1.20 |
| 7 | DOZ | 3 | 1.20 |
| 1 | PCS | 4 | 1.30 |
| 2 | SET | 4 | 1.30 |
| 3 | BOX | 4 | 1.30 |
| 4 | ROLL | 4 | 1.30 |
| 5 | PAIR | 4 | 1.30 |
| 6 | CAN | 4 | 1.30 |
| 7 | DOZ | 4 | 1.30 |
| 1 | PCS | 5 | 1.50 |
| 2 | SET | 5 | 1.50 |
| 3 | BOX | 5 | 1.50 |
| 4 | ROLL | 5 | 1.50 |
| 5 | PAIR | 5 | 1.50 |
| 6 | CAN | 5 | 1.50 |
| 7 | DOZ | 5 | 1.50 |
| 1 | PCS | 6 | 150 |
| 2 | SET | 6 | 150 |
| 3 | BOX | 6 | 150 |
| 4 | ROLL | 6 | 150 |
| 5 | PAIR | 6 | 150 |
| 6 | CAN | 6 | 150 |
| 7 | DOZ | 6 | 150 |
| 1 | PCS | 7 | 250 |
| 2 | SET | 7 | 250 |
| 3 | BOX | 7 | 250 |
| 4 | ROLL | 7 | 250 |
| 5 | PAIR | 7 | 250 |
| 6 | CAN | 7 | 250 |
| 7 | DOZ | 7 | 250 |
+----+------------+----+------+
49 rows in set (0.33 sec)
mysql> SELECT enq1.type1id, enq2.type1id FROM enq1, enq2 WHERE enq1.id > 1400;
mysql> SELECT type1id, type1id FROM enq1, enq2;
ERROR 1052 (23000): Column 'type1id' in field list is ambiguous
mysql> SELECT enq1.id,enq1.type1id, enq2.id,enq2.type1id FROM enq1,enq2 ORDER BY
RAND() LIMIT 3;
+-----+---------+------+---------+
| id | type1id | id | type1id |
+-----+---------+------+---------+
| 41 | 383 | 490 | 208 |
| 292 | 278 | 1161 | 1135 |
| 380 | 653 | 14 | 363 |
+-----+---------+------+---------+
3 rows in set (4.55 sec)
mysql> SELECT e1.type1id, e2.type1id FROM enq1 AS e1,enq2 AS e2 LIMIT 3;
+---------+---------+
| type1id | type1id |
+---------+---------+
| 367 | 356 |
| 73 | 356 |
| 356 | 356 |
+---------+---------+
3 rows in set (0.00 sec)
]]>
从不同的数据库中取数
<![CDATA[
mysql> SELECT cookbook.parts_nu.parts_Unit,cb2.metal.name FROM cookbook.parts_nu, cb2.metal;
+------------+----------+
| parts_Unit | name |
+------------+----------+
| PCS | copper |
| SET | copper |
| BOX | copper |
| ROLL | copper |
| PAIR | copper |
| CAN | copper |
| DOZ | copper |
| PCS | gold |
| SET | gold |
| BOX | gold |
| ROLL | gold |
| PAIR | gold |
| CAN | gold |
| DOZ | gold |
| PCS | iron |
| SET | iron |
| BOX | iron |
| ROLL | iron |
| PAIR | iron |
| CAN | iron |
| DOZ | iron |
| PCS | lead |
| SET | lead |
| BOX | lead |
| ROLL | lead |
| PAIR | lead |
| CAN | lead |
| DOZ | lead |
| PCS | mercury |
| SET | mercury |
| BOX | mercury |
| ROLL | mercury |
| PAIR | mercury |
| CAN | mercury |
| DOZ | mercury |
| PCS | platinum |
| SET | platinum |
| BOX | platinum |
| ROLL | platinum |
| PAIR | platinum |
| CAN | platinum |
| DOZ | platinum |
| PCS | silver |
| SET | silver |
| BOX | silver |
| ROLL | silver |
| PAIR | silver |
| CAN | silver |
| DOZ | silver |
| PCS | tin |
| SET | tin |
| BOX | tin |
| ROLL | tin |
| PAIR | tin |
| CAN | tin |
| DOZ | tin |
+------------+----------+
56 rows in set (0.00 sec)
mysql> SELECT parts_nu.parts_Unit,cb2.metal.name FROM parts_nu, cb2.metal;
]]>
从不同数据表取数
<![CDATA[
mysql> SELECT * from enq2 WHERE enq1id != 0 ORDER BY enq1id LIMIT 3\G
*************************** 1. row ***************************
id: 1
time: 2008-05-18
enq1id: 1
LANGUAGEid: 1
ORIGINid: 1
PRICEid: 1
PAYMENTid: 1
makerid: 2
DELIVERY:
type1id: 356
partsid: 2=1=4=3=5=7=8=6=9=10=11=12
QTY: 16=3=64=16=24=12=6=6=12=4=100=18
memo: A test memo for enq2
price: P
discount: D
discount0: 100
money: 1
price1: P1
makerref:
disc:
charges:
*************************** 2. row ***************************
id: 2
time: 2008-05-22
enq1id: 2
LANGUAGEid: 1
ORIGINid: 1
PRICEid: 1
PAYMENTid: 1
makerid: 46
DELIVERY:
type1id: 357
partsid: 11=2=6=5=1=9=8=10=4=3=7=13=12=14
QTY: 2=2=4=4=2=2=2=1=2=1=2=4=4=2
memo:
price: P
discount: D
discount0: 100
money: 1
price1: P1
makerref:
disc:
charges:
*************************** 3. row ***************************
id: 3
time: 2008-05-22
enq1id: 3
LANGUAGEid: 1
ORIGINid: 1
PRICEid: 1
PAYMENTid: 1
makerid: 1
DELIVERY:
type1id: 359
partsid: 1=2=3=4
QTY: 1=1=2=2
memo:
price: P
discount: D
discount0: 100
money: 1
price1: P1
makerref:
disc:
charges:
3 rows in set (0.05 sec)
mysql> SELECT enq1.id AS enq1_id,enq2.id AS enq2_id from enq1, enq2 WHERE enq2.enq1id = enq1.id LIMIT 5;
+---------+---------+
| enq1_id | enq2_id |
+---------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 193 | 4 |
| 5 | 5 |
+---------+---------+
5 rows in set (0.00 sec)
mysql> SELECT id,enq2s from enq1 WHERE enq2s LIKE "%=%" LIMIT 5;
+-----+------------+
| id | enq2s |
+-----+------------+
| 29 | 29=601=602 |
| 43 | 43=662 |
| 90 | 90=165 |
| 102 | 102=384 |
| 114 | 114=166 |
+-----+------------+
5 rows in set (0.00 sec)
]]>
PERL的取出数据程序
<![CDATA[
use strict;
use DBI;
my(%t,$pref,$ref,$sth,$count1,$count2,$stmt);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
$stmt = qq{
SELECT enq1.type1id, enq2.type1id
FROM enq1, enq2 LIMIT 5
};
$sth = $$pref{dbh}->prepare($stmt);
$sth->execute();
$count1 = $sth->{NUM_OF_FIELDS};
$ref = $sth->fetchrow_hashref();
$count2 = keys (%{$ref});
print "The statement is: $stmt\n";
print "According to NUM_OF_FIELDS, the result set has $count1 columns\n";
print "The column names are: " . join(",", sort(@{$sth->{NAME}})) . "\n";
print "According to the row hash size, the result set has $count2 columns\n";
print "The column names are: " . join(",", sort(keys %{$ref})) . "\n";
$sth->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
__END__;
The statement is:
SELECT enq1.type1id, enq2.type1id
FROM enq1, enq2 LIMIT 5
According to NUM_OF_FIELDS, the result set has 2 columns
The column names are: type1id,type1id
According to the row hash size, the result set has 1 columns
The column names are: type1id
-----------------------------------------
use strict;
use DBI;
my(%t,$pref,$ref,$sth,$count1,$count2,$stmt);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
$stmt = qq{
SELECT enq1.type1id AS type1, enq2.type1id AS type2
FROM enq1, enq2 LIMIT 5
};
$sth = $$pref{dbh}->prepare($stmt);
$sth->execute();
$count1 = $sth->{NUM_OF_FIELDS};
$ref = $sth->fetchrow_hashref();
$count2 = keys (%{$ref});
print "The statement is: $stmt\n";
print "According to NUM_OF_FIELDS, the result set has $count1 columns\n";
print "The column names are: " . join(",", sort(@{$sth->{NAME}})) . "\n";
print "According to the row hash size, the result set has $count2 columns\n";
print "The column names are: " . join(",", sort(keys %{$ref})) . "\n";
$sth->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
__END__;
The statement is:
SELECT enq1.type1id AS type1, enq2.type1id AS type2
FROM enq1, enq2 LIMIT 5
According to NUM_OF_FIELDS, the result set has 2 columns
The column names are: type1,type2
According to the row hash size, the result set has 2 columns
The column names are: type1,type2
]]>
返回