一.数据库查询
1.合并查询
合并查询是把两张表的记录合并到一起显示 关键词union
union会去除相同的数据
创建表
CREATE TABLE student(
stuid VARCHAR(10) PRIMARY KEY,
stuname VARCHAR(50)
);
— 分数表
CREATE TABLE score(
stuid VARCHAR(10),
score INT,
courseid INT
);
— 科目表
CREATE TABLE course(
courseid VARCHAR(10) PRIMARY KEY,
cname VARCHAR(50)
);
create table A(
name varchar(10),
score int
);
create table B(
name varchar(10),
score int
);
insert into A values('a',10),('b',20),('c',30);
insert into B values('a',10),('b',20),('d',40)
union可以取两张表的交集,去除重复数据,字段名和字段个数相同
SELECT * FROM A UNION SELECT * FROM B;
union all 查询表格所有,不去除两张表的重复数据
SELECT * FROM A UNION ALL SELECT * FROM B;
SELECT * FROM A, B;出现大量重复数据(笛卡尔积)
SELECT * FROM A, B;
去除重复数据
通过两张表关联的字段相等来去除重复(99查询)
SELECT * FROM student, score WHERE student.stuid=score.stuid;
查询学生编号和学生的分数,利用学生表和分数表
SELECT student.stuname, score.score FROM student, score WHERE student.stuid=score.stuid;
多表查询时不一定要有外键
SELECT s.stuname, sc.score FROM student s, score sc WHERE s.stuid=sc.stuid;
查询学生表 分数表 科目表
select * from student,score,course where student.stuid=score.stuid and score.courseid=course.courseid
查询学生的名字和对应的科目
select student.stuname, course.courseid from student, score , cource where student.stuid=score.stuid and score.courseid=course.courseid;
查询表中80分以上学生的 姓名 分数 科目信息
SELECT student.stuname, score.score, course.courseid from student, score, course WHERE
student.stuid=score.stuid AND score.courseid=course.courseid AND score.score>80;
2 连接查询
分为:内连接, 外链接, 自然连接
内连接 关键词 表一 inner(可以省略) join 表二 on 去除重复条件
SELECT * FROM student JOIN score ON student.stuid=score.stuid;
三个表查询
注意:ON后面只能加去除重复的条件,需要写其他条件时使用where
SELECT * FROM student JOIN score ON student.stuid=score.stuid JOIN course ON score.courseid=course.courseid WHERE score.score>70;
外链接 表一 OUTER(可省略) JOIN 表二 ON 去除重复条件
左外连接 以left左边的那张表为主,输出左边表的所有记录
右外连接 以right右边的那张表为主,输出右边表的所有记录
SELECT * FROM student RIGHT OUTER JOIN score ON student.stuid=score.stuid;
自然连接 关键词 natural join
可以自动匹配表中关联条件(字段名和类型相同)
SELECT * FROM student NATURAL JOIN score;
3, 子查询(嵌套查询)
查询工资高于JONES的员工信息
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=‘JONES’);
查询与SCOTT同一个部门的员工。
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=‘SCOTT’);
查询工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
查询工作和工资与MARTIN(马丁)完全相同的员工信息
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename=‘MARTIN’) AND sal=(SELECT sal FROM emp WHERE ename=‘MARTIN’);
查询有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno in (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);
查询员工编号为7788的 员工名称、员工工资、部门名称、部门地址
select emp.ename, emp.sal, emp.deptno, dept.loc from emp, dept where emp.deptno=dept.deptno and empno=7788;
求7369员工编号、姓名、经理编号和经理姓名
自连接把自己当成一张表使用
select e1.empno, e1.eename, e2.empno, e2.ename from emp e2, emp e2 where e1.empno=e2.mgr and e2.empno=7369
求各个部门薪水最高的员工所有信息
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno
SELECT * FROM emp e1, (SELECT deptno, MAX(sal) msal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal=e2.msal;
二.JDBC(数据库连接)
1.JDBC是Java为数据库提供的一套规范(接口)
连接数据库步骤:
1).加载驱动(注册驱动)
2).获取数据库连接通过数据库账号 密码
3).通过数据库连接对象获取SQL语句的执行对象
4).执行SQL语句执行对象 执行SQL语句
5).接收执行SQL后结果集处理
6).关闭资源
连接数据库:
public class Day27{
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//使用反射加载驱动类
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接通过数据库账号 密码
//参数:URL指 数据库地址: jdbc:mysql://主机IP:数据库端口号/数据库
String url = "jdbc:mysql://localhost:3306/ldmysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//3.通过数据库连接对象获取SQL语句的执行对象
Statement createStatement = connection.createStatement();
//4.执行SQL语句执行对象 执行SQL语句
//createStatement.executeUpdate(sql) 执行DDL DML 语句
//createStatement.executeQuery(sql) 执行 DQL语句(查询)
//插入一条数据
String sql = "insert into sort " + "(sname, sprice, sdesc) values " + "('鞋子', '200', '便宜耐用')";
int row = createStatement.executeUpdate(sql);
System.out.println("受影响行数" + row);
//关闭资源
connection.close();
createStatement.close();
}
}
查询全表 sort 打印数据
public class Day27{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
2.获取数据库连接通过数据库账号 密码
String url = "jdbc:mysql://localhost:3306/ldmysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//3.获取SQL语句执行对象
Statement statement = connection.createStatement();
//4.执行SQL语句
// 使用*查找列索引是数据库表字段的顺序 默认列的索引从1开始
// 如果没有使用 *那么索引的顺序就是你书写的字段的顺序
String sql = "select * from sort";
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集
while (resultSet.next()) {
// 通过索引获取值
//int sid = resultSet.getInt(2);
//String sname = resultSet.getString(1);
//通过字段名获取值
int sid = resultSet.getInt("sid");
String sname = resultSet.getString("sname");
System.out.println(sid +" "+ sname);
}
//关闭资源
connection.close();
statement.close();
resultSet.close();
}
}
练习:键盘输入账号密码, 根据账户密码去数据库查询用户信息
方法一:通过createStatement获取执行对象
public class Day27{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
System.out.println("输入账号");
Scanner scanner = new Scanner(System.in);
String us = scanner.nextLine();
System.out.println("输入密码");
String pas = scanner.nextLine();
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接通过数据库名和密码
String url = "jdbc:mysql://localhost:3306/ldmysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//通过数据库连接对象获取SQL语句的执行对象
Statement statement = connection.createStatement();
String sql = "select * from users where username='"+ us +"' and password='"+ pas +"'";
//执行SQL语句
ResultSet resultSet = statement.executeQuery(sql);
//处理结果集
while (resultSet.next()) {
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("username"));
System.out.println(resultSet.getString("password"));
}
// 关闭资源
connection.close();
statement.close();
resultSet.close();
}
}
方法二:通过PreparedStatement获取执行对象
预编译语句执行者
可以防止SQL注入的问题
public class Day27{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
System.out.println("输入账号");
Scanner scanner = new Scanner(System.in);
String us = scanner.nextLine();
System.out.println("输入密码");
String pas = scanner.nextLine();
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/ldmysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//获取执行sql对象
//prepareStatement 对SQL语句进行预编译
// SQL语句需要使用占位符?替换传入的值
String sql = "select * from users where username=? and password=?";
PreparedStatement statement = connection.prepareStatement(sql);
// 给占位符赋值 参数1 ?的索引 从1开始
statement.setObject(1, us);
statement.setObject(2, pas);
// 执行查询
ResultSet resultSet = statement.executeQuery();
// 处理结果集
while (resultSet.next()) {
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("username"));
System.out.println(resultSet.getString("password"));
}
// 关闭资源
connection.close();
statement.close();
resultSet.close();
}
}
练习: PreparedStatement 修改sort表 id为5的商品信息
public class Day27{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/ldmysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "update sort set sname=?, sprice=? where sid=5";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, "鞋子");
statement.setObject(2, 100);
int row = statement.executeUpdate();
System.out.println("受影响的行数" + row);
connection.close();
statement.close();
}
}