Detailed introduction to SQL addition, deletion and modification operations
This article brings you relevant knowledge about SQL, which mainly organizes issues related to addition, deletion and modification operations, including inserting records, updating records, deleting records, etc. The following is Let's take a look, hope it helps everyone.
SQL Tutorial"
Insert records
题目描述 牛客后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下： 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分； 用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。 试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。 该题最后会通过执行SELECT uid, exam_id, start_time, submit_time, score FROM exam_record; 来对比结果 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; TRUNCATE exam_record; 答案 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);
Table exam_record structure
题目描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录， 由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。 我们已经创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录， 结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。 后台会通过执行"SELECT * FROM exam_record_before_2021;"语句来对比结果 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE IF NOT EXISTS exam_record_before_2021 ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; TRUNCATE exam_record; TRUNCATE exam_record_before_2021; INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-01 09:00:01', null, null), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 70), (1001, 9002, '2020-09-02 09:00:01', null, null), (1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81), (1002, 9002, '2021-09-02 12:01:01', null, null); 答案 INSERT INTO exam_record_before_2021 SELECT NULL,uid, exam_id, start_time, submit_time, score FROM exam_record WHERE submit_time <p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/c4d8aa2b02d19a84beed2f83cf899099-4.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"></p>## SQL3 insert record (3)<h4> <strong></strong>Examination information table examination_info Structure</h4><p><br></p><pre class="brush:php;toolbar:false"> 题目描述 现在有一套ID为9003的高难度SQL试卷，时长为一个半小时， 请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info，不管该ID试卷是否存在，都要插入成功，请尝试插入它。 后台会通过执行 SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info 语句来对比结果。 建表语句 drop table if EXISTS examination_info; CREATE TABLE IF NOT EXISTS examination_info ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', exam_id int UNIQUE NOT NULL COMMENT '试卷ID', tag varchar(32) COMMENT '类别标签', difficulty varchar(8) COMMENT '难度', duration int NOT NULL COMMENT '时长(分钟数)', release_time datetime COMMENT '发布时间' )CHARACTER SET utf8 COLLATE utf8_bin; TRUNCATE examination_info; INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, '算法', 'easy', 60, '2020-01-01 10:00:00'), (9003, 'SQL', 'medium', 60, '2020-01-02 10:00:00'), (9004, '算法', 'hard', 80, '2020-01-01 10:00:00'); 答案 replace into examination_info (id,exam_id,tag,difficulty,duration,release_time) values(null,9003,'SQL','hard',90,'2021-01-01 00:00:00');
SQL4 Update record (1)
There is currently a test paper information table examination_info. The table structure is as shown below:
题目描述 请把examination_info表中tag为PYTHON的tag字段全部修改为Python。 后台会通过执行'SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info;'语句来对比结果。 建表语句 drop table if EXISTS examination_info; CREATE TABLE IF NOT EXISTS examination_info ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', exam_id int UNIQUE NOT NULL COMMENT '试卷ID', tag varchar(32) COMMENT '类别标签', difficulty varchar(8) COMMENT '难度', duration int NOT NULL COMMENT '时长', release_time datetime COMMENT '发布时间' )CHARACTER SET utf8 COLLATE utf8_bin; TRUNCATE examination_info; INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'python', 'easy', 60, '2020-01-01 10:00:00'), (9003, 'Python', 'medium', 80, '2020-01-01 10:00:00'), (9004, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00'); 答案 UPDATE examination_info set tag ='Python' WHERE tag='PYTHON';
## SQL5 update record ( 2)
Answer record table exam_record table structure
题目描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录,submit_time为 完成时间 请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成， 即：将完成时间改为'2099-01-01 00:00:00'，分数改为0。 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80), (1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90), (1002, 9001, '2021-08-02 19:01:01', null, null), (1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1003, 9001, '2021-09-02 12:01:01', null, null), (1003, 9002, '2021-09-01 12:01:01', null, null); 答案 UPDATE exam_record set submit_time='2099-01-01 00:00:00', score=0 WHERE start_time<br><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/fcb39cc7e5c93da4ccf4ba247c325d14-9.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/fcb39cc7e5c93da4ccf4ba247c325d14-10.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations">3 Delete record</p><h3> <strong></strong> SQL6 Delete records (1) </h3><h4 id="strong-Answer-record-table-exam-record-table-structure-start-time-is-the-start-time-of-the-test-paper-submit-time-is-the-hand-in-time-that-is-the-end-time-strong"><strong> Answer record table exam_record table structure, start_time is the start time of the test paper, submit_time is the hand-in time, that is, the end time</strong></h4><p></p><pre class="brush:php;toolbar:false"> 题目描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录， start_time是试卷开始时间 submit_time 是交卷，即结束时间 请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录； 后台会执行您的SQL，然后通过 SELECT * FROM exam_record; 语句来筛选出剩下的数据，与正确数据进行对比。 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; TRUNCATE exam_record; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58), (1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60), (1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54), (1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49), (1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70), (1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80), (1003, 9002, '2021-09-09 07:01:02', null, null); 答案 delete from exam_record where timestampdiff(minute,start_time,submit_time) <br><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/5b7c4e7ac7883ee97235b35a9f62dab9-11.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"><p> SQL7 Delete Records (2)<img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/5b7c4e7ac7883ee97235b35a9f62dab9-12.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"></p> The answer record table exam_record has the following structure: <h4></h4><p></p><pre class="brush:php;toolbar:false"> 题目描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，start_time是试卷开始时间 submit_time 是交卷时间，即结束时间，如果未完成的话，则为空 请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。 后台会通过 SELECT * FROM exam_record 语句来对比结果。 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; TRUNCATE exam_record; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58), (1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60), (1002, 9002, '2021-06-02 19:01:01', null, null), (1003, 9001, '2021-09-05 19:01:01', null, null), (1003, 9001, '2021-09-05 19:01:01', null, null), (1003, 9002, '2021-09-09 07:01:02', null, null); 答案 delete from exam_record where timestampdiff(minute, start_time, submit_time) <br><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/5b7c4e7ac7883ee97235b35a9f62dab9-13.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/067/5b7c4e7ac7883ee97235b35a9f62dab9-14.png?x-oss-process=image/resize,p_40" class="lazy" alt="Detailed introduction to SQL addition, deletion and modification operations"> SQL8 Delete records (3) </p><h4 id="strong-Exam-record-table-structure-of-exam-paper-answer-record-table-strong"><strong> Exam_record table structure of exam paper answer record table </strong></h4><p></p><pre class="brush:php;toolbar:false"> 题目描述 现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录， 请删除exam_record表中所有记录，并重置自增主键。 后台会通过SELECT table_rows, auto_increment FROM information_schema.tables WHERE table_name='exam_record'语句来对比输出结果 建表语句 drop table if EXISTS exam_record; CREATE TABLE IF NOT EXISTS exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; TRUNCATE exam_record; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58); 答案 TRUNCATE table exam_record;
Recommended study: " SQL tutorial》
The above is the detailed content of Detailed introduction to SQL addition, deletion and modification operations. For more information, please follow other related articles on the PHP Chinese website!
SQL: A Beginner-Friendly Approach to Data Management?Apr 19, 2025 am 12:12 AMSQL is suitable for beginners because it is simple in syntax, powerful in function, and widely used in database systems. 1.SQL is used to manage relational databases and organize data through tables. 2. Basic operations include creating, inserting, querying, updating and deleting data. 3. Advanced usage such as JOIN, subquery and window functions enhance data analysis capabilities. 4. Common errors include syntax, logic and performance issues, which can be solved through inspection and optimization. 5. Performance optimization suggestions include using indexes, avoiding SELECT*, using EXPLAIN to analyze queries, normalizing databases, and improving code readability.
SQL in Action: Real-World Examples and Use CasesApr 18, 2025 am 12:13 AMIn practical applications, SQL is mainly used for data query and analysis, data integration and reporting, data cleaning and preprocessing, advanced usage and optimization, as well as handling complex queries and avoiding common errors. 1) Data query and analysis can be used to find the most sales product; 2) Data integration and reporting generate customer purchase reports through JOIN operations; 3) Data cleaning and preprocessing can delete abnormal age records; 4) Advanced usage and optimization include using window functions and creating indexes; 5) CTE and JOIN can be used to handle complex queries to avoid common errors such as SQL injection.
SQL and MySQL: Understanding the Core DifferencesApr 17, 2025 am 12:03 AMSQL is a standard language for managing relational databases, while MySQL is a specific database management system. SQL provides a unified syntax and is suitable for a variety of databases; MySQL is lightweight and open source, with stable performance but has bottlenecks in big data processing.
SQL: The Learning Curve for BeginnersApr 16, 2025 am 12:11 AMThe SQL learning curve is steep, but it can be mastered through practice and understanding the core concepts. 1. Basic operations include SELECT, INSERT, UPDATE, DELETE. 2. Query execution is divided into three steps: analysis, optimization and execution. 3. Basic usage is such as querying employee information, and advanced usage is such as using JOIN connection table. 4. Common errors include not using alias and SQL injection, and parameterized query is required to prevent it. 5. Performance optimization is achieved by selecting necessary columns and maintaining code readability.
SQL: The Commands, MySQL: The EngineApr 15, 2025 am 12:04 AMSQL commands are divided into five categories in MySQL: DQL, DDL, DML, DCL and TCL, and are used to define, operate and control database data. MySQL processes SQL commands through lexical analysis, syntax analysis, optimization and execution, and uses index and query optimizers to improve performance. Examples of usage include SELECT for data queries and JOIN for multi-table operations. Common errors include syntax, logic, and performance issues, and optimization strategies include using indexes, optimizing queries, and choosing the right storage engine.
SQL for Data Analysis: Advanced Techniques for Business IntelligenceApr 14, 2025 am 12:02 AMAdvanced query skills in SQL include subqueries, window functions, CTEs and complex JOINs, which can handle complex data analysis requirements. 1) Subquery is used to find the employees with the highest salary in each department. 2) Window functions and CTE are used to analyze employee salary growth trends. 3) Performance optimization strategies include index optimization, query rewriting and using partition tables.
MySQL: A Specific Implementation of SQLApr 13, 2025 am 12:02 AMMySQL is an open source relational database management system that provides standard SQL functions and extensions. 1) MySQL supports standard SQL operations such as CREATE, INSERT, UPDATE, DELETE, and extends the LIMIT clause. 2) It uses storage engines such as InnoDB and MyISAM, which are suitable for different scenarios. 3) Users can efficiently use MySQL through advanced functions such as creating tables, inserting data, and using stored procedures.
SQL: Making Data Management Accessible to AllApr 12, 2025 am 12:14 AMSQLmakesdatamanagementaccessibletoallbyprovidingasimpleyetpowerfultoolsetforqueryingandmanagingdatabases.1)Itworkswithrelationaldatabases,allowinguserstospecifywhattheywanttodowiththedata.2)SQL'sstrengthliesinfiltering,sorting,andjoiningdataacrosstab
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
SublimeText3 English version
Recommended: Win version, supports code prompts!
VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.
SublimeText3 Linux new version
SublimeText3 Linux latest version
Dreamweaver CS6
Visual web development tools
