Mysql学习笔记

最新推荐文章于 2024-05-14 19:45:46 发布
最新推荐文章于 2024-05-14 19:45:46 发布
阅读量405 收藏 2
点赞数
文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42116025/article/details/115719510
版权

SQL语句

DQL（数据查询语言）：查询语句，凡是select语句都是DQL；
DML（数据操作语言）：insert delete update，对表中的数据进行增删改；
DDL（数据定义语言）：create drop alter，对表结构的增删改；
TCL（事物控制语言）：commit提交事务，roolback回滚事务。
DCL（数据控制语言）：grant授权，revoke撤销权限

登录数据库：
1、dos转到数据库bin目录下
mysql -uroot -p（密码）

表（table） 列/字段(column) 行（表中的记录）
每个字段都有：字段名称、字段数据类型、字段约束、字段长度

语句执行顺序：

select		5
..
from		1
..
(join on)	2
where
..
group by	3
..
having		4
..
order by	6
..
limit 		7
..

常用命令：

2、查看数据库

show databases;（Mysql命令）

3、创建数据库

create database power；（Mysql命令，数据库名称自己命名）

4、指定当前默认数据库

use power;

4、1、初始化表

source D:\Mysql\power.sql

将自建sql库文件覆盖到power库中

5、查看当前数据库中有哪些表

show tables;

6、查看其它数据库中的表（exam库）

show tables from exam;

7、查看表的创建语句（emp表）

show create table emp;

8、其他指令

	desc exam	显示表结构（exam库）
	select database();	查看当前使用的哪个数据库
	select version();	查看mysql的版本号
	\c		结束一条语句
	exit quit \q (ctrl+c)		退出mysql

1、（任何一条sql语句以为 " ; " 结尾）
2、（sql语句不区分大小写）
3、（标准sql语句要求字符串用单引号括起来‘中国’）

一、查询语句DQL（select）

​ select 字段名1,字段名2,字段名3……from 表名；

	select ename from emp;（查找）
​	select sal*12 from emp; (字段可以参与数学运算)
​	select sal*12 as yearsal from emp; (字段重命名)
​	select sal*12 as ‘年薪’ from emp; (中文需加单引号)
​	select * from emp；（查询所有字段）（效率较低 不建议常使用）

1、条件查询(where)：

​ select 字段，字段…… from 表名 where 条件；
​ （执行顺序，先from在where最后select）
查询工资=5000的员工姓名：
​

	select ename from emp where sal=5000;

查询smith的工资：
​

	select sal from emp where ename='smith';

找出工资>=3000的员工：
​

	select  ename,sal from emp where sal>=3000;

找出工资！=3000的员工：
​

	select ename,sal from emp where sal<>3000;
	select ename,sal from emp where sal！=3000;

找出工资在1100和3000之间的员工，包括1100和3000：

	select ename,sal from emp where sal>=1100 and sal<=3000;
	select ename,sal from emp where sal between 1100 and 3000;
between and

（between…and…是闭区间 between and 使用时必须左小右大）
（between and 除了数字方面外，还可以用在字符串方面，字符串原则 左闭右开）
找出名字在a和c（不包括c）的员工

	select ename from emp where ename between 'a'and 'c';
is null/ is not null

找出哪些人没有津贴：数据库中NULL不是一个值，代表为空；(is null /is not null)

	select ename，comm from emp where comm is null;
	select ename，comm from emp where comm is null or comm=0;
and/or

(and并且，or或者,and优先级高于or)
(运算符优先级不确定是加（小括号) ）
找出工作岗位是manager和salesman的员工

	select ename,job from emp where job='manager' or job='salesman';

找出薪资大于1000的并且部门编号是20或30部门的员工：

	select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);
in等同or

in等同于or（或者）（not in）不在 枚举
in（）后面的值不是区间 视具体的值
找出工作岗位是manager或salesman的员工

	select ename,job from emp where job='salesman' or job='manager';
	select ename,job from emp where job in('salesman','manager');
	select ename,job from emp where job not in('salesman','manager');

2、模糊查询(like)

（模糊查询中 两个特殊符号 %代表任意多个字符，_代表一个字符）
查找名字中含有o的：

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

查找名字中第二个字母是A的

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

找出名字中有下划线的(\转义字符)

	select ename from emp where ename like '%\_%'；

找出名字中最后一个字母是t的

	select ename from emp where ename like '%t';
排序（order by）

(默认升序；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;
	select ename,sal from emp order by sal desc,ename asc;

按照第六列排序（默认升序）

	select ename,sal from emp order by 6;

找出工作岗位是salesman的员工，并且按照薪资降序排列：

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

(1、先执行from，2、在执行where，3、在执行select，4、最后执行order by)

3、函数

分组函数(多行处理函数)

分组函数一个5个，count、max、min、sum、avg；
所有分组函数都是对 某一组 数据进行操作的
特点：输入多行 输出一行
分组函数自动忽略null
分组函数不可直接使用在where子句中，因为分组函数在group by执行结束之后执行

count 计数

总人数：

select count(*) from emp;

select count(ename) from emp;

统计总记录条数:

统计comm字段中不为null的元素总数：

select cout(comm) from emp;
sum 求和

工资总和：

select sum(sal) from emp;
avg 平均值

平均工资：

select avg(sal) from emp;
max 最大值

最高工资:

select max(sal) from emp;
min 最小值

最低工资：

select min(sal) from emp;
分组函数组合使用
select 						
	count(*),sum(sal),avg(sal),max(sal),min(sal) 
from 
	emp;
单行处理函数

输入一行，输出一行
数据库中规定只要有null（+ - * / ）参与的运算结果一定是 null

计算每个员工年薪：

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

ifnull()空处理函数

语法：

​ ifnull（可能为null的数据，被当做什么处理）

	select ename,comm,ifnull(comm,0) from emp;

4、分组查询(group by)

group by 按照某个字段或者某些字段进行分组
having 对分组之后的数据进行再次过滤（约束）(需要之前有group by)
分组函数一般会和group by 联合使用，这就是它被称为分组函数的原因
任何一个分组函数都是在group by语句执行结束之后执行
当sql语句没有group by,整张表的数据会自成一组

当语句中有group by时,select后面只能跟分组函数和参与分组的字段

语句执行顺序

	select		5
    ..
	from		1
	..
	where		2
	..
	group by	3
	..
	having		4
	..
	order by	6
	..

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

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

	select max(sal) from emp group by job;

查找工资高于平均工资的员工(语句嵌套)

	select ename,sal from emp where sal>(select avg(sal) from emp);

错误： 当语句中有group by时,select后面只能跟分组函数和参与分组的字段

	select ename,job,max(sal) from emp group by job;（X）

查找每个工作岗位的平均薪资

	select job,avg(sal) from emp group by job;
多个字段联合分组

找出每个部门不同工作岗位的最高薪资

	select deptno,job,max(sal) from emp group by deptno,job;

找出每个部门的最高薪资，要求显示薪资大于2900的数据

	select deptno,max(sal) from emp group by deptno having max(sal)>2900;//效率低

	select deptno,max(sal) from emp where sal>2900 group by deptno;//效率高

找出每个部门平均薪资大于2000的数据

	select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

5、去除重复记录(distinct)

distinct语句未删除更改数据，只是将重复语句不显示

distinct关键字只能出现在所有字段最前面
找出有哪些岗位：

	select distinct job from emp;

统计岗位的数量：

	select count(distinct job) from emp;

6、连接查询

笛卡尔积现象(交叉连接)

如果两张表连接无任何条件限制，最终查询结果条数是两张表中记录条数的乘积；

select ename,dname from emp,dept;

给表起别名：
好处：执行效率高，可读性好。

select e.ename,d.dname from emp e,dept d;

避免笛卡尔积现象不会减少记录匹配次数，只是会显示有效记录

	select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//sql92 语法
内连接(inner join)

两张表连接，使用内连接，凡是两张表能匹配上的记录查询出来，两边表同时有对应的数据，即任何一边缺失数据就不显示

两张表没有主副之分，是平等的；

等值连接

条件是等量关系
查询每个员工的部门名称，要求显示员工名和部门名
SQL92:（太老了，不用）

	select 
		e.ename,d.dname 
	from 
		emp e,dept d 
	where 
		e.deptno=d.deptno;//等值连接

SQL99:（常用）（inner可省略，带inner目的可读性好）

	select 
		e.ename,d.dname 
	from 
		emp e 
	inner join
		dept d 
	on 
		e.deptno=d.deptno;

SQL99语法：

	·····
		A
	inner join
		B
	on
		连接条件
	where
		···

SQL99语法结构更清晰，表的连接条件和后来的where条件分离

非等值连接

特点：连接条件中的关系是非等量关系

找出每个员工的工资等级，要求显示员工名

	select 
		e.ename,e.sal,s.grade
	from 
		emp e
	join 
		salgrade s
	on 
		e.sal between s.losal and s.hisal;
自连接

特点：一张表看成两张表，自己连接自己

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

	select
		a.ename as '员工',b.ename as '领导'
	from 
		emp a
	inner join
		emp b
	on
		a.mgr=b.empno;
外连接(outer join)

特点： 主表数据无条件查询出来

两张表进行连接，使用外连接，一张表是主表，另一张是副表，主要查询主表中数据，捎带查询副表，当副表中数据未和主表匹配，副表自动模拟null与之匹配。

左外连接（左连接）

左边那张表是主表

左连接有右连接的写法，右连接也有对应左连接的写法

left outer join

(outer)外连接 可以省略

查询员工和其上级

	select
		a.ename '员工',b.ename '领导'
	from
		emp a
	left join
		emp b
	on
		a.mgr=b.empno;
右外连接（右连接）

右边那张表是主表

select
	a.ename '员工',b.ename '领导'
from
	emp b
right join
	emp a
on
	a.mgr=b.empno;

查询哪个部门没有员工

select
	d.*
from
	emp e
right join
	dept d
on
	e.deptno=d.deptno
where
	e.empno is null;
全连接

既有左连接又有右连接，两张表都要查出来

两张表中能对应的查查出来，不能对应的也要查出

三张表以上的连接查询

1、找出每个员工的部门名称以及工资等级（内连接）

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;

2、找出每个员工的部门名称，工资等级，以及上级领导（外连接）

select
	e.ename '员工',d.dname '部门',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;

7、子查询

select语句中嵌套select语句，被嵌套的select语句是子查询

select
	..(select)
from
	..(select)
where
	..(select)
where子句中使用子查询

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

select 
	* from emp  
where
	sal>(select avg(sal) from emp);
from子句中使用子查询

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

1、找出每个部门平均薪水（按照 部门编号分组，求sal的平均值）

select
	deptno,avg(sal)
from
	emp
group by
	deptno;
deptnoavg(sal)
202175.000000
301566.666667
102916.666667

2、将以上结果当成临时表t，让 t 表和 salgrade 表连接，

条件：

 t.avg(sal)  between s.losal and s.hisal

select
	t.*,s.grade
from
	t
join
	salgrade s
on t.avgsal between s.losal and s.hisal;

总结：之前条件作为from的子查询

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;

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

1、找出每个员工的薪水等级

select 
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on 
	e.sal  between s.losal and s.hisal;
enamesalgrade
SMITH800.001
ALLEN1600.003
WARD1250.002
JONES2975.004
MARTIN1250.002
BLAKE2850.004
CLARK2450.004
SCOTT3000.004
KING5000.005
TURNER1500.003
ADAMS1100.001
JAMES950.001
FORD3000.004
MILLER1300.002

2、基于上表，按照deptno分组，求grade平均值

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
order by
	deptno;
在select后面嵌套子查询

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

(常用)
select
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.deptno;
（select子查询）
select
	e.ename,
	(select d.dname 
	from dept d 
	where e.deptno=d.deptno) as dname 
from 
	emp e;

8、union(将查询结果相加)

找出工作岗位是salesman和manager的员工

1、or

select 
	ename, job
from 
	emp
where 
	job='salesman' or job='manager';

2、in

select 
	ename,job
from 
	emp
where 
	job in('salesman','manager');

3、union

select
	ename,job
from 
	emp
where job='manager'
union
select
	ename,job
from 
	emp
where job='salesman';

常用在两张不相干的表中的数据拼接在一起显示

(两张表中查询的数据必须列数相同)

select ename from emp
union
select dname from dept;

9、limit(分页查询)

1、limit是mysql中特有的，其他数据库中没有。不通用（Oracle中有叫rownum的相同机制）

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

3、语法：

limit startIndex length

startIndex表示起始位置，从0开始，0表示第一条数据

length表示取多少个 数量

取出工资前五名的员工（降序取前5个）

select ename,sal
from emp
order by sal desc
limit 0,5;

limit 后只写一个数字后边默认从0开始

select ename,sal
from emp
order by sal desc
limit 5;

语句执行顺序：

select		5
..
from		1
..
where
(join on)	2
..
group by	3
..
having		4
..
order by	6
..
limit 		7
..

找出工资排名在第4到第9名的员工

第3名下标是3，4-9共6名员工

select ename,sal
from emp
order by sal desc
limit 3,6;
通用的标准分页sql

每页显示三条记录：

第1页：0，3

第2页：3，3

第3页：6，3

第4页：9，3

每页显示pageSize条记录

第pageNo页：（pageNo-1）*pageSize,pageSize

pageNo表示第几页

二、定义语言DDL（表结构增删改）

创建表(create)

建表语句的语法格式

create table 表名(
	字段名1 数据类型，
	字段名2 数据类型，
	字段名3 数据类型，
	……
)

create table t_class(
	cno int,
	cname varchar(255),
	primary key(cno)
);

Myqsl中常见字段数据类型：

int		整数型
bigint	长整型
float	浮点型
char	定长字符串 char(6),effective字符超过已定长度报错
varchar	可变长字符串
date	日期类型
BLOB	二进制大对象（存储图片，视频等流媒体信息） Binary Large OBject
CLOB	字符大对象（存储大文本，比如4G字符串）Character Large OBject

char和varchar

字段数据长度不发生改变，定长用char（生日，性别），长度不确定用varchar（简介，姓名）

创建学生表

学号：bigint

姓名：varchar

性别：char

班级编号：varchar

生日：char

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
);
删除表(drop)
drop table 表名//表不存在时报错
drop table if exists 表名;
修改表(alter)

三、操作语言DML(数据增删改)

插入数据（insert）

insert语句执行成功，表中必然多一条数据

语法格式：

insert into 表名（字段1，字段2，字段3，……） values（值1，值2，值3，……）

要求：字段数量和值的数量相同，并且数据类型要对应相同

insert into t_student(no,name,sex,classno,birth) values(001,'李杰','男','软工1班','1997-01-01');

插入字段内容不完整时，未插入内容都为null

drop table if exists t_student;//表存在时删除

default 设置默认值

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1) default '男'
	classno varchar(255),
	birth char(10)
);

表后未接参数字段时，values的值要要求必须与之对应

insert into t_student values(002,'jack','男','软工一班','1998-03-06');

一次插入多行数据

insert into t_student values
(003,'bruce','男','软工一班','1998-06-01'),
(004,'alex','男','软工二班','1999-06-01');
表的复制

语法：

1、将查询结果当做表创建出来

create table 表名 as select 语句;

create table emp2 as select ename,empno from emp;

2、将查询结果插入到一张表中（表需要之前创建）

insert into 表名 select 语句；

insert into dept1 select * from dept;
修改数据(update)

语法格式：

update 表名 set 字段名1=值1,字段名2=值2……where 条件;

注意：(无where条件时，整张表数据全部更新)

将部门10的loc修改为SHANGHAI。部门名称修改为RENSHIBU

update dept set dname='RENSHIBU',loc='shanghai' where deptno=10;
删除数据(delete)

未释放物理空间，删除大表效率低时间长

语法格式：

delete from 表名 where 条件：

注意：(没有条件时表全部删除)

删除编号为10部门的数据

delete from dept1 where deptno =10;

删除表中所有记录

delete from 表名;
删除大表(truncate)截断

删除表中数据，表还在。表被截断，不可回滚，永久丢失

truncate table 表名;

四、约束(constraint)

创建表时，可以给表的字段添加相应的约束，目的是为了保证表中数据的合法性、有效性、完整性。

常见约束:

非空约束：not null 约束字段不能为NULL

唯一约束：unique 约束字段不能重复

主键约束：primary key （PK）约束的字段既不能为NULL，也不能重复

外键约束：foreign key （FK）

检查约束：check，Mysql没有，Oracle有

非空约束 not null

约束字段不能为NULL

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);

insert into t_user(id,username,password) values(1,'lee','456');
唯一性约束(unique)

约束字段不能重复，但可以为null

列级约束

drop table if exists t_user;
create table t_user(
	id int unique,
	username varchar(255) unique);//列级约束
	
insert into t_user values(1,'lee'),(2,'Liu');

表级约束

unique(字段1，字段2)联合约束，只有两个字段都重复时才报错

create table t_user(
	id int,
	username varchar(255)
	unique(id username)//表级约束
	);
主键约束(primary key)

约束的字段既不能为NULL，也不能重复

create table t_user(
	id int primary key,
	username varchar(255)
	);

主键字段：字段添加primary key之后，叫做主键字段

主键值：该字段中每一个值都是主键值

主键设计三范式，第一范式要求任何一张表都应该有主键

主键作用：主键值是这行记录在这张表中的唯一标识

主键分类：

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

​ 单一主键

primary key(id)

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

primary key(id,username)

​ 根据主键性质划分

​ 自然主键：主键值最好是一个和业务无关的自然数（推荐）

​ 业务主键：主键值与系统的业务挂钩（不推荐用）

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

主键自增(auto_increment)
drop table if exists t_user;
create table t_user(
 	id int primary key auto_increment,//id字段自动维护一个自增的数字，从1开始，以1递增
 	username varchar(255)
 	);
外键约束(foreign key)
foreign key(字段名) references 另一表名(表名中字段)

t_student中的classno字段引用t_class表中的cno字段，t_student表叫做子表，t_class表叫做父表

顺序要求：

​ 删除数据时，先删除子表，在删除父表

​ 删除表时，先删除子表，再删除父表

​ 添加数据时，先添加父表，再添加子表

​ 创建表时，先创建父表，在创建子表

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	cno int,
	cname varchar(255),
	primary key(cno)
);
	
create table t_student(
	sno int,
	sname varchar(255),
	classno int,
	primary key(sno),
	foreign key(classno) references t_class(cno)	);

insert into t_class values(101,'soft1ban');
insert into t_class values(102,'soft2ban');

insert into t_student values(1,'zhang',101);
insert into t_student values(2,'liu',102);
insert into t_student values(3,'lee',102);

//错误，子表无法为父表添加记录
insert into t_student values(4,'huang',103);

1、外键可以为null

2、外键字段引用其他表的字段时，被引用字段不一定是主键，但必须具有唯一性

五、存储引擎

完整的建表语句：

create table `t_x`(
	`id` int(11) default null
	) engine=innodb default charset=utf8;

Mysql中但是标识符可用飘号(`)括起来,最好不用，不通用

建表是可以指定存储引擎，也可指定字符集

mysql默认使用的存储引擎是 InnoDB 方式

默认采用的字符集是UTF8

存储引擎：表的存储方式

查看当前mysql支持的存储引擎

show engines \g
常用存储引擎
1、MyISAM存储引擎

MylSAM存储引擎是MySQL最常用的引擎，但这种引擎不是默认的，也不支持事务

它使用三个文件表示每个表

​ 格式文件 存储表格式（XXX.frm）

​ 数据文件 存储表数据 （XXX.MYD）

​ 索引文件 存储表上的索引（XXX.MYI）

优点：可被压缩，节省存储空间，可转换为只读表，提高检索效率

2、InnoDB存储引擎

优点：支持事务、行级锁、外键。安全性高

表结构存储在（xxx.frm）文件中

数据存储在tablespace表空间中，无法被压缩，无法转化成只读

支持级联删除和级联更新（父子）

3、MEMORY存储引擎

缺点：不支持事物，所有数据和索引都存储在内存当中，数据易丢失.

优点：查询速度最快，适合查询

六、事务

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

多条语句共同构成一个事务，所有该事务语句必须同时执行成功要么同时执行失败

只有DML（insert delete update）语句才与事务相关，对数据进行操作

事务的存在时为了保证数据的完整性，安全性

通常一个事务需要多条DML语句共同联合完成

​ 开启事务机制（开始）操作在缓存中

​ 提交事务（commit）（将缓存保存到文件）或者回滚事务（rollback）（撤回） 保存操作到文件中,释放缓存

事务四大特性

原子性：事务是最小的工作单元，不可再分

一致性：事务必须保证多态DML语句同时成功或者失败

隔离性：事务A和事务B之间具有隔离

持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束

隔离级别

设置隔离级别：

set global transaction isolation level 级别
                                （read uncommitted）
                                （read committed）
                                （repeatable read）
                                （serializable）
第一级别：读未提交（read uncommitted）

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

读未提交的存在脏读（dirty read）现象：表示读了脏的数据

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

​ 对方事务提交之后的数据我方才可读取

​ 读已提交存在的问题：不可重复读取

​ 解决了脏读问题

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

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

​ 问题：读取到的数据是幻象（备份数据或真实数据已修改）

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

​ 处理完一个事务再处理另一个

​ 解决了所有问题

​ 效率低，需要事物排队

oracle数据库默认隔离级别是：读已提交（第二）

mysql数据库默认隔离级别是：可重复读（第三）

演示事物

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

使用（start transaction）关闭自动提交，开启事务

1、准备表

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,
	username varchar(255)
);

2、插入数据

insert into t_user(username) values('zhang');
select * from t_user;

3、回滚

rollback;
select * from t_user;

4、回滚失败，数据已自动提交

5、使用start transaction 开启事务

start transaction;

6、插入数据

insert into t_user(username) values('liu');
select * from t_user;

7、回滚

rollback;
select * from t_user;

8、回滚成功，插入数据被撤销

9、开启事务后，手动提交commit

insert into t_user(username) values('huang');
select * from t_user; 
commit;

10、已手动提交，回滚失败

rollback;
select * from t_user;

七、索引(index)

索引相当于一本书的目录，用过目录可以快速找到对应的资源

数据量方面：查询表有两种检索方式：

​ 第一种方式：全表扫描

​ 第二种方式：根据索引检索（效率很高）（原理：缩小了扫描范围）

索引不能随意添加，索引也是数据库中对象，需要数据库不断维护，有维护成本。表中数据经常被修改就不适合添加索引，因为数据修改，索引需要重新排序，维护。

合适创建索引的情景

1、数据量庞大

2、该字段很少DML操作（字段进行修改操作，索引也需要维护）

3、该字段经常出现在where子句中

查看某条语句执行情况

explain 语句
创建索引
create index 索引名称 on 表名(字段名);
删除索引
drop index 索引名称 on 表名;

索引底层采用的数据结构是：B+Tree

索引实现原理：通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的

select ename from emp where ename='SMITH';
通过索引转换为：
select ename from emp where 物理地址=0x3;
索引的分类

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

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

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

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

索引失效情况

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

模糊查询时。第一个通配符使用的是%时，索引失效

八、视图(view)

视图：站在不同的角度看数据

创建视图

创建视图时只能用DQL语句以视图对象的方式创建

create view 视图名 as 条件;
create view myview as select empno,ename from emp;
删除视图

视图创建完成后可用DDL对视图进行CRUD（增查改删）

drop view 视图名;
drop view myview;

对视图进行怎删改查会影响到原表数据

create table emp_bak as select * from emp;
创建视图
create view myview1 as select empno,ename,sal from emp_bak;
修改视图中数据
update myview1 set ename='xuan',sal=100 where empno=7369;
通过视图删除原表数据
delete from myview1 where empno='7369';
视图的作用

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

九、DBA命令

数据导出

在window的dos命令窗口中执行：

mysqldump 数据库名 >位置 -uroot -p密码
mysqldump power >D：newpower.sql -uroot -phang199881
导出指定数据库中的指定表
mysqldump power emp>D：newpower.sql -uroot -phang199881
数据导入
create database 数据库名;
use 数据库名;
source 文件路径

数据库设计三范式

设计范式：设计表的依据，按照该三范式设计的表不会出现数据冗余。

第一范式

任何一张表都应该有主键，并且每一个字段原子性不可再分

第二范式

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

多对多

三张表，两张数据表，一张关系表包含两个外键

多个老师对应多个学生
t_student 学生表
sno(pk)		sname
-------------------
1			张三
2			李四
3			王五

t_teacher 老师表
tno(pk)		tname
-------------------
1			张老师
2			李老师
3			王老师

t_student_teacher_relation 学生讲师关系表
id(pk)		sno(fk)		tno(fk)
-------------------------------
1			1			3
2			1			1
3			2			2
4			2			3
5			3			1
6			3			3
第三范式

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

一对多

两张表，多的表加外键

（一个班级对应多个学生）
t_class 班级
cno(pk)		cname
-----------------
1			一班
2			二班
3			三班

t_student 学生
sno(pk)		sname		classno(fk)
-----------------------------------
101			张			1
102			李			2
103			黄			1
104			关			3

提示：实际开发张总，以满足客户需求为主，有时会拿冗余换取执行速度

一对一方案设计
1、主键共享
t_user_login 用户登录表
id(pk)	username	password
----------------------------
1		zs			123
2		ls			456

t_user_detail 用户详细信息表
id(pk+fk)	realname	tel
---------------------------
1			张三			1231514
2			李四			1246546
2、外键唯一
t_user_login 用户登录表
id(pk)	username	password
----------------------------
1		zs			123
2		ls			456

t_user_detail 用户详细信息表
id(pk)	realname	tel			userid(fk+unique)
------------------------------------------------
1		张三			1231514		1
2		李四			1246546		2

十、锁

练习题

1、取得每个部门最高薪水的人员名称

1、取得每个部门的最高薪水

select 
	deptno,max(sal) as maxsal
from 
	emp
group by 
	deptno;

2、将以上结果当做临时表t，t表和emp e表进行连接，条件是 t.deptno=e.deptno and t.maxsal=e.sal

select 
	e.ename,t.*
from 
	(select 
		deptno,max(sal) as maxsal
	from 
		emp
	group by 
		deptno) t
join 
	emp e
on e.deptno=t.deptno and e.sal=t.maxsal;

注意：主键和具有unique约束的字段会自动添加索引。根据主键查询效率高，尽量根据主键检索。

2、哪些人薪水在部门的平均薪水之上

1、找出每个部门平均薪水

select 
	deptno,avg(sal) as avgsal
from 
	emp
group by 
	deptno;

2 、以上查询结果作 t 表， t和emp表连接

条件：e.deptno=t.deptno and e.sal>t.avgsal

select 
	t.*,e.ename,e.sal
from
	emp e
join
	(select 
		deptno,avg(sal) as avgsal
	from 
		emp
	group by 
		deptno) t
on 
	e.deptno=t.deptno and e.sal>t.avgsal;
3、取得每个部门中所有人的平均薪水等级

1、找出每个人的薪水等级

select 
	e.deptno,e.ename,e.sal,s.grade
from 
	emp e
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal

2、基于以上结果按照deptno分组，求grade的平均值

select 
	e.deptno,avg(s.grade)
from 
	emp e
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal
group by
	deptno;
4、取得员工中最高薪水

1、sal降序，limit 1

select 
	ename, sal
from 
	emp
order by 
	sal desc
limit 1;

2、使用max函数

select 
	max(sal)
from emp;

3、自连接

3、1、找出工资小于最大值的数据（去重）

select
	distinct a.sal
from
	emp a
join
	emp b
on
	a.sal<b.sal;

3、2、原来表除了以上数据之外的数据就是最大工资

select
	sal
from
	emp
where
	sal not in(select
					distinct a.sal
				from
					emp a
				join
					emp b
				on
						a.sal<b.sal);
5、取得平均薪水最高的部门编号

1、降序取第一

1、1、求每个部门的平均薪水

select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno;

1、2、降序选第一个

select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno
order by
	avgsal desc
limit 1;

2、max

2、1

select 
	max(t.avgsal)
from
	(select
		avg(sal) as avgsal
	from
		emp
	group by
		deptno) t;

2、2、having

select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno
having
	avgsal=(select max(t.avgsal) from (select
			avg(sal) as avgsal
		from
			emp
		group by
			deptno) t);
6、取得平均薪水最高的部门名称
select
	d.dname,avg(e.sal) as avgsal
from 
	emp e
join
	dept d
on
	d.deptno=e.deptno
group by
	d.dname
order by
	avgsal desc
limit 1;
7、求平均薪水等级最低的部门名称
select
	avg
LilLeO_O
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL学习笔记
12-19
MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记
Mysql学习笔记（全）
热门推荐
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
mysql 学习笔记
javaTalk
01-23 3369
mysql 学习记录，包含日志、事务、索引、锁等功能。
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
mysql学习笔记
04-20
个人学习mysql的一些笔记，包含一些基本的操作以及讲解，希望可以帮到一些初学者
MySQL学习笔记 MySQL学习笔记
09-28
MySQL学习笔记
MYSQL学习笔记
02-28
数据库：就是在服务器中安装一个可以管理数据的软件，这个文件称为“DBMS”...Mysql：甲骨文公司提供一个免费的中小型数据库 Sql_server：微软提供的一个收费的大型数据库 Oracle：甲骨文公司提供一个收费的大型数据库
MySQL学习笔记小结
09-10
主要介绍了MySQL学习笔记小结,整体介绍了mysql的很多细节我呢体，学习优化mysql的朋友可以参考下，需要的朋友可以参考下
MySql学习笔记
12-13
MySql学习笔记，MySql学习笔记，里面很详细的哈。
MySQL学习笔记分享
06-20
学习MySQL时做的笔记,分享给大家,学习时在b站跟着杜老师学的,word版本,方便大家修改
mysql学习笔记.pdf
08-10
mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql学习笔记mysql...
MySQL学习笔记MySQL学习笔记
05-03
日常学习笔说到数据库，如果是计算机专业的同学，他们往往需要学习数据库的原理，也就是其底层逻辑。而其他专业的同学需要学习的一般是对数据库操作层面的技巧和语法。题主就是属于后者。 未来是一个数字化的时代，...
MYSQL DBA运维实战 SQL2
最新发布
c5zm51zjr的博客
05-14 396
条件查询:单条件where查询 select name,post from 库名 where post=“hr”(部门为“hr”员工的姓名)更新，更新数据:update 表名 set 修改内容(如:name=“cc”) where 列名；查询排序:select * from 库名 order by 工资列名 ASC(升序)；非完全插入:insert into 表名(列名，列名) values(值)2.DQL:mysql数据查询。
openGauss学习笔记-279 openGauss性能调优-实际调优案例08-改写SQL消除in-clause
超哥的博客
05-10 73
openGauss学习笔记-279 openGauss性能调优-实际调优案例08-改写SQL消除in-clause279.1 现象描述279.2 优化说明 openGauss学习笔记-279 open
黑马mysql学习笔记
09-06
黑马程序员的MySQL学习笔记是一本介绍数据库语句、操作和基础概念的优秀材料。它包括了关于数据库的相关概念，安装和启动MySQL数据库的步骤，以及使用MySQL客户端进行连接的方法。此外，笔记还介绍了关系型数据库的数据模型和DDL（数据定义语言）的使用。同时，笔记还提供了创建、修改和删除MySQL用户的指令。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [黑马MySQL数据库笔记.doc](https://download.csdn.net/download/qq_39132095/12051469)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MySQL（黑马程序员学习笔记）](https://blog.csdn.net/weixin_59368946/article/details/130101408)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值