node，MySQL，Oracle連接數(shù)據(jù)庫，增刪改查，mockjs，中文亂碼，end會(huì)調(diào)用2次，write
本期專欄耗時(shí)的個(gè)人嘗試猜想后實(shí)踐確認(rèn)猜想后的發(fā)現(xiàn)：node和mysql數(shù)據(jù)庫連接時(shí)，用end顯示數(shù)據(jù)到網(wǎng)頁會(huì)讓查詢部分等等調(diào)用2次，用write就沒有這種情況。
目錄：
例子1：使用mockjs生成隨機(jī)數(shù)據(jù)后，用axios接收處理，拼接成城市下拉框
http://mockjs.com/
https://github.com/nuysoft/Mock/wiki
mock
npm?install?mockjs?--save-dev
mock是隨機(jī)生成數(shù)據(jù)，模擬數(shù)據(jù)庫的數(shù)據(jù)等等的東西。屬性名和生成規(guī)則用| 分隔。mock基本的語法說明如下。
axios獲取mock生成的數(shù)據(jù)中也用了解構(gòu)語法與map
Mk.js
mock.js
index.js
運(yùn)行
npm start
作業(yè)
例子2：1、使用Mock數(shù)據(jù)創(chuàng)建一個(gè)學(xué)歷下拉框
例子3：2、使用Mock數(shù)據(jù)創(chuàng)建一個(gè)數(shù)據(jù)表格（使用antd實(shí)現(xiàn)）
shuffle
@integer(1,120)
Dg_axios_mock.js
Dg_axios_mock_data.js
index.js
例子4：用Oracle連接數(shù)據(jù)庫，Vscode中使用nodejs查看一張表的所有數(shù)據(jù)，打印在控制臺(tái)，并且增加一條數(shù)據(jù)（增刪改其實(shí)基本一樣的代碼，就是使用的SQL語句不一樣）
關(guān)于 Userinfo表的SQL語句
ora.js
例子5：用MySQL連接數(shù)據(jù)庫，Vscode中使用nodejs查看一張表的所有數(shù)據(jù)，打印在控制臺(tái)，并且增加一條數(shù)據(jù)（增刪改其實(shí)基本一樣的代碼，就是使用的SQL語句不一樣）
增加數(shù)據(jù)如果單個(gè)寫時(shí)，只執(zhí)行一次增加的部分的代碼，
如果和查詢數(shù)據(jù)寫在一起，就會(huì)運(yùn)行一次服務(wù)時(shí)，會(huì)執(zhí)行2次增加部分的代碼
mysql.js
MySQL中表相關(guān)的SQL語句和執(zhí)行方法
嚴(yán)格模式下生命周期會(huì)被調(diào)用兩次。假設(shè)沒有更新state或者props，render函數(shù)又執(zhí)行了兩次，很有可能是哪個(gè)地方用了React.StrictMode
Node.js 連接 MySQL，增刪改查
????//處理中文亂碼?START
????resp.writeHead(200,{'Context-Type':'text/html'});
????resp.write('<head><meta?charset="utf-8"/></head>');
????????//處理中文亂碼?END
例子1：使用mockjs生成隨機(jī)數(shù)據(jù)后，用axios接收處理，拼接成城市下拉框
一、什么是Mock：可以自己構(gòu)造后臺(tái)數(shù)據(jù)，不需要寫復(fù)雜的后臺(tái)代碼了
二、安裝：npm install mockjs --save-dev
三、創(chuàng)建mock.js文件
四、mock數(shù)據(jù)生成語法
http://mockjs.com/
https://github.com/nuysoft/Mock/wiki
mock
npm?install?mockjs?--save-dev
mock是隨機(jī)生成數(shù)據(jù)，模擬數(shù)據(jù)庫的數(shù)據(jù)等等的東西。屬性名和生成規(guī)則用| 分隔。mock基本的語法說明如下。
axios獲取mock生成的數(shù)據(jù)中也用了解構(gòu)語法與map
Mk.js
//src/comp/Mk.js
import?React?from?'react';
import?axios?from?'axios';
//引入mock數(shù)據(jù)模塊
import?'../mock';
class?Mk?extends?React.Component{
????constructor(){
????????super();
????????//設(shè)置下拉框的初始狀態(tài)
????????this.state?=?{
????????????citys:?[]
????????};
????}
????componentDidMount(){
????????//請(qǐng)求數(shù)據(jù)
????????axios.get('/demo')
????????????.then(res?=>?{
????????????????const?{citys}?=?res.data.data;
????????????????let?opts?=?citys.map(c?=>?(<option?id={c.id}?key={c.id}>
???????????????????????????????????????????????{c.address}
???????????????????????????????????????????</option>));
????????????????this.setState({
????????????????????citys:?opts
????????????????});
????????????});
????}
????render(){
????????return?<h1><select>{this.state.citys}</select></h1>;
????}
}
export?default?Mk;
mock.js
//src/mock.js
import?Mock?from?'mockjs';
//使用mockjs模擬數(shù)據(jù)
Mock.mock('/demo',?{
????"ret":0,
????"data":
??????{
????????"citys|5-10":?[{???/*5-10表示隨機(jī)生成含5到10個(gè)數(shù)組元素的一個(gè)數(shù)組，
??????????這里的數(shù)組元素一般是含各種自定義屬性的object，stus為這個(gè)數(shù)組的名字*/
????????????'name':?'@cname',??//?@cname表示name這一屬性會(huì)自動(dòng)生成的內(nèi)容是中文名稱（@cname應(yīng)該是固定的寫法）
????????????'id|+1':?1,????//?屬性值自動(dòng)加?1，初始值為1
????????????'score|0-100.1':?1,???/*?整數(shù)部分在10到1000之間,保留1位小數(shù),?
????????????1只是用來確定類型(確定為數(shù)字類型),1寫成0或其他數(shù)字也可以。
??????????XX-?XXX.1-3應(yīng)該是表示整數(shù)部分保留xx到XXX之間，
??????????小數(shù)部分保留1到2位小數(shù)*/
????????????'birth':?'@date("yyyy-MM-dd")',??//?日期
????????????'address':?'@city(false)',???//?中國(guó)城市,true表示顯示省份,如果寫false就是不顯示省份，只顯示城市
????????}]
??????}
});
index.js
/******************/
//目錄——>
//?reactjs必備源碼部分
//?個(gè)人添加的導(dǎo)入antd.css的代碼部分
//?個(gè)人添加的導(dǎo)入組件的代碼部分
//?個(gè)人科普——>
//?個(gè)人注釋：Dg是表格組件
//?FormZuJian.js是表單組件
//個(gè)人總結(jié)的導(dǎo)入代碼模板：import?自定義文件別名（一般我習(xí)慣和導(dǎo)入文件名統(tǒng)一）?from??文件路徑;
/******************/
//reactjs必備源碼部分?START?
import?React?from?'react';
import?ReactDOM?from?'react-dom';
import?'./index.css';
//reactjs必備源碼部分?END
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?START?
import?Router?from?'./route';
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?END
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?START
import?'antd/dist/antd.css';
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?END
//個(gè)人添加的導(dǎo)入組件的代碼部分?START
import?MyTable?from?'./comp/MyTable';
import?Dg?from?'./comp/Dg';
import?FormZuJian?from?'./comp/FormZuJian';
import?Cascade?from?'./comp/Cascade';
import?Dg_axios?from?'./comp/Dg_axios';
import?ChongMingYanZheng?from?'./comp/ChongMingYanZheng';
import?Mk?from?'./comp/Mk';
//個(gè)人添加的導(dǎo)入組件的代碼部分?END
import?reportWebVitals?from?'./reportWebVitals';
ReactDOM.render(
??
??<Mk/>,
??document.getElementById('root')
);
//?If?you?want?to?start?measuring?performance?in?your?app,?pass?a?function
//?to?log?results?(for?example:?reportWebVitals(console.log))
//?or?send?to?an?analytics?endpoint.?Learn?more:?https://bit.ly/CRA-vitals
reportWebVitals();
/*可選擇的組件
?<Dg?/>?：表格組件
<FormZuJian?/>?：表單組件
??<Cascade?/>?：聯(lián)級(jí)選擇（下拉框聯(lián)動(dòng)）組件
??<Dg_axios/>:是用了axios技術(shù)接收數(shù)據(jù)的用了antd的表格組件
????<Router?/>：路由組件（跳轉(zhuǎn)功能）
<ChongMingYanZheng/>:是更簡(jiǎn)單的Ajax的重名驗(yàn)證例子組件
<Mk/>：使用了mockjs技術(shù)的城市下拉框組件
??*/
運(yùn)行
npm start
作業(yè)：
例子2：1、使用Mock數(shù)據(jù)創(chuàng)建一個(gè)學(xué)歷下拉框
Education_select_mock.js
import?React?from?'react';
import?axios?from?'axios';
//導(dǎo)入Education_select_data_mock.js文件，獲取其中的數(shù)據(jù)：
import?'../Education_select_mock_data';
class?Education_select_mock?extends?React.Component{
????constructor(){
????????super();
//一般都是通過?聲明constructor中的this.state來設(shè)置初始狀態(tài)：
????????this.state?=?{
????????????arr:?[]
????????};
????}
????componentDidMount(){
/*使用axios請(qǐng)求數(shù)據(jù)，獲取/sshc這個(gè)自定義的范圍路徑的數(shù)據(jù)后,
進(jìn)行處理，顯示數(shù)據(jù):*/
????????axios.get('/sshc')
????????????.then(res?=>?{
????????????????const?{arr}?=?res.data.data;
????????????????let?NewData?=?arr.map(o?=>?(<option?id={o.id}?key={o.id}>
???????????????????????????????????????????{o.name}?????{o.Education}
???????????????????????????????????????????</option>));
?/*個(gè)人理解，解釋，解析：一般都是通過this.setState來設(shè)置刷新狀態(tài)，
?讓this.state聲明的屬性值被動(dòng)態(tài)賦值后被獲?。??*/??????????????????????????????????????
????????????????this.setState({
????????????????????arr:?NewData
????????????????});
????????????});
????}
????render(){
????????return?<h1><select>{this.state.arr}</select></h1>;
????}
}
export?default?Education_select_mock;
Education_select_mock_data.js
import?Mock?from?'mockjs';
Mock.mock('/sshc',?{
????"ret":0,
????"data":
??????{
????????"arr|2-3":?[{?
????????????'id|+1':?1,?
???????????'name':'@cname',
????????????'Education|1':?['小學(xué)的學(xué)歷','初中的學(xué)歷','高中的學(xué)歷','大學(xué)的學(xué)歷'],?
????????????
????????}]
??????}
});
index.js
/******************/
//目錄——>
//?reactjs必備源碼部分
//?個(gè)人添加的導(dǎo)入antd.css的代碼部分
//?個(gè)人添加的導(dǎo)入組件的代碼部分
//?個(gè)人科普——>
//?個(gè)人注釋：Dg是表格組件
//?FormZuJian.js是表單組件
//個(gè)人總結(jié)的導(dǎo)入代碼模板：import?自定義文件別名（一般我習(xí)慣和導(dǎo)入文件名統(tǒng)一）?from??文件路徑;
/******************/
//reactjs必備源碼部分?START?
import?React?from?'react';
import?ReactDOM?from?'react-dom';
import?'./index.css';
//reactjs必備源碼部分?END
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?START?
import?Router?from?'./route';
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?END
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?START
import?'antd/dist/antd.css';
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?END
//個(gè)人添加的導(dǎo)入組件的代碼部分?START
import?MyTable?from?'./comp/MyTable';
import?Dg?from?'./comp/Dg';
import?FormZuJian?from?'./comp/FormZuJian';
import?Cascade?from?'./comp/Cascade';
import?Dg_axios?from?'./comp/Dg_axios';
import?ChongMingYanZheng?from?'./comp/ChongMingYanZheng';
import?Mk?from?'./comp/Mk';
import?Education_select_mock?from?'./comp/Education_select_mock';
//個(gè)人添加的導(dǎo)入組件的代碼部分?END
import?reportWebVitals?from?'./reportWebVitals';
ReactDOM.render(
??
??<Education_select_mock/>,
??document.getElementById('root')
);
//?If?you?want?to?start?measuring?performance?in?your?app,?pass?a?function
//?to?log?results?(for?example:?reportWebVitals(console.log))
//?or?send?to?an?analytics?endpoint.?Learn?more:?https://bit.ly/CRA-vitals
reportWebVitals();
/*可選擇的組件
?<Dg?/>?：表格組件
<FormZuJian?/>?：表單組件
??<Cascade?/>?：聯(lián)級(jí)選擇（下拉框聯(lián)動(dòng)）組件
??<Dg_axios/>:是用了axios技術(shù)接收數(shù)據(jù)的用了antd的表格組件
????<Router?/>：路由組件（跳轉(zhuǎn)功能）
<ChongMingYanZheng/>:是更簡(jiǎn)單的Ajax的重名驗(yàn)證例子組件
<Mk/>：使用了mockjs技術(shù)的城市下拉框組件
<Education_select_mock/>:使用了mockjs技術(shù)的學(xué)歷下拉框組件
??*/
例子3：2、使用Mock數(shù)據(jù)創(chuàng)建一個(gè)數(shù)據(jù)表格（使用antd實(shí)現(xiàn)）
shuffle
?/*shuffle:洗牌的意思。
????????????"@shuffle(['德藝雙馨',?'自律','失敗者','酷',?'非常受歡迎'],2,3)"表示從數(shù)組中隨機(jī)選取
????????????2到3個(gè)對(duì)象*/
Dg_axios_mock.js
//src/comp/Dg.js
import?{?Table,?Tag,?Space?}?from?'antd';
import?React?from?'react';
//npm?install?axios?--save
import?axios?from?'axios';
//解決頁面跳轉(zhuǎn)時(shí)，history屬性為空的問題
//?import?{?withRouter?}?from?"react-router-dom";
//導(dǎo)入Dg_axios_mock_data.js文件，獲取其中的數(shù)據(jù)(不可以少)：
import?'../Dg_axios_mock_data';
//自定義組件
class??Dg_axios_mock?extends?React.Component?{
????constructor(){
????????super();
????????this.state?=?{
????????????myDate:?[]
????????}
????}
????componentDidMount(){
????????//?//請(qǐng)求后臺(tái)數(shù)據(jù)
????????//?axios.get('http://localhost:8777')
????????//?????.then(res?=>?{
????????//?????????//res就是后臺(tái)請(qǐng)求的數(shù)據(jù)
????????//?????????//console.log(res.data);
????????//?????????this.setState({
????????//?????????????myDate:?res.data
????????//?????????});
????????//?????});
????????axios.get('/url')
????????.then(res?=>?{
????????????//?const?{NewData}?=?res.data.data.arr;
????????????//?let?NewData?=??myDate;?????????????????????????????????????
????????????this.setState({
????????????????myDate:?res.data.data.arr
????????????});
???????
???????console.log(res.data.data.arr);
???????
???????
????????});
????}
????//?detail(name){
????//?????//name觸發(fā)點(diǎn)擊事件和傳的name的值（一般數(shù)據(jù)庫連接時(shí)，詳情界面知道id就可以傳所有信息等等）
????//?????//console.log(n);
????//?????//跳轉(zhuǎn)到Detail頁面
????//?????this.props.history.push('/dl/'?+?name);
????//?}
???
????render()?{
????????
????????//表頭
????????const?columns?=?[
????????????{
????????????????title:?'姓名',
????????????????dataIndex:?'name',
????????????????key:?'name',
????????????????//?render:?name?=>?<a?onClick={this.detail.bind(this,name)}>{name}</a>,
????????????},
????????????{
????????????????title:?'年齡',
????????????????dataIndex:?'age',
????????????????key:?'age',
????????????????//?render:?text?=>?<a?onClick={this.detail.bind(this,text)}>{text}</a>,
????????????},
????????????{
????????????????title:?'地址',
????????????????dataIndex:?'address',
????????????????key:?'address',
????????????},
????????????{
????????????????title:?'標(biāo)簽',
????????????????key:?'tags',
????????????????dataIndex:?'tags',
????????????????render:?tags?=>?(
????????????????????<>
????????????????????????{tags.map(tag?=>?{
????????????????????????????let?color?=?tag.length?>?3???'geekblue'?:?'green';
????????????????????????????if?(tag?===?'loser')?{
????????????????????????????????color?=?'volcano';
????????????????????????????}
????????????????????????????return?(
????????????????????????????????<Tag?color={color}?key={tag}>
????????????????????????????????????{tag.toUpperCase()}
????????????????????????????????</Tag>
????????????????????????????);
????????????????????????})}
????????????????????</>
????????????????),
????????????},
????????????{
????????????????title:?'操作',
????????????????key:?'action',
????????????????render:?(text,?record)?=>?(
????????????????????<Space?size="middle">
????????????????????????<a>邀請(qǐng)?{record.name}</a>
????????????????????????<a>刪除</a>
????????????????????</Space>
????????????????),
????????????},
????????];
????????return?<Table?columns={columns}?dataSource={this.state.myDate}?/>
????}
}
?export?default?Dg_axios_mock;
//?export?default?withRouter(Dg_axios_mock);
Dg_axios_mock_data.js
import?Mock?from?'mockjs';
Mock.mock('/url',?{
????"ret":0,
????"data":
??????{
????????"arr|23-33":?[{?
????????????'key|+1':?1,?
???????????'name':'@cname',
???????????'age':'@integer(1,120)',
???????????'address':'@city(true)',
????????????'tags|':"@shuffle(['德藝雙馨',?'自律','失敗者','酷',?'非常受歡迎'],2,3)",?
????????????/*shuffle:洗牌的意思。
????????????"@shuffle(['德藝雙馨',?'自律','失敗者','酷',?'非常受歡迎'],2,3)"表示從數(shù)組中隨機(jī)選取
????????????2到3個(gè)對(duì)象*/
????????}]
??????}
});
/*?const?data?=?[
????????{
????????????key:?'1',
????????????name:?'劉德華',
????????????age:?32,
????????????address:?'中國(guó)香港',
????????????tags:?['德藝雙馨',?'自律'],
????????},
????????{
????????????key:?'2',
????????????name:?'汪方',
????????????age:?42,
????????????address:?'武漢',
????????????tags:?['失敗者'],
????????},
????????{
????????????key:?'3',
????????????name:?'吳彥祖',
????????????age:?32,
????????????address:?'阿拉斯加',
????????????tags:?['酷',?'非常受歡迎'],
????????},
????];?*/
index.js
/******************/
//目錄——>
//?reactjs必備源碼部分
//?個(gè)人添加的導(dǎo)入antd.css的代碼部分
//?個(gè)人添加的導(dǎo)入組件的代碼部分
//?個(gè)人科普——>
//?個(gè)人注釋：Dg是表格組件
//?FormZuJian.js是表單組件
//個(gè)人總結(jié)的導(dǎo)入代碼模板：import?自定義文件別名（一般我習(xí)慣和導(dǎo)入文件名統(tǒng)一）?from??文件路徑;
/******************/
//reactjs必備源碼部分?START?
import?React?from?'react';
import?ReactDOM?from?'react-dom';
import?'./index.css';
//reactjs必備源碼部分?END
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?START?
import?Router?from?'./route';
//（Router是要自己寫的路由的部分，暫時(shí)我不寫和不用導(dǎo)入）?END
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?START
import?'antd/dist/antd.css';
//個(gè)人添加的導(dǎo)入antd.css的代碼部分?END
//個(gè)人添加的導(dǎo)入組件的代碼部分?START
import?MyTable?from?'./comp/MyTable';
import?Dg?from?'./comp/Dg';
import?FormZuJian?from?'./comp/FormZuJian';
import?Cascade?from?'./comp/Cascade';
import?Dg_axios?from?'./comp/Dg_axios';
import?ChongMingYanZheng?from?'./comp/ChongMingYanZheng';
import?Mk?from?'./comp/Mk';
import?Education_select_mock?from?'./comp/Education_select_mock';
import?Dg_axios_mock?from?'./comp/Dg_axios_mock';
//個(gè)人添加的導(dǎo)入組件的代碼部分?END
import?reportWebVitals?from?'./reportWebVitals';
ReactDOM.render(
??
??<Dg_axios_mock/>,
??document.getElementById('root')
);
//?If?you?want?to?start?measuring?performance?in?your?app,?pass?a?function
//?to?log?results?(for?example:?reportWebVitals(console.log))
//?or?send?to?an?analytics?endpoint.?Learn?more:?https://bit.ly/CRA-vitals
reportWebVitals();
/*可選擇的組件
?<Dg?/>?：表格組件
<FormZuJian?/>?：表單組件
??<Cascade?/>?：聯(lián)級(jí)選擇（下拉框聯(lián)動(dòng)）組件
??<Dg_axios/>:是用了axios技術(shù)接收數(shù)據(jù)的用了antd的表格組件
????<Router?/>：路由組件（跳轉(zhuǎn)功能）
<ChongMingYanZheng/>:是更簡(jiǎn)單的Ajax的重名驗(yàn)證例子組件
<Mk/>：使用了mockjs技術(shù)的城市下拉框組件
<Education_select_mock/>:使用了mockjs技術(shù)的學(xué)歷下拉框組件
<Dg_axios_mock/>:使用了antd和mockjs技術(shù)的表格組件
??*/
例子4：用Oracle連接數(shù)據(jù)庫，Vscode中使用nodejs查看一張表的所有數(shù)據(jù)，打印在控制臺(tái)，并且增加一條數(shù)據(jù)（增刪改其實(shí)基本一樣的代碼，就是使用的SQL語句不一樣）
開啟服務(wù)
登錄Oracle
關(guān)于?Userinfo表的SQL語句
?--drop table Userinfo? ? ? ? ? ? ? ??
create table Userinfo(
? ? id number primary key,
? ? act varchar2(30) not null,
? ?pwd varchar2(30) not null,
? ?birth date
);
--drop sequence seq_Userinfo
create sequence seq_Userinfo
start with 1? ? ? ?--起始值是1
increment by 1? ? ?--增長(zhǎng)的值? ?
maxvalue 999999999 --序列號(hào)的最大值
minvalue 1? ? ? ? ?--序列號(hào)的最小值
nocycle? ? ? ? ? ? --是否循環(huán)
cache 10;? ? ? ? ? --預(yù)存
insert into Userinfo values(seq_Userinfo.nextval,'黑黑','pwd1',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'紅紅','pwd2',to_date('2020-06-07','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'藍(lán)藍(lán)','pwd3',to_date('2020-06-08','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'666','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'999','pwd5',to_date('2020-06-10','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'888','pwd6',to_date('2020-06-11','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'三連','pwd5',to_date('2020-06-10','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'關(guān)注','pwd6',to_date('2020-06-11','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱1','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱2','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱3','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱4','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
insert into Userinfo values(seq_Userinfo.nextval,'詩書畫唱5','pwd4',to_date('2020-06-06','yyyy-mm-dd'));
--select * from Userinfo?
select * from userinfo WHERE act like '%詩%'
--rows=5,page=2
--end = rows * page=10
--start = (page - 1) * rows + 1=6
--start表示>=號(hào)后面的值，end表示<=號(hào)后面的值
?select * from
(select p1.*,rownum r1 from Userinfo p1
where rownum <= 10)
where r1 >=6? and act like '%詩%'
【這個(gè)不是條件查詢后的分頁查詢，而是分頁查詢后的條件查詢】
select * from (select t.*,rownum rn from
?(select * from userinfo WHERE act like '%詩%' ) t?
?where rownum <= 10) where rn >= 6?
【這個(gè)是條件查詢后的分頁查詢，是常用的，如果組合查詢后的數(shù)據(jù)很少，比如只有1條，
就難看出效果】
ora.js
//?//引入oracledb模塊
//?const?{?connectionClass?}?=?require('oracledb');
//?let?oracledb=require('oracledb');
//?//設(shè)置oracle連接參數(shù)
//?let?config={
//?????user:'X',//數(shù)據(jù)庫鏈接賬號(hào)
//?????password:'sshcPwd',//密碼
//?????connectString:'127.0.0.1:1521/orcl'//數(shù)據(jù)庫的IP地址，端口號(hào)和數(shù)據(jù)庫名
//?}
//?//獲取數(shù)據(jù)庫連接對(duì)象conn
//?oracledb.getConnection(config,function(err,conn){
//?????if(err){//如果連接數(shù)據(jù)庫出現(xiàn)錯(cuò)誤
//?????????console.log(err.message);
//?????????return;
//?????}
//?????console.log('數(shù)據(jù)庫連接成功'+conn);
//?????//?查詢
//?????connection.execute("select?*?from?userinfo",function(err,result){
//?????????if(err){
//?????????????console.error(err.message);
//?????????????return;
//?????????}
//?????????//打印返回的表結(jié)構(gòu)
//?????????console.log(JSON.stringify(result.metaData));
//?????????//打印返回的行數(shù)據(jù)
//?????????console.log(JSON.stringify(result.rows));
//?????????doRelease(connection);//自定義方法
//?????});
//?});
let?http?=?require('http');
let?url?=?require('url');
//npm?install?oracledb?-g
let?oracledb?=?require('oracledb');
http.createServer(function(req,res){
????//參數(shù)解析
????let?ps?=?url.parse(req.url,true).query;
????//數(shù)據(jù)庫連接
????const?conf?=?{
????????user:?'X',//用戶名
????????password:?'sshcPwd',//密碼
????????connectString:?'127.0.0.1:1521/orcl'//連接字符串
????};
????oracledb.getConnection(conf,function(err,conn){
????????if(err)?{
????????????console.log(err.message);
????????????return;
????????}
?????//?查詢所有（可以封裝成一個(gè)方法后調(diào)用）?START：
????????conn.execute('select?*?from?userinfo',function(err,rs){
????????????if(err)?{
????????????????console.log(err.message);
????????????????return;
????????????}
????????????//打印所有的數(shù)據(jù)
????????????let?data?=?rs.rows;
????????????//?for(let?record?of?data)?{
????????????//?????//打印每行記錄
????????????//?????//console.log(record[1]);
????????????//?????let?[id,act,pwd,birth]?=?record;
????????????//?????let?o?=?{id,act,pwd,birth};
????????????//?????console.log(o);
????????????//?}
????????????let?newData?=?data.map(ay?=>?{
????????????????let?[id,act,pwd,birth]?=?ay;
????????????????return?{id,act,pwd,birth};
????????????});
????????????//?console.log("查詢所有的數(shù)據(jù)："+newData);這樣寫可能打印不出數(shù)據(jù)。
????????????console.log("查詢所有的數(shù)據(jù)：");
????????????console.log(newData);
????????});
????????????//?查詢所有?END
??//新增?START：
?????let?sql?=?'insert?into?userinfo?values(seq_userinfo.nextval,:1,:2,:3)';
????????conn.execute(sql,['詩書畫唱好帥！','帥到想給他三連！',new?Date()],{autoCommit:true},function(err,rs){
????????????if(err)?{
????????????????console.log(err.message);
????????????????return;
????????????}
????????????let?count?=?rs.rowsAffected;
????????????console.log('插入了'?+?count?+?'條數(shù)據(jù)');
????????});
??//新增?END
????});
}).listen(8081);
console.log('服務(wù)器啟動(dòng)成功，網(wǎng)頁上輸入http://localhost:8081的訪問路徑就可以控制臺(tái)打印數(shù)據(jù)等等')
輸入node ora 后按回車鍵
輸入http://localhost:8081訪問網(wǎng)頁
例子5：用MySQL連接數(shù)據(jù)庫，Vscode中使用nodejs查看一張表的所有數(shù)據(jù)，打印在控制臺(tái)，并且增加一條數(shù)據(jù)（增刪改其實(shí)基本一樣的代碼，就是使用的SQL語句不一樣）
npm?install?mysql?-g
開啟MySQL的服務(wù)
輸入注冊(cè)碼NAVJ-W56S-3YUU-MVHV登錄MySQL
增加數(shù)據(jù)如果單個(gè)寫時(shí)，只執(zhí)行一次增加的部分的代碼，
如果和查詢數(shù)據(jù)寫在一起，就會(huì)運(yùn)行一次服務(wù)時(shí)，會(huì)執(zhí)行2次增加部分的代碼
mysql.js
let?http?=?require('http');
let?url?=?require('url');
let?mysql?=?require('mysql');
//npm?install?mysql?-g
//創(chuàng)建一個(gè)簡(jiǎn)單的后臺(tái)服務(wù)程序
http.createServer(function(req,resp){
????console.log('注釋掉其他內(nèi)容的，看createServer調(diào)用幾次的打印語句');
/*測(cè)試過，createServer是調(diào)用一次的，
當(dāng)查詢mysql中一個(gè)表的所有數(shù)據(jù)的時(shí)候，其實(shí)會(huì)執(zhí)行2次。不用管，
發(fā)到網(wǎng)頁時(shí)仍然是執(zhí)行一次的數(shù)據(jù)，增加數(shù)據(jù)如果單個(gè)寫時(shí)，只執(zhí)行一次增加的部分的代碼，
如果和查詢數(shù)據(jù)寫在一起，就會(huì)運(yùn)行一次服務(wù)時(shí)，會(huì)執(zhí)行2次增加部分的代碼*/
????//從url路徑中解析傳遞過來的參數(shù)
????//req.url就是在瀏覽器地址欄輸入的路徑
????let?ps?=?url.parse(req.url,true).query;
????//?let?op?=?ps.op;
????//個(gè)人理解:op是地址欄傳過來的參數(shù)
????//?console.log(op);
????//獲取數(shù)據(jù)庫的連接對(duì)象
?const?conn?=?mysql.createConnection({
????????host:?'localhost',
????????user:?'root',
????????password:?'root',
????????port:?3306,
????????database:?'firstjsp'
????});
????//console.log(conn);
????//連接數(shù)據(jù)庫
conn.connect();???
//查詢所有：
let?sql?=?'select?*?from?News';
//?let?bid?=?ps.bid;
/*個(gè)人理解[bid]是設(shè)置sql語句中的?的占位符的值的內(nèi)容，
沒有的話就是設(shè)置為[]*/
conn.query(sql,[],function(err,result){
????if(err)?{
??????console.log(err.message);
????}
????/*?resp.end是把數(shù)據(jù)顯示在網(wǎng)頁上的方法?*/
????//處理中文亂碼?START
????resp.writeHead(200,{'Context-Type':'text/html'});
????resp.write('<head><meta?charset="utf-8"/></head>');
????????//處理中文亂碼?END
????????console.log(JSON.stringify(result));
????/*??resp.end(JSON.stringify(result));?
????使用這段代碼時(shí)render函數(shù)會(huì)執(zhí)行兩次?，所以我暫時(shí)不用?。?*/?
});
//??conn.end();
???
//增加數(shù)據(jù)（只執(zhí)行這個(gè)部分不會(huì)被調(diào)用2次，和上面的查詢語句結(jié)合就回執(zhí)行2次）?START?
let?sql2?=?'insert?into??News(content,pubtime?)?values?(?,?);';
conn.query(sql2,["666",'2021-1-1'],function(err,rs){
????//?if(err)?{
????//?????console.log(err.message);
????//?}
????//?resp.end(JSON.stringify(rs));
????//?if(err)?{
????//?????console.log(err.message);
????//?????return;
????//?}
????if(err){
????????console.log('error');
????????return;
????}
????console.log('-----------------新增成功----------------');
????let?count?=?rs.affectedRows;
????console.log('插入了'?+?count?+?'條數(shù)據(jù)');
});
//增加數(shù)據(jù)（只執(zhí)行這個(gè)部分不會(huì)被調(diào)用2次，和上面的查詢語句結(jié)合就回執(zhí)行2次）?END
//?增加數(shù)據(jù)
//?var?addsql?=?'insert?into??News(content,pubtime)?values?(?,?);';
//?var?addsqlparams?=?["詩書畫唱?jiǎng)?chuàng)作出了很好的作品！",'2021-1-1'];
//?conn.query(addsql,addsqlparams,function?(err,result)?{
//?????if(err){
//?????????console.log('error');
//?????????return;
//?????}
//?????console.log('-----------------新增成功----------------');
//?????console.log(result);
//?????console.log('-----------------結(jié)束----------------');
//?})
????/*if(op?===?'getLatest')?{//獲取最新的期刊數(shù)據(jù)
????????let?sql?=?'select?*?from?classic?order?by?`index`?desc?limit?0,1';
????????conn.query(sql,[],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}?else?{
????????????????//將查詢到的數(shù)據(jù)傳遞到前臺(tái)去
????????????????resp.end(JSON.stringify(result[0]));
????????????}???????????
????????});
????}?else?if(op?===?'like')?{//更新點(diǎn)贊狀態(tài)
????????let?nums?=?ps.nums;//點(diǎn)贊的次數(shù)
????????let?status?=?ps.status;//點(diǎn)贊的狀態(tài)
????????nums?=?status?===?'like'???parseInt(nums)?+?1?:?(nums?-?1);
????????status?=?status?===?'like'???1?:?0;
????????let?index?=?ps.index;//點(diǎn)贊的期刊號(hào)
????????let?sql?=?'update?classic?set?fav_nums?=??,like_status?=???'?
????????????+?'where?`index`?=??';
????????conn.query(sql,[nums,status,index],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end();??????????
????????});
????}?else?if(op?===?'getClassic'){
????????let?index?=?ps.index;
????????let?status?=?ps.status;
????????if(status?===?'next')?{
????????????index?++;
????????}?else?{
????????????index?--;
????????}
????????let?sql?=?'select?*?from?classic?where?`index`?=??'
????????conn.query(sql,[index],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end(JSON.stringify(result[0]));??????????
????????});
????}?else?if(op?===?'getHotList')?{
????????let?sql?=?'select?t1.*,t2.fav_nums,t2.like_status?from?book?t1,`like`?t2?where?t1.id?=?t2.bookid';
????????conn.query(sql,[],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end(JSON.stringify(result));??????????
????????});
????}?else?if(op?===?'getBookDetail')?{
????????let?sql?=?'select?*?from?book?where?id?=??';
????????let?bid?=?ps.bid;
????????conn.query(sql,[bid],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end(JSON.stringify(result));??????????
????????});
????}?else?if(op?===?'getBookComments')?{
????????let?sql?=?'select?*?from?comments?where?bookid?=??';
????????let?bid?=?ps.bid;
????????conn.query(sql,[bid],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end(JSON.stringify(result));??????????
????????});
????}?else?if(op?===?'getBookLike')?{
????????let?sql?=?'select?*?from?`like`?where?bookid?=??';
????????let?bid?=?ps.bid;
????????conn.query(sql,[bid],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????resp.end(JSON.stringify(result));??????????
????????});
????}?else?if(op?===?'query')?{
????????let?q?=?ps.q;
????????let?start?=?parseInt(ps.start);
????????let?pageSize?=?parseInt(ps.pageSize);
????????let?sql?=?'select?t1.*,t2.fav_nums,t2.like_status?from?book?t1,`like`?t2?where?t1.id?=?t2.bookid?and?author?like???or?title?like???limit??,?';
????????q?=?'%'?+?q?+?'%';
????????conn.query(sql,[q,q,start,pageSize],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????//查詢總記錄條數(shù)，將記錄條數(shù)放到result中去
????????????resp.end(JSON.stringify(result));??????????
????????});
????}?else?if(op?===?'getHot')?{
????????resp.end('{"hot":["Python","哈利·波特","村上春樹","東野圭吾","白夜行","韓寒","金庸","王小波"]}');
????}?else?if(op?===?'likeBook')?{
????????const?bid?=?ps.bid;
????????const?status?=?ps.status;
????????let?sql?=?'update?`like`?set?fav_nums?=?fav_nums?'
????????????+?(status?===?'like'???'+'?:?'-')?+'?1,like_status?=?'
????????????+?(status?===?'like'???1?:?0)?+'?where??bookid?=??';
????????????conn.query(sql,[bid],function(err,result){
????????????????if(err)?{
????????????????????console.log(err.message);
????????????????}
????????????????resp.end(JSON.stringify(result));??????????
????????????});
????}?else?if(op?===?'postComment')?{
????????let?bid?=?ps.bid;
????????let?comment?=?ps.comment;
????????//判斷表中是否有這條評(píng)論
????????let?sql?=?'select?count(*)?ct?from?comments?where?bookid?=???and?content?=??';
????????conn.query(sql,[bid,comment],function(err,result){
????????????if(err)?{
????????????????console.log(err.message);
????????????}
????????????let?count?=?result[0].ct
????????????if(count?>?0)?{//在數(shù)據(jù)庫中已經(jīng)有這條評(píng)論了
????????????????sql?=?'update?comments?set?nums?=?nums?+?1?where?bookid?=???and?content?=??';
????????????????conn.query(sql,[bid,comment],function(er,rs){
????????????????????if(er)?{
????????????????????????console.log(er.message);
????????????????????}
????????????????????resp.end(JSON.stringify(rs));
????????????????});
????????????}?else?{//在數(shù)據(jù)庫中沒有這條評(píng)論
????????????????sql?=?'insert?into?comments?(bookid,content,nums)?values(?,?,?)';
????????????????conn.query(sql,[bid,comment,1],function(er,rs){
????????????????????if(er)?{
????????????????????????console.log(er.message);
????????????????????}
????????????????????resp.end(JSON.stringify(rs));
????????????????});
????????????}
????????});
????}*/
???
}).listen(8889);
console.log('后臺(tái)服務(wù)創(chuàng)建成功，可以通過http://localhost:8889/的方式訪問');
MySQL中表相關(guān)的SQL語句和執(zhí)行方法
鼠標(biāo)右鍵，點(diǎn)擊“運(yùn)行所選”
create table News(
ID int primary key auto_increment,
content varchar(100) not null,
pubtime varchar(100)? not null
);
insert into? News(
content ,
pubtime?
) values ("世界衛(wèi)生組織總干事特德羅斯阿德哈諾姆格布雷耶蘇斯在日內(nèi)瓦宣布新型冠狀病毒感染的肺炎被命名為-19"
,'2020-2-11');
insert into? News(
content ,
pubtime?
) values ("國(guó)家衛(wèi)健委發(fā)布《關(guān)于修改新型冠狀病毒英文命名的通知》決定將“新型冠狀病毒”英文名稱修改為-19與世界衛(wèi)生組織命名一致且中文名稱不變",'2020-2-21');
insert into? News(
content ,
pubtime?
) values ("《中華人民共和國(guó)民法典》《中華人民共和國(guó)檔案法》《中華人民共和國(guó)退役軍人保障法》實(shí)施",'2021-1-1');
insert into? News(
content ,
pubtime?
) values ("自2021年起每年1月10日為中國(guó)人民警察節(jié)",'2021-1-10');
嚴(yán)格模式下生命周期會(huì)被調(diào)用兩次。假設(shè)沒有更新state或者props，render函數(shù)又執(zhí)行了兩次，很有可能是哪個(gè)地方用了React.StrictMode。
當(dāng)然，目前我認(rèn)為可能與render無關(guān)，我認(rèn)為和 resp.end(JSON.stringify(result)); 有關(guān)。因?yàn)榭赡軟]有用到render（但我老師說是render的原因，嚴(yán)格模式下render函數(shù)會(huì)執(zhí)行，可能渲染2次），但是這個(gè)情況，目前其實(shí)沒有太多影響（增加部分和查詢部分分開寫就可以不調(diào)用2次了?？赡芸尚械姆椒ǎ涸O(shè)置一個(gè)變量，比如i或state狀態(tài)機(jī)等等，第二次調(diào)用時(shí)，if判斷出是第2次，就停止第2次調(diào)用查詢等部分。以上是個(gè)人理解，可能有誤，請(qǐng)斧正，我有時(shí)有空等時(shí)會(huì)更改等等。）
這樣寫后執(zhí)行2次查詢和增加數(shù)據(jù)的代碼部分
這樣寫，查詢和增加部分就都只執(zhí)行1次
注釋掉查詢部分，增加部分就只執(zhí)行1次
本期專欄耗時(shí)的個(gè)人嘗試猜想后實(shí)踐確認(rèn)猜想后的發(fā)現(xiàn)：node和mysql數(shù)據(jù)庫連接時(shí)，用end顯示數(shù)據(jù)到網(wǎng)頁會(huì)讓查詢部分等等調(diào)用2次，用write就沒有這種情況。
經(jīng)過多次嘗試，猜想，實(shí)踐確認(rèn)，終于解決更多問題等等了！
Node.js 連接 MySQL，增刪改查
菜鳥教程
https://www.runoob.com/nodejs/nodejs-mysql.html
