SQL回顾

数据库的本质是一种特殊的文件

数据库是由数据表组成的,数据表是真正存储数据的

数据库客户端-->SQL语句-->数据库服务器-->数据库文件

表与表之间存在关联的数据库称为关系型数据库 (sql-server my-sql(5.1, 5.6, 5.7) Oracle(10g, 11g) db2)
非关系型数据库(mongodb redis)

唯一的标识一行信息的特殊的列 主键(primary key)不允许重复,不允许为空
当前表中的某个字段来自与另一张表中的主键的值,那么这个字段称之为 外键(foreign key)
列 字段 同一类的信息
行 记录 对象的详细信息
记录的集合组成数据表,数据表的集合组成数据库文件

DQL 数据查询语言 select
DML 数据操作语言 insert, update, delete
DDL 数据定义语言 create, drop

数据库服务端的安装 sudo apt-get install mysql
数据库客户端的安装 sudo apt-get install mysql-client

将程序的一些配置 写到配置文件中 修改的时候不用修改程序只需要修改配置文件即可(可以更好地保护程序源码)

decimal() 占5位小数占2位

enum("情况1","情况2",..) 把所有可能出现的情况都列举出来

体积比较大的文件不存在数据库中 存在第三方网站或者文件存储系统中 数据表中存储图片的url(如图片,音视频)(这个url是外键)

文件存储系统 (GFS TFS fastdfs--小文件)

数据的使用原则 合适为宜

数据约束
(not null,primary key,auto increment,unsigned 等 )

数据约束和数据类型 都保证数据的完整性

命令行连接数据库
mysql -u root -p-->需要输入密码
mysql -u -p密码

命令行数据库操作基本语句
select now();显示时间
select datdabases;查询所有数据库
create database 数据库名(不能重名) charset=utf8;创建数据库 数据库中需要汉字或字符 需要指定编码格式
alter 数据库名 character set 编码格式; 修改已创建数据库的编码格式
select datdabase(); 查看当前使用的数据库
use 数据库名; 使用一个数据库
drop database 数据库名; 删除数据库 谨慎使用最好别用

命令行表的操作

create table 表名(字段 约束,字段 约束,...); 创建表

desc 表名; 查看表结构
show tables; 查看数据库中有那些表
show create table 表名; 查看创建表的语句

select * from 表名; 查看表中的所有数据

insert into 表名 values(0,1字段的值, 2字段的值, 3字段的值,..);按照约束向表中添加一条数据(全部插入)
insert into 表名(字段1,字段2..)values(值1,值2..)----------------------------------------(部分插入)
insert into 表名(字段1,字段2..)values(值1,值2),(值1,值2),(值1,值2)... 一次插入多条数据--(多行插入)
insert into 表名 values(所有值),(所有值),... 一次插入多行完全信息-------------------(全列多行插入)

alter table 表明 drop 字段名 删除表中的字段

delete from 表名; 删除表中所有数据
truncate 表名; 清空表 主键也抹去至0`
drop table 表名; 删除表,表结构也也删除 慎用 不要用,没学过
rename table test to test1; 修改表的名字

insert into 表名 values(0,1字段的值, 2字段的值, 3字段的值,.);按照约束向表中添加一条数据(增)

update 表名 set 字段="新值",字段="新值",... where id=需要修改的人的id 更新表数据------(改)

select 字段 as 别名,... from 表名;-----------------------------------------------------(查)

delete from 表名 where 条件; 删除条件指定的表中的数据 ---------------------------------(删)

查的强化拓展

给表起别名 select 表别名.字段1,表别名.字段2,.... from 表名 as 表别名;
如果给表起了别名,但是非要用原表名那是不行的.!!

 

去重 select distinct 字段 from 表; 查询字段中的数据并去重

条件查询
select 字段1,字段2 from 表名 where 条件; >,>=,=,<,<=,(!=,<>) <>不通用 ----------算术运算符
select * from 表名 where 条件1 and 条件2; and,or,not,----------------------------逻辑运算符
select * from 表名 where 字段 like "条件"; %-->任意个字符,_一个任意字符 ---------模糊查询
select * from 表名 where 字段 rlike "^小.*月$";"^以什么开头 .*任意个字符 以什么结尾$"------正则
select * from 表名 where 字段 in (18,30,45); 非连续范围,可取反--------------------范围查询
select * from 表名 where 字段 between 值and 值;连续范围,可取反 -------------------范围查询
select * from 表名 where 字段 not between 值and 值;连续范围取反 ------------------范围查询
select * from 表名 where 字段 is null;判断某个字段的值为空------------------------空判断
select * from 表名 where 字段 is not null;判断某个字段的值不为空------------------判断不为空

排序
select * from 表名 where (age between 18 and 30 ) and gender = "女" order by age desc;
order by 多个条件时优先满足前面的条件
asc 升序(默认)
desc 降序

聚合函数
select count(*) as 别名 from 表名 where 条件; count() 总数
select max(字段) as 别名 from 表名; max()最大值min()最小值
select sum(字段) from 表名; sum()求和
select avg(字段) from 表名; avg()求平均
select round(avg(字段),保留的小数位数) from 表名; avg()求平均round()保留小数位数(四舍五入)

分组
select 字段1,字段2 from 表名 group by 字段1,字段2;按照字段1字段2进行分组
select gender,count(*) from students group by gender;查看分组中的人数

group_concat组连接函数
select gender,count(*),group_concat(name) from students group by gender;显示分组中具体的人物信息

having
select gender,group_concat(name,age) from students group by gender having avg(age)>30;查询平均年龄超过30岁的性别，以及姓名

rollup 汇总
select gender,count(*) from students group by gender with rollup;按性别分组查看每组的人数和总人数

limit 起始位置,每页显示的数据个数 分页显示
limit (第n页-1)*每页要显示的个数,每页要显示的个数
select * from students limit 0,5; 第一页,显示五个数据
select * from students limit 5,5; 第二页,显示五个数据
select * from students limit 10,5;第三页,显示五个数据
limit 必须 放置在语句最后

链接查询
外链接
左外链接
以左表为主没有查询到的数据补null
select * from students left join classes on students.cls_id = classes.id;
右外链接
以右表为主没有查询到的数据补null
select * from students right join classes on students.cls_id = classes.id;

内链接 两张表有交集
inner join on
select * from 表名inner join 表名; 笛卡尔积;
select * from 表名1inner join 表名2 on 表名1.字段1=表名2.字段2;
select students.* ,classes.name from students inner join classes on students.cls_id = classes.id;

select students.name as 姓名 ,classes.name as 班级 from students inner join classes on students.cls_id= classes.id group by classes.name desc;
+-----------+--------------+
| 姓名 | 班级 |
+-----------+--------------+
| 小月月 | python_02期 |
| 刘亦菲 | python_02期 |
| 刘德华 | python_02期 |
| 程坤 | python_02期 |
| 凤姐 | python_02期 |
| 周杰伦 | python_01期 |
| 黄蓉 | python_01期 |
| 王祖贤 | python_01期 |
| 小明 | python_01期 |
| 彭于晏 | python_01期 |
+-----------+--------------+
select c.name,s.name
from students as s inner join classes as c on s.cls_id = c.id order by c.name ,s.id asc;

多表联合查询
select * from students , classes where students.cls_id = classes.id;

行子查询
select 语句中嵌套select 语句
select * from students where age >(select avg(age) from students);
大于平均年龄的人

列子查询
select id from classes where id = 1;
select * from students where students.cls_id in(select id from classes);
select * from students where students.cls_id in(select id from classes where id =1);

自关联 将多张表合到一张表中本质就是一张表
可以解决无限分表的问题 层次关系分明时使用
select * from -----areas as county -----inner join----areas as city----on----county.pid = city.aid and city.atitle="武汉市";

 

拆分表
字段更名时使用 便于表的维护

流程
1建立空表good_cates 两个字段 id name
create table goods_cates(id int unsigned not null primary key auto_increment,name varchar(40) not null);

2把原表里的字段cate_name的数据查询出来插入到新表good_cates中
###select cate name from goods group by cate_name;
insert into goods_cates(name) select cate_name from goods group by cate_name;

3通过good_cates表的数据更新原表的数据, 把新表的id替换给原表的字段的值
selsect * from goods inner join goods_cates on goods.cate_name = goods_cates.name;
update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;
4修改原表的字段的类型 和新表的主键的类型一致 并且要把原表字段名改为_id
alter table goods change name cate_name cate_id int unsigened notnull;-->改名

 

1 建立空表goods_brands 两个字段 id name 插入数据
create table goods_brands (id int unsigned not null primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name;

2 同步数据
select * from goods inner join goods_brabds on goods.brand_name = goods_brands.name:

3 改变类型
update (goods_brands inner join goods on goods_brands.name = goods.brand_name) set goods.brand_name = goods_brands.id;

4 修改表结构
alter table goods change brand_name brand_id int unsigned not null;

外键的使用 限制非法数据的插入 使用外键导致数据库更新数据的性能大大降低
添加外键约束
alter table 要修改的表名 add foreign key (要修改的表的外键字段) references 参考表(主键字段);
创建表的时候就可以添加外键约束
取消外键约束
show create table 表名; 得到外键名称
alter table goods drop foreign key 外键名称;

不使用外键 添加约束的方法
使用应用程序 获取表中的id 使用 for 循环 判断新插入的数据是否合法(在内存中判断)

 

数据库编程
流程
开始--->创建连接对象---->创建游标cursor对象--->执行sql--->关闭cursor对象--->关闭connection--->结束

创建连接对象
conn = connect(host="localhost",port=3306,user="root",password="mysql",database="jing_dong",charset="utf8")

host: 连接的MySQL主机
port:MySQL主机的端口默认3306
database:数据库的名称
user:连接的用户
password :用户的密码
charset: utf8

调用Connection对象的cursor()方法
cur =conn.cursor()

定义要执行的sql语句
sql = "sql语句"

通过连接对象获取游标对象 cursor
游标对象=连接对象.cursor()

使用游标对象操作sql语句
sql语句的结果=游标对象.execute(sql语句)
print(sql语句的结果)

通过游标对象获取数据
游标对象.fetchall()获取所有结果集 格式是元组套元组
游标对象.fetchone()获取下一条结果集
游标对象.fetchmany(要获取的行数)获取多行结果集 格式是元组套元组

如果sql语句更新了数据库中的数据需要提交
conn.commit()

游标对象 cur.close()
关闭连接 conn.close()
提交数据 conn.commit()
撤销数据 conn.rollback()

sql注入
sql语句不进行过滤 通过非法语句可以攻进数据库获取所有数据
通过sql注入 where后的条件语句恒为真
语法
' or 1=1 or '1
' or 1=1 or '1== True
防止sql注入
select * from goods where name = %s;---设置参数化列表机制
使用execute()函数自己拼接sql语句
execut(sql,[录入的数据])

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

数据库恢复
首先创建 新数据库名 设置编码
执行 数据库备份的代码
mysql -uroot –p 新数据库名 < python.sql

> 重定向覆盖写入

 

视图
把复杂的aql语句封装起来的虚表,在物理内存中并不存在 可以直接对视图进行查询

提高重用性,像函数
对数据库进行重构更改,不影响程序运行
提高安全性,对用户展示指定字段,屏蔽重要的其它字段
让数据更加清晰

创建视图
create view v_视图名 as select 语句;
创建视图时不允许有相同的字段名,需要给字段起别名
原表的数据发生变化,视图里的内容也会发生相应的变化

视图可以指向新的数据 就是 修改视图
alter view v_视图名 as 新的select语句;

删除视图
drop view v_视图名;

事务 InnoDB引擎的数据库才支持事务
Transaction 作为一个基本工作单元执行的一系列sql语句要么全部执行成功,要么全部失败
四大特性
原子性 一整个单位不能拆分
一致性 客户端的操作的结果一致
隔离性 多个客户端都开启事务操作同一数据,一方不提交就会阻塞,另一方就无法操作数据,是行级锁机制
持久性 一旦提交所作的修改会永久保存到数据库

开启事务
begin 或者 start transaction;
提交事务 没有提交之前数据是在缓存区,一旦提交数据就会保存到数据库文件中
commit
回滚事务 在没有提交之前是可以回滚地,但是提交之后是无法回滚的,回滚是无效地
rollback
mysql 客户端默认是开启事务,默认提交
pyrthon程序中默认开启事务,需要手动提交

 

索引
查看表中是否存在索引
show index from 表名;
创建索引 创建索引时会创建B树数据结构所以时间比较慢
create index 索引名 on 表名(字段名);
删除索引
drop index 索引名 on 表名;
查询
set profiling =1; 设置时间监测
查找数据地sql语句
show profiles; 查看用时时长
索引会占用磁盘空间

 

MySQL用户管理
创建特定的账户,给予这个账户特定的权限
创建用户 并授予权限
使用root账户登陆
grant 权限列表 on 数据库名 to "用户名" @ "访问主机" identified by "新创建的账户地密码";
访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问
刷新权限：flush privileges;
给用户配置权限
grant 权限 on 数据库 to 用户名@localhost with grant option;
修改用户密码
update user set authentication_string=password('新密码') where user='用户名';
修改用户名
rename '用户名'@'主机' to '新用户名'@'主机';
回收权限
reveke 权限 on 数据库 from "用户名"@"主机";
删除用户
drop user "用户名"@"主机";
delete from user where user = "要删除的用户名";

每个权限修改命令执行之后需要刷新权限
flush privileges;

数据库设计

三范式
第一范式---列的原子性, 列不能拆分
第二范式---表必须有主键,非主键字段必须依赖主键,而不能只依赖于主键的一部分
第三范式---非主键字段必须直接依赖于主键,不能存在传递依赖A-->B-->C-->主键 传递依赖容易产生数据冗余
范式必须同时满足 不满足就拆表

E-R实体关系模型
一对一 任何以访添加字段表示两张表关系都可以

一对多 需要在多的一方添加字段表示两张表的关系

多对多 需要添加中间表,表中添加两个字段,分别是两张表的主键

 

数据库主从配置

策略型服务器 负载均衡
主服务器存储数据 从服务器用来备份
写数据往主服务器写 --> 写到二进制日志文件中 --> 从服务器读取日志文件数据保存在本地保持和主服务器数据一致 -|-|-|- 读取数据时从 从服务器读取 达到 读写分离 ,负载均衡,数据备份

主从配置的机制
数据库备份
数据库恢复
主服务器的配置文件mysqld.cnf 的 43 注释掉 84行85行 不能注释 84server-id 不能与从服务器一样
从服务器配置文件mysqld.cnf 84行的log_bin注释掉
把主服务器数据导入到从服务器中
创建一个同步账户slave
获取主服务器的二进制配置文件信息
从服务器连接主服务器 开启同步 start slave
查看同步状态 show slave status /G;

 

 

 

 

 

 

 

 

命令行中sql语句必须要以分号结尾,来表示语句结束

ctrl+a 行首
ctrl+e 行尾
数据库引擎 InnoDB支持事务.行级锁, MyISM 不支持

添加字段(列名) ALTER TABLE people ADD COLUMN name VARCHAR(100) DEFAULT NULL COMMENT '姓名'