数据库 MySQL

mysql数据库，是当前应用非常广泛的关系型数据库。数据库主要优点 方便存储，快速查找。

数据库分类

1. 服务型数据库 mysql postgresql mssql 数据存储在一个物理文件中，但是需要使用终端以tcp/ip协议连接，进行数据库的读写操作；

2. 文档型数据库库 sqlite 就是一个文件，通过对文件的复制完成数据库的复制；

E-R模型

当前物理的数据库都是按照E-R模型进行设计的，E表示 entry，实体；R表示relationship，关系；一个实体转换为数据库中的一个表，关系是描述两个实体之间的对应规则，包括 1对1,1对多，多对多；关系转换为数据库表中的一个列，在关系型数据库中一行就是第一个对象。

三范式

第一范式（1NF） 列不可拆分

第二范式（2NF） 唯一标识

第三范式（3NF） 引用主键

说明：后一个范式，都是在前一个范式的基础上建立的；

数据完整性

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

字段类型

mysql中的数据类型，数字：int decimal   字符串：char varchar text   日期：datetime   布尔：bit

约束

主键 primary key、  非空 not null 、  唯一 unique  、 默认 default  、 外键 foreign key

图形化界面操作数据库

使用工具 navicat for mysql 进行对应的操作；

逻辑删除 

在表中设置  isdelete 来进行逻辑删除，切记不要物理删除；

命令行连接数据库

mysql -h localhost -u root 回车  然后输入密码 

创建和删除数据库

create database db;

drop database db;

show create database db;  查看创建数据库名为db的数据库创建SQL语句；

创建数据表

create table students(

id int auto_increment primary key not null,

name varchar(10) not null,

birthday datetime,

gender bit default 1,

isDel bit default 0

);

重命名表

rename table 原表名 to 新表名;

查看表的创建语句

show create table '表名';

数据操作

查询：  select * from 表名

增加：全列插入 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 条件

alter table student add isdelete bit default 0;

数据库备份与恢复

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

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

根据提示输入mysql密码；

查询-比较运算符

消除重复行

select distinct gender from student;

比较运算符：

等于=、大于>、小于等于<=、大于等于>=、小于<、不等于!=或<>

select * from students where id>3;

逻辑运算符：

and、or、not

查询编号大于3的女同学

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

模糊查询

like关键字、%表示任意多个任意字符、_表示一个任意字符

select * from students where sname like '黄%';

范围查询

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

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

between...and...表示在一个连续范围内

select * from students where id between 3 and 8;

判断是否为空

is null

查询没有填写地址的学生

select * from students where hometown is null;

查询填写了地址的学生

seletct * from students where hometown is not null;

注意优先级：

小括号、not、比较运算符、逻辑运算符；and比or先运算，如果同时出现希望先算or，需要集合()使用；

聚合函数

为了快速得到统计数据，提供了五个聚合函数

count(*) 表示计算总行数，括号中写*与列名，结果是相同的；

查询学生总数： select count(*) from students;

max(列) 表示求此列的最大值

查询女生的编号最大值

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

min(列) 表示求此列的最小值

查询未删除的学生最小编号

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

sum(列) 表示求此列的和

查询男生的编号之和

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

avg(列) 表示求此列的平均成绩

查询未删除女生的编号的平均值

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

分组

按照字段分组，表示此字段相同的数据会被放在一个组中，分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中，可以对分组后的数据进行统计，做聚合运算；

select gender as 性别,count(*) from students group by gender;

select hometown as 家乡,count(*) from students group by hometown;

分组后的筛选

select gender as 性别,count(*) from students group by gender having gender = 1;

对比where 与 having

where 是对from后面指定的表进行数据筛选，属于对原始数据的筛选，having是对group by 的结果进行筛选；

排序

为了方便查看数据，可以对数据进行排序

select * from 表名 order by 列 asc|desc

select * from student where gender =1 and isdelete=0 order by age asc;

分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

select * from 表名 limit start,count;

从start开始，获取count条数据，start索引从0开始；

分页公式：

求第n页的数据

n为页数，m为每页需要显示的信息数目

select * from students where isdel=0 limit (n-1)*m,m

完整的select 语句

select distinct from 表名 where .. group by ... having ... order by ... limit start,count;

建立关系表

create table score(

id int primary key auto_increment,

stuid int,

subid int,

score decimal(5,2),

foreign key(stuid) references students(id),

foreign key(subid) references subject(id)

);

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

连接查询

select students.sname,subjects.stitle,scores.score from scores inner join students on scores.stuid=students.id inner join subjects on scores.subid=subjects.id;

总结查询：

select distince 列* from 表1 inner|left|right join 表2 on 表1与表2的关系 where ... group by ... having ... order by ... asc|desc limit start,count;

关联查询

自关联 

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

视图

对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情，解决：定义视图；

视图本质就是对查询的一个封装。

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

select * from stuscore;

事务和索引  这里感觉用的不是太多，但是也要掌握，后期用到再学。