MySQL数据库完整笔记
MySQL
基本逻辑结构
Mysql
1.通过（用户）建立连接
2.选择数据库
3.数据库内部由一些表和其他数据库对象组成
用户->数据库->表
Oracle
1.选择数据库
2.数据库里包含很多个用户，选择某个用户进行连接
3.用户有一些表和其他数据库对象组成
数据库->用户->表
服务命令：
net stop mysql 停服务
net start mysql 起服务
sc delete mysql 删除服务
关系型数据库的操作语言：SQL
SQL语句： 结构化查询语言
DQL：数据查询语言：mysql数据库本身对多表友好，对子查询支持不太好
DML: 数据操作语言（增加数据，修改数据，删除数据）
DDL: 数据定义语言
DCL: 数据控制语言（数据使用的权限）
TPL: 事务处理语言
Sql：不区分大小写（Oracle支持部分不区分大小写；MYSQL不一定，默认真正的不区分大小写；有一个配置选项）
'mysql'linux 可以调整，windows完全不可以调整
存储引擎：
MyISAM InnorDB
优点 性能快 全面
缺点 不支持事务 性能不快
不支持外键
DQL：
查询：
简单查询：查询全表数据
语法：
select [distinct] {*|列名|表达式 [as] [列别名] [,...]}
[from 表名];
关键字/子句：
select：
from：数据源
[]:可有可无可选项
{}:括号内自动为一组
*: 一个表的所有列
|:或者的意思
列名：列的名字
表达式：
1.字符表达式：
2.计算表达式： DIV ：整除
支持几乎所有的java运算符
null值：
没有或者不确定的值
null值和任意数字进行算数运算的结果一定为null
,...:前面的对象重复出现中间用，隔开
条件查询：查询表的一部分数据
默认对于字符数据不区分大小写
语法：
select [distinct] {*|列名|表达式 [as] [列别名] [,...]}
[from 表名]
where (列名 |表达式 比较运算符 列名 |表达式)[逻辑运算符]
order by {列名|表达式|列别名 [asc(默认执行asc升序)][desc] } [field()]
limit 子句;
条件表达式 = 关系表达式 [逻辑运算符...]
关系表达式 = 列名 |表达式 比较运算符 列名 |表达式
81年之前入职的员工信息
select ename,sal,hiredate from emp where hiredate<'1981'
逻辑运算符：
and 与 &&
or 或 ||
not 非 ！
xor 异或
特殊比较运算符：
is null ：专门判断空值
like ：模糊查询
通配符：
_:一个长度的任意字符
查询名字中倒数第二个字母是T的人员信息
select * from emp where ename like '%T_'
%:任意长度的任意字符
查询名字以S开头的人员信息
select * from emp where ename like 'S%'
escape：
声明转义字符@
select * from emp where ename like '%@%%' ESCAPE '@'
in ：代替 or 运算符
between and ：必须从小到大（大于等于 || 小于等于）
自定义排序：
select ename ,sal ,job from emp order by FIELD(job,'PRESIDENT','ANALYST','MANAGER','CLERK','SALESMAN')
limit:分页问题
select * from emp
LIMIT 10,5
位置从0开始
长度
多表查询：
内连接：
查询的数据不仅在同一个表内
--查询所有人的部门名称和员工编号
select dname ,empno
from dept,emp
oracle:笛卡尔积：两组集合中元素组成组合最多的可能
99:交叉连接：cross join
oracle:去笛卡尔积 ：等值连接
select ename,empno ,emp.deptno ,dept.deptno ,dname from emp ,dept
where dept.deptno = emp.deptno;
99:1.自然连接 ，字段名和数据类型都相等
select dname ,empno from emp NATURAL join dept ;
2. using连接 （自然连接2.0）：字段名相等并制定
select ename ,dname
from emp join dept using(deptno)
oracle：去笛卡尔积：不等值连接
-- 查询员工的姓名和工资等级
select *
from emp ,salgrade
where sal between losal and hisal
99：on连接：
select *
from emp join salgrade
on sal between losal and hisal;
自连接：查询的数据在同一个表内，但是不在同一行上，现在需要在同一行显示
查询员工的姓名和领导的姓名
select a.ename ,b.ename
from emp a ,emp b
where a.mgr = b.empno;
查询smith的工资和比smith工资高的人的工资
多个表连接：
select a.empno ,a.ename ,a.sal ,grade,loc ,b.ename
from emp a, dept , salgrade,emp b
where a.deptno = dept.deptno
and a.sal between losal and hisal
and a.mgr = b.empno;
select a.empno ,a.ename ,a.sal ,grade,loc ,b.ename
from emp a join dept
on(emp.deptno = dept.deptno)
join salgrade
on(a.sal between losal and hisal )
join emp b
on(a.mgr = b.empno)
外连接：
oracle：
左外连接：
select a.ename ,b.ename
from emp a ,emp b
where a.mgr = b.empno(+);
右外连接：
select * from emp , dept
on emp.deptno(+) = dept.deptno;
99:
左外连接：
select a.ename ,b.ename
from emp a left outer join emp b
on a.mgr = b.empno;
右外连接：
select * from emp right outer join dept
on emp.deptno = dept.deptno;
union:集合并集可以做全外连接（mysql没有full outer join）
带函数的查询：
函数：API
系统自带函数，单行函数，多行函数（聚合函数，分组函数）
单行函数和多行函数的区别：单行函数数据源几行，结果有几行；多行函数的数据源无论多少行结果永远只有一行。
单行函数：
字符：
concat 多个字符串拼接
concat_ws select CONCAT_WS('@','aliwangwang','qq.com');
lpad select lpad('abc',10,'*');
rpad
lower
upper
left 从左边截取几位字符串
right
substring select SUBSTRING('abcdefg',3,4);
instr select instr('abcdef','c') 返回一个字符串在另一个字符串中的位置
trim 去两头空格
reverse select reverse('abc'); 反转
repeat select repeat('abc',3);
space space(1) 返回几个空格
length 字节数
char_length 字符数
format 数字格式化 ： select format(1234567.9876,2);
数字：
mod
abs
sign 判断俩数大小
floor
ceil
round 默认保留到个位，可以指定
truncate 截取，截到哪位，其他的都不要，没有进位
power 求数字的次幂
rand 求随机数 ，也有种子
日期：
now 系统当前时间
date_add select DATE_ADD('2018-1-1',INTERVAL 1 hour);
timestampdiff 求两个时间点中间差多长时间select timestampdiff(month,'2018-1-2','2019-1-1');
date_format select DATE_FORMAT('1981-4-5 1:2:3','%Y@%m@%d %h@%i@%s')
str_to_date select str_to_date('1981@04@05 01@02@03','%Y@%m@%d %h@%i@%s')
unix_timestamp 1970到现在的毫秒数
FROM_DAYS 通过天数差额算年月日
通用型：
if select IF(1=3,1,2);
ifnull select sal+ifnull(comm,0) from emp;
case
SELECT
ename,
CASE deptno
WHEN 10 THEN
'ACCOUNTING'
WHEN 20 THEN
'RESEARCH'
WHEN 30 THEN
'SALES'
ELSE
'OPERATIONS'
END
FROM
emp;
简单写法：
select ename,
case
when sal < 1000 then
'穷'
when sal between 1000 and 2000 then
'贫'
when sal > 2000 and sal <= 3000 then
'勉强能活'
else
'高富帅'
end
from emp;
其他函数：
Inet_aton()
Inet_ntoa()
select Inet_aton('192.168.0.1');
select Inet_ntoa(3232235521);
多行（聚合）函数：进行统计分析
sum:求和,avg：平均数,max ：最大,min：最小,count：计数
多行函数的计算：自动忽略空值
where 中一定不能使用多行函数
计算中可以选择去重
sum，avg必须跟数字类型的字段，其他的无所谓
永远准确的得知当前表多少条数据：
select count(*) from emp;
select count(1) from emp;
分组语句：更精准的使用统计信息说明数据的实际情况（避免无谓的干扰）
select [distinct] {*|列名|表达式 [as] [列别名] [,...]}
[from 表名]
[where (列名 |表达式 比较运算符 列名 |表达式)[逻辑运算符]]
[group by 列名|表达式 [with rollup] [,...] ]
[having 条件表达式（聚合）]
[order by {列名|表达式|列别名 [asc(默认执行asc升序)][desc] } [field()]]
[limit 子句];
对于分组的细节体现：
-- 求每个部门的工资总和人员构成
select deptno, sum(sal),GROUP_CONCAT(ename)
from emp
group by deptno
--对分组再统计 with rollup
select deptno, sum(sal),GROUP_CONCAT(sal)
from emp
group by deptno with ROLLUP
子查询： 把多个简单查询组合成一个查询语句
where子查询：复杂的条件，什么不知道就查什么
select avg(sal )
from emp
where deptno =
(select deptno from emp where empno in
(select mgr from emp where sal>
(select sal from emp where ename = 'smith')
and job =
(select job from emp where ename ='jones')
)
);
from 子查询：自创数据结构，调整子句的执行顺序，（必须起别名，无论是否使用别名）
--mysql
1.聚合函数嵌套问题？
select max(avgsal) from (select avg(sal) avgsal ,deptno from emp group by deptno) a;
2.嵌套子查询问题
-- 查询出比本部门平均工资高的人员名字和部门平均工资
select ename,avg(sal)
from emp ,(select avg(sal) avgsal ,deptno from emp group by deptno) a
where sal>avgsal and emp.deptno = a.deptno
子查询分类：
where 子查询返回的结果类型：
单行，单列子查询 90%
select *
from emp
where sal> (select sal from emp where ename = 'jones')
多行子查询
-- 查询smith和jones的部门名称
select dname
from dept
where deptno in (select deptno from emp where ename in('smith','king'))
-- 查询比10部门所有人工资高的人员信息
select *
from emp
where sal>ALL(select sal from emp where deptno = 20)
-- 查询比10部门任意人员工资高的人员信息
select *
from emp
where sal>any(select sal from emp where deptno = 20)
多列子查询
-- 查询和smith部门相同工资等于smith部门最高工资的人员信息
select *
from emp
where (sal,deptno) = (select max(sal),deptno from emp where deptno = (select deptno from emp where ename ='smith'))
DML:增删改 （一切皆是查询）
mysql在用之前，需要查一下当前系统的配置项目，系统默认会直接修改数据
autocommit on 1
off 0
set global autocommit = 0 (native 连接的命令窗口)
删除：
delete 子句： 删除某一行数据
删除某行数据前线查询到这行数据，把from之前换成delete
Mysql:
多表删除：
delete emp,dept from emp join dept on emp.deptno = dept.deptno;
insert:插入数据（行插入）
基础数据插入：
标准：insert into tabname (cloumname[,...] ) values (value[,...]);
mysql：insert tabname set (columname = value [,...])
业务数据插入：
标准：insert into testa (haha) select haha from testb;
update：修改
注意难点在：
1.完成改多少？
2.完成改了谁？
update tablename
set columnName = value [,...]
[where]
[...]
mysql：多表更新
update emp ,(select deptno from emp where ename ='smith' ) a,(select max(sal)maxsal from emp )b
set sal = b.maxsal ,a.deptno =
where emp.deptno = a.deptno
TPL:事务处理语言 （处理事务）
什么叫事务？
commit：事务提交
rollback：事务回滚
事务用来解决数据操作过程中的数据一致性问题
事务通过当前会话的第一个dml语句开始，到第一个tpl语句结束
事务特点：
原子性：一个事务不可被分割
一致性：事务要不然全部成功，要不然全部失败
隔离性：事务之间互相不影响
持久性：事务提交永远改变，事务回滚永远失效
DDL:数据定义语言（用来创建数据库对象：表，索引，约束，序列，视图....）
数据类型:dataType
（只管显示的长度，跟数据存储无关），必须配合不够补零才有用
字符：char：不支持大小写
varchar：（ 可变）不支持大小写
binary：支持大小写
varbinary：可变的支持大小写
blob: 非文本大文件
text: 文本大文件
char 和varchar
都保存字符，一个是定长，一个是可变，定长速度快，浪费空间；可变速度慢，节省空间。
数字：
int：正常整数
bigint：大整数
double：浮点型
decimal:支持科学计算
(Oracle：number)
日期：date :不支持时间 ，只支持日期
timestamp:支持年~秒 ,支持时区 ，空间小 ，只支持用到2038年
datetime:支持年~秒，空间大，9999
create ：创建
创建哪种数据库对象
create table tabname(
columnName dataType [constraint conname conType] [,...]
)
约束：创建时间一般同建表一起，或者在表之后创建，用来保证数据完整性的一种技术手段
约束类型：唯一 ，检查， 非空，主键，外键
Mysql:唯一，非空，主键，外键
create table f1(
f1p number constraint f1_f1p_pk primary key,
ff number constraint f2_f1p_fk references f1(f1p)
)
create table f2 (
ff number constraint f2_ff_pk primary key,
f1p number constraint f2_f1p_fk references f1(f1p)
)
alter table f1 add constraint f1_ff_fk foreign key (ff) references f2(ff);
通过查询建表：
create table emp_dept
as
select emp.*,dname ,loc from emp ,dept where 1=2;
drop：删除
drop table tabname;
alter：修改
删除某列 alter table emp_dept drop COLUMN loc
增加某列 alter table emp_dept add COLUMN loc char(10)
修改某列 alter table emp_dept modify COLUMN loc char(15)
修改列的全部信息：alter table hehe change COLUMN ename namess varchar(15);
truncate ：截断 （删除全表的数据，不可选）
truncate table tabname;
truncate 和delete 和drop
只删除数据，不删除表
truncate 全表删除，不可还原 ，速度快
delete 部分可选，可以还原，效率慢
drop： 表和数据都删除，drop自动commit ，但是可以还原
auto_increment :自增列，如果不给赋值，则自动增长值，从1开始...
自增列的前提是主键，一个表只能用一次
这个功能在其他数据库叫sequence
视图：本质是一个保存了的查询语句
CREATE or replace VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
or replace :利用新的sql语句替换老的视图
索引：
create index hahahaha on emp (ename)
什么时候适合使用索引：
1.数据量较大
2.查询的内容占总数据20%
3.主要做查询
4.不要经常增删改
5.主键和唯一键
DCL:数据控制语言
思考题：
-- 求每个部门的工资总和（不用group_concat如何实现？）
select deptno, sum(sal),GROUP_CONCAT(sal)
from emp
group by deptno
