Mastering MySQL 4 [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Mastering MySQL 4 [Electronic resources] - نسخه متنی

Ian Gilfillan

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید







Adding Records to a Table from Other Tables

with INSERT SELECT

The INSERT statement also allows you to add records, or parts of records, that exist in other tables. For example, let's say you want to create a new table containing the customer names and the values of all the purchases they have made. The query to return the results you want would be the following:

mysql> SELECT first_name,surname,SUM(value) FROM sales NATURAL JOIN
customer GROUP BY first_name, surname;
+------------+-------------+------------+
| first_name | surname | SUM(value) |
+------------+-------------+------------+
| Johnny | Chaka-Chaka | 500 |
| Patricia | Mankunku | 450 |
| Winston | Powers | 750 |
| Yvonne | Clegg | 5800 |
+------------+-------------+------------+

First you'll need to create the table to receive the results:

mysql> CREATE TABLE customer_sales_values(first_name
VARCHAR(30), surname VARCHAR(40), value INT);

Now, you insert the results into this table:

mysql> INSERT INTO customer_sales_values(first_name,surname,value)
SELECT first_name,surname, SUM(value) FROM sales NATURAL JOIN
customer GROUP BY first_name, surname;

The customer_sales_values table now contains the following:

mysql> SELECT * FROM customer_sales_values;
+------------+-------------+-------+
| first_name | surname | value |
+------------+-------------+-------+
| Johnny | Chaka-Chaka | 500 |
| Patricia | Mankunku | 450 |
| Winston | Powers | 750 |
| Yvonne | Clegg | 5800 |
+------------+-------------+-------+


/ 229