MySQL必知必会之15-20 - 数据库编程

------------------------------------------------------------+

2 rows in set (0.00 sec)

这里使用了关键字IN BOOLEAN MODEN

mysql> SELECT note_text FROMproductnotes WHERE Match(note_text) Against('heavy

-rope*' IN BOOLEAN MODE);

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| note_text

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

sed for dropping, recommend ANV02 or ANV03instead. |

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

1 row in set (0.00 sec)

这里-rope*明确地指示MySQL排除包含rope*。

下面举几个例子

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+rabbit +bait’ IN BOOLEAN MODE);//这个搜索匹配包含词rabbit和bait的行

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘rabbit bait’ IN BOOLEAN MODE);//没有指定操作符，这个搜索匹配rabbit和bait中的至少一个词的行

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘”rabbit +bait”’ IN BOOLEAN MODE);//这个词匹配短语rabbitbait而不是匹配两个词rabbit和bait

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘>rabbit

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+safe +(

19.插入数据

19.1插入完整的行

mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country,cust_contact,cust_email)VALUES('Pep E. Lapew','100 main Street',

'LosAngeles','CA','90046','USA','NULL','NULL');

Query OK, 1 row affected (0.06 sec)

19.2插入多个行

mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country) VALUES('Pep E. Lapew','100main Street',

'Los Angeles','CA','90046','USA'),(‘M.Martian’,’42 Galaxy Way’,’New York’,’NY’,’11213’,’USA’);

19.3插入检索出的数据

INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country)SELECTcust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_countryFROM custnew;

20.更新和删除数据

20.1跟新数据

两种方式

1）更新表中所有行,不加WHERE

2）更新表中特定行，加WHERE

UPDATE语句由3个部分组成，分别是：

1）要更新的表

2）列名和他们的新值

3）确定要更新的过滤条件

mysql> UPDATE customers SET cust_name ='The Fudds',cust_email = 'elmer@fudd.com

' WHERE cust_id = 10005;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

20.2删除数据

两种方式：

1）从表中删除特定的行，不省略WHERE子句

2）从表中删除所有行，省略WHERE子句

DELETE FROM customers WHERE cust_id = 10006;

DELETE删除的是表的内容而不是表本身

要删除所有行，不要使用DELETE，用TRUNCATETABLE;

==参考MySQL必知必会

MySQL必知必会之15-20(四)