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
Articles by Chandu yadav
Page 74 of 81
SHOW 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 can I merge two MySQL tables?
To merge two MySQL tables, use the following syntax −INSERT IGNORE INTO yourTableName1 select *from yourTableName2;We will create two tables with some records. After that the merge process will begin using the above syntax.Creating first table −mysql> create table MergeDemo1 -> ( -> id int, -> primary key(id), -> Name varchar(200) -> ); Query OK, 0 rows affected (1.00 sec)Inserting records into the table −mysql> insert into MergeDemo1 values(1, 'John'); Query OK, 1 row affected (0.21 sec)Displaying records from the tablemysql> select *from MergeDemo1;The following ...
Read MoreGrant a user permission to only view a MySQL view?
To grant a user permission to only view a MySQL view, use the below syntaxGRANT SELECT ON yourDatabaseName.yourViewName TO ' yourUserName@'yourLocalHost';First you need to display all the view names from a table. The syntax is as follows −SHOW FULL TABLES IN yourDatabaseName WHERE TABLE_TYPE LIKE 'VIEW';Now implement the above syntax to display all views from a database. Here I am using the database name ‘test’. The query is as follows −mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';The following is the output −+-------------------------------+------------+ | Tables_in_test | Table_type | +-------------------------------+------------+ | empidandempname_view ...
Read MoreHow to take MySQL database backup using MySQL Workbench?
To make MySQL database backup using MySQL, first we need to install MySQL Workbench. Follow the below link to install MySQL workbench.https://dev.mysql.com/downloads/windows/installer/8.0.htmlAfter installing successfully, we need to open MySQL Workbench. Choose the option “Data Export”. Here is the snapshot.Select the database you want to export. You can also set the path here where you want to save the database.After successful completion, you can see the following screenshot.You have successfully created a backup of the above database. The following message is now visible.Export of E:\BackupDatabase has finished
Read MoreHow to keep the connection alive in MySQL Workbench?
To keep connection alive in MySQL Workbench, you need to reach at the following location −Edit -> Preferences -> SQL EditorHere is the snapshot of all the options.After clicking the “Edit” menu, we will select “Workbench Preferences” as shown below −Now, select SQL Editor and set an interval. You can also set the below options to set the connection alive in MySQL Workbench.DBMS connection Keep-alive intervalDBMS connection Read-timeout intervalDBMS connection Timeout intervalHere is the screenshot
Read MoreInserting multiple rows in MySQL?
Insert multiple rows in MySQL with the help of “values”. You can enclose the values with parentheses set with comma separation. The syntax is as follows to insert multiple rows in MySQL.insert into yourTableName(yourColumnName1, yourColumnName2, ..............yourColumnNameN) values(value1, value2, ...valueN), (value1, value2, ...valueN), (value1, value2, ...valueN), ...........((value1, value2, ...valueN);Let us now first create a table in MySQL −mysql> create table InsertMultipleRowsDemo -> ( -> Id int, -> Name varchar(200), -> Age int -> ); Query OK, 0 rows affected (0.45 sec)Apply the above syntax to insert ...
Read MoreSelecting a single row in MySQL?
If you want to select a single row on the basis of primary key, use the WHERE clause. The syntax is as follows −SELECT * FROM yourTableName WHERE yourPrimaryKeyColumnName = someValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectWithPrimaryKey -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Age int, -> Marks int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query is ...
Read MoreSort by date & time in descending order in MySQL?
Let us create a table to sort date and time in ascending order. The query to create a table is as follows −mysql> create table SortByDateAndTime -> ( -> UserId int, -> UserName varchar(100), -> IssueDate date, -> IssueTime time -> ); Query OK, 0 rows affected (0.60 sec)Insert the records in the table using insert command. The query is as follows −mysql> insert into SortByDateAndTime values(1, 'John', '2018-12-16', '10:30'); Query OK, 1 row affected (0.14 sec) mysql> insert into SortByDateAndTime values(2, 'Bob', '2018-12-16', '10:10'); Query OK, 1 row affected (0.14 sec) ...
Read MoreEasy way to re-order columns in MySQL?
To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table reOrderColumn -> ( -> ProductId int, -> DeliveryDate datetime, -> ProductName varchar(100) -> ); Query OK, 0 rows affected (0.76 sec)Now check the description of the table. The query is as follows.mysql> desc reOrderColumn;The following is the output.+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ...
Read MoreOrder a MySQL table by two columns?
Order a MySQL table by two columns with the help of below syntax −order by yourColumnName1 DESC, yourColumnName2 DESC;Let us first create a table for our example −mysql> create table OrderByDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (0.57 sec)Inserting records into the table with the help of insert command. The query is as follows −mysql> insert into OrderByDemo values(1, 'John', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into OrderByDemo values(3, ...
Read More