数据库基础

首先要明白什么是数据库，顾名思义就是存放数据的仓库。

数据库管理系统：设计开发出的用于管理数据和数据库的软件。

数据库系统：包含数据库，数据库管理系统，硬件软件支撑，DBA等。

 

在当前最流行的数据库：

关系型数据库MySql，SqlServer，Oracle，PostGreeSQL等，非关系型数据库MongoDB，缓存数据库Redis，Memcache等。

 

------------------------------------------------------------------------------------------------------------------------------

 

基本元素：

1.Catalog：俗称数据库，表空间，不同类型、project的数据库应该存放在不同的表空间下，实现个性化设置管，避免命名冲突，实现数据隔离。

2.Table：表，是用来存放具体的数据资源记录的集合空间。

3.Column：列，亦成为字段，属性，是表中用来规范和定义每个数据意义的最小单位。

4.Row：行，称记录，是一条完整的数据信息。

 

5.主键：PrimaryKey，用来标识识别一条记录的唯一性。可以设定逻辑主键或者业务主键：一般为了程序上的方便，设置随机生成的64位UUID作为逻辑主键，业务主键会暴露数据信息，重复数据也会造成主键冲突并且业务数据的删除修改也会改变数据主键、外键信息，造成大量数据紊乱，不推荐。

逻辑主键一般可以选用自增长类型或者GUID（UUID）实现。

自增长ID：方便快捷，无需花精力在这个上面，可读性强，但是效率低下，数据表间的导入导出冲突。

GUID：global Unique Identifier：全球唯一标识符。是一种算法，囊括了网卡MC地址，纳秒级时间，PC芯片ID等拼接。效率高，数据表间的导入导出方便，但是可读性比较差，如果不经过处理占用空间大（64位）

 

6.外键，ForeignKey，关联数据表中的数据存在要依赖主表数据，一旦主表数据变更，必须事先处理外键表，因此一般不推荐在数据库设计中出现外键，程序上设定逻辑外键即可。

 

字段类型

不同的数据库，可能会有细小的关键字差别，但大体上都有数据类型：int(number)，date，char，varchar等基本的数据类型结构。

create表时候都要根据具体的业务情景，数据规模和使用频次，合理将字段设计成为合理的数据类型，同时预先指定数据长度，节省数据表空间。

对于主键设置为自增长或者GUID时候，设置默认值为自增长或者newid()即可。

 

------------------------------------------------------------------------------------------------------------------------------

 

SQL：

sql全称：Structured Query Language，结构化查询语言。它是W3C制定的所有数据库厂商都要遵守的数据库查询语言规范，不同的数据库都在这个规范上实现细小的差异化。

1.sql所有关键字大小写不敏感，不区分字母大小写；

2.所有字符都使用 '' 单引号。

3.sql语言分为四大类，DDL / DML两大类数据定义操作语言，TCL事务控制语言，DCL权限控制语言，

3.1.DDL ：Data Define Language, statements are used in defining the database structure or schema.

即数据定义语言，用于定义数据库的三级结构，包括外模式，概念模式，内模式和视图。数据的完整性和安全控制亦然。

CREATE 创建

DROP 删除

ALTER 更改

 

TRUNCATE 清空

COMMENT 备注

RENAME 改名

以上6个关键字都是作用在数据库数据库表空间、表、索引、视图等“上层”结构，处理创建或者修改撤销等，用于定义表的基础架构，无序commit。

 

2.DML: Data Manipulate Language, statements are used in managing data within table propertity.

数据操作语言，用于处理表中的数据记录。

SELECT 查

INSERT 增
UPDATE 改
DELETE 删

 

MERGE 数据同步转换

CALL 存储过程

EXPLAIN PLAN 执行计划

LOCK TABLE 锁表

前面4中就是我们所说的增删查改数据的四大基本功能，也是程序设计语言，数据库DBA使用最频繁的四类，作为SQL的核心必须了解其基本的处理，然后在此基础上深入学习高级DML语言如子查询，连表查询等。

 

3.DCL：Data Control Language, statements are used to grant some authentication

数据控制语言，用于授权，取消授权

GRANT 授权

REVOKE 回收权限

包括对视图和基本表的授权

 

4.TCL：Transaction Control Language, statements are used for transaction control

事务控制语言

SAVEPOINT 存储过程时候使用节点

ROLLBACK 回滚事务

COMMIT 提交

SET TRANSACTION 设置事务处理级别 read only, read write

 

------------------------------------------------------------------------------------------------------------------------------

 

在写以下增删查改specification前，我们设置数据库CW下有表Employee

数据表Employee字段有id, name, age, sex字段

 

-----------------------------------------------

数据插入:

1.insert语句可以省略表名后的列名，这样必须使用和create时列的位置一致，并且个数一样，

 

insert into Employee(name, age, sex) values('cw', 12, 'man');

insert into Employee values('cw', 12, 'man');

 

如果只需要插入一条记录name，而age,sex保密：

insert into Employee(name) values('gl');

对主键id设置默认值newid()后，这里id就不需要在关心，数据库在每次insert时候自动按规则填充。

 

-----------------------------------------------

数据更新

1.更新所有记录

update Employee set age  = 18; 

update Employee set age = 18, name = 'cw_gl'; //更新所有记录的age＝19，name＝cw_gl

 

2.更新指定记录：

update Employee set age = 18 where name = 'cw'; //更新name为‘cw’的记录age属性为18

 

update Employee set age = 18 where name = 'gl' or age > 18; // 更新 满足name为‘gl’的记录 或者 age大于18的记录（满足任意一个的） age属性为18

 

where关键值，在sql中是作为过滤条件，对Employee表中所有满足条件的记录进行过滤筛选出符合条件者进行更新。可以使用的逻辑运算符：or, and, not, <, >, <=, >=, !=, <>.

 

-----------------------------------------------

数据删除

delete语句用于删除表中的记录，与drop table删除表不同。

1.删除数据表中的数据：

delete from Employee; // 删除所有的记录

 

2.删除一部分数据：

delete from Employee where name='cw1'; //删除所有记录中 name为cw1的记录

 

-----------------------------------------------

数据查询：

数据查询使用select

1.查询所有表中的记录

select id, name, age, sex from Employee; //查询所有记录，并将需要查看的字段属性值列出来

select * from Employee; //*表示表中定义的所有属性，如果需要查询的字段太多并且写起来比较麻烦，使用*

 

在以上*作为查询所有字段，数据库查询执行前，将*翻译为Employee表中的所有字段，也就是事先一步查询数据字段中该表的定义，然后就变成了第一条记录的形式

 

2.只查询需要看的列（字段属性）只看name：

select name from Employee;

 

3.为显示的列取其他的名字:

select name as "名字" from Employee; //将查询结果中，name列显示别名为“名字”

 

在SQL中，如果没有取别名，会默认将将原来数据库中定义的字段名，作为查询结果的显示名，如以上2默认显示的就是name，而3中定义了该name显示别名“名字”

 

-----------------------------------------------

特殊查询：

select 1+1 from dual; //在任何数据库中，都有一张预定义的默认数据表dual，我们可以看成是一个“默认的空表”，是用户创建数据库过程的同时就已经创建好的数据表之一，她是该schema表空间下已经预留的表我们可以看成是存放特殊查询结果的“容器”，该语句就是使用dual表，运算1+1这个数学表达式，同时列名显示仍然为1+1

 

select 1+1, id from Employee; //这里增加了一盒显示的字段，它是在所有查询结果显示之前，额外加在查询结果集中，如果返回的id有4调，则每一条记录都加上了一个列名为1+1， 值为常量2

 

select 1 from Employee; 一样的道理，这里数据表中有多少条记录，就会附加上多少额外的列名为1 值为1的显示结果，并且只显示了列名为1的列，其他的原有数据表Employee中的id，name，age，sex等都不显示出来

 

-----------------------------------------------

聚合函数：

1.max函数

select max(age) as max_age from Employee; // 查询数据表中，所有记录中age非空的记录中，age值最大的，如果结果不止一条，就显示所有符合条件的记录，并且显示的别名max_age，如果没有定义别名，就显示为max(age)

 

2.min函数

select min(age) from Employee where sex='女' //显示所有的sex＝‘nv’的记录中，age最小的记录

 

3.avg函数

select avg(age) as avg_age from Employee; // 显示数据表中所有记录的age的数学平均值，显示的别名avg_age，如果没有定义别名就显示成avg(age)

 

4.sum函数

select sum(age) from Employee; // 查处所有的记录中age的数学总和

 

在以上四个聚合函数中，使用的列对象的类型是数字类型的时候才合适使用。其他的情况不能适用，否则异常。

 

5.count函数

select count(*) from Employee; //查询记录的总条数，例如现在数据表中有4条记录，则返回4，这里又使用了*代表了需要count数量的基准，这里依然是数据库引擎将*通过数据字典翻译成id，等同于：

select count(id) from Employee; 

 

其实5中，只要理解了select 1 和select count(1) 的作用：

select 1 from Employee from name='cw'; //查询所有name='cw'的记录，如果有则返回字段1，并且显示的查询结果列名也是1，如果没有，则返回空

 

select count(1) from Employee; //这里也是一样的道理，执行步骤：1.将所有的Employee的记录查出来，并且通过上面介绍的 select 1 from Employee原理，count所有为1的记录，这样其实就是count(id)了，因此这条语句其实和select count(*) from Employee, select count(id) from Employee等是恒等的，但是数据库执行的时候的效率依次是：select count(1)最高，select(id)次之，select(*)最差（能理解吗？）因此在高效的sql优化中才需要特殊的注意。

 

-----------------------------------------------

数据排序order by：

order by 字句（sql关键字），这是一个用来排序的数据库sql字句，它用在需要对查询出来的最终结果进行显示上的列（一个或者多个列）按照某种规则（升序，降序）排序时，其中升序ASC(ascend 意思：上升)，降序DESC(意思：descend)，默认升序ASC。

在sql中几乎所有的类型都是可以排序的：数字类型直接按照数学大小排序规则，字符类型则按照首字母顺序进行排序（每个字符都有唯一对应的ASCII码，其实是按照ASCII码大小，相同的字母大小写之间ASCII(a) = ASCII(A) + 32，也就是相差32），如果按照升序，不同字母A-Za-z顺序排，如果首字母一样，依次第二个字母...循环类推。

order by字句是针对的查询出来的结果进行显示上的排序，必须放在整个sql语句之后，执行顺序也是所有语句最后。

 

1.按照age的升序查询出数据表Employee结果：

select * from Employee order by age asc; 

等同于：select * from Employee order by age; //因为默认的就是升序

 

2.按照age的升序，name的降序排列：

select * from Employee order by age asc, name desc; //首先按照age升序排列，如果age想等时候就按照name的降序排列。

 

3.查询所有sex='女'，按照age的升序，name的降序排列

select * from Employee where sex='女' order by age asc, name desc;

 

-----------------------------------------------

模糊查询：

在sql中，如果某个字段比如name只依稀记得某人姓而不知道名称，或者要查询name所有姓曹的记录，这个时候就需要使用模糊查询， 模糊查询使用sql关键字like，并使用通配符：单字母通配符_（下划线），一个活着一个以上通配符% 。

 

1.单个字母的模糊查询:

select * from Employee where name like 'c_'; //查询所有name中只有两个字符并且第一个字符为c的记录

 

2.多个字符模糊查询：

select from Employee where name like '%c%'; //查询所有name中含有字符c的记录

 

-----------------------------------------------

空置处理：

1.在所有的数据库字段中，如果没有强制在数据库定义语句中要求非空(not null)约束，那么表示这个字段可以为空，在数据库中显示为null，表示数据库不知道，因此数据库在数据库中，如果要进行特殊的控制要求，不能使用= ,!=， 而要使用特殊的 is null, is not null 语句。

 

select * from Employee where age is null; // 查询所有age为空的记录

 

-----------------------------------------------

多值匹配：

1.如果数据库中需要同时查询一个列，只要满足一定条件都要，用到多值匹配，就需要or，有时候对同一字段的多次or可以用 in

select * from Employee where age = 12 or age =16 or age = 18 or name='cw'; //找所有的age在12 16 18的记录

等同于：select * from Employee where age in (12, 16, 18) or name ='cw';

 

2.如果需要同时满足某些条件需要and，有时候对于一个字段的多次and可以使用between A  and B ：

select * from Employee where age <20 and age > 10 and sex='女'; // sex＝‘女’的age大于10并且小于20

select * from Employee where age between 20 and 10 and sex='女';

 

-----------------------------------------------

数据分组group by：

在sql中如果需要将某字段值相同的分为一组，或者这些组分别有多少条记录时候就需要用到分组语句group by，它是将所有的记录按照要求进行分门别类，如果在这些分门别类上加上特殊的要求如人数大于2等（聚合函数）这时候就需要having过滤。

没有出现在group by字句的字段，除了聚合函数和常量之外（select 1），其他的严禁出现在select后。

这里的having是能且只能联合在group by之后的过滤关键字，它是在group by 的基础上进行的再次过滤，与where的全局过滤不同，它是在where全局过滤之后进行并且where不能处理的聚合函数的过滤，语法顺序、执行顺序在where之后order by之前。

 

1.查询不同的age段的人数：

select 1, age, count(1) group by age; //这里不能select id, name, sex，否则异常

如要想加上id，在group by 后跟上id：

select 1, age, count(1), id group by age, id;

 

---------------------

having：

having关键字是作用于group by的充分条件，只有出现group by语句才会有having，它是对where不能处理的聚合函数的特殊要求的二次过滤。这时候必须使用having分组过滤

 

1.显示每个段上的age数量超过2人的记录

select * from Employee group by age having count(1) > 2;

 

-----------------------------------------------

限制结果集：

在oracle中，对字段没有要求但是只需要特定的数据量的时候，就需要使用到rownum来限制，比如只要首条

select * from Employee where rownum<2

 

-----------------------------------------------

去重：

distinct关键字对结果集的某个特定字段去重处理。

select distinct(age) from Employee; //age去重

 

 

 

 

 

 

 

 

 

 

 

 

.