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
MySQL Articles
Page 59 of 355
Adding a column that doesn't exist in a query?
Add a column that does not exist in a query, with the help of AS keyword. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ....N, yourValue AS yourColumnName, ....N' FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ColumnDoesNotExists -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.67 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into ColumnDoesNotExists(UserId, UserName) values(100, 'Larry'); Query OK, ...
Read MoreCheck if a table is empty or not in MySQL using EXISTS
The following is the syntax to check whether a table is empty or not using MySQL EXISTS −SELECT EXISTS(SELECT 1 FROM yourTableName);ExampleFirst, let us create a table. The query to create a table is as follows −mysql> create table ReturnDemo -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) ...
Read MoreWhich query is efficient to check if MySQL Table is empty? COUNT(*) vs. LIMIT?
If you use COUNT(*) around the LEAST() then MySQL scans at least one index, therefore avoid LEAST(COUNT(*)) and use LIMIT.Let us first create a table. The query to create a table is as follows −mysql> create table ReturnDemo -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.79 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) mysql> insert into ...
Read MoreUsing GROUP BY and MAX on multiple columns in MySQL?
To understand the GROUP BY and MAX on multiple columns, let us first create a table. The query to create a table is as follows −mysql> create table GroupByMaxDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CategoryId int, -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.68 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, 50); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByMaxDemo(CategoryId, Value1, Value2) values(10, 100, ...
Read MoreHow do I update NULL values in a field in MySQL?
Let us first create a table −mysql> create table OrderDemo -> ( -> OrderId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> OrderPrice int, -> OrderDatetime datetime -> ); Query OK, 0 rows affected (0.66 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(200, '2016-09-12'); Query OK, 1 row affected (0.24 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(NULL, '2002-11-18'); Query OK, 1 row affected (0.26 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(1000, '2017-12-28'); Query OK, 1 row affected (0.15 sec)Display all records ...
Read MoreHow do I lag columns in MySQL?
To lag a column in MySQL, first, let us create a table. The query to create a table is as follows −mysql> create table LagDemo -> ( -> UserId int, -> UserValue int -> ); Query OK, 0 rows affected (1.74 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into LagDemo values(12, 158); Query OK, 1 row affected (0.61 sec) mysql> insert into LagDemo values(18, 756); Query OK, 1 row affected (0.21 sec) mysql> insert into LagDemo values(15, 346); Query OK, 1 row affected (0.25 sec) mysql> insert ...
Read MoreGet a list of Foreign Key constraints in MySQL
Let’s say we have a database “business” with number of tables. If you want to show only foreign key constraints, then use the following query −mysql> select * −> from information_schema.referential_constraints −> where constraint_schema = 'business';The following is the output displaying only foreign key constraints −+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME | +--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+ | def | business | ConstChild ...
Read MoreInsert NULL value into INT column in MySQL?
You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows.INSERT INTO yourTableName(yourColumnName) values(NULL);To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table InsertNullDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (0.53 sec)Here is the query to insert NULL whenever you do not pass any value for column. Here this column is StudentAge. MySQL inserts null value by default. The query to ...
Read MoreMySQL sort string number?
To sort string number, use the CAST() function from MySQL. The syntax is as follows −SELECT *FROM yourTableName ORDER BY (yourColumnName as Decimal(integerValue, integerValueAfterDecimalPoint)) desc;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SortingStringDemo -> ( -> Amount varchar(10) -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into SortingStringDemo values('12.34'); Query OK, 1 row affected (0.21 sec) mysql> insert into SortingStringDemo values('124.50'); Query OK, 1 row affected (0.56 sec) ...
Read MoreMySQL query to select records with a particular date?
You can use DATE() from MySQL to select records with a particular date. The syntax is as follows.SELECT *from yourTableName WHERE DATE(yourDateColumnName)=’anyDate’;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AllRecordsFromadate -> ( -> Id int, -> Name varchar(100), -> Age int, -> AdmissionDate datetime -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query to insert records is as follows.mysql> insert into AllRecordsFromadate values(101, 'John', 23, '2018-10-13'); Query OK, 1 row affected (0.18 sec) mysql> insert ...
Read More