python--数据库的简单使用

– 数据库总结

一.数据库的操作 (之前的操作)

-- 链接数据库
    mysql -uroot -pmysql  # root 为用户名，mysql 为密码
-- 查看所有的数据库
    show databases;
-- 创建数据库
    create database python17 charset = utf8;
-- 使用数据库
    use python17;
-- 删除数据库 (谨慎使用)
    drop database python17;
-- 查看当前使用的数据库
    selcet database();
-- 退出
    exit/quit

-- 查看数据库的所有的表格
    show tables;
--显示时间
    selcet now();

二.数据库中表的结构的操作
– 创建数据库中的表
– int unsigned 无符号整形
– auto_increment 表示自动增长，一般用在主键上（id）
– not null 表示不能为空
– primary key 表示主键
– default 默认值
–gender (enum)、cls_id)
– create table 表名 （字段 类型 约束[长度]，字段 类型 约束[长度]）;
create table test (id int unsigned primary key auto_increment,name varchar(50) not null,age int not null )
– 创建 students 表(id、name、age、high (decimal)、
create table students(id int unsigned primary key auto_increment,name varchar(50) not null,age int unsigned
,high decimal(4,2),gender enum(“男”,”女”,”中性”,”妖”,”保密”) default “保密”,cls_id int unsigned);

-- 创建数据库表 students
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
 );

-- 查看表的结构
desc test;


-- 查看表的创建语句
show create table 表名；
-- 修改表（对一些字段的操作）alter 的使用
-- 添加字段 mascot（吉祥物）
alter table 表名 add mascot varchar(30);
alter table test add mascot varchar(30);

-- 修改字段（不重命名版）
alter table 表名 modify mascot varchar(100);
alter table test modify mascot varchar(100);

-- 修改字段，重命名版
alter table 表名 change 原名 新名 类型及约束
alter table test change mascot jxw int;

-- 修改表格-删除字段
alter table 表名 drop 列名；
alter table test drop jxw;


-- 删除表格
-- drop table 表名
-- drop database 数据库
drop table test;
drop database test;

-- 数据库备份与回复（了解）
mysqldump -uroot -p 数据库名 > python.sql;

mysql -uroot -p 新数据库名 < python.sql;  # 注意导入的时候需要先要创建数据库

– 修改表的结构 （重点是思路）
– 创建表格的格式
create table if not exists 表名 (
id int unsigned primary key auto_increment, 这个是主键
name varchar(40) not null); 这个名称

--第一步 创建 "商品种类表" -goods_cates
--id跟名称
create table goods_cates(id int unsigned primary key auto_increment,name varchar(50) not null);

--第二步 创建 "商品品牌表" -goods_brands
--id跟名称
create table goods_brands(id int unsigned primary key auto_increment,name varchar(50) not null);

三. 数据库中表的字段的操作

（一). 增 加 （insert）
– 全列插入
insert into [表名] values(…);
– 主键字段 可以用0 null default 来占位
insert into classes (插入的字段) values (插入的值)；
insert into classes (id ,name) values (0,’oldyang’);

-- 部分插入
insert into 表名（列1，列2....） values (值1，值2)；

-- 批量插入
select cate_name from goods group by cate_name;
insert into goods_cates(name) (select cate_name from goods group by cate_name);

（二）. 删 除 (delete, 物理删除)

-- delete from 表名 where 条件；
delete from classes where id = 10;

（三）. 修 改 （update）

-- update 表名 set 列1 = 值1，列2 = 值2 ... where 条件;
update students set gender = '',name = '' where id = 10;

-- 全部修改
update students set name = 'oldyang';
-- 按照条件修改
update students set name = '123',gender = '女' where id = 5;

-- 更新种类信息成种类信息表中的id
select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;
# 如果是查询出来的表必须把 select * from去掉
update (goods inner join goods_cates on goods.cate_name = goods_cates.name ) set goods.cate_name = goods_cates.id;

（四）.查 询 (selcet) select 特辑

-- 查询所有的列
 selcet * from 表名；
 selcet * from classes;

-- 查询指定的列
selcet 列1，列2 ... from 表名；

-- 可以使用 as 为列或表指定别名
selcet 字段 as 别名，字段 as 别名 from 数据表；
select name as n  from students as s;

-- select 表名.字段 ，表名.字段 from 数据表；(多表合并时，更加的精确)
select students.id , students.name from students;

-- 分组（group by）,分组功能更加的强大， 消除重复行
select gender from students group by gender ;
select distinct gender from students;
-- 计算每种性别中的人数
select gender,count(*) from students group by gender;
-- group_concat(...) 查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;

-- having(注意having和group by 连用 having后通常也要跟 聚合函数)
-- 查询平均年龄超过30岁的性别，以及姓名
select gender,avg(age),group_concat(name) from students group by gender having avg(age) > 30;



-- 条件查询 ,比较运算符 < > != >= <= and or not 
select ... from 表名 where ... ;
select * from students where age > 18;

-- 逻辑运算符
select * from students where age > 18 and age < 40;
select * from students where age > 18 or height >= 180;
select * from students where not (age > 18 and gender = '女');
select * from students where not age > 18 and gender = '女';


-- 模糊查询 (where name like 要查询的数据)
-- like 
-- % 替换任意个
-- _ 替换一个

select * from students where name like "小%"；
select * from students where name like "%小%";
select * from students where name like "__";  # 名字为两个字的
select * from students where name like "__%";  # 查询名字至少为两个的

-- 范围查询 (in)  in(1, 3, 5) 表示在一个非连续的范围内
select * from students where age in (18,34);
-- not in 不在这个非连续的范围内
select * from students where age not in (18,34);
-- between ... and ... 表示在一个来连续的范围内
select * from students where age between 18 and 34;
-- not between ... and ... 表示不在一个连续的范围内
select * from students where age not between 18 and 34;

-- 空判断 判空 is null
select * from students where height is null;  # 查询身高为空的信息
-- 判断为非空的 is not null
select * from students where height is not null;

-- 排序 
-- order by 字段、
-- asc  从小到大的排列，升序
-- desc 从大到小的排序 ，降序
-- 排序有优先级，第一优先级最高
select * from students where age between 18 and 34 and gender = "男" order by age asc;
select * from students where age between 18 and 34 and gender = "女" order by height desc,age asc;

-- 聚合函数
-- count 总数
-- max 最大值
-- min 最小值
-- sum 求和
-- avg 求平均值
-- round (数据,保留的小数位)
select count(*) from students;
select max(height) from students where gender = "女";
select min(height) from students where gender = "女";
select sum(age) from students;
select avg(age) from students;
select sum(age)/count(*) from students;
select round(avg(height),2) from students where gender = "男";

-- with rollup 汇总的作用(了解)
--select gender,count(*) from students group by gender with rollup;
select gender,count(*) from students group by gender with rollup having count(*) > 2;

– 分页 limit start（开始的位置） ,count（每页的个数）
– 起始位置:(页数-1) * 每页的个数
– 限制查询出来的数据个数
– 查询前5个数据
select * from students limit 0,5;
– 每页显示2个，第1个页面
select * from students limit 0,2;

– 连接查询
– inner join … on
– select … from 表A inner join 表B;
– 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;
select * from students inner join classes on students.cls_id = classes.id where gender = “女”;
–三个条件
select * from 表名 where # 数据的条件
select 分组字段 from 表名 group by 分组字段 having # 分组的条件
select * from 表A inner join 表B on 两张表连接的条件;
– 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
select students.*,classes.name from students inner join classes on students.cls_id = classes.id;
– left join(左连接)
– 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
– right join on （右连接）
– 将数据表名字互换位置，用left join完成
select * from classes right join students on students.cls_id = classes.id;

– 子连接
# 第一步应该要知道平均身高是多少
select avg(height) from students;
# 第二步进行判断
select * from students where height >”172.076923”;
select * from students where height >(select avg(height) from students);

四. 外键的使用

– 向goods表里插入任意一条品牌数据”老王牌拖拉机”
– 你会发现我们外键不能创建,因为没有对应的品牌

-- 约束 数据的插入 使用 外键 foreign key
-- alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);

alter table goods add foreign key(brand_id) references goods_brands(id);

-- 创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)
-- foreign key (主表的外键) references 外键表(主键)
create table if not exists goods_key(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(brand_id) references goods_brands(id)

);

– 如何取消外键约束
– 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
–show create table goods;
show create table goods;

-- 获取名称之后就可以根据名称来删除外键约束
--alter table students drop foreign key students_ibfk_1;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop key brand_id;

总结:在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

五 . 事务（ACID）
– 开启事务
begin：
或 start transaction;

-- 四大特性：
            原子性  一致性  隔离线（用update可以隔离，需要用commit提交）  持久性

-- 回滚(rollback)
    第一步 打开 终端1 begin
    第二步 终端1 update 表名 set 字段="xxx" where ...;
    第三步 rollback 数据返回最开始的原始值

-- 注意 
    -- innodb能使用事物
    -- 使用python操作数据库的时候 默认开启事物的 
    -- 但是python对数据库进行增删改的时候 需要手动commit

    -- 使用终端操作数据库(也就是mysql的客户端)的时候 也是默认开始事物的
    -- 只是在回车确认操作的时候 终端会默认的commit 所以我们不需要commit

六 . 索引

-- 索引
    -- 注意
    -- 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。
    -- 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，
    -- 对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

    -- 建立索引会占用磁盘空间


-- 索引最主要解决的问题
    -- 当数据非常庞大时,并且这些数据不需要经常修改,为了加快查询速度,我们会使用索引

-- 创建一张表
    create table test_index(title varchar(10));
-- python插入100000条数据 (可以在python程序中完成这一步操作)


--测试步骤
    1. 开启运行时间监测：
        set profiling=1;
    2. 查找第1万条数据ha-99999
        select * from test_index where title='ha-99999';
    3. 查看执行的时间：
        show profiles;
    4. 为表title_index的title列创建索引：
        create index 索引名称 on 表名(字段名称)

            create index my_index on test_index(title);
    5. 执行查询语句：
        select * from test_index where title='ha-99999';
    6. 再次查看执行的时间
        show profiles;

    --查看索引
        -- show index from 表名;
        show index from test_index;

    --删除索引

        ---drop index 索引名 on 表名;
        drop index my_index on test_index;

七 . 视图

八 . 权限管理