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 280 of 341
How to find capital letters with Regex in MySQL?
You can use REGEXP BINARY for thisselect *from yourTableName where yourColumnName REGEXP BINARY '[A-Z]{2}';Let us first create a tablemysql> create table FindCapitalLettrsDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentFirstName varchar(20) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into FindCapitalLettrsDemo(StudentFirstName) values('JOHN'); Query OK, 1 row affected (0.24 sec) mysql> insert into FindCapitalLettrsDemo(StudentFirstName) values('Carol'); Query OK, 1 row affected (0.15 sec) mysql> insert into FindCapitalLettrsDemo(StudentFirstName) values('bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreHow to retrieve a random row or multiple random rows in MySQL?
You can use RAND() method for this. To retrieve a random row, use the following syntaxSELECT *FROM yourTableName ORDER BY RAND() LIMIT yourIntegerNumber;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table gettingRandomRow -> ( -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CustomerName varchar(100) -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into gettingRandomRow(CustomerName) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into gettingRandomRow(CustomerName) values('Robert'); ...
Read MoreMySQL command-line tool: How to find out number of rows affected by a DELETE?
You can use row_count() at the end for this. Let us first create a table −mysql> create table rowAfftectedByDeleteDemo -> ( -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CustomerName varchar(20) -> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into rowAfftectedByDeleteDemo(CustomerName) values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into rowAfftectedByDeleteDemo(CustomerName) values('Carol'); Query OK, 1 row affected (0.10 sec) mysql> insert into rowAfftectedByDeleteDemo(CustomerName) values('Bob'); Query OK, 1 row affected (0.09 sec) mysql> insert into rowAfftectedByDeleteDemo(CustomerName) values('Sam'); Query ...
Read MoreReset the primary key to 1 after deleting all the data in MySQL?
To reset the primary key to 1 after deleting the data, use the following syntaxalter table yourTableName AUTO_INCREMENT=1; truncate table yourTableName;After doing the above two steps, you will get the primary key beginning from 1.To understand the above concept, let us create a table. The query to create a table is as followsmysql> create table resettingPrimaryKeyDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row ...
Read MoreMySQL GROUP BY date when using datetime?
To GROUP BY date while using datetime, the following is the syntax −select *from yourTableName GROUP BY date(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table groupByDateDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserPostDatetime datetime -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into groupByDateDemo(UserName, UserPostDatetime) values('Larry', '2018-01-02 13:45:40'); Query OK, 1 row affected (0.18 sec) mysql> insert ...
Read MoreMySQL UPDATE using IF condition
The syntax is as follows to perform UPDATE using IF condition in MySQL −update yourTableName set yourColumnName =if(yourColumnName =yourOldValue, yourNewValue, yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table updateIfConditionDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserAge int -> ); Query OK, 0 rows affected (4 min 0.59 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into updateIfConditionDemo(UserName, UserAge) values('Larry', 23); Query OK, ...
Read MoreFind minimum unused value in a MySQL table?
You can use LEFT JOIN to find minimum unused value in a MySQL table. Let us first create a tablemysql> create table FindValue -> ( -> SequenceNumber int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into FindValue values(109); Query OK, 1 row affected (0.14 sec) mysql> insert into FindValue values(110); Query OK, 1 row affected (0.15 sec) mysql> insert into FindValue values(111); Query OK, 1 row affected (0.13 sec) mysql> insert into FindValue values(113); Query OK, 1 row affected (0.13 ...
Read MoreCan't find my.ini in MySQL directory?
The my.ini is in hidden folder of program data. First go to C: drive and then hidden folder of program data. From that, move to the MySQL version directory.Here is the snapshot of the C: drive −Click on C drive. The snapshot is as follows. Here, you can see the Program Data folder −Now go to MySQL under Program Data folder. The snapshot is as follows −Go to MySQL version. The snapshot is as follows −Note − Here, we are using MySQL version 8.0.12Here is the my.ini file.In order to reach the location, you can also use the following command ...
Read MoreBest way to store weekly event in MySQL?
Let us see the best way to store weekly events in MySQL. For that, first create a new table and include fields for every day as well.mysql> create table WeeklyEventDemo -> ( -> ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EventName varchar(20), -> Monday tinyint(1), -> Tuesday tinyint(1), -> Wednesday tinyint(1), -> Thursday tinyint(1), -> Friday tinyint(1), -> Saturday tinyint(1), -> Sunday tinyint(1), -> StartDate date, ...
Read MoreMySQL appears to DROP USER but user still exists in MySQL.users table?
First check all the user and host from MySQL.user table with the help of select statement as shown belowmysql> select user, host from MySQL.user;The following is the output+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Manish | % | | User2 | % | | mysql.infoschema | % | | mysql.session | % ...
Read More