数据模型
关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
特点:
1、使用表存储数据,格式统一,便于维护。
2、使用SQL语言操作,标准统一,使用方便。
非关系型数据库
mysql客户端连接
mysql [-h 127.0.0.1] [-P 3306] -u root -p
sql通用语法(编写规则)
1、sql语句可以单行或多行书写,以分号;结尾。
2、sql语句可以使用空格或缩进来增强语句的可读性。
3、mysql数据库的sql语句不区分大小写,关键字建议使用大写。
4、注释:
1)、单行注释:--注释内容 或 #注释内容(mysql特有)
2)、多行注释:/* 注释内容 */
sql分类
DDL
Data Definition Language
数据定义语言,用来定义数据库对象(数据库、表、字段)。
DML
Data Manipulation Language
数据操作语言,用来对数据库表中的数据进行增删改。
DQL
Data Query Language
数据查询语言,用来查询数据库中表的记录。
DCL
Data Control Language
数据控制语言,用来创建数据库用户,控制数据库的访问权限。
DDL语句
数据库操作
查询所有数据库
show databases;
查询当前数据库
select database();
创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
[if not exists]:如果数据库不存在,则创建,如果存在则不创建,避免了报错。
[default charset 字符集]:比如utf8,不建议使用utf8,因为utf8的长度只有3个字节,在数据库中有些特殊字符是4个字节的,所以推荐使用utf8mb4。
删除数据库
drop database [if exists] 数据库名;
使用数据库
use 数据库名;
表操作
查询当前数据库所有表
show tables;
查询表结构
desc 表名;
查询指定表的建表语句
show create table 表名;
删除表
drop table 表名;
DML语句
添加数据(insert)
给指定字段添加数据
insert into 表名(字段名1,字段名2,…)values(值1,值2,…);
给全部字段添加数据
insert into 表名 values(值1,值2,…);
批量添加数据
insert into 表名(字段名1,字段名2,…)values(值1,值2,…),(值1,值2,…);
insert into 表名 values(值1,值2,…),(值1,值2,…);
注意:
1、插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
2、字符串和日期型数据应该包含在引号中。
3、插入的数据大小,应该在字段的规定范围内。
修改数据(update)
update 表名 set 字段名1=值1,字段名2=值2,… [where 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
删除数据(delete)
delete from 表名 [where 条件];
注意:
1、delete语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
2、delete语句不能删除某一个字段的值(可以使用update)。
DQL语句
基本查询
查询多个字段
select 字段1,字段2,字段3,… from 表名;
select * from 表名;
设置别名
select 字段1 [as 别名1],字段2 [as 别名2],字段3 [as 别名3],… from 表名;
注意:
1、别名不是必须,可以有,可以没有。
2、as可以省略不写。
去除重复记录
select distinct 字段列表 from 表名;
条件查询
语法
select 字段列表 from 表名 where 条件列表;
条件
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between…and… | 在某个范围之内(含最小、最大值) |
in(…) | 在in之后的列表中的值,(多个条件任意一个成立) |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 是null |
逻辑运算符 | 功能 |
and 或 && | 并且(多个条件同时成立) |
or 或 || | 或者(多个条件任意一个成立) |
not 或 ! | 非,不是 |
聚合函数
将一列数据作为一个整体,进行纵向计算。
注意:null值不参与所有聚合函数的运算。
常见聚合函数 | 功能 |
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
语法
select 聚合函数(字段列表) from 表名;
分组查询
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where与having区别
1、执行时机不同:where是分组之前进行过滤,不满足where条件的,不参与分组。而having是分组之后对结果进行过滤。
2、判断条件不同:where中不能使用聚合函数,而having可以。
注意:
1、执行顺序:where > 聚合函数 > having。
2、分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
排序查询
语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
排序方式
1、asc:升序(默认值)
2、desc:降序
注意:
语法支持多个字段排序,先按照字段1排序,如果第一个字段值相同,则按照第二个字段排序。
分页查询
语法
select 字段列表 from 表名 limit 起始索引,查询记录数;
注意:
1、起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数。
2、分页查询是数据库的方言,不同的数据库有不同的实现,mysql中是limit。
3、如果查询的是第一页数据,起始索引可以省略。
练习
1、查询第1页员工数据,每页显示10条记录
select * from emp limit 0,10;
或者起始索引可以省略
select * from emp limit 10;
2、查询第2页员工数据,每页显示10条记录
select * from emp limit 10,10;
DCL语句
用户管理
查询用户
use mysql;
select * from user;
创建用户
create user ‘用户名’@’主机名’ identified by ‘密码’;
仅当前主机访问:localhost
任意主机访问:%
修改用户密码
alter user ‘用户名’@’主机名’ identified with mysql_native_password by ‘新密码’;
删除用户
drop user ‘用户名’@’主机名’;
权限控制
权限列表
mysql中定义了很多权限,但常用的就以下几种:
常用权限 | 说明 |
ALL,ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
查询权限
show grants for ‘用户名’@’主机名’;
授予权限
grant 权限列表 on 数据库名.表名 to ‘用户名’@’主机名’;
如果是所有数据库和所有表就可以写*.*
撤销权限
revoke 权限列表 on 数据库名.表名 from ‘用户名’@’主机名’;
函数
函数是指一段可以直接被另一段程序调用的程序或代码。
字符串函数
mysql中内置了很多字符串函数,常用的几个如下:
常用函数 | 功能 |
CONCAT(S1,S2,…Sn) | 字符串拼接,将S1,S2,…Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度(n没有0) |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度(n没有0) |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串(start索引从1开始) |
语法
select 函数(参数);
练习
1、由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的,全部在前面补0,比如:1号员工的工号应该为00001。
update emp set workno = lpad(workno,5,’0’);
数值函数
常用数值函数如下:
常用函数 | 功能 |
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模(取余) |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
语法
select 函数(参数);
练习
1、通过数据库的函数,生成一个六位数的随机验证码。
select lpad(round(rand()*1000000,0),6,’0’);
日期函数
常用日期函数如下:
常用函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数(date1减去date2) |
语法
select 函数(参数);
练习
1、查询所有员工的入职天数,并根据入职天数倒序排序。
select name,datediff(curdate(),entrydate) from emp order by datediff(curdate(),entrydate) desc;
流程函数
流程函数也是很常用的一类函数,可以在sql语句中实现条件筛选,从而提高语句的效率。
常用函数 | 功能 |
IF(value,t,f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为null,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] …ELSE [default] END | 如果val1为true,返回res1,…否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END | 如果expr的值等于val1,返回res1,…否则返回default默认值 |
语法
select 函数(参数);
练习
1、统计班级各个学员的英语和语文成绩,展示的规则如下:
>=85,展示优秀
>=60,展示及格
否则,展示不及格
select
name,
(case when english >=85 then ‘优秀’ english >=60 then ‘及格’ else ‘不及格’ end) as ‘英语’,
(case when chinese >=85 then ‘优秀’ chinese >=60 then ‘及格’ else ‘不及格’ end) ‘语文’
from score;
约束
概述
1、概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。可以在创建表/修改表的时候添加约束,一个字段可以添加多个约束,多个约束之间用空格分开即可。
2、目的:保证数据库中数据的正确、有效性和完整性。
3、分类:
约束 | 描述 | 关键字 |
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识、要求非空且唯一 | PRIMARY KEY(自增:auto_increment,mysq独有) |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
外键约束
子表中的外键字段关联的就是父表中的主键字段,让两张表产生连接。
具有外键的表称之为子表(也叫从表),外键所关联的表称之为父表(也叫主表)。
添加外键语法
1、创建表的时候直接添加。
2、修改表结构的时候添加,如下:
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主键字段名);
删除外键语法
alter table 表名 drop foreign key 外键名称;
外键约束的删除/更新规则
规则 | 说明 |
NO ACTION(默认) | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT(默认) | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有,则设置子表中该外键值为null。(这就要求该外键允许取null) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值。(lnnodb不支持) |
语法
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主键字段名) on update 规则名 on delete 规则名;
多表查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
1、一对多(多对一)
2、多对多
3、一对一
一对多(多对一)
1、案例:部门与员工的关系。
2、关系:一个部门对应多个员工,一个员工对应一个部门。
3、实现:在多的一方(员工表)建立外键,指向一的一方(部门表)的主键。
多对多
1、案例:学生与课程的关系。
2、关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择。
3、实现:建立第三张中间表(学生课程关系表),中间表至少包含两个外键,分别关联两方主键(学生表、课程表)。
一对一
1、案例:用户与用户详情的关系。
2、关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。
3、实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)。
多表查询概述
概述:从多张表中查询数据。
笛卡尔积:笛卡尔乘积是指在数学中,两个集合,A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)。
多表查询分类
连接查询
内连接:相当于查询A、B交集部分数据。
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据。
右外连接:查询右表所有数据,以及两张表交集部分数据。
自连接:当前表与自身的连接查询,自连接必须使用表别名。
联合查询:对于union/union all查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
子查询
内连接
隐式内连接
select 字段列表 from 表1,表2 where 条件…;
练习
1、查询每一个员工的姓名,及关联的部门的名称(用隐式内连接)
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
(inner可以省略)
练习
1、查询每一个员工的姓名,及关联的部门的名称(用显式内连接)
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
隐式内连接与显式内连接的区别
区别在于表的连接形式(一个,号划分表,一个用inner join划分)和连接条件的表示形式(一个用where,一个用on)。
外连接
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件…;
相当于查询表1(左表)的所有数据,及包含表1和表2交集部分的数据。
练习
1、查询emp表的所有数据,和对应的部门信息(左外连接)
select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id;
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件…;
相当于查询表2(右表)的所有数据,及包含表1和表2交集部分的数据。
练习
1、查询dept表的所有数据,和对应的员工信息(右外连接)
select dept.*,emp.* from emp right outer join dept on emp.dept_id = dept.id;
自连接
自连接查询,自己连接自己,可以是内连接查询,也可以是外连接查询。
select 字段列表 from 表A 别名A join 表A 别名B on 条件…;
练习
1、查询员工及其所属领导的名字。
select a.name,b.name from emp a inner join emp b on a.managerid = b.id;
2、查询所有员工及其领导的名字,如果员工没有领导,也需要查询出来。
select a.name,b.name from emp a left outer join emp b on a.managerid = b.id;
联合查询
对于union/union all查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
注意:
1、对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
2、union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
语法
select 字段列表 from 表A …
union (all)
select 字段列表 from 表B …;
练习
1、将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来。
select * from emp where salary < 5000
union
select * from emp where age > 50;
子查询
概念:sql语句中嵌套select语句,称为嵌套查询,又称子查询。子查询外部的语句可以是insert/update/delete/select的任何一个,常见的就是select。
select * from t1 where column1 = (select column1 from t2);
根据子查询的结果不同,分为:
1、标量子查询(子查询结果为单个值)
2、列子查询(子查询结果为一列)
3、行子查询(子查询结果为一行)
4、表子查询(子查询结果为多行多列)
根据子查询位置,分为:
1、where之后。
2、from之后。
3、select之后。
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
练习
1、查询销售部的所有员工信息。
select * from emp where deptid = (select deptid from dept where deptname = ‘销售部’);
2、查询在李白入职之后的员工信息。
select * from emp where time > (select time from emp where name = ‘李白’);
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用操作符:in,not in,any,some,all
操作符 | 描述 |
in | 在指定的集合范围内,有一个满足即可(多选一) |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,有任意一个满足即可 |
some | 与any相同,使用some的地方也都可以使用any |
all | 子查询返回列表的所有值都必须满足 |
练习
1、查询销售部和市场部的所有员工信息。
select * from emp where deptid in (select id from dept where name = ‘销售部’ or name = ‘市场部’);
2、查询比财务部所有人工资都要高的员工信息。
select * from emp where salary > all (select salary from emp where deptid = (select id from dept where name = ‘财务部’));
3、查询比研发部其中任意一人工资高的员工信息。
select * from emp where salary > any (select salary from emp where deptid = (select id from dept where name = ‘研发部’));
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用操作符:=、<>、in、not in
练习
1、查询与李白的薪资及直属领导相同的员工信息。
select * from emp where (salary,managerid)= (select salary,managerid from emp where name = ‘李白’);
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用操作符:in
表子查询经常出现在from之后,把表子查询的返回结果作为一张临时表,在和其他表进行联合查询。(练习2)
练习
1、查询与张三、李白的职位和薪资相同的员工信息。
select * from emp where (job,salary)in (select job,salary from emp where name = ‘张三’ or name = ‘李白’);
2、查询入职日期是“2006-01-01”之后的员工信息,及其部门信息。
select e.*,d.* from (select * from emp where time > ‘2006-01-01’) e left outer join dept d on e.dept_id = d.id;
事务
事务简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败,从而保证数据的完整性和一致性。
以银行转账来举例,以数据库的角度来说,转账有3步。
1、查询张三的账户余额。
2、张三账户余额-1000。
3、李四账户余额+1000。
如果第3步出错,那么就会造成张三余额少1000,但李四账户没有增加1000,造成数据错误,所以要把这3步一起加到一个事务的范围内,要么同时成功,要么同时失败,在第1步之前,首先会开启事务,然后执行第1、2、3步,如果3步全部执行成功,那么最后再提交事务,即同时成功。如果第3步失败了,可以进行事务的回滚操作,即把之前临时修改的数据给恢复,即同时失败,保证数据的完整性和一致性。
注意:
默认mysql的事务是自动提交的,也就是说,当执行一条DML语句时,mysql会立即隐式地提交事务。
事务操作
查看/设置事务提交方式
查看事务的提交方式,如果为1则是自动提交,如果为0则是手动提交。
select @@autocommit;
设置
set @@autocommit = 0;
提交事务
commit;
回滚事务
rollback;
开启事务
start transaction 或 begin;
一旦执行了这条语句,就意味着后面要手动提交事务。(执行了提交或回滚操作后,事务结束,下次再想使用事务时,必须再次执行start命令)
事务四大特性(ACID)
1、原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
2、一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
3、隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
4、持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
问题 | 描述 |
脏读 | 一个事务读到另外一个事务还没有提交的数据。 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影。 |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read uncommitted | 不可规避 | 不可规避 | 不可规避 |
Read committed(oracle默认级别) | 可规避 | 不可规避 | 不可规避 |
Repeatable Read(mysql默认级别) | 可规避 | 可规避 | 不可规避 |
Serializable | 可规避 | 可规避 | 可规避 |
Serializable的隔离级别是最高的,数据最安全,但性能是最差的。
Read uncommitted的性能是最高的,但隔离级别是最差的。
查看事务隔离级别
select @@transaction_isolation;
设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
seesion:仅针对当前客户端窗口有效。
global:针对所有客户端的会话窗口有效。