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
MySQL Articles
Page 302 of 355
Display IDs in a particular order with MySQL IN()?
To display IDs in a particular order i.e. the order of your choice use FIELD() method.Let us first create a table −mysql> create table DemoTable ( UserId int ); Query OK, 0 rows affected (0.64 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.13 sec) ...
Read MoreAdd a temporary column in MySQL where the values depend on another column?
You can use CASE statement for this and set conditions to get result in the temporary column.Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(20), EmployeeSalary int, EmployeeExperience int ); Query OK, 0 rows affected (0.64 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, EmployeeSalary, EmployeeExperience) values('Larry', 4500, 5); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary, EmployeeExperience) values('Mike', 130000, 8); Query OK, 1 row affected (0.21 sec) mysql> ...
Read MoreHow to convert all the records in a MySQL table from uppercase to lowercase?
Convert all the records in a MySQL table from uppercase to lowercase using UPDATE command along with LOWER() method.Let us first create a table −mysql> create table DemoTable ( Id varchar(100), StudentFirstName varchar(20), StudentLastName varchar(20), StudentCountryName varchar(10) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-101', 'John', 'Smith', 'US'); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values('STU-102', 'John', 'Doe', 'UK'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('STU-103', 'David', 'Miller', 'AUS'); Query OK, 1 ...
Read MoreSelect current time with MySQL now() and convert it to GMT 0?
You can use utc_timestamp() for this. Following is the syntax −SELECT utc_timestamp();Let us check the current time using now().Following is the query −mysql> select now();This will produce the following output −+---------------------+ | now() | +---------------------+ | 2019-04-11 17:50:05 | +---------------------+ 1 row in set (0.00 sec)Following is the query to get GMT 0 −mysql> SELECT utc_timestamp();This will produce the following output −+---------------------+ | utc_timestamp() | +---------------------+ | 2019-04-11 12:20:08 | +---------------------+ 1 row in set (0.00 sec)
Read MoreHow to check if a table exists in MySQL and create if it does not already exist?
If you try to create a table and the table name already exist then MySQL will give a warning message. Let us verify the concept.Here, we are creating a table that already exist −mysql> CREATE TABLE IF NOT EXISTS DemoTable ( CustomerId int, CustomerName varchar(30), CustomerAge int ); Query OK, 0 rows affected, 1 warning (0.05 sec)The table name DemoTable is already present. Let us check the warning message.Following is the query −mysql> show warnings;This will produce the following output i.e. the warning message −+-------+------+------------------------------------+ | Level | Code | Message ...
Read MoreIs it possible to divide records in both ascending and descending order in MySQL and display them alternatively?
Yes, you can perform this in MySQL by first getting the middle value. Let us first create a table:mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK, 0 rows affected (0.65 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.16 sec) mysql> ...
Read MoreDrop a MySQL Table after x hours?
You need to create event to drop table after x hours. The syntax is as follows −CREATE EVENT yourEventName ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL x HOUR DO DROP TABLE IF EXISTS yourTableName;Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudnetFirstName varchar(20), StudentLastName varchar(20), StudnetAge int ); Query OK, 0 rows affected (0.52 sec)Now implement the above event in order to drop table after 2 hours −mysql> CREATE EVENT drop_table_event_after2HoursDemo ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR DO DROP TABLE IF EXISTS DemoTable; Query OK, ...
Read MoreCounting number of positive and negative votes in MySQL?
To count number of positive and negative votes, you can use CASE statement along with aggregate function SUM().Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Vote int ); Query OK, 0 rows affected (1.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Vote) values(-10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Vote) values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Vote) values(45); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Vote) values(-6); Query OK, 1 row ...
Read MoreHow to set default date time as system date time in MySQL?
You can use CURRENT_TIMESTAMP to set system date time. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientFirstName varchar(20), ClientLastName varchar(20), ClientAge int ); Query OK, 0 rows affected (0.66 sec)Following is the query to set default datetime as system date time in MySQL −mysql> alter table DemoTable add column ClientProjectDeadline timestamp default current_timestamp; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the description of table once again −mysql> desc DemoTable;This will produce the following output −+-----------------------+-------------+------+-----+-------------------+----------------+ | Field ...
Read MoreHow to extract part of a URL in MySQL?
You need to use SUBSTRING_INDEX() function from MySQL to extract part of a URL. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, URL text ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(URL) values('https:\www.example.com\homepage'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(URL) values('https:\www.onlinetest.com\welcome\indexpage'); Query OK, 1 row affected (0.12 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output. Here, we can ...
Read More