Java（含拓展知识和自己的做题图片记录）：jdbc学生表,打印输出,增删查改，用集合输出
--1./*常熬夜到12点或2点写代码，一写就好几个小时，有时手都酸痛死了，求三连，关注！*/
--在数据库中新建学生表，添加列学生ID(主键，自增)、学生姓名、学生年龄(大于5岁，小于60岁)、学生性别(男和女),添加5条测试数据，从数据库中查询出相应的数据打印输出
create table student(
Id int primary key identity(1, 1),
Name nvarchar (20) ,
Age int check (Age>5 and Age<60),
sex nvarchar (10) check(sex='男'or sex='女')
)
insert into student values('张三',19,'男')
insert into student values('王丽丽',20,'女')
insert into student values(' 李菲菲',21,'女')
insert into student values(' 陈德勇',22,'男')
insert into student values(' 诗书画唱',20,'男')
select * from student
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=Student库", "sa", "1234abcd");
String sql = "select * from Student";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.print(res.getObject("Id") + " ");
System.out.print(res.getObject("Name") + " ");
System.out.print(res.getObject("Age") + " ");
System.out.print(res.getObject("sex") + " ");
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第一题中的扩展知识：
--2
--在数据库中新建商品表,商品类型表，商品表包含商品ID（主键，自增）商品名称（非空验
--证），
--商品价格（大于0元），商品类型ID(外键)，商品保质期(Date类型),商品类型表包含商品类型
--ID(主键、自增)，商品类型名称(非空验证),各添加5条数据进行测试，使用JDBC在控制台打
--印其两表查询结果（含多表查询语法的个人理解记忆的方法）
/*个人理解:商品类型表就如父类，商品表就如子类，所以要先建商品类型表，有父亲才有儿子，再建商品表，要同过值相同的商品类型ID来连接，Foreign key(sp_TypeID) references shangpinleixing(sp_TypeID)在商品表中，
references 虽然是引证的意思，但为了方便理解和应用，可以理解为（但不等同，只是类比，比喻）Java中的extends延续关键字。或商品类型表为抽象的类，商品表为具体的对象，先有类，才有对象，所以先写商品类型表，再写商品表。有时出错可以换一下表的建造顺序*/
--create database shop
--use shop
create table shangpinleixing(
sp_TypeID int primary key identity(1, 1),
sp_TypeName varchar(50) not null,
)
create table shangpin(
sp_ID int primary key identity(1, 1),
sp_Name varchar(50) not null,
sp_Price int check(sp_Price>0),
sp_TypeID int,
sp_Baozhiqi Date default (getdate())
Foreign key(sp_TypeID) references shangpinleixing(sp_TypeID)
)
--drop table shangpin
--drop table shangpinleixing
insert into shangpinleixing(sp_TypeName) values('苹果')
insert into shangpinleixing(sp_TypeName) values('香蕉')
insert into shangpinleixing(sp_TypeName) values('面条')
insert into shangpinleixing(sp_TypeName) values('CD')
insert into shangpinleixing(sp_TypeName) values('海报')
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('诗书苹果',19,1)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('画唱香蕉',20,2)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('好吃面条',21,3)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('帅哥CD',22,4)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('诗书画唱海报',20,5)
--select * from shangpin
--select * from shangpinleixing
--出错时可删库，再重键库
--drop database shop
--create database shop
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shop", "sa", "1234abcd");
String sql = "select * from shangpin as sp join "
+ "shangpinleixing as splx on sp.sp_TypeID=splx.sp_TypeID";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品编号：" + res.getInt("sp_ID") + "\t"
+ ",商品名称："
+ res.getString("sp_Name") + "\t"
+ ",商品价格：" + res.getInt("sp_Price") + "\t"
+ ",商品类型ID：" + res.getInt("sp_TypeID") + "\t" +
",商品保质期：" + res.getString("sp_Baozhiqi") + "\t"
+ ",商品编号：" + res.getObject(6) + "\t" + ",商品类型名称："
+ res.getObject(7));
/*
* res.getObject(7)中的7代表第七列的所有内容,
* res.getInt("sp_ID")相当于res.getObject(1)
*/
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第二题的扩展知识：
--3.
在数据库中新建汽车表，汽车类型表，汽车表包含汽车ID，汽车名称(非空验证)，
--汽车价格，汽车类型ID（外键），汽车类型表包含汽车类型ID，汽车类型名称两列，
--使用JDBC添加5条数据进行测试，将其结果放入到集合中，遍历集合
--create database qiche
create table qicheleixing(
qiche_TypeID int primary key identity(1, 1),
qiche_TypeName varchar(50) not null,
)
create table qiche(
qiche_ID int primary key identity(1, 1),
qiche_Name varchar(50) not null,
qiche_Price int check(qiche_Price>0),
qiche_TypeID int,
Foreign key(qiche_TypeID) references qicheleixing(qiche_TypeID)
)
--drop table qiche
--drop table qicheleixing
insert into qicheleixing(qiche_TypeName) values('宝马')
insert into qicheleixing(qiche_TypeName) values('奔驰')
insert into qicheleixing(qiche_TypeName) values('大奔驰')
insert into qicheleixing(qiche_TypeName) values('大汽车')
insert into qicheleixing(qiche_TypeName) values('高科技汽车')
insert into qiche values('诗书宝马',19000,1)
,('画唱奔驰',20000,2)
,('好吃大奔驰',21000,3)
,('帅哥大汽车',22000,4)
,('诗书画唱高科技汽车',200000,5)
--select * from qiche
--select * from qicheleixing
--select * from qicheleixing as qclx join qiche as qc on qclx.qiche_TypeID=qc.qiche_TypeID
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=qiche", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("汽车ID:" + res.getInt("qiche_ID") + "\t"
+ "汽车名称:" + res.getString("qiche_Name") + "\t"
+ "汽车价格:" + res.getInt("qiche_Price") + "\t"
+ "汽车类型ID:" + res.getInt("qiche_TypeID"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第三题的扩展知识：
-
DESKTOP-49FTFSP
成功连接后：
--4.
新建数据库shopping，新建数据表（qiche）包含商品ID，商品名称，商品价格，
--商品保质期，--作业（1）：添加5条数据进行测试，在控制台将数据打印
--作业（2）：将商品ID为3的商品名称修改为“西瓜”，价格改为2.5
--作业（3）：删除ID为2的商品
--作业（4）：添加一条商品名称为“橘子”，价格为4元，保质期为当前时间+3天
--作业（5）：将增加后的商品进行重新遍历出来
--作业（1）答案：
create database shopping
use shopping
--drop table qiche
--drop table qicheleixing
create table qicheleixing(
sp_TypeID int primary key identity(1,1),
sp_TypeName varchar(50) not null,
)
create table qiche(
sp_ID int primary key identity(1,1),
sp_Name varchar(50) not null,
sp_Price int check(sp_Price>0),
sp_TypeID int ,
sp_Baozhiqi varchar(50)
Foreign key(sp_TypeID) references qicheleixing(sp_TypeID)
--外键会影响删除
)
--select * from qiche
--drop table qicheleixing
insert into qicheleixing(sp_TypeName) values('苹果')
insert into qicheleixing(sp_TypeName) values('香蕉')
insert into qicheleixing(sp_TypeName) values('面条')
insert into qicheleixing(sp_TypeName) values('CD')
insert into qicheleixing(sp_TypeName) values('海报')
insert into qiche values('诗书苹果',19,1,'1年'),('画唱香蕉',20,2,'2年')
,('好吃面条',21,3,'3年') ,('帅哥CD',22,4,'4年') ,('诗书画唱海报',20,5,'5年')
--select * from qiche
--select * from qicheleixing
--select * from qicheleixing as qclx join qiche as qc on qclx.sp_TypeID=qc.sp_TypeID
--出错时可删库，再重键库
--drop database shopping
--create database shopping
//第4题的第（1）题的方法一(普通遍历打印):
打印一张表：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品编号" + res.getInt("sp_ID") + "\t" + "商品名称"
+ res.getString("sp_Name") + "\t"
+ res.getInt("sp_Price") + "\t"
+ res.getInt("sp_TypeID") + "\t"
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
打印两张表：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from "
+ "qicheleixing as qclx join qiche as qc "
+ "on qclx.sp_TypeID=qc.sp_TypeID";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品编号：" + res.getInt("sp_ID") + "\t"
+ "，商品名称："
+ res.getString("sp_Name") + "\t"
+ "，商品价格："
+ res.getInt("sp_Price") + "\t"
+ "，商品类型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保质期："
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//第4题的第（1）题的方法二(用集合遍历打印一张表):
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
ArrayList<qiche> arr_list = new ArrayList<qiche>();
while (res.next()) {
// 用while(.next())给集合赋值:
qiche sp = new qiche();
sp.setSp_ID(res.getInt("sp_ID"));
sp.setSp_Name(res.getString("sp_Name"));
sp.setSp_Price(res.getInt("sp_Price"));
sp.setSp_TypeID(res.getInt("sp_TypeID"));
sp.setSp_Baozhiqi(res.getString("sp_baozhiqi"));
// 用.add()将模型类添加到集合中:
arr_list.add(sp);
}
for (qiche i : arr_list) {
System.out.println(i);
}
}
}
class qiche {
private String sp_Baozhiqi;
private int sp_ID;
private String sp_Name;
private int sp_Price;
private int sp_TypeID;
public int getSp_ID() {
return sp_ID;
}
public String getSp_Name() {
return sp_Name;
}
public int getSp_Price() {
return sp_Price;
}
public int getSp_TypeID() {
return sp_TypeID;
}
public void setSp_Baozhiqi(String sp_Baozhiqi) {
this.sp_Baozhiqi = sp_Baozhiqi;
}
public void setSp_ID(int sp_ID) {
this.sp_ID = sp_ID;
}
public void setSp_Name(String sp_Name) {
this.sp_Name = sp_Name;
}
public void setSp_Price(int sp_Price) {
this.sp_Price = sp_Price;
}
public void setSp_TypeID(int sp_TypeID) {
this.sp_TypeID = sp_TypeID;
}
@Override
public String toString() {
return "qiche [sp_Baozhiqi=" + sp_Baozhiqi + ", sp_ID=" + sp_ID
+ ", sp_Name=" + sp_Name + ", sp_Price=" + sp_Price
+ ", sp_TypeID=" + sp_TypeID + "]";
}
}
//--第4题的作业（2）：
将商品ID为3的商品名称修改为“西瓜”，价格改为4/*个人理解：因为sql中声明价格为int，所以改不了小数，除非sql中声明为小数*/
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql = "update qiche set sp_Name='西瓜' ,sp_Price=4 where sp_ID=3";
// 这个方法返回的数据是一个int类型的数据，如果是0,说明修改失败。
int num = sta.executeUpdate(sql);
if (num > 0) {
// 修改成功
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
}
}
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql="delete qiche where sp_ID=3";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
}
}
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql="insert into qiche values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失败");
}
}
}
//作业（5）
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql = "insert into qiche values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失败");
}
System.out.println("用while (res.next())遍历打印出来，用sql2语句两表查询");
String sql2 = "select * from "
+ "qicheleixing as qclx join qiche as qc "
+ "on qclx.sp_TypeID=qc.sp_TypeID";
ResultSet res = sta.executeQuery(sql2);
while (res.next()) {
System.out.println("商品编号：" + res.getInt("sp_ID") + "\t"
+ "，商品名称："
+ res.getString("sp_Name") + "\t"
+ "，商品价格："
+ res.getInt("sp_Price") + "\t"
+ "，商品类型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保质期："
+ res.getString("sp_Baozhiqi"));
}
}
}
第四题的扩展知识和我的做题历程，解决报错等的图片记录：
