How to do insert update delete in MySql Workbench

How to do insert update delete in MySql Workbench
In this part of the MySQL tutorial, we will insert, update and delete data from MySQL tables. We will use the INSERT, DELETE and UPDATE statements. These statements are part of the SQL Data Manipulation Language, DML. Inserting data The INSERT statement is used to insert data into tables. We will create a new table, where we will do our examples. mysql CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title VARCHAR(100), - Author VARCHAR(60)); We create a new table Books, with Id, Title and Author columns. mysql INSERT INTO Books(Id, Title, Author) VALUES(1, 'War and Peace', - 'Leo Tolstoy'); This is the classic INSERT SQL statement. We have specified all column names after the table name and all values after the VALUES keyword. We add our first row into the table. mysql SELECT * FROM Books; +----+---------------+-------------+ | Id | Title | Author | +----+---------------+-------------+ | 1 | War and Peace | Leo Tolstoy | +----+---------------+-------------+ We have inserted our first row into the Books table. mysql INSERT INTO Books(Title, Author) VALUES ('The Brothers Karamazov', - 'Fyodor Dostoyevsky'); We add a new title into the Books table. We have omitted the Id column. The Id column has AUTO_INCREMENT attribute. This means that MySQL will increase the Id column automatically. The value by which the AUTO_INCREMENT column is increased is controlled by auto_increment_increment system variable. By default it is 1. mysql SELECT * FROM Books; +----+------------------------+--------------------+ | Id | Title | Author | +----+------------------------+--------------------+ | 1 | War and Peace | Leo Tolstoy | | 2 | The Brothers Karamazov | Fyodor Dostoyevsky | +----+------------------------+--------------------+ Here is what we have in the Books table. mysql INSERT INTO Books VALUES(3, 'Crime and Punishment', - 'Fyodor Dostoyevsky'); In this SQL statement, we did not specify any column names after the table name. In such a case, we have to supply all values. mysql REPLACE INTO Books VALUES(3, 'Paradise Lost', 'John Milton'); Query OK, 2 rows affected (0.00 sec) The REPLACE statement is a MySQL extension to the SQL standard. It inserts a new row or replaces the old row if it collides with an existing row. In our table, there is a row with Id=3. So our previous statement replaces it with a new row. There is a message that two rows were affected. One row was deleted and one was inserted. mysql SELECT * FROM Books WHERE Id=3; +----+---------------+-------------+ | Id | Title | Author | +----+---------------+-------------+ | 3 | Paradise Lost | John Milton | +----+---------------+-------------+ This is what we have now in the third column. We can use the INSERT and SELECT statements together in one statement. mysql CREATE TABLE Books2(Id INTEGER PRIMARY KEY AUTO_INCREMENT, - Title VARCHAR(100), Author VARCHAR(60)) type=MEMORY; First, we create a temporary table called Books2 in memory. mysql INSERT INTO Books2 SELECT * FROM Books; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Here we insert all data into the Books2 that we select from the Books table. mysql SELECT * FROM Books2; +----+------------------------+--------------------+ | Id | Title | Author | +----+------------------------+--------------------+ | 1 | War and Peace | Leo Tolstoy | | 2 | The Brothers Karamazov | Fyodor Dostoyevsky | | 3 | Paradise Lost | John Milton | +----+------------------------+--------------------+ We verify it. All OK. mysql INSERT INTO Books(Title, Author) VALUES ('The Insulted and Humiliated', - 'Fyodor Dostoyevsky'), ('Cousin Bette', 'Honore de Balzac'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 We can insert more than one row into the table with the INSERT statement. Here we show how. We can insert data from a file on the filesystem. First, we dump data from the Books table in a books.csv file. mysql SELECT * INTO OUTFILE '/tmp/books.csv' - FIELDS TERMINATED BY ',' - LINES TERMINATED BY '\n' - FROM Books; We write data from the Books table into the books.csv file. The data will be in a CSV format. $ cat /tmp/books.csv 1,War and Peace,Leo Tolstoy 2,The Brothers Karamazov,Fyodor Dostoyevsky 3,Paradise Lost,John Milton 4,The Insulted and Humiliated,Fyodor Dostoyevsky 5,Cousin Bette,Honore de Balzac We show the contents of the books.csv file. mysql TRUNCATE Books; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM Books; Empty set (0.00 sec) We delete all data from the table. mysql LOAD DATA INFILE '/tmp/books.csv' - INTO TABLE Books - FIELDS TERMINATED BY ',' - LINES TERMINATED BY '\n'; We use the LOAD DATA INFILE syntax to populate the Books table from the books.csv file.
via YouTube https://youtu.be/yqFyjmGVwgg