MySQL Learning Note (ii) additions and deletions to the table structure

MySQL has not been updated for nearly one weeks. On the contrary, Linux is learning a lot. Maybe I'm emotionally more interested in Linux. But I'm not tired of MySQL, but once I put my energy into the same thing, it's hard to distract myself from doing anything else.

Recently I have also adjusted my study plan and fitness plan. Used to be every night after work, exercise half an hour to one hours, now is the night early to sleep, basically if there is no special arrangement, 10:30 is definitely going to sleep. Get home from work and take 2-3 hours to learn. As for the fitness, to put in the morning, yesterday test, morning still come up. 5:30 get up, run for half an hour, and even have time to eat breakfast in the morning.

In fact, if you don't work on something that interests you, the day goes by.

This part of MySQL, in fact, usually practiced more familiar. I wanted to skip this chapter and then thought about organizing my knowledge into a series. Even if later encountered a problem, forgotten the knowledge point, also can quickly from these notes to find the answer, quickly recalled.

Solid knowledge system is very important, I hope this good habit can continue to persist.

SQL code and data for testing:

#班级表Create Tableclasses (class_nointAuto_incrementPrimary Key, Class_nameChar( -) not NULL Unique, Department_nameChar( -) not NULL) Engine=InnoDBdefaultCharSet=UTF8; #下面是一些测试数据:Insert  intoClasses (Class_name,department_name)Values    ('English Class II','English Class II'),    ('English class three','English class three'),    ('English class four','English class four'),    ('English class five','English class five'),    ('English class six','English class six'),    ('Math Class One','one math class in freshman year'),    ('Math Class Two','Math Class Two'),    ('Math class Three','Math class Three'),    ('Math class Four','Math class Four in a freshman'),    ('Math class Five','Math class Five'),    ('Math class Six','Math class Six'),    ('Chinese class One','one Chinese class'),    ('Chinese Class II','Chinese Second Class'),    ('Chinese class three','Chinese class three'),    ('Chinese class four','Chinese class four'),    ('Chinese class five','Chinese class five'),    ('Chinese class six','Chinese class six');

1, the table structure of the increase, delete, change, insert

Note: This demo is based on the classes table. In practical applications, you can replace your own table name.

The original table structure is as follows:

View table structure and fields:

DESC classes;

Add Field:

ALTER TABLE classes add testfield1 varchar (TEN) NOT null default ';

To delete a field:

ALTER TABLE classes drop testfield1;

Modify Field Name:

(For ease of operation, re-add the field: ALTER TABLE classes add testfield1 varchar () NOT NULL default ';)

ALTER TABLE classes change TESTFIELD1 test varchar (TEN) NOT null default ';

To modify only the field type:

ALTER TABLE classes Modify test Chat (10);

Modify and change have the same effect. The difference is that change is the name of the replacement field. Modify only changes the field type of the field.

The use of the usual use can be distinguished use.

2. Increase, delete, change and check the constraint on the table.

At work, you often encounter an increase in constraints on a table, or a condition that removes constraints.

To add a constraint:

The syntax is: ALTER TABLE your table name add constraint constraint name constraint type (field name)

ALTER TABLE CLASSES ADD constraint Myunique unique (class_no);

To delete a constraint:

Before we delete the constraints, we need to know clearly that our table already has those constraints.

Can be displayed by the show create table name \g. The \g here means group (which I presume).

Show CREATE TABLE classes \g Note \g No;

To delete a constraint, you also need to know those constraints.

Constraints typically have a PRIMARY KEY constraint (primary key) FOREIGN KEY constraint (foreign key), unique index name (index)

Here is a brief introduction of what is a foreign key constraint, first look at what foreign mean: exotic foreign. This means that the field in this table is from another table, where the value cannot be V and must be derived from another table.

For a more detailed introduction, we look at the following article.

To delete a PRIMARY KEY constraint:

Syntax: ALTER TABLE name drop PRIMARY key.

Chestnuts: ALTER TABLE classes drop PRIMARY key.

To delete a foreign KEY constraint:

A table can have a primary key, but it can have multiple foreign keys. So delete the foreign key when you want to delete the name of the foreign key, that is, the constraint name.

Syntax: Aleter table Name drop FOREIGN KEY constraint name;

To delete a unique constraint:

What is the only constraint: to give a chestnut, for example, our ID card number is not repeated, then in the process of entering the ID card we will prevent mistakes and enter the duplicate ID number. This is the only constraint. Unqiue (distinctive, unique, rare). Of course, the only constraint a table can have multiple, such as the user table can have both a social security number, you can also have a phone number. When we delete, we also delete the constraint name.

Syntax: ALTER TABLE name DROP INDEX constraint name.

ALTER TABLE classes DROP INDEX class_name;

Other options for modifying tables: such as modifying the storage engine type, modifying the character set, and modifying the self-increment initial value.

Modify the storage Engine: ALTER TABLE name engine= new storage engine;

Modify character set: ALTER TABLE name charset= new character set;

Modified Auto-increment: ALTER TABLE name auto_increment= new initial value;

3, modification and deletion of the table name

Modifying table names is simple, but I often use them.

Re-project preparation phase, the design database time. Table name in order to more convenient understanding, so the design should be as human-friendly as possible, so inevitably there is a time to modify the table name.

Modify Table Name:

ALTER TABLE name rename new table name.

Here we distinguish between modifying the table name and modifying the name of the field: ALTER TABLE name change the original field constraint condition of the new field;

Wordy: Change emphasizes that the data structure is changed, and change emphasizes that the name of the table has changed and the data itself does not change. So it can be seen that foreigners are still very strictly forbidden when naming.

Delete Table name:

ALTER TABLE name;

When you delete a table. If there is a foreign key in the table, it will be an error. Why is it? Let's think about it.

Of course, the MyISAM storage engine will not error. Why is that?

MySQL Learning Note (ii) additions and deletions to the table structure