数据库学习

最新推荐文章于 2021-11-29 10:38:01 发布

图图小顽皮

最新推荐文章于 2021-11-29 10:38:01 发布

阅读量383

点赞数 1

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/qq_48595828/article/details/118973849

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库

—1概念：储存和管理数据的仓库；

主要分为两类：

1.关系数据库 :Oracle、MySQL、SQLServer、Access

2. 非关系数据库 :MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

–2，新增：create database 库名

–3，删除：drop database 库名

四，表的常见操作

–1，创建表：create table 表名(字段名字段类型(字段长度)，字段2，字段3…)

–2，删除表 : drop table 表名

–1,新增：insert into 表名 values(‘字段1的值’ , “字段2的值” , …)

–2,删除：delete from 表名

–3,修改：update 表名 set 字段名=字段的新值

–4,查询：select 字段名 from 表名

#round四舍五入 & ceil向上取整 & floor向下取整

二.条件查询

1.关键字概述

distinct去重 where条件过滤 like模糊查询 null空 between...and之间 limit分页 order by升序------desc降序

1.关键字用法（max最大值 min最小值 sum求和 avg平均数）

四.统计练习

1.count 统计个数

2.分组(group by)

分组总结：分组后过滤条件用having 低效可以使用聚合函数。分组前过滤条件使用where 高效但是不能用聚合函数（在where里）

数据库

—1概念：储存和管理数据的仓库；

主要分为两类：

1.关系数据库 :Oracle、MySQL、SQLServer、Access

2. 非关系数据库 :MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

–2，安装

一路next，注意几个地方：端口号默认是3306，字符集改成utf-8，密码：root

-3，检查

方式1：win+r打开了运行的对话框，输入cmd回车。输入固定的命令：mysql -uroot -proot
方式2：打开开始菜单，找到Mariadb，打开 Mariadb MySql Client，直接输密码回车

-4，数据库的结构

数据库–表--字段的名字和字段的值

二，SQL语言
-1，概念

是结构化查询语言，用来操作数据库，对库里的数据进行增删改查CRUD

-2，分类

DML：数据操纵语言，指对数据的操作，CRUD
DDL：数据定义语言，创建库，创建表
DCL：数据控制语言，分配具体操作数据的权限
DQL：数据查询语言，主要是指对数据的查询

三，数据库的常见操作

–1，查询：show databases

mysql> show databases;

–2，新增：create database 库名

mysql> create database pool;
mysql> show databases;

–3，删除：drop database 库名

mysql> drop database pool;
Query OK, 0 rows affected (0.00 sec)

四，表的常见操作

–1，创建表：create table 表名(字段名字段类型(字段长度)，字段2，字段3…)

String -> varchar
int -> int
double -> double

mysql> create table pool(id int(3),
    -> name varchar(10),
    -> age int(3)
    -> );
mysql> show tables;

–2，删除表 : drop table 表名

mysql> drop table pool;

–3，修改表

mysql> alter table pool add column addr varchar(100);
mysql> desc pool;

–4，查看表

mysql> show tables;

–5，描述表

mysql> desc pool ;

五，表里的记录的常见操作

–1,新增：insert into 表名 values(‘字段1的值’ , “字段2的值” , …)

mysql> insert into pool values(10000,'first',"010-666888",'bj');

–2,删除：delete from 表名

mysql> delete from pool;
mysql> select * from pool;

–3,修改：update 表名 set 字段名=字段的新值

mysql> update pool set addr='shanghai';

–4,查询：select 字段名 from 表名

select id from pool; --只查id列的值
select id,addr from pool; --查id和addr列的值
select * from pool; --查所有列的值 -- 低效

六，客户端工具

–1，写SQL

七，字段约束

#没有约束，id的值可以重复，可以是null
create table a(id int)

#主键约束，id的值不能重复，不能是null
create table b(id int PRIMARY key)

#主键约束自动递增，id的值不能重复，不能是null，而且自动递增
create table c(id int PRIMARY key auto_increment)

#没有非空约束：字段的值可以为空
create table d( name varchar(10) )
#非空约束：字段的值不能为空
create table e( name varchar(10) not null )

#不用唯一约束：字段的值可以重复
create table f( name varchar(10) )
#唯一约束：字段的值不能重复
create table g( name varchar(10) unique )

一，Mysql的基础函数

–1，常见函数

lower–全转小写
upper–全转大写
length–求长度
concat–拼接串
substr–截取字符串
replace–替换字符串
ifnull–对null元素的替换
round–对小数四舍五入
ceil–对小数向上取整
floor–对小数向下取整
now–获取当前的年月日时分秒
year–获取日期里的年
month–获取日期里的月
day–获取日期里的日
hour–获取日期里的时
minute–获取日期里的分
second–获取日期里的秒

–2，测试

#基础函数
#LOWER全转小写
SELECT dname from dept;#初始数据
SELECT dname,LOWER(dname) 转小写 FROM dept;
#lower转大写
SELECT dname from dept;
SELECT dname,UPPER(dname) 转大写 from dept;
#Length求长度
SELECT LENGTH(dname)from dept;
SELECT LENGTH(loc)from dept;
SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;
SELECT dname FROM dept
#截取substr
SELECT dname,SUBSTR(dname,1,3)FROM dept;
#concat拼接字符串
SELECT dname,concat(dname,1) x FROM dept;
#replace替换
SELECT dname,REPLACE(dname,'a',"666") x FROM dept;
#ifnull判断如果是null，用什么替换
SELECT IFNULL(comm,0)comm from emp;
#round四舍五入，ceil向上取整，floor向下取整
SELECT comm,ROUND(comm) from emp;
SELECT comm,ROUND(comm,1) from emp;
SELECT comm,ceil(comm),FLOOR(comm) from emp;
#NOW() CURDATE()年与日- curtime 时与秒
SELECT NOW(),HOUR(now()),MINUTE(NOW()),SECOND(NOW()) from emp;

lower全转小写

upper全转大写

length求长度

一个字母或数字占1一个字节,一个汉字占3个字节

SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;

截取字符串substr （x,y）

x表示从第几个开始截取，y表示截取几个，y可以超过字段值的长度，但是只显示到最后一个字段值

列如：x=2,y=111,此字段值长度为10，查询结果如下

正常查询：

SELECT dname,SUBSTR(dname,2,5) FROM dept;

concat拼接字符串

SELECT dname,concat(dname,1) 拼接后 FROM dept;

拼接汉字要用 ' ' 包裹

replace替换

SELECT dname,REPLACE(dname,'a',"666") x FROM dept;

ifnull如果是null就替换

SELECT IFNULL(comm,0)comm from emp;

第一个原数据是null

#round四舍五入 & ceil向上取整 & floor向下取整

二.条件查询

1.关键字概述

distinct去重 where条件过滤 like模糊查询 null空 between...and之间 limit分页 order by升序------desc降序

1.1distinct 用法


select loc from dept #包含重复结果

包含重复效果

SELECT DISTINCT loc FROM dept; #去掉重复结果

都比较简单就不一一举例

1.2where and or 用法

#where用来过滤数据
#查询部门编号=1的记录
select * from dept  #全表查--低效
select * from dept where deptno=1  #只查1条--高效
#SQL的执行顺序 from  where  select 

#查询在二区的部门
select * from dept where loc="二区"

#查询在二区的research的部门
select * from dept where loc='二区' and dname='research'

#查询在二区的research的部门编号
select deptno from dept where loc='二区' and dname='research'

#查询在一区的编号是1的部门名称
select dname from dept where loc='一区' and deptno=1 #and高效

#查询在一区的部门或者编号是3的部门
select * from dept where loc='一区' or deptno=3 #or低效
select * from dept where dname = 'o'  #明确的条件,查询名称=o的部门

1.3like

#like模糊的条件 %占位符匹配0~n个字符
select * from dept where dname like '%o%' #模糊的条件,查询名称里包含o的部门 
select * from dept where dname like 'a%' #以a开头的部门--高效
select * from dept where dname like '%ch' #以ch结尾的部门
#查询在一区的部门或者名称包含ting的部门
select * from dept where loc='一区' or dname like '%ting%'

1.4null

#查询comm是空的 员工的信息
select * from emp where comm is null
#查询comm不为空的 员工的信息
select * from emp where comm is not null
#查询每个员工的月薪
select *,sal+comm from emp 
select *,sal+ifnull(comm,0) from emp  #null不参与运算，需要特殊处理
select *,sal+ifnull(comm,0),sal*12+ifnull(comm,0)*12 from emp

月薪=奖金+工资 == 月薪=sal+comm

因为null特殊不能参与运算，需要特殊处理，所以用ifnull(comm,o)；把comm中的null都换成0来计算

1.5between and

#查询工资(5000,10000)的员工信息
select * from emp where sal>5000 and sal<10000
select * from emp where sal between 5000 and 10000 #都包含[5000,10000]

注意between是闭区间

1.6limit

#分页 limit,限制数据的条数
select * from emp limit 2 #取前两条
select * from emp limit 0,2 #从0(第1条记录)开始,取两条
select * from emp limit 2,2 #从2(第3条记录)开始,取两条
select * from emp limit 1,3 #从1(第2条记录)开始,取三条
#查询工资>5000的前两条记录
select * from emp where sal>5000 limit 2

这是emp所有数据

当我只要前两条数据注意语法规则是 select * from emp limit 2

如果我想从总监开始取，取前两条语法是什么？

看下面效果，发现limit语句第一条数据不是从1开始而是从0开始

1.7order by

#order by排序 升序(默认的)、降序
select * from emp order by sal asc #升序
select * from emp order by sal desc #desc降序

1.8练习

#练习1：查询工资>5000的两个最高薪的员工信息
#练习2：查询名字里包含o的老员工
#练习3：查询名字里包含o的员工的在职时间
#练习4：2015年以前入职的老员工
#练习5：查询每个员工入职了几年
#练习6：查询2015~2019年入职的员工
#练习7：一年13薪，算年薪


SELECT *FROM emp   WHERE sal>5000 ORDER BY sal desc LIMIT 2#练习一

SELECT *FROM emp   WHERE sal>5000 ORDER BY comm  ASC LIMIT 2
#这是什么意思？comm字段是奖金

SELECT * FROM emp WHERE ename LIKE '%o%' #练习2

#练习三 YEAR(NOW()) 目前年份
SELECT *,YEAR(hiredate) 入职时间,YEAR(NOW())-YEAR(hiredate) 在职时间
FROM emp WHERE ename LIKE '%o%'

#练习4：2015年以前入职的老员工
select * from emp where year(hiredate)<2015
select * from emp where hiredate < '2015-1-1'
#练习5：查询每个员工入职了几年
select *,year(NOW())-year(hiredate) from emp 
#练习6：查询2015~2019年入职的员工
select * from emp where year(hiredate) >=2015 and year(hiredate) < 2019
select * from emp where year(hiredate)  between 2015 and 2019
#练习7：一年13薪，算年薪 
select *,sal*13+ifnull(comm,0)*13 from emp 
select *,sal*13+ifnull(comm,0)*13 as 年薪 from emp  #给列/字段设置别名
select *,sal*13+ifnull(comm,0)*13 年薪 from emp  #省略as

三.聚合函数

1.关键字用法（max最大值 min最小值 sum求和 avg平均数）

#查询最高薪的员工信息
select * from emp order by sal desc limit 1
#聚合函数写法
SELECT *,MAX(sal)最高工资 FROM emp

聚合函数是不是简单很多

四.统计练习

1.count 统计个数

#count统计个数/行数/总记录数
select count(*) from emp #低效
select count(1) from emp #高效 底层做了优化所以高效
select count(empno) from emp 
select count(comm) from emp #最低效,按照字段名统计个数,如果字段值是null将不做统计 
#这个了解一下后面要用到

#统计 名字里包含a的员工人数
select * from emp 
select count(1) from emp where ename like '%a%'
#统计 普通员工 的平均工资
select avg(sal) from emp where job='员工'
#统计 2019年入职的员工的总人数
select count(1) from emp where year(hiredate)=2019
#统计 2号部门每年的工资开销
select sum(sal+ ifnull(comm,0) )*12 年开销 from emp where deptno=2

2.分组(group by)

概述：使用group by实现分组，使用having在分组后的结果上继续添加过滤条件

SELECT * FROM emp GROUP BY deptno #按照deptno进行分组

默认每组只展示一条数据

#分组  group by 字段名 -- 按照指定字段分组
#如果查询时,出现了聚合列和非聚合列,通常要按照非聚合列分组
（什么是聚合列？从上面目录找相关知识）
#部门的字段是deptno
#查询 每个部门里 的最高薪和人名
select deptno,ename,max(sal) from emp 
group by deptno

#查询 每种岗位 的平均工资和岗位名称
select avg(sal),job from emp group by job #按非聚合列分组

#查询 每个部门 的平均工资
select avg(sal) from emp group by deptno #分析需求，要按照部门分组
#统计 每个部门 出现的次数
select count(1) from emp GROUP BY deptno

#having 在分组后的结果中,继续添加过滤条件

#查询 每种岗位 的平均工资和岗位名称 --进一步查员工的
select avg(sal),job from emp 
#where job='员工' #分组前需要过滤,使用where --高效
group by job
#having job='员工' #分组后需要过滤,使用having --相对低效

#统计 部门 出现的次数--再过滤次数>1的
select count(1),deptno from emp 
#where count(1) > 1 #分组前过滤用where,但是where里不能出现聚合函数
group by deptno
having count(1) > 1 #分组后过滤用having

#查询 每个部门 的平均工资 --再过滤>10000的
select avg(sal),deptno from emp 
#where avg(sal)>10000  #where里不能有聚合函数
group by deptno
having avg(sal)>10000 #分组后过滤having

分组总结：分组后过滤条件用having 低效可以使用聚合函数。分组前过滤条件使用where 高效但是不能用聚合函数（在where里）

五.事务

1.概述

能保证多条SQL要么全成功要么全失败
4个特性：ACID
原子性：多个SQL处于同一个事务里，要么全成功要么全失败
一致性：保证数据在不同的电脑里是一致的
隔离性：数据库支持并发访问，保证事务间是隔离的，互不影响
持久性：对数据库的操作是永久的

2.事务的隔离级别

读未提交：性能最好，数据的安全性最差
读提交：Oracle的默认的隔离级别 – 性能较好，安全性较差
可重复读：Mysql的默认的隔离级别 – 性能较差，安全性较好
串行化：安全性最高，但是表级的锁，效率低

3.事务的处理

#Mysql默认就开启了事务，但是每条SQL一个事务
begin;#开启事务
insert into dept values(null,'java开发部','北京');
insert into dept values(null,'php开发部','上海');
commit;#提交事务--会对数据库产生持久影响
#rollback;#回滚事务--不会对数据库产生持久影响

去查看dept表发现多了两条数据

六.字段的约束

1.概述

外键约束：把两张表之间的关系，通过两个表的主键来表示
默认约束：给指定的字段设置默认值
检查约束：给字段增加检查条件，符合才能操作，不符合不能操作

2.示例

create table h(
	id int primary key auto_increment,
	sex char(3) default '女' #default设置默认值，给指定的字段设置默认值
)

create table i(
	id int primary key auto_increment,
	age int,
	check(age>0 and age<=150)#检查约束：给字段增加检查条件，符合才能操作，不符合不能操作
)

3.外键约束

#外键约束
create table tb_user(
	id int primary key auto_increment,
	name varchar(20),
	age int
)
create table tb_user_addr(
	user_id int primary key auto_increment,
	addr varchar(200) ,
	#描述了两张表之间通过哪个字段关联着--外键约束
	#  外键    (子表的字段)   参考      主表名称(主表的字段)
	foreign key(user_id)    references   tb_user(id)
)

七.索引

1.概述

好处：给加完索引的列，提高查询效率

坏处：索引本质上就是一张表，如果表的体积太大，比较占内存

主键本身就有索引、

分类：

单值索引(一个索引只包含着一个列)

复合索引(一个索引包含着多个列)

唯一索引(一个索引只包含着一个列，但是要求列的值不能相同)

2.创建索引

#1. 创建 普通索引： create index 索引名 on 表名(字段名)
#经常被查询的字段建议加索引,
create index dname_index on dept(dname)
#2. 查看索引(观察三列：表名/字段名/索引名)
show index from dept
#3. 使用索引,按照索引列查的快
explain
select * from dept where dname like '%a%'
#4. 创建 唯一索引：alter table 表名 add unique(字段名)
	#alter table dept add unique(loc) loc的值已经重复了，不能使用唯一索引
alter table dept add unique(dname)
#5. 创建 复合索引：给多个字段加一个索引
alter table dept add index fuhe(dname,loc)
#6. 删除 索引 :alter table 表名 drop index 索引名
alter table dept drop index dname_index

八.复合索引

1.用法

select * from emp
show index from emp
alter table emp add index fuhe_index(job,ename) #添加复合索引
explain
select * from emp where job='员工' and ename='jack' #索引生效
explain
select * from emp where ename='jack' and job='员工'  #索引生效
explain
select * from emp where job ='员工'  #索引生效
explain
select * from emp where ename='jack'  #不匹配最左特性，索引失效
explain
select * from emp where job like '%员工%'  #模糊查询，索引失效
explain
select * from emp where job='员工' or ename='jack' #or条件查询，索引失效

九.视图

1.概述

本质上就是缓存了查询的结果，作为一个表体现

#视图：把一个查询语句的结果缓存起来存入视图中
#好处：提高查询效率
#坏处：占用了内存，无法进行SQL优化，当更新了数据时视图也需要时间更新
#1. 创建视图 create view 视图名 as SQL语句 
create view empView as 
select * from emp where ename like '%a%' #把SQL执行的结果存入视图

#2. 使用视图,就当做一张表来用
select * from empView

10.多表联查

1.概述

是指联合查询多张表里的数据

2.用法

笛卡尔积：通过逗号隔开多个表，写在from后面。通过where 连接表之间的关联关系
表连接：通过join连接多个表，分为内连接和外连接。通过on描述表关系，通过where添加过滤条件
子查询：把上一次的查询结果，作为条件继续使用

3.笛卡尔积测试

#多表联查1：笛卡尔积，把多个表用逗号隔开 
#使用指定的字段deptno来描述两个表之间的关系
select * from dept , emp 
#表名.字段名
where dept.deptno=emp.deptno 
#要明确的表明使用哪个表里的哪个字段
and dept.deptno=1  

#使用别名
select * from dept a, emp b
where a.deptno=b.deptno 
and a.deptno=1 

#查询1号部门的员工姓名
select ename from dept a,emp b
where a.deptno=b.deptno#描述两个表的关系
and a.deptno=1 #查询1号部门的

#查询tony所在的办公地址
select b.loc from emp a , dept b
where a.deptno=b.deptno #描述表关系
and a.ename='tony' #业务条件

#查询accounting部门的员工信息
select b.* from dept a ,emp b
where a.deptno=b.deptno #描述表关系
and a.dname='accounting' #业务条件

#练习：查询二区员工的平均薪资
select avg(b.sal) from dept a ,emp b
where a.deptno=b.deptno #描述表关系
and a.loc='二区' #业务条件

4.join

#多表联查2：表连接 join，把多个表用join连接
select * from dept join emp 
on dept.deptno=emp.deptno #描述两个表之间的关系

#查询Jack的部门名称
select a.dname from dept a join emp b
on a.deptno = b.deptno  #描述表间的关系
where b.ename='jack' #过滤条件

#查询二区办公的员工名字
select b.ename from dept a join emp b
on a.deptno = b.deptno  #描述表关系
where a.loc='二区' #业务条件

#查询二区里的最高薪
select max(b.sal) from dept a join emp b
on a.deptno = b.deptno  #描述表关系
where a.loc='二区' #业务条件

#查询二区部门的部门信息和员工信息
#join分为内连接(inner join)  外连接(left join/right join)
#inner join：取左右两个表中，都满足了条件的记录
#left join：取左表里的所有数据，右边中满足了条件的记录不满足的用null填充
#right join：取右表里的所有数据，左边中满足了条件的记录不满足的用null填充

#select * from dept a inner join emp b #取交集
#select * from dept a left join emp b #取左表的所有和右边满足条件的不满足条件都是null
select * from dept a right join emp b #取左表的所有和右边满足条件的不满足条件都是null
on a.deptno=b.deptno
where a.loc='二区'

11.子查询

#列出research部门的所有员工的信息
#笛卡尔积
select b.* from dept a,emp b 
where a.deptno=b.deptno #表关系
and a.dname='research' #业务条件

#join连接
select b.* from dept a left join emp b 
on a.deptno=b.deptno #表关系
where a.dname='research' #业务条件

#子查询：把第一次的查询结果作为第二次查询的条件 
#练习1：列出research部门的所有员工的信息
#1.根据部门名称查部门编号
select deptno from dept where dname='research'  
#2.回员工表,根据查到的编号查员工信息
#select * from emp where deptno=2
select * from emp where deptno=(
	select deptno from dept where dname='research' )
#练习2：查询tony的部门信息
select * from dept where deptno=  #再查部门的信息
(select deptno from emp where ename='tony') #先查tony的部门
#练习3：查询二区员工的姓名
#select ename from emp where deptno in(2,3)#指编号可以是2或者3
select ename from emp where deptno in 
(select deptno from dept where loc='二区')

#练习4：查询高于平均工资的员工姓名
#select ename from emp where sal > avg(sal)#where里不能有聚合函数
select ename from emp where sal > (
 select avg(sal) from emp
)

图图小顽皮

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库学习

数据库—1概念：储存和管理数据的仓库；主要分为两类：1.关系数据库 2. 非关系数据库–2，安装一路next，注意几个地方：端口号默认是3306，字符集改成utf-8，密码：root-3，检查方式1：win+r打开了运行的对话框，输入cmd回车。输入固定的命令：mysql -uroot -proot方式2：打开开始菜单，找到Mariadb，打开 Mariadb MySql Client，直接输密码回车-4，数据库的结构...
复制链接

扫一扫