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
Database Articles
Page 511 of 546
How can I loop through all rows of a table in MySQL?
To loop through all rows of a table, use stored procedure in MySQL. The syntax is as follows −delimiter // CREATE PROCEDURE yourProcedureName() BEGIN DECLARE anyVariableName1 INT DEFAULT 0; DECLARE anyVariableName2 INT DEFAULT 0; SELECT COUNT(*) FROM yourTableName1 INTO anyVariableName1; SET anyVariableName2 =0; WHILE anyVariableName2 < anyVariableName1 DO INSERT INTO yourTableName2(yourColumnName, ...N) SELECT (yourColumnName1, ...N) FROM yourTableName1 LIMIT anyVariableName2, 1; SET anyVariableName2 = anyVariableName2+1; END WHILE; End; //To understand the above syntax, let us create two tables i.e. one has records and the second table will have records from the loop using stored procedures.The following is the query ...
Read MoreMySQL Select to get users who have logged in today?
To get the users logged in today, use the below syntax. Here, we are expecting that your datetime field is a string type −select yourColumnName1, yourColumnName2, yourColumnName3, ...N from youTableName WHERE STR_TO_DATE(yourColumnName1, ‘format’') =CURDATE();Let’s say we have the following “DateEqualToday “ table that stores users first and last name with the login date −+------+------------+-----------+------------+ | Id | First_Name | Last_Name | LoginDate | +------+------------+-----------+------------+ | 1 | James | Smith | 20-12-2018 | | 2 | Carol | Taylor | 21-12-2017 | | 3 | John ...
Read MoreHow to use FOR LOOP in MySQL Stored Procedure?
The following is the syntax to work with FOR LOOP in MySQL stored procedure −delimiter // CREATE procedure yourProcedureName() wholeblock:BEGIN DECLARE anyVariableName1 INT ; Declare anyVariableName3 int; DECLARE anyVariableName2 VARCHAR(255); SET anyVariableName1 =1 ; SET anyVariableName3 =10; SET anyVariableName2 = ''; loop_label: FORLOOP IF anyVariableName1 > anyVariableName3 THEN LEAVE loop_label; END IF; SET anyVariableName2 = CONCAT(anyVariableName2 ,anyVariableName1 ,', '); SET anyVariableName1 = anyVariableName1 + ...
Read MoreHow to Reset MySQL AutoIncrement using a MAX value from another table?
You can use prepare statement to Reset MySQL AutoIncrement using a MAX value from another table.The following is the syntax −set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1); SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2 AUTO_INCREMENT=', @anyVariableName1); PREPARE yourStatementName FROM @anyVariableName2; execute yourStatementName;The above syntax will reset MySQL auto_increment using a maximum value from another table. To understand the above syntax, let us create two tables. The first table will contain the records and the second table will use the maximum value from the first table and use for an auto_increment property.The query to create a table is as follows −mysql> create table FirstTableMaxValue ...
Read MoreHow to select record from last 6 months in a news table using MySQL?
To select the last 6 months records from news table, use the date_sub() function from MySQL since news records are arranged according to date.The syntax is as follows −select *from yourTableName where yourDateTimeColumnName >= date_sub(now(), interval 6 month);To understand the above concept, let us first create a NEWS table with only NEWS ID and the date on which it published −mysql> create table Newstable -> ( -> NewsId int, -> NewsDatetime datetime -> ); Query OK, 0 rows affected (0.66 sec)Insert records in the table using insert command. ...
Read MoreHow to create JSON format with group-concat in MySQL?
You can create JSON format using group_concat() function from MySQL. The syntax is as follows −SELECT yourColumnName1, GROUP_CONCAT(CONCAT('{anytName:"', yourColumnName, '", anyName:"', yourColunName, '"}')) anyVariableName from yourTableName group by yourColumnName1;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table JsonFormatDemo -> ( -> UserId int, -> UserName varchar(100), -> UserEmail varchar(100) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert ...
Read MoreCan you recommend a free light-weight MySQL GUI for Linux?
You can use phpMyAdmin, since it is one of the best free tools. This can be used for every system with PHP and MySQL. It is a free and open source administration tool for MySQL and MariaDB. PHPMYADMINHere is the URL to download −https://www.phpmyadmin.net/downloads/The following are the features of phpMyAdmin −Open source toolMySQL and MariaDB database management.One of the most popular MySQL administration toolsEasily Import data from CSV and SQLExport data to various formats. These include : CSV, SQL, XML, PDF, Word, Excel, LaTeX, etc.Administering multiple serversYou can also use EMMA. This is also a light-weight application.
Read MoreMySQL increment a database field by 1?
You can increment a database using update command. The syntax is as follows −UPDATE yourTableName set yourColumnName=yourColumnName+1 where condition;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table IncrementBy1 -> ( -> Id int, -> Name varchar(100), -> CounterLogin int -> ); Query OK, 0 rows affected (0.63 sec)Insert some records using insert command. The query to insert records in the table is as follows −mysql> insert into IncrementBy1 values(100, 'John', 30); Query OK, 1 row affected (0.17 sec) mysql> insert ...
Read MoreHow to form a MySQL Conditional Insert?
For this, you can insert using MySQL dual table. Let us create a table to understand the concept conditional insert. The query to create a table is as follows −mysql> create table ConditionalInsertDemo -> ( -> UserId int, -> TotalUser int, -> NumberOfItems int -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ConditionalInsertDemo values(101, 560, 780); Query OK, 1 row affected (0.19 sec) mysql> insert into ConditionalInsertDemo values(102, 660, 890); Query OK, 1 row affected (0.20 sec) ...
Read MoreHow do I add more members to my ENUM - type column in MySQL?
You can use alter command. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumNam enum(yourOldValue1, yourOldValue2, ....N, yourNewValue1, yourNewValue2, ....N);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> AllColors enum('RED', 'GREEN') -> ); Query OK, 0 rows affected (0.66 sec)Now you have two members in AllColors of enum type. If you want to add more members to your enum type, use the following query −mysql> alter table EnumDemo -> modify column ...
Read More