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
Articles by Vrundesha Joshi
Page 22 of 22
Get the fields in each constraint in MySQL
Let’s say we have a database “business” with number of tables. If you want to Get the fields in each constraint, then use the below query.The below query is to get the fields in each one of those constraints −mysql> select * −> from information_schema.key_column_usage −> where constraint_schema = 'business';The following is the output −+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def ...
Read MoreHow to select domain name from email address in MySQL?
To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.To understand the concept, let us create a table. The following is the query to create a table.mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −mysql> insert into selectDomainNameOnly values('John123@yahoo.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into selectDomainNameOnly ...
Read MoreGetting last 5 character of a string with MySQL query?
To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.The syntax for RIGHT() method is as follows −SELECT RIGHT(yourColumnName, valueOfN) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table gettingLast5Characters −> ( −> BookName varchar(100) −> ); Query OK, 0 rows affected (0.73 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into gettingLast5Characters values('Introduction ...
Read MoreWhat should I do? Select int as currency or convert int to currency format in MySql?
To convert int to current format, use CONCAT() with FORMAT() function from MySQL.The syntax is as follows −SELECT CONCAT(‘CurrencySymbol’, FORMAT(yourColumnName, valueAfterDecimal)) as AnyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table −mysql> create table AddingCurrencySymbolDemo −> ( −> Amount int −> ); Query OK, 0 rows affected (1.50 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into AddingCurrencySymbolDemo values(250); Query OK, 1 row affected (0.22 sec) mysql> insert into AddingCurrencySymbolDemo values(500); Query OK, 1 ...
Read MoreMySQL query to extract first word from a field?
To extract first word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, 1) as anyVariableName from yourTableName;In the above query, if you use -1 in place of 1 then you will get the last word. To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is ...
Read MoreCombine date and time column into a timestamp in MySQL?
To combine date and time column into a timestamp, you can use cast() function with concat().The syntax is as follows −select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table DateAndTimeToTimestamp −> ( −> Duedate date, −> DueTime time −> ); ...
Read MoreHow to echo print statements while executing an SQL script?
To perform echo print statements while executing SQL scripts, use the following syntax.The syntax is as follows −SELECT ‘anyStringValue as’ ‘;The query is as follows −mysql> select 'This is a SQL Script' AS' ';The following is the output −+----------------------+ | | +----------------------+ | This is a SQL Script | +----------------------+ 1 row in set, 1 warning (0.00 sec)You can add dynamic data to your status like insert, update and delete with the help of concat() function. The query is ...
Read MoreWhat is the use of FLUSH PRIVILEGES statement in MySQL?
Actually, we need to perform flush-privileges operation to tell the server to reload the grant tables. This can be done by issuing FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command. FLUSH PRIVILEGES is really needed if we modify the grant tables directly using such as INSERT, UPDATE or DELETE, the changes have no effect on privileges checking until we either restart the server or tell it to reload the tables. But, Privileges assigned through GRANT choice don't want FLUSH PRIVILEGES to take effect - MySQL server cannotice these changes and reload the grant tables instantly. ...
Read More