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 条评论
下一页