基础夯实day14:SQL之连接方式

一、表的复杂查询

1.连接查询

1.0连接的基本语法格式：

from TABLE1 join_type  TABLE2 [on (jion_condition)] [where (query_condition)]

TABLE1:左表

TABLE2:右表

jion_type:连接的类型。交叉、内连接、左外连接、右外连接

on:设置连接条件

where:对连接查询的结果的进一步的筛选

1.1交叉连接：笛卡尔积

1.2内连接

隐式内连接

显示内连接

1.3外连接

左外连接

右外连接

使用命令行登入mysql软件，创建数据库（create database），使用数据库(use 数据库名)，导入建表语句（source 路径名称）；

查询结果 中文显示乱码，需要设置查询结果的字符集，为指定的字符集。

1.1 交叉连接

直接登入指定数据库，存在中文显示乱码（set character_set_results= gbk）,交叉连接查询语句：select * from customer cross jion orders 或是直接 select * from customer,order;一般实际查询中很少用到交查询（了解就够了）

注意：表名不要拼错 join不要拼错。

1.2内连接

隐式内连接：（不使用on关键字，使用where）一般查询使用隐式内连接,只不过这里明确一下叫法。

select * from customer c ,orders o where c.id= o.customer_id;

注意：英文状态下输入。

显式内连接（使用on关键字）一般工作中很少这样查询吧，知道就好了。

mysql>select * from customer c  inner join  orders  o  on c.id = o.customer_id;

1.3外连接

左外连接：（返回符合连接条件的所有记录，同时还返回左表中的其余所有的所有记录）其实就是左表是一个基表。

mysql>select * from customer c left outer join orders o on c.id = o .customer_id;

右外连接：（返回符合连接条件的所有记录，同时还返回右表中的其余所有的所有记录）其实就是右表是一个基表。

mysql:select * from customer c right [outer] out  join orders o on  c.id = o.customer_id;这里outer可写可不写。

2子查询（嵌套查询）

一般开发中不建议使用子查询，能用连接查询，一般不推介使用子查询，一般自己使用客户端之类查询不计查询速度，查询数据少的可以使用。

查询"陈冠希"的所有订单信息；

查询"陈冠希"的客户id  select  id  from customer where name='陈冠希'；命令行中使用中文查询需要设置客户端字符集（set character_set_client=gbk）其中中文可以是用双引号也可以使用单引号。Oracle一般查询的字符串只能是单引号，Mysql可以是双引号也可以是单引号。

查询订单信息：select * from orders where customer_id =1;

两个合起来就是：

mysql>select * from orders where customer_id = (select id from customer where name ='陈冠希')；

用英文的分号；，这里只有一个所以可以用=，一般子查询的结果（select id from customer where name ='陈冠希')）要是多个用 in 。

3、联合查询

mysql>select * from orders where price >200 UNION  select  * from  orders  where customer_id=1;(去重的)

mysql>select * from orders where price >200 UNION  ALL select  * from  orders  where customer_id=1;(不去重的)

4.报表统计（合计函数）

count，sum 

注意：要是使用关键字作为表名，要使用`（Esc按键下面的~下面的符号）包围起来；

分组函数（group by ...having....）

日期时间函数：mysql中的是这些不知Oracle对应的是不是也是这些。

ADDTIME(date2,time_interval)	将time_interval加到date2
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime的日期部分
date_add(date2,INTERVAL d_value d_type)	在date2中加上日期或是时间
date_sub(date2,INTERVAL d_value d_type)	在date2上减去一个时间
datediff(date1,date2)	两个日期差
now()	当前时间
YEAR|MONTHER|DATE(datetime)	年月日

字符串相关函数：

charset(str)	返回字符串字符集
concat(string2[,....])	连接字符串
instr(String,subString)	返回subString在string中出现的位置，没有返回0；
ucase(String)	转换成大写
Lcase(string)	转换成小写
left(string,length)	从string中的左边起取length个字符
length(string)	string长度
replace(str,serch_str,replace_str)	在str中用replace_str替换search_str
strcmp(string1,string2)	逐字比较两个字符串的小
substring(str,position[,lenth])	从strpostion开始，取lenth个字符
Ltrim(string) ,rtrim(),trim	去除空格

数学相关函数：

ABS(number2)	绝对值
bin(decimal_number)	十进制转二进制
ceiling(dnumber2)	向上取整
conv(number2,from_base,to_base)	进制转换
floor(number2)	向下取整
format(number,decimal_places)	保留小数位数
HEX(decimalNumber)	十进制转十六进制
Least(number,number2[,..])	求最小值
mod(numerator,denominator)	求余
rand([seed])	求随机数

二、mysql的数据库的备份和恢复

数据库的备份：（不会备份数据库名）

shell>mysqldump [-h localhost] -u root  -psorry test >c:/test.sql注意是p后面紧跟密码 不要有空格,最后不要加分号；.

第一次备份的时候提示。拒绝访问，应该是自己电脑的用户权限问题，那就把备份路径换成d盘，就备份成功了。

登入数据库删除数据（正式环境，不要轻易尝试，删库跑路）

方式一：

恢复数据库：（数据库名必须先存在）

重建数据库，

刚建的数据库没有表：

shell>mysql -u root  -p密码 test<c:/test.sql

方式二：

mysql>use test;

mysql>source d:/test.sql;

自己要注意没有看导入的结果是不是提示有错误和警告；

用第二种方式更加安全一点，以免数据库搞错了。

三、JDBC概述（Java Database Connectivity）java连接数据库的标准，sun公司制订

JDBC和数据库的驱动是什么关系？JDBC是规范，抽象层，数据库驱动是具体实现。

JDBC规范由一些抽象类和接口组成，一般放在java.sal.*或javax.sql.*包中（JDK自带）

开发JDBC应用需要以上两个包以外，还需要导入相应的JDBC数据库实现（即数据库驱动）

四、JDBC编码步骤

准备：把数据库的驱动加入到构建路径中

1、加载驱动程序并注册程序

2、获取与数据库连接

3、得到代表SQL语句的对象，并发送SQL给数据库

4、如果由查询结果，得到封装了查询结果的对象

5、遍历结果

6、释放占用的资源

五、JDBC中常用接口和类的详细讲解

1、DriverManager

作用：注册驱动,得到数据库的连接

注册驱动：

DriverManager.registDriver(new com.mysql.jdbc.Driver());(不可取)； 

原因：1、严重依赖具体数据库驱动。2、会导致驱动注册2遍

替代方案：Class.forName("具体数据库驱动名");例如mysql：Class.forName("com.mysql.jdbc.Driver");

获取数据库的连接：

static Connection getConnection(String url,String user,String password):

                             url:数据库的链接串，

                            mysql数据库的链接串:jdbc:mysql://localhost（数据库IP，这里是web服务器与数据库同一台电                                                        脑）:3306/day14(数据库名)或者jdbc:mysql:///day14(链接本地默认端口数据库)

static Connection getConnection(String url,Properties info):例如：Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day14",info);

static Connection getConnection(String url),例如：DriverManager.getConnection("jdbc:mysql://localhost:3306/day14?user=root&password=root123");

URL用于标识数据库的位置,程序员通过URL地址告诉JDBC程序连接哪个数据库，url的写法：

jabc:myslq:[]//localhost:3306/test?参数名=参数值

jdbc	mysql:[]	localhost:3306	test
协议	子协议	主机：端口	数据库

 

常用数据库URL地址写法：

Oracle写法：jdbc:oracle:thin:@localhost:1521:sid
sqlServer-jdbc:microsoft:sqlserver://localhost:1433:DatabaseName=sid

mysql>jdbc:mysql://localhost:3306/sid

常用属性：useUnicode=true&characterEncoding=UTF-8

SQL类型	JDBC对应方法	返回类型
BIT(1) bit(n)	getBoolean()  getBytes()	Boolean  bytes[]
TINYINT	getByte()	Byte
SMALLINT	getShort()	Short
Int	getInt	Int
BIGINT	getLong()	Long
char,varchar(),longvarchar()	getString()	String
Text(clob),Blob	getClob(),getBolb()	Clob,Blob
Date	getDate()	java.sql.Date()
TIME	getTime()	java.sql.Time
TIMESTAMP	getTimestamp()	java.sql,Timestamp