表单创建，多表查询练习：
INSERT INTO orderlist VALUES(NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2),(NULL,'hm005',3);
SELECT * FROM orderlist;
DELETE FROM users WHERE NAME = '王五';
ALTER TABLE orderlist DROP FOREIGN KEY or_usfk1;
ALTER TABLE orderlist ADD CONSTRAINT or_us_fk1 FOREIGN KEY (ouid) REFERENCES users (uid) ON UPDATE CASCADE ON DELETE CASCADE;
DELETE FROM users WHERE uname = '王五';
USE db3;
SELECT
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`dedcription`,
t3.`dname`,
t3.`iov`
FROM
emp t1,
job t2,
dept t3
WHERE
t1.`job_id` = t2.`id` AND
t1.`dept_id` = t3.`id`;
/*
-->隐式内链接 适用于多个表格之间查询
*/
SELECT
t1.`ename`,
t1.`salary`,
t2.`dname`,
t2.`iov`
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;
/*
-->显示内链接 适用于两个表格之间查询
*/
SELECT t1.`ename`,t1.`salary`,t2.`dname`,t2.`iov` FROM emp t1 INNER JOIN dept t2 ON t1.`dept_id` = t2.`id`;
USE prectice;
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20)
);
INSERT INTO category VALUES(NULL,'手机数码'),(NULL,'电脑办公'),(NULL,'烟酒糖茶'),(NULL,'鞋靴箱包');
CREATE TABLE product(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
cid INT,
CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(cid)
);
INSERT INTO product VALUES
(NULL,'华为手机',1),
(NULL,'小米手机',1),
(NULL,'联想电脑',2),
(NULL,'苹果电脑',2),
(NULL,'中华香烟',3),
(NULL,'玉溪香烟',3),
(NULL,'成人用品',NULL);
SELECT * FROM product;
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT,
cid INT,
pid INT,
CONSTRAINT up_fk1 FOREIGN KEY (cid) REFERENCES category (cid),
CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product (pid)
);
INSERT INTO us_pro VALUES
(NULL,1,1),
(NULL,1,2),
(NULL,1,3),
(NULL,1,4),
(NULL,1,5),
(NULL,1,6),
(NULL,1,7),
(NULL,2,1),
(NULL,2,2),
(NULL,2,3),
(NULL,2,4),
(NULL,2,5),
(NULL,2,6),
(NULL,2,7),
(NULL,3,1),
(NULL,3,2),
(NULL,3,3),
(NULL,3,4),
(NULL,3,5),
(NULL,3,6),
(NULL,3,7),
(NULL,4,1),
(NULL,4,2),
(NULL,4,3),
(NULL,4,4),
(NULL,4,5),
(NULL,4,6),
(NULL,4,7);
SELECT * FROM us_pro;
SELECT
t1.`cid`,
t1.`cname`,
t2.`pname`
FROM
category t1,
product t2
WHERE
t1.`cid` = t2.`cid`;
JDBC工具类抽取及应用：
public class JDBCUtils {
private static String url;
private static String username;
private static String password;
private static String driver;
static{
Properties pro = new Properties();
String path = JDBCUtils.class.getClassLoader().getResource("mysql.properties").getPath();
try {
pro.load(new FileReader(path));
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, Statement stat, ResultSet rs){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, Statement stat){
close(connection,stat,null);
}
}
登陆验证应用：
public class Test01 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入您的账号");
String username = sc.next();
System.out.println("请输入您的密码");
String password = sc.next();
Boolean login = new Test01().login(username,password);
if (login){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
}
public Boolean login(String username, String password) {
Connection con = null;
PreparedStatement pre = null;
ResultSet rs = null;
Boolean b = false;
try {
con = JDBCUtils2.getConnection();
String sql = "select * from user where username = ? and password = ?";
pre = con.prepareStatement(sql);
pre.setString(1, username);
pre.setString(2, password);
rs = pre.executeQuery();
b = rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils2.close(con, pre, rs);
return b;
}
}
} |
|
收藏
淘帖
踩
