-- 单行注释：双中划线+空格 或 #号（mysql特有），多行注释：/*  */

-- 创建数据库，会在data目录生成同名文件夹，里面有一个db.opt文件，保存了库选项（比如字符集等信息）
CREATE DATABASE my_database CHARSET utf8;
-- 含有关键字需要使用反引号，但不建议使用关键字作数据库名
CREATE DATABASE `database` CHARSET utf8;
-- 中文数据库需要先指定字符集
SET NAMES gbk;
CREATE DATABASE 中文 CHARSET utf8;

-- 查看数据库
SHOW DATABASES;
-- 模糊查询中，_表示匹配单个字符，%表示匹配多个字符
SHOW DATABASES LIKE `my_%`;
-- 如果名字含有下划线则需要转义
SHOW DATABASES LIKE `my\_%`;
-- 查看建表语句
SHOW CREATE DATABASE my_database;

-- 数据库的名字不可更改
-- 修改数据库的字符集
ALTER DATABASE my_database CHARSET utf8;
ALTER DATABASE my_database CHARACTER SET utf8;
-- 可以使用空格，也可以使用等号
ALTER DATABASE my_database CHARSET=utf8;
-- COLLATE:校对集
ALTER DATABASE my_database COLLATE utf8;

-- 删除数据库
DROP DATABASE my_database;

-- 选择数据库
USE my_database;

-- 建表，会在数据库对应的文件夹下创建frm格式的结构文件（和引擎有关）
-- 可以指定数据库名
CREATE TABLE IF NOT EXISTS my_database.mytable(
id int,
name varchar(10)
)CHARSET=utf8 COLLATE=utf8 ENGINE=innodb;

-- 创建临时表，临时表只在当前连接可见，当断开连接就会自动删除
CREATE TEMPORARY TABLE temptable(
id int,
name varchar(10)
);

SHOW TABLES;
SHOW TABLES LIKE `my%`;
SHOW CREATE TABLE mytable;
-- \g 等价于分号
SHOW CREATE TABLE mytable\g
-- \G 将查询的结果的显示格式改成纵向
SHOW CREATE TABLE mytable\G

-- 复制已有表的表结构（不会复制数据），可以指定数据库名
CREATE TABLE mytable2 LIKE my_database.mytable;
-- 复制数据
INSERT INTO mytable2 SELECT * FROM mytable;
-- 压力测试时可以复制自身数据，实现指数级增长数据
INSERT INTO mytable2 SELECT * FROM mytable2;

-- 查看表结构，一下三种方式是等价的
DESC COLUMNS FROM mytable;
DESCRIBE COLUMNS FROM mytable;
SHOW COLUMNS FROM mytable;
-- 查看表结构
DESCRIBE mytable;

-- 重命名表
RENAME TABLE mytable TO my_table;
-- 同样，可以使用空格或等号
ALTER TABLE mytable CHARSET=utf8;

-- 新增字段
ALTER TABLE mytable ADD COLUMN addr VARCHAR(20);
-- COLUMN可以不写
ALTER TABLE mytable ADD addr VARCHAR(20);
-- 新增字段并指定位置
ALTER TABLE mytable ADD COLUMN addr VARCHAR(20) FIRST;
ALTER TABLE mytable ADD COLUMN addr VARCHAR(20) AFTER id;
-- 修改字段，可以更改属性或位置
ALTER TABLE mytable MODIFY addr VARCHAR(25) AFTER name;
-- 重命名字段并修改属性和位置
ALTER TABLE mytable CHANGE addr address VARCHAR(20) AFTER id;
-- 删除字段
ALTER TABLE mytable DROP address;

-- 删表
DROP TABLE mytable1, mytable2;

-- 不指定插入的字段时需数据的顺序需和字段顺序一致，且非数值数据都要使用引号（建议是单引号）
INSERT INTO mytable VALUE(1,'小明');
-- VALUE只能插入单条，VALUES可以插入单条或多条
INSERT INTO mytable(id,name) VALUES(1,'小明'), (2,'小红');

-- 主键没有冲突时直接插入数据；主键冲突时，更新对应记录
INSERT INTO mytable VALUES(1,'小红') ON DUPLICATE KEY UPDATE name='小红';
-- 主键没有冲突时直接插入数据；主键冲突时，先删除原记录，再插入新记录
REPLACE INTO mytable VALUES(1,'小红');
-- 主键没有冲突时直接插入数据；主键冲突时，保持原纪录，忽略新插入的记录
INSERT IGNORE INTO mytable VALUES(1,'小红');

-- 查看数据
SELECT ALL * FROM mytable; -- 查询所有记录
SELECT * FROM mytable; -- ALL可以省略
SELECT * FROM mytable,mytable2; -- 多个数据源
SELECT DISTINCT * FROM mytable; -- 去重
SELECT id, DISTINCT name FROM mytable; -- 部分去重
SELECT id AS my_id,name AS my_name FROM mytable AS t; -- 别名
SELECT id my_id,name my_name FROM mytable t; -- AS可以省略

SELECT * FROM mytable GROUP BY name; -- 分组
SELECT sex, count(*), max(age), min(age), avg(height), sum(age) FROM mytable1 GROUP BY sex; -- GROUP BY一般配合统计函数使用（sex、age、height都是我们的字段名，count、max、min、avg、sum是统计函数），GROUP BY会自动按照分组字段排序（默认ASC，升序）
SELECT sex, count(*), max(age), min(age) FROM mytable1 GROUP BY sex DESC; -- GROUP BY会对分组后合并的结果进行排序
SELECT city, sex, count(*), group_concat(name) FROM mytable1 GROUP BY city, sex; -- 多字段分组，group_concat是聚合函数，它可以对分组中的指定字段进行字符串连接，把多行数据变成单行数据
SELECT sex,count(*) FROM mytable1 GROUP BY sex WITH ROLLUP; -- 回溯统计
SELECT city,sex,count(*),group_concat(name) FROM mytable1 GROUP BY city,sex WITH ROLLUP; -- 多字段分组+回溯统计

SELECT * FROM mytable ORDER BY name DESC; -- 排序（ASC、DESC）
SELECT * FROM mytable ORDER BY city,name DESC; -- 多字段排序，这里是city升序、name降序

-- WHERE是从磁盘读一条记录，判断是否满足，不满足直接丢弃，从而保证加载到内存中的都是有效数据
SELECT * FROM mytable WHERE id<=5; -- 条件，可以使用>、<、=、>=、<=、、!=（不等于，旧标准）、<>（不等于，新标准）
SELECT * FROM mytable WHERE id BETWEEN 10 AND 20; -- BETWEEN TO 是闭区间，且左边的数要小于右边的
SELECT * FROM mytable WHERE name LIKE '小%';
SELECT * FROM mytable WHERE name IS NULL;
SELECT * FROM mytable WHERE id IN (1,2,3,4,5);
SELECT * FROM mytable WHERE id<=5 AND name NOT LIKE '小%'; -- 使用NOT、AND、OR组合多种条件，优先级：NOT > AND > OR
SELECT * FROM mytable WHERE id IN (SELECT mid FROM mytable2 WHERE name LIKE '%小%'); -- 使用子查询
SELECT * FROM mytable WHERE id=SELECT mid FROM mytable2 WHERE name LIKE '%小%' LIMIT 1;
SELECT id FROM mytable WHERE id > ALL(SELECT mid FROM mytable2); -- 使用IN（返回TRUE或FALSE，但如果含NULL，会返回UNKNOWN）、EXISTS（返回TRUE或FALSE，可以处理NULL）、ANY（满足任意一个，要和=或!=或<>一起使用，比如 xxxx=ANY(xxx)）、SOME（some是any的别名，用的比较少，也要和=或!=或<>一起使用）、ALL（ALL必须与比较操作符一起使用，<>ALL等同于NOT IN）
SELECT * FROM mytable WHERE (age,height) = (SELECT max(age),max(height) FROM mytable2); -- 返回单行时可以这样用
SELECT * FROM mytable WHERE (age,height) IN (SELECT age,height FROM mytable2); -- 返回多行时可以这样用
SELECT * FROM mytable WHERE (1,2) = (SELECT mid,mname FROM mytable2 WHERE mid=2); -- (1,2)即第1、2列
SELECT * FROM (SELECT * FROM mytable2) AS derived_table WHERE derived_table.id > 5; -- 派生表，从返回的结果中再次执行查询

SELECT id AS my_id,name FROM mytable HAVING my_id>3; -- HAVING可以使实现WHERE的所有功能
SELECT city,count(*) FROM mytable1 GROUP BY city HAVING count(*)>=2; -- 但WHERE不能实现HAVING的部分功能，比如：HAVING可以使用别名、统计函数，如果和GROUP BY一起使用，HAVING可以过滤分组的结果
SELECT city,count(*) AS total FROM mytable1 GROUP BY city HAVING total>=2; -- 优化：不需要重复查询两次count(*)，可以使用别名

SELECT * FROM mytable LIMIT 1,5; -- 分页，第1页，每页5条，即第6~10条记录
SELECT * FROM mytable LIMIT 5; -- 相当于LIMIT 0,5
SELECT * FROM mytable LIMIT 96,-1; -- 检索96~last

SELECT id,name FROM mytable UNION SELECT id,name FROM mytable2; -- 联合查询，把多条查询的得到的多个结果集合并成一个结果集，要求两个查询必须拥有相同数量的列，但列的数据类型不需要一致，且顺序必须相同
SELECT id,name FROM mytable UNION ALL SELECT id,name FROM mytable2; -- UNION会自动去重，要想不去重，使用UNION ALL
(SELECT id,name,sex FROM mytable ORDER BY sex DESC LIMIT 999) UNION (SELECT id,name,sex FROM mytable2 ORDER BY sex ASC LIMIT 999); -- 联合查询使用ORDER BY需要用括号括起来，但这样ORDER BY并不会生效，要使ORDER BY生效，还有搭配LIMIT使用（可以LIMIT一个足够大的数来实现不限制记录数）

SELECT t1.id,t1.name,t2.id id2,t2.name name2 FROM mytable t1 JOIN mytable2 t2 ON t1.id=t2.id WHERE name IS NOT NULL; -- 连接查询，JOIN分为CROSS JOIN（交叉连接）、JOIN（内连接）、LEFT JOIN（左外连接）、RIGHT JOIN（右外连接）
SELECT t1.*,t2.* FROM mytable t1 JOIN mytable2 t2 WHERE t1.id=t2.id; -- ON可以用WHERE替代，但WHERE效率会低一点
SELECT t1.*,t2.* FROM mytable t1 JOIN mytable2 t2 USING(id); -- USING(id)相当于ON t1.id=t2.id
SELECT * FROM mytable CROSS JOIN mytable2; -- CROSS JOIN
SELECT * FROM mytable,mytable2; -- CROSS JOIN也可以写成这样，其实就是前面提到的多数据源
SELECT * FROM mytable NATURAL JOIN mytable2; -- NATURAL JOIN 会自动根据相同的列名JOIN，并合并同名列，如果有多个同名列，会同时JOIN多个列

-- 更新数据
UPDATE mytable SET name='小明';
UPDATE mytable SET name='小明' WHERE name LIKE '小%' LIMIT 3;

-- 删除数据
DELETE FROM mytable WHERE id=2;
-- 删除表中所有数据（自增长不会重置）
DELETE FROM mytable;
-- 删除表中所有数据并重置自增长
TRUNCATE mytable;

-- 创建视图
CREATE VIEW myview AS SELECT * FROM mytable; -- 单表视图，其中AS不能省略
CREATE VIEW myview2 AS SELECT t1.*,t2.name,t2.sex FROM mytable t1 LEFT JOIN mytable2 t2 ON t1.other_id = t2.id; -- 多表视图。字段名不能重复，如果有重复，需使用别名

-- 视图就是一个虚拟表（但是它不存储数据，只是定义了表结构），表的所有查看方式都能用于视图
SHOW TABLES myview;
SHOW TABLES LIKE 'my%';
SHOW CREATE TABLE myview;
-- 视图也有自己特有的查看方式
SHOW CREATE VIEW myview;

-- 使用视图主要是为了查询方便，可以自动执行定义时指定的SQL
SELECT * FROM myview;

-- 多表视图不能增和删里面的数据，只有单表视图才可以（要求视图包含的所有字段都允许为NULL或者有默认值），这时会往基表增或删数据而不是往视图中增或删，因为视图不存储数据
INSERT INTO myview VALUES(null,180,150,1);
DELETE FROM myview WHERE id=1;

-- 无论单表还是多表视图，都可以修改里面的数据
UPDATE myview2 SET other_id=3 WHERE id=1;
-- 如果在创建视图时指定了WITH CHECK OPTION，修改视图数据时就要保证修改后数据仍然能被视图查出来（即修改不能影响总记录数）
CREATE VIEW myview3 AS SELECT * FROM mytable2 WHERE age>20 WITH CHECK OPTION; -- 禁止视图将age设置为小于20
-- 如果修改数据使其变为视图的查询范围，不会提示ERROR，但是修改无效（即只能更新视图中可以查询到的数据）
-- 找不到id=6的数据，WHERE没有任何匹配，所以没有UPDATE任何记录，但不会报错
UPDATE myview3 SET age=23 WHERE id=6; -- 假设原来age<=20，无法通过视图查询到

-- 修改视图
ALTER VIEW myview AS SELECT t1.height,t1.age,t2.name,t2.sex FROM mytable t1 LEFT JOIN mytable2 t2 ON t1.other_id = t2.id;

-- 删除视图，删除视图不会影响数据
DROP VIEW myview;

-- 视图的算法
/*
视图的算法分为
UNDEFINED: 未定义（默认），系统自动选择下面的一种
TEMPTABLE: 临时表算法，系统先执行视图的SELECT，后执行外部的查询语句，当外部查询的子句比内部查询优先级低，需要使用该算法，比如内部使用了ORDER BY，而外部使用了GROUP BY，而GROUP BY会对分组后合并的结果进行排序，GROUP BY优先级比ORDER BY低，就要使用TEMPTABLE算法（其他情况默认即可）
MERGE: 合并算法，系统先把视图的SELECT和外部的查询语句进行合并，然后执行（效率高，因为只执行了一次）
*/
-- 先ORDER BY然后GROUP BY
CREATE ALGORITHM=TEMPTABLE VIEW myview AS SELECT * FROM mytable ORDER BY height DESC;
SELECT * FROM myview GROUP BY age; -- GROUP BY会选取不同age的第一条记录，这里就是选区不同年龄的最高身高

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 手动事务
-- 开启事务（两种方式都是等价的）
BEGIN;
START TRANSACTION;

-- 创建事务保存点，一个事务中可以有多个SAVEPOINT
SAVEPOINT identifier;
-- 删除一个事务的保存点
RELEASE SAVEPOINT identifier;
-- 回滚到保存点
ROLLBACK TO identifier;

-- 提交事务（两种方式都是等价的）
COMMIT;
COMMIT WORK;

-- 回滚（两种方式都是等价的）
ROLLBACK;
ROLLBACK WORK;

-- 自动事务
-- 默认开启自动事务
SHOW VARIABLES LIKE 'autocommit';
-- 关闭自动事务（本次session有效）
SET autocommit = 0;

这时每次修改数据都需要手动提交
UPDATE mytable SET money=1000 WHERE id=1;
COMMIT; -- COMMIT或者ROLLBACK

-- 查看所有支持的字符集
SHOW CHARACTER SET;
-- 查看默认字符集
SHOW VARIABLES LIKE 'character_set%';

-- SET的更改只是当前会话有效，重新登陆命令行失效
-- 服务器端的编码是gbk，而数据库的编码是utf8，于是设置client的编码为gbk，接收到数据后会自动转成utf8
-- GBK两个字节一个汉字，UTF8三个字节一个汉字（英文和其他字符可能会优化成1~3个字节）
SET character_set_client=gbk;
-- 设置返回的数据的编码为gbk
SET character_set_results=gbk;
-- 设置字符集的快捷方式，相当于设置了character_set_client、character_set_results、character_set_connection
-- character_set_connection是中间转码的字符集，配置能提高效率，但不配置也不影响使用
SET NAMES gbk;

/*
校对集分为：
_bin: binary，二进制比较
_cs: case sensitive，大小写敏感
_ci: case insensitive，大小写不敏感
*/
-- 查看所有的校对集
SHOW COLLATION;
-- 在建表的时候指定校对集
CREATE TABLE mytable(
name char(2)
)CHARSET utf8 COLLATE utf8_general_ci
-- 校对集在比较的时候生效，比如ORDER BY
INSERT INTO mytable VALUES('a'),('A'),('B'),('b');
SELECT * FROM mytable ORDER BY name;
-- 校对集只能在没有数据之前声明好，如果有了数据，那么修改对之前的数据无效
ALTER TABLE mytable COLLATE=utf8_bin;

-- 查看警告
SHOW warnings;

-- 备份表
SELECT * INTO OUTFILE '~/backup/mytable' FROM mytable; -- "*"也可以改成想备份的字段
-- 可以指定导出的数据格式
SELECT * INTO OUTFILE '~/backup/mytable'
CHARACTER SET utf8
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' -- 字段间用竖线分隔（默认为制表符'\t'），使用双引号包裹数据（默认不包裹），特殊符号用'\\'处理（默认是'\\'）
LINES STARTING BY 'START:' TERMINATED BY '\n' -- 每行以START:开始，以换行符结束
FROM mytable;

-- 还原
LOAD DATA INFILE "~/backup/mytable" INTO TABLE mytable;
-- 如果备份时指定了格式，还原时也要指定相同的格式
LOAD DATA INFILE "~/backup/mytable"
INTO TABLE mytable
CHARACTER SET utf8
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY 'START:' TERMINATED BY '\n';

CREATE TABLE mytable(
s1 CHAR(20),
s2 VARCHAR(20),
s3 TEXT,
s4 BLOB,
s5 ENUM('男','女','保密')，
s6 SET('北京','上海','广州')
)CHARSET utf8;

INSERT INTO mytable VALUES('ABCD','ABCD','ABCD','ABCD','男','上海,北京');
-- enum: 1对应'男'，set: 3对应二进制为011，倒过来是110，所以对应是插入'北京,上海'
INSERT INTO mytable VALUES('ABCD','ABCD','ABCD','ABCD',1,3);

-- VARCHAR的实际最大长度受MYSQL中规定的最大记录长度为65535限制
CREATE TABLE mytable(
i1 TINYINT NOT NULL, -- 1
s2 VARCHAR(21844) NOT NULL, -- 21844*3+2=65534，UTF8一个字符最多占3个字节，分配空间是按最多的算的
)CHARSET utf8;

-- ENUM实际存储的是数值而不是字符串本身
-- 原理：
SELECT 1 + 'hello'; -- 得到1
SELECT 1 + '1hello'; -- 得到2，和PHP的自动转换类似，不以数字开头的字符串会自动转成0
-- 验证：如果s5是字符串，那么字符串+0结果还是0，如果是数值，那么结果会是其他数字；为了对比，我们也把原来的s5查出来
SELECT s5 + 0, s5 FROM mytable;
-- SET同理
SELECT s6 + 0, s6 FROM mytable;

CREATE TABLE mytable(
int_1 TINYINT,
int_2 SMALLINT,
int_3 MEDIUMINT
int_4 INT,
-- 使用无符号类型
int_5 INT UNSIGNED,
-- 括号中的数字为显示宽度，比如TINYINT默认是4位，因为对于无符号的TINYINT，可以是-127，即包含了符号位
-- 显示宽度和存储的大小无关，更改显示宽度不会影响存储大小
int_6 INT(11),
-- 显示宽度一般配合ZEORFILL使用，ZEORFILL表示没有达到显示宽度的时候补零（达到或超过显示宽度正常显示），一旦使用了ZEORFILL，会自动把数据改为UNSIGNED
int_7 INT(8) ZEORFILL,
int_8 BIGINT
)CHARSET utf8;

CREATE TABLE mytable(
-- 不使用小数位
f1 FLOAT,
-- 需要指定总长度（包括整数部分和小数部分，不包括小数点）和小数精度
f2 FLOAT(10,2),
d1 DECIMAL(10,2)
)CHARSET utf8;

CREATE TABLE mytable(
d1 DATETIME,
d2 TIMESTAMP,
d3 DATE,
d4 TIME,
d5 YEAR
)CHARSET utf8;

INSERT INTO mytable VALUES(
'1900-01-02 01:02:03','1970-01-02 01:02:03','1900-01-02','01:02:03',1901);

INSERT INTO mytable VALUES(
'1900-01-02 01:02:03',
'1970-01-02 01:02:03',
'1900-01-02',
-- -2表示过去两天，所以实际上是 -(48小时+01:02:03)
'-2 01:02:03',
-- 使用2位的YEAR，插入后变成2069
69);

INSERT INTO mytable VALUES(
'1900-01-02 01:02:03',
'1970-01-02 01:02:03',
'1900-01-02',
'01:02:03',
-- 使用2位的YEAR，插入后变成1970
70);

-- TIMESTAMP只要所在记录被更新，就会自动更新成当前的时间
UPDATE mytable SET d1='1900-00-00 00:00:00' WHERE d5=1970;
-- 查看当前时间
SELECT unix_timestamp();

CREATE TABLE mytable(
-- 参与业务的主键（如姓名等）称为业务主键/自然主键，不参与业务只用于唯一标识数据的主键称为逻辑主键
-- AUTO_INCREMENT只能有一个，一般配合PRIMARY KEY使用，类型一定是整型
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE KEY COMMENT '姓名',
gender CHAR(6) NOT NULL DEFAULT 'male'
)CHARSET utf8;

-- COMMENT可以这样查看
SHOW CREATE TABLE mytable;

-- 设置主键
CREATE TABLE mytable(
id INT,
name VARCHAR(20),
PRIMARY KEY(id)
)CHARSET utf8;
-- 或者追加主键，这时要求字段对应的数据不能重复
ALTER TABLE mytable MODIFY id INT PRIMARY KEY;

-- 联合主键
CREATE TABLE mytable(
id INT,
name VARCHAR(20),
PRIMARY KEY(id,name)
)CHARSET utf8;
-- 或者
ALTER TABLE mytable ADD PRIMARY KEY(id,name);

-- 主键无法更改，只能先删除再创建
ALTER TABLE mytable DROP PRIMARY KEY;

-- 自增长的主键传null或DEFAULT或不传都会触发自动增长
-- 传入DEFAULT或不传该字段的值会使用默认值
INSERT INTO mytable VALUES(null,'小明',DEFAULT);
INSERT INTO mytable VALUES(DEFAULT,'小明',DEFAULT);
INSERT INTO mytable(name) VALUES('小明');
-- 自增长如果传入值则不会触发，下次会从最大值继续+1
INSERT INTO mytable VALUES(10,'小明',DEFAULT);

-- 修改自增长的值，该值只能比当前自增长的值大，不能小（小了不会报错，但修改无效）
ALTER TABLE mytable AUTO_INCREMENT=10;
-- 查看自增长，可以修改起始值和步长，该修改是对整个数据库修改，通过SET修改是会话级的
SHOW VARIABLES LIKE 'auto_increment%';
SET auto_increment_increment=2;

-- 删除自增长就是MODIFY列的时候不写AUTO_INCREMENT
-- 有主键的时候不需要再声明主键，因为主键的声明不是在列后面的，而是通过PRIMARY KEY(id)在最后设置的（可以通过SHOW CREATE TABLE mytable查看）
ALTER TABLE mytable MODIFY id INT;


-- UNIQUE KEY和PRIMARY KEY类似
-- UNIQUE KEY允许为NULL，NULL不参与唯一性比较
CREATE TABLE mytable(
id INT,
name VARCHAR(20) UNIQUE KEY
)CHARSET utf8;
-- 或者
CREATE TABLE mytable(
id INT,
name VARCHAR(20),
UNIQUE KEY(id,name)
)CHARSET utf8;
-- 或者追加唯一键
ALTER TABLE mytable ADD UNIQUE KEY(id,name);

-- 删除UNIQUE KEY约束
-- name实际上是索引名，唯一键的索引名默认是列名
ALTER TABLE mytable DROP INDEX name; -- 这里不是删除列，只是删除唯一性约束

-- 查看索引
SHOW INDEX FROM mytable;


-- FOREIGN KEY和PRIMARY KEY类似，只能使用其他表的主键来作为外键
CREATE TABLE mytable(
id INT,
name VARCHAR(20) UNIQUE KEY,
other_id INT,
FOREIGN KEY(other_id) REFERENCES mytable2(id);
)CHARSET utf8;
-- 或者通过追加方式
ALTER TABLE mytable ADD FOREIGN KEY(other_id) REFERENCES mytable2(id);
-- 追加外键的同时指定外键的名字为id_1，也可以使用IF NOT EXISTS，括号可以写在一起也可以分开
ALTER TABLE `mytable` ADD CONSTRAINT `id_1` FOREIGN KEY IF NOT EXISTS (`other_id`) REFERENCES `mytable2` (`id`);

-- 外键的约束模式
-- 严格模式（RESTRICT），默认；RESTRICT和NO ACTION作用是一样的，父表在删除和更新记录并涉及到主键时，检查字表是否有关联的外键记录，如果有则不允许删除或更新（子表就是含外键的表）
CREATE TABLE mytable(
id INT,
name VARCHAR(20) UNIQUE KEY,
other_id INT,
FOREIGN KEY(other_id) REFERENCES mytable2(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
)CHARSET utf8;
-- 置空（SET NULL），当父表更新、删除并涉及到主键时，子表会把外键字段置为NULL，此时要求字段可NULL
CREATE TABLE mytable(
id INT,
name VARCHAR(20) UNIQUE KEY,
other_id INT,
FOREIGN KEY(other_id) REFERENCES mytable2(id) ON DELETE SET NULL ON UPDATE SET NULL;
)CHARSET utf8;
-- 级联（CASCADE），当父表更新、删除并涉及到主键时，子表会同步更新和删除
CREATE TABLE mytable(
id INT,
name VARCHAR(20) UNIQUE KEY,
other_id INT,
FOREIGN KEY(other_id) REFERENCES mytable2(id) ON DELETE CASCADE ON UPDATE CASCADE;
)CHARSET utf8;

-- 删除外键，需使用外键的名字，即通过ADD CONSTRAINT指定的名字
ALTER TABLE mytable DROP FOREIGN KEY id_1;

/*
创建方法：
#方法一：创建表时
CREATE TABLE 表名 (
字段名1  数据类型 [完整性约束条件…],
字段名2  数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX/KEY [索引名] (字段名[(长度)] [ASC |DESC]) 
);
#方法二：CREATE在已存在的表上创建索引
CREATE  [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]);
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 (字段名[(长度)]  [ASC |DESC]) ;
*/

CREATE TABLE mytable(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
INDEX nameindex (name) -- 索引的名字也可以不指定
)CHARSET utf8;
-- 或者
CREATE INDEX name ON mytable(name);
CREATE UNIQUE INDEX id_name ON mytable(id,name); -- 联合索引
-- 或者
ALTER TABLE mytable ADD INDEX name (name(4)); -- 前面的name是索引名字，括号的name是列名，可以指定索引的长度（如果是BLOB或TEXT则必须指定，CHAR或VARCHAR可以不指定）

-- 插入初始数据
INSERT INTO mytable VALUES(1,'小明'), (2,'小红');

-- 查看索引
SHOW INDEX FROM mytable;

-- 分析SQL用到的索引
EXPLAIN SELECT * FROM mytable WHERE name LIKE '%小%'\G
/*输出
           id: 1
  select_type: SIMPLE   -- 表示查询中每个select子句的类型（简单 或 复杂）
        table: mytable
   partitions: NULL
         type: index    -- 表示MySQL在表中找到所需行的方式，又称“访问类型”
possible_keys: NULL     -- 可能使用到的索引（不一定真的会使用）
          key: nameindex    -- 实际使用的索引，若没有使用索引，显示为NULL
      key_len: 63       -- 索引中使用的字节数
          ref: NULL     -- 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
         rows: 2        -- 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
     filtered: 50.00
        Extra: Using where; Using index     -- 包含不适合在其他列中显示但十分重要的额外信息 如using where，using index
*/

-- 删除索引
DROP INDEX name ON mytable;

-- 除此以外，还可以指定索引的查询方式，常用的有BTREE（二叉树结构，最常用）和HASH（只支持=、IN、<>，不支持范围查询；对于对于组合索引，只有使用了所有索引键时才会生效，因为是对所有组合键做HASH，少了无法匹配到；对于大量重复HASH的情况，效率不一定比BTREE高），不常用的有FULLTEXT（中文支持不是很好，MyISAM才支持）、RTREE（仅支持geometry数据类型）
CREATE TABLE mytable(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
INDEX nameindex (name) USING BTREE -- 索引的名字也可以不指定
)CHARSET utf8;
-- 或者
CREATE INDEX name ON mytable(name) USING BTREE;
-- 或者
ALTER TABLE mytable ADD INDEX name USING BTREE; 

ALTER TABLE mytable ADD INDEX abc (a,b,c,d);

SELECT * FROM mytable WHERE b=45 AND a=3 AND c=5 AND d=4; -- 全部生效
SELECT * FROM mytable WHERE a=3 and c=5; -- a生效，c无效
SELECT * FROM mytable WHERE b=45 AND c=5; -- 全部无效

SELECT * FROM mytable WHERE b=45 AND a=3 AND c=5 AND d>4; -- 全部生效
SELECT * FROM mytable WHERE a=3 AND b=45 AND d=4 ORDER BY c; -- a、b、c生效（正常order by使用EXPLAIN会在Extra中显示Using filesort，而这里没有，说明使用了c索引）
SELECT * FROM mytable WHERE a=3 AND d=4 ORDER BY c,b; -- 注意ORDER BY c,b和ORDER BY b,c不同，这里只有a生效（同样可以通过EXPLAIN查看是否使用了Using temporary和Using filesort，使用了索引不需要）

-- 查看所有系统变量
SHOW VARIABLES;
-- 查看某个变量
SELECT @@version,@@autocommit;

-- 会话级别修改
SET autocommit = 0;
SET @@autocommit=0;

-- 全局级别修改（其他新登陆的session也可以看到修改，但如果已登陆，无法看到修改）
SET GLOBAL autocommit = 0;

-- 自定义变量，自定义的变量都是当前session有效（不区分数据库），退出后清空
-- 系统为了区分系统变量和自定义变量，自定义变量只能使用一个@
SET @name = '张三';
SELECT @name;
-- 等号在MYSQL中大多表示比较，而非赋值，MYSQL为了区分比较和赋值，定义了一个新的赋值符号:=
SET @age := 18;
-- 变量的定义或更改需要使用SET，使用SET系统就知道是赋值，这时既可以用=，又可以用:=
SET @age = 1;

SELECT @name := name FROM mytable; -- 从字段中取值赋给变量，此时只能用:=，不能用=；如果记录有多条，取最后一条（MYSQL不支持数组）
SELECT name,age FROM mytable WHERE id = 2 INTO @name,@age; -- 这种方式要求查询的记录只有一条

/*
分支结构
IF xxx THEN
    xxx
ELSE
    xxx
END IF;
*/

/*
WHILE xxx DO
    xxx
END WHILE;

-- 可以用ITERATE实现跳过此次循环，用LEAVE实现跳出循环，但是要用到循环名字
循环名字:WHILE xxx DO
    xxx
    -- ITERATE 循环名字;
    -- 或者
    -- LEAVE 循环名字;
END WHILE;
*/

/*
一般定义如下：
CREATE TRIGGER 触发器名字 触发时间(BEFORE、AFTER) 事件类型(INSERT、DELETE、UPDATE) ON 表名 FOR EACH ROW
DELIMITER 自定义分隔符 -- 临时修改结束符，因为系统以结束符分隔SQL语句，而触发器内部需要用到多条语句，要是输入分号直接结束了SQL语句，就无法完整地定义一个触发器
BEGIN
    -- 触发器内容，以系统原来的结束符――分号（";"）――结束
    -- 触发器内部的语句不要再次触发当前触发器监听的事件，否则会导致死循环
    -- 可以通过 old.字段名 或 new.字段名 获取当前的数据以及即将更新的数据
END
自定义的那个分隔符 -- 表示定义完触发器了
DELIMITER 系统原来的分隔符 -- 把结束符修正过来

一张表中每种 触发时间+触发类型 只能有一个触发器，即一张表最多只能有6个触发器
*/
CREATE TRIGGER mytrigger AFTER INSERT ON mytable FOR EACH ROW
DELIMITER $$ 
BEGIN
    SELECT money FROM mytable2 WHERE id = new.wallet_id INTO @money;
    IF @money > new.price THEN
        UPADTE mytable2 SET money = money - new.price WHERE id = new.wallet_id;
    ELSE
        -- 触发器不能禁止插入/修改/删除，只能通过报错的方式使得操作失败
        INSERT INTO xxx VALUES(XXX); -- 不存在该表，所以保存，使得事务回滚，插入失败
    END IF;
END
$$
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE '%trigger%';

SHOW CREATE TRIGGER mytrigger;

-- 所有的触发器都会保存在information_schema.triggers中
SELECT * FROM information_schema.triggers\G

-- 触发器无法修改，只能先删除再创建
DROP TRIGGER mytrigger;

/*
一般定义如下
DELIMITER $$
CREATE FUNCTION 函数名(形参列表) RETURNS 返回值数据类型
BEGIN
    xxx;
    RETURN xxx;
END
$$
DELIMITER ;
*/

-- 只有一行的函数可以不写BEGIN、END
CREATE FUNCTION display100() RETURNS INT
RETURN 100;

-- 从1加到指定数
DELIMITER $$
CREATE FUNCTION sumTo(num INT) RETURNS INT
BEGIN
    SET @i = 1;
    SET @ret = 0;
    WHILE @i <= num DO
        SET @ret = @ret + @i;
    SET @i = @i + 1;
    END WHILE;
    RETURN @ret;
END
$$
DELIMITER ;

-- 调用
SELECT display100();
SELECT sumTo(100);

-- 查看函数
SHOW FUNCTION STATUS;
SHOW FUNCTION STATUS LIKE '%display%';
SHOW CREATE FUNCTION display100;

-- 函数无法修改，只能先删除再创建
DROP FUNCTION display100;


-- 带@的都是全局变量，要定义局部变量，使用DECLARE关键字声明，且没有@符号，局部变量只能在函数体开头声明
-- 从1加到指定数，5的倍数不加
DELIMITER $$
CREATE FUNCTION sumTo(num INT) RETURNS INT
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE ret INT DEFAULT 0;
    mywhile:WHILE i <= num DO
        IF i % 5 = 0 THEN -- 这里=是比较符号
        SET i = i + 1;
        ITERATE mywhile;
    END IF;

        SET ret = ret + i;
    SET i = i + 1;
    END WHILE;
    RETURN ret;
END
$$
DELIMITER ;

/*
存储过程可以理解为没有返回值的函数，一般定义如下
CREATE PROCEDURE 过程名字(参数列表)
DELIMITER $$
BEGIN
    xxx;
END
$$
DELIMITER ;
*/

-- 只有一行可以不写BEGIN、END
CREATE PROCEDURE procedure1()
SELECT * FROM mytable; -- 存储过程中使用SELECT可以显示数据

-- 查看存储过程
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE '%procedure%';
SHOW CREATE PROCEDURE procedure1;

-- 调用存储过程（SELECT只能获取返回值，而存储过程没有返回值，所以不能通过SELECT调用）
CALL procedure1();

-- 存储过程无法修改，只能先删除再创建
DROP PROCEDURE procedure1;

-- 存储过程有自己的类型限定
/*
三种类型：
IN: 数据只能从外部传入，供内部使用（值传递，可以传值或变量）
OUT: 引用传递，外部的变量会被先清空，才会进入到内部（只能传变量）
INOUT: 引用传递，既可以在内部修改然后给外部使用，也可以在内部使用外部的数据（只能传变量）
*/
CREATE PROCEDURE procedure2(IN num1 INT, OUT num2 INT, INOUT num3 INT)
DELIMITER $$
BEGIN
    SELECT num1,num2,num3; -- num2的值一定为NULL
END
$$
DELIMITER ;

-- 使用
SET @num1 = 1;
SET @num2 = 2;
SET @num3 = 3;
SELECT @num1,@num2,@num3; -- 显示1，2，3
CALL procedure2(@num1,@num2,@num3); -- 显示1，NULL，3
SELECT @num1,@num2,@num3; -- 显示1，NULL，3


-- 存储过程结束后修改才会同步到全部变量中
CREATE PROCEDURE procedure3(IN num1 INT, OUT num2 INT, INOUT num3 INT)
DELIMITER $$
BEGIN
    SELECT num1,num2,num3; -- 显示1，NULL，3
    SET num1 = 10;
    SET num2 = 100;
    SET num3 = 1000;
    SELECT num1,num2,num3; -- 查看局部变量，显示10，100，1000
    SELECT @num1,@num2,@num3; -- 查看全局变量，显示1，2，3

    SET @num1 = 11; -- 修改全局变量
    SET @num2 = 111;
    SET @num3 = 1111;
    SELECT @num1,@num2,@num3; -- 查看全局变量，显示11，111，1111
    -- 存储过程结束，把OUT和INOUT类型赋值给全局变量
END
$$
DELIMITER ;

-- 使用
SET @num1 = 1;
SET @num2 = 2;
SET @num3 = 3;
CALL procedure2(@num1,@num2,@num3);
SELECT @num1,@num2,@num3; -- 显示11，100，1000

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" version="4.0" >

    <!-- 配置当前web应用的初始化参数，可以通过ServletContext获取 -->
    <context-param>
        <param-name>configLocation1</param-name>
        <param-value>WEB-INF/myconfig1.properties</param-value>
    </context-param>
    <context-param>
        <param-name>configLocation2</param-name>
        <param-value>WEB-INF/myconfig2.properties</param-value>
    </context-param>

    <servlet>
        <servlet-name>HelloServlet</servlet-name>
        <servlet-class>webapp.servlets.HelloServlet</servlet-class>

        <!-- 配置Servlet的初始化参数，可以在init方法中通过ServletConfig获取 -->
        <!-- 和context-param不同的是，init-param只是当前servlet有效，而context-param是整个web应用有效 -->
        <init-param>
            <param-name>user</param-name>
            <param-value>root</param-value>
        </init-param>
        <init-param>
            <param-name>password</param-name>
            <param-value>123456</param-value>
        </init-param>

        <!-- 默认是在第一次请求该Servlet对应路径时创建Servlet实例，使用load-on-startup可以在Servlet容器初始化时就创建，该值为非负数，数值越小越先创建 -->
        <load-on-startup>1</load-on-startup>
    </servlet>

    <!-- 一个servlet可以有多个servlet-mapping -->
    <servlet-mapping>
        <servlet-name>HelloServlet</servlet-name>
        <!-- 要么指定后缀（/jsps/*.jsp）要么使用通配符（/jsps/*），通配符和后缀不能同时使用 -->
        <!-- /*表示拦截所有请求，比如http://localhost:8080/工程名字/xxx，就会调用该Servlet的service方法 -->
        <url-pattern>/*</url-pattern>
    </servlet-mapping>


    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>

    <error-page>
        <error-code>404</error=code>
        <location>/index.jsp</location>
    </error-page>
    <error-page>
        <exception-type>java.lang.ArithmeticException</exception-type>
        <location>/WEB-INF/error.jsp</location>
    </error-page>

</web-app>

public class HelloServlet implements Servlet {

    //Servlet是单例的，默认在第一次请求的时候创建，如果在web.xml中使用了load-on-startup，则在容器初始化时就创建
    public HelloServlet(){
        System.out.println("HelloServlet's constructor");
    }

    //在创建好实例后调用，只会被调用一次
    public void init(ServletConfig servletConfig) throws ServletException {
        System.out.println("init");

        //获取servlet的init-param
        String user = servletConfig.getInitParameter("user");
        String password = servletConfig.getInitParameter("password");
        //或者
        Enumeration<String> names1 = servletConfig.getInitParameterNames();
        while(names.hasMoreElements()){
            String name = names1.nextElement();
            String value = servletConfig.getInitParameter(name);
            System.out.println(name + "--" + value);
        }

        //获取context-param
        ServletContext servletContext = servletConfig.getServletContext();
        String configLocation1 = servletContext.getInitParameter("configLocation1");
        String configLocation2 = servletContext.getInitParameter("configLocation2");
        //或者
        Enumeration<String> names2 = servletContext.getInitParameterNames();
        while(names2.hasMoreElements()){
            String name = names2.nextElement();
            String value = servletContext.getInitParameter(name);
            System.out.println(name + "--" + value);
        }

        //获取web应用程序根目录下文件的绝对路径
        String realPath = servletContext.getRealPath("/hello.jsp");//斜杠表示当前web应用的根路径
        System.out.println(realPath);

        //获取web应用的根路径，即http://localhost:8080/web应用根路径/servlet-mapping的路径/xxx，通常web应用根路径为工程名字前面加斜杠(/)
        String contextPath = servletContext.getContextPath();
        System.out.println(contextPath);

        //读取文件
        try{
            ClassLoader classLoader = getClass().getClassLoader();
            InputStream is1 = classLoader.getResourceAsStream("jdbc.properties");//读取src目录下的jdbc.properties文件

            //这里的路径是web应用目录的路径，而不是src的路径
            InputStream is2 = servletConfig.getServletContext().getResourceAsStream("/WEB-INF/classes/jdbc.properties");//读取web应用目录下的/WEB-INF/classes/jdbc.properties文件（编译后会被拷贝到/WEB-INF/classes/下）
        }catch(Exception e){  }
    }

    public ServletConfig getServletConfig() {
        System.out.println("getServletConfig");
        return null;
    }

    public String getServletInfo() {
        System.out.println("getServletInfo");
        return null;
    }

    //每次请求该Servlet都会被调用
    public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
        System.out.println("service");

        //-------------ServletRequest-------------
        //http://localhost:8080/myproject/xxx?user=root&password=123456
        String user = request.getParameter("user");
        String password = request.getParameter("password");
        System.out.println(user + "--" + password);

        /* 用于表单中的多选框
         <input type="checkbox" name="habit" value="read">看书
         <input type="checkbox" name="habit" value="movie">电影
         <input type="checkbox" name="habit" value="game">游戏
        */
        String[] habbit = request.getParameterValues("habbit");
        System.out.println(Arrays.asList(habbit));

        //还有其他获取参数的方法
        //Enumeration<String> names = request.getParameterNames();
        //Map<String, String[]> map = request.getParameterMap();


        //有些参数要其子类HttpServletRequest才能获取
        HttpServletRequest httpRequest = (HttpServletRequest)request;

        httpRequest.setCharcterEncoding("utf-8");

        //获取 http://127.0.0.1:8080/web应用根路径/，web应用根路径一般就是工程名字前面加斜杠(/)
        String basePath = httpRequest.getScheme()+"://"+httpRequest.getServerName()+":"+httpRequest.getServerPort()+httpRequest.getContextPath()+"/";
        //获取 /servlet-mapping的路径/xxx
        String servletPath = httpRequest.getServletPath();
        //获取GET请求的参数（不包含?问号），如果是POST，获得的是null
        String queryString = httpRequest.getQueryString();

        //URI就是 /web应用根路径/servlet-mapping的路径/xxx ，即http://localhost:8080后面那一串（如果是GET请求，是不包含参数的，即不包含?及后面的内容）
        String requestURI = httpRequest.getRequestURI();
        //完整的访问路径叫URL（不包含GET的参数）
        String requestURL = httpRequest.getRequestURL();

        //获取请求方式
        String method = httpRequest.getMethod();

        //接收二进制数据，文件上传（form表单中method="post" enctype="multipart/form-data"）
        //这时得到的数据包含请求头、分隔符、其他请求参数等与文件数据无关的信息，需要手动解析获取的数据
        //所以一般使用Apache提供的工具包commons-fileupload.jar和commons-io.jar来实现文件上传
        //httpRequest.getInputStream();
        if(ServletFileUpload.isMultipartContent(httpRequest)){
            DiskFileItemFactory factory = new DiskFileItemFactory();
            factory.setSizeThreshold(1024 * 1024 * 3);
            factory.setRepository(new File(System.getProperty("java.io.tmpdir")));

            ServletFileUpload upload = new ServletFileUpload(factory);
            upload.setFileSizeMax(1024 * 1024 * 40);
            upload.setSizeMax(1024 * 1024 * 50);
            upload.setHeaderEncoding("UTF-8");

            String uploadPath = getServletContext().getRealPath("/") + File.separator + "upload";
            File uploadDir = new File(uploadPath);
            if (!uploadDir.exists()) {
                uploadDir.mkdir();
            }
            List<FileItem> formItems = upload.parseRequest(request);
            if (formItems != null && formItems.size() > 0) {
                // 迭代表单数据
                for (FileItem item : formItems) {
                    if (!item.isFormField()) {
                        String fileName = new File(item.getName()).getName();
                        String filePath = uploadPath + File.separator + fileName;
                        File storeFile = new File(filePath);
                        item.write(storeFile);
                        httpRequest.setAttribute("message", "文件上传成功!");
                    }
                }
            }
        }

        //转发请求
        String servletPath = "servlet2"
        RequestDispatcher requestDispatcher = httpRequest.getRequestDispatcher("/" + servletPath);//斜杠代表 域名+web应用的根路径
        requestDispatcher.forward(request, response);

        //-------------ServletResponse-------------
        response.setContentType("text/html");
        response.setCharcterEncoding("utf-8");

        //发送文本数据
        PrintWriter writer = response.getWriter();
        //writer.println("<!DOCTYPE html>");
        writer.println("<html><head><title>helloworld</title></head><body>Hello World!</body></html>");
        writer.flush();

        //发送二进制数据，文件下载
        /*
        //文件下载需要添加两个请求头
        response.setContentType("application/x-msdownload");
        //filename是服务器建议客户端浏览器保存的文件名，客户端可以更改实际保存的文件名
        response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("fileName", "UTF-8"));
        response.getOutputStream();
        */

        //重定向
        //response.sendRedirect("/" + servletPath);//斜杠代表 当前域名
    }

    //在当前Servlet所在WEB应用被卸载时调用，只会被调用一次
    public void destroy() {
        System.out.println("destroy");
    }
}

public class HelloHttpServlet extends HttpServlet {
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter writer = response.getWriter();
        writer.println("hello world!");
        writer.flush();
        writer.close();
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter writer = response.getWriter();
        writer.println("hello world!");
        writer.flush();
        writer.close();
    }
}

//获取所有Cookie（Cookie无法获取单个，只能获取全部，然后遍历出想要的Cookie）
Cookie[] cookies = request.getCookies();
if(cookies != null && cookies.length > 0){
    for(Cookie cookie : cookies){
        System.out.println(cookie.getName + "---" + cookie.getValue() + "---" + cookie.getMaxAge());
    }
}

//创建
Cookie cookie = new Cookie("name", "小明");
//默认cookie是会话级别的，它存储在浏览器内存中，当浏览器退出后会被删除；要让cookie存储在磁盘上，需设置maxAge（过期时间，单位为秒）
cookie.setMaxAge(60 * 60);
//会将cookie放在Set-Cookie的HTTP响应报头中，但这个操作不会修改之前的cookie，而是添加新的报头（一个HTTP报头可以有多个Set-Cookie），所以不叫setCookie，而是叫addCookie
response.addCookie(cookie);

//负数表示不在磁盘中存储该cookie，而是保存在浏览器内存中
cookie.setMaxAge(-1);
//删除，将过期时间设置为0就是删除
cookie.setMaxAge(0);

//是否仅通过加密连接（比如SSL）传输
cookie.setSecure(false);

//当前jsp设置的cookie只能在本路径及子路径下的jsp获取，如果父路径的jsp需要获取子路径jsp设置的cookie，需要setPath为同一个
//cookie.setPath("/");//这里的"/"是指当前web应用的根路径
//cookie.setPath(request.getContextPath());//一个站点的cookie需要共享可以使用站点的根路径，也就是项目的名字
cookie.setPath("/webapp_1");//手动指定path

//解决跨域共享cookie的问题；setPath只能解决同一站点的web应用之间的cookie共享问题，但如果网站域名不同（包括子域不同），则需要setDomain为同一个
cookie.setDomain("mywebsite.com");

//获取session，如果有JSESSIONID，则获取之前创建的session，否则新建一个HttpSession对象
HttpSession session = request.getSession(); //相当于request.getSession(true);
//如果有JSESSIONID，则获取之前创建的session；如果没有，则不创建，返回null
//HttpSession session = request.getSession(false);

Date createTime = new Date(session.getCreationTime());
//获取上次访问这个站点的时间
Date lastAccessTime = new Date(session.getLastAccessedTime());

if (session.isNew()) {
    title = "Welcome to my website";
    session.setAttribute("name", "游客");
    //设置过期时间，单位为秒（如果在指定的时间内没有访问则删除session，每次访问都重新计时）
    session.setMaxInactiveInterval(15 * 60);
}

String sessionId = session.getId();
String name = session.getAttribute("name");
String maxInactiveInterval = session.getMaxInactiveInterval();

//删除一个指定的属性
session.removeAttribute("name");
//删除整个session
session.invalidate();

<session-config>
    <!-- 单位为分钟 -->
    <session-timeout>15</session-timeout>
</session-config>

<%@ page language="java contextType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page session="false" %>
<html>
    <head><title>JSP页面</title></head>
    <body>
        <%
            //获取当前站点的其他页面创建过的session，如果之前没有创建过（比如第一次访问该站点），则返回null
            //HttpSession session = request.getSession(false);

            //如果没有创建过session，则新建一个HttpSession
            HttpSession session = request.getSession(true);
            out.println(session.getId());
        %>
    </body>
</html>

<a href="<%=response.encodeURL("index.jsp") %>" >index</a>
<a href="<%=response.encodeRedirectURL("index.jsp") %>" >redirect to index</a>

//转发
request.getRequestDispatcher("/b.jsp").forward(request, response);//斜杠代表 域名+web应用的根路径
//重定向
response.sendRedirect("/b.jsp");//斜杠代表 当前域名

public class MyFilter implements Filter  {

    //在加载WEB应用时调用，只会被调用一次
    public void init(FilterConfig config) throws ServletException {
        //获取init-param
        System.out.println(config.getInitParameter("param"));
    }

    //每次拦截都会调用该方法
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        //拦截请求

        //放行（执行后续的Filter，如果后面没有Filter，则执行Servlet对应方法）
        chain.doFilter(request,response);

        //拦截响应
    }

    //在销毁前调用，只会被调用一次
    public void destroy(){  }
}

<filter>
    <filter-name>MyFilter</filter-name>
    <filter-class>webapp.filters.MyFilter</filter-class>
    <init-param>
        <param-name>param</param-name>
        <param-value>paramValue</param-value>
    </init-param>
</filter>

<!-- 如果有多个过滤器，按照filter-mapping定义的先后顺序进行拦截 -->
<filter-mapping>
    <filter-name>MyFilter</filter-name>
    <url-pattern>/*</url-pattern>
    <!-- 可以指定通过何种方式访问时执行拦截
         可选值有REQUEST、FORWARD（包括Java或JSP代码的forward、jsp标签的forward、通过JSP的page指令配置的errorPage）、INCLUDE、ERROR（ERROR只有在web.xml中配置的error-page才会触发，而通过JSP的page指令配置的errorPage不会触发）
         默认只有REQUEST才拦截，可以配置多个dispatcher来拦截不同请求方式 -->
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
</filter-mapping>

public class MyServletContextListener implements ServletContextListener{

    public void contextInitialized(ServletContextEvent sce){
        //获取context-param
        System.out.println(sce.getServletContext().getInitParameter("contextParam"));
    }

    public void contextDestroyed(ServletContextEvent sce){  }
}

<listener>
    <listener-class>webapp.listeners.MyServletContextListener</listener-class>
</listener>

<context-param>
    <param-name>contextParam</param-name>
    <param-value>contextValue</param-value>
</context-param>

<%-- JSP指令有
    page: 定义页面依赖属性，例如脚本语言，错误页面和缓冲的要求
    include: 静态引入，将其他文件的内容合并到当前文件，这种合并是源码级别的合并，被引入的页面可以使用引入的页面定义的变量，引入的无论是何种后缀，最后都会按照JSP解析（相当于先把源码复制过来，再解析）
    taglib: 引入标签库 --%>
<%@ page language="java contextType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="java.util.*" %>

<%-- 指定错误页面，出错会转发到指定页面 --%>
<%@ page errorPage="/WEB-INF/error.jsp" %>

<%-- JSP声明，可以用于声明变量或方法 --%>
<%-- 前面提到，JSP是一个Servlet，JSP解析器会把JSP脚本片段或JSP表达式放到service方法中，而如果想定义函数或全局变量，就要使用JSP声明 --%>
<%! int day = 3; %>
<html>
    <head><title>JSP页面</title></head>
    <body>
        <%-- 使用include指令导入网页头部的模板，静态引入header.jsp不会生成header.jsp.java --%>
        <%@ include file = "header.jsp" %>
        <%-- 使用jsp标签进行动态引入，这时header.jsp会生成header.jsp.java，且header.jsp无法使用当前jsp定义的变量（相当于先解析，再放到这里输出） --%>>
        <jsp:include page="header.jsp"></jsp:include>

        <!-- html注释 -->
        <%-- JSP注释，和html注释的不同之处在于JSP注释不会被输出到网页源码中，而且JSP脚本只能用JSP注释来注释 --%>

        <%-- JSP脚本 --%>
        <%
            Date date = new Date();
            out.println(date);
        %>
        <br />

        <%-- JSP表达式（不用写分号） --%>
        <p>今天是:<%=(new java.util.Date()).toLocaleString()%></p>
        <br />

        <%-- JSP混合HTML代码，使用if-else（同理，可以写switch-case、while、for） --%>
        <% if (day == 1 | day == 7) { %>
              <p> Today is weekend</p>
        <% } else { %>
              <p> Today is not weekend</p>
        <% } %>
        <br />

        <%-- 定义和使用方法（方法的定义要使用JSP声明，以"<%!"开头） --%>
        <%!
        public int mul(int a, int b){
            return a * b;
        }
        %>
        <%= mul(2, 2) %>
        <br />

        <%-- 使用Session和Cookie --%>
        <%
        String name = request.getParameter("name");
        //以下四个方法四大域对象都有
        session.setAttribute("username",name);
        session.getAttribute("username");
        session.removeAttribute("username");
        Enumeration<String> names = session.getAttributeNames();
        %>
        <%
        Cookie[] cookies = request.getCookies();
        for (int i=0; i<cookies.length; i++){
            out.println(cookies[i].getValue());
        }

        String name=request.getParameter("name");
        Cookie cookie = new Cookie("name",name);
        response.addCookie(cookie);
        cookie.setMaxAge(50 * 50);//单位为秒（删除Cookie：cookie.setMaxAge(0)）
        %>

        <%-- 转发请求与重定向
        <%
        //request.getRequestDispatcher("/b.jsp").forward(request, response);
        response.sendRedirect("b.jsp");
        %>
        --%>

    </body>
</html>

<%-- 动态引入（区别于`<%@ include file="xxx"%>`） --%>
<jsp:include page="/index.jsp"></jsp:include>

<%-- 转发请求，可以使用jsp:param传递参数，另一页面可以通过request.getParameter获取 --%>
<jsp:forward page="/index.jsp">
    <jsp:param name="username" value="user" />
</jsp:forward>

<%-- 使用JavaBean，并设置、获取属性，name是指JavaBean的id --%>
<jsp:useBean id="user" class="webapp.pojo.User" scope="request" />
<jsp:setProperty name="user" property="username" value="小明" />
<jsp:getProperty name="user" property="username" />

<%-- 使用*可以自动为所有属性赋值为请求参数的值 --%>
<%-- http://localhost:8080/myproject/a.jsp?id=1&name=xiaoming&age=14 --%>
<jsp:setProperty name="user" property="*" />

<%-- JSP的写法，如果username为null，会输出"null"字符串，为了避免这种情况，需要手动处理 --%>
<%=request.getParameter("username") == null ? "":request.getParameter("username") %>
<%-- EL的写法，如果username为null，则输出空字符串"" --%>
${param.username}

<%-- 直接访问对象的属性 --%>
${sessionScope.user.name}
<%-- 或者 --%>
${sessionScope.user["name"]}

<%-- 特殊情况需要用中括号 --%>
<%
    User user = new User(1,"小明",14);
    session.setAttribute("webapp.pojo.user", user);
%>
${sessionScope["webapp.pojo.user"].name}

<%-- 如果不指定scope，会自动按照作用范围从小到大寻找page、request、session、application中的内容 --%>
${user.name}

<%-- EL表达式可以自动类型转换 --%>
${param.age + 10}
<!-- 复杂数据类型也可以自动转换 -->
<% application.setAttribute("time", new Date()); %>
${applicationScope.time.time}

<%-- empty运算符 --%>
<%
    List<String> names = new ArrayList<String>();
    request.setAttribute("names", names);
%>
<!-- 输出true，属性不存在或内容为空都会输出true -->
${empty requestScope.names}

<%-- EL表达式使用函数需要导入标签库 --%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
${fn:length(param.name) }

<%@ page language="java contextType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="java.util.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<html>
    <head><title>JSP页面</title></head>
    <body>

        <%-- scope可以指定为application、session、request、page --%>
        <c:set var="sesssionValue" value="<<java>>" scope="session" />
        <%-- 给JavaBean的属性赋值 --%>
        <% User user = new User(); %>
        <c:set target=${requestScope.user} property="id" value="1" />

        <%-- 如果sesssionValue含有特殊字符，可以自动转义（比如尖括号） --%>
        <c:out value="${sesssionValue}" default="null" escapeXml="true" />

        <c:remove var="sesssionValue" scope="session" />

        <%-- 条件判断 --%>
        <c:if test="${param.age > 18}" var="isAdult" scope="request"></c:if>
        <c:out value="${requestScope.isAdult}"></c:out>

        <c:choose>
            <c:when test="${param.age > 18}">成年</c:when>
            <c:when test="${param.age >= 0}">未成年</c:when>
            <c:otherwise>参数不合法</c:otherwise>
        </c:choose>

        <%-- 循环 --%>
        <c:forEach begin="0" end="10" step="2" var="i">
            ${i}
        </c:forEach>

        <%-- 遍历Collection或数组 --%>
        <%
            List<User> users1 = new ArrayList<User>();
            list.add(new User(0, 小明", 14));
            list.add(new User(1, 小红", 13));
            list.add(new User(2, 小王", 12));
            request.setAttribute("users1", users1);
        %>
        <c:forEach var="user" items="${requestScope.users1}" varStatus="status">
            ${status.index} -- ${user.id} -- ${user.name} -- ${user.age}
        </c:forEach>

        <%-- 遍历Map --%>
        <%
            Map<String, User> users2 = new HashMap<String, User>();
            users2.put("a", new User(0, 小明", 14));
            users2.put("b", new User(1, 小红", 13));
            users2.put("c", new User(2, 小王", 12));
            request.setAttribute("users2", users2);
        %>
        <c:forEach var="user" items="${requestScope.users2}">
            ${user.key} -- ${user.value.id} -- ${user.value.name} -- ${user.value.age}
        </c:forEach>

        <%-- JSTL或JSP标签中，无论转发还是重定向，斜杠都表示 当前域名+WEB应用的根路径 --%>
        <%-- 重定向 <c:redirect url="/b.jsp" /> --%>
        <%-- 转发可以使用 <jsp:forward page="/b.jsp"> --%>

        <%-- c:url生成的URL会自动加上WEB应用的根路径，且会在URL后面加上JSESSIONID，自动进行URL重写，并对GET参数进行转码 --%>
        <%-- c:url可以存储在指定的域中 --%>
        <c:url value="/b.jsp" var="testurl" scope="page">
            <c:param name="username" value="小明" />
        </c:url>
        ${testurl}

        <%-- 使用mysql的jstl --%>
        <sql:setDataSource var="connection" driver="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost/test?useSSL=false&characterEncoding=utf8" user="user" password="root" />

        <sql:update dataSource="${connection}" var="count">
            INSERT INTO user(`id`,`name`,`age`) VALUES (null,'小明', 14);
        </sql:update>

        <c:set var = "userId" value = "1"/>
        <sql:update dataSource="${connection}" var="count">
            UPDATE user SET name='小红' WHERE id=?
            <sql:param value = "${userId}" />
        </sql:update>

        <sql:update dataSource="${connection}" var="count">
            DELETE FROM user WHERE id=1;
        </sql:update>

        <sql:query dataSource="${connection}" var="result">
            SELECT * FROM user;
        </sql:query>
        <table border="1" width="100%">
            <tr>
                <th>id</th>
                <th>name</th>
                <th>age</th>
            </tr>
            <c:forEach var="row" items="${result.rows}">
                <tr>
                    <td><c:out value="${row.id}" /></td>
                    <td><c:out value="${row.name}" /></td>
                    <td><c:out value="${row.age}" /></td>
                </tr>
            </c:forEach>
        </table>

    </body>
</html>

//简单标签
public class MyIPTag extends SimpleTagSupport {
    PageContext pageContext;

    @Override
    public void doTag() throws JspException, IOException {
        HttpServletRequest request = (HttpServletRequest)pageContext.getRequest();
        JspWriter out = getJspContext().getOut();

        String ip = request.getRemoteAddr();
        out.write(ip);
    }

    @Override
    public void setJspContext(JspContext jspContext) {        
        this.pageContext = (PageContext)jspContext;  
    }
}

//有标签体和标签属性的复杂标签
public class MyComplexTag extends SimpleTagSupport {
    public String attr1;

    public void doTag() throws JspException, IOException {
        //获取标签体（如果有EL表达式或JSP动作元素(即JSP命名空间下的标签)会自动执行），并输出
        JspFragment jspFragment = this.getJspBody();

        //invoke(null)会直接输出到页面上
        //jspFragment.invoke(null);

        //要更改输出的内容可以invoke到指定的writer中
        StringWriter sw = new StringWriter();
        jspFragment.invoke(sw);
        getJspContext().getOut().println(sw.toString().toUpperCase());

        //输出属性
        if(attr1 != null){
            getJspContext().getOut().println(attr1);
        }

        //标签嵌套时获取父标签属性并输出
        //父标签无法获取子标签，但子标签能获取父标签，因为如果没有掉invoke，子标签就不会被初始化，解析器在解析时无法得知是否需要加载子标签
        JspTag parent = getParent();
        if(parent instanceof MyComplexTag){
            MyComplexTag parentTag = (MyComplexTag)parent;
            getJspContext().getOut().println(parentTag.attr1);
        }
    }

    //标签的属性会自动通过set方法设置
    public void setAttr1(String attr1) {
        this.attr1 = attr1;
    }
}

<?xml version="1.0" encoding="UTF-8" ?>  
<taglib xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-jsptaglibrary_2_0.xsd"
    version="2.0">

    <display-name>My Tag</display-name>
    <description>This is my Tag</description>
    <tlib-version>1.0</tlib-version>
    <jsp-version>2.0</jsp-version>

    <!-- 建议在jsp中使用的前缀（prefix） -->
    <short-name>my</short-name>
    <!-- 用于唯一标识当前的TLD文件，该地址不需要真实存在，多个TLD文件的uri不能重复 -->
    <uri>http://mywebsite.com/mytag/my</uri>

    <tag>
        <!-- 标签名字 -->
        <name>ip</name>
        <!-- 标签对应的class -->
        <tag-class>webapp.jstl.MyIPTag</tag-class>
        <!-- 标签类型，empty就是没有标签体的简单标签 -->
        <body-content>empty</body-content>
    </tag>

    <!-- 有标签体及标签属性的标签 -->
    <tag>
        <name>complex</name>
        <tag-class>webapp.jstl.MyComplexTag</tag-class>
        <!-- scriptless表示标签体可以包含EL表达式或JSP动作元素，但不能包含JSP脚本（包括JSP表达式）
             除了empty、scriptless外，还可以指定为tagdependent，表示标签体交由我们自己解析，标签体中的所有内容会原封不动地交给我们的标签处理器 -->
        <body-content>scriptless</body-content>
        <attribute>
            <name>attr1</name>
            <!-- 以下参数是可选的 -->
            <required>false</required>
            <type>java.lang.String</type>
            <!-- 能否使用JSP脚本或EL表达式 -->
            <rtexprvalue>true</rtexprvalue>
            <!-- 是否将该属性视为JspFragment -->
            <fragment>false</fragment>
        </attribute>
    </tag>
</taglib>

<%@ page language="java contextType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="my" uri="http://mywebsite.com/mytag/my"%>
<html>
    <head><title>mytag</title></head>
    <body>
        我的IP是：<my:ip/>
        <br />
        <my:complex attr1="属性" >标签体</my:complex>

        <my:complex attr1="parent" >
            <my:complex attr1="child" >
                child content
            </my:complex>
        </my:complex>
    </body>
</html>

public class MyELFunction{

    public static String concat(String str1, String str2){
        return str1 + str2;
    }
}

<?xml version="1.0" encoding="UTF-8" ?>  
<taglib xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-jsptaglibrary_2_0.xsd"
    version="2.0">

    <display-name>My EL Function</display-name>
    <description>This is my EL Function</description>
    <tlib-version>1.0</tlib-version>
    <jsp-version>2.0</jsp-version>
    
    <short-name>myfunc</short-name>
    <uri>http://mywebsite.com/mytag/myfunc</uri>

    <function>
        <name>concat</name>
        <function-class>webapp.jstl.MyELFunction</function-class>
        <function-signature>java.lang.String concat(java.lang.String, java.lang.String)</function-signature>
    </function>
</taglib>

<%@ page language="java contextType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="myfunc" uri="http://mywebsite.com/mytag/myfunc"%>

${myfunc:concat(param.name1, param.name2)}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="userDao" class="dao.repository.impl.UserDaoImpl"/>
</beans>

<bean id="userDao" class="dao.repository.impl.UserDaoImpl" init-method="init" destroy-method="destory" lazy-init="true" scope="prototype"/>

public class UserFactory {
    public UserDaoImpl createUser(){
        return new UserDaoImpl();
    }
}

<!-- 先创建工厂的bean，然后再使用其工厂方法 -->
<bean id="userFactory" class="Utils.UserFactory" />
<bean id="userDao" factory-bean="userFactory" factory-method="createUser" />

public class UserFactory {
    static public UserDaoImpl createUser(){
        return new UserDaoImpl();
    }
}

<!-- 静态的工厂方法无需创建工厂的bean，直接使用工厂方法即可 -->
<bean id="userDao" class="Utils.UserFactory" factory-method="createUser" />

public UserDaoImpl(String arg1,List arg2,Map arg3) {
}

<bean id="userDao" class="dao.repository.impl.UserDaoImpl">
    <constructor-arg index="0" value="hello"/>
    <constructor-arg index="1">
        <!-- list和array可以不区分，只要数据结构相同，标签可以互换 -->
        <list>
            <value>1</value>
            <value>2</value>
        </list>
    </constructor-arg>
    <constructor-arg index="2">
        <!-- map和prop也可以互换 -->
        <map>
            <entry key="1" value="hello"/>
            <entry key="2" value="world"/>
        </map>
    </constructor-arg>
</bean>

User arg1;
public void setArg1(User arg1) {
    this.arg1 = arg1;
}

<bean id="user" class="dao.pojo.User"/>
<bean id="userDao" class="dao.repository.impl.UserDaoImpl">
    <!-- 复杂数据类型使用ref -->
    <property name="arg1" ref="user"/>
</bean>

//classpath:就是指src目录下，可以省略
ApplicationContext ap = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
System.out.println("applicationContext loaded");
IUserDao userDao1 = (IUserDao) ap.getBean("userDao");
System.out.println(userDao1.findUserById(1));