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
Database Articles
Page 445 of 547
Adding new column after a specific column and defining a default in MySQL?
You need to follow some steps to add a new column after a specific column and defining default value. In order to achieve this, you need to use ALTER command. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20), StudentAge int, StudentCountryName varchar(100) ); Query OK, 0 rows affected (0.21 sec)Let us check the description of table −mysql> desc DemoTable;This will produce the following output −+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | ...
Read MoreHow to store time created in a MySQL table?
You can use DEFAULT CURRENT_TIMESTAMP. Keep in mind that it will work only at the time of insertion. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Arrivaltime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); Query OK, 0 rows affected (0.31 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Arrivaltime) values('2018-01-31 10:34:56'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(Arrivaltime) values('2019-01-31 11:10:12'); Query OK, 1 row affected (0.04 sec)Display ...
Read MoreSelect random number from a specific list in MySQL?
You can use elt() along with rand() for this. Let us select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * 10) + 1, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000) AS random_value_from_listOfValues;This will produce the following output −+--------------------------------+ | random_value_from_listOfValues | +--------------------------------+ | 1000 | +--------------------------------+ 1 row in set (0.00 sec)Now we will run the query again to select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * ...
Read MoreDoes MySQL DROP TABLE completely remove the table or just the structure?
The DROP TABLE removes the table completely and also removes all data. If you want to remove all data completely and wants the table structure, then you can use TRUNCATE TABLE command. The TRUNCATE command will recreate the table.Let us first check the DROP TABLE. For that, we will first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.20 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Larry'); Query OK, 1 row affected (0.07 sec) ...
Read MoreMySQL query to search exact word from string?
To search exact word from string, use the below syntax −select *from yourTableName where yourColumnName regexp '(^|[[:space:]])yourWord([[:space:]]|$)';Let us first create a table −mysql> create table DemoTable ( Title text ); Query OK, 0 rows affected (0.23 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('This is the Introduction to Java'); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable values('This is the Introduction to MongoDB'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values('This is the Introduction to MySQL'); Query OK, 1 row affected (0.06 ...
Read MoreSelect and add result of multiplying two columns from a table in MySQL?
You can use aggregate function SUM() for this. Let us first create a table −mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerProductName varchar(100), CustomerProductQuantity int, CustomerPrice int ); Query OK, 0 rows affected (0.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-1', 5, 400); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-2', 3, 100); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-1', 2, 300); Query OK, 1 ...
Read MoreDisplay first selected row in MySQL?
You can use ORDER BY for this. Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.20 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(8); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(18); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(11); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(9); Query OK, 1 row ...
Read MoreCombine columns before matching it with LIKE in a single query in MySQL?
You can use CONCAT() function for this. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value1 varchar(10), Value2 varchar(10) ); Query OK, 0 rows affected (0.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value1, Value2) values('10', '345'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(Value1, Value2) values('14', '789'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(Value1, Value2) values('18', '234'); Query OK, 1 row affected (0.13 sec)Display all records from the table using ...
Read MoreSort a MySQL table column value by part of its value?
You can use ORDER BY RIGHT() for this. Let us first create a table −mysql> create table DemoTable ( UserId varchar(100) ); Query OK, 0 rows affected (0.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('User1234'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values('User9874'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values('User9994'); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable values('User1211'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('User1012'); Query OK, 1 ...
Read MoreMySQL query to get result by month and year based on condition?
You need to use OR condition along with WHERE clause. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, MonthNumber int, YearNumber int ); Query OK, 0 rows affected (0.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(MonthNumber, YearNumber) values(11, 2018); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(MonthNumber, YearNumber) values(3, 2019); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(MonthNumber, YearNumber) values(12, 2018); Query OK, 1 row affected (0.08 sec) mysql> insert ...
Read More