

首页  思维导图  详情



07 Database(mysql)

2019-03-17 16:32:16   0  举报





仅支持查看

AI智能生成

Database(mysql)

模版推荐

作者其他创作

大纲/内容

简介

定义

数据库就是一个由一批分门别类的数据构成的有序集合，这个集合通常被保存 为一个或者多个彼此相关的文件

优点

持久化储存

读写速度极高

保证数据的有效性

对程序支持性非常好，容易扩展

数据库类型

Relational Database Management System(关系型数据库)

容易理解，二维表结构

使用方便，通用的SQL语言是得操作关系型数据库 非常方便，便于复杂查询

支持事务等复杂的数据操作功能

NoSQL(非关系型数据库)

数据之间无关系，容易扩展

结构简单，具有非常高的读写性能，在大数据量下，同样表现优秀

无需事先建立字段，随时可以存储自定义的数据格式

关系型数据库核心元素

数据行(记录)

数据列(字段)

数据表(数据行的集合)

数据库(数据表的集合)

入门操作命令

mysql -uroot -p

status;

exit;    quit;

show databases;

creata database databaseName charset=utf8;

use databaseName;

select database();

show tables;

source databaseName.sql;

desc tableName;

SQL

DQL

按条件查询

select 字段1，字段2，字段3 from 表名  where  条件

比较运算符：=    <>or!=    <=    >=    <    >

select * from student where age=20;

逻辑运算符：and or not

select name, age, description from studnent where age>=18 and sex=2;

范围运算符between ... and ...

select class, name, age, sex from student where not age between 18 and 22;

in运算符

select name, age, class from student where class in (301, 302, 303);

模糊查询：%(匹配任意多个字符)    _(匹配任意一个字符)

select * from student where name like "%风%";

聚合运算

avg     返回指定列的平均值

select avg(age) from student where class=306;

count    返回指定列中非null值的个数

select count(id) as c from student where class=305;

min    返回指定列的最小值

select min(age) from student;

max

sum    返回指定列的所有值之和

select sum(id), class  from student group by class;

分组查询：group by

select age, count(name) from student group by age;

结果排序：order by

select name, age, sex from student where class=309 order by age desce;

结果限制：limit

select id, name, sex from student order by age desc, id asc limit 10;

消除重复行：distinct

select distinct class from student;

where条件的运算符进阶

空判断：null

select * from student where description is null;

select * from student where descritpion is not null and sex=2;

运算符优先级

小括号》》》not》》》比较运算符》》》逻辑运算符

连接查询

内连接 inner join

select 字段1,字段2... from 主表 inner join 从表 on 主表.主键=从表.外键

左连接 left join

select 字段1，字段2，字段3... from 表1 left join 表2 on 表1.列 = 表2.列

右连接 right join

多表连查询

单表自连查询

嵌套查询

格式

select 字段 from 表名 where 条件(另一条查询语句)

# 判断301中的每个人平均成绩大于上面的到的平均成绩 select name,avg(achievement) from student as a left join achievement as b on a.id=b.sid where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a left join achievement as b on a.id=b.sid where class=301);

子查询

被嵌入的select语句

主查询

主要查询的对象，第一条select语句

关系

子查询是嵌入到主查询中

子查询是辅助主查询的,要么充当条件,要么充当数据源

子查询是可以独立存在的语句,是一条完整的 select 语句

having

group by 字段 having 条件；

过滤筛选，主要作用类似于where关键字，用于在SQL语句中进行条件判断，过滤结果的。 但是与where不同的地方在于having只能跟在group by 之后使用。

select查询语句完整格式

select distinct 字段1,字段2.... from 表名 as 表别名 left join 从表1 on 表名.主键=从表1.外键 left join .... where .... group by ... having ... order by ... limit start,count

执行顺序

from 表名(包括连表)

where...

group by...

select distinct

having...

order by...

limit start, count;

DML

添加数据：insert

INSERT INTO 表名 (字段1,字段2,字段3,....) VALUES (字段值1,字段值2,字段值3,....);

INSERT INTO student(name,sex,class,age,description) VALUES ('周润发',1,508,17,'5个A~'),('周杰伦',1,508,17,'给我一首歌的时间~');

更新数据：update

UPDATE 表名 SET 字段1=字段值1,字段2=字段值2 WHERE 条件

删除数据：delete

DELETE FROM 表名 WHERE 条件

DDL

DROP TBALE 表名

删除整个表

TRUNCATE table

清空/重置表[表还在数据被清空了]

drop database 数据库名

删除数据库

create database 数据库名 charset=utf8;

创建数据库

创建表

显示建表语句

show create table 表名\G;

修改表

添加字段

alter table 表名 add 列名类型；

alter table student add birthday datetime;

修改字段

alter table 表名 change 原名新名类型及约束；

alter table student change birthday birth datetime not null;

alter table 表名 modify 列名类型及约束；

alter table student modify birth date not null;

删除字段

alter table 表名 drop  列名；

alter table student drip birthday;

删除表

drop table 表名；

drop table student;

TPL

DCL

CCL

数据类型

整数

bit[0-64]

tinyint

smallint

int

bigint

小数类型

decimal(M,D)

字符串

varchar

不定长字符串

节省内存，但执行效率较低

char

定长字符串

执行效率高，但内存占用相对varchar较大

text

日期时间类型

date

2019-03-12

time

16:30:00

datetime

2019-03-12  16:30:00

year

timestamp

枚举类型

enum

enum("male", "female")

约束规则

primary key

在表中区分每一行数据的唯一性标识符，数据在物理上存储的顺序

not null

此字段不允许填写空值，默认允许

unique

此字段的值不允重复

default

当不填写此值时会使用默认值，如果填写时以填写为准

unsigned

数字无符号，即为正数

auto_increment

数字可以自动增加

foreign key

用于连接两个表的关系，对关系字段进行约束， 当为关系字段填写值时，会到关联的表中查询时是否此值是否存在， 如果存在则填写成功，如果不存在则填写失败并抛出异常

数据库设计

Entry(实体)

就是我们根据开发需求，要保存到数据库中作为一张表存在的事物。实体的名称最终会变成表名

实体会有属性，实体的属性就是描述这个事物的内容，实体的属性最终会在表中作为字段存在。

实体与实体之间会存在关系，这种关系一般就是根据三范式提取出来的主外键。

三范式

列的原子性,数据要保证不可分割

必须要有主键(可以是一个或多个列构成)；非主键的列必须完全依赖主键，不能部分依赖；

非主键对主键的直接依赖，不能传递依赖；

E-R模型

E----》Entry

R----》relationship

1-1

1-n

n-m

逻辑删除

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

删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0

对于非重要数据，可以进行物理删除

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

备份

运行mysqldump命令

mysqldump –uroot –p 数据库名 > python.sql;

恢复

连接mysql，创建新的数据库

退出连接，执行如下命令 mysql -uroot –p 新数据库名 < python.sql

我们之前学习的source也是一种恢复方式，但是两种使用有一个区别。就是 mysql 命令这种方式，可以远程恢复，而source这种只能本地电脑恢复。 mysql -hIP地址 -uroot -p密码

python操作mysql

安装pymysql模块

pip install pumysql

使用pymysql模块操作数据库

执行语句

#执行sql，更新单条数据，并返回受影响行数 result = cursor.execute("SQL语句") #插入多条，并返回受影响的函数，例如批量添加 result2 = cursor.executemany("多条数据") #获取最新自增ID new_id = cursor.lastrowid

获取结果

#获取一行 result1 = cursor.fetchone() #获取多行[参数可以设置指定返回数量] result2 = cursor.fetchmany(整型) #获取所有 result3 = cursor.fetchall()

操作数据

#提交，保存新建或修改的数据，如果是查询则不需要 conn.commit() # 写在execute()之后