Day05
1、表关系
一对一：如用户和身份证
一对多（多对一）：
一个班级---->多个学生而言是一对多
多个学生---->一个班级而言是多对一
多对多（需要借助中间表）
如学生与教师
create table student(
s_id int primary key auto_increment,
s_name varchar(64),
age int
);
create table middle(
m_id int primary key auto_increment,
s_id int,
t_id
);
create table teacher(
t_id int primary key auto_increment,
t_name varchar(64),
age int
);
多对多
题目：查询指定老师教的学生
select s.s_id,s.s_name,s.age from teacher t , student s , middle m where t .t_id=m.t_id and m.s_id = s.s_id and t.t_name = '老王';
讲解：借助中间表找到学生。
2、mysql练习
为管理业务培训信息，建立3个表：
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩
1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
答案：A：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)
B：select s.s#,s.sn from S s,C c,SC sc where c.CN='税收基础' and c.C# = sc.C# and sc.S# = s.S#
2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’
3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)
4)查询选修了课程的学员人数
答：select 学员人数=count(distinct s#) from sc
5) 查询选修课程超过5门的学员学号和所属单位?
答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)
查询表A中存在ID重复三次以上的记录，查询语句请写出来？
select * from table T where T.ID in((select ID from table group by ID having count(ID)>3))
用一条SQL语句 查询出每门课都大于80分的学生姓名
name chinese score
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
准备数据的sql代码：
create table score(id int primary key auto_increment,name varchar(20),subject varchar(20),score int);
insert into score values (null,'张三','语文',81),(null,'张三','数学',75),(null,'李四','语文',76),(null,'李四','数学',90),
(null,'王五','语文',81),(null,'王五','数学',100),(null,'王五 ','英语',90);
答案：
A: select distinct name from score where name not in (select distinct name from score where score<=80)
B:select distince name t1 from score where 80< all (select score from score where name=t1);
所有部门之间的比赛组合
一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
答：select a.name, b.name from team a, team b where a.name < b.name
每个月份的发生额都比101科目多的科目
请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB
准备数据的sql代码：
drop table if exists TestDB;
create table TestDB(id int primary key auto_increment,AccID varchar(20), Occmonth date, DebitOccur bigint);
insert into TestDB values (null,'101','1988-1-1',100),(null,'101','1988-2-1',110),(null,'101','1988-3-1',120),
(null,'101','1988-4-1',100),(null,'101','1988-5-1',100),(null,'101','1988-6-1',100),(null,'101','1988-7-1',100),
(null,'101','1988-8-1',100);
--复制上面的数据，故意把第一个月份的发生额数字改小一点
insert into TestDB values (null,'102','1988-1-1',90),(null,'102','1988-2-1',110),(null,'102','1988-3-1',120),
(null,'102','1988-4-1',100),(null,'102','1988-5-1',100),(null,'102','1988-6-1',100),(null,'102','1988-7-1',100),
(null,'102','1988-8-1',100);
--复制最上面的数据，故意把所有发生额数字改大一点
insert into TestDB values (null,'103','1988-1-1',150),(null,'103','1988-2-1',160),(null,'103','1988-3-1',180),
(null,'103','1988-4-1',120),(null,'103','1988-5-1',120),(null,'103','1988-6-1',120),(null,'103','1988-7-1',120),
(null,'103','1988-8-1',120);
--复制最上面的数据，故意把所有发生额数字改大一点
insert into TestDB values (null,'104','1988-1-1',130),(null,'104','1988-2-1',130),(null,'104','1988-3-1',140),
(null,'104','1988-4-1',150),(null,'104','1988-5-1',160),(null,'104','1988-6-1',170),(null,'104','1988-7-1',180),
(null,'104','1988-8-1',140);
--复制最上面的数据，故意把第二个月份的发生额数字改小一点
insert into TestDB values (null,'105','1988-1-1',100),(null,'105','1988-2-1',80),(null,'105','1988-3-1',120),
(null,'105','1988-4-1',100),(null,'105','1988-5-1',100),(null,'105','1988-6-1',100),(null,'105','1988-7-1',100),
(null,'105','1988-8-1',100);
答案：
select distinct AccID from TestDB where AccID not in (select TestDB.AccIDfrom TestDB,(select * from TestDB where AccID='101') as db101 where TestDB.Occmonth=db101.Occmonth and TestDB.DebitOccur<=db101.DebitOccur);
统计每年每月的信息
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
提示：这个与工资条非常类似，与学生的科目成绩也很相似。
准备sql语句：
drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));
insert into sales values(null,'1991','1',1.1),(null,'1991','2',1.2),(null,'1991','3',1.3),(null,'1991','4',1.4),(null,'1992','1',2.1),
(null,'1992','2',2.2),(null,'1992','3',2.3),(null,'1992','4',2.4);
答案一、
select sales.year ,
(select t.amount from sales t where t.month='1' and t.year= sales.year) '1',
(select t.amount from sales t where t.month='1' and t.year= sales.year) '2',
(select t.amount from sales t where t.month='1' and t.year= sales.year) '3',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '4'
from sales group by year;
显示文章标题，发帖人、最后回复时间
表：id,title,postuser,postdate,parentid
准备sql语句：
drop table if exists articles;
create table articles(id int auto_increment primary key,title varchar(50), postuser varchar(10), postdate datetime,parentid int references articles(id));
insert into articles values(null,'第一条','张三','1998-10-10 12:32:32',null),(null,'第二条','张三','1998-10-10 12:34:32',null),
(null,'第一条回复1','李四','1998-10-10 12:35:32',1),(null,'第二条回复1','李四','1998-10-10 12:36:32',2),
(null,'第一条回复2','王五','1998-10-10 12:37:32',1),(null,'第一条回复3','李四','1998-10-10 12:38:32',1),
(null,'第二条回复2','李四','1998-10-10 12:39:32',2),(null,'第一条回复4','王五','1998-10-10 12:39:40',1);
答案：
select a.title,a.postuser,(select max(postdate) from articles where parentid=a.id) reply
from articles a where a.parentid is null;
航空网的几个航班查询题：
表结构如下：
flight{flightID,StartCityID ,endCityID,StartTime}
city{cityID, CityName)
实验环境：
create table city(cityID int auto_increment primary key,cityName varchar(20));
create table flight (flightID int auto_increment primary key,
StartCityID int references city(cityID),
endCityID int references city(cityID),
StartTime timestamp);
//航班本来应该没有日期部分才好，但是下面的题目当中涉及到了日期
insert into city values(null,'北京'),(null,'上海'),(null,'广州');
insert into flight values(null,1,2,'9:37:23'),(null,1,3,'9:37:23'),(null,1,2,'10:37:23'),(null,2,3,'10:37:23');
1)查询起飞城市是北京的所有航班，按到达城市的名字排序
参与运算的列是我起码能够显示出来的那些列，但最终我不一定把它们显示出来。各个表组合出来的中间结果字段中必须包含所有运算的字段。
select * from flight f,city c where f.endcityid = c.cityid and startcityid = (select c1.cityid from city c1 where c1.cityname = "北京") order by c.cityname asc;
select flight.flightid,'北京' startcity, e.cityname from flight,city e where flight.endcityid=e.cityid and flight.startcityid=(select cityid from city where cityname='北京');
select flight.flightid,s.cityname,e.cityname from flight,city s,city e where flight.startcityid=s.cityid and s.cityname='北京' and flight.endCityId=e.cityID order by e.cityName desc;
2)查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
select c1.CityName,c2.CityName,f.StartTime,f.flightID from city c1,city c2,flight fwhere f.StartCityID=c1.cityID
and f.endCityID=c2.cityID and c1.cityName='北京' and c2.cityName='上海'
3)查询具体某一天（2005-5-8）的北京到上海的的航班次数
select count(*) from (select c1.CityName,c2.CityName,f.StartTime,f.flightID from city c1,city c2,flight f
where f.StartCityID=c1.cityID and f.endCityID=c2.cityID and c1.cityName='北京' and c2.cityName='上海'
and 查帮助获得的某个日期处理函数(startTime) like '2005-5-8%'
mysql中提取日期部分进行比较的示例代码如下：
select * from flight where date_format(starttime,'%Y-%m-%d')='1998-01-02'
Drop table if not exists employees;
create table employees(id int primary key auto_increment,name varchar(50)
,salary int,managerid int references employees(id));
insert into employees values (null,' lhm',10000,null), (null,' zxx',15000,1
),(null,'flx',9000,1),(null,'tg',10000,2),(null,'wzg',10000,3);
Wzg大于flx,lhm大于zxx
解题思路：
根据sql语句的查询特点，是逐行进行运算，不可能两行同时参与运算。
涉及了员工薪水和经理薪水，所有，一行记录要同时包含两个薪水，所有想到要把这个表自关联组合一下。
首先要组合出一个包含有各个员工及该员工的经理信息的长记录，譬如，左半部分是员工，右半部分是经理。而迪卡尔积会组合出很多垃圾信息，先去除这些垃圾信息。
select e.* from employees e,employees m where e.managerid=m.id and e.salary>m.salary;
求出小于45岁的各个老师所带的大于12岁的学生人数
数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果
1)显示的字段要有老师name, age 每个老师所带的学生人数
2)只列出老师age为40以下，学生age为12以上的记录
预备知识：
1)sql语句是对每一条记录依次处理，条件为真则执行动作（select,insert,delete,update）
2)只要是迪卡尔积，就会产生“垃圾”信息，所以，只要迪卡尔积了，我们首先就要想到清除“垃圾”信息
实验准备：
drop table if exists tea_stu;
drop table if exists teacher;
drop table if exists student;
create table teacher(teaID int primary key,name varchar(50),age int);
create table student(stuID int primary key,name varchar(50),age int);
create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));
insert into teacher values(1,'zxx',45), (2,'lhm',25) , (3,'wzg',26) , (4,'tg',27);
insert into student values(1,'wy',11), (2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);
insert into tea_stu values(1,1), (1,2), (1,3);
insert into tea_stu values(2,2), (2,3), (2,4);
insert into tea_stu values(3,3), (3,4), (3,1);
insert into tea_stu values(4,4), (4,1), (4,2) , (4,3);
结果：2 3,3 2,4 3
解题思路：（真实面试答题时，也要写出每个分析步骤，如果纸张不够，就找别人要）
1)要会统计分组信息，统计信息放在中间表中：
select teaid,count(*) from tea_stu group by teaid;
2)接着其实应该是筛除掉小于12岁的学生，然后再进行统计，中间表必须与student关联才能得到12岁以下学生和把该学生记录从中间表中剔除，代码是：
select tea_stu.teaid,count(*) total from student,tea_stu where student.stuid=tea_stu.stuid and student.age>12 group by tea_stu.teaid
3)接着把上面的结果做成虚表与teacher进行关联，并筛除大于45的老师
select teacher.teaid,teacher.name,total from teacher ,(select tea_stu.teaid,count(*) total from student,tea_stu where student.stuid=tea_stu.stuid and student.age>12 group by tea_stu.teaid) as tea_stu2 where teacher.teaid=tea_stu2.teaid and teacher.age<45;
求出发帖最多的人：
select authorid,count(*) total from articles group by authorid having total=
(select max(total2) from (select count(*) total2 from articles group by authorid) as t);
select t.authorid,max(t.total) from（select authorid,count(*) total from articles ）as t
这条语句不行，因为max只有一列，不能与其他列混淆。
select authorid,count(*) total from articles group by authorid having total=max(total)也不行。
一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?
alter table drop column score;
alter table add colunm score int;
可能会很快，但是需要试验，试验不能拿真实的环境来操刀，并且要注意，
这样的操作时无法回滚的，在我的印象中，只有insert update delete等DML语句才能回滚，
对于create table,drop table ,alter table等DDL语句是不能回滚。
解决方案一，update user set score=0;
解决方案二，假设上面的代码要执行好长时间，超出我们的容忍范围，那我就alter table user drop column score;alter table user add column score int。
下面代码实现每年的那个凌晨时刻进行清零。
Runnable runnable =
new Runnable(){
public void run(){
clearDb();
schedule(this,new Date(new Date().getYear()+1,0,0));
}
};
schedule(runnable,
new Date(new Date().getYear()+1,0,1));
一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。
select count(*) as num,tb.id from tb,(select role from tb where id=xxx) as t1 where tb.role = t1.role and tb.id != t1.id
group by tb.id having num = select count(role) from tb where id=xxx;
xxx公司的sql面试
Table EMPLOYEES Structure:
EMPLOYEE_ID NUMBER Primary Key,
FIRST_NAME VARCHAR2(25),
LAST_NAME VARCHAR2(25),
Salary number(8,2),
HiredDate DATE,
Departmentid number(2)
Table Departments Structure:
Departmentid number(2) Primary Key,
DepartmentName VARCHAR2(25).
1）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。
select * from employees
where Year(hiredDate) = Year(date())
or (salary between 1000 and 200)
or left(last_name,3)='abc';
2) 基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。
mysql> select id,name,salary,deptid did from employee1 where (select avg(salary)
from employee1 where deptid = did) > 1800;
3) 基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。
select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary
from employee1,
(select deptid,avg(salary) avgSalary from employee1 group by deptid) as t
where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;
3、jdbc
实现步骤：
1、将mysql-connector的jar包引入
2、加载驱动
3、建立连接
4、操作数据库
5、关闭
记得使用完之后要将资源关闭。
引发的问题：sql注入
当你使用Statement时，会出现sql注入的风险，如下图：
这样也能将数据查询出来，因为后面的'1' = '1'的结果为true，所以能够查询到数据。
解决方案：使用PreparedStatement就可以解决.
PreparedStatement之所以能够防止sql注入,是因为SQL语句在程序运行前已经进行了预编译，在程序运行时第一次操作数据库之前，SQL语句已经被数据库分析，编译和优化，对应的执行计划也会缓存下来并允许数据库已参数化的形式进行查询，当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1'也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令，如此，就起到了SQL注入的作用了！
Statement与PreparedStatement的对比：
PreparedStatement 继承Statement
PreparedStatement的性能比Statement性能好，因为它的预编译（即同一条sql，编译一遍则将其保存起来，下次使用则直接拿出来使用）功能。
PreparedStatement比Statement安全：防止sql注入
4、数据库连接池（DataSource）
传统连接操作缺陷:
每次连接都需要验证用户和密码,并且将连接对象加载到内容
每次连接使用之后就直接销毁(连接没有重复使用)
如果系统访问人数较多,频繁和数据库连接比较耗时,也可能造成内存溢出。
含义:缓存连接对象,关闭连接会将连接对象放入缓存池....
属性:
基本属性(驱动\url\用户名\密码)
连接池最大连接数
最大空闲连接数
最小空闲连接数
初始化连接数
不同连接池厂商有不同的属性…
常见厂商:
DBCP
C3P0
Druid
DBCP案例:
1>硬编码
通过配置文件
db.properties
注意:配置文件的key要和BasicDataSource的属性名要一致。
C3p0案例:
1>硬编码
配置文件
在src目录下新建c3p0-config.xml文件
Druid案例:
几乎和DBCP一样
性能比较
