一、数据库简介

1.数据库概述

　　①数据库：是按照某种数据结构对数据进行组织，存储和管理的容器，简单的说就是用来存储和管理数据的容器。
　　②数据库系统：是指在计算机中引入数据库后的系统，一般由数据库，数据库管理系统，应用程序和数据库管理员组成。
　　③数据库管理系统：是一个管理，控制数据库容器中各种数据库对象的系统软件。

2.结构化查询语言SQL

　　①结构化查询语言：是一种用来与数据库通信的语言，其利用一些简单的句子构成基本的语法来存取数据库的内容，便于用户从数据库中获得和操作所需数据。
　　②SQL语言特点：非过程化语言，统一的语言，关系数据库的公共语言。
　　③SQL语言的组成：数据库定义语言（DDL），数据库操纵语言（DML），数据控制语言（DCL）。

3.数据库设计基本步骤

　　①需求分析阶段。
　　②概念结构设计阶段。
　　③逻辑结构设计阶段。
　　④数据库的物理结构设计阶段。
　　⑤数据库的实施阶段。
　　⑥数据库运行与维护阶段。

4.MySQL数据库

　　它是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据存放在一个大仓库，提高了速度和灵活性。

5.MySQL特点

　　①体积小，运行速度快，成本低，开放源码。
　　②单进程，多线程架构，属于轻量级的数据库。

6.MySQL体系结构

    包括连接池组件，管理服务和工具组件，SQL接口组件，查询分析组件，优化器组件，缓存组件，插件式存储引擎以及物理组件。

7.数据库系统解决的问题

　　持久化存储，优化读写，保证数据的有效性。

8.数据库的分类

　　①文档型，如sqlite，就是一个文件，通过对文件的复制完成数据库的复制。
　　②服务型，如mysql、postgresql，数据存储在一个物理文件中，但是需要使用终端以tcp/ip协议连接，进行数据库的读写操作。

9.数据库的操作

　　①数据库的操作，包括创建、删除。
　　②表的操作，包括创建、修改、删除。
　　③数据的操作，包括增加、修改、删除、查询，简称crud。

10.常用的可视化操作工具

　　①MySQL-Front。
　　②MySQL Workbench。
　　③Navicat for MySQL。

11. SQL的基本书写规则

　　①关键字大写。
　　②表名的首字大写。
　　③其余(列名)小写。

二、E-R模型

1.E-R

　　E表示entry-实体，R表示relationship-关系。

2.实体之间的对应规则

　　①一对一。
　　②一对多。
　　③多对多。

3.三范式

　　①第一范式（1NF)：列不可拆分。
　　②第二范式（2NF)：唯一标识。
　　③第三范式（3NF)：引用主键。
　　说明：后一个范式，都是在前一个范式的基础上建立的。

4.数据完整性

　　一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中，在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。

5.字段类型

　　①数字：int,decimal：如decimal（5，2），表示数字长度不超过5位，小数不超过2位。
　　②字符串：char,varchar,text：其中char和varchar都是有限字符的，而text是不确定的。char和varchar都是8个字符，但char如果不够会补充空格，varchar是不会补充空格的。
　　③日期：datetime。
　　④布尔：bit。

6.约束  

　　①主键primary key。
　　②非空not null。
　　③惟一unique。
　　④默认default。
　　⑤外键foreign key 。
　　⑥auto_increment表示自动增长。

7. 数据库的选型

　　①开发人员的熟练程度,费用,数据规模,性能要求,集群能力等,也可参考数据库管理员的建议。
　　②对于有1:1关系的两个表,为两个表设置相同的主键列。
　　③对于有1:N关系的两个表,在N表中添加一个外键列,该列与1表的主键列向关联。
　　④对于M:N关系,生成一个单独的表表示该关系,该关系的列由两个表的主键组成。

三、命令脚本命令

1.远程连接

　　一般在公司开发中，可能会将数据库统一搭建在一台服务器上，所有开发人员共用一个数据库，而不是在自己的电脑中配置一个数据库。

mysql -hip地址 -uroot -p

2.数据库操作

　　①创建数据库。

create database 数据库名 charset=utf8;

　　②删除数据库。

drop database 数据库名;

　　③切换数据库。

use 数据库名;

　　④查看当前选择的数据库。

select database();

3.表操作

　　①查看当前数据库中所有表。

show tables;

　　②创建表。

create table 表名(列及类型);

　　③修改表。

增，改，删：alter table 表名 add|change|drop 列名 类型;
增加约束条件：alter table 表名 add constraint 约束名，类型（字段名）;
删除约束条件：alter table  表名 drop primary key;
添加外键约束：alter table 表名 add 约束名 constraint foreign key(字段名)references 父表名（字段名）;
修改存储引擎：alter table 表名 engine = 新的存储引擎;（MyISAM，InooDB）
修改默认字符集：alter table 表名 default charset=新的字符集;

　　④删除表。

drop table 表名;

　　⑤查看表结构。

desc 表名;

　　⑥更改表名称。

rename table 原表名 to 新表名;

　　⑦查看表的创建语句。

show create table 表名;

4.数据操作

　　①查询。

select distinct 列名 from students;消除重复
select * from 表名 where 条件;

　　②增加。

全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
同时插入多条数据：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;
主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

　　③修改。

update 表名 set 列1=值1,... where 条件

　　④删除。

delete from 表名 where 条件

　　⑤逻辑删除，本质就是修改操作update。

alter table students add isdelete bit default 0;

　　⑥如果需要删除则。

update students isdelete=1 where ...;

5.备份与恢复

　　1) 数据备份

　　　　①进入超级管理员。

sudo -s

　　　　②进入mysql库目录。

cd /var/lib/mysql

　　　　③运行mysqldump命令。

mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;

　　　　④按提示输入mysql的密码  。

　　2) 数据恢复

　　　　①连接mysql，创建数据库。
　　　　②退出连接，执行如下命令。

mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql

　　　　③根据提示输入mysql密码。

四、查询（条件）

1.比较运算符

select * from students where id>3;

2.逻辑运算符

select * from students where id>3 and gender=0;

3.模糊查询

Like：select * from students where sname like \'黄_\';

　　①%表示任意多个任意字符。
　　②_表示一个任意字符。

4.范围查询

　　①in表示在一个非连续的范围内。

select * from students where id in(1,3,8);

　　②between … and …表示在一个连续的范围内。

select * from students where id between 3 and 8;

5.空判断

　　①判空is null。

select * from students where hometown is null;

　　②判非空is not null。

select * from students where hometown is null;

6.优先级

　　①小括号，not，比较运算符，逻辑运算符。
　　②and比or先运算，如果同时出现并希望先算or，需要结合()使用。

五、查询（聚合）

1.count(*)

　　表示计算总行数，括号中写星与列名，结果是相同的。

select count(*) from students;

2.max(列)

　　表示求此列的最大值。

select max(id) from students where gender=0;

3.min(列)

　　表示求此列的最小值。

select min(id) from students where isdelete=0;

4.sum(列)

　　表示求此列的和。

select sum(id) from students where gender=1;

5.avg(列)

　　表示求此列的平均值。

select avg(id) from students where isdelete=0 and gender=0;

六、查询（分组）

1.语法1

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
分组后的数据筛选

2.语法2

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
having后面的条件运算符与where的相同，不同的是having对分组的结果集进行筛选，where对原始集筛选

3.与聚合函数和Group by子句有关的常见错误

　　①在select子句中书写了多余的列。

select product_id,purchase_price,count(*) from Product Group by purchase_price;

　　②在group by子句中写了列的别名。

select product_type as pt,count(*) From Product Group by pt;

　　③Group by的子句是随机的,不能人为排序。
　　④不能在where子句中使用聚合函数。
　　⑤只有select子句和having子句以及(order by)中才能够使用count等聚合函数。

七、查询（排序）

1.语法1

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

　　将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推默认按照列值从小到大排列,asc从小到大排列，即升序，desc从大到小排序，即降序查询（分页）。

2.语法2

select * from 表名 limit start,count;

八、查询（总结）

1.完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count

2.From子句是否有必要？

　　并不是，只使用select子句也是可以的,如select(100+300)*3 as calculation,但在oracle中不允许。

3.子查询

　　①子查询就是一次性视图，与视图不同,子查询在select语句执行完毕之后就会消失。
　　②标量子查询：标量就是单一的意思,标量子查询有一个特殊的限制,必须而且只能返回1行1列的结果。
　　③关联子查询：在子查询中添加where子句条件,where p1.product_type=p2.product_type。

九、关系

1.外键

　　为stuid添加外键约束。

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

2.外键的级联操作

　　①在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常。
　　②推荐使用逻辑删除，还可以解决这个问题。
　　③可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作。

alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

3.级联操作的类型

　　①restrict（限制）：默认值，抛异常。
　　②cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除。
　　③set null：将外键设置为空。
　　④no action：什么都不做。

十、连接查询

1.连接查询分类

　　①表A inner join 表B：表A与表B匹配的行会出现在结果中。
　　②表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充。
　　③表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充。
　　④查询学生的姓名、平均分。

select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;

2.自关联

　　创建areas表的语句如下：

create table areas(
    id int primary key,
    atitle varchar(20),
    pid int,
    foreign key(pid) references areas(id)
);

十一、视图

1.视图

　　从SQL的角度来看，视图和表是相同的两者的区别在于表中保存的是实际数据，而视图中保存的是select语句(视图本身并不存储数据)。

create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

2.视图的用途

　　就是查询。

select * from stuscore;

3.视图的优点

　　①视图无需保存数据，因此可以节省存储设备的容量。
　　②可以将频繁使用的select语句保存成视图，这样就不用每次都重写了。

4.视图的限制

　　①定义视图时不能使用order by子句，这是因为视图和表一样数据行都是没有顺序的。
　　②对视图进行更新(未被汇总得到的视图)  。
　　③限制条件
　　　　select子句未使用Dintinct。
　　　　From子句中只有一张表。
　　　　未使用group by子句。
　　　　未使用having子句。

　　④删除视图(多重视图)。

drop view 视图名 cascade

十二、事务

1.事务

　　是恢复和并发控制的基本单位,什么是事务，就是需要在一个处理单元中执行的一系列更新处理的集合,通过使用事务,可以对数据库中的数据更新处理提交和取消进行<beigin,commit,rollback>视图本质就是对查询的一个封装。

2.使用事务的请况

　　当数据被更改时，包括insert，update，delete，使用事务可以完成退回的功能，保证业务逻辑的正确性。

3.事务四大特性(简称ACID)

　　①原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
　　②一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
　　③隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明。
　　④持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。
　　⑤要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务。
　　⑥事务语句：

begin;  // 开启
commit; // 提交
rollback; // 回滚

十三、索引

1.单列索引和组合索引

　　①单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
　　②组合索引，即一个索包含多个列。

2.操作

　　①查看索引：

show index from table_name;

　　②创建索引：

create index indexName on mytable(usrname(length));

　　③删除索引：

drop index [indexName] on mytable;

3.缺点

　　①虽然索引提高了查询速度，同时却会降低更新表的速度，如对表进行insert，update和delete。
　　②因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。
　　③建立索引会占用磁盘空间的索引文件。

4.检测运行时间

　　①开启运行时间检测：

set profiling=1;

　　②执行查询语句：
　　③查看执行的时间：

show profiles;

十四、谓词

1.返回值为真值的函数

　　like，in，not in，between，is null，is not null。

十五、函数

1.根据用途划分

　　①算术函数(用来进行数值计算的函数)。
　　②字符串函数(用来进行字符串操作的函数)。
　　③日期函数(用来进行日期操作的函数)。
　　④转换函数(用来转换数值类型和值的函数)。
　　⑤聚合函数(用来进行数据聚合的函数)。

2.case表达式

　　①case表达式分为简单case表达式和搜索case表达式两种。
　　②搜索case表达式包含简单case表达式的全部功能。

case搜索表达式;
   case when<求值表达式>then<表达式>
      when<求值表达式>then<表达式>
      ....
      else<表达式>
   end
case简单表达式:
   case<表达式>
       when<表达式>then<表达式>
       when<表达式>then<表达式>
       when<表达式>then<表达式>
       .....
       else<表达式>
   end

十六、集合运算

1.集合运算

　　集合在数据库领域表示记录的集合，具体来说表，视图和查询执行的结果都是记录的集合。

2.集合运算注意事项

　　①作为运算对象的记录的列数必须相同。
　　②作为运算对象的记录中列的类型必须一致。
　　③可以使用任何select语句，但order by子句只能在最后使用一次。

3.表的加法-union并集

select product-id,product_name from Product union(all) select product_id,product_name from product2;

4.选取表中的公共部分-intersect交集

select product_id,product_name from Product intersect select product_id,product_name from Product2 order by product_id;

5.记录的减法-except差集

select product_id,product_name from Product except select product_id,product_name from Product2 order by product_id;

6.表的联结

　　联结join就是将其他表的列添加过来，进行添加列的集合运算，union是以行(纵向)为单位进行操作，而联结则是以列(横向)为单位进行的。

十七、与python交互

1.安装引入模块

　　①安装mysql模块。

sudo apt-get install python-mysqldb，pymysql

　　②在文件中引入模块。

import Mysqldb

2.Connection对象

　　用于建立与数据库的连接。

创建对象：调用connect()方法
conn=connect(参数列表)
参数host：连接的mysql主机，如果本机是\'localhost\'
参数port：连接的mysql主机的端口，默认是3306
参数db：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，默认是\'gb2312\'，要求与数据库创建时指定的编码一致

3.对象的方法

close()：关闭连接
commit()事务：所以需要提交才会生效
rollback()事务：放弃之前的操作
cursor()返回Cursor对象：用于执行sql语句并获得结果

4.Cursor对象

创建对象：调用Connection对象的cursor()方法
cursor1 = conn.cursor()

5.对象的方法

close()  关闭
execute(operation [, parameters ])  执行语句，返回受影响的行数
fetchone() 执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
next()  执行查询语句时，获取当前行的下一行
fetchall()  执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
scroll(value[,mode])  将行指针移动到某个位置
mode  表示移动的方式
mode的默认值为relative  表示基于当前行移动到value，value为正则向下移动，value为负则向上移动
mode的值为absolute  表示基于第一条数据的位置，第一条数据的位置为0

6.对象的属性

　　①rowcount只读属性，表示最近一次execute()执行后受影响的行数。
　　②connection获得当前连接对象。

7.sql语句参数化

sname = raw_input("请输入学生姓名：")
params = [sname]
count = cs1.execute(\'insert into students(sname) values(%s)\',params)

十八、SQL语句法则

1.数据库和SQL

　　法则1-1：关系数据库以行为单位读写数据。 
　　法则1-2：一个单元格只能插入一个数据。
　　法则1-3：学会标准SQL就可以在各种RDBMS中书写SQL语句了。
　　法则1-4：SQL根据功能不他可以分为三类,其中使用最多的是DML。
　　法则1-5：SQL语句以分号(;)结尾。
　　法则1-6：关键字不区分大小写。
　　法则1-7：字符串和日期常数需要使用单引号(\’\’)括起来,数字常数无需加注单引号。
　　法则1-8：单词之间需要使用半角空格或者换行符进行分隔。
　　法则1-9：数据库名,表名和列名等可以使用半角英文字母,半角数字和下划线。
　　法则1-10：名称必须以半角英文字母作为开头。
　　法则1-11：名称不能重复。
　　法则1-12：删除了的表是无法恢复的,在执行drop table语句之前需仔细确认。
　　法则1-13：表定义变更之后无法恢复,在执行alter table语句之前需仔细确认。

2.查询基础

　　法则2-1：星号(*)代表全部列的意思。
　　法则2-2：设定汉语别名时需要使用双引号(“”)括起来。
　　法则2-3：在select语句中使用distinct可以删除重复行。
　　法则2-4：where子句要紧跟在from子句之后。
　　法则2-5：注释是SQL语句中用来标识说明或者注意事项的部分,分为一行注释和多行注释两种。
　　法则2-6：select子句中可以使用常数或者表达式。
　　法则2-7：使用比较运算符一定要注意不等号和等号的位置。
　　法则2-8：字符串类型的数据原则上按照字典顺序进行排序,不能与数字的大小顺序混淆。
　　法则2-9：希望选取null记录时,需要在条件表达式中使用is null运算符,希望选取不是null的记录时,需要在条件表达式中使用is null的运算符。
　　法则2-10：not运算符用来否定某一条件,但是不能滥用。
　　法则2-11：多个条件进行组合时,需要使用and运算符或者or运算符。
　　法则2-12：and运算符的优先级高于or运算符,想要优先执行or运算符可以使用括号。

3.聚合与排序

　　法则3-1：count函数的结果根据参数的不同而不同,count(*)会得到包含null的数据行数,而count(列名)会得到null之外的数据行数。
　　法则3-2：聚合函数会将null排除在外,但count(*)例外,并不会排除null。
　　法则3-3：max/min函数几乎适用于所有数据类型的列,sum/avg函数只适用于数值类型的列。
　　法则3-4：想要计算值的种类时,可以在count函数的参数中使用distinct。
　　法则3-5：在聚合函数中使用distinct,可以删除重复数据。
　　法则3-6：group by就像是切分表的一把刀。
　　法则3-7：SQL子句的顺序不能改变,也不能互相替换。
　　法则3-8：聚合键中包含null时,在结果中会以”不确定”行(空行)的形式表现出来。
　　法则3-9：使用group by子句时,select子句中不能出现聚合键之外的列名。
　　法则3-10：在group by子句中不能使用select子句中定义的别名。
　　法则3-11：group by子句结果的显示是无序的。
　　法则3-12：只有select子句和having子句以及order by子句中才能够使用聚合函数。
　　法则3-13：having子句要写在group by子句之后。
　　法则3-14：聚合键所对于的条件不应该书写在having子句中,而应该书写在where子句当中。
　　法则3-15：order by子句通常写在select语句的末尾。
　　法则3-16：未指定order by子句中排列顺序时会默认使用升序进行排列。
　　法则3-17：排序键中包含null时,会在开头或结尾进行汇总。
　　法则3-18：在order by子句中可以使用select子句中定义的别名。
　　法则3-19：在order by子句中可以使用select子句中未使用的列和聚合函数。
　　法则3-20：在order by子句中不要使用列编号。

4.数据更新

　　法则4-1：原则上,执行一次insert语句会插入一行数据。
　　法则4-2：省略insert语句中的列名,就会设置为该列的默认值(没有默认值会设置为null)。
　　法则4-3：insert语句的select语句中,可以使用where子句或者group by子句等任何SQL语法(但使用order by子句并不会产生任何效果)(常用在数据表的复制)。
　　法则4-4：delete语句的删除对象并不是表或者列,而是记录(行)。
　　法则4-5：可以通过where子句指定对象条件来删除部分数据。
　　法则4-6：使用update语句可以将值清空为null(但只限于未设置not null约束的列)。
　　法则4-7：事务是需要在同一个处理单元中执行的一些列更新处理的集合。
　　法则4-8：虽然我们可以不清楚事务开始的时间点,但是在事务结束时一定要仔细进行确认。

5.复杂查询

　　法则5-1：表中存储的是实际数据,而视图中保存的是从列表中取出数据属于所使用的select语句。
　　法则5-2：应该将经常使用的select语句做成视图。
　　法则5-3：多重视图会降低SQL的性能,应该避免在视图的基础上创建视图。
　　法则5-4：定义视图时不要使用order by子句。
　　法则5-5：视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
　　法则5-6：子查询作为内层查询会首先执行。
　　法则5-7：标量子查询就是返回单一值的子查询。
　　法则5-8：在细分的组内进行比较时,需要使用关联子查询。

6.Case表达式

　　法则6-1：通常指定关联子查询作为exist的参数。
　　法则6-2：作为exist参数的子查询中通常使用select*。
　　法则6-3：虽然case表达式中的else子句可以忽略,但最好不要忽略。
　　法则6-4：case表达式中的end不能忽略。

7.集合运算

　　法则7-1：集合运算符会除去重复的记录。
　　法则7-2：在集合运算符中使用all选项,可以保留重复行。
　　法则7-3：进行联结时需要在from子句中使用多张表。
　　法则7-4：进行内联结时,必须使用on子句,并且要书写from和where之间。
　　法则7-5：使用联结时select子句中的列需要按照<表的别名>.<列名>的格式书写。