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
MySQL Articles
Page 320 of 355
Does MySQL Boolean "tinyint(1)" holds values up to 127?
Let us learn some points about TINYINT type in MySQL −The TINYINT type takes 1 byte i.e. 8 bits.The TINYINT(N), where N indicates the display width you want.For example, TINYINT(1) can be used to display width which is 1.Let us learn about the minimum and maximum values −The maximum value for tinyint is= (2(8-1)-1) = 127 The minimum value for tinyint is = -(2(8-1)) = -128.The value will be between -128 to 127. This means TINYINT (1) does not affect the maximum and minimum value of tinyint.Let us check it −Firstly, create a table with a column set as TINYINT ...
Read MoreHow to ORDER BY RELEVANCE in MySQL?
To order by relevance, use the case statement. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table OrderByRelevance -> ( -> UserId int, -> UserName varchar(200) -> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByRelevance values(101, 'Carol Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into OrderByRelevance values(102, 'Carol Adams'); Query OK, 1 row affected (0.17 sec) mysql> insert into ...
Read MoreIs BIGINT(8) the largest integer MySQL can store?
In BIGINT(8), the number 8 represents how the data will be displayed. It does not affect the storage. The number is used to display width.BIGINT takes 8 bytes i.e. 64 bits. The signed range is -9223372036854775808 to 9223372036854775807 and unsigned range takes positive value. The range of unsigned is 0 to 18446744073709551615.To understand bigint(8), let us create a table with BIGINT(8) and zerofill column −mysql> create table BigIntDemo8 -> ( -> Number1 BIGINT(8) not null, -> Number2 BIGINT(8) unsigned zerofill not null -> ); Query OK, 0 rows ...
Read MoreHow to insert data to MySQL having auto incremented primary key?
Whenever your column has an auto incremented primary key then there is an advantage that you do not need to give value for that column in the INSERT command. This means MySQL will give the value for that column.To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table AutoIncrementedPrimary -> ( -> Id int auto_increment, -> Name varchar(100), -> Age int, -> Primary key(Id) -> ); Query OK, 0 rows affected (0.56 sec)Now insert records for the column Name and Age ...
Read MoreChange One Cell's Data in MySQL?
Update only one cell’s data with the help of UPDATE command. The syntax is as follows −UPDATE yourTableName yourColumnName=yourNewValue where yourColumnName=yourOldValue;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table changeCellsData -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into changeCellsData values(101, 'Mike', 23); Query OK, 1 row affected (0.12 sec) mysql> insert into ...
Read MoreHow 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 More