View, modify and delete [database] table

Exercise 2 View of table 、 Modification and deletion

One 、 Modification of table structure

1、 by student Add native place column to the table jiguan, data type varchar, The field width is 20 byte , Null values are not allowed .

grammar ：ALTER TABLE table_name ADD Property name   Attribute types  NOT NULL; 

The order is as follows ：

If you change it ：alter table table_name modify Property name Attribute types not null;

2、 rename student Native place column of table “jiguan” by “jg”.

grammar ：ALTER TABLE table_name CHANGE Old attribute name   New property name   Old data types ; 

The order is as follows ;

3、 Delete student In the table jg Field .

grammar ：ALTER TABLE table_name DROP Property name ;

The order is as follows ：

4、 take student In the table sdept Change the field length to 20

grammar ：ALTER TABLE table_name MODIFY Property name data type ;

-- The data type is the modified data type

The order is as follows ：

5、 stay sno The field is Student Table set primary key .

grammar ：ALTER TABLE table_name ADD PRIMARY key  Property name ; 

The order is as follows ：

 

6、 stay Cno The field is Course Table set primary key .

The order is as follows ：

7、 by SC Tabular Sno and Cno Set up foreign keys .

The order is as follows ：

First, let's show three tables ：

Adding foreign keys ：

After adding foreign keys sc surface ：

Field Sno And field Cno Of key In the position MUL The words...

8、 Delete key ( If there is a foreign key , To delete a foreign key first , Delete the primary key )

One 、 How to delete foreign keys ？

Distribute delete foreign keys ：

First step ： Delete the foreign key first ：

alter table table_name drop foreign key Foreign key name ;

The second step ： Then delete the index ：

alter table table_name drop index Foreign key name ;

/* But be careful ：MySQL Direct deletion of foreign keys is not supported ：alter table table_name drop constraint Foreign key name ;*/

I find alter table table_name drop constraint Foreign key name ; and alter table table_name drop foreign key Foreign key name ; The effect is the same

And

------------------------------------ In fact, foreign keys have been deleted at this step ( If you add foreign keys again, you can )

If you want to delete this key completely, you need

alter table table_name drop index Foreign key name ;

And I check sc On the table Key Found no MUL

Some points to note about deleting foreign key constraints

* The foreign key of the slave table is usually the primary key of the master table （ Like here sc Tabular Sno yes student Primary key of 、Cno yes course Primary key of ）

* The data type of the foreign key in the slave table must be consistent with the data type of the primary key in the primary table

* When the master table changes, pay attention to the consistency of the data between the master table and the slave table

• How to delete a primary key ？

alter table table_name drop primary key;// Because the primary key is unique, the deleted object is the table

For primary keys with custom names ：

Add primary key ：alter table table_name add constraint p_pk primary key(Sname);// among p_pk Is the name of the primary key , Just like foreign keys .

Delete primary key ：alter table table_name drop constraint p_pk;

Two 、 Data manipulation

1、 Use insert-SQL Command to enter the following data records into the above table .

Student relations form Student

Sno	Sname	Ssex	Sbirthday	Sdept	Speciality
20180101	Li Yong	male	2000-01-12	CS	Computer application
20180201	Liu Chen	Woman	2001-06-04	IS	Electronic Commerce
20180301	Wang min.	Woman	2002-12-23	MA	mathematics
20180202	Zhang Li	male	2001-08-25	IS	Electronic Commerce

example ：insert into student(sno,sname) values('20180101',' Li Yong ');

The order is as follows ：

Sno	Cno	Degree
20180101	C01	92
20180101	C02	85
20180101	C03	88
20180201	C02	90
20180201	C03	80
20180301	C01	89
20180301	C02	87
20180202	C01	87

Course relationship table Course   League tables SC

Cno	Cname
C01	database
C02	mathematics
C03	The information system
C04	operating system

The order is as follows ：

Course relationship table Course：

League tables ：

At first I found the initial decimal Accuracy is too small , The value entered will be out of bounds

And then I was right Degree Some modifications have been made to the accuracy of ：

You can input normally

2、 Use update The order will SC surface C02 Curriculum Degree Add all field values 5 branch .

The order is as follows ：

3、 Delete SC In the table C02 All records of the course .

The order is as follows ：

4、 see SC All the data in the table .

The order is as follows ：

1. Thinking questions

1、MySQL There are several kinds of database files ？ What are the extensions ？

There are many kinds , Here I found an article blog Introduce database files , Very detailed

MySQL Database files - DB-Engineer - Blog Garden (cnblogs.com)

2、 How to backup and restore the database ？

Teach you how to backup and restore the database _zimao_ The blog of -CSDN Blog _ Database backup and recovery

3、 When defining basic table statements ,NOT NULL What is the function of parameters ？

The value of this property cannot be null

4、 The main code can be established in “ The value can be NULL” On the train ？

Can not be