###课程回顾:
- 数据库相关SQL
- show databases;
- create database db1 character set utf8/gbk;
- show create database db1;
- drop database db1;
- use db1;
- 表相关
- create table t1(name varchar(10),age int) charset=utf8/gbk;
- show tables;
- show create table t1;
- desc t1;
- drop table t1;
- alter table t1 add age int first/after xxx;
- alter table t1 drop 字段名;
- alter table t1 change 原名 新名 新类型;
- 数据相关
- insert into t1(字段名,字段名) values(值1,值2),(值1,值2);
- select name,age from t1 where 条件;
- update t1 set xxx=xxx where 条件;
- delete from t1 where 条件;
- 数据类型
- 整数 int(m)和bigint m代表显示长度 需要结合zerofill使用
- 浮点数 double(m,d) m总长度 d小数长度 超高精度浮点数 decimal
- 字符串: char:固定长度 最大255 varchar:可变长度最大65535,超过255建议使用text text:可变长度65535
- 日期: date 年月日 time 时分秒, datetime:默认null 最大9999-12-31 timestamp:默认当前系统时间 最大2038-1-19
-
主键约束 primary key,主键:表示数据唯一性的字段称为主键, 约束:创建表时给字段添加的限制条件, 主键约束:唯一且非空
-
主键约束+自增: parimary key auto_increment, 从历史最大值+1
-
导入*.sql文件 source 路径;
-
is null 和 is not null
-
比较运算符 > < >= <= = !=和<>
-
and 和 or
-
between x and y 包含x和y
-
in(x,z,y)
-
模糊查询like %代表0或多个未知字符 _代表单个未知
-
排序 order by 字段名 asc/desc,字段名 asc/desc;
-
分页 limit 跳过的条数,请求的条数
-
数值计算 + - * / %
-
聚合函数 求和sum() 平均值avg() 最大值max() 最小值min() 计数count()
-
分组查询 group by 字段
-
having: 用于聚合函数的条件,和分组查询group by结合使用
-
select … from 表名 where … group by … having … order by… limit …;
####综合练习题: -
查询没有上级领导的员工编号empno,姓名,工资
select empno,ename,sal from emp where mgr is null; -
查询有奖金的员工姓名和奖金
select ename,comm from emp where comm>0; -
查询名字中包含精的员工姓名和工资
select ename,sal from emp where ename like ‘%精%’; -
查询名字中第二个字是八的员工信息
select * from emp where ename like ‘_八%’; -
查询1号部门工资大于2000的员工信息
select * from emp where deptno=1 and sal>2000; -
查询2号部门或者工资低于1500的员工信息
select * from emp where deptno=2 or sal<1500; -
查询工资为3000,1500,5000的员工信息按照工资升序排序
select * from emp where sal in(3000,1500,5000) order by sal; -
查询3号部门的工资总和
select sum(sal) from emp where deptno=3; -
查询每个部门工资大于1000的员工人数,按照人数升序排序
select deptno,count(*) c from emp where sal>1000 group by deptno order by c; -
查询每种工作中有领导的员工人数按照人数降序排序
select job,count(*) c from emp where mgr is not null group by job order by c desc; -
查询所有员工信息,按照部门编号升序排序,如果部门编号一致则工资降序
select * from emp order by deptno,sal desc; -
查询有领导的员工,每个部门的编号和最高工资
select deptno,max(sal) from emp
where mgr is not null group by deptno; -
查询有领导的员工,按照工资升序排序,第3页的2条数据
select * from emp
where mgr is not null order by sal limit 4,2;
###子查询(嵌套查询) -
查询工资高于平均工资的员工信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp); -
查询员工表中工资最高的员工信息
select * from emp where sal=(select max(sal) from emp); -
查询工资高于2号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp where deptno=2); -
查询和孙悟空相同工作的其它员工信息
select job from emp where ename=‘孙悟空’;select * from emp where job=(select job from emp where ename=‘孙悟空’) and ename!=‘孙悟空’;
###关联查询
- 同时查询多张表的查询方式称为关联查询
- 如果关联查询不写关联关系,会得到两张表的乘积,这个乘积称为笛卡尔积,这是一个错误的查询结果,工作中切记不要出现
- 查询每个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno; - 查询孙悟空的部门信息
select d.*
from emp e,dept d
where e.deptno=d.deptno
and e.ename=‘孙悟空’;
###关联查询的三种查询方式 - 等值连接
- 格式:select * from A,B where A.x=B.x and A.age=18;
- 内连接
- 格式: select * from A join B on A.x=B.x where A.age=18;
- 查询每个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno; - 查询神仙部的员工信息
select e.*
from emp e join dept d
on e.deptno=d.deptno
where d.dname=‘神仙’;
- 查询每个员工的姓名和对应的部门名
- 等值连接和内连接查询到的数据都是两张表的交集数据
insert into emp(empno,ename,sal) values(12,‘灭霸’,500);
- 外连接:当需要查询一张表的全部和另外一张表的交集数据时,使用外连接
- 格式: select * from A left/right join B on A.x=B.x where A.age=18;
- 查询所有的员工信息和对应的部门名
select e.*,d.dname
from emp e left join dept d
on e.deptno=d.deptno; - 查询所有部门的名称和对应的员工姓名
select d.dname,e.ename
from emp e right join dept d
on e.deptno=d.deptno;
####关联查询总结
- 查询所有的员工信息和对应的部门名
- 如果需要同时查询多张表的数据则使用关联查询
- 关联查询必须写关联关系
- 关联查询有三种查询方式: 等值连接、内连接和外连接
- 查询两张表的交集数据时使用等值连接或内连接(推荐)
- 查询两张表中一张表的全部和另外一张表的交集使用外连接
###关联关系(了解)
- 关联关系指创建表时,表与表之间存在的业务关系
- 1对1: 有AB两张表,A表中的一条数据对应B表中的一条数据,同时B表中一条数据对应A表中的一条。
- 1对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据
- 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的多条数据,多对多建立关系需要准备一张关系表。
###JDBC
- Java DataBase Connectivity:Java数据库连接,实际上JDBC是Sun公司提供的一套链接数据的API(Application Programma Interface 应用程序编程接口)
- 为什么使用JDBC:因为Java语言中有可能连接多种数据库,如果没有JDBC,java程序员每一种数据库都需要学习一套新的api,是用JDBC后,各个数据库厂商根据JDBC中的方法声明写方法的实现,对应Java程序员来说只需要掌握JDBC中方法的调用就能通过一套代码访问任何数据库。
如何使用JDBC
-
创建一个Maven工程
-
下载MySQL驱动(JDBC接口的实现类,就是一个jar包)
- 在pom.xml文件中添加以下内容
mysql
mysql-connector-java
5.1.6
- 可以从 maven.tedu.cn 网站中搜索mysql 找到指定的版本坐标
-
创建Demo01.Java类 在main方法中写以下内容
//1. 注册驱动 告诉虚拟机使用的是哪个数据库软件 Class.forName("com.mysql.jdbc.Driver"); //2. 获取连接对象 Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/newdb3", "root", "root"); //3. 创建SQL执行对象 Statement stat = conn.createStatement(); //4. 执行SQL语句 String sql = "create table jdbct1" + "(id int primary key,name varchar(10))"; stat.execute(sql); System.out.println("执行完成!"); //5. 关闭资源 conn.close();
如果电脑上安装的MySQL版本是8.0以上的版本 有以下几点需要注意
- 下载jar包时就不能是5.1.6的版本了 必须是8.0以上版本
- 驱动类的地址 com.mysql.cj.jdbc.Driver
- 连接地址用以下写法:
jdbc:mysql://localhost:3306/newdb3?useSSL=false&serverTimezone=Asia/Shanghai
###Statement执行SQL语句的对象
- execute(sql); 此方法用于执行数据库相关和表相关的SQL语句, 返回值是一个布尔值,代表是否有结果集(了解)
- executeUpdate(sql); 用于执行增删改的相关SQL,返回值是int 代表生效行数
- ResultSet rs = executeQuery(sql); 用于执行查询的SQL,返回值是结果集,里面装着查询回来的所有数据