MySQL Database basic operations

Modify table structure (with Oracle)

• Rename:

 
   
  

  
    
   
ALTER TABLE test RENAME TO table_1；
 
   
  

• To add a field:

 
   
  

  
    
   
ALTER TABLE table_1 ADD id int(3);
  
    
   
ALTER TABLE table_1 ADD info varchar(30)；
 
   
  

• Modify Field Name:

 
   
  

  
    
   
 ALTER TABLE table_2 change info info2 varchar(30);
 
   
  

• To modify a field type:

 
   
  

  
    
   
ALTER TABLE table_1 modify info varchar(200);
 
   
  

• To delete a field:

 
   
  

  
    
   
ALTER TABLE table_1 DROP id;
 
   
  

• To add a constraint:

 
   
  

  
    
   
ALTER TABLE table_1 ADD CONSTRAINT 
 
   
  

Index

• To create an index:

 
   
  

  
    
   
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
  
    
   
CREATE INDEX 索引名 ON 表名字 (列名);
 
   
  

* * Note: An index is automatically created in MySQL foreign key * *

• To view the index of a table

 
   
  

  
    
   
 SHOW INDEX FROM table_name;
 
   
  

*

View

• Create a View

 
   
  

  
    
   
CREATE OR REPLACE VIEW vw_test[(col1,col2,...)] AS SELECT * FROM table_1; 
 
   
  

Import data

 
   
  

  
    
   
LOAD DATA INFILE ‘/tmp/SQL6/in.txt‘ INTO TABLE employee;
 
   
  

Exporting data

 
   
  

  
    
   
SELECT * INTO OUTFILE ‘/tmp/out.txt‘ FROM employee；
 
   
  

Note: The delimiter between the data (field) at this point is a space or a horizontal tab, and if the split symbol between the fields in the imported data is a comma, it is especially noted when importing, and its format is as follows

  
 

   
  

  
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE ‘file_name.txt‘ [REPLACE | IGNORE]
   
  

  

   
  

  
 INTO TABLE tbl_name
   
  

  
 [FIELDS
   
  

  
 [TERMINATED BY ‘\t‘]
   
  

  
 [OPTIONALLY] ENCLOSED BY ‘‘]
   
  

  
 [ESCAPED BY ‘\\‘]]
   
  

  
 [LINES TERMINATED BY ‘\n‘]
   
  

  
 [IGNORE number LINES]
   
  

  
 [(col_name,...)]
   
  

  
LOAD DATA INFILE 语句从一个文本文件中以很高的速度读入一个表中。如果指定 LOCAL 关键词，从客户主机读文件。如果 LOCAL 没指定，文件必须位于服务器上。(LOCAL 在 MySQL3.22.6 或以后版本中可用。）
  
 

Database backup

(There is also table structure information in addition to the data in the table, which is where the database backup and export differ.)

• Back up the entire database (at this point in the Linux terminal, not in MySQL, but the MySQL server must be turned on)

 
   
  

  
    
   
mysqldump -u root 数据库名>备份文件名;
 
   
  

• Backing up the entire table

 
   
  

  
    
   
mysqldump -u root 数据库名 表名称>备份文件名;
 
   
  

• Code instance

 
   
  

  
    
   
mysqldump -u root mysql_shiyan > db_bak.sql
 
   
  

Database recovery (now in MySQL)

• Method One

 
   
  

  
    
   
source /tmp/db_bak.sql;（此时位于mysql中）
 
   
  

• Method Two
  into MySQL:

 
   
  

  
    
   
mysql -u root
  
    
   
create database test;
  
    
   
exit
 
   
  

At this point in the terminal:

 
   
  

  
    
   
mysql -u root test < db_bak.sql
 
   
  

To restore the data in Db_bak.sql to the newly created empty database test

From for notes (Wiz)

MySQL Database basic operations