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
Database Articles
Page 253 of 547
How 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 ...
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 ...
Read MoreWhat is MySQL GENERATED COLUMN and how to use it while creating a table?
Basically generated columns are a feature that can be used in CREATE TABLE or ALTER TABLE statements and is a way of storing the data without actually sending it through the INSERT or UPDATE clause in SQL. This feature has been added in MySQL 5.7. A generated column works within the table domain. Its syntax would be as follows −Syntaxcolumn_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]]Here, first of all, specify the column name and its data type.Then add the GENERATED ALWAYS clause to indicate that the column is a generated column.Then, indicate whether the type of ...
Read MoreWhat are the different types of MySQL GENERATED COLUMNS?
We have two types of MYSQL generated columns as follows −VIRTUAL GENERATED COLUMNAs the name suggests, this kind of generated column will not take any disk space. It can be generated with or without using the keyword ‘virtual’. To understand we are illustrating it in the following example −Examplemysql> Create table triangle(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB))); Query OK, 0 rows affected (0.44 sec) mysql> Describe Triangle; +-------+--------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------------------+ | SideA ...
Read MoreHow can we create MySQL view by selecting data based on pattern matching from base table?
MySQL LIKE operator is used to select data based on pattern matching. Similarly, we can use LIKE operator with views to select particular data based on pattern matching from the base table. To understand this concept we are using the base table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | ...
Read MoreHow can we use logical operators while creating MySQL views?
MySQL views can be created by using logical operators like AND, OR, and NOT. It can be illustrated with the help of following examples −Views with AND operatorAs we know that logical AND operator compares two expressions and returns true if both the expressions are true. In the following example, we are creating a view which has the conditions based on ‘AND’ operator.ExampleThe base table is Student_info having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History ...
Read MoreHow can we create a MySQL view by selecting some range of values from a base table?
As we know that MySQL BETWEEN operator can be used to select values from some range of values. We can use BETWEEN operator along with views to select some range of values from the base table. To understand this concept we are using the base table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | ...
Read More