漫话Python

数据库知识点

2019-07-29  本文已影响0人  DayBreakL

数据库知识点

一、数据库基本概念

数据库是什么

数据存储
1.人工管理阶段

人工阶段的数据存储使用起来不方便，不便于查询、共享、保存

2.文件系统阶段

将数据存储在磁盘上，数据都存在文件中，查询很不方便

3.数据库系统阶段

长期存储在计算机内，有组织的数据集合，数据按照不同的分类存储到不同的表中，查询非常方便

RDBMS

Relational Database Managment System
关系型数据库系统
通过表来表示关系

关系型数据库核心元素

SQL

Structured Query Language
结构化查询语言

在数据库中进行操作的语言，称为sql，结构化查询语言，当前关系型数据库都支持使用sql语言进行操作。

MySQL

是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后被Sun公司收购，Sun公司后被Oracle公司收购，所以现在是Oracle旗下的产品。

特点：开源 免费 使用范围广 支持多平台

二、MySQL的安装与使用

MySQL下载中，已包含服务端和客户端

Linux平台下安装

Windows平台下安装

MacOS平台下安装

1.从MySQL官网下载安装包

2.安装

3.启动MySQL服务端

4.修改密码

5.配置环境变量
因为mysql的可执行命令在/usr/local/mysql/bin目录下，而这个目录不在普通用户的环境路径下，因此每次执行时都需要输入完整路径，比较麻烦。因此需要将这个路径添加到环境变量中。

6.启动MySQL客户端
MySQL自带的客户端是命令行界面，其实前面已经操作过。

7.使用Navicat连接MySQL服务端

三、Navicat的使用

连接数据库

（1）选择数据库类型
服务端是什么数据库就选择什么数据库（MySQL）




（2）填写连接信息（到公司问开发索要）

数据库操作

数据表操作

数据类型与约束

数据库的备份与恢复

三、SQL语言

查询编辑器介绍

数据表操作

等同于： 
  drop table if exists hero;
  create table hero (
  id int unsigned primary key auto_increment,
  name VARCHAR(10),
  age int unsigned,
  height decimal(5,2),
  contact varchar(10) not null
    )

数据操作-增删改

增

格式一：所有字段设置值，值的顺序与表中字段顺序对应
insert into 表名 values (……);
说明：主键列自动增长，插入时要占位，通常使用0或者defualt或者null来占位

insert into newHero values (default,'小鲁班',15,140.55,'tony');
insert into newHero values (0,'小亚瑟',20,185.00,'tony');
insert into newHero values (null,'小守约',18,180.00,'herry');

格式二：指定字段设置值，值的顺序跟给出的字段对应
insert into 表名(字段名1，字段名2，……) values (字段值1，字段值2，……);

-- 添加一行数据 --
insert into newHero(name,contact) values('小文姬','relics');
-- 添加多行数据 --
insert into newHero(name,contact) values('小露娜','luna'),('小悟空','merry');
改

格式：update 表名 set 列1=值1，列2=值2……where 条件

-- 修改name为小鲁班的身高为135.00，年龄为12 --
update newHero set height=135.00,age=12 where name='小鲁班';
删

格式：delete from 表名 where 条件

-- 删除id为7的这条数据 --
delete from newHero where id=7;

逻辑删除：对于重要的数据 ，不能轻易执行delete语句进行删除，一旦删除，数据无法恢复，这时可以进行逻辑删除。
1.给表添加字段，代表数据是否删除，一般起名isdeleted，0代表未删除，1代表删除，默认值为0。
2.当要删除某条数据时，只需要设置isdeleted字段的值为1
3.以后查询数据时，只查询出isdeleted为0的数据

数据操作-查询

创建表

drop table if exists students;
create table students(
    studentNo varchar(10) primary key,
    name varchar(10),
    sex varchar(1),
    hometown varchar(20),
    age tinyint(4),
    class varchar(10),
    card varchar(20)
)

准备数据

insert into students values ('001','王昭君','女','北京','20','1班','340322199001249654'),
('002','诸葛亮','男','上海','20','2班','340322199002242345'),
('003','张飞','男','南京','24','3班','340322199003242045'),
('004','白起','男','安徽','19','2班','340322199004249999'),
('005','大乔','女','天津','18','3班','340322199005242343'),
('006','孙尚香','女','安徽','24','2班','340322199006241233'),
('007','小乔','女','山西','20','2班','340322199007246789'),
('008','百里飞','男','河南','25','2班','340322199008242345'),
('009','百里守约','男','河南','30','2班',''),
('010','妲己','女','贵州','20','1班',null);

查询所有字段

select * from 表名;

例：
select * from students;

查询指定字段

select 列1,列2,...from 表名;

--表名.字段名
select students.name,students.age from students;

--可以通过as给表起名
select s.name,s.age from students as s;

--如果是单表查询可以省略表名 
select name,age from students;

--用as给字段起别名
select name as 姓名,age as 年龄 from students;

--查询性别有哪几种
select distinct sex from students;

消除重复行

select dictinct 列1,...from 表名;
例：
--sex 去重
select distinct sex  from  students; 
--sex+age的组合去重
select distinct sex,age  from  students;
条件查询
select 字段1，字段2... from 表名 where 条件;
例：
select * from students where id=1;
排序查询
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...;

--查询所有学生信息，按年龄从小到大排列
select * from students order by age;
--查询所有学生信息，按年龄从大到小排列，年龄相同时，再按学号从小到大排列
 select * from students order by age desc,studentNo asc;
--查询所有学生信息，按姓名从a-z排序(对中文进行排序)
 select * from students order by convert(name using gbk);
聚合函数
--计数count(*)
  --查询学生总数
select count(*) from students;

--max(列)表示求此列最大值
  --查询女生的最大年龄
   select max(age) from students where sex='女';

--min(列)表示求此列最大值
  --查询1班的最小年龄
   select min(age) from students where class='1班';

--sum(列)表示求此列的和
  -- 查询北京的学生的年龄总和
   select sum(age) from students where hometown='北京';

--avg(列)表示求此列的平均值
  --查询女生的平均年龄
  select avg(age) from students where sex='女';
分组
--查询各种性别的人数
select sex,count(*) from students group by sex;
--查询各种年龄的人数
select age,count(*) from students group by age;
--查询各个班级学生的平均年龄、最大年龄、最小年龄
select class,avg(age),max(age),min(age) from students group by class;
--查询各班男女生人数
select class,sex,count(*) from students group by class,sex;
分页

获取部分行

分页

-- 需求分页, 每页3条数据 --
-- 总共有多少条 10条
select count(*) from students;
-- 10/3 获取总页数，共4页
-- 第一页 --3(1-1)
select * from students limit 0,3; 
-- 第二页 --3(2-1)
select * from students limit 3,3;
-- 第三页 --3(3-1)
select * from students limit 6,3;
-- 第四页 --3(4-1)
select * from students limit 9,3;
连接查询

准备数据


drop table if exists courses;
create table courses(
    courseNo int(10) unsigned primary key auto_increment,
    name varchar(10)
)
insert into courses values
('1','数据库'),
('2','qtp'),
('3','linux'),
('4','系统测试'),
('5','单元测试'),
('6','测试过程');

drop table if exists scores;
create table scores(
    id int(10) unsigned primary key auto_increment,
    courseNo int(10),
    studentNo varchar(10),
    score tinyint(4)
);

insert into scores VALUES
('1','1','001','90'),
('2','1','002','75'),
('3','2','002','98'),
('4','3','001','86'),
('5','3','003','80'),
('6','4','004','79'),
('7','5','005','96'),
('8','6','006','80');

select * from 表1 
left join 表2 on 表1.列=表2.列

--查询所有学生的成绩，包括没有成绩的学生（2表查询）
select 
    *
from 
    students stu
left join scores sco on stu.studentNo=sco.studentNo

对于左表不存在的数据用null填充

-- 查询所有学生的成绩，包括没有成绩的学生,显示课程名(3表查询)
select 
    stu.name,sco.score,cour.courseNo
from 
    students stu
left join scores sco on stu.studentNo=sco.studentNo
left join courses cour on cour.courseNo=sco.courseNo

select * from 表1 
right join 表2 on 表1.列=表2.列

为了便于练习，在此数据库加入两条数据：

insert into courses values
(0,'语文'),
(0,'数学')

--查询所有课的成绩，包括没有成绩的课程(2表查询)
select
    * 
from 
    scores sco  
right join courses cour on cour.courseNo=sco.courseNo;

--查询所有课程的成绩，包括没有成绩的课程，包括学生信息(3表查询)
select
    * 
from 
    scores sco 
right join courses cour on cour.courseNo=sco.courseNo
left join students stu on sco.studentNo=stu.studentNo;
-- 注意区分要让左边全显示还是右边全显示

练习

-- 查询所有学生的成绩 包括没有成绩的学生，显示课程名
select 
    stu.name,cour.name,score
FROM
    students stu
left join scores sco on stu.studentNo=sco.studentNo
left join courses cour on sco.courseNo=cour.courseNo;
自关联
编号 城市 上级编号
01 湖南省
02 长沙市 01
03 岳阳市 01
05 陕西省
06 西安市 05
07 咸阳市 05

准备数据

drop table if exists areas;
create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

insert into areas values
('130000','河北省',NULL),
('130100','石家庄市','130000'),
('130400','邯郸市','130000'),
('130600','保定市','130000'),
('130700','张家口市','130000'),
('130800','承德市','130000'),
('410000','河南省',null),
('410100','郑州市','410000'),
('410300','洛阳市','410000'),
('410500','安阳市','410000'),
('410700','新乡市','410000'),
('410800','焦作市','410000');

--查询一共有多少个省
select count(*) from areas where pid is null;
--查询河南省所有城市(自关联2次)
select 
  * 
from 
  areas pro,areas city 
where 
  pro.aid=city.pid and pro.atitle='河南省';

添加区县数据

insert into areas values 
('410101','中原区','410100'),
('410102','二七区','410100'),
('410103','金水区','410100');

--查找河南省所有区县(关联3次)
select * from areas pro,areas city,areas qu
where pro.aid=city.pid and city.aid=qu.pid and pro.attle='河南省';
子查询

主查询：

主查询和子查询的关系：

子查询的分类：

标量子查询

--查询年龄大于平均年龄的学生
--方法1：
  --1.求出平均年龄的值为22
select avg(age) from students;
  --2.求age大于22的学生
select * from students where age > 22;
--方法2:
select * from students where age > (select avg(age) from students);
  --ps:第一个select语句就是主查询，第二个select语句就是子查询

--查询王昭君的数据库成绩
select 
  score
from 
  scores 
where 
  studentNo=(select studentNo from students where name ='王昭君')
and courseNo=(select courseNo from courses where name='数据库');

列级子查询

--查询20岁的学生的成绩，要求显示成绩
--方法1:
  --1.求出age等于20的学号是001,002,007,010
  select studentNo from students where age=18;
  --2.求学号是005的成绩
  select score from scores where studentNo in(001,002,007,010);
--方法2:
  select score from scores where studentNo in(select studentNo from students where age);

行级子查询

--查询男生中年龄最大的学生信息
--方法1:标量子查询
  select max(age) from students; --30
  select * from students where sex='男' and age=(select max(age) from students);
--where后面条件都是且关系的，可以有另一种写法
  select * from students where (sex,age)=('男','30');
--方法2:行级子查询
    --1.返回sex,age（一行多列）
  select sex,age from students where sex='男' order by age desc limit 1;
    --2.
  select 
    * 
  from 
    students 
  where 
    (sex,age)=(select sex,age from students where sex='男' order by age desc limit 1);

表级子查询

--查询数据库和系统测试的成绩
  --方法1:
  select * from courses
  inner join scores on courses.courseNo=scores.courseNo
  where courses.name in('数据库','系统测试');  
  --先连接了两个表，再筛选，连接两表时，多了很多无效数据。
 --方法2:
  select * from scores a
  inner join  (select courseNo from courses where name in('数据库','系统测试')) b 
  on a.courseNo=b.courseNo;
  --直接连接筛选后的表，会快一些

子查询中特定的关键字

查询演练

-- 求所有学生的平均成绩,保留两位小数
select round(avg(score),2) as 平均成绩 from scores; 
-- 查询年龄大于平均年龄的学生，并按年龄降序排序
select * from students 
where age >(select avg(age) from students) order by age desc;
-- 查询年龄大于王昭君的学生，并按年龄降序排列
select * from students 
where age>(select age from students where name='王昭君') order by age desc;

数据分表

四、数据库高级

数据库设计

E-R模型
E-R模型的基本元素是：实体、联系和属性

2.实体A对实体B为1对多：在表中创建一个字段，存储表A的主键值（让"多"的一方的表来维护关系）


3.实体A对实体B为多对多：新建一个表C， 这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值。（让中间表C来维护关系）

命令行客户端

windows

mac

常用命令

备份

恢复

函数

流程控制

自定义函数

存储过程

存储过程，也翻译为存储程序，是一条或多条SQL语句的集合

视图

create view v_stu_sco_cour as 
select 
    stu.name,sco.courseNo,sco.score 
from 
    students stu
inner join scores sco  on stu.studentNo=sco.studentNo
inner join courses cour on sco.courseNo=cour.courseNo;

create view v_students as 
select name 姓名,age 年龄 from students;

事务

为什么要有事务

--开启事务
begin;
所有操作都成功了;
--提交
commit;

--开启事务
begin;
任意一步操作失败了;
--回滚
rollback;

mysql> select * from students where name in('大乔','小乔');
+-----------+--------+------+----------+------+-------+--------------------+
| studentNo | name   | sex  | hometown | age  | class | card               |
+-----------+--------+------+----------+------+-------+--------------------+
| 005       | 大乔   | 女   | 天津     |   18 | 3班   | 340322199005242343 |
| 007       | 小乔   | 女   | 山西     |   20 | 2班   | 340322199007246789 |
+-----------+--------+------+----------+------+-------+--------------------+

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set age=age+3 where name='大乔';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update students set age=age-3 where name='小乔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0 

mysql> commit；
Query OK, 0 rows affected (0.00 sec)

mysql> select * from students where name in('大乔','小乔');
+-----------+--------+------+----------+------+-------+--------------------+
| studentNo | name   | sex  | hometown | age  | class | card               |
+-----------+--------+------+----------+------+-------+--------------------+
| 005       | 大乔   | 女   | 天津     |   21 | 3班   | 340322199005242343 |
| 007       | 小乔   | 女   | 山西     |   17 | 2班   | 340322199007246789 |
+-----------+--------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)

索引

语法
explain
select * from test_index where title='test10000';
缺点

外键 foreign key

语法
-- 需要先获取外键约束名称
show create table stu;  --stu_ibfk_1
-- 获取名称之后就可以根据名称来删除外键约束
alter table 表名 drop foreign key 外键名称;
alter table stu drop foreign key stu_ibfk_1;

修改密码

use mysql;
--update user set authentication_string=password('新密码') where user='用户名';
--旧版本：update user set password=password('新密码') where user='用户名';
update user set authentication_string=password('123') where user='root';
--刷新权限
flush privileges;

忘记密码

  1. 配置mysql登录时不需要密码，修改配置文件

2.重启mysql，免密码登陆，修改mysql数据库的user表

use mysql;
--update user set authentication_string=password('新密码') where user='用户名';
--旧版本：update user set password=password('新密码') where user='用户名';
update user set authentication_string=password('123') where user='root';
--刷新权限
flush privileges;

3.改完之后，把配置文件改回去，删除skip-grant-tables，再重启mysql

mac忘记密码 实践有效

上一篇 下一篇

猜你喜欢

热点阅读