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 312 of 341
Can we use str_replace in MySQL?
The str_replace version in MySQL is the replace() function. Let us first create a table to understand the function −mysql> create table StringReplaceDemo −> ( −> Id int, −> URL varchar(200) −> ); Query OK, 0 rows affected (0.38 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into StringReplaceDemo values(1001, 'https://www.google.co.in'); Query OK, 1 row affected (0.09 sec) mysql> insert into StringReplaceDemo values(1002, 'https://www.facebook.com'); Query OK, 1 row affected (0.11 sec) mysql> insert into StringReplaceDemo ...
Read MoreOrder strings by length of characters IN mYsql?
You can order by length of characters with the help of CHAR_LENGTH() function from MySQL. The function returns the number of characters i.e. 4 for the following string −AMITTo order strings by length of characters, the following is the syntax −select *from yourTableName order by CHAR_LENGTH(yourColumnName);To understand the above concept, let us first create a table. The following is the query to create a table −mysql> create table OrderByCharacterLength −> ( −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.97 sec)Insert some records in the table with the help of insert command. The query is ...
Read MoreHow to query between two dates in MySQL?
You can query between dates with the help of BETWEEN statement. The syntax is as follows −select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().Use curdate() or now(), both these functions will work. To understand the above syntax, let us create a table −mysql> create table BetweenDateDemo −> ( −> StartDate datetime −> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table with the help of the following query −mysql> insert into BetweenDateDemo values(date_add(now(), interval -1 year)); Query OK, 1 row affected (0.11 sec) mysql> insert ...
Read MoreHow to return the nth record from MySQL query?
To get the nth record from MySQL query, you can use LIMIT. The syntax is as follows −select *from yourTableName order by yourColumnName limit n, 1;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table NthRecordDemo −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using the following query −mysql> insert into NthRecordDemo values(100, 'John'); Query OK, 1 row affected (0.09 sec) ...
Read MoreHow to perform Increment in MySQL Update?
To update values incrementally in MySQL, you need to create a variable with the help of SET command. The syntax to create a variable is as follows −set @anyVariableName := 0;To update value, you need to use UPDATE command. Let us begin with creating a table. The query to create a table −mysql> create table UpdateValueIncrementally −> ( −> ProductId int −> ); Query OK, 0 rows affected (0.90 sec)Insert records in the table with the help of select statement. The query is as follows −mysql> insert into UpdateValueIncrementally values(10); Query ...
Read MoreSHOW TABLE statement with multiple LIKE values in MySQL?
You can use WHERE clause and OR operator to show table with multiple LIKE. The syntax is as follows:show table from yourDatabaseName where tables_in_yourDatabaseName Like ‘%anyTableName%’ or tables_in_yourDatabaseName Like ‘%anyTableName2%’ or tables_in_yourDatabaseName Like ‘%anyTableName3%’ . . . . or tables_in_yourDatabaseName Like ‘%anyTableNameN%’In the above syntax, only the table name in the database is displayed.Here the database ‘test’ and the tables in the same database is considered. The query to show tables with multiple LIKE is as follows -mysql> show tables from test -> where tables_in_test like '%userrole%' -> or tables_in_test like '%view_student%' -> or tables_in_test like '%wholewordmatchdemo%';The following is the ...
Read MoreHow to make MySQL's NOW() and CURDATE() functions use UTC?
To make MySQL’s NOW() and CURDATE() functions use UTC, you need to write my.cnf file. Write the below instruction in my.cnf −[mysqld_safe] timezone = UTCFirstly, reach the directory with the help of the following query −mysql> select @@datadir;The following is the output −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the directory for which the link ...
Read MoreMySQL BigInt zerofill vs int zerofill?
The difference between MySQL BigInt and int is that INT is a 32-bit long while BIGINT is 64-bit long.The following are some of the points −The BigInt takes 8 bytes of storage while int takes 4 bytes of storage.The int takes 4294967295 maximum values for int(10), whereas 18, 446, 744, 073, 709, 551, 615 for the bigint(20).The BigInt(20) and int(10), in this 20 and 10 can be used for width display with zerofill.Here is the demo of Bigint and int with zerofill. The following is the query to create a table.mysql> create table BigintandintDemo −> ( ...
Read MoreSetting similar value for a column in a MySQL table?
You can set value for a column of all records with the help of update command.The syntax is as follows if you want set NULL value for all the records in a column −update yourTableName set yourColumnName = NULL;Or if you want to use empty string, the following is the syntax −update yourTableName set yourColumnName = ’’;To understand the above concept, let us create a table. The query to create a table.mysql> create table StudentDemo −> ( −> Studentid int, −> StudentName varchar(100), −> Age int −> ); Query OK, 0 rows affected (0.64 sec)The ...
Read MoreHow to insert multiple rows with single MySQL query?
You can insert multiple rows with the help of values() separated by comma(, ). The syntax is as follows −insert into yourTableName values(value1, value2, ...N), (value1, value2, ...N), (value1, value2, ...N), (value1, value2, ...N), (value1, value2, ...N), (value1, value2, ...N)................N;To insert multiple rows, let us create a table. The following is the query to create a table −mysql> create table MultipleRowsInsert −> ( −> UserId int, −> UserName varchar(200) −> ); Query OK, 0 rows affected (1.21 sec)Here is the query to insert multiple rows in the table −mysql> insert into MultipleRowsInsert values(100, 'Bob'), (101, 'Smith'), ...
Read More