"MySQL must know" [04] table operation and view usage

1, table operation now the work of creating a table is mostly done using database management tools, although its nature is also used by SQL, but much more convenient. Now let's talk about using SQL statements to manipulate tables. 1.1 The base statement that creates the table for creating tables is (If you only want to create a table when it does not exist, give the keyword after the table name IF not EXISTS can be):

 
   
  

  
    
   
CREATE TABLE R (
  
    
   
 A1<数据类型>,
  
    
   
 A1<数据类型>,
  
    
   
 ...
  
    
   
 An<数据类型>
  
    
   
);
 
   
  

e.g.

  
 

   
  

  
CREATE TABLE vendors
   
  

  
(
   
  

  
 vend_id int NOT NULL AUTO_INCREMENT,
   
  

  
 vend_name char(50) NOT NULL,
   
  

  
 vend_state char(5) NULL,
   
  

  
 vend_country char(50) NULL DEFAULT ‘China‘,
   
  

  
 PRIMARY KEY (vend_id)
   
  

  
) ENGINE=InnoDB;
  
 

In the statement that creates the table, the definitions of the columns are separated by commas, after the table name, with the data type of the column, and in a more detailed definition, such as NOT NULL required column values must not be empty, Unique table column values must not be duplicated.

• Each column is either a null column or a NOT NULL column, which is defined when the table is created and, if not specified, the default is to allow null values;
• If a primary key is composed of multiple columns, it should be separated by commas, such as PRIMARY key (Order_num, Order_item)
• Auto_increment that is, this column increments automatically each time a row is added, only one auto_increment column is allowed per table, and must be indexed
• The last_insert_id () function can get the last Auto_increment value
• Default used to define values that are not specified
• The ENGINE=INNODB table specifies the internal engine that specifically manages and processes the data, which can be omitted, using the default engine
  
• InnoDB: A reliable transaction processing engine, but does not support full-text search
• MyISAM: An engine with very high performance, supports full-text search, but does not support transactional processing
• Memory: function is equivalent to MyISAM, but data is stored in RAM rather than disk, very fast (especially for temporary tables)

1.2 The basic statements for updating table update tables are:

 
   
  

  
    
   
ALTER TABLE R 具体的更改操作;
 
   
  

e.g.

  
 

   
  

  
//添加列
   
  

  
ALTER TABLE vendors
   
  

  
ADD vend_phone CHAR(20);
   
  

  

   
  

  
//删除列
   
  

  
ALTER TABLE vendors
   
  

  
DROP COLUMN vend_phone;
   
  

  

   
  

  
//添加外键
   
  

  
ALTER TABLE orderitems
   
  

  
ADD CONSTRAINT fk_orderitems_orders
   
  

  
FOREIGN KEY (order_num) REFERENCES orders (order_num);
  
 

1.3 The basic statement to delete table Delete table is:

 
   
  

  
    
   
DROP TABLE R;
 
   
  

1.4 The basic statements for renaming table renaming tables are:

 
   
  

  
    
   
RENAME TABLE R1 TO R2;
 
   
  

2. View Usage view is not a real table, it is a virtual table and contains only queries that use dynamic retrieval of data. Views are typically defined by different application areas or different user groups, allowing users to get out of the data in the database and only care about the data they need.
The basic statements for creating a view are:

 
   
  

  
    
   
CREATE VIEW <视图名> AS <子查询>;
 
   
  

With the view, you can write some basic SQL and, as necessary, match the SELECT statement to hide complex SQL for a simplified purpose.
Attention:

• The view does not actually contain data, but instead executes the query each time it is used, so the logic is too complex for the view to degrade performance
  
• Views can be nested, that is, you can query data from other views to construct a new view
• Views can be used with tables, such as a Nexus table and an attempted SELECT statement
• Views cannot be indexed

"MySQL must know" [04] table operation and view usage