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 62 of 355
Get the index of last substring in a given string in MySQL?
To get the index of last substring in a given string, use the char_length() function. First, we need to calculate string length and subtract the last sub string length from the entire length. The difference in length is index of substring.SyntaxThe syntax is as follows −select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ', REVERSE(yourColumnName))+1 as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SubStringIndexDemo -> ( -> Words varchar(200) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table ...
Read MoreHow to make a primary key start from 1000?
To make a primary key start from 1000, you need to alter your table and set to auto_increment with value 1000. The syntax is as follows −alter table yourTableName auto_increment=1000;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table PrimaryKey1000Demo -> ( -> ProductId int auto_increment, -> PRIMARY KEY(ProductId) -> ); Query OK, 0 rows affected (0.56 sec)Now here is the query that will update the primary key to start from 1000 −mysql> alter table PrimaryKey1000Demo auto_increment=1000; Query OK, 0 rows affected (0.20 ...
Read MoreGet only the date in timestamp in MySQL?
In order to get the date from the timestamp, you can use DATE() function from MySQL.The syntax is as follows −SyntaxSELECT DATE(yourTimestampColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table DateFromTimestamp -> ( -> ShippingDateTime timestamp -> ); Query OK, 0 rows affected (0.60 sec)Insert date and time for the column ShippingDateTime we created above.The query to insert record is as follows −mysql> insert into DateFromTimestamp values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFromTimestamp values('2013-11-26 14:36:40'); ...
Read MoreAdd a temporary column with a value in MySQL?
You can add a temporary column with value with the help of the following syntax −select yourColumnName1, yourColumnName2, .....N ,yourTemporaryColumnValue as yourTemporaryColumnName from yourTableName;To add a temporary column with a value, let us create a table. The following is the query −mysql> create table TemporaryColumnWithValueDemo −> ( −> StudentId int, −> StudentName varchar(100) −> ); Query OK, 0 rows affected (0.59 sec)Inserting some records in the table. The query to insert records are as follows −mysql> insert into TemporaryColumnWithValueDemo values(101, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read MoreCalculate average of numbers in a column MySQL query?
Calculate the average of numbers in a column with the help of MySQL aggregate function AVG().The syntax is as follows −select avg(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table AverageCalculateDemo −> ( −> SubjectMarks int −> ); Query OK, 0 rows affected (0.67 sec)The following is the query to insert some records into the table −mysql> insert into AverageCalculateDemo values(70); Query OK, 1 row affected (0.14 sec) mysql> insert into AverageCalculateDemo values(80); Query OK, 1 row affected ...
Read MoreHow to add 5 hours to current time in MySQL?
To add 5 hours in current time, we will use now() function from MySQL. The syntax is as follows −SELECT date_add(now(),interval some integer value hour);Now, I am applying the above query to add 5 hours to current time. The query is as follows −mysql> SELECT date_add(now(),interval 5 hour); The following is the output+---------------------------------+ | date_add(now(),interval 5 hour) | +---------------------------------+ | 2018-10-11 15:59:23 | +---------------------------------+ 1 row in set (0.00 sec)Look at the output above, it has increased the current time by 5 hours
Read MoreCheck if table exist without using "select from" in MySQL?
We can achieve this with the help of SHOW command. Firstly, I will use my database with the help of USE command −mysql> USE business; Database changedWe are in the “business” database now. After that, we can check that how many tables are available for this database. The query is as follows −mysql> SHOW tables; The following is the output+------------------------+ | Tables_in_business | +------------------------+ | addcolumntable | | autoincrement | | autoincrementtable | | bookindexes | | chardemo ...
Read MoreHow to check if a column exist in a MySQL table?
To understand whether a column exist or not, we have the following approaches −With the help of DESC commandUsing SHOW commandFirstly, we will create a table with columns −mysql> CREATE table ColumnExistDemo -> ( -> UniqueId int, -> UniqueName varchar(200), -> UniqueAddress varchar(200) -> ); Query OK, 0 rows affected (0.57 sec)In the first approach, we will get the entire column name with meaningful information. The syntax is as follows −DESC yourTableName; Let us apply the above query to check whether the column name exists or not. However, this approach is not good since it display all the columns.mysql> DESC ...
Read MoreCan a number be used to name a MySQL table column?
Yes, we can include a number for column name in MySQL. We need to use the symbol backtick, which is as follows( ` `)To understand, we will make a table with the help of CREATE command. Let us create a table −mysql> CREATE table NumberColumnDemo -> ( -> `123` varchar(100) -> ); Query OK, 0 rows affected (0.51 sec)Above, I have created a column name as a number with the help of backtick symbol.Now, we can check the same by inserting records with the help of INSERT command. Let us insert a record, which is as follows −mysql> INSERT into ...
Read MoreWhich one should I use? The datetime or timestamp data type in MySQL?
Timestamp is a data type in MySQL and works for different time zone. It is also used for date and time purpose To understand the concept, we need to create a table.Creating a tablemysql> CREATE table TimeStampDemo -> ( -> MyDataTime timestamp -> ); Query OK, 0 rows affected (0.57 sec)After creating the table, we will insert a record with the help of INSERT command.Inserting recordsmysql> INSERT into TimeStampDemo values (now()); Query OK, 1 row affected (0.12 sec)After inserting a record, we can display the records with the help of SELECT statement.Displaying recordsmysql> SELECT * from TimeStampDemo; After executing the ...
Read More