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 331 of 547
Update the records in a table with a specific year fetched from date format like '10/12/2010'?
To update records with a specific year, use the YEAR() method as in the below syntax:update yourTableName set yourColumnName1=yourValue1 where YEAR(str_to_date(yourColumnName2, '%d/%m/%Y'))=yourValue2;Let us first create a table −mysql> create table DemoTable1924 ( UserName varchar(20), UserJoiningDate varchar(40) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1924 values('Chris', '10/12/2010'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1924 values('David', '20/01/2011'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1924 values('Mike', '20/01/2010'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1924 values('Carol', ...
Read MoreFix a specific column value and display random values for rest of the rows in MySQL
For random rows, you can use RAND(), whereas to fix a specific column, use ORDER BY clause. Let us create a table −mysql> create table DemoTable1921 ( Number int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1921 values(40); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1921 values(80); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1921 values(820); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1921 values(10); Query OK, 1 row affected (0.00 sec)Display all records from the ...
Read MoreGroup the marks of a particular student from a table and display total marks in a separate column for each student?
To group marks, use MySQL GROUP BY. To sum, use MySQL sum()function. Let us first create a table −mysql> create table DemoTable1920 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1920 values('Chris', 67); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1920 values('David', 97); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1920 values('Chris', 57); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1920 values('David', 45); Query OK, 1 row affected (0.00 sec) mysql> ...
Read MoreFix: ERROR 1396 (HY000): Operation CREATE USER failed in MySQL?
To fix the error, let us see how to create a user correctly. Let us create a user −mysql> create user 'Emma'@'localhost' IDENTIFIED BY 'emma_654'; Query OK, 0 rows affected (0.00 sec)Let us display all users along with host −mysql> select user, host from MySQL.user;This will produce the following output. The new user created above is visible in the below list of all users along with host −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Charlie ...
Read MoreQuery the database for the values not in the MySQL table?
For this, you can use UNION ALL along with WHERE NOT EXISTS and implement NOT IN to ignore the values already in the table. Use SELECT with UNION ALL to add values not already in the table.Let us first create a table −mysql> create table DemoTable1918 ( Value int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into ...
Read MoreGet all rows apart from first and last in MySQL
To get all rows apart from first and last, use subquery along with MIN() and MAX(). Let us first create a table −mysql> create table DemoTable1917 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentCode int, StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 95); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 96); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 97); Query OK, 1 row affected (0.00 ...
Read MoreWrite MySQL case statement to set custom messages for student's result
For this, set conditions using MySQL CASE statement −mysql> create table DemoTable1916 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1916 values('Chris', 59); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('David', 89); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Sam', 94); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Mike', 75); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Carol', 69); Query OK, 1 row affected ...
Read MoreMySQL: How can I find a value with special character and replace with NULL?
For this, use SET yourColumnName = NULL as in the below syntax −update yourTableName set yourColumnName=NULL where yourColumnName=yourValue;Let us first create a table −mysql> create table DemoTable1914 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Code varchar(20) )AUTO_INCREMENT=1001; Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1914(Code) values('John101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('234David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('100_Mike'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select ...
Read MoreHow to append 000 in a MySQL column value?
To append 000, use the concept of ZEROFILL. Let us first create a table −mysql> create table DemoTable1913 ( Code int(4) ZEROFILL AUTO_INCREMENT NOT NULL, PRIMARY KEY(Code) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1913 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(4); Query OK, 1 row affected (0.00 sec)Display all records from the table ...
Read MoreHow to update User Logged in Time for a specific user in MySQL?
For this, use ORDER BY along with LIMIT. Let us first create a table wherein we have a column with User id, logged in time, and name −mysql> create table DemoTable1911 ( UserId int, UserLoggedInTime time, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1911 values(100, '7:32:00', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(101, '5:00:00', 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(102, '6:10:20', 'Mike'); Query OK, 1 row affected (0.00 ...
Read More