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
Database Articles
Page 540 of 546
Get table names using SELECT statement in MySQL?
To get table names using SELECT statement, use “information_schema.tables”. Let us see an example, wherein we have a database that contains 3 tables. The syntax to get all table names with the help of SELECT statement. SELECT Table_name as TablesName from information_schema.tables where table_schema = 'yourDatabaseName'; Using database “test”, and applying the above syntax to get the table names using SELECT mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema.tables where table_schema = 'test'; Output with the name of the three tables. +--------------------+ | TablesName ...
Read MoreHow to prepend a string to a column value in MySQL?
To prepend a string to a column value in MySQL, we can use the function CONCAT. The CONCAT function can be used with UPDATE statement. Creating a table. mysql> create table PrependStringOnCOlumnName -> ( -> Id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (1.35 sec) Inserting some records. mysql> insert into PrependStringOnCOlumnName values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into PrependStringOnCOlumnName values(2, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into PrependStringOnCOlumnName values(3, 'Johnson'); Query OK, 1 row affected (0.45 sec) ...
Read MoreHow should I enable LOAD DATA LOCAL INFILE in my.cnf in MySQL?
We can enable it with the help of the SET command with GLOBAL. The first time, local infile will be off. The following is the syntax. mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile'; Here is the output. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+ 1 row in set (0.01 sec) We can enable the local infile with the help of ON or boolean value true or numeric value 1. The following is the syntax to enable the local infile. mysql> SET GLOBAL local_infile = 'ON'; Query OK, 0 ...
Read MorePassing an array to a query using WHERE clause in MySQL?
We can pass an array with the help of where IN clause. Let us first create a new table for our example. mysql> create table PassingAnArrayDemo -> ( -> id int, -> Firstname varchar(100) -> ); Query OK, 0 rows affected (1.60 sec) Let us now insert records. mysql> insert into PassingAnArrayDemo values(1, 'John'), (2, 'Carol'), (3, 'Smith'), (4, 'Bob'), (5, 'Johnson'), (6, 'David'), (7, 'Sam'), (8, 'Jessica'); Query OK, 8 rows affected (0.32 sec) Records: 8 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from PassingAnArrayDemo; The ...
Read MoreWhat data type to use for hashed password field in MySQL?
The hashed password data type depends upon which hashing algorithm we are using. The hashing algorithm does not depends upon the input size because it produces a result of the same length. It gives the result in a series of hexadecimal digits, and we can reduce the hexadecimal digits by half with the help of UNHEX() function. There are various algorithms and data types to store values. MD5 − It can use char(32) or BINARY(16). SHA-1 − It can use data type char(40) or BINARY(20). Example of MD5 The following is an example − mysql> select MD5('This ...
Read MoreHow long is the SHA256 hash in MySQL?
As the name “SHA256” suggest, it is 256 bits long. If we are using hexadecimal notation then digit codes represent 4 bits. In order to represent 256, we need 256/4 = 64 bits. We need a data type varchar(64) or char(64). Creating a table for our example. mysql> create table SHA256Demo -> ( -> Password varchar(64) -> ); Query OK, 0 rows affected (0.54 sec) Inserting records into table. mysql> insert into SHA256Demo values(' 4e2e1a39dba84a0b5a91043bb0e4dbef23970837'); Query OK, 1 row affected (0.18 sec) Displaying all records. mysql> select *From SHA256Demo; The following ...
Read MoreHow to shrink/ purge ibdata1 file in MySQL?
The ibdata1 file cannot shrink, it is a particularly troublesome feature of MySQL. The ibdata1 file can be shrinked if you delete all databases, remove the files and reload the mysqldump. We can configure MySQL so that each table, including its indexes, is stored as a separate file. It is enabled by default as of version 5.6.6 of MySQL. To setup our server to use separate files for each table, we need to change my.cnf in order to enable it. If your MySQL version is below 5.6.6, then you need to add it in my.cnf file. [mysqld] ...
Read MoreHow to create a temporary MySQL table in a SELECT statement without a separate CREATE TABLE?
To create a temporary table in a SELECT statement we use TEMPORARY keyword. This temporary table will be visible for the current session and whenever a session is closed, it is automatically destroyed. Two sessions can use the same temporary table. Creating a table. mysql> create table MyTableDemo -> ( -> id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.69 sec) Inserting some records. mysql> insert into MyTableDemo values(1, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into MyTableDemo values(2, 'Carol'); Query OK, 1 row affected (0.13 ...
Read MoreGet digits from a record in MySQL?
Use the function CONVERT() or Regular Expression. The CONVERT() method converts a value from one datatype to another. This will ecnetually fetch digits for us. Let us see an example. Firstly, we will create a table. mysql> create table textIntoNumberDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.47 sec) Inserting some records. mysql> insert into textIntoNumberDemo values('John-11'); Query OK, 1 row affected (0.11 sec) mysql> insert into textIntoNumberDemo values('John-12'); Query OK, 1 row affected (0.17 sec) mysql> insert into textIntoNumberDemo values('John-2'); Query OK, 1 row affected (0.11 ...
Read MoreSwapping two column values in MySQL?
To swap two columns, we can apply the below swapping logic. Add both values and store them into the first column Subtract the first column’s value from the second and store it into the second column. Subtract the first column’s value from the updated second column and store it into the first. The above rule structure is as follows. Suppose, the first column is a and the second column is b. 1. a = a+b; 2. b = a-b; 3. a = a-b; Now we will apply the above rule in order to swap the two ...
Read More