Before you start creating an SQL table, you must define a database model. Design an ER diagram in which to define entities, attributes and relationships.
Basic concepts
Entities - objects or facts about which information must be stored. For example, an employee of a company or projects implemented by an enterprise. Attributes - a component that describes or qualifies an entity. For example, the entity attribute “employee” is the salary, and the entity attribute “project” is the estimated cost. Connections are associations between two elements. It can be bidirectional. There is also a recursive connection, that is, the connection of an entity with itself.
It is also necessary to determine the keys and conditions under which the integrity of the database is maintained. What does it mean? In other words, constraints that help keep the databases in the right and consistent way.
Transition from ER chart to tabular model
Rules for the transition to the tabular model:
- Convert all entities to tables.
- Convert all attributes to columns, that is, each attribute of the entity should be displayed in the column name of the table.
- Convert unique identifiers to primary keys.
- Convert all communications to foreign keys.
- Create an SQL table.
Base creation
You must first start the MySQL server. To start it, go to the "Start" menu, then to "Programs", then to MySQL and MySQL Server, select MySQL-Command-Line-Client.
To create a database, use the Create Database command. This function has the following format:
CREATE DATABASE database_name .
The restrictions on the name of the database are as follows:
- length is up to 64 characters and may include letters, numbers, characters "" and "";
- a name may begin with a number, but letters must be present.
You need to remember the general rule: any request or command ends with a delimiter. In SQL, it is customary to use a semicolon as a separator.
The server needs to specify which database it will need to work with. There is a USE operator for this. This statement has a simple syntax: USE and database_name.
SQL table creation
So, the model is designed, the database is created, and the server is told exactly how to work with it. Now you can start creating SQL tables. There is a data definition language (DDL). It is used to create MS SQL tables, as well as to define objects and work with their structure. DDL includes a set of commands.
SQL Server table creation
Using just one DDL command, you can create various database objects by varying its parameters. To create an SQL table, use the Create Table command. The tt format is as follows:
CREATE TADLE table_name , (column_name1 data type [DEFAULT expression] [ column_limitation ], column_name2 data type [DEFAULT expression] [ column_limitation ], [ table_limitation ]).
The syntax of the specified command should be described in more detail:
- The table name must be up to 30 characters long and begin with a letter. Only alphabetic characters, letters, as well as the characters "_", "$" and "#" are allowed. Use of the Cyrillic alphabet is allowed. It is important to note that the names of the tables must not coincide with the names of other objects and with the reserved words of the database server, such as Column, Table, Index, etc.
- For each column, you must specify the data type. There is a standard set used by most. For example, Char, Varchar, Number, Date, type Null, etc.
- Using the Default parameter, you can set the default value. This ensures that there are no undefined values in the table. What does it mean? The default value can be a symbol, expression, function. It is important to remember that the type of this data set by default must match the type of input data for the column.
- Constraints on each column are used to implement integrity conditions for data at the table level. There are more nuances. It is forbidden to delete a table if there are other tables dependent on it.
How to work with the database
For the implementation of large projects most often requires the creation of several databases, and each requires many tables. Of course, it is impossible for users to keep all the information in their heads. For this, it is possible to look at the structure of databases and tables in them. There are several teams, namely:
- SHOW DATABASES - shows on the screen all created SQL databases;
- SHOW TABLES - displays a list of all tables for the current database, which are selected by the USE command;
- DESCRIBE table_name - shows a description of all the columns in the table.
- ALTER TABLE - allows you to change the structure of the table.
The last command allows you to:
- Add a column or constraint to the table
- Modify an existing column
- Delete a column or columns
- remove integrity constraints.
The syntax for this command is: ALTER TABLE table_name {[ADD column_name or restrictions ] | [MODIFY name of _changeable_column ] | [DROP name of _ deleted column (s) ] | [DROP deletion_limitation ] | [{ENABLE | DISABLE} CONSTANT restriction_name ] | }.
There are other commands:
- RENAME - rename the table.
- TRUNCATE TABLE - removes all rows from the table. This function may be needed when it is necessary to fill in the table again, and there is no need to store previous data.
There are also situations when the structure of the database has changed and the table should be deleted. There is a DROP command for this. Of course, first you need to select the database from which you want to delete the table, if it differs from the current one.
The command syntax is quite simple: DROP TABLE table_name.
In SQL Access, tables are created and modified using the same commands listed above.
Using CREATE TABLE, you can create an empty table and fill it with data later. But that is not all. You can also immediately create a table from another table. Like this? That is, it is possible to define a table and fill it with data from another table. There is a special keyword AS for this.
The syntax is very simple:
- CREATE TABLE table_name [( column_definition )] AS subquery;
- column_definition - column names, integrity rules for columns of a newly created table, and default values;
- subquery - returns the rows that need to be added to the new table.
Thus, such a command creates a table with specific columns, inserts rows into it that are returned in the query.
Temporary tables
Temporary tables are tables in which data is erased at the end of each session or earlier. They are used to record intermediate values or results. They can be used as worksheets. You can define temporary ones in any session, and you can use their data only in the current session. Creating temporary SQL tables is the same as using the CREATE TABLE command. In order to show the system that the table is temporary, you need to use the GLOBAL TEMPORARY parameter.
The ON COMMIT clause sets the data lifetime in such a table and can perform the following actions:
- DELETE ROWS –clean the temporary table (delete all session data) after each transaction completion. Usually this value is used by default.
- PRESERVE ROWS –Provide data for use in the next transaction. In addition, you can clear the table only after the session ends. But there are features. If a transaction rollback occurs (ROLLBACK), the table will be returned to the state at the end of the previous transaction.
The syntax for creating a temporary table can be represented as follows: CREATE [GLOBAL TEMPORARY] TABLE table_name, ( column_name1 data type [DEFAULT expression] [ column_constraint ], column_name2 data type [DEFAULT expression] [ column_constraint ], [ table_constraint ]).