MySQL Articles - Page 275 of 439

You can use ORDER BY ASC to order timestamp values in ascending order.The following is the syntax without using TIMESTAMP() −SELECT yourTimestampColumnName from yourTableName order by yourTimestampColumnName ASC;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Timestamp_TableDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> yourTimestamp timestamp -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records in the table using insert command. The query is as follows ... Read More

To remove the first two characters of all fields, you need to use SUBSTRING() function from MySQL. The syntax is as follows −UPDATE yourTableName SET yourColumnName=SUBSTRING(yourColumnName, 3) WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveFirstTwoCharacterDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> StringValue varchar(30),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('U:100'); Query OK, 1 ... Read More

To add results from several COUNT queries, you can use the following syntax −SELECT (SELECT COUNT(*) FROM yourTableName1)+ (SELECT COUNT(*) FROM yourTableName2)+ (SELECT COUNT(*) FROM yourTableName3)+ . . . N AS anyAliasName;Let us use three tables in the test database −userssortingstringdemouserlogintableCheck the table records from the table using a select statement. Let’s take 3 sample tables with records.The table records for the first table is as follows −mysql> select *from users;The following is the output −+----+----------+---------------------+ | Id | UserName | UserLastseen        | +----+----------+---------------------+ |  1 | Larry    | 2019-01-15 02:45:00 | |  2 | Sam ... Read More

If you have spaces between letters then you can use REPLACE() function to remove spaces.The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’ ‘, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Now insert some records in the table using insert ... Read More

You can use TRIM() function to remove spaces. The syntax is as follows −UPDATE yourTableName SET yourColumnName=TRIM(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeSpaceDemo(UserId, ... Read More

Here you can use BETWEEN operator. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName BETWEEN 6 AND 10;You can use regular expression like this. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '10|[6-9]';To understand the both syntax, let us create a table. The query to create a table is as follows −mysql> create table RegularExpressionDemo    -> (    -> Id int    -> ); Query OK, 0 rows affected (1.11 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into RegularExpressionDemo values(1); Query OK, ... Read More

As you know if you use LIMIT 0 in MySQL SELECT statement, it returns an empty set.The LIMIT can be used when you want a specified number of rows from a result rather than the entire rows. If you use any MySQL API, then the job of LIMIT is to acquire the type of result columns.LIMIT 0 can be used to check the validity of a query. For more details use the following link −https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.htmlHere is the demo of LIMIT 0. The query to create a table is as follows −mysql> create table Limit0Demo    -> (    -> Id ... Read More

To delete all rows containing string “foo” in table “bar”, you need to use LIKE operator.To understand the above syntax, let us create a sample table with name “bar”. The query to create a table is as follows. We will always insert records with string “foo” using INSERT command after creating the below table −mysql> create table bar    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Words longtext,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.61 sec)Now you can insert some records in the table using insert command. The string “foo” ... Read More

To compare two tables and return missing ids, you need to use a subquery. The syntax is as follows −SELECT yourFirstTableName.yourIdColumnName FROM yourFirstTableName WHERE NOT IN(SELECT yourSecondTableName.yourIdColumnName FROM youSecondTableName);To understand the above syntax, let us create a table with sample fields and then we will insert records. The query to create the first table −First_Tablemysql> create table First_Table    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.88 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into First_Table values(1); Query OK, 1 row affected (0.68 ... Read More

To drop a database with the character ‘?’ in its name, you need to use backtick symbol around the database name. The syntax is as follows −DROP DATABASE `yourDatabaseName`;To understand the above syntax, let us create a database. The query to create a database is as follows −mysql> create database `test?data`; Query OK, 1 row affected (0.14 sec)So, I have a database with? character. The query to show all databases is as follows −mysql> show databases;The following is the output −+-----------------------+ | Database              | +-----------------------+ | business              | ... Read More