MySQL (v) DDL (data Definition Language) and six constraints

Objective

In the previous explanation of the database, in fact, a lot of things are very detailed, in the previous learning process I have not noticed. May be encountered in later work, so it is recorded.

Next, I'll share the MySQL DDL used to manipulate the database and the tables.

MySQL holds many databases, and a database can hold many tables .

Add (create table) to the data table (delete table) to change (to change tables) Check (query table structure). Note: Here the action object is a table, the operation of the table is the structure of the table, and the table of the operation of the field (fields and records to be clearly divided)

The premise: The table is under the database, so you have to use which database first .

First, the DDL create 　　 1.1. Create a database

Create DATABASE Db_name[character Set=utf8]

　 1.2. Create a data table

CREATE TABLE Tbl_name [Engine=innodb character Set=utf8];

1) format

CREATE table Data table name (

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

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

Field Name 3 data type [column-level constraint]

); Note: The format does not have to be written so that it can be completely written in one line. But that's very poor writing. I just write it so that I can see it more clearly.

Explanation: 1, [] The contents of the brackets in parentheses can have no, 2, column level This "column" must be clear what is said, a table has a row column, the column represents the vertical, the row represents the horizontal

2) Create a table with no constraints

　　　　　　

3) Query the data sheet

3.1) View table structure

DESCRIBE table name/DESC table name These two functions are the same, abbreviated the word DESCRIBE

　　　　　　　　

3.2) View the statement that created the table

SHOW CREATE table table name;

　　　　　　　　

This shows the format is very bad, can not see clearly, so with the following statement

SHOW CREATE Table Table name \g

　　　　　　　　

second, the DDL alter

Modifying a data table includes adding, deleting, and modifying fields in a table. The keyword used in this is the ALTER

　　 2.1. Modify changes in the data table

1) Modify the table name

Format: ALTER table< old table name > rename[to]< new table name >;

ALTER TABLE student Rename to Student1;

　　　　

2) Modify the fields in the table

Format: ALTER table< table name > change< old field name >< new field name >< new data type >

ALTER TABLE student change name sname varchar (30);

3) Modify the data type in the table

Format: ALTER table< table name > modify< field name >< data type >

ALTER TABLE student Modify sname varchar (50);

Explanation: only the data class of the field name can be modified , but the principle is the same as the above change, there is also the process of modifying the field name, except that the modified field name is the same as the field name before the modification, but the data type is different.

3) Modify the order of the fields

Way 1:alter table< Table name > modify< field 1>< data type > first| after< Field 2>

Explanation: Place the position of field 1 first, or after the specified field 2

ALTER TABLE student Modify sname varchar (in) after address;

Way 2:alter table< Table name > change< field 1>< field 2>< data type > first| after< Field 3>

Explanation: is actually the same, will be field 2 to cover field 1, and then in the sort

ALTER TABLE student change sname sname varchar (in) after address;

What is the difference between change and modify?

The principle is the same,modify can only modify the data type, but the change can modify the data type and field name, that is to say modify is a more specific operation of the change.

May feel that changing only one data type with change is not very good, it adds a direct change to the data type using the keyword modify to operate.

4) Change the engine in the table

Format: Alter table< table name > engine=< changed storage engine name >

2.2, modify the data table to add and delete

1) Add Field

Format: ALTER table< table name > add< new field name >< data type >[constraint][first| after< the existing table name;]

Explanation: Adding a new field at a specific location, if you do not specify a location, is the last one by default.

ALTER TABLE student add age int;

　　　　　　

2) Delete Field

Format: ALTER table< table name > drop< field name >;

　　　　　　

3) Delete the external check constraint for the table

Format: ALTER table< table name > DROP FOREIGN key< foreign KEY constraint name >

Note: A FOREIGN KEY constraint name refers to a field name that is not decorated by a foreign key constraint, remember, but rather the name we take when creating a foreign key constraint relationship.

third, the drop of the DDL 　　 3.1. Delete unrelated tables

Format: DROP table< table name >;

3.2. Delete the main table associated with other tables

This is a more important point, in a foreign key association between the two tables, if the main table is deleted, then it is deleted, and will error. Because there is a table that depends on him. What about that? In this case, there are a total of two ways

1) Delete your child table first, then delete the parent table, so that the purpose of deleting the parent table, but the child table is also deleted

2) First remove the foreign key relationship, and then delete the parent table, so that you can also achieve the purpose, and keep the child table, only delete the parent table we do not need. In 3.7, we explained how to delete a foreign key relationship.

iv. constraints in the data table 　 4.1. PRIMARY KEY constraint

　　　　PRIMARY key(PRIMARY key): unique (unique) and cannot be empty (non-empty), in layman's words, is to add records in the table, the data in the field cannot be duplicated, cannot be empty, such as the table created above as an example, in the table to add two records, If the ID field uses a PRIMARY KEY constraint.

The ID cannot be the same and cannot be empty. Typically, there is a field in each table that is the primary key that uniquely identifies the record. you will need to find this record later or you can confirm the record with this primary key, because the primary key is unique and not empty, and the primary key degree of each record in a table is different.

Therefore, the corresponding records can be found based on the primary key. Instead of multiple duplicate records. If there are no primary keys, there are many duplicate records in the table, which means wasting storage space and consuming more resources when querying.

The field degree that is usually constrained by the primary key is habitually called the field as the primary key of the table.

1) Single field PRIMARY KEY constraint　　　

int primary key, name varchar (+), password varchar (int(+), name varchar (password), varchar (50), primary key (ID));

Example

2) Multi-field constraints

            format one:     int(primary key), name varchar (primary ke) Y, password varchar(x), name varchar (5 0), password varchar (+), primary key (Id,name));            

Example

format one: CREATE TABLE student (IDint( -) primary key, name varchar ( -), Password varchar ( -)); Format two: CREATE TABLE student (IDint( -), name varchar ( -), Password varchar ( -), primary key (ID)); 2) multi-field PRIMARY KEY constraint format one: CREATE TABLE student (IDint( -) primary key, name varchar ( -) primary key, password varchar ( -)); Format two: CREATE TABLE student (IDint( -), name varchar ( -), Password varchar ( -), primary key (Id,name)

4.2, FOREIGN KEY constraints　　

1) Understanding foreign keys

　　　　　　

ab The primary key of the table, then he can be a

For example, there are two tables, one table is the EMP (employee) table, the other is the Dept (Department) Table, and an employee belongs to a department, so how can you let us know what department he is in? You can only add a field to the employee table that represents the department where the employee is located,

that field can only be the primary key in the storage dept (because the primary key is unique, in order to actually be the department that represents the employee's department, and if the department name, some departments may have the same name.) Can not be distinguished. ), a field like this, which conforms to the foreign key's characteristics, allows you to use a FOREIGN key constraint so that the field can only store the primary key of another table.

2) features of external inspection

2.1), FOREIGN KEY constraints can describe any one field (including the primary key), can be empty, and a table can have more than one foreign key. However, the value in the Foreign key field must be a primary key in another table.

2.2), the relationship between the two tables that are associated with a foreign key can be called a parent-child table or a master-slave table. A table that has a foreign key field in a child table (from a table), and the parent table (the primary table) is the table that the foreign key field points to.

2.3), the child table must be decorated with a foreign key constraint the same field as the parent table's primary key field.

Note: A table has a foreign key-decorated field that says the table has a foreign key (is a "foreign key"). Instead of "is a foreign key"), and will give the table a foreign key constraint to take a name, so we often say that the table has no foreign key, refers to the foreign key constraint is not decorated by the name of the field, but the table whether there is a foreign key constraint.

In other words, it is wrong to say that the foreign key of this table is xxx (the field name that is modified by the FOREIGN KEY constraint in the table), but most people are accustomed to this, although the impact is not great, but in a lot of times need to understand a thing, it will cause some trouble.

3) Create a foreign key

Format: CONSTRAINT foreign key name FOREIGN key (field name by foreign KEY constraint) REFERENCES primary table name (primary key field)

English explanation: CONSTRAINT: Constraint REFERENCES: Reference

  11   22 50  ), PRIMARY KEY (id)); CREATE TABLE TableB (id INT ( 11  ), name VARCHAR ( 22  ) not N       　　　　　　　　　　　　　　　    ULL, DeptID INT ( 11  ), PRIMARY key (ID), CONSTRAINT tablea_tableb_1 foreigh key (deptid) REFERENCES tab    LeA (ID)); 
 

Example

Explanation: TableB has a foreign key named tablea_tableb_1 associated with TableA and TableB two tables, a field that is decorated with a foreign key constraint TableB in DeptID, and a primary key field in TableA ID

4.3, non-null constraints　

Not NULL: The field that is modified by the constraint cannot be empty, and the constraint is included in the PRIMARY KEY constraint

int primary key, name varchar (null, password varchar ());

Example4.4. Unique constraint　

Unique: A field that has been modified by a unique constraint, indicating that the value in the field is unique, cannot have the same value, and, in layman's terms, it is like inserting two records in which the value of the field in the two record cannot be the same.

int primary key, name varchar (unique), password varchar (30< /c12>));

Example

Indicates that all values inserted into the student table are not the same

4.5. Default Constraints

Default: Specify how much this column defaults to, for example, male classmates more, gender can be set as the default male, if you insert a row of records, the gender is not filled, then the default plus male

int primary key, name varchar (+), password varchar (def Ault123;

Example4.6, automatic increase

Atuo_increment: A table can only be one field using Auto_increment, and the field using this constraint can only be an integer type (any integer type Tinyint,smallin,int,bigint), the default value is 1, That is to say, increased from 1 onwards.

The general is to use the primary key, automatically increase, so that the value of each primary key is different, and do not have our own management, let the primary key itself automatically generated.

int (primary key auto_increment, name varchar (), password varchar (
   
    30
    default
    123;
   

Example

　　

Feel good friends to order a "recommended" Oh!

MySQL (v) DDL (data Definition Language) and six constraints