前言
- 重新学习整理了下
MYSQL。
推荐资料：
- 菜鸟：MySQL 教程
- MySQL笔记（pymysql）
- https://github/wmh02240/MySQL-Notes
- 视频：pymysql的使用
- B站：python和mysql视频，有口音，但是讲得还可以。
文章目录
- 前言
- 数据库简单介绍
- 1. 关系型数据库管理系统的介绍
- 2. SQL的介绍
- 3.小结
- MySQL数据库
- 1. Ubuntu下服务器端和客户端的MySQL的安装
- ubuntu服务端安装
- ubuntu下客户端安装
- 2.window上安装MySQL服务端
- # 数据类型与约束
- # 数据库使用-命令行脚本（重点）
- ## `数据库的操作`
- ## 数据表的操作
- ## 数据的增删改查
- # 数据库查询（可以复制练习）
- ## 创建数据库、数据表
- ## 准备数据
- ## 查询
- 条件查询
- 范围查询
- 排序查询
- 聚合函数
- 分页查询
- 连接查询
- 自关联
- 子查询
- 数据库设计
- 后记
数据库简单介绍
数据库可以分为关系型数据库和非关系型数据库：
1. 关系型数据库管理系统的介绍
数据库管理系统(英语全拼： Relational Database Management System简称 RDBMS)是为管理关系型数据库而设计的软件系统，如果大家想要使用关系型数据库就需要安装数据库管理系统・其实就是一个应用软件
B站视频:此处详细阐释了关系型数据库，个人觉得类似线程池的的作用，可以减少开销。
- mysql：关系型
- redis：一般做缓存
- mongodb：非关系型
关系型数据库管理系统可以分为
- 关系型数据库服务端轵件
- 关系型数据库客户端软件
关系型数据库服务端软件：
主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，据文件是用来存储数据的，其实数据库就是一系列数据文件的集合
关系型数据库客户端软件：
主要负责和关系型数据库服务端软件进行通信，向服务端传输数据或者从服务端获取据。关系型数据库管理系统的效果图：
2. SQL的介绍
3.小结
关系型数据库管理系统是一个软件，它可以管理不同的数据库，想要对数据库进行操作安装关系型数据库管理系统软件即可。
SQL的作用是实现数据库客户端和数据库服务端之间的通信，好比是通信桥梁。
MySQL数据库
- MYSQL数据库的介绍
MSQL是一个关系型数据库管理系统，在WEB应用方面・ MYSQL是最好的 RDBMS( Relational Database Management System,关系数据库管理系）应用软件・它是由瑞典 MYSQL AB公司开发・目前属于Oracle旗下产品， MYSQL是最流行的关系型数据库管理系统中的一个
MYSQL的特点：
- MYSQL是开源的，所以你不需要支付额外的费用
- MYSQL支持大型的数据库。可以処理拥有上千万条记录的大型数据库。
- MySQL使用标准的SL数据语言形式
- MYSQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++ Python、Java、Ruby等等。
1. Ubuntu下服务器端和客户端的MySQL的安装
- Ubuntu下服务器端和客户端的MySQL的安装(链接)
ubuntu服务端安装
- Ubuntu20.04安装Mysql
# 安装服务器
sudo apt-get install mysql-server
# 启动服务
sudo service mysql start
# 停止服务
sudo service mysql stop
# 重启服务
sudo service mysql restart
# 查看mysql数据库服务端状态的几种操作
sudo service mysql status
ps ajx|grep mysql
#########################################################
# 配置文件路径 /etc/mysql/mysql.conf.d/mysqldf
##########################################################
sudo vim /etc/mysql/mysql.conf.d/mysqldf
# bind-address表示服务器绑定的ip，默认为127.0.0.1
# port表示端口，默认为3306
# datadir表示数据库目录，默认为/var/lib/mysql
# general_log_file表示普通日志，默认为/var/log/mysql/mysql.log
# log_error表示错误日志，默认为/var/log/mysql/error.log
注意：此处截图是配置文件路径
/etc/mysql/mysql.conf.d/mysqldf
这个问题解决之后，用Navicat就可以默认访问了，否则需要sudo命令启动Naviacat
- Ubuntu下解决root账户登录mysql只能sudo登录的问题:这个文章里面提到的配置文件是错误的
ubuntu下客户端安装
- 命令行界面安装
# 安装客户端
sudo apt-get install mysql-client
- 图形化界面客户端navicat安装，比较简单，直接上官网下载,按照下面命令操作即可
chmod +x navicat16-mysql-cs.AppImage
./navicat16-mysql-cs.AppImage
-
- Navicat使用
- Navicat使用
2.window上安装MySQL服务端
- 视频教程-新版MYSQL安装教程
- 视频教程-Navicat12破解教程
- Windows 下安装 MySQL，并安装Navicat for MySQL
# 数据类型与约束
- 数据类型与约束
对于图像、视频、音频文件一般存储于服务器上,然后在表中存储文件的路径
- 七牛云可以提供免费云存储
# 数据库使用-命令行脚本（重点）
## 数据库的操作
-- 数据库的操作
-- 连接数据库
mysql -uroot -p
mysql -uroot -p密码;
-- 退出数据库
exit/quit/ctrl+d
-- sql语句最后需要有分号;结尾
-- 显示数据库版本
select version();
-- 显示时间
select now();
-- 查看所有数据库
show databases;
-- 创建数据库
-- create dabase 数据库名 charset=utf8;
create database python_test charset = utf8;
-- 查看创建数据库的语句
-- show create database ...
show create database python_test;
-- 使用数据库
-- use 数据库的名字
use python_test;
-- 删除数据库
drop database python_test;
-- 查看当前使用数据库
select database();
## 数据表的操作
-- 数据表的操作
-- 查看当前数据库中的所有数据表
show tables ;
-- 创建数据表 约束中符号默认为有符号
-- auto_increment表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- create table 数据表名字（字段 类型 约束[,字段 类型 约束]）
create table students(
id int unsigned unique not null auto_increment primary key,
name varchar(30) not null,
age tinyint unsigned,
high decimal(5,2),
gender enum("男","女","中性","保密") default "保密",
cls_id int unsigned
);
-- 查看数据表的结构
-- desc 数据表的名字
desc table_test;
-- 数据表中插入数据
insert into students values (0, "老王", 23, 187, "男", 2);
-- 查看表的内容
select * from students;
-- 创建classes表（id/name）
create table classes(
id int unsigned not null auto_increment primary key,
name varchar(30)
);
-- 修改表结构
-- 添加字段
-- alter table 表名 add 列名 类型;
alter table studuents add brithday datetime;
-- 修改字段-不重命名版
-- alter table 表名 modify 列名 类型;
alter table studuents modify brithday date;
-- 修改字段-重命名版
-- alter table 表名 change 原名 新名 类型及约束;
alter table studuents change brithday brith date default "2000-01-01";
-- 删除字段
-- alter table 表名 drop 列名;
alter table studuents drop brith; #一般不会删除数据,多做加法,少做减法
-- 删除表中的数据 不删除表
delete from studuents where id > 3;
-- 删除表
-- drop table 表名 drop 列名;
drop table studuents;
-- 查看表的创建语句
show create table studuent;
## 数据的增删改查
- B站：数据的增删改查
- 别人笔记:
-- B站：紫舍利
-- 这是我做的，sql语句的笔记，基本上讲的我都写了，有需要的可以下载
-- 链接：https://pan.baidu/s/1hJOGCXY7NAtVje0zMpKM7w
-- 提取码：q7x8
create database name chartset=utf8;
查看正在使用的是哪个数据库
select database();
创建表
CREATE TABLE tb_enp1
(
id int(11),
name varchar(25),
deptld int(11),
salary float
);
创建单字段主键
一，
CREATE TABLE tb_enp2
(
id int(11) PRIMARY KEY,
name varchar(25),
deptld int(11),
salary float
);
二，
CREATE TABLE tb_enp3
(
id int(11),
name varchar(25),
deptld int(11),
salary float,
PRIMARY KEY(id)
);
定义联合主键
CREATE TABLE tb_enp4
(
name varchar(25),
deptld int(11),
salary float,
PRIMARY KEY(name,deptld)
);
外键约束
创建一个表
CREATE TABLE tb_dept1
(
id int(11) PRIMARY KEY,
name varchar(22) NOT NULL,
location varchar(50)
);
CREATE TABLE tb_nep5
(
id int(11) PRIMARY KEY,
name varchar(25),
deptld int(11),
salary float,
CONSTRAINT fk_enp1 FOREIGN KEY(deptld) REFERENCES tb_dept1(id)
# deptld约束为外键fk—enpl，参照tb_dept1（id）
);
create table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum("男","女"),
cls_id int unsigned
);
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVSION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
输入数据
insert into students values(0,"老王",18,188.88,"男",0);
查看表的内容
select * from name;
查看表的结构，名字，字段和字段属性。
desc name;
已经创建的表里添加字段
alter table students（表的名字） add 字段名 数据类型；
修改表里字段的数据类型
alter table students modify 字段名 数据类型;
修改字段的名称
alter table students change 字段名 新名字 数据类型；
删除字段!!
alter table students(表名) drop 字段名；
删除表
drop table 表名；
查看表的完全属性
show create table 表名；可以查看引擎
修改数据
插入数据
全部插入
insert into class（表名） values（1,2,3，...先desc 表名 查看数据结构，然后一一对应插入内容）
insert into students values (0,'小乔',18,160.00,02);
插入内容时 应为设置了主键primary key，以及自主增长auto_increment 所以插入主键id的那一行可以写0/null(空值)/default（默认）.
写性别gender的数据时因为用了枚举enum，只能写枚举里的值，也可以写1,2,3,4一一对应当初设置的顺序性别。
部分插入
insert into students (name（字段名），gender（字段名)） values （'小乔','女'）;
一次插入多个数据
insert into students (name（字段名），gender（字段名)） values （'小乔','女'），（'貂蝉','女'）;部分插入和全体插入都可.
修改数据
update studengs(表名) set gender(要修改的字段)=***，age=** where(条件限制) id=*(主键，数据的唯一标识)；
当where后面的条件限制不明确时所有符合条件的数据都会更改！！
insert into students values (0,'貂蝉',20,160.00,2,02，2000.01.01);
查询字段
select * from students where id>3(限制条件)，*代表全部字段。
select name，gender from students；查询两个字段，字段名顺序可以改变，显示时的顺序也会改变
查询字段时修改字段显示的名称
select name as 名字,gender as 性别 from students ;
删除数据
delete from students where name=**;
逻辑删除与物理删除
逻辑删除增加新行进行删除标记
alter table students add is_delete bit default 0;
update students set is_delete = 1 where id=*;
查询时筛除重复数据
select distinct gender(字段名) from students;
条件查询
select */字段名(表示所有字段) from 表名 where age>18(条件);
多条件查询 逻辑符号
and 同时满足条件
select */字段名(表示所有字段) from 表名 where age>18(条件)and age<30;
or 满足一种条件
select */字段名(表示所有字段) from 表名 where age>18(条件)or gender=2;
not
select */字段名(表示所有字段) from 表名 not(where age>18(条件)or gender=2);同时否定两个条件
select */字段名(表示所有字段) from 表名 notwhere age>18(条件)or gender=2;否定not
后面的条件。
模糊查询
like
select */name/字段名(表示所有字段) from 表名 where name like "%小%"；能查询名字中包含小的。
select */name/字段名(表示所有字段) from 表名 where name like "小%" %代表字符或没有，能查询名字以小开头的。
查询名字是两个字
select */name/字段名(表示所有字段) from 表名 where name like "__"(两个下划线);
查询名字至少是两个字
select */name/字段名(表示所有字段) from 表名 where name like "__%";
rlike后面接正则表达式
查询以周开始的姓名
select */name/字段名(表示所有字段) from 表名 where name rlike "^周.*";
查询以周开始以伦结束的姓名
select */name/字段名(表示所有字段) from 表名 where name rlike "^周.*伦s";
in 查询年龄为12,18,30的
select */字段名(表示所有字段) from 表名 not where age = 18 or age=22 or age=30;
select */字段名(表示所有字段) from 表名 not where age in (18,22,30);
not in 查询年龄不是12,18,30的
select */字段名(表示所有字段) from 表名 not where age not in (18,22,30);
查询范围之内；
between...and..
select */字段名(表示所有字段) from 表名 where age between 18 and 35;
不在范围内
select */字段名(表示所有字段) from 表名 where age not between 18 and 35;
select */字段名(表示所有字段) from 表名 where not age not between 18 and 35;
空值判断
select */字段名(表示所有字段) from 表名 where 字段名 is null;
不空
select */字段名(表示所有字段) from 表名 where 字段名 is not null;
排序
按照年龄18到35的男性排序,默认从小到大
select */字段名(表示所有字段) from 表名 where （age between 18 and 35）and gender=1 order by age;
select */字段名(表示所有字段) from 表名 where （age between 18 and 35）and gender=1 order by age asc;
从大到小,支持按照多字段排序。
select */字段名(表示所有字段) from 表名 where （age between 18 and 35）and gender=1 order by age dasc，id dasc;
聚合函数
count总数
select count(*) as 男性人数（显示注解）from students where gender=1(条件);
最大值max,最小值min，求和sum，求平均值avg
select max/min/(age) from students;
在students里女性的最大身高
select max/sum(height) from students where gender=2;
求平均值
select sum(height)/count(*) from students where gender=2
保留几位小数round,还有四舍五入的作用
select round(sum(height)/count(*),2(两位小数)) from students where gender=2;
分组
group by
select gender(分组后的同一属性) from students group by gender;
select gender，group_concat(name) from students group by gender;可以显示分组后每一组里人的姓名；
分组后每一组的人数,count（*）是对分组后的数据进行聚合
select gender，count(*) from students group by gender;
select gender，max(age) from students group by gender;每组的最大年龄
select gender，avg(age) from students group by gender;
分组后男性的人数
select gender，count(*) from students where gender=1 group by gender;
分组后的跟多操作
分组后查询男性的名字，id和年龄，中间添加空格。
select gender，group_concat(name,' ',id,' ',gae) from students where gender=1 group by gender;
having对结果的判断，where对原始数据的判断所以写的位置不同。
select gender，group_concat(name），avg(age) from students where gender=1 group by gender having avg(age)>30;
select gender，group_concat(name） from students where group by gender having avg(age)>30
分页
select * from students where 条件 limit 2;只显示两行；
select * from students where 条件 limit 5（开始位置，根据列表，这是第六个），2（两行）;
分页显示公式
select * from students where 条件 limit （第m页-1）*n，n;不能直接输入.
连接查询
inner join ...on on取交集，两个表的匹配列的交集
select *(表示显示所有字段) from students inner join calsses on students.cla_id=classes.id;通过students表里的cla_id和classes表里的id字段匹配连接。
select s.name,c.name from students as s inner join calsses as c on s.cla_id=c.id;只显示students表里的姓名和classes表里的班级。
例班级名字在前，学生所有信息在后，按班级排列后，按照id排列
select c.name，s.* from students as s inner join calsses as c on s.cla_id=c.id order by c.name,s.id;
左连接 left join on 两个表匹配以后把左边表的信息全部显示，右边表对不上的为空
select c.name，s.* from students as s left join calsses as c on s.cla_id=c.id order by c.name,s.id;
上边的语句可以当做新的表来查询
select c.name，s.* from students as s left join calsses as c on s.cla_id=c.id order by c.name,s.id having c.id si null;查询两个表内无法匹配的内容。
自关联 同一个表里的不同列有关联，例如省市县
select *(显示内容) from m（表1）as n inne join m（表1） as s（第二个名字，一个表就可以相当于两个表用。） on n.列=s.列 having（条件） n.列=??;
inne jion 表 on 里的表可以用sql语句代替作为一个表；
子查询
把一个sql语句当做另一个sql语句的条件
select * from students where height=(select max(height) ；
把一个表一列的值根据另一个相关表的列，替换成新的值。
update 表1(更新那个表) as 1 join 表2 as 2 on 1.列=2.列 set 1.列2=2.列2；
设置外键关联
alter table 表1 add forrign key (列) references 表2（列）；
建立视图 用以方便查询
create view 视图名称 as select 语句
查看视图
show tables
使用视图
select * from 视图名称
删除视图
dorp view 视图名称
事务的 ACID
1，原子性，作为最小的事务单位，要不全部成功，要不失败回滚。
2，一致性，防止程序运行到一般死机，导致事务没有全部运行。
3，隔离性，一个事务操作着一个数据，会无视其他的事务
4，持久性，永久保存
开启事务
start transaction ；begin；
commit；结束提交
查看运行时间
show profiles;
建立索引
create index title_index on text_index（title（10））；
title_index 索引名称 text_index表名 title（10）列名 长度，建立索引时如果是字符串类型（char，varchar）根据原列的长度建立能提高速度
# 数据库查询（可以复制练习）
## 创建数据库、数据表
-- 创建数据库
create database `python_test` charset=utf8;
-- 查看数据库
show databases;
-- 使用数据库
use python_test;
-- 显示当前使用那个数据库
select database();
-- 创建一个数据表 存0 1时使用bit 就行 bit(2)可以存4种 tinyint = bit(8) 2^8 = 256
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
);
-- classes表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
-- 查看数据表
show tables ;
-- 查看数据表怎么创建的
show create tables students;
## 准备数据
-- 向表中插入数据
insert into students values
(0,'小明',18,168.00,2,1,0),
(0,'小黄',17,175.00,1,2,0),
(0,'小红',14,177.00,2,3,0),
(0,'小汉',11,180.00,3,4,0),
(0,'小八',12,187.00,3,5,0),
(0,'小九',13,182.00,4,6,0),
(0,'小十',18,188.00,3,7,0),
(0,'小之',17,186.00,2,8,0),
(0,'小一',10,188.00,2,9,0),
(0,'小二',15,182.00,3,9,0),
(0,'小三',18,184.00,2,6,0),
(0,'小四',19,185.00,4,4,0),
(0,'小五',13,190.00,2,3,0),
(0,'小六',14,189.00,2,4,0),
(0,'小七',15,178.00,2,5,0),
(0,'十一',15,167.00,1,7,0),
(0,'十二',18,176.00,1,2,0);
-- 向classes表中插入数据
insert into classes values
(0, "python01期"),
(0, "python02期"),
(0, "python04期");
desc classes;
## 查询
-- 查询
-- 查询所有字段
-- select * from 表名;
select * from students;
select * from classes;
-- 查询指定字段
-- select 列1,列2,... from 表名;
select id, name from students;
-- 使用as给字段起别名
-- select 字段 as 名字....from 表名;
select name as 姓名, age as 年龄 from students;
-- select 表明.字段 ..... from 表名;
select students.name, students.age from students;
-- 可以通过as给表其别名
-- select 别名.字段 .... from 表名 as 别名;
select students.name, students.age from students;
select s.name, s.age from students as s;
# 消除重复行
# distinct 字段
select distinct gender from students;
条件查询
-- 条件查询
-- 1. 比较运算符 > < >= <= !=
-- --select ... from 表名 where ...;
select * from students where age > 18; -- 显示所有字段
select name, gender, id from students where age > 18; -- 显示选中的字段信息
--2. 逻辑运算符
-- and
-- 18到28岁之间的学生信息
select * from students where age > 18 and age < 28;
select * from students where age > 18 and gender = "女";
select * from students where age > 18 and gender = 2;
-- or
select * from students where age > 18 or gender = 2;
-- not
-- 不在 18岁以上 并且是女性这个范围内的信息
select * from students where not age > 18 or gender = 2; -- 仅仅对前一个条件起作用
# 不在18岁以上的女性的这个范围内的信息
select * from students where not (age > 18 or gender = 2); -- not对后面和括号内的都起作用
-- 年龄不是小于或者等于18 并且是女性
select * from students where not ( age < 18 or age = 18) and gender = 2;
select * from students where not age <=18 and gender = 2;
--3. 模糊查询（进行匹配 所以效率低）
-- like
-- % 替换零个或者多个
-- _ 替换一个
-- eg:查询姓名以小开头的信息
select * from students where name like "小%";
-- 查询姓名当中有小的名字
select * from students where name like "%小%";
-- eg:查询有两个字的名字
select * from students where name like "小_";
-- eg:查询两个字以上的名字数据信息
select * from students where name like "__%";
-- rlike (正则表达式)
-- 查询以小开始的信息
select * from students where name rlike "^小.*";
-- 查询以小开始 九结束的信息
select * from students where name rlike "^小*九$";
select * from students where name regexp "^小*九$";
select * from students where name regexp "一";
范围查询
-- in (1,3,8)表示在一个非连续的范围内
-- 查询年龄为18,34的人信息
select name from students where age = 18 or age = 34;
select name from students where age = 18 or age = 34 or age = 12;
select name, age from students where age in (12, 18, 34);
-- not in不非连续的范围内
-- 不是年龄为18， 34的人信息
select name, age from students where age not in (12, 18, 34);
-- between ... and ...表示在什么范围之内 属于连续问题查询
-- 在什么范围
select name, age from students where age between 12 and 34;
-- 不再范围
select name, age from students where age not between 12 and 34;
select name, age from students where not age between 12 and 34;
-- not ... between ... and 是一种语法
select name, age from students where age not (between 12 and 34); -- 语法错误
-- 判断空 is null
-- 判断身高为空
select name from students where age is null;
-- 判断非空 is not null
select name from students where age is not null;
排序查询
-- 排序
-- order by 字段
-- asc 从小到大排序 即升序
-- desc 从大到小排序 即将序
-- 在排序过程中遇到相同值时会按照主键进行排序
-- 查询年龄到12～23之间的男性，按照年龄从小到大排序
select * from students where (age between 12 and 23) and gender = 1;
select * from students where (age between 12 and 23) and gender = 1 order by age;
select * from students where (age between 12 and 23) and gender = 1 order by age asc;
-- 查询年龄到12～23之间的男性，按照年龄从大到小排序
select * from students where (age between 12 and 23) and gender = 1 order by age desc;
-- order by 多个字段
select * from students where (age between 12 and 23) and gender = 1 order by age desc, gender desc, height desc;
-- 在年龄相同的情况下按照性别按照升序的方式排序,如果前面排序字段都不相同则后面的字段不执行
聚合函数
--聚合函数：一些普通的函数得出的一些结论，不允许与其他字段混合使用, 想要用就要使用分组, 分组和聚合函数一起使用
-- 求总数
-- count
-- 查询男性有多少人
select count(name), avg(height) from students;
select * from students where gender = 1;
select count(*) from students where gender = 1;
select count(*) as 男性人数 from students where gender = 1;
-- 求最大值
-- max
-- 查询最大年龄
select * from students;
select max(age) as 最大值 from students;
-- 最小值
-- min 秋女性年龄最小值
select min(age) as 最小值 from students where gender = 2;
-- 求和
-- sum
-- 求所有年龄总和
select sum(age) as 和 from students;
-- 求平均值
-- avg
select sum(age)/count(*) as 均值 from students;
select avg(age) as 最大值 from students;
-- 四舍五入
-- round(123.232221133, 4)
select round(avg(age), 2) as 四舍五入 from students;
-- 经验：语言里面小数是由误差的，例如银行当中，但是整数没有
-- 举例：3.14 我们乘以100变成整数。
-- 分组 分组和聚合函数一起使用，否则分组就没啥意义
-- GROUP BY关键字通常和集合函数一起使用，比如MAX()、MIN()、COUNT()、SUM()、AVG()。
-- 例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。
-- 按照性别分组，查询所有的性别
select gender, count(gender) from students group by gender; -- 先分组，再从组内取数据
-- 计算每种性别的人数
select gender, count(*) from students group by gender; -- count(*)表示对分组的计算个数 count(*)指从分组内计算个数
desc students;
-- 按照性别分组
select gender, count(gender), group_concat(name, ' ', id, ' ', height, ' ', cls_id) from students group by gender;
-- 计算男性的人数
select gender, count(*) from students where gender = 1;
select gender, count(*) from students where gender = 1 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
+--------+----------+
1 row in set (0.00 sec)
-- group_concat(...) 显示的内容
--在MySQL中，可以在GROUP BY子句中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。
select gender, group_concat(name, age, id), count(*) from students where gender = 1 group by gender;
+--------+---------------------------------+----------+
| gender | group_concat(name, age, id) | count(*) |
+--------+---------------------------------+----------+
| 男 | 小黄172,十一1516,十二1817 | 3 |
+--------+---------------------------------+----------+
1 row in set (0.00 sec)
select gender, group_concat(name, "_", age, " ", id), count(*) from students where gender = 1 group by gender;
+--------+---------------------------------------+----------+
| gender | group_concat(name, "_", age, " ", id) | count(*) |
+--------+---------------------------------------+----------+
| 男 | 小黄_17 2,十一_15 16,十二_18 17 | 3 |
+--------+---------------------------------------+----------+
1 row in set (0.00 sec)
-- where是对原表的数据进行判断
-- having表示对分组进行条件判断,是进行分组之后在判断
-- 查询平均年年龄超过18岁的性别以及姓名 hanving avg(age)>18
select gender, group_concat(name) from students group by gender having avg(age)>8;
select gender, group_concat(name), avg(age), count(*) from students group by gender having count(*) > 3;
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 11;
--在GROUP BY子句中使用WITH ROLLUP
--使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
select gender, group_concat(name), count(name) from students group by gender with rollup having avg(age)>8;
--由结果可以看到，通过GROUP BY分组之后，在显示结果的最后面新添加了一行，该行Total列的值正好是上面所有数值之和。
-- 多字段分组
-- 使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，
-- 然后在第1个字段值相同的记录中再根据第2个字段的值进行分组，以此类推。
select gender, cls_id, group_concat(name, ' ', cls_id) from students group by gender, cls_id;
-- GROUP BY和ORDER BY一起使用
-- 某些情况下需要对分组进行排序，在前面的介绍中，ORDER BY用来对查询的记录排序，如果和GROUP BY一起使用可以完成对分组的排序。
select gender, group_concat(name, age, height) from students group by gender having avg(age)>8 order by count(height);
-- GROUP BY子句按订单号对数据进行分组，SUM()函数便可以返回总的订单价格，HAVING子句对分组数据进行过滤，使得只返回总价格大于100的订单，最后使用ORDER BY子句排序输出。
-- 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
分页查询
-- limit start, count --直接限制查询出来的个数
-- 带一个参数的LIMIT指定从查询结果的首行开始，唯一的参数表示返回的行数，即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。返回第一行时，位置偏移量是0。因此，“LIMIT 1, 1”将返回第二行，而不是第一行。
-- MySQL 8.0中可以使用“LIMIT 4 OFFSET 3”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
-- 查询前五个数据
select * from students limit 5; -- 只显示前两个
select * from students order by age desc limit 5;
-- 限制查询的个数
select * from students limit 7, 4;
-- 开始第一个为0
-- 规律：limit (第N页-1)*每页的个数, 每页的个数;
-- +----+--------+------+--------+--------+--------+-----------+
-- | id | name | age | height | gender | cls_id | is_delete |
-- +----+--------+------+--------+--------+--------+-----------+
-- | 9 | 小一 | 10 | 188.00 | 女 | 9 | |
-- | 10 | 小二 | 15 | 182.00 | 中性 | 9 | |
-- | 11 | 小三 | 18 | 184.00 | 女 | 6 | |
-- | 12 | 小四 | 19 | 185.00 | 保密 | 4 | |
-- +----+--------+------+--------+--------+--------+-----------+
-- 4 rows in set (0.00 sec)
-- 注意：limit必须放在最后。
-- 顺序为where order by limit
select * from students where gender = 2 order by height desc limit 0, 2
连接查询
#连接查询：即多个表之间进行关联查询
# 1.内连接查询 即取交集
#inner join ... on on意味着条件
select * from students inner join classes;
select * from students inner join classes on students.cls_id = classes.id; #找打相应的信息显示
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# 5 rows in set (0.00 sec)
# --按照要求现实姓名和年级
select students.* from students;
select classes.* from classes;
select students.*, classes.name from students inner join classes on students.cls_id = classes.id;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --简写重命名
select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --加上排序
select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --更改显示顺序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | name | id | name | age | height | gender | cls_id | is_delete |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# --多条件排序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name, s.id, s.age;
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | name | id | name | age | height | gender | cls_id | is_delete |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# 2. 外连接查询(分为左连接右连接)
# --右连接查询
#
# --左连接查询(那个表在左边，以这个表为基准取查询的信息，取不出来莫认为Null)
select * from students as s left join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 4 | 小汉 | 11 | 180.00 | 中性 | 4 | | NULL | NULL |
# | 5 | 小八 | 12 | 187.00 | 中性 | 5 | | NULL | NULL |
# | 6 | 小九 | 13 | 182.00 | 保密 | 6 | | NULL | NULL |
# | 7 | 小十 | 18 | 188.00 | 中性 | 7 | | NULL | NULL |
# | 8 | 小之 | 17 | 186.00 | 女 | 8 | | NULL | NULL |
# | 9 | 小一 | 10 | 188.00 | 女 | 9 | | NULL | NULL |
# | 10 | 小二 | 15 | 182.00 | 中性 | 9 | | NULL | NULL |
# | 11 | 小三 | 18 | 184.00 | 女 | 6 | | NULL | NULL |
# | 12 | 小四 | 19 | 185.00 | 保密 | 4 | | NULL | NULL |
# | 14 | 小六 | 14 | 189.00 | 女 | 4 | | NULL | NULL |
# | 15 | 小七 | 15 | 178.00 | 女 | 5 | | NULL | NULL |
# | 16 | 十一 | 15 | 167.00 | 男 | 7 | | NULL | NULL |
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# 17 rows in set (0.00 sec)
#
# --与inner join ... on比较
select * from students as s inner join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# 5 rows in set (0.01 sec)
#
#
# --left是指left左边(等号左边)的数据表
select * from students;
select * from classes as c left join students as s on c.id = s.cls_id;
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# | id | name | id | name | age | height | gender | cls_id | is_delete |
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# | 1 | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | 2 | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | 3 | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# | 3 | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# | 2 | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# --right join ... on 一般用的比较少 使用左连接调换两个数据表的顺序即可
#
# --查询没有对应班级信息的学生
# --思路：对于查询出的结果我们可以将其看作一个新的表，然后在此新表的基础上进行再次操作查询
# --如果在原表里面进行查询使用where
# --在查询的基础上进行再次操作使用having
select * from students as s left join classes as c on c.id = s.cls_id having c.id is NULL;
select * from students as s left join classes as c on c.id = s.cls_id where c.id is NULL; #这样也行
# +----+--------+------+--------+--------+--------+-----------+------+------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+------+------+
# | 4 | 小汉 | 11 | 180.00 | 中性 | 4 | | NULL | NULL |
# | 5 | 小八 | 12 | 187.00 | 中性 | 5 | | NULL | NULL |
# | 6 | 小九 | 13 | 182.00 | 保密 | 6 | | NULL | NULL |
# | 7 | 小十 | 18 | 188.00 | 中性 | 7 | | NULL | NULL |
# | 8 | 小之 | 17 | 186.00 | 女 | 8 | | NULL | NULL |
# | 9 | 小一 | 10 | 188.00 | 女 | 9 | | NULL | NULL |
# | 10 | 小二 | 15 | 182.00 | 中性 | 9 | | NULL | NULL |
# | 11 | 小三 | 18 | 184.00 | 女 | 6 | | NULL | NULL |
# | 12 | 小四 | 19 | 185.00 | 保密 | 4 | | NULL | NULL |
# | 14 | 小六 | 14 | 189.00 | 女 | 4 | | NULL | NULL |
# | 15 | 小七 | 15 | 178.00 | 女 | 5 | | NULL | NULL |
# | 16 | 十一 | 15 | 167.00 | 男 | 7 | | NULL | NULL |
# +----+--------+------+--------+--------+--------+-----------+------+------+
# 12 rows in set (0.00 sec)
自关联
earas.sql文件可以点击下载；
# 一个表中的一列关联到该表中的另一列：自关联
# 使用：省市关联、行政关系等
create table areas(
aid int primary key,
pid int,
atitle varchar(20),
types tinyint(1) default "2"
);
---------------------------------------------
-- #导入earas.sql文件中的数据:相当于自己创建了一个earas表，然后进行了后续的插入操作
-- #从earas.sql文件目录下登录数据库,使用source earas.sql导入数据
-------------------------------------------------------------
# 查看省份
select * from areas where pid = 1;
# 查看省份市的个数
select * from areas where atitle = '陕西';
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 24 | 1 | 陕西 | 1 |
+-----+------+--------+-------+
1 row in set (0.00 sec)
select * from areas where pid = 24;
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 311 | 24 | 西安 | 2 |
| 312 | 24 | 安康 | 2 |
| 313 | 24 | 宝鸡 | 2 |
| 314 | 24 | 汉中 | 2 |
| 315 | 24 | 商洛 | 2 |
| 316 | 24 | 铜川 | 2 |
| 317 | 24 | 渭南 | 2 |
| 318 | 24 | 咸阳 | 2 |
| 319 | 24 | 延安 | 2 |
| 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+
10 rows in set (0.00 sec)
select * from areas where pid = 311;
+------+------+-----------+-------+
| aid | pid | atitle | types |
+------+------+-----------+-------+
| 2596 | 311 | 莲湖区 | 3 |
| 2597 | 311 | 新城区 | 3 |
| 2598 | 311 | 碑林区 | 3 |
| 2599 | 311 | 雁塔区 | 3 |
| 2600 | 311 | 灞桥区 | 3 |
| 2601 | 311 | 未央区 | 3 |
| 2602 | 311 | 阎良区 | 3 |
| 2603 | 311 | 临潼区 | 3 |
| 2604 | 311 | 长安区 | 3 |
| 2605 | 311 | 蓝田县 | 3 |
| 2606 | 311 | 周至县 | 3 |
| 2607 | 311 | 户县 | 3 |
| 2608 | 311 | 高陵县 | 3 |
+------+------+-----------+-------+
13 rows in set (0.01 sec)
# 另一种查询方法(假如有两章表的情况下，一张省份表，一张地级市表的情况下，地级市的pid = 省份表的aid)
# 思路：可以通过as生成新的表
select * from areas as province inner join areas as city on province.aid = city.pid having province.atitle = "陕西";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "陕西";
+-----+------+--------+-------+-----+------+--------+-------+
| aid | pid | atitle | types | aid | pid | atitle | types |
+-----+------+--------+-------+-----+------+--------+-------+
| 24 | 1 | 陕西 | 1 | 311 | 24 | 西安 | 2 |
| 24 | 1 | 陕西 | 1 | 312 | 24 | 安康 | 2 |
| 24 | 1 | 陕西 | 1 | 313 | 24 | 宝鸡 | 2 |
| 24 | 1 | 陕西 | 1 | 314 | 24 | 汉中 | 2 |
| 24 | 1 | 陕西 | 1 | 315 | 24 | 商洛 | 2 |
| 24 | 1 | 陕西 | 1 | 316 | 24 | 铜川 | 2 |
| 24 | 1 | 陕西 | 1 | 317 | 24 | 渭南 | 2 |
| 24 | 1 | 陕西 | 1 | 318 | 24 | 咸阳 | 2 |
| 24 | 1 | 陕西 | 1 | 319 | 24 | 延安 | 2 |
| 24 | 1 | 陕西 | 1 | 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+-----+------+--------+-------+
10 rows in set (0.01 sec)
select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "西安";
+-----+------+--------+-------+------+------+-----------+-------+
| aid | pid | atitle | types | aid | pid | atitle | types |
+-----+------+--------+-------+------+------+-----------+-------+
| 311 | 24 | 西安 | 2 | 2596 | 311 | 莲湖区 | 3 |
| 311 | 24 | 西安 | 2 | 2597 | 311 | 新城区 | 3 |
| 311 | 24 | 西安 | 2 | 2598 | 311 | 碑林区 | 3 |
| 311 | 24 | 西安 | 2 | 2599 | 311 | 雁塔区 | 3 |
| 311 | 24 | 西安 | 2 | 2600 | 311 | 灞桥区 | 3 |
| 311 | 24 | 西安 | 2 | 2601 | 311 | 未央区 | 3 |
| 311 | 24 | 西安 | 2 | 2602 | 311 | 阎良区 | 3 |
| 311 | 24 | 西安 | 2 | 2603 | 311 | 临潼区 | 3 |
| 311 | 24 | 西安 | 2 | 2604 | 311 | 长安区 | 3 |
| 311 | 24 | 西安 | 2 | 2605 | 311 | 蓝田县 | 3 |
| 311 | 24 | 西安 | 2 | 2606 | 311 | 周至县 | 3 |
| 311 | 24 | 西安 | 2 | 2607 | 311 | 户县 | 3 |
| 311 | 24 | 西安 | 2 | 2608 | 311 | 高陵县 | 3 |
+-----+------+--------+-------+------+------+-----------+-------+
13 rows in set (0.01 sec)
子查询
--简单来讲，子查询就是一个select中嵌套了一个select
--子查询效率比较低
--标量子查询
--查询身高最高的男生
select max(height) from students;
+-------------+
| max(height) |
+-------------+
| 190.00 |
+-------------+
1 row in set (0.00 sec)
select * from students where height = (select max(height) from students);
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 13 | 小五 | 13 | 190.00 | 女 | 3 | |
+----+--------+------+--------+--------+--------+-----------+
1 row in set (0.00 sec)
--利用子查询解决自关联查询中的问题
select * from areas where pid = (select aid from areas where atitle = "陕西");
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 311 | 24 | 西安 | 2 |
| 312 | 24 | 安康 | 2 |
| 313 | 24 | 宝鸡 | 2 |
| 314 | 24 | 汉中 | 2 |
| 315 | 24 | 商洛 | 2 |
| 316 | 24 | 铜川 | 2 |
| 317 | 24 | 渭南 | 2 |
| 318 | 24 | 咸阳 | 2 |
| 319 | 24 | 延安 | 2 |
| 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+
10 rows in set (0.00 sec)
--列级子查询
-- 查询学生的班级号能够对应的学生信息
数据库设计
数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩
- 维基百科的表格
后记
- 在这里，更加深刻的体会到：网络编程、服务器与数据库的关系了，推动互联网的发展尤为重要。
更多推荐
MySQL数据库的基本使用:
发布评论