| 关于日期（date类型）的sql说明 | 在向date类型的字段进行插入数据写日期直接写就可以不能带 - 符号 |
|---|---|
| 例如 | insert into 表名 values （20201021） |
insert into 表名 values （20201022）
执行之后的结果为
如何向某个字段所有数据都加几
update 表名 set age=age+2 #说明age字段的所有数据都加2
性别是男或女
sex in（‘男’，‘女’）
一,数据库SQL语句
–1,概述
存储数据,管理数据 的仓库
–2,分类
关系型数据库:
是指存的数据有关系,一行就是一个用户的信息,每列就是要具体描述的数据.MySQL/Oracle
非关系型数据库:是指数据间的关系不紧密.Redis/Solr....
–3,结构
数据库 - 表 - 记录
增删改查 CURD
–4,MySQL
--是免费的,是关系型数据库.
--服务器端: 真正存数据的,安装.(采用的端口号3306,密码root,字符集utf-8)
--检测:连接服务器,访问服务器里的数据.安装.
--打开开始处的DOS窗口输入密码,显示Welcome就成功了
--打开运行对话框(win+r)输入cmd-执行以下mysql命令-显示Welcome就成功了
C:\Users\Administrator>mysql -uroot -proot
--使用图形化界面客户端.Navicat/sqlYog
–5,SQL
是结构化查询语言,用来操作数据库
DML:数据操纵语言(CRUD)
DDL:数据定义语言(create...)
DCL:数据控制语言(分配权限)
二,常见的操作
注意⚠️：使用sql语句时符号必须是英文字符，否则会报错；
--1,查看数据库show ??
mysql> show databases;
--2,新建数据库create database ??
mysql> create database cgb2104;
mysql> show databases;
--创建数据库的时候需要加入utf-8
mysql>DEFAULT CHARSET=utf8;或者 set names utf8; #这两种在任何地方都可以写
--3,(!!!)删除数据库 drop database ??
mysql> drop database cgb2104;
mysql> show databases;
--4,使用数据库 use ??
mysql> use cgb2104;
--5,查看表 show ???
mysql> show tables;
--6,创建表
--create table 表名 (字段1,字段2,字段3);
--create table 表名 (字段名称 字段类型(字段长度),字段名称 字段类型(字段长度));
mysql> create table user(
-> id int(3),
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.07 sec)
--创建学生表,包含id name addr age字段
mysql> create table student(
-> id int(10),
-> name varchar(100),
-> addr varchar(150),
-> age int(3)
-> );
Query OK, 0 rows affected (0.08 sec)
--7,(!!!)删除表 drop table ??
mysql> drop table student;
Query OK, 0 rows affected (0.05 sec)
--8,查看表结构 desc ??
mysql> desc user;
--9,修改表 alter table ??
添加age字段/列
alter table ??? add column 字段名 字段类型(字段长度)
mysql> alter table user add column age int(2);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
--10,新增表的记录
insert into 表名 values(字段1的值,字段2的值,字段3的值);
mysql> insert into user values(
-> 1,
-> 'jack',
-> 20
-> );
Query OK, 1 row affected (0.04 sec)
mysql> insert into user values(2,'rose',18);
Query OK, 1 row affected (0.04 sec)
mysql> insert into user values(2,'tony',30);
Query OK, 1 row affected (0.09 sec)
#插入多行数据，每一行数据间的括号使用‘，‘隔开即可；
insert into student values(9873,'hjg','一'),
(9876,'jfd','二'),
(9879,'gng','一'),
(9875,'gj','二');
--11,查询表的记录
select * from 表名;
mysql> select * from user;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | jack | 20 |
| 2 | rose | 18 |
| 2 | tony | 30 |
| 2 | 10000 | 30 |
+------+-------+------+
4 rows in set (0.00 sec)
--12,修改表的记录
update 表名 set 字段名 = 新值
mysql> update user set age = 20 ;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 4 Changed: 3 Warnings: 0
--13,(!!!)删除表的记录
delete from 表名;
mysql> delete from user;
Query OK, 4 rows affected (0.05 sec)
三,字段约束
–1,概述
可以给字段添加各种约束条件
–2,主键约束
--给字段添加主键约束,这个字段的值唯一并且不能为空.
--自动递增,这个字段的值由数据库自动维护,从1开始依次按照+1变大
mysql> create table a(id int primary key auto_increment);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into a values(null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from a;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
--3,非空约束
--让非空约束的列,值 不能为空
mysql> create table b(id int primary key auto_increment,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into b values(null,'jack');
Query OK, 1 row affected (0.04 sec)
mysql> insert into b values(null,null);
ERROR 1048 (23000): Column 'name' cannot be null
--4,唯一约束
--给字段加唯一约束,实现字段的值必须唯一
mysql> create table c(
-> id int primary key auto_increment,
-> name varchar(10) unique
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into c values(null,"jack");
Query OK, 1 row affected (0.04 sec)
mysql> insert into c values(null,"jack");
ERROR 1062 (23000): Duplicate entry 'jack' for key 2
四,使用工具连接服务器,操作数据
–0,操作
连接-输入连接名/密码-测试-确定
–1,数据库操作
--新建库:右键-新建数据库-输入数据库的名字/选成utf8-确定
--删除库:右键-删除数据库-确定
–2,表操作
–3,记录操作
一,工具的使用
--1,表的操作
--查询表
-双击库-双击表-ok
--创建表
-点击表-新建-输入字段名称/字段类型/字段长度-保存-起个表名-确定
--删除表
-点击表-右键-删除表
--修改表
-点击表-设计表-添加栏位/删除栏位-保存-确定
--2,记录的操作
双击打开表+ - √ 刷新
二,基本函数
--1,概述
SQL里的函数可以看做java里的方法
每个函数都提供了不同功能
--2,常见函数
select * from dept #查询所有的列 -- 低效
select dname from dept #查询dname的列 -- 高效
select id,dname from dept #查询id,dname的列 -- 高效
select id,dname,lower(dname) from dept #lower(?)把?转小写
select id,dname,lower(dname),upper(dname) from dept#upper(?) 把?转大写
select ename,upper(ename),lower(ename) from emp
select dname,length(dname) from dept #length(?)获取?的长度
select ename,length(ename),job,length(job) from emp
#length用的是utf-8编码表,一个数字或者字母只占一个字节,一个汉字占3个字节
#substr(1,2,3)截取子串,1是列名,2是从哪儿开始,3是截取多长
select dname,substr(dname,3,2) from dept
#concat(1,2)拼接字符串，1是列名，2是要拼接的新值...
select dname,concat(dname,"hello") from dept
#如果想要继续拼接新值则在后面继续添加逗号新值即可
select dname,concat(dname,"hello",'123') from dept
#replace(1,2,3)替换，1是字段名，2是要被替换的数据，3是新数据
select dname,replace(dname,"a",'6') from dept
select ename,replace(ename,"on","666") from emp
#ifnull(1,2)如果是null就替换成新值 1是字段名2是新值
select comm,ifnull(comm,10000) from emp
#null不参与运算
select ename,sal,comm,sal + ifnull(comm,0) from emp
select mgr,ifnull(mgr,0)*10 from emp
#round(四舍五入) & ceil(向上取整) & floor(向下取整)
select comm,round(comm),ceil(comm),floor(comm) from emp
#uuid
select uuid()
#now(当前年月日时分秒)
#year(当前年) month(当前月) day(当前日)
#hour(当前时) minute(当前分) second(当前秒)
select now(),year(now()),month(now()),day(now())
select now(),hour(now()),minute(now()),second(now())
# 转义符号\ xi'an
select 'xi\'an'
三,条件查询
--1,概述
满足了条件的才查
distinct(去重) / where(过滤条件) / like(模糊查询) / limit(分页) /order by(排序) / between...and[在..之间]
having只能用做要查询的列即就是select后面的列，不能查询select后面没有的列
但是where可以
having和group by是固定搭配,用来过滤分组后的数据
--2,测试
select loc from dept
#给结果去重
select DISTINCT loc from dept #将loc列中如果有多个重复的内容变成一个内容查询出来，没有重复的内容也直接查询出来；
#where用来过滤查询的数据
select * from dept where id = 1
select * from dept where dname='operations'
#条件满足了才能查出来
select * from dept where 1 = 0
#两个条件间的关系: and(并且) or(或者)
select * from dept where id=1 and loc='一区' #根据条件为id=1，且loc=‘一区’查询该数据
select * from dept where id=1 or id=3 #根据条件查询id=1和id=3的数据
#模糊查询 like %是0~n个字符 _是一个字符
select * from dept where dname like 'a%'#以a开头-高效
select * from dept where dname like '%s'#以s结尾
select * from dept where dname like '%a%'#包含a
select * from dept where dname like 'a__'#a后面有两个字符的
select * from emp where ename like '%n%'
#null 对字段值为null的处理 ，查询出来该列为null的所有数据
select * from emp where comm is null
select * from emp where comm is not null
#between and [1000,2000] 在1000~2000间
select * from emp where comm>=1000 and comm<=2000 #此时必须加 = 才包括 1000和2000
select * from emp where comm between 1000 and 2000 #此时已经包括1000和2000
#limit 分页
select * from emp limit 3; #取前三条
select * from emp limit 1,3; #从第2条开始,总共取3条
select * from emp limit 2,2; #从第3条开始,总共取2条
#order by 排序
select * from emp order by sal #默认升序,省略了asc
select * from emp order by sal desc #降序
四,聚合函数
–1,概述
指把一列的值,聚合在一起,统一做分析
max() / min() / sum() / avg() / count()
–2,测试
#获取2015年以前入职的员工
select * from emp where year(hiredate)< 2015
#年薪统计
select ename,sal,comm,
#as用来给列起个别名(as可以省略不写)
(sal+ifnull(comm,0))*12 as 年薪
from emp
#聚合函数 max(最大值) / min(最小值)
# sum(求和) / avg(平均数)
select max(sal),min(sal),sum(sal),avg(sal) from emp
# count() 求总记录数
select count(*) from emp #低效
select count(1) from emp #高效
select count(id) from emp #高效
select count(comm) from emp #低效
二,分组
–1,概述
把结果按照分组的形式,再数据分析
–2,测试
如果需要查询的列有：聚合函数的列 / 字段和非聚合函数的列 / 字段， 必须使用分组查询
且分组的列必须为非聚合函数的列；
分组查询的结果为分组的非聚合列的内容相同的为一行的数据（分为一组），在根据相应的聚合函数功能进行计算，然后在进行分组；
#查询每个部门里员工的最高薪
SELECT deptno,max(sal) from emp
GROUP BY deptno #分组
#查询每个部门里员工的最高薪和平均值
select max(sal),avg(sal),deptno from emp
#如果出现了聚合函数(max min avg count sum),非聚合列,必须分组
group by deptno
#查询每个岗位的员工的最高薪和最低薪
select max(sal),min(sal),job,avg(sal) from emp
GROUP BY job
#平均工资小于8000的部门
select deptno,avg(sal) from emp
group by deptno
#having和group by是固定搭配,用来过滤分组后的数据
having avg(sal) < 8000
#把having改成where !!! #where里不能出现聚合函数
-- select deptno,avg(sal) from emp
-- where avg(sal) < 8000
-- group by deptno
#deptno这个列里的数据出现的次数 >2
select deptno,count(deptno) from emp
group by deptno #按照非聚合列分组
having count(deptno) > 2 #过滤分组后的数据
三,事务
–1,概述
为了保证数据的安全,如果要操作多条SQL,要么全成功,要么全失败
–2,ACID特点
--原子性:多条SQL是密不可分的,整体的结果要么全成功,要么全失败
--一致性:保证数据的守恒,总和不变
--隔离性:数据库支持高并发,但是操作之间是独立的,被隔离的
--持久性:是指SQL语句对数据库的操作(增删改),影响是永久的
–3,隔离级别
--读未提交:性能好,但是数据不安全
--读已提交:性能稍差,但是数据安全
--可重复读:性能更差点,但是数据更安全 --MySQL默认的级别
--串行化:性能最好,最安全
–4,事务的处理
--提交:将会对数据库产生持久影响
--回滚:撤回到最初状态,没有影响
--开启:MySQL默认就已经开启了,是一条SQL一个事务
–5,测试
#不用mysql管理事务,一条语句一个事务
begin;#开启事务
insert into d values(8,"rose2");
insert into d values(9,"tony2");
#关闭事务(commit/rollback)
commit#提交事务,产生永久影响
#rollback;#回滚事务,数据库里没有新数据
#查询SQL,commit后才有数据,没有commit或者rollback都查不到
select * from d;
四,字段约束
外键约束 唯一约束 非空约束
外键约束：
在创建本表的时候，使用本表的字段（id），和想要连接的表子段（id）进行关联，
FOREIGN key 本表的字段 REFERENCES 想要连接的表名(想要连接的表子段)
注意⚠️：使用外键约束的两个表的子段的内容必须要一一对应；
#创建表,并使用字段约束
create table m(
id int primary key auto_increment,#主键约束,值不能是null,不能重复
name varchar(10) unique, #唯一约束,值不能重复
addr varchar(20) not null #非空约束,值不能为null
)
#插入数据
insert into m values(null,'jack',"bj");
insert into m values(null,'rose',"bj");
create table n(
id int primary key auto_increment,
sex char(3) default '男' #设置默认值
);
create table xyz(
id int primary key auto_increment,
age INT
CHECK (age>0 AND age<=200) #检查约束,在范围内才可以
);
#外键约束
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)
);
delete from tb_user where id=1;
#有外键约束,id=1在tb_user_address表中还在用,不能删.
delete from tb_user_address where user_id=1;
五,多表联查
#笛卡尔积
SELECT * from emp , dept
#inner join 交集
SELECT * from emp inner join dept
#left join 左边的所有数据和右面满足了的数据
#right join 右边的所有数据和左面满足了的数据
#查询tony的部门信息
#笛卡尔积
select * from emp , dept
where #如果这两张表使用笛卡尔积查询,如果没有加这个where条件查询,那么每行数据都会有重复;
emp.deptno = dept.id #描述了两张表的关系，emp的deptno 和 dept的id的值要相同
and
emp.ename='tony' #过滤条件,如果加入了这个条件那么查询到结果是ename='tony'的关于两张表的这一行数据
#join 分为内连接,外连接(左外连接left join/右外连接right join)
#select * from emp inner join dept #内连接,取交集 左边和右边的所有数据
#select * from emp left join dept #左连接,取左边的所有和右边满足了的
select * from emp right join dept #右连接,取右边的所有和左边满足了的
on. #注意：此时要使用on 不能使用where
emp.deptno = dept.id #描述了两张表的关系
and
emp.ename='tony' #过滤条件，如果加入了这个条件那么查询到结果是ename='tony'的关于两张表的这一行数据
#查询tony的部门信息
#根据tony查部门编号,查emp
select deptno from emp where ename='tony';
#根据上一步查到的部门编号,查询dept表里的信息
#select * from dept where id = 2;
#子查询--把上次的查询结果作为一张表来用
select * from dept where id =(
select deptno from emp where ename='tony'
);
#联查students和scores表的练习:
#分别使用笛卡尔积 / join / 子查询实现
#查询sno=101的总分
#查询sno=103的3-245的得分
一，练习
#联查students和scores表的练习:
#分别使用笛卡尔积 / join / 子查询实现
#查询sname='李军’的总分
#方式1：子查询
select sum(degree) from scores where sno=(
select sno from students where sname='李军'
)
#方式2：笛卡尔积
select sum(degree) from scores a , students b
where a.sno=b.sno #表关系
and b.sname='李军' #过滤条件
#方式3：join
select sum(degree) from scores a inner join students b
on a.sno=b.sno #表关系
and b.sname='李军' #过滤条件
二，索引
用户无法看到索引，它们只能被用来加速搜索/查询。
–1，概述
提高查询效率，给字段加索引
普通索引：
唯一索引：字段的值必须唯一
复合索引：一个索引包含着多个字段
–2，索引语法
创建普通索引：create index 索引名 on 表名(字段名)
查看索引：show index from 表名
创建唯一索引：ALTER TABLE 表名 ADD UNIQUE (字段名)
创建复合索引：ALTER TABLE 表名 ADD INDEX 索引名 (字段名,字段名)
删除索引：ALTER TABLE 表名 DROP INDEX 索引名
–3，测试
#创建普通索引：索引的名字是随便起的
#create index 索引的名字 on 表名（相应表里的字段，即就是这个字段就会加索引）
create index name_index on teachers(tname)
#查询索引
show index from teachers
#使用索引
explain
select * from teachers where tname='易天'
#创建唯一索引
ALTER TABLE teachers ADD UNIQUE (tbirthday)
#使用索引
explain
#解释SQL的执行过程,主要是看有没有用到索引(看key的是否有值)
select * from teachers
where tbirthday='1958-12-02 00:00:00'
#复合索引：在多个字段上创建索引；
#创建复合索引--使用时遵循最左特性：优先使用最左边的 即就是最左边或者和最左边联合使用就高效，如果不和最左边的子段联合使用就不会高效
ALTER TABLE teachers ADD INDEX fuhe_index
(tname,tsex,tbirthday)
--4，总结
好处：提高查询效率
坏处：索引不适合有太多，占用内存
怎么用索引？
--正常的写SQL语句
--使用explain关键字来查看SQL的执行计划，SQL有没有用到索引(key)
--查看所有的索引:show index from 表名
三，视图
–1，概述
当做一个表来用，缓存了上一次的查询结果。
–2，语法
创建视图：create view 视图名 as SQL语句
使用视图：select * from 视图名
–3，测试
#创建视图
create view stu_view
as
select * from students limit 3
#使用视图
select * from stu_view
–4，总结
好处：优化了SQL，复杂的SQL只写一次，结果就会缓存在视图中，下次查视图就可以了
坏处：数据都是重复的，占内存。SQL无法优化。
四，SQL面试题
使用一条sql语句将下面的表（表名为kb）里的分数大于80为优秀，小于60不及格
select (case when yuwen>=80 then '优秀' when yuwen>=60 then '及格' else '不及格' end) as 语文,(case when shuxue>=80 then '优秀' when shuxue>=60 then '及格' else '不及格' end) as 数学,(case when yingyu>=80 then '优秀' when yingyu>=60 then '及格' else '不及格' end) as 英语 from kb;
执行的结果为
#查询所有记录
select * from students #低效
#只查询指定
select sno,sname from students #高效
#查询id为100的记录
select sno,sname from students where sno=101
#模糊查询记录 %通配符，通配0~n字符
select sno,sname,class from students where class like '%3%'
#查询之间范围之间的所有记录 [101~105]
select * from students where sno BETWEEN 101 and 105
#in 以下两句等效
select * from students where sno in (101,105)
select * from students where sno=101 or sno=105
#查询满足两个条件的记录
select * from students where sno=103 and ssex='男'
#查询jack的住址(tb_user 联查 tb_user_address)
select address from tb_user_address
where user_id = (
select id from tb_user where name='jack'
)
#算每个学员的年龄
select sname,year(now())-year(sbirthday) age
from students
#按age升序查询记录asc
select sname,year(now())-year(sbirthday) age
from students order by age desc
#以name升序、age降序查询记录
select sname,year(now())-year(sbirthday) age
from students order by sname #unicode值
#总记录数
select count(1) from scores
#查询记录中最年长和最年轻
SELECT max(age),min(age) from user
#查询年龄最大的用户信息
SELECT * from user order by age desc LIMIT 1#年龄倒序取第一个
#查询大于平均年龄的记录
SELECT * from user where age > (
SELECT avg(age) from user
)
#查询各部门的最高薪
select max(sal),deptno from emp GROUP BY deptno
#查询各部门的平均工资
select avg(sal),deptno from emp GROUP BY deptno
#查询存在部门的员工信息
select * from emp where deptno is not null
#查询没划分部门的员工信息
select * from emp where deptno is null
#查询同名的员工记录
select * from emp
where #count(ename) >1 where里不能用聚合函数
ename in(
select ename from emp
group by ename #名字相同，就按名字分组
having count(ename)>1 #过滤，次数>1的
)
#查询得分前3名的学员信息
select sno from scores order by degree desc limit 3
select * from students where sno in (103,107,105)
select * from students where sno in (
#子查询里不支持order by / limit
#select sno from scores order by degree desc limit 3
)
#查询课程是“计算机导论”的，得分前3名的学员信息
#课程号“3-105”的倒数最后3名学员排行
#查询女老师的信息
关于having和where
having只能用做要查询的列即就是select后面的列，不能查询第三列
但是where可以
having和group by是固定搭配,用来过滤分组后的数据
五，Oracle
–1，概述
和MySQL一样，可以用来存储数据和管理数据
–2，安装
--服务器端：设置密码
--客户端：PLSQL，免安装绿色版，双击就能用
--登录：使用system，口令就是安装时的密码
–3，使用
--MySQL：数据库 - 表 - 记录
--Oracle：用户 - 表 - 记录
--创建用户：左上角新建-用户-输入用户名和密码-设置权限dba/resource/connect-确定
--新用户登录：左上角的钥匙-输入用户名和密码-确定
--创建表：选中Tables-右键-新建-输入表名、字段、主键-确定
--创建记录：选中表-右键-编辑数据-填新数据-√
–4，测试
select t.* from student t
--oracle默认的日期格式：日-月-年
insert into student values('王','1','10-8月-1990','php',3)
--修改日期格式：年-月-日
alter session set nls_date_format = 'yyyy-mm-dd' ;
--按照新格式插入数据
insert into student values('刘','0','2000-1-1','java',4)
--decode判断，如果查到的是0就显示女，是1就显示男
select ssex, decode(ssex,0,'女',1,'男') sex from student
--casewhen 判断，如果查到的是0就显示女，是1就显示男
select ssex,
case ssex when '0' then '女' else '男' end sex
from student
--oracle的分页 rownum
--select t.* from student t limit 2 mysql
select t.* from student t where rownum <= 2 --oracle的特殊语法
一，Oracle独特的技术
--1，虚表dual
select A from dual
select sysdate from dual --当前时间
--2，序列
--就像MySQL里的主键自动递增的功能，Oracle可以自定义从哪儿开始，怎么变化
--创建序列：选中sequences-右键-新建-序列名、开始、变化...-确定
--查看序列：
select 序列名.currval from dual
select 序列名.nextval from dual
--使用序列：
insert into student(id,name) values(100,'jack')--只给两列赋值
insert into student(id) values(序列名.nextval)--只给一列赋值
--事务
MySQL里自动提交事务，Oracle里需要自己提交
--3，视图
--用来缓存SQL的查询结果
--创建视图：选中Views-右键-新建-输入视图名-确定
--发起SQL(缓存SQL的查询结果)
create or replace view stu_v as
select *
from
student
where ssex=1
--使用视图：好处是SQL简单了，坏处是数据大量冗余
select * from stu_v;
--4，触发器
--在看不到的地方，给执行SQL时加一段功能
--创建触发器：新建-程序窗口-触发器-输入触发器名、激活、事件、表-确定
--发起SQL并执行：
create or replace trigger Stu_Tri
before update on student
for each row
declare
-- local variables here
begin
--如果 SEX>5,统一设置成1
IF :NEW.SSEX>5 THEN
:NEW.SSEX := 1;
END IF;
end Stu_Tri;
--使用触发器：
--满足了触发器的条件，会把10改成1
update student set ssex = 10 where sname='rose';
--不满足触发器的条件，就是3
update student set ssex = 3 where sname='rose';
--5，存储过程
--可以完成一段功能，在数据库端实现。
--语法复杂，不利于后期的维护，了解。
二，JDBC
--1，概述
用来通过java程序 连接 数据库的技术
--2，使用
导入jar包(丰富的工具类)
获取和数据库的连接(用户名、密码)
通过程序执行SQL
通过程序处理结果
--3，向IDEA里导入jar包(丰富的工具类)
--创建project：File-New-Project-选java-next-next-输入工程名-finish
--创建lib目录：选中project-右键-new-directory-输入lib-回车
--把jar包粘贴进lib里：cv大法
--！！！编译jar包：
File-Project Structure-选Libraries-点加号-找到jar包的位置-ok-选你要使用的位置-Apply-ok
--检查：
jar包前面有了小箭头，能展开源码就可以了
--4，入门案例
package cn.tedu.jdbc;
import java.sql.*;
//测试 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();//关闭连接
}
}
--5，SQL注入攻击的解决方案
package cn.tedu.jdbc;
import java.sql.*;
import java.util.Scanner;
//测试 jdbc
/*总结:
作用: java程序和数据库连接的技术
SQL注入攻击问题:拼接SQL时,出现了特殊符号#,只需要输入用户名而不需要密码都可以登录
SQL注入攻击解决方案:不用Statement,换成了PreparedStatement,
新的工具类的好处:不用自己拼SQl的字符串了,安全,高效
PreparedStatement的解决过程:先把SQL骨架发给数据库,数据库进行预编译
等到设置好了参数,再和骨架一起执行,避免了人为的恶意的拼串造成的隐患
*/
public class Test2 {
public static void main(String[] args) {
// method();
// login();//SQL注入攻击问题
login2();//解决SQL注入攻击的方案
}
//解决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);
ps.setString(2,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("执行失败。。。");//上线
}
}
/*自己准备user2表(id/name/password),准备数据
CREATE TABLE `user2` (
`id` int(11) PRIMARY KEY auto_increment,
`name` varchar(10) default NULL,
`password` varchar(10) default NULL
) ;
*/
//需求：利用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("执行失败。。。");//上线
}
}
//需求：cgb2104库里的dept表里插入数据
private static void method() {
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();
// ResultSet rs = st.executeQuery();//执行查询的SQL,返回结果集
//int rows = st.executeUpdate();//执行增删改的SQL,返回影响行数
int rows = st.executeUpdate("insert into dept values(null,'php开发部','北京')");
System.out.println(rows);//打印影响的行数
st.close();
conn.close();
}catch(Exception e){
e.printStackTrace();//有异常,直接打印异常信息
//System.out.println("执行失败。。。");//上线
}
}
}
更多推荐
数据库的应用
发布评论