MySQL表的结构及数据
表customer(客户信息表)
表dict(数据字典表)
表user(用户信息表,用于登录及注册)
create table customer
(
id int(32) auto_increment primary key,
cust_name varchar(50) null comment '客户姓名',
cust_source int(32) null comment '客户来源',
cust_industry int(32) null comment '客户所属行业',
cust_level int(32) null comment '客户级别',
cust_linkman varchar(50) null comment '客户联系人',
cust_phone varchar(60) null comment '联系电话',
cust_mobile varchar(16) null comment '移动电话',
cust_zipcode varchar(10) null comment '邮政编码',
cust_address varchar(100) null comment '联系地址',
cust_createtime datetime null comment '创建时间'
);
create table dict
(
dict_id int auto_increment primary key,
type_code varchar(50) null comment '类别代码',
type_name varchar(50) null comment '类别名称',
item_name varchar(50) null comment '项目名称'
);
create table user
(
id int(32) auto_increment primary key,
code varchar(32) null comment '用户账号',
name varchar(50) null comment '用户名称',
password varchar(50) null comment '用户密码',
state int(1) null comment '用户状态1:正常 0:暂停'
);
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (1, '韩非子', 5, 1, 8, '小韩', '0373-
88888888', '188888888888', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (2, '韩铭', 6, 2, 8, '韩铭', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-01 21:46:18');
INSERT INTO `customer` VALUES (4, '小张', 6, 1, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (5, '小王', 6, 2, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (6, '小薛', 6, 1, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (7, '小李', 6, 3, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (9, '韩寒', 5, 4, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (10, '王力宏', 5, 4, 7, '小韩', '0373-
88888888', '15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (11, '林俊杰', 5, 4, 8, '小韩', '0373-
88888888', '15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (12, '阿三', 5, 4, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (13, '泰坦', 5, 1, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (14, '胡涂', 5, 1, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (15, '李华', 6, 1, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (16, '李丽', 6, 2, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (17, '丽丽', 6, 1, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (18, '莉莉', 6, 3, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (19, '李莉', 5, 2, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (20, '黎明', 6, 4, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (21, '辛辰', 6, 4, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (22, '星辰', 5, 4, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (23, '雪雪', 5, 4, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (24, '豪杰', 6, 1, 7, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (25, '芹芹', 5, 1, 8, '小韩', '0373-88888888',
'15555555555', '450000', '郑州市经开区', '2020-07-28 21:44:58');
INSERT INTO `customer` VALUES (26, '孔子', 5, 1, 8, '孟子', '0373-87654321',
'188888888888', '100001', '山东', '2020-07-30 15:48:23');
-- ----------------------------
-- Records of dict
-- ----------------------------
INSERT INTO `dict` VALUES (1, '101', '客户行业', '教育培训');
INSERT INTO `dict` VALUES (2, '101', '客户行业', '对外贸易');
INSERT INTO `dict` VALUES (3, '101', '客户行业', '酒店旅游');
INSERT INTO `dict` VALUES (4, '101', '客户行业', '房地产');
INSERT INTO `dict` VALUES (5, '102', '客户来源', '电话营销');
INSERT INTO `dict` VALUES (6, '102', '客户来源', '网络营销');
INSERT INTO `dict` VALUES (7, '103', '客户级别', '普通客户');
INSERT INTO `dict` VALUES (8, '103', '客户级别', 'VIP客户');
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'lit001', '张翠山', '123', 1);
INSERT INTO `user` VALUES (2, 'lit002', '张无忌', '123', 1);
INSERT INTO `user` VALUES (3, 'lit003', '张三丰', '123', 1);
要求
新建数据库,根据以上信息建表添加测试数据,完成以下操作:
- 根据用户名以及密码完成登录查询
- 查询所有客户的信息(关联查询)
- 查询客户来源为电话营销的客户信息
- 查询客户级别为普通客户的客户信息
- 查询客户行业为教育培训的客户信息
- 根据客户姓名模糊 查询客户信息
实现
package Project_3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Sql_ {
static String pwd = "";
public static PreparedStatement conn(String sql) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
// 定义mysql连接地址、用户名、密码
String url = "jdbc:mysql://127.0.0.1:3306/java_project?characterEncoding=utf-8&serverTimezone=GMT";
String username = "root";
String password = "123";
// 获取数据库连接
Connection connection = DriverManager.getConnection(url, username, password);
// 获取一个预处理sql对象
PreparedStatement statement = connection.prepareStatement(sql);
return statement;
}
public static void Login() throws Exception {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.next();
queryLogin(username);
System.out.println("请输入密码:");
while (true) {
String pwd = sc.next();
if (pwd.equals(Sql_.pwd)) {
System.out.println("login successfully!");
break;
} else {
System.out.println("密码输入错误,请重新输入!");
}
}
}
public static void queryLogin(String name) throws Exception {
// 将sql中的参数 写成? 占位符
String sql = "select * from user where name=?";
// 加载驱动 8版本的jar包 cj 8以下的不用加cj
PreparedStatement statement=conn(sql);
// 将sql中的占位符进行动态填充
statement.setString(1, name);
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
int i = rs.getInt("id");
String user_password = rs.getString("password");
pwd = user_password;
}
}
public static void queryofNormal() throws ClassNotFoundException, SQLException {
System.out.println("查询客户级别为普通客户的客户信息:");
// 将sql中的参数 写成? 占位符
String sql = "select * from customer where cust_level=7";
PreparedStatement statement = conn(sql);
// 将sql中的占位符进行动态填充
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
String name = rs.getString("cust_name");
String phone = rs.getString("cust_phone");
String mobile = rs.getString("cust_mobile");
String zip = rs.getString("cust_zipcode");
String add = rs.getString("cust_address");
System.out.print("客户姓名 = " + name);
System.out.print(",客户热线 = " + phone);
System.out.print(",客户电话 = " + mobile);
System.out.print(",客户邮编 = " + zip);
System.out.println(",住址 = " + add);
}
}
public static void queryofEducation() throws ClassNotFoundException, SQLException {
System.out.println("查询客户行业为教育培训的客户信息:");
// 将sql中的参数 写成? 占位符
String sql = "select * from customer where cust_industry=1;";
PreparedStatement statement = conn(sql);
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
String name = rs.getString("cust_name");
String phone = rs.getString("cust_phone");
String mobile = rs.getString("cust_mobile");
String zip = rs.getString("cust_zipcode");
String add = rs.getString("cust_address");
System.out.print("客户姓名 = " + name);
System.out.print(",客户热线 = " + phone);
System.out.print(",客户电话 = " + mobile);
System.out.print(",客户邮编 = " + zip);
System.out.println(",住址 = " + add);
}
}
public static void queryofMobile() throws ClassNotFoundException, SQLException {
System.out.println("查询客户来源为电话营销的客户信息:");
// 将sql中的参数 写成? 占位符
String sql = "select * from customer where cust_source=5;";
PreparedStatement statement = conn(sql);
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
String name = rs.getString("cust_name");
String phone = rs.getString("cust_phone");
String mobile = rs.getString("cust_mobile");
String zip = rs.getString("cust_zipcode");
String add = rs.getString("cust_address");
System.out.print("客户姓名 = " + name);
System.out.print(",客户热线 = " + phone);
System.out.print(",客户电话 = " + mobile);
System.out.print(",客户邮编 = " + zip);
System.out.println(",住址 = " + add);
}
}
public static void queryAllofLevel() throws Exception {
System.out.println("按照用户等级降序查询所有用户:");
// 将sql中的参数 写成? 占位符
String sql = "select cust_name,cust_level from customer,dict where cust_level=dict.dict_id order by cust_level desc";
PreparedStatement statement = conn(sql);
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
String name = rs.getString("cust_name");
String level = rs.getString("cust_level");
System.out.print("客户姓名 = " + name);
System.out.println(",客户等级 = " + level);
}
}
public static void queryofName() throws ClassNotFoundException, SQLException {
System.out.println("查询客户姓名中带有“辰”信息:");
// 将sql中的参数 写成? 占位符
String sql = "select * from customer where cust_name like '%辰%'";
PreparedStatement statement = conn(sql);
ResultSet rs = statement.executeQuery();
// 结果集的遍历
while (rs.next()) {
String name = rs.getString("cust_name");
String phone = rs.getString("cust_phone");
String mobile = rs.getString("cust_mobile");
String zip = rs.getString("cust_zipcode");
String add = rs.getString("cust_address");
System.out.print("客户姓名 = " + name);
System.out.print(",客户热线 = " + phone);
System.out.print(",客户电话 = " + mobile);
System.out.print(",客户邮编 = " + zip);
System.out.println(",住址 = " + add);
}
}
public static void main(String[] args) throws Exception {
Login();
queryAllofLevel();
queryofMobile();
queryofNormal();
queryofEducation();
queryofName();
}
}