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 538 of 547
How to get the count of each distinct value in a column in MySQL?
Let us see an example to get the count of each distinct value in a column. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table DistinctDemo1 - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.43 sec) Inserting records mysql> insert into DistinctDemo1 values(1, 'John'); Query OK, 1 row affected (0.34 sec) mysql> insert into DistinctDemo1 values(2, 'John'); Query OK, 1 row affected (0.20 sec) ...
Read MoreUsing DISTINCT and COUNT together in a MySQL Query?
We can use DISTINCT and COUNT together in a single MySQL query. Firstly, let us create a table. The CREATE command is used to create a table. mysql> create table DistCountDemo - > ( - > id int, - > name varchar(100), - > age int - > ); Query OK, 0 rows affected (0.48 sec) Records are inserted with the help of INSERT command. mysql> insert into DistCountDemo values(1, 'John', 23); Query OK, 1 row affected (0.11 sec) mysql> insert ...
Read MoreWhat does the KEY keyword mean in MySQL?
Key is synonymous to an index. If you want to create an index for a column, then use ‘Key’. As stated in the official docs: KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems. The key can be used with Primary Key: Let us first create a table. Here is the query to set primary key for a column “id”. mysql> create table KeyDemo -> ( ...
Read MoreWhen to use MyISAM and InnoDB?
InnoDB is a general-purpose storage engine that balances high reliability and performance. Since MySQL 5.6, InnoDB is the default MySQL storage engine. InnoDB can be used for various purposes. Here are some of them − It can be used for transaction purpose i.e. all ACID properties. InnoDB can be used for row level locking, that means it gives higher performance as compared to MyISAM. InnoDB can be used for both data and index for a large buffer pool. InnoDB can be used when we need better performance than MyISAM. MyISAM is the default storage engine for the MySQL ...
Read MoreHow to escape apostrophe (') in MySQL?
We can escape apostrophe (‘) in MySQL in the following two ways − We can use backslash. We can use single quotes twice (double quoted) Using backslash Let us first create a table. mysql> create table SingleQuotesDemo - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (1.16 sec) Following direct usage does not give the desired result for name “John’s”. mysql> insert into SingleQuotesDemo values(1, 'John's'); '> Let us now use backslash. ...
Read MoreIs it possible to have a function-based index in MySQL?
Function-based index wasn’t possible in MySQL versions below 5.6. Firstly, to create function-based index in MySQL, we will create a table. mysql> create table FunctionIndexDemo - > ( - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.70 sec) Let us see the syntax to create a function based index. create index index_name on yourTableName (column_name(IntegerSize)); Here is the query. mysql> create index indFirstName on FunctionIndexDemo (FirstName(6)); Query OK, 0 rows affected (0.56 sec) Records: 0 Duplicates: 0 Warnings: 0 ...
Read MoreHow to create a MySQL table with MyISAM engine table?
To create a MySQL table with MyISAM engine, we can use ENGINE command. Let us first create a table using CREATE command. mysql> create table StudentRecordWithMyISAM -> ( -> Id int, -> StudentName varchar(100), -> StudentAge int -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.26 sec) Above, we have set the ENGINE as “MyISAM”. To check how many columns are present in the table, use DESC command. mysql> DESC StudentRecordWithMyISAM; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ...
Read MoreHow to convert MyISAM to InnoDB storage engine in MySQL?
To convert the MyISAM Engine to InnoDB, we can use the ALTER command. Let us now create a table with the help of engine MyISAM. mysql> create table MyISAMToInnoDBDemo -> ( -> id int, -> Name varchar(100) -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.19 sec) To check if the table is created with engine MyISAM or not. mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'business' and ENGINE = 'MyISAM'; The following is the output that displays the table created with MyISAM ...
Read MoreHow to debug Lock wait timeout exceeded on MySQL?
The debug Lock wait timeout situation occurs because of some threads. If one thread is holding on to some records for a very long time, it means the thread has exceeded time. To see all the details, implement the following query − mysql> SHOW ENGINE INNODB STATUS; The following is the output. +--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status ...
Read MoreWhy doesn't MySQL support millisecond / microsecond precision?
The millisecond/ microsecond precision wasn’t supported in previous versions like 5.6.4. But now MySQL supports millisecond/ microsecond precision with timestamp, datetime, and time. The official statement. “MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision”. You can check the MySQL version on your system using the version() method. mysql> SELECT version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.01 sec) Let us now see the syntax to check the date difference. mysql> SELECT DATEDIFF(now(), ...
Read More