小言_互联网的博客

数据库介绍及SQL语句及视图、事务

2020-05-28 15:53 674人阅读  评论(0)

数据库笔记

本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客

数据库介绍

  • 数据库DataBase：

用于存储和管理数据的仓库

  • 特点：
  1. 持久化存储数据，数据库就是一个文件系统
  2. 方便存储和管理数据
  3. 使用统一操作方式
  • 常见的数据库软件：
  1. Oracel
  2. MySQL
    MySQL图形化工具：SQLYog
  3. SQL Sever（微软中型数据库）
  4. DB2（IBM公司的）
  5. SQLite（嵌入式的小型数据库，应用在手机端）
  • MySQL目录结构：
  1. 数据库：文件夹
  2. 表：文件
  3. 数据：数据
  • 关系型数据库多表之间的关系
  1. 一对一（了解）：
  2. 一对多（多对一）：外键在多中，是一的主键
  3. 多对多： 学生 老师 必须新建一张表 实现关系：新建一张表

SQL

SQL定义

SQL：结构化查询语言
定义了操作所有关系型数据库的规则

  1. 以分号结尾
  2. 使用空格、缩进提高可读性
  3. MySQL数据库的SQL不区分大小写
  4. 三种注释： 单行:-- 注释内容或# 注释内容 多行:/注释/

SQL的分类：

DDL（数据定义语言：操作数据库和表）

  1. 操作数据库：CRUD
  • C（Create）：创建
	create database 名称;
	create database if not exists 名称;

--指定字符集为gbk：

	create database 名称 character set gbk;
  • R（Retrieve）：查询
--查询所有数据库的名称：
	show databases；
--查询某个数据库的字符集（查询某个数据库的创建语句）：
	show create database 数据库名称;
  • U (Update)：修改
--修改数据库的字符集
	alter database 数据库名称 character set 字符集名称;
  • D （Delete）:删除
--删除数据库
		drop database 数据库名称;
--判断数据库是否存在，存在再删除
		drop database if exists 数据库名称;
  • 使用数据库
--查询当前正在使用的数据库名称
	select database()
--使用数据库
	use 数据库名称;
  1. 操作表
  • C（Create）：创建
    1. 语法：
create table 表名（列名1 数据类型1，
				  列名2 数据类型2，	
）;

数据库类型：
1. int：整数类型
2. double：小数类型
3. date：日期，只包含年月日
4. datetime日期：包含年月日秒
5. timestamp：时间错类型
6. 字符串类型：varchar

  • R（Retrieve）：查询
--查询某个数据库中所有的表名称
	show tables;
--查询表结构
	desc 表名;
  • U (Update)：修改
  1. 修改表名
alter table 表名 rename to 新的表名;
  1. 修改表的字符集
--首先学会查看 
	show create table 表名;
alter table 表名 character set 字符集名称;
  1. 添加一列
alter table 表名  add 列名 数据类型;
  1. 修改列的名称、类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;
  1. 删除列
alter table 表名 drop 列名;
  • D （Delete）:删除
drop table 表名;
drop table if exists 表名;
  • 约束：
    概念：对表中的数据限定，保证正确性、有效性、 完整性； 分类：
  1. 主键约束：primary key
  • 注意：
  1. 含义：非空且唯一
    2. 一张表只能有一个字段为主键
    3. 主键就是表中记录的唯一标识
  • 在创建表时，添加主键约束
create table stu(
	id int primary key,
	name varchar(20)
);
  • 删除主键
alter table stu drop  primary key;
  • 创建完表后，添加主键约束
alter table stu modify id int primary key;
  • 自动增长
  1. 概念：如果某一列是数值类型的，使用auto_increment可以来完成自动增长；
    2. 在创建表时，添加主键约束，并且完成主键自增长:
    3. 删除
    alter table stu modify ID int；

增加知识点：identity【主键自动增长，用户不需要为identity修饰的主键赋值】 …primary key
identity(100,5);–id从100开始，以5递增，可以并不设置参数，为1,1；

  1. 非空约束：not null
  • 创建表时添加约束；
  • 创建表完后，添加非空约束；
  • 删除非空约束；
  1. 唯一约束：unique，值不能重复
  • 删除唯一约束；
alter table 表名 drop index 列名;
  1. 外键约束：foreign key
  • 创建表时，可以添加外键；
    语法：
create  table 表名(
...
外键列
constraint 外键名称 foreign key （外键名称） references 主表名称（主表列名称） 			);
  • 删除外键
alter table   表名  drop foreign key   外键名称  ；
  • 创建表之后添加外键
alter table   表名  add constraint 外键名称 foreign key （外键名称） references 主表名称（主表列名称）；

DML（数据操纵语言：操作数据（增删改））

  • 添加数据
    语法：
insert into 表名（列名1，列名2...列名n） values（值1，值2...值n）;

注意事项：
1. 列名和值要一一对应；
2. 如果表名后，不定义列名，则默认给所有列添加值
3. 除了数字类型，其他类型需要使用引号引起来

  • 删除数据
    语法:
delete from 表名 where 条件;

注意：
1. 如果不加条件，则删除表中所有记录
2. truncate table 表名； -----效率更高，删除表，再创建一个一模一样的空表

  • 修改数据
    语法：
update 表名 set 列名1=值1，列名2=值2... where 条件;

注意事项：
如果不加任何条件，则会将表中所有记录修改；

DQL（查询表中的数据记录）（重点）

--复杂查询语法：
	select 
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having  
		分组之后的条件
	order by
		排序
	limit
		分页限定

查询：

  1. 计算列
--查询所有
select * from 表名;
	如：select * from banji;

--as可以起别名		
select banji_id as "班级号",banji_name as "班级名" from banji;
select banji_id,banji_name,banji_num*12 as "年薪",banji_name as "班级名" from banji;

--输出的行数是banji表的行数，每行只有一个字段，值是4		
select 4 from banji;
  1. distinct【不允许重复的】消除重复（多个NULL也可以过滤掉）
--如：
select jiaoshi_id from banji_jiaoshi_mapping;
select distinct jiaoshi_id from banji_jiaoshi_mapping;
	--若查询两列，则会把组合过滤，distinct放在两列之前，不能放在中间，要不然会逻辑错误
  1. between
	--如：select * from banji 
	where banji_id>=1 and banji_id <=2;
--等价于
select * from banji
	where banji_id between 1 and 2;

select * from banji
	where banji_id <=1 or banji_id>2;
--等价于
select * from banji
	where banji_id not between 1 and 2;
  1. in[属于若干个孤立的值]
	如： select * from banji
	where banji_id in (2,4);
--等价于
select * from banji
	where banji_id=2 or banji_id=4;
	
	select * from banji
	where banji_id not in (2,4);
--等价于
select * from banji
	where banji_id!=2 and banji_id!=4;
  1. top
	select top 2 * from banji;

select top 100 percent * from banji; --百分比

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num desc;  --降序

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num;   --不写默认升序
  1. null[空值]
select * from banji;
select * from banji where banji_num <> null;  --error
select * from banji where banji_num !=null;  --error
select * from banji where banji_num =null;   --error
--null不能参与<>   !=   =运算
--null可以参与is    not is
select * from banji where banji_num  is null;
select * from banji where banji_num  is not null;
  1. order by【以某个字段排序】默认升序asc
select * from banji order by banji_num;--默认升序排列
select * from banji order by banji_num desc;--降序

--出现多列是按一列排完排另一列，不是按组合排序
select * from banji order by banji_num,banji_id ;
select * from banji order by banji_id,banji_num ;
--asc是升序的意思，默认可以不写，desc是降序
select * from banji order by banji_num desc,banji_id ;--desc只对banji_num产生影响
select * from banji order by banji_num ,banji_id  desc;--desc对banji_id产生影响

强烈建议为每一个字段都制定排序的标准
  1. 模糊查询【匹配的条件必须用单引号括起来】
	/*格式：select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件
	通配符：
		% :表示任意0个或多个字符
	    _:[下划线] 任意的单个字符
	    [a-f]: a到f的任意单个字符 只能是其中的任意一个字符  
	    [a,f] : a或f 
	    [^a-c]:不是a 也不是b也不是c的任意字符*/

select * 
	from banji 
	where banji_name like '二%'

select * 
	from banji 
	where banji_name like '二_'

select * 
	from banji 
	where banji_name like '[^a-c]_'

select * 
	from banji 
	where banji_name like '%\二%' escape '\' --表示\后面的字符就是普通的字符
  1. 聚合函数【多行记录返回一个值，通常用于统计分组的信息】

  1. 函数分类：单行函数：每行返回一个值

  2. 多行函数：多行返回一个值

  3. 聚合函数是多行函数

  4. 单行函数和多行函数一起用出错

    如：聚合函数的分类：
    1.max()

    1. min()
    2. avg():平均值
    3. count():求个数 count(*)返回表中所有记录的个数 count(字段名)：返回字段值非空的记录个数，重复的记录有效

可能遇到的问题： select max(banji_num),min(banji_num),count(*) from banji;

  1. group by
use student;
select * from banji
alter table  banji add sal int;
update banji set sal=4000;
update banji set sal=5000 where banji_id in (2,4,6);
select dept,avg(sal)as "平均工资" from banji group by dept;

--使用了group by之后只能显示分组后的整体信息，不能出现组内详细信息
  1. having【对分组后的信息过滤】
  • having使用时通常先使用group by
  • 如果没使用group by使用having意味着把所有记录当做一组来过滤
  • having子句出现的字段必须是分组之后组的整体信息，不是详细信息
  • having中不能使用字段的别名
  • having和where区别： 相同：都是对数据过滤，只保留有效的数据 不同：where是对原始的记录的过滤，having是对分组之后的记录过滤
--如：
select dept,avg(sal)as "平均工资" from banji group by dept having avg(sal)>3000 order by dept desc;

--参数的位置不可以变
  1. 连接查询
    定义：将两个或多个表以一定条件连接起来从中检索出满足条件的数据

分类：

  • 内连接
  1. select … from A,B 的用法
select * from jiaoshi;
select * from banji;
update banji set banji_name='六班' where banji_id=6;

select * from banji,jiaoshi;-- 产生4*6 行临时表，行数相乘，列数相加，笛卡尔积
  1. select … from A,B where … 的用法
select * from banji,jiaoshi where banji_id=1;--产生4*1行
--对	select  ... from A,B where ... 产生的笛卡尔积用where中的条件进行过滤
  1. select … from A join B on …的用法
select banji.sal from banji join jiaoshi on banji_id in(1,2);--join是连接，on是连接条件，必须是组合
  1. select … from A,B where …与
    select … from A join B on …的比较
  • 分别是SQL92和99标准；输出结果是一样的，推荐SQL99标准
    • SQL99更容易理解
    • SQL99标准中，on和where可以做不同的分工 on指定连接条件，where可以对连接后过滤
  1. select、 from、 where、 join、 on、 group、 order、 top 、having的混合使用
--查询的顺序：
select top...
	from A
	join B
	on ...
	join C
	on ...
	where ...
	group by ...
	having ...
	order by ...
  • 外连接【返回满足连接条件的记录和不满足条件的部分记录】
  • 内连接默认inner join 分类：
  • 左外连接：返回满足连接条件的记录和左表不满足条件的部分记录 left join 匹配则输出，不匹配就用第几行做匹配输出第几行，其中左边为左表，右边为null
    实际意义：返回一个事物的相关信息，如果该事物没相关信息，则输出null；如：仓库订单
  • 右外连接：返回满足连接条件的记录和右表不满足条件的部分记录 right join 匹配则输出，不匹配就用第几行做匹配输出第几行，其中右边为右表，左边为null
  • 完全连接
    • full join
      左外连接+右外连接-所有正常记录=完全连接
      交叉连接
    • cross join结果是笛卡尔积，等价于什么也不写
  • 自连接
    一张表自己和自己连接起来查询数据
    起两个不同的别名
    子查询返回的值只有一个
  1. 联合 ：union，两个select中使用

定义：表和表之间的数据以纵向的方式连接在一起 以前的所有的连接是以横向的方式连接；
注意：要联合成功，列数必须是相等的，类型是兼容的

  1. 分页：
--如：
select top 4 * from banji
	where banji_num not in (select top 6 banji_id from banji order by banji_num desc)
	order by banji_num desc;
  1. 公式：开始的索引=（当前的页码-1）*每页显示的条数
  2. 分页操作是一个“方言”
  1. 嵌套查询

视图

为什么需要视图： 简化查询，避免了代码的冗余，避免书写大量重复的SQL语句.

什么是视图？视图逻辑上当做一张虚拟表看待，

视图的格式：

create view 视图的名字
as
	--select前面不能添加begin
	select语句
	--select后面不能添加end

视图的优点：简化查询、增加数据的保密性
视图的缺点：增加了数据库维护的成本 视图只能简化查询，但并不能加快查询的速度

注意问题：1. 创建视图的select语句必须为所有的计算列指定别名
2. 视图不是物理表，是虚拟表
3. 不建议通过视图更新视图所依附的原始表的数据

事务

事务要么全部执行成功，要么全部执行失败

为什么需要事务？ 事务主要用来保证数据的合理性和并发处理的能力； 如：银行转账、卖票

事务研究的问题

  1. 避免数据处于不合理的中间状态
  2. 怎样避免多用户同时访问是数据呈现给用户的数据是合理的 事务和线程的关系： 事务也是通过锁来解决很多问题的 线程同步就是通过锁来解决的 synchronized

事务和第三方插件的关系 直接使用事务库技术难度大，所以大多使用第三方插件库来实现
第三方插件要想完成预期的功能，一般必须借助数据库的事务机制

T-SQL使用下列语句管理事务：
开始事务：begin transaction
提交事务：commit transaction
回滚（撤销）事务：rollback transaction

TL-SQL：速度快，安全性好；难移植（也可以使用Java在编译器去实现）

一旦事务提交或者回滚，则事务结束

事务三种运行模式：
1. 自动提交事务
2. 显式事务 每个事务均以begin transaction开始，以commit或rollback显式结束
3. 隐形事务 在前一个事务完成时新事务隐式启动，但每个事务仍以commit或rollback语句结束

事务的四大属性：
1. 事务是一个完整的操作，事务的各步操作不可再分
2. 当事务完成时，数据必须处于一致状态
3. 当前事务与其他未完成的事务是隔离的
4. 事务完成后，事务日志能够保持事务的永久性

本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客


转载：https://blog.csdn.net/qq_40996576/article/details/105951524
查看评论
* 以上用户言论只代表其个人观点，不代表本网站的观点或立场