Addition, deletion and modification

SQL1 insert record (I)

In fact, through the output, we can know the value of the inserted data.

insert into exam_record values
(
    null, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90
),
(
    null, 1002, 9002, '2021-09-04 07:01:02', null, null 
);

You can also use interval to add or subtract time.
A separate interval needs to follow the addition and subtraction operator. If date is used_ Add() or date_sub() can be omitted

insert into exam_record
VALUES (null,1001,9001,'2021-09-01 22:11:12',
'2021-09-01 22:11:12' +INTERVAL 50 minute,90),
(null,1002,9002,'2021-09-04 07:01:02',null,NULL);

SQL2 insert record (II)

Problem solution - > link

insert into exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
select uid, exam_id, start_time, submit_time, score
from exam_record
where submit_time < '2021-01-01';

SQL3 insert record (III)

REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');

The functions of replace into and insert into are similar, but the special feature of replace into is
When using replace into to insert data, it will first judge whether there are duplicate elements in the table. If there are duplicate elements, delete the duplicate elements first,
Then insert a new element. If not, insert it directly
The table used by the replace into statement must have a primary key

SQL4 update record (I)

The difference between replace and update
replace modifies the value of an entire record
update can only modify the value of one or some records

Problem solution - > link

update examination_info 
set tag='Python'
where tag='PYTHON'

SQL5 update record (II)

update exam_record
set submit_time='2099-01-01 00:00:00', score=0
where start_time < '2021-09-01' and submit_time is null

SQL6 delete record (I) (timestampdiff calculation time difference)

Problem solution - > link

delete from exam_record
where timestampdiff(minute, start_time, submit_time) < 5
and score < 60;

SQL7 delete record (II)

Similar to the previous question, the operation of sorting and taking the first n bits is added.

delete from exam_record
where 
timestampdiff(minute, start_time, submit_time) < 5
or submit_time is null
order by start_time
limit 3;

SQL8 delete record (III)

Tips:
DROP TABLE, TRUNCATE TABLE, DELETE TABLE . Differences between three deletion statements

1.DROP TABLE
Clearing data and destroying tables is a database definition language (DDL Data Definition Language). It cannot be revoked after execution. The relationships, indexes, permissions, etc. of the deleted tables will be permanently deleted.
2.TRUNCATE TABLE　
Clearing only data and retaining table structure, columns, permissions, indexes, views, relationships, etc. is equivalent to clearing data. It is a database definition language (DDL Data Definition Language), which cannot be revoked after execution.
3.DELETE TABLE　
Deleting data (meeting certain conditions) is a data manipulation language (DML Data Manipulation Language), which can be revoked after execution.
4. Generally, DROP is the fastest and DELETE is the slowest, but DELETE is the safest.

Choose to use truncate table according to the meaning of the question

truncate table exam_record;

Table and index operations

SQL9 creates a new table

Supplementary knowledge:
Problem solution - > link

Fill in the data and type as required, and then pay attention to some small details.

 CREATE TABLE IF NOT EXISTS user_info_vip (
    id int(11) not null primary key auto_increment comment 'Self increasing ID',
    uid int(11) not null unique key comment 'user ID',
    nick_name varchar(64) comment 'nickname',
    achievement int(11) default 0 comment 'Achievement value',
    level int(11) comment 'User level',
    job varchar(32) comment 'Career direction',
    register_time datetime default CURRENT_TIMESTAMP comment 'Registration time'
    )default charset=utf8;

SQL10 modify table

Problem solution - > link

After is added after that column. If after is not added, it will be added last by default;
change modify the column name and type;
modify modifies data types and constraints.

ALTER TABLE user_info ADD school varchar(15) AFTER level;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info modify achievement int DEFAULT 0;

SQL11 delete table

I was careless. I didn't expect it to be so simple.

drop table if exists exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014;

SQL12 create index

Tips on Indexing: link


Problem solution - > link

create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);

SQL13 delete index

Method 1:

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

Method 2:

drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag  on examination_info;

Sql14 truncated average score of SQL type high difficulty test paper

Question making ideas:

1. Connect two tables (from part)
2. Conditional filtering. The label is SQL and the difficulty is hard. (where section)
3. Columns to be extracted, calculation formula (select part)
Truncated average: (sum of scores - Maximum - Minimum) / (total number - 2): (sum(score) - max(score) - min(score)) / (count(score) - 2)
Note that the denominator should be count(score), not count other columns.

select tag, difficulty,
       ROUND((sum(score)-max(score)-min(score)) / (COUNT(score)-2), 1) AS clip_avg_score
from examination_info ei join exam_record er
on ei.exam_id=er.exam_id
where ei.tag='SQL' and difficulty='hard'

SQL15 counts the number of answers

1. Count the total number of answers: directly count exam_id,*,start_ Any time;
2. Number of answers completed, i.e. statistical submit_ If time is not null and score is not null, one of the conditions can be filtered out.
The direct count (submit_time) here is due to the fact that the count function does not make the statistical value null;
3. The number of test papers completed is the same as 2+ duplicate removal.

SELECT 
    COUNT(exam_id) AS total_pv, 
    COUNT(submit_time) AS complete_pv, 
    COUNT(DISTINCT exam_id AND score is not null) AS complete_exam_cnt
FROM exam_record

SQL16 score is not less than the lowest score of the average score

To be continued