Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 225 of 341
MySQL Quoted table/field names vs unquoted names?
Any identifiers like tablename, stored procedure, viewname or column etc. may be quoted or unquoted. When an identifier is a reserved keyword then you must quote it, else an error would occur.Let us first create a table. Here, we have taken field names as reserved keywords −mysql> create table `INT` (`select` int, `varchar` varchar(100)); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into `INT` values(1, 'MySQL'); Query OK, 1 row affected (0.14 sec) mysql> insert into `INT` values(2, 'MongoDB'); Query OK, 1 row affected (0.34 sec) mysql> insert into `INT` values(3, ...
Read MoreRetrieve records whenever a column value starts with 2 vowel letters in MySQL
Let us first create a table −mysql> create table DemoTable664 (CityName varchar(100)); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable664 values('Springfield'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable664 values('Austin'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable664 values('Franklin'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable664 values('OAKLAND'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable664 values('Anchorage'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable664;This will ...
Read MoreHow to separate last name and first names in single column into two new columns in MySQL?
For this, use SUBSTRING_INDEX() and REPLACE(). Let us first create a table −mysql> create table DemoTable (Name varchar(100)); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. Here, we have inserted last name and first names −mysql> insert into DemoTable values('Chris | Bob Brown'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Carol | Robert Taylor'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('Sam | David Miller'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will ...
Read MoreBest way to combine multiple advanced MySQL select queries?
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 MoreHow to implement the opposite of INITCAP() functionality with MySQL?
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 MoreHow can I install or enable innoDB in MySQL?
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 MoreGet at least x number of rows in MySQL?
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 MoreWhich is faster, a MySQL CASE statement or a PHP if statement?
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 MoreMySQL query to select records approaching in next 12 hours?
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 MoreHow to generate 5 random numbers in MySQL stored procedure?
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