MySQL Self-study chapter (II)

Creating data table 1, creating a syntax format for a data table

The data table belongs to the database, and before you create the data table, you use the usage< database name >to specify which database the operation is in, and if you do not select a database, the Selected"Error

The statement that creates the data table is:

CREATE TABLE,

The syntax rules are:

CREATE TABLE < show >

(

Field 1 data type [ column-level constraint ] [ default ],

Field 2 data type [ column-level constraint ] [ default ],

......

[ table-level constraints ]

);

When creating tables using CREATE TABLE, you must specify the following information:

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

(2) To create multiple columns, use commas when creating the name and data type of each column in the table.

Example:

Create an employee table employee.

First select the database that created the table:

Use study

To create an employee table SQL statement:

CREATE TABLE Employee

(

ID INT (11),

Name VARCHAR (25),

DeptID INT (11),

Salary FLOAT,

);

Use SHOW TABLES; whether the query data table was created successfully.

2. PRIMARY KEY constraint

A primary key, also known as a master code, is a combination of one or more columns in a table. A gradual constraint requires that the data for the primary key column is unique and is not allowed to be empty. A primary key can uniquely represent a single piece of data in a table, can be combined with foreign keys to define relationships between different data tables, and can speed up data queries. There are two types of primary keys: Single Chini and multi-field federated primary keys.

Single Field primary key

The single-field primary key is composed of one, and theSQL statement format is divided into the following two scenarios:

(1) Specify the primary key while defining the column, with the following syntax rules:

Field name data type PRIMARY KEY [ default value ]

Example:

Define data table employee2, whose primary key is ID

CREATE TABLE Employee2

(

ID INT (one) PRIMARY KEY,

Name VARCHAR (25),

DeptID INT (11),

Salary FLOAT

);

(2) Specify a primary key after all columns have been defined

[CONSTRAINT < constraint name ;] PRIMARY KEY ( field name )

Example: Define the data table employee3, whose primary key is the id,sql statement as follows

CREATE TABLE employee3

(

ID INT (11),

Name VARCHAR (25),

DeptID INT (11),

Salary FLOAT,

PRIMARY KEY (ID)

);

Multi-field Federated primary key

A multi-field federated primary key is composed of multiple fields, with the following syntax rules:

PRIMARY KEY [ field 1, field 2,... Field N]

Example:

Define the data table employee4, Assuming the table does not have a primary key ID, in order to uniquely identify an employee, you can put the name and DeptID unite as the primary key, SQL The statements are as follows:

CREATE TABLE employee4

(

Name VARCHAR (25),

DeptID INT (11),

Salary FLOAT,

PRIMARY KEY (Name,deptid)

);

3. Using FOREIGN KEY constraints

Foreign keys are used to establish a connection between two tables, which can be one or more columns. A table can have one or more foreign keys. Foreign keys correspond to referential integrity, a table's foreign keys can be null, and if not NULL, each foreign key value must be equal to a value of the primary key in another table.

FOREIGN key:

A foreign key is a field in a table, and the foreign key may not be the primary key for the table, but the foreign key should be the primary key of the other table. The additional primary function is to preserve the integrity of the data reference, and after the foreign key is defined, it is not allowed to delete rows that have an association relationship in another table. The role of foreign keys is to maintain data consistency, integrity. For example: the primary key for the departmental table Department is the ID, and there is a key in the employee table employee5 deptid with this ID Association.

Primary table (parent table): For two tables with associated relationships, the table that contains the primary key in the related connection field is the primary table

From a table (child table): For two tables with associated relationships, the table that contains the foreign key for the associated field is from the table

To create a syntax format for a foreign key:

[CONSTRAINT < foreign key name ;] FOREIGN key Field name 1 [, field name 2,...]

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

Foreign key name is the name of the foreign KEY constraint defined. A table cannot have the same foreign key; field name denotes the field column for which the child table needs to add a foreign key constraint; "PRIMARY table name" is the name of the table on which the outer key of the quilt depends; "PRIMARY key Column" represents a primary key column or combination of columns defined in the primary table

Example:

Define the data table emplpyee5, and create a foreign KEY constraint on the Employee5 table.

Create a departmental Table department:

CREATE TABLE Department

(

ID INT (one) PRIMARY KEY,

Name VARCHAR () not NULL;

Location VARCHAR (50)

);

Define the data table Employee5, and let its key deptid be associated with the primary key of the deparment as a foreign key :

CREATE TABLE Employee5

(

ID Int (one) primary key,

Name varchar (25),

DeptID Int (11),

Salary float,

Constraint foreign_key_emy_dept foreign KEY (deptid) references Department (ID)

);

After the execution of the above statement succeeds, a FOREIGN key constraint named foreign_key_emy_dept is added to table emplyee5 . The foreign key name is called the deptid, which relies on the primary key ID of the primary table Department

4. Use non-null constraints

A non-null constraint refers to the value of a field that cannot be empty. For fields that use a non-null constraint, the database system will error if the user does not specify a value when adding data.

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

Field name data type not null

Example:

Define the data table employee6, specify that the name of the employee cannot be empty:

CREATE TABLE Employee6

(

ID Int (one) primary key,

Name varchar (+) is 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 insertion value cannot be empty.

5. Use of uniqueness constraints

A uniqueness constraint requires that the column be unique, allowed to be empty, but only one null value can appear. Uniqueness constraints ensure that a column or columns do not have duplicate values.

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

(1) Define a Uniqueness constraint directly after the column is defined. The syntax rules are as follows:

Field name data type UNIQUE

Example: Create a department2To specify a unique department name:

CREATE TABLE Department2

(

ID Int (one) primary key,

Name varchar (unique),

Location varchar (50)

);

(2) Specify a Uniqueness constraint after all columns have been defined, with the following syntax rules:

[constraint< constraint name ;] UNIQUE (< field name >)

Example:

Define the data table department3, specifying the department name unique.

CREATE TABLE Department3

(

ID Int (one) is not NULL,

Name varchar (22),

Location varchar (50),

Constraint sth Unique (name)

);

Unique and PRIMARY key differences: A table can have multiple fields declared as UNIQUE, but only one PRIMARY key a column declared as PRIMARY KEY is not allowed to have a null value, but a field declared as UNIQUE can have a null value.

6. Using Default Constraints

The default constraint specifies the default value for a column. such as male students more, gender can be set as the default ' male '. If you do not assign a value to this field when inserting a new record, the system automatically assigns the field a value of ' male '.

Syntax format for default constraints:

Field name data type defualt Default value

Example:

Define data table Employee7, specify the employee's department number by default to 1111:

CREATE TABLE Employee7

(

ID Int (one) primary key,

Name varchar () is not NULL,

DeptID Int (one) default 1111,

Salary float,

Constraint Foreign_key_emp_dept4 foreign KEY (deptid) references Department (ID);

）；

7, set the value of the property in the table automatically increase

In a database, you often want the system to automatically generate primary key values for a field every time you insert a new record. This can be done by adding the auto_increment keyword to the primary key of the table . By default, in MySQL , the initial value of Auto_increment is 1, with each new record added, the field value is automatically added 1. A table can have only one field using the auto_increment constraint, and the field must be part of the primary key. auto_incre ment constrained fields can be of any integer type (tinyint/samllin/int/bigintd , etc.)

To set the syntax rules for automatic additions:

Field name data type auto_increment

Example:

CREATE TABLE Employee8

(

ID Int (one) primary key auto_increment,

Name varchar (+) is not NULL,

DeptID Int (11)

);

MySQL Self-study chapter (II)