mysql 详细笔记总结

主要知识点：

数据库与表的创建、删除

字段的类型、约束

关系的存储

数据行的增加、修改、删除

数据行的查询

试图、事务、索引

基础

1、ER模型、三范式

ER模型：E表示entry，实体。

R表示relationship，关系。

一个实体转换为数据库中的一个表。

关系描述两个实体之间的对应规则，包括：一对一、一对多、多对多

三范式：经过研究和对使用中问题的总结，对于设计数据库提出一些规范，这些规范被称为范式。

第一范式（1NF）：列不可拆分（够用就行：比如商品的生产日期）

第二范式（2NF）：唯一标识（通过一个属性直接找到一个唯一的对象）

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

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

2、数据完整性

字段类型：

·数字：int，decimal(a,b)：a表示一共有多少位，b表示小数有多少位

·字符串：char（有限长度，字符固定），varchar（有限长度，字符可变），text（大文本）·日期：datetime（包括日期和时间）

·布尔：bit

约束：

·主键：primary key

·非空：not null

·唯一：unique

·默认：default

·外键：foreign key

3、逻辑删除

·对于重要数据，并不希望物理删除，一旦删除，数据无法找回

·一般对于重要数据，会设置一个isdelete/deleteflag的列，类型位bit，表示逻辑删除·大于大量增长的非重要数据，可以进行物理删除

·数据的重要性，要根据实际开发决定

4、连接服务器

5、创建表

1)表操作

·显示当前所有数据库：Show databases；

·删除当前数据库：drop database 数据库名;

例如：drop database gy1;

·查看当前选择的数据库：select database();

·创建数据库：Create database 数据库名charset=utf8; 例如：Create database python1 charset=utf8;

·使用/切换数据库：use 数据库名;

例如：use python1；

·查看当前数据库中所有表：show tables；

2)创建表

auto_increment 表示自动增长

create table 表名(列及类型);

·创建表：create table 表名(字段类型约束)；

例如：

Create table students(

Id int auto_increment primary key not null,

Name varchar(10) not null,

Gender bit default 1,

Birthday datetime);

·查看表：desc 表名;

例如：desc students;

·修改表：alter table 表名add|change|drop 列名类型;

例如：alter table students add isdelete bit default 0;

·查看表的创建语句：show create table students;

·删除表：drop table 表名;

·修改表的列名：alter table 表名称change 列名新列名varchar(10);

例如：alter table stu change title name varchar(10);

6、数据操作

·查询：Select*from 表名;

例如：Select*from students;

·添加：insert into 表名values(),()...; 只要执行insert就会有新的行产生。例如：insert into students values(0,’白敬亭’,1,’1990-1-1’,0);

全列插入：insert into 表名value(...)

缺省插入：insert into 表名(列1,...) values(值1,...)

同时插入多条数据：insert into 表名values(...),(...)...;

或insert into 表名(列1,...) values (值1,...),(值1,...)...;

·主键列是自动增长，但是在全列插入时需要占位，通过使用0，插入成功后以实际数据为准。

·修改：update 表名set 列1=值1,... where 条件

例如：Update students set birthday=’1999-2-2’ where id=2;

·删除：delete from students where id=3;（物理删除）

·Update students set isdelete=1 where id=3;（逻辑删除，其实就是修改操作update）

·更改表名：rename table 原表名to 新表名;

例如：rename table stu to s;

7、备份与恢复

·备份

第一步：找出要备份的数据库，退出mysql

第二步：使用mysqldump命令备份数据库

Mysqldump -uroot -p 数据库名> 保存名.sql

例如：Mysqldump -uroot -p py21 > C:/abc.sql。c盘目录下有个abc.sql文件，则备份成功。

·恢复

退出MySQL，执行mysql -uroot -p py11

再次连接MySQL，则可以查看py11中含有恢复的表。

查询

1.建立表rooms、stu、外键

create table rooms(id int primary key not null,

title varchar(10));

Create table stu(id int primary key auto_increment not null,

Name varchar(10),

Roomid int);

·外键

Alter table stu add constraint stu_room foreign key(roomid) references rooms(id);

Insert into rooms values(314,’聚义厅’);

Insert into stu values(0,’郭靖’,314);

·错误处理

Alter table areas modify title varchar(20) character set gbk;

2.查询

·from 关键字后面写表名，表示数据来源于是这张表

·select后面写表中的列名，如果是*表示在结果中显示表中所有列

·在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

·如果要查询多个列，之间使用逗号分隔

查询所有：Select*from students;

查询部分：select id ,name from students;

3.消除

在select后面列前使用distinct可以消除重复的行

Select distinct gender from students;

4.条件

·使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

Select*from 表名 where 条件;

·比较运算符：等于 = ；大于 >；大于等于 >=；小于 < ；小于等于 <= ；不等于！= 或 <> 。·查询id大于3的学生

Select*from students where id>3;

·查询id<=4的科目

Select*from subjects where id<=4;

·查询姓名不是黄蓉的学生

Select*from students where name!=’黄蓉’;

·查询未被删除的

Select*from students where isdelete=0;

·逻辑运算符：and or not

·模糊查询： like

%表示任意多个任意字符

_表示一个任意字符

·查询姓黄的学生

Select*from students where name like ‘黄%’;

·查询姓黄并且名字是一个字的学生Select*from students where name like ‘黄_’;

·查询姓黄或叫靖的学生

Select*from students where name like ‘黄%’or name like ‘%靖%’;

·范围查询：

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

查询编号是1或3或8的学生

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

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

查询学生是3至8的学生

Select*from students where id between 3 and 8;

查询学生是3至8的男生

Select*from students where id between 3 and 8 and gender=1;

5.空判断

·注意null(没有内存)与’’(空字符串)是不同的

·判空 is null

查询没有填写生日的学生

Select*from students where birthday is null;

·判非空 is not null

查询填写了生日的男生

Select*from students where birthday is not null and gender =1;

6.优先级

·小括号，not，比较运算符，逻辑运算符

·and比or先运算，如果同时出现并希望先算or,需要结合（）使用。聚合

聚合得不到具体信息，通过子查询可以得到详细信息。

·count()表示计算总行数，括号内写*或列名，结果是相同的

计算未删除的学生

Select count(*) from students where isdelete=0;

·Max(列)表示求此列的最大值

计算当gender=0时，id的最大值

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

·Min(列)表示求此列的最小值

计算未删除学生的最小编号

Select min(id) from students where isdelete=0;只能看到编号，不能看到是谁

计算未删除学生的最小编号,并列出学生的信息

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

·Sum(列)表示求此列的和

计算gender=1，id的和

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

·Avg(列)表示此列的平均值

查询未删除gender=0（女生）的编号的平均值

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

分组

按照字段分组，表示此字段相同的数据会被放到一个组中

分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中

可以对分组后的数据进行统计，做聚合计算

语法：select 列1，列2，聚合...from 表名

group by 列1，列2，列3...

Having 列1，...聚合...

注：having后面的条件运算符与where的相同。Where是对from后面指定的表进行数据筛选，属于对原始数据筛选，having是对group by 的结果进行筛选。

·根据性别分组

Select count(*) from students group by gender;

Select gender,count(*) from students group by gender;

Select gender,count(*) from students group by gender having gender=0;

Select gender,count(*) from students group by gender having count(*)>2;

Select gender,count(*) as rs from students group by gender having rs>2;

排序

·将行数据按照列1进行排序，如果某些行列的值相同，则按照列2排序，以此类推·默认按照值从小到大排序

·asc从小到大排序，即升序

·desc从大到小排序，即降序

1.查询未删除男生学生信息，按学号降序

Select*from students where gender=1 and isdelete=0 order by id desc;

2.为subjects添加isdelete列，并删除linux和redis

查询未删除科目信息，按名称升序

Select*from subjects where isdelete=0 order by id asc;

分页

Select*from 表名

Limit start, count

从start开始，获取count条数据，start索引从0开始select*from students limit 2,1;

select*from students limit 1,3;

·示例：分页

·已知：每页显示m条数据，当前显示第n页

·求总页数：此段逻辑后面会在python中实现

查询总条数p1

使用p1除以m得到p2

如果整除则p2为总数页

如果不整除则p2+1为总数页

·求第n页的数据

Select*from students

Where isdelete=0

Limit(n-1)*m,m

总结执行顺序：

From 表名

Where...

Group by...

Select distinct*

Having...

Order by...

Limit star ,count

关系

建立关系表

Create table scores(

Id int auto_increment primary key not null,

Scores decimal(4,1),

Stuid int,

Subid int,

Foreign key(stuid) references students(id),

Foreign key(subid) references subjects(id));

外键的级联操作

·在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常

·推荐使用逻辑删除，还可以解决这个问题

·可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作

·语法

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

级联操作的类型包括：

·restrict(限制)：默认值，抛异常

·cascade(级联)：如果主表的记录删掉，则从表中相关联的记录都将被删除

·set null :将外键设置为空

·no action ：什么都不做

例子：

·问：查询每个学生每个科目的分数

·分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示在同一个结果中呢？

·答：当查询结果来源于多张表时，需要使用连接查询

·关键：找到表间的关系，当前的关系时

·students表的id---scores表的stuid

·subjects表的id---scores表的subid

则上面问题的答案为：

Select https://www.360docs.net/doc/b95745204.html,,subjects.title,scores.scores

From scores

Inner join students on scores.stuid=students.id

Inner join subjects on scores.subid=subjects.id;

·结论：当需要对有关系的多张表进行查询时，需要使用连接join

连接查询

·连接查询分类如下：

·表A inner join 表B：表A与表B匹配的行会出现在结果中

·表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充

·表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B 中独有的数据，未对应的数据使用null填充

·在查询或条件中推荐使用“表名.列名”的语法

·如果多个表中列名不重复可以省略“表名.”部分

·如果表的名称太长，可以在表名后面使用’as简写名’或’简写名’，为表起个临时的简写名称

关联查询

查询男生的姓名、总分

Students.gender=1

https://www.360docs.net/doc/b95745204.html,

Sum(scores.score)

建立连接：students.id=scores.stuid

使用sum->分组，姓名：每个人的总分

例如：Select name ,sum(scores)

From students

Inner join scores on students.id=scores.stuid

Where gender=1

Group by students.id;

查询科目的名称、平均分

Subjects.title

Avg(scores.score)

连接：sunjects.id=scores.subid

Avg->group by subjects.title

例如：Select subjects.title,avg(scores.scores)

From scores

Inner join subjects on scores.subid=subid=subjects.id

Group by subjects.title;

查询未删除科目的名称、最高分、平均分

Where subjects.isdelete=0

Subjects.title

Max(scores.scores)

Avg(scores.scores)

连接：subjects.id=scores.subid

Max,avg->group by subjects.title

例如：Select subjects.title,avg(scores.scores),max(scores.scores) From scores

Inner join subjects on scores.subid=subjects.id

Where subjects.isdelete=0

Group by subjects.title;

自关联

create table areas (id int not null primary key auto_increment , title varchar(20) ,pid int,

foreign key(pid) references areas(id));

导入数据

Source areas.sql;

查询有多少条

Select count(*) from areas;

·查询一共有多少个省

Select count(*) from areas where pid is null;

·查询省的名称为‘山西省’的所有城市Select city.*from areas as city

Inner join areas as province on city.pid=province.id Where province.title=’山西省’;

·查询市的名称为“广州市”的所有区县Select dis.*from areas as dis

Inner join areas as city on city.id=dis.pid

Where city.title=’广州市’;

Select sheng.id as sid,sheng.title as stitle,

Shi.id as shiid,shi.title as shititle

From areas as sheng

Inner join areas as shi on sheng.id=shi.pid

Where sheng.pid is null

Limit 0,100;

Select sheng.id as sid,sheng.title as stitle,

Shi.id as shiid,shi.title as shititle

From areas as sheng

Inner join areas as shi on sheng.id=shi.pid

Where sheng.pid is null and sheng.title=’山西省’;

视图

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

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

·定义视图

Create view stuscore as

Select student.*,scores.scores from scores

Inner join students on scores.stuid=students.id;

·视图的用途就是查询

Select*from stuscore;

建立一个视图

Create view v_1 as

Select stu.*,sco.scores,sub.title from scores as sco

Inner join students as stu on sco.stuid=stu.id

Inner join subjects as sub on sco.subid=sub.id;

查询表

Alter view v_1 as

Select stu.*,sco.scores,sub.title from scores as sco

Inner join students as stu on sco.stuid=stu.id

Inner join subjects as sub on sco.subid=sub.id

Where stu.isdelete=0 and sub.isdelete=0;

事务

·当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回

·使用事务可以完成退回的功能，保证业务逻辑的正确性

·事务四大特性（ACID）

·原子性（Atomicity）:事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行

·一致性（consistency）:几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致

·隔离性（isolation）:事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的

·持久性（durability）:对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

·要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务

·查看表的创建语句

Show create table students;

·修改表的类型

Alter table ‘表名’ engine=innodb；

·事务语句

开启begin;

提交commit;

回滚rollback;

·使用事务的情况：当数据被更改时，包括insert、update、delete

示例1：

·步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表终端1：

Select*from students；

终端2：

Begin;

Insert into students(name) values(‘王子’);

·步骤2：

终端1：

Select*from students;

·步骤3

终端2:

Commit;

终端1：select*from students;

示例2：

·步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一种表终端1：

Select*from students;

终端2：

Begin;

Insert into students(name) values(‘张飞’);

·步骤2

终端1：

Select*from students；

·步骤3

终端2：

rollback;

终端1：

Select*from students;

索引

·当数据库中数据量很大时，查找数据会变的很慢

·索引能提高数据访问性能

·主键和唯一索引，都是索引，可以提高查询速度

在MySQL中怎么实现索引：

·选择列的数据类型

·越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

·简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂·尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

操作

·索引分单列索引和组合索引

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

·组合索引，即一个索包含多个列

·查看索引

Show index from students;

·创建索引

Create index indexname on students(name(10));

·删除索引

Drop index indexname on students;

缺点

·虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update、