一些SQL操作语句

mysql> select price1 from enq2 where price1 ne 'P1'; 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 'ne 'P 1'' at line 1 mysql> select price1 from enq2 where price1 != 'P1'; Empty set (0.01 sec) mysql> update enq1 set memo = "id2 \"test\" " where id = 495; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from enq1 where id = 495\G *************************** 1. row *************************** id: 495 time: 2008-07-11 ourref: 8071109 owner: 15 ownerno: 039-05 hullnoid: 166 type1id: 738==526 partsid: 1==1 QTY: 2==1 memo: id2 "test" LANGUAGEid: 1 makerid: 1 enq2s: 513=515 1 row in set (0.00 sec) mysql> select * from enq1 where type1id like "%738%"\G *************************** 1. row *************************** id: 495 time: 2008-07-11 ourref: 8071109 owner: 15 ownerno: 039-05 hullnoid: 166 type1id: 738==526 partsid: 1==1 QTY: 2==1 memo: id2 is the same! id2_enq1_id495_495 LANGUAGEid: 1 makerid: 1 enq2s: 513=515 1 row in set (0.00 sec) 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 | varchar(100) | YES | | NULL | | | gname_id | int(11) | 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.08 sec) mysql> alter table main_type1 change series series text; Query OK, 740 rows affected (1.06 sec) Records: 740 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 | int(11) | 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.02 sec) 检索表格a000730含有"2"的code的数量 --------------------------------------------------------------------------- mysql> SELECT COUNT(*) FROM a000730 WHERE code LIKE "%2%"; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) #删除一个表格的所有数据 mysql> delete from enq1_tmp; Query OK, 1 row affected (0.06 sec) #带入操作 mysql> update enq1 set enq2s = enq1.id; Query OK, 0 rows affected (0.00 sec) Rows matched: 28 Changed: 0 Warnings: 0 mysql> update enq2 set type1id = "B" where type1id is NULL; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> update enq2 set partsid = "C" where partsid is NULL; Query OK, 3 rows affected (0.05 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> update enq2 set price = "P" where price is NULL; Query OK, 22 rows affected (0.05 sec) Rows matched: 22 Changed: 22 Warnings: 0 mysql> update enq2 set discount = "D" where discount is NULL; Query OK, 22 rows affected (0.01 sec) Rows matched: 22 Changed: 22 Warnings: 0 mysql> update quo2 set percent = "P1" where percent is NULL; Query OK, 24 rows affected (0.06 sec) Rows matched: 24 Changed: 24 Warnings: 0 mysql> update quo2 set price = "P2" where price is NULL; Query OK, 23 rows affected (0.01 sec) Rows matched: 23 Changed: 23 Warnings: 0 mysql> select id, built, date_format(built,'%M %d, %Y') from hull_no where id =333; +-----+------------+--------------------------------+ | id | built | date_format(built,'%M %d, %Y') | +-----+------------+--------------------------------+ | 333 | 0000-01-01 | January 01, 0000 | +-----+------------+--------------------------------+ 1 row in set (0.00 sec) mysql> select count(*) from enq1; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.08 sec) 检索 mysql> select name, built from hull_no where built like '1971-%'; +------+------------+ | name | built | +------+------------+ | n367 | 1971-00-00 | +------+------------+ 1 row in set, 1 warning (0.00 sec) mysql> select id, type1id from enq1 where type1id like "%5%"; +----+---------+ | id | type1id | +----+---------+ | 1 | 5 | | 3 | 5 | | 5 | 5 | | 9 | 5 | | 11 | 5 | +----+---------+ 5 rows in set (0.00 sec) mysql> select id, type1id from enq1 where type1id like "B"; +----+---------+ | id | type1id | +----+---------+ | 2 | B | | 6 | B | | 7 | B | | 8 | B | | 10 | B | +----+---------+ 5 rows in set (0.00 sec) mysql> select enq1.type1id,enq2.type1id from enq1, enq2; +---------+---------+ | type1id | type1id | +---------+---------+ | 5 | 5 | | 5 | B | | 5 | 3 | | 5 | 2 | | B | 5 | | B | B | | B | 3 | | B | 2 | | 5 | 5 | | 5 | B | | 5 | 3 | | 5 | 2 | | 3 | 5 | | 3 | B | | 3 | 3 | | 3 | 2 | | 5 | 5 | | 5 | B | | 5 | 3 | | 5 | 2 | | B | 5 | | B | B | | B | 3 | | B | 2 | | B | 5 | | B | B | | B | 3 | | B | 2 | | B | 5 | | B | B | | B | 3 | | B | 2 | | 5 | 5 | | 5 | B | | 5 | 3 | | 5 | 2 | | B | 5 | | B | B | | B | 3 | | B | 2 | | 5 | 5 | | 5 | B | | 5 | 3 | | 5 | 2 | +---------+---------+ mysql> select enq1.partsid,enq2.partsid from enq1, enq2; +---------+---------+ | partsid | partsid | +---------+---------+ | 1 | 1 | | 1 | C | | 1 | 16=1 | | 1 | 1=2=3 | | C | 1 | | C | C | | C | 16=1 | | C | 1=2=3 | | 1 | 1 | | 1 | C | | 1 | 16=1 | | 1 | 1=2=3 | | 16=1 | 1 | | 16=1 | C | | 16=1 | 16=1 | | 16=1 | 1=2=3 | | 1 | 1 | | 1 | C | | 1 | 16=1 | | 1 | 1=2=3 | | C | 1 | | C | C | | C | 16=1 | | C | 1=2=3 | | C | 1 | | C | C | | C | 16=1 | | C | 1=2=3 | | C | 1 | | C | C | | C | 16=1 | | C | 1=2=3 | | 1 | 1 | | 1 | C | | 1 | 16=1 | | 1 | 1=2=3 | | C | 1 | | C | C | | C | 16=1 | | C | 1=2=3 | | 1 | 1 | | 1 | C | | 1 | 16=1 | | 1 | 1=2=3 | +---------+---------+ 44 rows in set (0.00 sec) mysql> select id,time from enq1; +----+------------+ | id | time | +----+------------+ | 1 | 2008-05-15 | | 2 | 2008-05-24 | | 3 | 2008-05-24 | | 4 | 2008-05-24 | | 5 | 2008-06-01 | | 6 | 2008-06-01 | | 7 | 2008-06-01 | | 8 | 2008-06-01 | | 9 | 2008-06-01 | | 10 | 2008-06-01 | | 11 | 2008-06-01 | +----+------------+ 11 rows in set (0.00 sec) 显示4行 mysql> select id,time from enq1 limit 4; +----+------------+ | id | time | +----+------------+ | 1 | 2008-05-15 | | 2 | 2008-05-24 | | 3 | 2008-05-24 | | 4 | 2008-05-24 | +----+------------+ 4 rows in set (0.00 sec) 按时间的倒序显示4行 mysql> select id,time from enq1 order by time desc limit 4; +----+------------+ | id | time | +----+------------+ | 11 | 2008-06-01 | | 10 | 2008-06-01 | | 9 | 2008-06-01 | | 8 | 2008-06-01 | +----+------------+ 4 rows in set (0.03 sec) 显示第3列开始的3行 mysql> select id,time from enq1 limit 3,3; +----+------------+ | id | time | +----+------------+ | 4 | 2008-05-24 | | 5 | 2008-06-01 | | 6 | 2008-06-01 | +----+------------+ 3 rows in set (0.00 sec) 显示不重复项目 mysql> select distinct time from enq1; +------------+ | time | +------------+ | 2008-05-15 | | 2008-05-24 | | 2008-06-01 | +------------+ 3 rows in set (0.03 sec) 计算不重复项目数 mysql> select count(distinct time) from enq1; +----------------------+ | count(distinct time) | +----------------------+ | 3 | +----------------------+ 1 row in set (0.08 sec) mysql> select id, enq2s from enq1; +----+-------+ | id | enq2s | +----+-------+ | 1 | 1 | | 2 | NULL | | 3 | NULL | | 4 | 2=3=4 | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | | 11 | NULL | +----+-------+ 11 rows in set (0.02 sec) 用is null来判断是否是NULL mysql> select id, enq2s from enq1 where enq2s is null; +----+-------+ | id | enq2s | +----+-------+ | 2 | NULL | | 3 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | | 11 | NULL | +----+-------+ 9 rows in set (0.00 sec) 用is null来判断是否不是NULL mysql> select id, enq2s from enq1 where enq2s is not null; +----+-------+ | id | enq2s | +----+-------+ | 1 | 1 | | 4 | 2=3=4 | +----+-------+ 2 rows in set (0.00 sec) 检查enq1id为4的enq2,发现了三个,说明有错(1个enq2只能对应一个enq1) mysql> select id, time from enq2 where enq1id = 4; +----+------------+ | id | time | +----+------------+ | 2 | 2008-05-24 | | 3 | 2008-05-24 | | 4 | 2008-05-24 | +----+------------+ 3 rows in set (0.00 sec) -------------------------------------------------------------- 检查是否含指定信息的语句 mysql> select partsid,locate("16",partsid) from enq1; +---------+----------------------+ | partsid | locate("16",partsid) | +---------+----------------------+ | 1 | 0 | | C | 0 | | 1 | 0 | | 16=1 | 1 | | 1 | 0 | | C | 0 | | C | 0 | | C | 0 | | 1 | 0 | | C | 0 | | 1 | 0 | +---------+----------------------+ 11 rows in set (0.00 sec)