# 数据库
# io流文件存储数据的弊端
- 存储效率低
- 不管存还是取操作比较麻烦
- 一般只能保存小量字符串数据
# DB
- date base 数据库
- 什么是数据库： 数据库实际上就是一个文件集合,本质就是一个文件系统,数据按照特定的格式存储到文件中,使用sql语言对数据进行增删改查操作.
# 什么是DBMS
DataBaseManagementSystem:数据库管理系统,管理数据库文件的软件
- 指一种操作和管理数据库的大型软件,用于建立,使用和维护数据库,对数据进行统一的管理和控制,用户通过DBMS访问数据库中的数据 常见:mysql oracle db2 sqlserver sqlite ...
# 数据库的分类
- 关系型数据库:经过数学理论验证,可以将现实生活中存在的各种关系,保存到数据库中,这种数据库称为关系型数据库,在此数据库中,以表的形式保存数据之间的关系
- 非关系型数据库: 主要为了解决特定的应用场景,如:缓存,高并发访问等,存储数据的方式有多种,redis是常见的非关系型数据库,redis是以键值对的形式保存数据.
# 常见数据库介绍
- mysql: oracle公司,08年被sun收购 09 sun被oracle收购 ,开源,市场占有最高 被oracle收购后出了5.5版本由于使用了oracle核心技术,性能有了大幅提高,导致oracle用户流失,公司决定将mysql闭源,导致原mysql程序员离开并创建了mariaDB,maria创始人的女儿名字
- oracle: oracle公司,性能最强大的数据库,而且收费最贵,通常不缺钱却技术的公司会选用,市场排名第二
- DB2: IBM公司,闭源收费,通常一些银行项目会使用
- sqlserver: 微软公司,排名第三,闭源收费,提供整套解决方案(web服务器,操作系统,数据库服务器等)
- sqlite:轻量级数据库,应用在嵌入式或移动设备中,大小只有几十k,功能和性能较大型数据库要少很多 ##开源和闭源
- 开源: 公开源代码,免费, 大牛程序员会维护和升级 弊端:通过卖服务赚钱,来钱比较慢
- 闭源: 不公开源代码,收费(卖产品) 弊端:大牛程序员会攻击找漏洞
# mysql安装
- 参见mysql安装文档,注意两点:1. 端口号选择默认的3306
- 字符集选择utf-8
# 什么是数据库服务器
服务器:一台高配置电脑 ftp服务器:安装了ftp服务软件提供了ftp服务功能的高配置电脑 邮件服务器:安装了邮件服务的软件 供了邮件服务功能的高配置电脑 数据库服务器:安装了数据库服务的软件 供了数据服务功能的高配置电脑 web服务器:安装了web服务软件的高配置电脑
# 连接数据库
- 打开终端或命令行 在终端中输入以下命令: mysql -uroot -p然后敲回车,然后再敲回车
- 退出指令: exit;
# 什么是sql
Stuctured Query Language: 结构化查询语言,使用sql语言和数据库服务器进行交互,通过sql告诉数据库服务器对数据进行什么操作.
# sql规范
- 以;(分号)结尾
- 关键字之间有空格,通常只有一个,但多个也可以
- 可以存在换行
- 数据库名称和表名称区分大小写
# 数据库相关的sql
- 查看所有数据
show databases; - 创建数据库
- 格式:
create database 数据库名称;
- 查看数据库详情
- 格式
show create database 数据库名;
- 创建数据库指定字符集
- utf8或gbk
create database db2 character set utf8;
- 删除数据库
drop database db2;
- 使用数据库
use db1;
# 和表相关的SQL
- 什么是表：关系型数据库中保存数据的单元，类似于excel中的表格，创建表时需要指定字段信息
- 创建表
- 格式：create table 表名(字段1名 字段1类型，字段2名 字段2类型);
create table person(name varchar(10),age int);
- 练习1:创建一个员工表(emp) 有名字(ename)和工资(sal)
create table emp(ename varchar(10),sal int);
- 练习2:创建一个学生表(student)有学号(id)姓名(name)性别(gender)语文(chinese)数学(math)英语(english)
create table student(id int,name varchar(10),gender varchar(5),chinese int,math int,english int);
- 查询所有表
show tables;
- 查看单个表属性
show create table 表名;
# 表的引擎
Myisam:只支持数据基础的增删改查，不支持高级操作，如:事务，外键盘等
InnoDB：支持高级操作，默认为InnoDB
创建表并且指定引擎和字符集
create table t1(id int, name varchar(10)) engine=myisam charset=gbk;
- 查看表字段信息
desc 表名;
# 练习： 创建5个数据库 每个里面创建一张表，第三个数据库字符集gbk,里面的表引擎为myisam，创建完后全部删除
- 对创建好的表进行修改
- 修改表名
rename table 原名 to 新名；
rename table student to t_stu;
create table hero(name varchar(10));（创建一个新表，因为有的同学删除了以前的表）
修改表属性
alter table hero engine=myisam charset=gbk;添加表字段
- 最后位置添加
alter table hero add age int; - 最前面添加
alter table hero add money int first; - 在某个字段的后面添加
alter table hero add gender varchar(5) after name;
- 最后位置添加
删除表字段
alter table hero drop money;修改表字段的名和类型
alter table hero change 原字段名 新字段名 类型；
alter table hero change name heroname varchar(10);
- 修改表字段的类型和位置
alter table hero modify age int first;
alter table hero modify ageint int after XXX;
- 删除表
drop table hero;
# 数据相关的sql
# 插入数据
- 创建表
create table student(id int, name varchar(10),chinese int, math int, english int);
- 插入数据，全表插入: 每个字段都赋值，顺序要和表字段一致
insert into 表名 values();
insert into student values(1,'zhangsan',88,38,98);
- 指定字段插入
insert into student (id,name) values(2,'张飞');
# 批量插入
- 全表批量插入
insert into student values(6,'唐僧',56,57,58),(7,'八戒',88,89,99);
- 指定字段插入
insert into student (id,name) values(8,'吕布'),(9,'貂蝉'),(10,'孙尚香');
# 查询
- 查询全部数据的全部字段信息
select * from student; - 查询指定字段
select id,name from student; - 条件查询
select * from student where id<5;
# 修改数据
update student set math=100;
update student set english=30 where id=7;
# 删除数据
- 按条件删除
delete from student where id=7; - 全部删除
delete from student;
# 主键约束
- 给主键添加约束，起到非空并且唯一的作用，主键指表示数据唯一性的字段，一张表中只有一个主键
- 如何使用：
create table t1 (id int primary key,name varchar(10));
- 测试： 一下代码第二行报错因为id重复了
insert into t1 values(1,'李白');
insert into t1 values(1,'杜甫');
- 测试: 第一行mariaDB报错，在linux上mysql会插入一个0,第二行会报错，id值不能为null;
insert into t1 (name) values('黄忠');
<!--ERROR 1364 (HY000): Field 'id' doesn't have a default value-->
insert into t1 values(null,'张三');
<!--ERROR 1048 (23000): Column 'id' cannot be null-->
- 主键约束+自增
create table t2(id int primary key auto_increment, name varchar(10));
- 测试：
insert into t2 values (null,'貂蝉');
insert into t2 values (null,'小乔');
insert into t2 (name) values('吕布');
insert into t2 values (5,'魏延');
- 自增总结
- 表内自增的数值 只增不减
- 清空表的数据 数值不归零 继续增长
- 数值以出现过的最大值的基础+1
# 注释 comment
- 注释 可以在创建表添加字段的时候对字段进行介绍，便于以后查看表的时候知道每个字段的作用
- 如何使用:
create table t3(id int primary key auto_increment comment '这是个主键', name varchar(10), comm int comment '这是奖金');
show create table t3;
# ` '区别
- `的作用是用来修饰表名和字段名，可以省略
- '的作用是用来修饰字符串的
# 数据冗余
- 什么是冗余：如果设计表不够合理，随着数据量的增多，出现大量的重复数据，称为数据的冗余。
# 练习
- 创建商品表 price单价，num库存，category_id分类id
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));
- 插入苹果手机 价格5888 库存200， dell电脑价格4000, 库存150，联想电脑价格3500， 库存100。
insert into item (name,price, num,category_id) values('苹果手机',5888,200,1);
- 插入手机和电脑分类
insert into category values(null,'手机'),(null,'电脑');
- 给分类表添加上级id的字段
alter table category add parent_id int;
- 插入一条办公耗材分类下的打印机分类下的惠普打印机价格2300 库存20
insert into category values(null,'办公耗材',null);
insert into category values(null,'打印机',3);
insert into item values(null,'惠普打印机',2300,20,4);
# 事务
- 事务是数据库中执行sql语句中的最小工作单元
- 如何开启事务：关闭客户端自动提交，改成手动提交，把多次修改数据库的sql放在一次提交中，则多次操作数据库的sql就相当于是放到了同一个事务中
- 数据库的默认提交方式是自动提交
- 没有事务的话，语句是挨条执行，如果第一次提交中断(断电)则数据库中的数据会异常
1. 超人+300 提交
2. 蝙蝠侠-300 提交
- 有事务的过程
1. 关闭自动提交
2. 超人+300
3. 蝙蝠侠-300
4. 手动提交
提上代码就算中间异常中断，结果要么同时成功，要么同时失败
- 为什么使用事务？
如果不使用事务，客户端会自动提交，在多次操作数据库做一件事的时候（如：转账）会出现部分成功，部分失败，则数据会出现异常。使用事务，把多次操作数据库的sql合并到一次提交中，这样就能保证同时成功或失败。 - 使用事务的执行过程？
- 关闭自动提交
- 执行多次sql（在内存中执行）
- 手动提交
# 查看客户端自动提交状态
show variables like '%autocommit%';
# 关闭自动提交 仅对当前窗口生效
- 关闭
set autocommit=0; - 打开
set autocommit=1;
# 验证转账流程
- 创建表
create table person(id int primary key auto_increment, name varchar(10),money int); - 插入数据
insert into person values(null,'超人',200),(null,'蝙蝠侠',10000); - 关闭自动提交
- 转账
update person set money=500 where id=1;
update person set money=9700 where id=2;
- 打开新的窗口 验证是否转账成功！数据没变，以为两次操作都是在内存中操作，并未提交
- 手动提交
commit;
# 数据回滚
- 执行rollback会将数据回滚到上次提交的点
rollback;
# 设置回滚点
- 保存回滚点：savepoint s1(标识)；
- 回滚到指定的回滚点: rollback to s1;
# SQL分类
# DDL
- data Definition Language 数据定义语言,包括：create,alter,drop,truncate，不支持事务。
- truncate 删除表 并且创建一个新表
truncate table t1;
# DML
- Data Manipulation Language 数据操作语言，包括：insert, update, delete, select(DQL),支持事务
# DQL
- Data Query Language数据查询语言，包括：select,跟事务没关系
# TCL
- Transaction COntrol Language: 事务控制语言，包括：commit,rollback,savepoint
# DCL
- Data Control Language:数据控制语言，分配用户权限相关的sql
# truncate, delete, drop的区别
- delete：删除表中的数据，自增数值不清零，支持事务
- drop：删除表，不支持事务
- truncate:删除表并且创建一个新表，自增数值清零，不支持事务
# 数据库的数据类型
- 物种数据类型分别为：
- 整数
- 浮点数
- 字符串
- 日期
- 其他
# 整数
- 常用：int(m) bigint(m), m代表显示长度，如果数据长度不足m时会在数值前面补0，但是必须和zerofill关键字结合使用，使用方式如下：
例如： create table t_int(num int(10) zerofill);
insert into t_int values(25);
select * from t_int;
# 浮点数
- 常用：
- double(m,d), m代表总长度，d代表小数长度,超过小数长度四舍五入，不足补零
2. decimal(m,d), m代表总长度，d代表小数长度，超高精度小数，需要设计超高精度运算的时候使用。
# 字符串
- char(m): m代表字符长度，固定长度，执行效率高（不用改变长度），最大长度255，
- varchar(m): 可变长度，会根据内容长度改变自身长度，更节省资源，最大长度65535，如果长度超过255建议使用text。
- text: 可变长度，最大值65535。
# 日期
- 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('2018-05-16',null,null,null);
insert into t_date values(null,'12:32:18','2018_05-11 18:30:08',null);
在数据库中先导入day03中的两个文件，直接复制粘贴
# is null
- 查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
- 查询emp表中没有奖金(comm)的员工姓名，工资和奖金
select empno,ename,sal from emp where comm is null;
# is not null 不为null
- 查询emp表中有奖金的员工
select ename,sal,comm from emp where comm is not null;
# 别名，还有多表联查的作用
- 查询emp中所有姓名 将ename 显示成'姓名'
select ename as '姓名' from emp; select ename '姓名' from emp; select ename 姓名 from emp;
# 去重
- 查询emp表中出现的所有职位
select distinct job from emp;
# 比较运算符 >, <, >=, <=, =, !=和<>
- 查询工资小于等于1600的所有员工姓名和工资
select ename,sal from emp where sal<=1600; - 查询部门编号是20的所有员工姓名，职位和部门编号
select ename,job,deptno from emp where deptno=20; - 查询职位是manager的所有员工姓名和职位
select ename,job from emp where job='manager'; - 查询部门不是10号部门的所有员工姓名和部门编号使用两种方式实现
select ename,deptno from emp where deptno!=10;select ename,deptno from emp where deptno<>10; - 查询t_item表中单价等于23的商品
select id,title from t_item where price=23; - 查询t_item表中单价不等于8443的商品的信息
select id,title from t_item where price!=8443;
# and和or
- and和Java中的&&效果一样
- or和java中的||效果一样
- 查询不是10号部门并且工资小于3000的员工信息
select * from emp where deptno!=10 and sal<3000;- 查询部门编号为30或者上级领导为7698的员工姓名，职位，上级领导和部门编号
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;
# in
- 查询emp表中工资为5000，1500，3000的员工信息
select * from emp where sal in(5000,1500,3000);
# between x and y 在x和y之间(包括x和y)
- 查询emp表中，工资在2000至4000之间的员工信息
select * from emp where sal between 2000 and 4000;
# like
- _: 代表单个未知字符
- %： 代表0个或多个未知字符
- 举例：
- 包含字符a: %a%
- 以a开头：a%
- 以a结尾：%a
- 第二个字符是a：_a%
- 倒数第三个字符是a：%a__
- 第二个字符是a,最后一个字符是b：_a%b
- like 案例
- 查询标题中包含记事本的商品信息
select id,title,price from t_item where title like '%记事本%';- 查询单价低于100的记事本标题和单价
select title,price from t_item where title like '%记事本%' and price<100;- 查询单价在50到200之间的得力商品
select * from t_item where title like '%得力%' and price between 50 and 200;- 查询有图片(image字段不为null)的得力商品
select id,title,price,image from t_item where image is not null and title like '%得力%';- 查询分类(category_id)为238,917的商品信息
select id,title,price,category_id from t_item where category_id in(238,917);- 查询含有赠品的商品信息（sell_point中包含赠字）
select id,title,price,sell_point from t_item where sell_point like '%赠%';- 查询标题中不包含得力的商品标题
select * from t_item where sell_point not like '%得力%';- 查询价格介于50到200之外的商品信息
select * from t_item where price not between 50 and 200;
# 排序 order by
- order by 写在where查询要求后面，没有where写在最后
- by的后面写排序的字段名称
- 默认排序是升序，也可指定升序：asc；降序desc
- 查询所有员工的姓名和工资，按照工资降序排序
select ename,sal from emp order by desc;- 查询所有dell商品，按照单价降序排列
select * from t_item where title like '%dell%' order by price desc;- 查询所有员工信息按照部门编号升序排序，工资按照将序排序
select * from t_item order by deptno,sal desc;
# 分页查询 limit
- limit 跳过数量，每页的数量
- limit关键字通常写在sql的最后面
- 查询所有商品 按照单价升序排序，显示第二页，每页7条数据
select id,litle,price from t_item order by price desc limit 7,7;- 查询工资金额前三名的三位员工信息
select * from emp order by sal desc limit 0,3;
# 数值计算 + - * / % 7%2等效mod(7,2)
- 查询员工姓名，工资，及年终奖信息（年终奖=工资*5);
select ename,sal,sal*5 年终奖 from emp; - 查询t_item表中商品单价，库存和总金额（单价*库存）
select ename,price,num,price*num 总金额 from t_item;
# 日期相关函数
select 'helloworld';
- 获取当前日期+时间 now();
select now(); - 获取当前日期 curdate();
select curdate() - 获取当前的时间 curtime();
select curtime() - 从年月日时分秒中，提取年月日和提取时分秒
- 提取年月日
select date(now()); - 提取时分秒
select time(now());
- 提取年月日
- 从年月日时分秒中提取年，月，日，时，分，秒
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()); - 日期格式化函数
- 格式：date_format(date,format);
- format: %Y四位年，%y两位年，%m两位月，%c一位月份，%d日，%H 24小时，%h 12小时，%i 分，%s 秒；
- 把now()转换成2018年06月19日 16时27分30秒的格式
select date_format(now(),'%Y年%m月%d日%H时%i分%s秒'); - 把非标准格式的时间转换成标准格式
str_to_date(非标准格式的时间，非标准时间格式);- 把14.08.2008 08:00:00 转换成标准格式
select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');
# ifnull()函数
- age=ifnull(x,y) 如果x的值为null则age=y,如果x不为null，则age的值为x
- 将emp表中的奖金为null的全部修改为0
update t_item set comm=ifnull(null,0);
# 聚合函数
- 对多行数据进行统计：
- 求和：sum(字段名)
select sum(sal) from emp;emp中工资总和 - 平均值：avg(字段名)
select avg(sal) from emp where deptno=10; - 最大值：max(字段名)
select max(sal) from emp where deptno=10; - 最小值：min(字段名)
select min(sal) from emp where deptno=10; - 统计数量：count(*)
select count(*) from emp where sal<1000;
# 字符串相关函数
- 字符串拼接 concat(s1,s2) s1s2
- 查询emp表中，员工姓名和工资，工资后面有单位元
select ename,concat(sal,'元') from emp; - 获取字符串的长度 char_length(str)
- 查询员工的姓名和名字的长度
select ename,char_length(ename) 名字长度 from emp; - 获取字符串在另外一个字符串中出现的位置(位置从1开始)
- 格式1：instr(str,substr)
select instr('abcdefg','d'); - 格式2：
select locate('d','abcdefg');
- 格式1：instr(str,substr)
- 插入字符串(位置从1开始)
- 格式：
insert(str,start,length,newstr);
select insert('abcdefg',3,2,'m');//abmefg - 格式：
- 转大写转小写
- 格式： upper(str) lower(str)
select upper('Nba'),lower('NBA');//NBA,nba - 从左边截取和从右边截取
- 格式：left(str,count) right(str,count)
select left('abcdefg',2);//ab select right('abcdefg',2);//fg - 去除字符串两端的空格
select trim(' ab ');//ab - 获取字符串()
select substring('abcdefg',2);//从2开始截 select substring('abcdefghij',4,3);//def(这里的3代表长度) - 重复 repeat(str,count);(内容，次数)
select repeat('ab',2);//abab - 替换replace(str,old,new)
select replace('abcde','c','m');//abmde - 反转 reverse(str)
select reverse('abc');//cba
# 数学相关的函数
- floor(num) 向下取整
select floor(3.84);//3 - round(num) 四舍五入
select round(23.8);//24 - round(num,m) 四舍五入，m代表小数位数
select round(23.869,2);//23.87 - truncate(num,m) 和上面一样,(非四舍五入)
select truncate(23.869,2);//23.86 - rand() 随机数，获取0-1的随机数
select rand();- 获取0-5的随机数
select floor(rand()*6); - 获取3-8的随机数
select floor(rand()*6)+3; - 获取8-10的随机数
select floor(rand()*3)+8;
- 获取0-5的随机数
# 分组查询
- 分组查询通常和聚合函数结合使用
- 一般情况下 每个部门（职位，分类）就以部门（职位，分类）作为分组的条件
- 可以有多个分组条件
- group by 存在的位置 where...group by...order by...limit...
- 查询每个部门的最高工资
select deptno,max(sal) from emp group by deptno;- 查询每个职位的平均工资
select job,avg(sal) from emp group by job;- 查询每个部门下每个主管的手下人数
select deptno,mgr,count(mgr) from emp group by deptno,mgr;
- select deptno,count(),sum(sal) from emp group by deptno order by count(),sum(sal) desc;
- select deptno,sum(sal),avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal);
- select job,count(*),sum(sal) 总和,avg(sal) 平均,min(sal) from emp where mgr is not null group by job order by 总和 desc, 平均;
#
- 查询每个部门的平均工资，要求平均工资大于2000；
- 以下的错误语法： where后面不能写聚合函数
select deptno,avg(sal) a from emp where a>2000 group by deptno;
- 使用having 解决聚合函数的条件过滤问题，having写在group by 后面
- where后面写普通字段的过滤条件，having后面写聚合函数的过滤条件
select deptno,avg(sal) a from emp group by deptno having a>2000;- 查询每个分类商品的库存总量，高于1000的库存总量
select category_id,sum(num) from t_item group by category_id having a>19999;- 查询每个分类商品所对应的平均单价，要求平均单价低于100
select category_id,avg(price) from t_item group by category_id having avg(price)<100;- 查询分类id为238和917两个分类的平均单价
select category_id,avg(price) 单价 from t_item where category_id in(238,917) group by category_id;- 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
select deptno,count(*),avg(sal) a from emp group by deptno having avg(sal)>2000 order by a desc;- 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资升序排序
select deptno,count(*),sum(sal),avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000 order by avg(sal);- 查询emp表中名字不是以s开头，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序排序，如果一致根据工资总和降序排序
select job,count(*),sum(sal),avg(sal) from emp where ename not like 's%' group by job having avg(sal)!=3000 order by count(*),sum(sal) desc;- 查询emp表，每年入职的人数(提高题)
select extract(year from hiredate),count(*) from emp group by extract(year from hiredate);
# 子查询(嵌套查询)
# 子查询总结
- 嵌套在sql语句中的查询语句称为查询语句
- 子查询能嵌套n层
- 子查询可写位置
- 可以写在where/having的后面作为查询条件的值
- 可以写在from后面当作一张新表 新表必须有别名
- 可以写在创建表的时候
create table t_emp_10 as (select * from emp where deptno=10);
- 查询emp表中工资最高的员工信息
select * from emp where sal=(select max(sal) from emp); - 查询emp表中工资超过平均吸纳的所有员工的信息
select * from emp where sal>(select avg(sal) from emp); - 查询工资高于20号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp where deptno=20); - 查询和jones形同工作的其他员工信息
select * from emp where job=(select job from emp where ename='jones') and ename!=jones; - 查询工资最低的员工的相同部门的员工信息
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)); - 查询最后入职的员工信息
select * from emp where hiredate=(select max(hiredate) from emp); - 查询姓名为king部门编号和部门名称(需要使用dept表)
select deptno,dname from dept where deptno=(select deptno from emp where ename='king'); - 查询有商品的分类id和分类名称（有商品 就是在商品中出现的分类，需要使用t_item_category表）
- 先从商品表中得到所有的分类id select distinct category_id from t_item; - 从分类表中查询id等于上面结果的分类信息 select id,name from t_item_category where id in(select distinct category_id from t_item); - 查询有员工的部门信息
select * from dept where deptno in(select distinct deptno from emp); - 扩展题(难度最高)：查询平均工资最高的部门信息
- 得到最高的平均工资
select deptno,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(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 e.ename,d.dname from emp e,dept d where e.ename=d.ename; - 查询在纽约工作的所有员工的信息
select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
# 笛卡尔积
- 关联查询如果不写关联关系，则查询结果为两张表的乘积，这个乘积称为笛卡尔积
- 笛卡尔积是一种错误的查询结果，工作中切记不要出现
# 等值链接和内部连接
- 等值连接：
select * from A,B where A.x=B.x and A.age=18; - 内连接 用的更多
select * from A join B on A.x=B.x where A.age=18;
- 查询每个员工的姓名和对应的部门名称
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
# 外连接
- 左外连接：以join左边表为主表，左边表显示所有数据，右边交集数据
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno; - 右外连接：以join右边表为主表，右边表显示所有数据，左边交集数据
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
# 关联查询总结
- 查两个表的交集数据，使用内连接（推荐）或等值连接
- 查一个表所有数据另外一个表交集数据使用外连接
# 表设计 关联关系
- 外键：用来建立关系的字段称为外键
- 主键：用来表示数据唯一性的字段称为主键
# 一对一
- 什么是一对一：两张表a表和b表，a表中一条数据对应b表中的一条数据，这种关系称为一对一的关系
- 应用场景：用户表和用户信息扩展表，商品表和商品信息表扩展表
- 如何建立关系：在从表中添加一个外键字段值指向主表的主键字段
- 创建两张表
create table user(id int primary key auto_increment,name varchar(10),password varchar(10)); create table userinfo(userid int primary key,nick varchar(10),tel varchar(15),address varchar(20)); - 往表中添加数据
insert into user values(null,'wukong','123456'),(null,'bajie','123456'),(null,'libai','123456'); insert into userinfo values(1,'齐天大圣','13833446622','花果山'),(2,'二师兄','13833446622','高老庄'),(3,'李白','13833446622','语文书里');
- 查询李白的用户名和密码是什么
select u.username,u.password from user u join userinfo ui on u.id=ui.userid - 查询每一个用户的所有信息
select * from user u join userinfo ui on u.id=ui.userid; - 查询用户名bajie的昵称是什么
select ui.nick from user u join userinfo ui on u.id=ui.userid where u.name='bajie';
# 一对多
- 什么是一对多：ab两张表，a表中一条数据对应b表中多条数据，b表中一条数据对应a表中一条数据，称为一对多
- 应用场景：部门和员工，分类和商品等
- 如何建立关系： 一对多的两张表，在多的表里面添加外键盘记录另外一个表的主键值
# 练习
- 创建t_emp(id,name,deptid) 和 t_dept(id,name,loc)
create table t_emp(id int primary key auto_increment, name varchar(10), deptid int); create table t_dept(id int primary key auto_increment, name varchar(10), loc varchar(10)); - 表中插入数据 部门表：
insert into t_emp values(null,'悟空',1),(null,'刘备',2),(null,'路飞',3),(null,'八戒',1); insert into t_dept values(null,'神仙部门','花果山'),(null,'三国部', '蜀国'),(null,'海贼部','日本');
- 查询每个员工的姓名和部门
select e.name,d.name from t_emp e join t_dept d on e.deptid=d.id; - 查询工作在花果山的员工
select e.name from t_emp e join t_dept d on e.deptid=d.id where d.loc='花果山';
# 多对多
- 什么是多对多：AB两张表，A表中的一条对应B表多条数据，同时B表中的一条数据对应A表的多条数据称为多对多关系。
- 应用场景：老师和学生，用户表和角色表
- 如何建立关系：需要创建单独的关系表，关系表中两个外键字段保存两个表的主键值。
# 练习：
- 创建三个表：student(id,name) teacher(id,name) t_s(sid,tid)
create table student(id int primary key auto_increment, name varchar(10)); create table teacher(id int primary key auto_increment,name varchar(10)); create table t_s(tid int,sid int); - 插入以下数据，苍老师和对应的学生：小明，小红，小花，刘老师对应的学生 小绿，小蓝，小明；
insert into teacher values(null,'苍老师'),(null,'刘老师'); insert into student values(null,'小明'),(null,'小红'),(null,'小花'),(null,'小绿'),(null,'小蓝'); insert into t_s values(1,1),(2,1),(3,1),(4,2),(5,2),(1,2); - 查询每个老师对应的所有学生
select t.name,s.name from student s join t_s ts on s.id=ts.sid join teacher t on t.id=ts.tid; - 查询小明的老师都有谁
select t.name from student s join t_s ts on s.id=ts.sid join teacher t on t.id=ts.tid where s.name='小明'; - 查询苍老师的所有学生
select s.name from student s join t_s ts on s.id=ts.sid join teacher t on t.id=ts.tid where t.name='苍老师';
# 自关联
- 什么是自关联：在表中有一个外键字段指向自己表的主键值，称为自关联
- 应用场景：需要保存层级关系，但是不确定有多少层的时候使用自关联
- 查询员工姓名和对应的上级主管姓名
select e.ename,m.ename from emp e join emp m on e.empno=m.mgr;
# 连接方式和关联关系
- 连接方式：包括内连接和外连接（左外，右外），是关联查询的查询方式。
- 关联关系：包括一对一，一对多，多对多，是指设计表时两个表之间存在的逻辑关系。
# 案例：权限管理的设计
- 用户表 角色表 权限表
- 用户-角色关系表 角色—权限关系表
创建db4并使用，创建五张表
create table user(id int primary key auto_increment,name varchar(10)); create table role(id int primary key auto_increment,name varchar(10)); create table module(id int primary key auto_increment,name varchar(10));创建关系表
create table u_r(uid int,rid int); create table r_m(rid int,mid int);插入数据
insert into user values(null,'刘德华'),(null,'张学友'),(null,'凤姐'); insert into role values(null,'男游客'),(null,'男会员'),(null,'女游客'),(null,'女管理员'); insert into module values(null,'男浏览'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖'),(null,'女删帖');插入关系数据
insert into u_r values(1,1),(2,2),(3,4),(3,3); insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);查询每个用户对应的权限
select u.name,m.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id;查询张学友的所有权限
select u.name,m.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where u.name='张学友';查询男发帖的用户都有哪些
select u.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where m.name='女发帖';- 创建两张表
- 创建trade交易流水表(id,time,money,type,pid)
create database db6; use db6; create table trade(id int primary key auto_increment, time date,money int,type varchar(5),pid int); - 创建pwerson人物表(id,name,gender,rel)
create table person(id int primary key auto_increment, name varchar(10),gender varchar(5),rel varchar(5));
- 插入数据
insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友'); insert into trade values (null,'2018-03-20',-20,'微信',1), (null,'2018-04-20',500,'现金',2), (null,'2018-05-20',-50,'现金',2), (null,'2018-03-21',50000,'支付宝',3), (null,'2018-03-22',-5,'支付宝',3), (null,'2018-03-26',2000,'微信',4), (null,'2018-04-22',-20000,'微信',5); - 统计2018年2月15号到现在的所有红包收益
select sum(money) from trade where time>str_to_date('2018年04月21号','%Y年%c月%d号'); - 查询2018年2月15号到现在金额大于100所有女性亲戚的名字和红包总额
select p.name,t.money from trade t join person p on t.pid=p.id where p.gender='女' and t.money not between -100 and 100 and t.time>str_to_date('2018年02月15号','%Y年%c月%d号') and p.rel='亲戚'; - 查询三个平台分别收入的红包总额
select type,sum(money) from trade where money>0 group by type;
# 视图
- 什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一个虚拟的表，视图本质就是取代了一段sql语句
- 为什么使用视图：因为有些数据的查询需要使用大量的sql语句，每次书写比较麻烦，使用视图可以起到sql重用的作用，可以隐藏表中的敏感信息。
- 如何使用：
- 格式：create view 视图名 as 子查询;
- 练习1:创建部门是20并且工资小于3000的视图
create view v_emp_20 as (select * from emp where deptno=20 and sal<3000); - 练习2:创建每个部门的工资总和，平均工资，最大工资，最小工资的视图
create view v_emp_group as ( select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
# 视图的分类
- 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询的视图称为简单视图，可以对数据进行增删改查操作。
- 复杂视图：创建视图的子查询中包含：去重，函数，分组，关联查询的视图称为简单视图，只能进行查询操作
# 简单视图的增删改操作 操作方式和table一样
- 插入数据,如果插入的数据在视图中不显示但是原表中显示，称为数据污染
insert into v_emp_10 (empno,ename) values(10010,'Tom');- 正确写法
insert into v_emp_10 (empno,ename,deptno) values(10086,'jerry',10);- 可以通过with check option 关键字禁止出现数据污染
create view v_emp_30 as (select * from emp where deptno=30) with check option;- 测试：
insert into v_emp_30(empno,ename,deptno) values(10011,'张三',30); - 修改数据,只能修改视图中存在的数据
update v_emp_30 set ename='zhangsan' where empno=10011;（成功) update v_emp_30 set ename='汤姆' where empno=10010;(失败); - 删除数据 只能删除视图中存在的数据
delete from v_emp_30 where empno=10011;(成功) delete from v_emp_30 where empno=10010;(失败)
# 修改视图
- 格式：create or replace view 视图名 as 子查询;
create or replace view v_emp_10 as(select * from emp);
# 删除视图
drop view v_emp_10;
drop view if exists v_emp_10;没有也不会报错
# 视图字段别名
- 创建视图的时候子查询对字段起了别名，则后期对视图进行操作只能使用别名。
create view v_emp_10 as (select empno,ename name from emp);
- 测试：
update v_emp_10 set name='汤姆' where empno=10010;(成功) update v_emp_10 set ename='汤姆aa' where empno=10010;(失败)
# 约束
- 什么是约束：约束是给表字段添加的限制条件
# 非空约束 not null
- 添加的字段值为null
create table t1(id int,age int not null); insert into t1 values(1,20);(正确) insert into t1 values(2,null);（报错）
# 唯一约束 unique
- 字段值不能重复
create table t2(id int,age int unique); insert into t2 values(1,20);(成功) insert into t2 values(2,20);(失败)
# 主键约束 primary key
- 字段值不能重复也不能为null
- 创建表的时候添加主键
create table t_pri(id int primary key auto_increment); - 创建表后添加主键
create table t_pri2(id int); alter table t_pri2 add primary key(id); - 删除主键，一个表只能有一个主键
alter table t_pri2 drop primary key;
# 自增
- 当字段的值为null的时候数值自动+1
- 删除数据数值不减
- 如果插入数据指定比较大的值，下次从最大值的基础上+1，如果删除最大值，也是从曾经出现的最大值基础上+1
- 使用delete删除全表数据，自增值不变
- 使用truncate
# 默认约束 default
- 当字段的值为不赋值 的时候默认约束的内容生效
create table t_def(id int,age int default 10); insert into t_def values(1,2); insert into t_def(id) values(2);
# 检查约束 check
- mysql语法支持，不报错，但是没有效果
create table t_check(id int,age int check(age>10));
# 外键约束
- 作用：
- 外键值可以为null，可以重复，但是不能是不存在的值
- 外键指向的表，不能先删除
- 外键指向的数据不能先删除，如果需要删除先删除外键约束，或先删除外键的数据(断开关联关系)
- 使用外键必须要两张表相同的InnoDB引擎,myisam不支持外键约束
- 除非特定情况 一般工作中不使用外键约束，使用Java代码通过逻辑对插入和删除的数据进行限制，因为加了约束后不方便测试。
# 如何使用外键约束
- 创建部门表
create table dept(id int primary key auto_increment, name varchar(10)); - 创建员工表
constraint 外键约束名 foreign key(外键字段) references 表名(主键)create table emp(id int primary key auto_increment, name varchar(10), deptid int, constraint fk_dept foreign key(deptid) references dept(id));- 插入数据
insert into dept values(null,'神仙部'),(null,'妖怪');- 测试：
insert into emp values(null,'悟空',1);(成功) insert into emp values(null,'赛亚人',3);(失败) drop table dept;()失败 delete from dept where id=1;(失败)
# 索引
# 导入数据
在db6数据库下 执行source 命令，将文件拖入，自动补全路径；
测试：
- show tables; 看是否有item2的表
- select count(*) from item2; 看是否有172万
- select * form item2 where title='100'; 看查询时间是多少
- 什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
- 为设么使用索引：如果不使用索引数据会零散的保存在磁盘块中，查询数据需要挨个的遍历每一个磁盘块，知道找到数据为止，使用索引会在磁盘中以树状结构对数据进行保存，查询数据时会大大降低磁盘块访问量，从而提高查询效率。
# 索引是越多越好吗？
索引会占用磁盘空间，只对常用的查询字段创建索引
# 有索引就一定好吗？
不一定，如果数据量比较小，使用索引反而会降低查询效率
# 索引的分类
- 聚集索引：一张表只有一个聚集索引，数据库会自动为添加了主键的表创建聚集索引，一般情况下聚集索引就是通过主键值创建的索引，聚集索引的树状结构中保存了数据
- 非聚集索引：一张表可以有多个非聚集索引，通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据，保存的是磁盘块的地址
# 如何创建索引
- 格式： create index 索引名 on 表名(字段名(长度)); (一般不写长度)
- 创建title索引
create index index_item2_title on item2(title); - 测试：
select * from item2 where title='100';
# 查看索引
show index from item2;
# 删除索引
drop index index_item2_title on item2;
# 复合索引
- 通过多个字段创建的索引称为复合索引
- 应用场景：频繁使用多个字段作为查询条件时，可以为这几个字段创建一个复合索引
- 创建格式：
create index index_item2_title_price on item2(title,price);
# 索引总结
- 索引是用来提高查询效率的技术，类似目录的作用
- 因为会占用空间不是越多越好
- 数据量小时，会降低查询效率
- 聚集索引自动通过主键创建，保存数据，只有一个， 非聚集索引，有多个，没有数据只有磁盘块地址
- 尽量不要在频繁修改的表上创建索引
# 事务
- 数据库汇总执行sql语句中的最小工作单月，不能拆分，执行同一业务的sql语句可以保证全部成功或全部失败
# 事务的ACID特性 面试常考
- Atomicitu:原子性, 最小不可拆分，全部成功或全部失败
- Consistency:一致性, 从一个一致状态到另一个一致状态
- Isolation:隔离性, 多个事务之间互不影响
- Durability:持久性, 事务完成后数据提交到数据库中持久保存
# 事务相关指令
- 查看自动提交状态 show variables like '%autocommit%';
- 设置自动提交状态 set autocommit=0/1;
- 提交 commit
- 回滚 rollback
- 保存回滚点 savepoint s1;
- 回滚到某个回滚点 rollback to s1;