数据库回顾
数据库概述
- 什么是数据库:数据库就是存储数据的仓库;其本质是一个文件系统,将数据按照特定的格式进行存储。可以通过SQL语句对数据库中的数据进行增删改查。
- 数据库和表:不解释。
MySQL的使用
- MySQL安装(Windows上安装,Linux上的安装略)
- 下一步。。。
- 启动/关闭MySQL服务命令
- 启动:net start mysql
- 关闭:net stop mysql
- 登录mysql命令:先切换到MySQL安装目录的bin目录下,然后输入
mysql -u root -p密码
SQL语句
- 什么是SQL语句?
- 结构化查询语言。
- 关系数据库的国际标准。
- 各个数据库厂商都支持ISO的数据库标准:普通话。
- 各个数据库厂商在标准的基础上做了自己的扩展:方言。
SQL分类
DDL
- 数据定义语言(Database Definition Language)
- 用于定义数据库对象
- 数据库
- 表
- 字段
- 关键字
- create
- drop
- alter
DML
- 数据操作语言(Database Manipulation Language)
- 用于对数据库中表记录进行更新、删除、插入
- 关键字
- insert
- delete
- update
DQL
- 数据查询语言(Datebase Query Language)
- 用于对数据库中表的记录进行查询
- 关键字
- select
- from
- where
DCL
- 数据控制语言(Database Control Language)
- 用于定义数据库的访问权限和安全级别
- 关键字
- grant
数据库操作
创建数据库
create database 数据库名;
create database 数据库名 character set 编码;
查看数据库
- 查看数据库服务器中的所有数据库:
show databases;
- 查看某个数据库定义的信息:
show create database 数据库名;
删除数据库
drop database 数据库名;
切换数据库
use 数据库名;
查看正在使用的数据库
- 查看正在使用的数据库:
select database();
表操作
创建表
- 单表约束
- 主键约束
primary key
- 要求被约束的字段非空且唯一
- 唯一约束
unique
- 要求被约束的字段唯一
- 非空约束
not null
- 要求被约束的字段非空
- 主键约束
- 添加外键约束
constraint constraint_name foreign key(外键字段名) references 主表(外键字段名) on delete action(restrict | cascade | set null | no action) on update action(restrict | cascade | set null | no action)
- 例子
- 在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。 RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下, 父表不能更新; CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录; SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。 针对上面创建的两个表, 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表对应更新 。
create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT 'fk_city_country' FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看表
- 查看当前数据库中所有的表:
show tables;
- 查看表结构:
desc 表名;
删除表
drop table 表名;
修改表
- 添加字段:
alter table 表名 add 字段名 类型(长度) 约束;
- 删除字段:
alter table 表名 drop 字段名;
- 修改字段名:
alter table 表名 change 字段名 新字段名 类型(长度) 约束;
- 修改字段名,肯定要有一个旧字段名和一个新字段名。
- 修改字段的类型(长度)和约束:
alter table 表名 modify 字段名 类型(长度) 约束;
- 修改表的字符集:
alter table 表名 character set 编码;
- 修改表的名字:
rename table 表名 to 新表名;
- 注意:这个没有alter table,直接就rename就可以了。
插入表记录
- 插入语句
insert into 表名(字段1,字段2,......,字段n) values(值1,值2,......,值n);
insert into 表名 values(值1,值2,......,值n);
- 这种写法values里面的值要与创建这张表时的字段顺序一一对应,并且所有的字段都要插入值。
- 解决中文乱码问题
- 方式一:修改MySQL中的my.ini文件,然后重启服务器(不推荐使用该方法);
- 方式二:在dos窗口中写入set names gbk; 仅对当前窗口有效;
- 注意点
- 字段名与后面的值要一一对应;
- 值如果是字符串或者日期需要加引号(一般是单引号);
更新表记录
- 语句
update 表名 set 字段名=值;
- 这是无条件更新,就是该表中所有的记录都会更新。
update 表名 set 字段名=值 where 条件;
- 注意点
- 值如果是字符串或者日期需要加单引号。
删除表记录
- 语句
delete from 表名;
- 这是无条件删除,就是会把该表中的所有记录删除
delete from 表名 where 条件;
delete from 表名和truncate table 表名 删除记录的区别?
- 删除方式
- delete是一条一条记录进行删除,不清空auto_increment的记录数。
- truncate删除时,把整张表摧毁,然后新建一张一模一样的表,并将auto_increment的记录是归零,从新开始计数。
- 事务方面
- 如果开启了一个事务,那么使用delete删除的记录是可以通过回滚恢复的。
- 而使用truncate删除的记录是不能通过回滚恢复的。
- 注意
truncate table 表名;
这种删除方式是不能加where条件的,只能全部删除。
查询表记录
- 简单查询
- 语句:
select distinct(字段名) | * | 字段名1,字段名2,......,字段名n from 表名 [where 条件]
- 查询所有商品
select * from 表名;
- 查询商品名和商品价格
select pname,price from product;
- 别名查询
- 表别名
select * from product [as] p;
- 列别名
select pname [as] p1,price [as] p2 from product;
- 注意点:
别名中的as是可以省略的,一般都省略。
- 表别名
- 去掉重复值查询
select distinct(字段名) from 表名;
- 运算查询(查询结果是表达式)
- 例子:将所有商品价格加10元显示
select price+10 from product;
- 语句:
- 条件查询
- 运算符
- 比较运算符
- 大于:
>
- 大于等于:
>=
- 小于:
<
- 小于等于:
<=
- 等于:
=
- 不等于:
<>
- 在某一区间的值(含头含尾):
between a and b
- 在in列表中的值:
in (23,34,45)
- 模糊查询:
like
- 占位符
_
:表示一个字符%
:表示任意个字符(可以是0个字符)
- 占位符
- 判断是否为空:
is null
- 大于:
- 逻辑运算符
and
:多个条件同时成立or
:多个条件任意一个成立not
:条件不成立。select * from product where not (price>10)
:表示查询price>10这一条件不成立的商品信息。
- 比较运算符
- 例子
- 运算符
排序
select * from 表名 [where 条件] order by 字段名 asc | desc
- asc是递增排序;desc是降序排列。
- 注意点
- order必须写在where条件语句后面
- 如果不写asc或desc,那么默认是按照升序排列
聚合函数
- 常用的聚合函数
sum(字段名)
:求和avg(字段名)
:求平均数count(字段名)
:求个数。count()里面是可以写字段名,也可以写*
- 注意点:
聚合函数不统计null值。
分组
select * from 表名 where 查询条件 group by 字段名 having 分组条件;
- 分组条件一定要写在having后面,而不能通过where来写,因为where后面的是查询条件。
- 例子
- 根据cid字段分组,分组后统计商品的个数:
select cid,count(*) from product group by cid;
- 根据cid分组,分组统计每组商品的平均价格,并且平均价格大于20000元:
select cid,avg(price) from product group by cid having avg(price) > 20000;
- 注意:这里的“并且平均价格大于20000元”是分组条件,不是查询条件。
- 根据cid字段分组,分组后统计商品的个数:
查询语句总结
select * from 表名 where 查询条件 group by 字段名 having 分组条件 order by 字段名 asc | desc;
- 排序order by一定要写在最后。
- 分组和排序都是写在where后面(分组,排序 排序在最后)
JDBC
JDBC介绍
- 全称:Java DataBase Connectivity(Java数据库连接)
- JDBC是一种用于执行SQL语句的Java API。
- JDBC可以为多种关系型数据库提供统一的访问入口。
- JDBC由一组Java工具类和接口组成。
JDBC原理
- SUN公司提供访问数据库规范成为JDBC,而生产厂商提供规范的实现类成为驱动。
JDBC开发步骤
- 注册驱动
Class.forName("com.mysql.jdbc.Driver");
:这种是推荐写法DriverManager.registerDriver(new com.mysql.jdbc.Driver())
:这种写法不推荐,因为这种写法有缺点- 01.硬编码,如果换了别的数据库(不再是MySQL),那么要创建别的Driver,所以要重写括号里面的Driver了,而使用Class.forName()方法括号里面的参数一般都只从配置文件里面读取的,只需要修改配置文件即可。
- 02.这种方法会注册两次,因为com.mysql.jdbc.Driver类中有静态代码块,该静态代码块会再次注册驱动。
- 获得连接
Connection conn=DriverManager.getConnection(url,username,password);
- 编写SQL语句并获得语句执行者
- 编写SQL语句:
- 通过Statement对象来执行SQL语句:
Statement stmt=conn.createStatement();
- 执行SQL语句
int executeUpdate(String sql);
:执行insert、update、delete操作,返回影响的行数ResultSet executeQuery(String sql);
:执行查询操作,返回查询到的结果集boolean execute(String sql);
:- 执行查询操作返回true:如果返回true,需要用ResultSet getResultSet()来获取查询结果
- 执行insert、update、delete操作返回false:如果返回false,需要用int getUpdateCount()来获取影响行数
- 一般这种方法使用的比较少,使用前两种比较多。
- 处理结果
- ResultSet解释:ResultSet其实就是一张二维的表格,它里面有一个“光标”,初始时,“光标”在第一行的上面,可以使用next()方法来将“光标”移到下一行;第一次调用next()方法时,“光标”会移动到第一行。可以通过getXXX()方法来获取当前行的数据。
boolean next()
:该方法返回一个boolean类型的数据,如果遍历到了行末尾,那么就会返回false,否则就会返回true。- 获取当前行的数据
- getXXX(参数)方法里面的“参数”有两种写法
- 字段名(columnName):
resultSet.getInt("cid");
(获取字段名为cid的数据) - 字段所处的列(columnIndex):
resultSet.getInt(1);
(获取当前行第一列的数据,columnIndex从1开始) - 常用的getXXX()方法
Object getObject(String columnName | int columnIndex)
:如果不知道获取的数据是什么类型,就是用getObject()方法,返回一个Object类型的对象。int getInt(String columnName | int columnIndex)
:如果知道要获取的数据是int类型,那么就可以使用该方法。一定要能确保获取到的数据是int类型,下同。String getString(String columnName | int columnIndex)
:如果知道获取的数据时String类型,那么就可以使用该方法。double getDouble(String columnName | int columnIndex)
:如果知道获取的数据时double类型,那么就可以使用该方法。
- 字段名(columnName):
- getXXX(参数)方法里面的“参数”有两种写法
- 释放资源
- 与IO流一样,使用后的资源都需要关闭
- 释放资源的原则:先得到的后关闭,后得到的先关闭。
- jdbc数据库查询数据完整写法
@Test
public void test1(){
Connection conn=null;
Statement stmt=null;
ResultSet resultSet=null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/w eb08","root","pgmx0835");
//编写 SQL 语句并执行
String sql="select * from category";
stmt=conn.createStatement();
resultSet = stmt.executeQuery(sql);
//处理执行结果
while(resultSet.next()){
//获取查询到的数据
Integer cid=resultSet.getInt(1);
String cname=resultSet.getString(2);
System.out.println("cid="+cid+",cname="+cname);
}
}
catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
try {
//释放资源
//注意:释放资源要按照创建顺序的逆序来释放
if (resultSet != null) {
resultSet.close();
}
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
SQL注入问题
- 登录功能实现
- 实现原理:根据传入的name和password去数据库中查询,查询条件为同时满足名字为name和密码为password,如果查询到了,那么登录成功,否则登录失败。
- 实现方式:
- 方式一
- 使用字符串拼接,把name和password拼接进SQL语句中。
- 实现代码:
- 这种方式带来的问题(缺点):当传入的name中有or时,可能也会登录成功;即根本没有用户的名字叫传入的name参数.
- 解决办法:使用预处理PreparedStatement(见方式二)
- 方式二
- 实现原理:使用预处理PreparedStatement,在编写SQL语句时,使用占位符,然后通过给参数设置值,从而解决上述问题。
- 实现代码
- PreparedStatement总结
- 01.创建PreparedStatement:
PreparedStatement prepareStatement(String sql)
,创建时,要把SQL语句传入,因为要预处理。 - 02.编写SQL语句:使用预处理后,编写SQL语句可以使用占位符。
- 03.设置参数:因为使用了占位符,所以要设置参数,
setXXX(int parameterIndex,Object value)
- 04.执行SQL语句:执行SQL语句的方法与Statement的一样,只不过此处执行时,不需要把SQL语句传入。
- 01.创建PreparedStatement:
- 方式一
limit[m],n
- 参数解释
- m:m表示从第几条记录开始查询,注意:该索引是从0开始的。比如:m为0,那么就表示从第1条记录开始查询。m参数是可选择的,也就是可写可不写。如果m不写,那么就默认从第一条记录开始查询。
- n:n表示查询几条记录。例如,n为3,就表示此次查询3条记录。
- 例子:
select * from 表名 limit 2,5;
:表示从第3条记录开始查询,一共查询5条记录。也就是查询第3,4,5,6,7条记录。