新手MySQL数据库常用的语句

批量导包快捷键：ctrl+shift+O;
MySQL命令行分号无法结束问题解决: 用 '; 解决。
MySQL命令行找不到光标问题解决: 按键盘上的insert 解决。
解决不能对齐：在登录mysql时输入以下命令： mysql --default-character-set=gbk -uroot -p
解决中文乱码：进入mysql后在命令行输入：set names gbk;
数据库不区分大小写
创建数据库
1. 显示数据库：show databases;
2. 创建数据库: create database +数据库名字;
3. 查看数据库：show create database + 数据库名字
4 .创建数据库指定的字符集：create database db3 character set gbk;
修改数据库的字符集|：alter database db3 character set 'utf8';
5. 删除数据库：drop database db3;
6. 使用数据库 ：use +数据库名字

数据库查询多条重复的sql：select * from uf_ddycdd where order_id in (select order_id from uf_ddycdd group by order_id having count(order_id) > 1) order by id desc

删除数据库查询多条重复语句

DELETE from uf_ddycdd
WHERE (order_id) IN (SELECT order_id FROM uf_ddycdd GROUP BY order_id HAVING COUNT(order_id) > 1)
AND ROWID NOT IN
(SELECT MIN(ROWID) FROM uf_ddycdd GROUP BY order_id HAVING COUNT(*) > 1);

1. 创建表：create table +表名()default charset=utf8;
create table person(name varchar(10),age int);
create table student(id int,name varchar(10), chinese int, math int,english int);
2. 查看表 show create table +表名；
3. create table t1(name varchar(10),age int) engine=myisam charset=gbk;
4.查看表字段：desc+表名
5.删除表：drop table +表名；
6.修改表名；rename table 旧名 to 新名
7.修改引擎和字符集：alter table 表名 engine=myisam/innodb charset=utf8/gbk;
8.添加表字段
alter table +表名 add 字段名 字段类型；//添加到最后
alter table +表名 add 字段名 字段类型 first；//添加到最前面
alter table +表名 add 字段名 字段类型 after xxx；//添加到xxx的后面

9.删除表字段：alter table +表名 drop 字段名
10.修改表字段和类型：alter table +表名 change 原字段名 新字段名 新字段类型；
11.修改字段类型和位置：alter table +表名 modify 字段名 新类型 first/after xxx
truncate table 表名;
删除表并创建新表 让自增数值清零

数据相关的SQL
1.插入数据（增）
全表插入格式:insert into 表名 values(值1，值2，值3)
insert into emp values(3,'刘备',9),(4,'张飞',9),(4,'关羽',9);
insert into emp (name)values('悟空'),('八戒'),('沙僧');
2.查询数据:select 字段信息 from 表名 where 条件
select name,age from emp;

3.修改数据: updata 表名 set 字段名=xxx where 条件
updata emp set age=500 where name='悟空';
4.删除数据: delete from +表名 where +条件
如果删除的是null，条件则用 is null;

###主键约束 primary key
- 什么是主键: 表示数据唯一性的字段称为主键
- 什么是约束: 是创建表时给表字段添加的限制条件
- 主键约束: 让该字段的数据唯一且非空(不能重复,不能null)
- 格式: create table t1(id int primary key,name varchar(10));
insert into t1 values(1,'AAA'); //成功!
insert into t1 values(1,'BBB');//报错 不能重复
insert into t1 values(null,'CCC'); //报错 不能为null
###主键约束+自增
- 自增数值只增不减,从历史最大值基础上+1
- 格式: create table t2(id int primary key auto_increment,name varchar(10));
insert into t2 values(null,'aaa'); //1
insert into t2 values(null,'bbb'); //2
insert into t2 values(3,'ccc'); //3
insert into t2 values(10,'ddd'); //10
insert into t2 values(null,'eee'); //11
delete from t2 where id>=10;
insert into t2 values(null,'fff'); //12
###注释
- 格式:
create table t3
(id int primary key auto_increment comment '这是主键',name varchar(10) comment '这是名字');
###`的作用
- 用于修饰表名和字段名,可以省略
create table `t4`(`id` int,`name` varchar(10));
###冗余
- 由于表设计不够合理导致的大量重复数据称为数据冗余
- 练习:
1. 创建表保存以下数据
集团总部下的教学研发部下的Java教研部下的苍老师工资200年龄18性别男
集团总部下的市场部下的市场A部下的小明工资5000年龄25性别男
- 创建员工表和部门表
create table emp(id int primary key auto_increment,name varchar(10),age int,sal int,gender varchar(10),dept_id int);
create table dept(id int primary key auto_increment,name varchar(10),parent_id int);
- 插入数据:
insert into dept values(null,'集团总部',null),(null,'教学研发部',1),(null,'Java教研部',2),(null,'市场部',1),(null,'市场A部',4);
insert into emp values(null,'苍老师',18,200,'男',3),(null,'小明',25,5000,'男',5);
- 练习2:
创建商品表item和分类表category保存以下数据
1. 保存家电分类下电视机分类下的小米电视 价格(price)1888 库存(num)200
2. 办公用品分类下的打印机分类下的惠普打印机 价格1500 库存100
- 创建表
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
- 插入数据:
insert into category values(null,'家电',null),(null,'电视机',1),(null,'办公用品',null),(null,'打印机',3);
insert into item values(null,'小米电视',1888,200,2),(null,'惠普打印机',1500,100,4);
###事务
- 什么是事务:事务是数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败.
- 事务相关指令:
1. 开启事务 begin;
2. 提交事务 commit;
3. 回滚 rollback;
- 验证转账流程:
create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
insert into user values(null,'超人',50,'冻结'),(null,'蝙蝠侠',5000,'正常'),(null,'灭霸',20,'正常');
- 转账的SQL:
update user set money=money-2000 where id=2 and status='正常';
update user set money=money+2000 where id=1 and status='正常';
- 有事务保护的情况下 回滚流程:
1. 开启事务
begin;
2. 蝙蝠侠-2000
update user set money=money-2000 where id=2 and status='正常';
3. 此时在当前终端查询数据时 数据已经改变(因为查询到的是内存中的改动), 开启另外一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)
4. 超人+2000
update user set money=money+2000 where id=1 and status='正常';
5. 此时从执行结果中发现一条成功一条失败,应该执行回滚操作
rollback;
- 有事务保护的情况下 提交流程:
1. 开启事务
begin;
2. 蝙蝠侠-2000
update user set money=money-2000 where id=2 and status='正常';
3. 此时仍然是在内存中改动 磁盘数据没有发生改变
4. 灭霸+2000
update user set money=money+2000 where id=3 and status='正常';
5. 此时两次改动都是在内存中改完,发现两次全部成功,所以执行提交
commit;
- 保存回滚点:
begin;
update user set money=1 where id=2;
savepoint s1;
update user set money=2 where id=2;
savepoint s2;
update user set money=3 where id=2;
rollback to s2;
- 事务的ACID特性 **面试常考**
保证事务正确执行的四大基本要素
1. Atomicity原子性: 最小不可拆分 保证全部执行成功或全部执行失败
2. Consistency一致性: 从一个一致状态到另一个一致状态
3. Isolation隔离性: 多个事务之间互相隔离互不影响
4. Durability持久性: 当事务提交后数据保存到磁盘中持久生效
###SQL分类
####DDL Data Definition Language数据定义语言
- truncate table 表名;
删除表并创建新表 让自增数值清零
- 包括: create drop alter truncate
- 不支持事务
####DML Data Manipulation Language数据操作语言
- 包括: insert update delete select
- 支持事务
####DQL Data Query Language 数据查询语言
- 只包括: select
####TCL Transaction Control Language 事务控制语言
- 包括: begin,commit,rollback,savepoint xxx, rollback to xxx;
####DCL Data Control Language 数据控制语言
- 负责分配用户权限相关的SQL

###数据类型
1. 整数: int(m) 和 bigint(m) m代表的是显示长度,需要结合zerofill使用
create table t_int(id int,age int(10) zerofill);
insert into t_int values(1,18);
select * from t_int;
2. 浮点数: double(m,d) m代表的是总长度 d代表小数长度,超高精度的浮点数decimal(m,d)
25.234 m=5 d=3
3. 字符串:
- char(m) 固定长度 最大长度255 好处执行效率略高
- varchar(m) 可变长度 好处节省空间 最大长度65535(但是超过255建议使用text)
- text(m) 可变长度 最大长度65535
4. 日期:
- date: 保存年月日
- time: 保存时分秒
- datetime: 保存年月日时分秒,默认值为null,最大9999-12-31
- timestamp(时间戳):保存年月日时分秒,默认值为当前系统时间,最大值2038-1-19
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values('2019-6-20',null,null,null);
insert into t_date values(null,'16:46:30','2019-06-20 16:30:30',null);
###导入*.sql文件
- windows系统 把文件放在C或D盘的根目录
source d:/tables.sql;
- linux系统 把文件放在桌面
source /home/soft01/桌面/tables.sql

show databases;
use newdb3;
show tables; 四张表

select * from t_item;
如果是乱码 执行 set names gbk;

9. 导入*.sql文件
source 路径;

###is null 和 is not null
1. 从员工表中查询 没有上级领导的员工姓名,工资
select ename,sal from emp where mgr is null;
2. 查询有领导的员工姓名和上级领导的编号
select ename,mgr from emp where mgr is not null;
###别名
select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;
###比较运算符 > < >= <= = !=和<>
1. 查询工资在2000以下的员工姓名和工资
select ename,sal from emp where sal<2000;
2. 查询职位(job)是manager的员工姓名、工资、职位
select ename,sal,job from emp where job='manager';
3. 查询工资小于等于1600的员工姓名、职位、工资
select ename,job,sal from emp wehere sal<=1600;
4. 查询不是10号部门的员工姓名和部门编号（两种写法）
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
5. 查询商品表（t_item）中单价为23的商品信息
select * from t_item where price=23 \G;
6. 查询单价不是8443的商品标题(title)
select title from t_item where price!=8443;
###去重 distinct
1. 查询员工表中有哪些职位
select distinct job from emp;
2. 查询员工表中出现了那几个部门编号
select distinct deptno from emp;
###and 和 or
- 如果查询数据时需要同时满足多个条件则使用and &&
- 如果查询数据时只需要满足条件中的某一个则使用or ||
1. 查询10号部门中工资低于2000的员工信息
select * from emp where deptno=10 and sal<2000;
2. 查询部门是30号或者工资高于3000的员工信息
select * from emp where deptno=30 or sal>3000;
###模糊查询 like
- % 代表0或多个未知字符
- _ 代表单个未知字符
举例: 第一个字符是x x%
最后一个字符是y %y
包含 %x%
倒数第二个字符是x %x_
x开头y结尾 x%y
第二个字符是x 倒数第三个字符是y _x%y__
1. 查询名字以j开头的所有员工姓名
select ename from emp where ename like 'j%';
2. 查询名字第二个字符是l的员工姓名和工资
select ename,sal from emp where ename like '_l%';
3. 查询t_item表中 标题中包含记事本的商品标题
select title from t_item where title like '%记事本%';
4. 查询职位中包含an并且工资高于1500的员工姓名、工资、职位
select ename,sal,job from emp where job like '%an%' and sal>1500;
5. 查询有赠品的dell商品详情(卖点sell_point中包含“赠”，并且title包含dell)
select * from t_item where sell_point like '%赠%' and title like '%dell%';
6. 查询单价低于100块钱的笔记本标题和价格(提示:title包含笔记本)
select title,price from t_item where price<100 and title like '%笔记本%';
7. 查询有图片的得力商品信息（image字段不为null,title包含得力）
select * from t_item where image is not null and title like '%得力%';
8. 查询不包含a的员工姓名
select ename from emp where ename not like '%a%';
###between x and y
1. 查询工资在2000到3000之间的员工姓名和工资(要求包含2000和3000)
select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;
2. 查询单价在50到100之间的商品标题和单价
select title,price from t_item where price between 50 and 100;
3. 查询工资在1000到2000之外的员工姓名和工资
select ename,sal from emp where sal not between 1000 and 2000;
###in
- 当查询字段值为多个值的时候使用
1. 查询员工工资为800,1300,1500的员工信息
select * from emp where sal=800 or sal=1300 or sal=1500;
select * from emp where sal in(800,1300,1500);
2. 查询商品价格为56,58,89的商品标题和单价
select title,price from t_item where price in(56,58,89);
3. 查询工资不等于3000,5000,1500的员工姓名和工资
select ename,sal from emp where sal not in(3000,5000,1500);
####综合练习题
1. 查询分类id为238,917的商品信息 (涉及字段自己查)
select * from t_item where category_id in(238,917);
2. 查询价格在50-200之间的得力商品标题和价格
select title,price from t_item where price between 50 and 200 and title like '%得力%';
3. 查询有上级领导并且工资小于2000的员工姓名、工资和领导编号
select ename,sal,mgr from emp where mgr is not null and sal<2000;
4. 查询有奖金并且有上级领导的员工姓名、奖金、领导编号
select ename,comm,mgr from emp where comm>0 and mgr is not null;
5. 查询名字中包含a,并且工资在3000以内的员工从事的职业有哪几种
select distinct job from emp where ename like '%a%' and sal<3000;
###排序 order by
- 格式: order by 字段名 asc/desc asc:升序(默认) desc:降序
- order by 写在条件的后面
1. 查询工资小于3000的员工姓名和工资 要求按照工资降序排序
select ename,sal from emp where sal<3000 order by sal desc;
2. 查询10号部门每个员工的姓名和工资 按照工资升序排序
select ename,sal from emp where deptno=10 order by sal;
3. 查询有奖金的员工姓名、工资、奖金 按照奖金降序排序
select ename,sal,comm from emp where comm>0 order by comm desc;
4. 查询单价低于100的商品标题和单价并按照单价升序排序
select title,price from t_item where price<100 order by price;
- 多字段排序: order by 字段1 asc/desc,字段2 asc/desc
5. 查询每个员工的姓名、工资和部门编号 按照部门编号降序排序,如果部门一致则按照工资升序排序
select ename,sal,deptno from emp order by deptno desc,sal;
####分页查询 limit 跳过的条数,请求的条数
- 请求第一页的10条数据 limit 0,10
- 请求第三页的10条数据 limit 20,10
- 请求第八页的5条数据 limit 35,5
- 公式 limit (页数-1)*数量,数量
- 请求第四页的7条数据 limit 21,7
1. 查询每个员工的姓名和工资,按照工资降序排序 请求第三页的3条数据
select ename,sal from emp order by sal desc limit 6,3;
2. 请求员工表中工资最高的前三名员工的信息
select * from emp order by sal desc limit 0,3;
3. 查询商品标题和单价 按照单价升序排序 第三页的5条数据
select title,price from t_item order by price limit 10,5;
4. 查询30号部门中工资最高的员工信息
select * from emp where deptno=30 order by sal desc limit 0,1;
###数值计算 + - * / %
1. 查询员工姓名,工资和年终奖(年终奖=工资*5)
select ename,sal,sal*5 年终奖 from emp;
2. 查询商品标题，商品单价，库存和总价值（单价*库存）
select title,price,num,price*num 总价值 from t_item;
3. 查询每个员工的姓名和 加薪5块钱之后的工资
select ename,sal+5 from emp;
###日期相关
SQL
1. 获取系统时间 now()
create table t_date(name varchar(10),birthday datetime);
insert into t_date values('刘德华',now());
2. 获取当前的年月日 和当前的时分秒 cur=current 当前
select "hellworld";
select curdate(),curtime();
3. 从年月日时分秒中 提取年月日 和 提取时分秒
select date(now());
select time(now());
查询商品的上传日期 只查询年月日
select created_time from t_item; //商品上传年月日时分秒
select date(created_time) from t_item;//商品上传年月日
4. 从年月日时分秒中提取时间分量
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
查询员工入职的年份
select extract(year from hiredate) from emp;
5. 日期格式化 date_format(时间,格式);
- 格式规则:
%Y 四位年 2019 %y 两位年 19
%m 2位月 06 %c 1位月 6
%d 日
%H 24小时 %h 12小时
%i 分钟
%s 秒
- 测试: 把now() 2019-06-21 15:29:30 转成 2019年06月21号 15点29分30秒
select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
6. 反向格式化 str_to_date(时间,格式)
21.06.2019 15点36分20秒 转回 2019-06-21 15:36:20
select str_to_date('21.06.2019 15点36分20秒','%d.%m.%Y %H点%i分%s秒');
###ifnull(x,y)
- age=ifnull(x,y) 如果x值为null则age=y 否则age=x
- 练习:修改奖金为null的值为0 不为null则不变
update emp set comm=ifnull(comm,0);
###聚合函数
- 对查询的多条数据进行统计查询: 平均值,最大值,最小值,求和,计数
1. 平均值 avg(字段名)
-查询员工的平均工资
select avg(sal) from emp;
2. 最大值 max(字段名)
-查询20号部门的最高工资
select max(sal) from emp where deptno=20;
3. 最小值 min(字段名)
-查询名字里面包含a的员工的最低工资
select min(sal) from emp where ename like '%a%';
4. 求和 sum(字段名)
-查询10号部门工资总和
select sum(sal) from emp where deptno=10;
5. 计数 count(字段名)
-查询工资高于2000的员工数量
select count(*) from emp where sal>2000;
- 查询20号部门的平均工资,最高工资,最低工资,工资总和,员工数量
select avg(sal),max(sal),min(sal),sum(sal),count(*) from emp where deptno=20;
###字符串相关
1. 字符串拼接concat(s1,s2); s1s2
select concat('aa','bb');
- 查询每个员工的姓名和工资 要求工资以元为单位
select ename,concat(sal,'元') from emp;
2. 获取字符串的长度 char_length(str);
select char_length('abc');
- 查询每个员工的姓名和名字长度
select ename,char_length(ename) from emp;
3. 获取字符串出现的位置 instr(str,substr);
select instr('abcdefg','d');
4. 转大写和转小写
select upper('abc'),lower('NBA');
5. 截取字符串
- 左边截取 select left('abcdefg',2);
- 右边截取 select right('abcdefg',2);
- 自由截取 select substring('abcdefg',2,3); 3代表长度
6. 去两端空白 trim()
select trim(' a b ');
7. 重复repeat()
select repeat('ab',2);
8. 替换replace()
select replace('abcde abc','b','m');
9. 反转reverse()
select reverse('abc');

####数学相关
- 向下取整 floor(num)
select floor(3.93);
- 四舍五入 round(num)
select round(3.93);
- 四舍五入 round(num,m) m代表小数位数
select round(23.879,2);
- 非四舍五入 truncate(num,m) m代表小数位数
select truncate(23.879,2);
- 随机数 rand() 获得0-1的随机数
select rand();
- where 后面只能写普通字段的条件
- having 后面写聚合函数的条件 ,having和分组查询结合使用
- 各个关键字的顺序:
select .....from 表名 where ......group by .... having ....
order by .... limit .....;
###分组查询
1. 查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
2. 查询每种工作的最高工资
select job,max(sal) from emp group by job;
3. 查询每个部门的人数
select deptno,count(*) from emp group by deptno;
4. 查询每个部门工资高于1500的人数
select deptno,count(*) from emp where sal>1500 group by deptno;
5. 查询每个主管mgr的手下人数
select mgr,count(*) from emp where mgr is not null group by mgr;
- 多字段分组:
group by 字段1名,字段2名
1. 查询每个部门下每种职业的平均工资
select deptno,job,avg(sal) from emp group by deptno,job;
###子查询(嵌套查询)
1. 查询工资高于平均工资的员工信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
2. 查询员工表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp);
3. 查询工资高于20号部门最高工资的员工信息
select max(sal) from emp where deptno=20;
select * from emp where sal>(select max(sal) from emp where deptno=20);
4. 查询和jones相同工作的其它员工信息
select job from emp where ename='jones';
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
5. 查询工资最低员工的同事们的信息（同事指同一部门）
select min(sal) from emp;

select deptno from emp where sal=(select min(sal) from emp);

select * from emp where deptno=(第二坨) and sal != (第一坨);
6. 查询最后入职的员工信息
select max(hiredate) from emp;
select * from emp where hiredate=(select max(hiredate) from emp);
7. 查询king的部门信息(需要用到dept表)
select deptno from emp where ename='king';
select * from dept where deptno=(select deptno from emp where ename='king');
8. 查询员工表中出现过的部门的信息(部门表里面有个40号部门,需要过滤掉它)
- 先查询员工表中出现的部门编号
select distinct deptno from emp;
- 去部门表中查询详情
select * from dept where deptno in(select distinct deptno from emp);
9. 查询平均工资最高的部门信息(难度最高,需要考虑并列第一的问题)
- 先查询平均工资最高的值
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
- 通过最高的平均工资 查询 部门编号
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
- 通过部门编号 查询部门信息
select * from dept where deptno in(上面一坨);
- 子查询可以写的位置
1. 写在where和having后面,当做查询条件的值
2. 写在创建表的时候,把查询结果保存到新的表中
create table emp_10 as (select * from emp where deptno=10);
3. 写在from后面 **一定要有别名**
select * from emp where deptno=10;
select ename from (select * from emp where deptno=10) t;
###关联查询
- 同时查询多张表的查询方式称为关联查询
- 关联查询必须写关联关系,如果不写则会得到两张表的乘积,这个乘积称为笛卡尔积, 这是一个错误的查询结果,切记工作中不要出现.

1. 查询每一个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
####关联查询的查询方式之 等值连接和内连接
1. 等值连接:
select * from A,B where A.x=B.x and A.y>2000;
2. 内连接:
select * from A [?inner] join B on A.x=B.x where A.y>2000;
-查询每一个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;
-查询工资高于2000的每个员工的姓名 工资和对应的部门地点
select e.ename,e.sal,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.sal>2000;
- 查询在new york工作的员工姓名和工资
select e.ename,e.sal
from emp e join dept d
on e.deptno=d.deptno
where d.loc='new york';
- 查询james的部门名称和地点
select d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.ename='james';
###关联查询的查询方式之 外连接
- 等值连接和内连接查询的是两张表的交集数据
- 外连接查询的是一张表的全部数据和另外一张表的交集数据
- 格式:
select * from A left/right join B on A.x=B.x where A.y<2000;
- 查询所有的部门名和对应的员工姓名
select d.dname,e.ename
from emp e right join dept d
on e.deptno=d.deptno;

- 总结: 如果查询的数据是两张表的交集数据使用等值连接或内连接(推荐),如果查询的是一张表的全部数据和另外一张表的交集数据则使用外连接

###
1 案例：查询没有上级领导的员工的编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
2 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
select ename,job,sal,comm from emp where comm=0;
3 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
select ename,job,sal,comm from emp where comm>0;
4 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
select ename,sal,mgr from emp where mgr is not null;
5 案例：查询emp表中名字以‘S’开头的所有员工的姓名
select ename from emp where ename like 's%';
6 案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
select ename from emp where ename like '%s';
7 案例：查询倒数的第2个字符是‘E’的员工的姓名
select ename from emp where ename like '%e_';
8 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
select ename from emp where ename like '%n__';
9 案例：查询emp表中员工的名字中包含‘A’的员工的姓名
select ename from emp where ename like '%A%';
10 案例：查询emp表中名字不是以'K'开头的员工的所有信息
select * from emp where ename not like 'K%';
11 案例：查询emp表中名字中不包含‘A’的所有员工的信息
select * from emp where ename not like '%a%';
12 案例：做文员的员工人数（job 中 含有 CLERK 的）
select count(*)from emp where job in('clerk');
13 案例：销售人员 job: SALESMAN 的最高薪水
select max(sal) from emp where job in('salesman');
14 案例：最早和最晚入职时间
select max(HIREdate),min(HIREdate) from emp;
15 案例：查询类别 163的商品总库存量
select sum(num) from t_item where category_id=163;
16 案例：查询 类别 163 的商品
select * from t_item where category_id=163;
17 案例：查询商品价格不大于100的商品名称列表
select title from t_item where price >100;
18 案例：查询品牌是联想,且价格在40000以上的商品名称和价格
select title,price from t_item where title like '%联想%'and price>40000;
19 案例：查询品牌是三木,或价格在50以下的商品名称和价格
select title,price from t_item where title like '%三木%'or price<50;
20 案例：查询品牌是三木、广博、齐心的商品名称和价格
select title,price from t_item where title not like '%联想%'or title not like '%戴尔%'or title not like '%齐心%';
21 案例：查询品牌不是联想、戴尔的商品名称和价格
select title,price from t_item where title not like '%联想%'and title not like '%戴尔%';
22 案例：查找品牌是联想且价格大于10000的名称
select title from t_item where title like '%联想%'and price>10000;
23 案例：查询联想或戴尔的电脑名称列表
select title from t_item where title like '%联想%' or title like '%戴尔%';
24 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
select empno,ename,job,sal from emp where sal between 1000 and 2000;
25 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
26 案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
select deptno,ename,job,sal from emp where ename like '%e%'and job <>'manager';
27 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
select deptno,ename,deptno from emp where deptno=10 or deptno =20;
28 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
select deptno,ename,comm from emp where comm=0 or ename not like '%t_';
29 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
select ename,job,sal,HIREdate,deptno from emp where sal>3000 or deptno=30;
30 案例：查询不是30号部门的员工的所有信息
select *from emp where deptno!=30;
31 案例：查询奖金不为空的员工的所有信息
select *from emp where comm<>0;
32 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
select deptno, ename,job from emp order by deptno desc;
33 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
select ename,job,sal from emp where deptno=10 or deptno=30 order by sal;
34 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
select *from emp order by deptno ,empno desc;
35 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
select empno,ename,sal,deptno from emp where sal>1000 or mgr is null order by deptno desc,sal;
36 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
select empno,ename,sal,comm from emp where ename not like'%S%' order by sal,deptno desc;
37 案例：统计emp表中员工的总数量
select count(*) from emp;
38 案例：统计emp表中获得奖金的员工的数量
select count(*) from emp where comm<>0;
39 案例：求出emp表中所有的工资累加之和
select sum(sal) from emp ;
40 案例：求出emp表中所有的奖金累加之和
select sum(comm) from emp;
41 案例：求出emp表中员工的平均工资
select avg(sal) from emp;
42 案例：求出emp表中员工的平均奖金
select avg(comm) from emp;
43 案例：求出emp表中员工的最高工资
select max(sal)from emp;
44 案例：求出emp表中员工编号的最大值
select max(empno) from emp;
45 案例：查询emp表中员工的最低工资。
select min(sal) from emp;
46 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
select count(*) 员工的人数,sum(sal) 工资的总和,avg(sal)平均工资,max(comm)奖金的最大值,min(comm)

select avg(sal) a from emp group by deptno order by a desc limit 0,1;
select deptno,avg(sal) from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1);

select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1));

select deptno, avg(sal) a from emp group by deptno having a>2000;
select job,max(sal) from emp group by job ;

select deptno,count(*) from emp group by deptno;
select mgr ,count(*) from emp where job != 'king' group by mgr;

select distinct hiredate) (year from hiredate) from emp ;
//查找平均工资最高的部门信息
1.先获取最高平均工资
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
2.找到最平均工资最高的部门
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
3.通过部门编号获取部门信息
select * from dept where deptno=(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));

//获取有员工的部门信息
1.select deptno from emp group by deptno;
2.select * from dept where deptno in(select deptno from emp group by deptno);
//查询最后入职的员工信息
select max(hiredate) from emp ;
select *from emp where hiredate=(select max(hiredate) from emp);
//查询员工king的部分编号和部门名称
select deptno from emp where ename='king';
select deptno,dname from dept where deptno=(select deptno from emp where ename='king');
//查询jones相同工作的其他员工信息
select job from emp where ename='jones';
select *from emp where job=(select job from emp where ename='jones') and ename!='jones';
//查询工资最低的员工的同事们的信息
select min(sal) from emp;
select job from emp where sal=(select min(sal) from emp);
select *from emp where job=(select job from emp where sal=(select min(sal) from emp))and sal!=(select min(sal) from emp);

----------------------练习
1.select deptno,count(*)c from emp group by deptno order by c desc;
2.select deptno,mgr,count(*)c from emp where mgr (s not null group by deptno,mgr order by c desc;
3.select job,avg(sal) from emp group by job;
4.select extract(year from hiredate) year,count(*) from emp group by year;
5.select *from emp where sal=(select min(sal) from emp);
6.少于等于3的部门信息
不考虑40号部门
select *from dept where deptno in (select deptno from emp group by deptno having count(*)<=3);
考虑40号部门
select d.*
from emp e right join dept d on e.deptno=d.deptno
group by d.deptno having count(e.ename)<=3;

7. 只有一个下属的主管信息
select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count(*)=1);
8. 每月发工资最多的部门信息
select * from dept where deptno=(select deptno from emp group by deptno having sum(sal)=(select sum(sal)s from emp group by deptno order by s desc limit 0,1));
9. 下属最多的人，查询其个人信息
select * from emp where mgr in(select mgr from emp group by mgr having count(*)=(select count(*) from emp group by mgr order by count(*) desc limit 0,1));
10. 拿最高工资员工的同事信息
select * from emp where deptno=(select deptno from emp where sal=(select max(sal) from emp))and sal!=(select max(sal) from emp);
11. 和最后入职的员工在同一部门的员工信息 实现流程和第十题一样
select *from emp where deptno=(select deptno from emp where hiredate=(select max(hiredate) from emp ))and hiredate!=(select max(hiredate) from emp );
12. 查询平均工资高于20号平均工资的部门信息
select *from dept where deptno=(select deptno from emp group by deptno having avg(sal) >(select avg(sal) from emp where deptno=20));
13. 查询员工信息和员工对应的部门名称
select e.*,d.dname from emp e join dept d on e.deptno=d.deptno;

14. 查询员工信息，部门名称，所在城市
select e.*,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno;
15. 查询Dallas市所有的员工信息
select e.*from emp e left join dept d on e.deptno=d.deptno where d.loc='Dallas';

16. 计算每个城市的员工数量
select count(e.ename),d.loc from emp e right join dept d on e.deptno=d.deptno group by d.loc;
17. 查询员工信息和他的主管姓名
select e.*,m.ename
from emp e join emp m on e.mgr=m.empno;
18. 员工信息，员工主管名字，部门名
select e.*,m.ename
from emp e join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;
19. 员工名和他所在部门名
select e.ename,d.dname
from emp e join dept d on e.deptno=d.deptno;
20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
select e.ename,d.empno,d.ename,d.job,d.sal
from emp e left join emp d on e.mgr=d.empno;
21. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
select e.empno,e.ename,e.job,d.dname,d.loc from
emp e left join dept d on e.deptno=d.deptno where e.ename not like '%k%';

22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
select e.empno,e.ename,e.job,e.job,d.* from
emp e right join dept d on e.deptno=d.deptno ;

建表流程：
1.建表
create table t_product(id int,name varchar(10),abstract varchar(100),price decimal(8,2));
create table t_product_info(id int,cpu varchar(20),ram varchar(20),hdd varchar(20));
2.插入数据
insert into t_product values(1,'p30','top 1 mobile',500.00);
insert into t_product_info values(1,'960','6gb ram','128gb');
3。查看数据
select p.id,name,cpu,ram,hdd
from t_product p join t_product_info i on p.id=i.id;

create table sp(id int primary key auto_increment,name varchar(20),abstract varchar(20),price decimal(8,2);

create table sp_xq(id int primary key auto_increment,cpu varchar(20),ram varchar(20),hdd varchar(20);

insert into sp values(null,'苹果','iphone7','像素不错',5000.00);
insert into sp_xq values(null,'690','8gb','64gb');

create table t_order(id int,username varchar(10));
create table t_order_item(id int,oid int ,product varchar(100),price double);

insert into t_order values(1,'范传奇');
insert into t_order_item values(1,1,'大黄蜂',500),(2,1,'擎天柱',1500),(3,1,'威震天',200);

insert into t_order values(2,'刘国斌');
insert into t_order_item values(1,2,'尿不湿',100),(2,2,'奶粉',2000),(3,2,'婴儿床',100);

create table category(id int,pid int,name varchar(10));

insert into category values(1,0,'家电'),(2,0,'数码'),(3,0,'服装'),(4,1,'电视'),(5,1,'冰箱'),(6,4,'智能电视'),(7,4,'黑白电视')；

create table t_teacher(id int,name varchar(100));
create table t_student(id int,name varchar(100));
create table t_teach(tid int,sid int));
-- 老师数据
insert into t_teacher values(1,'范传奇'),(2,'刘国斌');
insert into t_teacher values(3,'王克晶');
-- 学生数据
insert into t_student values(1,'莫小贝'),(2,'白展堂'),(3,'吕秀才'),(4,'李大嘴');
-- 多对多关系表
insert into t_teach values(1,1),(1,2),(1,3),(2,2),(2,3),(2,4);
insert into t_teach values(3,1),(3,2),(3,3)(3,4);
select t.name teacher,s.name student from t_teacher t join t_teach tt on t.id=tt.tid
join t_student s on tt.sid=s.id where t.id=1;

select t.name teacher ,s.name student
from t_teacher t join t_teach tt on t.id=tt.tid join t_student s on tt.sid=s.id where t.id=3;

select s.name 学生,t.name 老师
from t_teacher t join t_teach tt on t.id=tt.tid join t_student s on s.id=tt.sid where s.id=1;

课程表练习

create table t_student(id int,name varchar(100));
create table t_choose(sid int,cid int));
create table t_course(id int,name varchar(100));

insert into t_student values(1,'莫小贝'),(2,'白展堂');
insert into t_course values(1,'java'),(2,'php'),(3,'python'),(4,'cpp');

insert into t_choose values(1,1),(1,4),(2,1),(2,3);

select s.name 学生,sss.name 课程
from t_student s join t_choose ss on s.id=ss.sid join t_course sss on sss.id=ss.cid where s.id=1;

用户表练习

create table t_user(id int,name varchar(100));
create table t_user_role(uid int,rid int);
create table t_role(id int,name varchar(100));
create table t_role_function(rid int,fid int);
create table t_function(id int,name varchar(100));

insert into t_user values(1,'范老'),(2,'王老'),(3,'刘老');
insert into t_user_role values(1,1),(1,2),(2,3);
insert into t_role values(1,'消费者'),(2,'店家'),(3,'店小二'),(4,'管理员');
insert into t_role_function values(1,1),(2,2),(2,3),(3,3),(4,4);
insert into t_function values(1,'购买商品'),(2,'搜索商品'),(3,'上货'),(4,'管理用户');

select t.name user,ttt.name role ,ttttt.name function
from t_user t join t_user_role tt on t.id=tt.uid join t_role ttt on ttt.id=tt.rid
join t_role_function tttt on tttt.rid=ttt.id join t_function ttttt on tttt.fid=ttttt.id
where t.id=1;

 

### 用户-角色-功能 的关系

由两个多对多关系连接而成:


1. 编写表

create database db5;
use db5;

create table t_user(
id int,
name varchar(100)
);

create table t_role(
id int,
name varchar(100)
);

create table t_function(
id int,
name varchar(100)
);

create table t_user_role(
uid int,
rid int
);

create table t_role_function(
rid int,
fid int
);

2. 插入数据

-- 用户表插入数据
insert into t_user (id, name)
values (1, '范老');
insert into t_user (id, name)
values (2, '王老');
insert into t_user (id, name)
values (3, '刘老');

-- 角色表插入数据
insert into t_role (id, name)
values (1, '消费者');
insert into t_role (id, name)
values (2, '店家');
insert into t_role (id, name)
values (3, '店小二');
insert into t_role (id, name)
values (4, '管理员');

-- 功能表插入数据
insert into t_function (id, name)
values (1, '购买商品');
insert into t_function (id, name)
values (2, '搜索商品');
insert into t_function (id, name)
values (3, '上货');
insert into t_function (id, name)
values (4, '管理用户');

-- 用户角色关系
insert into t_user_role (uid, rid)
values (1, 1);
insert into t_user_role (uid, rid)
values (1, 2);
insert into t_user_role (uid, rid)
values (2, 3);

-- 角色功能关系
insert into t_role_function (rid, fid)
values (1, 1);
insert into t_role_function (rid, fid)
values (2, 2);
insert into t_role_function (rid, fid)
values (2, 3);
insert into t_role_function (rid, fid)
values (3, 3);
insert into t_role_function (rid, fid)
values (4, 4);

3. 查询: 范老有哪些角色和功能

select u.name as user, r.name as role, f.name as function
from t_user u
join t_user_role ur on u.id=ur.uid
join t_role r on ur.rid=r.id
join t_role_function rf on rf.rid=r.id
join t_function f on f.id=rf.fid
where u.id=1;

## View (视图)

利用一个查询语句构建一个虚拟表. 如果需要查询就可以通过查询视图获得结果.

利用视图可以重用复杂的查询功能, 简化二次查询工作


视图不是表, 视图中不存储数据, 数据是通过内部封装的SQL语句动态查询得到的. 视图的本质是"复用复杂查询"!!!

1. 创建视图

create view student_course as (
select s.name as student, c.name as course
from t_student s
join t_choose cc on s.id = cc.sid
join t_course c on cc.cid=c.id
);

2. 利用视图进行查询:

select student, course from student_course
where student = '莫小贝';

select student, course from student_course
where student = '白展堂';

3. 管理view
- 创建 create view 视图名 as 查询
- 查询全部视图SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';
- 删除视图 drop view 视图名
- 视图可以嵌套使用
------------------第六天
## 约束
用于限制约定表中的数据完整性, 有效性.
数据库提供了"基础的"完整性,有效性检查.

1.主键约束 primary key
约束一个列中的值, 符合数据库主键规则要求, 非空且不能重复(唯一).
如果违反就会报错误!
大多数情况下, 表都使用主键约束, 但有的时候,为提高数据插入性能, 故意不设置主键约束. 比如日志表.
2.非空约束 not null
验证表中的列值, 不能添加空值.
在设计表的时候为列添加非空约束, 则在插入更改数据时候, 一旦数据为null则报错, 进制添加和修改.
create table t3(
id int primary key,
name varchar(50) not null,
nick varchar(50)
);

-- 正常插入
insert into t3 (id, name, nick)
values (1, '刘国斌', null);
insert into t3 (id, name, nick)
values (2, '范传奇', '范老湿');
-- 错误插入
insert into t3 (id, name, nick)
values (3, null, '范');
3.唯一约束 unique
限定一个列中的值, 不能重复, 保持唯一. 除了主键以外的其他列保持唯一
1. 建表

-- 设定邮箱地址列是唯一约束, 不能重复
create table t4(
id int primary key,
name varchar(50) not null,
email varchar(100) unique
);

4. 默认约束 default '男'

为列添加默认值
1. 建表

-- 将性别的默认值设置为 男
create table t5(
id int primary key,
name varchar(50) not null,
sex varchar(10) default '男'
);
5. 外键约束
语法:FOREIGN KEY(外键列) REFERENCES 主键表(主键列)

. 建表

-- 主键表
create table user(
id int primary key,
name varchar(50) not null
);
-- 外键表
create table trad(
id int primary key,
uid int,
money double,
FOREIGN KEY(uid) REFERENCES user(id)
);
> 外键列: uid 列的值必须是 user id 的值!
外键特点:

1. 添加外键约束的字段值可以为null，但是不能是关联表中不存在的数据
2. 如果建立了关系被关联的数据不能先删除，被关联的表不能先删除
3. 有外键关联以后,删除数据时候,就必须先删除外键关联, 在删除主键表中的数据.

## 索引

数据库提供的一种高效查询算法! 可以提高海量数据查询效率!

索引可以大大加快大数量的查询效率!

数据量常见索引算法: B+树(B plus Tree)

B+树原理:
1. 数据库数据是连续存储到 磁盘 上的数据块. 每次读写一个数据块, 每个数据块读写 需要 几个ms. 如果是海量数据查询时候, 就需要很多次读写每个数据块, 累计时间很长!
2. B+树 建立两层索引数据块, 索引中包含数据的范围. 只需要读取两层索引块两次, 就能确定目标数据的位置. 查询到结果. 大大减少磁盘IO次数, 提高查询性能!
3. 在最终数据块上还有一次IO, 一共3次磁盘块IO就可以读取到数据, 其性能非常好!!

语法

create index 索引名 on 表名(字段名(?长度));

1. 创建索引

create index idx_user_name on user(name);

2. 使用索引(自动使用索引!)

select id, name from user where name='Tom';
# JDBC

Java 数据库连接: 将Java程序连接到数据的桥梁.

1. Sun(Java) 设计了JDBC API底层封装了Socket, 简化数据库的访问.
2. JDBC为数据库提供了统一访问接口.


使用JDBC

1. 利用maven导入数据库驱动
1. 创建Maven项目
2. 找到 JDBC驱动程序的 坐标
3. 将JDBC坐标添加到 pom.xml
2. 注册数据库驱动: 告诉JDBC如何找到数据库驱动的实现类
1. 最新的数据库驱动,会自动注册(有些驱动程序不支持).
2. 建议手动注册:
1. Class.forName("数据库驱动程序类名")
2. Class.forName("com.mysql.jdbc.Driver")
3. 建立与数据库之间的连接

String usr="root";
String pwd="";
// jdbc:mysql://数据库IP:3306/数据库名
String url="jdbc:mysql://localhost:3306/db6";
Connection conn=DriverManger.getConnection(url, usr, pwd);

4. 创建Statement(语句)对象: 用于执行SQL(操作数据库)
1. DDL create drop 等 一般使用 execute
2. DML insert delete update 一般使用 executeUpdate 执行
3. DQL select 一般使用 executeQuery

String sql = "create table t_user(id int, name varchar(50))";
Statement st = conn.createStatement();
st.execute(sql);
st.close();

5. 关闭连接

conn.close();
MySQL JDBC 连接常用参数, 写在连接url上:

1. characterEncoding 字符编码, 可以设置为utf8
2. useUnicode 是否使用unicode字符编码, 设置为true
3. 关闭ssl加密, useSSL 设置为false

jdbc:mysql://localhost:3306/db6?characterEncoding=utf8&useUnicode=true&useSSL=false

## 批量:批量参数

PreparedStatement

1. 将SQL发送到服务器创建执行计划
2. 绑定参数
3. 将绑定参数添加到 批处理
4. 重复 2 3 n次
5. 执行: 将一批参数发送到服务器

案例:

public class BatchDemo01 {

public static void main(String[] args) {
/**
* 将批量参数传递到数据库执行
*/
String sql=
"insert into t_user (id, name) values (?,?)";
Connection conn = null;
try {
conn = DBUtil.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, 100);
ps.setString(2, "user1");
//将上述参数添加到ps的参数缓冲区中
ps.addBatch();

ps.setInt(1, 101);
ps.setString(2, "user2");
//将上述参数添加到ps的参数缓冲区中
ps.addBatch();

//将缓冲区中的参数一批发送到服务器执行
int[] arr = ps.executeBatch();
System.out.println(Arrays.toString(arr));
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}

}

## JDBC事务处理

使用JDBC API 调用数据库事务(编程式事务处理)

1. begin -- conn.setAutoCommit(false) 关闭自动提交
2. commit -- conn.commit()
3. rollback -- conn.rollback()

原理:


案例:

1. 创建数据库表

create table acc(
id int,
name varchar(100),
balance double
);

insert into acc (id, name, balance)
values (1, '范传奇', 1000);
insert into acc (id, name, balance)
values (2, '刘国斌', 200);

2. 案例:

public class TxDemo02 {

public static void main(String[] args) {
/**
* 事务演示
*/
trans(1, 2, 2000);
trans(3, 2, 500);
trans(1, 3, 500);
trans(1, 2, 500);
}
/**
* 从 from 账号 向 to 账号转钱, 钱数是money
*/
public static void trans(
int from, int to, double money) {
String sql1="update acc set balance=balance+? "
+ "where id=?";
String sql2="select balance from acc where id=?";
Connection conn = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
PreparedStatement ps=conn.prepareStatement(sql1);
ps.setDouble(1, -money); //-500
ps.setInt(2, from);
int n = ps.executeUpdate();
if(n!=1) throw new Exception("汇出失败");
ps.setDouble(1, money); //500
ps.setInt(2, to);
n = ps.executeUpdate();
if(n!=1) throw new Exception("汇入失败");
ps.close();//ps对象用完了, 关闭释放
//检查汇出人的余额
ps = conn.prepareStatement(sql2); //重用ps变量
ps.setInt(1, from);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
double balance = rs.getDouble("balance");
if(balance<0) throw new Exception("透支");
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if(conn!=null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(conn);
}
}

}

## 读取自增主键

问题: 添加一个战队数据

create table team(
id int primary key auto_increment,
name varchar(10)
);
create table player(
id int primary key auto_increment,
name varchar(10),
team_id int
);

使用原因:


原理:


案例:

public class KeyDemo03 {

public static void main(String[] args) {
/**
* 读取自动生成的ID
*/
String sql="insert into team (id, name) "
+ "values (null, ?)";
Connection conn = null;
try {
conn = DBUtil.getConnection();
//创建可以返回自动生成key的 ps 对象
//GENERATED 生成的 KEYS"键"
PreparedStatement ps =
conn.prepareStatement(
sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "狂浪");
ps.executeUpdate();//ps中已经包含了生成的keys
//利用API读取包含在ps中的key,
ResultSet rs = ps.getGeneratedKeys();
//生成的key就存储在 rs对象中
int id=0;
while(rs.next()) {
id=rs.getInt(1);
//只能使用序号读取,1是第一个数据序号
}
System.out.println(id);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
}

## 元数据(了解)

元数据: 与XXX相关的数据

数据库连接相关的数据? 称为数据库连接的元数据.

Connection conn

Connection 上提供的相关数据(元数据)查询的API

1. 可以获取当前连接数据库是什么
2. 可以获取当前数据库版本
3. 可以获取当前驱动程序等信息
4. ...

ResultSet也提供的相关数据查询API

1. 获得当前结果集的列数
2. 当前结果集的列名等
3. ...

元数据案例:

public class MetaDataDemo04 {

public static void main(String[] args) {
/**
* 获取数据库连接相关的元数据
*/
Connection conn = null;
try {
conn = DBUtil.getConnection();
//获取 元数据 对象meta
DatabaseMetaData meta=conn.getMetaData();
//meta封装了与当前数据库有关的信息
String product=meta.getDatabaseProductName();
String version=meta.getDatabaseProductVersion();
System.out.println(product+version);

String sql = "select * from team";
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery(sql);
//获得 rs(结果集) 相关的元数据
ResultSetMetaData m=rs.getMetaData();
//m对象包含 与结果集有关的数据
//列的数量, 类名等. Column列
int n = m.getColumnCount();
System.out.println(n);
System.out.println(m.getColumnName(1));
System.out.println(m.getColumnName(2));

} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}

}

# Servlet

Servlet: 是指在在服务器上执行的程序片段.

1. Servlet是JavaEE规范的一部分
2. 用来解决Web服务器端编程问题
3. Servlet组件工作在Java Web服务器中

## 安装配置Tomcat服务器

1. 下载Tomcat http://tomcat.apache.org
2. 安装: 释放tomcat压缩包即可
3. 在Eclipse中配置Tomcat

参考: http://doc.tedu.cn/tomcat/index.html

## Servlet Hello World

实现步骤

1. 创建 Maven Web 项目
1. 创建maven war项目(Eclipse必须能够连接到Mavne仓库)
2. 在项目上 使用 右键创建 "部署描述文件 web.xml"
2. 导入Tomcat 目标服务器运行环境
1. 就是导入 Servlet 接口
3. 创建一个类 DemoServlet
1. 向response发送 Hello World

public class DemoServlet implements Servlet{

public void destroy() {
}
public ServletConfig getServletConfig() {
return null;
}
public String getServletInfo() {
return null;
}
public void init(ServletConfig arg0) throws ServletException {
}

public void service(ServletRequest request,
ServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
//将 Hello World 写到response中
response.getWriter().print("Hello World!");
}

}

4. 配置web.xml
1. 将请求路径 /hello 映射到 DemoServlet 上

<!-- 登记 Servlet -->
<servlet>
<servlet-name>demo</servlet-name>
<servlet-class>day01.DemoServlet</servlet-class>
</servlet>
<!-- 将Servlet 映射到 URL 连接 -->
<servlet-mapping>
<servlet-name>demo</servlet-name>
<url-pattern>/hello</url-pattern>
</servlet-mapping>

5. 将web程序部署到 Tomcat 中
1. 部署就是复制, 将web.xml DemoServlet 等复制到Tomcat服务器中.
6. 使用浏览器请求 /hello, 在浏览器中看到 Hello World!

http://localhost:8080/Servlet01/hello

-- 发红包和收红包的人
create table person(id int ,name varchar(10),gender varchar(10),tel bigint);
insert into person values (1,'张三','男',13233380881),(2,'李四','男',13233380882),(3,'李四','女',13233380883),(4,'王五','男',13233380884);
-- 红包信息
create table message(id int ,pid int,date varchar(20),money int);
insert into message values(1,1,'2018.6.24',10),(2,1,'2018.6.25',-20),(3,2,'2018.6.26',-20),(4,3,'2018.6.27',50),(5,4,'2018.6.28',30);

select p.id,name,gender,tel,m.date,m.money
from person p join message m on p.id=m.pid
where p.name='张三';

---------邮箱案例按时唯一标识 unique
create table zmm(id int primary key,name varchar(10) not null,email varchar(100) unique);
insert into zmm values(1,'张玲玲','1158809291@qq.com');
insert into zmm values(2,'张','1158809291@qq.com');

# 回顾

## 约束

数据完整(有效性)检查.

1. 主键
2. 外键
3. 非空
4. 唯一
5. 默认

## 事务

## JDBC

1. Java程序与数据库之间沟通的桥梁.
2. JDBC 是 Java EE 标准的一部分, 是一套接口.
3. 使用JDBC时候, 需要导入由数据库厂商提供的JDBC驱动.
1. JDBC驱动,就是JDBC标准接口的实现类!!!
2. 使用JDBC接口时候, 实际上使用的是JDBC驱动实现类

使用JDBC的步骤:

1. 导入注册JDBC驱动

Class.forName(驱动程序名)

2. 创建数据库连接

Connection conn = DriverManager.getConnection(url, name, pwd);

3. 创建 Statement 对象, 用来执行SQL
- execute DDL
- executeUpdate DML
- executeQuery DQL

4. 执行SQL
5. 处理SQL结果!!!
6. 关闭资源和连接!!!
1. 务必关闭连接!!!

## Properties 文件

Java 提供一套配置文件API, 可以解决软件的参数配置文件问题.

原理为:


使用步骤:

1. 在resource文件夹中创建配置文件 db.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db6?characterEncoding=utf8&useUnicode=true&useSSL=false
username=root
password=root

2. 编写测试类读取配置文件

public class PropertiesDemo02 {

public static void main(String[] args)
throws Exception {
/**
* 利用Properties API 读取配置文件db.properties信息
*/
//利用当前的classLoader 加载resource中的资源
String file="db.properties";
//固定写法:
// 类名.class.getClassLoader().getResourceAsStream()
InputStream in=PropertiesDemo02.class
.getClassLoader()
.getResourceAsStream(file);
Properties cfg = new Properties();
//将资源中信息读取到 Properties 对象中
cfg.load(in);
in.close();
//检查读取结果
System.out.println(cfg);
//获取一个参数
System.out.println(cfg.getProperty("url"));
System.out.println(cfg.get("url"));
}
}

> 读取在resource文件夹中的经典操作是 类名.class.getClassLoader().getResourceAsStream()

3. 测试.

## 封装数据连接

利于工具类封装数据库连接过程, 可以重用数据库连接功能, 简化JDBC编程.

1. 编写DBUtil封装数据库的连接和关闭过程:

/**
* 抽取封装数据库连接/关闭过程, 用于简化数据库编程
*/
public class DBUtil {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
try {
//利用静态代码块初始化静态属性
String file="db.properties";
//configuration:配置信息, 缩写为 cfg
Properties cfg=new Properties();
InputStream in =DBUtil.class.getClassLoader()
.getResourceAsStream(file);
cfg.load(in);
in.close();
driverClass = cfg.getProperty("driverClass");
url = cfg.getProperty("url");
username = cfg.getProperty("username");
password = cfg.getProperty("password");
}catch(IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}

/**
* 封装数据库连接过程, 返回数据库连接对象
* @return 数据库连接对象
* @throws Exception 如果数据库驱动无法注册, 则抛出异常
* 不能连接到数据库
*/
public static Connection getConnection()
throws Exception {
//将数据库驱动程序类名也放到配置文件中
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(
url, username, password);
return conn;
}
/**
* 关闭数据库连接方法
* @param conn 被关闭的数据库连接
*/
public static void close(Connection conn) {
//如果数据库在关闭期间出现意外, 是无需处理的异常
//可以直接忽略!
try {
//检查conn对象是否为null, 避免空指针异常!
if(conn!=null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}

2. 重构插入功能:

public class InsertDemo01 {

public static void main(String[] args) {
/**
* 向数据库插入数据
*/
String sql="insert into t_user values (9,'Nemo')";
Connection conn=null;
try {
//利用数据库连接工具连接到数据库
conn=DBUtil.getConnection();
//创建Statement对象
Statement st = conn.createStatement();
//执行SQL
int n = st.executeUpdate(sql);
//处理结果
System.out.println(n);
st.close();
//st对象可以不关闭, conn关闭时候会自动关闭st对象
}catch(Exception e) {
e.printStackTrace();
}finally {
//可靠关闭数据库的连接!!!
DBUtil.close(conn);
}
}
}

3. 测试.

## ResultSet 结果集

使用executeQuery方法可以执行查询语句, 得到结果集对象ResultSet

结果集对象的读取原理:


案例, 查询数据表:

public class SelectDemo03 {

public static void main(String[] args) {
/**
* 演示JDBC查询功能
*/
String sql="select id, name from t_user";
Connection conn = null;
try {
conn = DBUtil.getConnection();
Statement st=conn.createStatement();
//Result结果, Set集, 结果集对象封装了查询结果
ResultSet rs=st.executeQuery(sql);
//利用while和next方法配合移动 "结果集游标"
while(rs.next()) {
//读取rs当前行中的数据
int id = rs.getInt("id");
String name=rs.getString("name");
System.out.println(id+","+name);
}
rs.close();//可以不关闭rs, conn关闭时候自动关闭rs
st.close();
}catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.close(conn);
}
}

}

## PreparedStatement

预编译的SQL语句的工作原理:

1. 将带有参数的SQL发送到数据库, 编译成执行计划.
2. 设置执行计划的参数值
3. 发送参数, 执行数据库的执行计划


好处:

1. 复用执行计划: SQL固定不变, 数据库端可以重用相同的执行计划, 提高了执行效率
1. SQL改变时候, 数据库会花费高昂的计算成本, 创建执行计划.
2. 可以避免SQL注入!!
1. SQL注入是常见的安全漏洞现象, 原因是用户提交的数据中包含SQL语句成分, 服务器端拼接SQL时候, SQL成分改变了SQL语句执行逻辑, 造成SQL执行了注入攻击逻辑.

> 重要: SQL拼接时候, 不要拼接用户提交的字符串!!!!

插入案例:

public class InsertDemo04 {

public static void main(String[] args) {
/**
* 测试有参数的插入语句
*/
System.out.println(addUser(10, "Jerry"));
System.out.println(addUser(11, "熊大"));
System.out.println(addUser(12, "熊二"));
}
public static int addUser(int id, String name) {
//update t_user set name=? where id=?
String sql="insert into t_user (id, name) "
+ "values (?,?)";
Connection conn = null;
try {
conn = DBUtil.getConnection();
//发送SQL到服务器, 编译执行计划
PreparedStatement ps=
conn.prepareStatement(sql);
//替换执行计划参数
ps.setInt(1, id); //绑定第一个参数
ps.setString(2, name); //绑定第二个参数
//执行编译好的执行计划
int n = ps.executeUpdate(); //不要传递SQL参数!!!
return n;
} catch (Exception e) {
e.printStackTrace();
return 0; //返回0表示插入失败
}finally {
DBUtil.close(conn);
}
}

}

更新案例:

public class UpdateDemo05 {

public static void main(String[] args) {
/**
* 更新数据
*/
System.out.println(updateUser(10, "王克晶"));
System.out.println(updateUser(11, "威震天"));
}
public static int updateUser(int id, String name) {
String sql="update t_user set name=? "
+ "where id=? ";
Connection conn = null;
try {
conn = DBUtil.getConnection();
PreparedStatement ps=
conn.prepareStatement(sql);
ps.setInt(2, id); //绑定第一个参数
ps.setString(1, name); //绑定第二个参数
int n = ps.executeUpdate(); //不要传递SQL参数!!!
return n;
} catch (Exception e) {
e.printStackTrace();
return 0; //返回0表示插入失败
}finally {
DBUtil.close(conn);
}

}

}

查询案例:

public class SelectDemo06 {

public static void main(String[] args) {
/**
* 执行带参数是SQL查询
*/
findUser(10);
findUser(11);
}

public static void findUser(int id) {
String sql="select id, name from t_user "
+ "where id=?";
Connection conn = null;
try {
conn=DBUtil.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int uid = rs.getInt("id");
String name = rs.getString("name");
System.out.println(uid+","+name);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.close(conn);
}

}

}

## SQL注入

SQL注入是常见的安全漏洞现象, 原因是用户提交的数据中包含SQL语句成分, 服务器端拼接SQL时候, SQL成分改变了SQL语句执行逻辑, 造成SQL执行了注入攻击逻辑.

> 重要: SQL拼接时候, 不要拼接用户提交的字符串!!!!


SQL注入案例:

表:

create table users(
id int,
name varchar(50),
password varchar(50)
);

insert into users(id, name, password) values (1, 'Tom', 123);

SQL注入问题案例:

public class LoginDemo07 {

public static void main(String[] args) {
Scanner in = new Scanner(System.in);
while(true) {
System.out.print("输入用户:");
String name = in.nextLine();
System.out.print("输入密码:");
String password = in.nextLine();
if(login(name, password)) {
System.out.println("登录成功!");
break;
}
}
}
public static boolean login(String name,
String password) {
String sql = "select count(*) as c from "
+ "users where name='"+name+"' "
+ "and password='"+password+"'";
Connection conn = null;
try {
conn = DBUtil.getConnection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()) {
int c = rs.getInt("c");
return c == 1;
}
return false;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally {
DBUtil.close(conn);
}
}
}

没有SQL注入问题的案例:

public class LoginDemo08 {

public static void main(String[] args) {
Scanner in = new Scanner(System.in);
while(true) {
System.out.print("输入用户:");
String name = in.nextLine();
System.out.print("输入密码:");
String password = in.nextLine();
if(login(name, password)) {
System.out.println("登录成功!");
break;
}
}
}
public static boolean login(String name,
String password) {
String sql = "select count(*) as c from "
+ "users where name=? and password=?";
Connection conn = null;
try {
conn = DBUtil.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int c = rs.getInt("c");
return c == 1;
}
return false;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally {
DBUtil.close(conn);
}
}
}

## 批量操作

大量SQL操作时候, 使用批量执行可以提升执行效率:


案例1:

public class BatchSQLDemo09 {

public static void main(String[] args) {
/**
* 批量执行SQL
*/
String[] sql = {
"create table t_1(id int, name varchar(50))",
"create table t_2(id int, name varchar(50))",
"create table t_3(id int, name varchar(50))",
"create table t_4(id int, name varchar(50))",
"create table t_5(id int, name varchar(50))",
"create table t_6(id int, name varchar(50))",
"create table t_7(id int, name varchar(50))",
"create table t_8(id int, name varchar(50))",
"insert into t_1(id, name) values (1,'Tom')"
};
Connection conn = null;
try {
conn = DBUtil.getConnection();
Statement st = conn.createStatement();

//Batch 批量
//addBatch 将SQL添加到 st 的缓存中
st.addBatch(sql[0]);
st.addBatch(sql[1]);
st.addBatch(sql[2]);
st.addBatch(sql[3]);
st.addBatch(sql[4]);
st.addBatch(sql[5]);
st.addBatch(sql[6]);
st.addBatch(sql[7]);
st.addBatch(sql[8]);
//executeBatch将一批SQL一起发送到数据库执行
int[] arr = st.executeBatch();
for (int i : arr) {
System.out.println(i);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.close(conn);
}

}

}

create table t_emp(empno int auto_increment primary key,ename varchar(10),mgr int,deptno int,hiredate datetime,salary double, comm double);

insert into t_emp values(null,'tom',0,null,now(),1000,200),(null,'jerry',1,null,now(),2000,200);
insert into score values(null,'tom',0,null,now(),1000,200),(null,'jerry',1,null,now(),2000,200);