Last Updated:

Create a SQL database and working with tables in SQL {Include MySQL}

Create a table

In the previous tutorial, we learned how to create a database on a MySQL database server. Now it's time to create some tables in our database that will contain the data. A database table simply organizes information into rows and columns.

To create a table, use the CREATE TABLE statement.

Syntax

The basic syntax for creating a table can be given using:

 

CREATE TABLE table_name ( column1_name data_type constraints, column2_name data_type constraints, .... );

 

To understand this syntax, let's create a table in our demo demo database. Type the following statement in the MySQL command-line tool and press enter:

CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE ); Block 1

The example above creates a table with four columns: id, name, birth_date, and phone. Note that each column name is followed by a data type declaration; this declaration specifies what type of data the column will store: integer, string, date, and so on.

Some data types can be declared with a length parameter that specifies how many characters can be stored in a column. For example, a VARCHAR(50) may contain up to 50 characters.

Data Types in MySQL

The following table lists the most commonly used data types supported by MySQL.

INTStores numeric values ranging from -2147483648 to 2147483647.
DECIMALStores decimal values.
CHARStores fixed-length strings with a maximum size of 255 characters.
VARCHARStores variable-length strings with a maximum size of 65,535 characters.
TEXTStores strings with a maximum size of 65,535 characters.
DATEStores date values in the format YYYY-MM-DD.
DATETIMEStores concatenated date/time values in the format YYYY-MM-DD HH: MM:SS.
TIMESTAMPStores timestamp values. TIMESTAMP values are stored as the number of seconds since the beginning of the Unix era (1970-01-01 00:00:01 UTC).

There are several additional constraints (also called modifiers) that are set for the columns of the table in the preceding expression. Constraints define rules about the values allowed in columns.

    • The NOT NULL constraint ensures that the field cannot accept a null value.
    • The PRIMARY KEY constraint marks the corresponding field as the primary key of the table.
 
  • The AUTO_INCREMENT attribute is a MySQL extension to standard SQL that tells MySQL to automatically assign a value to this field if it is not specified by incrementing the previous value by 1. Available only for numeric fields.
  • The UNIQUE constraint ensures that each row for a column must have a unique value.

Create a database table

There is a sql statement that is used to create database tables. Statement: CREATE TABLE.

Recall that a relational database table must have unique:

  • Unique name;
  • Columns (attributes);
  • Primary key.
  • Strings (records);
 

To begin with, we create only a table and do not fill it with data.

 

In a good way, the general appearance of a table with unique row and column names is specified when you create a conceptual database model. For example, we will create a simple database table and dispense with the conceptual model.

In this article, we will create a database table with buyers. Buyers are taken as an example.

How is the buyer identified? We decide that each buyer is identified by the following data:

  • Name;
  • Surname
  • Login;
  • Password;
  • Email;
  • telephone.

Remember that in the table it is necessary to specify the primary key.

To create a database table, use the sql CREATE TABLE statement.

The syntax of the operator is as follows:

CREATE TABLE table name (column 1, column type (space), column parameter (space), column 2, column type (space), column parameter (space), column 3, column type (space) column parameter (space), etc.)

In our version, the table name is clients. We give the buyers' data column designations:

  • Name: Let it be client_customer;
  • Surname: client_subclient;
  • Login: client_login;
  • Password: client_password;
  • eMail: client_mail;
  • Telephone: client_telefon.
 

Think about what type of data will be stored in these columns, and select the data types CHAR (field length constantly) or VARCHAR (field length variable). In our case, the VARCHAR data type is suitable.

We pay special attention to the primary key. We make the primary key the buyer id. In the parameters of the key, we specify that this is the primary key, it cannot be zero, and that for the next record it is incremented by one. The last property is called autoincrement. Look at the hint in the reference (link above) and get a column with the primary key:

client_id integer not null auto_increment primary key

NULL and NOT NULL values

 

If the column is null, then empty rows will be added to the table. Conversely, if a column is defined as NOT NULL, then empty rows will not be added.

Primary Keys

 

A primary key is a column that is used to identify records in a table. The value of the primary key column must be unique. If multiple columns are combined into a primary key, the combination of key values must be unique for each row.

The primary key is defined using the PRIMARY KEY operator during table creation. If multiple columns are used, they are separated by a comma:

PRIMARY KEY (column_name, column_name...)

The following example creates a table using two columns as the primary key:

CREATE TABLE product ( prod_code INT NOT NULL AUTO_INCREMENT, prod_name char(30) NOT NULL, prod_desc char(60) NULL, PRIMARY KEY (prod_code, prod_name) ) ENGINE=InnoDB; Database Structure and Design Guide

AUTO_INCREMENT

 

When a column is defined by using a AUTO_INCREMENT, its value is automatically incremented each time a new record is added to the table. This is useful when using a column as the primary key. Because of AUTO_INCREMENT do you do not need to write SQL statements to calculate a unique identifier for each row.

AUTO_INCREMENT can be assigned to only one column in a table. And it must be indexed (for example, declared as a primary key).

 
 

You can override the AUTO_INCREMENT value for a column by specifying a new value when you execute an INSERT statement.

You can query MySQL for the most recent AUTO_INCREMENT value using the last_insert_id() function as follows:

SELECT last_insert_value();

Define default values when creating a table

Default values are used when a value is not defined when inserted into the database.
Default values are set using the DEFAULT keyword in the CREATE TABLE statement. For example, the following SQL query sets a default value for the sales_quantity column:

CREATE TABLE sales { sales_number int NOT_NULL, sales_quantity int NOT_NULL DEFAULT 1, sales_desc char(20) NOT_NULL, PRIMARY KEY (sales_number) ) ENGINE=MyGlobel; Lab : MySQL

Types of MySQL database engines

Each of the examples of creating a table in this article up to this point has included the definition of ENGINE= . MySQL comes with several different database engines, each with its own advantages. Using the ENGINE = directive, you can choose which engine to use for each table. The following MySQL database engines are currently available:

  • InnoDB — was introduced in MySQL version 4.0 and is classified as a secure environment for transactions.Its mechanism ensures that all transactions are completed 100%. In this case, partially completed transactions (for example, as a result of a server failure or power failure) will not be recorded. The disadvantage of InnoDB is the lack of support for full-text search.
  • MyGlobel is a high-performance engine with support for full-text search. This performance and functionality is ensured by the lack of transaction security.
  • MEMORY— in terms of functionality, it is equivalent to MyGlobel, except that all data is stored in RAM, not on the hard drive. This ensures high processing speed. The temporary nature of the data stored in RAM makes the MEMORY engine more suitable for temporary storage of tables.

Engines of different types can be combined in one database. For example, some tables may use the InnoDB engine and others may use MyGlobel. If the engine is not specified during table creation, MySQL will use MyGlobel by default.

To specify the type of engine that will be used for the table, put the appropriate ENGINE= definition after defining the columns of the table:

CREATE TABLE tmp_orders { tmp_number int NOT_NULL, tmp_quantity int NOT_NULL, tmp_desc char(20) NOT_NULL, PRIMARY KEY (tmp_number) ) ENGINE=MEMORY;

Please post your comments on the current topic of the article. For comments, responses, likes, dislikes, subscriptions low bow to you!

This publication is a translation of the article "Creating Databases and Tables Using SQL Commands", prepared by the editorial board of the project.

Write a SQL query to create a database table

Summarize all the initial data and get the following SQL query:

/*Clients*/ create table clients ( /*client_id will be a primary key (always an integer) with an autoincrement (+1) that will never be zero*/ client_id integer not null auto_increment primary key, client_customer varchar(13), /*name */ client_surclient varchar(22), /*last name */ client_login varchar(21), /*login*/ client_passwd varchar(7), /*password*/ client_email varchar(44) /*email*/ client_telefon varchar(26) /*phone*/ );

Note: We create a SQL query to create a table in a text editor of the Notepad++ type. In parentheses we limit the length of the field, can be from 1 to 255.

Check the permissions first!

This task requires CREATE TABLE permission on the database and ALTER permission on the schema in which the table is created.

If any of the columns in the CREATE TABLE statement are defined as belonging to a CLR user-defined data type, you must own or have REFERENCES permission on that type.

If any columns in a CREATE TABLE statement have an associated XML schema collection, you must own or have REFERENCES permission on that schema set.

Using Table Designer

  1. In SSMS, in Object Explorer, connect to the instance of the Database Engine that contains the database that you are modifying.

  2. In Object Explorer, expand the Databases node, and then expand the database that will host the new table.

  3. In Object Explorer, right-click the Database Tables node, and then click New Table.

  4. Enter column names, select data types, and determine for each column whether null values can be present in the column.

  5. You can also set other properties of a column, such as whether the column is an identity column or a calculated column. To do this, click a column on the column properties tab. For more information about column properties, see Table Column Properties (SQL Server Management Studio).

  6. To specify that a column is a primary key column, right-click the column, and then click Set Primary Key. For more information, see Create Primary Keys.

  7. To create foreign key relationships, check constraints, or indexes, right-click the Table Designer pane and select an object from the list, as shown in the following illustration:

    For more information about these objects, see Create Foreign Key Relationships, Create Check Constraints, and Indexes.

  8. By default, the table is contained in the dbo schema. To specify a different schema for the table, right-click the Table Designer pane and select Properties , as shown in the following illustration. Select the desired schema from the Schema drop-down list.

    For more information about schemas, see Create a Database Schema.

  9. On the File menu, click SaveTableName.

  10. In the Select Name dialog box, type a name for the table, and then click OK.

  11. To view the new table, in Object Explorer, expand the Tables node, and then press F5 to refresh the list of objects. The new table is displayed in the list of tables.

Using Query Editor

  1. In Object Explorer, connect to an instance of the Database Engine.

  2. In the standard pane, click New Query.

  3. Copy the following example into the query window, and then click Run.

    CREATE TABLE dbo. PurchaseOrderDetail ( PurchaseOrderID int NOT NULL ,LineNumber smallint NOT NULL ,ProductID int NULL ,UnitPrice money NULL ,OrderQty smallint NULL ,ReceivedQty float NULL ,RejectedQty float NULL ,DueDate datetime NULL );

For more information, see CREATE TABLE (Transact-SQL).

Preconditions

Before you start, you must have both MS SQL Server and SSMS installed. The server version should be selected based on the technical characteristics of your computer and the installed operating system. This article uses MS SQL Server 2017 Express.

Also, tables cannot exist without a database. Therefore, before you start creating tables, you should create an empty database. This is done very simply – just start the SSMS environment and perform a few simple steps: 1) right-click on the "Databases" container and select "Create Database"; 2) in the field that appears, specify the name of the database; 3) click "OK".

 

Source data

 

The database has been created, so you can think about its structure. It is planned to create two tables: 1. Goods – table with information about goods. Will contain several columns: — ProductId. Represents a product identifier. The value should not be NULL. There will also be a primary key; — Category. This is a link to the product category. Not NULL. If the product is not distributed in the desired category, it is assigned a default category ("Not specified", "Not defined"); — ProductName. In this column, the name of the product will be displayed. Not NULL; — Price. It's about cost. If the price is not yet determined, NULL is possible. 2. Categories — the second table. It will describe the categories of goods sold, represented by two columns: — CategoryId. Represents a category identifier. Non-NULL, primary key; — CategoryName. Category name, not NULL.

In order to prevent the entry of goods with a non-existent category, a foreign key restriction will be added.

 

Creating a table in Management Studio

 

When the structure is clear, you can start creating: 1) open the "Databases" container; 2) open the newly created test database; 3) Right-click on the "Tables" category and select "Table".

 

As a result, a special table designer with three columns will be opened: • column name; • data type (read more about data types here); • NULL values (if yes , check the box).

Columns are filled in taking into account the previously designed structure for table Categories.

Next, we define the primary key by right-clicking on the required column (we have this CategoryId). Also select the item "Set primary key".

Now you need to ensure that a unique ID record is automatically generated in the column. To do this, the identity must be set to the IDENTITY property. This is done by including an "Identifier Specification" clause.

Now the user can save the table (right mouse button -> "Save" or the well-known combination "Ctrl+S"). It remains to enter the name of the table and click "OK".

 

Next up is the Goods table. Here, the same actions are performed with the only difference that the Category column is additionally set to a default value and a foreign key constraint is created. To do this, in the properties of the corresponding column in the default value, write 1.

 

And to create a FOREIGN KEY, right-click on any surface of the design surface and select "Relationships...".

Next, click the "Add" button.

 

Now let's specify the specification of the tables and columns:

In the window that opens, specify: • the table of the primary key – Categories; • The foreign key table is the current table that has not yet been created, so it is displayed as a Table_1. Here you should select the Category column from the current table - it will act as a foreign key (the mapping will be CategoryId = Category); • Link Name — the name of the restriction. You can write FK_Category.

 

What remains is to define the update and uninstall rules. Leave the update rule as is (changing the ID is not a good idea). And to ensure that when a category is deleted, all products are assigned a default value, let's define the deletion rule as "Assign a default value".

 

It remains to save the created table, calling it Goods. A warning message may appear – it should not confuse you.

 

The tables that you create are visible in Object Explorer.

 

You can now work with them and add data by using the INSERT statement.

Инструкция CREATE TABLE

You can also create a new table by using the CREATE TABLE statement. CREATE TABLE is a keyword that tells the DBMS exactly what you want to execute. The creation syntax in the SQL language is as follows:

create table

As you can see, after the CREATE TABLE statement, you need to specify the name or ID of the table, then the list of columns and the data type for each column, as well as the primary key.

For example, let's create a TABLE CUSTOMERS. Specify the ID column as the primary key. Also set limits for fields that cannot have a null value when creating records (NOT NULL):

create table customers

You can see the structure of the created table if you make the corresponding query with the DESC command:

Description of customer in table

Numeric data types in MySQL

Data TypeSizeDefining
INT(Size)4 bytes

Integer -2,147,483,648 to 2,147,483,647

INT UNSIGNED(Size)4 bytesInteger 0 to 4,294,967,295
INTEGER(Size)4 bytesSynonym for INT
TINYINT(Size)1 byteInteger -128 to 127
TINYINT UNSIGNED(Size)1 byteInteger from 0 to 256
SMALLINT(Size)2 bytesInteger -32,768 to 32,767
SMALLINT UNSIGNED(Size)2 bytesInteger from 0 to 465.535
MEDIUMINT(Size)3 bytesInteger -8,388,608 to 8,388,607
MEDIUMINT UNSIGNED(Size)3 bytesAn integer between 0 and 16777215
BIGINT(Size)8 bytesInteger -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
BIGINT UNSIGNED(Size)8 bytesInteger 0 to 18,446,744,073,709,551,615
FLOAT(M,D)4 bytes

The decimal number from -3.402823466E+38 to -1.175494351E-38, 0 and from 1.175494351E-38 to 3.402823466E+38, where M is the number of digits in the number (<=25), D is the number of digits after the period. M and D are optional. By default, M=10, D=2.

Example: for FLOAT(5,2), the interval of valid values will be from -999.99 to 999.99.

FLOAT(M,D) UNSIGNED4 bytesSame as FLOAT only without negative values
DOUBLE(M,D)8 bytes

The decimal number from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and from 2.22507385858072014E-308 to 1.7976931348623157E+308, where M is the number of digits displayed in the number (25 < = M < = 53), D is the number of digits after the period. M and D are optional. By default, M=16, D=4.

Example: For DOUBLE(5,2), the interval of valid values would be from -999.99 to 999.99.

DOUBLE(M,D) UNSIGNED8 bytesSame as DOUBLE, only does not allow negative values

REAL(M,D)
DOUBLE PRESICION(M,D)

8 bytesSynonym for DOUBLE
DECIMAL(M,D)depends on the number of digitsThe same as DOUBLE, only the number is stored as a string. By default, M=10, D=0.
DECIMAL(M,D) UNSIGNED8 bytesSame as DECIMAL, only does not allow negative values
DEC(M,D)
NUMERIC(M,D)
8 bytesSynonyms for DECIMAL
 

String data types in MySQL

Data TypeSizeDefining
CHAR(Size)Depends on encodingA string of immutable length. Line length (Size) from 0 to 255
CHARACTER(Size)Depends on encodingSynonym for CHAR
BINARY(Size)0-255 bytesCHAR version for storing binary data
VARCHAR(Size)Depends on encodingA variable-length string. Line length (Size) from 0 to 65.532 (0-255 for versions lower than MySQL 5). if the value is greater than 65.532 (255) it will convert to text
CHARACTER VARYING(Size)Depends on encodingSynonym for VARCHAR
VARBINARY(Size)0-65,532 bytesA version of VARCHAR for storing binary data.
TEXTString length + 2 bytesString with a maximum length of 65,535 characters
TINYTEXTString length + 1 byteString with a maximum length of 255 characters
MEDIUMTEXTString length + 3 bytesString with a maximum length of 16,777,215 characters
LONGTEXTString length + 4 bytesString with a maximum length of 4,294,967,295 characters
BLOB 0-65,535 bytesBinary Large OBjects. Can store up to 65,535 bytes of binary data (e.g. pictures). It is also used along with TEXT to store text.
TINYBLOB0-255 bytesBinary Large OBjects. Can store up to 255 bytes of binary data (such as pictures). It is also used along with TINYTEXT to store text.
MEDIUMBLOB0-16,777,215 bytesBinary Large OBjects. Can store up to 16,777,215 bytes of binary data (e.g. pictures). It is also used along with MEDIUMTEXT to store text.
LONGBLOB0-4,294,967,295 bytesBinary Large OBjects. Can store up to 4,294,967,295 bytes of binary data (e.g. pictures). It is also used along with LONGTEXT to store text.
 

Complex data types in MySQL

Data TypeSizeDefining
ENUM(a,b,c,…,n)

1-255 values:
1 byte

256-65,535 values:
2 bytes

List. The maximum number of values in the list is 65,535. A field can take only one value from the list. If the value is incorrect, leaves the field blank.
Example field: Gender ENUM("male", "female").
SET(a,b,c,…,n)

1-8 values:
1 byte

9-16 values:
2 bytes

17-24 values:
3 bytes

25-32 values:
4 bytes

33-64 values:
8 bytes

List. Similar to ENUM, a field can take multiple values from a list. The maximum number of values in the list is 64.
Example of a field: Fruits SET("orange", "apple", "kiwi").
 

Temporary data types in MySQL

Data TypeSizeDefining
DATE3 bytesDate. The format is YYYY-MM-DD. Valid value is from 1000-01-01 to 9999-12-31
DATETIME8 byteDate and time. The format is YYYY-MM-DD HH:MM:SS. Valid value is from 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP4 byteDate and time. Responds to table changes and sets the date and time when the table was last modified. The format is YYYYMMDD HHMMSS. Valid value is from 1970-01-01 00:00:01 to 2038-01-09 03:14:07
TIME3 byteIt's time. The format is HH:MM:SS. Valid value is between 00:00:00 and 23:59:59
YEAR(Size)1 byteYear. The format is YYYY or YY. Valid value is from 1901 to 2155 or 70 to 69 (1970 to 2069)