Python——mysql 学习笔记
MySQL
图形界面操控MySQL
navicat
- 下载navicat
- 赋予执行权限
chmod +x navicat15-premium-cs.AppImage
- 安装运行
./navicat15-premium-cs.AppImage
- 链接本地数据库
- 14天试用期到期后可延长,删除主目录下的 .navicat64
rm -r ~/.navicat64
命令行控制数据库
base命令
# 登入数据库
mysql -uroot -p"密码"
# 创建数据库
create database 库名 charset=utf8;
# 查看数据库
show databases;
# 查看创库sql语句
show create database 库名;
# 用数据库
use 库名;
# 查看当前数据库
select database();
# 查看表
show tables;
表结构指令
# 创建表
create table 表名(id int auto_increment,
name varchar(50) not null unique,
sex enum('m','f') default 'm' not null,
age int unsigned default 18 not null,
hobby set('disc','book','music') default 'disc,book' not null,
primary key(id));
# 查看表属性
desc 表名;
# 查看创表sql语句
show create table 表名;
# 授权用户test3在任意计算机拥有数据库student的所有权限
grant all privileges on student.* to test3@‘%’
# 更新特权
flush privileges
# 删除表
drop table 表名;
### 添加字段 ###
# alter table 表名 add 字段名 数据类型 其他属性;
alter table student add birthday datetime not null;
### 修改字段 ###
#1、修改字段属性
# alter table 表名 modify 字段名 数据类型;
alter table persons modify age int;
#2、修改字段
# alter table 表名 change 旧字段名 新字段名 数据类型 其他属性;
alter table persons change sex gender varchar(10) Null default 'f';
数据结构指令
增
# 全列插入数据
insert into 表名 values(”数据1“,2);
# 部分列插入数据
insert into 表名(列名1,列名2) values(“数据1”,2);
# 全列多行插入数据
insert into 表名 values(”数据1“,2),values(”数据1“,2);
# 部分列多行插入数据
insert into 表名(列名1,列名2) values(“数据1”,2),(“数据1”,2);
删
# 物理删除,删除id为8的数据
delete from 表名 where id=8;
# 逻辑删除,添加标识字段is_del识别是否删除(1:删除 0:未删除)
# alter table 表名 add is_del tinyint default 0;
# 逻辑删除,删除id为8的数据
update table set is_del=1 where id=8;
改
# 修改id为3的数据
update 表名 set 列名1=”修改数据1“,列名2=”修改数据2“ where id=3;
查
# 查看所有列数据
select * from 表名;
# 查看指定列数据
select 列名1,列名2 from 表名;
# 更改列名查询 as
select 原列名 as 新列名 from 表名;
# 去重复查询 distinct
select distinct 列名1,列名2 from 表名;
where条件语句查询
语法:select * from 表名 where 条件;
比较运算符
比较运算符:< <= > >= !=和<>
# 查询id>=3的学生
select * from 表名 id>=3;
# 查询id!=3(id<>3)的学生
select * from 表名 id!=3;
逻辑运算符
逻辑运算符:and or not
模糊查询
模糊查询:like
‘%’ 任意多个字符,’_‘ 任意一个字符
# 查询姓黄的和姓陈的学生
select * from 表名 name like "黄%" or name like "陈%";
范围查询
范围查询:between…and…; …in…
# 查询id为3-8的学生
select * from 表名 where id>=3 and id<=8;
select * from 表名 where id between 3 and 8;
# 查询id是3,6,8的学生
select * from 表名 where id in (3,6,8);
# 查询id不是3,6,8的学生
select * from 表名 where id not in (3,6,8);
空判断
空判断:is null; is not null
# 查询体重数据为空的学生
select * from 表名 where weight is null;
# 查询体重数据不为空的学生
select * from 表名 where weight is not null;
高级查询
排序查询
排序查询:order by
desc 降序
asc 默认升序
# 查询按年龄降序,如果年龄相同按身高降序排序
select * from 表名 order by age desc,height desc;
分页查询
分页查询:limit start count
# 查询前三行年龄为18的学生
select * from 表名 where age=18 limit 0 3;
select * from 表名 where age=18 limit 3; # start 默认为0
# 查询第n页的数据,每页数据有m条
select * from 表名 where limit (n-1)*m m;
聚合函数
聚合函数不统计空值行数
函数 | 描述 |
---|---|
count(col) | 表示求指定列的总行数 |
max(col) | 表示求指定列的最大值 |
min(col) | 表示求指定列的最小值 |
sum(col) | 表示求指定列的和 |
avg(col) | 表示求指定列的平均值 |
round(avg(col),2) | 平均值保留两位小数 |
age(ifnull(age,0)) | 判断如果是空值以0替代,求平均数 |
# 统计男生与多少人
select count(id) from 表名 where gender="Man"
# 找出年龄最大的学生
select max(age) from students;
# 找出年龄最小的学生
select min(age) from students;
# 统计男年龄
select sum(age) from students where gender="Man";
# 统计学生平均值
# 1. 不统计空值
select avg(age) from students;
# 2. 统计空值,将空值设为0
select avg(ifnull(age,0)) from students;
select sum(age)/count(id) from students;
分组查询
分组查询:
group by 分组关键字
having 筛选过滤结果
# 在gender字段进行分组,查询每个分组的姓名信息
select gender,group_concat(name) from students group by gender;
# 统计不同性别的人的个数
select gender,count(*) from students group by gender;
# 统计不同性别的人的个数,并筛选出个数大于2的
select gender,count(*) from students group by gender having count(*)>2;
# 在gender字段进行分组,增加汇总记录
select gender,count(*) from students group by gender with rollup;
连接查询
-
内连接查询
语法:select 字段1,字段2 from 表1 inner join 表2 on 表1.字段1=表2.字段2select s.name c.name from students s inner join classes c on s.c_id = c.id;
-
左连接查询 (以左表为标准,没有的数据以null填充)
语法:select 字段1,字段2 from 表1 left join 表2 on 表1.字段1=表2.字段2select * from students s left join classes c on s.c_id = c.id;
-
右连接查询(以右表为标准,没有的数据以null填充)
语法:select 字段1,字段2 from 表1 right join 表2 on 表1.字段1=表2.字段2select s.name c.name from students s right join classes c on s.c_id = c.id;
-
自连接查询
语法:select 字段1,字段2 from 表1 inner join 表1 on 表1.字段1=表1.字段2
select a.id,a.title,b.pid,b.title from school a inner join school b on a.id = b.pid where b.title="金融学院";
子查询
语法:主查询 (子查询);
子查询是完整的sql语句,先执行子查询在执行主查询;
键表约束条件
约束条件 | 描述说明 |
---|---|
PRIMARY KEY (PK) | 标识该字段为该表的主键,可以唯一的标识记录,不可以为空UNIQUE + NOT NULL |
FOREIGN KEY (FK) | 标识该字段为该表的外键,实现表与表(父表主键/子表1外键/子表2外键)之间的关联 |
NOT NULL | 标识该字段不能为空 |
UNIQUE KEY (UK) | 标识该字段的值是唯一的,可以为空,一个表中可以有多 UNIQUE KEY |
AUTO_INCREMENT | 标识该字段的值自动增长(整数类型,而且为主键) |
DEFAULT | 为该字段设置默认值 |
UNSIGNED | 无符号,正数 |
ZEROFTLL | 使用0填充,例如0000001 |
外键 FOREIGN KEY (FK)
# 创表时添加外键(前提父表存在)
FOREIGN KEY(子表字段名) references 父表名(父表名字段);
# 给现存表添加外键
alert table 子表名 add FOREIGN KEY(子表字段名) references 父表名(父表名字段);
# 删除外键 #外键名可通过show create table 子表名; 中查看Constraint '外键名'
alert table 子表名 drop foreign key 外键名;
高级操作
### 将查询结果插入表中 ###
# 查询goods表中的分类信息
# select cate_name from goods group by cate_name;
# 将结果插入到goods_cates表中
insert into goods_cates(name) select cate_name from goods group by cate_name;
### 更新子表中信息 ###
# 将goods表中的分类名称更改成goods_cates表中对应的id
update goods g inner join goods_cates gc on g.cate_name = gc.name set g.cate_name = gc.id;
# 将goods表中的字段的属性和字段名更改
alert table goods change cate_name cates_id int not null,change brand_name brand_id int not null;
MySQL数据库远程登入
下载Navicat
数据库与其他ip连接,需要更改host为”%“,在重启mysql服务。
update user set host = "%" where user = "root";
数据库设计
遵循三个范式
E-R模型
一对一:一对一随便放
一对多:将关系(外键)放在多的一方
多对多:使用关系表(例:学生选课关系表)