什么是数据库
数据库是结构化信息或数据（一般以电子形式存储在计算机系统中）的有组织的集合，通常由数据库管理系统 (DBMS) 来控制。在现实中，数据、DBMS 及关联应用一起被称为数据库系统，通常简称为数据库。
为了提高数据处理和查询效率，当今最常见的数据库通常以行和列的形式将数据存储在一系列的表中，支持用户便捷地访问、管理、修改、更新、控制和组织数据。另外，大多数数据库都使用结构化查询语言 (SQL) 来编写和查询数据
1.数据库分类
1.1关系型数据库
- MySQL，oracle,Sql server,DB2,SQLlite.
- 通过表和表之间，行和列之间的关系对数据进行存储。
1.2非关系型数据库
- redis，MongDB
- 对象储存，通过对象自身属性来决定。
DBMS
数据库管理软件，科学有效的管理数据，维护和获取数据。
2.操作数据库
2.1数据库基本操作
命令行操作
mysql -uroot -p密码 --连接数据库
--所有的命令都以;结尾
show databases; --查看所有的数据库
use 数据库名 --切换数据库
show tables --查看数据库中的表
describe 表名 --显示数据库中表的信息
create database 数据库名 --创建一个数据库
sql语言分类
DDL 定义语言
DML 操作语言
DQL 查询语言
DCL 控制语言
sqlyog
CREATE DATABASE IF NOT EXISTS shcool; --如果这个数据库不存在，创建一个数据库
DROP DATABASE IF EXISTS school; --如果数据库存在，删除它
USE school; --使用/切换数据库
show databases --显示所有的数据库
2.2 数据库列类型
1. 值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准整数 4个字节
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式浮点数 金融数据
2. 字符串
- char 固定大小的字符串 0~255
- varchar 可变字符串 0~65535
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1
3.时间日期
- date YYYY-MM-DD 日期格式
- time HH：mm：ss 时间格式
- datetime
- timestamp 时间戳
- year 年份表示
** 4.null**
- 没有值
- 不要使用null运算
2.3 数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充
- 不足的位数用0填充
自增
- 自动在上一条记录的基础上+1
- 通常用来设计唯一主键，必须是整数
- 可以自定义的设置主键自增的起始值和步长
非空
- 假设设置为非空，如果不进行赋值，就会报错。
2.4 数据库创建表
CREATE TABLE IF NOT EXISTS `students`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
--auto_increment自增
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
--default 默认值
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(3) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`) --设置为主键
)ENGINE=INNODB DEFAULT CHARSET=utf8
SHOW CREATE DATABASE school; --显示创建数据库的语句
SHOW CREATE TABLE students --显示创建表的语句
DESC students; --显示具体表的结构
2.5 数据表类型
| INNODB | MYISAM | |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文检索 | 不支持 | 支持 |
| 表空间大小 | 较大约为两倍 | 较小 |
常规操作：
MYISAM 节约空间，速度较快。
INNODB 安全性高，支持事务处理，多表操作
2.6 操作表 alter table
--修改表名 ALTER TABLE 原表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--添加一列 ALTER TABLE 表名ADD 添加字段名 列属性
ALTER TABLE teacher1 ADD age INT(4)
--修改表的字段属性（修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 更改的列属性
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束
-- ALTER TABLE 表名 CHANGE 原字段 新字段名 列属性
ALTER TABLE teacher1 CHANGE age age1 INT(1) --重命名（可修改约束）
--删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
-- 删除表 (如果存在删除)
DROP TABLE IF EXISTS teacher1
3. MySQL数据管理
3.1 添加
insert
-- INSERT INTO `表名`(`字段名`) VALUES('插入的值')
INSERT INTO `teacher1`(`id`,`grade`) VALUES('1','90')
INSERT INTO `teacher1`(`id`,`grade`) VALUES('1','90'),('2','80')
注意事项：
字段可以省略，但是后面的值必须一一对应。
可以同时插入多个值，使用逗号隔开即可(),()
3.2 修改
update
-- 格式 UPDATE `表名` SET `字段`='要换上去的值' WHERE `id`='1'(要修改的是谁)
UPDATE `students` SET `name`='xiaojia' WHERE `id`='1'
大于某个条件 >
小于某个条件 <
不等于某个条件 <>或者!=
在某个区间 between…and…
等于 =
3.3 删除
delete
--删除 DELETE FROM `表名` WHERE 位置
DELETE FROM `students` WHERE `id`=1
--清空数据库表单 truncate 表名
delete和truncate区别
- trubcate重新设置自增列，自增器会归零。
- truncate不回影响事务。
DQL查询数据*
4.1 查询指定字段
select 字段 from 表名
联表
查询条件
按字段分组
分组后的查询条件
排序
分页
-- 查询全部学生 SELECT 字段名 FROM 表名
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
--起别名，给结果起一个别名 AS
-- 格式 SELECT `字段名` AS 别名 ,`字段名` AS 别名FROM `表名`
SELECT `studentno` AS 学号 ,`studentname` AS 姓名 FROM `student`
--函数 SELECT CONCAT(a,b) FROM 表名
SELECT CONCAT('姓名: ',studentname ) AS 新名字 FROM student
去重 distinct
SELECT DISTINCT `studentno` FROM result
数据库的列（表达式）
SELECT VERSION() --查看系统版本
SELECT 32*12-4 AS 计算结果 --用来计算
SELECT @@auto_increment_increment --查询自增步长
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM `result` --加分后查询
4.2条件查询 where
--格式 select 字段名 from 表名 where 条件(in,like....)
模糊查询
- null
- not null
- between
- in 具体的一个或者多个值
- like %(不论有多少个字符) _(一个字符)
4.3 联表查询
join 对比
inner join 两边必须重复的才能查询出来
-- 格式： select 字段 from 表一 查询方式 表二 on 条件 （可再加条件）
SELECT s.`studentno`,`subjectno`,`studentname` ,`studentresult` --如果有重复的字段，要指明使用哪个表的。
FROM student AS s --起别名，好分辨
inner JOIN result AS a
ON s.`studentno`=a.`studentno` --一定要有相同的字段连接
left join 查询出左边全部的信息，不管右边有没有（同时右边独有的信息也不会别查询）
right join 查询出右边所有的信息，不管左边有没有（同时左边独有的信息也不会别查询）
4.4分页和排序
排序 order by
升序 asc
降序 desc
-- 格式 ORDER BY `字段` 排序方法
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
ORDER BY `studentresult` ASC
limit 分页
limit 起始值 一个页面中数据的个数
4.5 分组和条件（having）
分组 group by 通过什么字段分组
--格式 group by 字段
having 分组后的查询条件
-- 格式 having 条件
5.函数
常用函数
abs() 绝对值
ceiling() 向上取整
floor() 向下取整
char_length() --字符串长度
concat() --拼接字符串
insert() --插入字符串
聚合函数
count() --计数
count(字段) --会忽略null
--求和
sum()
--平均分
avg()
-- 最大值
max()
--最小值
min()
6.事务
要么全部成功，要么全部失败。
ACID原则 原子性，一致性，隔离性，持久性
原子性
要么都成功，要么都失败
一致性
事务前后数据完全保持一致
持久性 --事务提交
事务一旦提交就不可逆，被持续化到数据库中。
隔离性
事物的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的的事务，不能被其他的事务操作数据所干扰，事务之间要相互隔离。
隔离导致的一些问题
脏读：
指一个事务读取了另一个事务没有提交的数据
不可重复读：
在事务内读取表中的一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读（幻读）
指在一个事务内读到了别的事务插入进来的数据，导致前后读取不一致。
处理流程
-- 首先要将自动提交关闭（因为系统是默认自动提交的）
set autocommit=0
-- 事务开启
start transaction
事物内部的操作
commit --提交 持久化（成功）
rollback -- 回滚 回到原来的样子（失败）
--事务结束
set autocommit = 1 --将自动提交开启
其他操作
savepoint 保存点名 --设置一个事务的保存点 （存档）
rollback to savepoint 保存点名 --回滚到保存点 （读档）
release savepoint 保存点名 --撤销保存点（删档）
实例
SET autocommit=0;
START TRANSACTION
UPDATE `result` SET `studentresult`=`studentresult`-10 WHERE `studentno`='1000' --1000号分数-10
UPDATE `result` SET `studentresult`=`studentresult`+10 WHERE `studentno`='1001' --1001号分数+10
COMMIT; --提交后，回滚无效
ROLLBACK;
SET autocommit=1;
7.索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构
提取句子的主干，就可以得到索引的本质：索引是数据结构
7.1 索引的分类
-
主键索引（primary key）
唯一标识，主键不可重复，只能有一个列设为主键 -
唯一索引 （unique key）
避免重复的列出现，唯一索引可以重复，多个列可以表示为唯一索引 -
常规索引（key/index）
默认值 -
全文索引（fulltext key）
在特定的数据库引擎下才有（innodb）
show index from 表名
--创建索引
alter table 表名 add 索引类型 索引名 字段名
create 索引类型 索引名 on 表名(字段名)
索引会大大提升查找速度
7.2索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构
innodb的默认数据结构：btree（b树）
8. 权限管理
8.1 用户管理
-- 创建用户
create user 用户名 inentified by 密码
--修改密码
set password [for 用户名]= password(密码)
-- 重命名
rename user 旧名字 to 新名字
8.2 数据库备份
为什么要备份
- 保证重要数据不丢失
- 数据转移
MySQL 数据库备份方式
- 直接拷贝物理文件
- 在Sqlyog这种可视化工具上手动导出
- 使用命令行导出 mysqldump 命令行导出
格式：mysqldump -h主机 -u账号 -p密码 数据库 表名 >导出位置
9. 规范数据库设计
9.1 为什么需要设计
当数据库比较复杂时，我们就需要设计了
糟糕的数据库设计：
- 浪费空间
- 数据库插入和删除都会麻烦，异常
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求，分析业务和需要处理的数据库需求
- 概要设计：涉及关系图 E-R图
9.2 三大范式
大一范式
原子性：保证每一列不可分。
第二范式
满足第一范式前提下，每张表只满足一个事情
第三范式
满足第一和第二范式条件下，确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（在实际生产中，为了提高效率不一定会严格遵守三大范式）
jdbc工程步骤
-
加载驱动
-
连接数据库 （DriverManager）
-
获得可执行sql的对象 Statement
-
获得返回的结果集
-
释放连接
实现
package com.huang.www;
import java.sql.*;
//我的第一个jdbc程序
public class jdbcfirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//用户信息和url
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username ="root";
String password ="123";
//如果连接成功，可拿到数据库内容 connection代表数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//执行sql的对象，statement=执行sql的对象
Statement statement = connection.createStatement();
//执行sql的对象
String sql="SELECT * FROM grade";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id: "+resultSet.getObject("gradeid"));
System.out.println("name: "+resultSet.getObject("gradename"));
}
resultSet.close();
statement.close();
connection.close();
}
}
加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定不变
URL
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
//协议 ://主机地址:端口号/数据库名?参数一&参数二&参数三（mysql默认端口号为3306）
Connection 数据库
connection.commit(); //事务提交
connection.rollback(); //事务回滚
connection.getAutoCommit(); //自动提交
statement 执行sql的对象 prepareStatement执行sql对的对象（表单）
statement.executeQuery(); //查询操作 返回resultset
statement.execute();// 执行任何sql
statement.executeUpdate(); //更新，插入，删除都用这个 ，返回受影响的行数
ResultSet 执行操作的结果集 （查询到的内容）
可以获得指定数据类型
ResultSet resultSet = statement.executeQuery(sql);
resultSet.getInt();
resultSet.getDouble();
是指针，可遍历
释放连接 （重要）
封装类实现增删改
package com.huang.lesson1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbcInsert {
public static void main(String[] args) {
Connection con = null;
Statement sta = null;
ResultSet res = null;
try {
con = jdbcUtils.getConnection(); //获得连接
sta = con.createStatement(); //执行sql语句的对象
String sql = " INSERT INTO `category`(`categoryid`,`pid`,`categoryname`)\n" +
" VALUE (11,4,'java开发')"; //sql语句，在这里修改要执行的语句即可
int i = sta.executeUpdate(sql); //执行
if(i>0) {
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
jdbcUtils.release(con,sta,res); //释放空间
}
}
}
封装工具类
package com.huang.lesson1;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
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("dp.properties");//固定写法
Properties properties = new Properties();
properties.load(in);//拿到properties类中的数据
driver = properties.getProperty("driver");
url = properties.getProperty("url");
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 con, Statement sta, ResultSet res){
if(res!=null) {
try {
res.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(sta!=null){
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
properties类
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false //不用加分号！！！！！！！！
username=root
password=123
sql注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
为了防止发生sql注入，建议使用preparedStatement
preparedStatement使用方法
其他地方和Statement保持一致，但是后面的sql语句编译产生变化，preparStatement会进行预编译
String sql = " INSERT INTO `category`(`categoryid`,`pid`,`categoryname`)\n" +
" VALUE (?,?)"; //sql语句，在这里修改要执行的语句即可
preparStatement(sql); //预编译
//给value中参数进行具体赋值。
set.setString(第几个参数，参数的值); //给String类型参数赋值
事务
在idea中提交事务
package com.huang.lesson03;
import jdk.nashorn.internal.ir.CallNode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class test {
public static void main(String[] args) {
Connection con = null;
PreparedStatement sta = null;
ResultSet res = null;
try {
con = jdbcUtils.getConnection();
con.setAutoCommit(false); //关闭自动提交，同时会自动开启事务
String sql1 = "update account set money= money-100 where NAME = 'A' "; //书写sql语句
sta = con.prepareStatement(sql1); //预编译
sta.executeUpdate(); //更新数据
String sql2 = "update account set money= money+100 where NAME = 'B' ";
sta = con.prepareStatement(sql2);
sta.executeUpdate();
con.commit();
System.out.println("提交成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
