Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. MySQL Group Replication

    So MySQL's group replication came out with MySQL 5.7. Now that is has been out a little while people are starting to ask more about it. https://dev.mysql.com/doc/refman/8.0/en/group-replication.html https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html Below is an example of how to set this up and a few pain point examples as I poked around with it.I am using three different servers, Server CENTOSA mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.02 sec)vi my.cnf disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=1gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONElog_bin=binloglog_slave_updates=ONbinlog_format=ROWmaster_info_repository=TABLErelay_log_info_repository=TABLEtransaction_write_set_extraction=XXHASH64group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" group_replication_start_on_boot=offgroup_replication_local_address= "192.168.111.17:33061"group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"group_replication_bootstrap_group=off mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword';mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='replpassword' FOR CHANNEL 'group_replication_recovery';mysql> SET GLOBAL group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.11 sec)mysql> SET GLOBAL group_replication_bootstrap_group=OFF;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM performance_schema.replication_group_members \G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 MEMBER_HOST: centosa MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.15So now we can add more servers.Server CENTOSBhttps://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html vi my.cnf disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=2gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONElog_bin=binloglog_slave_updates=ONbinlog_format=ROWmaster_info_repository=TABLErelay_log_info_repository=TABLEtransaction_write_set_extraction=XXHASH64group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"group_replication_start_on_boot=offgroup_replication_local_address= "192.168.111.89:33061"group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"group_replication_bootstrap_group=off mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='replpassword' FOR CHANNEL 'group_replication_recovery';Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;Query OK, 0 rows affected (0.02 sec)mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (4.03 sec)mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec) Server CENTOSC https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html vi my.cnf disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=3gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONE log_bin=binloglog_slave_updates=ONbinlog_format=ROWmaster_info_repository=TABLErelay_log_info_repository=TABLEtransaction_write_set_extraction=XXHASH64group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"group_replication_start_on_boot=offgroup_replication_local_address= "192.168.111.124:33061"group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"group_replication_bootstrap_group=off mysql> CHANGE MASTER TO -> MASTER_USER='repl', -> MASTER_PASSWORD='replpassword' -> FOR CHANNEL 'group_replication_recovery';Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;Query OK, 0 rows affected (0.02 sec) mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.58 sec) mysql> SELECT * FROM performance_schema.replication_group_members \G*************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 MEMBER_HOST: centosa MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.15*************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1 MEMBER_HOST: centosb MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARYMEMBER_VERSION: 8.0.15*************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6 MEMBER_HOST: centosc MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARYMEMBER_VERSION: 8.0.153 rows in set (0.00 sec) So this is all great but it doesn't always mean they go online, they can often sit in recovery mode.I have seen this fail with MySQL crashes so far so need to ensure it stable. mysql> create database testcentosb;<br> ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement<br> Side Note to address some of those factors -- mysql> START GROUP_REPLICATION;ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.mysql> reset slave all;Query OK, 0 rows affected (0.03 sec) -- Then start over from Change master command mysql> START GROUP_REPLICATION;ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.111.17:33061 on local port: 33061.'[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 > [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/   [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the recovery process of Group Replication. The server will leave the group.'[ERROR] [MY-013173] [Repl] Plugin group_replication reported: 'The plugin encountered a critical error and will abort: Fatal error during execution of Group Replication'SELECT * FROM performance_schema.replication_connection_status\G My thoughts...Keep in mind that group replication can be set up in single primary mode or multi-node mysql> select @@group_replication_single_primary_mode\G*************************** 1. row ***************************@@group_replication_single_primary_mode: 1mysql> create database testcentosb;ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementyou will of course get an error if you write to none primary node. group-replication-single-primary-mode=off  <-- added to the cnf files.  mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         | | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb     |        3306 | ONLINE       | PRIMARY     | 8.0.15         | | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) It is now however if you use Keepalived, MySQL router, ProxySQL etc to handle your traffic to automatically roll over in case of a failover. We can see from below it failed over right away when I stopped the primary. mysql> SELECT * FROM performance_schema.replication_group_members ;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 || group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 || group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec)[root@centosa]# systemctl stop mysqldmysql> SELECT * FROM performance_schema.replication_group_members ;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 || group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+2 rows in set (0.00 sec)[root@centosa]# systemctl start mysqld[root@centosa]# mysqlmysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.34 sec)mysql> SELECT * FROM performance_schema.replication_group_members ;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 || group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 || group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec) Now the recovery was still an issue, as it is would not simply join back. Had to review all accounts and steps again but I did get it back eventually. mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 || group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 || group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec) I need to test more with this as I am not 100% sold yet as to needing this as I lean towards Galera replication still.URLS of Interesthttps://dev.mysql.com/doc/refman/8.0/en/group-replication.html https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html  https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-instances.html https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/ https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04  https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds  https://bugs.mysql.com/bug.php?id=90534 https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/ https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/ https://www.youtube.com/watch?v=IfZK-Up03Mw https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/
  2. SQL Right Join Tutorial With Example | Right Outer Join In SQL

    SQL Right Join Tutorial With Example | Right Outer Join In SQL is today’s topic. We have already seen the Left Join, Full Outer Join, Cross Join, Self Join, and Inner Join in this blog. The RIGHT JOIN keyword returns all the records from a right table (table2), and the matched records from a left table (table1). The result is NULL from the left side when there is no match. In some databases, the RIGHT JOIN is called RIGHT OUTER JOIN. The RIGHT JOIN clause allows us to query data from the multiple tables. SQL Right Join Tutorial With Example See the following VENN Diagram of SQL Right Join.   The syntax of SQL Right Join is the following. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; In this syntax, table1 and table2 are the left and right tables, respectively. For each row from the table2(Right) table, the query compares it with all the rows from the table1(left) table. If the pair of rows causes the join predicate to evaluate to true, the column values from these rows will be in the combined form to the new row which then included in the final result set. If a row from the right table (table2) does not have any matching row from the table1(Left) table, the query combines column values of a row from the right table(table2) with NULL for each column values from the left table. In short, the RIGHT JOIN clause returns all rows from the right table (table2) and matching the rows or NULL values from the left table (table1). #SQL RIGHT JOIN Example First, we need to create two tables. If you do not know how to create the table in SQL, then check out SQL Create Table tutorial. Now, create the first table. #SQL Create Tables Run the following query to create Products table. CREATE TABLE Products ( ProductID int PRIMARY KEY AUTO_INCREMENT, ProductName varchar(255) NOT NULL, ProductPrice int NOT NULL ); You can see in the table in the database. The varchar, int is SQL Datatypes. Now, create a second table using the following query. CREATE TABLE Orders ( OrderID int PRIMARY KEY AUTO_INCREMENT, ProductID int NOT NULL, OrderDate date NOT NULL ); So, we have created the Products and Orders table. Now, we need to fill the data into the tables. #INSERT Data into Tables Next thing to do is that we need to insert the data into the database. Insert the data into the Products and Orders table using INSERT INTO statement. INSERT INTO Products (`ProductName`,`ProductPrice`) VALUES ('LV INITIALES', 60 ), ('KEEPALL BANDOULIÈRE 50', 70 ), ('KEEPALL BANDOULIÈRE 50', 80 ), ('AFTERGAME SNEAKER', 90 ), ('FRONTROW SNEAKER', 100 ) Run the above code, and you will see the data is filled inside the Products with these values. Now, we need to add Orders data. See let’s do that. See the below query. INSERT INTO Orders (`ProductID`,`OrderDate`) VALUES (1, NOW() ), (3, NOW() ), (3, NOW() ), (1, NOW() ), (2, NOW() ), (4, NOW() ) We have used SQL NOW() function to create current data. Run the above query and data is created inside the Orders table. So we have created two tables with the data. Now, we will use SQL RIGHT JOIN to query the data and fetch the results. SELECT p.ProductID, p.ProductName, o.OrderDate FROM Products p RIGHT JOIN Orders o ON o.ProductID = p.ProductID ORDER BY o.OrderID; See the following output.   So, in the above query, we are fetching three columns. p.ProductID means in the Products table fetch the ProductID. p.productName means in the Products table fetch the ProductName. o,orderDate means in the Orders table fetch the OrderDate. We have Right Join Products with Orders table in which ProductID is common in both the tables. In the Orders table, ProductID is a foreign key. Each sales order item includes one product. The link between the Products and the Orders tables is via the values in the ProductID column. So, based on the foreign key, it maps the result in the final table. In the final table, all the rows from the right table are included. In our case, the right table is Orders. So, all the Orders table row will be added and on the right side, if the condition is specified then the rows will be included from the left table otherwise null will be returned for a particular column. See the following query. SELECT o.OrderID, p.ProductID, p.ProductName FROM Products p RIGHT JOIN Orders o ON o.ProductID = p.ProductID ORDER BY o.OrderID; See the output.   So, we have seen how to create a table and then use the Right JOIN to fetch the records from two tables. Conclusively, SQL RIGHT Join Tutorial With Example | Right Outer Join in SQL article is over. The post SQL Right Join Tutorial With Example | Right Outer Join In SQL appeared first on AppDividend.
  3. SQL Datatypes Tutorial | Datatypes In SQL Explained

    SQL Datatypes Tutorial With Example | Datatypes In SQL is today’s topic. Data types are used to represent a nature of a data that can be stored in the database. The data type is the set of representable values. It is also known as the attribute that specifies a type of data of the object. Each column, variable, and expression has the related data type in the SQL while creating the table. In the last tutorial, we have seen the SQL Date Time Functions. Now, let’s start our SQL Datatypes Tutorial. #SQL Datatypes important points Relational database vendors support not all data types. For example, the Oracle database doesn’t support a DATETIME, and MySQL doesn’t support a CLOB data type. So while designing the database schema and writing the SQL queries, make sure to check if the data types are supported or not. Datatypes listed here doesn’t include all the data types; these are the most popularly used data types. Some relational database vendors have their data types that might be not listed here. For example, the Microsoft SQL Server has money and smallmoney data types, but since other favorite database vendors do not support it, it’s not listed here. Every relational database vendor has its maximum size limit for the different data types, and you don’t need to remember a limit. The idea is to know what data type to be used in a specific scenario. SQL Datatypes Tutorial The SQL developer must decide what type of the data that will be stored inside each column when creating the table. The data type is the guideline for SQL to understand what kind of data is expected inside of each column, and it also identifies how the SQL will interact with the stored data. SQL Datatypes mainly classified into six categories for every database. String Datatypes Numeric Datatypes Date and time Datatypes Binary data types like binary, varbinary, etc. Unicode character string datatypes such as nchar, nvarchar, ntext, etc. Miscellaneous data types such as clob, blob, XML, cursor, table, etc. #SQL Numeric Data Types DATATYPE FROM TO bit 0 1 tinyint 0 255 smallint -32,768 32,767 int -2,147,483,648 2,147,483,647 bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 decimal -10^38 +1 10^38 -1 numeric -10^38 +1 10^38 -1 float -1.79E + 308 1.79E + 308 real -3.40E + 38 3.40E + 38 #SQL Date and Time Data Types DATATYPE DESCRIPTION DATE Stores date in the format YYYY-MM-DD TIME Stores time in the format HH:MI:SS DATETIME Stores date and time information in the format YYYY-MM-DD HH:MI:SS TIMESTAMP Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC) YEAR Stores year in 2 digits or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069. #SQL Character and String Data Types DATATYPE DESCRIPTION CHAR Fixed length with a maximum length of 8,000 characters VARCHAR Variable length storage with a maximum length of 8,000 characters VARCHAR(max) Variable length storage with provided max characters, not supported in the MySQL TEXT The variable length storage with a maximum size of 2GB data Note that all the above data types are for the character stream; they should not be used with a Unicode data. #SQL Unicode Character and String Data Types DATATYPE DESCRIPTION NCHAR Fixed length with a maximum length of 4,000 characters NVARCHAR Variable length storage with a maximum length of 4,000 characters NVARCHAR(max) Variable length storage with provided max characters NTEXT Variable length storage with a maximum size of 1GB data Note that above data types are not supported in the MySQL database. #SQL Binary Data Types DATATYPE DESCRIPTION BINARY Fixed length with a maximum length of 8,000 bytes VARBINARY Variable length storage with a maximum length of 8,000 bytes VARBINARY(max) Variable length storage with provided max bytes IMAGE Variable length storage with a maximum size of 2GB binary data #SQL Miscellaneous Data Types DATATYPE DESCRIPTION CLOB Character large objects that can hold up to 2GB BLOB For large binary objects XML for storing XML data JSON for storing JSON data #MySQL String Datatypes Varchar(size) It is used for specifying a variable length string that can contain numbers, letters, and special characters. Its size limit is 0 to 65535 characters. Char(size) It is used for specifying a fixed length string that can contain numbers, letters, and special characters. By default, it can hold 1 character. Its size limit is 0 to 255 characters. VARBINARY(size) It is as similar as VARCHAR(), and the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes. Binary(size) It is used for storing binary byte strings. The default value is 1, and its size parameter specifies the column length in bytes. TINYTEXT It holds a string with a max value of 255 characters. TEXT(size) It is used for storing a string with a max length of 255 characters which is similar to CHAR(). LONGTEXT It holds the string with a max value of 4,294,967,295 characters.   MEDIUMTEXT It holds the string with a max value of 16,777,215 characters which is quite larger than VARCHAR() ENUM(val1,val2,….) It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65535 values in the ENUM list. If a value is inserted, that is not in a list, and the empty value will be inserted. The values are sorted in order at the time of entering. SET(val1,val2,……) It is used to specify the string that can has 0 or more values, chosen from a list of possible values. At one time, 64 values can be listed. BLOB(size) It is used for large binary objects which can hold up to 65535 bytes.   #MySQL NUMERIC DATATYPES BIT (size) Used for a bit value type. Size is used for specifying the number of bits. The range is from 1-64. By default, value is 1. INT (size) Used for the integer value. The range is from -2147483648-2147483647. The size parameter specifies the max display width of 255. INTEGER (size) It is similar to INT (size). FLOAT (size,d) Used for floating point number. The size parameter specifies the total number of digits. d is used for setting the number of several digits after the decimal point. Float(p) Used for a floating point as well as double type. If the value of p is from 0-24, then the data becomes float, and if the value of p is from 25-53, then the data becomes double. DOUBLE (size,d) It is similar to FLOAT(size,d). DECIMAL(size,d) Used for specifying a fixed-point number. The maximum value size can hold 65, and by default, its value will be 10 and d can hold a maximum value of 30, and by default, value is 0. BOOL Used for specifying Boolean values. Zero is considered as false and remaining non-zero values as true.   #MySQL DATE AND TIME DATATYPES DATE Used for specifying the date format. In MySQL the format is YYYY-MM-DD. The range is from ‘1000-01-01’ to ‘9999-12-31’. DATETIME(fsp) Used for specifying date and time combination. The format is YYYY-MM-DD hh:mm:ss. Range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. TIMESTAMP(fsp) Used for specifying the timestamp. The format is YYYY-MM-DD hh:mm:ss. Its supported range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. TIME(fsp) Used for specifying the time format. The format is hh:mm:ss. The range is from ‘-838:59:59’ to ‘838:59:59’. YEAR Used for specifying the year in four-digit format. The range is from 1901 to 2155 and 0000.   The above datatypes, as discussed above, are used for creating tables for beginners. Let me show you where this datatype is used. Suppose we want to create a table: (STUDENT) The following attributes are: ID, NAME, CITY, DOJ SYNTAX: Create table table_name (column1 datatype, column2 datatype,…………); So, the query will be following. Create table student (ID integer, NAME varchar (25), CITY char (10), DOJ DATE); Now, Integer, varchar (25), char (10), DATE this all are datatypes which describes what kind of data is to be stored. Now let’s discuss some server data types which are used in MySQL. #SQL Server String Data Type    Char(n) It is a fixed width character string data type. A range is 8000 characters. varchar(n) It is a variable width character string data type. A range is 8000 characters. varchar(max) It is a variable width character string data type. Its size can be up to 1,073,741,824 characters. text It is a variable width character string data type. Its size can be up to 2GB of text data. nchar It is a fixed width Unicode string data type. Its size can be up to 4000 characters. nvarchar It is a variable width Unicode string data type. Its size can be up to 4000 characters. ntext It is a variable width Unicode string data type. Its size can be up to 2GB. binary(n) It is a fixed width Binary string data type. Its size can be up to 8000 bytes. varbinary It is a variable width Binary string data type. Its size can be up to 8000 bytes. image It is also a variable width Binary string data type. Its size can be up to 2GB.   #SQL Server Numeric Data Types Bit It is an integer that can be 0, 1, or null. Tinyint It allows whole numbers from 0 to 255. Smallint It allows whole numbers in the range -32,768 and 32,767. Int It allows whole numbers between -2,147,483,648 and 2,147,483,647. bigint It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. float(n) It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. The default value of n is 53. real It is a floating precision number data from -3.40E+38 to 3.40E+38. money It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.   #SQL Server Date and Time Data Type datetime It is used to specify the date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. datetime2 It is used to specify the date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. date It is used to store the date only. It supports range from January 1, 0001 to December 31, 9999 Time It stores time only to an accuracy of 100 nanoseconds. timestamp It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only a one-time stamp variable.   #SQL Server Other Data Types( Miscellaneous Data Types) Sql_variant It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. XML It stores XML formatted data. Maximum 2GB. cursor It stores a reference to a cursor used for database operations. Table It stores result set for later processing. Uniqueidentifier It stores GUID (Globally unique identifier).   #Oracle Datatypes #Oracle String data types CHAR(size) It is used to store character data. It can be stored up to 2000 bytes. NCHAR(size) It is used to store national character data within the predefined length. It can be stored up to 2000 bytes. VARCHAR2(size) It is used to store variable string data within the predefined length. It can be stored up to 4000 bytes. VARCHAR(SIZE) It is the same as VARCHAR2(size). NVARCHAR2(size) It is used to store Unicode string data within the predefined length. We have to must specify the size of an NVARCHAR2 data type. It can be stored up to 4000 bytes.   #Oracle Numeric Data Types NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127. FLOAT(p) It is a subtype of the NUMBER data type. The precision p can range from 1 to 126. BINARY_FLOAT It is used for binary precision ( 32-bit). It requires 5 bytes, including length byte. BINARY_DOUBLE It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.   #Oracle Date and Time Data Types DATE It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD. TIMESTAMP It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.   #Oracle Large Object Data Types (LOB Types) BLOB Used for specifying unstructured binary data. Its range goes up to 232-1 bytes or 4 GB. BFILE Used for storing binary data in an external file. Its range is from 232-1 bytes or 4 GB. CLOB It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB. NCLOB It is used to specify a single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB. RAW(size) It is used to specify the variable length of raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be determined. LONG RAW It is used to specify the variable length of raw binary data. Its range up to 231-1 bytes or 2 GB, per row.   We have not only written SQL Datatypes, but also, MySQL Datatypes, Oracle Datatypes, and SQL Server Datatypes in this tutorial. Finally, SQL Datatypes Tutorial | Datatypes In SQL Explained is over. The post SQL Datatypes Tutorial | Datatypes In SQL Explained appeared first on AppDividend.
  4. SQL Self Join Tutorial With Example | Self Join in SQL

    SQL Self Join Tutorial With Example | Self Join in SQL is today’s topic. A self-join is the join in which a table is joined with itself means we are joining a table with that same table (which is also called Unary relationships), especially when the table has the FOREIGN KEY which references its PRIMARY KEY. If we want to join the table itself means that each row of the table is combined with itself and with every other row of the table. The self join allows you to join the table to itself. It is useful for querying the hierarchical data or comparing rows within the same table. Till now, we have seen the Outer Join, Cross Join, Left Join, SQL Joins Overview and Inner Join in this blog. #SQL Self JOIN Key Points The self JOIN occurs when a table takes a ‘selfie.’ The self JOIN is a regular join, but the table is joined with itself. It can be useful when modeling hierarchies. They are also useful for comparisons within the table. You use the self join when the table references data in itself. One most used example is where you wanted to get a list of employees and their immediate managers. SQL Self Join Tutorial With Example The self join uses an inner join or left join clause. Because the query that uses the self join references a same table, the table alias is used to assign the different names to a same table within the query. The self-join can be viewed as a join of two copies of the same table. The table is not copied, but SQL performs a command as though it were. The syntax of a command for joining a table to itself is almost the same as that for the joining two different tables. If we want to distinguish the column names from one another, aliases for an actual the table name are used, since both the tables have a same name. Table name aliases are defined in a FROM clause of the SELECT statement. Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; In the above syntax, T1 and T2 are different table aliases for the same table. See the following table. Products   #SQL Self JOIN Example The following SQL statement matches Products that have different categories. SELECT A.ProductID, A.ProductName AS ProductNameA, B.ProductName AS ProductNameB, A.Category FROM Products A, Products B WHERE A.`ProductName` <> B.`ProductName` AND A.`Category` = B.`Category` ORDER BY B.`ProductName`; See the following output.   In the above query, we have used a WHERE condition and ORDER BY clause. Finally, SQL Self Join Tutorial With Example | Self Join in SQL is over. The post SQL Self Join Tutorial With Example | Self Join in SQL appeared first on AppDividend.
  5. SQL Date Functions Tutorial With Example | Date and Time in SQL

    SQL Date Functions Tutorial With Example | Date and Time in SQL is today’s topic. In this section, we cover standard date functions in SQL. The different database system has different formats for date type data, and each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is same. SQL Date Functions Tutorial With Example MySQL comes with the following data types for storing a date or a date/time value in the database: DATE – format YYYY-MM-DD DATETIME – format: YYYY-MM-DD HH:MI:SS TIMESTAMP – format: YYYY-MM-DD HH:MI:SS YEAR – format YYYY or YY LIST OF SQL DATE FUNCTIONS #ADDDATE() It returns a date after a certain time/date interval has been added. select adddate("2019-06-09 02:52:47","7"); See the output.     #ADDTIME() It returns a time/date time after a certain time interval has been added. select addtime("2019-06-09 02:52:47","2"); See the Output.     #CURDATE() It returns the current date. select curdate(); See the output.   #CURRENT_DATE() It returns the current date. select current_date(); See the below output.   #CURRENT_TIME() It returns the current time. select current_time(); See the below output.   #CURRENT_TIMESTAMP() It returns the current date and time. select current_timestamp(); See the output.   #CURTIME()  It returns the current time. select curtime(); See the output.   #DATE() It extracts the date value from a date-time expression. select date("2019-06-10"); See the output.   #DATEDIFF()  It returns the difference in days between two date values. select datediff("2019-06-16","2019-06-10"); See the output.   #DATE_ADD()  It returns a date after a certain time/date interval has been added. select date_add("2019-06-05", INTERVAL 10 DAY); See the following output.   #DATE_FORMAT()  It formats a date as specified by a format mask. select DATE_FORMAT("2018-06-15", "%Y"); See the following output.   #DATE_SUB() It returns a date after a certain time/date interval has been subtracted. SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY); See the following output.   #DAY() It returns the day portion of a date value. SELECT DAY("2019-07-15"); See the output.   #DAYNAME()  It returns the weekday name for a date. SELECT DAYNAME('2008-05-15'); See the output.   #DAYOFMONTH() It returns the day portion of a date value. SELECT DAYOFMONTH('2018-07-16'); See the output.   #DAYWEEK() It returns the weekday index for a date value. SELECT WEEKDAY("2019-07-16"); See the output.   #DAYOFYEAR() It returns the day of the year for a date value. SELECT DAYOFYEAR("2019-07-16"); See the output.   #EXTRACT()  It extracts parts from a date. SELECT EXTRACT(MONTH FROM "2018-07-16"); See the output.   #FROM_DAYS() It returns a date value from a numeric representation of the day. select from_days(1234567); See the output.   #HOUR() It returns the hour portion of a date value. select hour("2019-01-16 09:25:27"); See the output.   #LAST_DAY() It returns the last day of the month for a given date. select last_day('2019-01-25'); See the output.   #LOCALTIME() It returns the current date and time. select localtime(); See the output.     #LOCALTIMESTAMP() It returns the current date and time. select localtimestamp(); See the output.     #MAKEDATE() It returns the date for a particular year. select makedate(2007,128); See the output.   #MAKETIME() It returns the time for a particular hour, minute, second combination. select maketime(10,25,4); See the output.   #MICROSECOND() It returns the microsecond portion of a date value. select microsecond("2019-06-19 09:10:45.000245"); See the output.   #MINUTE() It returns the minute portion of a date value. select minute("2019-08-20 09:12:00"); See the output.   #MONTH() It returns the month portion of a date value. select month('2019/01/15'); See the output.   #MONTHNAME()  It returns the full month name for a date. select monthname('2019/1/16'); See the output.   #NOW()  It returns the current date and time. select now(); See the output.   #PERIOD_ADD()  It takes a period and adds a specified number of months to it. select period_add(201803, 6); See the output.   #PERIOD_DIFF() It returns the difference in months between two periods. SELECT PERIOD_DIFF(201810, 201802); See the output.   #QUARTER() It returns the quarter portion of a date value. SELECT QUARTER("2018/07/18"); See the output.   #SECOND() It returns the second portion of a date value. SELECT SECOND("09:14:00:00032"); See the output.   #SEC_TO_TIME() It converts numeric seconds into a time value. SELECT SEC_TO_TIME(1); See the output.   #STR_TO_DATE() It takes a string and returns a date specified by a format mask. SELECT STR_TO_DATE("JULY 18 2019", "%M %D %Y"); See the output.   #SUBDATE()  It returns a date after which a certain time/date interval has been subtracted. SELECT SUBDATE("2019-06-15", INTERVAL 10 DAY); See the output.   #SYSDATE()  It returns the current date and time. SELECT SYSDATE(); See the output.   #TIME() It extracts the time value from a time/date time expression. SELECT TIME("09:16:10"); See the output.   #TIME_FORMAT() It formats the time as specified by a format mask. SELECT TIME_FORMAT("09:16:10", "%H %I %S"); See the output.   #TIME_TO_SEC()  It converts a time value into numeric seconds. SELECT TIME_TO_SEC("09:16:10"); See the output.   #TIMEDIFF() It returns the difference between two time/datetime values. SELECT TIMEDIFF("09:16:10", "09:16:04"); See the output.   #TIMESTAMP()  It converts an expression to a date-time value and if specified, adds an optional time interval to the value. SELECT TIMESTAMP("2019-06-10", "08:16:10"); See the output.   #TO_DAYS() It converts a date into numeric days. SELECT TO_DAYS("2018-07-18"); See the output.   #WEEK() It returns the week portion of a date value. SELECT WEEK("2018-06-18"); See the output.   #WEEKDAY() It returns the weekday index for a date value. SELECT WEEKDAY("2018-07-18"); See the output.   #WEEKOFYEAR() It returns the week of the year for a date value. SELECT WEEKOFYEAR("2018-07-18"); See the output.   #YEAR()  It returns the year portion of a date value. SELECT YEAR("2019-07-18"); See the output.   #YEARWEEK() It returns the year and week for a date value. SELECT YEARWEEK("2019-06-18"); See the output.   Finally, SQL Date Functions Tutorial With Example | Date and Time in SQL is over. The post SQL Date Functions Tutorial With Example | Date and Time in SQL appeared first on AppDividend.

Upcoming Events

Latest News

Visitors

We have 67 guests and no members online