38.sql-dml.md

DML in Detail

Continuing with the school course-selection system database created in the previous lesson, we will explain how to use DML. DML can help us insert data into two-dimensional tables (insert operation), delete data from two-dimensional tables (delete operation), and update data in two-dimensional tables (update operation). Before running DML, let us first switch to the school database with the use command below.

USE `school`;

insert Operation

As the name suggests, insert is used to insert rows into a two-dimensional table. The ways of inserting include inserting a complete row, inserting part of a row, inserting multiple rows, and inserting query results. We use the SQL below to add one college into the college table.

INSERT INTO `tb_college` 
VALUES
    (DEFAULT, 'College of Computer Science', 'A place to study computer science and technology');

Because the primary key of the college table is an auto-increment field, default in the SQL above means this column uses the default value. We can also use the following way to do the same operation.

INSERT INTO `tb_college` (`col_name`, `col_intro`) 
VALUES 
    ('College of Computer Science', 'A place to study computer science and technology');

We recommend the way below: specify which fields will get values. In this way, you do not need to assign values according to the field order set when the table was created. You can assign values according to the field order given in the tuple before values. But you need to note that except for fields that allow null and fields that have default values, all other fields must be listed one by one and must be assigned values in the tuple after values. If you want to insert multiple records at one time, we can put multiple tuples after values to do batch insert, as shown below.

INSERT INTO `tb_college` 
    (`col_name`, `col_intro`) 
VALUES 
    ('College of Foreign Languages', 'A college for studying the languages of foreigners'),
    ('College of Economics and Management', 'Manage the world and benefit the people, govern the country; management science, the way to make the country prosperous'),
    ('College of Physical Education', 'Develop sports and improve physical fitness');

When inserting data, note that the primary key cannot be repeated. If the inserted data has the same primary key as an existing record in the table, the insert operation will produce a Duplicate Entry error. Let me remind everyone again: if an insert operation leaves out some columns, then these columns must either have default values or allow null, otherwise an error will also happen. In business systems, to make the insert operation not affect the performance of other operations, mainly the select operation we will talk about later, you can add low_priority between insert and into to lower the priority of the insert operation. This way also applies to the delete and update operations introduced below.

Suppose there is a table named tb_temp, and it has two columns, a and b, which save the college name and college introduction. We can also get the data of the tb_temp table through a query operation and insert it into the college table, as shown below. The select here is the DQL we mentioned before, and it will be explained in detail in the next lesson.

INSERT INTO `tb_college`
    (`col_name`, `col_intro`)
SELECT `a`, `b` 
  FROM `tb_temp`;

delete Operation

If you need to delete data from a table, you can use the delete operation. It can help us delete specified rows or all rows. For example, if we want to delete the college with ID 1, we can use the SQL shown below.

DELETE
  FROM `tb_college`
 WHERE col_id=1;

Note that the where clause in the delete operation above is used to specify the condition. Only rows that satisfy the condition will be deleted. If we accidentally write the SQL below, all records in the college table will be deleted. This is very dangerous, and in actual work we usually would not do this either.

DELETE
  FROM `tb_college`;

It should be explained that even if all data is deleted, the delete operation will not delete the table itself, and it will not make the value of the AUTO_INCREMENT field go back to the initial value. If you need to delete all data and also make the AUTO_INCREMENT field return to the initial value, you can use truncate table to do a table-truncation operation. The nature of truncate is deleting the original table and creating a table again. Its speed is actually faster, because it does not need to delete data row by row. But please remember one thing: using truncate table to delete data is very dangerous, because it will delete all data, and because the original table has already been deleted, recovering wrongly deleted data becomes extremely difficult.

update Operation

If you want to modify the data in a table, you can use the update operation. It can be used to modify specified rows or all rows. For example, we change "Yang Guo" in the student table to "Yang Xiao". Here we assume the student ID of "Yang Guo" is 1001. The code is shown below.

UPDATE `tb_student`
   SET `stu_name`='Yang Xiao'
 WHERE `stu_id`=1001;

Note the where clause in the SQL above. We use the student ID as the condition to find the corresponding student, and then use the assignment operation before it to change the name to "Yang Xiao". Why do we not directly use the name as the filter condition here? That is because there may be more than one student named "Yang Guo" in the student table. If stu_name is used as the filter condition, then our update operation may update multiple rows of data at one time. Obviously this is not what we want to see. Another point to note is the set keyword in the update operation, because in SQL = does not mean assignment, but the operator for testing equality. Only when it appears after the set keyword does = have the ability to assign a value.

If you want to modify the student's name and birthday at the same time, you can make a small change to the update statement above, as shown below.

UPDATE `tb_student`
   SET `stu_name`='Yang Xiao'
     , `stu_birth`='1975-12-29'
 WHERE `stu_id`=1001;

The update statement can also use a query to get data, and then use it to update the specified table data. Interested readers can study that by themselves. When writing an update statement, there is usually a where clause, because in actual work, operations that update the whole table are almost never used. Everyone must pay attention to this point.

Complete Data

Below we give the complete SQL for inserting data into the five tables of the school database.

USE `school`;

-- Insert college data
INSERT INTO `tb_college` 
    (`col_name`, `col_intro`) 
VALUES 
    ('College of Computer Science', 'The College of Computer Science set up the computer major in 1958, established the Department of Computer Science in 1981, and established the College of Computer Science in 1998. In May 2005, in order to further integrate teaching and research resources, the university decided that the College of Computer Science and the School of Software would merge their administrative teams and operate in a unified way, while teaching and student management would run independently. The college has three departments: the Department of Computer Science and Technology, the Department of Internet of Things Engineering, and the Department of Computational Finance; two research institutes: the Institute of Image and Graphics and the Institute of Cyberspace Security, which was established in 2015; and three teaching experiment centers: the Computer Foundation Teaching Experiment Center, the IBM Technology Center, and the Computer Major Experiment Center.'),
    ('College of Foreign Languages', 'The College of Foreign Languages has 7 teaching units and 6 undergraduate majors that admit both arts and science students; it has 1 first-level discipline doctoral degree authorization point, 3 second-level discipline doctoral degree authorization points, 5 first-level discipline master degree authorization points, 5 second-level discipline master degree authorization points, and 5 professional master authorization fields. At the same time, it also has 2 Master of Translation and Interpreting professional degree programs. It has more than 210 faculty and staff members, among whom there are more than 80 professors and associate professors. Among the teachers, the proportion of full-time teachers who have received doctoral degrees from famous universities in China and abroad, or who are studying for doctoral degrees while on the job, is more than 60 percent.'),
    ('College of Economics and Management', 'The predecessor of the College of Economics was the Economics Department founded in 1905. The late economists Peng Dixian, Zhang Yujiu, Jiang Xuemo, Hu Jichuang, Tao Dayong, Hu Daiguang, as well as contemporary scholar Liu Shibai and others, once taught or studied here.');

-- Insert student data
INSERT INTO `tb_student` 
    (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`) 
VALUES
    (1001, 'Yang Guo', 1, '1990-3-4', 'Changsha, Hunan', 1),
    (1002, 'Ren Woxing', 1, '1992-2-2', 'Changsha, Hunan', 1),
    (1033, 'Wang Yuyan', 0, '1989-12-3', 'Chengdu, Sichuan', 1),
    (1572, 'Yue Buqun', 1, '1993-7-19', 'Xianyang, Shaanxi', 1),
    (1378, 'Ji Yanran', 0, '1995-8-12', 'Mianyang, Sichuan', 1),
    (1954, 'Lin Pingzhi', 1, '1994-9-20', 'Putian, Fujian', 1),
    (2035, 'Dongfang Bubai', 1, '1988-6-30', NULL, 2),
    (3011, 'Lin Zhennan', 1, '1985-12-12', 'Putian, Fujian', 3),
    (3755, 'Xiang Shaolong', 1, '1993-1-25', 'Chengdu, Sichuan', 3),
    (3923, 'Yang Buhui', 0, '1985-4-17', 'Chengdu, Sichuan', 3);

-- Insert teacher data
INSERT INTO `tb_teacher` 
    (`tea_id`, `tea_name`, `tea_title`, `col_id`) 
VALUES 
    (1122, 'Zhang Sanfeng', 'Professor', 1),
    (1133, 'Song Yuanqiao', 'Associate Professor', 1),
    (1144, 'Yang Xiao', 'Associate Professor', 1),
    (2255, 'Fan Yao', 'Associate Professor', 2),
    (3366, 'Wei Yixiao', DEFAULT, 3);

-- Insert course data
INSERT INTO `tb_course` 
    (`cou_id`, `cou_name`, `cou_credit`, `tea_id`) 
VALUES 
    (1111, 'Python Programming', 3, 1122),
    (2222, 'Web Frontend Development', 2, 1122),
    (3333, 'Operating Systems', 4, 1122),
    (4444, 'Computer Networks', 2, 1133),
    (5555, 'Compiler Principles', 4, 1144),
    (6666, 'Algorithms and Data Structures', 3, 1144),
    (7777, 'Business French', 3, 2255),
    (8888, 'Cost Accounting', 2, 3366),
    (9999, 'Auditing', 3, 3366);

-- Insert course-selection data
INSERT INTO `tb_record` 
    (`stu_id`, `cou_id`, `sel_date`, `score`) 
VALUES 
    (1001, 1111, '2017-09-01', 95),
    (1001, 2222, '2017-09-01', 87.5),
    (1001, 3333, '2017-09-01', 100),
    (1001, 4444, '2018-09-03', NULL),
    (1001, 6666, '2017-09-02', 100),
    (1002, 1111, '2017-09-03', 65),
    (1002, 5555, '2017-09-01', 42),
    (1033, 1111, '2017-09-03', 92.5),
    (1033, 4444, '2017-09-01', 78),
    (1033, 5555, '2017-09-01', 82.5),
    (1572, 1111, '2017-09-02', 78),
    (1378, 1111, '2017-09-05', 82),
    (1378, 7777, '2017-09-02', 65.5),
    (2035, 7777, '2018-09-03', 88),
    (2035, 9999, '2019-09-02', NULL),
    (3755, 1111, '2019-09-02', NULL),
    (3755, 8888, '2019-09-02', NULL),
    (3755, 9999, '2017-09-01', 92);

Note: The insert statements above use batch processing to insert data. This way is more efficient.