- 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
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
329 Views
To exactly count all rows, you need to use the aggregate function COUNT(*). The syntax is as follows −select count(*) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CountAllRowsDemo -> ( -> Id int, -> Name varchar(10), -> Age int -> ); Query OK, 0 rows affected (1.49 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into CountAllRowsDemo values(1, 'John', 23); Query OK, 1 row affected (0.15 ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
599 Views
There is no equivalent of ROW_NUMBER() in MySQL for inserting but you can achieve this with the help of variable. The syntax is as follows −SELECT (@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`, yourColumnName1, yourColumnName2, ...N FROM yourTableName ,(SELECT @yourVariableName:=0) AS anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowNumberDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
3K+ Views
To set column values as column names in the query result, you need to use a CASE statement.The syntax is as follows −select yourIdColumnName, max(case when (yourColumnName1='yourValue1') then yourColumnName2 else NULL end) as 'yourValue1', max(case when (yourColumnName1='yourValue2') then yourColumnName2 else NULL end) as 'yourValue2', max(case when yourColumnName1='yourValue3') then yourColumnName2 else NULL end) as 'yourValue3’, . . N from valueAsColumn group by yourIdColumnName order by yourIdColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table valueAsColumn -> ( -> UserId int, -> UserColumn1 varchar(10), -> ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
128 Views
To merge selects together, you need to use GROUP BY clause. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table MergingSelectDemo -> ( -> RoomServicesId int, -> RoomId int, -> ServiceId int -> ); Query OK, 0 rows affected (1.98 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into MergingSelectDemo values(10, 10, 10); Query OK, 1 row affected (0.29 sec) mysql> insert into MergingSelectDemo values(20, 10, 20); Query OK, 1 row affected ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
325 Views
After creating a user and giving all privileges to the user, you need to FLUSH PRIVILEGES to set up and want the new settings to work correctly.The syntax is as follows −FLUSH PRIVILEGES;Here is the query to create a new user which has the name ‘Bob’ in my case. The query to create a new user is as follows −mysql> CREATE USER 'Bob'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.56 sec)Now given all privileges to user Bob −mysql> GRANT ALL PRIVILEGES ON *.* TO 'Bob'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.23 sec)Now flush the privileges. ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
3K+ Views
You need to use MySQL event scheduler. It manages the execution of events as well as scheduling.First, you need to create a table. After that you can create a event that will schedule every single day.Let us create a table. The query to create a table is as follows −mysql> create table EventDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EventDateTime datetime -> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table using insert command. The query ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
4K+ Views
The syntax for multiple inserts for a single column in MySQL is as follows −INSERT INTO yourTableName(yourColumnName) values(‘yourValue1'), (‘yourValue2'), (‘yourValue3'), (‘yourValue4'), .........N;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InsertMultipleDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(10), -> UserRole varchar(20) -> , -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (3.14 sec)Now you can insert some records in the ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
104 Views
You need to use FIND_IN_SET() function to select MySQL rows in the order of IN clause. The syntax is as follows −SELECT yourVariableName.* FROM yourTableName yourVariableName WHERE yourVariableName.yourColumnName IN(value1, value2, ...N) ORDER BY FIND_IN_SET( yourVariableName.yourColumnName, 'value1, value2, ...N');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InDemo -> ( -> CodeId int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
545 Views
You need to use tinyint(1) unsigned NULL to store the value 0, 1 and null values. The syntax is as follows −yourColumnName TINYINT(1) UNSIGNED NULL;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StoreValue0and1orNULLDemo -> ( -> isDigit TINYINT(1) UNSIGNED NULL -> ); Query OK, 0 rows affected (0.63 sec)Now you can insert records 0, 1, and NULL in the table using insert command. The query is as follows −mysql> insert into StoreValue0and1orNULLDemo values(0); Query OK, 1 row ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
2K+ Views
You need to use DATE_FORMAT() for this. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%k:%i') as anyAliasName FROM yourTableName;You can use ‘%H:%i’ for the same result. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table TimeDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> LastLoginTime time -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into TimeDemo(LastLoginTime) values('09:30:35'); Query OK, 1 row affected ... Read More