mysql学习笔记（动力节点）

1.连接数据库

1.1命令行连接

mysql -uroot -p123456 -- 连接
flush privileges; -- 刷新权限
-- 所有的命令都采用分号结尾，切换不需要
show databases; -- 查看所有的数据库
mysql> use school -- 切换数据库：use+数据库名
Database changed

show tables; -- 查看数据库所有的表
describe students; -- 查看表的信息

create database newdatabase; -- 创建一个数据库

exit; -- 退出连接 //注意分号结束一个语句

-- 单行注释
/*
多行注释
*/
-- 修改登录密码，登陆之后输入：
-- set password for username @localhost = password(newpwd);
-- 其中 username 为要修改密码的用户名，newpwd 为要修改的新密码
-- 再推出即可登入
set password for root @localhost = password('root')；  -- 新密码为root

1.2数据库 xxx 语言

DDL 对表结构增删改

DML 对表中数据增删改

DQL 查询

DCL 数据控制

TCL 事物控制

写命令行的地方：

点击【工具】菜单，选择【命令列界面】

2.操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

mysql关键字不区分大小写

2.1操作数据库

2.1.1创建数据库

-- 为mysql语句
create database [if not exists] west

2.1.2删除数据库

drop database [if exists] west

2.1.3使用数据库

use school -- 如果表名或字段名是一个特殊字符或者系统带的变量名字，需要在使用的名字左右加上Tab见上方的符号
-- e.g.
select `user` from student;

2.1.4查看数据库

--查看当前使用的是哪个
show databases;
-- 或：
select database;
-- 查看mysql的版本号
select version();

2.1.5查看其它库中的表

show tables from databases;

2.1.6查看表的结构/创建语句

decs table name;
show create table <table name>;

2.1.8 退出数据库

exit;

2.2 条件限制查询

select 字段 from 表名 where 条件;
    -- 若字段部分为*，证明查询所有内容，但速度较慢，JDBC中一般不用

2.2.1 and / or

and 优先级高于or，若一起使用记不清优先级时，可使用括号

2.2.2 between … and …

等同于 >= and <=

可用于字符串和数字，都是闭区间

2.2.3 in

in 等同于多个 on

select ename,job from emp where sal in (800,5000); -- 注意in后边不是区间，而是具体的值

not in : 不是800和5000的

2.2.4 模糊查询 like

需掌握两个符号：

% : 多个字符 _ : 一个字符

比如找出名字里含O的：

select ename from emp where ename like '%O%';

找出名字里第二个字母为A的

select ename from emp where ename like '_A%';

找出名字里第三个字母为A的

select enmae from emp where ename like '__A';

找出名字里有下划线的

~~select enmae from emp where ename like '%_%';~~

因为‘_’表示任意一个字符，这样写会查询到所有名字，应该用转义字符：

select enmae from emp where ename like '%/_%';

找出名字中最后一个字是T的：

select ename from emp where ename like '%T';

2.3 排序（升序、降序）

2.3.1 单个字段

按照工资升序，找出员工名字和工资

select 
	ename,sal
from
	emp
order -- 排序
	by -- 通过
 		sal; -- 工资

注意，默认为升序

若需指定：升序 asc 降序 desc

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;

2.3.2 多个字段

越靠前的字段起的主导作用越大,只有当前面的字段无法完成排序时，才会启用后面的字段。

按照工资降序，若工资相等，按照名字升序排列。找出员工名字和工资：

select enmae,sal from emp order by sal desc,eame asc;

找出工作岗位是salesman的员工,并且按工资降序排列

select 
	ename,job,sal
from
	emp
where
	job='salesman'
order by
	sal 
		desc;

2.4 分组函数 (group function)

2.4.1 一共五个函数

属于多行处理函数 : 输入多行，输出一行

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

对于某一组数据（字段）操作

e.g 找出工资总和

select sum(sal) from emp;

2.4.2 单行处理函数

输入n行，输出n行，以ifnull()函数示例

前提：所有数据库中只要有null参与的运算，其最后结果一定是null，但所有分组函数自动忽略null.

引入：ifnull（）空处理函数

ifnull（可能为空的数据，被当作什么处理）

e.g

select ename,ifnull(comm,0) as comm from emp;
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

2.4.3 分组函数使用的语法规则

分组函数不可直接使用在where子句当中（下边解释）

count(*)和count(具体的某个字段)的区别：

count(*)：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）

count(comm)：表示统计comm字段中不为NULL的数据的总数量。

2.5 分组查询group by

group by：按照某个字段或某些字段进行分组

having：对分组之后的数据进行再次过滤

两个搭配使用

案例：找出每个工作岗位的最高薪资

select max(sal) from emp group by job;
-- 先分组再找最大值

注意：分组函数一般都会和group by联合使用，且分组函数都是在group by结束之后才执行的，而group by在where

之后执行，所以分组函数不能在where中使用（相当于还未分组，不能使用分组函数）

当一句sql语句没有group by语句时，整张表的数据自成一组

当一句sql语句有group by语句时，select后边跟着的字段只能是分组函数和参与分组的字段，否则报错或结果不正确（Oracle比mysql语法规则严谨）

2.6分组之后再次过滤

用 having 还是 where ?

-- 找出薪资大于2900的部门
select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
-- 这种方式效率低，分组后又舍弃数据，此时应使用where先过滤掉小于2900的再进行分组（基于where比group by先执行
select deptno,max(sal) from emp where sal > 2900 group by deptno; 
-- 效率较高，能使用where过滤的尽量使用where，涉及到求平均等分组函数的情况则只能用having

3.DQL语句

总结：以上为单表查询，接下来是多表查询

3.1执行顺序：

select

from

where

having

order by

3.2 查询结果集的去重

-- 在字段前加distinct(截然不同的，完全分开的；清晰的)
select distinct job from emp;

注意：distinct：只能出现在所有字段的前面，且对后边的所有字段联合去重

3.3 连接查询

定义：多张表共同查询出最终的结果

3.3.1 连接查询的分类

根据表的连接方式：

内连接：

等值连接

非等值连接

自连接

外连接：

左外连接（左连接）

右外连接（右连接）

全连接

笛卡尔乘积现象：若未对连接查询的多张表的字段进行条件限制，最后的查询结果为多张表字段数量的乘积。

但是，加条件过滤避免了笛卡尔积现象也不能减少底层记录的匹配次数，只是显示出来的减少了。

3.3.2 给表起别名

select e.ename,d.dname from emp e,dept d;-- as或者空格都可以

起别名的好处：效率高（防止多张表有字段相同，需得访问多遍）、可读性好

3.3.3 内连接

等值连接：查询条件是等量关系

e.g查询每个员工的部门名称 (join on语句)

-- sql99语法结构更清晰，将查询条件与过滤条件where分离
select
	e.ename,d.dname
from 
	emp
inner join -- inner可以省略，带着可读性更好些（表示内连接）
	dmp
on 
	e.dptno = d.dptno;
where...

非等值连接：查询条件不是等量关系

select
	e.ename,e.salary,s.grade
from 
	emp
inner join -- inner可以省略，带着可读性更好些（表示内连接）
	dmp
on 
	e.salary btween s.lsal and s.higsal; -- 非等量关系
where...

自连接：一张表看作两张表，自己连自己。

e.g找出每个员工的上级领导，要求显示员工名和对应的领导名

将两张表视作员工表和领导表，员工的领导编号=领导的员工编号时即找出对应关系

select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp a
inner join
	emp b
on -- 条件
	a.mgr = b.empno;

3.3.4 外连接（使用的多）

3.3.4.1 与内连接区别

内连接：A和B表进行连接，将两个表能够匹配上的记录查询出来。A、B表没有主副之分，是平等的。

外连接：AB两表中一主表一副表，主要查询主表中的数据，捎带查询附表，若副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。特点：主表的数据无条件的全部查询出来。

比如查员工领导，大boss也是员工，虽然没有领导但也不能丢失数据，要查出来的。

3.3.4.2 分类

左外连接（左连接）：表示左边的表使主表。

右外连接（右连接）：表示右边的表使主表。

两种连接写法不同。

-- 左外连接，左表占主导地位
select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp a
left outer join -- outer可省略
	emp b
on -- 条件
	a.mgr = b.empno;
-- 右外连接，右表占主导地位
select 
	a.enamem as '员工名',b.ename as '领导名'
from
	emp b -- 两表交换位置，等效
right join
	emp a
on -- 条件
	a.mgr = b.empno;

3.3.5 三张表以上的连接

找出每一个员工的部门名称、工资等级和上级领导。

员工和部门名称、工资等级内连接，和上级领导左连接，嵌套查询

select e.ename as '员工',d.name,s.grade,e1.ename '领导' 
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;

3.4 子查询

3.4.1 where子句中使用子查询

找出高于平均薪资的员工信息

-- 第一步：找出平均薪资
select avg(sal) from emp;
-- 第二步：where过滤
select * from emp where sal > 2073.21;
-- 第一二步合并：
select * from emp where sal > (select avg(sal) from emp);

3.4.2 from子句中使用子查询

找出每个部门平均薪水的薪资等级

-- 一：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
-- 二：将以上查询结果当作临时表t,让t和salgrade表连接查询，条件是t.avgsal 在其薪资范围之间
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.higsal;

找出每个部门平均的薪资等级

-- 一：找出每个员工的薪水等级
select e.name,e.sal,e.deptno,s.grade from emp join salgrade on e.sal between s.loasal and s.higsal;
-- 二：基于以上结果继续按照部门分组，求薪水等级的平均值
select 
	e.deptno,avg(s.grade) -- 基于以上查询出的等级结果求每个组的平均值
from 
	emp 
join 
	salgrade 
on 
	e.sal between s.loasal and s.higsal
group by -- 没有必要作临时表，继续往下写效率更高
	e.deptno;

3.4.3 select后边嵌套子查询

找出每个员工所在的部门名称，要求显示员工姓名和部门名

-- 之前学的：
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
-- 直接在select后边嵌套子查询
select e.ename,e.deptno,(select d.dname from dept d where d.deptno = e.deptno) as dname from emp e;

3.5 union（可以将查询结果相加）

找出工作岗位是a和b的员工

-- 一：
select e.ename,job from emp e where job = 'a' or job = 'b';
-- 二：
select e.ename,job from emp e where job in('a','b'); -- in相当于多个on，是值不是区间
-- 三：union
select e.ename,job from emp e where job = 'a'
union
select e.ename,job from emp e where job = 'b';

union可以将不同表中的数据拼接到一起，但两个字段的列数需一致

3.6 limit（分页查询用）

mysql特有

作用：取结果集中的部分数据

3.6.1 语法机制

limit startIndex,length; -- startIndex起始位置，length取几个长度，注意start是开区间，不包括起始位置

-- 比如，取工资前五名的员工
select ename,sal from emp order by sal desc limit 0,5;

3.6.2 Limit是sql语句最后执行的一个环节

4.DML语句只操作表中的数据

4.1 表的创建

4.1.1 表的基本数据类型

varchar：可变长字符串

data：日期类型

BLOB：二进制大对象（存储图片、视频等流媒体信息）

CLOB：字符大对象（存储较大文本，比如，可以存储较大的字符串）

e.g 创建一个学生表

create table t_student(
-- 先写变量名再加数据类型
	no bigint,
	name varchar(266),
	sex char(1) default 1, -- 默认性别的值为1
	classno varchar(),
	birth char(10)，
);

4.1.2 insert 语句插入数据

第一种方式

语法格式：

insert into 表明（字段名1，2，3，4) values(值1，2，3，4);
注意：字段的数量和数据类型与值的数量和数据类型一一对应

insert into t(no,name,sex) values(1,'Marry','0');
-- 注意数据库用的utf-8,dos命令窗口采用的GBK，输入中文会出现乱码

需要注意的地方：

当一条inser语句执行成功之后，表中必然会多一行记录，而新加的记录即使某些字段是NULL（未输入，系统自动指配），后面也没法用insert语句插入数据了，只能用update更新

第二种方式

insert into t_student values(值。。。)

这种方法输入的值必须和列数匹配

第三种方式 -- 一次插入多行数据

插入多行顺序时不用写字段名字，但要确保值的顺序与表中的列顺序相同

insert into t_student values(值。。。),（值。。。）

4.2 表的复制以及批量插入

表的复制：

create table emp2 as select * from emp;
-- 将查询语句作为表创建出来

将查询结果插入到一张表中：

insert into dept1 select * from dept;

4.3 修改表中的数据 ---- update

语法格式：

update 表名 set 字段名1=值1，字段名2=值2.。。。where 条件;

没有条件，整张表的数据全部更新

4.4 删除数据

语法格式：

delete from 表名 where 条件;

没有条件，整张表的数据全部删除

删除一行：

delete from dept where deptno=10;

删除大表（重点）：

delete :时间很长，效率慢，原因：没有释放真实的存储空间，可以找回delete删掉的数据

表截断 truncate：风险较高的不能后悔的方法

5 DDL语句

5.1关于表结构的修改

很少发生，用工具，出现在java中的sql：insert delete update select (对表中的数据操作)

增删改查术语：CRUD（各取首字母）Create（增） Retrieve(检索) Update(修改) Delete(删除)

drop: alter table 表名 drop 字段

drop table

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

5.2 查看表结构的信息

1.desc查看表结构的详细信息

desc table_name；
-- PS：此处desc是describe的缩写，用法： desc 表名/查询语句

2.desc降序排列数据

select ename,sal from emp order by sal desc; 
手动指定按照薪水由大到小排序（降序关键字desc） 
select ename,sal from emp order by sal asc; 
手动指定按照薪水由小到大排序（升序关键字 asc）
PS：此处desc是descend的缩写， 用法：select * from 表 order by 字段 desc

6 表的约束语句

定义：约束用于限制加入表的数据的类型。保证数据的合法性、完整性、有效性。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

比如，加唯一性约束（unique）避免昵称重复，

非空约束（not null），

主键约束（primary key）:不能为空也不能重复，简称PK

外键约束（foreign），简称PK

检查约束（check），注意Oracle数据库有检查约束，mysql不支持

6.1 非空约束 not null

只有列级约束，没有表级约束

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255) not null,
    psd varchar(255),
);
insert into t_user(id,username,psd) values(1,123);
-- 报错，必须一一匹配

6.2 唯一性约束 (unique)

修饰的字段具有唯一性，不能重复，但可以为NULL

6.2.1 给一个字段添加unique属性

drop table if exists t_user;
create table t_user(id int,username varchar(255) unique);
insert into t_user(id,username) values(2,'zhang');
insert into t_user(id,username) values(3,'zhang');
-- 报错：  Duplicate entry 'zhang' for key 'username'，
-- duplicate重复 entry进入
insert into t_user(id) values(3);
-- 未报错，查询后多一行数据

6.2.1 给多个字段添加unique属性

情况一：表级约束

drop table if exists t_user;
-- 这种方法对两个字段一起约束。只要两个不完全相同就行
create table t_user(id int,username varchar(255),unique(id,username) );
insert into t_user values(1,'li'),(2,'li'),(1,'zhang');
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
|    1 | li       |
|    1 | zhang    |
|    2 | li       |
+------+----------+
-- 结果正确

情况二：列级约束

drop table if exists t_user;
-- 这种方法分别对两个字段约束。一个字段都不能重复，每个字段都具有唯一性
create table t_user(id int unique,username varchar(255) unique);
insert into t_user values(5,'li'),(5,'zhang');
-- ERROR 1062 (23000): Duplicate entry '5' for key 'id'

6.3 主键约束（primary key）

一张表的主键约束只能有一个

6.3.1 列级约束

drop table if exists t_user;
create table t_user(id int primary key,-- 列级约束
                    username varchar(255),email varchar(255));
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
+----+----------+------------+
insert into t_user(id,username,email) values(1,'la','la@123.com');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- 除主键之外的字段可以为空
insert into t_user(id,email) values(7,'ee');
+----+----------+------------+
| id | username | email      |
+----+----------+------------+
|  1 | zs       | zs@123.com |
|  2 | ls       | ls@123.com |
|  3 | ww       | ww@123.com |
|  5 | gg       | NULL       |
|  7 | NULL     | ee         |
+----+----------+------------+
-- 主键不能为空
insert into t_user(email) values('xx');
-- ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- 主键不能重复
insert into t_user(id,email) values(7,'ee');
-- ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMA'
-- 由上可知，id为主键，因为添加了主键约束，所以==主键字段不能重复也不能为NULL==

6.3.2主键的作用

表的设计三范式中第一范式就要求任何一张表都应该有主键，其作用：

主键值是这行记录在这张表的唯一标识（类似身份证号码

6.3.3 主键的分类

根据主键字段的数量划分：

单一主键：（常用）

复合主键：多个字段联合起来添加一个主键（不建议使用，违背三范式）

根据主键性质划分：

自然主键：主键值最好是一个和业务没有关系的自然数（推荐这种方式）

业务主键：主键值和系统的业务挂钩，比如，拿着身份证号做主键（不建议使用）修改不方便

最好不要拿和业务挂钩的字段作为主键，因为以后的业务一旦发生大概，主键值可能也需要随之变化，但主键变化可能会导致键值重复

6.2.3 使用表级约束方式定义主键

drop table if exists t_user;
create table t_user(id int,username varchar(255),primary key(id));
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | ws       |
+----+----------+
insert into t_user(id,username) values(3,'oo');
-- ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

主键约束的列级约束和表级约束的区别和唯一性约束的差不多，不同之处是写在每个字段之后还是写在最后边

复合主键：不建议使用！！！

主键约束的复合主键就是将多个字段联合起来作为主键，联合起来不能重复也不能为空

6.2.4 主键值不可以为NULL

简单记忆为每个人不能没有身份证号

insert into t_student(sname) values('zz');
-- ERROR 1364 (HY000): Field 'sno' doesn't have a default value

6.4 mysql提供主键值自增 (重要) auto_increment

drop table if exists t_user;
-- 设置主键后加auto_increment,主键实现自增，id字段自动维护一个自增的数字，从1递增
create table t_user(id int primary key auto_increment,username varchar(255));
-- 只传入value的值
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('v');
insert into t_user(username) values('d');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | v        |
|  4 | d        |
+----+----------+

6.5 外键约束 (foreign key)

e,g,设计数据库表，维护学生和班级的信息

外键定义在子表引用父表数据的字段

先创建父表再创建子表，删除数据时先删除子表再删除父表，类似于继承的构造和析构，子表引用父表的数据

先插父再插子

-- 因为之前有这两个表，所以删表需要先删子表再删父表，否则会报错
drop table if exists t_student;
drop table if exists t_class;
-- 先创父再创子
create table t_class( cno int primary key,cname varchar(255) );
create table t_student( sno int,sname varchar(255),classno int,primary key(sno),foreign key(classno) references t_class(cno) ); -- reference：引用
insert into t_class values(101,'xxxxxx');
insert into t_class values(102,'yyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',101);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_student;
select * from t_class;

insert into t_student values(7,'tt',103);-- 对应班级不存在，a child row：子记录
-- 报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

6.5.1 sql脚本的执行

运行sql脚本文件，连接数据库时直接 source+脚本路径即可，路径不可以加引号

source E:\Ty md\mysql\school.sql
Query OK, 0 rows affected (0.01 sec)

……..很多OK,按语句执行

	+------+-------+---------+
	| sno  | sname | classno |
	+------+-------+---------+
	|    1 | zs1   |     101 |
	|    2 | zs2   |     101 |
	|    3 | zs3   |     101 |
	|    4 | zs4   |     102 |
	|    5 | zs5   |     102 |
	|    6 | zs6   |     102 |
	+------+-------+---------+
6 rows in set (0.00 sec)

	+-----+--------+
	| cno | cname  |
	+-----+--------+
	| 101 | xxxxxx |
	| 102 | yyyyyy |
	+-----+--------+
2 rows in set (0.00 sec)

6.5.2 外键值可以为NULL

insert into t_student(sno,sname) values(15,'pp');
select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | zs1   |     101 |
|    2 | zs2   |     101 |
|    3 | zs3   |     101 |
|    4 | zs4   |     102 |
|    5 | zs5   |     102 |
|    6 | zs6   |     102 |
| NULL | pp    |     101 |
|   15 | pp    |    NULL |
+------+-------+---------+

6.5.3 外键不一定是主键

外键引用的别的表某个字段不一定必须要主键，但该字段必须要有唯一性，有unique约束

7. 存储引擎（了解）

mysql特有，为表的存储方式( ORACLE中有对应的机制，名字就叫“表的存储方式” ）

7.1 完整的建表语句

create table emp( id int,name varchar(255) );
-- 查看建表语句：
show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT COLLATE=utf8mb4_bin |
-- 可以看出默认存储引擎为InnoDB，默认字符集是utf8mb4_bin
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 完整的建表语句
create table `emp` ( `id` int(11) DEFAULT NULL,`name` varchar(255)  COLLATE utf8mb4_bin DEFAULT NULL)ENGINE=InnoDB DEFAULT COLLATE=utf8mb4_bin;

7.2 查看不同版本支持的存储引擎

每个存储引擎都有其不同的优缺点，应合理选择

-- 显示本版本mysql 5.7.41 支持的存储引擎方式
show engines \G;
-- 共九个
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
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: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
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: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

7.3 常见的存储引擎

7.3.1 MyISAM

Transaction事务，MyISAM不支持事务，是mysql最常用的存储引擎，但不是默认的

使用三个文件表示一张表：分别用来存储表结构（.frm)、表数据（.MYD)、表的索引（.MYI)

      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
  Transactions: NO
          XA: NO
  Savepoints: NO

 Transaction事务，MyISAM不支持事务，是mysql最常用的存储引擎，但不是默认的
使用三个文件表示一张表：分别用来存储表结构（.frm)、表数据（.MYD)、表的索引（.MYI)
优点：可被压缩，可以转换为只读表，提高检索效率
缺点：不支持事务

7.3.2 InnoDB

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
  
  优点：支持事务、行级锁、外键等。这种存储引擎使数据的安全得到保障。
  表的结构存储在.frm文件中
  数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩和转换成只读
  数据库崩溃之后提供自动恢复机制
  支持即联删除和级联更新

7.3.3 MEMORY

	 Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
  
缺点：不支持事务。所有数据和索引存储在内存中，容易丢失
优点：查询索引速度最快
以前叫做HEPA引擎

8 事务（Transaction） -- 很重要

8.1 事务的概述

一个事务是一个完整的业务逻辑单元，不可再分

比如，银行账户转账，从a账户转账10000到b账户，需要执行两次upadate语句

update emp set balance = balance - 10000 where name = 'a';
update emp set balance = balance + 10000 where name = 'b';
-- 以上两条语句必须同时成功或同时失败，不然金额错误
-- 因此需要使用数据库的事务机制

8.2 和事务相关的语句：DML

insert delete update

因为事务的存在是为了保障数据的完整性和安全性，而DML语句是操作表中的数据的

8.3 事务的原理

执行顺序写入缓存，留下操作历史记录，在提交或事务回滚后被清除

事务提交（commit）：对应文件随之改变

事务回滚（rollback）：将该事务已经完成对数据库的更新操作撤销

两个标志一出来都表示事务结束了

8.4 事务的四大特性（ACID）

1.原子性（atomicity）

一个事务必须被视为一个不可分割的最小的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

2.一致性（consistency）

要么同时成功，要么同时失败

数据库总是从一个一致性的状态转换到另一个一致性的状态（在前面的例子中，在没有执行取款前，余额总数为5000，在执行完事务后，取出来的钱再加上银行余额的总数跟原来的余额保持一致性原则，否则要么事务中的语句没有执行完，要么执行过程中出现异常）

3.隔离性（isolation）

事务A与事务B之间有距离

通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

4.持久性（durability）

数据必须持久化到硬盘文件里，事务才算成功地结束

一旦事务提交，则其所做的修改会永久保存到数据库（此时即使系统崩溃，修改的数据也不会丢失。）

8.5 事务的隔离性

事务隔离性存在隔离级别，理论上隔离级别有4个：

第一级别：读未提交（read uncommitted)

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

读未提交存在脏读（Dirty commited)现象：表示读到了脏的数据

第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以提取到

这种隔离级别解决了脏读现象

存在的问题：不可重复读，可能每次读到的数据不一样

第三级别：可重复读（read committed）

只要事务没结束，数据一直在，重复读取的数据相同

这种隔离级别解决了不可重复读的问题

存在读取到的数据是幻象的问题，读到的是备份数据

第四级别：序列化读/串行化读

解决了所有问题

效率低，事务需要进行排队

Oracle数据库默认的隔离级别是：读已提交

Mysql数据库默认的隔离级别是：可重复读

8.6 演示事务

mysql事务默认情况下自动提交（自动提交：只要执行任意一条DML语句则提交一次）

关闭自动提交：start transaction

演示回滚：

-- 创表
drop table if exists t_user;
create table t_user ( id int primary key auto_increment,username varchar(255) );
-- 演示回滚失败
insert t_user(username) values('zs');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
-- 写一句DML语句后自动提交（committ),回滚后仍是一条记录

-- 演示：使用start transaction;
start transaction;
insert t_user(username) values('ls');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
+----+----------+
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
-- 回滚成功

演示提交：

start transaction;
insert t_user(username) values('ww');
insert t_user(username) values('jack');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ww       |
|  4 | jack     |
+----+----------+
commit;rollback;
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ww       |
|  4 | jack     |
+----+----------+
-- commit提交后数据就写入硬盘了，rollback只能回滚到上一次的提交点

8.7 使用两个事务演示隔离级别

需要开两个cmd窗口表示两个事务

-- 设置事务的隔离级别，global为全局的意思
set global transaction isolation level read uncommitted;
-- 查看事务的全局隔离级别
select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
exit;

-- 修改完隔离级别后，所有数据库用户必须退出重新登录，才能使新的隔离级别生效
-- 开两个cmd窗口，都开启事务
-- 1.读未提交：这边写一条数据，还未提交或回滚（即写入硬盘），另一边再查询数据就多出新写的记录
-- 2.读已提交：这边插入数据并提交后，另一边才能查询到记录
-- 3.可重复读：即使这边删除表并提交，那边也可以查询到数据（读的是备份数据）
-- 4.序列化（serializable）/串行化：事务按顺序进行，这边的事务未提交，那边的DML语句一直处于等待状态，一旦这边的事务commit，那边瞬间出结果

9 索引(index)

9.1 定义

索引是给单个字段添加，底层用的B_tree的数据结构

索引相当于目录，在数据库中，查询一张表有两种方式：

第一种：全表扫描

第二种：根据索引检索（缩小扫描范围，提高检索效率）

9.2 添加索引的条件

不能随意添加索引，属于数据库中的对象，数据更改时索引也需要维护，数据经常修改的不适合添加索引；

添加索引：数据量庞大（根据需求和环境）、该字段的DML操作很少、该字段经常出现在where子句中

注意：主键和具有unique约束 的字段会自动添加索引

所以，根据主键检索效率较高，尽量使用主句。

9.3 添加 / 删除索引的方式

用explain查看sql语句的执行计划：

explain select username from t_user where username = 'za';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+------
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filt
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+------
-- type ：ALL表示全表扫描，rows表示扫描的记录数量

给username添加索引：

create index 索引名称 on 表名（字段名）；

create index t_username_index on t_user(username);
explain select username from t_user where username = 'zs';
 -- type ：ref，key变成了索引名，rows=1：只查询了一条记录

给username删除索引：

drop index 索引名 on 表名；

drop index t_username_index on t_user;

9.4 索引底层的实现原理

底层索引进行了排序，分区，表的索引会携带着数据在表中的物理地址，作为b+树的叶子节点存储，通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率最高。

9.5 索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加一个索引

主键索引：主键上自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

9.6 索引的失效

模糊查询时第一个通配符时‘%’，索引无法定位分区，只能使用全局扫描，索引失效

10 视图（了解）

10.1 定义

视图：站在不同的角度看同一张表的数据。（看作另一张表）

创建：create view myview as select empno,ename from emp;

只有select（DQL）语句可以创建视图对象

删除：drop view myview;

create table emp(empno int,ename varchar(255) );

注意：对视图进行CRUD会影响到原表数据

10.2 面向视图操作

mysql> select * from myview;
+-------+-------+
| empno | ename |
+-------+-------+
|     1 | zs    |
|     2 | ls    |
|     3 | ww    |
+-------+-------+

10.3 视图的作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

11 DBA命令（了解）

11.1 将数据库中的数据导出

在windows的dos命令窗口中实行（不需要登录数据库）：

-- 1.导出整个库
mysqldump 数据库表名>D:\文件名.sql -uroot -proot
-- 将左边的表导入到（>是导入的意思）右边的文件中，要有数据库的密码才行

-- 2.导出指定数据库的指定表
mysqldump 数据库 表名>D:\文件名.sql -uroot -proot

11.2 导入数据

source(之前用过)

12 数据库设计的三大范式

12.1 定义

设计表的依据。按照这个三范式设计的表不会出现数据冗余。

12.2 三范式

第一范式：任何一张表都应该有主键，每个字段的原子性不可再分

第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖（不完全依赖）

多对多关系：用三张表，关系表里有两个外键（两张表表示信息，第三张表只用关系表示）

第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖

一对多关系：两张表，多的表加外键

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余数据换执行速度

12.3 一对多的设计

两种方案：主键共享、外键唯一

posted @ 2023-05-04 11:55 hangsingplus Views(111) Comments(0) Edit 收藏举报

刷新页面返回顶部

hangsingplus

mysql学习笔记（动力节点）

1.连接数据库

1.1命令行连接

1.2数据库 xxx 语言

2.操作数据库

2.1操作数据库

2.1.1创建数据库

2.1.2删除数据库

2.1.3使用数据库

2.1.4查看数据库

2.1.5查看其它库中的表

2.1.6查看表的结构/创建语句

2.1.8 退出数据库

2.2 条件限制查询

2.2.1 and / or

2.2.2 between … and …

2.2.3 in

2.2.4 模糊查询 like

2.3 排序（升序、降序）

2.3.1 单个字段

2.3.2 多个字段

2.4 分组函数 (group function)

2.4.1 一共五个函数

2.4.2 单行处理函数

2.4.3 分组函数使用的语法规则

2.5 分组查询group by

2.6分组之后再次过滤

3.DQL语句

3.1执行顺序：

3.2 查询结果集的去重

3.3 连接查询

3.3.1 连接查询的分类

3.3.2 给表起别名

3.3.3 内连接

3.3.4 外连接（使用的多）

3.3.4.1 与内连接区别

3.3.4.2 分类

3.3.5 三张表以上的连接

3.4 子查询

3.4.1 where子句中使用子查询

3.4.2 from子句中使用子查询

3.4.3 select后边嵌套子查询

3.5 union（可以将查询结果相加）

3.6 limit（分页查询用）

3.6.1 语法机制

3.6.2 Limit是sql语句最后执行的一个环节

4.DML语句 只操作表中的数据

4.1 表的创建

4.1.1 表的基本数据类型

4.1.2 insert 语句插入数据

4.2 表的复制以及批量插入

4.3 修改表中的数据 ---- update

4.4 删除数据

5 DDL语句

5.1关于表结构的修改

5.2 查看表结构的信息

1.desc查看表结构的详细信息

2.desc降序排列数据

6 表的约束语句

6.1 非空约束 not null

6.2 唯一性约束 (unique)

6.2.1 给一个字段添加unique属性

6.2.1 给多个字段添加unique属性

6.3 主键约束 （primary key）

6.3.1 列级约束

6.3.2主键的作用

6.3.3 主键的分类

6.2.3 使用表级约束方式定义主键

6.2.4 主键值不可以为NULL

6.4 mysql提供主键值自增 (重要) auto_increment

6.5 外键约束 (foreign key)

6.5.1 sql脚本的执行

6.5.2 外键值可以为NULL

6.5.3 外键不一定是主键

7. 存储引擎（了解）

7.1 完整的建表语句

7.2 查看不同版本支持的存储引擎

7.3 常见的存储引擎

7.3.1 MyISAM

4.DML语句只操作表中的数据

6.3 主键约束（primary key）

8 事务（Transaction） -- 很重要