MySQL知识总结

白木松

已于 2022-02-24 10:27:49 修改

阅读量704

点赞数

文章标签： java

于 2022-01-06 19:30:47 首次发布

本文链接：https://blog.csdn.net/qq_43641887/article/details/122351133

版权

数据库

概述

专门用来存储数据和管理数据库的一个仓库.英文叫database
分类: 关系型和非关系型
1, 关系型数据库: 存的数据都有关系,代表是:Mysql / Oracle…
2, 非关系型数据库: 存的数据之间有着松散的关系, 代表是: Redis

简而言之，就是存储数据，管理数据的仓库。

常见的数据库分为：

关系型数据库， Oracle、MySQL、SQLServer、Access
非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

MySQL客户端1：DOS窗口

mysql -uroot -proot

查看端口 MySQL命令

show global variables like 'port'

status

指定端口登录 mysql -uroot -P3306 -proot

语法：mysql.exe执行文件

代表参数

-u 用户名，紧接着写的

-p 密码，紧接着写的

MySQL客户端2：可视化工具

在这里插入图片描述

SQL语言

结构化查询语言(Structured Query Language)简称SQL

1,概述
全称是结构化查询语言,专门用来操作数据库的语言.
而且是一个标准语言,可以操作任何数据库.

分类
DML（Data Manipulation Language）数据操纵语言
如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

DDL（Data Definition Language）数据库定义语言
如：create table之类

DCL（Data Control Language）数据库控制语言
如：grant、deny、revoke等，只有管理员才有相应的权限

DQL（Data Query Language）数据库查询语言
如: select 语法
注意：SQL不区分大小写

SQL的分类
DML:是指数据库的操纵语言,就是对数据的操作(新增,修改,查询,删除CRUD)
DDL:是指数据库的定义语言,比如说创建表…
DCL:是指数据库的控制语言,比如说权限管理
DQL:是指数据库的查询语言,比如进行复杂的查询语句

数据库的常见操作

在这里插入图片描述

create database cgb2022 DEFAULT CHARACTER SET utf8;

删库

在这里插入图片描述

表的常见操作

在这里插入图片描述

记录的常见操作

在这里插入图片描述

插入
语法: insert into 表名 values(1,2,3)
注意:
1, values后面括号里,要写几个值? 参考表里字段的个数,要和字段的个数匹配
2, 值的意义是什么呢? 要和字段的顺序位置匹配.每个值交给每个对应字段来保存
3, 值的写法有一些细节??? 如果字段是varchar类型,值必须有字符串的标识(单引号 / 双引号)
在这里插入图片描述

在这里插入图片描述

命名规则

字段名必须以字母开头，尽量不要使用拼音
长度不能超过30个字符（不同数据库，不同版本会有不同）
不能使用SQL的保留字，如where,order,group
只能使用如下字符az、AZ、0~9、$ 等
Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
多个单词用下划线隔开，而非java语言的驼峰规则

字符

char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
大文本: 大量文字（不推荐使用，尽量使用varchar替代）
以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

面试题：char和varchar的区别

1. char类型的长度是固定的，varchar的长度是可变的。

这就表示，存储字符串'abc'，使用char(10)，表示存储的字符将占10个字节（包括7个空字符）

　　　　　　　　　　　　　　使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。

2.char类型的效率比varchar的效率稍高

数据类型

1,数字:
整型 tinyint / int 小数float / double 精确的小数 numeric(5,2) / decimal(5,2)–5是指有5位数字,2是指包含着的小数位数
2,日期:
年月日 date 时分秒 time 年月日时分秒 datetime 毫秒数 timestamp时间戳
3,字符串:
char和varchar的区别?
前者,长度固定,浪费空间,但查询高效.
后者,长度可变,节省空间,但查询相对低效.

数字

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

日期

date 包含年月日
time时分秒
datetime包含年月日和时分秒
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

字段约束

非空约束 not null

唯一约束 unique

主键约束 primary key

外键约束 foreign key

foreign key(当前表的主键) references 主表(主键)


DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
 
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);

默认约束 default

哪个字段添加了默认约束,哪个字段的值就有了默认值,使用default来实现.

#1.默认约束:给字段添加默认值--用的少!
CREATE TABLE test01(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex CHAR(3) DEFAULT '男'#默认约束
)
#虽然sex设置了默认值,但是只是手动录入时有效,发起insert语句时还是要写具体值的
INSERT INTO test01 VALUES(NULL,'男')

检查约束 check

#2.检查约束:检查字段的值的合理性
CREATE TABLE test02(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0) #检查约束,了解即可!
)
INSERT INTO test02 VALUES(NULL,10) #ok的
INSERT INTO test02 VALUES(NULL,-10) #会报错,没有通过检查约束

基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

upper
select upper(dname) from dept --数据转大写

select upper(dname) from dept --数据转大写

length

select length(dname) from dept --数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,'123') X from dept --拼接数据

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

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

–直接四舍五入取整

select comm,round(comm) from emp

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

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

uuid
SELECT UUID()

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

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

select curdate() --年与日

select curtime() --时分秒

year & month & day
–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 ;

转义字符
'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

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

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

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

条件查询

distinct

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

where

注意:where中不能使用列别名!!

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&amp;关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

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);

like

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

between and

SELECT * FROM emp

select * from emp where sal<3000 and sal>10000

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

重点：limt（(页数-1)*每页显示条数，6）

order by

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

#练习:条件查询CRUD
#练习1:修改1号部门的名称和地址
UPDATE dept SET dname='java开发部',loc='北京'
WHERE deptno=1
#练习2:删除3号部门的数据
DELETE FROM dept WHERE deptno=3
#1.order by排序,字典顺序,默认就是升序asc,降序desc
#练习3:把员工按照名字排序
SELECT * FROM emp ORDER BY ename #a~z
#练习4:把员工按照工资排序
SELECT * FROM emp ORDER BY sal DESC #降序
#练习5:把员工按照入职时间排序
SELECT * FROM emp ORDER BY hiredate #升序排

聚合 aggregation

count

select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp --慢,只统计非NULL的

max / min

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

分组 group

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

group by

#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

having

#平均工资小于8000的部门

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的

扩展

char和varchar有什么区别？
char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

datetime和timestamp有什么区别？

数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是年与日

time是时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数

中文乱码

1.如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names gbk;

2.那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

注释

/* 很多注释内容 */

#行注释内容

– 行注释内容，这个使用较多

主键、外键、唯一索引的区别？
Primary Key 主键约束，自动创建唯一索引
Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
Unique Index 唯一索引，唯一值但不是主键
对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop、delete和truncate之间的区别？
drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

事务 transaction

什么是事务
数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

概述
1,英文叫transaction,主要作用是用来保证多条SQL,要么全成功要么全失败.
2,四大特征:ACID
原子性: 同一个事务里的多条SQL语句,是一个原子密不可分,要不全成功,要不全失败
一致性: 保证多台服务器里的数据是一致的(分布式系统)
隔离性: 数据库为了提高操作的效率允许高并发的访问,并采用了隔离性保证了数据的安全性(采用锁机制)
持久性: 是指,我们对数据库的操作(增删改)是持久生效的
3,隔离级别:
read uncommitted: 读未提交,安全性最差,但是效率高.
read committed: 读已提交,安全性有所提升,但是效率降低一些.也是Oracle数据库的默认隔离级别
repeatable read : 可重复读,安全性有所提升,但是效率又会低一些.也是MySQL数据库的默认隔离级别
Serializable: 串行化,安全性最高,但是性能最低

事物执行过程

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

开启事务：start transaction；
结束事务：commit（提交事务）或rollback（回滚事务）。
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

mysql> start transaction;  #开启事务
mysql> insert into dept values(10,'php','bj');  #执行SQL
mysql> commit;  #提交事务

事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

BEGIN; #关闭事务的自动提交,相当于start transaction
 
INSERT INTO user (id) VALUES(25);#成功
 
INSERT INTO user (id) VALUES(5);#已经存在5了，会失败
 
COMMIT; #手动提交事务

回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;
 
INSERT INTO user (id) VALUES(15);
 
INSERT INTO user (id) VALUES(35);#存在了
 
ROLLBACK;#事务回滚，就不会再提交了

表关联 association

概念
表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

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

多表联查 join

多表联查 join
1.笛卡尔积 Cartesian product
多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

#把两个表的数据都拼接起来

笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

这就是阿里规范中禁止3张表以上的联查的原因：

三种连接 join

内连接 inner join
左（外）连接 left join
右（外）连接 right join

inner join、left join、right join的区别？

INNER JOIN两边都对应有记录的才展示，其他去掉
LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

#多表联查:
#1.笛卡尔积
#练习1:查询部门表和员工表里的所有数据
SELECT * FROM dept,emp
#问题:产生了庞大的结果集,出现了重复的数据
#练习2:查询部门表和员工表里的所有数据,添加过滤条件
SELECT * FROM dept,emp
WHERE dept.deptno=emp.deptno; #描述了两个表的关系
#练习3:计算计算机导论课程所得的总分
      #聚合函数(表名.字段名)    表1,表2
SELECT SUM(scores.degree) FROM courses,scores
#描述了两个表的关系(表名.字段名)
WHERE courses.cno=scores.cno
AND courses.cname='计算机导论'#真正的业务条件
#练习4:查询学员陆君的总得分 sno
SELECT SUM(scores.degree) FROM scores,students
WHERE scores.sno=students.sno#描述了两个表的关系(表名.字段名)
AND students.sname='陆君'

2.子查询 subquery

概念

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

又叫嵌套查询,是指,把第一次的查询结果,作为第二次的查询条件,继续发起查询语句.

单行子查询 =

返回结果为一个


--列出tony所在部门的所有人员
 
select deptno from emp where ename='tony';
 
select * from emp where deptno = (select deptno from emp where ename='tony');

多行子查询 in

in子查询


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

SQL面试题

查询所有记录
select * from emp
 
只查询指定列
SELECT id,ename,sal from emp
 
查询id为100的记录
select * from emp where id=100
 
模糊查询记录
select * from emp where ename like 'j%' #以j开头的记录
 
select * from emp where ename like '%k' #以k结束的记录
 
select * from emp where ename like '%a%' #包含a的记录
 
select * from emp where ename not like 'j%' #不 以j开头的记录
 
查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]
 
select * from emp where sal>8000 and sal<20000 #(8000,20000)
 
查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系
 
SELECT * from user where age in (19,20)
 
SELECT * from user where age=20 and name='xiongda' #并且关系
 
查询用户住址
SELECT distinct addr from user
 
查询19岁人的名字
SELECT distinct name from user where age=19
 
按age升序查询记录
SELECT * from user order by age asc #升序,默认
 
SELECT * from user order by age desc #降序
 
以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序
 
查询总人数
SELECT count(*) from user
 
SELECT count(1) from user
 
SELECT count(id) from user
 
查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组
 
查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr
 
SELECT addr,count(name) X from user GROUP BY addr having X>2 #条件过滤
 
查询记录中最年长和最年轻
select max(age),min(age) from user
 
查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)
 
查询年龄最大的用户信息
select * from user where age = (select max(age) from user)
 
查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno
 
查询各科的平均工资
select avg(comm) from emp
 
select ROUND(avg(comm),1) from emp #保留一位小数
 
SELECT * from emp where comm > (select avg(comm) from emp)
 
查询id是100或200的记录
select * from emp where id=100
 
select * from emp where id=200
 
select * from emp where id=100 or id=200
 
select * from emp where id in(100,200)
 
select * from emp where id=200
 
#UNION #合并重复内容
 
union all #不合并重复内容
 
select * from emp where id=200
 
查询存在部门的员工信息
select * from emp where deptno in (select id from dept)
 
查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)
 
查询同名的员工记录
select * from emp WHERE ename in (
 
select ename from emp GROUP BY ename HAVING count(ename)>1
 
)
 
全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了
 
每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的
 
查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students
 
查询男教师及其所上的课程
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno AND a.tsex='男'
 
SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex='男'
 
查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
查询女老师的信息
SELECT *
 
FROM teachers t
 
LEFT JOIN courses c ON t.tno = c.tno
 
where t.tsex='女'
第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。
 
查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条
 
select * from scores order by degree desc limit 1,3
 
#从1位置(第二条)开始,总共取3条
 
查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname='计算机导论')
 
order by degree desc limit 3
 
课程号“3-105”的倒数最后3名学员排行
select * from scores where cno='3-105' order by degree limit 3
————————————————
版权声明：本文为CSDN博主「cgblpx」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u012932876/article/details/117359992

SQL的执行顺序

(1) FROM [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> 排序
 
(10) LIMIT count OFFSET count; 分页
————————————————

索引 index

定义
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

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

show index from dept;

#创建普通索引

#create index 索引名字 on 表名(字段名); #创建索引

create index loc_index on dept(loc); #创建索引

# 创建唯一索引

#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)

CREATE UNIQUE INDEX bindex ON dept(loc)

# 创建复合索引

#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX 索引名 ON 表名 (字段1, 字段2)

CREATE INDEX PIndex ON Persons (LastName, FirstName)

# 删除索引

alter table dept drop index fuhe_index

索引扫描类型
type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果

最左特性

explain
 
select * from dept where loc='二区' #使用了loc索引
 
explain
 
select * from dept where dname='研发部'#使用了dname索引
 
explain
 
select * from dept where dname='研发部' and loc='二区' #使用了dname索引

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)、(k1,k3)和(k1,k2,k3)索引，这就是最左匹配原则，也称为最左特性。

为何索引快？
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

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

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率
缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

#索引:提高查询效率,建议给经常用来查询的字段加索引
#1.查看索引(主键自带索引)
SHOW INDEX FROM students
#2.创建单值索引
#create index 索引名 on 表名(字段名)
CREATE INDEX index1 ON students(sname)
#3.使用索引(按照索引列去查)
SELECT * FROM students WHERE sname='陆君'
#4.查看SQL的执行性能/计划(只想关注你的SQL是否用到了索引)
EXPLAIN #看执行结果里的key
SELECT * FROM students WHERE sname='陆君'

#5.创建唯一索引:找到合适的列,值要唯一
#语法:create unique index 索引名 on 表名(字段名)
#create unique index index2 on students(ssex)#不让加,ssex值大量重复
CREATE UNIQUE INDEX index2 ON students(sno)
SHOW INDEX FROM students#查看索引
EXPLAIN SELECT * FROM students WHERE sno=101 #使用索引,索引失效!!
#!!6.创建复合索引:一个索引包含着多个列
CREATE INDEX index3 ON emp(ename,job,deptno)
SHOW INDEX FROM emp#查看索引
#使用索引,最左特性(查询条件里必须包含最左元素)
EXPLAIN SELECT * FROM emp WHERE ename='jack'#复合索引生效
EXPLAIN SELECT * FROM emp WHERE job='总监'#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE deptno=2#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='总监'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' AND ename='jack'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' OR ename='jack'#失效!
#删除索引
ALTER TABLE emp DROP INDEX index3

总结

1,优点:
大大提高了查询效率
本质上数据库会为索引列的数据进行排序,快速查询
2,缺点:
本身索引是一个单独的表,也需要占空间的
索引适合查询的业务,但是,也需要同步更新修改一些新的数据,需要一定的时间
3,原则:
什么时候加索引? 频繁的按照一个规则去查询的数据,就应该考虑添加索引
给谁加索引? 给那些经常作为查询条件的字段添加索引
加啥索引? 索引是有分类的, 单值索引 / 唯一索引 / 复合索引,看情况选择不同的索引类型
如何查看SQL的执行计划/性能? explain

视图View

概念

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

视图:就是一张特殊的表,用来缓存查询的结果


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

#好处是: #提高SQL的复用性 . #屏蔽了真实的业务表的复杂性 #被所有人共享

#坏处是: #视图一旦创建成功,就无法被优化 #视图中存了和业务中大量重复的数据

,"浪费空间" #视图通常用来查询,不便于更新

SQL优化

1, 查询语句中的*尽量换成字段名称
2, 查询条件where中,尽量用and不用or. 尽量把查询条件的范围控制到最小
3, 查询条件中,尽量用= 别用!= <> , 尽量别用in
4, 表设计时,字段类型尽量用varchar代替char, 尽量用数字代替字符串
5, 索引: 单表中的索引最好控制在5个以内,
模糊查询时,只有一种可以让索引生效: 确定开始元素 ‘abc%’
写SQL时注意字符串的标准写法,
where name=123,name的索引会失效
where name=‘123’,name的索引会生效
注意复合索引的最左特性,如果不遵循会导致复合索引失效
6, 多表联查: 可以使用三种方式笛卡尔积/连接查询/子查询
尽量不要有太多表联查,阿里的开发规范规定了最多三张表
连接查询相对高效,最高效的是内连接,因为内连接只是查交集
但是,在工作中,使用左连接是最多的,也会使用子查询

查询SQL尽量不要使用select *，而是具体字段
反例：SELECT * FROM student
 
正例：SELECT id,NAME FROM student
 
理由：
 
字段多时，大表能达到100多个字段甚至达200多个字段
只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能不会用到索引，就会造成全表扫描
避免在where子句中使用or来连接条件
 
反例：SELECT * FROM student WHERE id=1 OR salary=30000
 
正例：
 
# 分开两条sql写
 
SELECT * FROM student WHERE id=1
 
SELECT * FROM student WHERE salary=30000
 
理由：
 
使用or可能会使索引失效，从而全表扫描
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的
使用varchar代替char
反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
 
正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
 
理由：
 
varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高
尽量使用数值替代字符串类型
主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除
查询尽量避免返回大量数据
如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

使用explain分析你SQL执行计划
SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN
 
SELECT * FROM student WHERE id=1
是否使用了索引及其扫描类型
type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
key：

真正使用的索引方式
创建name字段的索引
ALTER TABLE student ADD INDEX index_name (NAME)
优化like语句
模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
 
EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'
正例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '1%'
字符串怪现象
反例：

#未使用索引
 
EXPLAIN
 
SELECT * FROM student WHERE NAME=123
正例：

#使用索引
 
EXPLAIN
 
SELECT * FROM student WHERE NAME='123'
理由：

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
索引不宜太多，一般5个以内
索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要
索引不适合建在有大量重复数据的字段上
如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

where限定查询的数据
数据中假定就一个男的记录

反例：

SELECT id,NAME FROM student WHERE sex='男'
正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'
理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销
避免在where中对字段进行表达式操作
反例：

EXPLAIN
 
SELECT * FROM student WHERE id+1-1=+1
正例：

EXPLAIN
 
SELECT * FROM student WHERE id=+1-1+1
 
EXPLAIN
 
SELECT * FROM student WHERE id=1
理由：

SQL解析时，如果字段相关的是表达式就进行全表扫描
避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
 
SELECT * FROM student WHERE salary!=3000
 
EXPLAIN
 
SELECT * FROM student WHERE salary<>3000
理由：

使用!=和<>很可能会让索引失效
去重distinct过滤字段要少
#索引失效

EXPLAIN
 
SELECT DISTINCT * FROM student
#索引生效

EXPLAIN
 
SELECT DISTINCT id,NAME FROM student
 
EXPLAIN
 
SELECT DISTINCT NAME FROM student
理由：

带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
where中使用默认值代替null
#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
批量插入性能提升
大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷');
 
INSERT INTO student (id,NAME) VALUES(5,'刘昱江');
批量提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');
理由：

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
数据量小体现不出来
批量删除优化
避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？

delete from student where id <100000;
#采用单一循环操作，效率低，时间漫长

for（User user:list）{
 
delete from student;
 
}
正例：

//分批进行删除，如每次500

for(){
 
delete student where id<500;
 
}
 
delete student where id>=500 and id<1000;
理由：

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
伪删除设计
商品状态（state）：1-上架、2-下架、3-删除

理由：

这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下
提高group by语句的效率
可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';
正例：先过滤，后分组

select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;
复合索引最左特性
创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='陈子枢'
没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
 
SELECT * FROM student WHERE salary=3000
复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
 
SELECT * FROM student WHERE salary=3000 AND NAME='陈子枢'
理由：

复合索引也称为联合索引
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的
排序字段创建索引
什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用 *，包含了未索引的字段，导致索引失效

EXPLAIN
 
SELECT * FROM student ORDER BY NAME;
#name字段有索引

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY NAME
#排序字段未创建索引，性能就慢

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY sex
删除冗余和重复的索引
SHOW INDEX FROM student
#创建索引index_name

ALTER TABLE student ADD INDEX index_name (NAME)
#删除student表的index_name索引

DROP INDEX index_name ON student ;
#修改表结果，删除student表的index_name索引

ALTER TABLE student DROP INDEX index_name ;
#主键会自动创建索引，删除主键索引

ALTER TABLE student DROP PRIMARY KEY ;
不要有超过5个以上的表连接
关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下
inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集
left join会返回左表所有的行，即使在右表中没有匹配的记录
right join会返回右表所有的行，即使在左表中没有匹配的记录
理由：

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
in子查询的优化
日常开发实现业务需求可以有两种方式实现：

一种使用数据库SQL脚本实现
一种使用程序实现
如需求：查询所有部门的所有员工：

#in子查询

SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：

#先查询部门表

SELECT id FROM tb_dept
#再由部门dept_id，查询tb_user的员工

SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;
 
for(int i=0;i<B.length;i++) {
 
for(int j=0;j<A.length;j++) {
 
if(A[i].id==B[j].id) {
 
resultSet.add(A[i]);
 
break;
 
}
 
}
 
}

数据库设计的三范式

概述
简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库设计的一些规则.
关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余.

对于数据库的设计人员,必须要遵循的规范,就叫做范式.
常见的范式有三范式: 1NF 2NF 3NF
好处是: 可以让数据减少冗余

1NF的定义为：符合1NF的关系中的每个属性都不可再分

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖

第一范式: 1NF, 是指表里的字段值不能再被分割了
第二范式: 2NF, 是指遵循了1NF的基础上,再遵循2NF.
是指表里都应该设计主键字段(主属性),
其他的非主属性,应该都围绕着主属性展开描述.
第三范式: 3NF, 是指遵循了前两范式
是指 字段之间的依赖性是直接的,而不是还要间接的产生依赖,导致查询关闭变得复杂

JDBC

1,概述

全称叫java database connectivity,专门用来完成利用一段java程序来操作数据库的事情.
是一套标准,是一套规范. 本质上就是一个jar包(一堆的java工具类)

2,使用步骤

1, 找到jar包,并拷贝进项目
2, 利用工具类通过java连接数据库 (用户名root 密码root 数据库名cgb211101 数据库的端口号3306)
3, 通过java程序发起SQL语句
4, 数据库把执行结果, 返回给java程序

jdbc编写步骤

1.注册驱动

2,获取和数据库的连接

3,获取传输器,执行SQL

4,执行SQL

5,解析结果集

6,释放资源

入门案例

//测试 jdbc
//需求：查询cgb2104库里的students表里的所有数据
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取和数据库的连接
//String url= "jdbc:mysql://localhost:3306/cgb2104?characterEncoding=utf8";//指定要连接哪个数据库
String url= "jdbc:mysql:///cgb2104?characterEncoding=utf8";//指定要连接哪个数据库
        String user= "root" ; //使用的用户名
        String pwd= "root" ; //使用的密码
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3,获取传输器,执行SQL
        Statement st = conn.createStatement();
        //4,执行SQL
        ResultSet rs = st.executeQuery("select * from students");
        //5,解析结果集
        while( rs.next() ){//next()判断结果集中是否有数据
            for (int i = 1; i <= 5 ; i++) {
                //获取每列的值并打印
                System.out.println( rs.getString(i) );
            }
        }
        //6,释放资源
        rs.close(); //关闭结果集
        st.close();//关闭传输器
        conn.close();//关闭连接
    }
}

//利用jdbc,操作数据库
//需求:查询部门表的所有数据
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取数据库的连接
        //String url = "协议://数据库的服务器的IP地址:端口号/数据库名";
        String url = "jdbc:mysql://localhost:3306/cgb211101";
        Connection c = DriverManager.getConnection(url,"root","root");
        //3,获取传输器
        Statement s = c.createStatement();
        //4,执行SQL--查询部门表的所有数据
        ResultSet r = s.executeQuery("select * from dept");//执行查询的SQL
        System.out.println("java程序与数据库连接成功!!");
        //5,处理结果--遍历结果
        //next()从来判断,只要r里有数据就返回true.没数据就返回false
        while( r.next() ){
            //获取不同类型的数据getXxx()
            int deptno = r.getInt(1); //获取第N列的值
            String dname = r.getString(2);
            String loc = r.getString(3);
            System.out.println(deptno+dname+loc);
        }
        //6,关闭资源close
        r.close();//关闭结果集resultset
        s.close();//关闭传输器statement
        c.close();//关闭连接Connection
    }
}

SQL注入

SQL攻击
–1,概述
1,问题:当用户输入特殊值: jack ’ #时,甚至不需要密码也能登录
2,产生的原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了…
SELECT * FROM USER WHERE NAME=‘jack’ #’ AND pwd=‘123456’
现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#,导致了,SQL语义发生改变
3,哪里出现的问题?Statement传输器不安全,低效
4,解决方案?使用新的传输器PreparedStatement代码现有的Statement
5,PreparedStatement工具,安全,高效.而且SQL写法简洁.
PreparedStatement把特殊符号#当做普通文本字符在使用,没有注释的意思了

//需求：利用jdbc,根据用户名和密码查询cgb2104库里的user表
//SQL注入攻击问题
private static void login() {
    try{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql:///cgb2104?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        Statement st = conn.createStatement();
// String sql ="select * from user2 where name='jack' and password='123456'";//写死了

        String user = new Scanner(System.in).nextLine();//用户输入jack'#
        String pwd = new Scanner(System.in).nextLine();
        //SQL注入攻击问题：本质上是因为SQL语句中出现了特殊符号#,改变了SQL语义
String sql ="select * from user2 where name='"+user+"' and password='"+pwd+"'";
        ResultSet rs = st.executeQuery(sql);//执行查询的SQL,返回结果集
        if(rs.next()){
            System.out.println("登录成功~");
        }else{
            System.out.println("登录失败~");
        }
        st.close();
        conn.close();
    }catch(Exception e){
        e.printStackTrace();//有异常,直接打印异常信息
        //System.out.println("执行失败。。。");//上线
    }

}

SQL注入的解决方案

PreparedStatement 语句

SQL注入解决方案：

Statement对象换为PreparedStatement对象

//解决SQL注入攻击的方案
private static void login2() {
    try{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql:///cgb2104?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
//            Statement st = conn.createStatement();不行,不安全,会被SQL攻击

        String user = new Scanner(System.in).nextLine();//用户输入jack'#
        String pwd = new Scanner(System.in).nextLine();
        //?叫占位符 ,SQL的骨架
String sql ="select * from user2 where name=? and password=?";
        //先把SQL骨架发给数据库执行
        PreparedStatement ps = conn.prepareStatement(sql);
        //给SQL里的? 设置参数
        ps.setString(1,user);//给第一个?设置值是user
        ps.setString(2,pwd);//给第二个?设置值是pwd
        
        ResultSet rs = ps.executeQuery();//执行拼接好的SQL,返回结果集

        if(rs.next()){
            System.out.println("登录成功~");
        }else{
            System.out.println("登录失败~");
        }
        ps.close();
        conn.close();
    }catch(Exception e){
        e.printStackTrace();//有异常,直接打印异常信息
        //System.out.println("执行失败。。。");//上线
    }
}

//利用新的传输器,查询部门编号为1的数据
public class Test5 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //3,获取传输器,并执行SQL骨架
        String sql="select * from dept where deptno = ?";
        PreparedStatement s = c.prepareStatement(sql);
        //设置SQL的参数--是指给第几个问号,设置什么值
        s.setObject(1,1);
        //4,执行SQL
        ResultSet r = s.executeQuery();//执行查询的SQL
        //5,解析结果集
        while(r.next()){
            Object deptno = r.getObject(1);//获取第1列的值
            Object dname = r.getObject(2);//获取第2列的值
            Object loc = r.getObject(3);//获取第3列的值
            System.out.println(""+deptno+dname+loc);
        }
        //6,释放资源
        r.close();
        s.close();
        c.close();
    }
}

常见错误

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
错误原因：

1）jar没有导入，没有builder path

2）Class.forName("com.mysql.jdbc.Driver"); 字符串拼写错误

Unknown database mydb;
错误原因：

数据库名称拼写错误

Access denied for user ‘root123’@‘localhost’ (using password: YES)
错误原因：

数据库用户名或者密码错误

Table ‘py-school-db.mydb’ doesn’t exist
错误原因：

表不存在,也可能表名写错了

优化: 提供jdbc的工具类

–1,创建工具类

//充当了jdbc的工具类,抽取一些共性代码
public class JDBCUtils {
    /**
     * 释放资源
     * @param r 结果集
     * @param s 传输器
     * @param c 连接器
     */
    static public void close(ResultSet r, PreparedStatement s,Connection c){
        if(r != null){//防止了空指针异常
            try {
                r.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                r = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(s != null) {//防止空指针异常
            try {
                s.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                s = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(c != null) {//防止空指针异常
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                c = null;//手动置空,等着GC进行垃圾回收
            }
        }
    }

    /**
     * 获取数据库的连接
     * @return 将给调用者返回一个和数据库连接的对象Connection
     * @throws Exception
     * static:保证资源在内存中,贮存的时间长.只会加载一次节省内存
     * public:工具类可以被所有人使用,最大的访问权限方便调用来调用
     */
    static public Connection get() throws Exception{
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //把获取到的数据库的连接,返回给调用者
        return c;
    }
}

使用工具类(用新的传输器新增)

//利用新的传输器结合着工具类,新增一个用户信息
public class Test6 {
    public static void main(String[] args) {
       //扩大变量的作用范围:为了让try  catch  finally都能用
       Connection c = null;
       PreparedStatement p = null;
       try {
            //1,利用工具类,来获取数据库的连接
            c = JDBCUtils.get();
            //2,获取传输器,执行SQL
            String sql="insert into user values(null,?,?)";
            p = c.prepareStatement(sql);
            //给SQL绑定参数
            p.setObject(1,"jerry");
            p.setObject(2,"123");
            //3,执行SQL
            p.executeUpdate();//执行增删改的SQL,返回一个影响行数(通常不处理)
       }catch (Exception e){
           System.out.println("数据插入失败!!");
       }finally {//保证一定会被执行的代码
           //利用工具类close完成释放资源(新增业务,没有结果集,传入null就可以了)
           JDBCUtils.close(null,p,c);
       }
    }
}