To cut only the first character, use the substr() function with UPDATE command. The syntax is as follows.UPDATE yourTableName set yourColumnName=substr(yourColumnName, 2);To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table CutStringDemo -> ( -> Value varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into CutStringDemo values(', 12, 3456'); Query OK, 1 row affected (0.14 sec) mysql> insert into CutStringDemo values(', 23, 9867'); Query OK, 1 row affected ... Read More

To get the first 40 characters from a text field, use LEFT() function from MySQL. The syntax is as follows −SELECT LEFT(yourColumnName, 40) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table retrieveFirst40Characters −> ( −> AllWords text −> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some record in the table with the help of insert command. The query is as follows −mysql> insert into retrieveFirst40Characters values('This is a query demo ... Read More

Firstly, get a list of MySQL user accounts, using MySQL.user table. You can use select user column from MySQL.user table to get a list of MySQL user accounts.The query is as follows −mysql> select user from MySQL.user;The following output displays all the users −+------------------+ | user             | +------------------+ | Manish           | | mysql.infoschema | | mysql.session    | | mysql.sys        | | root             | | am               | +------------------+ 6 rows in set (0.06 ... Read More

Yes, you can achieve this in the following two ways.First Approach At the time of creation of a table.Second Approach At the time of writing query.The syntax is as follows.CREATE TABLE yourTableName ( yourDateTimeColumnName datetime default current_timestamp );You can use alter command.The syntax is as follows.ALTER TABLE yourTableName ADD yourColumnName datetime DEFAULT CURRENT_TIMESTAMP;Implement both the syntaxes now.The first approach is as follows.mysql> create table CurrentTimeStampDemo -> ( -> CreationDate datetime default current_timestamp -> ); Query OK, 0 rows affected (0.61 sec)If you do not pass any parameter for the column ‘CreationDate’, MySQL by default stores the current timestamp.Insert record in ... Read More

You can achieve this with the help of update command. To understand the method, let us create a table. The query to create a table is as follows −mysql> create table AddDateTimeWithOther −> ( −> Id int, −> IssueDate datetime, −> DueDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table with insert statement. The query is as follows −mysql> insert into AddDateTimeWithOther values(100, now(), date_add(now(), interval -3 year)); Query OK, 1 row affected (0.13 sec) mysql> insert ... Read More

To return the field names of a table, you can use desc command. The syntax is as follows −desc yourTableName;Or you can use column_name field from information_schema.columns table. The syntax is as follows −select column_name from information_schema.columns where table_name = ’yourTableName’;To understand both the syntax, let’s say we have a table ‘ExtractCommentDemo1’.Using the first syntax −mysql> desc ExtractCommentDemo1;The following is the output displaying the fields −+----------+--------------+------+-----+---------+-------+ | Field    | Type         | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | UserId   | int(11)      | YES  |     | NULL ... Read More

You can append data to a MySQL database field with the help of in-built CONCAT() function.The syntax is as follows −update yourTableName set yourColumnName = CONCAT(yourColumnName, ’AppendValue’);To understand the above concept, let us create a table. The query to create a table −mysql> create table AppendingDataDemo −> ( −> FirstNameAndLastName varchar(200) −> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into AppendingDataDemo values('John'); Query OK, 1 row affected (0.27 sec) mysql> insert ... Read More

You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −select *from yourTableName order by yourFieldName = 0, yourFieldName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table SortColumnZeroAtLastDemo    −> (    −> RankNumber int    −> ); Query OK, 0 rows affected (1.40 sec)Now you can insert records in the table using the following query −mysql> insert into SortColumnZeroAtLastDemo values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 ... Read More

To select the row value containing string in MySQL, use the following syntax.SELECT *FROM yourTableName where yourColumnName like ‘%yourPattern%’;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table PatternDemo -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.97 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into PatternDemo values(1, 'James', 23); Query OK, 1 row affected (0.11 sec) mysql> insert into PatternDemo values(2, 'Joseph', 21); Query OK, 1 row affected (0.18 ... Read More

You can decrement value in MySQL with update command. With this, you can also restrict the value to not reach below 0.The syntax is as follows −update yourTableName set yourColumnName = yourColumnName - 1 where yourColumnName > 0;To avoid the value to go below zero, you can use yourColumnName > 0.To understand the above syntax, let us create a table. The query to create a table.mysql> create table DecrementDemo −> ( −> DecrementValue int −> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table with insert statement. ... Read More