Following is the query to check character set of the columns of MySQL table −mysql> Select Column_name 'Column', Character_set_name 'Charset' FROM        information_schema.columns where table_schema = 'db_name' and        table_name ='table_name';ExampleFor example, the query below returns the name of the columns of ‘test_char_set’ table in a database named ‘sample’ along with the character sets of those columns.mysql> Select Column_name 'Column', Character_set_name 'Charset' FROM        information_schema.columns where table_schema = 'Sample' and        table_name ='test_char_set'; +--------+---------+ | Column | Charset | +--------+---------+ | Name   | latin1  | | Field  | latin1  | ... Read More

Following is the query to check default character set of the particular MySQL database −mysql> SELECT SCHEMA_NAME 'DatabaseName', default_character_set_name 'Charset' FROM information_schema.SCHEMATA where schema_name = 'db_name';ExampleFor example, the query below will return the default character set of a database named ‘Sample’ −mysql> SELECT SCHEMA_NAME 'DatabaseName', default_character_set_name 'Charset' FROM information_schema.SCHEMATA where schema_name = 'Sample'; +----------------+---------+ | DatabaseName   | Charset | +----------------+---------+ | Sample         | latin1  | +----------------+---------+ 1 row in set (0.00 sec)

The query below will return the name of the database along with the default character set −mysql> SELECT SCHEMA_NAME 'Database', default_character_set_name 'charset' FROM information_schema.SCHEMATA; +--------------------+---------+ | Database           | Charset | +--------------------+---------+ | information_schema | utf8    | | gaurav             | latin1  | | menagerie          | latin1  | | mysql              | latin1  | | performance_schema | utf8    | | sample             | latin1  | | test               | latin1  | | tutorial           | latin1  | +--------------------+---------+ 8 rows in set (0.00 sec)

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)

With the help of DISTINCT keyword in SELECT statement, we can get the unique rows in MySQL result set.Examplemysql> Select * from names; +------+-----------+ | id   | name      | +------+-----------+ | 1    | Rahul     | | 2    | Gaurav    | | 3    | Raman     | | 4    | Aarav     | | 5    | Ram       | | 5    | Ram       | | 5    | Ram       | +------+-----------+ 7 rows in set (0.00 sec)As we can see that table ‘names’ is having three duplicate rows, with the help of following query we can get the result set having only unique rows.mysql> Select DISTINCT * from names; +------+-----------+ | id   | name      | +------+-----------+ | 1    | Rahul     | | 2    | Gaurav    | | 3    | Raman     | | 4    | Aarav     | | 5    | Ram       | +------+-----------+ 5 rows in set (0.00 sec)

With the help of the following query we can see all the character sets supported by MySQL −mysql> Show Character Set; +-----------+-----------------------------+---------------------+--------+ | Charset   | Description                 | Default collation   | Maxlen | +---------- +-----------------------------+---------------------+--------+ | big5      | Big5 Traditional Chinese    | big5_chinese_ci     |      2 | | dec8      | DEC West European           | dec8_swedish_ci     |      1 | | cp850     | DOS West European           | cp850_general_ci   ... Read More

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 More

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 More

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  |     | NULL    |   ... Read More

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