2阶段-------------------------------------------------------------------------------------数据库
什么是数据库？
简而言之，就是存储数据，管理数据的仓库。
常见的数据库分为：
关系型数据库， Oracle、MySQL、SQLServer、Access 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、Hbase
Mysql数据库1.mysql服务端，它来处理具体数据维护，保存磁盘
2.mysql客户端，CRUD新增，修改，删除，查询
注意：SQL不区分大小写
default character set utf8;---------------设置成utf8格式
增删改查 建库创建数据库，数据库名称：cgb2108
create database cgb2108 default character set utf8;删库
删除名称是cgb2108的数据库
drop database cgb2108;查库
查看所有数据库
show databases;表的常用操作 ：使用库
use cgb2108;创建表
创建student表，有id,name,tel字段
create table student( id int primary key auto_increment, name varchar(100), tel varchar(50) );修改表 ⭕
#添加列 学生表里添加age列
alter table student add column age varchar(10);删除表
#删除名称是student的表
drop table student;查看所有表
show tables;查看表结构/表设计
#查看student表结构
desc student;表记录的常用操作 插入记录
#向student表中插入2条记录
insert into student values(1,'阿名'); insert into student values(2,'阿明');查看记录
#查询student表中的所有记录
select * from student;修改记录
#修改student表中id为1的记录(age修改成2)
update student set age=2 where id=1删除记录
#删除student中id为2的数据
delete from student where id=2排序
#将student表记录按照tel排序
select * from student order by tel #正序 select * from student order by tel desc;#排倒序记录总数
#查询student表中的总记录数
select count(1) from student; select count(*) from student;数据类型 命名规则
-
字段名必须以字母开头，尽量不要使用拼音
-
长度不能超过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个字节
-
注：不同数据库版本长度限制可能会有不同
-
数字:
1.tinyint,int整数类型
2.float,double小数类型
3.numeric(5,2) decimal(5,2)----也可以表示小数，表示总共5位，其中可以有两位小数
4.decimal和numeric表示精确的整数数字
日期
-
date包含年月日
-
time时分秒
-
datetime包含年月日和时分秒
-
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数
图片
blob 二进制数据，可以存放图片、声音，容量4g.早期有这样的设计但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。
添加数据 部门表dept| 字段名称 | 数据类型 | 是否为空 | 备注 |
|---|---|---|---|
| deptno | int | 编号，主键 | |
| dname | varcahr(20) | yes | 部门名 |
| loc | varchar(10) | yes | 部门地点 |
create table dept( deptno int primary key auto_increment, dname varchar(20), loc varchar(10) ); insert into dept values(null,'cgb','一班'); insert into dept values(null,'jsd','二班'); insert into dept values(null,'ccb','三班');员工表emp
| 字段名称 | 数据类型 | 是否为空 | 备注 |
|---|---|---|---|
| empno | int | 员工编号，PK主键 | |
| ename | varchar(10) | Y | 员工名称 |
| job | varchar(10) | Y | 职位 |
| mgr | int | Y | 上级编号 |
| hiredate | datetime | Y | 入职时间 |
| sal | double | 月工资 | |
| comm | NUMERIC(8,2) | Y | 奖金 |
| deptno | int | Y | 所属部门 FK外键 |
create table emp( empno int primary key auto_increment, ename varchar(10), job varchar(10) unique, mgr int, hiredate datetime, sal double not null, comm NUMERIC(8,2), deptno int ); insert into emp values(null,'jack','经理','null','2002-05-01',99888,null,1); insert into emp values(null,'tony','总监',100,'2011-01-01',10000,2000,2); insert into emp values(null,'haha','经理',200,'2012-02-02',8000,1000,2); insert into emp values(null,'lao','员工',300,'2013-03-03',3000,200.12,2); insert into emp values(null,'liu','员工',300,'2014-04-04',3500,200.58,2);字段约束 主键约束
#主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。 #主键自增 当主键为数值类型时，为了方便维护，可以设置主键自增
create table abc( id int primary key auto_increment ); insert into abc values(null); insert into abc values(null); insert into abc values(null); select * from abc;非空约束
#非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复
create table user( id int primary key auto_increment, password varchar(50) not null ); show tables; insert into user values(null,null);#不符合非空约束 insert into user values(null,123;);#OK唯一约束
#唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
create table test( id int primary key auto_increment, username varchar(50) unique--唯一约束 ); show tables; insert into test values(null,'lisi'); insert into test values(null,'lisi'); #username的值要唯一,重复会报错的 select * from test;基础函数 lower
select'ABC',lower('ABC') from dept; #数据转小写
upper
select 'ABC',upper('ABC') from dept; #数据转大写
length
select length(job) 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字符替换成666ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换round & ceil & floor
round四舍五入，ceil向上取整，floor向下取整–直接四舍五入取整
select comm,round(comm) from emp–ceil向上取整,floor向下取整
select comm,ceil(comm) ,floor(comm) from empuuid
SELECt UUID() 返回uuid：a08528ca-741c-11ea-a9a1-005056c00001now
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' --数据中有单引号时,用一个转义变成普通字符条件查询 distinct
#使用distinct关键字，去除重复的记录行 select loc from dept;#查看dept库里的loc表 select distinct loc from dept; #distinct 去重行 去除loc表里重复的行where
#注意：where里不能用别名，不能出现聚合函数，比having高效！ select * from emp select * from emp where id=1--唯一条件 select * from emp where id=1 and age=2 ---相当于两个条件的&关系，同时需要满足 select * from emp where id=1 or age=2 ---相同于两个条件的|关系，不需要同时满足 select name, sal, from emp where sal=1000 or sal=1200 or sal=1400;---查询工资为1000或1200或1400人员的名字 select name,sal from emp where sal in(1000,1200,1400);---同上 select name,sal from emp where sal sal not in(1000,1200,1400);同上相反like
通配符%代表0到n个字符，通配符下划线_代表1个字符 select * from emp where name like '1%'---以1开头的 select * from emp where name like '%a'---以a结尾的 select * from emp where name like '%b%'---中间包含b的 select * from emp where name 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---于between and等效 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条记录---前三条order by
select * from emp order by sal ASC#默认升序 ASC可以省略不写 select * from emp order by sal desc #降序 SELECt * FROM emp ORDER BY ename #按照字母升序顺序 SELECt * FROM emp ORDER BY hiredate #按照数字大小升序排 SELECt * FROM emp ORDER BY job #了解::汉字排序时会查utf8里对应的数字,按照数字升序排序统计案例 入职统计 #2015年以前入职的员工
select * from emp where year(hiredate)<2015 select * from emp where hiredate<'2019-1-1'#比较的是数字大小不是日期#2019年以后签约的员工，日期进行格式转换后方便比较
select * from emp where year(date_format(hiredate,'%Y-%m-%d'))<=2019;#2015年到2019年入职的员工
select * from emp where str_to_date(hiredate,'%Y-%m-%d')<='2015-01-01' and str_to_date(hiredate,'%Y-%m-%d')<='2019-12-31'年薪统计 #公司福利不错13薪，年底双薪，统计员工的年薪=sal13+comm13
select empno,ename,job,sal*13+comm*13 from emp; select empno,ename,job,sal*13+comm*13 as 别名 from emp; 用as给列起个别名 select empno,ename,job,sal*13+comm*13 别名 from emp; as也可以省略 select ename,sal+comm from emp select ename,sal,comm,sal+ifnull(comm,0)from emp ---如果comm为null,用0替换掉聚合函数 aggregation count
#根据列统计结果 count()统计表里有多少条记录
select count(*) from emp ---底层优化了 #总记录数 selcet count(1) from emp ---效果和*一样 selcet count(comm) from emp ---慢，不统计null元素max/min
select max(sal) from emp #查询最高工资 select min(sal) from emp #查询最低工资 select max(sal) max,min(sal) min from emp #查询最高工资和最低工资 select ename,max(sal) from emp group by ename #分组sum/avg
select sum(sal) from emp #求和 select avg(sal) from emp #平均数 SELECt SUM(sal),AVG(sal) FROM emp #SUM查询工资总和,AVG平均工资分组group
分组：用于对查询的结果进行分组统计
: 就是把数据,按照一些维度分成组,然后再把这一组数据继续分析
group by 表示分组，having 子句类似where过滤返回的结果
#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对 #1,分组:group by,把数据按照维度分组,后,数据分析 #口诀: #什么时候要分组?查询时出现了混合列 (包含聚合列和非聚合列时) #按照什么分组?按照非聚合列分组 #练习1:统计每个岗位的平均薪资 select job ,avg(sal) from emp group by job #练习2:统计每个部门的员工人数 select deptno,count(1) from emp group deptno #练习3:统计每年入职的人数 select year(hiredate),count(1) from emp group by year(hiredate) #练习4:统计每个部门的最高薪 select deptno,max(sal) from emp group by deptno
#2.分组后过滤having #练习1:统计每个部门的最高薪,只要>10000的记录 select deptno,max(sal) from emp group by deptno #按照非聚合列分组 having max(sal)>1000 #group by后的过滤必须用having #练习2:统计每年入职的人数,只要人数>1的记录 select count(1),year(hiredate) from emp #where COUNT(1)>1 报错 #where year(hiredate)>2019 可以使用 #where 里不能用别名，不能出现聚合函数，比having高效 group by year(hiredate) #按照非聚合列分组 having count(1)>1 # 按照人数过滤
事务
什么是事务：数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。
事务的四大特性：
1.原子性: 把多条SQL，看做是一个原子，密不可分，要不全成功，要不全失败 2.隔离性：数据库为了保证性能也支持高并发，但是有安全隐患。保证多个操作之间 3.持久性：对数据库的操作是持久生效的 4.一致性：保证数据在多个系统是一致的
隔离级别
：从上至下，安全性越来越好，但是性能越来越差
1.读未提交：安全性最差，可能会发生并发数据问题，性能最好
2.读已提交：Oracle默认的隔离级别
3.可重复读：Mysql默认的隔离级别，安全性较号，性能一般
4.串行化：表级锁，读写都加锁，效率底下哎，安全性高，不能并发
查询mysql的隔离级别
在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。
开启事务：star transaction;
结束事务：commit（提交事务） 或 rollback（回滚事物）
commit(提交事务)BEGIN; #关闭事务的自动提交,相当于start transaction INSERT INTO user (id) VALUES(25);#成功 INSERT INTO user (id) VALUES(5);#已经存在5了，会失败 COMMIT; #手动提交事务rollback（回滚事务）
BEGIN; INSERT INTO user (id) VALUES(15); INSERT INTO user (id) VALUES(35);#存在了 ROLLBACK;#事务回滚，就不会再提交了表强化：6约束（constraints） 1.非空约束 not null
create table b( id int primary key auto_increment, name varchar(10)not null )2.唯一约束 unique
create table a( id int primary key auto_increment, name varchar(10)unique )3.主键约束 primary key
create table e( id int primary key auto_increment,#主键唯一不能为空 添加自增 name varchar(10) )4.外键约束 foreign key
#外键约束：防止了冗余的数据，通过外键来描述两张表的关系
#特点是：当子表中添加数据时，子表的主键值 必须 取自主表！！！
#当主表删除数据时，子表没有相关的记录
create table tb_user(
id int primary key auto_increment,
name varchar(10),
password varchar(10)
)
create table tb_user_addr(
user_id int primary key,
address varchar(100),
#描述和tb_user表的关系，外键
#语法：foreign key(本表的主键名) references 对方表名(对方表的主键)
foreign key(user_id) references tb_user(id)#创建外键
)
5.检查约束 check
create table f( id int primary key auto_increment, age int, check(age<100)#检查约束，age录入不合法数据时无法保存 )6.默认约束 default
create table e( id int primary key auto_increment, sex varchar(10) default '男' #默认约束,设置默认值 )索引 概述：
为了提高查询效率. 索引会单独生成一张表,要合理的使用索引.定义
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。 一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。分类： 1.单值索引
:一个索引只包含一个字段,也就是一个列，一个表可以包含多个列
2. 唯一索引:一个索引只包含一个字段，但字段不能重复
3.复合索引:一个索引可以包含多个字段
使用步骤：创建索引（经常按照指定字段查询）+使用索引
查看索引#主键会自动创建索引
show index from 表名创建单值索引
#create index 索引名字 on 表名(字段名)
create index en_index on emp(ename)创建唯一索引
#唯一索引 索引列的值必须唯一 #create unique index 索引名 on 表名(字段名)
create unique index mc on emp(loc)创建复合索引
#复合索引 索引列的名字在括号中用逗号隔开 #create index 索引名 on 表名(字段名，字段名)
create index a on emp(sal,age)删除索引
#alter table 表名 drop index 索引名
alter table emp drop index mc最左特性
create index ace on emp(sal,age,job) #复合索引创建等于创建了(sal)(sal,age) (sal,age,job)这三个索引，这是最左匹配原则，也称最左特性 explain select * from emp where sal=100 #生效 explain select * from emp where sal=100 and age=10 #生效 explain select * from emp where sal=100 and job='开发' #失效 explain select * from emp where sal=100 and age=10 and job='开发' #生效索引扫描类型
ALL 全表扫描，没有优化，最慢的方式 index 索引全扫描，其次慢的方式 range 索引范围扫描，常用语<，<=，>=，between等操作 ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中 eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询 const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况 null MySQL不访问任何表或索引，直接返回结果为何索引快？
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。
排序，tree结构，类似二分查找
索引表小
优点、缺点
优点: 1. 索引是数据库优化 2. 表的主键会默认自动创建索引 3. 每个字段都可以被索引 4. 大量降低数据库的IO磁盘读写成本，极大提高了检索速度 5. 索引事先对数据进行了排序，大大提高了查询效率 缺点： 1. 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间 2. 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的” 3. 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件 4. 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引视图
概述：
和索引一样，都是对数据库优化的有效方案
特点：
1.可以把视图当做表来使用 2.视图里存的数据是sql查询到的结果 3.sql无法优化，要合理使用视图
#测试 #视图：缓存了sql语句的执行结果,当做表来用 #好处是：1.简化了查询的sql(相同的sql需求不必再写sql了，直接查视图) # 2.视图可以被共享，视图屏蔽了真实业务表的复杂性 #坏处是:视图一旦创建,sql无法被优化 #创建视图 语法： create view 视图名 as 查询的sql语句
create view name_view as like'%1' select * from name_view
表关联 association
概念表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。 同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关表的关系分为四种
一对一 one to one QQ和QQ邮箱 ， 员工和员工编号
一对多 one to many 最常见，部门和员工，用户和订单
多对一 many to one 一对多反过来，员工和部门，订单和用户
多对多 many to many 老师和学生，老师和课程
多表联查join多表查询是指两个和两个以上的比表的查询，在实际用时，查询单个表可能不能满足你的需求， 如显示员工表emp中不只显示deptno 还要显示部门名称， 而部门名称dname在dept表中笛卡尔积
selec * from dept,emp #上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。 #这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。 #这就是阿里规范中禁止3张表以上的联查的原因：三种链接join 内连接 inner join
inner join 两边都对应有记录的才展示，其他的去掉左(外)连接 left join
left join 左边表中的数据都出现，右边没有数据以null填充右(外)连接 right join
right join 右边表中的数据都出现，左边没有数据以null填充案例：列出tony的部门信息
select * from emp inner join dept on emp.empno = dept.empno where emp.ename='tony'
#换成left join 和 right join 有不同
案例：列出adc部门下的所有员工的信息
select * from where #嵌套查询 deptno=(select deptno from dept where dname='adc')
案例：怎么用内链接 INNER JOIN 实现上面的需求？
#select emp.* from emp inner join dept select emp.ename,emp.job,dept.dname from emp inner join dept on emp.deptno=dept.deptno where dept.dname='adc'
#换成left join 和 right join 有不同子查询subquery
概念： 子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。单行子查询
#列出tony所在部门的所有人员
select deptno from emp where ename='tony' select * from emp where deptno= ~ 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)
小节练习
#练习1:查询部门编号是1的员工姓名
#方式一：嵌套查询
SELECt deptno FROM dept WHERe empno=1 SELECt ename FROM emp WHERe deptno=1 SELECt ename FROM emp WHERe deptno=(SELECt deptno FROM dept WHERe dname='炸弹部')
#方式二：笛卡儿积查询
SELECt emp.ename FROM emp,dept WHERe dept.deptno=emp.deptno AND dept.deptno=1
#方式三：连接查询
SELECt emp.ename FROM emp JOIN dept ON dept.deptno=emp.deptno WHERe dept.deptno=1#练习2 查询员工姓名叫jack的部门信息 #方法一：嵌套查询
SELECt deptno FROM emp WHERe ename='jack' SELECt * FROM dept WHERe deptno= 1 SELECt * FROM dept WHERe deptno=(SELECt deptno FROM emp WHERe ename='jack')#方法二：笛卡儿积查询
SELECt * FROM emp,dept WHERe emp.deptno=dept.deptno AND emp.ename='jack'#方法三：连接查询
SELECt * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERe emp.ename='jack'#练习3:查询岗位是总监所在的部门信息 #方法一：嵌套查询
SELECt deptno FROM emp WHERe job='总监' SELECt dname FROM dept WHERe deptno=2 SELECt * FROM dept WHERe deptno=(SELECt deptno FROM emp WHERe job='总监')#方法二：笛卡儿积查询
SELECt * FROM emp,dept WHERe emp.deptno=dept.deptno AND emp.job='总监'#方式三：连接查询
SELECt * FROM emp JOIN dept WHERe emp.deptno=dept.deptno AND emp.job='总监'#练习4:列出java开发部 部门下的所有员工的信息 #方式一：嵌套查询
SELECt deptno FROM dept WHERe dname='开发部' SELECt * FROM emp WHERe deptno=5 SELECt * FROM emp WHERe deptno=(SELECt deptno FROM dept WHERe dname='开发部')#方式二：笛卡儿积查询
SELECt * FROM emp,dept WHERe emp.deptno=dept.deptno AND dept.dname='开发部'#方式三：连接查询
SELECt * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERe dept.dname='开发部'#练习5:查询部门地址在北京和广州的员工信息 #方式一：嵌套查询
SELECt deptno FROM dept WHERe loc='北京'OR loc='广州'
SELECt * FROM emp WHERe deptno=1 OR deptno=3
SELECt * FROM emp WHERe
#deptno in(SELECt deptno FROM dept WHERe loc BETWEEN '北京'AND '广州')
#deptno=any(SELECt deptno FROM dept WHERe loc='北京'or loc='广州' )
#deptno in (SELECt deptno FROM dept WHERe loc='北京'or loc='广州' )
#deptno in (SELECt deptno FROM dept WHERe loc in('北京','广州' ))
deptno=(SELECt deptno FROM dept WHERe loc='北京' )
OR
deptno=(SELECt deptno FROM dept WHERe loc='广州')
#方式二：笛卡儿积查询
SELECt emp.* FROM emp,dept WHERe emp.deptno=dept.deptno AND dept.loc BETWEEN '北京' AND '广州'#方式三：连接查询
SELECt emp.* FROM emp JOIN dept ON emp.deptno=dept.deptno WHERe dept.loc BETWEEN '北京' AND '广州'
SQL面试题
#SQL面试题
#1.报错，emp表中没有id字段
select * from emp where id=100;
#2.模糊查询，最好指定开始元素,否则索引失效
show index from emp; #查看表里的索引，确定了ename是有索引的
explain #查看了possible_keys是有值的就是索引生效了
select * from emp where ename='jack' #索引生效
explain select * from emp where ename like ='j%' #生效
explain select * from emp where ename like ='%j' #失效
explain select * from emp where ename like ='%j%' #失效
#3.查询员工编号[100,300]范围内的数据
select * from dept where deptno between 100 and 300
#4.综合排序 order by
#按照名字降序，按照sal升序,默认就是升序 ASC 可以省略不写
select * from emp order by ename desc ,sal asc
select * from emp order by sal
#5.分组:查询学生表，每个班级的总人数,只要95033班的数据
#当查询结果中出现了聚合列(用了聚合函数) 和非聚合列，必须分组
select count(*),class from students
group by class #按照非聚合列分组
having class=95033
#5.1比上面的一句SQL要高效
select count(*),class from students
where class=95033 #分组前的过滤，使用where(不能用别名,聚合函数)
group by class=95033
#6.子查询：查询>平均工资的员工信息
select * from emp where sal>(select avg(sal)from emp)
#6.1查询最高薪的员工姓名
select ename from emp where sal>(select avg(sal)from emp)
JDBC
概述
简称，全称是：java database connectivity, 专门用来完成java程序和数据库的连接的技术
使用步骤
1.导入jar包(使用JDBC提供了丰富的工具类)
2.提供连接数据库的参数(用户名root 密码root 端口号3307)
3.在java程序中，发起SQL语句操作数据库
4.如果数据库有查到的结果，返回给java程序
入门案例
1.创建project:file-NEW-Project-选择java-next-next-输入工程名称-Finish
2.导入jar包:
找到磁盘里的mysql-connector-java-5.1.32.jar复制，粘贴到Project里
在IDEA里，选中jar包,右键编译(add as library...),ok
检查是否编译成功：看到IDEA里的jar包可以被点卡了
//1.注册驱动
Class.froname("com.mysql.jdbc.Driver");
//2.获取数据库的连接
String url="jdbc:mysql://localhost:3307/test";
Connection c= DriverManager.getConnection(url,"root","root");
//3.获取传输器
Statement s=c.createStatement();
//4.执行SQL
String sql="select * from dept"
ResultSet r=s.executeQuery(sql);
//5.解析结界集
while(r.next()){
int a=r.getInt(1);
String b = r.getString(2);
int c1=r.getInt(3);
System.out.println(a+","+b+","+c1);
}
//6.释放资源
r.close();
s.close();
c.close();
创建工具类
//提供丰富的方法,方便的jdbc操作
public class JDBCUtils {
//1,获取数据库的连接(注册驱动+获取连接)
static public Connection getConnection() throws Exception{
//1,注册驱动
Class.forName("com.mysql.jdbc.Driver");//全路径
//2,获取数据库的连接(用户名/密码)
//jdbc连接mysql数据库的协议//本机:端口号/数据库的名字 解决中文乱码 指定时区 关闭权限检验
String url="jdbc:mysql://localhost:3306/cgb2108?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false" ;
Connection c = DriverManager.getConnection(
url,"root","root");
return c ;//返回给调用者
}
}
模拟用户
登录
//需求:利用JDBC,查询tb_user表里的数据
public static void main(String[] args) throws Exception {
// method();//查询tb_user表里的数据
method2();//解决SQL攻击问题
}//解决SQL攻击问题
private static void method2() throws Exception {
//1,注册驱动 2,获取连接
Connection c = JDBCUtils.getConnection();
//3,执行SQL
String a = new Scanner(System.in).nextLine();//用户名
String b = new Scanner(System.in).nextLine();//密码
//如果动态的拼接字符串时,数据在中间的位置 "+a+"
// String sql="select * from tb_user where name='jack' and password='321'" ;
// String sql="select * from tb_user where name='"+a+"' and password='"+b+"'" ;
//SQL骨架:用?代替了参数的位置,?叫占位符,好处:简洁(避免了SQL拼接参数)
String sql="select * from tb_user where name=? and password=?" ;
//4,获取传输器
// Statement s = c.createStatement();
PreparedStatement s = c.prepareStatement(sql);
//设置SQL参数--setXxx()设置不同类型的参数
s.setString(1,a);//?的索引,要给?设置的值
s.setString(2,b);//?的索引,要给?设置的值
//TODO 当用户名输入jack'#时还会发生SQL攻击吗???
ResultSet r = s.executeQuery();
//5,解析结果集
if(r.next()){//查到数据了吗?查到了就登录成功
System.out.println("登录成功~");
}else{
System.out.println("用户名或者密码输入错误,登录失败~");
}
//6,关闭资源
r.close();
s.close();
c.close();
}
模拟用户登录
1,发起SQL:select * from tb_user where name='jack' and password='321'
2,判断result,如果有结果就登录成功,没结果就登录失败
本质上就是因为SQL语句中出现了特殊符号(#,注释掉了一些条件),导致了SQL语义改变了
解决方案:Statement低级的传输器,不安全,低效
换成PreparedStatement高级,安全
private static void method() throws Exception {
//1,注册驱动 2,获取连接
Connection c = JDBCUtils.getConnection();
//3,获取传输器
Statement s = c.createStatement();
//4,执行SQL
String a = new Scanner(System.in).nextLine();//用户名
String b = new Scanner(System.in).nextLine();//密码
//如果动态的拼接字符串时,数据在中间的位置 "+a+"
// String sql="select * from tb_user where name='jack' and password='321'" ;
String sql="select * from tb_user where name='"+a+"' and password='"+b+"'";
ResultSet r = s.executeQuery(sql);
//5,解析结果集
if(r.next()){//查到数据了吗?查到了就登录成功
System.out.println("登录成功~");
}else{
System.out.println("用户名或者密码输入错误,登录失败~");
}
//6,关闭资源
r.close();
s.close();
c.close();
}
JDBC的练习
//练习按条件对数据的查询 与 插入
public static void main(String[] args) throws Exception{
method();//查询部门表的<100数据
method2();//向dept表里插入数据
}
//向dept表里插入数据
private static void method2() throws Exception {
Connection c = JDBCUtils.getConnection();
//插入数据时怎么决定要几个问号? 要看表里有几个字段需要设置值
String sql = "insert into dept values(?,?,?)" ;
PreparedStatement p = c.prepareStatement(sql);
//设置SQL的参数
p.setObject(1,666);
p.setObject(2,"软件测试部");
p.setObject(3,"大山西");
//执行SQL
int rows=p.executeUpdate();//执行增删改的SQL
System.out.println("影响的行数："+rows);
p.close();
c.close();
//TODO 会返回结果集吗?返回了的是啥?
}
//查询部门表的<100数据
private static void method() throws Exception{
Connection c = JDBCUtils.getConnection();//利用工具类,获取数据库的连接
//获取传输器,执行SQL骨架
String sql = "select * from dept where deptno < ?";
PreparedStatement s = c.prepareStatement(sql);
//设置SQL的参数
s.setInt(1,100);//给第一个?设置100
ResultSet r = s.executeQuery();//执行查询的SQL语句
//处理结果集
while(r.next()){//next()判断有数据吗
//获取数据getXxx()--获取表里的dname字段的值,并打印
String str = r.getString("dname");
System.out.println(str);
}
//关闭资源
r.close();
s.close();
c.close();
}
修改释放资源的代码
//向dept表里插入数据
//为了资源一定会被释放?
// 把释放资源的代码放入finally里+扩大变量的作用范围
// +在try里修改变量的默认值null+在finally里进行try catch
private static void method() {
Connection c =null;
PreparedStatement s =null;
ResultSet r =null;
try{
c = JDBCUtils.getConnection();//利用工具类,获取数据库的连接
//获取传输器,执行SQL骨架
String sql = "select * from dept where deptno < ?";
s = c.prepareStatement(sql);
//设置SQL的参数
s.setInt(1,100);//给第一个?设置100
r = s.executeQuery();//执行查询的SQL语句
//处理结果集
while(r.next()){//next()判断有数据吗
//获取数据getXxx()--获取表里的dname字段的值,并打印
String str = r.getString("dname");
System.out.println(str);
}
}catch (Exception e){
//项目上线阶段,给出的解决方案,比如输出
System.out.println("数据库连接出错~~");
//项目开发调试阶段,给出的解决方案,根据报错信息
e.printStackTrace();
}finally {
//关闭资源
try {
r.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
s.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}} }
.
