本帖最后由 小石姐姐 于 2018-1-12 11:07 编辑
每日总结
# day01 MySQL SQL语句
* 数据库
* 概念: 存储数据的仓库. 本质是一个文件系统, 数据按照指定的格式将数据存储起来, 用户可以对数据库中的数据进行增删改查
* DBMS: Database Management System, 数据库管理系统. 是操作和管理数据库的大型软件, 用于建立, 使用和维护数据库, 对数据库进行统一管理和控制, 以保证数据库的安全性和完整性. 用户通过数据库管理系统访问数据库中表内的数据
* 常见数据库
* `MySQL`
* `Oracle`
* 数据库: 存储数据表
* 数据表: 存储记录
* 由行和列组成的
* DBMS: 操作数据库
* 表和Java类的对应关系
* 表名: Java类名
* 列名: 成员属性名
* 记录: 类对象
* MySQL
* 安装
* 配置和遇到的问题
* 命令行的登录和退出
* 登录: `mysql -u 用户名 -p`
* 退出: `exit`
* SQL基本语言
* SQL的概念: Structured Query Language, 结构化查询语言
* 结构: 数据表是由行和列组成的, 是有结构的, 从这种结构中查询
* SQL的4中分类
* DDL: Data Defination Language, 数据定义语言, 用来定义数据库对象(库, 表等)
* CREATE
* ALTER
* DROP
* DML: Data Manipulation Language, 数据操作语言, 对表中的记录进行增, 删, 改
* INSERT
* UPDATE
* DELETE
* DQL: Data Query Language, 数据查询语言, 对表中的记录进行查
* SELECT
* FROM
* WHERE
* DCL: Data Control Language, 数据控制语言, 创建修改用户, 权限
* SQL特点
* SQL不区分大小写
* 一条SQL语句以`;`分号结尾, 一条语句可以多行书写
* 单词之间通过空格分隔, 参数之间通过`,`逗号分隔
* 字符串和日期要用`''`或`""`引起来, 建议单引号
* SQL注释(2+1种)
* 2种单行注释
* `#单行注释`
* `-- 单行注释`: 减号后必须有一个空格
* 1种多行注释: `/* 注释内容 */`
* SQL的数据类型
* `INT`: 整数
* `DOUBLE`: 小数
* `VARCHAR`: 可变长度字符
* `CHAR`: 固定长度字符
* 其他看文档
* VARCHAR和CHAR的区别:
* VARCHAR(200), 可变长度字符, 存储占用的内存空间是可变的, 如果你的数据容量小于我们指定的空间大小, 那么就会按照实际的数据空间来开辟内存大小
* 优点: 节省内存
* 缺点: 因为每次存入数据时都会判断数据的实际大小, 来动态修改内存空间, 所以需要占用资源, 效率低
* CHAR(200), 固定长度字符, 存储占用的内存空间是不可变的. 无论实际存入的数据容量是多大, 都占用这么大的空间
* 优点: 插入数据时不需要额外操作, 效率高
* 缺点: 可能会浪费空间
* SQL对库的操作
* 创建库
* 使用默认字符集创建库: `CREATE DATABASE 库名;`
* 默认是UTF-8编码
* 指定字符集: `CREATE DATABASE 库名 CHARACTER SET '字符集';`
* 删除库
* `DROP DATABASE 库名;`
* 查看所有库
* `SHOW DATABASES;`
* 使用库
* `USE 库名;`
* 查看当前使用的库
* `SELECT DATABASE();`
* SQL对表的操作
* 创建表: `CREATE TABLE 表名 (列名1 数据类型 约束, 列名2 数据类型 约束);`
* 删除表: `DROP TABLE 表名;`
* 查看当前库中的所有表: `SHOW TABLES;`
* 修改表的结构: `ALTER TABLE 表名 操作关键字 列名 数据类型 约束;`
* 增加列: `ALTER TABLE 表名 ADD 新列名 新数据类型 新约束;`
* 删除列: `ALTER TABLE 表名 DROP 列名;`
* 修改列的数据类型和约束(不能修改列名): `ALTER TABLE 表名 MODIFY 列名 新数据类型 新约束;`
* 修改列的列名, 数据类型, 约束: `ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 新约束;`
* 修改表名: `RENAME TABLE 旧表名 TO 新表名;`
* SQL对记录的操作
* 增
* 一次插入一条记录: `INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);`
* 一次性插入一条记录(省略自增主键): `INSERT INTO 表名 (非主键自增列1, 非主键自增列2) VALUES (值1, 值2);`
* 一次插入所有列的记录(省略列名): `INSERT INTO 表名 VALUES (值1, 值2);`
* 值的数量和顺序必须和表的列一致
* 一次插入多个记录(指定列): `INSERT INTO 表名 (列1, 列2) VALUES (记录1值1, 记录1值2), (记录2值1, 记录2值2);`
* 一次插入多个记录(省略列): `INSERT INTO 表名 VALUES (记录1值1, 记录1值2), (记录2值1, 记录2值2);`
* 删
* 删除符合条件的记录: `DELETE FROM 表名 WHERE 条件;`
* 删除所有记录: `DELETE FROM 表名;`
* 清空表: `TRUNCATE TABLE 表名;`
* `DELETE FROM 表名;`和`TRUNCATE TABLE 表名;`的区别
* DELETE, 逐条删除记录, 不会重置自增计数器
* TRUNCATE, 删除表再重建, 会重置自增计数器
* 改
* 修改符合条件的记录: `UPDATE 表名 SET 列名1=新值, 列名2=新值 WHERE 条件;`
* 修改所有记录: `UPDATE 表名 SET 列名1=新值, 列名2=新值;`
* 查
* 查询指定列: `SELECT 列名1, 列名2 FROM 表名;`
* 查询所有列: `SELECT * FROM 表名;`
* 条件查询: `SELECT 列名1, 列名2 FROM 表名 WHERE 条件;`
* 运算符
* 比较运算符
* `=`: 相等
* `<>`或`!=`: 不等
* `>`: 大于
* `<`: 小于
* `>=`: 大于等于
* `<=`: 小于等于
* `BETWEEN...AND...`: 在一个范围内(包含头和尾)
* 如: BETWEEN 0 AND 10
* `IN ()`: 在列表中, 满足列表中一个即可
* 如: IN (1, 3, 5)
* `IS NULL`: 是空
* `LIKE '通配符'`: 模糊查询
* `%`: 一个百分号可以表示任意个字符. 比如'王%', 王大锤, 王五
* `_`: 一个下划线可以表示一个字符. 比如'王_', 只能匹配王五
* 逻辑运算符
* `AND`: 与. 两边条件同时成立才成立
* `OR`: 或. 两边条件只要有一个成立就成立
* `NOT`: 非, 取相反结果
* `NOT BETWEEN ... AND ...`: 不在范围内
* `NOT IN`: 不在列表中
* `NOT LIKE`: 不匹配
* `IS NOT NULL`: 非空
* 去重: `SELECT DICTINCT 列名1, 列名2 ... FROM 表名 WHERE 条件;`
* 别名:
* `字段名 AS 别名`
* `表名 AS 别名`
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
day02 高级SQL, JDBC, SQL注入
## 排序查询
* 排序查询
* 是`SELECT`的子句, 将查询后的结果集进行排序
* 格式:
* `ORDER BY 列名 排序选项`
* `SELECT ... FROM ... WHERE ... ORDER BY 列名 排序选项`
* 排序选项: 适用于数字, 英文字母, 日期
* `ASC`: 默认, 升序
* `DESC`: 降序
* 注意: `WHERE`和`ORDER BY`的顺序
* `WHERE`在前, `ORDER BY`在后, 否则报错
* 因为排序是对SELECT查询出的 结果集 再次进行排序, 而不是对表中的数据进行排序. 所以要先把SELECT WHERE的查询结果拿到, 才能使用ORDER BY排序
* 扩展:
* 排序可以按照多列, 如先按a列降序, 如果a列中有相同的值, 则按b列降序
* `SELECT * FROM users ORDER BY age DESC, salary DESC;`
* `ORDER BY`是SQL语句的末尾吗?
* 不一定是SQL语句的末尾, 后面还可以写LIMIT子句
*
-----------------------------------------------------------------------------------------------------------------------------------------------------------
## 聚合函数
* 聚合函数
* SQL内置了一些函数(类似于API方法), 可以对查询的结果进行相关计算
* 作用:
* 竖向对某一列的值进行计算, 然后返回一个计算结果.
* 格式:
* `SELECT 函数名(列名) FROM 表名 ...;`
* 常用的5个聚合函数
* `COUNT(列名)`: 计算指定列的记录行数.
* 注意: 值为NULL的记录不会被统计
* `SUM(列名)`: 计算指定列的数值总和
* 如果数据类型不是数值, 则结果为: 0
* `MAX(列名)`: 获取指定列的数值中的最大值
* `MIN(列名)`: 获取指定列的数值中的最小值
* `AVG(列名)`: 计算指定列的数值中的平均值
* 如果数据类型不是数值, 则结果为: 0
* 注意:
* 聚合函数的函数名和小括号之间要连着写, 不要有空格:
* 正确: `SUM(zname)`
* 错误: `SUM (zname)`
* NULL与任何数的运算结果都是NULL: `SELECT (NULL + 10)`结果为NULL
*AS ‘起别名’
-----------------------------------------------------------------------------------------------------------------------------------------------------------
## 分组查询
* 分组查询
* 是`SELECT`的子句, 用于将 查询出的结果集 再次按照某列分组合并显示, 记录值相同的归为一组. 同时还可以对 分组后的结果 再次使用条件进行过滤
* 格式:
* `GROUP BY 列名 HAVING 条件`,
* `SELECT ... FROM ... WHERE ... GROUP BY 列名 HAVING 条件;
* `HAVING`的作用: 可选, 对查询结果进行分组显示后, 再次按条件过滤
* 注意:
* 聚合函数与分组的使用效果
* 使用聚合函数后再进行分组, 才相当于汇总统计
* `SELECT zname, SUM(zmoney) FROM zhangwu GROUP BY zname;`
* 不使用聚合函数的分组, 只有第一条记录的值
* `SELECT zname, zmoney FROM zhangwu GROUP BY zname;`
* `HAVING`和`WHERE`的区别：
* 过滤时机，不同
* WHERE是在分组前先对查询结果进行过滤
* HAVING是在查询结果分组后再次过滤
* 是否可跟随聚合函数，不同
* WHERE条件中不可使用聚合函数
* HAVING条件中可以使用聚合函数
* 条件可用列名，不同
* HAVING条件中的列名必须是SELECT或GROUP BY中使用过的列名
* WHERE条件中可以使用表的所有列名
```sql
/* 最终一个完整的SELECT语句格式 */
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... DESC;
---------------------------------------------------------------------------------------------------------------------------------------------------------
## JDBC概念和数据库驱动程序
* JDBC:
* Java DataBase Connectivity, Java数据库连接. 是一种技术的名称
* 是SUN公司提供的用于执行SQL语句的Java API, 用为多种关系型数据库提供统一的访问. 它由一组用Java语言编写的类和接口组成, 是Java访问数据库的标准规范
* 数据库驱动:
* 两个不同的设备要通信, 需要满足一定的通信数据格式, 数据格式由设备提供商规定, 设备提供商为设备提供驱动软件, 通过驱动软件就可以让两个设备通信.
* Java和MySQL数据库就可以看作2个不同的设备, JDBC需要使用数据库驱动来让Java代码与数据库服务器通信, 将Java中的SQL字符串发送给数据库来执行
* 数据库驱动有多种, 用于不同开发语言, 对于Java也有专门的一个驱动
* Java用于MySQL的驱动从哪里得到: [MySQL官网](https://dev.mysql.com/downloads/connector/j/)
## JDBC原理
* JDBC和数据库驱动的关系
* JDBC是一套API, 可以通过Java代码来使用JDBC, 同时提供了一套接口, 用于让数据库厂商根据自家数据库特点去实现JDBC的功能
* 数据库驱动是数据库厂商根据自家数据库特点去实现JDBC接口而制作的, 用于让JDBC能够调用数据库的相关功能
* Java程序通过使用JDBC的API, 通过多态的方式调用数据库驱动中实现类的方法, 对数据库进行操作
```
+----------+
| Java程序 |
+-----+----+
|
+-----+----+
| JDBC |
| 接口 |
+-----+----+
|
+---------+---------+
| |
+------+------+ +------+------+
| MySQL驱动 | | Oracle驱动 |
| 实现类 | | 实现类 |
+------+------+ +------+------+
| |
+------+------+ +------+------+
| MySQL数据库 | | Oracle数据库 |
+-------------+ +-------------+
|
|
收藏
淘帖
踩
