Found 6702 Articles for Database

Simulating MySQL's ORDER BY FIELD() in PostgreSQL?

Arjun Thakur
Updated on 26-Jun-2020 12:53:09

301 Views

The following is the process to simulate MySQL’s ORDER BY FIELD() in PostgreSQL.We have used an Online Compiler to run PostgreSQL.Let us now see what we did above to get the output.Firstly, we created a table.create table PostgreOrderIdDemo (    countryName varchar(20) );Inserted records with the help of INSERT command.insert into PostgreOrderIdDemo values('IND'); insert into PostgreOrderIdDemo values('US'); insert into PostgreOrderIdDemo values('UK');The following is the syntax in PostgreSQL.SELECT * FROM yourTableName ORDER BY CASE WHEN columnName='IND' THEN 1 WHEN columnName='US' THEN 2 WHEN columnName='UK' THEN 3 ELSE 0 END, columnName;ExampleLet us now use the above syntax to get the output.SELECT * ... Read More

How to use a function for default value in MySQL?

Chandu yadav
Updated on 30-Jul-2019 22:30:23

871 Views

We cannot use a function for default value in MySQL, but we can use triggers. Let us see an example. First, we will create a table. The CREATE command is used to create a table. mysql> CREATE table TbLFunctionTrigger - > ( - > id int, - > username varchar(100) - > ); Query OK, 0 rows affected (0.55 sec) The following is the syntax to create a trigger and include a default value. CREATE TRIGGER anyName BEFORE INSERT ON yourTableName FOR EACH ROW ... Read More

Generating a unique random 10 character string using MySQL?

George John
Updated on 30-Jul-2019 22:30:23

3K+ Views

In order to generate a 10 character string, we can use inbuilt functions ‘rand()’ and ‘char()’. The following is the query to generate random 10 character string. mysql> SELECT concat( - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97) - > )AS Random10CharacterString; ... Read More

IF ELSE statement in a MySQL Statement?

Ankith Reddy
Updated on 30-Jul-2019 22:30:23

568 Views

In an If-Else statement, the condition is evaluated to be true or false depending on the value. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table IfelseDemo - > ( - > id int, - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.46 sec) Records are inserted with the help of INSERT command. mysql> insert into IfelseDemo values(1, 'John'); Query OK, 1 row affected (0.13 sec) ... Read More

How to use MySQL JOIN without ON condition?

Arjun Thakur
Updated on 30-Jul-2019 22:30:23

4K+ Views

We can use ‘cross join’ without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on. Example of cross join Creating the first table mysql> CREATE table ForeignTableDemo - > ( - > Id int, - > Name varchar(100), - > FK int ... Read More

MySQL ON vs USING?

Chandu yadav
Updated on 30-Jul-2019 22:30:23

5K+ Views

In general, we use ON in MySQL. In Joins, we use ON in a set of columns. USING is useful when both the tables share a column of the exact same name on which they join. Example of On. Creating our first table. mysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), - > FK int - > ); Query OK, 0 rows affected (0.47 sec) Creating our second table. mysql> CREATE table PrimaryTableDemo - > ... Read More

Usage of backtick in SQL statements?

George John
Updated on 30-Jul-2019 22:30:23

380 Views

The backtick can be used in MySQL. To create a table, we can put table_name in backticks. Example of Backtick in MySQL. The CREATE command is used to create a table. Here, we have added the table name using the backtick symbol. mysql> create table `backtickSymbol` -> ( -> uniId int -> ); Query OK, 0 rows affected (1.65 sec) Records are inserted with the help of INSERT command. mysql> insert into `backtickSymbol` values(1); Query OK, 1 row affected (0.20 sec) mysql> insert into `backtickSymbol` values(2); Query ... Read More

Sorting varchar field numerically in MySQL?

Ankith Reddy
Updated on 30-Jul-2019 22:30:23

2K+ Views

‘LPAD(lower(column_name))’ is used to sort the varchar field numerically in MySQL. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table SortingvarcharDemo -> ( -> List varchar(10) -> ); Query OK, 0 rows affected (0.82 sec) Records are inserted with the help of INSERT command. mysql> insert into SortingvarcharDemo values("99"); Query OK, 1 row affected (0.12 sec) mysql> insert into SortingvarcharDemo values("9"); Query OK, 1 row affected (0.17 sec) mysql> insert into SortingvarcharDemo ... Read More

How to check similarity between two strings in MySQL?

Arjun Thakur
Updated on 26-Jun-2020 12:27:33

1K+ Views

Similarity between two strings can be checked with the help of ‘strcmp()’ function. Here are the conditions.If both strings are equal, then it returns 0.If first string is less than the second string, it returns -1.If first string is greater than the second string, it returns 1.Here is an example.Case 1 − If both strings are equal.The following is the query.mysql > SELECT STRCMP("demo", "demo");The following is the output of the above query.+------------------------+ | STRCMP("demo", "demo") | +------------------------+ | 0 ... Read More

Is it possible to use UPDATE query with LIMIT in MySQL?

Chandu yadav
Updated on 26-Jun-2020 12:30:35

8K+ Views

Yes, it is possible to use UPDATE query with LIMIT in MySQL. Let us see how.For our example, we will first create a table. The CREATE command is used to create a table.mysql>CREATE table tblUpdateLimit -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Records are inserted with the help of INSERT command.mysql>INSERT into tblUpdateLimit values(1, 'John'); Query OK, 1 row affected (0.54 sec) mysql>INSERT into tblUpdateLimit values(2, 'Carol'); Query OK, 1 row affected (0.12 sec) mysql>INSERT into tblUpdateLimit values(3, 'Smith'); Query OK, 1 row affected (0.10 sec) mysql>INSERT into ... Read More

Advertisements