Database: A warehouse that stores data.
Data structure
realizes data sharing
can reduce redundant data
High data independence
Unified data management and control
Database introduction
MySQL database
Oracle
Orcale database management system is provided by Oracle Corporation Development, has always been a leader in the field of databases
Commercial charging, large-scale systems, Taobao and JD.com use Oracle database
SqlServer database
developed by Microsoft and can only run on Windows. .net development programmer
DB2
was developed by IBM and is widely used in the financial industry. IBM's server, buy the server and get the database free
Commercial charges
MS SQL Server
The above databases are all operated using SQL language
SQL language is an operating language for relational databases
Using this SQL language you can Add, delete, and modify databases and tables, as well as add, delete, and modify table data
Tables are relationships
Between tables are relationships
MongoDB
Non-relational database
There are no tables, and the data stored in this database are all collected , similar to JavaScript objects, is actually data in json format
A collection has no structure, a collection is an array
You can also insert data into a collection
MongoDB is a between relational database and non-relational database developed by 10gen. Among the products among non-relational databases, it is the most feature-rich among non-relational databases and most similar to relational databases. The data structure it supports is very loose and is in a json-like format, so it can store more complex data structure types. The biggest feature of the MongoDB database management system is that the query language it supports is very powerful, and its syntax is similar to the object-oriented query language. It is also an open source database that supports Internet applications with large amounts of data and high concurrency. You do not need to use SQL language to operate non-relational databases.
Relational database storage structure
Storage record data according to the table structure
Design the table structure according to the business
Database management system
Database server
Database
Data table
Record
oracle, sqlserver, DB2, MySQL They are all relational databases.
Install and configure the MySQL database
Official website: http://www.mysql.com/
Start and stop the MySQL service:
Run through Windows, enter services.msc to find the MySQL service
via DOS Command to start the MySQL service (open the terminal with administrator privileges)
Log in to the MySQL database:
mysql -h localhost -P 3306 -u root -p
-h: host name
-P: port
-u: Username
-p: Password
mysql connects to localhost and 3306 by default, so you can omit -h and -P:
mysql -u root -p
Enter "help;" or "h" in the command line, MySQL help information will be displayed.
Database storage structure
A database server can have multiple databases
A database can have multiple tables
A table must have a table structure
All data is stored in the table, so After you have a database, you must first design your table structure
Storage multiple records in one table
Records are stored in the form of table structure
Create databases and tables
Create database
CREATE DATABASE [IF NOT EXISTS] db_name
View database
SHOW DATABASES;
Show database creation statement
SHOW CREATE DATABASE db_name;
Delete database
DROP DATABASE [IF EXISTS] db_name;
Select database
USE db_name;
View the currently used database
SELECT database();
Data type
Integer type
Floating point type
In the MySQL database, decimals are stored using floating point numbers and fixed point numbers. of.
There are two types of floating point numbers:
Single precision floating point number (FLOAT)
Double precision floating point number (DOUBLE)
Fixed point number (DECIMAL) [ˈdesɪml]
Time and date types
If the inserted value is illegal, the system will automatically insert the corresponding zero value into the database.
YEAR
Using a 4-digit string or number, the range is '1901' ~ '2155' or 1901 ~ 2155
For example, if you enter '2016' or 2016, the values inserted into the database will be 2016
DATE
DATE type is used to represent date values and does not include the time part.
You can use "YYYY-MM-DD" or 'YYYYMMDD' string representation
For example, if you enter '2016-10-01' or '20161001', the date inserted into the database is 2016-10-01
TIME
TIME type is used to represent time values. Its display format is generally HH:MM:SS, where HH represents hours, MM represents minutes, and SS represents seconds.
You can use the following three methods to specify the time value:
With " D HH:MM:SS" string format representation. Among them, D represents the day, which can take a value between 0-34. When inserting data, the value of the hour is equal to (D*24+HH)
For example, enter '2 11:30:50', and the date inserted into the database is 59:30:50
Represented in 'HHMMSS' string format or HHMMSS number format
For example: input '345454' or 345454, the date inserted into the database is 34:54:54
Use CURRENT_TIME or NOW() to enter the current System time
DATETIME
Specify the value of DATETIME type:
Use 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS' as a string or number.
Use NOW to enter the date and time of the current system
TIMESTAMP
TIMESTAMP type display format is the same as DATETIME, but the value range is smaller than DATETIME.
Enter CURRENT_TIMESTAMP to enter the current date and time of the system
When you enter NULL, the system will automatically enter the current date and time
When there is no input, the system will enter the current date and time of the system
mark. You can use it to save the last modification time of a record in the table. Automatic maintenance.
CHAR and VARCHAR
When the data is of CHAR(4) type, regardless of the length of the inserted value, the storage space occupied is 4 bytes. The number of bytes occupied by the data corresponding to VARCHAR (4) is the actual length plus 1.
Summary:
Types with variable character length need to calculate the byte length when querying using VARCHAR
The string length is fixed The use of CHAR query is fast.
VARCAHR saves space than CHAR
CHAR saves time than VARCHAR
TEXT type
represents large text data, such as article content, comments, etc.
Basic operations
View table structure
View all tables in the current database ： show tables;
View table structure: desc table_name;
View table creation statements: show create table table_name;
Modify data table
Add columns: ALTER TABLE table_name ADD colum datatype;
Modify columns: ALTER TABLE table_name MODIFY colum datatype;
Delete column: ALTER TABLE table_name DROP colum;
Modify table name: rename TABLE table_name to new_table_name;
Modify column name: ALTER TABLE table_name change colum_name new_colum_name datatype;
Delete data table
DROP TABLE table_name;
Constraints of the table
The constraints of the table are all restrictions on the fields in the table, thereby ensuring the correctness and uniqueness of the data in the data table.
Primary key constraints
There can be at most one primary key constraint in each data table. The fields defined as PRIMARY KEY cannot have duplicate values and cannot be NULL values. That is, non-null and unique
Syntax: Field name Data type PRIMARY KEY
Non-null constraint
Non-null constraint means that the value of the field cannot be NULL. In MySQL, non-null constraint is defined by NOT NULL.
Syntax: field name data type NOT NULL;
Unique constraint
Unique constraint is used to ensure the uniqueness of fields in the data table, that is, the values of the fields in the table cannot appear repeatedly.
Syntax: Field name data type UNIQUE;
Default constraints
Default constraints are used to specify default values for fields in the database, that is, when a record is inserted into the table, if this field is not assigned a value, the database system will This field inserts a default value.
Syntax: Field name Data type DEFAULT Default value;
Set the field value of the table to automatically increase
If you want to automatically generate a unique ID for new records inserted into the table. You can use AUTO_INCREMENT to achieve
Syntax: field name data type AUTO_INCREMENT;
Add, update and delete data
Add data
Add data to all fields in the table
INSERT INTO 表名 VALUES(列1值,列2值,...)
Note: The values in
values must be the same as those in the table The fields correspond one to one.
The inserted data should be of the same type as the data in the field
The size of the data should be within the specified range of the column, for example, a string of length 80 cannot be inserted into a column of length 40
Character and date type Data should be enclosed in single quotes
If you want to insert a null value, do not specify or use NULL
Add data according to the specified column:
INSERT INTO 表名(列1名, 列2名,...) VALUES(列1值, 列2值,...)
Note: The value in values must correspond one-to-one with the column in the column declaration
Add multiple records at the same time
INSERT INTO employee
VALUES (value1,value2,value3...),
(value1,value2,value3...),
(value1,value2,value3),
...; Update all data:
UPDATE 表名 SET 列名=值, 列名=值[,列名=值]
Update by condition:
UPDATE 表名 SET 列名=值, 列名=值[,列名=值] WHERE 条件;
Summary:
UPDATE statement can update the columns of the rows in the original table with new values .
SET clause specifies which columns to modify and which values to give
WHERE需要给定一个条件，表示要更新符号该条件的行，没有WHERE字句，则更新所有行
条件可以使用的运算符：
-- 比较运算符 > < <= >= = <> 大于、小于、大于(小于等于)、不等于 BETWEEN…AND -- 显示在某一区间的值 IN(set) -- 显示在in列表中的值，例：in(100,200) LIKE -- ‘张pattern’ 模糊查询% IS NULL -- 判断是否为空 -- 逻辑运算符 AND 多个条件同时成立 OR 多个条件任一成立 NOT 不成立，例：WHERE NOT(salary>100)
删除数据：
删除全部数据
DELETE FROM 表名;
根据条件删除：
DELETE FROM 表名 WHERE 条件;
初始化
runcate初始化数据表
truncate table_name;
truncate和delete的区别：
delete会一条一条的删
truncate先摧毁整张表，再创建一张和原来的表结构一模一样的表
拿拆迁举例子
truncate在效率上比delete高
truncate只能删除整表的数据，也就是格式化。
truncate会把自增id截断恢复为1
总结：
如果不使用WHERE语句，将删除表中所有数据
DELETE不能删除某一列的值，（可使用UPDATE）
使用DELETE语句仅仅删除记录，不删除表本身，如果要删除表，使用DROP TABLE语句
删除表中所有数据也可以使用truncate table_name语句
单表查询
简单查询
SELECT [DISTINCT] *|{colum1, colum2, colum3...} FROM table_name;SELECT指定查询哪些列的数据
column指定列名
号表示查询所有列
FROM 指定查询哪种表
DISTINCT 可选，指查询结果时，是否去除重复数据
查询表中所有数据：
SELECT * FROM 表名;
按照指定列查询表中所有数据：
SELECT 列名,列名[,列名] FROM 表名;
根据条件查询数据：
SELECT * FROM 表名 WHERE 条件;
在WHERE字句中经常使用的运算符
LIKE语句中，% 代表零个或多个任意字符，_代表一个字符，例如：name LIKE '_a%';
多表查询：
-- 多表查询 -- 找到表 articles 中 user_id 等于 users 表中 id 的 -- 多表查询可以起别名 SELECT a.id as article_id, a.title, a.time FROM articles as a INSERT JOIN users as u ON a.user_id=u.id
查询总记录数：
-- 查询表中的总记录数据 SELECT COUNT(id) as count FROM articles;
聚合函数
在实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值，最小值，平均值等，为此，MySQL提供了一些函数来实现这些功能。
COUNT（列名）返回某一列，行的总数
COUNT（列名）返回某一列，行的总数
SUM()函数返回满足WHERE条件的行的和
SELECT SUM(列名) {, SUM(列名)...} FROM table_name
[WHERE where_definition]注意：SUM仅对数值起作用，否则报错; 对多列求和，“,”不能少。
MAX()/MIN()函数返回满足WHERE条件的一列的最大/最小值
SELECT MAX(列名) FROM table_name
[WHERE where_definition];对查询结果排序
SELECT colum1, colum2, colum3..
FROM table_name
ORDER BY colum ASC|DESC;ORDER BY 指定排序的列，排序的列表即可以是表中的列名，也可以是SELECT语句后指定的列名.
ASC 升序，DESC 降序
ORDER BY 字句应该位于SELECT 语句的结尾
分组查询
SELECT colum1, colum2, ...
FROM 表名
LIMIT [OFFSET, ] 记录数LIMIT表示从哪一条记录开始往后【不包含该记录】，以及一共查询多少记录
OFFSET表示偏移量:
如果为0则表示从第一条记录开始
如果为5则表示从第6条记录开始
使用场景：分页查询
分页查询一个例子
-- 仅仅取了前 10 条 SELECET * FROM articles LIMIT 10 -- 跳过一条取一条 SELECET * FROM articles LIMIT 1, 1
为表和字段区别名
为表取别名
SELECT 表别名.id,表别名.name... FROM 表名 AS 表别名
WHERE 表别名.id = 2..为字段取别名
SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,...] FROM 表名;
多表操作
实际开发中业务逻辑比较复杂，可能有几十到几百张表不等，所以我们就需要对多张表来进行查询操作，对两张以上的表进行操作，就是多表操作。
外键
为了保证数据的完整性，将两张表之间的数据建立关系，因此就需要在成绩表中添加外键约束。
外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一约束。
外键用于建立和加强两个表数据之间的链接。
为表添加外键约束
创建表的时候添加外键:
CREATE TABLE department(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) NOT NULL
);
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) NOT NULL,
dept_id INT,
FOREIGN KEY (id) REFERENCES department(id)
);表已经存在，通过修改表的语句增加外键:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名);
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
操作关联表
关联关系:
多对一
多对多
一对一
使用 Node 操作 MySQL 数据库
安装：
$ npm install --save mysql
使用连接池操作 MySQL 数据库
修改安装目录下 my.ini 文件中的: max_connections=1000 默认是 max_connections=151
重启服务器
连接池
封装过程：
const mysql = require('mysql');
// 使用连接，提高操作数据库效率
// 创建一个连接池，池子存放的连接数量是 100 个
const pool = mysql.createPool({
connectionLimit: 100,
host: 'localhost',
user: 'root',
password: 'root',
database: 'personal'
});
for (let i = 0; i < 1000; i++) {
// 从池子中拿一个可用的连接
pool.getConnection((err, connection) => {
if (err) {
throw err;
}
connection.query('INSERT INTO `feedback`(`message`, `name`, `email`, `date`) VALUES(?, ?, ?, ?)', [
'今天的雾霾很醇厚',
'校长',
'xiaozhang@abc.com',
'2016-11-17 09:31:00'
], (err, stat) => {
// 尽早的释放回连接池
// 只要操作数据库的回调函数被执行，说明这个连接的任务完成了
connection.release();
if (err) {
throw err;
}
console.log(`第${i+1}个任务完成了`);
});
});
}封装：db.js
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 100,
host: 'localhost',
user: 'root',
password: 'root',
database: 'personal'
});
// rest 参数
// 作为函数参数的最后一个参数出现，以 ... 开头，后面跟一个名字
// rest 参数就代替了 arguments
exports.query = function (sql, ...values) {
let callback;
let params = [];
if (values.length === 3) {
params = values[0];
callback = values[1];
} else if (values.length === 2) {
callback = values[0];
}
pool.getConnection((err, connection) => {
if (err) {
return callback(err);
}
// 如果传递了两个参数，则第二个参数就是 callback
// 也就是说这种情况下，params 就是 callback
// 后面的 参数就忽略不计了
// 如果真的传递了三个参数，那就是一一对应
connection.query(sql, params, (err, result) => {
connection.release();
if (err) {
return callback(err);
}
callback(null, result);
});
});
};promise 版
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 100,
host: 'localhost',
user: 'root',
password: 'root',
database: 'personal'
});
exports.query = (sql, params = []) => {
return new Promise((resolve, reject) => {
pool.getConnection((err, connection) => {
if (err) {
return reject(err);
}
connection.query(sql, params, (err, result) => {
connection.release();
if (err) {
return reject(err);
}
resolve(result);
});
});
});
};调用示例：
const db = require('./db')
db.query('SELECT 1 + 1 as solution');
.then(rows => {
// use rows
return db.query('INSERT INTO table_name VALUES(?, ?, ?)', ['值1', '值2', '值3'])
})
.then(rows => {
// use rows
})
.catch(err => {
// handle error
});
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
