MySQL Create, modify, and delete tables

Log in to the database system:

Mysql–h Localhost–u root–p

where the "-H" parameter refers to the host name of the connection, so here is the connection to the machine, so the following content is localhost;

The "-u" parameter indicates the user name, where the user name is root;

the "-P" parameter refers to the user name password.

To create a database:

CREATE database name;

To display a database that already exists:

SHOW DATABASES;

To delete a database:

DROP database;

Database Storage Engine: The type of table that the storage engine refers to. The storage engine of the database determines how the table is stored on the computer.

To view the types of storage engines supported by the database:

SHOW ENGINES;

Enquiry MySQL -supported storage engines:

SHOW VARIABLES like ' have% ';

To query the default storage engine:

SHOW VARIABLES like ' storage_engine ';

If you want to change the default storage engine, you can modify it in My.ini. Change "Default-storage-engine=innodb" to "Default-storage-engine = MyISAM". Then restart the service and the changes will take effect. ( need to verify, did not find the file, it may be the file name is different)

Comparison of storage engines:

Recommendations for choosing a storage engine:

  ? ? InnoDB ? ? Storage Engine ????:InnoDBthe storage engine supports transactional processing and supports foreign keys. It also supports crash repair and concurrency control. If you need to have a high level of integrity requirements for transactions that require concurrency control, selectInnoDBThe storage engine has a great advantage. If you need to update frequently, delete the database of operations, you can also selectInnoDBstorage engine. Because this class of storage engine can implement transaction commit (Commit) and rollback (Rollback).

MyISAM Storage Engine : MyISAM storage Engine has fast insert data, low space and memory usage. If the table is primarily used to insert new records and read out records, then choosing the MyISAM storage engine can achieve high efficiency in processing. If the integrity of the application, the concurrency requirements are low, you can also choose the MyISAM storage engine.

Memory Storage Engine : All the data in memory storage engine is in RAM, the data is processed fast, but the security is not high. If you need fast read and write speed, the data security requirements are low, you can choose memory storage engine. the memory storage engine has a requirement for the size of the table and cannot build too large a table. Therefore, this type of database is only used in relatively small database tables.

tips : a table with multiple storage engines can be used in the same database. If a table requires a higher transaction, you can choose InnoDB. This database can select the MyISAM storage engine for tables with higher query requirements . If you need a temporary table for queries in your database, you can choose the memory storage engine.

Syntax for creating tables:

CREATE Table Table name (property name data type [integrity constraint], property name data type [integrity constraint], ... Property name data type);

The table name parameter represents the name of the table you want to create,

The property name parameter represents the name of the field in the table,

The data type parameter specifies the data type of the field,

the integrity constraint parameter specifies certain special constraints for a field.

Note: When creating a table using the create table statement, you first Select the database by using the use statement. The basic format for selecting a data statement is " use database name ". If you do not select a database, "error 1046 (3d000): No database selected" errors appear when you create the table .

Integrity constraint Table

The DESCRIBE statement can view the basic definition of a table. This includes the field name, the field data type, whether the primary key and the default value, and so on. The syntax form of the DESCRIBE statement is as follows:

DESCRIBE table name; (DESCRIBE can be abbreviated as DESC)

The Show CREATE table statement allows you to view a detailed definition of the table. The statement can view the table's field names, field data types, integrity constraints, and other information, as well as view the table's default storage engine and character encoding. the SHOW CREATE TABLE statement has the following syntax:

SHOW CREATE table table name;

Modify Table Name:

ALTER table Old table name RENAME [to] new table name;

To modify the data type of a field:

ALTER Table name MODIFY property name data type;

Where the "table name" parameter refers to the name of the table to be modified;

The "attribute name" parameter refers to the name of the field to be modified;

the "data type" parameter refers to the modified new data type.

Modify Field Name:

ALTER Table name change old property name new data type with new property name;

The new data type parameter modifies the data type and, if no modification is required, sets the new data type to be the same as the original.

Add Field:

ALTER Table name ADD property name 1 data type [integrity constraint] [first| After property name 2];

The " First" parameter is also an optional parameter, which is used to set the new field as the field of the table;

The After property name 2 parameter is also an optional parameter that adds new fields to the field referred to by property name 2. If you execute an SQL statement that does not have the "first" "after property name 2" parameter specifying the location of the new field, the new word defaults the last field of the table.

To delete a field:

ALTER table name DROP attribute name;

Where the attribute name parameter refers to the name of the field that needs to be removed from the table.

To modify the order in which fields are arranged:

ALTER Table name MODIFY property name 1 data type first| After property name 2;

the "property name 1" parameter refers to the field name where the location needs to be modified;

"data type" parameter refers to the data type of " property name 1";

" First "parameter specifies the position of the table;

" After property Name 2 " parameter specified " property name 1 " is inserted after" property name 2 " .

To change the storage engine for a table:

ALTER table name engine= storage engine name;

Where the "Storage engine name" parameter refers to the name of the new storage engine that is set.

MySQl storage engine includes InnoDB, MyISAM, memory and so on.

To delete a foreign KEY constraint for a table:

ALTER table name DROP FOREIGN key foreign key alias;

To delete a table:

DROP table name;

Tip: When you delete a table, all of the data in the table is also deleted. Therefore, you must be cautious when deleting a table. The surest way to do this is to back up all the data in the table before deleting the table. Once the table has been deleted and the loss is found, it can be restored by backing up the data to minimize the loss.

MySQL Create, modify, and delete tables