Shulou(Shulou.com)06/01 Report--

1.INSERT INTO SELECT statement

The statement form is: Insert into Table2 (field1,field2,...) Select value1,value2,... From Table1

Or: Insert into Table2 select * from Table1

Note: (1) it is required that the target table Table2 must exist and the field field,field2... It has to exist.

(2) pay attention to the primary key constraint of Table2. If Table2 has a primary key and is not empty, then field1, field2... The primary key must be included in

(3) pay attention to the syntax. Don't add values, mix it with the sql that inserts a piece of data, and don't write it as:

Insert into Table2 (field1,field2,...) Values (select value1,value2,... From Table1)

Since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1. Examples are as follows:

-1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10)) create TABLE Table2 (a varchar (10), c varchar (10), d int)-- 2. Create test data Insert into Table1 values ('Zhao', 'asds','90') Insert into Table1 values (' Qian', 'asds','100') Insert into Table1 values (' Sun', 'asds','80') Insert into Table1 values (' Li', 'asds',null) select * from Table2-- 3.INSERT INTO SELECT statement to copy the partial columns of the table data and the constant Insert into Table2 (AMagol c, d) select a memoir 5 from Table1 or: Insert into Table2 select * from Table1--4. Display the updated result select * from Table2-- 5. Delete test table drop TABLE Table1 drop TABLE Table2

2.SELECT INTO FROM statement

The statement form is: SELECT vale1, value2 into Table2 from Table1

It is required that the target table Table2 does not exist because the table Table2 is automatically created upon insertion and the field data specified in the Table1 is copied to the Table2. Examples are as follows:

-1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10))-- 2. Create test data Insert into Table1 values ('Zhao', 'asds','90') Insert into Table1 values (' Qian', 'asds','100') Insert into Table1 values (' Sun', 'asds','80') Insert into Table1 values (' Li', 'asds',null)-- 3.SELECT INTO FROM statement to create table Table2 and copy data select an INTO Table2 from Table1 c INTO Table2 from Table1-4. Display the updated result select * from Table2-- 5. Delete test table drop TABLE Table1 drop TABLE Table2

Note: if you execute this statement in sql/plus or PL/SQL, a "ORA-00905: missing keyword" error will be reported because of the difference between PL/Sql and T-SQL.

This sentence is normal in T-SQL, but it is explained in PL/SQL as:

Select..into is part of PL/SQL language which means you have to use it inside a PL/SQL block. You can not use it in a SQL statement outside of PL/SQL.

That is, it cannot be executed as a single sql statement, and is generally used in PL/SQL blocks (block).

If you want to implement this feature in PL/SQL, you can use Create table newTable as select * from.:

Such as: create table NewTable as select * from ATable

NewTable is the same as ATable except that there are no keys.

-introduction to SQL SELECT INTO syntax

The SQL SELECT INTO statement can be used to create a backup copy of a table.

SELECT INTO statement

The SELECT INTO statement selects data from one table and inserts it into another table.

SELECT INTO statements are often used to create backup copies of tables or to archive records.

SQL SELECT INTO syntax

You can insert all the columns into the new table:

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename

Or just insert the desired columns into the new table:

SELECT column_name (s) INTO new_table_name [IN externaldatabase] FROM old_tablename

SQL SELECT INTO instance-making backup copy

The following example makes a backup copy of the "Persons" table:

SELECT * INTO Persons_backup FROM Persons

The IN clause can be used to copy tables to another database:

SELECT * INTO Persons IN 'Backup.mdb' FROM Persons

If we want to copy some fields, we can list them after the SELECT statement:

SELECT LastName,FirstName

INTO Persons_backup

FROM Persons

SQL SELECT INTO instance-with WHERE clause

We can also add the WHERE clause.

The following example creates a table called "Persons_backup" with two columns by extracting information about people living in "Beijing" from the "Persons" table:

SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'

SQL SELECT INTO instance-joined table

It is also possible to select data from more than one table.

The following example creates a new table named "Persons_Order_Backup" that contains the information obtained from the Persons and Orders tables:

SELECT Persons.LastName,Orders.OrderNo

INTO Persons_Order_Backup

FROM Persons

INNER JOIN Orders

ON Persons.Id_P=Orders.Id_P

Comparison of select into between select into in mysql and sql

Now that there is a table called student, I want to copy the data in this table to a new table called dust.

Answer 01:

Create table dust select * from student;// is used when a new table dust is not created before replication

Answer 02:

When insert into dust select * from student;// has created a new table dust

Use select..into.. now Statement to achieve the above.

MySQL does not support Select Into statements to back up table structures and data directly. Some methods can be replaced, and there are other ways to deal with them. It is summarized as follows:

Method 1:

MYSQL does not support:

Select * Into new_table_name from old_table_name; this is the use of sql server

Alternative method:

Create table new_table_name (Select * from old_table_name)

Method 2:

1. Back up the table structure and data first

# Export command-u user name-p password-h host IP address database name table name 1 > export file. Sql

Mysqldump-uroot-proot-h292.168.0.88 ok_db oktable2 > ok_db.sql

two。 Modify the name of the backup table

3. Log in to MySQL

4. Select a database

5. Execute: the path of the Source backup table, such as: Source d:/ok_db.sql enter.

6. Done.

MySQL Select into outfile is used to export the specified query data to a file as follows:

1. Export all the data in the table to the C disk root directory outfile.txt as follows:

Select * into outfile'cDrexUniUniUniAccording to outfile.txt' from test

two。 Export the data specified in query condition 2005-06-08 in the table to the root directory outfile1.txt of disk C as follows:

Select * into outfile'cDrexUniUniUniUniCompact outfile.txt' from test where beginDate='2008-06-08'

Mysql > load data local infile "d:/gpsdata.txt" into table positiondata fields terminated by';'(userid,latitude,longitude,altitude,speed,innerid,repo

Rttime,status)

LOAD DATA [LOW_PRIORITY CONCURRENT] [LOCAL] INFILE 'file_name.txt'

[REPLACE IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name = eXPr,...)]

Fields and lines are in front, (col_name_or_user_var, …) Later, if you use these attributes directly after the table name, this is not correct, be sure to write after fields and lines!

In addition, table A data is copied to table B, and table B cannot have self-increasing ID.

If there is self-increment ID, do not insert self-increment

Insert into B (title) select title from A

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.