MySQL Articles - Page 223 of 355

Yes, it is possible to enforce data checking in MySQL using regular expression. First, you need to create a table. After that you need to create a trigger before insert in table. Here, we will be checking the Phone Number format.The query to create a table is as followsmysql> create table enforceDataUsingRegularExpression - > ( - > yourPhoneNumber varchar(60) - > ); Query OK, 0 rows affected (0.59 sec)The query to create a trigger is as followsmysql> DELIMITER // mysql> CREATE TRIGGER enforce_phone_check BEFORE INSERT ON enforceDataUsingRegularExpression - ... Read More

Let’s say you have a table with UserLoginTime column wherein we have stored some values for sample. This is the login time of users and we want to filter all these records on the basis of current day, month and year i.e. the current date. We will beLet us now create the table we discussed abovemysql> create table userLoginInformation    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),    - > UserLoginTime datetime    - > ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using ... Read More

To specify exact order with where id IN, you need to use find_in_set() function.The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1, yourValue2, yourValue3, ....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1, yourValue2, yourValue3, ....N’');Let us first create a tablemysql> create table FindInSetDemo    - > (    - > Id int,    - > Name varchar(20),    - > Age int    - > ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into FindInSetDemo values(10, 'John', 23); Query OK, 1 row affected (0.20 sec) mysql> insert ... Read More

To calculate the total time duration in MySQL, you need to use SEC_TO_TIME(). Let us see an example by creating a tablemysql> create table AddTotalTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > LoginTime time - > ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AddTotalTimeDemo(LoginTime) values('05:05:00'); Query OK, 1 row affected (0.10 sec) mysql> insert into AddTotalTimeDemo(LoginTime) values('07:20:00'); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

To exclude entries with “0”, you need to use NULLIF() with function AVG().The syntax is as followsSELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;Let us first create a tablemysql> create table AverageDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > StudentName varchar(20),    - > StudentMarks int    - > ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AverageDemo(StudentName, StudentMarks) values('Adam', NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into AverageDemo(StudentName, StudentMarks) values('Larry', 23); Query OK, ... Read More

To group by range in MySQL, let us first create a table. The query to create a table is as followsmysql> create table GroupByRangeDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > YourRangeValue int    - > ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into GroupByRangeDemo(YourRangeValue) values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(7); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(9); Query OK, 1 ... Read More

For this, let us first create a new table in MySQLmysql> create table useProcedure - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(20), - > LastName varchar(20) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into useProcedure(FirstName, LastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.27 sec)The following is your stored procedure to set two variables in a stored procedure with single select ... Read More

To change a MySQL column datatype from text to timestamp, you need to use ALTER command.The syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName TIMESTAMP;To understand the above syntax, let us create a table.The query to create a table is as followsmysql> create table textTotimestampdemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Source text    - > ); Query OK, 0 rows affected (0.44 sec)Here is the description of table using DESC command.The syntax is as followsDESC yourTableName;The query is as followsmysql> desc textTotimestampdemo;The following is the output+--------+---------+------+-----+---------+----------------+ | ... Read More

If you want the database location i.e. where it is created in MySQL, you can use system variable @@datadir.The syntax is as followsSELECT @@datadir;The following is the querymysql> select @@datadir;Here is the output. The above query returns the location+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the above directory in your system. The screenshot of the ... Read More

To increment all the rows of a particular ID column by 1, you need to use UPDATE command and update the table. The syntax of the query is as follows. We have also used ORDER BY hereUPDATE yourTableName SET yourIdColumnName=yourIdColumnName+1 ORDER BY yourIdColumnName DESC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table IdColumnadd1Demo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY - > ); Query OK, 0 rows affected (0.58 sec)Insert some records in the ... Read More