– 数据库总结
一.数据库的操作 (之前的操作)
-- 链接数据库
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;
七 . 视图
八 . 权限管理