黑马程序员 SQL和JDBC连接数据库

------- android培训、java培训、期待与您交流！ ----------

 

 

一、多表设计

外键约束的基本语法：

CONSTRAINT 外键约束名称（test库中唯一） FOREIGN KEY(外键字段) REFERENCES 参照表名(字段名)

1、设计一对多

CREATE TABLE department(

id int PRIMARY KEY,

name varchar(100)

);

CREATE TABLE employee(

id int PRIMARY KEY,

name varchar(100),

dept_id int,

salary float(8,2),

CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)

);

2、设计多对多

CREATE TABLE teacher(

id int PRIMARY KEY,

name varchar(100),

salary float(8,2)

);

CREATE TABLE student(

id int PRIMARY KEY,

name varchar(100),

grade varchar(10)

);

CREATE TABLE teacher_student(

t_id int,

s_id int,

PRIMARY KEY(t_id,s_id),

CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),

CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)

);

3、设计一对一

CREATE TABLE person(

id int PRIMARY KEY,

name varchar(100)

);

CREATE TABLE idcard(

id int PRIMARY KEY,

num varchar(100) unique,

CONSTRAINT person_id_fk FOREIGN KEY(id) REFERENCES person(id)

);

（必面）二、多表查询

1、连接查询

基本查询语法：FROM 表1 连接类型 表2  [ON (连接条件)] [WHERE (筛选条件)]

表1：左表

表2：右表

连接类型：CROSS JOIN(交叉连接) INNER JOIN（内连接） LEFT OUTER JOIN（左外连接） RIGHT OUTER JOIN（右外连接）

1.1交叉连接查询：

返回的结果：表1和表2的笛卡尔积。（表1有5条记录，表2有7条记录，返回5*7=35条记录）

SELECT * FROM customer CROSS JOIN orders;

或者

SELECT * FROM customer,orders;

1.2内连接查询：

返回的结果：满足连接条件的记录

1.2.1隐式内连接查询：不使用INNER JOIN关键字

SELECT * FROM customer c,orders o WHERE c.id=o.customer_id;

1.2.2显式内连接查询：使用INNER JOIN关键字

SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;

1.3外连接查询：

1.3.1左外连接查询

返回的结果：返回满足连接条件的所有记录，且还返回左表中剩余的并不满足连接条件的所有记录。

SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;

1.3.2右外连接查询

返回的结果：返回满足连接条件的所有记录，且还返回右表中剩余的并不满足连接条件的所有记录。

SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;

2、子查询

子查询也叫嵌套查询，是指在select子句或者where子句中又嵌入select查询语句

查询“陈冠希”的所有订单信息

SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name='陈冠希');

3、联合查询

联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用UNION关键字

SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;

返回结果：两条语句返回结果的并集且去除了重复的记录。

4、报表查询（使用数据库提供的一些函数）

统计一个班级共有多少学生？

mysql>SELECT COUNT(*) FROM student;

统计数学成绩大于90的学生有多少个？

mysql>SELECT COUNT(*) FROM student WHERE math>90;

统计总分大于250的人数有多少？

mysql>SELECT COUNT(*) FROM student WHERE (chinese+english+math)>250;

统计一个班级数学总成绩？

mysql>SELECT SUM(math) FROM student;

统计一个班级语文、英语、数学各科的总成绩

mysql>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;

统计一个班级语文、英语、数学的成绩总和

mysql>SELECT SUM(math+chinese+english) FROM student;

统计一个班级语文成绩平均分

mysql>SELECT SUM(chinese)/COUNT(*) FROM student;

求一个班级数学平均分？

mysql>SELECT AVG(math) FROM student;

求一个班级总分平均分

mysql>SELECT AVG(math+chinese+english) FROM student;

Tips:

如果想用关键字作为表名等用户自定义的东东，请使用``（不是单引号，~键上的）引起来

对订单表中商品归类后，显示每一类商品的总价

mysql>SELECT product,SUM(price) FROM orderss GROUP BY product;

查询购买了几类商品，并且每类总价大于100的商品

mysql>SELECT product,SUM(price) FROM orderss GROUP BY product HAVING SUM(price)>100;

三、数据库的备份和恢复

1、备份day12数据库（不会备份数据库名称，只会备份库中的所有内容）

shell>mysqldump [-h 主机名或IP] -u root -psorry day12>c:\day12.sql

2、恢复备份的数据

方式一：

mysql>CREATE DATABASE day12;

mysql>USE day12;

mysql>SOURCE c:\\day12.sql;

方式二：

mysql>CREATE DATABASE day12;

shell>mysql -u root -psorry day12<c:\day12.sql

四、JDBC简介

1、JDBC：Java DataBase Connectivity

2、JDBC是一套规范，SUN制定。API在JDK中都自带。

java.sql.*;

javax.sql.*;

3、数据库厂商的驱动就是对JDBC规范的一个实现。

五、JDBC快速入门：编写步骤

0、拷贝数据库的驱动到构建路径中

准备数据库：

create database day12 character set utf8 collate utf8_general_ci;

use day12;

create table users(

id int primary key auto_increment,

name varchar(40),

password varchar(40),

email varchar(60),

birthday date

)character set utf8 collate utf8_general_ci;

insert into users(name,password,email,birthday) values('zs','123456','[e-mail]','1980-12-04');

insert into users(name,password,email,birthday) values('lisi','123456','[e-mail]','1981-12-04');

insert into users(name,password,email,birthday) values('wangwu','123456','[e-mail]','1979-12-04');

1、注册驱动程序

2、获取与数据库的连接

3、创建发送SQL语句的对象，并执行语句

4、如果有查询结果，获取封装了查询结果的对象

5、遍历结果

6、释放占用的资源

六、JDBC用到的接口详解

1、DriverManager：

a、注册驱动：DriverManager.registerDriver(new com.mysql.jdbc.Driver())缺点1，严重依赖具体的驱动类库；缺点2：导致驱动注册2次；

应该怎么做？

Class.forName("com.mysql.jdbc.Driver");

b、获取数据库的连接

Connection getConnection(String url)：url从xml中获取的话，注意xml中的特殊字符的转义问题。

Connection getConnection(String url, Properties info) ：

info：连接参数

Connection getConnection(String url, String user, String password) ：

url：连接数据库的连接串。具体参考连接的数据库的文档。（不同数据库不同，相同数据库的不同版本有可能不同）

jdbc:mysql://localhost:3306/day12 或者 jdbc:mysql:///day12(默认端口的本机数据库)

2、Connection：非常重要，日后详解

3、Statement：代表发送sql语句的对象。

ResultSet executeQuery(String sql):返回有查询结果的SQL语句。DQL语句

int executeUpdate(String sql):sql是DML语句。或者是没有返回结果的DDL语句。  返回值为受影响的行数。

boolean execute(String sql):sql可以是任何sql语句。返回值是有查询结果true，没有查询结果false。

4、ResultSet

索引都是从1开始。

5、释放资源

对象通常是ResultSet, Statement和Connection对象

6、PreparedStatement接口

Connection.prepareStatment(String sql)

作用：

a、防注入

b、预编译

c、SQL语句中的值可以使用站位符"?"代替

 

------- android培训、java培训、期待与您交流！ ----------