MySQL connection command table management, clone table, temp table, string attribute, set delimiter between statements

Source: Internet
Author: User

Connect and Disconnect
Connection
Mysql-h Host-u User-p (that is, the connected host, user name, and password used).
Disconnect
Enter quit (or \q) to exit at any time:


Table Management
Clone table
Note: The CREATE TABLE ... like statement does not clone the foreign key definition of the table, and does not clone the data directory and index directory that the original table might use. and auto_increment


--Create a table that is consistent with the existing table structure.
CREATE TABLE new_table like original_table

--Inserting a piece of data into a cloned table
INSERT INTO new_table select * from original_table
INSERT into new_table (column) Select column from original_table


--Clone a table and copy the data.
CREATE TABLE new_table select * FROM original_table

Temp table
Temporary tables are related to individual database connections, and disconnection automatically deletes temporary tables.
Another feature of temporary tables is that temporary tables can use the table name of a normal table, and the result is that it masks the normal table with the same name in the life cycle of the temporary table.

Create a temporary table
Create temporary table name like directory table name
Delete temporary table
Drop temporary table name. (Temporary keyword here, you can avoid the wrong deletion of the common table with the same name.) )

drop table name.


Changing the table's storage engine
MyISAM
ALTER TABLE name engine = InnoDB

View the storage engine for a table
Select Engine from INFORMATION_SCHEMA. ' TABLES ' where table_name= ' table name '


String properties
is divided into two types, binary and non-binary.
One of the characteristics of non-binary strings is that they have a character set.
View system character set show character set;
Two characteristics of non-binary strings: Collation
View COLLATION in the character set: Show COLLATION;
You can also view only the COLLATION of a specific character set for example: show COLLATION like ' latin1% ';

Delimiter//Set the delimiter between statements is//
Cases
SELECT * from ' user ';
SELECT * from ' user '
Change into
Delimiter//
SELECT * from ' user '//
SELECT * from ' user '

MySQL connection command table management, clone table, temp table, string attribute, set delimiter between statements

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.