数据库（DB，datebase）

一、数据库中的常用术语

              1、DB（数据库，datebase）用来存储数据的仓库，能够更加好的组织和管理数据，将数据持久化地存储下来

              2、数据库管理系统（DBMS datebase management system）用来对数据库进行管理的软件 Navicat pl /sql

              3、DBA数据库管理员 (datebase management  administrator) 专门从事数据库管理和研发的人员统称

二、常用的关系型数据库

               MySQL 免费，安装简单，本身效率不低，但是如果需要分库分表则需要使用三方组件完成

               Oracle(神谕，甲骨文)        收费，安装复杂，配置复杂，本身就支持大数据量和分布式部署

               SQL Server(一般用在.NET开发中比较多)

               DB2

               PostgreSQL

三、表的概念

                客户端访问数据库的过程：

                客户端可以是数据库管理工具也可以是访问数据库的工具

                客户端其实访问的是安装在计算机当中的数据库工具

                每个数据库中用来存储数据的基本单元叫做表

 

       表示一个二维的结构，表是有行有列的：

              行:用来表示数据库中的一条数据，也可以叫做记录

              列：纵列数据（对于某项属性的所有的数据），也叫作字段

E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型

四、通过数据库中的三范式来规划数据库的设计

           （1）第一范式（1NF）

                 一个数据表中，所有的列应该是不可分割的基本数据项，某一个列不应该包含多个值，即实体中的某个属性不能有多            个值或者不能够有重复的属性。不满足第一范式的数据库不是关系型数据库

            （2）第二范式

                基于第一范式建立，数据表中的每一行或者每个实例都可以被唯一区分，且其它的列必须都和主键关联，否则就要分               出新的实体，一般每个表添加一列字段，一般每个表添加一列字段，用于保存对对象的唯一标识

            （3）第三范式

                基于第二范式建立起来，要求一个数据库表中不包含已在其它表中已经包含的非主键字信息，简而言之，第三范式就               是属性不依赖于其它的非主属性。用于消除数据库中的数据冗余。

五、SQL语句     

                关系型数据库在访问过程中，提供一套规范的语言，叫做SQL

               Structtured Query Language(SQL)

                通过SQL可以对几乎所有的关系型数据库进行统一操作

         SQL语句的分类：

              

              SQL可以将不同数据库之间统一起来，即SQL语句可以访问几乎所有的关系型数据库

             

六、常用操作

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名；

2.显示所有的数据库

SHOW DATABASES;

3.显示数据库创建语句 

 SHOW CREATE DATABASE 数据库名；

4.数据库删除语句

DROP DATABASE [IF EXISTS] 数据库名；

5.数据库的修改

ALTER DATABASE 数据库名 CHARACTER SET 字符集编码；           

6.数据库备份和恢复

7.切换数据库

use 数据库名

七、数据库的完整性

 实体完整性

    实体完整性指的是表中行的完整性，用于保证操作的数据记录非空、唯一。即实体完整性要求每个关系表有且仅有一个主键，每一个主键必须唯一，而且不允许为空或者重复值。

 域完整性

   是指数据库表中的列必须满足某种特定的数据类型或者约束。其中约束又包括取值范围，精度等规定。表中的CHECK、FORELGN KEY约束、NOT NULL 定义都属于域完整性的范畴

 用户定义完整性

   是对数据表中字段属性的约束，用户定义完整性规则也叫作域完整性规则。包括字段的值域，字段的类型和字段的有效规则等约束。

 参照完整性（引用完整性）

  属于表间规则。 对于永久关联的相关表，对表进行增删改查，如果值改其一，那么就会影响数据的完整性。

八、约束

 1、唯一约束  

   1）添加唯一约束通过ALTER语句给定

        ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名1,列名2,……,列名N)

        ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_UNIQUE UNIQUE(ID);

        ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_UNIQUE UNIQUE(ID,NAME);

      建表时指定的第一种方式

                       create table EMP(

                              ID CHAR(11) unique,

                               ……

                         )character set UTF8 collate utf8_general_ci  engine INNODB;

 

      建表时指定的第二种方式

                       create table EMP(

                             ID CHAR(11),

                              ……

                        UNIQUE KEY  pk_id_unique (id)  

                        )character set UTF8 collate utf8_general_ci  engine INNODB;

 

      2）删除唯一约束

                  语法：ALTER TABLE 表名 DROP INDEX 约束名;

                  alter table emp drop index pk_id_unique;

 2、非空约束

         指定单列值必须填写不能为null

1. 添加非空约束

          1）通过alter语句添加

              语法：ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL

              ALTER TABLE emp modify id int NOT NULL;

 

          2） 建表时指定

              create table EMP(

              ID CHAR(11) not null,

                ……

              )character set UTF8 collate utf8_general_ci  engine INNODB;

 

    2. 删除非空约束

             语法：ALTER TABLE 表名 MODIFY 列名 列类型 NULL

             ALTER TABLE emp modify id int  NULL;

  

 3、默认值约束

         指定列当没有写入时也能以默认值呈现（更新时更新成null，不会变成默认值）

             1）添加默认约束

                1.建表时给定默认值

                     creat table EMP(

                                  ID char(11) not null,

                                   name varchar(20) default  " 默认值 "，

                                   ....   

                                       );

                 2.通过alter语句指定

                       语法： alter table 表名  alter column 列名 set default 默认值；

                       alter table EMP alter column name set default 'defaultName';

 

            2）删除默认约束

                      语法：

                           alter table EMP alter column name set default null;

 4、主键约束

 5、外键约束

     语法：

              ALTER TABLE 表名 ADD CONSTRAINT 外键约束名  FOREIGN KEY(外键名)  references 主表名 (主键名)

              ALTER TABLE 表名 DROP  FOREIGN KEY  外键约束名

九、数据库CRED--update语句

update语句用来在修改数据库中的数据

语法：

update 表名 set 列名 1=值，列名2=值.....，列名n=值 where 条件

update emp set ename='Tom';

update emp set ename='Jack',job='Java';

update语句后面如果没有加条件则更新表中所有的记录。

如果需要指定哪些数据进行更新，则需要通过where关键字来指定需要更新的记录所满足的条件。

where后面跟的是一个条件表达式，返回是或者否

如果where后面有多个条件，可以使用and或or将条件连接起来

update emp set comm=200 where sal>1000 and sal<=2000;

update emp set comm=500 where deptno!=1

<>等价于！=

update emp set comm where deptno<>10

update emp set comm where sal between 1000 and 2000;

update emp set comm=100 where sal not between 1000 and 2000;

in 表示值在某个列表中

更新部门编号在20和30中的员工信息

update emp set comm =500 where deptno=20 or deptno=30;

update emp set comm=2500 where deptno in(20,30)

update emp set comm=5000 where deptno not in(20,30)

判断字段是否为null，需要使用is null

update emp set comm = 10000 where mgr is null

update emp set comm = 10000 where mgr is  not null

like 用来模糊匹配

_：用来匹配任意的某个字符

%:用来匹配连续的任意多个字符

名字中第二个字符是A的人  xAx xAxx xAxxx xAxx……  

update emp set comm=200 where ename like '_A%';

名字中包含A这个字符的人  A…… xA…… xxA…… xx……xxA

update emp set comm=600 where ename like '%A%';

update emp set comm=3000 where ename not like '%A%';

十、CRED--delete语句

删除数据库中的记录

语法：select from 表名 where 条件

delete from emp where  条件；

where 条件的写法同update语句

delete from emp where empno=7369;

删除语句如果不加条件则删除所有的记录

delete from 表名；

TRUNCATE 语法： TRUNCATE TABLE 表名

truncate table emp;

TRUNCATE删除和delete删除的区别？

DELETE可以有条件删除，TRUNCATE只能将表数据全部删除

DELETE是DML语句。可以回滚，TRUNCATE是DDL语句，立即生效，无法回退

如果是删除全部表记录，且数据量较大，DELETE语句效率比TRUNCATE语句低，操作时主键会重置，相当于先删除表再建立表。

十一、数据库CRUD-select语句

select 语句用于从数据库中查询数据

select语句是DQL语句

insert、delete、update是DML语句

查询的语法： 

       

 1.select * from 表名 where 条件;

 *：表示查询所有列

 select * from emp where empno=7369;

 

 2.select 列名1,列名2,……,列名N from 表名 where 条件;

 指定哪些列需要被查询出来

 select empno,ename,job from emp;

 

 3.select 列名1 as 别名,列名2 别名,……,列名N from 表名 where 条件;

 查询出来的列可以指定别名，表也可以指定别名（多表查询中会用到）

 select empno as 员工编号,ename 员工姓名,job job from emp e;

注意点：

    （1）查询出来的页可以用表达式计算

          -- 对所有员工加薪1000

          select empno,ename,sal,sal+1000 aftersal from emp;

    （2）where条件中可以使用函数或表达式    

           在条件中使用了函数

           select empno,ename from emp where LOWER(ename)='smith';

    （3）where后关于时间区间的范围

           >= <=

           between and

           查询80年开始到81年1月的所有入职人员的信息

           select * from emp where hiredate between '1980-01-01' and '1981-01-31';

去重查询：

      查询DISTINCT后面指定列的内容都重复的记录，只显示其中一条

      查询公司中所有的职位名称——

              select distinct job from emp;

排序查询

     根据数据表中的某个列或者某几个列进行排序

     语法：

     select  列1,列2,……,列N from 表名 where 表达式 order by 列名1 排序规则，列名2 排序规则；

排序规则：

      ASC用来指定升序排列，不写默认升序

      DESC用来指定将序排列，降序一定要写   

      select * from emp order by sal asc;

      select * from emp order by sal;

      select * from emp order by sal desc;

      null视作最小，升序时在最前面，降序排列时在最后

      select *from emp order by mgr desc;

 多个列参与排序的情况：

       -- 先根据mgr升序，再根据sal降序

       -- 当第一个列值相同时，再根据后面的列进行排序

       select * from emp where job='MANAGER' OR job='CLERK' order by mgr,sal desc;

十二、常用函数

  

    （1）数学函数

               ABS (number2 )求绝对值

              select abs(-1),abs(0),abs(1);

              CEILING (number2 )向上取整

              select CEILING(4.1),CEILING(0),CEILING(-3.9);

              FLOOR (number2 )向下取整

              select floor(8.9),floor(0),floor(-3.1);

              ROUND(n,m)用于四舍五入

              select round(3.567,2),round(3.563,2);

              FORMAT (number,decimal_places )保留小数位数

              select format(3.15,1),format(3.14,1);

              POW(X,Y)乘方运算

              select pow(3,2);

             SQRT求算数平方根

             select sqrt(4);

             MOD(M,N)返回M除以N以后的余数，N为0直接返回NULL

             select mod(5,3),mod(5,0);

             LEAST (number , number2  [,..])/GREATEST(number , number2  [,..])求最小/最大值

             select least(3,4,1,7,0,-3,9),GREATEST(3,4,1,7,0,-3,9);

             RAND([seed])随机函数

             select rand();

 （2）字符串函数

           CHARSET(str)返回字串字符集

           select CHARSET("哈哈");

           在SQL语句中，函数不仅能够单独使用，也可以和更新中的列，查询中的列以及where条件等一起使用

           select CHARSET(ename) from emp;  

           CONCAT (string2  [,... ])连接字串

           select concat('a','b','c');

          查询职员表中的职员名字、职位、薪资,每条信息显示成一列名为OUT_PUT,并用逗号将名字、职位、薪资隔开（可提前看一下concat函数的使用）

          select concat(ename,',',job,',',sal) as OUT_PUT from emp;

          INSTR (string ,substring )

          返回substring在string中出现的位置,位置从1开始，没有返回0

          select instr('i can because i can','can');

          UCASE (string2 )转换成大写

          LCASE (string2 )转换成小写

          select UCASE('abc'),LCASE('ALLEN');

          LEFT (string2 ,length )、RIGHT(string2 ,length)

          从string2中的左/右边起取length个字符,length超出总长度就截取所有

          SELECT LEFT('abcdefg',3),RIGHT('abcdefg',3);

          LENGTH (string )string长度

          select ename,length(ename) from emp;

          REPLACE (str ,search_str ,replace_str )

          在str中用replace_str替换search_str

          select replace('abcaaabcdaafg','aa','*')

          STRCMP (string1 ,string2 )逐字符比较两字串大小

          select strcmp('abc','abd'),strcmp('abd','abc'),strcmp('abc','bcd'),strcmp('abc','abc');

           SUBSTRING (str , position  [,length ])

           从str的position开始,取length个字符，length不填或大于最大长度，则表示后面所有，如果position为负数，则表示从后面开始截取

            position的值从1算起

            select SUBSTR('abcdefg',1,3);

            LTRIM (string2 ) RTRIM (string2 )  

           select ltrim(' aaa aaa  '),rtrim(' aaa aaa  ');

           TRIM(c2 FROM c1)从c1的首尾去掉c2,

           select trim('aa' from 'abcdaefgaa');

           LPAD(char1,n,char2)/RPAD(char1,n,char2)

           补位函数，用于在字符char1的左(右)端用char2补足到n位，总长度不会超过n

           select lpad('abc',5,'*'),rpad('abc',5,'*'),lpad('abc',5,'!@#$');

（3）日期函数

-- 当前时间的获取

-- current_date()当前日期

-- now()当前时间

-- sysdate()当前时间

-- CURRENT_TIME() 当前时间（只有时间部分）

-- CURRENT_TIMESTAMP() 当前时间戳

select current_date(),now(),sysdate(),current_time(),CURRENT_TIMESTAMP();

-- DATE (datetime )

-- 返回datetime的日期部分

select date(now());

-- EXTRACT(unit FROM date)

-- 返回日期中指定分量的值

select EXTRACT(year FROM now()),EXTRACT(MONTH FROM now()),EXTRACT(DAY FROM now()),

EXTRACT(HOUR FROM now()),EXTRACT(MINUTE FROM now()),EXTRACT(SECOND FROM now());

-- 每个时间分量都有各自获取的函数

select YEAR(HIREDATE),MONTH(HIREDATE),DAY(HIREDATE),HOUR(HIREDATE),MINUTE(HIREDATE),SECOND(HIREDATE) from emp;

-- DATEDIFF (date1 ,date2 )

-- 两个日期差(结果是天)，参与运算的可以是date/datetime/timestamp

-- STR_TO_DATE(str,fmt)

-- 字符串转日期类型

-- '%Y-%m-%d %H:%i:%s'

select datediff(STR_TO_DATE('2019-03-04','%Y-%m-%d %H:%i:%s'),STR_TO_DATE("2019-01-02",'%Y-%m-%d %H:%i:%s'));

-- TIMEDIFF(date1,date2)

-- 两个时间差(多少小时多少分钟多少秒)，参与运算的可以是date/datetime/timestamp

select timediff(STR_TO_DATE('2019-03-04','%Y-%m-%d %H:%i:%s'),STR_TO_DATE("2019-01-02",'%Y-%m-%d %H:%i:%s'));

-- DATE_ADD (date2 , INTERVAL d_value d_type )

-- 在date2中加上日期或时间，参与运算的可以是date/datetime/timestamp

select date_add(now(),interval 2 year);

select DATE_SUB(now(),INTERVAL -2 year);

（4）其它函数

-- IF(expr1,expr2,expr3)

-- 如果expr1为True ,则返回 expr2 否则返回 expr3

select if(1=2,'a','b');

-- IFNULL(expr1,expr2)

-- 如果expr1不为空NULL,则返回expr1,否则返回expr2

select ifnull(null,'默认值');

select empno,ename,ifnull(mgr,'无直属领导') from emp;

-- NULLIF(expression_1,expression_2)

-- 如果第一个参数等于第二个参数，则NULLIF函数返回NULL，否则返回第一个参数。

select nullif('a','a'),nullif('a','b');

-- SELECT

-- CASE WHEN expr1 THEN expr2

--    ELSE expr3  END;

--  [也可以多重分支.]

-- 如果expr1 为TRUE,则返回expr2,否则返回expr3

select empno,ename,sal,

  case when sal>=5000 then '优秀'

   when sal>3000 then '中等'

 when sal>2000 then '一般'

   else '辣鸡'

END as result

 from emp;

-- USER()

-- 查询用户

select user();

-- DATABASE()

-- 当前数据库名称

select database();

-- MD5(str)

-- 为字符串算出一个 MD5 128比特检查和,通常用于对应用程序使用到的表的某个字段(比如用户密码)加密

select md5('root');

-- PASSWORD(str)

-- 从原文密码str 计算并返回密码字符串,通常用于对mysql数据库的用户密码加密

select PASSWORD('root');

-- UUID()

-- 生成32位不重复随机数，可以用作为主键

select uuid();

（5）聚合函数

如果需要查询某一条数据，但是这个数据无法在表中直接列出，并且如果要得到这个结果，需要数据表中的多行记录同时参与运算，此时使用多行函数（此前的函数都是单行函数），

聚合函数的别名：多行函数、组函数、集合函数

-- SUM 求和汇总

select sum(sal) from emp;

select sum(comm) from emp;

-- AVG 平均值

select avg(sal) from emp;

-- 聚合函数在运算过程中自动忽略值为null的行

select avg(ifnull(comm,0)) from emp

-- MAX 最大值/MIN 最小值（可以统计任何数据类型）

select max(sal),min(sal) from emp;

select max(ename),min(ename) from emp;

select max(hiredate),min(hiredate) from emp;

-- COUNT 统计记录数

select count(*) from emp;

select count(empno) from emp;

select count(comm) from emp;

-- 查询所有部门所有人的平均薪水

select avg(sal) from emp;

-- 查询各个部门的平均薪水应该如何写？

-- 思路：根据部门编号对员工信息进行分组，然后分别统计每一个部门员工的平均薪水。

-- 可以使用分组查询语句

-- 语法：

--    select 列 from 表 group by 作为分组依据的列名1,作为分组依据的列名2

-- 查询各个部门的平均薪水应该如何写？

select deptno,avg(sal) from emp group by deptno;

-- 查询各个部门中每种职位的平均薪水？

-- group by后面可以跟多个列，分组时先以第一个列分组，再以第二个列分组，……

select deptno,job,avg(sal) from emp group by deptno,job;

-- 列出部门的平均薪水大于2000的部门编号

-- 不能在where子句的后面出现组函数

-- select deptno,avg(sal) from emp where avg(sal)>2000  group by deptno;

-- 如果要对分组函数的结果进行过滤，应该使用having子句

select deptno,avg(sal) from emp   group by deptno having avg(sal)>2000;

-- 列出部门的平均薪水大于2000的部门编号,且部门编号小于20

-- 组函数只能放在having中进行过滤，不带组函数的条件where和having中均可

select deptno,avg(sal) from emp where deptno<20  group by deptno having avg(sal)>2000;

select deptno,avg(sal) from emp   group by deptno having avg(sal)>2000 and deptno<20;

-- 各个子句同时存在时的书写顺序

-- where-group by-having-order by

SELECT  deptno,COUNT（*）

FROM  emp

WHERE  sal>=2000

GROUP BY deptno

HAVING  COUNT(*)>2;

查询工资大于2000的总人数大于2的部门

十三、DQL、DML、DDL、DCL的概念与区别（附）

SQL的发展是从1974年开始的，其发展过程如下：
1974年-----由Boyce和Chamberlin提出，当时称SEQUEL。
1976年-----IBM公司的Sanjase研究所在研制RDBMS SYSTEM R
时改为SQL。
1979年-----ORACLE公司发表第一个基于SQL的商业化RDBMS产品。
1982年-----IBM公司出版第一个RDBMS语言SQL/DS。
1985年-----IBM公司出版第一个RDBMS语言DB2。
1986年-----美国国家标准化组织ANSI宣布SQL作为数据库工业标准。
SQL是一个标准的数据库语言，是面向集合的描述性非过程化语言。
它功能强，效率高，简单易学易维护（迄今为止，我还没见过比它还好
学的语言）。然而SQL语言由于以上优点，同时也出现了这样一个问题：
它是非过程性语言，即大多数语句都是独立执行的，与上下文无关，而
绝大部分应用都是一个完整的过程，显然用SQL完全实现这些功能是很困
难的。所以大多数数据库公司为了解决此问题，作了如下两方面的工作：
(1)扩充SQL，在SQL中引入过程性结构；(2)把SQL嵌入到高级语言中，
以便一起完成一个完整的应用。

二. SQL语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2 .数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback 

4. 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;

3) COMMIT [WORK]：提交。

    在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。

(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；

(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；

 

 

                ...