MySQL's select,insert,delete,update

Write in front

The previous article learned to create a database and data table, this article will learn to add additions and deletions to the data table operation.

Series Articles

MySQL CREATE database, create data table

An example

In the previous article, we created a database and a data table, and there was no data in the data table, so we tested the data for three tables.

Note: To avoid field names or to indicate duplicates of some of the system's keywords, you can use the ' wrap string ' similar to [] in SQL Server. "The key to the left of the numeric key above the keyboard (English input method)

1, add four classes of information

 UseSchool;--Add class InformationInsert  intoTb_class (' name ')Values('Letter Tube');Insert  intoTb_class (' name ')Values('Letter Tube');Insert  intoTb_class (' name ')Values('Letter Tube');Insert  intoTb_class (' name ')Values('Xin Guan');

2. Add Student Information

 UseSchool;--Add student InformationInsert  intoTb_student (' Name ', ' phone ', ' age ', ' gender ', ' classid ')Values('Zhang San','13810707322', -,1,1);Insert  intoTb_student (' Name ', ' phone ', ' age ', ' gender ', ' classid ')Values('John Doe','13810707324', +,1,2);--Bulk AddInsert  intotb_student (' Name ', ' phone ', ' age ', ' gender ', ' classid ')Values('Wang er','13810707325', -,1,2),('Leper','13810707323', at,1,1),('Zhang Sanfeng','13810707321', A,1,3),('Zhang Mowgli','13810707326', +,1,1),('Gohan Rice','13810707328', -,1,1),('Monkey King','13810707327', at,1,4),('Naruto','13810707329', -,1,1),('Lu Fei','13810707320', -,1,2);

3. Add Student Achievement Information

--Add student scoresInsert  intoTb_score (' Course ', ' score ', ' Stuid ')Values('High number', the,1),('Computer', the,1),('Java', the,1),('. NET', the,1);

Well, there are data, you can learn to query, delete, modify and other operations.
4. Check all student Information

1  Use School; 2 -- Query all student information 3 Select *  from tb_student; 4 -- is equivalent, but when the amount of data is very large, it is recommended to use the following query method.  5Select from Tb_student;

Result set

You will find that in the previous article, the default constraints have been added for CreateDate, but the results are not shown here. The original field name was date and was later changed to CreateDate. The default constraint is not persisted.

Alter Table datetime default now ();

Test, add a piece of data

Insert  into Values (' brother Ming ','13810707322',1, 1);

Results

5. Check all student information of the ' 03 ' class.

--Enquiry information for students in class 03SelectS.id'numbering', S. ' Name ''name', S. ' Phone ''Telephone', S.age'Age', S.gender'Sex', S.createdate as 'Admission Time', c. ' Name ' as 'class name'  fromTb_student sInner JoinTb_class C onS.classid=C.id;

As you can see from the SQL statement above, you can alias a field name, pass as or write aliases directly, which is similar to SQL Server. The use of inner joins is similar.
Results

The result above looks messy and can be sorted in ascending order by ID.

 UseSchool;--Enquiry information for students in class 03SelectS.id'numbering', S. ' Name ''name', S. ' Phone ''Telephone', S.age'Age', S.gender'Sex', S.createdate as 'Admission Time', c. ' Name ' as 'class name'  fromTb_student sInner JoinTb_class C onS.classid=C.idOrder  byS.id;

6, take the first 3 for the students information.

Selects.id'numbering', S. ' Name ''name', S. ' Phone ''Telephone', S.age'Age', S.gender'Sex', S.createdate as 'Admission Time', c. ' Name ' as 'class name'  fromtb_student SInner JoinTb_class C onS.classid=c.idOrder  bys.id Limit3;

Note: Take the first few data, this is different from SQL Server usage, the first few data in SQL Server uses top, and MySQL uses limit.

7, delete id=1 student information.

 Use School; -- Delete id=1 student information.  Deletefromwhere id=1;

If you delete it directly, MySQL will report an error.
Error code:1451. Cannot delete or update a parent ROW:A FOREIGN KEY constraint fails (' School '. ' Tb_score ', constraint ' fk_stuid ' foreign K EY (' stuid ') REFERENCES ' tb_student ' (' ID '))

This sentence can be added first and then deleted.

 Use School; -- Delete id=1 student information.  Set=0; Delete  from where id=1;

Note: Set foreign_key_checks = 0; cancel foreign key detection. Otherwise, MySQL will consider the deletion to be non-secure.
9. Update all student information at the time of enrollment and set the enrollment time to the current time.

 Use School; -- Update all student information at the time of enrollment and set the current time for enrollment SET = 0  updateset createdate=whereisnull(createdate); Select *  from Tb_student;

When using MySQL to perform an update, if you do not use the primary key when the where statement, the following error is reported, using the primary key for the where statement is normal.

Error code:1175. You is using Safe update mode and you tried to update a table without a WHERE is uses a KEY column to disable safe mode , toggle the option in Preferences, SQL Editor and reconnect.

Note: When modifying, you need to add set sql_safe_updates = 0 and cancel the security update mode. If you want to improve the database security level, you can restore the original settings, execute the command: set sql_safe_updates = 1;

Executes the above statement and executes successfully.

Summarize

Well, MySQL used in the addition and deletion of the change to summarize here, if there is SQL Server database base, learning MySQL is very simple. The following article describes the use of order By,group by, and so on.

MySQL's select,insert,delete,update