目录

一、数据库应用

1、概念

2、Mysql数据库

2.1、存放位置

2.2、服务端:存数据

2.3、客户端：操作数据

3、数据库的结构

4.SQL语句

4.1、定义

4.2、分类

5、数据库常用操作

6、表的常用操作

7、表记录的常用操作

8、数据类型

9、准备数据

10、字段约束

11、基础函数

12、转义字符

二、SQL进阶二

1、条件查询

1.1、distinct：去除重复记录

1.2、where:精确查询

1.3、like：模糊查询

1.4、null：判空

1.5、between and 取区间范围

1.6、limit

1.7、order by:排序

2、统计案列

2.1、入职统计

2.2、年薪统计

3、聚合 aggregation

3.1、count：求总数

3.3、max / min

3.4、sum / avg

4、group：分组 

4.1、group by

4.2、having

三、SQL进阶三

1、事务 transaction

1.1、事务4个特性ACID（面试重点）

1.2、隔离级别

1.3、查询mysql的隔离级别

1.4、事物处理（保障数据安全）

2、表强化：6大约束 constraints

3、索引

3.1、分类

3.2、查看索引：

3.3、创建索引

3.4、使用索引

3.5、删除索引

4、表关联 association

4.1、多表联查

5、视图

6、SQL优化

四、JDBC

1、概念

2、使用步骤

3、入门案列

4、SQL注入

5、SQL注入的解决方案（sql攻击）

一、数据库应用

1、概念

关系型数据库： Oracle、MySQL、SQLServer、Access（关系紧密）

非关系型数据库： MongoDB、Redis、Solr、ElasticSearch、Hive、Hbase（数据无关系）

2、Mysql数据库

服务端：它来处理具体数据维护，保存磁盘

客户端：C新增，U修改，D删除，R查询(可视化工具)

2.1、存放位置

        MySQL的配置文件my.ini中会进行默认配置

2.2、服务端:存数据

        mysql-5.5.27-winx64.msi

2.3、客户端：操作数据

        DOS窗口

        打开数据库：mysql -u用户名 -p密码

        mysql -uroot -proot

        语法：mysql.exe执行文件

        可视化工具：Sqlyog

3、数据库的结构

数据库（一个）-->表（多）-->数据（多字段/字段值）

4.SQL语句

4.1、定义

是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，专门对数据库进行增删改查的语言；同时也是数据库脚本文件的扩展名。

4.2、分类

DML（Data Manipulation Language）数据操作语言
如：insert，delete，update，select（插入、删除、修改、检索）

DDL（Data Definition Language）数据库定义语言
如：create table之类，创建库，表等等

DCL（Data Control Language）数据库控制语言
如：grant、deny、revoke等，权限管理

DQL（Data Query Language）数据查询语言

        数据库的各种查询方式
常用操作：数据库/表/数据（字段）：增删改查
        注意：SQL不区分大小写

5、数据库常用操作

打开数据库：mysql   -u用户名  -p密码
mysql  -uroot  -proot

增加：create database 库名 default character set utf8;
删除：drop database 库名;
查找：show databases;

6、表的常用操作

使用数据库：use 表名;

表设计：

创建表：

create table 表名(字段一，字段二);
字段：字段名 字段类型(字段最大长度),
create table 表名(
字段名 varchar(100),
字段名int(10)
);  

主键：Primary key   自动递增：auto_increment
外键：foreign key(当前表的主键)  references 对方表（对方表的主键）

修改表：添加列：alter table 表名 add column 字段名 numeric(字段长度)

        numeric(7,2)：字段类型（长度为7,保留2位小数）。

删除表：drop table 表名;

查看所有表：show tables;

查看表内部结构：desc 表名;

7、表记录的常用操作

增加记录：insert  into 表名 values(null,'永和大王1店',666);

查询记录：select* from 表名;

修改记录：update 表名  set  字段名=字段值  where  id=1;

                     修改id为1的记录

删除记录：Delete  from 表名 where id=2;

                     删除id为2的数据

排序:Select * from 表名 order by tel desc;

                     将表记录按照tel排序

记录总数:Select count(*) from 表名;

                     查询表中的总记录数

                防止中文乱码： set  names  gbk;

8、数据类型

命名规则：字母开头，不能超过30个字符，多个单词用下划线隔开，只能使用如 下字符az、AZ、0~9、$ 等

字符：

char长度固定，不足使用空格填充，最多容纳2000个字符
varchar变长字符串，最多容纳4000个字符  

数字：

•     tinyint,int整数类型
•     float,double小数类型
•     numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
•     decimal和numeric表示精确的整数数字

日期：

• date 包含年月日
• time时分秒
• datetime包含年月日和时分秒
• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数
• 图片：blob 二进制数据，可以存放图片、声音，容量4g，目前主流都是存储其访问路径，文件存放在磁盘上。

9、准备数据

10、字段约束

约束字段的值

主键约束：主键的特点是唯一（不能为空）且不能为空。

        主键：Primary key   自动递增：auto_increment

非空约束：这个列的值就不能为空，但可以重复。

Not null

唯一约束：这个列的值就必须是唯一的（即不能重复），但可以为空。

unique

11、基础函数

Lower：SELECt  字段,LOWER(字段)  from  表名; --数据转小写
Upper：SELECt  字段,upper(字段)   from 表名--数据转大写

Length：select  length(字段)  from  表名; --数据的长度

一个汉字3字节，一个字母一个数字都是1字节

Substr：select  字段,substr(字段,1,3) from 表名; --截取字段，1号位置开始，长度为3

Concat：select  字段,concat(字段,'123')  from 表名;--拼接数据，在字段的数据后面链接

Replace：select 字段,replace(字段,'a','666')  from 表名; --把数据a替换成666

Ifnull：select  ifnull(字段,10)字段  from 表名;#判断,如果comm是null,用10替换

Now：获取时间

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

获取单个时间：（比如只获取：年）

        –hour()时 minute()分 second()秒

                select now(),hour(now()),minute(now()),second(now()) from emp ;

        –year()年 month()月 day()日

                select now(),year(now()),month(now()),day(now()) from emp ;

round & ceil & floor

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

        –直接四舍五入取整

                select 字段名,round(字段名) from 表名;

        –四舍五入并保留一位小数

                select 字段名,round(字段名,1) from 表名;

        –ceil向上取整，--floor向下取整

                select 字段名,ceil(字段名)from 表名

uuid

        SELECt UUID()

                返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

12、转义字符

                select 'ab'cd' --数据中有单引号时,用一个转义变成普通字符

二、SQL进阶二

1、条件查询

1.1、distinct：去除重复记录

        select  distint 字段 from表名;

1.2、where:精确查询

        #查询deptno=1的部门信息

        Select  *  from  表名  where  deptno=1

        #查询deptno=2的部门名称

        Select  部门  FROM  表名  where  deptno=2

        #查询loc在二区 并且 deptno=3的所有信息

        select * FROM  表名  where  loc='二区'  AND  deptno=3

        #查询deptno=2 或者 deptno=3的所有信息

        SELECt * FROM  dept  where  deptno=2  OR  deptno=3

1.3、like：模糊查询

        'a%' 以a开头的，'%a' 以a结束的，'%a%' 中间包含a的

        select * from 表名 where 字段名 like 'a%' 

        select * from 表名 where 字段名 like a__' --a后面有两个字符的 _代表一个字符位置

1.4、null：判空

        #查寻字段是mull的员工信息

        SELECt * FROM 表名 WHERe 字段  IS  NULL

        IS:是

        IS  NOT：不是

1.5、between and 取区间范围

        SELECt * FROM 表名 WHERe 字段>3000  and 字段<10000

        SELECt * FROM 表名 WHERe 字段 between 3000  and  10000    两边能取到

1.6、limit

分数最高的记录：按分数排序后，limit n，返回前n条。

select * from  表名  limit  n --列出前n条

select * from  表名  limit  n,m --从第n+1条开始,展示m条记录

1.7、order by:排序

SELECt * FROM  表名  order by  字段; ------升序（这是默认的:asc）

SELECt * FROM  表名  order by  字段  desc;-----降序

2、统计案列

2.1、入职统计

#查询2017年以前入职的员工信息？
SELECt * FROM emp WHERe hiredate<'2017-1-1'
SELECt * FROM emp WHERe YEAR(hiredate)<2017

#2015年到2019年入职的员工
SELECt * FROM emp WHERe YEAR(hiredate)>=2015 AND YEAR(hiredate)<=2019

#统计员工入职年份多长（当前时间-入职时间=入职年份）
SELECt *,YEAR(NOW())-YEAR(hiredate) FROM emp
SELECt *,YEAR(NOW())-YEAR(hiredate)  '入职年份' FROM emp

2.2、年薪统计

#统计13薪
SELECt  *,月收入*12+年终奖  AS  '13薪'  FROM  表名

月收入*12+年终奖：计算值
新增一列，名字叫：13薪

3、聚合 aggregation

        概念：拿到一列的数据计算结果

3.1、count：求总数

Select  count(*)  from  表名 --统计个数
Select  count(*) from  表名 --总记录数
select  count(1)  from  表名 --统计个数（高效）
select  count(字段名)  from  表名 --慢,统计非NULL的

3.3、max / min

select  max(字段) 自定义名字  from  表名 --求字段的最大值

select  min(字段) 自定义名字  from  表名 --求字段的最小值

3.4、sum / avg

select  sum(字段) from  表名 --求和

Select  avg(字段) from  表名 --平均数

4、group：分组 

• 解决聚合列与非聚合列混合的现象
• 查询时出现了聚合列和非聚合列时，必须按照非聚合列分组
• 把查询结果进行分组，统计一组数据中最大值，最小值.......使用group by

4.1、group by

#查询每个部门最高薪
SELECt MAX(工资),部门 FROM  表名  GROUP BY  部门

#（查询每个岗位的平均工资）
SELECt  AVG(工资),部门  FROM  表名  GROUP BY  部门

#查每年入职人数（按group by 后面的数据分组过后在统计总数）
SELECt  YEAR(字段),COUNT(1)  FROM  表名  GROUP BY  YEAR(字段)
Year(字段)：获取字段的年份

4.2、having

后面接判断条件
#查每个部门的人数,但是人数>1的
SELECt  deptno,COUNT(*) FROM emp GROUP BY deptno HAVINg COUNT(*)>1

三、SQL进阶三

1、事务 transaction

列子：（转账）转账成功,收帐一定要成功，要么都失败

事务：就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败

1.1、事务4个特性ACID（面试重点）

• 原子性：（不可分割性）是一个整体，要么执行成功，要么失败。
• 一致性：保证在多个项目中，数据是一致的
• 隔离性：多人操作数据都是独立的，他们互不干扰，（高并发）相当于同步锁机制。
• 持久性：对数据的修改就是永久的，即便系统故障也不会丢失。

1.2、隔离级别

• 读未提交：安全性最差,性能最好
• 读已提交：安全性一般,性能较好，Oracle默认的隔离级别。
• 可重复读：安全性较好,性能一般，MySQL默认的隔离级别。
• 串 行 化：安全性最好,性能最差。读写都加锁,不能并发

1.3、查询mysql的隔离级别

1.4、事物处理（保障数据安全）

MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start  transaction；

结束事务：commit（提交事务）：最终持久性影响数据库

                  rollback（回滚事务）：回滚到事物操作以前

提交 commit

回滚 rollback

2、表强化：6大约束 constraints

给字段添加约束，初始化时使用

唯一约束：  name  varchar(50)  unique；（name的值必须唯一，不能重复）

非空约束：  name  VARCHAr(30)  UNIQUE  NOT  NULL,（name不能为空）

主键约束：  id INT PRIMARY KEY AUTO_INCREMENT,（主键，自动递增）

外键约束：

两张表之间的关系（为了防止数据重复）
CREATE  TABLE  tb_user(
Id  INT  PRIMARY KEY AUTO_INCREMENT,
NAME  VARCHAr(20)
)

#外键约束：

• 子表的id必须取自主表的id
• 想删除主表的数据，先删除子表的数据
• 子表没有了自增

        CREATE  TABLE  tb_user_addr(
        user_id  INT  PRIMARY KEY,
        addr  VARCHAr(20),
        #通过特殊字段描述了两张表间的关系
        #foreign key(当前表的主键)  references 对方表（对方表的主键）
        FOREIGN  KEY(user_id)  REFERENCES  tb_user(id)
        )

默认约束(default)：默认值

        Sex  char(2)  defaulev '男'   sex默认的值是男

检查约束(check)：检查字段的值是否合法

        age int(20),check (age>0 AND age<=200)

3、索引

        索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。

        好处：提高查询效率，坏处：它本身也是一张表，不适合大量添加。

3.1、分类

•         单值索引：一个索引只包括一个列，一个表可以有多个列
•         唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
•         复合索引：一个索引同时包括多列

3.2、查看索引：

                Show  index  from 表名;

3.3、创建索引

• 普通创建：create  index 索引名 on 表名(字段名);
• 唯一索引：create  unique  index 索引名 on 表名(字段名)
• 复合索引：create  index  索引名  on 表名(字段1, 字段2)

                最左特性:查询必须从最左边开始，直接查字段2无效

3.4、使用索引

排序：tree结构，类似二分查找，索引表小

#explain:观察sql的性能/执行计划（找possible_keys的值）

        explain  sekect*from  表名  where  dname='java'

3.5、删除索引

        alter table 表名 drop index 索引名

4、表关联 association

                分类：   

• 一对一： QQ和QQ邮箱，员工和员工编号
• 一对多： 最常见，部门和员工，用户和订单
• 多对一： 一对多反过来，员工和部门，订单和用户
• 多对多： 老师和学生，老师和课、

        两种查询

4.1、多表联查

        产生了大量的冗余数据(只能3张一下)

笛卡尔积(效率很低)

        select * from  表1,表2  where 表1.相同字段名=表2.相同字段名;

三种连接查询（高效）

select * from  表1  join 表2  where 表1.相同字段名=表2.相同字段名;

子查询（嵌套查询）

        把上一次的结果当作下一次查询的条件

单行子查询

        select deptno from emp where ename='tony';

        select * from emp where deptno = (select deptno from emp where ename='tony');

多行子查询（in）

        select * from emp where job in ('经理','员工');

        select * from emp where job in (select distinct job from emp);

5、视图

        可视化的表，试图当作是一个特殊的表，指把sql执行的结果直接缓冲到视图中，下次还要发起相同的sql直接查视图

优点：提高了sql的复用率，缺点：占内存，无法被优化

#创建视图

        Create  view 视图名 as  sql语句;

#查询视图

        select*from  视图名;

6、SQL优化

1. 查询SQL尽量不要使用select *，而是具体字段
2. where尽量使用and ，少使用or来连接条件
3. 使用varchar代替char（长度不可变）
4. 查询尽量避免一页返回大量数据，通常采用分页，一页习惯10/20/50/100条。
5. 给常用来作为查询条件的地段设计索引，单表索引控制在个以内
6. 观察sql的性能，使用explain关键字，就是看看有没有索引
7. 模糊查询：where name like ‘张%’，高效写法
8. 尽量使用数值替代字符串类型
9. Where name=123,会导致索引失效，正确写法where name=’123’

四、JDBC

1、概念

通过java程序操作数据库

Jdbc是sun公司设定的一套链接数据库的标准，但它只是规范，不做具体实现。

MySQL数据库使用JDBC:提供了jar包

2、使用步骤

//1,注册驱动

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

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

//数据传输协议   数据库的ip 端口号  数据库名
String url="jdbc:mysql://localhost:3306/cgb2107?";
Connection c = DriverManager.getConnection(url, "root", "970824");

//3,获取传输器,

//4.利用传输器执行SQL

ResultSet r = s.executeQuery("select * from teachers");//执行查询语句

//5,解析结果集

while(r.next()){//next()判断有结果吗？有结果就执行
    for (int i = 1; i < 7; i++) {
         System.out.print("t"+r.getObject(i));
    }
}

 //6,释放资源(非常重要)

r.close();//关闭结果集
s.close();//关闭传输器
c.close();//关闭连接

3、入门案列

4、SQL注入

5、SQL注入的解决方案（sql攻击）

String sql = "select * from teachers where  t=?"; #参数使用问号
PreparedStatement p = cn.prepareStatement(sql); #对象换掉
p.setString(1, 值1);   #第一个？的值，值1
ResultSet rs =p.executeQuery(); #去掉sql参数