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
MySQL Articles
Page 38 of 355
Select three random records with a fixed number of characters for each column value in MySQL
For this, you can use CHAR_LENGTH(). Use RAND() for random records. Let us first create a table −mysql> create table DemoTable (Subject text); Query OK, 0 rows affected (0.61 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values('RubyOnRails'); Query OK, 1 row affected (0.25 sec) mysql> insert ...
Read MoreMySQL query to select top n rows efficiently?
Use index to select top n rows efficiently. Let us first create a table −mysql> create table DemoTable (StudentName varchar(100), StudentScore int ); Query OK, 0 rows affected (0.66 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable values('John', 34); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Carol', 55); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Bob', 58); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Sam', 38); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Mike', 48); Query OK, 1 row ...
Read MoreSwap data between two columns in MySQL?
To swap data between two columns in MySQL, use the concept of variable. Let us first create a table. Here, we will swap Name1 with Name2 −mysql> create table DemoTable -> ( -> Name1 varchar(100), -> Name2 varchar(100) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Smith', 'Chris Brown'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('David Miller', 'Jone Doe'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce ...
Read MoreMySQL stored procedure to return a column value?
Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Score int -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 858858686); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(2, 9900554); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(3, 646565667); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------+-----------+ | Id ...
Read MoreMySQL query to add dots if string has more than 10 words?
For this, use CASE statement. Let us first create a table −mysql> create table DemoTable -> ( -> Title text -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('My name is John and this is my first tutorial on MySQL'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('This is Carol and I work on MongoDB'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+--------------------------------------------------------+ | Title ...
Read MoreIncrease database field value by specified percentage using user-defined variables in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Amount int -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(500); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+--------+ | Amount | +--------+ | 100 | | 200 ...
Read MoreAdd values of two columns considering NULL values as zero in MySQL
For this, use COALESCE() function from MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 200); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(NULL, 90); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(NULL, NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(600, NULL); Query OK, 1 row affected (0.12 sec)Display all records from ...
Read MoreHow can I get the number of times a specific word appears in a column with MySQL?
For this, you can use COUNT() function. 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.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName) values('John'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(EmployeeName) values('Carol'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(EmployeeName) values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(EmployeeName) values('Carol'); Query OK, 1 row affected ...
Read MoreFind a specific column in all the tables in a database?
For this, use COLUMN_NAME and set LIKE with that specific column name. Let us find a specific column in an unknown table in a database −mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE column_name LIKE '%StudentName%' -> AND table_schema = 'web';OutputThis will produce the following output −+-------------------+-------------+-----------+-------------+----------------+ | TABLE_NAME | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |COLUMN_DEFAULT | +-------------------+-------------+-----------+-------------+----------------+ | demotable215 | StudentName | varchar | YES | NULL | | demotable221 | StudentName | varchar ...
Read MoreMySQL query to select top 10 records?
To select top 10 records, use LIMIT in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> PageNumber text -> ); Query OK, 0 rows affected (2.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Page-1'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values('Page-2'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Page-3'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Page-4'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Page-5'); Query OK, ...
Read More