「数据库学习」数据库的深入学习

数据库学习

首先输入以下代码,创建出几个表,方便使用

## 学生表
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)
);

## A表
create table A(
	name varchar(10),
	score int
);

## B表
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);

A表如下

在这里插入图片描述

B表如下

在这里插入图片描述

创建出学生表,添加几个记录,如下

在这里插入图片描述

创建出科目表,添加几个记录,如下

在这里插入图片描述

创建出分数表,添加几个记录,如下

在这里插入图片描述

合并查询（把两张表的记录合并到一起 , 关键词UNION)

取两张表交集（字段名数据类型相同）

SELECT * FROM a
UNION ALL
SELECT * FROM b;

在这里插入图片描述

注意:出现重复数据,应该把all去掉,正确代码如下

SELECT * FROM a
UNION
SELECT * FROM b;

在这里插入图片描述

学生表和分数表一起查询

SELECT * FROM student,score;

在这里插入图片描述

注意:出现重复数据,去除重复数据（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 s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid;

在这里插入图片描述

查询学生的名字对应的分数和科目

SELECT s.stuname,sc.score,c.cname FROM student s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid;

在这里插入图片描述

查询表中80分以上学生的姓名,分数和科目信息

SELECT s.stuname,sc.score,c.cname FROM student s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid
AND sc.score>80;

在这里插入图片描述

链接查询（多表查询）

分为:内链接,外链接,自然链接

内链接写法:内链接表1 INNER（可以省略） JOIN 表2 ON 去除重复的条件

查询学生表和分数表

SELECT * FROM student s
JOIN
score sc
ON s.stuid=sc.stuid;

在这里插入图片描述

查询学生表,科目表和分数表(注意：on后面只能加去除重复的条件)

SELECT * FROM student s
JOIN
score sc
ON s.stuid=sc.stuid
JOIN
course c
ON sc.courseid=c.courseid
WHERE sc.score>80;

外连接(OUTER JOIN ON)

左外链接left(以左边那张表为主,会输出这个表的全部数据)
右外链接right(以右边那张表为主,会输出这个表的全部数据)

SELECT * FROM student s
LEFT  JOIN
score sc
ON s.stuid=sc.stuid;

在这里插入图片描述

自然链接(NATURAL JOIN)

自动匹配表中关联条件（字段名和类型相同）

SELECT * FROM student 
NATURAL JOIN score;

在这里插入图片描述

子查询（嵌套查询）

首先制作2张表

表名emp

在这里插入图片描述

表名dept

在这里插入图片描述

查询工资高于JONES的员工信息

1.先查Jones工资,查出工资为2975

SELECT sal FROM emp WHERE ename='jones';

2.利用Jones工资当条件，查询工资高于JONES的员工信息

SELECT * FROM emp WHERE sal>2975;

3.可以将两个sql语句嵌套在一起

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个以上直接下属的员工信息(mgr这列一共出现几次,这个人就有几个下属)

SELECT * FROM emp WHERE empno in(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2
);

在这里插入图片描述

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=7788;

在这里插入图片描述

自连接

查询7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno,e1.ename,e2.empno,e2.ename FROM emp e1,emp e2 
WHERE e1.empno=e2.mgr
AND e2.empno=7369;

在这里插入图片描述

查询各个部门薪水最高的员工所有信息(将查询完返回的数据,当做一张新表来使用)

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(java DataBase Connection)

定义:是java为连接数据库提供的一套规范(接口)

连接数据库步骤:

1.加载驱动(注册驱动类)

2.获取数据库链接(通过数据库账号密码)

3.通过数据库链接对象获取sql语句的执行对象

4.使用sql执行对象,执行sql语句

5.接收执行sql后,处理结果集

6.关闭资源

制作一个表

在这里插入图片描述

用Eclipse插入一条数据

	public static void main(String[] args) throws SQLException, ClassnotfoundException {
		// 注册驱动
		// 使用反射来加载驱动类
		class.forname("com.mysql.jdbc.Driver");
		// 获取链接
		// 参数url:数据库的地址
		// jdbc:mysql://主机ip地址:数据库端口号/数据库名
		// jdbc:mysql://localhost:3306/wljdbc01
		String url ="jdbc:mysql://localhost:3306/wljdbc01";
		String user ="root";
		String password ="123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		// 通过数据库链接对象获取sql语句的执行对象
		statement statement = connection.createStatement();
		// 使用sql执行对象 执行sql语句
		// statement.executeLargeUpdate(sql) 执行DDL,DML语句
		// statement.executequery(sql)       执行DQL语句
		// 插入一条数据
		String sql = "insert into sort"
				+ "(sname,sprice,sdesc)values"
				+ "('柜子','30','方便好用包邮')";
		int row = statement.executeUpdate(sql);
		System.out.println("受影响的行数"+row);
		// 关闭资源
		// 关闭连接对象
		connection.close();
		// 关闭sql语句执行对象
		statement.close();
	}

数据库的深入学习