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
MySQLi Articles
Page 48 of 341
MySQL query to select records with a particular date?
You can use DATE() from MySQL to select records with a particular date. The syntax is as follows.SELECT *from yourTableName WHERE DATE(yourDateColumnName)=’anyDate’;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AllRecordsFromadate -> ( -> Id int, -> Name varchar(100), -> Age int, -> AdmissionDate datetime -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query to insert records is as follows.mysql> insert into AllRecordsFromadate values(101, 'John', 23, '2018-10-13'); Query OK, 1 row affected (0.18 sec) mysql> insert ...
Read MoreHow to add columns at specific position in existing table in MySQL?
To add columns at a specific position in existing table, use after command. The syntax is as follows −ALTER TABLE yourTableName ADD COLUMN yourColumnName data type AFTER yourExistingColumnName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table changeColumnPosition -> ( -> Id_Position1 int, -> Name_Position2 varchar(100), -> Address_Position4 varchar(200) -> ); Query OK, 0 rows affected (0.53 sec)Now you can check the description of existing table using desc command. The syntax is as follows −desc yourTableName;The following is the query to check the description.mysql> desc changeColumnPosition;The following is ...
Read MoreChange the Auto Increment counter in MySQL?
In MySQL, auto increment counter starts from 0 by default, but if you want the auto increment to start from another number, use the below syntax.ALTER TABLE yourTable auto_increment=yourIntegerNumber;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table startAutoIncrement -> ( -> Counter int auto_increment , -> primary key(Counter) -> ); Query OK, 0 rows affected (0.90 sec)Implement the above syntax to begin auto increment from 20. The query is as follows.mysql> alter table startAutoIncrement auto_increment=20; Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: ...
Read MoreIs there any whoami function or command in MySQL like UNIX?
There is no whoami function in MySQL. The whoami can be used to know the current user in UNIX. Use user() or current_user() function from MySQL for the same purpose.The following is the output.+-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)Case 1 −Using CURRENT_USER() function.The query to know the current user is as follows.mysql> select current_user();The following is the output.+----------------+ | current_user() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec)Case 2 − Using USER() function.The query is as follows −mysql> select user();The following is ...
Read MoreSelect values that meet different conditions on different rows in MySQL?
You can select values that meet different conditions on different rows using IN() and GROUP BY. The syntax is as follows −SELECT yourColumnName1 from yourTableName WHERE yourColumnName2 IN(value1, value2, .....N) GROUP BY yourColumnName1 HAVING COUNT(DISTINCT yourColumnName2)=conditionValue;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table DifferentRows -> ( -> FirstRow int, -> SecondRow int -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferentRows values(10, 10); Query OK, 1 row affected ...
Read MoreSELECT increment counter in MySQL?
To select increment counter in MySQL, first you need to declare and initialize a variable. The syntax is as follows −set @anyVariableName=0; select yourColumnName, @anyVariableName:=@anyVariableName+1 as anyVariableName from yourTableName;To understand the above syntax and set an increment counter, let us first create a table. The query to create a table is as follows.mysql> create table incrementCounterDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into incrementCounterDemo values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into incrementCounterDemo values('Carol'); ...
Read MoreMySQL update with random number between 1 - 3
The syntax for updating a column with random number between 1-3 is is as follows −update yourTableName set yourColumnName=FLOOR(1+RAND()*3);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UpdateNumber1To3 -> ( -> MyNumber int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UpdateNumber1To3 values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into UpdateNumber1To3 values(140); Query OK, 1 row affected (0.25 sec) mysql> insert into UpdateNumber1To3 values(130); ...
Read MoreHow to do a count on a MySQL union query?
To do a count on a union i.e. to get the count of the UNION result, use the below syntax −SELECT COUNT(*) FROM ( SELECT yourColumName1 from yourTableName1 UNION SELECT yourColumName1 from yourTableName2 ) anyVariableName;To understand the above syntax, let us create two tables with some records. The query to create a table is as follows −mysql> create table union_Table1 -> ( -> UserId int -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into union_Table1 values(1); Query OK, 1 row affected (0.18 sec) ...
Read MoreWhen running UPDATE … datetime = NOW(); will all rows updated have the same date/ time in mysql?
The now() function returns the constant time that exhibits the time at which any statement began to execute. The sysdate() function returns the exact same datetime at which it executed the statement from MySQL 5.0.13.Suppose if you are updating datetime with now() in triggers or stored procedure, the now() method returns the time at which time the triggering and stored procedure begin to execute.Here is the demo of update with now(). Let us first create a table. The query to create a table is as follows −mysql> create table NowDemo -> ( -> DueDateTime datetime -> ); Query OK, 0 ...
Read MoreHow to insert date in single quotes with MySQL date formats?
To insert the date with date formats, use the str_to_date() function with date in single quotes. The following is the syntax −insert into yourTableName values(Value1, value2, ......ValueN, str_to_date(‘anyDate’, ’%Y-%m-%d’));Here are the Date Formats in MySQL −FormatDescription%aAbbreviated weekday name (Sun to Sat)%bAbbreviated month name (Jan to Dec)%cNumeric month name (0 to 12)%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)%dDay of the month as a numeric value (01 to 31)%eDay of the month as a numeric value (0 to 31)%fMicroseconds (000000 to 999999)%HHour (00 to 23)%hHour (00 to 12)%IHour (00 to 12)%iMinutes (00 to 59)%jDay ...
Read More