unit05-Servlet

Servlet概述

什么是Servlet?

Servlet是由SUN公司提供的一门动态Web资源开发技术

静态Web资源:不同的人,在不同的条件下访问后看到的是相同的效果,这样的资源叫做静态Web资源(html、css、js等)
动态Web资源:在不同的访问条件下看到的是不同的效果,这样的资源叫做动态Web资源
	(Servlet、jsp、.NET、PHP等)

Servlet本质上是一段Java程序,和之前的Java程序不同的是,Servlet程序无法独立运行,需要将Servlet程序放在服务器中(比如tomcat服务器),由服务器调用才可以执行。

Servlet: 服务器端的Java程序.

Servlet是运行在服务器端的Java程序,其作用是什么?

其作用是对服务器接收过来的请求进行处理(作用为处理请求)

开发Servlet程序

开发Servlet程序的步骤

第一步: 写一个类,实现一个Servlet接口或者继承Servlet接口的子类(GenericServlet/HttpServlet),并实现其中的方法

Servlet接口
	|-- GenericServlet类(抽象类)
				|-- HttpServlet类

第二步: 在web应用的web.xml文件中配置Servlet程序对外访问的路径。

Eclipse在创建一个Servlet时,会在web.xml文件中生成Servlet配置,所以不需要我们手动配置。

使用Eclipse创建Web项目

以上是Web项目在工程视图(Project)和包视图(package)下结构,推荐使用包视图!

1、创建一个Web工程: 在左侧窗口中, 点击鼠标右键 ---> New --->
Dynamic Web Project

2、接着会弹出如下窗口:

注意:

(1) 3.0版本不会创建web.xml文件,
并且创建Servlet时也不会在web.xml文件中生成Servlet相关的配置信息, 记得改为2.5。

(2) Target runtime选项中如果没有可选的服务器,可点击右侧的"New
Runtime…"进行配置。

详细操作步骤在《5.2配置Target runtime(Web项目运行环境)》

3、Eclipse中创建的Web工程的目录结构:

(1) day09: 工程名称/项目名称
(2) src: 源码目录, 创建的java源文件、配置文件(properties、xml文件等)都可以放在src源码目录下
(3) build/classes: 编译文件的输出目录, src源码目录中的文件编译后会输出到classes目录下。
	其中的classes目录在发布时会放在WEB-INF目录下,随着项目一起发布到服务器中
(4) WebContent: 就是Web应用的目录,其中可以存放 html、css、js、jsp、图片以及编译后的class文件、jar包、web.xml文件等. 将来发布项目到服务器,其实就是将WebContent中的所有内容一起发布到服务器中。
(5) WebContent/WEB-INF/lib: 用于存放当前项目所依赖的jar包。比如要访问mysql数据库,需要导入mysql驱动包,直接将jar包拷贝到lib目录下即可!(也不用再去做 build path --> add to build path)

使用Eclipse创建Servlet

1、选中项目中的src目录,鼠标右键 ---> New ---> Servlet

2、在弹出的窗口中,根据提示填写内容:

3、点击finish即可完成Servlet创建过程, 创建好的Servlet如下:

通过Eclipse创建Servlet,默认继承HttpServlet。由于HttpServlet也是Servlet接口的子类,让HelloServlet继承HttpServlet,相当于间接实现了Servlet接口。

继承HttpServlet类,默认会覆盖doGet方法和doPost方法,两个方法的作用为:

*** doGet方法:**当浏览器发送请求的方式为GET提交时, 将会调用doGet方法来处理请求

*** doPost方法:**当浏览器发送请求的方式为POST提交时,
将会调用doPost方法来处理请求

提示:如果当GET提交和POST提交处理代码相同时,可以将代码写在其中一个方法里(例如写在doGet中),并在另外一个方法(例如doPost)中调这个方法。这样一来,不管是GET提交还是POST提交,最终doGet方法都会执行,都会对请求进行处理!!

Servlet在web.xml中的配置

在通过Eclipse创建Servlet时,会自动在web.xml文件中进行Servlet相关信息的配置

(注意:如果是复制Servlet类文件,但配置信息不会跟着复制,需要自己手动添加配置,否则复制的Servlet将无法访问!)

<servlet>
    <servlet-name>HelloServlet</servlet-name>
    <servlet-class>com.tedu.HelloServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>HelloServlet</servlet-name>
    <url-pattern>/HelloServlet</url-pattern>
</servlet-mapping>

关于上面的配置信息:

a) Eclipse每创建一个Servlet,就会在web.xml文件中添加两个标签:<servlet>和<servlet-mapping>标签(可以将这两个标签看成一个组的标签)

**b) **<servlet>和<servlet-mapping>标签内都会有一个<servlet-name>标签,标签的内容可以更改,但要求更改后的这两个<servlet-name>标签的内容也必须一致。

c) <servlet-class>标签用于配置Servlet类的全限定类名(即包名+类名)

需要注意:如果在创建Servlet后修改了Servlet类的名称,这个地方也要一起更改,否则将会出现"ClassNotFoundException" 即类找不到异常

**d) **<url-pattern>标签用于配置浏览器以什么路径访问当前Servlet(即Servlet对外访问的路径),默认的路径是:/类名

例如:上面为HelloServlet配置的<url-pattern>为**/HelloServlet**,因此我们在浏览器中的访问路径则为:

http://主机名/web项目访问路径**/HelloServlet**

运行Servlet程序、访问测试

1、访问Servlet方式一:

若是第一次运行,需要先创建tomcat服务器,即在Servers窗口中点击链接可创建一个tomcat服务器,且只需创建一次即可!

(1)发布项目到服务器:在服务器上右键 --> 点击 "add and remove" 将当前web项目发布到服务器中,并点击完成。

(2)启动tomcat服务器:在服务器上右键 Start 即可启动服务器

(3)通过浏览器访问Servlet:打开本地浏览器,通过路径访问,即可访问Servlet程序

http://localhost:端口/项目名称/HelloServlet

2、访问Servlet方式二:

(1) 在运行的Servlet上点击右键 ---> "Run As" ---> "1 Run on Server"

(2) 在弹出的窗口中,直接点击完成即可!!!

(3) 运行结果如下:

或者打开浏览器,复制上图中的路径:

http://localhost:8080/Hello/HelloServlet,粘贴到浏览器的地址栏中,回车访问:

Eclipse如何发布一个Web应用

当通过eclipse将day09项目发布到服务器中,是直接将day09拷贝到服务器中对应的目录下吗?

发布的过程如下:

Eclipse默认发布Web应用的位置

Tomcat服务器中默认只有一台虚拟主机,叫做localhost主机

而localhost主机发布web应用的位置是webapps。

将day09发布到localhost主机中,但为什么day09项目没有在webapps目录下?

--------------------------------------------------------------------------------------------------------

默认情况下,发布一个Web应用到localhost主机中,只需要将Web应用的目录拷贝到webapps目录下即可完成发布!

而将Eclipse和Tomcat整合之后,通2中,发布的路径默认被改成了:

[工作空间]\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps

如何修改Eclipse默认发布Web应用的目录位置:

(1)关闭服务器,将服务器中的所有应用移除

(2)在服务器上右键 --> clean

(3)双击tomcat服务器,在弹出窗口中找到 Server location, 选择第二个选项

并将下方的Deploy Path改为: webapps 改完后,Ctrl+s保存配置即可!!

Servlet调用过程

通过浏览器访问服务器中的一个Servlet程序,这个Servlet程序是如何执行的?又是如何被调用的?

localhost/Hello/HelloServlet

参考<<Servlet调用过程图>>

扩展内容

添加Servlet模版

通过Eclipse可以直接创建一个Servlet类,这相比通过记事本等文本编辑工具创建Servlet,可以节省配置Servlet的时间,提高了我们的开发效率。

但是通过Eclipse生成的Servlet类中包含了许多我们不需要的注释和默认实现代码,这些每次都删除也非常占用时间。

接下来可以通过添加模版代码的形式,来生成Servlet的内容,以便于提高我们的开发效率。

1、先创建一个Servlet,将其中的内容修改为自己期望的模版格式,并复制其中的内容,例如:

package ${enclosing_package};

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * author: bjzhangsz@tedu.cn
 * datetime: ${date} ${time}
 */
public class ${primary_type_name} extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

2、点击菜单栏中的window --> Preferences

3、在出现的窗口左侧依次点击:Java --> Editor --> templates
-->(在右边的窗口中) 点击New… :

4、在出现的新窗口中填写如下内容:

5、替换包路径和类名(作用是在新建Servlet生成的Servlet模版中使用当前类的包路径和类型)

效果如下:

效果如下:

6、点击OK保存,创建新的Servlet文件,测试

将Servlet中的所有内容全选删除,并输入"servlet",接着按 "Alt+ /"
提示即可生成自己想要的Servlet模版内容!

效果如下:

Servlet3.0的特性

上面使用的是Servlet2.5版本,其实JavaEE5.0规范的子规范,要求运行环境最低是JDK5.0,tomcat5.0。

而Servlet3.0规范是JavaEE6.0的子规范,其要求运行环境最低是JDK6.0,tomcat7.0。若要使用Servlet3.0规范,则需要在创建动态Web项目时就要指定。具体用法是,在Eclipse中创建动态Web工程时,指定创建的“动态Web模块版本”为3.0或以上版本,此时创建的Web工程中默认是没有web.xml文件的。

image-20200629235313342

image-20200629235622349

思考:如果没有web.xml文件,那么如何配置Servlet的访问路径呢?

通过注解方式进行配置Servlet访问路径。下面来几个Servlet3.0的示例。

1、在3.0以上版本的动态Web项目中创建一个Servlet,如下:

@WebServlet("/HelloServlet")
public class HelloServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		out.write( "Hello Servlet3.0.."+new Date() );
	}
}

其中@WebServlet("/HelloServlet") 这个注解的作用就是配置当前Servlet的访问路径为/HelloServlet,完善doGet方法中的代码,直接运行Servlet。

可以在浏览器中看到如下效果:

image-20200630001900994

2、@WebServlet注解中可以配置多个访问路径

在@WebServlet注解中配置的访问路径,其实前面省略了value属性,完整写法为:

@WebServlet(value="/HelloServlet"),只不过value可以省略

由于在源码中value属性其本质是一个字符串数组,因此可以为value属性赋值为一个数组直接量。例如:@WebServlet(value={})

将HelloServlet类上的注解改为如下形式:

@WebServlet(value={"/HelloServlet", "/hello01", "/hello02"})
public class HelloServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		out.write( "Hello Servlet3.0.."+new Date() );
	}
}

在浏览器中运行效果为:

3、可以将value属性替换为urlPattern属性

@WebServlet(urlPatterns={"/HelloServlet", "/hello01", "/hello02"})
public class HelloServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		out.write( "Hello Servlet3.0.."+new Date() );
	}
}

在浏览器中运行效果为:

request和response介绍

request是代表HTTP请求信息的对象,response是代表HTTP响应信息的对象。

当浏览器发请求访问服务器中的某一个Servlet时,服务器将会调用Servlet中的service方法来处理请求。在调用service方法之前会创建出request和response对象。

其中request对象中封装了浏览器发送给服务器的请求信息(请求行、请求头、请求实体等),response对象中将会封装服务器要发送给浏览器的响应信息(状态行、响应头、响应实体),在service方法执行完后,服务器再将response中的数据取出,按照HTTP协议的格式发送给浏览器。

每次浏览器访问服务器,服务器在调用service方法处理请求之前都会创建request和response对象。(即,服务器每次处理请求都会创建request和response对象)

在请求处理完,响应结束时,服务器会销毁request和response对象。

request对象

获取请求参数

问题1:什么是请求参数?

所谓的请求参数,就是浏览器发送给服务器的数据(不区分请求方式),例如:通过表单向服务器提交的用户名、密码等,或者在超链接后面通过问号提交的数据,都是请求参数。

http://localhost/day10/TestParam?user=zhangsan&pwd=123&like=篮球&like=足球

问题2:如何获取请求参数?

(1)request.getParameter(String paramName) 
//-- 根据请求参数的名字获取对应的参数值,返回值是一个字符串;
//-- 如果一个参数有多个值,该方法只会返回第一个值。
//-- 如果获取的是一个不存在的参数,返回值为null
(2)request.getParameterValues(String paramName)
//-- 根据请求参数的名字获取该名字对应的所有参数值组成的数组,返回值是一个字符串数组,其中包含了这个参数名对应的所有参数值
//-- 如果获取的是一个不存在的参数,返回值为null

代码示例:

//1.获取请求参数中的用户名(user)
String user = request.getParameter("user");
System.out.println( "user="+user );

//2.获取请求参数中的爱好(like)
String[] like = request.getParameterValues( "like" );
System.out.println( "like="+Arrays.toString( like ) );

问题3:如何解决获取请求参数时的中文乱码问题?

在获取中文的请求参数时,可能会出现乱码问题(和请求方式、tomcat服务器版本有关),具体可以分为以下三种情况:

(1)如果请求是GET提交,并且tomcat是8.0及以后的版本,GET提交的中文参数,在获取时不会出现乱码问题!(8.0以后的tomcat包括8.0在获取GET提交的中文参数时,已经处理中文乱码问题。)

(2)如果请求是POST提交,不管是哪个版本的tomcat服务器,在获取中文参数时,都会出现乱码问题。因为tomcat底层在接收POST提交的参数时,默认会使用iso8859-1编码接收,而这个编码中没有中文字符,所以在接收中文参数时,一定会出现中文乱码问题!

解决方法是:通知服务器在接收POST提交的参数时,使用utf-8编码来接收!

request.setCharacterEncoding("utf-8");

注意:这行代码不会影响GET提交,只对POST提交有效!

这行代码要放在任何获取参数的代码之前执行!

(3)如果请求是GET提交,并且tomcat是7.0及以前的版本,GET提交的中文参数,在获取时会出现乱码问题!

解决方法:在[tomcat安装目录]/ conf/server.xml文件的(修改端口的)Connector标签上,添加一个 URIEncoding="utf-8" 属性,如下:

<Connector port="80" protocol="HTTP/1.1"
           connectionTimeout="20000"
           redirectPort="8443"
           URIEncoding="utf-8" />

同时在[Eclipse]/Servers/[当前tomcat服务器对应的配置目录]/server.xml文件中,在Connector标签上,添加一个 URIEncoding="utf-8" 属性,同上!

实现请求转发

请求转发是服务器内部资源的一种跳转方式,即当浏览器发送请求访问服务器中的某一个资源(A)时,该资源将请求转交给另外一个资源(B)进行处理并且由资源B做出响应的过程,就叫做请求转发。

请求转发和重定向都是资源的跳转方式,但是跳转的过程有所不同。

请求转发的特点:

(1)转发是一次请求,一次响应
(2)请求转发前后,浏览器的地址栏地址不会发生变化。(浏览器--访问--> A --转发--> B,地址栏地址始终指向A的地址)
(3)请求转发前后的request对象是同一个(转发前在A中的request和转发到B后,B中的request对象和A中的request对象是同一个。基于这一点,可以通过request从A带数据到B)
(4)请求转发前后的两个资源必须属于同一个Web应用,否则将无法进行转发。(A--转发-->B,A和B必须属于同一个Web应用!)

请求转发实现:

request.getRequestDispatcher(url地址/转发到资源的地址).forward(req, res);

代码示例:

//从当前Servlet转发到 index.jsp(http://localhost/day10/index.jsp)
//request.getRequestDispatcher("/index.jsp").forward(request, response);
request.getRequestDispatcher("index.jsp").forward(request, response);
image-20200221171059404

作为域对象使用

request在实现转发时,通过request.setAttribute方法和request.getAttribute方法带数据到目的地时,就是通过request对象中的map集合带数据,这个request对象上的map集合以及request对象所在的范围即称之为是一个域对象。

如果一个对象具备可以被访问的范围,通过这个对象上的map集合可以在整个范围内实现数据的共享。这样的对象就叫做域对象。

在request对象上提供了往域对象(map)中存数据的方法以及取数据的方法:

request.setAttribute(String attrName, Object attrValue);
-- 往request域中存入一个域属性,属性名(key)只能是字符串,属性值(value)可以是任意类型。
request.getAttribute(String attrName);
-- 根据属性名(key)获取对应的属性值(value)。返回的是一个Object类型的对象。

request域对象所具备的三大特征:

**生命周期:**在服务器调用Servlet程序的service方法之前,会创建代表请求的request对象,在请求处理完,响应结束时,会销毁request对象。

**作用范围:**在一次请求范围内,都可以获取到同一个request对象,通过request域带数据到目的地。

**主要功能:**和请求转发配合使用,从Servlet带数据到JSP(带数据到目的地)

**扩展内容:**request对象的getParameter和getAttribute方法有什么区别?

  • getParameter()方法是用于获取(从浏览器发送过来的)请求参数的,请求参数不能设置,只能是浏览器发送给服务器,在服务器端再通过getParameter方法获取请求中的参数
  • getAttribute()方法是用于从request域中获取域属性时用的,域属性得先存入到域中(即得先通过setAttribute方法将数据存入request域中),再通过getAttribute()方法从域中获取。

response对象

response是代表HTTP响应信息的对象。

向客户端发送数据

PrintWriter out = response.getWriter();

由于服务器在通过response获取的流发送数据时,默认使用iso8859-1编码,而这个编码中没有中文字符,所以在通过response获取的流发送中文数据时,会出现乱码问题。

解决方法是:在响应数据之前,通知服务器使用utf-8发送数据。

/*  通知服务器在响应数据时,使用utf-8编码
 * 也能通知浏览器使用utf-8接收服务器发送的数据 */
response.setContentType( "text/html;charset=utf-8" );
PrintWriter out = response.getWriter();
out.write( "你好" );

实现重定向

当浏览器向服务器发请求访问某一个资源A,资源A在响应时通知浏览器需要再进一步请求才能获取到对应的资源,浏览器再次发请求访问服务器中的资源B,最终由资源B响应浏览器要获取的资源,这个过程叫做重定向。

重定向的特点:

(1)重定向是两次请求、两次响应
(2)重定向前后,浏览器的地址栏地址会发生变化。(因为两次请求都是通过浏览器发起,浏览器知道这个跳转的过程,因此地址栏地址会变化)
(3)重定向前后的request对象不是同一个(因为重定向是两次请求,服务器会根据两次请求创建两个不同的request对象,request对象不是同一个,也就不能在重定向时通过request带数据到目的地。)
(4)重定向前后的两个资源可以是来自不同的web应用,甚至可以是来自不同的服务器。(进行跳转的两个资源之间没有限制)

实现代码:

response.sendRedirect(所重定向到资源的URL地址);

代码示例:

//测试1: 从当前Servlet(day10/TestRedirect)重定向到day10/index.jsp
// http://localhost/day10/TestRedirect
// http://localhost/day10/index.jsp
response.sendRedirect( "http://localhost/day10/index.jsp" );
response.sendRedirect( "/day10/index.jsp" );
response.sendRedirect( "/index.jsp" ); //错误路径
response.sendRedirect( "index.jsp" ); //正确路径

//测试2: 从当前Servlet重定向到day09/index.jsp
response.sendRedirect( "http://localhost/day09/index.jsp" );

//测试3: 从当前Servlet重定向到百度首页
response.sendRedirect( "http://www.baidu.com" );

总结-1:请求转发(forward)和重定向(redirect)的区别?

(1)请求转发是一次请求,一次响应; 而重定向是两次请求两次响应
(2)请求转发前后地址栏地址不会发生变化; 而重定向前后地址栏地址会发生变化
(3)请求转发前后的request对象是同一个,可以配合request域对象带数据到目的地; 而重定向前后的request对象不是同一个, 不能结合request域对象在重定向前后带数据.
(4)请求转发要求两个资源必须属于同一个Web应用; 而进行重定向的两个资源可以是同一个Web应用,也可以不是同一个Web应用,甚至可以是来自于不同的主机或服务器.

总结-2:什么时候用请求转发(forward)?什么时候用重定向(redirect)?

(1)如果希望跳转前后地址栏地址不会发生变化, 只能使用转发; 如果希望跳转前后地址栏地址会发生变化, 只能使用重定向
(2)如果希望在跳转前后, 能够通过request对象带数据到目的地, 只能使用转发
(3)如果仅仅是做一个跳转,没有其他要求,此时推荐使用转发(转发是一次请求,一次响应,可以减少访问服务器的次数,降低服务器的压力)

unit06-MySQL

数据库概述

什么是数据库?

所谓的数据库就是指存储和管理数据的仓库

扩展内容1:数据库有哪些分类?(了解)

早期: 层次式数据库、网络型数据库
现在:关系型数据库、非关系型数据库

什么是关系型数据库?

底层以二维表的形式保存数据的库就是关系型数据库

stu-学生表

学生编号姓名年龄
1001刘沛霞35
1002陈子枢18

扩展内容2:常见的关系型数据库有哪些?(了解)

  • Sql Server:微软提供,收费,适用于一些中型或大型的项目中,在java中的使用占比不高(.NET中使用的较多)

  • Oracle:甲骨文公司提供,收费,适用于一些大型或者超大型的项目中,在java中的使用占比非常高

  • mysql:瑞典MySQLAB公司提供,免费开源,适用于一些小型或者中型的项目中,在Java中的使用占比较高(小巧轻量)
    mariadb其实就是MySQL的一个分支,用法和MySQL完全一样。

  • DB2:IBM公司提供,收费,在一些银行、金融等行业中使用较多。在java中的使用占比也不高。

  • Sqlite:迷你数据库,嵌入式设备中(安卓、苹果手机、pad)

数据库相关概念

1、什么是数据库服务器

数据库服务器就是一个软件(比如mysql软件)将数据库软件安装在电脑上,当前电脑就是一个数据库服务器。就可以对外提供存取数据的服务

在一个数据库服务器中可以创建多个数据库(dataBases),每一个数据库都是一个单独的仓库。

2、什么是数据库

数据库就是存储和管理数据的仓库,通常情况下,一个网站的中的所有数据会存放在一个数据库中。例如:

jd.com 				db_jd(数据库)
taobao.com 		db_taobao(数据库)
...

3、什么是表

一个数据库中可以创建多张表,每张表用于存储一类信息(数据库),例如:

jd.com中的用户数据 tb_user(表)

jd.com中的商品数据 tb_product(表)

jd.com中的订单数据 tb_order(表)

...

4、什么表记录·

一张表中可以包含多行表记录,每一行表记录用于存储某一个具体的数据

学生编号姓名年龄
1001刘沛霞35
1002陈子枢18
。。。。。。。。。

什么是SQL语言?

SQL是一门用于操作关系型数据库的通用的语言(使用SQL可以操作所有的关系型数据库)

使用SQL可以操作数据库、表、表记录

(1)创建数据库、删除数据库、修改数据库、查询数据库

(2)创建表、删除表、修改表、查询表

(3)新增表记录、删除表记录、修改表记录、查询表记录

使用SQL也可以操作存储过程/视图/索引等。

提示:SQL是一个标准通用的操作关系型数据库的语言(普通话),每个数据库厂商为了增强自己数据库的功能,都提供了支持自己数据库的语言,称之为数据库的方言。方言不通用!

连接mysql服务器

通过命令行工具可以登录MySQL客户端,连接MySQL服务器,从而访问服务器中的数据。

1、连接mysql服务器:

mysql -uroot -p密码

**-u:**后面的root是用户名,这里使用的是超级管理员root;

**-p:(小写的p)**后面的root是密码,这是在安装MySQL时就已经指定的密码;

2、连接mysql服务器并指定IP和端口:

mysql -uroot -proot -h127.0.0.1 -P3306

**-h:**后面给出的127.0.0.1是服务器主机名或ip地址,可以省略的,默认连接本机;

**-P:(大写的P)**后面的3306是连接端口,可以省略,默认连接3306端口;

3、退出客户端命令:quit或exit或 \q

4、FAQ:常见问题:

解决方法:复制mysql安装目录下的bin目录的路径,将bin目录的路径添加到path环境变量中!!

扩展内容3:

(1)在cmd中连接mysql服务器之后,可以使用 #、/**/、-- 等符号添加注释,例如:

(2)在cmd中连接mysql服务器之后,在书写SQL语句时,可以通过 \c 取消当前语句的执行。例如:

数据库及表操作

创建、删除、查看数据库

提示: (1)SQL语句对大小写不敏感。推荐关键字使用大写,自定义的名称(库名,表名,列名等)使用小写。

SHOW DATABASES; -- 查看当前数据库服务器中的所有库
CREATE DATABASE mydb1; -- 创建mydb1库

(2)并且在自定义名称时,针对多个单词不要使用驼峰命名,而是使用下划线连接。(例如:tab_name,而不是 tabName )

-- 01.查看mysql服务器中所有数据库

show databases; -- 查询服务器所有的数据库
show tables; -- 查询当前库所有表

-- 02.进入某一数据库(进入数据库后,才能操作库中的表和表记录)

-- 语法:USE 库名;

use mysql; -- 进入到‘mysql’数据库
show tables; -- 查询当前库中的所有表

-- 查看已进入的库(了解)

select database();

-- 03.查看当前数据库中的所有表

-- 先进入某一个库,再查看当前库中的所有表
use test;
show tables;

-- 04.删除mydb1库

-- 语法:DROP DATABASE 库名;

drop database mydb1; -- 删除mydb1库,但如果删除的库不存在,则会报错

-- 思考:当删除的库不存在时,如何避免错误产生?

drop database if exists mydb1;
-- 如果mydb1库存在则删除,如果不存在,也就不执行删除操作!

-- 05.重新创建mydb1库,指定编码为utf8

-- 语法:CREATE DATABASE 库名 CHARSET 编码;

-- 需要注意的是,mysql中不支持横杠(-),所以utf-8要写成utf8;

create database mydb1 charset utf8;

-- 如果不存在则创建mydb1;

create database if not exists mydb1 charset utf8; 

-- 06.查看建库时的语句(并验证数据库库使用的编码)

-- 语法:SHOW CREATE DATABASE 库名;

show create database mydb1;

创建、删除、查看表

-- 07.进入mydb1库,删除stu学生表(如果存在)

-- 语法:DROP TABLE 表名;

use mydb1; -- 进入mydb1库
drop table if exists stu;-- 如果存在stu表,则删除

-- 08.创建stu学生表(编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]),建表的语法:

CREATE TABLE 表名(
	列名 数据类型,
	列名 数据类型,
	...
  	列名 数据类型
);

SQL语句:

-- 如果存在,则删除stu表
drop table if exists stu;
-- 创建stu学生表
create table stu(
  id int primary key auto_increment, -- 给id添加主键约束,并设置自增
  name varchar(50),
  gender varchar(10) not null, -- 给gender添加非空约束
  birthday date,
  score double
);

-- 09.查看stu学生表结构

-- 语法:desc 表名

desc stu;
image-20200316141717739

新增、更新、删除表记录

-- 10.往学生表(stu)中插入记录(数据)

-- 语法:INSERT INTO 表名(列名1,列名2,列名3...) VALUES(值1,值2,值3...);

-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
-- 由于id已经设置了主键自增,所以在插入数据,id可以不用给值
insert into stu(id,name,gender,birthday,score) value (null,'tom','male','2000-3-4',89);
insert into stu value(null,'john','male','2002-5-6',78);
insert into stu value(null,'andy','female','2004-7-6',91);
-- 查询学生表中的所有记录
select * from stu;

提示:

(1)当为所有列插入值时,可以省写列名,但值的个数和顺序必须和声明时列的个数和顺序保持一致!
(2)SQL语句中的值为字符串或日期时,值的两边要加上单引号(有的版本的数据库双引号也可以,但推荐使用单引号)。
(3)(针对cmd窗口)在插入数据之前,先设置编码:set names gbk;
或者用以下命令连接mysql服务器:
	mysql --default-character-set=gbk -uroot -proot
等价于:
	mysql -uroot -proot
	set names gbk;

-- 11.查询stu表所有学生的信息

-- 语法:SELECT 列名 | * FROM 表名

select * from stu;

-- 12.修改stu表中所有学生的成绩,加10分特长分

-- 修改语法: UPDATE 表名 SET 列=值,列=值,列=值...[WHERE子句];

update stu set score=score+10;
-- score+=10 mysql中不支持+=

-- 13.修改stu表中编号为1的学生成绩,将成绩改为83分。

update stu set score=83 where id=1;
-- 修改3号学生的性别为 'male',成绩改为99;
update stu set score=99,gender='male' where id=3;

提示:where子句用于对记录进行筛选过滤,保留符合条件的记录,将不符合条件的记录剔除。

-- 14.删除stu表中所有的记录

-- 删除记录语法: DELETE FROM 表名 [where子句]

delete from stu; -- 删除stu表中的所有记录

-- 仅删除符合条件的

delete from stu where id>2; -- 删除stu表中id大于2的记录

查询表记录

-- 准备数据: 以下练习将使用db10库中的表及表记录,请先进入db10数据库!!!

基础查询

SELECT 语句用于从表中选取数据。结果被存储在一个结果表中(称为结果集)。

语法:SELECT 列名称 | * FROM 表名

提示:(1) *(星号)为通配符,表示查询所有列。

(2)但使用 *(星号)有时会把不必要的列也查出来了,并且效率不如直接指定列名

-- 15.查询emp表中的所有员工,显示姓名,薪资,奖金

select name,sal,bonus from emp;

-- 16.查询emp表中的所有部门和职位

select dept,job from emp;

思考:如果查询的结果中,存在大量重复的记录,如何剔除重复记录,只保留一条? */

-- 在select之后、列名之前,使用DISTINCT 剔除重复的记录

select distinct dept,job from emp;

WHERE子句查询

WHERE子句查询语法:SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值

WHERE子句后面跟的是条件,条件可以有多个,多个条件之间用连接词(or | and)进行连接

下面的运算符可在 WHERE 子句中使用:

-- 17.查询emp表中【薪资大于3000】的所有员工,显示员工姓名、薪资

select name,sal from emp where sal>3000;

-- 18.查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工,显示员工姓名、总薪资

select name,sal+bonus from emp; -- 求所有员工的总薪资

select name,sal+bonus from emp
where sal+bonus > 3500;

-- 如果将'韩少云'的奖金更新为 null值,再执行上面的SQL语句,会有问题吗?

update emp set bonus=null where name='韩少云';

-- ifnull(列名, 值)函数: 判断指定的列是否包含null值,如果有null值,用第二个值替换null值

select name,sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0) > 3500;

-- 注意查看上面查询结果中的表头,如何将表头中的 sal+bonus 修改为 "总薪资"

-- 使用as可以为表头指定别名

select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;

-- 另外as可以省略

select name  姓名,sal+ifnull(bonus,0)  总薪资 from emp
where sal+ifnull(bonus,0) > 3500;

-- 19.查询emp表中【薪资在3000和4500之间】的员工,显示员工姓名和薪资

select name, sal from emp
where sal>=3000 and sal<=4500;

-- 提示: between...and... 在...和...之间

select name, sal from emp
where sal between 3000 and 4500; -- 包括3000,也包括4500

-- 20.查询emp表中【薪资为 1400、1600、1800】的员工,显示员工姓名和薪资

select name,sal from emp
where sal=1400 or sal=1600 or sal=1800;

-- 或者

select name,sal from emp
where sal in(1400,1600,1800);

-- 21.查询薪资不为1400、1600、1800的员工,显示员工姓名和薪资

select name,sal from emp
where not(sal=1400 or sal=1600 or sal=1800);
-- 或
select name,sal from emp
where sal not in(1400,1600,1800);

-- 22.(自己完成) 查询emp表中【薪资大于4000和薪资小于2000】的员工,显示员工姓名、薪资。

select name,sal from emp
where sal>4000 or sal<2000;

-- 23.(自己完成) 查询emp表中薪资大于3000并且奖金小于600的员工,显示员工姓名、薪资、奖金。

select name,sal,bonus from emp
where sal>3000 and bonus<600; -- 结果有误差

-- 处理null值

select name,sal,ifnull(bonus,0) from emp
where sal>3000 and ifnull(bonus,0)<600;

-- 24.查询没有部门的员工(即部门列为null值)

select * from emp where dept=null; -- 条件错误!
select * from emp where dept is null; -- 条件正确!

-- 思考:如何查询有部门的员工(即部门列不为null值)

select * from emp where dept is not null;

模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

可以和通配符(%、_)配合使用,其中"%"表示0或多个任意的字符,"_"表示一个任意的字符

语法:SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值

示例:

-- 25.查询emp表中姓名中以"刘"字开头的员工,显示员工姓名。

select name from emp where name like '刘%';

-- 26.查询emp表中姓名中包含"涛"字的员工,显示员工姓名。

select name from emp where name like '%涛%';

-- 27.查询emp表中姓名以"刘"开头,并且姓名为两个字的员工,显示员工姓名。

select name from emp where name like '刘_';
select name from emp where name like '刘__';

多行函数查询

多行函数也叫做聚合(聚集)函数,根据某一列或所有列进行统计。

常见的多行函数有:

多行函数作用
COUNT( 列名 | * )统计结果集中指定列的记录的行数。
MAX( 列名 )统计结果集中某一列值中的最大值
MIN( 列名 )统计结果集中某一列值中的最小值
SUM( 列名 )统计结果集中某一列所有值的和
AVG( 列名 )统计结果集中某一列值的平均值

提示:(1)多行函数不能用在where子句中

(2)多行函数和是否分组有关,分组与否会直接影响多行函数的执行结果。

(3)多行函数在统计时会对null值进行过滤,直接将null值丢弃,不参与统计。

-- 28.统计emp表中薪资大于3000的员工个数

select count(*) from emp where sal>3000; -- 7
select count(id) from emp where sal>3000; -- 7

-- 29.求emp表中的最高薪资

select max(sal) from emp; -- 返回最高薪资,5000

-- 30.统计emp表中所有员工的薪资总和(不包含奖金)

select sum(sal) from emp; -- 薪资总和:39650
select sum(bonus) from emp; -- 奖金总和:5900,多行函数对null会处理,直接丢弃,不参与统计

-- 31.统计emp表员工的平均薪资(不包含奖金)

select avg(sal) from emp; -- 39650/12

多行函数需要注意的问题:

  • 多行函数和是否分组有关,如果查询结果中的数据没有经过分组,默认整个查询结果是一个组,多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。

  • 如果查询结果中的数据经过分组(分的组不止一个),多行函数会根据分的组进行统计,有多少个组,就会统计出多少个结果。

select * from emp;

例如:统计emp表中的人数

select count(*) from emp; -- 12

结果返回的就是emp表中的所有人数

再例如:根据性别对emp表中的所有员工进行分组,再统计每组的人数,显示性别和对应人数

select count(*) from emp group by gender; -- 10, 2

分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT,SUM,AVG,MAX,MIN等函数。

语法:SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;

-- 32.对emp表,按照部门对员工进行分组,查看分组后效果。

-- 根据部门对员工进行分组
select name,dept from emp group by dept;
-- 统计分组后,每组的人数
select count(*) from emp group by dept; -- 3个组,所以会统计出三个结果

-- 33.对emp表按照职位进行分组,并统计每个职位的人数,显示职位和对应人数

-- 根据job进行分组,统计每个组的人数(每个职位的人数)
select job,count(*) from emp group by job;

-- 34.对emp表按照部门进行分组,求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资

-- 如果不分组,直接使用max(sal),这是统计整个emp表中的最高薪资
select max(sal) from emp; -- 5000;
-- 如果根据部门分组,可以分为三个组,再使用max(sal),就是统计每个组的最高薪资
select dept, max(sal) from emp group by dept;

排序查询

使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回

语法:SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]

ASC(默认)升序,即从低到高;DESC 降序,即从高到低。

-- 35.对emp表中所有员工的薪资进行升序(从低到高)排序,显示员工姓名、薪资。

select name,sal from emp order by sal asc;
-- 默认是升序,asc可以省略
select name,sal from emp order by sal;

-- 36.对emp表中所有员工的奖金进行降序(从高到低)排序,显示员工姓名、奖金。

select name,bonus from emp order by bonus desc;

分页查询

在mysql中,通过limit进行分页查询,查询公式为:

limit (页码-1)*每页显示记录数, 每页显示记录数

-- 37.查询emp表中的所有记录,分页显示:每页显示3条记录,返回所有页的数据。

-- 每页显示3条,查询第1页数据
select * from emp limit 0,3;
-- 每页显示3条,查询第2页数据
select * from emp limit 3,3;
-- 每页显示3条,查询第3页数据
select * from emp limit 6,3;
-- 每页显示3条,查询第4页数据
select * from emp limit 9,3;

-- 38.求emp表中薪资最高的前3名员工的信息,显示姓名和薪资

-- 先根据薪资降序(从高到低)排序
select name,sal from emp order by sal desc;
-- 在排序的基础上,分页查询,每页显示3条,只查询第1页
select name,sal from emp order by sal desc limit 0,3;

其他函数

函数名解释说明
curdate()获取当前日期,格式是:年月日
curtime()获取当前时间 ,格式是:时分秒
sysdate()/now()获取当前日期+时间,格式是:年月日 时分秒
year(date)返回date中的年份
month(date)返回date中的月份
day(date)返回date中的天数
hour(date)返回date中的小时
minute(date)返回date中的分钟
second(date)返回date中的秒
CONCAT(s1,s2..)将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2..)同CONCAT(s1,s2,..)函数,但是每个字符串之间要加上x,x是分隔符

-- 39.查询emp表中所有【在1993和1995年之间出生】的员工,显示姓名、出生日期。

-- 将运算符两边的值都转成日期类型,再进行比较!
select name,birthday from emp 
where birthday>='1993-1-1' and birthday<='1995-12-31';
-- 或者,将日期中的年份提取出来,用年份和年份进行比较
select name,birthday from emp
where year(birthday)>=1993 and year(birthday)<=1995;

-- 40.查询emp表中本月过生日的所有员工

select * from emp
where month( now() )=month( birthday );

-- 41.查询emp表中员工的姓名和薪资(薪资格式为: xxx(元) )

select name,concat(sal,'(元)') from emp; -- concat(s1,s2,s3,s4...)

-- 补充练习:查询emp表中员工的姓名和薪资(薪资格式为: xxx/元 )

-- 用concat函数实现
select name,concat(sal,'/元') from emp;
-- 或,用concat_ws函数实现, concat_ws(x,s1,s2,s3...)
select name,concat_ws('/', sal, '元') from emp;

mysql的数据类型

数值类型

MySQL中支持多种整型,其实很大程度上是相同的,只是存储值的大小范围不同而已。

tinyint:占用1个字节,相对于java中的byte

smallint:占用2个字节,相对于java中的short

int:占用4个字节,相对于java中的int

bigint:占用8个字节,相对于java中的long

其次是浮点类型即:float和double类型

float:4字节单精度浮点类型,相对于java中的float

double:8字节双精度浮点类型,相对于java中的double

字符串类型

1、char(n) 定长字符串,最长255个字符。n表示字符数,例如:

-- 创建user表,指定用户名为char类型,字符长度不超过10

create table user(
    username char(10),
    ...
);

所谓的定长,是当插入的数据的长度小于指定的长度时,剩余的空间会用空格填充。(这样会浪费空间)

char类型往往用于存储长度固定的数据。

2、varchar(n) 变长字符串,最长不超过65535个字节,n表示字符数,一般超过255个字符,会使用text类型,例如:

iso8859-1码表:一个字符占用1个字节,1*n < 65535, n最多等于 65535
utf8码表:一个中文汉字占用3个字节,3*n < 65535,n最多等于 65535/3
GBK码表:一个中文汉字占用2个字节,2*n < 65535,n最多等于 65535/2

-- 创建user表,指定用户名为varchar类型,长度不超过10个字符

create table user(
	username varchar(10)
);

所谓的不定长,是当插入的数据的长度小于指定的长度时,剩余的空间可以留给别的数据使用。(节省空间)

总结:长度固定的数据,用char类型,这样既不会浪费空间,效率也比较高

如果长度不固定,使用varchar类型,这样不会浪费空间。

3、大文本(长文本)类型

最长65535个字节,一般超过255个字符列的会使用text。

-- 创建user表:

create table user(
	resume text
);

另,text也分多种,其中bigtext存储数据的长度约为4GB。

扩展内容3:(面试题)char(n)、varchar(n)、text都可以表示字符串类型,其区别在于:

(1)char(n)在保存数据时,如果存入的字符串长度小于指定的长度n,后面会用空格补全,因此可能会造成空间浪费,但是char类型的存储速度较varchar和text快。

因此char类型适合存储长度固定的数据,这样就不会有空间浪费,存储效率比后两者还快!

(2)varchar(n)保存数据时,按数据的真实长度存储,剩余的空间可以留给别的数据用,因此varchar不会浪费空间。

因此varchar适合存储长度不固定的数据,这样不会有空间的浪费。

(3)text是大文本类型,一般文本长度超过255个字符,就会使用text类型存储。

日期类型

date:年月日

time:时分秒

datetime:年月日 时分秒

timestamp:时间戳(实际存储的是一个时间毫秒值),与datetime存储日期格式相同。两者的区别是:

  • timestamp最大表示2038年,而datetime范围是1000~9999

  • timestamp在插入数据、修改数据时,可以自动更新成系统当前时间(后面用到时再做讲解)

mysql的字段约束

字段约束/列约束 --> 约束: 限制

主键约束

主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。

主键的作用: 作为一个唯一标识,唯一的表示一条表记录(作用类似于人的身份证号,可以唯一的表示一个人一样。)

添加主键约束,例如将id设置为主键:

create table stu(
	id int primary key,
	...
);

如果主键是数值类型,为了方便插入主键(并且保证插入数据时,主键不会因为重复而报错),可以设置一个主键自增策略。

主键自增策略是指:设置了自增策略的主键,可以在插入记录时,不给id赋值,只需要设置一个null值,数据库会自动为id分配一个值(AUTO_INCREMENT变量,默认从1开始,后面依次+1),这样既可以保证id是唯一的,也省去了设置id的麻烦。

将id主键设置为自增:

create table stu(
	id int primary key auto_increment,
	...
);

非空约束

非空约束:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。

添加非空约束,例如为password添加非空约束:

create table user(
	password varchar(50) not null,
	...
);

唯一约束

唯一约束:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。

添加唯一约束,例如为username添加唯一约束及非空约束:

create table user(
	username varchar(50) unique not null,
	...
);

外键约束

外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。

这样数据库就会帮我们维护两张表中数据之间的关系。

(1) 创建表的同时添加外键

create table emp(
	id int,
	name varchar(50),
	dept_id int,
	foreign key(dept_id) references dept(id)
);

(1)如果是要表示两张表的数据之间存在对应关系,只需要在其中的一张表中添加一个列,保存另外一张表的主键,就可以保存两张表数据之间的关系。

但是添加的这个列(dept_id)对于数据库来说就是一个普通列,数据库不会知道两张表存在任何关系,因此数据库也不会帮我们维护这层关系。

(2)如果将dept_id列设置为外键,等同于通知数据库,部门表和员工表之间存在对应关系,dept_id列中的数据要参考部门的主键,数据库一旦知道部门和员工表之间存在关系,就会帮我们维护这层关系。

思考:如果在创建表时没有指定外键,那么后期该如何指定外键?以及如何删除外键?

表关系

常见的表关系分为以下三种:

一对多(多对一)、一对一、多对多

多表查询

-- 准备数据: 以下练习将使用db30库中的表及表记录,请先进入db30数据库!!!

连接查询

-- 42.查询部门和部门对应的员工信息

select * from dept,emp;

上面的查询中存在大量错误的数据,一般我们不会直接使用这种查询。

笛卡尔积查询:所谓笛卡尔积查询就是指,查询两张表,其中一张表有m条记录,另一张表有n条记录,查询的结果是m*n条。

虽然笛卡尔积查询中包含大量错误数据,但我们可以通过where子句将错误数据剔除,保留下来的就是正确数据。

-- 员工所属的部门编号,等于部门的编号
select * from dept,emp
where emp.dept_id=dept.id;

通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!

上面的查询可以换成下面的查询:

select * from dept inner join emp
on emp.dept_id=dept.id; -- 内连接查询,和上面的查询结果一样。

左外连接查询

-- 43.查询【所有部门】及部门对应的员工,如果某个部门下没有员工,员工显示为null

select * from dept left join emp
on emp.dept_id=dept.id;

左外连接查询:可以将左边表中的所有记录都查询出来,右边表只显示和左边相对应的数据,如果左边表中某些记录在右边没有对应的数据,右边显示为null即可。

右外连接查询

-- 44.查询【所有员工】及员工所属的部门,如果某个员工没有所属部门,部门显示为null即可

select * from dept right join emp
on emp.dept_id=dept.id;

右外连接查询:可以将右边表中的所有记录都查询出来,左边表只显示和右边相对应的数据,如果右边表中某些记录在左边没有对应的数据,可以显示为null。

扩展:如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录),可以使用全外连接查询,但是mysql又不支持全外连接查询。

select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on emp.dept_id=dept.id;

可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起,并去除重复的记录。例如:

需要注意的是:union可以将两条SQL语句执行的结果合并,但是有前提:

(1)两条SQL语句查询的结果列数必须一致

(2)两条SQL语句查询的结果列名、顺序也必须一致

并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录,可以使用union all)

子查询练习

-- 准备数据:以下练习将使用db40库中的表及表记录,请先进入db40数据库!!!

-- 45.列出薪资比'王海涛'的薪资高的所有员工,显示姓名、薪资

-- 求出‘王海涛’的薪资
select sal from emp where name='王海涛'; -- 2450
-- 求出比'王海涛'薪资高的所有员工信息
select name,sal from emp 
where sal>(select sal from emp where name='王海涛');

-- 46.列出与'刘沛霞'从事相同职位的所有员工,显示姓名、职位。

-- 求出'刘沛霞'从事的职位
select job from emp where name='刘沛霞';
-- 求出和‘刘沛霞’从事相同职位的员工
select name,job from emp 
where job=(select job from emp where name='刘沛霞');

-- 47.列出薪资比'大数据部'部门(已知部门编号为30)所有员工薪资都高的员工信息,显示员工姓名、薪资和部门名称。

如果不考虑没有部门的员工

-- 先连接查询部门表和员工表
select emp.name,sal,dept.name from dept,emp 
where emp.dept_id=dept.id;
-- 求出‘大数据’部门的最高薪资
select max(sal) from emp where dept_id=30;
-- 列出薪资比 ‘大数据’部门最高薪资还高的员工信息
select emp.name,sal,dept.name from dept,emp 
where emp.dept_id=dept.id and
	sal > (select max(sal) from emp where dept_id=30);

如果加上没有部门的员工

-- 使用外连接查询部门表和员工表
select emp.name,sal,dept.name from dept right join emp 
on emp.dept_id=dept.id;
-- 求出‘大数据’部门的最高薪资
select max(sal) from emp where dept_id=30;
-- 列出薪资比 ‘大数据’部门最高薪资还高的员工信息
select emp.name,sal,dept.name from dept right join emp 
on emp.dept_id=dept.id 
where sal > (select max(sal) from emp where dept_id=30);

多表查询练习

-- 48.列出在'培优部'任职的员工,假定不知道'培优部'的部门编号,显示部门名称,员工名称。

-- 连接查询部门表和员工表
select dept.name,emp.name from dept,emp
where emp.dept_id=dept.id;
-- 求出部门名称为‘培优部’的员工
select dept.name,emp.name from dept,emp
where emp.dept_id=dept.id and dept.name='培优部';

-- 扩展:可以为表名加别名,加了别名后,就需要使用别名替换表名
select d.name,e.name from dept d,emp e
where e.dept_id=d.id and d.name='培优部';

-- 49.(自查询)列出所有员工及其直接上级,显示员工姓名、上级编号,上级姓名

/* 将emp同时看做员工表和上级表
 * emp e1 看做员工表  emp e2  看做上级表
 * 查询的表: emp e1, emp e2
 * 查询的列: e1.name, e2.id, e2.name
 * 连接条件: 员工所属上级编号=上级的编号
 *					e1.topid=e2.id  */
 select e1.name, e2.id, e2.name
 from emp e1, emp e2
 where e1.topid=e2.id;

-- 50.列出最低薪资大于1500的各种职位,显示职位和该职位的最低薪资

-- 根据职位进行分组,再求出每个职位的最低薪资
select job, min(sal) from emp group by job;

-- 求出最低薪资大于1500的职位有哪些
select job, min(sal) from emp group by job 
having min(sal) > 1500;

补充内容:where和having子句的区别:

(1)相同点: where和having都可以对记录进行筛选过滤。
(2)区别:where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
(3)区别:having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。

-- 51.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量,平均薪资。

-- 根据部门分组,统计每个组(每个部门)的人数、平均薪资
select dept_id, count(*), avg(sal)
from emp
group by dept_id;

-- 52.查出至少有一个员工的部门,显示部门编号、部门名称、部门位置、部门人数。

-- 连接查询部门表和员工表
select d.id, d.name, d.loc
from dept d, emp e
where d.id=e.dept_id;
-- 根据部门进行分组
select d.id, d.name, d.loc, count(*)
from dept d, emp e
where d.id=e.dept_id
group by d.name
having count(*)>0;

-- 53.列出受雇日期早于直接上级的所有员工,显示员工编号、员工姓名、部门名称。

/* emp e1 员工表, emp e2 上级表
 查询的表:emp e1, emp e2, dept d
 查询的列:e1.id, e1.name, d.name
 连接条件:e1.topid=e2.id
 					e1.dept_id=d.id
 筛选条件:e1.hdate < e2.hdate
 */
select e1.id, e1.name, d.name
from emp e1, emp e2, dept d
where e1.topid=e2.id 
		and e1.dept_id=d.id
		and e1.hdate < e2.hdate;

-- 补充:查询员工表中薪资最高的员工信息

select name, max(sal) from emp; -- 这个查询结果是错误的!
-- 可以按照薪资降序排序,每页显示1条,查询第一页。
select * from emp order by sal desc limit 0,1;
-- 也可以使用子查询
select max(sal) from emp; -- 求emp表中的最高薪资
select * from emp where sal=( select max(sal) from emp );

数据库备份与恢复

备份数据库

在cmd窗口中(未登录的状态下),可以通过如下命令对指定的数据库进行备份:

mysqldump -u用户名 -p 数据库的名字 > 备份文件的位置

示例1: 对db40库中的数据(表,表记录)进行备份,备份到 d:/db40.sql文件中

mysqldump -uroot -p db40 > d:/db40.sql

键入密码,如果没有提示,即表示备份成功!

也可以一次性备份所有库,例如:

对mysql服务器中所有的数据库进行备份,备份到 d:/all.sql文件中

mysqldump -uroot -p --all-database > d:/all.sql

键入密码,如果没有提示错误(警告信息不是错误,可以忽略),即表示备份成功!

恢复数据库

1、恢复数据库方式一:

在cmd窗口中(未登录的状态下),可以通过如下命令对指定的数据库进行恢复:

mysql -u用户名 -p 数据库的名字 < 备份文件的位置

示例:将d:/db40.sql文件中的数据恢复到db60库中

-- 在cmd窗口中(已登录的状态下),先创建db60库:

create database db60 charset utf8;

-- 在cmd窗口中(未登录的状态下)

mysql -uroot -p db60 < d:/db40.sql

2、恢复数据库方式二:

在cmd窗口中(已登录的状态下),可以通过source执行指定位置的SQL文件:

source sql文件的位置

示例:将d:/db40.sql文件中的数据恢复到db80库中

-- 在cmd窗口中(已登录的状态下),先创建db80库,进入db80库:

create database db80 charset utf8;
use db80;

-- 再通过source执行指定位置下的sql文件:

source d:/db40.sql

Navicat Premium是一套带图形用户界面的数据库管理工具,让你从单一应用程序中同时连接MySQL、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL 和 SQLite数据库。使用Navicat可以快速、轻松地创建、管理和维护数据库。

1、使用navicat连接mysql服务器(使用cmd连接mysql服务器)
2、查看所有库、进入数据库、创建数据库、删除数据库、修改数据库
3、创建表、查看表、修改表、删除表
4、新增表记录、查询表记录、修改表记录、删除表记录
5、使用navicat书写SQL语句操作数据库、表和表记录
...

哔哩哔哩视频链接:https://www.bilibili.com/video/BV1yA41147Vi/

扩展内容

现创建学生表:

use test; -- 进入test库
drop table if exists stu; -- 删除学生表(如果存在)
create table stu( -- 创建学生表
    id int, -- 学生id
    name varchar(20), -- 学生姓名
    gender char(1), -- 学生性别
    birthday date -- 出生年月
);

修改表—新增列

语法:ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype...];

1、往stu表中添加score列,double类型

alter table stu add score double;

修改表—修改列

语法:ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]...);

1、修改id列,将id设置为主键

alter table stu modify id int primary key;

2、修改id列,将id主键设置为自动增长

alter table stu modify id int auto_increment;

修改表—删除列

语法:ALTER TABLE tabname DROP [COLUMN] col_name;

1、删除stu表中的score列

alter table stu drop score;

添加或删除主键及自增

思考:a) 在建表时,如何为id指定主键约束和自增?

b) 建好的表,如何通过修改添加主键约束和自增?

c) 如何删除表中的主键约束和自增?

1、创建stu学生表,不添加主键自增, 查看表结果

use mydb1; -- 切换到mydb1库
drop table if exists stu; -- 删除stu学生表(如果存在)
create table stu( -- 重建stu学生表,没有主键自增
    id int,
    name varchar(20),
    gender char(1),
    birthday date
);
desc stu; -- 查看表结构

表结构如下: 没有主键约束和自增。

2、如果表没有创建,或者要删除重建,在创建时可以指定主键或主键自增

drop table if exists stu; -- 删除stu表
create table stu( -- 重新创建stu表时,指定主键自增
    id int primary key auto_increment,
    name varchar(20),
    gender char(1),
    birthday date
);
desc stu; -- 查看表结构

表结构如下: 已经添加了主键约束和自增。

3、如果不想删除重建表,也可以通过修改表添加主键或主键自增

再次执行第1步,创建stu学生表,不添加主键自增,查看表结果

-- 例如: 将stu学生表中的id设置为主键和自动增长

alter table stu modify id int primary key auto_increment;
desc stu; -- 查看表结构

如果只添加主键约束,不设置自增

alter table stu modify id int **primary key**;

如果已经添加主键约束,仅仅设置自增,但需注意:

(1)如果没有设置主键,不可添加自增

(2)只有当主键是数值时,才可以添加自增

alter table stu modify id int **auto_increment**;

4、如果想删除主键自增

-- 删除主键自增时,要先删除自增

alter table stu modify id int;

-- 再删除主键约束

alter table stu drop primary key;
desc stu; -- 查看表结构

添加外键约束

1、添加外键方式一:建表时添加外键

现有部门表如下:

-- 创建部门表

create table dept(
	id int primary key auto_increment, -- 部门编号
	name varchar(20) -- 部门名称
);

要求创建员工表,并在员工表中添加外键关联部门主键

-- 创建员工表

create table emp(
    id int primary key auto_increment, -- 员工编号
    name varchar(20), -- 员工姓名
    dept_id int, -- 部门编号
    foreign key(dept_id) references dept(id) -- 指定dept_id为外键
);

2、添加外键方式二:建表后添加外键

现有部门表和员工表:

-- 创建部门表

create table dept(
    id int primary key auto_increment, -- 部门编号
    name varchar(20) -- 部门名称
);

-- 创建员工表

create table emp(
    id int primary key auto_increment, -- 员工编号
    name varchar(20), -- 员工姓名
    dept_id int -- 部门编号
);

-- 如果表已存在,可以使用下面这种方式:

alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 fk_dept_id (名字由自己定义),是指外键约束名称,也可以将【constraint fk_dept_id】省略,MySQL会自动分配一个外键名称,将来可以通过该名称删除外键。

foreign key(dept_id)中的dept_id为外键

删除外键约束

1、首先通过 “show create table 表名”语法,查询含有外键表的建表语句,例如:

show create table emp;

显示结果如下:

其中,emp_ibfk_1是在创建表时,数据库为外键约束指定的一个名字,删除这个名字即可删除外键关系,例如:

alter table emp drop foreign key emp_ibfk_1;

外键删除成功!

添加外键约束(多对多)

-- 现有学生(stu)表和教师(tea)表:

-- 创建学生表

create table stu(
    stu_id int primary key auto_increment, -- 学生编号
    name varchar(20) -- 学生姓名
);

-- 创建教师表

create table tea(
	tea_id int primary key auto_increment, -- 教师编号
	name varchar(20) -- 教师姓名
);

-- 添加第三方表(stu_tea)表示学生表和教师表关系

-- 创建学生和教师关系表

create table stu_tea(
    stu_id int, -- 学生编号
    tea_id int, -- 教师编号
    primary key(stu_id,tea_id), -- 设置联合主键
    foreign key(stu_id) references stu(stu_id), -- 添加外键
    foreign key(tea_id) references tea(tea_id) -- 添加外键
);

其中为了防止重复数据,将stu_id和tea_id设置为联合主键。

将stu_id设置为外键,参考stu表中的stu_id列

并将tea_id设置为外键,参考tea表中的tea_id列

级联更新、级联删除

-- 创建db20库、dept表、emp表并插入记录

-- 删除db20库(如果存在),并重新创建db20库

drop database if exists db20;
create database db20 charset utf8;
use db20;

-- 创建部门表, 要求id, name字段

create table dept(
	id int primary key auto_increment, -- 部门编号
	name varchar(20) -- 部门名称
);

-- 往部门表中插入记录

insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');

-- 创建员工表, 要求id, name, dept_id

create table emp(
    id int primary key auto_increment, -- 员工编号
    name varchar(20), -- 员工姓名
    dept_id int, -- 部门编号
    foreign key(dept_id) references dept(id) -- 指定外键
    on update cascade -- 级联更新
    on delete cascade -- 级联删除
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);

级联更新:主表(dept表)中的主键发生更新时(例如将销售部的id改为40),从表(emp表)中的记录的外键数据也会跟着该表(即赵六和刘能的部门编号也会更新为40)

级联删除:如果不添加级联删除,当删除部门表中的某一个部门时(例如删除4号部门),若该部门在员工表中有对应的员工(赵六和刘能),删除会失败!

若果添加了级联删除,当删除部门表中的某一个部门时,若该部门在员工表中有对应的员工,会在删除部门的同时,将员工表中对应的员工也删除!

where中不能使用列别名

SQL语句的书写顺序:

select * | 列名 -- 确定要查询的列有哪些
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤,剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:

from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤,剔除不符合条件的记录
select * | 列名 列别名 -- 确定要查询的列有哪些,
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount

**** 关于where中不能使用列别名但是可以使用表别名?**

是因为,表别名是声明在from中,from先于where执行,先声明再使用没有问题,但是列别名是声明在select中,where先于select执行,如果先使用列别名,再声明,这样执行会报错!!

MySQL作业和总结

06-28作业:

1、将课堂上讲过的 第 01~17 题再过一遍!

2、完成 “day01.02-mysql作业.txt” 文件中的 第01~12题!

06-29作业:

1、将课堂上讲过的 第 18~42题再过一遍!

2、完成“day01.02-mysql作业.txt” 文件中的 第13~26题!

3、自学navicat软件的使用!

06-30作业:

1、将课堂上讲过的 第 43~53题再过一遍!

2、完成“day01.02-mysql作业.txt” 文件中的 第27~34题!

3、练习mysql数据库的备份和恢复


06-28总结:

0、第二阶段的课程简介、学习方法建议、mysql环境检测及mysql安装

1、数据库及相关的概念(数据库,关系型数据库,数据库服务器、数据库、表、表记录)

2、如何连接mysql服务器:

mysql -uroot -p密码
mysql -uroot -p密码 -h主机名或ip地址 -P端口

3、SQL语言:用于操作关系型数据库的通用的语言

4、如何查询所有数据库、创建数据库、删除数据库、进入数据库

5、如何查询所有表、创建表、删除表、修改表(扩展内容中有)

6、新增表记录、修改表记录、删除表记录

7、查询表记录(基础查询、where子句查询)

8、mysql的数据类型(数值、字符串、日期)、mysql的字段约束(主键、唯一、非空约束)


06-29总结:

9、查询表记录(模糊查询、多行函数查询、分组查询、排序查询、分页查询)

10、外键约束(什么是外键:用于通知数据库两张表之间的数据存在对应关系的列)

11、表关系(一对多、多对一、一对一、多对多)

12、多表查询(两张表连接查询、左外连接查询、右外连接查询、使用union模拟全外连接查询)

04-30总结:

13、多表查询练习(子查询、多表查询、自查询)

14、navicat软件的使用、数据库的备份和恢复

15、什么是Jdbc? 为什么要学习Jdbc? Jdbc快速入门案例

unit07-JDBC

学习目标:

  • 了解什么是JDBC?为什么要学习这门技术?
  • 掌握通过JDBC连接并访问数据库
  • 掌握PreparedStatement传输器的使用(SQL注入攻击)
  • 掌握什么是连接池?为什么要使用连接池?
  • 掌握C3P0连接池的用法
  • 数据库事务(放在后面讲,框架之前)

JDBC概述

什么是JDBC?为什么要学习JDBC?

JDBC(Java DataBase Connectivity) Java数据库连接

其实就是利用Java语言/程序连接并访问数据库的一门技术

之前我们可以通过CMD或者navicat等工具连接数据库

但在企业开发中,更多的是通过程序(Java程序)连接并访问数据库,通过Java程序访问数据库,就需要用到JDBC这门技术。

如何通过JDBC程序访问数据库?

1、提出需求:

创建一个 jt_db 数据库,在库中创建一个account表,并插入三条记录,然后利用Java程序查询出account表中所有的记录,并将查询的结果打印在控制台上。

2、开发步骤:

(1)准备数据, 创建jt_db库, 创建account表

drop database if exists jt_db;
create database jt_db charset utf8;
use jt_db;
create table account(
    id int primary key auto_increment,
    name varchar(50),
    money double
);
insert into account values(null, 'tom', 1000);
insert into account values(null, 'andy', 1000);
insert into account values(null, 'tony', 1000);

如果已经执行过课前资料中的"SQL脚本文件",此步骤可以跳过。

(2)创建JAVA工程:

(3)导入jar包——mysql驱动包:

image-20200318153627820

(4)创建类并实现JDBC程序(六个步骤)

代码实现:

public static void main(String[] args) throws Exception {
    //1.注册数据库驱动
    Class.forName("com.mysql.jdbc.Driver");
    //2.获取数据库连接
    Connection conn = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
        "root", "root");
    //3.获取传输器
    Statement stat = conn.createStatement();
    //4.发送SQL到服务器执行并返回执行结果
    String sql = "select * from account";
    ResultSet rs = stat.executeQuery( sql );
    //5.处理结果
    while( rs.next() ) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double money = rs.getDouble("money");
        System.out.println(id+" : "+name+" : "+money);
    }
    //6.释放资源
    rs.close();
    stat.close();
    conn.close();
    System.out.println("TestJdbc.main()....");
}

3、执行结果:

JDBC API总结

1、注册数据库驱动

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

所谓的注册驱动,就是让JDBC程序加载mysql驱动程序,并管理驱动

驱动程序实现了JDBC API定义的接口以及和数据库服务器交互的功能,加载驱动是为了方便使用这些功能。

2、获取连接之数据库URL

Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
    "root", "root" );

DriverManager.getConnection() 用于获取数据连接,返回的Connection连接对象是JDBC程序连接数据库至关重要的一个对象。

参数2参数3分别是所连接数据库的用户名和密码。

参数1:"jdbc:mysql://localhost:3306/jt_db" 是连接数据库的URL,用于指定访问哪一个位置上的数据库服务器及服务器中的哪一个数据库,其写法为:

当连接本地数据库,并且端口为3306,可以简写为如下形式:

jdbc:mysql:///jt_db

3、Statement传输器对象

Statement stat = conn.createStatement();
该方法返回用于向数据库服务器发送sql语句的Statement传输器对象

该对象上提供了发送sql的方法:

executeQuery(String sql) --
用于向数据库发送查询类型的sql语句,返回一个ResultSet对象中
executeUpdate(String sql) --
用于向数据库发送更新(增加、删除、修改)类型的sql语句,返回一个int值,表示影响的记录行数

4、ResultSet结果集对象

ResultSet对象用于封装sql语句查询的结果,也是一个非常重要的对象。该对象上提供了遍历数据及获取数据的方法。

(1)遍历数据行的方法

next() – 使指向数据行的箭头向下移动一行,并返回一个布尔类型的结果,true表示箭头指向了一行数据,false表示箭头没有指向任何数据(后面也没有数据了)

(2)获取数据的方法

getInt(int columnIndex)
getInt(String columnLable)
getString(int columnIndex)
getString(String columnLable)
getDouble(int columnIndex)
getDouble(String columnLable)
getObject(int columnIndex)
getObject(String columnLable)

5、释放资源

rs.close();
stat.close();
conn.close();

此处释放资源必须按照一定的顺序释放,越晚获取的越先关闭。所以先关闭
rs对象,再关闭stat对象,最后关闭conn对象。

另,为了避免上面的程序抛出异常,释放资源的代码不会执行,应该把释放资源的代码放在finally块中.

try{
	...
}catch(Exception e){
	...
}finally{
    if (rs != null) {
        try {
        	rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            rs = null;
        }
    }
    if (stat != null) {
        try {
        	stat.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stat = null;
        }
    }
    if (conn != null) {
        try {
        	conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
        }
    }
}

增删改查

JDBC增删改查

1、新增:往account表中添加一个名称为john、money为3500的记录

/* 1、新增:往account表中添加一个名称为john、money为3500的记录 */
@Test
public void testInsert() {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		//注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//获取传输器
		stat = conn.createStatement();
		//发送sql语句到服务器执行,并返回执行结果
		String sql = "insert into account values(null, 'john', 3500)";
		int rows = stat.executeUpdate( sql );
		//处理结果
		System.out.println( "影响行数: "+rows );
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		//通过JdbcUtil工具类中的close方法释放资源
		JdbcUtil.close(conn, stat, rs);
	}
}

2、修改:将account表中名称为john的记录,money修改为1500

/* 2、修改:将account表中名称为john的记录,money修改为1500 */
@Test
public void testUpdate() {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		//注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//获取传输器
		stat = conn.createStatement();
		//发送sql语句到服务器执行,并返回执行结果
		String sql = "update account set money=1500 where name='john'";
		int rows = stat.executeUpdate( sql );
		//处理结果
		System.out.println( "影响行数: "+rows );
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		//通过JdbcUtil工具类中的close方法释放资源
		JdbcUtil.close(conn, stat, rs);
	}
}

3、查询:查询account表中名称为john的记录

/* 3、查询:查询account表中id为1的记录 */
@Test
public void testFindById() {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		//注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//获取传输器
		stat = conn.createStatement();
		//执行sql语句,返回执行结果
		String sql = "select * from account where id=1";
		rs = stat.executeQuery( sql );
		//处理结果
		if( rs.next() ) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			double money = rs.getDouble("money");
			System.out.println( id+" : "+name+" : "+money);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		JdbcUtil.close(conn, stat, rs);
	}
}

4、删除:删除account表中名称为john的记录

/* 4、删除:删除account表中名称为john的记录 */
@Test
public void testDelete() {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		//注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//获取传输器
		stat = conn.createStatement();
		//发送sql语句到服务器执行,并返回执行结果
		String sql = "delete from account where name='john'";
		int rows = stat.executeUpdate( sql );
		//处理结果
		System.out.println( "影响行数: "+rows );
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		//通过JdbcUtil工具类中的close方法释放资源
		JdbcUtil.close(conn, stat, rs);
	}
}

单元测试补充

单元测试:不用创建新的类,也不用提供main函数,也不用创建类的实例,就可以直接执行一个方法

加了@Test注解的方法,可以通过单元测试(junit)框架测试该方法。底层会创建该方法所在类的实例,通过实例调用该方法。

@Test
public void testInsert() {
	System.out.println("TestPreparedStatement.testInsert()");
}

能够使用@Test单元测试测试的方法必须满足如下几个条件:

(1)方法必须是公共的
(2)方法必须是非静态的
(3)方法必须是无返回值的
(4)方法必须是无参数的
(5)进行单元测试的方法或类,命名时不要命名为 Test/test

PreparedStatement

在上面的增删改查的操作中,使用的是Statement传输器对象,而在开发中我们用的更多的传输器对象是PreparedStatement对象,PreparedStatement是Statement的子接口,比Statement更加安全,并且能够提高程序执行的效率。

Statement 父对象

PreparedStatement 子对象

模拟用户登录案例

(1)准备数据

use jt_db;
create table user(
    id int primary key auto_increment,
    username varchar(50),
    password varchar(50)
);
insert into user values(null,'张三','123');
insert into user values(null,'李四','234');

(2)创建LoginUser 类,提供 main 方法 和 login 方法。

public static void main(String[] args) {
	/* 1、提示用户登录,提示用户输入用户名并接收用户名
	 *  2、提示用户输入密码并接收密码
	 *  3、根据用户名和密码查询用户信息
	 */
	// 1、提示用户登录,提示用户输入用户名并接收用户名
	Scanner sc = new Scanner(System.in);
	System.out.println( "请登录:" );
	System.out.println( "请输入用户名:" );
	String user = sc.nextLine();
	
	// 2、提示用户输入密码并接收密码
	System.out.println( "请输入密码:" );
	String pwd = sc.nextLine();
	
	// 3、根据用户名和密码查询用户信息
	login( user, pwd );
}
/**
 * 根据用户名和密码查询用户信息
 * @param user 用户名
 * @param pwd 密码
 */
private static void login(String user, String pwd) {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		//1.注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//2.获取传输器,执行sql并返回执行结果
		stat = conn.createStatement();
		String sql = "select * from user where username='"+user+"' and password='"+pwd+"'";
		rs = stat.executeQuery(sql);
		System.out.println( sql );
		//3.处理结果
		if( rs.next() ) { //有数据 -- 用户名密码都正确
			System.out.println("恭喜您登录成功!");
		}else { //没数据 -- 用户名或密码不正确
			System.out.println("登录失败, 用户名或密码不正确!");
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		//4.释放资源
		JdbcUtil.close(conn, stat, rs);
	}
}

执行时,输入:

请登录:
请输入用户名:
张飞'#'
请输入密码:

select * from user where username='张飞'#'' and password=''
恭喜您登录成功了!

或输入

请登录:
请输入用户名:
张飞' or '1=1
请输入密码:

select * from user where username='张飞' or '1=1' and password=''
恭喜您登录成功了!

或输入

请登录:
请输入用户名:

请输入密码:
' or '2=2
select * from user where username='' and password='' or '2=2'
恭喜您登录成功了!

SQL注入攻击

通过上面的案例,我们发现在执行时,不输入密码只输入用户名也可以登陆成功。这就是SQL注入攻击。

SQL注入攻击产生的原因: 由于后台执行的SQL语句是拼接而来的:

select * from user where username='"+user+"' and password='"+pwd+"'

其中的参数是用户提交过来的,如果用户在提交参数时,在参数中掺杂了一些SQL关键字(比如or)或者特殊符号(#、-- 、' 等),就可能会导致SQL语句语义的变化,从而执行一些意外的操作(用户名或密码不正确也能登录成功)!

防止SQL注入攻击

如何防止SQL注入攻击?

(1)使用正则表达式对用户提交的参数进行校验。如果参数中有(# -- ' or等)这些符号就直接结束程序,通知用户输入的参数不合法

(2)使用PreparedStatement对象来替代Statement对象。

下面通过第二种方式解决SQL注入攻击:添加loginByPreparedSatement方法,在方法中,使用PreparedStatement来代替Statement作为传输器对象使用,代码示例:

/**
 * 根据用户名和密码查询用户信息
 * @param user 用户名
 * @param pwd 密码
 */
private static void login(String user, String pwd) {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		//1.注册驱动并获取连接
		conn = JdbcUtil.getConn();
		//2.获取传输器,执行sql并返回执行结果
		String sql = "select * from user where username=? and password=?";
		ps = conn.prepareStatement( sql );
		//设置SQL语句中的参数
		ps.setString( 1 , user );
		ps.setString( 2 , pwd );
		//执行SQL语句
		rs = ps.executeQuery();//这里不要再传输SQL语句
		
		//3.处理结果
		if( rs.next() ) { //有数据 -- 用户名密码都正确
			System.out.println("恭喜您登录成功!");
		}else { //没数据 -- 用户名或密码不正确
			System.out.println("登录失败, 用户名或密码不正确!");
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		//4.释放资源
		JdbcUtil.close(conn, ps, rs);
	}
}

再次执行程序,按照上面的操作登录。此时,已经成功的防止了SQL注入攻击问题了。

PreparedStatement对象是如何防止SQL注入攻击的:

使用PreparedStatement对象是先将SQL语句的骨架发送给服务器编译并确定下来,编译之后,SQL语句的骨架和语义就不会再被改变了,再将SQL语句中的参数发送给服务器,即使参数中再包含SQL关键字或者特殊符号,也不会导致SQL语句的骨架或语义被改变,只会被当作普通的文本来处理!


使用PreparedStatement对象可以防止SQL注入攻击

而且通过方法设置参数更加的方便且不易出错!

还可以从某些方面提高程序执行的效率!

数据库连接池

什么是连接池

池:指内存中的一片空间(容器,比如数组、集合)

连接池:就是将连接存放在容器中,供整个程序共享,可以实现连接的复用,减少连接创建和关闭的次数,从而提高程序执行的效率!

为什么要使用连接池

1、传统方式操作数据库

Connection conn = DriverManager.getConnection( url, user, pwd ); 
//创建连接对象
conn.close(); //关闭连接, 销毁连接

在传统方式中,每次用户需要连接访问数据库时,都是创建一个连接对象,基于这个连接对象访问数据库,用完连接后,会将连接关闭(conn.close)。

由于每次创建连接和关闭连接非常的耗时间而且耗资源,因此会导致程序执行的效率低下。

2、使用连接池操作数据库

可以在程序一启动时,就创建一批连接放在一个连接池中(容器),当用户需要连接时,就从连接池中获取一个连接对象,用完连接后,不要关闭,而是将连接再还回连接池中,这样一来,用来用去都是池中的这一批连接,实现了连接的复用,减少了连接创建和关闭的次数,从而提高了程序执行的效率!

如何使用C3P0连接池

dbcp/c3p0/druid

所有的连接池都要实现一个接口——DataSource(数据源),因此连接池也被叫做数据源!

使用C3P0连接池开发步骤:

1、导入开发包

2、创建数据库连接池(对象)

ComboPooledDataSource cpds = new ComboPooledDataSource();

3、设置连接数据库的基本信息

(1)方式一:(不推荐) 直接将参数通过 pool.setXxx方法设置给c3p0程序

这种方式直接将参数写死在了程序中,后期一旦参数发生变化,就要修改程序,要重新编译项目、重新发布项目,非常麻烦。

//设置连接数据库的基本信息
pool.setDriverClass( "com.mysql.jdbc.Driver" );
pool.setJdbcUrl( "jdbc:mysql:///jt_db?characterEncoding=utf-8" );
pool.setUser( "root" );
pool.setPassword( "root" );

(2)方式二:将连接参数提取到properties文件中(推荐)

文件必须放在src(源码根目录)目录下 !

文件名必须叫做 c3p0.properties !

在类目录下(开发时可以放在src或者类似的源码目录下),添加一个c3p0.properties文件,配置内容如下:

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql:///jt_db?characterEncoding=utf-8
c3p0.user=root
c3p0.password=root

这种方式由于是c3p0到指定的位置下寻找指定名称的properties文件,所以文件的位置必须是放在src或其他源码根目录下,文件名必须是c3p0.properties。

(3)方式三:将连接参数提取到xml文件中(推荐)

文件必须放在src(源码根目录)目录下 !

文件名必须叫做 c3p0-config.xml

在类目录下(开发时可以放在src或者类似的源码目录下),添加一个c3p0-config.xml文件,配置内容如下:

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///jt_db?characterEncoding=utf-8</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </default-config>
</c3p0-config>

这种方式由于是c3p0到指定的位置下寻找指定名称的xml文件,所以文件的位置必须是放在src或其他源码根目录下,文件名必须是c3p0-config.xml。

4、从连接池中获取一个连接对象并进行使用

Connection conn = pool.getConnection();

5、用完连接后将连接还回连接池中

JdbcUtil.close(conn, ps, rs);
//conn.close()
/* 如果是自己创建的连接对象,这个连接对象没有经过任何的改动,调用
* conn.close方法,是将连接对象关闭
* 如果是从连接池中获取的连接对象,该连接对象在返回时就已经被连接池
* 改造了,将连接对象的close方法改为了还连接到连接池中
*/

扩展:切换工作空间,修改默认编码

切换新的工作空间,设置工作空间编码为utf-8

(1)切换到新的工作空间目的:若旧的工作空间内容过多,可能会导致eclipse不编译,甚至进入休眠状态。

(2)设置工作空间编码为utf-8,此后在当前工作空间下创建的项目编码也默认是utf-8。

设置JSP文件的编码为utf-8

扩展:JDBC实现学生信息管理系统

B站视频链接:https://www.bilibili.com/video/BV1ka4y1x7M6

准备数据

建库建表语句如下:

-- 1、创建数据库jt_db数据库(如果不存在才创建)
create database if not exists jt_db charset utf8;
use jt_db; -- 选择jt_db数据库
-- 2、在 jt_db 库中创建 stu 表(学生表)
drop table if exists stu;
create table stu(
    id int,
    name varchar(50),
    gender char(2),
    addr varchar(50),
    score double
);
-- 3、往 stu 表中, 插入记录
insert into stu values(1001,'张三','男', '北京', 86);

功能实现

运行程序控制台提示如下:

输入a:查询所有学生信息

输入b:添加学生信息

输入c:根据id修改学生信息

输入d:根据id删除学生信息

查询所有学生信息

在控制台中输入操作代码"a",效果如下:

添加学生信息

在控制台中输入操作代码"b",效果如下:

根据id修改学生信息

在控制台中输入操作代码"c",效果如下:

根据id删除学生信息

在控制台中输入操作代码"d",效果如下:

Jdbc作业

05-07作业:

1、为什么要学习或使用JDBC?

2、JDBC开发程序的步骤(六步)

3、使用Statement对象完成对Account表的增删改查操作!

(1)新增:往account表中添加一个名称为john123、money为5500的记录
(2)查询:查询account表中名称为john123的记录

4、使用PreparedStatement对象完成对user表的增删改查操作!

(1)新增:往user表中添加一个username为赵云、password为123123的记录
(2)修改:将user表中username为赵云的记录,password修改为111

5、在上面第4题中加入c3p0,使用c3p0获取连接,用完连接后再将连接还回连接池中。

05-07作业:

1、新增:(使用c3p0)往account表中添加一个名称为john、money为2500的记录

2、查询:(使用c3p0)查询account表中名称为john的记录

3、什么是连接池? 为什么要使用连接池?

4、c3p0连接池的开发步骤?

5、完成“day03.04-jdbc作业”文件中的"JDBC练习题"(单选题+多选题)

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

毕生所求无它,爱与自由而已