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 53 of 341
Difference between Schema and Database in MySQL?
In MySQL, schema is synonymous with database. As the query is written to create the database, similarly the query can be written to create the schema.Logical structure can be used by the schema to store data while memory component can be used by the database to store data. Also, a schema is collection of tables while a database is a collection of schema.To clarify this concept, a database and a schema are created. The steps for this are as follows −First, a database is created with the following syntax −create database yourDatabaseName;The above syntax is used in a query as ...
Read MoreHow to get the max of two values MySQL?
To get the maximum of two values in MySQL, we can use the predefined function “greatest”. The syntax of greatest() function is as follows −SELECT greatest(value1, value2);Applying the above query, To get the maximum value from two values. The query is as follows −Case 1We are giving both the values int.mysql> SELECT greatest(100, -300); After executing the above query, we will get the following output+--------------------+ | greatest(100, -300) | +--------------------+ | 100 | +--------------------+ 1 row in set (0.00 sec)Case 2We are giving both the ...
Read MoreMySQL error 1452 - Cannot add or a child row: a foreign key constraint fails
To understand error 1452, first we need to create a table and relate that to another table with the help of a foreign key constraint.Creating the first table −mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows affected (0.43 sec)After creating the first table successfully, we will create the second table −mysql> CREATE table primaryTable1 -> ( -> Fk_pk int, -> DeptName varchar(200), -> Primary key(Fk_pk) -> ); Query OK, 0 rows affected (0.48 sec)Now, we have created both tables. Then both the tables are related with the ...
Read MoreHow to check if a MySQL database exists?
The schema_name command is used to check if a MySQL database exists or not. The syntax of this command is as follows −select schema_name from information_schema.schemata where schema_name = 'database name';Now, the above command is used to check whether the database exists or not. The query for that is as follows −Case 1 − The database exists.mysql> select schema_name from information_schema.schemata where schema_name = 'business'; The output obtained is as follows −+-------------+ | SCHEMA_NAME | +-------------+ | business | +-------------+ 1 row in set (0.00 sec)Case 2 − The database does not exist.mysql> select schema_name from information_schema.schemata ...
Read MoreHow do I see all foreign keys to a table column?
To see all the foreign keys to a table or column, the referenced_column_name command is used.First, two tables are created and then related with the help of the foreign key constraint.Creating the first table −mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows affected (0.43 sec)After creating the first table successfully, the second table is created as follows −mysql> CREATE table primaryTable1 -> ( -> Fk_pk int, -> DeptName varchar(200), -> Primary key(Fk_pk) -> ); Query OK, 0 rows affected (0.48 sec)Now, both the tables are related with ...
Read MoreHow to change max_allowed_packet size in MySQL?
The max_allowed_packet size is a session variable and is also a read only variable.To check what is the present value of max_allowed_packet, the command show variables is used. It is given as follows −mysql> show variables like 'max_allowed_packet';The following is the output+--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+ 1 row in set (0.04 sec)The value of the max_allowed_packet can be changed in the ‘my.ini’ file on the client side. The query for that is given as follows −max_allowed_packet = 4567890; Now, the value can be changed globally ...
Read MoreWhat is “where 1=1” statement in MySQL?
In MySQL “Where 1=1” results in all the rows of a table as this statement is always true. An example to better unerstand this statement is given as follows −First, a table is created with the help of the create command. This is given as follows −mysql> CREATE table WhereConditon -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.43 sec)After successfully creating a table, some records are inserted with the help of insert command The query for this is given as follows −mysql> INSERT into WhereConditon values(1, 'John'); Query OK, 1 row affected ...
Read MoreHow to modify the size of column in MySQL table?
We can modify a column size with the help of ALTER command. Let us see how to modify column size. Suppose we are defining any column with some size. At the time of inserting if we are giving more size in comparison to the one we defined, then an error will generate.The above problem can be reduced while modifying the size. For more understanding, we can create a table with the help of CREATE command −mysql> CREATE table ModifyColumnNameDemo -> ( -> id int, -> StudentName varchar(10) -> ); Query OK, 0 rows affected (0.45 sec)After creating a table successfully, ...
Read MoreHow we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
It is quite possible to add multiple stored generated columns in a MySQL table. It can be illustrated with the following example as follows −Examplemysql> Create table profit1(cost int, price int, profit int AS (price-cost) STORED, price_revised int AS (price-2) STORED); Query OK, 0 rows affected (0.36 sec) mysql> Describe profit1; +---------------+---------+------+-----+---------+------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------+------+-----+---------+------------------+ | cost | int(11) | YES | | NULL | ...
Read MoreHow can we alter table to add MySQL stored GENERATED COLUMNS?
For adding MySQL stored GENERATED COLUMNS in a table, we can use the same syntax as adding a column just adding “AS(expression)” after the data type. Its syntax would be as follows −SyntaxALTER TABLE table_name ADD COLUMN column_name AS(expression)STORED;Examplemysql> ALTER TABLE employee_data_stored ADD COLUMN FULLName Varchar(200) AS (CONCAT_WS(" ", 'First_name', 'Last_name')) STORED; Query OK, 2 rows affected (1.23 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> Describe employee_data_stored; +------------+--------------+------+-----+---------+------------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+------------------+ | ID ...
Read More