mysql笔记

MySQL笔记

1. 数据库

1. 概述

数据库database用来存储数据和管理数据的仓库

分类：关系型MySQL / 非关系型Redis

1. 使用

1. 安装服务器端口：存数据

设置端口号3306，设置密码root，设置字符集 / 编码表gbk / utf8

1. 安装客户端：链接 服务器，操作 服务器里的数据，CRUD

DOS窗口：小黑窗口

可视化工具：Sqlyog软件

1. 结构：数据库 -> 表-> 数据（字段/字段的值）

1. SQL语言

1. 概述

是结构化查询语言，专门用来操作数据库的语言，是一种标准化语言，可以操作各种数据库产品分类：

1. DML：数据库操纵语言，是指对数据进行CRUD
2. DDL：数据定义语言，是指创建的SQL语言
3. DCL：数据控制语言，是指权限的分配
4. DQL：数据查询语言，是指对数据的各种查询语言

常用操作：

对数据库的操作，对标的操作，对数据/记录的操作

1. 对数据库的操作

create database 库名 default character set utf8; 创建数据库 设置默认的字符集，防止中文乱码

Show databases; 查询所有库

Drop database 库名; 删除指定库

Use 库名; 进入指定库

Create table 表明(

Id int(3),

Name varchar(10),

...

); 创建表

Show tables; 查看所有表

Desc 表名; 查看表内容

Insert into 表名 values(100,’test’, ... ) 往表里添加数据

Set names gbk; 防治中文代码

Update 表名 set 字段名 = 新的值;  修改信息

delete from 表名; 删除数据

1. 总结

show databases; #查看所有库

create database 库名 default character set utf8 ; #创建库

drop database 库名 ; #删库

use 库名; #使用指定的数据库

create table 表名(字段名 字段类型(字段长度),字段名 字段类型(字段长度)); #新建表

show tables ; #查看表

drop table 表名; #删表

alter table 表名 add column 字段名 字段类型(字段长度) ; #修改表

desc 表名; #描述表的结构

select * from 表名; #查数据

insert into 表名 values(字段1的值,字段2的值,字段3的值,字段4的值) ;#添加数据

update 表名 set 字段名=新值 #改数据

delete from 表名 ; #删数据

1. 可视化工具

1）概述

用sqlyog,连接服务器,操作服务器里的数据.库, 表, 记录.

1. 新连接: 文件-新连接-输入连接名和密码-测试连接-连接-ok
2. 创建库: 右键-新建数据库-输入库名选好utf8-ok
3. 创建表: 右键-创建表-输入表名和选utf8-设计字段名字段类型和长 度-保存
4. 表里的记录: 右键-打开表-新增/删除/保存/刷新

1. 字段约束

1. 概述

当表里的字段值想要添加约束条件时,就可以使用各种字段约束
常见的有: 主键约束 非空约束 唯一约束
通常,在创建表时已经确定了,哪些字段要添加哪种约束

1. 测试

#字段约束

#1.主键约束:给字段添加主键约束时,字段的值必须唯一且非空

   #自增策略:AUTO_INCREMENT把主键的值交给数据库维护,值是自增

CREATE TABLE a(id INT)

CREATE TABLE b(id INT PRIMARY KEY)#主键(唯一+非空)

CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT)#主键且自增

#2.非空约束:not null,给字段加了非空约束,字段值不能为空

CREATE TABLE d(NAME VARCHAR(10))

CREATE TABLE e(NAME VARCHAR(10) NOT NULL)

INSERT INTO e VALUES()#报错,必须有值

INSERT INTO e VALUES(NULL)#报错,必须有值

#3.唯一约束:unique,给字段添加唯一约束,字段的值不能相同

CREATE TABLE f(tel VARCHAR(11))

CREATE TABLE g(tel VARCHAR(11) UNIQUE )

1. 函数

1. 概述

为了方便的操作数据库里的数据 , MySQL提供了很多函数.
常见的有: lower upper length substr concat replace 日期函数 小 数的函数

1. 测试

SELECT * FROM emp#查所有列,低效

SELECT ename FROM emp#查指定的列(把*换成字段名),高效

SELECT ename,empno FROM emp#查询结果和查询顺序一样的

#基础函数:

#lower:全转小写 upper:全转大写

SELECT ename,empno,LOWER(ename),UPPER(ename) FROM emp

#length:获取长度,一个字母或数字长度为1,一个汉字长度是3

SELECT * FROM dept

SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept#查部门名称

#concat:拼接字符串,第一个参数是字段名,后面的参数都是要拼接的数 据

SELECT dname,CONCAT(dname,'hello') FROM dept

SELECT dname,CONCAT(dname,'hello',123,789) FROM dept

#substr(1,2):截取字符串,1是字段名,2是从哪个字符开始

SELECT dname,SUBSTR(dname,3) FROM dept

#substr(1,2,3):1是字段名,2是从哪个字符开始,3是截取长度

SELECT dname,SUBSTR(dname,5,3) FROM dept

#replace(1,2,3):1是字段名,是要把2换成3

SELECT dname,REPLACE(dname,'a','666') FROM dept

#ifnull(1,2):1是字段名,判断1如果是null就换成2

SELECT comm,IFNULL(comm,200) FROM emp

SELECT * FROM emp

#计算月薪

SELECT sal,comm,sal+IFNULL(comm,0) FROM emp

SELECT sal,comm,sal+IFNULL(comm,0),sal*12+IFNULL(comm,0)*12 FROM emp

#round四舍五入, ceil向上取整 , floor向下取整

SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp

#日期函数 now当前时间

#year年 month月 day日 hour时 minute分 second秒

SELECT NOW()#2021-08-26 15:39:18

SELECT YEAR( NOW() )

SELECT YEAR('2020-08-26'),MONTH(NOW()),DAY(NOW())

SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())

#转义符号\

SELECT 'xi\'an'

SELECT "xi'an"

1. 条件查询

1. 测试

#条件查询

#1. distinct 去重

SELECT DISTINCT loc  FROM dept

SELECT * FROM dept

#2. where条件

#查询deptno=1的部门信息

SELECT * FROM dept WHERE deptno=1

#查询deptno=2的部门名称

SELECT dname FROM dept WHERE deptno=2

#查询地址在二区的部门名称

SELECT dname FROM dept WHERE loc='二区'

#多个条件可以用and 或者 or连接

#查询地址在二区，编号是2的部门信息

SELECT * FROM dept WHERE loc='二区' OR deptno=2

#查询编号是2的或者编号是3的 部门信息

SELECT * FROM dept WHERE deptno=2 OR deptno=3

SELECT * FROM dept WHERE deptno IN(2,3)#同上等效

#3. like模糊查询

#%通配符,通配了0~n个字符,_通配符,通配了1个字符

#查名称里包含o的部门信息

SELECT * FROM dept WHERE dname LIKE '%o%'#低效

SELECT * FROM dept WHERE dname LIKE 'o%'#以o开始,高效

SELECT * FROM dept WHERE dname LIKE '%o'#以o结束

SELECT * FROM emp WHERE ename LIKE 'l__'#了解

#4. null操作空数据

SELECT * FROM emp WHERE comm IS NULL#查是null的

SELECT * FROM emp WHERE comm IS NOT NULL#查不是null的

#5. between and 区间范围,是包含的关系

SELECT * FROM emp WHERE sal>3000 AND sal<10000

SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000

1. 条件查询

1. 概述

#limit:分页

SELECT * FROM emp LIMIT 2 #展示前两条数据

SELECT * FROM emp LIMIT 0,2 #展示前两条数据

SELECT * FROM emp LIMIT 1,4 #从第n+1行开始展示,要展示的行数

SELECT * FROM emp LIMIT 3,2

#order by: 排序,默认是升序ASC(可以省略)

SELECT * FROM emp ORDER BY sal ASC#按照empno排序

SELECT * FROM emp ORDER BY sal DESC#按照empno降序

SELECT * FROM emp ORDER BY ename#按照ename排序,字典顺序

SELECT * FROM emp ORDER BY hiredate#按照数值排序

SELECT * FROM emp ORDER BY job#按照汉字对应的数字排

#统计案例:

#查询2017年以前入职的员工信息

SELECT * FROM emp WHERE hiredate<'2017-1-1'

SELECT * FROM emp WHERE YEAR(hiredate) < 2017

#可以给列设置别名 ,使用as关键字(也可以省略)

#计算入职年份

SELECT *,YEAR(NOW())-YEAR(hiredate) AS 年份 FROM emp

#统计年薪

SELECT *,sal*13+IFNULL(comm,0)*13 年薪  FROM emp

1. 聚合函数

1. 概述

指把一列的值聚合在一起,在做分析
聚合函数: max() min() avg() sum() count()

1. 测试

#聚合函数: max min sum avg count

SELECT MAX(sal) FROM emp#查最高薪

SELECT MIN(sal) FROM emp #查最低薪

SELECT AVG(sal) FROM emp #查平均工资

SELECT SUM(sal) FROM emp #工资总和

SELECT COUNT(*) FROM emp #统计个数

SELECT COUNT(1) FROM emp #统计个数,高效

SELECT COUNT(empno) FROM emp #了解

SELECT COUNT(comm) FROM emp #了解,不统计null

#查询时,出现了混合(聚合列和非聚合列)列的现象

SELECT empno,SUM(sal) FROM emp #报错

SELECT SUM(sal) FROM emp

SELECT empno FROM emp

1. 分组

1. 概述

把查询结果进行分组 , 统计一组数据中的最大值,最小值…使用group by

1. 测试

#分组:GROUP BY

 #口诀:当查询时出现了聚合列和非聚合列时,必须按非聚合列分组

SELECT MAX(sal) FROM emp #查最高薪

#查每个部门的最高薪

SELECT MAX(sal),deptno FROM emp GROUP BY deptno

#查每个岗位的平均工资

SELECT job,AVG(sal) FROM emp GROUP BY job

#查每年入职的人数

SELECT YEAR(hiredate) YEAR,COUNT(1) FROM emp

GROUP BY YEAR(hiredate)

#查每个部门的人数

SELECT deptno,COUNT(1) FROM emp

        GROUP BY deptno

#having:完成分组后的过滤         

#查每个部门的人数,只要人数>1的部门

SELECT deptno,COUNT(1) FROM emp

GROUP BY deptno

HAVING COUNT(1) > 1

#查每个岗位的平均工资,只要>8000的

SELECT job,AVG(sal) FROM emp

GROUP BY job

HAVING AVG(sal)>8000#低效,分完组才过滤

SELECT job,AVG(sal) FROM emp

WHERE AVG(sal)>8000#比having高效,因为在分组前就过滤了

#where里不能出现聚合函数

GROUP BY job

#查每年入职的人数,只要人数>1的

SELECT COUNT(1),YEAR(hiredate) FROM emp

GROUP BY YEAR(hiredate) #按照非聚合列分组

HAVING COUNT(1) > 1

1. 事务

1. 概述

用来 保证多个操作 要么全成功,要么全失败.
四个特性:ACID

1. 原子性: 多个操作, 是绑定到一起的,要么全成功,要么全失败
2. 一致性: 在多个系统中,保证数据是一致的
3. 隔离性: 在保证了性能,的同时隔离用户的操作
4. 持久性: 对数据的操作都是有持久影响的

隔离级别:

1. 读未提交: 效率高,安全性差
2. 读已提交: 牺牲了效率,提高了安全性–Oracle数据的默认隔离 级别
3. 可重复读: 牺牲了效率,提高了安全性–Mysql数据的默认隔离级 别
4. 串行化: 安全性最高,但是效率太低

1. 操作

1. 开启事务: start transaction;
2. 结束事务: commit 提交事务(最终持久性影响数据库)
   rollback 回滚事务(回滚到事务操作前)
3. MySQL数据库默认就已经管理了事务 , 会为每条SQL提供事务

1. 测试

START TRANSACTION ; #开启事务

INSERT INTO dept VALUES(NULL,'test','大钟寺');

INSERT INTO dept VALUES(NULL,'test2','大钟寺2');

COMMIT;#关闭事务

1. 字段约束

1. 概述

给字段添加约束, 字段的值都有了要求
包括: 唯一 非空 主键 外键 默认 检查

1. 测试

#默认约束default: 给字段设置默认值

CREATE TABLE h(

 id INT PRIMARY KEY AUTO_INCREMENT,#主键自增

 sex VARCHAR(10) DEFAULT '男' #默认约束

)

#检查约束CHECK:检查字段的值是否合法

CREATE TABLE i(

 id INT PRIMARY KEY AUTO_INCREMENT,

 age INT,

 CHECK(age>0 AND age<18)#检查约束,不合法时会报错

)

1. 外键约束

1. 测试

CREATE TABLE tb_user(

 id INT PRIMARY KEY AUTO_INCREMENT,

 NAME VARCHAR(20)

)

#外键约束的效果：

#1,子表的id必须取自主表的id

#2,想删除主表的数据必须先删掉子表相关的

CREATE TABLE tb_user_addr(

 user_id INT PRIMARY KEY,

 addr VARCHAR(20),

 #外键：通过特殊字段(外键),描述了两张表间的关系

 #foreign key(当前表的主键) REFERENCES 对方表(对方表的主键)

 FOREIGN KEY(user_id) REFERENCES tb_user(id)

)

1. 索引

1. 概述

好处是最大的作用就是提高查询效率，坏处是索引本身也是一张表不适 合大量的添加
实现过程：1，设置索引 2，使用索引
分类：单值索引 ， 唯一索引 ， 复合索引

1. 测试

#0.查看索引

SHOW INDEX FROM dept

#1.创建单值索引(给常用来作为查询条件的字段加)

  #语法：CREATE INDEX 索引名  on 表名（字段名）

CREATE INDEX dname_index ON dept(dname)

#2.使用索引(背后的手段)

EXPLAIN#观察sql的性能/执行计划(找possible_keys的值)

SELECT * FROM dept WHERE dname='java'

#3.创建唯一索引(索引列的值不能重复)

CREATE UNIQUE INDEX uni_index ON dept(dname)

#4. 创建复合索引 并 使用复合索引

  # CREATE INDEX 索引名 ON 表名 (字段1,字段2)

CREATE INDEX fuhe ON emp(ename,job)

SHOW INDEX FROM emp

#5. 使用复合索引(最左特性,否则复合索引失效)

EXPLAIN SELECT * FROM emp WHERE ename='jack' #按ename查，生效

#按ename和job查，生效

EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='副总'

#按job和ename查，生效

EXPLAIN SELECT * FROM emp WHERE job='副总' AND ename='jack'

#按job查，失效

EXPLAIN SELECT * FROM emp WHERE job='副总'

SHOW INDEX FROM emp

#删除索引

#   修改表  表名  删除索引  索引名

ALTER TABLE emp DROP INDEX fuhe

1. 多联查询

1. 概述

用来完成联合多张表的查询（3张以下）

1. 测试

#多表联查：产生了大量的冗余数据

#1.笛卡尔积：把多张表用逗号隔开

SELECT * FROM dept,emp

#描述两张表的关系：表名.字段名

WHERE dept.deptno=emp.deptno

#2.连接查询,join..on

SELECT * FROM dept JOIN emp

#描述两张表的关系：表名.字段名

ON dept.deptno=emp.deptno

#3.子查询：把上次的查询结果，用来作为下次查询的条件

#练习语法：teachers / courses

SELECT * FROM teachers , courses WHERE teachers.tno=courses.tno

SELECT * FROM teachers JOIN courses ON teachers.tno=courses.tno

#练习1：查询部门名称叫accounting的员工姓名

#子查询：

#1，查部门表，根据部门名称accounting查deptno

SELECT deptno FROM dept WHERE dname='accounting'

#2，查询员工表，根据deptno查姓名

SELECT ename FROM emp WHERE deptno=1

SELECT ename FROM emp WHERE deptno=(

SELECT deptno FROM dept WHERE dname='accounting')

#笛卡尔积：逗号隔开表名，where里写查询条件，可以多个

SELECT emp.ename FROM dept,emp

WHERE dept.deptno=emp.deptno #表关系

AND dept.dname='accounting' #业务条件

#连接查询：JOIN连接表名，ON写表关系，where里写查询条件，可以多个

SELECT emp.ename FROM dept JOIN emp

ON dept.deptno=emp.deptno #表关系

WHERE dept.dname='accounting' #业务条件

#练习2：列出research部门下的所有员工的信息

#子查询：

#根据部门名称查部门编号

SELECT deptno FROM dept WHERE dname='research'

#根据部门编号查员工信息

SELECT * FROM emp WHERE deptno=2

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='research')

#笛卡尔积：

SELECT emp.* FROM dept,emp

WHERE dept.deptno=emp.deptno #表关系

AND dept.dname='research' #业务条件

#三种连接查询：

#内连接inner join:取两个表的交集

#左外连接left join:左表的所有和右表满足条件的，不满足是null

#右外连接right join:右表的所有和左表满足条件的，不满足是null

SELECT * FROM dept LEFT JOIN emp

ON dept.deptno=emp.deptno #表关系

WHERE dept.dname='research' #业务条件

#练习3：查询工作地址在二区的所有员工信息

#子查询:可以用in或者=连接子查询

#根据部门地址查部门编号

SELECT deptno FROM dept WHERE loc="二区" #会查到多个值

#根据编号查员工信息

SELECT * FROM emp WHERE deptno=2 OR deptno=3

SELECT * FROM emp WHERE deptno IN(2,3) #in子查询

SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc="二区")

#笛卡尔积

SELECT emp.* FROM dept,emp

WHERE dept.deptno=emp.deptno #表示了两张表的关系

AND dept.loc="二区" #业务条件

#连接查询--相对高效，小表驱动大表（左表写一个小表）

SELECT emp.* FROM dept INNER JOIN emp #内连接取交集

ON dept.deptno=emp.deptno #表示了两张表的关系

WHERE dept.loc="二区" #业务条件

1. 扩展视图

1. 概述

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直 接缓存到了视图中。
下次还要发起相同的sql，直接查视图。
使用： 1，创建视图 2，使用视图

1. 测试

create view 视图名 as  SQL语句;

select * from 视图名;

#视图：就是一个特殊的表，缓存上次的查询结果

#好处是提高了SQL的复用率，坏处是占内存无法被优化

#1.创建视图

CREATE VIEW emp_view AS

SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含 a的

#2.使用视图

SELECT * FROM emp_view

1. SQL练习

1. 测试

参考帖子里的内容：

数据库从入门到精通03_cgblpx的博客-CSDN博客

#统计每个岗位的老师有几个人

SELECT COUNT(1),prof FROM teachers

GROUP BY prof #按照非聚合列分组

HAVING prof='副教授' #having分组后再过滤

#统计每个系最年长的老师

SELECT depart,MIN(tbirthday) FROM teachers

GROUP BY depart

#order by tbirthday #按照数字升序

#查询大于平均年龄的老师

SELECT AVG(tbirthday) FROM teachers

#'19690708250000.0000000000'

SELECT * FROM teachers WHERE tbirthday<(

SELECT AVG(tbirthday) FROM teachers )

#查询男老师能上的课程

#子查询:in子查询，因为第一次查到了多个结果

SELECT cname FROM courses WHERE tno IN(

SELECT tno FROM teachers WHERE tsex='男')

#连接查询：小表驱动大表，高效

SELECT courses.cname FROM courses INNER JOIN teachers

ON teachers.tno=courses.tno #表关系

WHERE teachers.tsex='男' #业务条件

#笛卡尔积

SELECT courses.cname FROM teachers,courses

WHERE teachers.tno=courses.tno #表关系

AND teachers.tsex='男' #业务条件

#了解insert into

#给指定列插入指定值，多个列名用逗号隔开，多个值也是逗号隔开

INSERT INTO dept(dname) VALUES('java开发部')

1. SQL优化

1. SQL的执行顺序

1. EROM [left_table}] 选择表
2. ON <join_condition> 连接条件
3. <join_type> JOIN <right_table> 链接
4. WHERE <where_condition> 条件过滤
5. GROUP BY <group_by_list> 分组
6. AGG_FUNC(column or expression),... 聚合
7. HAVING <having_condition> 分组过滤
8. SELECT (9) DISTINCT column,... 选择字段、去重
9. ORDER BY <order_by_list> 排序

1. 总结

参考帖子里的内容：

数据库从入门到精通03_cgblpx的博客-CSDN博客

1. 用字段名代替*
2. where里能用and就不用or
3. 设置字段类型时，能用varchar就不用char
4. 字段值尽量用数字代替字符串
5. 给常用来作为查询条件的字段，设计索引，但是，单表的索引控制 再5个以内
6. 观察SQL的性能，使用explain关键字，本质上就是看有没有用到 索引
7. 模糊查询，where name like ‘张%’ ，是高效的写法，甚至可以 让索引生效
8. where name=123，会导致索引失效。
9. where name=‘123’，会让name字段的索引生效

1. JDBC

1. 概述

全称是java database connectivity,专门用来通过Java程序 操作 数 据库。
JDBC还是一套标准，支持对各种数据库产品的操作。
MySQL数据库使用JDBC：提供了一个jar包(很多的工具类)。

1. 使用步骤

1. 创建工程，导入MySQL提供的jar包并编译：把jar包复制到工程 里，编译jar包（选中jar包右键add as library…）
2. 通过Java连接数据库，需要指定一些连接数据库的参数：用户名， 密码，端口号，ip地址,指定库名。
3. 发起SQL语句并执行
4. 处理结果集

1. 入门案例

package cn.tedu.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

//测试 jdbc

//1,导入jar包  2,连接数据库

public class Test1 {

    public static void main(String[] args) throws Exception {

        //1,注册驱动

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

        //2,获取数据库的连接

                    //数据传输协议   数据库的ip 端口号 数据库 名

        String url = "jdbc:mysql://localhost:3306/cgb2107";

        Connection c =  DriverManager.getConnection(url,"root","root");

        //3,获取传输器

        Statement s = c.createStatement();

        //4,利用传输器执行SQL,返回结果

        ResultSet r = s.executeQuery("select * from dept");

//执行查询语句

        //5,处理结果

        while( r.next() ){//next()判断有数据吗,有就返回true

            //解析数据,用getXxx()获取每个字段的值--参数可以是

字段名或者字段索引

            int a = r.getInt(1);//获取第1列的值

            System.out.println(a);

            String b = r.getString("dname");

//获取dname字段值

            System.out.println(b);

            String loc = r.getString(3);//获取第3列的值

            System.out.println(loc);

        }

        //6,释放资源

        r.close();//结果集

        s.close();//传输器

        c.close();//连接

    }

}

1. 练习

package cn.tedu.test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

//练习jdbc

public class Test2 {

    public static void main(String[] args) throws Exception {

//        method();

        method2();

    }

    //利用jdbc,完成新增的功能

    private static void method2() throws Exception{

        //1,注册驱动

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

        //2,获取数据库的连接

        //数据传输协议   数据库的ip 端口号 数据库名

        String url = "jdbc:mysql://localhost:3306/cgb2107";

        Connection c =

DriverManager.getConnection(url,"root","root");

        //3,获取传输器

        Statement s = c.createStatement();

        //4,利用传输器执行  增删改的SQL

        //executeUpdate()用来执行增删改的SQL,只返回影响行数

        int rows = s.executeUpdate(

                "INSERT INTO emp(ename,job) VALUES('rose','副总

')");

        //5,释放资源

        //r.close();//结果集

        s.close();//传输器

        c.close();//连接

    }

    //利用jdbc,查询emp表的所有数据

    private static void method() throws Exception {

        //1,注册驱动

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

        //2,获取数据库的连接

        //数据传输协议   数据库的ip 端口号 数据库名

        String url = "jdbc:mysql://localhost:3306/cgb2107";

        Connection c =

DriverManager.getConnection(url,"root","root");

        //3,获取传输器

        Statement s = c.createStatement();

        //4,利用传输器执行SQL,返回结果

        ResultSet r = s.executeQuery("select * from emp");

        //5,处理结果

        while(r.next()){//如果有数据,next()返回true

            for(int i=1;i<9;i++){//自己数表里一共有几列,就循环

几次

                Object a = r.getObject(i);//参数是字段的索引1 2

3...

                System.out.println(a);//打印每列得到的值

            }

        }

        //6,释放资源

        r.close();//结果集

        s.close();//传输器

        c.close();//连接

    }

}

1. 模拟用户登录现象（作业）

1. 需求：模拟用户登录现象

1. 需要你自己创建user表,并提供一些字段(id name password)
2. 向表中添加记录,如1 jack 123456
3. 编写JDBC程序,完成登录
4. 本质上就是向发起了select语句:
   select * from user where name=‘jack’ and password=‘123456’
5. 当用户输入正确的用户名和密码时,提示登录成功,否则提示登录 失败

1. 测试

package cn.tedu.test2;

import java.sql.*;

import java.util.Scanner;

//测试 作业

/*

总结:

1, SQL攻击:出现了特殊的符号#,改变了SQL语义,本质上是因为用

Statement

2, 解决方案:利用全新的传输器PreparedStatement,

    本质上 把SQL骨架和SQL的参数分开执行的,遇到了#只是当做一个

普通的文本而不是注释符号了

3, 优点: 省去了拼接SQL语句的麻烦, 防止了SQL攻击, 高效

 */

public class Test1 {

    public static void main(String[] args) {

// method();//用普通的传输器

        method2();//用安全的传输器

    }

    //用安全的传输器,解决SQL攻击

    private static void method2() {

        try {

            //调用封装的方法,获取数据库的连接

            Connection c = getConnection();

            //3,准备SQL

            String a = new Scanner(System.in).nextLine() ;

            String b = new Scanner(System.in).nextLine() ;

            //SQL骨架,?叫占位符

            String sql = "select * from user where name=? and

password=?";

            //4,获取 新的传输器

            PreparedStatement s = c.prepareStatement(sql);

            //给SQL设置参数

            s.setString(1,a);//给第1个?的位置,设置a的值

            s.setString(2,b);//给第2个?的位置,设置b的值

            //执行拼接好的 SQL

            ResultSet r = s.executeQuery();

            //5,判断是否查到了数据,就直接登录,否则登录失败

            if(r.next()){

                System.out.println("恭喜您,登录成功~");

            }else{

                System.out.println("登录失败~");

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            //TODO 6,释放资源

            r.close();

            s.close();

            c.close();

        }

    }

    /**

     * 封装了方法,用来 获取数据库的连接

     */

    public static Connection getConnection() throws Exception{

        //1,注册驱动

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

        //2,获取数据库的连接

        String url = "jdbc:mysql://localhost:3306/cgb2107";

        Connection c =

 DriverManager.getConnection(url,"root","root");

        return c ;//返回给调用者

    }

    //模拟登录

 //SQL攻击/注入:当SQL语句中出现了#时,会把后面的SQL语句注释,

改变了SQL的语义

    private static void method() {

        try {

            //调用封装的方法,获取数据库的连接

            Connection c = getConnection();

            //3,获取传输器

            Statement s = c.createStatement();

            //4,利用传输器执行SQL,返回结果

// String sql = "select * from user where name='jack' and

password='123'";参数写死了

            //动态接受用户输入的用户名和密码,并动态拼接到SQL

语句里

            String a = new Scanner(System.in).nextLine() ;

            String b = new Scanner(System.in).nextLine() ;

            //当用户输入了特殊的名字时jack'#,发生了SQL攻击现

象

       String sql = "select * from user where name='"+a+"' and

password='"+b+"'";

            ResultSet r = s.executeQuery(sql);

            //5,判断是否查到了数据,就直接登录,否则登录失败

            if(r.next()){

                System.out.println("恭喜您,登录成功~");

            }else{

                System.out.println("登录失败~");

            }

            //6,释放资源

            r.close();

            s.close();

            c.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}

1. 标准的释放资源

 //用安全的传输器,解决SQL攻击

    private static void method2() {

        //为了能让finally使用变量

        Connection c = null;

        PreparedStatement s =null;

        ResultSet r =null;

        try {

            //调用封装的方法,获取数据库的连接

            c = getConnection();

            //3,准备SQL

            String a = new Scanner(System.in).nextLine() ;

            String b = new Scanner(System.in).nextLine() ;

            //SQL骨架,?叫占位符

            String sql = "select * from user where name=? and

password=?";

            //4,获取 新的传输器

            s = c.prepareStatement(sql);

            //给SQL设置参数

            s.setString(1,a);//给第1个?的位置,设置a的值

            s.setString(2,b);//给第2个?的位置,设置b的值

            //执行拼接好的 SQL

            r = s.executeQuery();

            //5,判断是否查到了数据,就直接登录,否则登录失败

            if(r.next()){

                System.out.println("恭喜您,登录成功~");

            }else{

                System.out.println("登录失败~");

            }

        }catch (Exception e) {

            e.printStackTrace();

        }finally{

            //TODO 6,释放资源

            if(c != null){//为了防止空指针异常

                try {

                    c.close();

                } catch (SQLException throwables) {

                    throwables.printStackTrace();

                }

            }

            if(r != null) {//为了防止空指针异常

                try {

                    r.close();

                } catch (SQLException throwables) {

                    throwables.printStackTrace();

                }

            }

            if(s != null) {//为了防止空指针异常

                try {

                    s.close();

                } catch (SQLException throwables) {

                    throwables.printStackTrace();

                }

            }

        }

    }

1. 改造释放资源

/**

* 封装了方法,用来 关闭资源

*/

public static void close(Connection c,ResultSet r,PreparedStatement s){

   if(r != null) {//为了防止空指针异常

       try {

           r.close();

       } catch (SQLException throwables) {

           throwables.printStackTrace();

       }

   }

   if(s != null) {//为了防止空指针异常

       try {

           s.close();

       } catch (SQLException throwables) {

           throwables.printStackTrace();

       }

   }

   if(c != null){//为了防止空指针异常

       try {

           c.close();

       } catch (SQLException throwables) {

           throwables.printStackTrace();

       }

   }

}

 //用安全的传输器,解决SQL攻击

    private static void method2() {

        //为了能让finally使用变量

        Connection c = null;

        PreparedStatement s =null;

        ResultSet r =null;

        try {

            //调用封装的方法,获取数据库的连接

            c = getConnection();

            //3,准备SQL

            String a = new Scanner(System.in).nextLine() ;

            String b = new Scanner(System.in).nextLine() ;

            //SQL骨架,?叫占位符

            String sql = "select * from user where name=? and

password=?";

            //4,获取 新的传输器

            s = c.prepareStatement(sql);

            //给SQL设置参数

            s.setString(1,a);//给第1个?的位置,设置a的值

            s.setString(2,b);//给第2个?的位置,设置b的值

            //执行拼接好的 SQL

            r = s.executeQuery();

            //5,判断是否查到了数据,就直接登录,否则登录失败

            if(r.next()){

                System.out.println("恭喜您,登录成功~");

            }else{

                System.out.println("登录失败~");

            }

        }catch (Exception e) {

            e.printStackTrace();

        }finally{

            //TODO 6,释放资源

            close(c,r,s);  //调用封装的方法,释放资源

        }

    }