в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:49:53Z

1 версия

Новая страница

== Do calculation in the insert statement referencing the column name==



<source lang="sql">
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-> Copyright YEAR,
-> Expire YEAR,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Copyright, Expire, Name)VALUES (1,1994,Copyright + Copyright,"A");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> select * from myTable;
+----+-----------+--------+------+
| ID | Copyright | Expire | Name |
+----+-----------+--------+------+
| 1 | 1994 | 0000 | A |
+----+-----------+--------+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql></source>

== INSERTing data==

<p>The basic syntax for the INSERT statement is as follows.</p>



<source lang="sql">
INSERT INTO <table_name>
VALUES (
value1,
value2,
etc......
);</source>

== Insert more than one rows in a single insert statement==



<source lang="sql">
mysql>
mysql>
mysql> CREATE TABLE employee (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("A"),
-> ("B"),
-> ("C"),
-> ("D"),
-> ("E"),
-> ("F");
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM employee;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+----+------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql></source>

== Insert NULL to a NOT NULL column==



<source lang="sql">
mysql>
mysql> CREATE TABLE myTable(
-> ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (NULL, "Ain\"t ");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from myTable;
+----+--------+
| ID | Name |
+----+--------+
| 1 | Ain"t |
+----+--------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql></source>

== Insert quotation marks==



<source lang="sql">
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1, "Ain\"t ");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+----+--------+
| ID | Name |
+----+--------+
| 1 | Ain"t |
+----+--------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)</source>

== Insert two rows with one insert statement==



<source lang="sql">
mysql>
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1,"A"),(2,"B");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from myTable;
+----+------+
| ID | Name |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.02 sec)
mysql></source>

== Insert with set statement==



<source lang="sql">
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT DELAYED INTO myTable SET ID=1, Name="C";
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from myTable;
Empty set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.01 sec)</source>

== Use the DEFAULT in the insert statement==



<source lang="sql">
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Name)VALUES (1,"A"),(DEFAULT,"B");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from myTable;
+----+------+
| ID | Name |
+----+------+
| 1 | A |
| 1 | B |
+----+------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.01 sec)
mysql></source>

== Using encode function with insert statement==



<source lang="sql">
mysql>
mysql>
mysql> CREATE TABLE UserAccounts
-> (
-> UserID SMALLINT NOT NULL PRIMARY KEY,
-> Password VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> INSERT INTO UserAccounts VALUES (101, ENCODE("pw101", "key101"));
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from UserAccounts;
+--------+----------+
| UserID | Password |
+--------+----------+
| 101 | ?* |
+--------+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop table UserAccounts;
Query OK, 0 rows affected (0.00 sec)
mysql></source>

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/Insert Update Delete/Insert - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия