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

How to write the table creation statement in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

In MySQL, you can use the "CREATE TABLE" statement to create a table, written as "CREATE TABLE table name ([table definition option]) [table option] [partition option];" where "[table definition option]" is in the format "column name 1 type 1 [, …] column name n type n".

After you create the database, the next step is to create the data table in the database. The so-called creation of data tables refers to the creation of new tables in the database that has already been created.

The process of creating a data table is not only the process of specifying the attributes of the data column, but also the process of implementing data integrity (including entity integrity, referential integrity, and domain integrity) constraints.

Basic grammar

In MySQL, you can use CREATE TABLE statements to create tables. Its grammatical format is:

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

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).

Here we first describe a simple example of creating a new table, and then focus on some of the main syntax points in the CREATE TABLE command.

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

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

Specifies the name of the table to be created, given after CREATE TABLE, and must conform to the naming convention 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.

A table creation definition consisting of a column name (col_name), a column definition (column_definition), and a possible null value description, integrity constraint, or table index

By default, the table is 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.

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.

Example

Create the employee table tb_emp1, with the structure shown in the following table.

Field name data type remarks idINT (ll) employee number nameVARCHAR (25) employee name deptldINT (ll) Department number salaryFLOAT salary

Select the database test_db that created the table, and create the tb_emp1 data table. Enter the SQL statement and run the results as shown below.

Mysql > USE test_db;Database changedmysql > 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 below.

Mysql > SHOW TABLES;+-+ | Tables_in_test_db | +-+ | tb_emp1 | +-+ 1 rows in set (0.00 sec) Thank you for reading! After reading the above, do you have a general understanding of how to write the table creation statement in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, 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.