数据库操作

数据库
1、定义 存储和管理数据的
2、分类
关系型数据库 mysql Oracle
非关系型数据库 MongoDb Redis

3、特点
持久化存储
读写速度高
保证数据的有效性

4、sql 语句
用来管理 关系型数据库的
对数据的操作
insert 增加
delete 删除
select 查询 查看
update 改
对数据库和表的操作
create 创建
drop 删除库

不区分大小写
以英文的 ; 结尾
mysql
属于Oracle 公司
特点：
免费 开源 支持大型的数据库 使用标准的sql语言 支持多个操作系统 ，给多种编程语言提供接口

mysql 的安装

mysql 语句

mysql -uroot -p1234 连接 mysql
退出 exit

select now(); 查看当前时间
select version(); 查看当前数据库版本号

show databases; 查看当前所有的数据库
create database + 库名 charset=utf8; 创建数据库
use 库名 使用数据库
drop database + 库名 删除数据库

创建表

数据类型
整数 int
小数 decimal(6,3) 123.456
字符串 char varchar
枚举 enum
日期 date time datetime

常见的约束
primary key 主键
foreign key 外键
unique 惟一
not null 非空
default 默认
auto_increment 自增

------------------------------------------------------------------------------
创建表的命令
create table + 表名(字段名 约束)
查看创建表的语句
show create table + 表名
删除表
drop table + 表名
查看数据库里的所有表
show tables ;
给表里添加字段
alter table 表名 add 字段名 类型 约束
修改字段类型
alter table 表名 modify 字段名 类型 约束
删除字段
alter table 表名 drop 字段名
修改字段名和类型
alter table 表名 change 旧字段名 新字段名 类型 约束
重名命名表
alter table 旧表名 rename 新表名

-------------------------------------------------------------------

数据的增删改查
1、查
查询所有数据
select * from 表名
查询指定的列
select 列名1，列名2 from 表名
2、插
全列插入
insert into 表名 values(),(),(),();
部分插入
insert into 表名(列名1，列名2) values()
3、改
update 表名 set 列名=新内容 where 条件
4、删除
delete from 表名 where 条件

-------------------------------------------------------------------------------------

外键 foreign key
是用来连接两张表的
确保表的完整性和一致性
外键在哪张表 那张就是子表
外键和关联表的主键类型必须相同
主表必须先创建
创建外键的两种方式
1、在创建表的时候创建
foreign key 子表(列名) references 主表(主键)
2、在创建表之后创建外键
alter table 子表 add foreign key(列名) references 主表(主键)
删除外键
1、查看 外键名称
show create table + 表名
2、删除
alter table 子表名 drop foreign key 外键名称
------------------------------------------------------------------------
11.21

as 起别名
1、 给字段名 起别名
select id as i,bookname as b from books;
2、给表起别名
select b.id,b.price from books as b;

distinct 去掉重复行
select distinct bookname,price from books;

条件查询 where
1 比较运算符
> < >= <= != =
2、逻辑运算符
and 满足两个条件才输出
or 满足任意一个条件才输出
not 相反的输出

3、模糊查询

like
select * from books where author like '王%';
% 查询多个字符
select * from books where author like '王_';
_ 查询一个字符
4、范围查询
连续的
between and
select * from books where id between 4 and 7;
不连续的
in
select * from books where id in (6,8);

5、判断空值
空 is null
select * from books where price is null;
非空 is not null
select * from books where price is not null;

6、排序
order by desc 降序 asc 升序(默认)
select * from books where price<70 order by price desc;

7、分页
limit
select * from 表名 limit start,count

8、聚合函数
1、count(*) 总行数
select count(*) from books;
2、sum 求和
select sum(price) from books;
3、avg 平均值
select avg(price) from books;
保留三位小数
select round(avg(price),3) from books;
4、最大值 max
selcet max(price) from books;
5、最小值 min
selcet min(price) from books;
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用
9、group by 分组
select gender from students group by gender
2、group by + group_concat(字段名)
select gender,group_concat(字段名) from students group by gender
3、group by + 聚合函数
4、group by having
having 和 where 用法是一样的 都是添加查询
分组 条件查询 聚合函数 排序
select gender,avg(age) from students group by gender having avg(age)>25 order by avg(age) asc;

10、自关联
select * from 表1 inner/left/right/ join 表2 on 表1.列 = 表二.列
内连接
select * from 表1 inner join 表2 on 表1.列 = 表二.列
两张表的交集 共同拥有的部分
左连接
查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
select * from 表1 left join 表2 on 表1.列 = 表二.列
右连接
查询的结果为两个表匹配到的数据，右表特有的数据，对于右表中不存在的数据使用null填充
select * from 表1 right join 表2 on 表1.列 = 表二.列
11、连接查询

 

12、子查询
定义 在一个select语句中嵌入另一个select 语句
主查询 是第一条select 语句
主查询和子查询的关系
子查询必须是嵌套在主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
分类
标量子查询 返回是一行一列
查询班级学生的年龄大于平均年龄的学生
select * from students where age >( select avg(age) from students);
列级子查询 返回是一行多列
select name from classes where id in( select cls_id from students);
行级子查询 返回是多行一列
select * from students where (height,age)=(select max(height),max(age) from students);

13、表设计
数据库设计之三范式
1、列的原子性 即一列对应一条数据
第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主 键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不 能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

14、索引
15、事务
16、视图
17、pycharm连接MySQL

需求
创建一个英雄联盟LOL库
创建一个hero 表

id 主键 自增 非空
name
attack 小数 decimal
move 小数
性别 enum
日期 datetime

插入数据

需求
首先对出版社进行分组
查询每一个出版社的平均价格大于15的
进行降序排序

 

mysql -uroot -p
database 数据库
create 创建
drop 删除
table 表
select 查
insert into 插入
use 使用
int 数字
varchar 字符串
enum 枚举
decimal 小数
default 默认
primary key 主键
auto_increment 自增
not null 非空
values 值
datetime 日期

视图：
定义 查询语句返回的集 他是一个虚表 不能改变表里面的数据
作用 方便查询 减少SQL语句，增加可读性
创建 create view 视图名称 as select语句；
查询 select * from 视图名称
删除 drop view 视图名

事务
四大特性：原子性
一致性
隔离性
持久性
开启事务： begin
要插入的内容： insert into 表名 values（内容）
提交事务： commit
要关闭事务需要回滚 先开启begin 再结束rollback

修改mysql密码

一、在windows 下修改密码
1、进入mysql
2、use mysql
3、update user set password = password('1234') where user='root'
4、关闭mysql
5、重启电脑

 

二、在linux下修改密码 密码一定要记住
1、进入mysql
2、use mysql
3、update user set authentication_string = password('1234') where user='root'
4、关闭mysql
5、重启虚拟机

 

索引：
相当于字典的目录
目的： 方便查询 提高效率
查看索引命令
show index from 表名
创建索引
create index 索引名称 on 表名（字段名）
删除索引：
drop index 索引名称 on 表名
注意：
1，索引经常建立在经常查询的表中经常修改和插入的表不建议使用
2，索引会占用磁盘空间
3，不建议创建太多的索引

 

Connection对象
用于建立与数据库的连接
创建对象：调用connect()方法
conn=pymysql.connect(参数列表)
参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306，可以不写?
参数db：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，建议是'utf8'，要求与数据库创建时指定的编码一致。
conn对象的方法
? ?当连接成功后可以做如下操作：
close()关闭连接
commit()事务，所以需要提交才会生效;
rollback()事务，放弃之前的操作
cursor()返回Cursor对象，用于执行sql语句并获得结果
Cursor对象
创建对象：调用 Connection对象的cursor()方法 cursor=conn.cursor()
执行sql语句
Cursor对象的方法
close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数。
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组。
next()执行查询语句时，获取当前行的下一行
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

pycharm 连接 mysql
1，需要导包 pymysql
2，建立连接 创建游标 准备SQL语句 执行语句 关闭游标 关闭连接