MySQL及JDBC

最新推荐文章于 2022-04-18 08:00:00 发布

Limeye

最新推荐文章于 2022-04-18 08:00:00 发布

阅读量340

点赞数

分类专栏： Java基础文章标签： JDBC MySQL

本文链接：https://blog.csdn.net/Limeye/article/details/84933344

版权

Java基础专栏收录该内容

14 篇文章 0 订阅

订阅专栏

什么是JDBC

Java 数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。

简单地说，就是用于执行SQL语句的一类Java API，通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装，可以使开发人员使用纯Java API完成SQL的执行
　　

今日内容

多表查询

课前练习

现有一张用户表数据：

name tel content date
张三 13333663366 大专毕业 2006-10-11
张三 13612312331 本科毕业 2006-10-15
李四 021-55665566 中专毕业 2006-10-15

1）创建数据库和表结构并添加初始的3条记录

CREATE DATABASE exam;

USE exam;

CREATE TABLE user (
    -- id（唯一标识， 自增 + 主键（唯一 + 非空））
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    tel VARCHAR(15) NOT NULL,
    content VARCHAR(10) NOT NULL,
    date DATE NOT NULL
);

INSERT INTO user VALUES(NULL, '张三', '13333663366', '大专毕业', '2006-10-11'),(NULL, '张三', '13612312331', '本科毕业', '2006-10-15'),(NULL, '李四', '021-55665566', '中专毕业', '2006-10-15');

2）添加有一条新记录(小王 13254748547 高中毕业 2007-05-06)至用户表中

INSERT INTO user(name, tel, content, date) VALUES('小王','13254748547','高中毕业','2007-05-06');

3）把李四的时间更新成为当前系统时间

UPDATE user SET date=NOW() WHERE name='李四';

4）删除名为张三的全部记录

DELETE FROM user WHERE name='张三';

5）查询这张表的全部记录，要求按时间升序

SELECT * FROM user ORDER BY date ASC;

五种约束

SQL 约束用于规定表中的数据规则，共有五种约束：

1）*主键约束（primary key）：每个表都应该有主键，此约束的特点是数据唯一，且不能为null

第一种添加方式：
CREATE TABLE student(
    id int primary key,  -- id int auto_increment primary key,
    name varchar(50)
);

第二种添加方式：
CREATE TABLE student(
    id int,
    name varchar(50),
    primary key(id)
);

第三种添加方式：
CREATE TABLE student(
    id int,
    name varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (id);
注：主键删除语句为ALTER TABLE student DROP PRIMARY KEY;

扩展：还有一种联合主键，它由多个字段组成！

2）=唯一约束（unique）*：字段内数据不能重复

CREATE TABLE student(
    id int primary key,
    name varchar(50) unique
);

3）*非空约束（not null）：字段数据不能为空

CREATE TABLE student(
    id int primary key,
    name varchar(50) not null,
    sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);

4）默认值约束（default）：使用预先设定的默认值

CREATE TABLE student(
    id int primary key,
    name varchar(50) not null,
    sex varchar(10) default '男'
);
insert into student values(1,'tom','女');
insert into student values(2,'jerry',default);

5）外键约束（foreign key）：用于指定与其它表的关联

CREATE TABLE student(
    sid int primary key,
    name varchar(50) not null,
    sex varchar(10) default '男'
);
create table score(
    id int,
    score int,
    sid int, -- 外键列的数据类型一定要与主键的类型一致
    CONSTRAINT fk_score_sid foreign key(sid) references student(id)
);
第二种添加外键方式。
ALTER TABLE score ADD CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(id);
注：外键删除语句为alter table score drop foreign key fk_score_sid;

在实际开发中，不建议给关联字段添加外键约束，原因：
https://www.zhihu.com/question/19600081

6）检查约束（check）：保证列中的值符合指定的条件，MySQL不支持。

7）主键自增（auto_increment）：仅仅针对于int类型！一般建议在表中都添加这样一个唯一标识的id字段：

id INT AUTO_INCREMENT PRIMARY KEY

关联表设计的三种关系

3.1. 一对一

一对一，如一个人有一个身份证。示例如下：

  CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(20) NOT NULL, 
    age INT 
  ); 
  INSERT INTO user VALUES(NULL, ‘zs’, 18); – id:1 
  CREATE TABLE idcard (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    idcard_no VARCHAR(18) NOT NULL, 
    address VARCHAR(100) NOT NULL, 
    user_id INT NOT NULL – 通过此外键来建立与user表的关系！
  ); 
  INSERT INTO idcard VALUES(NULL, ‘500228199999999’, ‘xxxx地址’, 1); – id:1

一对一建表原则：可选在任意一方建立外键！
在实际的开发中应用不多，因为一对一一般都可以创建成一张表。

3.2. 一对多

<li>一对多关系，如：客户和订单，分类和商品，部门和员工。示例如下： 
  CREATE TABLE user ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(20) NOT NULL,
    age INT <br>
  ); 
  CREATE TABLE orders ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(20) NOT NULL, 
    order_time DATETIME NOT NULL, 
    user_id INT NOT NULL– 一对多的情况下，我们都是在多的一方建立外键！ 
  ); 
  INSERT INTO orders VALUES(NULL, ‘订单1’, NOW(), 1); 
  INSERT INTO orders VALUES(NULL, ‘订单2’, NOW(), 1); 
  INSERT INTO orders VALUES(NULL, ‘订单3’, NOW(), 1);

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键。

3.3. 多对多

多对多关系，如：学生和课程。示例如下：

 CREATE TABLE user ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(20) NOT NULL, 
    age INT 
  ); 
  INSERT INTO user VALUES(NULL, ‘zs’, 18); – id:1 
  INSERT INTO user VALUES(NULL, ‘ls’, 20); – id:2

<p>CREATE TABLE course ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(20) NOT NULL, 
    teacher VARCHAR(20) 
  ); 
  INSERT INTO course VALUES(NULL, ‘JAVA程序设计’, “张老师”); – id:1 
  INSERT INTO course VALUES(NULL, ‘JAVA WEB开发’, “杨老师”); – id:2 
  INSERT INTO course VALUES(NULL, ‘ANDROID 游戏开发’, “杨老师”); – id:3

– 选课信息表

CREATE TABLE user_course ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL, – 指向学生表的id 
    course_id INT NOT NULL, – 指向课程表的id
    choice_time DATETIME
  ); 
  INSERT INTO user_course VALUES(NULL, 2, 1, ‘2010-09-01’); – id:1 
  INSERT INTO user_course VALUES(NULL, 2, 2, ‘2010-09-11’); – id:2 
  INSERT INTO user_course VALUES(NULL, 2, 3, ‘2010-09-21’); – id:3 
  INSERT INTO user_course VALUES(NULL, 1, 3, ‘2010-09-01’); – id:4

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

单表查询

5.1. 单表查询练习1

-- 创建表teacher
CREATE TABLE teacher (
    -- 注意char不能添加auto_increment
    -- char和varchar都是表示字段类型为字符串，如果字段的长度固定，char的性能高于varchar！
    id CHAR(6) PRIMARY KEY, 
    name VARCHAR(50) NOT NULL,
    age INT,
    gender VARCHAR(50)
);

-- 添加数据
INSERT INTO teacher VALUES('1001', 'liuYi', 35, 'male');
INSERT INTO teacher VALUES('1002', 'chenEr', 15, 'female');
INSERT INTO teacher VALUES('1003', 'zhangSan', 95, 'male');
INSERT INTO teacher VALUES('1004', 'liSi', 65, 'female');
INSERT INTO teacher VALUES('1005', 'wangWu', 55, 'male');
INSERT INTO teacher VALUES('1006', 'zhaoLiu', 75, 'female');
INSERT INTO teacher VALUES('1007', 'sunQi', 25, 'male');
INSERT INTO teacher VALUES('1008', 'zhouBa', 45, 'female');
INSERT INTO teacher VALUES('1009', 'wuJiu', 85, 'male');
INSERT INTO teacher VALUES('1010', 'zhengShi', 5, 'female');
INSERT INTO teacher VALUES('1011', 'xxx', NULL, NULL);

-- 完成如下条件查询：

-- 1)查询性别为女，并且年龄50的记录？
SELECT * FROM teacher WHERE gender='female' AND age=50;

-- 2)查询工号为1001，或者姓名为liSi的记录？
SELECT * FROM teacher WHERE id='1001' OR name='liSi';

-- 3)查询工号为1001，1002，1003的记录？
SELECT * FROM teacher WHERE id='1001' OR id='1002' OR id='1003';
SELECT * FROM teacher WHERE id IN('1001', '1002', '1003');

-- 4)查询工号不是1001，1002，1003的记录？
SELECT * FROM teacher WHERE id!='1001' AND id!='1002' AND id!='1003';
SELECT * FROM teacher WHERE id NOT IN('1001', '1002', '1003');

-- 5)查询年龄为null的记录？
SELECT * FROM teacher WHERE age IS NULL;

-- 6)查询年龄在20到40之间的记录？
SELECT * FROM teacher WHERE age >= 20 AND age <= 40;
SELECT * FROM teacher WHERE age BETWEEN 20 AND 40;

-- 7)查询性别非男的记录？
SELECT * FROM teacher WHERE gender != 'male';

-- 8)查询姓名不为null的记录？
SELECT * FROM teacher WHERE name IS NOT NULL;

-- 完成如下模糊查询：
-- 1)查询姓名由5个字符构成的记录？
SELECT * FROM teacher WHERE name LIKE '_____';
SELECT * FROM teacher WHERE LENGTH(name) = 5;

-- 2)查询姓名以“z”开头的记录？
SELECT * FROM teacher WHERE name LIKE 'z%';

-- 3)查询姓名中第2个字母为“i”的记录？
SELECT * FROM teacher WHERE name LIKE '_i%';

-- 4)查询姓名中包含“a”字母的记录？
SELECT * FROM teacher WHERE name LIKE '%a%';

-- 其它查询

-- 1）查询teacher表中有几种性别？
SELECT DISTINCT gender FROM teacher;

-- 注意：COUNT(DISTINCT gender)会把NULL记录排除！
SELECT COUNT(DISTINCT gender) FROM teacher;

-- 2）把性别为女的老师按年龄降序输出？
SELECT * FROM teacher WHERE gender='female' ORDER BY age DESC;

-- 3）统计年龄在20~60的区间内有多少个男老师？
SELECT COUNT(*) AS '20~60岁区间的男老师总数' FROM teacher WHERE gender='male' AND (age BETWEEN 20 AND 60);

-- 4）按性别进行分组，统计各组有多少人？      
select gender, count(*) from teacher group by gender;
select gender, count(*), max(age), min(age), avg(age), sum(age) from teacher group by gender;

-- 5）按性别进行分组，把超过5人小组给输出出来？
select gender, count(*) as total from teacher group by gender having count(*) > 5;
select gender, count(*) total from teacher group by gender having total > 5;

5.2. 单表查询练习2

-- 创建雇员表
CREATE TABLE emp(
    empno INT PRIMARY KEY, -- 编号需要自己指定，因此这里也没有用auto_increment
    ename VARCHAR(50) NOT NULL,
    job VARCHAR(50) NOT NULL,
    mgr INT,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2) NOT NULL,
    comm decimal(7,2),
    deptno INT NOT NULL
) ;
-- 添加数据
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 完成如下字段控制查询：

-- 1）使用DISTINCT查询去除重复后的记录，如emp表中sal字段就存在相同的记录？
SELECT DISTINCT sal FROM emp;

-- 2）查看雇员的月薪与佣金之和，并把结果列名添加别名？
SELECT sal + IFNULL(comm, 0) '月薪与佣金之和' FROM emp;

-- 3）聚合函数测试：

-- a）查询emp表中记录数？
SELECT COUNT(*) FROM emp;

-- b）查询emp表中有佣金的人数？
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL AND comm != 0.0;

-- c）查询emp表中月薪大于2500的人数？
SELECT COUNT(*) FROM emp WHERE sal > 2500;

-- d）统计月薪与佣金之和大于2500元的人数？
SELECT COUNT(*) FROM emp WHERE sal + IFNULL(comm, 0) > 2500;

-- e）查询有佣金的人数，以及有领导的人数？
SELECT COUNT(*) FROM emp where (comm IS NOT NULL AND comm != 0.0) AND mgr IS NOT NULL;

-- f）查询所有雇员月薪和？
SELECT SUM(sal) FROM emp;

-- g）查询所有雇员月薪和，以及所有雇员佣金和？
SELECT SUM(sal), SUM(comm) FROM emp;

-- h）查询所有雇员月薪+佣金和？
SELECT SUM(sal) + SUM(comm) FROM emp;

-- i）统计所有员工平均工资？
SELECT AVG(sal + IFNULL(comm, 0)) FROM emp;

-- j）查询最高工资和最低工资？
SELECT MAX(sal), MIN(sal) FROM emp;

-- 完成如下分组查询：
-- 1）查询每个部门的部门编号和每个部门的工资和？
SELECT deptno, SUM(sal + IFNULL(comm, 0)) FROM emp GROUP BY deptno;

-- 2）查询每个部门的部门编号以及每个部门的人数？
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

-- 3）查询每个部门的部门编号以及每个部门工资总和大于1500的人数？
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING SUM(sal + IFNULL(comm, 0)) > 1500;

-- 4）查询每个部门的部门编号以及每个部门的员工工资大于1500的人数？
SELECT deptno, COUNT(*) FROM emp WHERE (sal + IFNULL(comm, 0)) > 1500 GROUP BY deptno;

-- 5）查询工资总和大于9000的部门编号以及工资和？
SELECT deptno, SUM(sal + IFNULL(comm, 0)) AS sum FROM emp GROUP BY deptno HAVING sum > 9000;

-- 完成如下排序查询：
-- 1）查询所有雇员信息，按月薪降序？
SELECT * FROM emp ORDER BY sal DESC;

-- 2）查询所有雇员信息，按月薪降序，如果月薪相同，再按佣金升序？
SELECT * FROM emp ORDER BY sal DESC, comm ASC;      

-- 完成如下LIMIT限定查询：
-- 1）从雇员表中查询满足条件（条件可自定）的1条记录？
SELECT * FROM emp ORDER BY sal DESC LIMIT 1;

-- 2）从雇员表中查询5行记录，起始行从0开始？
SELECT * FROM emp LIMIT 0, 5;

-- 3）从雇员表中查询10行记录，起始行从3开始？
SELECT * FROM emp LIMIT 3, 10;

-- 4）从雇员表中分页查询（如果一页记录为5条，希望查看第3页记录应该怎么查呢？）
-- 第1页：SELECT * FROM emp LIMIT 0, 5;
-- 第2页：SELECT * FROM emp LIMIT 5, 5;
-- 第3页：SELECT * FROM emp LIMIT 10, 5;

-- 其它查询
-- 1）找出奖金高于工资的雇员 
-- 2）找出奖金高于工资60%的雇员
-- 3）找出部门10中所有经理和部门20中所有店员的信息
-- 4）找出部门10中所有经理(MANAGER)，部门20中所有店员，既不是经理又不是店员但其5.薪资大于或等于2000的所有员工的信息。
-- 6）查询没有奖金或者奖金低于100的员工信息
-- 7）查询姓名不带”R”的员工姓名
-- 8）显示员工的姓名和入职时间，根据入职时间，将最老的员工排放在最前面。
-- 9）显示所有员工的姓名、工作和工资，按照工作的降序排序，若工作相同则按工资升序排序。

5.3. 单表查询练习3

 设有成绩表如下所示
 编号    姓名    科目    分数
  1      张三    数学    90
  2      张三    语文    50
  3      张三    地理    40
  4      李四    语文    55
  5      李四    政治    45
  6      王五    政治    30
  7      李四    数学    80
  8      王五    语文    70
 根据以下要求完成查询：
 CREATE DATABASE user;
 USE user;
 CREATE TABLE student(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    subject VARCHAR(20) NOT NULL,
    score INT(3) NOT NULL
 );
 INSERT INTO student VALUES(NULL,'张三','数学',90);
 INSERT INTO student VALUES(NULL,'张三','语文',50);
 INSERT INTO student VALUES(NULL,'张三','地理',40);
 INSERT INTO student VALUES(NULL,'李四','语文',55);
 INSERT INTO student VALUES(NULL,'李四','政治',45);
 INSERT INTO student VALUES(NULL,'王五','政治',30);
 INSERT INTO student VALUES(NULL,'李四','数学',80);
 INSERT INTO student VALUES(NULL,'王五','语文',70);

 1）查询两门及两门以上不及格的学生姓名
 2）查询学生平均分  
 3）查询姓名是张三的学生并将学生信息按照分数倒序
 4）获取学生信息中分数最低的学生姓名和分数最高的学生姓名
 5）查询两门及两门以上不及格同学的平均分。

5.4. 单表查询的所有形式

查询所有的字段
```
SELECT * FROM company;
```

查询部分的字段

SELECT name FROM company; 
SELECT name,gender,company_id FROM position;

带条件的查询（>、<、>=、<=、=、<>/!=、like、null、IN）
- 比较运算符
- >、<、>=、<=、=、<>/!=得到的是一个boolean值，如果为true就表示条件匹配
- =、<>/!=适用于数值和字符串类型
- 所有比较都适用于时间类型
- like匹配/模糊匹配，会与%和_结合使用
- 查询以java开头的所有职位信息？
```
  SELECT * FROM position WHERE name LIKE ‘java%’;
```
查询含有“开发”关键词的所有职位信息？
```
SELECT * FROM position WHERE name LIKE ‘%开发%’;
```
查询第三字符是“开”的所有职位信息？
```
SELECT * FROM position WHERE name LIKE ‘_开%’;
```
null匹配

查询某个字段为null的所有记录？

SELECT * FROM  user WHERE conment IS NULL; 
SELECT * FROM  user WHERE conment IS NOT NULL;

in匹配

SELECT * FROM position WHERE id IN(1,2,3);

等同于

SELECT * FROM position WHERE id=1 OR id=2 OR id=3;

连接多个条件（AND、OR）
- 查询含有“java”并且不限男女的职位信息？
```
SELECT * FROM position WHERE name LIKE ‘%java%’ AND gender=3;
```
统计函数（count、max、min、avg、sum）
- 统计有多少个职位信息？
```
select count(*) from position;
```
等同于
```
select count(id) from position;
```

统计有多少个conment字段不为空的记录？

select count(*) from user where conment is not null;

等同于
```
select count(conment) from user;
```
查询薪水最高的职位？
```
SELECT MAX(salary) FROM position;
```
查询薪水最低的职位？
```
SELECT MIN(salary) FROM position;
```

计算所有java职位薪水的平均值？

SELECT AVG(salary) FROM position WHERE name LIKE ‘%java%’;

计算所有非java职位薪水的总和？

SELECT sum(salary) FROM position WHERE name NOT LIKE ‘%java%’;

LIMIT
- 获取满足条件的1个记录
```
SELECT * FROM position LIMIT 1;
```

分页查询（每页显示2条）

第1页数据：SELECT * FROM position LIMIT 0,2; 
第2页数据：SELECT * FROM position LIMIT 2,2; 
第3页数据：SELECT * FROM position LIMIT 4,2;

去除重复
- 统计哪些公司发布了职位信息？
```
SELECT DISTINCT company_id FROM position;
```

统计有几家公司发布了职位信息？

SELECT COUNT(DISTINCT company_id) FROM position;

排序
- 把所有的职位信息按薪水降序输出？
- 升序
```
SELECT * FROM position ORDER BY salary; 
```

等同

SELECT * FROM position ORDER BY salary ASC;

降序

SELECT * FROM position ORDER BY salary DESC;

把所有的职位信息按薪水降序输出，如果薪水相同，再按id降序输出？
```
SELECT * FROM position ORDER BY salary DESC, id DESC;
```
把所有含有“java”的职位按薪水升序输出，并分页显示第一页数据（每页2条）？
```
SELECT * FROM position WHERE name LIKE ‘%java%’ ORDER BY salary LIMIT 0, 2;
```
分组
- 统计在position表中，每个公司各自发布了多少个职位？
```
SELECT company_id, count(*) FROM position GROUP BY company_id;
```
统计在position表中，每个公司各自发布的职位的平均薪水？
```
SELECT company_id, AVG(salary) FROM position GROUP BY company_id;
```
统计在position表中，每个公司各自发布了多少个职位超过3个的记录？
```
SELECT company_id, count() FROM position GROUP BY company_id HAVING count3;
```

SELECT的语法结构

SELECT 字段相关信息 
FROM 表名 
WHERE 条件 
GROUP BY 按哪个字段分组 
HAVING 对分组的过滤 
ORDER BY 排序字段 
LIMIT 分页信息

查询语句执行顺序：FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

给查询字段设置别名

SELECT COUNT(DISTINCT company_id) AS ‘总数’ FROM position;

等同于

SELECT COUNT(DISTINCT company_id) ‘总数’ FROM position;

…
1. 多表查询

6.1. 多表查询练习1

参考 3.关联表设计的三种关系章节的相关表完成如下多表查询

连表查询用户信息和身份证信息？
- 内连接查询：只会查询多张表里面匹配的记录
- SQL92标准：
  f
- 左外连接查询：以左表为主表，右表匹配的记录正常显示，不匹配的记录显示为NULL
  SELECT u.name, u.age, i.idcard_no, i.address FROM user u LEFT OUTER JOIN idcard i ON i.user_id=u.id;
连表查询张三的信息和下的订单信息？
SELECT u.name, o.name, o.order_time FROM user u, orders o WHERE u.id=o.user_id AND u.name=’zs’;
连表查询所有学生的选课情况？
SELECT u.name, c.name, uc.choice_time FROM user u, course c, user_course uc WHERE uc.user_id=u.id AND uc.course_id=c.id;
连表查询用户信息（包含身份证信息）和所选课程的信息？
SELECT u.name, c.name, uc.choice_time, i.idcard_no
FROM user u
LEFT OUTER JOIN idcard i
ON i.user_id=u.id
INNER JOIN user_course uc
ON uc.user_id=u.id
INNER JOIN course c
ON uc.course_id=c.id

多表查询的注意点：

前提：多张表之间有关联关系（一对一、一对多、多对多）
一般每个表都设置一个别名，方便输出指定的字段（特别是在多表字段重名的时候）
考虑到查询性能的问题，建议不要直接用*
消除笛卡尔积（不匹配的记录），N张表连接就至少有N-1个消除笛卡尔积条件
多表连接类型选择（内连接、左外连接、右外连接、自连接）

6.2. 多表查询练习2

参考《MySQL作业1.doc》和 hiring.sql完成多表查询

6.3. 多表查询练习3

参考《MySQL作业2.doc》完成多表查询

6.4. 多表查询练习4

-- 创建部门表
CREATE TABLE dept(
    deptno INT, -- 编号需要自己指定，因此这里也没有用auto_increment
    dname varchar(14) NOT NULL,
    loc varchar(13) NOT NULL
);
-- 添加数据
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

-- 1）查询至少有一个员工的所有部门编号，部门姓名，并统计出这些部门的平均工资，最低工资，最高工资？

-- 2）列出SMITH或ALLEN的员工的编号，姓名，部门名称，其领导姓名？
-- 3）列出薪金比SMITH或ALLEN多的员工编号，姓名，部门名称，其领导姓名？
-- 4）列出所有员工的编号，姓名，以及上司的编号，姓名，显示按领导的年工资降序排列？
-- 5）列出受雇日期早于直接领导的编号，姓名，部门名称，部门位置，部门人数？
-- 6）查询雇员表里的第10条到15条的数据？

6.5. 多表查询练习5（扩展）

     创建四张表，分别存储学生信息、课程信息、分数、讲师信息表。
     学生信息表 Student
     字段名    字段类型    字段约束    / 含义
     Sno        Varchar(3)  Not null    / 学员编号
     Sname      Varchar(4)  Not null    / 学员姓名 
     Ssex       Varchar(2)  Not null    / 性别
     Sbirthday  Datetime                / 生日
     Class      Varchar(5)              / 班级号

     CREATE TABLE STUDENT (
        SNO VARCHAR(3) NOT NULL,
        SNAME VARCHAR(4) NOT NULL,
        SSEX VARCHAR(2) NOT NULL,
        SBIRTHDAY DATETIME,
        CLASS VARCHAR(5)
     )

     ____________________________________________________________________


     课程信息表 course
     字段名    字段类型    字段约束    / 含义
     Cno        Varchar(5)  Not null    / 课程编号
     Cname      Varchar(10) Not null    / 课程名称
     Tno        Varchar(10) Not null    / 授课老师编号

     CREATE TABLE COURSE (
        CNO VARCHAR(5) NOT NULL,
        CNAME VARCHAR(10) NOT NULL,
        TNO VARCHAR(10) NOT NULL
     )

     ____________________________________________________________________


    成绩表score
    字段名     字段类型    字段约束    / 含义
    Sno         Varchar(3)  Not null    / 学员编号
    Cno         Varchar(5)  Not null    / 课程编号
    Degree      Double(3,1) Not null    / 分数

    CREATE TABLE SCORE (
        SNO VARCHAR(3) NOT NULL,
        CNO VARCHAR(5) NOT NULL,
        DEGREE NUMERIC(10, 1) NOT NULL
    )

     ____________________________________________________________________


    讲师表teacher
    字段名     字段类型    字段约束    / 含义
    Tno         Varchar(3)  Not null    / 讲师编号
    Tname       Varchar(4)  Not null    / 讲师姓名
    Tsex        Varchar(2)  Not null    / 讲师性别
    Tbirthday   Datetime    Not null    / 出生日期
    Prof        Varchar(6)              / 等级
    Depart      Varchar(10)             / 所属院系

    CREATE TABLE TEACHER (
        TNO VARCHAR(3) NOT NULL,
        TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
        TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
        DEPART VARCHAR(10) NOT NULL
    )

     ____________________________________________________________________


    向表中存储数据
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,19770901,95033);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,19751002,95031);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,19760123,95033);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,19760220,95033);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,19750210,95031);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,19740603,95031);

    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

    INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
    INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
    INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
    VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
    INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
    VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');


    1）查询Student表中的所有记录的Sname、Ssex和Class列。
        select * from student;
    2）查询教师所有的单位即不重复的Depart列。
        select distinct depart from teacher;
    3）查询Student表的所有记录。
        select sname,ssex,class from student;
    4）查询Score表中成绩在60到80之间的所有记录。
        select * from score where Degree between 60 and 80;
    5）查询Score表中成绩为85，86或88的记录。
        select * from score where Degree in (85,86,88);
    6）查询Student表中“95031”班或性别为“女”的同学记录。
        select * from student where class='95031' or Ssex='女';
    7）以Class降序查询Student表的所有记录。
        select * from student order by class desc ;
    8）以Cno升序、Degree降序查询Score表的所有记录。                                 8
        select * from score  order by Cno asc , Degree desc;
    9）查询“95031”班的学生人数。
    select count(*) from student where class='95031';
    10）查询Score表中的最高分的学生学号和课程号。
    select max(degree), sno, cno from score ;
    11）查询‘3-105’号课程的平均分。
    select avg(degree) from score where cno='3-105';
    12）查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。                    12
    select avg(degree) from score where cno like'3%' and count(cno)>=5;
    13）查询最低分大于70，最高分小于90的Sno列。
    select sno from score where degree between 70 and 90;
    14）查询所有学生的Sname、Cno和Degree列。
    select s.sname,c.cno,c.degree from score c , student s where c.sno=s.sno;
    15）查询所有学生的Sno、Cname和Degree列。
    select c2.cname '课程',c1.sno '学生no',c1.degree '成绩' from score c1 , course c2 where c1.cno=c2.cno;
    16）查询所有学生的Sname、Cname和Degree列。
    内连接：
    select s.sname '学生',c.cname '课程',s1.degree '成绩' from student s,course c, score s1 where s.sno=s1.sno and c.cno=s1.cno;
    左外连接：
    select s.sname '学生',c.cname '课程',s1.degree '成绩' 
    from score s1 
    left  join student s
    on s.sno=s1.sno
    join course c
    on  c.cno=s1.cno  ;

    17）查询“95033”班所选课程的平均分。
    select avg(sc.Degree) from score sc,student s where s.sno= sc.sno and s.class='95033';
    18）假设使用如下命令建立了一个grade表：
        create table grade(low number(3,0),upp number(3),rank char(1));
        insert into grade values(90,100,’A’);
        insert into grade values(80,89,’B’);
        insert into grade values(70,79,’C’);
        insert into grade values(60,69,’D’);
        insert into grade values(0,59,’E’);
        commit;
        现查询所有同学的Sno、Cno和rank列。
    19）查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
    20）查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
    21）查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
    22）查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
    23）查询“张旭“教师任课的学生成绩。
    24）查询选修某课程的同学人数多于5人的教师姓名。
    25）查询95033班和95031班全体学生的记录。
    26）查询存在有85分以上成绩的课程Cno.
    27）查询出“计算机系“教师所教课程的成绩表。
    28）查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
    29）查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
    30）查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
    31）查询所有教师和同学的name、sex和birthday.
    32）查询所有“女”教师和“女”同学的name、sex和birthday.
    33）查询成绩比该课程平均成绩低的同学的成绩表。
    34）查询所有任课教师的Tname和Depart.
    35）查询所有未讲课的教师的Tname和Depart. 
    36）查询至少有2名男生的班号。
    37）查询Student表中不姓“王”的同学记录。
    38）查询Student表中每个学生的姓名和年龄。
    39）查询Student表中最大和最小的Sbirthday日期值。
    40）以班号和年龄从大到小的顺序查询Student表中的全部记录。
    41）查询“男”教师及其所上的课程。
    42）查询最高分同学的Sno、Cno和Degree列。
    43）查询和“李军”同性别的所有同学的Sname.
    44）查询和“李军”同性别并同班的同学Sname.
    45）查询所有选修“计算机导论”课程的“男”同学的成绩表

    相关参考：https://blog.csdn.net/qq_23994787/article/details/77161741

6.5. 多表查询的所有形式

合并结果集
- UNION（去除重复记录）
  SELECT * FROM emp e1 WHERE e1.sal > 2500
  UNION
  SELECT * FROM emp e2 WHERE e2.job = ‘MANAGER’;
- UNION ALL（去除重复记录）
  SELECT * FROM emp e1 WHERE e1.sal > 2500
  UNION ALL
  SELECT * FROM emp e2 WHERE e2.job = ‘MANAGER’;
子查询/嵌套查询：一个SELECT语句中包含另一个完整的SELECT语句，子查询有明显的先后顺序
- 显示出FORD的上级领导的姓名和工资？
  SELECT ename,sal FROM emp WHERE empno = (SELECT mgr FROM emp WHERE ename=’FORD’);
- 工资高于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);
  等同于
  SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);
- 查询出emp表中工资最高的员工的名字和工作岗位？
  SELECT ename,job FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
- 查询工资高于平均工资的员工？
  SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
- 查询部门名称里有O的员工信息？
  子查询：
  SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE ‘%O%’);
  多表：
  SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname LIKE ‘%O%’;
- 查询部门名称里有O的员工信息和部门名称？
  把子查询后结果当做临时表：
  SELECT * FROM emp e, (SELECT * FROM dept WHERE dname LIKE ‘%O%’) d WHERE e.deptno=d.deptno;
  多表：
  SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname LIKE ‘%O%’;
连接查询（多表连接查询，可以获取多表中的信息）

内连接（INNER JOIN）：特点是查询结果必须满足条件，这种连接查询是平时最常用的查询。
SQL92标准：SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno;
SQL99标准：SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
多表关联要去除无用的笛卡尔积，一般来讲N张表，就至少会有N-1个消除条件！
获取所有的员工的姓名以及它所在部门信息？
SQL92标准：
SELECT
e.ename, d.*
FROM emp e, dept d
WHERE e.deptno = d.deptno;
SQL99标准：
SELECT
e.ename, d.*
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno;
查询所有学生的选课情况
SQL92标准：
SELECT
u.name 学生姓名, c.name 课程名称, uc.choice_time 选择时间
FROM user u, course c, user_course uc
WHERE uc.user_id=u.id AND uc.course_id=c.id AND u.name=’ls’
ORDER BY uc.choice_time DESC;
SQL99标准：
SELECT
u.name 学生姓名, c.name 课程名称, uc.choice_time 选择时间
FROM user u
INNER JOIN course c
INNER JOIN user_course uc
ON uc.user_id=u.id AND uc.course_id=c.id
WHERE u.name=’ls’
ORDER BY uc.choice_time DESC;
外连接包含左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）：查询的结果存在不满足条件的可能。
左外连接：SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
右外连接：SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
全外连接（MySQL不支持！）
全外连接就是左外连接和右外连接的综合，它除了返回内连接匹配的数据外，也将返回两个表中不匹配的数据！如：SELECT e.ename,d.dname FROM emp e FULL JOIN dept d ON e.deptno=d.deptno;
自连接
所谓自连接，就是把自身表的一个引用作为另一个表来处理，自己连接自己。
显示公司每个员工的名字和他上级的名字？
SELECT worker.ename,boss.ename FROM emp worker,emp boss WHERE worker.mgr=boss.empno;
一个只有 name的表，有name分别是a b c d四个球队，任意两个球队打比赛，一共会有几场比赛？
额外条件：
1）不能与自已球队组合：a队不能跟a队打
2）单向组合，如a队与b队打后，b队不能再与a队打
select a.name, b.name from test a,test b where a.name < b.name

SQL优化

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
in 和 not in 也要慎用，否则会导致全表扫描。
应尽量避免在 where 子句中对字段进行表达式操作。
应尽量避免在where子句中对字段进行函数操作。
很多时候用 exists 代替 in 是一个好的选择。
尽量使用数字型字段。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

Java通过JDBC连接MySQL数据库

第一步，创建数据库，利用Navicat数据库可视化工具随便建立一个数据库，在库中建立一张表，表里给几个字段（记得给个id字段，唯一主键，自增序列），再随便给上两条数据便好，用来测试功能，如图：

第二步，打通数据库（这个例子希望大家自己动手敲敲，耽误不了多少时间，熟悉一下jdbc如何和数据库打交道，故以图示之），如图：

第三步，改造DBUtil类，方便在dao层获得数据库的连接，代码如下：

package com.czgo.db;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil
{
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc";
  private static final String UNAME = "root";
  private static final String PWD = "root";

  private static Connection conn = null;

  static
  {
    try
    {
      // 1.加载驱动程序
      Class.forName("com.mysql.jdbc.Driver");
      // 2.获得数据库的连接
      conn = DriverManager.getConnection(URL, UNAME, PWD);
    }
    catch (ClassNotFoundException e)
    {
      e.printStackTrace();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }

  public static Connection getConnection()
  {
    return conn;
  }
}

第四步，创建实体类（如上图，大家观察包的分配，我们将采用MVC思想设计本实例，有关于mvc的设计思想，请大家自行学习，这里不多说）代码如下：

package com.czgo.model;

import java.io.Serializable;

/**
 * 实体类：女神类
 * 
 * @author AlanLee
 * 
 */
public class Goddess implements Serializable
{
  private static final long serialVersionUID = 1L;

  /**
   * 唯一主键
   */
  private Integer id;
  /**
   * 姓名
   */
  private String name;
  /**
   * 手机号码
   */
  private String mobie;
  /**
   * 电子邮件
   */
  private String email;
  /**
   * 家庭住址
   */
  private String address;

  public Integer getId()
  {
    return id;
  }

  public void setId(Integer id)
  {
    this.id = id;
  }

  public String getName()
  {
    return name;
  }

  public void setName(String name)
  {
    this.name = name;
  }

  public String getMobie()
  {
    return mobie;
  }

  public void setMobie(String mobie)
  {
    this.mobie = mobie;
  }

  public String getEmail()
  {
    return email;
  }

  public void setEmail(String email)
  {
    this.email = email;
  }

  public String getAddress()
  {
    return address;
  }

  public void setAddress(String address)
  {
    this.address = address;
  }
}

第五步，dao层的实现（这里由于是小例子没有写dao接口，实际工作中大型项目应该是要写dao接口的，便于程序的维护和扩展），代码如下：

package com.czgo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.czgo.db.DBUtil;
import com.czgo.model.Goddess;

/**
 * 数据层处理类
 * 
 * @author AlanLee
 * 
 */
public class GoddessDao
{
  /**
   * 查询全部女神
   * 
   * @return
   * @throws SQLException
   */
  public List&lt;Goddess&gt; query() throws SQLException
  {
    List&lt;Goddess&gt; goddessList = new ArrayList&lt;Goddess&gt;();

    // 获得数据库连接
    Connection conn = DBUtil.getConnection();

    StringBuilder sb = new StringBuilder();
    sb.append("select id,name,mobie,email,address from goddess");

    // 通过数据库的连接操作数据库，实现增删改查
    PreparedStatement ptmt = conn.prepareStatement(sb.toString());

    ResultSet rs = ptmt.executeQuery();

    Goddess goddess = null;

    while (rs.next())
    {
      goddess = new Goddess();
      goddess.setId(rs.getInt("id"));
      goddess.setName(rs.getString("name"));
      goddess.setMobie(rs.getString("mobie"));
      goddess.setEmail(rs.getString("email"));
      goddess.setAddress(rs.getString("address"));

      goddessList.add(goddess);
    }
    return goddessList;
  }

  /**
   * 查询单个女神
   * 
   * @return
   * @throws SQLException
   */
  public Goddess queryById(Integer id) throws SQLException
  {
    Goddess g = null;

    Connection conn = DBUtil.getConnection();

    String sql = "" + " select * from imooc_goddess " + " where id=&#63; ";

    PreparedStatement ptmt = conn.prepareStatement(sql);

    ptmt.setInt(1, id);

    ResultSet rs = ptmt.executeQuery();

    while (rs.next())
    {
      g = new Goddess();
      g.setId(rs.getInt("id"));
      g.setName(rs.getString("name"));
      g.setMobie(rs.getString("mobie"));
      g.setEmail(rs.getString("email"));
      g.setAddress(rs.getString("address"));
    }

    return g;
  }

  /**
   * 添加女神
   * 
   * @throws SQLException
   */
  public void addGoddess(Goddess goddess) throws SQLException
  {
    // 获得数据库连接
    Connection conn = DBUtil.getConnection();

    String sql = "insert into goddess(name,mobie,email,address) values(&#63;,&#63;,&#63;,&#63;)";

    PreparedStatement ptmt = conn.prepareStatement(sql);

    ptmt.setString(1, goddess.getName());
    ptmt.setString(2, goddess.getMobie());
    ptmt.setString(3, goddess.getEmail());
    ptmt.setString(4, goddess.getAddress());

    ptmt.execute();
  }

  /**
   * 修改女神资料
   * 
   * @throws SQLException
   */
  public void updateGoddess(Goddess goddess) throws SQLException
  {
    Connection conn = DBUtil.getConnection();

    String sql = "update goddess set name=&#63;,mobie=&#63;,email=&#63;,address=&#63; where id=&#63;";

    PreparedStatement ptmt = conn.prepareStatement(sql);

    ptmt.setString(1, goddess.getName());
    ptmt.setString(2, goddess.getMobie());
    ptmt.setString(3, goddess.getEmail());
    ptmt.setString(4, goddess.getAddress());

    ptmt.execute();
  }

  /**
   * 删除女神
   * 
   * @throws SQLException
   */
  public void deleteGoddess(Integer id) throws SQLException
  {
    Connection conn = DBUtil.getConnection();

    String sql = "delete from goddess where id=&#63;";

    PreparedStatement ptmt = conn.prepareStatement(sql);

    ptmt.setInt(1, id);

    ptmt.execute();
  }
}

第六步，控制层的实现（控制层在此处用来模仿控制层和界面，直接在这里构建数据，如果是界面的数据则通过请求传递接收参数即可，控制层的代码大家可以根据实际情况去更改完善，这里只是给大家抛砖引玉，做个简单的测试，时间比较紧，希望大家理解），代码如下：

package com.czgo.action;

import java.sql.SQLException;
import java.util.List;

import com.czgo.dao.GoddessDao;
import com.czgo.model.Goddess;

/**
 * 控制层，直接在这里构建数据，界面的数据则通过请求传递接收即可，亦是同理
 * 
 * @author AlanLee
 * 
 */
public class GoddessAction
{
  /**
   * 新增女神
   * 
   * @param goddess
   * @throws Exception
   */
  public void add(Goddess goddess) throws Exception
  {
    GoddessDao dao = new GoddessDao();
    goddess.setName("苍井空");
    goddess.setMobie("52220000");
    goddess.setEmail("52220000@qq.com");
    goddess.setAddress("北京红灯区");
    dao.addGoddess(goddess);
  }

  /**
   * 查询单个女神
   * 
   * @param id
   * @return
   * @throws SQLException
   */
  public Goddess get(Integer id) throws SQLException
  {
    GoddessDao dao = new GoddessDao();
    return dao.queryById(id);
  }

  /**
   * 修改女神
   * 
   * @param goddess
   * @throws Exception
   */
  public void edit(Goddess goddess) throws Exception
  {
    GoddessDao dao = new GoddessDao();
    dao.updateGoddess(goddess);
  }

  /**
   * 删除女神
   * 
   * @param id
   * @throws SQLException
   */
  public void del(Integer id) throws SQLException
  {
    GoddessDao dao = new GoddessDao();
    dao.deleteGoddess(id);
  }

  /**
   * 查询全部女神
   * 
   * @return
   * @throws Exception
   */
  public List&lt;Goddess&gt; query() throws Exception
  {
    GoddessDao dao = new GoddessDao();
    return dao.query();
  }

  /**
   * 测试是否成功
   * 
   * @param args
   * @throws SQLException
   */
  public static void main(String[] args) throws SQLException
  {
    GoddessDao goddessDao = new GoddessDao();

    List&lt;Goddess&gt; goddessList = goddessDao.query();

    for (Goddess goddess : goddessList)
    {
      System.out.println(goddess.getName() + "," + goddess.getMobie() + "," + goddess.getEmail());
    }
  }
}

最后，让我们看一下main方法的运行结果是否成功：

这样，一个简单的java jdbc 连接mysql数据库实现增删改查便完成了，大家可以在查询的基础上试着去做一个高级查询，也就是多条件查询来巩固jdbc的使用。

Limeye

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL及JDBC

什么是JDBCJava 数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。简单地说，就是用于执行SQL语句的一类Java API，通过JDBC使得我们可以直接使用Java编程...
复制链接

扫一扫