Collection of basic MySQL operations

Export
#> Cd/usr/local/src
#>/Usr/local/MySQL/bin/mysqldump-uroot-p123456 cacti> cacti. SQL;
So that cacti. SQL is exported to the/usr/local/src/directory.
1. Export the entire database
Mysqldump-u username-P Database Name> exported file name
Mysqldump-u root-p abc> ABC. SQL
2. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u root-p abc users> abc_users. SQL
3. Export a database structure
Mysqldump-u root-p-d -- add-drop-Table ABC>/usr/local/abc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.
 
Import
#>/Usr/local/MySQL/bin/MySQL-uroot-P
Mysql> Create Database cacti; name of the new database cacti
Mysql> Use cacti; select the target database
Mysql> source/usr/local/src/cacti. SQL import database
In this way, the cacti. SQL file is imported.

Alter table: add, modify, and delete table columns, constraints, and other table definitions.

View column: DESC table name;
Alter table t_book Rename to BBB;
Add column: alter table table name Add column name varchar (30 );
Delete column: alter table Table Name drop column name;
Alter column name MYSQL: alter table bbb change oldname newname mysql_filed_type (forexample varchar (255) or INT );
Modify the column name sqlserver: exec sp_rename 't_ student. name', 'nn ', 'column ';
Alter column name ORACLE: alter table bbb rename column nnnnn to HH int;
Alter table t_book modify name varchar (22 );

1. Primary Key (primary key index)
Mysql> alter table 'table _ name' add primary key ('column ')
2. Unique (unique index)
Mysql> alter table 'table _ name' add unique ('column ')
3. Index (Common Index)
Mysql> alter table 'table _ name' add index index_name ('column ')
4. Fulltext (full-text index)
Mysql> alter table 'table _ name' add Fulltext ('column ')
5. Multi-column Index
Mysql> alter table 'table _ name' add index index_name ('column1 ', 'column2', 'column3 ')

--------------------------------------------------------
You can use the alter table or drop index statement to delete an index. Similar to the create index statement, drop index can be processed as a statement in alter table. The syntax is as follows.

 
Drop index index_name on talbe_name

Alter table table_name drop index index_name

Alter table table_name drop primary key

 

The first two statements are equivalent. The index index_name in table_name is deleted.

The first statement is only used to delete the primary key index. Because a table only has one primary key index, you do not need to specify the index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.

If a column is deleted from the table, the index is affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

--------------------------------------------------------

Insert is a common statement in T-SQL. insert into table (field1, field2 ,...) values (value1, value2 ,...) this form is essential for application development. However, during the development and testing processes, we often encounter situations where table replication is required, such as copying some of the data fields of Table 1 to table 2 or copying the entire table 1 to table 2, in this case, we need to use the select into and insert into select table copy statements.

1. insert into SELECT statement

Statement format: insert into Table2 (field1, field2,...) Select value1, value2,... from Table1

The target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1.

2. Select into from statement

Statement format: Select vale1, value2 into Table2 from Table1

The target table 2 does not exist because table 2 is automatically created during insertion and the specified field data in table 1 is copied to table 2.

Conclusion: The tables created in the form of create table tablename as select * from B in MySQL do not contain index information, and the like (CREATE TABLE tablename like tablename) clause contains the complete table structure and index information.

Therefore, the as select clause is generally applicable when you create a table and copy data from the source table. The like clause is applicable when only the table structure is copied.

Misuse risk: Missing indexes are critical to service performance.

ORACLE:

A create as select will not create an index.

B Oracle does not support like clauses

The methods for fully creating table structures and indexes are to be further explored!