- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
1K+ Views
We can use either TRUNCATE statement or DROP statement to delete all the rows from the table. It can also be done with the help of DELETE statement but in that case WHERE clause must identify all the rows of the table. As we know that both TRUNCATE and DELETE statement will not delete the structure of the table but still it is better to use TRUNCATE statement rather than DELETE statement. DROP statement will delete table’s structure also. Syntax TRUNCATE statement TRUNCATE table table_name: Syntax DROP statement DROP table table_name:
366 Views
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 More
702 Views
MySQL defines NCHAR as a way to indicate that a CHAR column should use predefined character set. Utf8 is used by MySQL as its predefined character set.ExampleIn the example below, we are creating a table named ‘Student1’. In this table, we are declaring the data types of three columns with three different declaration styles which are rather equivalent of each other. It is all due to NCHAR.mysql> Create table Student1(Name Char(10) character set utf8, Address NATIONAL CHARACTER(10), FatherName NCHAR(10)); Query OK, 0 rows affected (0.25 sec)Now on checking the status of table, with the help of query below, we ... Read More
143 Views
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 More
100 Views
With the help of the following MySQL query we can check the character sets of all the tables in a particular database −mysql> Select Column_name, 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 along with the column names in a database named ‘Alpha’.mysql> Select Column_name 'Column', TABLE_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns Where TABLE_SCHEMA = 'Alpha'; +---------+------------+--------------------+ | Column | TABLE_NAME | CHARACTER_SET_NAME | +---------+------------+--------------------+ | Name | employee | latin1 | | email | employee ... Read More
2K+ Views
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 | | student | latin1 ... Read More
6K+ Views
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)
376 Views
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
102 Views
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)
66 Views
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)