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 258 of 341
Write a MySQL query equivalent to "SHOW TABLES" in sorted order?
Use INFORMATION_SCHEMA.TABLES to display tables in sorted order. The below syntax will give sorted list of tables in ascending order:select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA= 'yourDatabaseName' order by TABLE_NAME;Following is the query to implement the equivalent to SHOW TABLES:mysql> select TABLE_NAME from INFORMATION_SCHEMA.TABLES -> where TABLE_SCHEMA= 'sample' order by TABLE_NAME;This will produce the following output+------------------------------------+ | TABLE_NAME | +------------------------------------+ | a | | accumulateddemo ...
Read MoreWhat is the alias to Show Tables in MySQL Result?
You can use AS command for alias to show tables in MySQL result. Following is the syntax −SELECT TABLE_NAME AS anyAliasName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();Let us implement the above syntax −mysql> SELECT TABLE_NAME AS MY_TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();This will produce the following output −+------------------------------------+ | MY_TABLE_NAME | +------------------------------------+ | a | | accumulateddemo | | add10minutedemo ...
Read MoreWhat does /* in MySQL means?
This is a type of comment. The /* is the beginning of a comment and */ is the end of comment.Let us implement and display how to create a commentmysql> /* This is the first MySQL Program */MySQL will ignore the above comment.Let us see an example. Here, we have written a comment with /* and */mysql> /*This table has information about person */ mysql> create table DemoTable ( PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY, PersonName varchar(20), PersonAge int ); Query OK, 0 rows affected (0.58 sec)
Read MoreRun SQL file in MySQL database from terminal?
To run SQL file in database, you need to use below syntax:mysql -u yourUserName -p yourDatabaseName < yourFileName.sqlTo understand the above syntax, let us open command prompt using windows+R shortcut key.The snapshot is as follows:After pressing OK button, you will get a command prompt. The snapshot is as follows:Now reach the bin directory, the snapshot is as follows:Here is my file ‘mydb.sql’ which is located in bin directory. The snapshot and content of SQL file is as follows:Now you can type the above syntax which I have discussed to run SQL file. The snapshot of command is as follows:Now you ...
Read MoreImplement MySQL INSERT MAX()+1?
You need to use COALESCE() function for this. The syntax is as follows:INSERT INTO yourTableName(yourColumnName1, yourColumnName2) SELECT 1 + COALESCE((SELECT MAX(yourColumnName1) FROM yourTableName WHERE yourColumnName2=’yourValue’), 0), ’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table InsertMaxPlus1Demo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.27 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into InsertMaxPlus1Demo(Id, Name) values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert ...
Read MoreCan we select row by DATEPART() in MySQL? Is it possible?
There is no DATEPART() function in MySQL, you need to use MONTH() function to extract the month name from date column. The syntax is as follows:SELECT *FROM yourTableName WHERE MONTH(yourDateColumnName)=yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table SelectRowFromDatePart -> ( -> Id int NOT NULL AUTO_INCREMENT, -> LoginDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (2.42 sec)Now you can insert some records in the table using ...
Read MoreHow do I update the decimal column to allow more digits in MySQL?
To update the decimal column to allow more digit, use the MODIFY COLUMN. The syntax is as follows:ALTER TABLE MODIFY COLUMN yourColumnName DECIMAL(yourIntValue, yourIntValue);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table allowDecimalWithMoreDigit -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Salary DECIMAL(3, 2), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.64 sec)Now you can check the description of table using DESC command. The syntax is as follows:DESC yourTableName;Now you can check the description of table using above ...
Read MoreHow do I cast a type to a BigInt in MySQL?
You need to use the CAST operator along with CONV() function. The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table castTypeToBigIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, ...
Read MoreGet another order after limit with MySQL?
You need to use subquery with select statement, one select for inner and one for outer. The inner select will return rows and outer will order by ascending order. The syntax is as follows:SELECT *FROM ( SELECT *FROM yourTableName ORDER BY yourColumnName1 DESC LIMIT 9 ) AS anyAliasName ORDER BY yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table OrderByAfterLimit -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(20), -> UserAge int, -> PRIMARY KEY(Id) -> ); Query OK, ...
Read MoreCan I use InnoDB and MyISAM tables in a single database in MySQL?
Yes, you can use InnoDB and MyISAM tables in one database or combine both of them in a single database. This is the recommended way.Here is the demo of both MyISAM and InnoDB in a one database. The following is the database and both the table types InnoDB and MyISAM. The query to create a database is as follows −mysql> create database BothInnoDBandMyISAM; Query OK, 1 row affected (0.20 sec) mysql> use BothInnoDBandMyISAM; Database changedI have a database with name ‘BothInnoDBandMyISAM’.First the table has engine type InnoDB. The query to create a table with engine InnoDB is as follows −mysql> ...
Read More