Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Use the LPAD() function to left pad values. Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (2.26 secInsert some records in the table using insert command −mysql> insert into DemoTable values(857786); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(89696); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(89049443); Query OK, 1 row affected (0.25 secDisplay all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+----------+ | ... Read More
Use CHANGE with ALTER statement. Let us first create a table −mysql> create table DemoTable -> ( -> StudentName varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.84 sec)Now check the description of table −mysql> desc DemoTable;OutputThis will produce the following output −+----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | StudentName | varchar(100) | YES | | NULL | | | Age ... Read More
To correctly drop a view, use the below syntax −drop view yourViewName;Let us first create a table −mysql> create table DemoTable -> ( -> Id int -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1001); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(1002); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values(1003); Query OK, 1 row affected (0.26 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------+ | ... Read More
Let us first create a table −mysql> create table DemoTable -> ( -> value int -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.24 sec) mysql> insert into ... Read More
If you truncate a table, you do not need to add indexes because table is recreated after truncating a table and indexes get added automatically.Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.65 sec)Following is the query to create an index −mysql> create index Index_firstName_LastName on DemoTable(FirstName, LastName); Query OK, 0 rows affected (1.04 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> ... Read More
Use SUBSTRING() to return values after delimiter. Let us first create a table −mysql> create table DemoTable -> ( -> Title text -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John is good in MySQL, Sam is good in MongoDB, Mike is good in Java'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------------------------------------------------------------+ | Title ... Read More
Zerofill pads the displayed value of the field with zeros up to the display width specified in the column definition. For example, if column is set int(8), therefore the width is 8. If the number is let’s say 4376, then zero will be padded on the left for total width i.e. 8 −00004376Let us first create a table −mysql> create table DemoTable -> ( -> Number int(8) zerofill -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More
You can use LIKE operator to conduct a basic search for last name. Let us first create a table: −mysql> create table DemoTable -> ( -> CustomerName varchar(100), -> CustomerAge int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Doe', 34); Query OK, 1 row affected (1.32 sec) mysql> insert into DemoTable values('David Miller', 24); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Bob Doe', 27); Query OK, 1 row affected (0.18 sec) mysql> insert into ... Read More
Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate datetime -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command. Consider current date “2019-06-28” −mysql> insert into DemoTable(ShippingDate) values('2019-01-31'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(ShippingDate) values('2019-06-06'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(ShippingDate) values('2019-06-28'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ShippingDate) values('2019-07-01'); Query OK, 1 row affected (0.16 sec)Display all records from the table ... Read More
Use the MONTH() method in MySQL to select date based on month. Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserPostMessageDate datetime, -> UserLikes int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserName, UserPostMessageDate, UserLikes) values('John', '2019-01-31', 4560); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(UserName, UserPostMessageDate, UserLikes) values('Sam', '2019-06-28', 790); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(UserName, UserPostMessageDate, UserLikes) values('Carol', '2019-05-01', ... Read More