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;
\c
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 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 之后使用。
但是与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 表名 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
mysql -uroot –p 新数据库名 < python.sql
我们之前学习的source也是一种恢复方式，但是两种使用有一个区别。就是
mysql 命令这种方式，可以远程 恢复，而source这种只能本地电脑恢复。
mysql -hIP地址 -uroot -p密码
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
result = cursor.execute("SQL语句")
#插入多条，并返回受影响的函数，例如批量添加
result2 = cursor.executemany("多条数据")
#获取最新自增ID
new_id = cursor.lastrowid
获取结果
#获取一行
result1 = cursor.fetchone()
#获取多行[参数可以设置指定返回数量]
result2 = cursor.fetchmany(整型)
#获取所有
result3 = cursor.fetchall()
result1 = cursor.fetchone()
#获取多行[参数可以设置指定返回数量]
result2 = cursor.fetchmany(整型)
#获取所有
result3 = cursor.fetchall()
操作数据
#提交，保存新建或修改的数据，如果是查询则不需要
conn.commit() # 写在execute()之后
conn.commit() # 写在execute()之后
0 条评论
下一页
