java访问数据库
分为四步:
1、加载连接器
高级版本的mysql可以省略这一步
Class.forName("com.mysql.jdbc.Driver");
2、建立连接
我们最好在建立连接的时候指定编码,这样可以保证执行的时候不会因此乱码。当然也可以采用默认编码方式连接,不过不推荐。
//String url = "jdbc:mysql://127.0.0.1:3306/aa";
String url = "jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=UTF-8";
Connection con = DriverManager.getConnection(url,"root","");
3、获取语句对象
Statement st = con.createStatement();
4、对数据库进行操作
对于sql语句都可以用st.exectute(sql);
来执行,只不过这个返回的是一个boolean值,表示是否执行成功。我们在查询的时候,希望返回的是一些数据,此时,用这种方式显然不合理。所以我们对于查询采用st.executeQuery(sql)
来做。这个函数返回的是一个ResultSet集合对象,因为第一个是表头,我们不需要,所以我们直接在while中用rs.next()来做操作。而且要注意的是,rs.getString(1);
第一列的序号不是0,而是1;
//4 对数据库进行操作
//增删改用 st.exectute(sql);
//增 String sql = "insert into sstud values('1015','张三',20,'武汉');";
//删 String sql = "delete from sstud where sno='1010';";
//改 String sql = "update sstud set saddress='城院' where sno='1009';";
//st.execute(sql);
//查
String sql = "select * from sstud;";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
String sno = rs.getString(1);//第一列的序号是1.不是0;
String sname = rs.getString("sname");
int age = rs.getInt("age");
String saddress = rs.getString(4);
System.out.println(sno+","+sname+","+age+","+saddress);
}
数据库编码
查看当前数据库编码
SHOW VARIABLES WHERE variable_name LIKE 'character%';
解决中文乱码:
要求:保证如下三个编码一致。
1、character_set_client
2、character_set_connection
3、character_set_results
设置编码
SET character_set_results= gbk;
控制流程函数 CASE…WHEN
实际语法是:CASE…WHEN…THEN…END
例子:当sex值为1时,显示男 ,当sex值为0时,显示女;
SELECT sno,sname,age,saddress,(CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' END ) AS 性别 FROM sstud;
无关/相关子查询
无关子查询
例子:查询哪些人有同龄人
SELECT * FROM sstud GROUP BY age HAVING COUNT(age)>=2;
相关子查询
例子:查询哪些人有同龄人并且性别为男
SELECT * FROM sstud GROUP BY age HAVING COUNT(age)>=2 AND sex='1';
表与表之间的关系
1一对一
需要两个表,做项目时通常只建一个物理表。如果要实现一对一的查询,建立两个视图。
/*1.1建立物理表 初始化数据*/
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);
/*1.2建立两个视图*/
CREATE VIEW women AS SELECT * FROM person WHERE sex='0';
CREATE VIEW men AS SELECT * FROM person WHERE sex='1';
/*1.3查询*/
/*老版本的1:1查询*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women , men WHERE women.husband=men.id;
/*利用连接进行1:1查询*/
/*右连接*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women RIGHT JOIN men ON women.husband=men.id;
/*左连接*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women LEFT JOIN men ON women.husband=men.id;
2一对多
建立两张表,一张独立表,另一张表用外键连接此表。
/*2一对多*/
/*建立表格 初始化*/
CREATE TABLE person2(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
sex CHAR(1),
age INT
);
INSERT INTO person2 VALUES('P1001','小花','0',25);
INSERT INTO person2 VALUES('P1002','张三','1',22);
INSERT INTO person2 VALUES('P1003','Jack','1',24);
INSERT INTO person2 VALUES('P1004','Rose','0',25);
CREATE TABLE car(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
price NUMERIC(10,2),
pid VARCHAR(32),
/*为字段pid定义一个外键约束(来自person2表的字段id)*/
CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id)
);
INSERT INTO car VALUES('C001','BMW',80.5,'P1001');
INSERT INTO car VALUES('C002','Benz',100,'P1001');
INSERT INTO car VALUES('C003','BMW',120.05,'P1001');
INSERT INTO car VALUES('C004','Benz',88.5,'P1002');
INSERT INTO car VALUES('C005','QQ',8.5,'P1002');
INSERT INTO car VALUES('C006','BIKE',0.5,'P1003');
/*这时候不能进行删除 因为主键这时是car表格的外键依赖*/
DELETE FROM person2 WHERE(id='P1001');
3多对多
/* 3、多对多
数据库设计分析
※案例:一个人可以选择多门课程,一门课程又可以被很多人选择。
*/
/*3.1 学生表--实体*/
CREATE TABLE student(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
age INT
);
/*3.2 课程表--实体*/
CREATE TABLE ject(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
/*3.3 关系表--关系*/
CREATE TABLE sj(
studid VARCHAR(32),
subjectid VARCHAR(32)
);
/*3.4 单独添加约束(注意顺序: 要先添加联合主键,再添加外键)*/
/*创建联合主键*/
ALTER TABLE sj ADD CONSTRAINT pk_sj PRIMARY KEY(studid,subjectid);
/*创建两个外键*/
ALTER TABLE sj ADD CONSTRAINT pk_stud FOREIGN KEY(studid) REFERENCES student(id);
ALTER TABLE sj ADD CONSTRAINT pk_subj FOREIGN KEY(subjectid) REFERENCES ject(id);
/*3.5初始化数据*/
INSERT INTO student VALUES('P001','小花',25);
INSERT INTO student VALUES('P002','Jack',23);
INSERT INTO student VALUES('P003','Tom',24);
INSERT INTO student VALUES('P004','张三',24);
INSERT INTO student VALUES('P005','赵子龙',26);
INSERT INTO ject VALUES('S001','Java');
INSERT INTO ject VALUES('S002','JavaEE');
INSERT INTO ject VALUES('S003','XML');
INSERT INTO ject VALUES('S004','数据库');
INSERT INTO ject VALUES('S005','JQuery');
INSERT INTO sj VALUES('P001','S001');
INSERT INTO sj VALUES('P001','S003');
INSERT INTO sj VALUES('P002','S001');
INSERT INTO sj VALUES('P002','S002');
INSERT INTO sj VALUES('P002','S003');
INSERT INTO sj VALUES('P003','S001');
INSERT INTO sj VALUES('P004','S002');
INSERT INTO sj VALUES('P004','S003');
/*3.6 数据库操作*/
/*关联: 内连接、左关联、右关联*/
/*要求:查询哪些人选了哪些课*/
SELECT student.NAME AS 学生姓名,ject.NAME AS 课程名称 FROM student INNER JOIN sj ON student.id=sj.studid INNER JOIN ject ON sj.subjectid = ject.id;
/*要求:查询哪些人没有选课*/
SELECT student.NAME AS 学生姓名 FROM student LEFT JOIN sj ON student.id=sj.studid LEFT JOIN ject ON sj.subjectid=ject.id WHERE ject.NAME IS NULL;
/*要求:查询哪些课程没有人选*/
SELECT ject.id AS 课程编号, ject.NAME AS 课程名称 FROM student RIGHT JOIN sj ON student.id=sj.studid RIGHT JOIN ject ON sj.subjectid=ject.id WHERE student.name IS NULL;
/*注意:在左右关联中没有绝对,只是看你怎么连表*/
mysql第三天课堂记录
/*查询当前数据库编码*/
SHOW VARIABLES WHERE variable_name LIKE 'character%';
/*解决中文乱码*/
/*要求:character_set_client、character_set_connection、character_set_results保持编码一致*/
/*设置编码*/
SET character_set_results= gbk;
SELECT * FROM sstud;
SET character_set_results= utf8;
/*控制流程函数 CASE...WHEN...THEN...END*/
/*增加一列*/
ALTER TABLE sstud ADD COLUMN sex CHAR(1);
UPDATE sstud SET sex=1 WHERE sex IS NULL;
SELECT sno,sname,age,saddress,(CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' END ) AS 性别 FROM sstud;
/*无关子查询*/
/*查那些人有同龄人*/
SELECT * FROM sstud GROUP BY age HAVING COUNT(age)>=2;
/*查那些人有同龄人且性别为男*/
SELECT * FROM sstud GROUP BY age HAVING COUNT(age)>=2 AND sex='1';
/*表与表之间的关系*/
/*1 一对一*/
/*1.1建立物理表 初始化数据*/
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);
/*1.2建立两个视图*/
CREATE VIEW women AS SELECT * FROM person WHERE sex='0';
CREATE VIEW men AS SELECT * FROM person WHERE sex='1';
/*1.3查询*/
/*老版本的1:1查询*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women , men WHERE women.husband=men.id;
/*利用连接进行1:1查询*/
/*右连接*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women RIGHT JOIN men ON women.husband=men.id;
/*左连接*/
SELECT women.NAME AS 妻子, men.NAME AS 丈夫 FROM women LEFT JOIN men ON women.husband=men.id;
/*2一对多*/
/*建立表格 初始化*/
CREATE TABLE person2(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
sex CHAR(1),
age INT
);
INSERT INTO person2 VALUES('P1001','小花','0',25);
INSERT INTO person2 VALUES('P1002','张三','1',22);
INSERT INTO person2 VALUES('P1003','Jack','1',24);
INSERT INTO person2 VALUES('P1004','Rose','0',25);
CREATE TABLE car(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
price NUMERIC(10,2),
pid VARCHAR(32),
/*为字段pid定义一个外键约束(来自person2表的字段id)*/
CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id)
);
INSERT INTO car VALUES('C001','BMW',80.5,'P1001');
INSERT INTO car VALUES('C002','Benz',100,'P1001');
INSERT INTO car VALUES('C003','BMW',120.05,'P1001');
INSERT INTO car VALUES('C004','Benz',88.5,'P1002');
INSERT INTO car VALUES('C005','QQ',8.5,'P1002');
INSERT INTO car VALUES('C006','BIKE',0.5,'P1003');
/*这时候不能进行删除 因为主键这时是car表格的外键依赖*/
DELETE FROM person2 WHERE(id='P1001');
/* 3、多对多
数据库设计分析
※案例:一个人可以选择多门课程,一门课程又可以被很多人选择。
*/
/*3.1 学生表--实体*/
CREATE TABLE student(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
age INT
);
/*3.2 课程表--实体*/
CREATE TABLE ject(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
/*3.3 关系表--关系*/
CREATE TABLE sj(
studid VARCHAR(32),
subjectid VARCHAR(32)
);
/*3.4 单独添加约束(注意顺序: 要先添加联合主键,再添加外键)*/
/*创建联合主键*/
ALTER TABLE sj ADD CONSTRAINT pk_sj PRIMARY KEY(studid,subjectid);
/*创建两个外键*/
ALTER TABLE sj ADD CONSTRAINT pk_stud FOREIGN KEY(studid) REFERENCES student(id);
ALTER TABLE sj ADD CONSTRAINT pk_subj FOREIGN KEY(subjectid) REFERENCES ject(id);
/*3.5初始化数据*/
INSERT INTO student VALUES('P001','小花',25);
INSERT INTO student VALUES('P002','Jack',23);
INSERT INTO student VALUES('P003','Tom',24);
INSERT INTO student VALUES('P004','张三',24);
INSERT INTO student VALUES('P005','赵子龙',26);
INSERT INTO ject VALUES('S001','Java');
INSERT INTO ject VALUES('S002','JavaEE');
INSERT INTO ject VALUES('S003','XML');
INSERT INTO ject VALUES('S004','数据库');
INSERT INTO ject VALUES('S005','JQuery');
INSERT INTO sj VALUES('P001','S001');
INSERT INTO sj VALUES('P001','S003');
INSERT INTO sj VALUES('P002','S001');
INSERT INTO sj VALUES('P002','S002');
INSERT INTO sj VALUES('P002','S003');
INSERT INTO sj VALUES('P003','S001');
INSERT INTO sj VALUES('P004','S002');
INSERT INTO sj VALUES('P004','S003');
/*3.6 数据库操作*/
/*关联: 内连接、左关联、右关联*/
/*要求:查询哪些人选了哪些课*/
SELECT student.NAME AS 学生姓名,ject.NAME AS 课程名称 FROM student INNER JOIN sj ON student.id=sj.studid INNER JOIN ject ON sj.subjectid = ject.id;
/*要求:查询哪些人没有选课*/
SELECT student.NAME AS 学生姓名 FROM student LEFT JOIN sj ON student.id=sj.studid LEFT JOIN ject ON sj.subjectid=ject.id WHERE ject.NAME IS NULL;
/*要求:查询哪些课程没有人选*/
SELECT ject.id AS 课程编号, ject.NAME AS 课程名称 FROM student RIGHT JOIN sj ON student.id=sj.studid RIGHT JOIN ject ON sj.subjectid=ject.id WHERE student.name IS NULL;
/*注意:在左右关联中没有绝对,只是看你怎么连表*/