数据库知识回顾
1,mysql的安装
2,通过客户端工具:建库、建表、操作表(增删改查)
3,SQL语句(重点)
(1)插入语句
语法:insert into 表(列1,列2,...)values(值1,列2,...)
-- 标准的插入语句
INSERT INTO user_info(id,birthday,address,NAME)VALUES(3,'1992-1-1',6,'沙僧')
-- 使用自增列的插入语句
INSERT INTO user_info(NAME,birthday,address)VALUES('bajie','1998-3-5',7)
-- 省略列名的插入语句
INSERT INTO user_info VALUES(4,'白晶晶','1993-2-1',10)
-- 使用系统当前时间来插入日期
INSERT INTO user_info VALUES(5,'铁扇公主',NOW(),11)
-- 插入多行的插入语句
INSERT INTO user_info(NAME,birthday,address) VALUES('王五',NOW(),12),('哪吒',NOW(),68),('唐僧',NOW(),111)
-- 备份数据的插入语句
INSERT INTO user_bak(NAME,birthday) SELECT NAME,birthday FROM user_info
语法:update 表名 set 列1=值1,列2=值2 where 条件
UPDATE user_info SET NAME='齐天大圣',birthday=NOW(),address=6 WHERE id=1
(3)删除语句
语法:delete from 表名 where 条件
DELETE FROM user_info WHERE id IN(1,2,6)
4,SQL查询(重点掌握)
(1)基础查询
语法:select * from 表 where 条件 order by 排序列 limit 限制行数
-- 查询表中所有数据
SELECT * FROM user_info;
-- 重命名查询 : as
SELECT NAME AS '姓名',birthday AS '生日',address AS '城市' FROM user_info;
-- 排序查询:order by
SELECT * FROM user_info ORDER BY birthday,address DESC
-- 限制行查询:limit m,n (m表示开始行,n表示查询的行数)
SELECT * FROM student_score ORDER BY score DESC LIMIT 3
-- 分页查询(page表示当前页,size表示每页显示的行数)
SELECT * FROM user_info LIMIT 5;
SELECT * FROM user_info LIMIT 5,9;
-- 分页SQL语句
SELECT * FROM user_info LIMIT size*(page-1),size
-- page=2,size=3
SELECT * FROM user_info LIMIT 3,3
-- 模糊查询(like配合通配符:% 表示任意个字符 _ 表示任意一个字符)
-- 查询姓张的人
SELECT * FROM user_info WHERE NAME LIKE '张%';
-- 查询名字中含三的人
SELECT * FROM user_info WHERE NAME LIKE '%三%';
-- 查询姓张并且名是一个字的人
SELECT * FROM user_info WHERE NAME LIKE '张_';
(2)聚合函数查询
统计个数:count(*)
-- 用户表的总人数
SELECT COUNT(*) FROM user_bak;
SELECT COUNT(NAME) FROM user_bak; -- 不统计name为null的行
SELECT COUNT(NAME) FROM user_bak; -- 不统计name为null的行
-- 去重后统计:统计用户表中的人来自于多少个不同的城市
SELECT COUNT(DISTINCT address) FROM user_info;
求和统计:sum(数值列)
-- 求总分数
SELECT SUM(score) FROM student_score;
均值统计:avg(数值列)
-- 求平均分
SELECT AVG(score) FROM student_score;
求最大值:max(数值列)
SELECT MAX(score) FROM student_score;
求最小值:min(数值列
SELECT MIN(score) FROM student_score;
-- 求总分数
SELECT SUM(score) FROM student_score;
-- 求平均分
SELECT AVG(score) FROM student_score;
-- 求最值
SELECT MAX(score) FROM student_score;
SELECT MIN(score) FROM student_score;
(3)分组查询
select 聚合函数 from 表 group by 列名 having 筛选条件(聚合函数)
-- 求每个城市的人数
SELECT address,COUNT(*) FROM user_info GROUP BY address;
-- 求每个城市的最大年龄
SELECT address,MAX(age) FROM user_info GROUP BY address;
-- 求男生和女生的人数
SELECT COUNT(*) FROM user_info WHERE sex='男'
SELECT COUNT(*) FROM user_info WHERE sex='女'
SELECT sex,COUNT(*) FROM user_info GROUP BY sex
(4)表连接查询
前提:表与表之间存在“主外键关系”
a,内连接
select * from A inner join B on A.主键=B.外键
select * from A,B where A.主键=B.外键
-- 高效
SELECT * FROM user_info INNER JOIN city_info ON user_info.address=city_info.cid;
SELECT u.name,c.name FROM user_info u INNER JOIN city_info c ON u.address=c.cid;
-- 语法简单
SELECT NAME,cname FROM user_info u,city_info c WHERE u.address=c.cid;
b,外连接(注意事项:以主表为主)
左外连接(左表为主表):select * from A left join B on A.主键=B.外键
右外连接(右表为主表):select * from A right join B on A.主键=B.外键
SELECT u.name,c.cname FROM user_info u RIGHT JOIN city_info c ON u.address=c.cid;
SELECT u.name,c.cname FROM city_info c LEFT JOIN user_info u ON u.address=c.cid;
-- 求每个城市的人数
SELECT cname,COUNT(NAME) FROM user_info u RIGHT JOIN city_info c ON u.address=c.cid GROUP BY c.cname
(5)子查询
概念:一个查询语句嵌套在另一个查询语句中的这种查询称为子查询。
-- 子查询的列和条件需要对应
-- 一般情况下子查询的结果需要唯一,除非in子查询
SELECT * FROM user_info WHERE age=(SELECT age FROM user_info WHERE NAME = '张三');
SELECT * FROM user_info WHERE age IN (SELECT age FROM user_info WHERE NAME LIKE '张%');
SELECT pname AS '姓名',cname AS '球队', body AS '身体',shoot AS '投篮' FROM clubs c INNER JOIN (SELECT pname,body,shoot,control,cid FROM player p INNER JOIN abiilities a ON p.PID=a.PID
) b ON c.cid=b.cid
1,JDBC概述
(1)JDBC是数据库连接技术的简称,提供了连接各种数据库的能力。
(2)JDBC API:连接数据库----->执行SQL---->处理结果
DriverManager:管理不同数据库的驱动程序,以实现连接各种不同的数据库。
Connection:负责连接数据库,建立通道。
Statement:执行SQL语句
ResultSet:保存SQL查询的结果
2,连接数据库
引入数据库驱动jar包
Class.forName("driver");
DriverManager.getConnection("url?characterEncoding=utf-8","user","password");
3,增删改数据
Statement stmt=conn.createStatement();
int row=stmt.executeUpdate("insert/update/delete语句");
4,查询数据
ResultSet rs=stmt.executeQuery("select语句");
5,从结果集(ResultSet)中获取数据
rs.next():将光标从当前位置向下移动一行
rs.getXxx(index/"列名"):获取某列的数据
6,查询练习:用户登录
(1)创建user1表(id,name,password),添加测试数据
(2)使用JDBC完成登录
import java.sql.*;
import java.util.Scanner;
public class jdbc1{
//增删改
public void test1(){
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
//创建Statement的对象
String sql="select * from student_bak";
sql="insert into student_bak values('小董',21,9)";//增
sql="delete from student_bak where id=6";//删
sql="update student_bak set age=age-1 where id=10";//改
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);//查询数据
//输出整个表
System.out.println("姓名"+"\t"+"年龄"+"\t"+"编号");
while(rs.next()){
System.out.println(rs.getString("name")+"\t"+rs.getInt(2)+"\t"+rs.getString("id"));
}
//执行增删改
int i=stmt.executeUpdate(sql);
System.out.println(i);
rs.close();//关闭查询
stmt.close();//关闭操作连接
conn.close();//关闭连接
} catch (Exception e) {
e.printStackTrace();
}
}
public void test2(){
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
//创建Statement的对象
String sql="select * from student_bak";
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);//查询数据
//输出整个表
System.out.println("姓名"+"\t"+"年龄"+"\t"+"编号");
while(rs.next()){
System.out.println(rs.getString("name")+"\t"+rs.getInt(2)+"\t"+rs.getString("id"));
}
rs.close();//关闭查询
stmt.close();//关闭操作连接
conn.close();//关闭连接
} catch (Exception e) {
e.printStackTrace();
}
}
public void test3(){
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
//创建Statement的对象
Scanner sc=new Scanner(System.in);
System.out.println("请输入姓名");
String n=sc.next();
System.out.println("请输入密码");
String p=sc.next();
String sql="select * from user1 where name='"+n+"' and password='"+p+"'";
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);//查询数据
if(rs.next()){
System.out.println("登陆成功");
}else{
System.out.println("登录失败");
}
rs.close();//关闭查询
stmt.close();//关闭操作连接
conn.close();//关闭连接
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入姓名");
String n=sc.next();
System.out.println("请输入密码");
String p=sc.next();
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
//创建Statement的对象
String sql = "SELECT * from user1 where name=? and password=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1, n);
pstmt.setString(2, p);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println("登录成功!");
} else {
System.out.println("登录失败!");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
7,PreparedStatement的使用
(1)优点(和Statement比较):
a,高效:预编译和检查SQL
b,安全:避免了拼接SQL,防止SQL注入
(2)用法:
PreparedStatement pstmt=conn.prepareStatement("带?参数的sql语句");
pstmt.setXxx(n,值);//设置每个?参数的值,其中n表示从左到右的第几个?
pstmt.executeUpdate()/executeQuery();
8,DAO开发模式
BaseDAO (封装数据库操作)
XxxDAO(接口)
XxxDAOImpl extends BaseDAO implements XxxDao
商品表
bookid(主键)
bookName
price
...
订单表
订单号(主键) varchar
订单金额 double
订单时间 date
订单明细表
bookid(外键)
订单号(外键)
购买数量