第七色在线视频,2021少妇久久久久久久久久,亚洲欧洲精品成人久久av18,亚洲国产精品特色大片观看完整版,孙宇晨将参加特朗普的晚宴

1、插入數(shù)據(jù)

insert into 表（字段1，字段2） values（字段1值1，字段2值1），（字段1值2，字段2值2）

?

2、刪除數(shù)據(jù)

delete from 表：刪除所有數(shù)據(jù)

delete from 表 where 條件：刪除指定的數(shù)據(jù)

3、更新數(shù)據(jù)

update 表 set 字段=值 ：修改表中所有這個字段的值

update 表 set 字段1=值1，字段2=值2 where 條件：修改指定數(shù)據(jù)的值

MySQL總結(jié)

在Node.js中使用MySQL

• 在Node.js中連接數(shù)據(jù)庫

• 使用數(shù)據(jù)庫連接池

• 使用Promise

• 使用預(yù)處理

在執(zhí)行sql語句時，有時會遇到大量結(jié)構(gòu)相同僅部分變量不同的語句，直接執(zhí)行這些語句消耗的時間是巨大的，而預(yù)處理則是為了這種情況準備的語法

一種減輕服務(wù)器壓力的技術(shù)

傳統(tǒng)SQL語句處理流程：

--在客戶端準備sql語句

select * from student where id =1

--發(fā)送sql語句到mysql服務(wù)器

--mysql服務(wù)器對sql語句進行解析-編譯-執(zhí)行

--將執(zhí)行結(jié)果返回給客戶端

傳統(tǒng)的弊端：

--即使多次傳遞的語句大部分內(nèi)容都是相同的，每次還是要重復(fù)傳遞

--即使多次傳遞的語句大部分內(nèi)容都是相同的，每次執(zhí)行之前還是要先解析、編譯之后才能執(zhí)行

預(yù)處理的處理流程：

--在客戶端準備預(yù)處理的SQL語句

--發(fā)送預(yù)處理SQL語句進行解析-編譯，但不執(zhí)行

--在客戶端準備相關(guān)數(shù)據(jù)

--MySQL服務(wù)器對數(shù)據(jù)和預(yù)處理SQL編譯，然后執(zhí)行該SQL語句

--服務(wù)器將執(zhí)行結(jié)果返回給客戶端

--預(yù)處理優(yōu)點：只對SQL語句進行了一次解析；重復(fù)內(nèi)容大大減少(網(wǎng)絡(luò)傳輸更快)

如何使用預(yù)處理

-在客戶端準備預(yù)處理的SQL語句

prepare? ppstmt from 'select * from student where id = ?;';

--發(fā)送預(yù)處理SQL語句進行解析-編譯，但不執(zhí)行

--在客戶端準備相關(guān)數(shù)據(jù)

set @id = 1;

--MySQL服務(wù)器對數(shù)據(jù)和預(yù)處理SQL編譯，然后執(zhí)行該SQL語句

execute?ppstmt using @id

--服務(wù)器將執(zhí)行結(jié)果返回給客戶端

--多表查詢

直接查詢：只需要在單表查詢的基礎(chǔ)上增加一張表即可，返回的結(jié)果是多張表的表數(shù)據(jù)個數(shù)的乘積，會有冗余數(shù)據(jù)

select * from student，class

select * from student，class where student.class_id = class.id

連接查詢：

內(nèi)連接查詢(INNER JOIN 或 JOIN)

select * from student join class on student.class_id = class.id;

select student.id class.name? from student join class on?student.class_id = class.id;

select stu?.id cls.name? from student stu join class cls on?stu?.class_id = cls.id;? ?--- 取別名

外連接查詢：?

左外連接查詢(LEFT OUTER JOIN 或 LEFT JOIN)

左邊的表是不看條件的，無論條件是否滿足，都會返回左邊中的所有數(shù)據(jù)，只有右邊的表會看條件，對于右表，只有滿足條件，才會返回

select * from student left join class on?student.class_id = class.id;

右外連接查詢(LEFT OUTER JOIN 或 LEFT JOIN)

右邊的表是不看條件的，無論條件是否滿足，都會返回右邊中的所有數(shù)據(jù)，只有左邊的表會看條件，對于左表，只有滿足條件，才會返回

select * from student right join class on?student.class_id = class.id;

UNION 查詢

在縱向上將多張表的查詢結(jié)果拼接起來返回? 注意：必須保證多張表查詢的字段個數(shù)一致(id,name)

select id,name from student union select id,name,from class

子查詢：將一個查詢語句查詢的結(jié)果作為另外一張表的查詢條件來使用

select class_id from student where id=3

select class_id from student where id>=3

select name from class where id = (select class_id from student where id=3)

select name from class where id = (select class_id from student where id>=3) -- 報錯

select name from class where id in (select class_id from student where id>=3) -- 正確

--分組查詢 GROUP BY: 在對數(shù)據(jù)進行分組的時候，select后面必須是分組字段或者聚合函數(shù)

select * from student;

select class from student group by class;

select class, avg(age) from student group by class;

select gender, avg(age) avgAge from student group by gender;

--HAVING條件查詢:? WHERE 是取數(shù)據(jù)表中查詢符合條件的數(shù)據(jù)返回結(jié)果集，HAVING是取結(jié)果集中查詢符合條件的數(shù)據(jù)，可以對分組之后查詢到的結(jié)果進行篩選

select class , avg(age) from student group by class;

select class , avg(age) from student group by class where avg(age) <= 19 ; --報錯

select class , avg(age) avg from student group by class having avg(age) <= 19 ; --正確? 在結(jié)果集中篩選

-- 聚合函數(shù): 對表中的數(shù)據(jù)進行統(tǒng)計和計算，一般結(jié)合分組(GROUP BY)來使用，用于統(tǒng)計和計算分組數(shù)據(jù)

COUNT() 計算查詢到的多少條數(shù)據(jù)

SUM() 計算查詢結(jié)果中所有指定字段的和

AVG() 計算查詢結(jié)果中所有指定字段的平均值

MAX() 求查詢結(jié)果中指定字段的最大值

MIN() 求查詢結(jié)果中指定字段的最小值

select * from student;

select count(*) from student;

select count(id) from student;

select sum(age) from student;

select avg(age) from student;

select max(age) from student;

select min(age) from student;

--給查詢出來的字段起別名? 兩種寫法同效果

select count(*) as total from student;??

select count(*) total from student;

---排序

select * from student order by age asc; -- 默認就是升序 ascending

select * from student order by age desc; -- 默認就是降序 descending

select * from student order by age desc , id asc; -- 先按照年齡降序，再按id升序

--分頁查詢

limit -- 一次查詢的條數(shù) offset 偏移量

limit --? 偏移量，一次查詢的條數(shù)

select * from student limit 10 offset 0;

select * from student limit 10 offset 10;

select * from student limit 10 offset 20;

select * from student limit 0, 10;? 偏移量0查詢10條

--1、 基礎(chǔ)查詢

select id, name from student; -- 查詢指定的字段

select * from student -- 查詢所有字段

--2、WHERE 條件查詢

select * from student? where id=4;

select * from student? where id>=4;

select * from student? where id!=4;

select * from student? where age >=19 and? gender ='男';

select * from student? where age >=19 or gender ='男';

select * from student? where not age >=19;

select * from student? where id in(1, 2, 4); -- 范圍限定在指定的值中

select * from student? where id between 1 and 4; -- 在一個范圍內(nèi)，包含邊界

select * from student? where? class? is null; -- 查 class 為null的數(shù)據(jù)

select * from student? where? class? is not null; -- 查 class 不為null的數(shù)據(jù)

select * from student? where? name like '張%';? ?----? %百分號是匹配任意個任意字符

select * from student? where? name like '張_';? ?----? ? _下劃線匹配一個任意字符

select * from student? where? name like '%三';? ?----? ? 查詢‘三’結(jié)尾，前面任意個任意字符

select * from student? where? name like '%三%';? ?----? ?查詢包含‘三' 的數(shù)據(jù)

-- 1、插入數(shù)據(jù)(增)

insert into user (username) values('Alex');

insert into user (username,gender) values ('張三', '男');

insert into user (username,gender) values ('張三', '男'), ('李四', '男');

---2、刪除數(shù)據(jù)(刪)

delete from user; --- 刪除所有的數(shù)據(jù)

delete from user where id=6;

---3、更新數(shù)據(jù)(改)

update user set username='趙六'; --- 修改所有的

update user set username='Alex', gender='男' where id=5; -- 修改id=5指定的數(shù)據(jù)

預(yù)處理

?

create table if not EXISTS user (

id int UNSIGNED PRIMARY KEY auto_increment,

username VARCHAR(20),

gender ENUM('男','女', '保密') DEFAULT '保密'

);

show TABLES;

DESC user;

-- 1、插入

insert INTO user (username) VALUES ('Alex');

insert INTO user (username, gender) VALUES ('張三', '男');

insert INTO user (username, gender) VALUES ('李四', '男'), ('王五', '男');

-- 2、刪除

DELETE from user WHERE id = 2;

-- 3、更新

update user set username='mali', gender='女' where user.id = 1;

create table if not EXISTS student (

id int UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) not NULL,

gender ENUM('男','女', '保密') DEFAULT '保密',

class VARCHAR(20),

age INT NOT NULL,

createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT INTO student (name, age, gender, class) values ('王五',21,'女','二班');

INSERT INTO student (name, age, gender, class) values ('趙柳',22,'男','一班');

INSERT INTO student (name, age, gender, class) values ('孫琦',20,'女','三班');

INSERT INTO student (name, age, gender) values ('粥吧',19,'男');

INSERT INTO student (name, age, gender, class) values ('及時',18,'女','二班');

INSERT INTO student (name, age, gender, class) values ('李盼',18,'女','一班');

INSERT INTO student (name, age, gender) values ('馮吉', 19,'男');

-- 1. 基礎(chǔ)查詢

SELECT id,name from student;

SELECT * from student;

-- 2. where條件查詢

-- >、>=、<、<=、=、!=

-- AND、OR、NOT

-- IN(值1，值2,...)、BETWEEN...AND

-- IS NULL、IS NOT NULL

-- LIKE 模糊查詢

SELECT * from student WHERE student.id=4;

SELECT * from student WHERE student.age>=19 AND gender='男';

SELECT * from student WHERE student.age IN(19,20,21);

SELECT * from student WHERE student.age BETWEEN 19 AND 21;

SELECT * from student WHERE student.class IS NULL;

SELECT * from student WHERE student.name LIKE '王%'; -- % 表示任意個數(shù)的任意字符

SELECT * from student WHERE student.name LIKE '王_'; -- _ 表示一個任意字符

SELECT * from student WHERE student.name LIKE '%五%'; -- _ 表示一個任意字符

-- 3.排序

SELECT * FROM student ORDER BY age asc; -- 默認就是升序 ascending

SELECT * FROM student ORDER BY age desc; -- 降序 descending

SELECT * FROM student ORDER BY age desc, id asc; -- 先按照年齡降序，如果年齡相同則按照id升序排列

-- 4.分頁查詢

-- LIMIT 一次查詢的條數(shù) OFFSET 偏移量

-- LIMIT 偏移量,一次查詢的條數(shù)

SELECT * FROM student LIMIT 2 OFFSET 0;

SELECT * FROM student LIMIT 2 OFFSET 2;

SELECT * FROM student LIMIT 2 OFFSET 4;

SELECT * FROM student LIMIT 0, 2;

-- 5.聚合函數(shù)

-- 例如函數(shù) SELECT DATABASE() 非聚合函數(shù);

-- 對表中的數(shù)據(jù)進行統(tǒng)計和計算，一般結(jié)合分組（GROUP BY）來使用,用于統(tǒng)計和計算分組數(shù)據(jù)

-- COUNT() 計算查詢到了多少條數(shù)據(jù)

-- SUM() 計算查詢結(jié)果中所有指定字段的和

-- AVG() 計算查詢結(jié)果中所有指定字段的平均值

-- MAX() 查詢結(jié)果中指定字段的最大值

-- MIN() 查詢結(jié)果中指定字段的最小值

SELECT * FROM student;

SELECT COUNT(*) FROM student;

SELECT SUM(age) FROM student;

SELECT AVG(age) FROM student;

SELECT MAX(age) FROM student;

SELECT MIN(age) FROM student;

-- 給查詢出來的字段起別名

SELECT COUNT(*) totalRecord FROM student;

-- 6.分組查詢 GROUP BY

SELECT * FROM student;

-- 在對數(shù)據(jù)進行分組的時候, SELECT 后面必須是分組字段或者聚合函數(shù)

-- SELECT * FROM student GROUP BY class; -- 錯誤案例

SELECT class FROM student GROUP BY class;

SELECT class, avg(age) FROM student GROUP BY class;

SELECT gender,AVG(age) avgAge FROM student GROUP BY gender;

-- 7.HAVING 條件查詢

-- WHERE 是去數(shù)據(jù)表中查詢符合條件的數(shù)據(jù)返回結(jié)果集

-- HAVING 是去結(jié)果集中查詢符合條件的數(shù)據(jù)，可以對分組之后查詢到的結(jié)果進行篩選

-- SELECT class,AVG(age) avgAge FROM student GROUP BY class WHERE avgAge<=19.5; -- where不行會報錯

SELECT class,AVG(age) avgAge FROM student GROUP BY class HAVING avgAge<=19.5;

-- 8. 多表查詢

-- 8.0.準備

create table if not EXISTS class ( -- 班級表

id TINYINT UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

`desc` VARCHAR(255),

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

undateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

INSERT into class (name, `desc`) VALUES('一班', '火箭班');

INSERT into class (name, `desc`) VALUES('二班', '飛機班');

INSERT into class (name, `desc`) VALUES('三班', '高鐵班');

INSERT into class (name, `desc`) VALUES('四班', '汽車班');

CREATE TABLE IF NOT EXISTS student ( -- 學生表

id INT UNSIGNED PRIMARY KEY auto_increment,

name VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NOT NULL,

gender ENUM('男', '女', '未知') DEFAULT '未知',

class_id TINYINT UNSIGNED,

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

undateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY(class_id) REFERENCES class(id) ON UPDATE CASCADE ON DELETE SET NULL

);

INSERT INTO student(name, age,gender, class_id) values('張三', 20, '男', 1);

INSERT INTO student(name, age,gender, class_id) values('李四', 21, '女', 3);

INSERT INTO student(name, age,gender, class_id) values('王五', 19, '女', 2);

INSERT INTO student(name, age,gender, class_id) values('趙六', 16, '男', 1);

INSERT INTO student(name, age,gender, class_id) values('宋七', 19, '女', 2);

INSERT INTO student(name, age,gender) values('康良', 19, '女');

-- 8.10 直接查詢

-- 秩序在單表查詢基礎(chǔ)上增加一張表即可，返回的結(jié)果是多張表數(shù)據(jù)局條數(shù)的乘積

SELECT * FROM student, class WHERE student.class_id = class.id;

-- 8.2 連接查詢

-- 8.2.1 內(nèi)連接（INNER JOIN 或 JOIN）

-- 內(nèi)連接的查詢結(jié)果和和直接查詢的結(jié)果是一樣的

SELECT * FROM student JOIN class; -- SELECT * FROM student,class;

SELECT * FROM student JOIN class ON student.class_id = class.id;

SELECT student.id id,student.name,class.name,class.id cid FROM student JOIN class ON student.class_id = class.id;

-- 8.2.2 外連接查詢

-- 8.2.2.1 左外連接查詢（LEFT OUTER JOIN 或 LEFT JOIN）

SELECT * FROM student LEFT JOIN class on student.class_id = class.id;

-- 8.2.2.2 右外連接查詢 （RIGHT OUTER JOIN 或 RIGHT JOIN）

SELECT * FROM student RIGHT JOIN class on student.class_id = class.id;

-- 8.3 UNION 查詢

-- 在縱向上將多張表的查詢結(jié)果拼接起來返回

-- 必須保證查詢的多張表的字段是一致的

SELECT id,name FROM student UNION SELECT id,name from class;

-- 8.4 子查詢

-- 8.4.1 將一個查詢語句查詢的結(jié)果作為另一個查詢語句的條件來使用

SELECT name, id from class WHERE id = (SELECT class_id FROM student WHERE id = 3)

SELECT name, id from class WHERE id IN (SELECT class_id FROM student WHERE id >= 3)

-- 8.4.2 講一個查詢語句的結(jié)果作為另一個查詢語句的表來使用

SELECT name from class WHERE id >= 2;

SELECT * FROM (SELECT name from class WHERE id >= 2) t; -- 必須給子查詢起個別名

select class,avg(age) from student group by class;

111111biao