Mysql study notes (2) addition, deletion, modification and query of table structure

Mysql has not been updated for nearly a week. On the contrary, I learned a lot about Linux. Maybe I am more emotionally interested in Linux. But I'm not bothered by mysql, but once I devote my energy to one thing, it's difficult to divert my energy to other things.

Recently, I have also adjusted my study plan and fitness plan. I used to exercise for half an hour to an hour every night after get off work, but now I go to bed early at night. Basically, if there are no special arrangements, I will definitely fall asleep at 10:30. Get home from get off work and take two to three hours to study. As for fitness, it should be done in the morning. I tried it yesterday and I can still get up in the morning. Get up at 5:30, run for half an hour, and even have time to go to the morning market for breakfast.

In fact, if you don’t do something that interests you after get off work, the day will just go by.

This part of mysql is actually familiar to me from daily practice. I originally wanted to skip this chapter, but then I thought of organizing my knowledge into a series. Even if you encounter problems in the future and forget the knowledge points, you can quickly find the answers from these notes and recall them quickly.

A solid knowledge system is very important. I hope I can stick to this good habit.

SQL code and data used for testing:

#班级表 create table classes(
    class_no int auto_increment primary key,
    class_name char(20) not null unique,
    department_name char(20) not null )engine=innodb default charset=utf8;

#下面是一些测试数据： insert into classes(class_name,department_name) values ('英语二班','高一英语二班'),
    ('英语三班','高一英语三班'),
    ('英语四班','高一英语四班'),
    ('英语五班','高一英语五班'),
    ('英语六班','高一英语六班'),
    ('数学一班','高一数学一班'),
    ('数学二班','高一数学二班'),
    ('数学三班','高一数学三班'),
    ('数学四班','高一数学四班'),
    ('数学五班','高一数学五班'),
    ('数学六班','高一数学六班'),
    ('语文一班','高一语文一班'),
    ('语文二班','高一语文二班'),
    ('语文三班','高一语文三班'),
    ('语文四班','高一语文四班'),
    ('语文五班','高一语文五班'),
    ('语文六班','高一语文六班');

1. Add, delete, modify and insert table structure

Note: The demonstration here is based on the classes table. In actual applications, you can replace it with your own table name.如 The original table structure is shown below:

View table structure and field:

desc classes;

increase field:

alter table classes add test1 varchar (10) not null default '';

Delete field:

                                                            use using use with using use using         through   through out through out through through off off ‐‐ ‐ ‐‐‐‐‐ alter table classes to drop testfield1;

　　Classes Change Testfield1 Test Varchar (10) not null default '';

Only modify the field type:

Alter Table Classes Modify Test Chat (10);

modify and Change are similar. The difference is that change is the name of the field to be replaced. Modify only changes the field type of the field.

You can use it differently when using it.

2. Add, delete, modify and check table constraints.

In work, we often encounter the situation of adding constraints to the table or deleting constraints.

Add constraints:

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);

delete constraints ：

Before deleting constraints, we must clearly know which constraints already exist in our table.

It can be displayed by show create table table name G. The G here means group (my subjective guess).

Show create table classes G Note that there is no ; sign after G.

To delete constraints, you also need to know what constraints there are.

Constraints generally include primary key constraints (primary key), foreign key constraints (foreign key), and unique index names (index)

Here we will briefly introduce what foreign key constraints are, and first look at what foreign means: foreign foreign. That is to say, the source of this field in this table is from another table, and the value in it cannot be created out of five, but must come from another table.

For a more detailed introduction, let’s look at the following article.

Delete primary key constraint:

Syntax: alter table table name drop primary key.

Chestnut: alter table classes drop primary key.

Delete foreign key constraint:

A table can have one primary key, but there can be more than one. foreign key. Therefore, when deleting a foreign key, you must delete the name given to the foreign key at that time, that is, the constraint name.

Syntax: aleter table table name drop foreign key constraint name;

delete unique constraint:

What is the unique constraint: For example, for example, our ID card numbers will not be repeated, so during the process of entering the ID card, we will prevent the entry of duplicate ID numbers due to mistakes. This is the only constraint. unqiue (unique, unique, rare). Of course, a table can also have multiple unique constraints. For example, the user table can have both ID number and phone number. Then when we delete, we must also delete the constraint name.

Syntax: alter table table name drop index constraint name.

Alter table classes drop index class_name;

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

     Modify the storage engine:     alter table table name engine = new storage engine;

                                                           .                                                                   out out out out out out out of out out of Value;

3. Modification and deletion of table names

Although it is very simple to modify the table name, I often use it.

In the project preparation stage and when designing the database. In order to make the table name easier to understand, the design should be as user-friendly as possible, so it is inevitable that the table name will be modified.

Modify table name:

Alter table Table name rename New table name.改 Here we distinguish the difference between modifying table names and modification field names: Alter Table Table Name Change's original field new field constraints;

啰 嗦 嗦: Change emphasizes that the data structure changes, and Change emphasizes that the name of the table occurs. If the data is changed, the data itself does not change. Therefore, it can be seen that naming by foreigners is still strictly prohibited.

Delete table name:

Alter table table name;

When deleting the table. If there is a foreign key in the table, an error will be reported. why? Think about it, everyone.

Of course, deleting the table will not report an error to the myisam storage engine. This is why?

The above is the content of Mysql study notes (2) on adding, deleting, modifying and checking the table structure. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!