MD5加密
update test set passward=MD5(passward);
insert into test values('4','name',MD5('passward'));
检验:将用户传入的密码也进行MD5加密,再进行对比即可检验
create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment 学号,
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment 学生姓名,
`sex` tinyint(1) default null comment 性别,0或1,
`gradeid` int(11) default null comment 年级编号,
`phone` varchar(50) not null comment 联系电话,允许为空,
`address` varchar(255) not null comment 地址,允许为空,
`borndate` datetime default null comment 出生时间,
`email` varchar (50) not null comment 邮箱账号允许为空,
`identitycard` varchar(18) default null comment 身份证号,
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment 年级编号,
`gradename` varchar(50) not null comment 年级名称,
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`subjectno`int(11) not null auto_increment comment 课程编号,
`subjectname` varchar(50) default null comment 课程名称,
`classhour` int(4) default null comment 学时,
`gradeid` int(4) default null comment 年级编号,
primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`studentno` int(4) not null comment 学号,
`subjectno` int(4) not null comment 课程编号,
`examdate` datetime not null comment 考试日期,
`studentresult` int (4) not null comment 考试成绩,
key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;
事务管理(ACID)
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
事务手动开启与关闭
MySQL是默认开启事务自动提交的
set autocommit = 0; /*事务关闭*/
set autocommit = 1; /*事务关闭*/
手动开启事务
事务开启
start transaction --标记一个事务的开始,从这之后的事务都在同一个事务内
insert xx
insert xx
commit --提交:持久化(成功)
rollback --回滚:回到原来的样子(失败)
savepoint name --设置一个事务的保存点
rollback to savepoint name --回滚到保存点
release savepoint name --撤销保存点
例子:
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
索引
分类
主键索引 (Primary Key)
唯一索引 (Unique)
常规索引 (Index)
全文索引 (FullText)
/*
#方法一:创建表时
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]) ;
#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/
索引准则
索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段
用户权限
/* 用户和权限管理 */ ------------------
用户信息表:mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
-- 必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限。
-- 只能创建用户,不能赋予权限。
-- 用户名,注意引号:如 'user_name'@'192.168.1.1'
-- 密码也需引号,纯数字密码也要加引号
-- 要在纯文本中指定密码,需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值,需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
MySQL备份
导出
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- 导入
1. 在登录mysql的情况下:-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
规范化数据库设计
糟糕的数据库设计 :
数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差
良好的数据库设计 :
节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
JDBC
记得要jdbc添加到lib
package com.company;
import java.sql.*;
public class Main {
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 url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3.连接成功,数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//4.执行数据库对象 statement执行sql的对象
Statement statement = connection.createStatement();
//5.去执行SQl
String sql = "SELECT * FROM student";
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("age:" + resultSet.getObject("age"));
System.out.println("-----------------");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
PreparedSatement
PreparedSatement 的好处
- prepareStatement()会先将 SQL 语句发送给数据库预编译。PreparedStatement 会引用着预编译后的结果。
可以多次传入不同的参数给 PreparedStatement 对象并执行。减少 SQL 编译次数,提高效率。 - 安全性更高,没有 SQL 注入的隐患。
- 提高了程序的可读性
使用 PreparedStatement 的步骤:
1.编写 SQL 语句,未知内容使用?占位:“SELECT * FROM user WHERE name=? AND password=?”;
2. 获得 PreparedStatement 对象
3.设置实际参数:setXxx(占位符的位置, 真实的值)
4.执行参数化 SQL 语句
5.关闭资源
package com.lqg;
import com.itheima.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 使用 PreparedStatement
*/
public class Demo8Login {
//从控制台上输入的用户名和密码
public static void main(String[] args) throws SQLException {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String name = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
login(name, password);
}
/**
* 登录的方法
* @param name
16 / 21
* @param password
*/
private static void login(String name, String password) throws SQLException {
Connection connection = JdbcUtils.getConnection();
//写成登录 SQL 语句,没有单引号 ‘?’是占位符,代替参数
String sql = "select * from user where name=? and password=?";
//得到语句对象
PreparedStatement ps = connection.prepareStatement(sql);
//设置参数
ps.setString(1, name);
ps.setString(2,password);
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
System.out.println("登录成功:" + name);
}
else {
System.out.println("登录失败");
}
//释放资源,子接口直接给父接口
JdbcUtils.close(connection,ps,resultSet);
}
}
JDBC操作事务
package le4;
import le2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭自动提交功能,开始事务
conn.setAutoCommit(false);
String sql1 = "UPDATE account set money=money-100 WHERE name='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
//会导致操作失败,事务回滚。
int x=1/0;
String sql2 = "UPDATE account set money=money+100 WHERE name='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕提交事务
conn.commit();
System.out.println("Success");
} catch (SQLException throwables) {
//如果失败则回滚
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
数据库连接池-DBCP
connection= JdbcUtils_DBCP.getConnection();
package com.company;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource=null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties=new Properties();
assert in != null;
properties.load(in);
//创建数据源 工厂模式→创建
dataSource=BasicDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws Exception {
return dataSource.getConnection();
}
//施放资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws Exception {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
}