数据库知识点总结

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

数据库知识点总结

上一篇:c++基础gcc和gdb朝花夕拾


下一篇:百度地图代码调用到网页后显示灰色