web安全学习第三周

web安全学习（后端，爬虫代码）

爬虫学习

urllib库的学习

UA反爬的应对

解编码

MYSQL学习：

sql是资料库管理语言

表格中的主键primary key 外键 foregin key（对应另外一个表格的主键）

第一步创建表格，管理资料

添加表格属性的语法

DECIMAL(3,2)表示3位数，2位小数

提取数据

聚合函数

万用字元 _代表一个字符 %代表所有的字符

union集合必须集合同种类型的属性

join连接

subquery子查询

primary key不能为空

源码区：

1.单表格操作：

CREATE DATABASE `sql_tutorial`;

SHOW DATABASES;

DROP DATABASE `sql_tutorial`;

#创建表格

USE `sql_tutorial`;

CREATE TABLE `student`(

`student_id` INT PRIMARY KEY,

`name` VARCHAR(20),

`major` VARCHAR(20)

);

DESCRIBE `student`;

DROP TABLE `student`;

#输入表格中值

ALTER TABLE `student` ADD gpa DECIMAL(3,2); #在表格中添加一列gpa，是小数，总共3位数，小数占2位

ALTER TABLE `student` DROP COLUMN gpa;

INSERT INTO `student` VALUES(1,'小白','历史');

INSERT INTO `student` VALUES(2,'小黑','历史');

INSERT INTO `student` VALUES(3,'小绿',NULL);

SELECT * FROM `student`; #从全部student表格搜索

INSERT INTO `student`(`name`,`major`,`student_id`) VALUES('小红','英语',5); #可以自己定义顺序，如果没有写某个属性，就把该行资料的该属性定义为NULL

# constraint 限制、约束，预设值：DEFAULT

DROP TABLE `student`;

CREATE TABLE `student`(

`student_id` INT PRIMARY KEY,

`name` VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

`major` VARCHAR(20) UNIQUE #该列值要唯一，把UNIQUE改为 DEFAULT '历史' 表示把major的预设值改为历史，在没有major的输入时，自动表示历史。

);

INSERT INTO `student` VALUES(1,'小白','历史');

INSERT INTO `student` VALUES(2,'小黑','英语');

DESCRIBE `student`;

CREATE TABLE `student`(

`student_id` INT PRIMARY KEY,

`name` VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

`major` VARCHAR(20) DEFAULT '历史'

);

INSERT INTO `student` (`student_id`,`name`) VALUES(2,'小白');

DESCRIBE `student`;

SELECT * FROM `student`;

#AUTO_INCREMENT, #id自动增加，不用在输入时写id

DROP TABLE `student`;

CREATE TABLE `student`(

`student_id` INT AUTO_INCREMENT, #id自动增加，不用在输入时写id

`name` VARCHAR(20) NOT NULL, #后面的表示限制，该限制为不能空白

`major` VARCHAR(20) DEFAULT '历史' ,

PRIMARY KEY(`student_id`)

);

INSERT INTO `student` (`major`,`name`) VALUES('英语','小白');

INSERT INTO `student` (`major`,`name`) VALUES('英语','小黑');

DESCRIBE `student`;

SELECT * FROM `student`;

## 修改、删除资料

#修改 UPDATE

SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新

DROP TABLE student;

DROP TABLE student;

CREATE TABLE `student`(

`student_id` INT AUTO_INCREMENT,

`name` VARCHAR(20), #后面的表示限制，该限制为不能空白

`major` VARCHAR(20),

`score` INT,

PRIMARY KEY(`student_id`)

);

INSERT INTO `student` (`major`,`name`,`score`) VALUES('英语','小白',70);

INSERT INTO `student` (`major`,`name`,`score`) VALUES('生物','小蓝',72);

INSERT INTO `student` (`major`,`name`,`score`) VALUES('数学','小绿',76);

INSERT INTO `student` (`major`,`name`,`score`) VALUES('英语','小红',71);

SELECT * FROM `student`;

UPDATE `student` #把英语改成英语文学

SET `major` = '英语文学'

WHERE `major` = '英语';

UPDATE `student` #把第三个学生的major改为英语文学

SET `major` = '英语文学'

WHERE `student_id` = 3;

UPDATE `student` #把生物或者数学的改成生数

SET `major` = '生数'

WHERE `major` = '生物' OR `major`= '数学';

UPDATE `student` #把第三个学生的major改为英语文学,并且同时把名字也改了

SET `major` = '英语文学',`name` = '小灰'

WHERE `student_id` = 1;

UPDATE `student` #不写条件的话，就会吧所有的major都改变

SET `major` = '英语文学';

#删除 DELETE FROM

DELETE FROM `student` #把编号为4的学生删掉

WHERE `student_id` = 4;

SELECT * FROM `student`;

DELETE FROM `student` #可以用and选多个条件

WHERE `name` = '小红' AND `major` = '英语文学';

DELETE FROM `student` #根据成绩

WHERE `score` < 71; #不等于，不写where就会把整个表格删掉

##取得资料 SELECT 、 LIMIT

#SELECT

SELECT `major`, `name` FROM `student`; #SELECT 后接要搜寻的内容，*表示所有

SELECT * FROM `student`;

#排序 ORDER BY 由低到高；ORDER BY 属性 DESC 由高到低；ORDER BY 属性1,属性2 先按属性1排，如果1一样，再按2排序

SELECT * FROM `student`

ORDER BY `score`;

SELECT * FROM `student`

ORDER BY `score` DESC;

SELECT * FROM `student`

ORDER BY `score`, `student_id`;

# LIMIT n 限制回传的数量为n笔，就是前n行。

SELECT * FROM `student`

ORDER BY `student_id`

LIMIT 2;

SELECT * FROM `student`

ORDER BY `student_id`

LIMIT 2;

#用WHERE进行限制

SELECT * FROM `student`

WHERE `major`= '英语' AND `score` <> 71;

SELECT * FROM `student`

WHERE `major` IN('英语' , '历史' , '数学');#等价于下面用or联接

SELECT * FROM `student`

WHERE `major`= '英语' OR `major`='历史' OR `major`='数学';

# SELECT、where、order任意搭配使用。

2.多表格操作：

-- 创建公司资料表格

CREATE DATABASE `sql_tutorial`;

SHOW DATABASES;

USE `sql_tutorial`;

DROP TABLE `student`;

SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新

#创建表格

CREATE TABLE `employee`(

`emp_id` INT PRIMARY KEY,

`name` VARCHAR(20),

`brith_date` DATE,

`sex` VARCHAR(1),

`salary` INT,

`branch_id` INT,

`sup_id` INT

);

DROP TABLE `employee`;

describe TABLE employee;

#创建部门表格

CREATE TABLE `branch`(

`branch_id` INT PRIMARY KEY,

`branch_name` VARCHAR(20),

`manager_id` INT,

FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL #FOREIGN KEY表示外键；REFERENCES 表示外键接的表格名，再加外接的表格的primary key

);

#对公司表格补上外键foreign key，

ALTER TABLE `employee`

ADD FOREIGN KEY(`branch_id`) #加外键 branch_id属性

REFERENCES `branch`(`branch_id`) #对应到branch表格的branch_id的属性

ON DELETE SET NULL;

ALTER TABLE `employee`

ADD FOREIGN KEY(`sup_id`) #加外键sup_id

REFERENCES `employee`(`emp_id`) #对应到branch表格的branch_id的属性

ON DELETE SET NULL;

#创建客户表格

CREATE TABLE `client`(

`client_id` INT PRIMARY KEY,

`client_name` VARCHAR(20),

`phone` VARCHAR(20)

);

#创建worh_with表格

CREATE TABLE `work_with`(

`emp_id` INT,

`client_id` INT,

`total_sales` INT,

PRIMARY KEY ( `emp_id`, `client_id`),

FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,

FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE

);

#填写表格数据

#有foreign key时，对应的表没有相应资料时，直接输入会报错，要用NULL代替

INSERT INTO `branch` VALUES(1,'研发',NULL);

INSERT INTO `branch` VALUES(2,'行政',NULL);

INSERT INTO `branch` VALUES(3,'查询',NULL);

INSERT INTO `branch` VALUES(4,'查询',NULL);

INSERT INTO `employee` VALUES(206,'小黄','1998-10-08','F',50000,1,NULL);

INSERT INTO `employee` VALUES(207,'小绿','1995-10-09','M',51000,2,206);

INSERT INTO `employee` VALUES(208,'小灰','1993-11-24','M',23000,3,207);

INSERT INTO `employee` VALUES(209,'小黑','1996-10-08','M',36000,3,208);

INSERT INTO `employee` VALUES(210,'小红','1997-07-05','F',50000,1,209);

#修改

UPDATE `employee`

SET `branch_id` = '3'

WHERE `emp_id` = 209;

select * from `employee`;

desc `employee`;

DROP TABLE `employee`;

#把原本branch的manager_id=NULL改回来

UPDATE `branch`

SET `manager_id`=206

where `branch_id`=1;

UPDATE `branch`

SET `manager_id`=207

where `branch_id`=2;

UPDATE `branch`

SET `manager_id`=208

where `branch_id`=3;

INSERT INTO `client` VALUES(400,'阿狗','1234567');

INSERT INTO `client` VALUES(401,'阿猫','1673692');

INSERT INTO `client` VALUES(402,'来福','1894738');

INSERT INTO `client` VALUES(403,'路西','1746952');

INSERT INTO `client` VALUES(405,'杰克','9947538');

INSERT INTO `work_with` VALUES(206,400,70000);

INSERT INTO `work_with` VALUES(207,400,'56000');

INSERT INTO `work_with` VALUES(208,402,'35000');

INSERT INTO `work_with` VALUES(209,403,'54000');

INSERT INTO `work_with` VALUES(210,404,'64000');

DROP TABLE `worh_with`;

desc work_with;

select * from `work_with`;

##聚合函数 aggregate functions

#取得员工数目

select count(*) from `employee`; #表格里有基笔资料

select count(sup_id) from `employee`; #count()里对应的属性的资料有几笔

#取得所有出生在1996-10-01之后出生的女性员工

select count(*) from `employee`

where `brith_date` > '1996-10-01' and `sex`='F';

#取得所有员工的平均薪水

select avg(`salary`) from `employee`;

#取得所有员工的总和薪水 sum

select sum(`salary`) from `employee`;

#取得最高/低员工的薪水 max,min

select max(`salary`) from `employee`;

##wildcards 万用字元 %表示多个字元，_表示一个字元，可以用一个，也可以前后都用

#取得电话号码尾数是567的客户

select * from `client`

where `phone` like '%567';

#取得电话号码开头是189的客户

select * from `client`

where `phone` like '189%';

#取得姓阿的员工

select * from `client`

where `client_name` like '阿%';

#取得生日在10月的员工

select * from `employee`

where `brith_date` like '_____10%';

select * from `employee`;

##union 连集 可以合并搜寻的结果，合并的属性资料形态要一样，不同表格的属性数目要一样，不能用表格1的2个属性去和表格2的1个属性合并

#员工名字和客户名和部门名字字合并

select (`name`) from `employee`

union

select (`client_name`) from `client`

union

select (`branch_name`) from `branch`;

select `name`,`emp_id` from `employee`

union

select `client_name`,`client_id` from `client`;

select `name` as `total_name`,`emp_id` as `total_id` #改变回传的属性名称

from `employee`

union

select `client_name`,`client_id` from `client`;

##join 连接

insert into `branch` values(4,'瑜伽',NULL);

#取得所有部门经理的名字，把employee和branch两个表格连接起来，条件是连个id一样。

select * from employee

#select `emp_id`, `name`, `branch_name` from `employee`

join `branch`

on `emp_id` = `manager_id`;

#如果不同表格有相同属性，用表格.属性名称进行区分，说明表格属性来源

select `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name` from `employee`

join `branch`

on `employee`.`emp_id` = `branch`.`manager_id`;

#把左/右边的表格都回传，右/左边边表格符合条件才回传,否则回传NULL, left join/right join

select *

#from `employee` left join `branch`

from `employee` right join `branch`

on `emp_id` = `manager_id`;

select * from `branch`;

delete from `branch` where `branch_id` = 4;

##subquery 子查询，在一个查询语句里面查询另外一个结果，就是在一个查询语句里面插入另外一个查询结果

#找出研发部门的经理名字：先找研发部门的manager_id(放在括号里面的，就是子查询的部分），再用id进行查找名字

select `name` from `employee`

where `emp_id` = (

select `manager_id` from `branch`

where `branch_name` = '研发'

);

#找出一位客户销售金额超过50000的员工名字:当子查询回传结果不止一个，要用in不用=

select `name` from `employee`

where `emp_id`in (

select `emp_id` from `work_with`

where `total_sales` > 50000

);

# on delete 删除某一行

# on delete set NULL：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2设为null,当资料2为primary key时，不能设置为null，只能为on delete cascade

#on delete cascade：如果删掉某个资料1，其他表格的资料2用外键对应的该资料1，在其他表格中把资料2也删掉

#创建worh_with表格

CREATE TABLE `work_with`(

`emp_id` INT,

`client_id` INT,

`total_sales` INT,

PRIMARY KEY ( `emp_id`, `client_id`),

FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,

FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE

);

#创建部门表格

CREATE TABLE `branch`(

`branch_id` INT PRIMARY KEY,

`branch_name` VARCHAR(20),

`manager_id` INT,

FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL #FOREIGN KEY表示外键；REFERENCES 表示外键接的表格名，再加外接的表格的primary key

);

delete from `employee`

where `emp_id`= 207;

select * from `work_with`;

select * from `branch`;

update `branch`

set `manager_id`=207

where `branch_id`=2;

INSERT INTO `employee` VALUES(207,'小绿','1995-10-09','M',51000,2,206);

INSERT INTO `work_with` VALUES(207,400,'56000');