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 98 of 355
How MySQL reacts when we specify a CHARACTER SET binary attribute for a character string data type?
On specifying a CHARACTER SET binary attribute for a character string data type, MySQL creates that column as its subsequent binary string type. The conversions for CHAR, VARCHAR and BLOB data types take place as follows −CHAR would become BINARYVARCHAR would become VARBINARYTEXT would become BLOBThe above kind of conversion does not occur for ENUM and SET data type and they both are created as declared while creating the table.ExampleIn the example below we have created a table named ‘EMP’ with four columns all specified as CHARACTER SET binary as follows −mysql> Create table Emp(Name varchar(10) CHARACTER SET binary, Address ...
Read MoreOn inserting 'NULL', '0' or No Value to the column, will MySQL assign sequence number for AUTO_INCREMENT column?
MySQL will automatically assign sequence numbers to the AUTO_INCREMENT column even if we insert NULL, 0 or No Value to the column in a table.Examplemysql> create table test123(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(10)); Query OK, 0 rows affected (0.15 sec)The query above created a MySQL table named ‘test123’ with columns named ‘id’ and ‘Name’. The column ‘id’ is declared AUTO_INCREMENT. Now, if we insert ‘No Value’, ‘0’ or ‘NULL’ in the ‘Name’ column, MySQL will assign the sequence numbers to column ‘id’. It can be seen from the result queries below −mysql> Insert Into test123(Name) values(''), ('0'), ...
Read MoreHow can we check the character set of all the tables in a particular MySQL database?
With the help of the following MySQL query we can check the character sets of all the tables in a particular database −mysql> Select TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'db_name';ExampleFor example, the query below returns the character sets of all the tables in a database named ‘Alpha’.mysql> Select TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'Alpha'; +------------+--------------------+ | TABLE_NAME | CHARACTER_SET_NAME | +------------+--------------------+ | employee | latin1 | | employee | latin1 ...
Read MoreHow can we delete a single row from a MySQL table?
We can use DELETE statement along with a WHERE clause, which identifies that particular row, to delete a row from MySQL table.Examplemysql> Select * from names; +------+-----------+ | id | name | +------+-----------+ | 1 | Rahul | | 2 | Gaurav | | 3 | Raman | | 4 | Aarav | | 5 | Ram | +------+-----------+ 5 rows in set (0.00 sec) mysql> DELETE from names where id = 4; Query OK, 1 row affected (0.07 sec)The query above will delete a single row having id = 4 from table ‘names’.mysql> Select * from names; +------+-----------+ | id | name | +------+-----------+ | 1 | Rahul | | 2 | Gaurav | | 3 | Raman | | 5 | Ram | +------+-----------+ 4 rows in set (0.00 sec)
Read MoreHow can we delete multiple rows from a MySQL table?
We can use DELETE statement along with a WHERE clause, which identifies those multiple rows, to delete multiple rows from MySQL table.Examplemysql> Select * from names; +------+-----------+ | id | name | +------+-----------+ | 1 | Rahul | | 2 | Gaurav | | 3 | Raman | | 5 | Ram | +------+-----------+ 4 rows in set (0.00 sec) mysql> DELETE from names WHERE id > 2; Query OK, 2 rows affected (0.04 sec)The query above will delete multiple rows because WHERE clause identify two rows having id > 2 from table ‘names’.mysql> Select * from names; +------+-----------+ | id | name | +------+-----------+ | 1 | Rahul | | 2 | Gaurav | +------+-----------+ 2 rows in set (0.00 sec)
Read MoreIn MySQL, what is the difference between SERIAL and AUTO_INCREMENT?
In MySQL, both SERIAL and AUTO_INCREMENT are used to define a sequence as a default value for a field. But they are technically different from each other.The AUTO_INCREMENT attribute is supported by all numeric data types except for BIT and DECIMAL. There can only be one AUTO_INCREMENT field per table and the sequence generated by an AUTO_INCREMENT field in one table cannot be used in any other table. This attribute requires that a UNIQUE index exists on the field to ensure the sequence has no duplicates. The sequence would start by default from 1 and increment by 1 for every insert.Examplemysql> ...
Read MoreHow can I store the fixed length string as well as variable length string in the same MySQL table?
As we know that CHAR is used to store fixed length string and VARCHAR is used to store variable length strings. Hence we can store a fixed length as well as variable length string in the same table by declaring a column as CHAR and other as VARCHAR.Examplemysql> Create Table Employees(FirstName CHAR(10), LastName VARCHAR(10)); Query OK, 0 rows affected (0.64 sec) mysql> Desc Employees; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | FirstName | char(10) | YES ...
Read MoreWhat is the importance of the order of Columns in the SET clause of UPDATE statement? Will it make big difference in result set returned by MySQL?
The order of columns in the SET clause of UPDATE statement is important because MySQL provides us the updated value on columns names used in an expression. Yes, it will make big difference in the result set returned by MySQL. Following is an example to make it clear −ExampleIn this example, we are having a table ‘tender’. First, we will write UPDATE statement by using ‘tender_id’ as the first and ‘rate’ as the second column in SET clause and then we will write UPDATE statement by using ‘rate’ as the first and ‘tender_id’ as the second column on table ‘tender’.mysql> ...
Read MoreWhat MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns multiple rows?
In this case, MySQL will return an error message because we know that if sub-query is used to assign new values in the SET clause of UPDATE statement then it must return exactly one row for each row in the update table that matches the WHERE clause.Examplemysql> insert into info(id, remarks) values(5, 'average'); Query OK, 1 row affected (0.06 sec) mysql> select * from info; +------+-----------+ | id | remarks | +------+-----------+ | 1 | Good | | 2 | Good | | 3 | Excellent | | 4 ...
Read MoreWhat MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns no rows?
In this case, MySQL will provide a NULL value to the SET clause. Following example will demonstrate it −Examplemysql> Select * from student; +----+---------+-----------+ | Id | Name | grade | +----+---------+-----------+ | 1 | Rahul | Good | | 2 | Gaurav | Good | | 3 | Raman | Excellent | | 4 | Harshit | Average | | 5 | Aarav | Best | | 6 | Ram | average | +----+---------+-----------+ 6 rows in set (0.00 sec) ...
Read More