Insert multiple messages at a time

SQL34 link address: https://www.nowcoder.com/practice/51c12cea6a97468da149c04b7ecf362e?tpId=82&&tqId=29802&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking
Title Description:
The topic has already executed the following statement:

drop table if exists actor;
CREATE TABLE actor (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  DATETIME NOT NULL)

Please insert the following data in batch for the table actor (there cannot be two insert statements!)

Analysis: insert multiple pieces of information and write them at the same time after VALUES, separated by commas

INSERT INTO actor
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
       (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

Insert information without using insert

SQL35 link address: https://www.nowcoder.com/practice/153c8a8e7805400ba8e384e03acc6b3e?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1
Title Description:
The topic has already executed the following statement:

drop table if exists actor;
CREATE TABLE actor (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  DATETIME NOT NULL);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

For the table actor, insert the following data. If the data already exists, please ignore it (replace operation is not supported)

//There are three commonly used statements for inserting data in mysql: 
//insert into means to insert data. The database will check the primary key. If there is a duplicate, an error will be reported; 
//replace into means to insert replacement data. There is a PrimaryKey in the demand table,
// Or unique index. If data already exists in the database, replace it with new data
//If there is no data effect, it is the same as insert into; 
//insert ignore means that if the same record already exists in, the current new data will be ignored;
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
//Niuke SQL is sqlite3 and must be written according to sqlite3:
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');
// If it is mysql, remove or, as follows:
insert into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

Add a new column

SQL40 link address: https://www.nowcoder.com/practice/119f04716d284cb7a19fba65dd876b03?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1

Title Description:
There is an actor table containing the following information:

CREATE TABLE  actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  datetime NOT NULL);

Now at last_ A new column named create is added after update_ Date, the type is datetime, NOT NULL, and the default value is' 2020-10-01 00:00:00 '

The syntax of SQLite modification table is:

CREATE [TEMP|TEMPORARY] TABLE table_name (column_definitions [, constraints]);

The vertical bar indicates that one of multiple tables is selected. The table declared with the TEMP or TEMPORARY reserved word is a TEMPORARY table and only exists in the current session. Once the connection is disconnected, it will be automatically deleted.
The answer to this question is:

alter table actor
add
create_date datetime  NOT NULL DEFAULT '2020-10-01 00:00:00';

Construct trigger

SQL41 link address: https://www.nowcoder.com/practice/7e920bb2e1e74c4e83750f5c16033e2e?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1
Title Description:
Construct a trigger audit_log to employees_ When a piece of data is inserted into the test table, it triggers the insertion of relevant data into audit.

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

The background will go to employees_test insert a piece of data:

INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );

Then use the query statement from audit:

select * from audit;

In MySQL, the syntax for creating triggers is as follows:

CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

Of which:

• trigger_name: identifies the trigger name, which is specified by the user;
• trigger_time: identifies the trigger time, and the value is BEFORE or AFTER;
• trigger_event: identifies the trigger event, and the values are INSERT, UPDATE or DELETE;
• tbl_name: identifies the table name on which the trigger is created, that is, the table on which the trigger is created;
• trigger_stmt: trigger program body. It can be an SQL statement or multiple statements contained in BEGIN and END. Each statement should END with a semicolon.

Combined with the situation, the answer to this question is:

create trigger audit_log
after insert on employees_test
for each row
begin
    insert into audit values(new.id,new.name);
end

Reference blog: https://blog.csdn.net/weixin_41177699/article/details/80302987