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
MySQLi Articles
Page 301 of 341
How to use IF in stored procedure and select in MySQL?
You can use IF in stored procedure and IF() in select statement as well.IF() in select statementIF() in select statement mysql> select if(0=0, 'Hello MySQL', 'condition is wrong');This will produce the following output −+------------------------------------------------------+ | if('test'='test', 'Hello MySQL', 'condition is wrong') | +------------------------------------------------------+ | Hello MySQL | +------------------------------------------------------+ 1 row in set (0.00 sec)The second case if your condition ...
Read MorePHP and MYSQL database connection and table creation only once if it does not already exist?
To create database only once, use the below syntax.CREATE DATABASE IF NOT EXISTS yourDatabaseName;To create table only once, use the below syntax −CREATE TABLE IF NOT EXISTS yourTableName ( yourColumnName yourDatatype, . . . N );Let us implement both the above syntaxes to create database and table only once if does not already exist −mysql> CREATE DATABASE IF NOT EXISTS login; Query OK, 1 row affected (0.23 sec)Above query creates a database successfully.Following is the query to create a table −mysql> CREATE TABLE IF NOT EXISTS DemoTable ( Id int ); Query OK, 0 rows affected (0.56 sec)Above query creates a table successfully.
Read MoreCan we know the last MySQL error?
In order to know the last MySQL error, you can use SHOW command −SHOW ERRORS;Or you can use another syntax −SHOW WARNINGS;Here, we are creating a table that displays an error and then we will find out how to know the last MySQL error. Here, the error occurs because we have intentionally written incorrect create table statement −mysql> create table DemoTable(Id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table DemoTable(Id int)' at line 1Following is the query to ...
Read MoreWrite a MySQL query to check if field exists and then return the result set?
To check if field exists and then to return the result set, you can use the below syntax −show columns from yourTableName where field='yourColumnName';Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserFirstName varchar(20), UserLastName varchar(20), UserAge int, UserAddress varchar(200), UserCountryName varchar(20) ); Query OK, 0 rows affected (0.67 sec)Here is the query to check if field exists and then return the result set −mysql> show columns from DemoTable where field='UserCountryName';This will produce the following output −+-----------------+-------------+------+-----+---------+-------+ | Field | ...
Read MoreHow to get first N characters from a MySQL column?
Use SUBSTRING() to get first N characters from a MySQL column. Let us first create a table −mysql>create table DemoTable ( Information text ); Query OK, 0 rows affected (2.63 sec)Insert records in the table using insert command −mysql>insert into DemoTable values('MySQL is a structured query language'); Query OK, 1 row affected (0.13 sec)Following is the query to display all records from the table using select statement −mysql>select *from DemoTable;This will produce the following output −+--------------------------------------+ | Information | +--------------------------------------+ | MySQL is a structured query ...
Read MoreGet MySQL DISTINCT to work correctly if the records contain whitespace?
To get distinct including whitespace, you can use below syntax −SELECT DISTINCT replace(yourColumnName, ' ', '') FROM yourTableName;Let us first create a table:mysql>create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql>insert into DemoTable(Name) values('John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values(' John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.09 sec) mysql>insert into DemoTable(Name) values('Sam'); Query OK, 1 row affected (0.15 sec) ...
Read MoreMySQL - How to count all rows per table in one query?
You can count all rows per table with the help of aggregate function count (TABLE_ROWS) from informatio_schema.tables. The syntax is as follows −SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName';Now you can apply the above syntax to get all rows per table. The query is as follows −mysql> SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'business';Here is the output −+------------------------------------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------------------------------------+------------+ | accentsearchdemo ...
Read MoreGet the fields in each constraint in MySQL
Let’s say we have a database “business” with number of tables. If you want to Get the fields in each constraint, then use the below query.The below query is to get the fields in each one of those constraints −mysql> select * −> from information_schema.key_column_usage −> where constraint_schema = 'business';The following is the output −+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def ...
Read MoreList of non-empty tables in all your MySQL databases?
To list non-empty tables in MySQL database, use “information_schema.tables”. The following is the query for all database tables −mysql> select table_type, table_name from information_schema.tables −> where table_rows >= 1;Above, we have considered only the table that have 1 or more than 1 rows i.e. non-empty table.The following is the output −+------------+------------------------------------------------------+ | TABLE_TYPE | TABLE_NAME | +------------+------------------------------------------------------+ | BASE TABLE | innodb_table_stats ...
Read MoreDisplay all tables inside a MySQL database using Java?
We will see here how to display all tables inside a MySQL database using Java. You can use show command from MySQL to get all tables inside a MySQL database.Let’s say our database is ‘test’. The Java code is as follows to show all table names inside a database ‘test’.The Java code is as follows. Here, connection is established between MySQL and Java −import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.DatabaseMetaData; public class GetAllTables { public static void main(String[] args) throws SQLException { Connection conn = null; try { ...
Read More