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

目录

• 数据库介绍
• SQL
  • SQL定义
  • SQL的分类：
    • DDL（数据定义语言：操作数据库和表）
    • DML（数据操纵语言：操作数据（增删改））
    • DQL（查询表中的数据记录）（重点）
• 视图
• 事务

数据库介绍

• 数据库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 数据库名称;

2. 操作表

• 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 新的表名;

2. 修改表的字符集

--首先学会查看 
	show create table 表名;
alter table 表名 character set 字符集名称;

3. 添加一列

alter table 表名  add 列名 数据类型; 

4. 修改列的名称、类型

alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;

5. 删除列

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；

2. 非空约束：not null

• 创建表时添加约束；
• 创建表完后，添加非空约束；
• 删除非空约束；

3. 唯一约束：unique，值不能重复

• 删除唯一约束；

alter table 表名 drop index 列名;

4. 外键约束：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;         
		

2. distinct【不允许重复的】消除重复（多个NULL也可以过滤掉）

--如：
select jiaoshi_id from banji_jiaoshi_mapping;
select distinct jiaoshi_id from banji_jiaoshi_mapping;
	--若查询两列，则会把组合过滤，distinct放在两列之前，不能放在中间，要不然会逻辑错误 

3. 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;

4. 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;

5. 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;   --不写默认升序

6. 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;

7. 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产生影响

强烈建议为每一个字段都制定排序的标准

8. 模糊查询【匹配的条件必须用单引号括起来】

	/*格式：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 '\' --表示\后面的字符就是普通的字符

9. 聚合函数【多行记录返回一个值，通常用于统计分组的信息】

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

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

3. 聚合函数是多行函数

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

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

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

10. 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之后只能显示分组后的整体信息，不能出现组内详细信息

11. 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;

--参数的位置不可以变

12. 连接查询
    定义：将两个或多个表以一定条件连接起来从中检索出满足条件的数据

分类：

• 内连接

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 行临时表，行数相乘，列数相加，笛卡尔积

2. select ... from A,B where ... 的用法

select * from banji,jiaoshi where banji_id=1;--产生4*1行
--对	select  ... from A,B where ... 产生的笛卡尔积用where中的条件进行过滤

3. select ... from A join B on ...的用法

select banji.sal from banji join jiaoshi on banji_id in(1,2);--join是连接，on是连接条件，必须是组合

4. select ... from A,B where ...与
   select ... from A join B on ...的比较

• 分别是SQL92和99标准；输出结果是一样的，推荐SQL99标准
  • SQL99更容易理解
  • SQL99标准中，on和where可以做不同的分工 on指定连接条件，where可以对连接后过滤

5. 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结果是笛卡尔积，等价于什么也不写
• 自连接
  一张表自己和自己连接起来查询数据
  起两个不同的别名
  子查询返回的值只有一个

13. 联合 ：union，两个select中使用

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

14. 分页：

--如：
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. 分页操作是一个“方言”

15. 嵌套查询

视图

为什么需要视图： 简化查询，避免了代码的冗余，避免书写大量重复的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. 事务完成后，事务日志能够保持事务的永久性