这里写目录标题
记录MySQL的基础知识方便自己查阅。所记全部基于 bilibili老杜带你学mysql。
命令参考:
①安装服务:mysqld --install
②初始化: mysqld --initialize --console
③开启服务:net start mysql
④关闭服务:net stop mysql
⑤登录mysql:mysql -u root -p
Enter PassWord:(密码)
⑥修改密码:alter user 'root'@'localhost' identified by 'root';
(by 接着的 是密码)
⑦标记删除mysql服务:sc delete mysql
什么是表?
表是数据库基本组成单元。行称为数据/记录(data),列(column)称为字段。
语句分类
DQL:Data Query Language 数据查询语言标准语法。select语句都是DQL。
DML:Data Manipulation Language 数据操作语言。对表中数据进行insert增、delete删、uopdata改。
DDL:Data Defination Language 数据定义语言。对表结构进行create增、drop删、alter改。
TCL:Transaction Control Language 事务控制语言
导入数据
先创建一个数据库 create database name character set utf8;
选择数据库: use name;
导入数据:source
+路径***.sql。
对于右图的这个表:
查看数据 select * from 表名
, *代表所有。
查看结构 desc dept
; desc就是describe。结构中varchar就是String。
查询
强调:sql语句不区分大小写;语句以“;”结尾。
查询一个字段:select 字段名 from 表名;
查询多个字段:字段名用逗号隔开,如select a,b,c from 表名;
查询所有字段:① select a,b,c,e... from 表名;
②select * from 表名;
不推荐使用,因为效率低,可读性差。
列起别名
as
关键字起别名。
可以用空格代替。如果别名里想带空格,那整个别名放在‘’
里。
列参与数学运算
字段可以使用数学表达式:
sal*12不好看,用as改名:
条件查询
语法格式:
select
字段1、字段2....
from
表名
where
条件
between and 左小右大,闭区间。
找null,不能=null,用is null。
and 和 or连用,注意优先级。
or加小括号,因为and优先级高。
in 后面跟值。in(800,5000)
不是800到5000,是800或者5000,
like 模糊查询。
_
代表任意一个字符。如果想找名字中带有_的?转义字符like ‘%\_%’;
排序
order by sal;
按照sal排序,默认升序排序。
order by sal desc;
指定降序,按照sal降序排序。
order by sal asc;
指定升序
多字段排序
假设 ename是员工名称,sal是薪资。要求查询员工名称和薪资,按照薪资升序排列,假设薪资一样,按名称升序。
select
ename,sal
from
表名
order by
sal asc, ename asc;
以上语句的执行顺序: from,where,select,order by。
数据处理函数
单行处理函数
特点:单输入对应单输出 (每行都有一个输出)。
1、lower(),upper(),小 大写转换。
select lower(ename) as ename from emo;
2、substr(str,start,end)取子串,与java不同,起始下标从1开始。
3、concat(str1,str2) 字符串拼接。
4、length(str) 字符串长度。
首字母大写?
select concat(upper(substr(ename,1,1)), substr(ename,2, length(ename)-1)) as result from emp;`
5、 trim() 去除字符串前空白
select ename from emp where ename=trim(' KING');
6、format设置千分位
7、round 四舍五入
特殊记忆:select + 字面值 : select ‘abc’ from emp ; 借助你的表生成一堆abc。 比如emp 14列,那就输出select 1000同理
select round(1236.567) as result from emp;//0表示保留0位小数
8、rand() 生成1以内随机数
9、ifnull 空处理函数
在所有数据库中,有NULL参与的数学计算结果就为null
补助为null时,将补助当作0?
select ifnull(comm, 0) from emp;
10、case…when…then…when…then…else…end
当员工的岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%
注意:不改变数据库,只是显为上调
select
ename,job,(case job when 'MANAGE' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
from
emp;
多行处理函数
特点:输入多行,输出一行。5个:
count 计数
sum
avg
max
min
注意 分组函数在使用时必须分组。否则,默认整张表为一组。
注意事项:
1、自动忽略null,不需要提前处理null; null没有算在内。
统计数量也不会统计null。因为不存在某一行全为null
2、count(*)和count字段(的区别):
3、分组函数不能直接使用在where字句中
4、所有的分组函数可以组合起来一起用
分组查询
先分组,再查询。计算每个部门薪资和?计算每个岗位平均薪资?
select
...
from
...
group by
...
以上语句的执行顺序: from,where,group by,select,order by。
解释了为什么where sal> min(sal);不可运行。
因为分组函数在使用时必须先分组,where执行时还没有分组,所以where后面不能出现分组函数。
找出每个岗位薪资和?
select
job, sum(sal)
from emp
group by job;
是否可以在job前面加上ename?不可以,因为ename有14条,加上后毫无意义。
所以,若语句中有 group by语句,select 后面只能跟group by后面的字段以及分组函数。
联合字段查询
找出每个部门,不同工作岗位的最高薪资?
两个字段联合成一个字段看,比如deptno20的MANAGER,和deptno30的MANAGER,分别看作MANAGER20 MANAGER30!
select
deptno,job,max(sal)
from
emp
group by
deptno,job
order by
deptno asc;
having
找出每个部门最高薪资,显示大于3000的?
使用having对分完组之后的数据进一步过滤。(不能单独使用)
select
deptno, max(sal)
from
emp
group by
deptno
having max(sal)>3000;
效率较低,可以先将大于3000的工资选出来,再分组。
select
deptno,max(sal)
from
emp
where
sal>3000
group by
deptno;
优化策略:where和having 优先选择 where。where没办法就用having
where没办法的情况:找出每个部门的平均薪资,显示不大于2500的。
where avg(sal)<2500? 不可以,因为where的执行顺序先于分组函数。 必须使用having
总结
select
...
where
...
group by
...
having
...
order by
...
执行顺序:from, where, group by, having, select, order by
从
某张表中,经过where筛选
出有价值的数据。对这些有价值的数据进行分组
。
分组后可以使用having
继续筛选。select查询
出来。最后排序
。
找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除了MANAGER之外,要求按照平均薪资降序排列。
select
job, avg(sal) as avgsal //注意这里起了别名。
from
emp
where
job <> 'MANAGER'
group by
job
having
avg(sal) > 1500 //having 不可以使用上面的别名,因为select语句在having后面执行。
order by
avgsal desc; //order by在select后,可以使用别名
ditinct关键字
1、把查询结果去除重复记录
注意:原表数据不会被修改,只是查询结果去重
使用关键字 distinct
原表:
select distinct job from emp;
distinct 只能出现在所有字段最前方。
错误写法:select ename, distinct job from emp;
之所以错误,是因为ename有14条,distinct job有五条,不匹配。
用在两字段之前,代表两字段联合去重。
select distinct job,deptno from emp;
MANAGER20 和 MANAGER30 算作两个。两字段联合去重.
题目:统计工作岗位数量?
select count(distinct job) from emp;
连接查询(重点内容)
概念:从一张表中单独查询,称为单表查询。emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
分类:按年代:
①SQL92:1992年的时候出现的语法
②SQL99:1999年的时候出现的语法
根据表连接的方式分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接
笛卡尔积现象:
第一张表:14行
select ename,deptno from emp;//14行
第二张表:4行
select * from dept;//4行
无限制条件链接两张表:
select ename,dname from emp, dept;
一共会输出 14 * 4 = 56 行
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是
两张表条数的乘积,这种现象被称为:笛卡尔积现象。
怎么避免笛卡尔积现象?
emp表:
dept表:
连接时加条件,满足这个条件的记录被筛选出来!
select
ename,dname
from
emp, dept
where
emp.deptno = dept.deptno;
ename和dname要在每个表里查一次有没有他俩,效率低,改:
select
emp.ename,dept.dname //仅在emp里查询ename,dept里查询dname
from
emp, dept
where
emp.deptno = dept.deptno;
再改:表起别名。很重要。效率问题。
select
e.ename,d.dname
from
emp e, dept d//from最先执行,所以从from起别名
where
e.deptno = d.deptno;
最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?
还是56次,只不过进行了四选一。次数没有减少。
内连接
等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名?
上面介绍笛卡尔积的时候都是SQL92语法。缺点:如果想再筛选,还要在where后面写(and)
,结构不清晰。
SQL99语法:
select
e.ename,d.dname
from
emp e
inner join //inner 可以省略,带着可读性好。
dept d
on
e.deptno = d.deptno;//等值连接 连接条件独立。想再筛选,往后加where
where
.....
非等值连接
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
salgrade 表:
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;//非等值连接
自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
先看一下员工表中 员工编号(EMPNO),员工名(ENAME),及其领导编号(MGR)
select empno,ename,mgr from emp;
如:smith领导是7902,7902是Ford。也就是说这张表要自己和自己连接。
技巧: 一张表看作两张表 把这个emp,看作emp 1 和emp 2.
select
e1.ename as '员工名', e2.ename as '领导名'
from
emp e1
join
emp e2
on
e1.mgr=e2.empno;//员工的领导编号 = 领导的员工编号
注意,只有13条记录,没有KING这个人的记录,他没有领导。
外连接
部门表:
员工表:
显示每个人部门名称。
内连接:(A和B连接,AB两张表没有主次关系。平等的。)
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
可见内连接不显示40 OPERATIONS,因为没有人是这个部门的,内连接的特点:完成能够匹配上这个条件的数据查询出来。
右外连接
select
e.name,d.name
from
emp e
right outer join // right outer join, outer 可省略
dept d //我主要是是为了查询dept,捎带着把emp查询出来,没有对应的就NULL
on
e.deptno = d.deptno;
可见使用右外连接把OPERATIONS显示出来了,其ename为null。也就是说我把dept作为主表了,它必须全部显示,ename身为次表,没有对应的就null。
左连接
和右连接一样,比如还是上面的例子
select
e.ename,d.dname
from
dept d
left join
emp e
on
e.deptno = d.deptno;
效果是一样的。
小总结
带有right的是右外连接,又叫做右连接。
带有left的是左外连接,又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
外连接的查询结果条数一定是 >= 内连接的查询结果条数
多张表连接
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的链接条件
right join
d
on
a和d的连接条件
内连接和外连接可以混合,可同时出现。
案例:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
select
e.ename, d.dname,e.sal,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and hisal;
案例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
select
e.ename, d.dname,e.sal,s.grade,l.ename
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and hisal
join
emp l
on
e.mgr = l.empno;
变成13行记录了,少了ename=KING的,因为KING没有领导。为了显示所有员工名,需要用外连接
select
e.ename, e.sal, d.dname,s.grade, l.ename //员工表的名字,薪资。部门表的名字。薪资表的等级. 领导名字
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and hisal
left join //领导名字是稍带的,所以左边是主表
emp l
on
e.mgr = l.empno;
子查询
select语句中嵌套select语句,被嵌套的select语句称为子查询。
子查询都可以出现在哪里呢?
select
…(select).
from
…(select).
where
…(select).
where子句中的子查询:
案例:找出比最低工资高的员工姓名和工资?
之前说过:
select
ename,sal
from
emp
where
sal > min(sal);
是错误的写法,因为where执行顺序大于分组函数,where子句中不能直接使用分组函数
实现思路:
第一步:查询最低工资是多少
select min(sal) from emp; //输出 800
第二步:找出>800的
select
ename,sal
from
emp
where
sal > 800;
第三步:合并
select
ename,sal
from
emp
where
sal > (select min(sal) from emp);
from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)
案例:找出每个岗位的平均工资的薪资等级。
第一步:找出每个岗位的平均工资(按照岗位分组求平均值)
select job,avg(sal) from emp group by job;
第二步:把以上的查询结果就当做一张真实存在的表t。
select
t.*, s.grade
from
(select job,avg(sal) as avgsal from emp group by job) as t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;//上面如果不使用avg(sal) as avgsal, t.avg(sal)会报错,因为t.avg()被看做分组函数。
select后面出现的子查询(了解)
案例:找出每个员工的部门名称,要求显示员工名,部门名?
select
e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
下面这种写法报错:
select
e.ename,e.deptno,(select dname from dept) as dname
from
emp e;
注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,
多于1条,就报错了。
个人记忆:正常来讲,select a from b; 需要提供字段名和表。
现在,把 select c from d 看作一张表,那我们就不需要from表了,直接 select (select c from d),表示从“select c from d”这张表中查询c。
union合并查询结果集
案例:查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');
使用union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是:1000
a 连接 b一个结果:10 * 10 --> 100次
a 连接 c一个结果:10 * 10 --> 100次
使用union的话是:100次 + 100次 = 200次。
注意事项:①union在进行结果集合并的时候,要求两个结果集的列数相同。②结果集合并时列和列的数据类型也要一致。
limit(非常重要)
limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
百度默认:一页显示10条记录。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差。
可以一页一页翻页看。
用法
limit startIndex, length // startIndex 从0开始
缺省用法:
limit 5;
这是取前5.
案例:按照薪资降序,取出排名在前5名的员工?
select
ename,sal
from
emp
order by
sal desc
limit 5;//或者limit (0,5)
注意:mysql当中limit在order by之后执行。
分页
公式:第pageNo页:limit (pageNo - 1) * pageSize , pageSize
public static void main(String[] args){
// 用户提交过来一个页码,以及每页显示的记录条数
int pageNo = 5; //第5页
int pageSize = 10; //每页显示10条
int startIndex = (pageNo - 1) * pageSize;
String sql = "select ...limit " + startIndex + ", " + pageSize;
}
关于DQL语句的大总结
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序?
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit…
从,条件,分组,继续筛选,选择,排序,取部。
表的创建(建表)DDL语句 DDL包括:create drop alter
建表语句
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
表名:建议以t_ 或者 tbl_开始,可读性强。见名知意。
字段名:见名知意。
表名和字段名都属于标识符。
mysql中的数据类型
项目 | Value |
---|---|
varchar(最长255) | 可变长度的字符串比较智能,节省空间。会根据实际的数据长度动态分配空间。优点:节省空间缺点:需要动态分配空间,速度慢。姓名字段你选什么?每一个人的名字长度不同,所以选择varchar。 |
char(最长255) | 定长字符串不管实际的数据长度是多少。分配固定长度的空间去存储数据。使用不恰当的时候,可能会导致空间的浪费。优点:不需要动态分配空间,速度快。缺点:使用不当可能会导致空间的浪费。性别字段你选什么?因为性别是固定长度的字符串,所以选择char。 |
int(最长11) | 数字中的整数型。等同于java的int。 |
bigint | 数字中的长整型。等同于java中的long。 |
float | 单精度浮点型数据 |
double | 双精度浮点型数据 |
date | 短日期类型 |
datetime | 长日期类型 |
clob | 字符大对象最多可以存储4G的字符串。比如:存储一篇文章,存储一个说明。超过255个字符的都要采用CLOB字符大对象来存储。Character Large OBject:CLOB |
blob | 二进制大对象 Binary Large OBject专门用来存储图片、声音、视频等流媒体数据。往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,你需要使用IO流才行。 |
例:创建学生表
学号、姓名、年龄、性别、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
自定义默认值 :
create table t_student(
no int,
name varchar(32),
sex char(1) default 'm', //设置性别默认值为‘m’
age int(3),
email varchar(255)
);
删除表:drop table t_student;
// 当这张表不存在的时候会报错!
所以:
drop table if exists t_student;
插入数据insert (DML)
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
注意:字段名和值要一一对应。什么是一一对应?
数量要对应。数据类型要对应。
insert into t_students(no,name,sex,age,email) values(1,'zhangsan',1,20,'zhangsan@qq.com');
只insert一个字段:
insert into t_students(no) values (3);
是否可以再insert一个name,替换null? 不可以。注意:insert语句但凡是执行成功了,那么必然会多一条记录。没有给其它字段指定值的话,默认值是NULL。
insert语句中字段名可以省略,但是要把所有值全部写上:
insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
一次插入多条数据
insert into t_user(id,name,birth,create_time) values
(1,'zs','1980-10-11',now()),
(2,'lisi','1981-10-11',now()),
(3,'wangwu','1982-10-11',now());
插入日期 date
format作用:
select ename,format(sal, '$999,999') as sal from emp;
str_to_date:将字符串varchar类型转换成date类型
create table t_user(
id int,
name varchar(32),
birth date // 生日也可以使用date日期类型
);
mysql的日期格式:
项目 | Value |
---|---|
%Y | 年 |
%m | 月 |
%d | 日 |
%h | 时 |
%i | 分 |
%s | 秒 |
insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));
如果使用 ‘%Y-%m-%d’格式,str_to_date函数就不需要了.
insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
date_format:将date类型转换成具有一定格式的varchar字符串类型。
查询的时候可以以某个特定的日期格式展示
select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
select id,name,birth from t_user;
进行了默认的日期格式化,自动将数据库中的date类型转换成varchar类型。并且采用的格式是mysql默认的日期格式:’%Y-%m-%d’。
date和datetime两个类型的区别?
date是短日期:只包括年月日信息。
datetime是长日期:包括年月日时分秒信息
mysql短日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');
在mysql当中怎么获取系统当前时间?
now() 函数,并且获取的时间带有:时分秒信息!!!!是datetime类型的。
insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());
修改数据 update(DML)
update 表名 set 字段1=值1,字段名2=值2 where 条件;
注意:没有条件限制会导致所有数据全部更新。
update t_students set name='lisi',sex=2 where no = 1;
删除数据 delete (DML)
delete from 表名 where 条件;
注意:没有条件,整张表的数据会全部删除!
delete from t_students where no = 3;
快速创建表【了解内容】
mysql> create table emp2 as select * from emp;
原理:
将一个查询结果当做一张表新建!!!!!
这个可以完成表的快速复制!!!!
表创建出来,同时表中的数据也存在了!!!
create table mytable as select empno,ename from emp where job = 'MANAGER';
快速删除表中数据【truncate比较重要,必须掌握】
delete from tablename; //慢
delete语句删除数据的原理?(delete属于DML语句!!!)
表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放!!!
这种删除缺点是:删除效率比较低。
这种删除优点是:支持回滚,后悔了可以再恢复数据!!!
数据删了,但空间未被释放。缺点 效率低。优点 可恢复.
truncate 快速删除
truncate table tablename; //属于 DDL 语句
truncate语句删除数据的原理?
这种删除效率比较高,表被一次截断,物理删除。
这种删除缺点:不支持回滚。
这种删除优点:快速。
大表非常大,上亿条记录????
删除的时候,使用delete,也许需要执行1个小时才能删除完!效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前,必须仔细询问客户是否真的要删除,并警告删除之后不可恢复!
truncate是删除表中的数据,表还在!
删除表操作?
drop table 表名; // 这不是删除表中的数据,这是把表删除。
对表结构的增删改
对表结构的修改需要使用:alter
我们不需要掌握
约束(非常重要,五颗星*****)
什么是约束?
约束对应的英语单词:constraint
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的
完整性、有效性!!!
约束包括哪些?
非空约束:not null
唯一性约束: unique
主键约束: primary key (简称PK)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,oracle支持)
我们这里重点学习四个约束:
not null
unique
primary key
foreign key
非空约束:not null
create table t_vip(
id int,
name varchar(255) not null // not null只有列级约束,没有表级约束!
);
insert时必须包含name字段,否则报错。
唯一性约束: unique
create table t_vip(
id int,
name varchar(255) unique,// 约束直接添加到列后面的,叫做列级约束。
email varchar(255)
);
name字段不可以重复。(可以同时为NULL)
新需求:name和email两个字段联合起来具有唯一性
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 约束没有添加在列的后面,这种约束被称为表级约束。
);
此时,'zhangsan', 'zhangsan@123.com'
和 'zhangsan','zhangsan@sina.com'
,算作不同的字段。
unique 和not null可以联合
create table t_vip(
id int,
name varchar(255) not null unique
);
在mysql当中,如果一个字段同时被not null和unique约束的话,
该字段自动变成主键字段。(注意:oracle中不一样!)
主键约束(primary key,简称PK)非常重要五颗星*****
主键约束:就是一种约束。
主键字段:该字段上添加了主键约束,这样的字段叫做:主键字段
主键值:主键字段中的每一个值都叫做:主键值。
主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号!!!
记住:任何一张表都应该有主键,没有主键,表无效!!
主键的特征:not null + unique(主键值不能是NULL,同时也不能重复!)
主键值不能是NULL,同时也不能重复
主键值不能是NULL,同时也不能重复
// 1个字段做主键,叫做:单一主键
create table t_vip(
id int primary key, //列级约束
name varchar(255)
);
create table t_vip(
id int,
name varchar(255),
primary key(id) // 表级约束
);
复合主键:实际开发中不建议使用
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
一张表,主键约束只能添加1个。
主键值建议使用:
int
bigint
char
等类型。
不建议使用:varchar来做主键。主键值一般都是数字,一般都是定长的!
主键除了:单一主键和复合主键之外,还可以这样进行分类?
自然主键:主键值是一个自然数,和业务没关系。
业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。这就是业务主键!
在实际开发中使用业务主键多,还是使用自然主键多一些?
自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,
可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。
mysql自动维护主键机制:
create table t_vip(
id int primary key auto_increment, //auto_increment表示自增,从1开始,以1递增!
name varchar(255)
);
此时插入name,id会依次自增。
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
外键约束(foreign key,简称FK)非常重要五颗星*****
外键约束涉及到的相关术语:
外键约束:一种约束(foreign key)
外键字段:该字段上添加了外键约束
外键值:外键字段当中的每一个值。
业务背景:
请设计数据库表,来描述“班级和学生”的信息?
第一种方案:班级和学生存储在一张表中???
t_student
no(pk) name classno classname
----------------------------------------------------------------------------------
1 jack 100 北京市大兴区亦庄镇第二中学高三1班
2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点:
数据冗余,空间浪费!!!!
这个设计是比较失败的!
第二种方案:班级一张表、学生一张表??
t_class 班级表
classno(pk) classname
------------------------------------------------------
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三1班
t_student 学生表
no(pk) name cno(FK引用t_class这张表的classno)
----------------------------------------------------------------
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
外键约束,使得班级编号始终是有效的。如 不能添加102,因为102不存在。所以为了保证cno字段中的值都是100和101,需要给cno字段添加外键约束。那么:cno字段就是外键字段。cno字段中的每一个值都是外键值。
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno) //cno字段外键约束,引用t_class表的classno
);
注意:
t_class是父表
t_student是子表
删除表的顺序?
先删子,再删父。
创建表的顺序?
先创建父,再创建子。
删除数据的顺序?
先删子,再删父。
插入数据的顺序?
先插入父,再插入子。
思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
不一定是主键,但至少具有unique约束。
测试:外键可以为NULL吗?
外键值可以为NULL。
存储引擎(了解)
8.1、什么是存储引擎,有什么用呢?
存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)
存储引擎这个名字高端大气上档次。
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
8.2、怎么给表添加/指定“存储引擎”呢?
show create table t_student;
可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
在建表的时候可以在最后小括号的")"的右边使用:
ENGINE来指定存储引擎。
CHARSET来指定这张表的字符编码方式。
结论:
mysql默认的存储引擎是:InnoDB
mysql默认的字符编码方式是:utf8
建表时指定存储引擎,以及字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
8.3、怎么查看mysql支持哪些存储引擎呢?
mysql> select version();
±----------+
| version() |
±----------+
| 5.5.36 |
±----------+
命令: show engines \G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
mysql支持九大存储引擎,当前5.5.36支持8个。版本不同支持情况不同。
8.4、关于mysql常用的存储引擎介绍一下
MyISAM存储引擎?
它管理的表具有以下特征:
使用三个文件表示每个表:
格式文件 — 存储表结构的定义(mytable.frm)
数据文件 — 存储表行的内容(mytable.MYD)
索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间
提示一下:
对于一张表来说,只要是主键,
或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点:
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势!!!!
MyISAM不支持事务机制,安全性低。
InnoDB存储引擎?
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本(MVCC)和行级锁定
– 支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。
MEMORY存储引擎?
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,
这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征:
– 在数据库目录内,每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。(目的就是快,查询快!)
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点:查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。
事务transaction(重点:五颗星*****,必须理解,必须掌握)
(个人感觉很像java里的多线程)
什么是事务?
一个事务其实就是一个完整的业务逻辑。
是一个最小的工作单元。不可再分。
什么是一个完整的业务逻辑?
假设转账,从A账户向B账户中转账10000.
将A账户的钱减去10000(update语句)
将B账户的钱加上10000(update语句)
这就是一个完整的业务逻辑。
以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分。
这两个update语句要求必须同时成功或者同时失败,这样才能保证钱是正确的。
只有DML语句才会有事务这一说,其它语句和事务无关
insert
delete
update
只有以上的三个语句和事务有关系,其它都没有关系。
因为 只有以上的三个语句是数据库表中数据进行增、删、改的。
只要你的操作一旦涉及到数据的增、删、改,那么就一定要考虑安全问题。
说到底,说到本质上,一个事务其实就是多条DML语句同时成功,或者同时失败!
事务是怎么做到多条DML语句同时成功和同时失败的呢?
InnoDB存储引擎:提供一组用来记录事务性活动的日志文件
事务开启了:
insert
insert
insert
delete
update
update
update
事务结束了!
在事务的执行过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中,我们可以提交事务,也可以回滚事务。
提交事务?
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。
提交事务标志着,事务的结束。并且是一种全部成功的结束。
回滚事务?
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
回滚事务标志着,事务的结束。并且是一种全部失败的结束。
提交事务:commit
; 语句
回滚事务:rollback
; 语句(回滚永远都是只能回滚到上一次的提交点!)
mysql默认情况下是支持自动提交事务的。(自动提交)
什么是自动提交?
每执行一条DML语句,则提交一次!(更改了表数据,想rollback,发现回不去。因为mysql自动提交了)
关闭自动提交:
start transaction;//关闭自动提交机制
---------------------------------回滚事务----------------------------------------
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc', 'tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc', 'tj');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
| 10 | abc | tj |
+--------+-------+------+
2 rows in set (0.00 sec)
mysql> rollback; //回滚,没了。
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)
---------------------------------提交事务----------------------------------------
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
+--------+-------+------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc
Query OK, 1 row affected (0.00 sec)
mysql> commit; //提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
事务包括4个特性
A:原子性
说明事务是最小的工作单元。不可再分。
C:一致性
所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,
以保证数据的一致性。
I:隔离性
A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙,这道墙就是隔离性。
A事务在操作一张表的时候,另一个事务B也操作这张表会那样???
D:持久性
事务最终结束的一个保障。事务提交,就相当于将没有保存到硬盘上的数据
保存到硬盘上!
重点研究一下事务的隔离性
A教室和B教室中间有一道墙,这道墙可以很厚,也可以很薄。这就是事务的隔离级别。
这道墙越厚,表示隔离级别就越高。
事务和事务之间的隔离级别有哪些呢?4个级别
读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》
什么是读未提交?
事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是:
脏读现象!(Dirty Read)
我们称读到了脏数据。
这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!
读已提交:read committed 《提交之后才能读到》
什么是读已提交?
事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了什么问题?
解决了脏读的现象。
这种隔离级别存在什么问题?
不可重复读取数据。
什么是不可重复读取数据呢?
在事务开启之后,第一次读到的数据是3条,当前事务还没有
结束,可能第二次再读取的时候,读到的数据是4条,3不等于4
称为不可重复读取。
这种隔离级别是比较真实的数据,每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是:read committed
可重复读:repeatable read 《提交之后也读不到,永远读取的都是刚开启事务时的数据》
什么是可重复读取?
事务A开启之后,不管是多久,每一次在事务A中读取到的数据
都是一致的。即使事务B将数据已经修改,并且提交了,事务A
读取到的数据还是没有发生改变,这就是可重复读。
可重复读解决了什么问题?
解决了不可重复读取数据。
可重复读存在的问题是什么?
可以会出现幻影读。
每一次读取到的数据都是幻象。不够真实!
早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!
读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个!!!!!!!!!!!
序列化/串行化:serializable(最高的隔离级别)
这是最高隔离级别,效率最低。解决了所有的问题。
这种隔离级别表示事务排队,不能并发!
synchronized,线程同步(事务同步)
每一次读取到的数据都是最真实的,并且效率是最低的。
验证:
查看隔离级别:
SELECT @@tx_isolation
设置全局隔离级别:
验证:read uncommited
set global transaction isolation level read uncommitted;
改完记得退一下。
exit;
开两个窗口,分别为 事务A 和事务B
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
select * from t_user;//此时没有数据
start transaction;
insert into t_user values('zhangsan');//事务B窗口插入数据,但是没有提交
select * from t_user;//此时事务A可以查到事务B上插入的那条数据
read commited:
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;//此时没有数据
insert into t_user values('zhangsan');//事务B窗口插入数据,但是没有提交
select * from t_user;//虽然事务B插入了,但是没提交
commit;//事务B提交
select * from t_user;//此时事务A可以查到事务B上插入的那条数据
repeatable read
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;//假设此时表里有一条记录
insert into t_user values('lisi');//事务B插入
insert into t_user values('wangwu');//事务B插入
commit;//提交
select * from t_user;//还是只有原来那一条(重复读)
serializable
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;//此时无数据
insert into t_user values('abc');//插入一条
select * from t_user;//这条语句会一直卡着执行不了
//因为事务A还没有结束(commit或者rollback)