mybatis实现一对多连接查询 -
- ITeye博客
博客分类：
问题：两个对象User和Score，它们之间的关系为一对多。
底层数据库为postgresql，ORM框架为mybatis。
关键代码如下：
mybatis配置文件如下：
mybatis.xml文件内容为：
&?xml version="1.0" encoding="UTF-8" ?&
&!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd"&
&configuration&
&settings&
&setting name="cacheEnabled" value="true" /&
&setting name="lazyLoadingEnabled" value="true" /&
&setting name="multipleResultSetsEnabled" value="true" /&
&setting name="useColumnLabel" value="true" /&
&setting name="useGeneratedKeys" value="true" /&
&setting name="defaultExecutorType" value="SIMPLE" /&
&setting name="defaultStatementTimeout" value="25000" /&
&/settings&
&typeAliases&
&typeAlias type="com.mybatis.domain.User" alias="User" /&
&typeAlias type="com.mybatis.domain.Score" alias="Score" /&
&/typeAliases&
&environments default="development"&
&environment id="development"&
&transactionManager type="JDBC" /&
&dataSource type="POOLED"&
&property name="driver" value="org.postgresql.Driver" /&
&property name="url" value="jdbc:postgresql://localhost:5432/mybatis" /&
&property name="username" value="postgres" /&
&property name="password" value="admin" /&
&/dataSource&
&/environment&
&/environments&
&mapper resource="com/mybatis/domain/User.xml" /&
&mapper resource="com/mybatis/domain/Score.xml" /&
&/mappers&
&/configuration&
User.java代码为：
package com.mybatis.
public class User {
private I//用户id
private S//用户名
private S//密码
private S//地址
public User(){
public User(String username,String password,String address){
this.username =
this.password =
this.address =
public User(Integer id,String username,String password,String address){
this.username =
this.password =
this.address =
public int getId() {
public void setId(Integer id) {
public String getUsername() {
public void setUsername(String username) {
this.username =
public String getPassword() {
public void setPassword(String password) {
this.password =
public String getAddress() {
public void setAddress(String address) {
this.address =
public String toString(){
return "当前用户为：id = "+id+",username = "+username+",password = "+password+",address = "+
Score.java代码如下：
package com.mybatis.
public class Score {
private I//主键id
private U//所属用户
//数学成绩
//语文成绩
//英语成绩
p//计算机成绩
public Score(){
public Score(User user, int math,int chinese,int english,int computer){
this.user =
this.math =
this.chinese =
this.english =
public Integer getId() {
public void setId(Integer id) {
public User getUser() {
public void setUser(User user) {
this.user =
public int getMath() {
public void setMath(int math) {
this.math =
public int getChinese() {
public void setChinese(int chinese) {
this.chinese =
public int getEnglish() {
public void setEnglish(int english) {
this.english =
public int getComputer() {
public void setComputer(int computer) {
public String toString(){
return "id = "+ this.id+",math = "+this.math+",chinese = "+this.chinese+",english = "+this.english+",computer = "+puter+
", userid = "+this.user.getId()+",username = "+this.user.getUsername()+",password = "+this.user.getPassword()+
",address = "+this.user.getAddress();
user.xml中的关键代码为：
&resultMap type="User" id="userResult"&
&id property="id" column="userid"/&
&result property="username" column="username"/&
&result property="password" column="password"/&
&result property="address" column="address"/&
&/resultMap&
这里的对象的属性id对应的数据库表列名为userid，这是user对象为pg_score表中
score.xml代码如下：
&?xml version="1.0" encoding="UTF-8" ?&
&!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"&
&mapper namespace="ScoreDaoMapping"&
&resultMap type="Score" id="score"&
&constructor&
&idArg column="id" javaType="int" /&
&arg column="userid" javaType="int" /&
&arg column="math" javaType="int" /&
&arg column="chinese" javaType="int" /&
&arg column="english" javaType="int" /&
&arg column="computer" javaType="int" /&
&/constructor&
&/resultMap&
&resultMap id="joinSelectScore" type="Score" &
&id property="id" column="id"/&
&result property="math" column="math"/&
&result property="chinese" column="chinese"/&
&result property="english" column="english"/&
&result property="computer" column="computer"/&
&association property="user" column="userid" javaType="User" resultMap="UserDaoMapping.userResult"/&
&/resultMap&
&insert id="insertScore" parameterType="Score"&
insert into pg_score(math,chinese,english,computer,userid) values(#{math},#{chinese},#{english},#{computer},#{user.id})
&select id="findScoreByUser" resultMap="joinSelectScore" resultType="list" parameterType="map"&
s.id as id,
s.math as math,
s.chinese as chinese,
s.english as english,
s.computer as computer,
u.id as userid,
u.username as username,
u.password as password,
u.address as address
from pg_score s left outer join pg_userinfo u on s.userid = u.id where u.id=#{userid}
ScoreDao.java中的关键代码为：
String resource = "com/mybatis/configuration/mybatis.xml";
public List&Score& selectScoreByUser(User user) throws IOException{
Reader reader = Resources.getResourceAsReader(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = ssf.openSession();
reader.close();
Map&String,Integer& params = new HashMap&String,Integer&();
params.put("userid", user.getId());
List&Score& scoreList = session.selectList("ScoreDaoMapping.findScoreByUser", params);
session.close();
return scoreL
ScoreService.java代码如下：
package com.mybatis.
import java.io.IOE
import java.util.L
import com.mybatis.dao.ScoreD
import com.mybatis.domain.S
import com.mybatis.domain.U
public class ScoreService {
private ScoreDao scoreDao = new ScoreDao();
public ScoreDao getScoreDao() {
return scoreD
public void setScoreDao(ScoreDao scoreDao) {
this.scoreDao = scoreD
public List&Score& getScoreByUser(User user) throws IOException{
return scoreDao.selectScoreByUser(user);
public void insertScore(Score score) throws IOException{
scoreDao.insert(score);
Test.java代码如下：
package com.mybatis.
import java.util.L
import com.mybatis.domain.S
import com.mybatis.domain.U
import com.mybatis.service.ScoreS
import com.mybatis.service.UserS
public class Test {
private UserService userService = new UserService();
private ScoreService scoreSerice = new ScoreService();
public static void main(String[] args) throws Exception{
Test test = new Test();
//test.insertScore();
List&Score& scoreList = test.getScore();
Score score =
for(int i=0;i&scoreList.size();i++){
System.out.println("第"+(i+1)+"个score对象为：");
score = scoreList.get(i);
System.out.println(score);
public void insertScore() throws Exception{
List&User& userList = userService.getPageUsers(10, 0);
User user = userList.get(2);
Score score = new Score();
score.setUser(user);
score.setChinese(80);
score.setComputer(90);
score.setEnglish(91);
score.setMath(98);
scoreSerice.insertScore(score);
public List&Score& getScore() throws Exception{
List&User& userList = userService.getPageUsers(10, 0);
User user = userList.get(0);
List&Score& scoreList = scoreSerice.getScoreByUser(user);
return scoreL
/* public User getUserById(int id) throws Exception{
return userService.getUserById(id);
数据库表记录为：
运行结果如下：
第1个score对象为：
id = 1,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0
第2个score对象为：
id = 2,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0
第3个score对象为：
id = 4,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0
浏览 45601
yangjianzhouctgu
浏览: 220844 次
来自: 上海
Neoman 写道hi，我看你引入了kendo.web.min ...
hi，我看你引入了kendo.web.min.js 这个js， ...
llscp 写道这是JS吧...对的呀
这是JS吧...一、一对多
以班级Classes和学生Student为例：
回忆sql语句:
//内链接,两种方式效果一样,查询的是两边都有的数据
SELECT c.*,s.* FROM classes c,student s WHERE s.cid=c.ame,s.sname FROM classes c INNER JOIN student s ON s.cid=c.
//左外连接，在内链接基础上，左边表有而右边表没有，两种方式等效；SELECT c.* ,s.* FROM student s LEFT OUTER JOIN classes c ON s.cid=c.SELECT c.* ,s.* FROM student s LEFT& JOIN classes c ON s.cid=c.//右外连接，在内链接基础上，右边有而左边无，两种方式等效；SELECT c.* ,s.* FROM classes c RIGHT& OUTER JOIN student s ON s.cid=c.SELECT c.* ,s.* FROM classes c RIGHT& JOIN student s ON s.cid=c.
//查询所有：
from Classes c,Student s where c.cid=s.classes.cid；
//选择某些属性查询
ame,s.sname from Classes c,Student s where c.cid=s.classes.cid；
//选择某些属性，封装为bean查询；
select new cn.itheima03.hibernate.domain.ame,s.sname) &from Classes c,Student s where c.cid=s.classes.cid；
//内链接查询，得到的是两个bean
from Classes c inner join c.students s；
//内敛链接查询，得到的是Classes对象，对象中包含studet集合
from Classes c inner join fetch c.students s；
from Student s inner join fetch s.classes c；
select new cn.itheima03.hibernate.domain.ame,s.sname)&&from Student s inner join& s.classes c ；
from Classes c left outer join fetch c.students s；
from Student s left outer join fetch s.classes；
示例代码：
&&&&& * 1.一对多
&&&&& * sql:select c.*,s.* from classes c,student s where c.cid=s.
&&&&& * hql:from Classes c,Student s where c.cid=s.classes.cid，注意与上句的区别；
&&&&& * 得到的list是object[]，数组中的元素是Classes和Student对象；
&&&&&@Test
&&&&&public&void&testOneToMany_EQ(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&& Query query = session.createQuery(&"from Classes c,Student s where c.cid=s.classes.cid");
&&&&&&&&&List&list&= query.list();
&&&&&&&& System.&out.println(query.list().size());
&&&&&&&& session.close();
&&&&& * 2.带属性的查询；
&&&&& * list中装的是object[];
&&&&&@Test
&&&&&public&void&testOneToMany_EQ_Property(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&& Query query = session.createQuery(&"ame,s.sname from Classes c,Student s where c.cid=s.classes.cid");
&&&&&&&& query.list();
&&&&&&&& session.close();
&&&&& * 3.带属性查询，将查询结果封装成一个bean；
&&&&& * 得到的list中装的是classView对象；
&&&&&@Test
&&&&&public&void&testOneToMany_EQ_Property_Constructor(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&& Query query = session.createQuery(&"select new cn.itheima03.hibernate.domain.ame,s.sname) "&+
&&&&&&&& &&&&&&&"from Classes c,Student s where c.cid=s.classes.cid");
&&&&&&&&&List&list&= query.list();
&&&&&&&& session.close();
&&&&& * 4.内连接
&&&&& * 结果与例子1一样；
&&&&&@Test
&&&&&public&void&testOneToMany_InnerJoin_Query(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Classes c inner join c.students s");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& query.list();
&&&&&&&& session.close();
&&&&& * 5.迫切内连接1：获取所有有学生的班级及班级下的学生；
&&&&& * 要想得到的集合中装的Classes对象，对象中set集合中装student，可以使用迫切内链接。
&&&&&@Test
&&&&&public&void&testOneToMany_InnerJoin_Fetch_Query_1(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Classes c inner join fetch c.students s");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&&&List&list&= query.list();
&&&&&&&& session.close();
&&&&& * 6.迫切内连接2
&&&&& * 从学生端出发；
&&&&&@Test
&&&&&public&void&testOneToMany_InnerJoin_Fetch_Query_2(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Student s inner join fetch s.classes c");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& query.list();
&&&&&&&& session.close();
&&&&& * 7.迫切内连接3：获取属性，封装结果；
&&&&& * select new cn.itheima03.hibernate.domain.ame,s.sname)
&&&&& * &&from Student s inner join fetch s.classes c；
&&&&& * 上述的&hql语句会报错，因为from后面想要的结构和select想要的结构是冲突的，所以
&& &&&&&&&如果在from后面加fetch,不能写select语句，如果加select，不能写fetch,两者只能选择其一
&&&&&@Test
&&&&&public&void&testOneToMany_InnerJoin_Fetch_Query_Property(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&&//下面的写法不对；
//&&&&&&&&buffer.append("select new cn.itheima03.hibernate.domain.ame,s.sname) " +
//&&&&&&&&&&&&&&&&&" from Student s inner join fetch s.classes c");
&&&&&&&&&&
&&&&&&&&&&&//不要
&&&&&&&&&&buffer.append(&"select new cn.itheima03.hibernate.domain.ame,s.sname)& "&+
&&&&&&&&&&&"& from Student s inner join& s.classes c "&);
&&&&&&&&&&
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&&&List&list&= query.list();
&&&&&&&& session.close();
&&&&& * 8.迫切左外连接
&&&&& * 从班级出发，得到班级对应的学生
&&&&&@Test
&&&&&public&void&testOneToMany_LeftJoin_Fetch(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Classes c left outer join fetch c.students s");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& List&Classes& list =&query.list();
&&&&&&&&&for&(Classes classes : list) {
&&&&&&&&&&&&&&&System.&out.println("classes:"&+classes.getCname());
&&&&&&&&&&&&&&Set&Student& students = classes.getStudents();
&&&&&&&&&&&&&&&for&(Student student : students) {
&&&&&&&&&&&&&&&&&&&System.&out.println("&&&&&student:"&+student.getSname());
&&&&&&&&&&&&&&}
&&&&&&&&&&}
&&&&&&&& session.close();
&&&&& * 9.迫切左外连接2
&&&&& * 从学生出发，得到对应的班级
&&&&&@Test
&&&&&public&void&testOneToMany_RightJoin_Fetch(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Student s left outer join fetch s.classes ");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& List&Student& list =&query.list();
&&&&&&&&&for&(Student student : list) {
&&&&&&&&&&&&&&&System.&out.println("student:"&+student.getSname());
&&&&&&&&&&&&&&&if&(student.getClasses()!=null) {
&&&&&&&&&&&&&&&&&&&System.&out.println("&&&&&"&+student.getClasses().getCname());
&&&&&&&&&&&&&&}
&&&&&&&&&&}
&&&&&&&& session.close();
二。多对多
学生Student和课程Course为例:
Student里有装Course的set集合，Course里也有装Student的set集合；
多对多与一对多操作差不多
&&&&& * 1.得到所有的学生以及其对应的课程
&&&&& * 从学生端出发
&&&&& * list装的是学生；
&&&&&@Test
&&&&&public&void&testManyToMany_LeftJoin_Fecth(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Student s left outer join fetch s.courses");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&&&List&list&= query.list();
&&&&&&&& session.close();
&&&&& * 2.得到所有的课程及课程下对应的学生；
&&&&& * list装的是课程
&&&&&@Test
&&&&&public&void&testManyToMany_LeftJoin_Fecth_2(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Course c left outer join fetch c.students s");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& query.list();
&&&&&&&& session.close();
&&&&& * 3.一对多和多对多的结合
&&&&& * 得到所有班级下的所有学生以及所有学生下的所有课程；
&&&&& * 从班级出发
&&&&&@Test
&&&&&public&void&testManyToManyAndOneToMany(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Classes c left outer join fetch"&+
&&&&&&&&&&&&&&&&&&&&" c.students s left outer join fetch s.courses");
&&&&&&&&&&
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& List&Classes& classeList =&query.list();
&&&&&&&&&//去掉集合中的重复元素
&&&&&&&& Set&Classes& sets =&new&HashSet&Classes&(classeList);
&&&&&&&& classeList =&new&ArrayList&Classes&(sets);
&&&&&&&& System.&out.println(classeList.size());
&&&&&&&&&for(Classes classes:classeList){//遍历班级
&&&&&&&& &System.&out.println(classes.getCname());
&&&&&&&& &Set&Student& students = classes.getStudents();//得到班级下的学生
&&&&&&&& &&for(Student student:students){//遍历学生
&&&&&&&& &&&&&&System.&out.println(student.getSname());
&&&&&&&& &&&&&&Set&Course& courses = student.getCourses();
&&&&&&&& &&&&&&&for(Course course:courses){//遍历学生下的课程
&&&&&&&& &&&&&&&&&&System.&out.println(course.getCname());
&&&&&&&& &&&&&&}
&&&&&&&& &}
&&&&&&&& }
&&&&&&&& session.close();
&&&&& * 从中间表出发，班级有学生，学生修课程，故从学生角度出发进行查询；
&&&&&@Test
&&&&&public&void&testManyToManyAndOneToMany_2(){
&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&StringBuffer buffer =&new&StringBuffer();
&&&&&&&&&&buffer.append(&"from Student s left outer join fetch s.classes c&
& & & & & & & & & & & & & &left outer join fetch s.courses cc");
&&&&&&&& Query query = session.createQuery(buffer.toString());
&&&&&&&& List&Student& studentList =&query.list();
&&&&&&&&&for(Student student:studentList){
&&&&&&&& &System.&out.println(student.getSname());
&&&&&&&& &Classes classes = student.getClasses();
&&&&&&&& &System.&out.println(classes.getCname());
&&&&&&&& &Set&Course& courses = student.getCourses();
&&&&&&&& &&for(Course course:courses){
&&&&&&&& &&&&&&System.&out.println(course.getCname());
&&&&&&&& &}
&&&&&&&& }
&&&&&&&& session.close();
&&&&&/*******************************************************************************/
&&&&&&&&/**
&&&&&&&& * 面向对象的查询
&&&&&&&& */
&&&&&&&&&&&@Test
&&&&&&&&&&&public&void&testQueryCriteria(){
&&&&&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&&&&&List&Classes&& classesList =&session.createCriteria(Classes.class).list()&;
&&&&&&&&&&&&&&System.&out.println(classesList.size());
&&&&&&&&&&&&&&session.close();
&&&&&&&&&&}
&&&&&&&&&&
&&&&&&&&&&&@Test
&&&&&&&&&&&public&void&testQueryCriteria_Where(){
&&&&&&&&&&&&&&Session&&&session =&sessionFactory.openSession();
&&&&&&&&&&&&&&Classes classes = (Classes)session.createCriteria(Classes.class).add(Restrictions.eq("cid"&, 1L)).uniqueResult();
&&&&&&&&&&&&&&System.&out.println(classes.getCname());
&&&&&&&&&&&&&&session.close();
&&&&&&&&&&}
无论是一对多还是多对多，hql语句中含有fetch时，得到的list装的是From 后面的对象，对象中可能有相关联对象的集合或者对象；
阅读(...) 评论()使用VLOOKUP函数进行一对多查询的方法 - Office办公助手
最专业的Office办公软件学习和原创Office教程网站！
使用VLOOKUP函数进行一对多查询的方法
发布时间：10-07　来源：Office办公助手()
就像下图所示，需要根据不同的部门，查询这个部门的所有人员。
=INDEX(C:C,SMALL(IF(B$2:B$12=E$2,ROW($2:$12),4^8),ROW(A1)))&&&
这个公式看起来好长啊
今天既然你说到了，那么我们就乘机离这个猥琐四人组远点，也好来多了解了解VLOOKUP这个大众情人。
关于返回符合条件的多个值的问题，VLOOKUP想大声告诉你：不是我不能，而是你不行&&给我一个辅助列，我能给你所有!
具体操作：
第一步：部门所在列之前插入一个辅助列。
A2输入公式
=B2&COUNTIF($B$2:B2,B2)
下拉填充到你要想去到的地方。
这样就相当于给部门加上了编号。
第二步：在E3输入公式
=IFERROR(VLOOKUP(E$2&ROW(A1),A:C,3,),&&)
下拉填充到足够多的地方。
E$2&ROW(A1)相当于将 VLOOKUP函数的查询值加上了不同的序号。
第三步：已经没有第三步了。
上一篇： 下一篇：
专题教程推荐
(window.slotbydup=window.slotbydup || []).push({
id: '3577897',
container: s,
size: '300,450',
display: 'inlay-fix'
热门资源下载11869人阅读
学习笔记（21）
mysql（7）
算法代码（4）

//创建用户且置密码，在MySQL中行，但在Oracle中行& ----必须在超级管理员身份下操作
create user hncu identified by '1234'
DROP DATABASE mydb2; //删除数据库
//查询(显示数据库)
SHOW DATABASES; /*用Tab键补全代码，类似MyEclipse中的Alt+/ */
//创建数据库 并手动指定编码格式
//错，因为根据手册的查询，数据库名应该在 EXISTS后面
CREATE DATABASE hncu IF NOT EXISTS DEFAULT CHARACTER SET 'utf8';
CREATE DATABASE IF NOT EXISTS hncu DEFAULT CHARACTER SET 'utf8';
//创建表格
CREATE TABLE stud(
& id INT PRIMARY KEY,
& sname VARCHAR(30) ,
& age INT);
&//往数据表中插入数据
&INSERT INTO stud VALUES( 1003,'Rose',22 );
&SELECT * FROM
&SHOW TABLES;//查询(显示)表格
&DESC& //查看数据表student的表结构
&SELECT sname,age FROM //只查询(显示)表中的指定列
&DELETE FROM stud WHERE age=30; //删除年龄为30的表记录
&UPDATE stud SET sname='杰克' WHERE sname='Jack'; //更改数据
-----------------------------------------------------------------------
varchar(20)& ----可变的char数组，类似Java当中的String
char(20)&& ----固定长度的char数组
//SQL语言 全部忽略大小写----大小写不敏感
//更改表结构ALTER TABLE (添加一列)
ALTER TABLE stud ADD COLUMN score NUMERIC(4,2);
INSERT INTO stud VALUES(1004,'张三',38,60);
INSERT INTO stud VALUES(1005,'王三',30,60);
INSERT INTO stud VALUES(1006,'王五',30,60);
INSERT INTO stud VALUES(1007,'王五六',30,80);
SELECT * FROM
//※范围查询
//查询年龄在24-26之间的学生信息
//连续区间内的查询
SELECT * FROM stud WHERE age&=24 AND age&27;
//用BETWEEN是左右都包含，如下功能是[24,26]
SELECT * FROM stud WHERE age BETWEEN 24 AND 26;
//离散的多个
SELECT * FROM stud WHERE age=24 OR age=38;
SELECT * FROM stud WHERE age IN(24,26,30);
//※模糊查询LIKE& 参数：%(任意匹配), _(匹配一个)
//查询姓“王”的学生信息
SELECT * FROM stud WHERE sname LIKE '王%'
//查询姓“王”且名为单字的学生信息
SELECT * FROM stud WHERE sname LIKE '王_';
//查询姓“王”且名为双字的学生信息
SELECT * FROM stud WHERE sname LIKE '王__';
//查询姓名中包含“五”字的学生信息
SELECT * FROM stud WHERE sname LIKE '%五%';
//※范围查询和模糊查询联合
//查询姓名中包含“五”字 且 年龄大于30 的学生信息
SELECT * FROM stud WHERE sname LIKE '%五%' AND age&30;
//查询无名英雄学生的信息(VARCHAR)
SELECT * FROM stud WHERE sname IS NULL;
//查询没有年龄信息的学生
//错：SELECT * FROM stud WHERE age==NULL;
SELECT * FROM stud WHERE age IS NULL;
//创建视图
CREATE VIEW studView AS SELECT * FROM stud WHERE score&=60;
SELECT * FROM
//聚合函数
//统计表格的行数
SELECT COUNT(*) AS TEMPTABLE FROM
SELECT COUNT(1) AS TEMPTABLE FROM
//统计有年龄值的学生人数
SELECT COUNT(age) AS TEMPTABLE FROM
//统计有年龄值且有分数值的学生人数
SELECT COUNT(age) AS TEMPTABLE FROM stud WHERE score IS NOT NULL;
//统计平均分且取整(AVG函数只统计非NULL的数据记录)
SELECT ROUND(AVG(score)) FROM //这种方式一般不用，因为列名是自动生成的，我们在程序中不好访问
SELECT ROUND(AVG(score)) AS averageScore FROM
//分数求和
SELECT SUM(score) AS ss FROM
//年龄最大值
SELECT MAX(age) AS maxAge FROM
//WHERE子句 IN子句
//查询年龄最小的那个人的名字
SELECT sname FROM stud WHERE age=(SELECT MIN(age) FROM stud);
SELECT sname FROM stud WHERE age IN(SELECT MIN(age) FROM stud);
SELECT * FROM stud GROUP BY age ASC; //不重复排序(即年龄相同的，只显示第一个0
SELECT * FROM stud GROUP BY age ASC; //显示出所有年龄段
SELECT * FROM stud ORDER BY age ASC; //普通排序--升序
SELECT * FROM stud ORDER BY age DESC; //普通排序--降序
SELECT DISTINCT sname,age FROM stud GROUP BY age DESC;
//EXISTS() 判断括号内的内容是否存在----注意，下面的例子，只要存在年龄为26的学生，就会输出所有数据
SELECT * FROM stud WHERE EXISTS( SELECT * FROM stud WHERE age=26 );
//演示分组
ALTER TABLE stud ADD COLUMN dept VARCHAR(20);
UPDATE stud SET dept='信息学院' WHERE score&=65;
UPDATE stud SET dept='通信学院' WHERE score=60;
UPDATE stud SET dept='土木学院' WHERE score&60;
SELECT * FROM
//分组计算
//计算信息学院学生的平均分
SELECT AVG(score) FROM stud WHERE dept='信息学院';
//按学院计算平均分
SELECT dept,AVG(score) AS '学院平均分' FROM stud GROUP BY
//插入记录，当数据不全时，要指定列名
INSERT INTO stud(id,sname,age,score) VALUES(1010,'李小明',25,90);
------------------------------------
数据库编码问题
方法1：可以在MySQL的配置文件如my.ini中指定：
& default-character-set = GBK& 或
& default-character-set = utf8
方法2：在创建数据库时自己指定，如：
CREATE DATABASE IF NOT EXISTS hncu DEFAULT CHARACTER SET 'utf8';
▲相比较而言，对我们来讲，第2种方法更好。一是配置文件是平台，不能随意改，而且通常是由DBA来做的，我们没权设置，甚至MySQL都不在我们的电脑中。二是只要我们自己在创建数据库时自己指定编码，无论平台默认配置的是什么编码，对我们没有影响，一句话不依赖你的平台----可移置及兼容性好。
SHOW VARIABLES; //查询系统中的所有变量
SHOW VARIABLES WHERE variable_name LIKE 'character%'; //查询系统中所有的编码方面的参数
查询结果：
character_set_client utf8& //客户端编码 (不同的客户端显示的可能不一样，如cmd窗口和SQLyong)
character_set_connection& utf8& //客户端连接数据库时用的编码
character_set_database gbk& //数据库的默认编码(一般来讲，这是由my.ini配置文件定的。这是没进数据库时的通用编码)
character_set_filesystem binary& //这是数据库自己存储数据文件时用的编码，跟我们关系不大
character_set_results utf8& //查询之后的结果集的编码
character_set_server gbk& //MySQL数据库服务器自己的编码& iso8859-1在这里称Latin1
character_set_system& utf8
character_set_dir&& MySQL安装目录 \share\charsets
对我们来讲，为了不出现乱码，必须保证client和connection的编码一致，否则就会乱码。
character_
set_database 数据库的编码必须要能够支持中文，否则输入中文有问题的
//如何设置指定的编码
set character_set_client=
SET character_set_client=
SHOW VARIABLES WHERE variable_name LIKE 'character%';
SELECT * FROM//原来的数据显示正常
INSERT INTO stud VALUES(1011,'城院',20,88,'数计学院');
SELECT * FROM//刚刚插入的那条记录，是乱码 //因为我们这里client是gbk，而connection是utf8，不一致了
SET character_set_connection=//已经把client和connection设成gbk，一致了
INSERT INTO stud VALUES(1012,'城院2',20,88,'数计学院');
SELECT * FROM//显示刚刚插入的那条记录，还是乱码。因为数据库表stud的编码是utf8，而我们客户端与它的连接都是gbk
▲综上，client、connection、我们所访问的数据库的编码& 这三者都要一致且应该是支持中文的编码
上面只能保证添加到数据库中的汉字不会出现乱码。
▲如果读取出来，还要看results、所访问的数据库的编码和我们java代码中的解码是否一致。
CREATE DATABASE IF NOT EXISTS aaa DEFAULT CHARACTER SET 'utf8';
DROP DATABASE
SHOW VARIABLES; //查询系统中的所有变量
SHOW VARIABLES WHERE variable_name LIKE 'character%'; //查询系统中所有的编码方面的参数
SET character_set_client=
SHOW VARIABLES WHERE variable_name LIKE 'character%';
SELECT * FROM
INSERT INTO stud VALUES(1011,'城院',20,88,'数计学院');
SET character_set_connection=
INSERT INTO stud VALUES(1012,'城院2',20,88,'数计学院');
SELECT * FROM
DELETE FROM stud WHERE id&=1011;
//统计各个学院的平均分且排序
SELECT dept,AVG(score) FROM stud GROUP BY dept& ORDER BY AVG(score);
//字符串处理函数
SELECT * FROM stud WHERE sname='AAA';
SELECT * FROM stud WHERE TRIM(sname)='AAA';
SELECT * FROM stud WHERE LTRIM(RTRIM(sname))='AAA';//和上面等价
SELECT * FROM
SHOW VARIABLES WHERE variable_name LIKE 'character%';
SET character_set_connection=utf8;
SET character_set_client=utf8;
UPDATE stud SET dept='数计学院' WHERE id=1011;
SELECT LEFT(TRIM(sname),2) FROM //取去掉空格后的左起2个字符
SELECT REVERSE(TRIM(sname)) FROM
CREATE TABLE person(
& sname VARCHAR(30) ,
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY(id);//更灵活
DROP TABLE
CREATE TABLE person(
& id INT PRIMARY KEY,
& sname VARCHAR(30) NOT NULL,
); //用NOT NULL 限制非空输入
SELECT * FROM TABLES;
//性别字段: 一般不定义成BOOLEAN型，因为有的数据库不支持，为考虑兼容，通常用CHAR(1)
CREATE TABLE a(
& id INT UNIQUE,
& sname VARCHAR(10),
& sex CHAR(1) DEFAULT '0'
INSERT INTO a VALUES(1,'Jack');& //错：未指定列名的方式赋值时，必须要给全，默认值不给也不行
INSERT INTO a VALUES(1,'Jack','0');//对
INSERT INTO a(id,sname) VALUES(1,'Tom'); //错：UNIQUE限定该列的值必须唯一(可以为NULL，但最多只能有一个)
INSERT INTO a(id,sname) VALUES(2,'Tom'); //对，性别未赋，则用默认
//性能优化：指定列名的方式性能更好！
//显示性别 (真实值与显示值之间的转换)
SELECT * FROM
SELECT id,sname,(CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' ELSE 'Null' END) xb FROM
SELECT id,sname,(CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' ELSE 'Null' END) AS xb FROM
SELECT id,sname,(CASE WHEN sex='0' THEN '女' WHEN sex='1' THEN '男' ELSE 'Null' END) AS xb FROM
//无关子查询
//需求：具有同龄人的学生
SELECT * FROM
SELECT * FROM stud WHERE age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)&=2 ) ORDER BY age DESC;
//练练别名
SELECT * FROM stud AS xs WHERE age IN ( SELECT age FROM xs GROUP BY age HAVING COUNT(age)&=2 ) ORDER BY age DESC;
//需求2：不但具有同龄人，而且年龄大于等于30的学生
SELECT * FROM stud WHERE age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)&=2 AND age&=30 ) ORDER BY age DESC;
SELECT * FROM stud WHERE age&=30 AND age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)&=2 ) ORDER BY age DESC;
//固定搭配
SELECT * FROM& +& WHERE& + ORDER BY(要放在最后)
GROUP BY + HAVING
-----------------------------------------
DROP TABLE
SELECT NAME, NAME
CREATE TABLE person(
&& id INT,
&& NAME VARCHAR(10),
&& sex CHAR(1),
&& wife INT,
&& husband INT
INSERT INTO person VALUES(1,'小花','0',0,3);
INSERT INTO person VALUES(2,'玉芬','0',0,4);
INSERT INTO person VALUES(3,'张三','1',1,0);
INSERT INTO person VALUES(4,'李四','1',2,0);
INSERT INTO person VALUES(5,'王五','1',0,0);
//一对一关系的操作：查出每对夫妻的姓名
CREATE VIEW w AS SELECT * FROM person WHERE sex='0';
CREATE VIEW m AS SELECT * FROM person WHERE sex='1';
//不利用表与表之间的关系
SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w,m WHERE w.husband=m.id AND m.wife=w.
//现在更先进的方式：利用表间的关系
SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w.
SELECT * FROM
//一对多的关系& 代码演示
//步骤1：画E-R图
//步骤2:分别建实体表，并给多方的表添加外键约束
CREATE TABLE person2(
&& id VARCHAR(32) PRIMARY KEY,
&& pname VARCHAR(30),
&& sex CHAR(1)
CREATE TABLE car(
&& id VARCHAR(32) PRIMARY KEY,
&& cname VARCHAR(30),
&& price NUMERIC(10,2),
&& pid VARCHAR(32),
&& CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id)
DROP TABLE
//步骤3:为两个表添加测试数据
INSERT INTO person2(id,pname,sex) VALUES('P001','Jack','1');
INSERT INTO person2(id,pname,sex) VALUES('P002','Tom','1');
INSERT INTO person2(id,pname,sex) VALUES('P003','Rose','0');
INSERT INTO person2(id,pname,sex) VALUES('P004','Mary','0');
INSERT INTO person2(id,pname,sex) VALUES('P005','Mike','1');
SELECT * FROM person2;
////实体表2
INSERT INTO car(id,cname,price,pid) VALUES('C001','BMW',123.5,'P001');
INSERT INTO car(id,cname,price,pid) VALUES('C002','Benz',123.5,'P001');
INSERT INTO car(id,cname,price,pid) VALUES('C003','BMW',223.5,'P001');
INSERT INTO car(id,cname,price,pid) VALUES('C011','BMW',83.5,'P003');
INSERT INTO car(id,cname,price,pid) VALUES('C012','Benz',100,'P003');
INSERT INTO car(id,cname,price,pid) VALUES('C013','Audi',223.5,'P003');
INSERT INTO car(id,cname,price,pid) VALUES('C021','BMW',88.5,'P004');
INSERT INTO car(id,cname,price,pid) VALUES('C022','QQ',10,'P004');
INSERT INTO car(id,cname,price,pid) VALUES('C023','Audi',73,'P005');
INSERT INTO car(id,cname,price) VALUES('C033','Audi',1000);
//该句代码执行错误，因为编号为P006的人在Person2表中不存在，这就是参照完整性
INSERT INTO car(id,cname,price,pid) VALUES('C033','Audi',1000,'P006');
SELECT * FROM
//查询：哪些人有什么样的车 (用&表名.列名&的形式访问列，如果列名不重复，可以省略表名)
//利用一方的主键和“多方”的外键进行关联
SELECT person2.ame FROM person2,car WHERE person2.id=car.
//查询Jack有什么车
SELECT person2.ame FROM person2,car WHERE person2.id=car.pid AND person2.pname='Jack' ;
//查询哪些人有两辆以上的车
SELECT person2.pname,COUNT(pname) AS 车数量 FROM person2,car WHERE person2.id=car.pid GROUP BY pname HAVING COUNT(pname)&=2 ORDER BY 车数量;
SELECT * FROM person2 WHERE id IN ( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)&=2 );
//查询哪些人没有车
SELECT * FROM person2 WHERE id NOT IN( SELECT pid FROM car );
//用左关联(LEFT JOIN)来查询：哪些人有什么样的车(没车的也是一种情况，要显示)
SELECT person2.ame,car.price FROM person2 LEFT JOIN car ON person2.id=car.pid ORDER BY person2.
//用内关联(INNER JOIN)来查询：哪些人有什么样的车(没车的不显示)
SELECT person2.ame,car.price FROM person2 INNER JOIN car ON person2.id=car.pid ORDER BY person2.
//查询每辆车的销售情况(如果有主人就显示，没有则显示NULL)
SELECT person2.ame,car.price FROM person2 RIGHT JOIN car ON person2.id=car.pid ORDER BY person2.
--------------------------------------------------
DELETE FROM person2 WHERE id='P005';
CREATE TABLE student(
&& id VARCHAR(32) PRIMARY KEY,
&& NAME VARCHAR(30),
&& age INT
CREATE TABLE ject(
&& id VARCHAR(32) PRIMARY KEY,
&& NAME VARCHAR(30),
&& price NUMERIC(5,2)
CREATE TABLE sj(
&& studId VARCHAR(32) NOT NULL,
&& jectId VARCHAR(32)
//建立联合主键
ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studId,jectId);
//添加外键约束1(为sj表的studId字段添加外键student(id)约束)
ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studId) REFERENCES student(id);
//添加外键约束2(为sj表的jectId字段添加外键ject(id)约束)
ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectId) REFERENCES ject(id);
//添加测试数据
INSERT INTO student(id,NAME,age) VALUES('S001','Jack',25);
INSERT INTO student(id,NAME,age) VALUES('S002','Tom',24);
INSERT INTO student(id,NAME,age) VALUES('S003','张三',23);
INSERT INTO student(id,NAME,age) VALUES('S004','李四',24);
INSERT INTO student(id,NAME,age) VALUES('S005','Rose',25);
SELECT * FROM
INSERT INTO ject(id,NAME,price) VALUES('J001','Java',25);
INSERT INTO ject(id,NAME,price) VALUES('J002','MySQL',30);
INSERT INTO ject(id,NAME,price) VALUES('J003','Oracle',55.9);
INSERT INTO ject(id,NAME,price) VALUES('J004','软件工程',20.25);
INSERT INTO ject(id,NAME,price) VALUES('J005','WEB开发',125);
SELECT * FROM
INSERT INTO sj(studId,jectId) VALUES('S001','J001');
INSERT INTO sj(studId,jectId) VALUES('S001','J002');
INSERT INTO sj(studId,jectId) VALUES('S001','J003');
INSERT INTO sj(studId,jectId) VALUES('S002','J001');
INSERT INTO sj(studId,jectId) VALUES('S002','J003');
INSERT INTO sj(studId,jectId) VALUES('S003','J001');
INSERT INTO sj(studId,jectId) VALUES('S003','J002');
INSERT INTO sj(studId,jectId) VALUES('S004','J003');
INSERT INTO sj(studId,jectId) VALUES('S005','J001');
SELECT * FROM
//查询哪些人选了哪些课 ----要求显示：人名，课程名
//采用的是92标准
SELECT student.name,ject.NAME FROM student,ject,sj WHERE student.id=sj.studId AND sj.jectId=ject.
//采用96标准
SELECT student.name,ject.NAME FROM student
&&&&&& INNER JOIN sj ON student.id=sj.studId
&&&&&& INNER JOIN ject ON sj.jectId=ject.
//查询哪些人没有选课(左关联)
SELECT student.name,ject.NAME FROM student
&&&&&& LEFT JOIN sj ON student.id=sj.studId
&&&&&& LEFT JOIN ject ON sj.jectId=ject.id WHERE ject.NAME IS NULL;
//查询哪些课没人选(右关联)
SELECT student.name,ject.NAME FROM student
&&&&&& RIGHT JOIN sj ON student.id=sj.studId
&&&&&& RIGHT JOIN ject ON sj.jectId=ject.id WHERE student.NAME IS NULL;
//左右关联可以相互转换，如把上面的代码用左关联
SELECT student.name,ject.NAME FROM ject
&&&&&& LEFT JOIN sj ON ject.id=sj.jectId
&&&&&& LEFT JOIN student ON sj.studId=student.id WHERE student.NAME IS NULL;
※存储过程
//定义存储过程p1 // “DELIMITER$$”这句是为了让解析器把“$$”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符&;&就不会当作过程的结束标记。
DELIMITER$$
CREATE PROCEDURE p1()
&& SELECT * FROM
&& INSERT INTO stud(id,sname,age,score,dept) VALUES(1014,'刘三丰',33,55,'通信学院');
DELIMITER;&& //把结束标记还原回来
CALL p1();& //调用存储过程p1
//带参数的存储过程
DELIMITER$$
CREATE PROCEDURE p2(IN id INT, IN nm VARCHAR(30) )
&& INSERT INTO stud(id,sname) VALUES(id,nm);
DELIMITER ;
DROP PROCEDURE p2;
CALL p2(1015,'吊丝');
//有返回值的存储过程----参数与变量问题( @变量名& ，一个@为用户变量，两个@即 @@为全局的系统变量 )
DELIMITER$$
CREATE PROCEDURE p3(IN id INT, IN nm VARCHAR(30), OUT num INT )
&& INSERT INTO stud(id,sname) VALUES(id,nm);
&& SELECT COUNT(*) INTO num FROM
DELIMITER ;
CALL p3(1016,'无名',@aa);
SELECT @ //输出变量aa的值
※事务处理
START TRANSACTION
& DELETE FROM stud WHERE id=1015;
& DELETE FROM stud WHERE id=1014;
& SELECT * FROM
ROLLBACK /& COMMIT;
◎采用事务的java编程
&&& st.execute(&START TRANSACTION;&);
&&& st.execute(&DELETE FROM stud WHERE id=1015;&);
&&& st.execute(&DELETE FROM stud WHERE id=1014;&);
&&& st.execute(& commit();& &);
}catch(Exception e){
&& rollback();
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：22155次
排名：千里之外
原创：48篇
(9)(11)(4)(23)(3)