项目开发流程
可行性分析 --- 做还是不做
需求分析 --- 做什么
概要设计
详细设计
UML --- ROSE / StarUML / EA（Enterprise Architect）
数据库设计 --- ER图 PowerDesigner
验证码
例子1:
public final class CommonUtil {
private static final String CHARS = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
private static final int MAX_LEN = CHARS.length();
private static final int MAX_THETA_DEGREE = 25;
private CommonUtil() {
throw new AssertionError();
}
public static int randomInt(int min, int max) {
return (int) (Math.random() * (max - min)) + min;
}
public static Color randomColor() {
int r = randomInt(0, 256);
int g = randomInt(0, 256);
int b = randomInt(0, 256);
return new Color(r, g, b);
}
public static String verifyCodeStr(int len) {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < len; i++) {
char ch = CHARS.charAt(randomInt(0, MAX_LEN));
sb.append(ch);
}
return sb.toString();
}
public static BufferedImage codeStrToImage(String codeStr, int width ,int height) {
BufferedImage image = new BufferedImage(width, height, 1);
Graphics g = image.getGraphics();
g.setColor(Color.WHITE);
g.fillRect(0, 0, width, height);
for (int i = 0; i < 10; i++) {
g.setColor(randomColor());
g.drawLine(randomInt(0, 80), randomInt(0, 30),
randomInt(0, 80), randomInt(0, 30));
}
int size = (width - 20) / codeStr.length();
size = size < height ? size : height;
g.setFont(new Font("Consolas", Font.BOLD, size));
for (int i = 0; i < codeStr.length(); i++) {
char ch = codeStr.charAt(i);
double theta = Math.PI / 180 / (i + 1) *
randomInt(-MAX_THETA_DEGREE, MAX_THETA_DEGREE);
((Graphics2D) g).rotate(theta);
g.setColor(randomColor());
g.drawString(String.valueOf(ch), 10 + i * size, height / 2 + randomInt(4, 10));
((Graphics2D) g).rotate(-theta);
}
return image;
}
}
例子2:
public final class MyUtil {
public static final String CHARS = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
public static final int UNIT_CODE_SIZE = 45;
public static final int MAX_THETA_DEGREE = 15;
private MyUtil() {
throw new AssertionError();
}
public static Image generateImageVC(int len) {
int width = UNIT_CODE_SIZE * len + 20;
int height = UNIT_CODE_SIZE;
BufferedImage image = new BufferedImage(width, height, 1);
Graphics graphics = image.getGraphics();
graphics.setColor(Color.WHITE);
graphics.fillRect(0, 0, width, height);
String vc = generateVC(len);
int size = (int) (UNIT_CODE_SIZE * 0.618);
graphics.setFont(new Font("Arial", Font.PLAIN, size));
for (int i = 0; i < len; i++) {
// 旋转画布后再绘制验证码(歪曲文字增加OCR的难度)
double theta = Math.PI / 180 / (i + 1) *
randomInt(-MAX_THETA_DEGREE, MAX_THETA_DEGREE);
((Graphics2D) graphics).rotate(theta);
// 将画笔设置为随机颜色
graphics.setColor(randomColor());
int x = 20 + i * UNIT_CODE_SIZE;
int y = 10 + (int) (UNIT_CODE_SIZE *0.382);
graphics.drawString(vc.substring(i, i + 1), x, y);
// 旋转后要将画布转回去否则多次的旋转会产生叠加效果
((Graphics2D) graphics).rotate(-theta);
}
return image;
}
public static String generateVC(int len) {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < len; i++) {
int randomIndex = (int) (Math.random() * CHARS.length());
char ch = CHARS.charAt(randomIndex);
sb.append(ch);
}
return sb.toString();
}
public static Color randomColor() {
return new Color(randomInt(0, 255), randomInt(0, 255), randomInt(0, 255));
}
public static int randomInt(int min, int max) {
return (int) (Math.random() * (max - min + 1) + min);
}
public static double randomDouble(double min, double max) {
return Math.random() * (max - min + 1) + min;
}
}
数据库
图片 - blob
photo blob, -- Binary Large Object 二进制大对象
clob -- Character
如果有xxx表就先删除
drop table if exists tb_dept;
外键
- 外键约束（参照完整性）
dno integer,
/constraint fk_emp_dno/ foreign key(dno) references tb_dept(dno)
- 添加外键约束
alter table tb_emp add constraint fk_emp_dno foreign key (dno) references tb_dept
(dno) on update cascade on delete set null;
查询
- 查询所有行所有列
select * from tb_dept;
select * from tb_emp;
- 投影
select ename, job, sal from tb_emp;
- 别名 as 可以省略
select ename as 姓名, job as 职位, sal as 月薪
from tb_emp;
select ename 姓名, job 职位, sal 月薪
from tb_emp;
- 筛选
select ename, job, sal from tb_emp
where sal>=5000;
select ename, job, sal from tb_emp
where sal>=5000 or sal<=2000;
select ename, job, sal from tb_emp
where sal>=3000 and sal<=5000;
select ename, job, sal from tb_emp
where sal between 3000 and 5000;
- 模糊
select ename, job, sal from tb_emp
where ename like '张%'; -- %: 0个或任意多个字符
select ename, job, sal from tb_emp
where ename like '%张%';
select ename, job, sal from tb_emp
where ename like '张_'; -- _: 精确匹配一个字符
- 排序
select ename, job, sal from tb_emp
where ename like '张%' order by sal; -- 默认升序asc
select ename, job, sal from tb_emp
where ename like '张%' order by sal desc;
- 连接
- 自然连接
select ename, job, sal, dname
from tb_emp as t1, tb_dept as t2
where t1.dno=t2.dno and ename like '张%';
- 内连接 先写连表条件，后写筛选条件
select ename, job, sal, dname
from tb_emp as t1 inner join tb_dept as t2
on t1.dno=t2.dno;
- 分组查询/聚合函数
select max(sal) as maxSal from tb_emp;
select min(sal) as minSal from tb_emp;
select avg(sal) as avgSal from tb_emp;
select sum(sal) as sumSal from tb_emp;
select count(sal) as total from tb_emp;
select dno, avg(sal) from tb_emp
group by dno;
- 子查询 把一个查询的结果拿来作为另一个查询的条件和结果来使用
select dname, total from
(select dno, count(dno) as total from tb_emp
group by dno) as t1 inner join tb_dept as t2
on t1.dno=t2.dno;
select ename, job, sal from tb_emp
where sal=(select max(sal) from tb_emp);
- 分页
select * from tb_emp
order by sal desc limit 0,5;
select * from tb_emp
order by sal desc limit 5,5;
select * from tb_emp
order by sal desc limit 10,5;
作业
- 4.求挣最高薪水的员工（BOSS除外）的姓名
select ename, max(sal) from tb_emp where job<>'总裁';
- 5.查询薪水超过平均薪水的员工的姓名和工资
select ename, sal from tb_emp as t1,
(select avg(sal) as avgSal from tb_emp) as t2
where t1.sal>=t2.avgSal;
- 6.查询薪水超过其所在部门平均薪水的员工的姓名和工资
select ename, sal, avgSal from
(select dno, avg(sal) as avgSal from tb_emp group by dno) as t1,
tb_emp as t2
where t1.dno=t2.dno and t2.sal>t1.avgSal;
- 7.查询部门中薪水最高的人姓名、工资和所在部门名称
select ename, sal, dname from
(select dname, maxSal from
(select dno, max(sal) as maxSal from tb_emp group by dno) as t1 inner join tb_dept as t2
on t1.dno=t2.dno) as t3, tb_emp as t4
where t3.maxSal=t4.sal;
- 8.哪些人是主管
select ename from
(select mgr from tb_emp group by mgr) as t1, tb_emp as t2
where t1.mgr=t2.eno;
