mysql学习笔记
1、创建数据表
CREATE TABLE IF NOT EXISTS `student2`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(10) NOT NULL DEFAULT '男' COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-
注意点 ,使用英文(),表的名称和字段要用 `` 括起来
-
AUTO_INCREMENT 表示自增
-
字符串使用 单括号 括起来
-
所有的语句后面加 , 最后一个语句不用加
-
PRIMARY KEY 主键,一般一个表只有一个唯一的主键
2、insert语句详解
-- 一般写插入语句,我们一定要数字和字段一一对应
-- 插入单个字段
INSERT INTO `student2`(`name`,`pwd`,`sex`) VALUES ('1','233','女')
-- 插入多个字段
INSERT INTO `student2`(`name`,`pwd`,`sex`) VALUES ('张三','3667','男'),('李四','999','男'),('王五','0088','女')
语法 : insert into 表名([][][字段一,字段二,字段三])values('值1'),('值2'),('值3')
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段可以省略,但后面的值必须一一对应,不能少
- 可以同时插入多条数据,但需要用逗号隔开
3、Updata语句详解
-- 修改学生的名字
UPDATE `student2` SET `name`='旺仔' WHERE id=1
-- 不指定条件的情况下,会改变所有的表!
UPDATE `student2` SET `name`='旺仔'
-- 修改多个属性
UPDATE `student2` SET `name`='旺仔',`sex`='男' WHERE id=1
-- 语法:
-- UPDATE 表名 set colnum_name=value,[colnum_name=value] where 条件
4、Delete和Truncate详解
delete命令
语法:delete from 表名 [where 条件]
-- 删除数据(尽量避免这种写法,会删除所有的数据)
DELETE FROM `student2`
-- 删除数据
DELETE FROM `student2` WHERE id=1
Truncate命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空所有的数据
TRUNCATE `student2`
delete与truncate 的区别
- 相同点:都能删除数据,都不会删除表的结构
- 不同点:
- truncate 重新设置 自增列,计数器会归零
- truncate 不会影响事务
- truncate 重新设置 自增列,计数器会归零
-- 测试delete与truncate 的区别
CREATE TABLE IF NOT EXISTS `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`name`) VALUES ('张三'),('李四'),('王五')
DELETE FROM `test` -- 不会影响自增
TRUNCATE FROM `test` -- 自增归零
5、DQL查询数据(最重点)
5.1、DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询它都能用
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM `student2`
-- 查询指定字段
SELECT `name`,`sex` FROM `student2`
-- 别名 给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECT `name` AS 姓名,`sex` AS 性别 FROM `student2`
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',`name`) AS 新名字 FROM `student2`
语法:SELECT 字段........ FROM 表
5.2、去重 distinct
作用:去除select查询出来的结果中重复的数据,重复数字只显示一条
-- 发现重复数据,去重
SELECT DISTINCT `name` FROM `student2`
5.3、where条件子句
作用:检索数据中 符合条件 的值
搜索条件由一个或多个条件组成,结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a && b | 逻辑与 |
or || | a or b a || b | 逻辑或 |
Not ! | not a !a | 逻辑非 |
-- 查询成绩在区间80到100之间
SELECT `name`,`score` FROM `student3`
WHERE `score`>=80 AND `score`<=100
-- 模糊查询
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100
-- not
SELECT `name`,`score` FROM `student3`
WHERE NOT `score`=100
5.4、分页和排序
排序 ORDER
-- 排序 :升序ASC ,降序 DESC
-- ORDER BY 通过那个字段排序
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100
ORDER BY `score` ASC
分页 LIMIT
-- 分页 每页只显示五条数据
-- 语法 LIMIT 当前页,页面大小
-- LIMIT 0,5
SELECT `name`,`score` FROM `student3`
WHERE `score` BETWEEN 70 AND 100
ORDER BY `score` ASC
LIMIT 0,5
6、Mysql函数
官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html
6.1、常用函数
-- 数学运算
SELECT ABS(-9) -- 绝对值
SELECT CEILING(7.4)
SELECT CEIL(7.3) -- 返回不低于参数的最小整数值
SELECT FLOOR(6.4)-- 返回不超过参数的最大整数值
-- 字符串函数
SELECT CHAR_LENGTH('孤帆远影碧空尽') -- 参数中的字符返回数
......
......
-- 时间和日期(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
6.2、聚合函数(重要)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
.... | ..... |
6.3、数据库级别的MD5加密(扩展)
什么是MD5
主要增强算法复杂度和不可逆性。
MD5不可逆,具体的值的MD5是一样的。
-- =================================测试MD5加密原理=============================
CREATE TABLE IF NOT EXISTS `test1`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO `test1` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE `test1` SET pwd=MD5(pwd) WHERE id=1 -- 加密后:e10adc3949ba59abbe56e057f20f883e
UPDATE `test1` SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO `test1` VALUES(4,'小明',MD5('123456'))
7、事务
7.1、什么是事务
要么都成功,要么都失败
1、SQL 执行 A给B 转账 A 1000 ---->200 B 200
2、SQL执行 B收到A的钱 A 800 -----> B 400
将一组SQL放在同一批次去执行
事务原则:ACID原则 原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
————————————————
原文链接:https://blog.csdn.net/dengjili/article/details/82468576
隔离导致的一些问题
脏读
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
页面统计查询值)
虚读(幻读)
是指在一个事 务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
7.2、测试事务:转账实例
执行事务 :转账
-- 转账
CREATE TABLE `account`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`money` VARCHAR(10) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account`(`name`,`money`) VALUES('A','2000.0'),('B','1000.0')
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;-- 开启一个事务
UPDATE `account` SET money = money - 500 WHERE `name` = 'A'; -- A减500
UPDATE `account` SET money = money + 500 WHERE `name` = 'B'; -- B加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
8、JDBC
8.1、第一个JDBC程序
sun公司为了简化开发人员的(对数据库的同一)操作,提供了一个(java操作数据库)规范,俗称JDBC,这些规范的实现具体由具体的厂商去做
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcstudy`;
CREATE TABLE users(
`id` INT PRIMARY KEY,
`name` VARCHAR(10),
`password` VARCHAR(50),
`email` VARCHAR(50),
`birthday` DATE
)
INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'张三','123456','kih@167367.com','2000-12-03'),(2,'李四','123456','nih@167337.com','1999-12-04'),(3,'王五','123456','lin@167367.com','2000-12-03')
编写测试代码
package com.hygv.lessom01;
import java.sql.*;
//我的第一个JDBC程序
public class jdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法,加载驱动
//2、用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=true"
String ur1 = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3、连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(ur1, username, password);
//4、执行sql对象 Statement 执行的对象
Statement statement = connection.createStatement();
//5、执行sql对象 去执行sql,可能存在结果,查看返回结果
String sql = "SELECT * FROM `users`";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果值
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("====================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
8.2、statement对象(重要)
数据库配置文件
driver=com.mysql.cj.jdbc.Driver
ur1=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
提取工具类
public class jdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properities");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("ur1");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动值加载一次
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet res){
if (res!=null){
try {
res.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
测试增加数据
public class testInsert {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet res = null;
try {
conn = jdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)\n" +
"VALUES(6,'wangzai2','2636245744','hug@7654272.com','2010-05-23')";
int i = sta.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,sta,res);
}
}
}
测试删除数据
public class testDelete {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet res = null;
try {
conn = jdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "DELETE FROM `users` WHERE id = 4";
int i = sta.executeUpdate(sql);
if (i>0){
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,sta,res);
}
}
}
测试更新数据
public class testUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet res = null;
try {
conn = jdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "UPDATE `users` SET `name`='wushang',`email`='786888' WHERE id=1";
int i = sta.executeUpdate(sql);
if (i>0){
System.out.println("更新成功!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,sta,res);
}
}
}
8.3、SQL注入问题
-
SQL注入
根据我们提交的用户名和密码被合成到SQL查询语句当中之后是这样的:
select * from app01_test where name='用户名' and pwd='密码';
很明显,用户名和密码都和我们之前给出的一样,肯定能够成功登陆。但是,如果我们输入一个错误的用户名或密码呢?很明显,肯定登入不了吧。恩,正常情况下是如此,但是对于有SQL注入漏洞的网站来说,只要构造个特殊的“字符串”,照样能够成功登录。
比如:在用户名输入框中输入:’or 1=1#,密码随便输入,这时候的合成后的SQL查询语句为:
select * from app01_test where name='' or 1=1#' and pwd='';
语义分析:“#”在mysql中是注释符,这样井号后面的内容将被mysql视为注释内容,这样就不会去执行了,换句话说,以下的两句sql语句等价:
select * from app01_test where name='' or 1=1#' and pwd='';
等价于
select * from app01_test where name='' or 1=1
因为1=1永远都是成立的,即where子句总是为真,将该sql进一步简化之后,等价如下select语句:
select * from app01_test
没错,该sql语句的作用是检索users表中的所有字段
果不其然,我们利用万能语句(’or 1=1#)能够登录!看到了吧,一个经构造后的sql语句竟有如此可怕的破坏力,相信你看到这后,开始对sql注入有了一个理性的认识了吧~ -
三、攻击原理
正常登陆name框中填写root,pwd为123 上面sql_sel = " select * from app01_test where name='{0}' and pwd='{1}' 就相当于select * from app01_test where name=root and pwd=123 进行查询
但
如果name框中填写root or 1=1 #,pwd 随便输入
上面sql_sel = " select * from app01_test where name='{0}' and pwd='{1}'
就相当于select * from app01_test where name=root or 1=1 # and pwd=123 进行查询
这里的#相当于把后面的所有查询包括password查询给注释,并且 or 1 = 1的查询永远是正确的,所以sql攻击注入就完成了通过这样的方式就绕过了密码检查
所以设计SQL语句时不建议这样设计,或者对特殊字符 #、or、and 等做特殊处理
8.4、PreparedStatement
public class testInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = jdbcUtils.getConnection();
//区别
//使用? 占位符代替参数
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)\n" +
"VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数配值
st.setInt(1,7);
st.setString(2,"五六七");
st.setString(3,"2648245744");
st.setString(4,"2648245744@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
//new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
}