MySQL database and MongoDB database related operations and the difference of two databases

Performing data operations in the Docs command

MySQL Database

Start MySQL server net start MySQL first

Go to MySQL server mysql-uroot-p (write your database password here) (-P is the port from which to enter)

The first time I did it was because I had MySQL on my computer. MySQL57 Mysqlla three services causing port conflicts

Cause I can't find the corresponding data

Database operations

Create Database

Show databases displays the database on the MySQL service

Use database using databases

Data table Operations

CREATE table table name () Creates a data table

For example, CREATE TABLE user (ID int,name char,age int)

DESC table name Gets the structure of the table

Show tables displays the tables in the current database

drop TABLE Table name Delete datasheet

Rename table name to new table name modify data table names

Data manipulation in tables

Insert into Userone (id,name) VALUES (1, "BBB"); Add data

SELECT * from table name; Querying all data in a data table

Select username from Userone; Query a column

SELECT * from Userone username= "AVC"; Query a single piece of data by conditional query

Update Userone set password= "QQQ" where username= "111"; Update a piece of data data

Delete from Userone where username= "111"; Delete a piece of data

MongoDB Database

To use a MongoDB database, you first need to

Installing the MongoDB Database

NPM Install MongoDB

Start MongoDB Service

net start MongoDB

Enter the MongoDB database

Mongo

Show DBS shows all databases in MongoDB

Use database name to create a database

Db.dropdatabase (database name) Delete database

DB View Current Database

Db.createcollection ("database name") Create a collection

Show Tables/collections Display Collection

Db. Collection name. Drop () Delete collection

Db.person.insert ({"Name": "GG", "Age": 12}); Insert Data "

Db.person.save ({"name": "AA", "Age": 12}) Insert data If you insert the current collection ID value the same save will not error and insert will error

Db.person.remove ({content}) Delete data

Db.person.find () Querying all data in the current collection

Db.person.find ({"Age": {$lt: 12}}) signed query greater than $GT less than $lt not equal to $ne

Less than or equal to $lte greater than or equal to $gte

Db.person.update ({condition},{content},true,false) Update data

Updated document with modifier

Db.person.update ({condition},{$set: {content},true,true})

The third true if there are no query criteria for the data to perform the insert operation

The fourth true batch modification false instead of

$inc used for numeric types to add and subtract values that correspond to a specified key

Advantages and disadvantages of MongoDB versus MySQL The advantages of MongoDB compared to relational databases:① Weak consistency (eventually consistent), to ensure the user's access speed:

For example, in a traditional relational database, a count type of operation locks the dataset so that the exact value in the "current" case can be guaranteed. This is important in some cases, such as checking account information through ATM, but for Wordnik, the data is constantly being updated and growing, and this "precise" guarantee has little meaning, but can have a significant delay. What they need is a "about" number and faster processing speed.

The ② document structure is stored in a way that makes it easier to get data.

For a hierarchical data structure, if you want to use a flat, tabular structure to hold the data, it is difficult to query or retrieve data.

The ③ built-in GRIDFS supports high-capacity storage.

Gridfs is an excellent distributed file system that can support massive data storage. Built-in Gridfs, MongoDB, can meet the fast range of large data set query. ④ built-in sharding. Provides a range-based auto sharding mechanism: A collection can be divided into several segments according to the range of records, to be segmented onto different shard.

⑤ third-party support is plentiful. (This is the advantage of MongoDB compared to other NoSQL)

Many of the NoSQL open source databases on the Web are entirely community-based, with no official support, and pose a significant risk to users. and the open source document database MongoDB behind the commercial company 10gen provides it for business training and support.
And the MongoDB community is very active, and many development frameworks quickly provide support for MONGDB. Many well-known big companies and websites also use MongoDB in the production environment, and more and more innovative enterprises use MONGODB as the technical solution to match django,ror.

⑥ Superior Performance:

In the case of Tens other document objects, nearly 10G of data, queries for indexed IDs are no slower than MySQL, while queries on non-indexed fields are fully victorious. MySQL is not really competent for queries of any field under large data volumes, and MongoDB's query performance is surprising to me. Write performance is also very satisfying, also write millions other data, MongoDB than I have tried before the couchdb is much faster, basic 10 minutes below can be resolved. To make up a sentence, the observation process of MongoDB is far from being a CPU killer.

The disadvantages of MongoDB compared to relational databases:①mongodb does not support transactional operations.

So a strict system of transactions (if the banking system) must not be able to use it. (This and the merit ① are corresponding)

②mongodb occupies too much space.

For its reasons, in the official FAQ, the following aspects are mentioned:
1, space pre-allocation: In order to avoid the formation of excessive hard disk fragmentation, MongoDB each time the space shortage will be requested to generate a large chunk of hard disk space, and the amount of applications from 64M, 128M, 256M, such as the exponential increment, until 2G for the maximum volume of a single file. As the amount of data increases, you can see in their data directory the entire volume of files that have been incrementally generated.

2, the space occupied by the field name: In order to keep the structure information within each record used for querying, MongoDB needs to store the key-value of each field in the form of Bson, if the value field is not large relative to the key field, such as storing the data of numeric type, The overhead of the data is the largest. One way to reduce space usage is to take the field names as short as possible, so that space is small, but this requires a tradeoff between legibility and space occupancy. I have suggested that the author put the field name index, each of the field names in one byte, so you don't have to worry about how long the field name takes. But the author's concern is not unreasonable, this index method needs to be a result of each query to replace the index value with the original value, and then send to the client, this substitution is also very time-consuming. Now the realization is to take the space to exchange time.

3, delete the record does not free space: This is easy to understand, in order to avoid the record deleted after the large-scale movement of data, the original record space is not deleted, only marked "deleted" can be reused later.

4, can regularly run db.repairdatabase () to organize records, but this process will be relatively slow

③mongodb does not have a proven maintenance tool like MySQL, which is a noteworthy area for development and IT operations.

MySQL database and MongoDB database related operations and the difference of two databases