数据库介绍

• 数据库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. 事务完成后，事务日志能够保持事务的永久性