MySQL Learning (ii) Basic operation of data sheet

Thank you for a cup of herbal tea greatly share, a little borrowed!

First, the basic operation of the data table

　　Data tables and databases and MySQL relationships.

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.

Use test_1;

　　

1. Create a data table

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 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 3, the constraints will be described later

1.1. Create a student table without constraints

CREATE TABLE Student (

ID INT (11),

Name VARCHAR (12),

Age INT (11)

);

　　

Note: SHOW TABLES queries All tables under the database.

1.2. Create a constrained student table

Six constraints: PRIMARY KEY constraint, FOREIGN KEY constraint, non-null constraint, UNIQUE constraint, default constraint, auto increment

1.2.1: PRIMARY KEY constraint

PRIMARY key (PRIMARY key): Unique (unique) and cannot be empty (non-empty), colloquially, 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, add two records in the table, if the ID field with the primary key constraints. The ID cannot be the same and cannot be empty. In general, each table has a field that is the primary key, uniquely identifying the record. You will need to find the record later or you can confirm the record with the primary key, because the primary key is unique and not NULL, and the primary key degree of each record in a table is different, so the corresponding record can be found according to 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.

Single Field PRIMARY KEY constraint

Both ways can be

CREATE TABLE student (CREATE TABLE student (

ID int (one) PRIMARY KEY, id int (11),

Name varchar (), name varchar (12),

Age Int (one) age int (11),

); PRIMARY KEY (id));

Multi-field PRIMARY KEY constraint (composite primary key)

This ID and name City primary key indicate that the ID and name cannot be the same at the same time as the inserted record added later, for example. One record is id=1,name=yyy and the other record is: Id=1,name=zzz. This is possible. Not all of the two fields that you understand are not the same for each other.

CREATE TABLE student (CREATE TABLE student (

ID int (one) PRIMARY KEY, id int (11),

Name varchar (PRIMARY KEY, name varchar (12),

Age Int (one) age int (11),

); PRIMARY KEY (id,name));

　

1.2.2: FOREIGN KEY constraint

What is a foreign key for example is clear, there are two tables, one table is the EMP (employee) table, the other table is Dept (Department) Table, an employee belongs to a department, then how to pass the staff can let ourselves in which department? You can only add a field to the employee table that represents the department where the employee is located, and that field can only be the primary key in the storage dept (because the primary key is unique in order to really be the department that represents the department where the employee is located, 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. If the foreign key is not constrained, then the field cannot guarantee that the value stored in it must be the primary key value of the other table.

Features of FOREIGN KEY constraints:

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

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.

3. The child table must be decorated with a foreign key constraint that is the same as the type of 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.

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

CREATE TABLE TableA
(
ID INT (11),
Name VARCHAR (22),
Location VARCHAR (50),
PRIMARY KEY (ID)
);
CREATE TABLE TableB
(
ID INT (11),
Name VARCHAR () is not NULL,
DeptID INT (11),
PRIMARY KEY (ID),
CONSTRAINT tablea_tableb_1 Foreigh KEY (deptid) REFERENCES TableA (ID)
);

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

1.2.3: non-null constraint

Not NULL. A field that is decorated with the constraint cannot be empty, and the constraint is included in the PRIMARY KEY constraint

CREATE TABLE TableA
(
ID INT (11),
Name VARCHAR () is not NULL,
Location VARCHAR (50),
PRIMARY KEY (ID)
);

1.2.4: Unique Constraint

Unique is a unique constraint decorated field, indicating that the value in the field is unique, cannot have the same value, in layman's terms, like inserting two records, the value of the two record in the field cannot be the same.

CREATE TABLE TableA
(
ID INT (11),
Name VARCHAR (UNIQUE),
Location VARCHAR (50),
PRIMARY KEY (ID)
);

In other words, the name value of each record cannot be the same in the inserted record.

1.2.5: Default constraint

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

　　　　　　　　　　　　　　　　　　　
CREATE Table Table
(
ID INT (one) PRIMARY KEY,
Name VARCHAR () is not NULL,
DeptID INT (one) DEFAULT 1111,
Salary FLOAT
);

1.2.6: Auto Add

Auto_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 self-generated

CREATE Table Table
(
ID INT (one) PRIMARY KEY auto_increment,
Name VARCHAR (a) not NULL
);

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

2. Query table structure

2.1. View the table basic Structure statement

Format 1:describe table name/DESC table name the two functions are the same, abbreviated the word DESCRIBE

DESCRIBE student;

　　　　　　　　　　　　　　　　　　　　　　　　　　

2.2. View the statement that created the table

Format: SHOW CREATE table table name

SHOW CREATE TABLE student;

　　　　　　　　　　　　　　　　　　　　　　　　　　

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

Format: SHOW CREATE table name \g

SHOW CREATE TABLE student\g;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

3. Modify the Data sheet

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

3.1. Modify the table name

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

Change the student table name to Student1 (change it back)

ALTER TABLE student RENAME to Student1;

　　　　　　　　　　　　　　　　　　　　　　　　　　

3.2. Modify the field names in the table

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

Change Name field name in student table to username

ALTER TABLE Student Change name username VARCHAR (30);

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

Explanation: This not only can change the field name, but also the data type of the field can be modified, that is, you can simply modify the field name, you can simply modify the data type, can also be modified together

3.3. Modify the data type in the table

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

ALTER TABLE Student MODIFY username VARCHAR (20);

　　　　　　　　　　　　　　　　　　　　　　　　　　

Explanation: Only the data type 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.4. Modify the position of the field

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 username VARCHAR (a) after age;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　

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 username username VARCHAR (a) after age;

　　　　　　　　　　　　　　　　　　　　　　　　　　　

Summarize

What is the difference between change and modify?

The principle of the city, modify can only modify the data type, but the change can modify the data type and field name, that is, 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.

3.5. Add fields

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 sex VARCHAR (11);

　　　　　　　　　　　　　　　　　　　　　　　　　

3.6. Delete fields

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

ALTER TABLE student DROP sex;

　　　　　　　　　　　　　　　　　　　　　　　　　　

3.7. Delete the foreign KEY 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.

3.8. Change the storage engine of the table

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

This storage engine is not clear to me at the moment, although I know what kinds of engines, but it is not clear that a little bit deeper, so I intend to stay in the future to say.

4. Delete a table

4.1. Delete unrelated tables

Format: DROP table< table name >;

ALTER TABLE student;

　　　　　　　　　　　　　　　　　　　　　　　　　　

4.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, and 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, this can also achieve the purpose, and preserves 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. Summary

Speaking so much, but in practice, not many, especially the table structure of the modification of the operation, in the actual development, the General database table is defined, it will not be modified, found that the database table design is not good, but also to delete all the table, and then re-created the new table. But in the course of our study, these operations are still very important, because the need for these foundations to learn the deeper things behind, it is impossible because the actual use, do not learn that do not learn that, to believe that no matter what to do, it is certainly meaningful things, it may not be meaningful, but will certainly be helpful to us in the future.

MySQL Learning (ii) Basic operation of data sheet