Shulou(Shulou.com)06/03 Report--

This article shows you what is the basic operation of MySQL database, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL creates a data table

1. CREATE TABLE creates the basic format of Datasheet syntax:

CREATE TABLE ([Table definition options]) [Table options] [Partition options]

2. CREATE TABLE create data table parameters description:

The format of [table definition options] is: [, …]

There are many syntax for CREATE TABLE command, which is mainly composed of table creation definition (create-definition), table option (table-options), and partitioning option (partition-options).

The main syntax and usage instructions of the 3.CREATE TABLE statement are as follows

(1) CREATE TABLE: used to create a table with a given name, you must have permission for the table CREATE.

(2): specify the name of the table to be created, given after CREATE TABLE, must conform to the naming rules for identifiers. The table name is specified as db_name.tbl_name to create the table in a specific database. It can be created in this way, regardless of whether there is a current database or not. You can omit db-name when you create a table in the current database. If you use a quoted distinguished name, the database and table names should be in quotation marks, respectively. For example, 'mydb'.'mytbl' is legal, but' mydb.mytbl' is illegal.

(3): table creation definition, which consists of column name (col_name), column definition (column_definition), and possible null value descriptions, integrity constraints, or table indexes.

(4) by default, tables are created into the current database. An error occurs if the table already exists, there is no current database, or the database does not exist.

Tip: when you create a table using CREATE TABLE, you must specify the following information:

The name of the table to be created is case-insensitive and cannot use keywords in the SQL language, such as DROP, ALTER, INSERT, and so on.

The name and data type of each column (field) in the data table. If you create multiple columns, separate them with commas.

4. Creates a table in the specified database

The data table belongs to the database. Before creating the data table, you should use the statement "USE" to specify the database in which the operation is performed. If no database is selected, an No database selected error will be thrown.

For example: select the database test_db that creates the table, and create the tb_emp1 data table. Enter the SQL statement and run the result as shown below.

Mysql > USE test_db

Database changed

Mysql > CREATE TABLE tb_emp1

-> (

> id INT (11)

Name VARCHAR (25)

> deptId INT (11)

-> salary FLOAT

->)

Query OK, 0 rows affected (0.37 sec)

After the statement is executed, a data table named tb_emp1 is created, and the SHOW TABLES statement is used to see if the data table is created successfully, as shown in the following figure.

5. View table structure

You can use DESCRIBE and SHOW CREATE TABLE statements to view the table structure. The DESCRIBE/DESC statement can view the field information of the table, including field name, field data type, whether it is a primary key, whether there is a default value, and so on. The syntax rules are as follows:

DESCRIBE

/ / abbreviated to DESC

The SHOW CREATE TABLE statement can be used to display the CREATE TABLE statement when the table was created. The syntax format is as follows:

SHOW CREATE TABLE\ G

Tip: using the SHOW CREATE TABLE statement, you can view not only the detailed statements when the table was created, but also the storage engine and character encoding. If you do not add the "\ G" parameter, the displayed results may be very confusing, and after adding the "\ G" parameter, you can make the displayed results more intuitive and easy to view.

MySQL modifies database tables

1. ALTER TABLE modifies the basic format of table name syntax:

ALTER TABLE RENAME [TO]; / / TO is an optional parameter, and whether you use it or not will not affect the result.

two。 Modify table character set

The ALTER TABLE table name is [DEFAULT] CHARACTER SET [DEFAULT] COLLATE; / / where DEFAULT is an optional parameter, and whether you use it or not will not affect the result. MySQL modify / delete fields

1. The basic syntax for ALTER TABLE to modify field names is as follows:

ALTER TABLE CHANGE

2. ALTER TABLE modifies field name parameter description

(1) Old field name: refers to the field name before modification

(2) New field name: refers to the modified field name

(3) New data type: refers to the modified data type. If you do not need to modify the data type of the field, you can set the new data type to the same as the original, but the data type cannot be empty.

3. ALTER TABLE modifies the basic syntax format of field data types:

ALTER TABLE MODIFY

4. ALTER TABLE modifies field data type parameter description

(1) Table name: refers to the name of the table where the field of the data type is to be modified

(2) Field name: refers to the field to be modified

(3) data type: refers to the new data type of the modified field.

5.ALTER TABLE delete field basic syntax format:

ALTER TABLE DROP; / / Field name "refers to the name of the field that needs to be deleted from the table. MySQL Delete data Table"

1. DROP TABLE deletes the basic format of data table:

DROP TABLE [IF EXISTS] Table name 1 [, Table name 2, Table name 3.]

2. DROP TABLE Delete data Table parameters description:

(1) Table name 1, table name 2, table name 3. Represents the name of the data table to be deleted. DROP TABLE can delete multiple tables at the same time, as long as the table names are written after each other and separated by commas.

(2) IF EXISTS is used to determine whether a data table exists before it is deleted. Without IF EXISTS, MySQL will prompt an error when the data table does not exist and interrupt the execution of the SQL statement; with IF EXISTS, the SQL statement can be executed smoothly when the data table does not exist, but will issue a warning (warning).

Note:

The user must have permission to execute the DROP TABLE command, or the data table will not be deleted.

When a table is deleted, the user's permissions on the table are not automatically deleted.

MySQL deletes the primary table associated with other tables

There are two ways to delete a parent table:

Delete the child table associated with it first, and then delete the parent table; however, this will delete the data in both tables.

Cancel the foreign key constraint of the associated table, and then delete the parent table; it is applicable to the case where you need to retain the data of the child table and delete only the parent table.

Add fields to MySQL data sheet (three ways)

1. Add the basic format of field syntax at the end:

ALTER TABLE ADD [constraint]

two。 Parameter description

(1) is the name of the data table

(2) name of the field to be added

(3) the data type that can store data for the field to be added

(4) [constraints] are optional and are used to constrain added fields.

Note: this syntax format defaults to adding new fields at the end of the table (after the last column).

3. Add the basic format of field syntax at the beginning:

ALTER TABLE ADD [constraint] FIRST

4. Add the basic format of field syntax in the middle:

ALTER TABLE ADD [constraint] AFTER

Note that you can only add a new field after an existing field, not before it.

The above is what is the basic operation of MySQL database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.