1 向数据库中添加记录
mysql> INSERT INTO SALESREPS (NAME,AGE,EMPL_NUM,SALES,TITLE,HIRE_DATE,REP_OFFICE) VALUES ('Henry Jacobsen',36,111,0.00,'Sales Mgr','2008-07-25',13);
Query OK, 1 row affected (0.04 sec)
插入多条语句
mysql> INSERT INTO OLDORDERS(ORDER_NUM,ORDER_DATE,AMOUNT) SELECT ORDER_NUM,ORDER_DATE,AMOUNT FROM ORDERS WHERE ORDER_DATE<'2008-01-01';
Query OK, 9 rows affected, 1 warning (0.00 sec)
当你需要查看满足条件的数据时，可以用这样的方法建立一张表，将满足条件的数据插入到新表当中，这样容易查看和操作。
2 从数据库中删除记录
mysql> DELETE FROM SALESREPS WHERE NAME='Henry Jacobsen';
Query OK, 1 row affected (0.00 sec)
3 带有子查询的DELETE
删除由Sue Smith取得的订单
DELETE FROM ORDERS WHERE REP=(SELETE EMPL_NUM FROM SALESREPS WHERE NAME='Sue Smith');
4 修改数据库中的数据
mysql> UPDATE CUSTOMERS SET CREDIT_LIMIT=6000.00,CUST_REP=109 WHERE COMPANY='Acme Mfg.';
Query OK, 0 rows affected (0.01 sec)
将由雇员编号位105,106,107的销售人员取得的客户重新分配给雇员编号位102的销售人员。
mysql> UPDATE CUSTOMERS SET CUST_REP=102 WHERE CUST_REP IN(105,106,107);
Query OK, 5 rows affected (0.00 sec)
mysql> UPDATE SALESREPS SET QUOTA=400000.00,SALES=QUOTA WHERE QUOTA<400000.00;
Query OK, 10 rows affected (0.00 sec)
QUOTA和SALES的值都是400000.00。
5 带有子查询的UPDATE
让取得3个以上客户的所有销售人员直接向Sam Clark报告。
mysql> UPDATE SALESREPS SET MANAGER=106 WHERE 3<(SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_REP=EMPL_NUM);
Query OK, 1 row affected (0.01 sec)
列出每个销售人员的名字，以及他们的客户数目还有他们的经理。(通过右连接先扩展表，然后在计数)
mysql> SELECT NAME,COUNT(*),MANAGER FROM SALESREPS RIGHT OUTER JOIN CUSTOMERS ON CUST_REP=EMPL_NUM GROUP BY EMPL_NUM;
+-------------+----------+---------+
| NAME | COUNT(*) | MANAGER |
+-------------+----------+---------+
| Dan Roberts | 3 | 104 |
| Sue Smith | 9 | 106 |
| Paul Cruz | 3 | 104 |