Database Articles - Page 335 of 671

To combine multiple advanced MySQL select queries, use UNION. Let us first create a table −mysql> create table DemoTable1 (Value1 int, Value2 int); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10, 29); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1 values(100, 190); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values(40, 101); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+--------+--------+ | Value1 | Value2 | ... Read More

The INITCAP() method display the first character in every word in uppercase and rest in lowercase.To implement the opposite functionality, you need to create your own function in MySQL. Here’s the function −mysql> delimiter // mysql> create function convertFirstLetterToLowerAndRemainingToCapital(value varchar(250))    returns varchar(250)    deterministic    begin    declare valueLength int;    declare l int;    set valueLength = char_length(value);    set value = upper(value);    set l = 0;    while (l < valueLength ) do       if (mid(value, l ,1) = ' ' or l = 0) then          if (l < valueLength ... Read More

The CHANGE command in MySQL is used to rename column name. Let us first create a table −mysql> create table DemoTable796 (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100), StudentAge int ); Query OK, 0 rows affected (0.56 sec)Let us check the description of table −mysql> desc DemoTable796;This will produce the following output −+------------+--------------+------+-----+---------+----------------+ | Field      | Type         | Null | Key | Default | Extra          | +------------+--------------+------+-----+---------+----------------+ | StudentId  | int(11)      | NO   | PRI | NULL    | auto_increment | | Name ... Read More

Let us first create a table −mysql> create table DemoTable (CountryName varchar(100)); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('US'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('AUS'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('UK'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------------+ | CountryName | +-------------+ | US          | | AUS         | | ... Read More

In order to enable innoDB in MySQ, you need to work around my.ini file. However, in MySQL version 8, the default storage engine is innoDB. Check the same from my.ini file −You can even set this at the time of table creation −mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(100),    StudentLastName varchar(100),    StudentAge int    ) ENGINE=InnoDB; Query OK, 0 rows affected (1.66 sec)Let us now run a query to check the engine type of specific table −mysql> select table_name, engine from information_schema.tables where table_name="DemoTable";This will produce the following ... Read More

To get at least x number of rows, you need to use the LIMIT clause. Following is the syntax −select *from yourTableName order by yourColumnName DESC limit yourXNumberOfRows;Let us first create a table −mysql> create table DemoTable (    EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(100) ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(EmployeeName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(EmployeeName) values('Bob'); Query OK, 1 row affected (0.51 sec) ... Read More

The MySQL CASE statement is faster in comparison to PHP if statement. The PHP if statement takes too much time because it loads data and then process while CASE statement does not.Let us first create a table and work around an example of MySQL CASE statement −mysql> create table DemoTable (Value int); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(500); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(1000); Query OK, 1 row ... Read More

For this, you can use INTERVAL 12 hour using DATE_ADD(). Let us first create a table −mysql> create table DemoTable (DueDateTime datetime); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-07-12 10:50:30'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('2019-07-12 22:02:00'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('2019-07-12 11:12:10'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-07-12 09:02:00'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from ... Read More

To generate random numbers, use the ORDER BY RAND() function in MySQL. Let us first create a table −mysql> create table DemoTable (Value int); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(89); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(75); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

For this, you can use LAST_INSERT_ID(). Let us first create a table. Here, we have set the auto_increment id to StudentId column −mysql> create table DemoTable1 (StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(null); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-----------+ | StudentId | +-----------+ |         1 | +-----------+ 1 row in set (0.00 sec)Following is the query to ... Read More