Solution: Create a new tmp table Copy data to tmp table Delete original table Rename the tmp table’s name to original one’s # Step 1: CREATE TABLE tmp_table ( # your new column types ); # If you don't need to make any change CREATE TABLE tmp_table LIKE original_table; # Read more…
1. If <select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select> 2. Choose, When, Otherwise <select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <choose> # choose only one case among many options <when test="title != Read more…
SUBSTRING_INDEX(str, delim, count) str: the string to be split. delim: the delimiter. count: the position of the delimiter. A positive number means extract from left to right, a negative number means extract from right to left. For example: SELECT SUBSTRING_INDEX('100-200-300-400', '-', 1); — output: '100' SELECT SUBSTRING_INDEX('100-200-300-400', '-', 2); — Read more…
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. There are four types of join in MySQL: INNER JOIN: Returns records that have matching values in both tables LEFT JOIN: Returns all records from the left table, and the matched records Read more…
Atomicity: operations in a transaction either all succeed or all fail. Consistency: The database always transitions from one consistent state to another consistent state. The integrity of the database is not broken before the transaction starts and after the transaction ends. This means that the material written must fully comply Read more…
TRUNCATE TRUNCATE Command is a Data Definition Language operation. It is used to remove all the records from a table. It deletes all the records from an existing table but not the table itself. The structure or schema of the table is preserved. Truncate command marks the table for deallocation. This operation Read more…
Database and Table · Create Database CREATE DATABASE <database name>; CREATE DATABASE shop; · Create Table CREATE TABLE <table name> (<column name> <data type> <constrains>, <column name> <data type> <constrains>, …, <constrains of the table>,<constrains of the table>,…); CREATE TABLE product (id CHAR(4) NOT NULL, name VARCHAR(100) NOT NULL, price Read more…