Navicat use–mysql

Table relationship

Database operations and table field design are generally completed through the graphical interface tool Navicat.
The addition, deletion, modification and query of data in the table require familiarity with SQL statements.

One-on-one

One-to-one: An A corresponds to a B, and a B corresponds to an A.

• Set the primary key of any table A or B as a foreign key

One-to-many

One-to-many: One A corresponds to multiple Bs, and one B corresponds to one A. It is said that A and B are one-to-many, and B and A are many-to-one.

• Set a foreign key on one end of the multi table, corresponding to the primary key of another table

Adding completed:

Many-to-many

Many-to-many: One A corresponds to multiple Bs, and one B corresponds to multiple A’s

• Create a new relationship table. The relationship table contains at least two foreign keys, corresponding to A and B respectively.
  

Data addition, deletion, modification and query

Add, delete, modify

-- 1. Add data
INSERT INTO `user` ( `name`, `age`) VALUES ('cjc', 100);
INSERT INTO `user` ( `name`, `age`) VALUES ('ccc', 999);
INSERT INTO `user` ( `name`, `age`) VALUES ('aaa', 111);

-- 2. Delete data
-- Delete all data
DELETE FROM `user`
DELETE FROM `user` WHERE `name` = 'aaa'

-- 3. Modify data
UPDATE `user` SET `name` = 'CJC',`age` = 10000 WHERE `name` = 'cjc'
--When modifying data, manually add time updates
UPDATE `user` SET `name` = 'CCC',`age` = 99999,`updateTime` = CURRENT_TIMESTAMP WHERE `name` = 'ccc'

-- The data is modified and updateTime is updated based on the current timestamp.
ALTER TABLE `user` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Single table query

select…from…

-- extra column
SELECT id, loginid, loginpwd, 'abc' as 'extra column' from `user`;

-- Rename column name as
SELECT *, 'abc' as 'extra' from `employee`;

-- Map 1/0 to 'male'/'female'
-- Add a new column of levels, with values of high/medium/low
SELECT id, `name`,
case
when ismale = 1 then 'male'
else 'female'
end sex,
case
when salary>=10000 then 'high'
when salary>=5000 then '中'
else 'low'
end `level`,
salary
FROM employee;

-- DISTINCT to remove duplicates
select DISTINCT location from employee;

where

SELECT * FROM employee
WHERE ismale = 1;

-- companyId = 1 or companyId = 2
SELECT * FROM department
WHERE companyId in (1, 2);


-- null
SELECT * from employee
WHERE location is not null;

SELECT * from employee
WHERE location is null;


-- between...and
-- > >= < <=
SELECT * from employee
WHERE salary>=10000;

SELECT * from employee
WHERE salary BETWEEN 10000 and 12000;


-- like fuzzy query
-- %0 or more any characters _1 any character
SELECT * from employee
WHERE `name` like '%cao%';
-- The second character is c
SELECT * from employee
WHERE `name` like '_c';


-- and or
SELECT * from employee
WHERE `name` like '张%' and ismale=0 and salary>=12000;

SELECT * from employee
WHERE `name` like '张%' and (ismale=0 and salary>=12000
or
birthday>='1996-1-1');

order by

--In ascending order by gender,
-- If the gender is the same, then order in descending order of salary
SELECT * from employee
ORDER BY gender asc, salary desc;

limit

-- After skipping 1 piece of data, query the first 20 pieces of data
SELECT * FROM `user` LIMIT 20 OFFSET 1
SELECT * FROM `user` LIMIT 1,20
-- Query page 3, 5 pieces of data per page
--Paging formula limit (curPage-1)*pageSize, pageSize
SELECT * FROM `user` LIMIT 10,5

Function and grouping

1. Aggregation function

-- Math functions
SELECT ABS(-1);
SELECT CEIL(1.4);
SELECT ROUND(3.1415926, 3);
SELECT TRUNCATE(3.1415926,3);

-- String functions
SELECT CURDATE();
SELECT CURTIME();
SELECT TIMESTAMPDIFF(DAY,'2010-1-1 11:11:11','2010-1-2 11:11:12');

-- Aggregation function
SELECT count(id) as number of employees,
avg(salary) as average salary,
sum(salary) as total salary,
min(salary) as minimum salary
FROM employee;

2.Group by
Grouping can only query grouped columns or aggregated columns

-- Query the places of residence where employees are distributed and how many employees there are in each place of residence.
SELECT location, count(id) as empnumber
FROM employee
GROUP BY location

-- Group people with the same place of residence and gender into one group
SELECT location, count(id) as empnumber
FROM employee
GROUP BY location,gender

Multiple table query

-- 1. Descartes product
-- a represents m rows of records, b represents n rows of records, and the Cartesian product operation results in m*n rows of records.
-- Query the football team's lineup
SELECT t1.name home, t2.name away
FROM team as t1, team as t2
WHERE t1.id != t2.id;

-- 2. Left outer join
SELECT *
from department as d
left join employee as e
on d.id = e.deptId;

-- 3. Right outer join
SELECT *
from employee as e
right join department as d
on d.id = e.deptId;

-- 4.Inner connection
SELECT e.`name` as empname, d.`name` as dptname, c.`name` as companyname
from employee as e
inner join department as d
on d.id = e.deptId
inner join company c
on d.companyId = c.id;

SQL writing order and execution order

Writing order

SELECT
tagname as "tag1",
tagname2 as "tag2",
[aggregate function]...
\t
FROM table1
[LEFT] JOIN table2
onxxx
[LEFT] JOIN table3
onxxx
WHERE condition without aggregate function
GROUP BY tag1, tag2... and other non-aggregate function fields
HAVING conditions with aggregate functions

ORDER BY tag1,tag2 DESC
LIMIT [offset], the number of records displayed; # LIMIT the number of records displayed OFFSET offset;

Execution order

1. from
2. join…on…
3. where
4. group by
5. select
6. having
7. order by
8. limit

SQL query statement practice

--Three table join
SELECT *
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id

-- 1. Query the number of employees in each department of Duyi
SELECT COUNT(e.id),d.`name`
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
WHERE c.`name` LIKE '%多一%'
GROUP BY d.id

-- 2. Query the number of employees in each company
SELECT COUNT(e.id),c.`name`
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
GROUP BY c.id

-- 3. Query the number of female employees living in Wanjiawan who joined all companies within 10 years
-- Note: All companies must be displayed
SELECT c.id,c.`name`,res.count
from company c
LEFT JOIN (
SELECT c.id,c.`name`,COUNT(e.id) count
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
WHERE TIMESTAMPDIFF(YEAR,e.joinDate,CURDATE())<=10 AND e.location LIKE '%Wanjiawan%'
GROUP BY c.id
) as res
on c.id = res.id

-- 4. Query the places of residence where all employees of Duyi are located, and the number of each place of residence.
SELECT e.location,COUNT(e.id)
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
WHERE c.`name` LIKE '%多一%'
GROUP BY e.location

-- 5. Query company information with more than 200 employees
SELECT *
from company c
WHERE c.id = (
-- Find the company ID that meets the conditions
SELECT c.id
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
GROUP BY c.id
HAVING count(e.id)>200
)


-- 6. Query the employees in a company whose average salary is higher than that of the company
SELECT e.*
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on c.id = d.companyId
WHERE c.`name` LIKE '%多一%' AND e.salary > (
  -- Average salary of Toichi
SELECT AVG(e.salary)
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
WHERE c.`name` LIKE '%多一%'
)


-- 7. Query the corresponding number of employees with two-character and three-character names in Duyi
SELECT CHARACTER_LENGTH(e.`name`) nameLen, COUNT(e.id)
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on c.id = d.companyId
WHERE c.`name` LIKE '%多一%'
GROUP BY CHARACTER_LENGTH(e.`name`)
HAVING nameLen in (2,3)

-- 8. Query the total monthly salary expenditure of each company and sort it from low to high
SELECT c.`name`,sum(e.salary) totalSalary
from employee e
INNER JOIN department d
on d.id = e.deptId
INNER JOIN company c
on d.companyId = c.id
GROUP BY c.id
ORDER BY totalSalary desc

View

We can encapsulate reusable queries into views

mysql driver

mysql driver: connecting mysql data and data in memory
Commonly used mysql drivers: mysql, mysql2

const mysql = require('mysql2')

// 1. Create a connection pool
const connectionPool = mysql.createPool({<!-- -->
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'koa-apis',
  connectionLimit: 5
})

// 2. Test whether the connection is successful
connectionPool.getConnection((err, connection) => {<!-- -->
  if (err) {<!-- -->
    console.log('Database connection failed', err);
    return
  }

  connection.connect(err => {<!-- -->
    if (err) {<!-- -->
      console.log('Interaction with database failed', err);
    } else {<!-- -->
      console.log('Interaction with database successful');
    }
  })
})

// 3. Define prepared statements
//Prevent sql injection
const statement = 'SELECT * FROM `student` WHERE id > ? AND name LIKE ?'

// 4. Execute sql statement
// Use promise syntax
const connection = connectionPool.promise()
connection.execute(statement, [2, '%c%']).then(res => {<!-- -->
  const [val, fields] = res
  console.log(val);
}).catch(err => {<!-- -->
  console.log(err);
})

ORM

ORM (Object Relational Mapping) object relational mapping

• Associate objects in the program with the database
• Use a unified interface to complete operations on different databases

Commonly used ORM frameworks in node

• Sequelize
• TypeORM

Connect to database

const {<!-- --> Sequelize } = require('sequelize');

const sequelize = new Sequelize('school', 'root', 'root', {<!-- -->
  host: 'localhost',
  dialect: 'mysql',
  port: '13306',
  logging: false // Turn off printing logs
});

//Test connectivity
(async function () {<!-- -->
  try {<!-- -->
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {<!-- -->
    console.error('Unable to connect to the database:', error);
  }
})();

module.exports = sequelize

Model definition and synchronization

const sequelize = require('./db');
const {<!-- --> DataTypes } = require('sequelize');

const Admin = sequelize.define('Admin', {<!-- -->
  // Define model properties here
  loginId: {<!-- -->
    type: DataTypes.STRING,
    allowNull: false
  },
  loginPwd: {<!-- -->
    type: DataTypes.STRING,
    allowNull: false
  },
  name: {<!-- -->
    type: DataTypes.STRING,
    allowNull: false
  }
}, {<!-- -->
  createdAt: true,
  updatedAt: true,
  paranoid: true, // Record the time of deletion, the data will not be actually deleted
  freezeTableName: false // Whether to add plural numbers to the table name
});


(async function () {<!-- -->
  await Admin.sync({<!-- --> alter: true })
  console.log('Admin synchronization completed');
})();

module.exports = Admin

Table relationship (foreign key)

1. One to one
A.hasOne(B) association means there is a one-to-one relationship between A and B, with the foreign key defined in the target model (B).
The A.belongsTo(B) association means there is a one-to-one relationship between A and B with the foreign key defined in the source model (A).

2. One to many
A.hasMany(B) association means that there is a one-to-many relationship between A and B, and the foreign key is defined in the target model (B).

3. Many to many
A.belongsToMany(B, { through: C’ }) Association means using table C as join table, there is a many-to-many relationship between A and B. With foreign keys (for example, aId and bId)

Three-tier architecture

Add, delete, modify and check

bookService.js

const Book = require("../models/Book");

// 1. Increase
exports.addBook = async function (obj) {<!-- -->
  //Business logic judgment
  // ...
  const ins = await Book.create(obj);
  return ins.toJSON();
};

// 2. Delete
exports.deleteBook = async function (id) {<!-- -->
  const result = await Book.destroy({<!-- -->
    where: {<!-- -->
      ID,
    },
  });
  return result;
};

// 3.Change
exports.updateBook = async function (id, obj) {<!-- -->
  const result = await Book.update(obj, {<!-- -->
    where: {<!-- -->
      ID,
    },
  });
  return result;
};

Data import and export

Import and export data through files with the suffix .sql

Import sql file

Export sql file