- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
429 Views
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 More
1K+ Views
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 More
108 Views
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 More
66 Views
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 More
194 Views
Let us first create a table. After that we will create a new random value column and order the record randomly:mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(StudentName) values('Larry'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentName) values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName) values('Mike'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable(StudentName) values('Carol'); Query OK, 1 ... Read More
1K+ Views
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 More
255 Views
You can use INFORMATION_SCHEMA.COLUMNS for this. Following is the syntax −SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='yourTableName';Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(60), ClientAge int, ClientSalary DECIMAL(10, 4), isRegularClient bool ); Query OK, 0 rows affected (0.44 sec)Following is the query to get field name types from a SQL database −mysql> SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DemoTable';This will produce the following output −+-----------------+---------------+ | COLUMN_NAME | COLUMN_TYPE | +-----------------+---------------+ | Id ... Read More
389 Views
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)
664 Views
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 More
155 Views
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 More