####MySQL
####数据库简介
之前通过流操作文件方式保存数据的弊端：
.效率低
. 实现过程比较复杂,开发效率低
。一般只能 保存小量数据
。只能保存文本
####什么是数据库
DataBase:数据库，数据库是一个文件集合
####什么是DBMS
databaseManagementSystem:数据库管理系统，用于管理数据库文件
常见的DBMS：Oracle MySQL，DB２，ＳＱＬｓｅｒｖｅｒ Sqlite
####数据库的分类
。关系型数据库
以表为数据库存储数据的单位，关系型数据库是经过数学理论验证可以保存
现实生活中任何关系型的数据
。非关系型数据库
主要应用在特定场景，解决某些具体问题，如解决高并发访问时的数据缓存，
（redis数据库属于非关系型数据库，redis内部以键值对的形式保存数据）
###主流关系型数据库的介绍
–MySQL:Oracle公司产品，后被Sun公司收购，后Sun公司被Oracle收购，
开源产品，分支：MariaDB，
2.Oracle:闭源产品，价格性能高
3.DB2:IBM公司产品，闭源，主要应用在IBM公司整套解决方案中
4.SqlServer:微软，闭源，主要应用在微软公司整套解决方案中
5.Sqlite:轻量级数据库，主要应用在嵌入式设备中或移动设备中
#####开源和闭源
-开源：公开源代码，靠卖服务盈利，任何人可以维护升级
-闭源：不公开源代码，靠卖产品和服务盈利
###什么是SQL
-Structured Query Language:结构化查询语言，用于人和DBMS进行交互
#####连接数据库
-Linux系统：mysql -uroot -p
-windows系统：打开命令行，直接输入密码即可
#####数据库相关的SQL
1.查看所有数据库
show databases;
2.创建数据库
create database 数据库的名称；
3.删除数据库
格式：drop database 数据库名称；
4.查看数据库详情
格式：show create database 数据库名称；
5.创建数据库指定字符集
格式：create database 数据库名称 character set utf8/gbk;
6.使用数据库
格式：use 数据库名称
use db1;
#####表相关SQL
-什么是表：表是数据库中保存数据的基本单位
1.创建表
格式：create table 表名（字段名 字段类型。。。。。）
2.查看所有表
show tables;
3.查看表详情
格式：show create table 表名；
4.创建表指定引擎和字符集
格式：create table 表名（字段名 字段类型。。。。。）engine=innodb/myisam charset=gbk/utf8;
####数据库表的引擎
1.innodb:支持数据库的复杂操作，包括外键，事务等
2.myisam:只支持数据库基础的增删改查操作
5.查看表字段信息
格式：desc 表名；
6.修改表名
格式：rename table 原名 to 新名；
rename table person to t_person;
7.修改表引擎和字符集
格式：alter table 表名 engine=engine=innodb/myisam charset=gbk/utf8;
8.添加表字段
格式1：alter table 表名 add 字段名 字段类型；最后
格式2：alter table 表名 add 字段名 字段类型 first；最前面
格式1：alter table 表名 add 字段名 字段类型 after xxx；xxx的后面
alter table t1 add gender varchar(5);
alter table t1 add id int first;
alter table t1 add sal int after id;
9. 删除表字段
格式：alter table 表名 drop 字段名
alter table t1 drop sal;
10。修改字段名称和类型
格式：alter table 表名 change 原字段名 新字段名 新类型；
11.修改字段类型和位置
格式：alter table 表名 modify 字段名 新类型 first/after xxx；
12.删除表
drop table 表名；
###数据相关SQL
create table hero(id int,name varchar(10),gender varchar(5));
1.插入数据
全表插入：insert into hero values(1,‘Tom’,‘男’);
指定字段插入：inert into herp(name,gender)values(‘张飞’,‘男’);
如果使用WIndows电脑SQL语句中写中文报错的话 通过以下指令解决：set names gbk;
-批量插入数据：insert into hero values(5,‘唐僧’,‘男’),(6,‘悟空’,‘女’);
2.查询数据
select 字段信息 from 表名 where 查询条件；
select * from hero;
select name from hero;
select * from hero where id>5;
3.修改数据
格式：update 表名 set gender=‘女’ where id=’’;
update hero set gender=‘女’ where id=1;
4.删除数据
格式：delete from 表名 where 条件；
delete from hero where name=‘Tom’;
#####主键约束
-主键：用于表示数据唯一性的字段称为主键
约束：就是对表字段的值添加限制条件
-主键约束：保证主键的值唯一并且非空
格式：create table t1(id int primary key,name varchar(12));
主键具有唯一性和非空性
###主键约束 +自增
格式：create table t2(id int primary key auto_increament,name varchar(10));
自增数值只增不减，从历史最大值的基础上+1
truncate table t2;//删除表并创建新表 自增数值清零
####注释 comment
-格式：create table t3(id int primary key auto_increment comment ‘这是个主键’,
name varchar (10) comment ‘这是名字’);
查看注释内容：show create table t3;
####和单引号的区别 -：用于修饰表名和字段名
create table t4(id int,name varchar(10));
’ :用于修饰文本
数据冗余：
如果表涉及不合里，可能会出现大量的重复数据，这种现象称为数据冗余，通过
拆分表的形式解决此问题
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age int dept int );
create table t_dept(id int primary key auto_increment,name varchar(10),parent_id int);
//插入数据 先插入部门表数据然后再插入员工表数据
insert into t_dept values(null,‘集团总部’,null),(null,‘财务部’,1),(null,‘财务a部’,2),(null,‘研发部’,1);
insert into t_emo values(null,‘张三’,8000,18,3),(null,‘李四’,800,75,4);
-创建表：
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
create table item(id int primary key auto_increment,name varchar(10),price int ,num int ,category_id int);
-插入数据
insert into category values(null,‘家电’,null),(null,‘洗衣机’,1),(null,‘办公用品’,null),(null,‘必’,3);
insert into item values(null,‘海尔洗衣机’,2300,38,2),(null,'晨光圆珠笔’5,100,4);
#####事务
事务（transaction）是数据库中执行统一业务多条SQL语句的工作单元，事务可以
保证多条SQL语句全部执行成功或全部执行失败。
验证事务
1.创建表
create table user(id int primary key auto_ncrement,name varchar(10),money int,state varchar(5));
2.插入数据
insert into user values(null,‘李雷’,5000,‘正常’),(null,‘韩梅梅’,50,‘正常’),(null,‘Lucy’,10,‘冻结’);
3.给李雷转账SQL
update user set money=4000 where id=1 and state=‘正常’;
update user set money=1010 where id=3 and state=‘正常’;
添加事务保护
-开启事务
begin;
回滚事务：因为一条成功一条失败所以不能把内存中的改变提交到磁盘上去，所以需要执行
回滚命令。执行之前可以打开另外一个窗口数据库查看数据库中的数据是否发生改变
（变的是内存中的数据，数据库里面的数据并没有发生改变）
rollback；
-转账成功执行流程
1.开启事务
begin;
2.让李雷-1000
update user set money=3000 where id=1 and state=‘正常’;
3.让韩梅梅+1000
update user set money=1050 where id=2 and state=‘正常’;
4.提交事务
commit;
和事务相关的SQL
１.begin:开启事务
2.rollback：回滚事务 把内存中的改动清除
3.commit：把内存中的改动一次性的提交到磁盘
第二种开启事务的方式:
数据库中事务默认是自动提交的
1.查看数据库中自动提交的状态
show variables like ‘%autocommit%’;
2.修改自动提交的状态 0关闭 1开启
set autocommit=0;
—savepoint :保存回滚点
格式：savepoint s1;
rollback to s1;
####SQL 分类
####DDL data Definition Language
-数据定义语言
包括：create ，alter，drop，truncate
不支持事务
####DML Data Manipulation language
-数据操作语言
包括：insert delete update select(DQL)
支持事务
####DQL Data Query Language
-数据查询语言
-只包括select
###TCL Transaction Control Language
-事务控制语言
包括：begin,rollback, commit，savepoint xxx,rollback to xxx;
####DCL Data Control Language 数据控制语言
用于分配用户权限相关的SQL
数据库的数据类型
主要包括以下五大类
整数类型：BIT ，BOOL，TINY INT,SMALL INT,MEDIUM INT, INT ,BIG INT
浮点数类型：FLOAT,DOUBLE, DECIMAL
字符串类型：CHAR, VARCAR , TINY BLOB,BLOB,MEDIUM BLOB ,LONG BLOB;
日期类型：Date,DateTime,TimeStamp,Time,Year
其他类型：Binary,varbinary ,enum , set,geometry,point,MultiPoint,LineString,Polygon,GeometryCollection;
1.bigint(m)对应Java中的long,m代表现实长度，需要结合zerofill使用
create table t_int(id int,age int(8),zerofill)
2.浮点数：double(m,d)m代表总长度，d代表小数长度decimal超高精度小数
3.字符串 char(m):固定长度
varchar(可变长度)
可变长度更节省空间，固定长度执行效率高
text 可变长度 最大65535；
日期：date:只能保存年月日
time：只能保存时分秒
datetime:保存年月日时分秒，最大值9999-12-31默认值null
timestampe:保存年月日时分秒，最大位2038-1-19，默认值为当前时间
create table t_date (d1 date,d2 time,d3datetime,d4 timestamp);
insert into t_date values(‘2018-11-15’,‘16:58:33’,null,null);
######导入*.sql数据到数据库
window系统
source d:/tables.sql;
Linux系统：
source /桌面/tables.sql;
#####is null 和 is not null
-查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal,from emp where mgr is null;
2.查询emp中没有奖金comm的员工姓名，工资，奖金
select ename,sal,comm from enp where comm is null;
3.查询emp表中有奖金的员工信息
select * from emp where comm is not null and comm>0;
#####别名
-把查询到的员工姓名enamel 改成 名字
select ename as ‘名字’ from emp;
select ename as 名字 from emp;
select ename 名字 from emp;
####去重
1.查询emp表中出现的所有职位job
select distinct job from emp;//去重
####比较运算符>,<,>=,<=,=,!=和<>
#####and和or
-and和Java中的&&效果一样
or 和Java中的||效果一样
-使用in:
select * from emp where sal in(5000,1500,3000);
select * from emp where sal not in(5000,1500,3000);
–between and
select * from emp where sal between 1000 and 2000;
select * from emp where sal not between 1000 and 2000;
####模糊查询
-_：代表单个未知字符
-%：代表0或多个未知字符
select * from t_item where title like ‘%记事本%’;
#####分页查询 limit
格式：limit 跳过的条数，请求的数量
如：请求第一页20条数据 limit 0,20;
请求第三页十条数据 limit 20,10;
公式：limit(页数-1)*每页数量，每页数量
如：
select * from t_item order by price limit 7,7;
select name,sal from emp order by sal desc limit 6,3;
######数值计算+ - * / %
例如：
select ename,sal, sal*5 年终奖 from emp;
select ‘helloworld’;
1.获取当前时间now()
select now();
2获取当前日期curdate();
select curdate();
3.获取当前时间curtime();
select curtime();
4.从年月日时分秒中提取年月日 和时分秒
select date(now());
select time(now());
5.从年月日时分秒中提取时间分量 extract(year from now())
select extract(year from now());
日期格式化
格式：
%Y四位年 %y两位年
%m两位月 %c一位月
%d日
%H24小时 %h12小时
%i 分
%s 秒
-date_format(时间，格式);
-把非标准格式的时间转回默认格式 str_to_date()
select str_to_date(‘14.08.2018 08:00:00’,’%d.%m.%Y %H:%i:%s’);
####ifnull(x,y)函数
age=if(x,y)如果x的值为null则age=y 如果x值不为null则age=x;
######聚合函数
-聚合函数用于多行数据进行统计，平均数，最大值，最小值，求和，统计数量
平均值；avg(字段名称)
1.查询所有员工的平均工资
select avg(sal) from emp;
最大值：max(字段名);
1.查询所有员工中的最高工资
select max(sal) from emp;
最小值：min（字段名）；
select max(sal) from emp;
求和：sum(字段名)；
select sum(sal) from emp;
统计数量 count(字段名)不统计null
select count(num) from emp;
select count(*) from emp;
字符串相关
1.字符串拼接concat(‘aa’,‘bb’);
2.获取字符串长度 char_length(‘abc’)
select ename,char_length(ename) from emp;
3.获取字符串在另外一个字符串出现的位置
-格式1：instr(str,substr);
格式2：locate（substr,str）；
4插入字符串 insert(str,start,length,newstr);
select insert(‘abcdefg’,3,2,‘m’);结果abmefg
5.转大小写
格式：upper(str) lower(str);
select upper(‘nba’);
6.取出两端的空白 trim(str);
7.截取字符串
-left(str,num);
select left(‘adsdasd’,2);
-right(str,num);
substring(str,start,?end);
8.重复reperat
select repeat(‘ab’,2);
9.替换replace
select replace(‘abcdefg’,‘c’,‘x’);
10.反转 reverse
select reverse(‘abc’);
######数学相关的函数
-向下取整 floor();
select floor(3.15);->3
四舍五入 round(num)
select round(23.8);->24
round(num,m)
select round(23.879)；->23.88
非四舍五入
select truncate(23.879,2);->23.87
-随机数 rand();0-1
获取3，4，5随机数
select floor(rand()*3)+3
##############分组查询
group by
例如：
select avg(sal) from emp group by deptno;
#####多字段分组查询
如：
select deptno,gender,max(age) from emp group by deptno,gender;
######having
having后面可以写集合函数的条件也可以写聚合函数的条件，但是不推荐在having
后面写普通字段的条件
where 后面不能写聚合函数
having要结合分组查询使用
错误写法
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
正确写法：
select deptno,avg(sal) a from emp group by deptno having a>2000;
###3子查询（嵌套）可以多层嵌套
1.查询emp表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=5000;
通过子查询把上面两条合并成一条
select * from emp where sal=(select max(sal) from emp);
子查询总结：
1.嵌套在SQL语句中的查询语句称为子查询
2.子查询可以嵌套n层
3.子查询可以嵌套在那些位置？
1.写在where或having后面作为查询的条件的值
2.写在from后面当成一张表使用 ****必须有别名###
3.写在创建表的时候
create table newemp as (select * from emp where deptno=20);
#####关联查询
同时查询多张表的查询方式称为关联查询
select e.name d.name from emp e,dept d where e.deptno=d.deptno;
#####笛卡尔积
如果关联查询不写关联关系，则得到这两张表结果的乘积，这个成绩称为笛卡尔积
-笛卡尔积是错误的查询方式导致的结果，工作中切记不要出现
######等值连接和内连接
1.等值连接：select * from A,B where A.x=B.x…;
内连接：select * from A join B on A.x=B.x where A.age=10;
#####外连接
查询A，B两张表的的数据，如果查询两张表的交集数据使用内连接或等值连接，
如果查询某一张表的全部数据两外一张表的交集数据则用外连接
左外连接：select * from A left join B on A.x=B.x where a.age=1;
右外连接：select * from A right join B on A.x=B.x where a.age=1;
查询两张表的交集用等值连接或内连接，推荐使用内连接
查询一张表的全部数据和另外一张表的交集数据使用外连接
select …from 表名 where …group by…having …order by …limit …
如果表中建立关系的字段指向当前表的主键，则称为自关联
#######表设计之关联关系
什么是外键：主键是用于表示数据唯一性的字段，外键是用于建立关系的字段，
值通常指向另外一张表的主键
###一对一
什么是一对一关系：有A、B两张表，A表中一条数据对应B表中的一条数据，称为一对一关系。
应用场景：
如何建立关系：在从表中添加外键指向主表的主键
----练习，创建user(id,username,password)和userinfo(uid,nick,age,phone)然后插入一下数据
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(uid int ,nick varchar(10),age int,phone varchar(15));
1.libai admin 李白 28 13838383388
2.liubei admin 刘备 33 13222333322
3.guanyu 123456 关羽 30 13555333355
insert into user values(null,‘libai’,‘admin’),(null,‘liubei’,‘admin’),(null,‘guanyu’,‘123456’);
insert into userinfo values(1,‘李白’,28,‘13838383388’),(2,‘刘备’,33,‘13222333322’),
(3,‘关羽’,30,‘13555333355’);
1.查询每一个用户的用户名，昵称和电话
select u.username,ui.nick,ui.phone
from user u join userinfo ui on u.id=ui.uid;
2.查询李白的用户名和密码
select u.username,u.password
from user u join userinfo ui on u.id=ui.uid
where ui.nick=‘李白’;
3.查询guanyu的昵称
select ui.nick
from user u join userinfo ui on u.id=ui.uid where u.username=‘guanyu’;
###一对多
什么是一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的
一条对应A表中的一条数据。
如何建立关系？
在多的表中添加外键指向另外一张表的主键
###多对多
什么是多对多：AB两张表，A表中的一条数据对应B表中的多条数据，B表中的一条数据
对应A表中的多条数据。
如何建立关系：创建一个关系表，两个外键字段，分别指向另外两张表的主键
####自关联
-当前表的外键指向当前表的主键，这种关系叫做自关联
应用场景：需要保存上下级关系时
####连接方式和关联关系
- 连接方式：包括等值连接，内连接，外连接 是指查询多张表数据时使用的查询方式
关联
-关联关系：包括 一对一 一对多 多对多 是指设计表时，两张表之间存在的逻辑关系
######表设计案例：权限管理
需要创建五张关系表，3张主表 2张关系表
create database db5 character set utf8;
use db5;
create table user(id int primary key auto_increament,name varchar(10));
create table role(id int primary key auto_increament,name varchar(10));
create table module(id int primary key auto_increament,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
插入一下数据 并在表中建立关系
权限：男浏览 男发帖 男删帖 女浏览 女发帖 女删帖
角色:男会员，男管理员，女游客，女会员
用户：刘德华，张学友，凤姐
insert into module(name) values(‘男浏览’),(‘男发帖’),(‘男删帖’),(‘女浏览’),(‘女发帖’),(‘女删帖’);
insert into role(name) values(‘男会员’),(‘男管理员’),(‘女游客’),(‘女会员’);
insert into user(name) values(‘刘德华’),(‘张学友’),(‘凤姐’);
insert into u_r values(1,1),(1,3),(2,2),(‘3,4’),(3,1);
insert into r_m values(1,1),(1,2),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
1.查询每个用户对应的权限有哪些
select u.name,m.name from user u join u_r ur onu.id=ur.id join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id;
2.查询凤姐的权限有哪些
select m.name from user u join u_r ur onu.id=ur.id join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where u.name=‘凤姐’;
3.查询拥有 男发帖权限的游湖有谁？
select u.name,m.name from user u join u_r ur onu.id=ur.id join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where m.name=‘男发帖’;
############视图
什么是视图：数据库中的表和视图都是其内部对象，视图可以理解成一张虚拟的表
视图本质就是取代了一条SQL查询语句。
为什么使用视图：因为有些数据的查询需要大量的SQL语句，每次书写效率太低，使用
视图剋有起到SQL重用的作用，视图可以隐藏敏感信息，
-创建视图格式：
create view 视图名 as 子查询
create table 表名 as 子查询
-创建一个10号部门员工的视图
create view v_emp_10 as (select * from emp where deptno=10);
-从视图中查数据
select * from v_emp_10;
等同于select * from (select * from emp where deptno=10) newtable;
#####视图的分类
1.简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询，可以对视图
中的数据进行增删改查操作
2.复杂视图：和简单视图相反，只能对视图中的数据进行查询操作
创建一个视图
create view v_emp_info as (select avg(sal),max(sal),min(sal) from emp);
查询：select * from v_emp_info;
####对简单视图进行增删改查，操作方式和table一样
1.插入数据
insert into v_emp_10(empno,ename,deptno)values(10011,‘刘备’,10);
select * from v_emp_10;
select * from emp;
-如果插入一条在视图中不可见，但是原表中却可见的数据称为数据污染。
insert into v_emp_10(empno,ename,deptno) values(10012,‘关羽’,20);
select * from v_emp_10;
select * from emp;
-通过with check option 解决数据污染问题
create view v_emp_20 as(select * from emp where deptno=20)
with check option;
insert into v_emp_20(empno,ename,deptno) values(10013,‘赵云’,20);//成功
insert into v_emp_20(empno,ename,deptno) values(10014,‘黄忠’,30);//失败
-修改和删除视图中的数据（只能删除视图中有的数据）
update v_emp_20 set ename=‘赵云2’ where ename=‘赵云’;
update v_emp_20 set ename=‘刘备2’ where ename=‘刘备’;
delete from v_emp_20 where deptno=20;//没有数据被删除
-创建或替换视图
create or replace view v_emp_10 as (select * from emp where deptno=10 and sal>2000);
-删除视图
drop view 视图名；
-如果创建视图的子查询中使用了别名 则对视图操作时只能使用别名
create view v_emp_10 as (select ename name from emp where deptno=10);
####视图总结：
1.视图是数据库中耳朵对象，代表一段SQL语句，可以理解成一张虚拟的表
2.作用：重用SQL，隐藏敏感信息
3.分类：简单视图和复杂视图
4.插入数据时可能出现数据污染，可以通过with check option解决
5.删除和修改只能操作视图中存在的数据
6.起了别名后只能用别名
#####约束
什么是约束：约束就是给表字段添加的限制条件
####主键约束+自增 primary key auto_increment
作用唯一且非空
#####非空约束 not null
作用：该字段的值不能为null
create table t_null(id int ,age int not null);//成功
insert into t_null values(2,null);//失败
####唯一约束 unique
作用：该字段的值不能重复
create table t_unique(id int ,age int unique);
insert into t_unique values(1,28);//成功
insert into t_unique values(1,28);//失败
#####默认约束 default
作用：给字段设置默认值
create table t_default(id int,age int default 20);
insert into t_default(id) values(1);//默认值会生效
insert into t_default values(2,null);//默认值不会生效
insert into t_default values(3,30);//可以赋值其他值
####外键约束
外键：用来建立关系的字段称为外键
外键约束：添加外键约束的字段，值可以为null,可以重复，但是值不能是关联表中不存在的数据
，外键指向的数据不能先删除，外键指向的表不能先删除。
-如何使用外键约束？
use db6;
1.创建部门表
create table dept(id int primary key auto_increment,name varchar(10));
2.创建员工表
create table emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id)
references dept(id));
-格式介绍：constraint 约束名称 foreign key(外键字段名) references 依赖的表名（依赖的字段名）
-测试插入数据
insert into dept values(null,‘神仙’),(null,‘妖怪’)；
insert into emp values(null,‘悟空’,1);
insert into emp values(null,‘赛亚人’,3);//失败
delete from dept where id=1;//失败
-由于添加外键约束后 会影响测试效率，所以工作中很少使用，一般都是通过java代码实现逻辑外键
######索引
什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
为什么使用索引：如果不适用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，
直到找到数据为止，使用索引后会将磁盘块以树的结构保存，查询数据时会大大降低磁盘的访问数量，从而提高查询效率
有索引就一定好吗？
如果表中的数据很少，使用索引反而会降低查询效率
索引是越多越好吗？
索引会占用磁盘空间，只针对常用的查询字段创建索引
-导入数据文件
windows系统：source d:/item_backup.sql;
linux系统：source /home/item_backup.sql;
show tables;查看
select count(*) from item2;
-测试查询耗时
select * from item2 where title=‘100’;
#####如何创建索引
-格式：create index 索引名 on 表名（字段名（字符长度））
create index index_item_title on item2(title);
-测试查询耗时
select * from item2 where title=‘100’;
#####索引分类
-聚集索引：通过主键创建的索引称为聚集索引，聚集索引中保存数据，只要给表添加
主键约束，则会自动创建聚集索引
-非聚集索引：通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据
####如何查看索引
格式：show index from 表名；
show index from item2;
删除索引
-格式：drop index 索引名 on 表名；
drop index index_item_title on item2;
########符合索引
-通过多个字段创建的索引称为复合索引
格式：create index 索引名 on 表名（字段1，字段2）；
create index index_item_title_price on item2(title,price);
#####索引总结
1.索引是用来提高查询效率，类似目录
2.因为索引会占用磁盘空间，所以不是越多越好
3.因为数据量很小的时候使用索引会降低查询效率所以不是有索引就一定好
4.分类：聚集索引和非聚集索引
5.通过多个字段创建的索引称为符合索引
####事务
-数据库中执行统一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败
####事务的ACID特性
ACID是保证数据库事务正确执行的四大基本特性：
1.Atomicty:原子性，最小不可拆分，保证全部成功或全部失败
2.Consistency：一致性，保证事务从一个一直状态到另外一个一致状态
3.Isolation：隔离性，多个事务之间互不影响
4.Durablity：持久性，事务提交之后数据保存到数据库文件中持久生效
事务相关SQL
1.开启事务
begin;
2.回滚
rollback；
3.提交
commit；
4.保存回滚点
savepoint s1;
5.回滚到某个回滚点
rollback to s1;
6.查看自动提交状态
show variables like ‘%autocommit%’;
7.修改自动提交状态
set autocommit=0/1;0代表关闭
####group_concat()
查询每一个部门所有员工的姓名和工资
select deptno,ename,sal from emp group by depeno;
select deptno,concat(ename,’-’,sal) from emp group by depeno;
查询每个部门的员工姓名，要求每个部门只显示一行
select deptno,group_concat(ename,’-’,sal) from emp group by depeno;//显示到一行
###面试题
有个学生成绩表 student (id主键，name姓名，subject学科，score成绩)
保存以下数据：
张三 语文 66，张三 数学 77，张三英语 55，张三体育95
李四语文59 李四 数学 88，李四英语78 ，李四 体育95
王五 语文 75 王五 数学 54，王五 英语 98 ，王五 体育 88
1.查询每个人的平均分 从大到小排序
select name,avg(score) from student group by name order by a desc;
2.查询每个人的名字 科目和成绩 一行显示出来
select name,group_concat (subject,’:’,score) from student group by name;
3.查询每个人的最高分和最低分
select name,max(score),min(score)from student group by name;
4.查询每个人的名字，不及格的科目以及分数，不及格的数量 一行显示
select name,group_concat (subject,’:’,score),count(*) from student where score>60 group by name;
######JDBC
####什么是JDBC
-Java DataBase Connectivity,java数据库连接，JDBC实际是Java中的一套和数据库
交互的API（ａｐｐlication program interface 应用程序编程接口）
#####为什么用JDBC？
因为Java程序员需要连接多种数据库，为了避免每一个数据库都学习一套新的API，sun
公司提出一个JDBC接口，各个数据库厂商根据此接口写实现类，（驱动），这样
只需要掌握JDBC接口中的一套方法就可以访问任何数据库。
如何使用JDBC？
访问maven.aliyun.com
1.在文件源码里面 project内部标签 添加标签，把复制
的内容粘贴到里面，保存。
2.创建Demo01类
public class Demo01 {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接对象
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3366/newdb3", "root","211");
// System.out.println(conn);
//创建执行SQL语句的对象
Statement stat=conn.createStatement();
//执行SQL
String sql="create table jdbct1(id int primary key auro_increment,name varchar(10))";
stat.execute(sql);
//关闭资源
stat.close();
conn.close();
}
}###执行SQL的方法
-execute(sql):此方法可以执行任意SQL，但是推荐执行DDL（create drop alter truncate）,方法的返回值为布尔值，返回值代表是否有结果集
-executeUpdate(sql):此方法执行增删改的SQL，方法返回值为int 类型，代表生效行数
###粘连测试
import org.junit.Test;
public class demo03 {
@Test
public void test01() {
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3", "root", "123");
Statement stat=conn.createStatement();
String sql="insert into jdbct01 values(null,'爸爸')";
stat.executeUpdate(sql);
stat.close();
conn.close();
}
@Test
public void test02() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3", "root", "123");
Statement stat=conn.createStatement();
String sql="update jdbct1 set name='Jerry' where id=1";
stat.executeUpdate(sql);
stat.close();
conn.close();
}
@Test
public void test03() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3", "root", "123");
Statement stat=conn.createStatement();
String sql="delete from jdbct1 wherre id=1";
stat.executeUpdate(sql);
stat.close();
conn.close();
}
}-executeQuery(sql):此方法执行select查询的SQL，方法返回值为ResultSet结果集
public void findAll() throws Exception {
Class.forName(“com.mysql.jdbc.Driver”);
Connection conn=DriverManager.getConnection(“jdbc:mysql://localhost:3306/newdb3”, “root”, “123”);
Statement stat=conn.createStatement();
//执行查询SQL
ResultSet rs= stat.executeQuery(“select * from emp”);
//遍历的到数据
while(rs.next()) {
String name=rs.getString(“ename”);//或者查询的表位置
float salary =rs.getFloat(“sal”);
System.out.println(name+":"+salary);
}
rs.close();
stat.close();
conn.close();
}###ResultSet
-获取数据方式呦两种：
通过查询到的字段名称获取数据 rs.getString(“ename”)
通过查询到的字段位置获取数据 rs.getString(2)
数据库类型和Java类型对比：
MySQL java
int getInt()
varchar getString();
float/double getFloat()/getDouble()
datetime/timestamp getDate()
##实例：
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
//创建属性对象
Properties prop=new Properties();
//得到文件输入流
InputStream ips=DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//把流加载到属性对象中
try {
prop.load(ips);
//声明四个变量 把配置文件中的数据读取到变量中
driver=prop.getProperty("driver");
url=prop.getProperty("url");
username=prop.getProperty("username");
password=prop.getProperty("password");
//把四个变量放到下面两行代码中取代写死的字符串
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
//获取连接的方法
public static Connection getConn() throws Exception {
//注册驱动
Class.forName("driver");
//创建连接对象
Connection conn =DriverManager.getConnection("url","username","password");
return conn;
}
//封装关闭资源的方法
public static void close(Connection conn,Statement stat,ResultSet rs) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
try {
if(stat!=null)
stat.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class Demo04 {
@Test
public void insert() {
Connection conn=null;
Statement stat=null;
ResultSet rs=null;
try {
conn=DBUtils.getConn();
stat=conn.createStatement();
//执行SQL
String sql="insert into jdbct1 values(null,'Tom')";
stat.executeUpdate(sql);
System.out.println("插入成功");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.close(conn, stat, rs);
}
}
@Test
public void delete() {
Connection conn=null;
Statement stat=null;
ResultSet rs=null;
try {
conn=DBUtils.getConn();
stat=conn.createStatement();
//执行SQL
String sql="update jdbct1 set name";
stat.executeUpdate(sql);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.close(conn, stat, rs);
}
}
@Test
public void update() {
Connection conn=null;
Statement stat=null;
ResultSet rs=null;
try {
conn=DBUtils.getConn();
stat=conn.createStatement();
//执行SQL
String sql="insert into jdbct1 values(null,'Tom')";
stat.executeUpdate(sql);
System.out.println("插入成功");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.close(conn, stat, rs);
}
}
@Test
public void select() {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
stat = conn.createStatement();
//执行SQL
rs=stat.executeQuery("select * from emp");
while(rs.next()) {
String name=rs.getString("ename");
System.out.println(name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}建立一个jdbc.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3366/newdb3
username=root
password=123
####数据库连接池
DataBase Connextion Pool:数据库连接池
为什么使用：如果没有数据库连接池，每次业务都需要和数据库建立一次连接，用完之后断开，如果频繁开关连接，浪费资源，使用数据库连接池可以将连接重用，避免资源的浪费。
-如和使用数据库连接池
1.下载jar包：从maven搜索dbcp
2.导入
实例：
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class DBUtils {
private static BasicDataSource dataSource;
private static Connection conn;
static {
//创建属性对象
Properties prop=new Properties();
//得到文件输入流
InputStream ips=DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//把流加载到属性对象中
try {
prop.load(ips);
//声明四个变量 把配置文件中的数据读取到变量中
String driver=prop.getProperty("driver");
String url=prop.getProperty("url");
String username=prop.getProperty("username");
String password=prop.getProperty("password");
//把四个变量放到下面两行代码中取代写死的字符串
//创建数据源对象
dataSource=new BasicDataSource();
//设置数据库连接信息
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
//设置初始连接数量
dataSource.setInitialSize(3);
//设置最大连接数量
dataSource.setMaxActive(5);
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
//获取连接的方法
public static Connection getConn() throws Exception {
//System.out.println(conn);
//返回数据库连接池中的连接
return dataSource.getConnection();
}
//封装关闭资源的方法
public static void close(Connection conn,Statement stat,ResultSet rs) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
try {
if(stat!=null)
stat.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.SQLException;
public class demo02 {
public static void main(String[] args) {
DemoThread t1=new DemoThread();
t1.start();
DemoThread t2=new DemoThread();
t1.start();
DemoThread t3=new DemoThread();
t1.start();
DemoThread t4=new DemoThread();
t1.start();
}
}
class DemoThread extends Thread{
@Override
public void run() {
//获取连接
try {
Connection conn =DBUtils.getConn();
System.out.println("获取连接");
//模拟耗时
Thread.sleep(5000);
//归还链接
conn.close();
System.out.println("归还连接");
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo03 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入名字");
String name=sc.nextLine();
System.out.println("请输入年龄");
int age=sc.nextInt();
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
stat = conn.createStatement();
//执行SQL
String sql="insert into jdbcuser values(null,'"+name+"',"+age+")";
stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo03 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入名字");
String name=sc.nextLine();
System.out.println("请输入年龄");
int age=sc.nextInt();
Connection conn = null;
//预编译的SQL执行对象
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql="insert into jdbcuser values(null,?,?)";
stat = conn.prepareStatement(sql);
//把？替换成真正的数据
stat.setString(1, name);
stat.setInt(2, age);
//执行SQL
stat.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}####Statement
create table t_user(id int primary key auto_increment,username varchar(10),password varchar(10));
insert into t_user values(null,‘libai’,‘admin’),(null,‘liubei’,‘123456’);
-登陆的SQL
select count(*) from t_user where username=xxx and password=xxx;
为什么preparedStatement可以避免SQL注入？
因为预编译的SQL执行对象 在创建stat对象的时候已经将SQL进行编译并且把SQL语句的逻辑固定，不会因为后期替换进去的内容改变SQL语句的逻辑。
好处：代码更直观避免拼接SQL语句
可以避免SQL注入
执行效率略高于statment
－如果SQL语句中存在变量使用preparedStatement,如果没有变量则使用Statement
演示：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class demo05 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名");
String username=sc.nextLine();
System.out.println("请输入密码");
String password=sc.nextLine();
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql="select count(*) from t_user where username=? and password=?";
stat = conn.prepareStatement(sql);
//执行SQL
stat.setString(1,username);
stat.setString(2,password);
rs=stat.executeQuery();
while(rs.next()) {
//得到结果集中唯一的一个数
int count=rs.getInt(1);
if(count==0) {
System.out.println("登陆失败");
}else {
System.out.println("登陆成功");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}####批量操作
-将多次和数据库服务器的数据交互合并成一次，提高执行效率
try {
conn = DBUtils.getConn();
stat = conn.createStatement();
//执行SQL
String sql1=“insert into jdbcuser values(null,‘张三’,20)”;
String sql2=“insert into jdbcuser values(null,‘张三’,20)”;
String sql3=“insert into jdbcuser values(null,‘张三’,20)”;
// stat.executeUpdate(sql1);
stat.addBatch(sql1);
stat.addBatch(sql2);
stat.addBatch(sql3);
//执行
stat.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}name1 101 name2 102 name3 103
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class demo07 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql="insert into jdbcuser values(null,?,?)";
stat = conn.prepareStatement(sql);
//执行SQL
//由于statment插入100条数据时SQL语句编译了100次而PreparedStatement
//只需要编译一次，所以说后者执行效率高
for(int i=1;i<=100;i++) {
sql="insert into jdbcuser values(null,'"+("name"+i)+"',"+(100+i)+")";
stat.executeQuery();
}
stat.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}####事务
-设置自动提交的状态
conn.setAutoCommit(false/true);
-提交
conn.commit();
-回滚
conn.rollback();
####获取自增主键的值
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo11 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
stat = conn.createStatement();
//执行SQL
String sql="insert into jdbcuser values (null,'Tom',18)";
stat.executeUpdate(sql,java.sql.Statement.RETURN_GENERATED_KEYS);
rs= stat.getGeneratedKeys();
while(rs.next()) {
//得到返回值主键值
int id=rs.getInt(1);
System.out.println("主键值："+id);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}###获取元数据
元数据包括：数据库的元数据和表的元数据
1.数据库元数据里面保存一些和数据库相关的信息
2.表的元数据里面保存一些和表相关的信息
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class demo13 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
DatabaseMetaData dbmd=conn.getMetaData();
System.out.println("驱动版本"+dbmd.getDriverMajorVersion());;
stat=conn.createStatement();
rs=stat.executeQuery("select * from emp");
//从结果集中得到表的元数据
ResultSetMetaData rsmd=rs.getMetaData();
//得到表字段的数量
int count=rsmd.getColumnCount();
//得到每个字段的字段名和类型
for (int i = 0; i < count; i++) {
String name=rsmd.getColumnName(i+1);
String type=rsmd.getColumnTypeName(i+1);
System.out.println(name+":"+type);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(conn, stat, rs);
}
}
}
