MySQL
演示数据
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE DEPT
(DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
CREATE TABLE EMP
(EMPNO int(4) not null ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE(7,2),
COMM DOUBLE(7,2),
primary key (EMPNO),
DEPTNO INT(2)
)
;
CREATE TABLE SALGRADE
( GRADE INT,
LOSAL INT,
HISAL INT );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
commit;
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;
- 查看表的结构:desc 表名
desc emp;
1、DQL(查询语句)
1.1、简单查询
查询一个字段
select 字段名 from 表名;
查询多个字段
使用逗号隔开
select deptno,dname from dept;
查询所有字段
select * from 表名
这种方式需要将*号先转化为所有字段的字段名,效率较低,可读性差,建议将字段名全部写出来
给查询的列起别名(as)
select dname as name from dept;
select dname as ‘部门名称’ from dept;
字段可以使用数学表达式
select ename,sal*12 as ‘年薪’ from emp;
1.2、条件查询(where)
between…and…
等同于>=…and<=…
is null(is not null)
and
or
and 和 or同时出现,存在优先级问题,and>or
查询工资大于2500,并且部门编号为10或20部门的员工
select * from emp where sal >2500 and deptno = 10 or deptno = 20;-- 错误语句,这个语句会先查询 sal >2500 and deptno = 10
select * from emp where sal>2500 and (deptno = 10 or deptno = 20);
in,not in
在某个范围,不是区间
select empno,ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
select empno,ename,job from emp where job in (‘MANAGER’,‘SALESMAN’);
like(模糊查询)
%:匹配任意多个字符
_:任意一个字符
查询名字中含有下划线的,需要添加转义字符
select name from student where name like ‘%\_%’;
1.3、排序
单个字段排序
默认是升序asc
select ename,sal from emp order by sal desc;
多个字段排序
查询员工名字和薪资,按照薪资升序,如果薪资一样,按照名字升序
select ename,sal
from emp
order by
sal asc,ename asc;
1.4、数据处理函数(upper,lower等)
单行处理函数的特点:一个输入对应一个输出
和单行处理函数相对的是:多行处理函数(多个输入对应一个输出)
常见的单行处理函数:
- upper 大写
select upper(ename) as ename from emp;
- lower 小写
select lower(ename) as ename from emp;
- substr 取子串(substr(被截取的字符串,起始下标,截取长度))
select substr(ename,1,1) as ename from emp;-- 起始下标从1开始,不是0
- length 取长度
select length(ename) as result from emp;
- concat 字符串拼接
- trim 去空格
select * from emp where ename = trim(' KING');
- str_to_date 将字符串转换成日期:str_to_date(‘字符串日期’,‘日期格式’)
将varchar类型转换为date类型
mysql的日期格式:%Y年,%m 月,%d 日,%h 时,%i 分,%s 秒
- date_format 格式化日期
将date类型转换为具有一定格式的varchar类型
select date_format(birth,'%d/%m/%Y') from user
- format 设置千分位
- round 四舍五入
select round(sal,1) as sal from emp;-- 后面的数字意思是保留几位小数
- rand() 生成随机数
- ifnull 可以将null转化成一个具体值:ifnull(null字段,被当作哪个值)
在数据库中,只要有null参数与数学运算,最终结果都是null
select ename, sal+comm as result from emp;
select ename, ifnull(sal+comm,sal) as result from emp;
- case…when…then…when…then…else…end
select ename,job,sal,
(case job when 'MANAGER' then sal*1.1 when 'SALEMAN' then sal*1.5 else sal end) as new sal
from emp;-- 当job为namager,工资上调10%,当job为salesman,上调50%,其他的不变
1.5、分组函数/聚合函数/多行处理函数
特点:输入多行,最终输出一行
5个:
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:分组函数在使用的时候,必须先进行分组,然后才能用,如果没有对数据进行分组,整张表默认为一组
分组函数在使用的时候需要注意哪些?
-
分组函数自动忽略null,不需要提前对null进行处理
-
分组函数中count(*)和count(具体字段)有什么区别?
count(具体字段):表示统计该字段下所有不为null的元素的总数
count(*):统计表当中的总行数
-
分组函数不能直接使用在where子句中,因为分组函数必须在分组之后才能使用,where执行的时候,还没有分组
-- 找出比最低工资高的员工信息
select ename,sal,from emp where sal >min(sal);-- 这是错误的语句,表面上看起来正确
- 所有的分组函数可以组合起来一起使用
找出每个工作岗位的工资和
select job, sum(sal) as '工资和' from emp group by job;
在一条sql语句中,如果有group by,select后面只能加分组字段(job)和分组函数(sum)
1.6、分组查询(group by)
sql语句书写顺序
select...
from
where
group by
having
order by
执行顺序:
from where group by having select order by
可以使用having对分完组之后的数据进一步过滤,having不能单独使用,不能代替where
找出每个部门的最高薪资,显示最高薪资大于3000的
select deptno,max(sal) from emp group by deptno having max(sal) >3000;
以上sql语句效率较低,可以使用where先将>3000的都找出来,然后再分组
select deptno,max(sal) from emp where sal >3000 group by deptno;
1.7、去重(distinct)
distinct把查询结果去重,原表数据不会被修改
distinct只能出现在所有字段的前面,可以在分组函数中,count(distinct job),其实这还是字段job的前面
1.8、连接查询
从一张表中单独查询,称为单表查询
多张表联合起来查询数据,从emp中取员工的名字,从dept表中取部门的名字,这种跨表查询,称为连接查询
根据表链接的方式分类:
内连接:
-
等值连接
-
非等值连接
-
自连接
外连接:
-
左外连接
-
右外连接
全连接
当两张表进行连接查询时,没有任何条件限制,会发生什么现象?
案例:查询每个员工所在部门名称
select ename,dname from emp,dept;
SMITH OPERATIONS
SMITH SALES
SMITH RESEARCH
SMITH ACCOUNTING
ALLEN OPERATIONS
ALLEN SALES
ALLEN RESEARCH
ALLEN ACCOUNTING
WARD OPERATIONS
...56条数据
上面出现了一个员工对应多个部门的情况
当两长表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象
如何避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来
select ename,dname from emp,dept where emp.deptno = dept.deptno;
还是匹配了56次,只不过筛选了数据
内连接之等值连接
连接条件是等量关系
案例:查询每个员工所在部门名称,显示员工名和部门名
sql92语法:
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and 后面的筛选条件;
sql92的缺点:结构不清晰,表的连接条件和后期进一步筛选的条件都放在了where后面
sql99语法:
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where…;
sql99优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后添加where
内连接之非等值连接
连接条件不是一个等量关系,称为非等值连接
案例:找出每个员工的薪资等级,要求显示员工名,薪资,薪资等级
select ename,sal,grade from emp join salgrade on sal between salgrade.LOSAL and salgrade.HISAL;
内连接之自连接
将一张表看成两张表
案例:查询员工的上级领导,要求显示员工名和对应的领导名
select e.ename '员工名',b.ename '领导名' from emp e join emp b on e.mgr = b.empno;
外连接(left/right join)
内连接的特点:两张表没有主次关系,将能够匹配上条件的数据查询出来
select e.name,d.name from emp e right join dept d on e.deptno = d.deptno;
right代表什么:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表
在外连接中,两张表连接,产生了主次关系
任何一个右连接,都有左连接的写法,任何一个左连接,都有右连接的写法
外连接的查询结果条数一定是>=内连接的查询结果条数
三张表,四张表怎么连接
语法:
select ...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
join
d
on
a和d的连接条件
一条sql中内连接和外连接可以混合,都可以出现
案例:找出每个员工的部门名称以及工资等级,要求显示员工名,部门名,薪资,薪资等级
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 s.hisal;
1.9、子查询
什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询
子查询可以出现在哪?
select
…(select)
from
…(select)
where
…(select)
where子句中的子查询
案例:找出比最低工资高的员工姓名和工资
where中不能直接使用分组函数
select ename,sal from emp where sal > (select min(sal) from emp);
from子句中的子查询
from后面的子查询,可以将子查询的查询结果当作一张临时表
案例:找出每个岗位的平均工资的薪资等级
步骤:
- 找出每个岗位的平均工资,查询结果当作一张临时表t
select job,avg(sal) avgsal from emp group by job;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AixOLJln-1653620320515)(img/image-20220423101410614.png)]
- 临时表t和工资等级表进行连接查询,查询出工资等级
select t.*,s.grade from t join salgrade s on t.avgsal between s.losal and s.hisal;
- 将t表的语句替换进去
select t.*,s.grade from (select job, avg(sal) avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
select后面的子查询
案例:找出每个员工的部门名称,要求显示员工名,部门名
select e.ename,e.deptno,(select dname from dept d where e.deptno = d.deptno) dname from emp e;
1.10、union合并查询结果集
union的效率更高,对于表连接来说,每连接一次新表,则匹配次数满足笛卡尔积,成倍增加
但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接
a连接b连接c
a 10条记录
b 10条记录
c 10条记录
笛卡尔积匹配次数:1000次
使用union:
a连接b一个结果:10*10=100次
a连接c一个结果:10*10=100次
完成拼接只需要200次(union把乘法变成了加法)
使用union的注意事项:
union在进行结果集合并的时候,要求两个结果集的列数相同
列和列的数据类型也要相同
1.11、limit
limit在order by之后执行
limit 起始下标,length
limit只写一个表示取几条数据
limit(2,3) 从下标为2开始取,也就是第三条,取3个
limit 5 从下标0取,取5个
分页
每页显示3条记录
第1页:limit 0,3
第2页:limit 3,3
第3页:limit 6,3
每页显示pageSize条记录
*第pageNo页:limit (pageNo-1)pageSize , pageSize
2、DDL
2.1、表的创建create
语法格式:
create table 表名(
字段1 数据类型
字段2 数据类型
字段3 数据类型
);
快速创建表(将查询结果创建一张表):create table 表名 select * from 已有表
mysql的数据类型
varchar(255):变长字符串,比较智能,节省空间
char(255):定长字符串
int (11)
bigint:长整型,等同于java中的long
float
double
date:短日期类型,年月日
datetime:长日期类型,年月日时分秒
在mysql中可以使用now()来获取当前时间,获取的是datetime类型的
insert into table user(id,name,birth,create_time) values(2019001,'李四','2001-10-29',now())
clob:字符大对象,最多可以存储4G的字符串,超过255个字符串,使用这个
blob:二进制大对象,专门用来存储图片,声音,视频等流媒体数据,往blob类型的字段上插入数据的时候,需要使用IO流
2.2、删除表drop
drop table if exists 表名;
2.3、插入数据insert(DML)
insert into 表名(字段1,字段2,字段3...) values
(),
(),
()
2.4、修改update(DML)
update 表名 set 字段1 = 值1, 字段2 = 值2, 字段3 = 值3... where 条件
不加where条件就会更新所有数据
2.5、删除delete(DML),truncate
delete from 表名 where 条件
不加where会删除表中所有数据
快速删除表中的数据:truncate table 表名 where条件(上亿条数据可以用这个,这个效率很高)
truncate 方式删除不可回滚
2.6、对表结构的修改
什么是表结构的修改?
添加一个字段,删除一个字段,修改一个字段
使用alter
3、约束
什么是约束?
在创建表的时候,我们可以给表的字段加上一些约束,来保证这个表中的数据的完整性,有效性
3.1、约束包括哪些?
-
非空约束 not null
-
唯一性约束 unique
-
主键约束 primary key = not null + unique
-
外键约束 foreign key
-
检查约束(mysql不支持)
3.2、两个字段联合唯一
create table user(
id int,
name varchar(255),
enail varchar(255),
unique(name,email)
);
4、事务
什么是事务?
一个事务就是一个完整的业务逻辑
例如:
假设转账,从a账户向b账户中转账1000
将a账户的钱减去1000(update)
将b账户的钱加上1000(update)
这就是一个完整的业务逻辑
以上的操作是一个最小的业务单元,要么同时成功,要么同时失败,这样才能保证钱是正确的
只有DML语句和事务有关:insert,delete,update
mysql默认情况下是支持自动提交事务的,即每执行一条DML语句,则提交一次,回滚永远只能回滚到上一次的提交点,所以在自动提交事务的情况下,是无法回滚的
使用start transaction;将自动提交机制关闭,变成手动提交
提交事务:commit;
回滚事务:rollback;
4.1、事务的4个特性
-
原子性
事务是最小的工作单元,不可再分
-
一致性
同一事务中,所有操作同时成功,同时失败
-
隔离性
a事务和b事务之间具有一定的隔离
教室a和教室b之间有一道墙,这道墙就是隔离性
a事务在操作一张表的时候,另一个事务b也操作这张表会怎样?
-
持久性
事务最终结束的一个保障,事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘
隔离性
a教室和b教室中间有一道墙,这道墙可以很厚,也可以很薄,这就是事务的隔离级别,这道墙越厚,事务隔离级别越高
4个隔离级别:
读未提交:read uncommitted(最低隔离级别)
事务a可以读取到事务b未提交的数据
存在的问题:脏读
读已提交:read committed
事务a只能读取事务b提交之后的数据
解决了脏读,但是存在不可重复读取数据,即每次读取的数据不一样
这种级别是比较真实的数据,每一次读到的数据是绝对真实的
可重复读:repeatable read(mysql默认的隔离级别)
存在幻读问题,每一次读取的数据都是幻象,不够真实
序列化:serializable(最高隔离级别)
解决了所有问题,这种级别表示事务排队,不能并发
每一次读取的数据都是真实的,但是效率最低
5、索引
索引是在数据库表的字段上添加的,是为了提高查询效率的一种机制,一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引,索引相当于一本字典的目录
在实际中,汉语字典前面的目录是排序的,按照a,b,c,d排序,只有排序了,才会有区间查找,才能缩小范围查找
mysql数据库中索引也是需要排序的,mysql中索引是一个B-Tree数据结构,是一个自平衡二叉树,左小右大,采用中序遍历方式
主键和unique约束,mysql中都会自动创建索引对象
什么时候给字段添加索引?
- 数据量庞大
- 该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描
- 该字段很少的DML(insert,delete,update),因为增删改之后,索引需要重新排序
5.1、索引的创建和删除
创建索引
create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引,起名为emp_ename_index
删除索引
drop index emp_ename_index on emp;
怎么查看是否使用了索引进行检查
explain+查询语句
type = all 未使用索引
索引失效
-
模糊查询当中以%开头了,索引会失效
-
使用or的时候会失效,只有当or两边的条件字段都有索引,索引才会生效
-
使用复合索引,没有使用左侧的列查找
create index emp_job_sal_index on emp(job,sal); select * from emp where job = 'MANAGER';-- 索引有效 select * from emp where sal = 800;-- 索引失效
-
在where当中索引列参加了运算
-
在where当中,索引列使用了函数
6、视图
视图:站在不同角度看同一份数据
创建视图
create view view_name as 这里的语句必须是DQL语句
删除视图
drop view view_name
用视图做什么?
我们可以面向视图对象进行增删改查,会导致原表被操作