Database Articles - Page 535 of 671

You can use in-built function CONCAT() from MySQL. The syntax is as follows −SELECT CONCAT(('(', yourColumnName1, ', ', yourColumnName2, ', ', yourColumnName3, ...N')')as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table Concatenation_OperatorDemo -> ( -> -> VendorId int, -> VendorName varchar(100), -> VendorCountry varchar(100) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into Concatenation_OperatorDemo values(101, 'Carol', 'US'); Query OK, 1 row affected (0.19 sec) mysql> insert ... Read More

The syntax for updating a column with random number between 1-3 is is as follows −update yourTableName set yourColumnName=FLOOR(1+RAND()*3);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UpdateNumber1To3 -> ( -> MyNumber int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UpdateNumber1To3 values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into UpdateNumber1To3 values(140); Query OK, 1 row affected (0.25 sec) mysql> insert into UpdateNumber1To3 values(130); ... Read More

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 More

To store query result in a variable with MySQL, use the SET command. The syntax is as follows −SET @anyVariableName = ( yourQuery);To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table QueryResultDemo    −> (    −> Price int    −> ); Query OK, 0 rows affected (0.59 sec)Now let us insert some records into the table. The following is the query to insert records −mysql> insert into QueryResultDemo values(100); Query OK, 1 row affected (0.17 sec) mysql> insert into QueryResultDemo values(20); Query OK, 1 row ... Read More

You can compare DATE string with string from DATETIME field with the help of DATE() function in MySQL.The syntax is as follows −select *from yourTableName where DATE(yourColumnName) = ’anyDateString’;To understand the above syntax, let us create a table and set some datetime values in the table. The query to create a table −mysql> create table DateTimeDemo −> ( −> ArrivalTime datetime −> ); Query OK, 0 rows affected (0.61 sec)Let us insert some records in the table with the help of insert command. The following is the query to insert records ... Read More

To get difference between two timestamps in seconds, use two in-built functions TIME_TO_SEC() and TIMEDIFF() in MySQL. The syntax is as follows −select time_to_sec(timediff(yourCoulnName1, yourCoulnName2)) as anyVariableName from yourTableName;To understand the above concept, let us first create a table. The query to create a table.mysql> create table TimeToSecond −> ( −> MyTime timestamp, −> YourTime timestamp −> ); Query OK, 0 rows affected (0.48 sec)Now you can insert some datetime values in the table. The query is as follows −mysql> insert into TimeToSecond values('2016-05-10 10:02:00', '2016-05-10 10:00:00'); Query ... Read More

To concatenate two columns, use CONCAT() function in MySQL. The syntax is as follows −select CONCAT(yourColumnName1, ' ', yourColumnName2) 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 concatenateTwoColumnsDemo    −> (    −> StudentId int,    −> StudentName varchar(200),    −> StudentAge int    −> ); Query OK, 0 rows affected (1.06 sec)Now you can insert some records in the table. The query to insert records is as follows −mysql> insert into concatenateTwoColumnsDemo values(1, 'Sam', 21); Query OK, 1 row affected (0.18 sec) ... Read More

To front pad zip code with 0, use LPAD() function in MySQL. The syntax is as follows −SELECT LPAD(yourColumnName, columnWidth+1, '0') as anyVariableName from yourTableName;To understand the above concept of LPAD() to add front pad zip code with 0, let us create a table. One of the columns of the table is Zip Code. The following is the query to create a table.mysql> create table ZipCodePadWithZeroDemo    −> (    −> Name varchar(200),    −> YourZipCode int(6)    −> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table. The query to insert records is as follows ... Read More

To check how many users are present in MySQL, use MySQL.user table. The syntax is as follows to check how many users are present.mysql> SELECT User FROM mysql.user;The following output displays the users −+------------------+ | User | +------------------+ | Mac | | Manish | | mysql.infoschema | | mysql.session | | mysql.sys | | root ... Read More

To select MySQL based on month and year, use in-built function YEAR() and MONTH(). The syntax is as follows −select *from yourTableName where YEAR(yourColumnName) = YearValue AND MONTH(yourColumnName) = monthValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectDataOnYearandMonthDemo −> ( −> BookId int, −> BookName varchar(100), −> BookDueDate datetime −> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table. The query is as ... Read More