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 318 of 355
Which rows are returned while using LIMIT with OFFSET in MySQL?
Suppose the LIMIT is 4 and OFFSET is 6 then it will return the rows from 7 to 10 i.e. will end with row 10. The LIMIT 4 and OFFSET 6 returns row 7, 8, 9, 10.You can understand the above concept by implementing LIMIT and OFFSET. Let us create a table.mysql> create table LimitOffsettable -> ( -> Id int -> ); Query OK, 0 rows affected (0.60 sec)Let us insert some records in the table. The query is as follows −Mysql> insert into LimitOffsettable values(1); Query OK, 1 row affected ...
Read MoreMySQL create user if it does not exist?
You can create user if it does not exist with the help of “create user” command. The command will work on MySQL version 5.7.6 and above. The syntax is as follows −mysql> CREATE USER IF NOT EXISTS 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';Apply the above syntax to create a user if it does not exist. The query is as follows −mysql> CREATE USER IF NOT EXISTS 'Smith'@'localhost' IDENTIFIED BY 'Smith123456'; Query OK, 0 rows affected (0.29 sec)To check the new user is created or not, use the below query −mysql> SELECT User FROM mysql.user;The following is the output −+------------------+ | User ...
Read MoreHow to find MySQL my.cnf on my windows computer?
To find my.cnf on Windows system, first open the command prompt with the help of shortcut key Windows + R (run). The snapshot is as follows −Type “services.msc” on command prompt and press ENTER as shown in the following screenshot −Now, a new wizard will open. The snapshot is as follows −Now, search for MySQL. The snapshot is as follows −Right click on “MySQL80” and select “Properties” as in the following screenshot −As you can see in the above screenshot, “Path to executable” tells about the location of my.cnf on Windows.
Read MoreHow to part DATE and TIME from DATETIME in MySQL?
To part DATE and TIME from DATETIME, you can use the DATE_FORMAT() method from MySQL. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%Y-%m-%d') VariableName, DATE_FORMAT(yourColumnName, '%H:%i:%s') VariableName from yourTableName;To understand the above method DATE_FORMAT(), let us create a table with data type “datetime”.Creating a table −mysql> create table DateAndTimePartDemo -> ( -> YourDateandtime datetime -> ); Query OK, 0 rows affected (0.56 sec)Now, I am inserting current date and time using now(). The query is as follows −mysql> insert into DateAndTimePartDemo values(now()); Query OK, 1 row affected (0.37 sec)Display the record ...
Read MoreMySQL strip time component from datetime?
You can strip time component from datetime with the help of DATE() function. The syntax is as follows −SELECT DATE(yourColumnName) from yourTableName;To understand the above concept, let us first create a table −mysql> create table StripComponentDatetimeDemo -> ( -> YourDateTime datetime -> ); Query OK, 0 rows affected (0.60 sec)Let us now insert some records in the table. The query is as follows −mysql> insert into StripComponentDatetimeDemo values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.13 sec)Display records with the help of select statement. The query is as follows displaying ...
Read MoreBiggest value from two or more fields in MySQL?
To know the biggest value from two or more fields, use the function GREATEST() from MySQL.The syntax is as follows −SELECT GREATEST(MAX(yourColumnName1), MAX(yourColumnName2), ...............MAX(yourColumnName2) ) from yourTableName;Let us understand the above concept by creating a table with more than two columns −mysql> create table GreatestOfTwoOrMore -> ( -> Marks1 int, -> Marks2 int, -> Marks3 int -> ); Query OK, 0 rows affected (0.57 sec)Here is the query to insert records in a table −mysql> insert into GreatestOfTwoOrMore values(23, 78, 89); Query OK, 1 row ...
Read MoreALTER table by adding AUTOINCREMENT in MySQL?
To add AUTOINCREMENT in MySQL, you can use the ALTER command.ALTER TABLE yourTableName change yourColumName yourColumnName dataType AUTO_INCREMENT PRIMARY KEY;To understand the above concept, create a table with a column. Let us create a table −mysql> create table AlterTableToAddAutoIncrement -> ( -> StudentId int -> ); Query OK, 0 rows affected (0.57 sec)Implement the above syntax to change “StudentId” with AUTOINCREMENT. The query is as follows −mysql> alter table AlterTableToAddAutoIncrement change StudentId StudentId int AUTO_INCREMENT Primary key; Query OK, 0 rows affected (1.93 sec) Records: 0 Duplicates: 0 Warnings: 0We ...
Read MoreMySQL Select IN range?
You cannot do select IN range. For the same result, use BETWEEN. Let us see an example −IN(start, end): It means that the intermediate value between start and end won’t get displayed. For the above logic, you can use BETWEEN.BETWEEN clause is inclusive, for example, suppose there are 1, 2, 3, 4, 5, 6 numbers. If you want to display numbers from 2 to 6 inclusively, then using BETWEEN the numbers 2 and 6 will also get displayed.Let us create a table −mysql> create table SelectInWithBetweenDemo -> ( -> PortalId int ...
Read MoreIs there a way to know your current username in MySQL?
Yes, you can use the method CURRENT_USER() to know the current username in MySQL.The above method returns the username that can be used to authenticate the client connection.The query is as follows −mysql> select CURRENT_USER();The following is the output −+----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec)Or you can use USER() method from MySQL. The query is as follows −mysql> select user();Here is the output −+----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
Read MoreHow to implement ternary conditional operator in MySQL?
A ternary conditional operator looks like ?: in programming language like C, C++, Java etc. The syntax is as follows −(yourCondition) ? statement1:statement2;In the above syntax, if yourCondition becomes true then statement1 will evaluate and if yourCondition becomes false then statement2 will evaluate.But the above syntax does not work in MySQL. We can use IF() function from MySQL for the same purpose.Let us see an example −Case 1mysql> select if(3 > 5, 'Condition is true', 'Condition is not true') as ConditionalResult;The following is the output in which second statement evaluates since is 3 isn’t more than 5 −+-----------------------+ | ConditionalResult ...
Read More