INSERT 数据插入
INSERT INTO (…,
…,
…)
VALUES(…,
…,
…);
单行插入:
mysql> INSERT INTO test.customers(cust_name,
-> cust_contact,
-> cust_email,
-> cust_address,
-> cust_city,
-> cust_state,
-> cust_zip,
-> cust_country)
-> VALUES('Pep E. LaPew',
-> NULL,
-> NULL,
-> '100 Main Street',
-> 'Los Angeles',
-> 'CA',
-> '90046',
-> 'CHINA');
Query OK, 1 row affected (0.17 sec)
多行插入:
mysql> INSERT INTO test.customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'W. ZiMing',
'18 WaiTan',
'ShangHai',
'SH',
'90046',
'CHINA'
),
(
'A. Ji',
'17 WaiTan',
'ShangHai',
'SH',
'90035',
'CHINA'
);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
INSERT SELECT //插入检索数据
释:下例中:使用INSERT SELECT 把custnew 中的数据检索出来并插入到 customers表中,INSERT与SELECT 中的列的位置与数量存在一一对应的关系,列的数量要保持一致,列名可以不同;
1. 如果要保证cust_id 的值不重复,可以省略不传(INSERT与SELECT都不传),这样SQL会生成新的值;
2. 也可以使用WHERE 语句更行过滤筛选数据;
INSERT INTO test.customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM test.custnew;
UPDATE 更新数据
mysql> UPDATE test.customers
-> SET cust_email = 'elmer@fudd.com'
-> WHERE cust_id = 10005;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新多列:
mysql> UPDATE test.customers
-> SET cust_name = 'The Fudds',
-> cust_email= 'elmer@qq.com'
-> WHERE cust_id = 10005;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除数据
注:
- DELETE 从表中删除行或所有行,但是不删除表本身;
- TRUNCHTE TABLE 可删除所以行,速度更快(实际是删除原来的表并重新创建一个新表,而不是逐行删除表中的数据);
mysql> DELETE FROM test.customers
-> WHERE cust_id = 10006;
Query OK, 1 row affected (0.09 sec)
更新与删除注意:
- 除非确实打算更新或删除表中的每一行数据,否则 UPDATE 和 DELETE 语句一定要配合 WHERE子句一起使用;
- 保证每个表都有主键;
- 在UPDATE和DELETE语句使用WHERE子句前,应该使用SELECT进行测试, 保证过滤的数据正确;
- 使用强制实施引用完整的数据库,这样MySQL将不允许删除与其它表相关联的数据;
5. MySQL没有撤销 按钮,一旦删除数据无法恢复!!!
创建表及操作
**CREATE TABLE ** // 创建表
注:
- 主键值 不能定义为 NULL, 且具有唯一值;
- 有多个列为主键值 ,则多列的组合值必须唯一;
- ‘ ’ 不等于 NULL;
mysql> CREATE TABLE test.demo1 //demo1,创建的表名
-> (
-> id int NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL, //NOT NULL,此列数据不可为空
-> city char(20) NULL, //此列可以为空
-> PRIMARY KEY (id, name) //设置表的主键为: id和name的组合为唯一值
-> )ENGINE=InnoDB; //引擎类型
Query OK, 0 rows affected (0.43 sec)
AUTO_INCREMENT 自动增量
注:
- 使用 AUTO_INCREMENT语句的列,在本列每当增加一行时自动增量,每次执行一个INSERT时,MySQL自动对本列进行增量。
- 每个表允许有一个AUTO_INCREMENT列,而且该列必须被索引(使本列成为主键)
- last_insert_id() 函数可以获取最后一个 AUTO_INCREMENT值
DEFAULT //默认值
注: 默认值 只能是常量,不能是 函数;
score int NOT NULL DEFAULT 0
引擎类型
- InnoDB 支持事务处理引擎,不支持全文本搜索;
- MEMORY 支持全文本搜索,不支持事务处理, 数据存储在内存中(不是磁盘),速度很快(适用于临时表)
- MyISAM支持全文本搜索,不支持事务处理;
更新表
ALTER TABLE … ADD … //增加列
mysql> ALTER TABLE test.vendors
-> ADD vend_phone CHAR(20);
Query OK, 0 rows affected (0.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE … DROP COLUMN … //删除列
mysql> ALTER TABLE test.vendors
-> DROP COLUMN vend_phone;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
***ALTER TABLE … ADD CONSTRAINT … *** 定义外键
DROP TABLE xx_table //删除表
注: 删除表没有确认提示,也不能撤销,执行后将永久删除该表;
RENAME TABLE xx_table TO aa_table // 重命名表
VIEW // 视图
CREATE VIEW //创建视图
1.利用视图简化复杂联结
mysql> CREATE VIEW productcustomers AS
-> SELECT cust_name, cust_contact, prod_id
-> FROM test.customers, test.orders, test.orderitems
-> WHERE customers.cust_id = orders.cust_id
-> AND orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.19 sec)
mysql> SELECT *FROM productcustomers;
+----------------+--------------+---------+
| cust_name | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc. | Y Lee | ANV01 |
| Coyote Inc. | Y Lee | ANV02 |
| Coyote Inc. | Y Lee | TNT2 |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | FB |
| Coyote Inc. | Y Lee | OL1 |
| Coyote Inc. | Y Lee | SLING |
| Coyote Inc. | Y Lee | ANV03 |
| Wascals | Jim Jones | JP2000 |
| Yosemite Place | Y Sam | TNT2 |
| The Fudds | E Fudd | FC |
+----------------+--------------+---------+
11 rows in set (0.02 sec)
2.格式化检索出的数据
mysql> CREATE VIEW vendorlocations AS
-> SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
-> AS vend_title
-> FROM test.vendors
-> ORDER BY vend_name;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM vendorlocations;
+------------------------+
| vend_title |
+------------------------+
| ACME(USA) |
| Anvils R Us(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
| LT Supplies(USA) |
+------------------------+
6 rows in set (0.04 sec)
3.过滤不想要的数据
mysql> CREATE VIEW customeremaillist AS
-> SELECT cust_id , cust_name, cust_email
-> FROM test.customers
-> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT * FROM customers;
ERROR 1146 (42S02): Table 'mysql.customers' doesn't exist
mysql> SELECT * FROM customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name | cust_email |
+---------+----------------+---------------------+
| 10001 | Coyote Inc. | ylee@coyote.com |
| 10003 | Wascals | rabbit@wascally.com |
| 10004 | Yosemite Place | sam@yosemite.com |
| 10005 | The Fudds | elmer@qq.com |
+---------+----------------+---------------------+
存储过程
游标
注:只用在存储过程中;