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 419 of 547
MySQL query to set current date in the datetime field for all the column values
Let us first create a table −mysql> create table DemoTable821(AdmissionDate datetime); Query OK, 0 rows affected (1.24 sec)Insert some records in the table using insert command −mysql> insert into DemoTable821 values('2019-01-21'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable821 values('2018-11-02'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable821 values('2016-12-31'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable821 values('2015-03-19'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable821;This will produce the following output −+---------------------+ | AdmissionDate ...
Read MoreHow to limit records to only the last five results in MySQL
To fetch only the last five records below is the syntax −select *from yourTableName order by yourColumnName DESC LIMIT 5;Let us first create a table −mysql> create table DemoTable820( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable820(CustomerName) values('Chris'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable820(CustomerName) values('Robert'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable820(CustomerName) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable820(CustomerName) values('Bob'); Query OK, 1 ...
Read MoreCheck user rights before attempting to CREATE MySQL DATABASE?
To display grants, the syntax is as follows −show grants for yourUserName;Let us implement the above syntax in order to check user rights before attempting to create database.Case 1 −This is for current user which is root. Following is the syntax −mysql> show grants for current_user;This will produce the following output −+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% ...
Read MoreHow to select from table where conditions are set for id and name in MySQL?
Let us first create a table −mysql> create table DemoTable819( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100) ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable819(StudentName) values('Chris'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable819(StudentName) values('Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable819(StudentName) values('Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable819(StudentName) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable819(StudentName) values('Sam'); Query OK, 1 row affected (0.16 sec)Display all records from ...
Read MoreMySQL query to convert YYYY-MM-DD to DD Month, YYYY date format
Let us first create a table −mysql> create table DemoTable845(AdmissionDate date); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable845 values('2018-01-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2016-12-12'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2019-08-05'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable845 values('2019-10-15'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable845;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2018-01-21 | ...
Read MoreCan we convert MD5 to SHA256 in a MySQL table with user password column?
Use SHA2() to convert the MD5 password to SHA256. It calculates the SHA-2 family of hash functions i.e. SHA-224, SHA-256, SHA-384, and SHA-512).Let us first create a table −mysql> create table DemoTable818(UserPassword text); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable818 values(MD5('John_123')); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable818 values(MD5('999Carol@22')); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable818;This will produce the following output −+----------------------------------+ | UserPassword ...
Read MoreHow to display only the column values whose sum is less than 150 in MySQL? Arrange the result in descending order
For this, you can use subquery. Let us first create a table −mysql> create table DemoTable844( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Amount int ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable844(Amount) values(80); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable844(Amount) values(100); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable844(Amount) values(60); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable844(Amount) values(40); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable844(Amount) values(150); Query OK, 1 row ...
Read MoreSet value only for NULL values in a MySQL table
Use IFNULL to check for NULL values and set a value using the SET command. Let us first create a table −mysql> create table DemoTable817(Value int); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable817 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable817 values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreDisplay all grants for user in MySQL
Use INFORMATION_SCHEMA.SCHEMA_PRIVILEGES to display all grants for a user −select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;Let us implement the above syntax to show all grants for a user −mysql> select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;This will produce the following output −+-----------------------------+---------------+--------------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +-----------------------------+---------------+--------------------+-------------------------+--------------+ | 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO ...
Read MoreSet particular value of column without using update and while inserting values in MySQL
Here, we will see an example wherein we are inserting dates and updating them while using the INSERT query.Let us first create a table −mysql> create table DemoTable816(DueDate datetime); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using the insert command. Here is the query to add (minutes/hours/days/months/ years) to date when performing INSERT −mysql> insert into DemoTable816 values(date_add(now(), interval 3 minute)); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable816 values(date_add('2018-01-21 00:00:00', interval 3 Hour)); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable816 values(date_add('2016-11-11 12:40:00', interval 3 Day)); Query OK, ...
Read More