Mysql知识精化
一、Mysql实用知识量并不多，核心就是增删改查，其他的一切基本都是围绕这这个核心展开，下面的知识包含了Mysql使用的方方面面，请大家放心食用！将以三张表为例展开，表结构如下所示：
数据库文件直接下载：sql文件
二、信息介绍
该数据有三张表，信息介绍如下：
部门表dept（deptno部门编号、dname部门名称、loc位置）
员工表emp（empno工号、ename员工姓名、job职位、mgr直属领导工号、
hiredate入职日期、sal月薪、comm补贴、deptno部门编号）
薪水等级表salgrade（grade等级、losal最低工资、hisal最高工资）
三、知识+例子+sql语句
1、查询年薪：select ename,sal×12 from emp;
给查询结果的列重命名:select ename,sal×12 as yearsal from emp; （as是可以省略的）
别名中有中文：select ename,sal×12 as 年薪 from emp; //错误
select ename,sal×12 as ‘年薪’ from emp; //正确
注意：标准sql语句中字符串使用单引号括起来（双引号也可以，尽量别用）
2、条件查询。select 字段，字段… from 表名 where 条件;
查询工资等于5000的员工姓名：select ename from emp where sal = 5000;
查询SMITH的工资：select sal from emp where ename = ‘SMITH’; (姓名字段类型是varcher,是字符串)
查询工资大于3000的员工姓名：select ename from emp where sal >3000;
工资在1100和3000之间的员工姓名，包括1100和3000：select ename from emp where sal>=1100 and sal<=3000;
select ename from emp where sal between 1100 and 3000;
大于等于 ： >= 不等于3000： !=3000 或 <>3000 且：and
between…and… ：在…和…之间（闭区间） 在使用时必须左小右大
between and 还可以使用在字符串方面：select ename frome emp where ename between ‘A’ and ‘C’; (根据首字母判定，左闭右开)
3、在数据库中NULL不是一个值，代表什么都没有，为空，和0不一样。空不是一个值，不能用等号衡量，必须使用 is null或 is not null
找出哪些津贴为null：select ename from emp where comn is null;
找出哪些津贴为null和0：select ename from emp where comn is null and comn=0;
4、工作岗位是MANAGER和SALESMAN的员工：select ename from emp where job=‘MANAGER’ or job =‘SALESMAN’;
and: 和 or:或者 and运算级高
找出工资大于1000并且部门编号是20或30部门的员工名字：select ename from emp where sal>1000 and (deptno=20 or deptno =30 ) ;
注意：当运算符的优先级不确定时，加小括号
5、in等同于or
工作岗位是MANAGER和SALESMAN的员工：select ename from emp where job=‘MANAGER’ or job =‘SALESMAN’;
select ename from emp where job in ( ‘MANAGER’ , ‘SALESMAN’ ) ;
not in:不在这几个值当中。
查询工资不是800和5000的人： select ename from emp where sal not in ( 800,5000 ) ;
6、模糊查询like
2个特殊的符号：% _
%:任意多个字符 _:任意一个字符
找出名字里含o的： select ename from emp where ename like ’ %o% ’ ;
找出名字中有下划线的：select ename from emp where ename like ‘%_%’ ; // 下划线转义 在前面加上斜杠
7、排序（升序、降序）
按照工资升序排序：select ename,sal from emp order by sal; (默认是升序排列)
asc : 升序 desc :降序
select ename,sal from emp order by sal; //升序
select ename,sal from emp order by sal asc; //升序
select ename,sal from emp order by sal desc; //降序
按照工资的降序排列，当工资相同时按照名字的升序排列：select ename,sal from emp order by sal desc , ename asc;
多个字段同时排序，越靠前的字段越能起到主导作用，只有前面字段无法排序的时候，才会启用后面的字段。
找出工作岗位是SALESMAN的员工，并且要求按照工资的降序排列：select ename from emp where job=‘SALESMAN’ order by say desc;
order by 是最后执行的。 先起别名，排序也是根据别名进行排序的。
8、分组函数
count:计数 sum:求和 avg:平均值 max:最大值 min：最小值
所有的分组函数都是对”某一组“数据进行操作的。
找出工资总和：select sun(sal) from emp;
找出总人数：select count (*) from emp;
找出总人数：select count (ename) from emp;
分组函数一共5个，又名：多行处理函数
多行处理函数的特点：输入多行，最终输出一行
分组函数自动忽略NULL
找出工资高于平均工资的员工：select ename,sal from emp where sal>age(sal); //错误
原因：分组函数不可直接使用在where子句中，因为group by是在where执行后才会执行的。
找出工资高于平均工资的员工：select ename,sal from emp where sal>(select avg(sal) from emp); //正确
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
count（*） ：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm)：表示统计commm字段不为NULL 的数据总数量。
9、单行处理函数：输入一行，输出一行
计算每个员工的年薪：select ename,(sal+comm)*12 as ‘年薪’ from emp;
如果某人的comm为NULL，他的年薪就是0
重点：只要数学表达式中有NULL参与运算，结果一定是NULL
故 计算每个员工的年薪：select ename,(sal+ifnull(comm,0) )*12 as ‘年薪’ from emp;
ifnull()空处理函数： ifnull(可能为NULL的数据，被当成什么来处理) 属于单行处理函数
select ename,ifnull(comm,0) as comm from emp;
10、group by 和 having
group by :按照某个字段或者某些字段进行分组
having :对分组之后的数据进行再次过滤
找出每个工作岗位的最高薪资：select max(sal) from emp group by job;
注意：分组函数一般都会和group by 联合使用，这也是为什么它被称为分组函数的原因
当一条sql语句中没有group by的话，整张表的数据自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中可以执行，但是结果没有意义，在Oracle数据库中会语法错误，因为它的语法规则比Mysql严谨
注意：当一条语句中有group by 的话，select后面只能跟分组函数和参与分组的字段。
找出每个部门不同岗位的最高工资： select deptno,job,max(sal) from emp group by deptno,job; (把两个分组看成一个字段)
找出每个部门的最高工资，要求显示工资大于2500的：select deptno, max(sal) from emp group by deptno having max(sal)>2500; //可以但是效率低
select deptno,max(sal) from emp where sal>2500 group by deptno //效率高，先用where过滤掉
找出每个部门的平均工资，要求显示工资大于2000的：select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
11、查看无线网密码：
netsh wlan show profiles ：查看本机连接过无线密码
netsh wlan show profile name=“楠” key=clear 查看这个无线网的信息（name是无线网的密码）
12、关于查询结果集的去重
select distinct job from emp; //distinct 关键字去除重复记录
select ename,distinct job from emp; //错误 distinct只能出现在所有字段的前面
注意：distinct 出现在最前方，表示后面所有字段联合起来去除重复
统计岗位的数量：select count(distinct job) from emp;
13、连接查询：多张表联合查询取出最终的结果
当学号、姓名、班级、地址都放在一张表的时候，数据会存在大量的重复，导致数据的冗余，往往拆分成多张表。
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全连接（很少用）
在表的连接查询方面有一种现象被称为 笛卡尔积现象: 如果两张表连接但没有条件限制，结果条数为两张表条数的乘积
找出每一个员工的部门名称，要显示员工名和部门名：select ename，dname from emp,dept; （ename和dname要联合起来显示，所以发生笛卡尔积现象）
14、关于表的别名
select e.ename , d.dname from emp e , dept d; (56个结果)
表的别名好处：执行效率高、可读性好
加条件避免笛卡尔积现象，不会减少记录的匹配次数，依然是56次，只不过显示的是 有效记录。
显示员工名和部门名：select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; (避免了笛卡尔积现象，但是暗地还是匹配了56次)
+--------+-------------+
| ename | dname |
+--------+-------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SIMITH | RESEARCHING |
| JONES | RESEARCHING |
| SCOTT | RESEARCHING |
| ADAMS | RESEARCHING |
| FORD | RESEARCHING |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+-------------+
14 rows in set (0.00 sec)
15、内连接之等值连接：最大特点是：条件是等量关系
查询每个员工的部门名称：SQL92： select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
SQL99： select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
SQL99语法： …
A
（inner）join //inner 可以省略，内的意思
B
on
连接条件
where
…
SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。
16、内连接之非等值连接：最大特点是：连接条件中的关系是非等量关系。
找出每个员工的工资等级，要求显示员工名和工资等级：select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal >= s.losal and e.sal<= s.hisal;
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
±——-±——–±——+
| ename | sal | grade |
±——-±——–±——+
| SIMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±——-±——–±——+
17、自连接：最大的特点是：一张表看做两张表，自己连接自己
±——±——-±—–+
| empno | ename | mgr |
±——±——-±—–+
| 7369 | SIMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
±——±——-±—–+
找出每个员工的上级领导，要求显示员工名和对应的领导名
员工表 emp a 领导表 emp b
故sql语句：select a.ename as ‘员工’,b.ename as ‘领导’ from emp a join emp b on a.mgr=b.empno;
±——-±——+
| 员工 | 领导 |
±——-±——+
| SIMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK | 13条记录 少一个人 KING 因为他没有领导（他是老板）
±——-±——+
18、外连接？
内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，就是内连接，AB两张表没有主副之分，是平等的
外连接：假设A和B表进行连接，使用外连接的话，AB两张表有一张是主表，一张是副表，主要查询主表中的数据，捎带的查询副表，当
副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与匹配
外连接的分类：
左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表
左连接有右连接的写法，右连接也有对应的左连接的写法
找出每个员工的上级领导（所有员工都要查询出来，King也要查）？
内连接sql语句：select a.ename as ‘员工’ , b.ename as ‘领导’ from emp a join emp b on a.mgr = b.empno; //13条记录，少了King
左外连接：select a.ename as ‘员工’ , b.ename as ‘领导’ from emp a left join emp b on a.mgr = b.empno;
±——-±——+
| 员工 | 领导 | 左外连接：left outer join 位于左边的a是主表（员工表），顺带b（领导表），b没值就用NULL代表
±——-±——+ //outer可以省略
| SIMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±——-±——+
14 rows in set (0.00 sec)
右外连接：select a.ename as ‘员工’ , b.ename as ‘领导’ from emp b right join emp a on a.mgr = b.empno;
±——-±——+
| 员工 | 领导 | 右外连接：right outer join 位于右边的a是主表（员工表），顺带b（领导表），b没值就用NULL代表
±——-±——+ //outer 可以省略
| SIMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±——-±——+
14 rows in set (0.00 sec)
外连接最重要的特点是：主表的数据无条件的全部查询出来。如果使用内连接，可能会造成数据的丢失。
找出哪个部门没有员工： select distinct d.* from emp e right join dept d on e.deptno =d.deptno where e.empno is null;
±——-±———–±——-+
| deptno | dname | loc |
±——-±———–±——-+
| 40 | OPERATIONS | BOSTON |
±——-±———–±——-+
1 row in set (0.00 sec)
19、三张表怎么连接查询？
找出每个员工的部门名称以及工资等级： select e.ename,d.dname,s.grade from emp e, dept d,salgrade s where( e.deptno=d.deptno) and( e.sal between s.losal and s.hisal); //SQL92
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal; //SQL99
…
A //表示：A表和B表先进行表连接，连接之后A表继续和C表连接
join
B
join
C
on
…
±——-±————±——+
| ename | dname | grade |
±——-±————±——+
| SIMITH | RESEARCHING | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCHING | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCHING | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCHING | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCHING | 4 |
| MILLER | ACCOUNTING | 2 |
±——-±————±——+
14 rows in set (0.00 sec)
找出每个员工的部门名称、工资等级、上层领导：
select
e.ename ‘员工’ ,d.dname ‘部门’,s.grade ‘工资等级’,e1.ename as ‘领导’
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr=e1.empno;
±——-±————±———±——+
| 员工 | 部门 | 工资等级 | 领导 |
±——-±————±———±——+
| SIMITH | RESEARCHING | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCHING | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCHING | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCHING | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCHING | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
±——-±————±———±——+
14 rows in set (0.01 sec)
20、子查询
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
…(select)
from
…(select)
where
…(select)
where子句中使用子查询？
找出高于平均工资的员工信息：select * from emp where sal>(select avg(sal) from emp );
21、from后面嵌套子查询？
找出每个部门平均工资的工资等级：
第一步：找出每个部门的平均工资：select deptno,avg(sal) as avgsal from emp group by deptno;
±——-±————+
| deptno | avgsal | //把这张表当成存在的表 t ,让t表和salgrade表连接，条件是t.avgsal between s.losal and s.hisal
±——-±————+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±——-±————+
第二步：将以上的查询结果当成临时表t，让t表和salgrade s 表连接
select
t.* , s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
±——-±————±——+
| deptno | avgsal | grade |
±——-±————±——+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±——-±————±——+
找出每个部门平均的工资等级：
方法1： select
t.deptno,avg(t.salavg)
from
(select e.deptno,s.grade as salavg from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by
deptno ;
方法2： select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
±——-±————–+
| deptno | avg(t.salavg) |
±——-±————–+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±——-±————–+
22、在select后面嵌套子查询
找出每个员工所在的部门名称，要求显示员工名和部门名：select
e.ename , (select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
23、union (可以将查询结果集相加)
找出工作岗位是SALESMAN和MANAGER的员工：
第一种： select * from emp where job=‘SALESMAN’ or job= ‘MANAGER’;
第二种： select * from emp where job in (‘SALESMAN’’ , ‘MANAGER’);
第三种： select * from emp where job='SALESMAN'
union
select * from emp where job='MANAGER';
±——±——-±———±—–±———–±——–±——–±——-+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
±——±——-±———±—–±———–±——–±——–±——-+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
±——±——-±———±—–±———–±——–±——–±——-+
两张不相干的表中的数据拼接在一起显示：select ename from emp union select dname from dept;
±————+
| ename |
±————+
| SIMITH | //员工名
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING | //部门名
| RESEARCHING |
| SALES |
| OPERATIONS |
±————+
使用union时，每张表查询的字段数目要相同
24、limit (重点，分页查询靠它)
limit 是mysql 特有的，其他数据库中没有，不同用。（Oracle中有个相同的机制，叫rownum）
limit的作用： 取结果集中的部分数据
语法机制：
limit startIndex , length
startIndex 表示起始位置 length 表示取几个
取出工资前5名的员工：select ename ,sal from emp order by sal desc limit 0,5;
select ename ,sal from emp order by sal desc limit 5;
limit是sql语句最后执行的环节
找出工资排名在第四到第九的员工：select ename,sal from emp order by sal desc limit 3,6;
每页显示3条记录：
第一页： 0，3 第二页：3，3 第3页：6，3 第4页：9，3
每页显示pageSize条记录，第pageNo页 ： （pageNo-1）* pageSize , pageSize
25、创建表：
建表语句的语法格式：
create table 表名(
字段名1 数据类型，
字段名2 数据类型，
…
)；
MYSQL当中字段的数据类型：
int 整数
bigint 长整数
float 浮点数
char 定长字符串
varchar 可变字符串 最大255字符
date 日期类型
BLOB 二进制大对象呢（存储图片、视频等流媒体信息)
CLOB 字符大对象（存储较大文本，可以存储4G的字符串）
char和varcher怎么选择：
在实际开发中，当某个字段中的数据长度不发生改变时，是定长的 比如性别、生日等都是采用char
当一个字段的数据长度不确定，比如简介等采用varchar
BLOB 和CLOB类型的使用：
电影的海报：BLOB
电影历史：CLOB
表名在数据库当中一般是t_或tbl_开始
创建学生表：create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1, //插入数据时如果没有规定值，默认值为1
classno varchar(255),
birth char(10)
);
26、insert语句插入数据
语法格式：
insert into 表名（字段名1，字段名2，字段名3, …） values (值1，值2，值3, …)
要求：字段数量和值的数量相同 ，且数据类型要对应相同。
insert into t_student (no,name,sex,classno,birth) values (1,'zhangsan' , '1' , 'gaosan1ban' , '1950-10-12');
字段名顺序、个数没有要求，改怎么插入怎么插入，没有数据的用NULL代替
注意：insert 语句插入数据了，表格中必然会多出一行记录，即使多的这一行记录中某些字段是NULL，后期也没有办法在执行insert语句插入数据了
只能使用update进行更新
insert into t_student values(2,'jack','1' , 'gaosan1ban','1955-11-15'); //字段可以省略不写，但是后面的values对数量和循序都有要求
一次性插入多行数据： insert into t_student
(no,name,sex,classno,birth)
values
(3,'hgueg' , '1' , 'gaosan1ban' , '1930-12-12'), (4,'yali' , '2' , 'gaosan1ban' , '1950-10-16');
27、表的复制
create table 表名 as select 语句;
将查询结果当做表创建出来
28、将查询结果插入到一张表中
insert into t_student select * from t_sr
±—–±———±—–±———–±———–+
| no | name | sex | classno | birth |
±—–±———±—–±———–±———–+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | jack | 1 | gaosan1ban | 1955-11-15 |
| 3 | hgueg | 1 | gaosan1ban | 1930-12-12 |
| 4 | yali | 2 | gaosan1ban | 1950-10-16 |
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | jack | 1 | gaosan1ban | 1955-11-15 |
| 3 | hgueg | 1 | gaosan1ban | 1930-12-12 |
| 4 | yali | 2 | gaosan1ban | 1950-10-16 |
±—–±———±—–±———–±———–+
8 rows in set (0.00 sec)
29、修改数据：update
语法格式：
update 表名 set 字段名1=值1 , 字段名2=值2, … where 条件;
注意：没有条件整张表数据全部更新
将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU:
update dept1 set loc='SHANGHAI' , dname='RENSHIBU' where deptno=10;
30、删除数据
delete from 表名 where 条件
删除10部门数据：delete from dept1 where deptno=10;
删除所有记录：delete from dept1;
怎么删除大表数据？（重点）
truncate table emp1; //表被截断，不可回滚，永久丢失
删除表（结构 ） ：drop table 表名;
31、约束
约束：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。
常见的约束：
非空约束（not null） ：约束的字段不能为NULL
唯一约束（unique） ： 约束的字段不能重复
主键约束（primary key） ：约束的字段既不能为NULL,也不能重复（简称PK）
外键约束（foreign key） ： 。。。。。。。。。。。。。。（简称FK）
检查约束（check） :注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
32、非空约束 not null
创建表的时候，在数据类型后面加上 not null ，这样插入数据时，这个字段名必须赋值
33、唯一性约束（unique）
唯一约束修饰的字段具有唯一性，不能重复，但可以为NULL（NULL和NULL不一样，不能划等号）
在数据类型后面加上 unique ，这样插入数据时，这个字段的数据不能出现相同的
create table t_user(
id int,
usercode varchar(255) unique, //列级约束
username varchar(255)
);
给多个字段联合添加unique：
create table t_user(
id int,
usercode varchar(255) ,
username varchar(255)
unique(usercode , usetname) //这两个字段的联合值 不能重复！！！！ 表级约束
);
34、主键约束 primary key 列级约束
create table t_user(
id int,
usercode varchar(255) primary key , //这个字段的数据不能为NULL 也不能重复 usercode:主键字段 插入的值：主键值
usename varchar(255)
);
表的设计三范式中有要求，第一范式要求任何一张表都应该有主键
主键的作用：主键值是这行记录在这个表中的唯一标识（像身份证一样）
主键的分类：
根据主键字段的字段数量来划分：
单一主键：（推荐、常用）
复合主键(多个字段联合起来添加一个主键约束) （不建议使用）
根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数 （推荐）
业务主键：主键值和系统业务挂钩，如：拿着银行卡卡号做主键（不建议）
最好不要拿着业务挂钩的字段作为主键，因为以后的业务一旦发生改变时，主键值可能也需要随着发生变化
但是有的时候没办法变化，因为变化可能会导致主键值重复
一张表的主键约束只能有一个（重点！！！）
使用表级约束定义主键：
create table t_user(
id int,
usercode varchar(255) ,
usename varchar(255)
primary key( id)
);
复合主键：（不建议使用）
create table t_user(
id int,
usercode varchar(255) ,
usename varchar(255)
primary key(id,usetcode)
);
mysql提供主键自增：auto increment（重要）
create table t_user(
id int primary key auto increment , //id字段自动维护一个自增的数字，从1开始，递增加1
usercode varchar(255) ,
usename varchar(255)
);
Oracle 中也提供了自增机制：序列对象 （sequence）
35、外键约束 ： foreign key
外键字段: 添加有外键约束的字段
外键值：外键字段中每一个值
t_class 班级表 t_student 学生表
cno(pk) cname sno(pk) sname classno（添加外键约束fk)
101 上海1班 1 s1 101
102 北京 2班 2 s2 102
3 s3 101
4 s4 102
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表
删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，再添加子表
先创父，再创子 先删子，再删父
create table t_class( create table t_student (
cno int, sno int,
cname varchar(255), sname varchar(255),
primary key(cno) classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
); );
外键值可以为NULL
外键字段引用其他表的字段时，这个字段必须具有唯一性（一般用主键）
36、存储引擎：只有MysqL存在，Oracle叫存储方式
建表时，可以指定存储引擎和字符集，默认引擎是InnoDB 字符集是UTF8
查看当前mysql支持的存储引擎：show engines \G
常用的：
MyISAM存储引擎：使用3个文件来代表表 ：
格式文件：存储表结构的定义 XXX.frm
数据文件：存储表行的内容 XXXX.MYD
索引文件；存储表上索引 XXX.MYI
优点：可被压缩、节省空间、可转换成只读表，提升检索效率 缺点：不支持事物
InnoDB存储引擎：
优点：支持事物、行级锁、外键等，这种引擎安全
表的结构存储再xxx.frm中
数据存储在tavlespace这样的空间中，无法被压缩
在mysql服务器崩溃后，自动恢复
支持级联删除和级联更新
MEMORY存储引擎：
缺点：不支持事物，数据容易丢失，因为所有数据和索引存储在内存中
优点：查询速度最快
断电数据就没了
37、事物（Transaction）
事物：一个事物是一个完整的业务逻辑单元，不可再分
比如：银行账户转账，从A账户向B账户转账10000，需执行两条update语句
update t_act set balance =balance-10000 where actno=‘act-001’;
update t_act set balance =balance+10000 where actno=‘act-002’;
以上语句必须同时成功或同时失败，不能一个成功一个失败
要想以上两条DML语句同时成功或同时失败，需要使用数据库的‘事物’
和事物相关的语句只有DML（insert delete update）
因为事物的存在是为了保证数据的完整性
如果所有的业务都能使用一条DML语句完成，就不用事物机制了
事物包括四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分
B：一致性：事务必须保证多条DML语句同时成功或者同时失败
I：隔离性：事务A与事务B之间具有隔离
D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束
事物隔离性存在隔离级别，理论上有4个：
第一级别：读未提交(read uncommitted) ：对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
读未提交存在脏读现象：读到了脏的数据
第二级别：读已提交（read committed）：对方事务提交之后的数据我方可以读取到
缺点：我方不可重复读
解决了脏读现象
第三级别：可重复读 (repeatable read) ；读到的一直是最开始的数据
这种隔离级 别解决了不可重读问题
缺点：读取到的数据是幻象
第四级别：序列化读/串行化读( serializable)
解决了所有问题
效率低，需要事务排队
Oracle数据库默认的隔离级别是：读已提交
mysql数据库默认的隔离级别是：可重复读
38、mysql事务默认情况是自动提交：执行任意一条DML语句则提交一次 回滚不了！！！！
start transaction :关闭自动提交（这样可以回滚了） 仅在当前事务有效
commit: 提交
rollback: 回滚到上一次的提交点
39、设置事务的隔离级别：set global transaction isolation level （在后面加上事务级别）
如 set global transaction isolation level read uncommitted;
查看事务的全局隔离界别：select @@global.tx_isolation;
40、索引
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源
在数据库方面，查询一张表的时候有两种检索方式
第一种方式；全表扫描
第二种方式：根据索引检索（效率很高）
索引最根本的原理是：缩小了扫描的范围
索引可以提高检索效率，但是不能随意添加索引，因为索引也是数据库种的对象，需要维护，有维护成本
比如表中数据经常被修改时，就不适合添加索引，一旦数据修改，索引需要重新排序。
添加索引是给某一个字段，或则说某些字段添加索引
select ename,sal from emp where ename='SMITH';
当ename字段上没有添加索引时候，sql语句会进行全表扫描，扫描ename字段的所有值
当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位
-------------------------------------------------------------------------
什么时候考虑添加索引：
数据量大、字段很少的DML操作、该字段经常出现在where子句中
注意：主键和具有unique约束的字段自动会添加索引
根据主键查询效率高，尽量根据主键检索
查看sql语句的执行计划： explain select ename,sal from emp where sal=5000;
±—±————±——±—–±————–±—–±——–±—–±—–±————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±————±——±—–±————–±—–±——–±—–±—–±————+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
±—±————±——±—–±————–±—–±——–±—–±—–±————+
怎么创建索引： create index 索引名称 on 表名（字段名）;
删除索引对象：drop index 索引名称 on 表名;
索引底层采用的数据结构：Betree
索引实现原理：
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的‘物理地址’
最终通过索引检索到数据之后，获取到i关联的物理地址，通过物理地址定位 表中的数据，效率
是最高的
select ename from emp where ename = 'SMITH';
通过索引转换为：
select ename from emp where 物理地址=0x3;
索引的分类：
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自动添加索引
.........
索引什么什么会失效：select ename from emp ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的
41、视图（view）：同一张表的数据，通过不同的角度去看
创建视图：create view myview as select empno,ename from emp;
删除视图：drop view myview;
只有DQL语句才能以视图对象的方式创建出来
对视图进行增删改查，会影响到原表数据(通过视图影响原表数据，不是直接操作原表)
视图的作用：可以隐藏表的实现细节，保密级别较高的系统，数据库只提供对外的视图，java程序员只对视图对象进行CRUD
42、数据库设计三范式：
按照这个三范式设计的表不会出现数据冗余
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖
多对多？三张表，关系表两个外键
第三范式：建立在第二范式基础之上，所有非主键字段直接依赖主键，不能产生传递依赖
一对多？ 两张表，多的表加外键
提醒：在实际开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/117972.html
