MySQL self-study

MySQL self-study
Create a data table 1. Create a data table syntax

 

A data table belongs to a database. Before creating a data table, USE "USE <Database Name>" to specify the database in which the operation is performed. If no database is selected, the "No database selected" error will occur.

The statement for creating a data table is:

Create table,

Syntax Rules:

Create table <indicates>

(

Field 1 Data Type [column-level constraints] [default value],

Field 2 Data Type [column-level constraints] [default value],

......

[Table-level constraints]

);

When using create table to CREATE a TABLE, you must specify the following information:

(1) The name of the table to be created. It is case-insensitive and cannot use keywords in SQL, such as DROP, ALTER, and INSERT.

(2) when creating the names and Data Types of each column in the table, use commas (,) to create multiple columns.

 

Example:

Create the employee table "employee.

First, select the database for creating the table:

USE study

SQL statement used to create an employee table:

Create table employee

(

Id INT (11 ),

Name VARCHAR (25 ),

DeptId INT (11 ),

Salary FLOAT,

);

 

Use show tables to query whether the data table is created successfully.

 

2. Primary Key constraints

A primary key, also known as the primary code, is a combination of one or more columns in a table. The progressive constraint requires that the data of the primary key column be unique and cannot be blank. A primary key uniquely represents a piece of data in a table. It can be used with foreign keys to define the relationship between different data tables and accelerate data query. There are two types of primary keys: Single-field primary keys and multi-field primary keys.

 

Single-field primary key

A single-field primary key is composed of one field. The SQL statement format is divided into the following two situations:

(1) Specify the primary key while defining the column. The syntax rules are as follows:

 

Field name data type primary key [default value]

Example:

Define the data table employee2 with the primary key id

Create table employee2

(

Id INT (11) primary key,

Name VARCHAR (25 ),

DeptId INT (11 ),

Salary FLOAT

);

(2) Specify the primary key after defining all columns

 

[CONSTRAINT <CONSTRAINT name>] primary key (field name)

 

Example: define the data table employee3 with the primary key id. The SQL statement is as follows:

Create table employee3

(

Id INT (11 ),

Name VARCHAR (25 ),

DeptId INT (11 ),

Salary FLOAT,

Primary key (id)

);

 

 

Multi-field joint primary key

A multi-field joint primary key is composed of multiple fields. The syntax rules are as follows:

Primary key [Field 1, Field 2,... field n]

 

Example:

Define the data table employee4. Assume that the table does not have a primary key id. to uniquely identify an employee, you can combine the name and deptId as the primary key. The SQL statement is as follows:

Create table employee4

(

Name VARCHAR (25 ),

DeptId INT (11 ),

Salary FLOAT,

Primary key (name, deptId)

);

 

3. Use foreign key constraints

A foreign key is used to establish a connection between two tables. It can be one or multiple columns. A table can have one or more foreign keys. The foreign key corresponds to the referential integrity. The foreign key of a table can be null. If it is not null, each foreign key value must be equal to a value of the primary key of another table.

Foreign key:

A foreign key is a field in a table. A foreign key may not be the primary key of the table, but the foreign key should be the primary key of another table. The primary function of the addition is to save the integrity of the data reference. After defining the foreign key, it is not allowed to delete rows with associations in another table. Foreign keys are used to maintain data consistency and integrity. For example, the primary key of the department table is id, and a key deptId is associated with this id in the employee table employee5.

 

Master table (parent table): For two associated tables, the table where the primary key of the related connection field is the master table.

 

Slave table (sub-table): For two associated tables, the table where the foreign key of the associated field is located is the slave table.

 

Syntax format for creating a foreign key:

[CONSTRAINT <foreign key name>] foreign key field name 1 [, field name 2,...]

REFERENCES <primary Table Name> primary key column 1 [, primary key column 2,...]

"Foreign key name" is the name of the defined foreign key constraint. A table cannot have the same foreign key. "field name" indicates the field columns for which the foreign key constraint needs to be added to the sub-table; "Primary table name" refers to the name of the table on which the foreign key of the quilt table depends. "primary key column" indicates the primary key column or column combination defined in the primary table.

Example:

Define the data table emplpyee5 and create a foreign key constraint on the employee 5 Table.

Create a department table department:

Create table department

(

Id INT (11) primary key,

Name VARCHAR (22) not null;

Location VARCHAR (50)

);

 

Define the data table employee5 so that its key deptId is used as the primary key associated with the deparment foreign key:

Create table employee5

(

Id int (11) primary key,

Name varchar (25 ),

DeptId int (11 ),

Salary float,

Constraint foreign_key_emy_dept foreign key (deptId) references department (id)

);

 

After the preceding statement is successfully executed, a foreign key constraint foreign_key_emy_dept is added to the table emplyee5. The deptId with the foreign key name depends on the primary key id of the department of the master table.

 

4. Use non-empty Constraints

 

A non-empty constraint indicates that the field value cannot be blank. For fields that use non-null constraints, if you do not specify a value when adding data, the database system reports an error.

The syntax rules for non-null constraints are as follows:

Field name data type not null

 

Example:

Define the data table employee6. The name of the specified employee cannot be blank:

Create table employee6

(

Id int (11) primary key,

Name varchar (25) not null,

DeptId int (11 ),

Salary float,

Constraint foreign_key_emy_dept2 foreign key (deptId) references department (id)

);

After execution, a name field is created in employee6, and its insert value cannot be blank.

 

5. Use uniqueness constraints

 

The Uniqueness constraint requires that the column be unique and can be null, but only one null value can appear. The Uniqueness constraint ensures that duplicate values are not displayed in one or more columns.

The syntax rules for non-null constraints are as follows:

(1) define the uniqueness constraint after defining the column. The syntax rules are as follows:

 

Field Name Data Type UNIQUE

Example: Create department2. The specified department name must be unique:

Create table department2

(

Id int (11) primary key,

Name varchar (22) unique,

Location varchar (50)

);

 

(2) Specify the uniqueness constraint after defining all columns. The syntax rules are as follows:

 

[CONSTRAINT <CONSTRAINT name>] UNIQUE (<field Name>)

Example:

Define the data table department3. The specified department name is unique.

Create table department3

(

Id int (11) not null,

Name varchar (22 ),

Location varchar (50 ),

Constraint… unique (name)

);

Difference between UNIQUE and primary key: A table can have multiple fields declared as UNIQUE, but only one primary key can be declared. Columns declared as primary key cannot have null values, however, fields declared as UNIQUE can have null values.

 

6. use default Constraints

 

Default constraint specifies the default value of a column. If there are many male students, you can set the gender to the default 'male '. If this field is not assigned a value when a new record is inserted, the system automatically assigns this field a value of 'male '.

Syntax format of the default constraint:

Default Value of field name data type DEFUALT

 

Example:

Define the data table employee7. The default employee Department number is 1111:

Create table employee7

(

Id int (11) primary key,

Name varchar (24) not null,

DeptId int (11) default 1111,

Salary float,

Constraint foreign_key_emp_dept4 foreign key (deptId) references department (id );

);

 

7. Set the attribute values in the table to automatically increase

 

In the database, you often want the system to automatically generate the primary key value of the field each time you insert a new record. You can add the AUTO_INCREMENT keyword to the table's primary key. By default, in MySQL, the initial value of AUTO_INCREMENT is 1. Each new record is added, and the field value is automatically added to 1. only one field in a table can use the AUTO_INCREMENT constraint, and this field must be part of the primary key. Fields bound by auto_increment can be any integer type (TINYINT, SAMLLIN, INT, BIGINTD, etc)

Set automatically added syntax rules:

Field Name Data Type AUTO_INCREMENT

Example:

 

Create table employee8

(

Id int (11) primary key auto_increment,

Name varchar (25) not null,

DeptId int (11)

);