本文介绍了数据库的基本概念，重点讲解了MySQL的注释规则、SQL语言的三大范式（DDL、DML、DQL），包括数据类型、约束、数据库和表的操作以及查询语言。还涉及到了多表设计中的关联查询、子查询、JDBC以及PreparedStatement与Statement的区别，是学习数据库管理与编程的基础资料。
目录
11.PreparedStatement和Statement的区别
1.前言
在java代码运行时，数据都是在内存中临时存储的，一旦程序停止、关机，数据就消失了，并不能持久化保存。在我们学习完IO后，我们可以通过序列化把数据存储在文件里，可达到持久化的效果，但是再通过反序列化读取起来很不方便，所以我们就需要有一款工具来持久的存储数据和管理数据！
2.数据库概述
数据库就是为了方便数据的存储和管理，将数据按照特定的规则存储到一个仓库中(在硬盘中存储)，利用数据库管理系统就可以有效地管理数据。而mysql是一款关系型数据库。
3.mysql的注释规则
| 单行注释 | #注释文字 |
| 单行注释 | -- 注释文字(后面必须要有空格) |
| 多行注释 | /* 注释文字 */ |
4.sql概述
结构化查询语言简称sql，是一种独立的编程语言，用于增加、删除、修改、查询数据库中数据表里的数据。
5.数据类型
| int | 表示整数 |
| char(n) | 长度为n的定长字符串 |
| varchar(n) | 最大长度为n的可变字符串 |
| date | 日期 yyyy-MM-dd |
| datetime | 时间 yyyy-MM-dd HH:mm:ss |
| float(M,D) | M：精度，数据的总长度 D：标度，小数点后的长度 |
| double(M,D) | 浮点型(建议使用double) |
6.约束
| PRIMARY KEY | 主键 |
| NOT NULL | 不能为空约束 |
| UNIQUE | 唯一性约束 |
| CHECK(height<3) | 检查约束 |
| DEFAULT '男' | 默认值 |
| COMMENT '性别' | 字段注释 |
5.DDL(用于创建和修改数据库和表)
5.1对数据库的操作语句
| sql语句 | 语句作用 |
|---|---|
| CREATE DATABASE [if not exists] 数据库名 [ CHARSETutf8] | 创建数据库 |
| DROP DATABASE 数据库名 / [IF EXISTS数据库名] | 删除数据库 |
| ALTER DATABASE 数据库名 CHARSET gbk | 修改数据库的编码 |
5.2对数据表的操作语句
| sql语句 | 语句作用 |
|---|---|
| CREATE TABLE 表名(列名 数据类型 [约束] [默认值] [ 注释],......) | 创建表 |
| DROP TABLE [if exists ]表名 | 删除表 |
| RENAME TABLE 旧表名 TO 新表名 | 修改表名 |
| CREATE TABLE 新表名 LIKE 被复制表名 | 复制表的结构，只有结构，无数据 |
| TRUNCATE TABLE 表名 | 清空表，归位计数器，效率高 |
| ALTER TABLE 表名 ADD COLUMN 列名 列数据类型 | 给表中添加列 |
6.DML(对表中数据操纵的语言 增、删、改)
6.1insert
| sql语句 | 语句作用 |
|---|---|
| INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n) | 选择字段赋值插入 |
| INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n); | 多条插入 |
| INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配) | 将查询的数据插入到其他表 |
6.2update
| sql语句 | 语句作用 |
|---|---|
| UPDATE 表名 SET 列名 = ‘新值’WHERE 条件 | 修改数据 |
6.3delete
| sql语句 | 语句作用 |
|---|---|
| DELETE FROM 表名 WHERE 条件 | 删除数据 |
7.DQL(查询数据)
7.1查询结果处理
| sql语句 | 语句作用 |
|---|---|
| SELECT 查询列表 FROM 表名 | 查询所需列表的数据 |
| SELECT height+1 FROM student | 算术运算符用于字段为数值的，不能做拼接 |
| SELECT DISTINCT column1,column2 FROM TABLE | 去除重复数据(所有列值相同才可称为相同数据) |
| SELECT CONCAT(NAME,':',address) FROM student | 用于字符串结果的拼接 |
注意：
算数运算符:+ - * / 不能用于拼接，比如：SELECT NAME+1 FROM team，name是varchar类型的，他会先把name+1看作一个字段去查询，最后再把结果+1，所以最后显示都是1。
7.2字符函数
| sql语句 | 语句作用 |
|---|---|
| SELECT LENGTH(NAME) FROM team | 获取参数值的字节个数 |
| SELECT CHAR_LENGTH(NAME) FROM team | 获取参数值的字符个数 |
| SELECT UPPER(NAME) FROM team | 将字符串变成大写 |
| SELECT LOWER(NAME) FROM team | 将字符串变成小写 |
| SELECT SUBSTRING(NAME,1,3) FROM team | 截取字符串 位置从1开始 |
| SELECT INSTR(NAME,'火') FROM team | 返回子串第一次出现的索引，如果找不到返回0 |
| SELECT TRIM(NAME) FROM team | 去掉字符串前后的空格 |
| SELECT TRIM('箭' FROM NAME) FROM team | 去掉字符串前后的子串 |
| SELECT LPAD(NAME,10,'a') FROM team | 用指定的字符实现左填充将str填充为指定长度 |
| SELECT RPAD(NAME,10,'a') FROM team | 用指定的字符实现右填充将str填充为指定长度 |
| SELECT REPLACE(NAME,'火','箭') FROM team | 替换，替换所有的子串 |
7.3逻辑处理
| sql语句 | 语句作用 |
|---|---|
| SELECT num, NAME, (CASE WHEN NAME = '火箭' THEN '优秀' WHEN NAME = '骑士' THEN '良好' ELSE '一般' END) AS POWER FROM team | case when 条件 then 结果1 else 结果2 end; 可以有多个when |
| SELECT num, NAME, IFNULL(address,'为null') FROM team | ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值 |
| SELECT num, NAME, IF(NAME='火箭','优秀','良好') FROM team | if函数：if else的 效果 if(条件,结果1,结果2) |
7.4数字函数
| sql语句 | 语句作用 |
|---|---|
| SELECT ROUND(height) FROM team WHERE num = 3 | 四舍五入 |
| SELECT CEIL(height) FROM team WHERE num = 5 | 向上取整，返回>=该参数的最小整数 |
| SELECT FLOOR(height) FROM team WHERE num = 5 | 向下取整，返回<=该参数的最大整数 |
| SELECT TRUNCATE(1.86685,2) | 截断，小数点后截断到几位 |
| SELECT MOD(9,4) | 取余，被除数为正，则为正；被除数为负，则为负 |
| SELECT RAND() | 获取随机数，返回0-1之间的小数 |
7.5日期函数
| sql语句 | 语句作用 |
|---|---|
| SELECT NOW() | 返回当前系统日期+时间 |
| SELECT CURDATE() | 返回当前系统日期，不包含时间 |
| SELECT CURTIME() | 返回当前时间，不包含日期 |
| SELECT STR_TO_DATE('2003-8-25','%Y-%m-%d') | 将日期格式的字符转换成指定格式的日期 |
| SELECT DATE_FORMAT(DateTime,'%Y-%m-%d') | 将日期转换成相应的格式 |
| SELECT DATEDIFF(CURDATE(),'2003-11-20') | 返回两个日期相差的天数 |
7.6统计使用
| sql语句 | 语句作用 |
|---|---|
| SELECT SUM(height) FROM team | 求和 |
| SELECT AVG(height) FROM team | 求平均数 |
| SELECT COUNT(*) FROM team | 求数据条数 |
| SELECT MAX(height) FROM team | 求最大值 |
| SELECT MIN(height) FROM team | 求最小值 |
7.7条件查询(where后加条件)
| sql语句 | 语句作用 |
|---|---|
| SELECT * FROM team WHERE height BETWEEN 1.5 AND 2.0 | 两者之间,包含临界值 |
| SELECT * FROM team WHERE height IN(1.89,1.67) | 查询字段的值属于in列表中的 |
| SELECT * FROM team WHERE height NOT IN(1.89,1.67) | 查询字段的值不属于in列表中的 |
| SELECT * FROM team WHERE address IS NOT NULL | 查询字段的值不为null的 |
7.8UNION和UNION ALL
| sql语句 | 语句作用 |
|---|---|
| SELECT * FROM student WHERE SUBJECT = '语文' UNION SELECT * FROM student | 当使用union 时，mysql 会把结果集中重复的记录删掉，效率稍低。 |
| SELECT * FROM student WHERE SUBJECT = '语文' UNION ALL SELECT * FROM student | 当使用UNION ALL时，会把所有的记录返回，且效率会高一些。 |
7.9排序(ORDER BY)
| sql语句 | 语句作用 |
|---|---|
| SELECT * FROM student WHERE stu_num=1 ORDER BY stu_score ASC | 按照升序排序(默认) |
| SELECT * FROM student WHERE stu_num=1 ORDER BY stu_score DESC | 降序排序 |
7.10数量限制 (limit)
| sql语句 (sql语句最末尾位置) | 语句作用 |
|---|---|
| SELECT * FROM table LIMIT offset rows | (n-1)*每页显示的数据数，每页显示的数据数 |
| SELECT * FROM table LIMIT 0,5 | 限制查询 |
7.11分组查询
| sql语句 | 语句作用 |
|---|---|
| SELECT | 先进行条件筛选再进行分组 |
| SELECT stu_name, stu_num, SUM (stu_score) AS total FROM student GROUP BY stu_name,stu_num HAVING total > 210 | 先进行分组之后再进行筛选 |
注意：
| 数据源 | 源位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by子句的前面 | where |
| 分组后筛选 | 分组后的结果集 | group by的后面 | having |
8.多表设计——关联查询
8.1数据库范式
目前数据库有五种范式，第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。但是通常情况下我们只需要满足前三种就行了。
第一范式：确保每列保持原子性，确保列名不会再被拆分
第二范式：要有主键，要求其他字段都依赖于主键。
第三范式：要消除传递依赖，方便理解，可以看做是“消除冗余”。
8.2外键
外键：用来引用另一个数据表中的某条记录。
如果表已创建好了，想给某个从表添加外键：
ALTER TABLE 从表名 ADD FOREIGN KEY (从表字段) REFERENCES 主表名 (主表字段)
从表：
CREATE TABLE stu (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(10),
majorid INT,
CONSTRAINT 约束名 FOREIGN KEY (majorid) REFERENCES major (id)
)约束名可自定义
主表：
CREATE TABLE major(
id INT PRIMARY KEY,
NAME VARCHAR(10)
)
使用外键要注意的规则：
1.不能在从表中majorid中添加主表没有的id数据。
2.不能更改主表的id值
3.不能删除主表中已被关联的id，不能孤立从表
4.要删主表，要先把从表中所关联的数据先删除
8.3关联查询
8.3.1内关联
SELECT
s.id,
s.name,
m.name
FROM stu s INNER JOIN major m ON s.majorid = m.id
查询结果：
8.3.2左关联
SELECT
s.id,
s.name,
m.name
FROM stu s LEFT JOIN major m ON s.majorid = m.id
查询结果：
8.3.3右关联
SELECT
s.id,
s.name,
m.name
FROM stu s RIGHT JOIN major m ON s.majorid = m.id
查询结果：
8.4多对多表的设计
像我们之前的一对多，多对一表的设计时(学生和专业)，我们可以利用外键来进行连接，但是例如像学生和课程这种时，一个学生可以选择多门课程，一门课程又可以被多名学生选择时，这种关系的数据表我们应该怎么设计呢？
其实可以再创建一张学生课程关系表，这张表可以专门存放课程和学生的关系。
学生表：
课程表：
学生课程关系表：
关联查询：
SELECT
s.name,
c.name
FROM
stu s
LEFT JOIN stu_cour sc
ON s.id = sc.stuid
LEFT JOIN course c
ON sc.courid = c.id
查询的结果：
group_concat()优化：
SELECT
s.id,
s.name,
GROUP_CONCAT(c.name)
FROM
stu s
LEFT JOIN stu_cour sc
ON s.id = sc.stuid
LEFT JOIN course c
ON sc.courid = c.id
GROUP BY s.id,s.name
9.子查询
含义：出现在其他语句中的select语句，称为子查询或内查询；
例如：查找员工中工资最多的，我们的思路是先查询出最高工资，最后匹配员工。
SELECT
*
FROM
stuff
WHERE money =
(SELECT
MAX (money)
FROM
stuff)
10.JDBC(java数据库连接)
是执行sql语句的API，由Java编写的类和接口组成，集成在java.sql包中，每个数据库开发商(如：mysql、sqlServer)需要写实现其接口的具体类，目的为了让java来连接自己的数据库，所以在java代码中方法都是一样的，只是在连接不同的数据库其底层实现接口的类不同罢了。例如：在连接mysql时我们导入的jar包，里面都是mysql开放商写的实现了JDBC接口规范的类罢了。
JDBC API：
| 接口 | 作用 |
|---|---|
| DriverManager类作用 | 管理各种不同的jDBC驱动 |
| Connection 接口 | 与特定数据库的连接 |
| Statement 接口(不安全) | 执行sql |
| PreparedStatement接口(安全) | 执行sql |
| ResultSet接口 | 接收查询结果 |
JDBC搭建(以mysql8来举例)：
1.注册驱动：
Class.forName(“com.mysql.cj.jdbc.Driver”); //反射实现
new Driver(); //new对象实现
2.建立于数据库连接：
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/连接的数据库?serverTimezone=Asia/Shanghai", "root", "密码");
3.编写sql(采用预编译的方式)：
String sql= "select * from user where username = ? and password =? ";
4.获得PrepareStatement执行sql语句
PrepareStatement pst = con.prepareStatement(sql);
5.设置参数
pst.setObject(1,username);
pst.setObject(2,password);
6.执行操作、返回结果集(查询 executeQuery 增删改executeUpdate)
ResultSet rs = pst.executeQuery();
7.结果集处理
rs.next(); //获得下一行的数据，如果有则rs指向下一行的数据，如果没有返回false；
rs.getXXX(String column); //根据数据库中的列名来获取值
7.关闭连接交互通道
pst.close();
rs.close();
con.close();
11.PreparedStatement和Statement的区别
举例：
Statement方式的sql：
delete from user where id = 1
PreparedStatement方式的sql：
delete from user where id = ?
区别1：
Statement是将变量拼接到sql中，如果值多了的话，书写不方便。
PreparedStatement是将值set进去，书写方便。
区别2：
Statement直接将值拼接进去不安全，如果在值里面加上比如 （1 or 1=1）这种永远成立的条件，这种值得情况下，上面的delete就会把user表中的所有数据全部删掉。也叫sql注入，后果可想而知！
而 PreparedStatement是采用预编译的方式，在set值时自动的检查值是否唯一且合法。可以有效的防止sql注入！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
