Basic concepts of database

learning target

◆ Be able to know how to configure the MySQL database environment

◆ Be able to recognize and use common SQL statements to operate the database

◆ Ability to operate MySQL database in the project

Article Directory

One, the basic concept of the database


1. What is a database

1. A database is a warehouse used to organize, store and manage data

2. In order to facilitate the management of data in the Internet world, there is the concept of a database management system (abbreviated as: database). Users can add, query, update, delete and other operations on the data in the database


2. Common databases and classifications

Common database classifications are as follows

  • MySQLDatabase (currently the most widely used and most popular open source free database; Community+ Enterprise)
  • Oracle Database (fees apply)
  • SQL Server Database (fees apply)
  • MongodbDatabase ( Community+ Enterprise)

Database classification

  • Traditional database (relational database or SQL database)
  • MySQL, Oracle, SQL Server, the design concepts of these three are the same, and the usage is similar
  • New database (non-relational database or NoSQL database)
  • Mongodb, it makes up for the shortcomings of traditional databases to a certain extent


Two, SQL statement

SQL` is Structured Query Language , devoted to programming language to access and manipulate databases . Allows us to manipulate the data in the database in the form of programming

Three key points:

  • SQLIs a database programming language
  • Use SQLcode written out of the language called SQL statements
  • SQLLanguage can only be used (for example in a relational database MySQL, Oracle, SQL Server). Non-relational databases (for example Mongodb) does not support the SQLlanguage


Three, MySQL module

  1. Mounting operation MySQLthird party database module ( mysql)
  2. By mysqlconnecting the module to the MySQLdatabase
  3. By mysqlexecuting module SQLstatement

1. Install and configure the mysql module

Install the mysql module

mysqlModules are hosted npmon third-party modules. It provides the Node.jsconnection and operation of the project MySQLdatabase capabilities. Want to use it in your project, you need to run the following command to mysqlinstall the dependencies for the project

cnpm i mysql -S

Configure the mysql module

In use mysqlthe module operation MySQLbefore the database must first of mysqlmodules necessary configuration , the configuration of the main steps of

// 1. 导入 mysql 模块
var mysql  = require('mysql')

// 2. 建立与 mysql 数据库的连接
var db = mysql.createPool({
  host: '127.0.0.1',  // 数据库的 ip 地址
  user: 'root', // 登录数据库的账号
  password: 'toor', // 登录数据库的密码
  database: 'my_db_01' // 指定要操作哪个数据库
})

Test whether the mysql module works

Call db.query()function, designated to execute SQLthe statement, get the results performed by a callback function

Case code

// 1. 导入 mysql 模块
var mysql  = require('mysql')
// 2. 建立与 mysql 数据库的连接
var db = mysql.createPool({
  host: '127.0.0.1',  // 数据库的 ip 地址
  user: 'root', // 登录数据库的账号
  password: 'toor', // 登录数据库的密码
  database: 'my_db_01' // 指定要操作哪个数据库
})

// 测试 mysql 模块能够正常运行
db.query('select 1', (err, results) => {
  // mysql 模块如果错误,err 中会返回错误信息
  if (err) return console.log(err.message)

  // 能够成功的执行 SQL 语句
  console.log(results)
})