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
MySQLi Articles
Page 183 of 341
MySQL edit and update records including employee salary
The UPDATE command is used in MySQL to update records. With it, the SET command is used to set new values. Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(50), EmployeeSalary int ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Chris', 56780); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Robert', 45670); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Mike', 87654); Query OK, 1 ...
Read MoreDoes using SERIAL as column name already includes 'NOT NULL' in MySQL?
In MySQL, SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. So, do not use NOT NULL after SERIAL, since it already includes NOT NULL as part of its definition.Let us see an example and create a table. Here, we have a column with the name “serial” −mysql> create table DemoTable ( Id serial ); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command. We aren’t including any value while inserting −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(); Query OK, 1 ...
Read MoreHow to delete rows older than 14 days in MySQL?
To delete, use MySQL DELETE. However, to get records older than 14 days, subtract the current date with date interval of 14 days. The syntax for the same is shown below −delete from yourTableName where yourColumnName< (curdate() - interval 14 day);Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, DueDate date ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(DueDate) values('2019-08-20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(DueDate) values('2019-08-19'); Query OK, 1 row affected ...
Read MoreCreate a table inside a MySQL stored procedure and insert a record on calling the procedure
Create a table inside the stored procedure and use INSERT as well −mysql> DELIMITER // mysql> CREATE PROCEDURE create_TableDemo(id int,name varchar(100),age int) BEGIN CREATE TABLE DemoTable ( ClientId int NOT NULL, ClientName varchar(30), ClientAge int, PRIMARY KEY(ClientId) ); INSERT INTO DemoTable VALUES(id,name,age); SELECT *FROM DemoTable; END // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Call the stored procedure using CALL command −mysql> CALL create_TableDemo(100,'Robert',28);This will produce the following output −+----------+------------+-----------+ | ClientId | ClientName | ClientAge | +----------+------------+-----------+ | 100 | Robert | 28 | +----------+------------+-----------+ 1 row in set (0.76 sec) Query OK, 0 rows affected (0.78 sec)
Read MoreHow to find last date from records with date values in MySQL?
To get the last date i.e. the latest, use aggregate function MAX() with a subquery. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ExpiryDate date ); Query OK, 0 rows affected (1.40 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ExpiryDate) values('2018-12-31'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(ExpiryDate) values('2019-09-01'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(ExpiryDate) values('2019-09-01'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(ExpiryDate) values('2016-08-30'); Query OK, 1 row affected (0.13 ...
Read MoreIs it compulsory to set PRIMARY KEY for AUTO_INCREMENT value?
Yes, use AUTO_INCREMENT with PRIMARY KEY. Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT, EmployeeName varchar(40), EmployeeAge int, PRIMARY KEY(EmployeeId), UNIQUE KEY(EmployeeName, EmployeeAge) ); Query OK, 0 rows affected (0.96 sec)Let us check the table description of the table −mysql> desc DemoTable;This will produce the following output −+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | EmployeeId | int(11) | NO | PRI | NULL ...
Read MoreMySQL query to find the average of only first three values from a column with five values
For this, you can use a subquery. Let us first create a table −mysql> create table DemoTable ( Score int ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(45); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(55); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.13 sec)Display all records from ...
Read MoreMySQL query to display record with maximum count values in a group with other column values?
For this, use the GROUP BY HAVING clause. Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(99); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(99); Query OK, 1 row affected (0.09 sec) mysql> insert ...
Read MoreHow to count specific comma separated values in a row retrieved from MySQL database?
To count comma-separated-values, use aggregate function COUNT(*) along with FIND_IN_SET(). Let us first create a table −mysql> create table DemoTable ( Value varchar(100) ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10, 20, 60, 80'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('60, 70, 90'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('50, 55, 65, 60'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('90, 98, 97'); Query OK, 1 row affected (0.12 sec)Display all records ...
Read MoreIs there anything like substr_replace in MySQL?
For this, use the INSERT() function from MySQL. The INSERT(str, pos, len, newstr) returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string.It replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.Let us first create a table −mysql> create table DemoTable ( Password varchar(50) ); Query OK, 0 rows affected (0.51 sec)Insert some records in ...
Read More