Love Me Some Coding | Love Some Coding | Love Coding

About Me Java Table of Content Spring Boot Table of Content Spring Data Table of Content SQL Table of Content Elasticsearch Table of Content Git Table of Content HTML Table of Content CSS Table of Content Javascript Table of Content Linux Table of Content Soft Skills Table of Content

MySQL Delete

To delete data from a table, DELETE statement is used. Once data is deleted from your tables you can’t recover it. It is gone. It is recommended to back up your database regularly so that if data is gone you can still recover it from your backups.

DELETE FROM table_name
WHERE condition;

The WHERE clause is optional but if it’s omitted then all rows will be deleted.

Here is an example of DELETE where we are deleting customer with id 21.

As you can see row 21st is gone. The DELETE statement returns the number of rows deleted.

To delete all rows within a table, use the TRUNCATE command which has better performance.

DELETE with LIMIT

When deleting data it is recommended to turn off SQL_SAFE_UPDATES.

DELETE FROM table_name
ORDER BY column_name
LIMIT count;

DELETE with ON CASCADE

ON DELETE CASCADE is used to delete rows or data in a child table when a row is deleted in the parent table.

You have to create your child table with it.

CREATE TABLE table_name (
    column_names...
    FOREIGN KEY (FK)
        REFERENCES parent_table_name (FK)
        ON DELETE CASCADE
);

Here is the query to show which tables use ON DELETE CASCADE.

USE information_schema;
 
SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'

March 31, 2019

MySQL Update

The UPDATE clause is used to update an existing row. It can be used to update a single row, a group of rows, or all rows within a table.

UPDATE 
    table_name1, table_name2,... 
SET 
    column_name1 = value1,
    column_name2 = value2,
    ...
WHERE conditions;

How to use UPDATE
1. Specify the tables you want to update.
2. Set the column values to their new values.
3. The WHERE clause is optional but in most cases required. If you forget the WHERE clause, your UPDATE will affect every row which is something you might not want.

Let’s update the row with id 2.

We updated the first_name and last_name of the row with id 2 to Folau and Kaveinga. Don’t be confused that we have two queries above. One for the update and the other is a SELECT query which is used to check our update.

UPDATE with LOW_PRIORITY

LOW_PRIORITY tells MySQL to perform the update after all reading from any connection to the table is done.

UPDATE with IGNORE

IGNORE tells MySQL to ignore an error in the update. If errors occur then ignore those rows and continue til the end.

Our example above updated rows 2 and 4 even though ids 100 and 101 don’t exist.

How to update a table column by values return from a SELECT statement

Here we are updating the last_name of the customer by the last_name of the employee whose id is 2.

How to update a table from a table in another database but in the same server instance.

SET SQL_SAFE_UPDATES=0;

UPDATE

	db_account.user as acctUser, db_billing.user as billingUser
    
SET 
	
    billingUser.name = acctUser.name

WHERE 
	
    acctUser.id = billingUser.id;

SET SQL_SAFE_UPDATES=1;

Here we have a db_account database and db_billing database that live in the same server. And we are updating the user name of the db_billing.user by the user name of the db_account.user.

UPDATE with no KEY

MySQL will not allow you to use UPDATE without a WHERE clause or a LIMIT clause. MySQL will not allow you to use WHERE without including the id column in the WHERE condition.

These queries will not run because the first query does not use a WHERE clause. The second query does not have a id check condition.

UPDATE user SET name = 'Folau';
or
UPDATE user SET name = 'Folau' WHERE email = 'test@gmail.com';

MySQL uses SQL_SAFE_UPDATES as being set to 0 to disable this checking.

SET SQL_SAFE_UPDATES=0;

It is always good practice to turn SQL_SAFE_UPDATES back to on right after.

SET SQL_SAFE_UPDATES=1;

Bulk UPDATE

Sometimes you are tasked to update database records which are too much to write individual UPDATE queries for.

UPDATE user SET name = 'Folau' WHERE id=1;
UPDATE user SET name = 'Lisa' WHERE id=2;
UPDATE user SET name = 'Kinga' WHERE id=3;
...

The above queries can be 10000+ rows which will perform very slow to execute.

What you can do is use the case statement

UPDATE user 
SET name = 
  (CASE id WHEN 1 THEN 'Folau'
           WHEN 2 THEN 'Lisa'
           WHEN 3 THEN 'Kinga'
   END)
WHERE id IN (1,2,3);

And you can have 10000+ WHEN THEN clauses and the query will perform better than individual UPDATEs.

Bulk UPDATE with multiple columns

You can update multiple columns all at the same time.

UPDATE with JOIN

UPDATE account as acct
  LEFT JOIN member as memb ON memb.account_id = acct.id
  LEFT JOIN group as grp ON grp.uuid = memb.group_uuid
SET acct.group_uuid = memb.group_uuid,
  acct.partner_slug = grp.url_slug,
  acct.paid_by = grp.paid_by
WHERE acct.id <> 0 
  AND memb.group_uuid IS NOT NULL 
  AND acct.group_uuid IS NULL;

March 31, 2019

MySQL Insert

The INSERT command inserts one or more rows into a table. Let’s see how the syntax works.

INSERT INTO table_name(column_name1,column_name2,..)
VALUES (column_value1,column_value2,...);

INSERT INTO `employee` 
(`age`, `email`, `first_name`, `last_name`, `title`) 
VALUES 
('20', 'test@gmail.com', 'John', 'Brown', 'Manager');

INSERT INTO table_name – this query is an insert query into a table.
VALUES – values for the columns you have chosen.
* The number of columns and values must be equal otherwise it won’t work.

* It is very important that you insert string data within single quotes to avoid abnormal behavior.
For example if your string data has a comma in it, mysql will complain as it thinks you are inserting into more columns than specified.

-- it seems like we are inserting data into 3 columns which is not true
INSERT INTO `employee` 
(`first_name`, `last_name`) 
VALUES 
(John,roe, Brown);

-- using single quotes
INSERT INTO `employee` 
(`first_name`, `last_name`) 
VALUES 
('John,roe', 'Brown');

You can also add multiple rows in a single insert call.

Insert with multiple rows in a single call

INSERT INTO table_name(column_name1,column_name2,..)
VALUES 
(column_value1,column_value2,...),
(column_value1,column_value2,...),
(column_value1,column_value2,...);

INSERT INTO `employee` 
(`age`, `email`, `first_name`, `last_name`, `title`) 
VALUES 
('20', 'test@gmail.com', 'John', 'Brown', 'Manager'), 
('35', 'test1@gmail.com', 'James', 'Brown', 'Manager');

Insert into a table what is selected from another table

Let’s say you have a table and for whatever reason, you want to copy it to another table. What you would do is, create the new table and then move the data from the old table to the new table.

INSERT INTO table_name1(column_name1,column_name2,...)
/* replace VALUES with this */
(SELECT 
   column_name1,column_name2,...
FROM 
   table_name2);

VALUES is replaced by a SELECT statement which reads the data from table_name2.

INSERT INTO `customer`(`first_name`,`last_name`)

(SELECT `first_name`,`last_name` 
FROM `store`.`employee`);

As you can see, we just inserted all first names and last names from our employee table into the first_name and last_name columns for the customer table.

Insert with ignore

When we are inserting multiple rows into a table and something goes wrong, mysql will roll back all transactions so none of the rows are added. If we want to ignore any error and continue the sql execution then we have to use IGNORE. IGNORE indicates that if any of the row fails that other rows that are good should be inserted.

INSERT IGNORE INTO table_name(column_name1,column_name2,..)
VALUES 
(column_value1,column_value2,...),
(column_value1,column_value2,...),
(column_value1,column_value2,...);

INSERT IGNORE INTO `customer`
(`first_name`,`address_id`)
VALUES
('James',20),
('Peter','Rollin'),
('John',3);

As you can see, James was added but Peter Rollin was not added because ‘Rollin’ was an invalid value for that column. The SQL execution did not stop and roll back instead it continued to add John.

March 31, 2019

Spring Boot Migration from Spring

March 19, 2019

Spring Boot Web MVC

Spring boot makes easy for us developers to get up and running with a spring mvc project. You add the following dependencies and you are ready to go.

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
</dependency>
<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
</dependency>
<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>

We are using thymeleaf for our views.

Create a controller.

Pay attention to the @Controller annotation which turns this class, UserController, into a spring controller component.

@Controller
public class UserController {
}

Create a request handler within @Controller.

In this case we are using a Get request handler. Use the Model class to pass data to you view.

@GetMapping("/home")
public String home(Model model){
		log.debug("home(..)");
		
		model.addAttribute("message", "Welcome to my page");
		
		log.debug("model={}",ObjectUtils.toJson(model));
		
		return "home";
}

Create a view.

Views are generally located in the templates folder under resources folder. Here we are creating a view for our home page.

<!DOCTYPE HTML>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="utf-8">
<meta name="viewport"
	content="width=device-width, initial-scale=1, shrink-to-fit=no">

<title>Spring Boot Thymeleaf Hello World Example</title>

<link rel="stylesheet" th:href="@{/css/main.css}" />

<!-- Latest compiled and minified CSS -->
<link rel="stylesheet"
	href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">



</head>

<body>

	<nav class="navbar navbar-expand-md navbar-dark bg-dark fixed-top">
		<a class="navbar-brand" href="#">Folau Kaveinga</a>
	</nav>

	<div role="main" class="container">

		<div class="starter-template">
			<h1>Spring Boot Web Thymeleaf Example</h1>
			<h2>
				<span th:text="'Hello, ' + ${message}"></span>
			</h2>
		</div>

	</div>
	<!-- /.container -->
</body>

<!-- jQuery library -->
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>

<!-- Popper JS -->
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>

<!-- Latest compiled JavaScript -->
<script
	src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</html>

Static files

Static files such as css and js files can be served from the static folder under the resources folder.

For demo purposes, let create a main css file to style our home page.

body {
  padding-top: 5rem;
}
.starter-template {
  padding: 3rem 1.5rem;
  text-align: center;
}

h1{
	color:#0000FF;
}

h2{
	color:#FF0000;
}

If you start your server and go to /home, your should see the home page being displayed.

Source code on Github

March 19, 2019

Subscribe To Our Newsletter

You will receive our latest post and tutorial.

MySQL Delete

MySQL Update

MySQL Insert

Spring Boot Migration from Spring

Spring Boot Web MVC

About

Policies