The difference between SQL and NoSQL

First, the concept of SQL (structured Query Language) database, refers to the relational database.　　Main representative: SQL Server,oracle,mysql (open source), PostgreSQL (open source). NoSQL (not only SQL) refers to a non-relational database. Main representative: MONGODB,REDIS,COUCHDB. Second, the difference 1. Storage modeSQL data exists in a structure-specific table, and NoSQL is more flexible and extensible, and storage can be saved in JSON documents, hash tables, or other ways. SQL typically stores data as a database table. Take a chestnut and save a student. Library data: While NoSQL storage is more flexible, such as using a class JSON file to store the big borrowing data from the table above: 2. Relationship of data in table/data setIn SQL, you must define the table and field structure before you can add data, such as defining a table's primary key (primary key), index, trigger (trigger), stored procedure (stored procedure), and so on. The table structure can be updated after it is defined, but it becomes more complex if there are larger structural changes. In NoSQL, data can be added anywhere, at any time, without having to define a table first. For example, the following code automatically creates a new "borrow Table" data collection: NoSQL can also build indexes in datasets. In MongoDB, for example, a unique value _id field is automatically created after the data collection is created, so that you can increase the index after the dataset is created. From this point of view, NoSQL may be more suitable for projects that initialize data that are unclear or undecided. 3. External Data storageIn SQL, it is a normalization practice to add a foreign key to an external data table in the original table if you want to increase the external associated data. For example, you need to add the reviewer information in the Borrowing table, first set up a reviewer table: then the original borrower table to increase the reviewer foreign key: So if we need to update the reviewer's personal information only need to update the reviewer table and do not need to update the borrower table. In addition to this normalized external data table approach in NoSQL, we can also use the following denormalized methods to put external data directly into the original data set to improve query efficiency. Shortcomings are also more obvious, updating the reviewer data will be more troublesome. 4. Join query in SQLIn SQL, you can use join table connections to query data from multiple relational data tables with a simple query statement. NoSQL does not provide query methods like join to query data in multiple datasets. So most NoSQL uses non-normalized data storage to store data. 5. Coupling of dataSQL does not allow the deletion of external data that has already been used, such as "Bear three" in the reviewer table has been assigned to the borrower bear big, then in the reviewer table will not be allowed to delete the bear three this data, to ensure data integrity. There is no such strong coupling concept in NoSQL, and any data can be deleted at any time. 6. BusinessIf multiple table data needs to be updated in SQL if one of the tables fails to update, the other tables cannot be updated successfully. This scenario can be controlled by a transaction, and the transaction can be committed uniformly after all the commands have been completed. There is no concept of transaction in NoSQL, and the operation of each dataset is atomic. 7, increase the deletion check grammar 8. Query PerformanceUnder the premise of the same level of system design, because the consumption of join query is omitted in NoSQL, the performance is better than SQL in theory. Third, supplementaryAt present, many large Internet projects will choose MySQL (or any relational database) + NoSQL combination scheme. relational databases are suitable for storing structured data, such as the user's account number, Address: 1) These data often require structured queries (well, as if it's nonsense), such as join, when the relational database will win a 2) the scale of the data, the speed of growth is usually expected 3) transactional, consistent　　NoSQL is ideal for storing unstructured data, such as articles, comments: 1) These data are often used for obfuscation, such as full-text search, machine learning 2, and the data is massive, and the speed of growth is unpredictable, and 3) based on the characteristics of the data, NoSQL databases typically have unlimited (at least approximate) scalability 4) It is very efficient to get data by key, but the support for join or other structured queries is relatively poor based on their scope of application, the current mainstream architecture will adopt a combination of solutions, one can not be less.  So far, there has not been a database that takes all kinds of scenarios, and according to the CAP theory, such a database does not exist. Ext.: https://www.cnblogs.com/jeakeven/p/5402095.html

The difference between SQL and NoSQL