首页  思维导图  详情

《数据库系统原理》日常使用

2021-10-13 11:21:48   0  举报





AI智能生成

实操语句，适用于自考、日常使用

自考

数据库系统原理

mysql

mariadb

计算机科学与技术

作者其他创作

大纲/内容

需要请联系1031574211

学习前提

MariaDB与mysql 的操作命令一致，所以直接学习mysql即可

二者的关系

先创建有了mysql

后mysql被sun公司收购

后sun公司被Oracle公司收购，mysql的所有权归于Oracle

而创始人随后为mysql另开分支，创建了mariadb，并且其完全兼容mysql的所有命令和api

常用命令

mysql -u+(用户名） -p+(密码，如果不写，则后面输入密码时看不见内容）

退出

exit

直接ctrl+c（强制退出）

查看所有数据库

show databases;

查看该数据库中的表

show tables;

使用某个数据库

use 数据库名字;

创建数据库

create databases 数据库名;

导入数据库

source 绝对路径;

查看表的数据结构

desc 表名

查看mysql数据库的版本号

select version();

查看当前使用的数据库

select database();

DQL

概念：数据查询语言（凡是带有select 关键字的都是查询语句）

写法

select ....from ...where ...group by ...having ...order by ...limit ...

语句顺序：

第一步：from

第二步：where

第三步：group by

第四步：having

第五步：select

第六步：order by

第七步：limit

查看表内所有的数据

select * from 表名;

简单查询

select 字段名 from 表名;

查询多个字段

select 字段名,字段名,..... from 表明;

列起别名

select dname as deptname from 表名;

select dname deptname from 表名;

select dname "dept name" from 表名;

select dname 'dept name' from 表名;

列参与数学运算

select name,money*12 from emp;

条件查询

条件语句

= 等于

<> 或!= 不等于

< 小于

<= 小于等于

> 大于

>= 大于等于

between .... and ... 两个值之间，等同于>= and <=

is null 为空

不可用 = null因为数据的null不是一个值，不能用等号衡量

is not null 非空

and 并且

or 或者

in 包含，相当于多个or （not in 不在这个范围中）

in不是一个区间，括号内的是具体值

查询薪资是800或者500的员工

select 名字，员工 from 表名 where 工资 = 800 or 工资 = 5000;

select 名字，员工 from 表名 where 工资 in (800,5000);

not 取非,主要用在is和in

like 模糊查询，支持%或下划线匹配

% 匹配任意个字符

以T结尾

where 列名 like '%T';

以T开头

where 列名 like 'T%';

下划线，一个下划线只匹配一个字符

第二个字母为A

where 列名 like '_A%';

第三个字母为A

where 列名 like '__A%';

当存在双重条件语句时，可加括号进行修饰

找出工资大于2500并且部门编号为10或20的员工

select * from emp where 工资 > 2500 and (部门编号 = 10 or 部门编号 = 20);

排序

升序

select 列名 from 表名 order by 列名2;

默认情况下是按照列名2进行升序（由小到大）

select 列名 from 表名 order by 列名2 asc;

按照列名2进行升序（由小到大）

降序

select 列名 from 表名 order by 列名2 desc;

按照列名2进行降序（由大到小）

多个字段排序

查询员工名字和薪资要求按照薪资升序，如果薪资一样的话再按照名字升序排列

select 名字,工资 from 表名 order by 工资 asc ,名字 asc

按照字段位置进行排序

select 列名1,列名2 from 表名 order by 2;

按照列名2 进行排序（不建议再开发中这样写）

数据处理函数/单行处理行数

特点：一个输入对应一个输出

lower

转换小写

upper

转换大写

substr

去字串

select substr(列名,1,1) from 表名;

concat

字符串拼接

案例

首字母大写

select name from student;

select upper(substr(name,1,1)) from student;

select substr(name,2,length(name) - 1) from student;

select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as name from student;

length

取长度

trim

去空格

format(数字,'格式')

将查询结果的数字进行格式化

select ename,format(sal,'$999,999') as sal from emp;

str_to_date

将字符串varchar转换成date日期

date_format

格式化date日期成varchar字符串

通常用在查询日期方面，设置展示日期格式

now()可以获取当前时间

round

四舍五入

round(数值,保留几位小数)

若为负数，则保留到负几的整数位

rand()

生成随机数

只生成小数，如果需要整数，请配合round使用

ifnull

可将null转换成一个具体值

格式：ifnull(数据,被当作的值)

case...when...then...then...else...end

案例

当员工岗位为aaa时，工资上调10%；当工作岗位为bbb时，工资上调50%;其他保持不变;旧工资为old,调整后的工资为new;

select name, job, old, (case job when 'aaa' then old*1.1 when 'bbb' then old*1.5 else old end) as new from 表名;

分组函数/多行处理函数

特点：

多个输入对一个输出

分组函数不可使用在where后面

因为分组函数需要在分组之后才能使用where执行的时候还没有分组

分组函数可以在select和order by 时使用

select后面只能跟着参加分组的字段，以及分组函数

格式：

select ... from ... group by ...

执行顺序：

1.from

2.where

3.group by

4.having

5.select

6.order by

函数

count

计数

sum

求和

avg

平均数

max

最大值

min

最小值

having

可以对分组完成后的数据进一步的过滤

但having不能单独使用,必须和group by 联合使用,且不能代替where

一般在having中使用的都是where语句无法实现的功能

案例

找出每个部门，每个岗位的最高工资

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

找出每个部门的最高工资，要求显示最高薪资大于3000

select deptno,max(sal) from emp group by deptno having max(sal) > 3000;

优化策略：where和having，优先选择where，where实现不了的再选择having

select deptno,max(sal) from emp where sal > 3000 group by deptno;

找出每个部门的平均薪资，要求显示平均薪资高于2500

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

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，出'aaa'岗位之外

select job,avg(sal) as avgsal from emp where job != 'aaa' group by job having avg(sal) > 1500 order by avgsal desc;

distinct

去除关键字，放置在所有字段的最前方，表示把所有字段名去除重合数据

笛卡尔积现象

表的连接次数在只用where的时候会调用多次数据，效率不高

select ename,dname from emp,dept;

表连接使用join，可以减少调用次数，效率更高(99语法，语言更清晰)

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

99语法

select ...from ainner join bon a和b的连接条件where 筛选条件

inner 可省略

连接查询

内连接

特点：完成能够匹配上这个条件的数据查询出来

和外连接不同，没有主次关系

等值连接

非等值连接

自连接

外连接

特点：在外连接中存在主次关系，会把主表的数据全部查询出来，并关联查询左边的表

左外连接和右外连接可以相互转换，更改right 和left即可

左外连接

案例

selecet e.ename,d.dnamefrom dept d left outer join emp eon e.deptno = d.deptno

outer可以省略

右外连接

案例

selecet e.ename,d.dnamefrom emp e right outer join dept don e.deptno = d.deptno

outer可以省略

全连接

多表连接

连接查询中，内连接和外连接可以联合使用

语法

select .... from .... join ... on ... join ... on ... join ... on ...

子查询

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

语法

select ...(selecet)from ...(select)where ...(select)

案例

比最低工资高的员工和工资(where的子查询)

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

每个岗位的平均工资的薪资等级（from的子查询）

select t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal

找出每个员工的部门名称，显示员工名和部门名（select的子查询）

select e.ename,e.depno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

该方式的子查询结果只能返回一个，不能多个

合并

案例

合并工作岗位为'aaa'和'bbb'的员工

select ename,jpb from emp where job = 'aaa'uninoselect ename,job from emp where job = 'bbb';

特点

使用or的查询匹配次数满足笛卡尔积，查询次数成倍的翻使用unino的查询匹配可以减少匹配次数

注意事项

unino在结果集合并的时候要求列数相同

在合并的时候最好两个的数据类型一致，orcle会报错,mysql不会

limit

将查询结果的一部分取出来，通常使用在分页查询分页的作用是为了提高用户的体验，因为一次全部查出来，用户体验差，可以一页页翻

用法

limit 5

取出前五

limit 0,5

从下标0开始取，长度为5

案例

取出前五名的员工

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

注意：mysql中limit在order by 之后执行

分页

limit (页数-1)*分页长度，分页长度

DML

概念：数据操作语言（对表的数据进行insert、delete、update的都是DML）

数据类型

varchar（最长255）

可变长度的字符串

char （最长255）

定长字符串（不管实际多大，会分配固定长度的空间去存储，不恰当的时候会浪费空间）

int（最长11）

java的int

bigint

java的long

float

单精度高的浮点型

double

双精度高的浮点型数据

date

短日期类型

%Y-%m-%d

datetime

长日期类型

%Y-%m-%d %h:%i:%s

clob

字符大对象最多可以存储4G的字符串，例如存储一批那文章超过255个字符的都要采用CLOB字符大对象来存储

blob

二进制大对象专门来存储图片、声音、视频等流媒体往BLOB类型插入数据的时候，需要使用IO流

mysql的日期格式

年

月

日

时

分

秒

建表

create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型);

快速复制建表

create table emp2 as select * from emp;

删表

drop table 表名;

当表不存在的时候会报错

drop table if exists 表名;

如果这张表存在就删除

插入数据

插入单条记录

insert into 表名（字段名1、字段名2、字段名3...） value (值1、值2、值3...);

插入多条记录

insert into 表名 (字段名) values (...),(...)...

将查询结果插入（很少用）

insert into dept_bark select * from dept;

注意：插入时需要跟形参对应

修改数据

update 表名 set 字段名-值1，字段名2-值2,where 条件;

删除数据

delete from 表名 where 条件;

不加条件会全部删除，但是比较慢，数据可以恢复

快速删表

truncate table 表名

效率较高，无法恢复

DDL

概念：数据定义语言（对表的结构进行create、drop、alter的都是DDL）

约束

非空约束：no null

字段不能为空

案例

create tabele v_vip{ id int, name varchar(255) not null}；

唯一性约束：unique

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

案例

列级约束：单个字段具有唯一性

create tabele v_vip{ id int, name varchar(255) unique}；

表级约束：多个字段联合起来具有唯一性

create tabele v_vip{ id int, name varchar(255),email varchar(255),unique(name,email)}；

主键约束：Primary key（简称PK）

相关术语

外键约束

一种约束

外键字段

该字段添加了外键约束

外键值

外键字段中的每一个值

业务背景

请设计数据库表，描述“班级和学生”的信息

班级表

将班级名称对应编号

create table t_class{ classon int primary key, classname varchar(255)};

学生表

使用对应的班级编号

create table t_student{ no int primary key auto_increment, name varchar(255), cno int, foreign key(cno) refereces t_class(classno)};

格式

foreign key(子表字段) refereces 父表(父表字段)

问题

子表中的外键引用的父表中的某个字段，被引用的这个字段必须时主键吗

不一定是主键，但至少有unique约束，因为外键引用可以为null

外键可以为null吗

可以为null

检查约束：check（mysql不支持，oracle支持）

分支主题

存储引擎

怎么添加/指定存储引擎

show create table 表名;

ENGING

指定存储引擎

默认的存储引擎是：InnoDb

CHARSET

指定这张表的编码方式

默认的字符编码方式是：UTF8

九大存储引擎

当前5.5.36支持8个，版本不同支持情况不同

常用存储引擎

MyISAM存储引擎

特点

有三个文件：格式文件（存储表结构）.frm、数据文件（存储表内容）.MYD、索引文件（存储表上的索引，减少搜索范围）.MYI

可被转换为压缩、只读表来节省空间

提示

主要是主键或者具有unique约束的字段会自动创建索引

InnoDB存储引擎

特点

支持事物，支持数据库崩溃后自动恢复机制，非常安全

.frm格式文件

MEMORY存储引擎

特点

表数据及索引存储在内存中，查询快

不能包含TEXT或BLOB字段

表级锁机制

不安全，关机之后数据消失

.frm格式文件

TCL

概念：事务控制语言（transaction）

事务提交：commit

清空事务性的日志文件，将数据持久化到数据库表中，标志这事务的成功结束

语句顺序

start transaction

DML语句

提交：commit

事务回滚：rollback

将之前的DML语句全部撤销，并且清空事务性的日志文件，标志这事务的失败结束

回滚只能回滚到上一次的提交点，但是mysql每执行一条语句，则提交一次（自动提交），需要另外关闭自动提交机制

语句顺序

start transaction

DML语句

回滚：rollback

四个特性

原子性

说明事物师最小的工作单元，不可再分

一致性

同一个事务中，所有操作要么同时成功，要么同时失败

隔离性

A事务和B事务之间具有一定的隔离A事务在操作一张表的时候，另一个事务B也操作这张表的话（多线程并发，具有安全问题）

持久性

事务最终结束的一个保障

隔离级别

读未提交：read uncommitted（最低的隔离级别）

事务A可以读取到事务B未提交的数据（脏读现象）

读已提交：read committed

事务A只能读取到事务B提交之后的数据（不可重复读取数据）

可重复读：repeatable read

事务A开启之后，每一次在事务A读取到的数据都是一致的，即使事务B修改了数据仍然不会改变（幻影读，不够真实）

mysql 的默认事务隔离级别就是可重复读

序列化 / 串行化：serializable（最高的隔离级别）

最高隔离级别，效率最低

事务排队，不能并发

查看当前事务隔离级别

select @@tx_isolation

更改隔离级别

set global transaction isolation level 隔离级别;

索引（Index）

在数据据库表的字段上添加的，为了提高查询效率存在的机制，相当于是一个目录在mysql当中以一个树的形式存在（自平衡二叉树，B-Tree）mysql中，竹简上，以及unique字段都会自动添加索引

什么条件下，会考虑给字段添加索引

条件一：数据量庞大

条件二：该字段经常出现在where后面

条件三：不常使用DML语句操作该字段

创建索引

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

删除索引

drop 索引名 on 表名

查看一条sql语句是否使用了索引进行检索

explain sql语句

索引失效

第一种情况：在使用模糊查询，并且条件是以“%”开头时索引无法使用

第二种情况：在使用or的时候会失效，如果使用or那么要求两边都添加索引，不然两边都会失效

第三种情况：联合索引

第四种情况：where当中的参与列进行了运算

第五种情况：在where当中索引列使用了函数

视图

新建视图

create view 视图名 as DQL语句

后面只能跟着DQL语句，并且形成的视图后修改其中的数据，先关联的表也会一起修改

删除视图

drop view 视图名

插入视图

insert into 视图名(字段名) values(值)

修改视图数据

update 视图名 set 字段名 = 值 where 条件

在开发的应用

简化select语句，简化开发

假设有一条非常复杂的sql语句，并且在不同的位置上反复的使用，每次使用都需要对这个sql语句重新的编写，很长很麻烦。这时候就可以利用视图来进行开发，利于后期的维护

提示

视图对应的语句只能是DQL语句。但是视图对象创建完成之后，可以对视图进行增删改查等操作

增删改查，又叫做“CURD”，在公司内部统一沟通的术语

C：Create（增）

R：Retrive（查：检索）

U ：Update

D：Delete

DBA

数据导出

mysqldump 数据库名>路径.sql -u root -p 密码

指定数据表

mysqldump 表名>路径 -u root -p 密码

数据导入

第一步：先登录到mysql数据库服务器上

第二步：创建数据库：create database 数据库名（需要和之前的库名保持一致）

第三步：使用数据库：use 数据库

子主题

DCL

数据控制语言

授权：grant

撤销权限：revoke

数据库设计三范式

第一范式

要求任何一张表必须有主键，每一个字段原子性不可再分

第二范式

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

第三范式

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

三范式是面试官经常问的，所以一定要记，可减少表中数据的冗余和空间的浪费

 收藏

立即使用

尚硅谷 - Docker资源

 收藏

立即使用

《数据库系统原理》日常使用

 收藏

立即使用

《数据库系统原理》读书笔记

耀

职业：暂无

去主页





0 条评论

下一页

为你推荐

查看更多

