文章目录
一、MySQL数据库知识点补充
- 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
- 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的数据类型、约束(重点,后面要用)
数据类型
-
可以通过查看帮助文档查阅所有支持的数据类型:
mysql --help
-
使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
-
常用数据类型如下
- 整数类型(Integer Types):如TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等。这些类型可以指定有符号或无符号整数,并具有不同的存储大小和范围
- 浮点数类型(Floating-Point Types):如FLOAT和DOUBLE,用于存储具有小数部分的数字。这些类型具有可变精度,并且在计算机内部使用二进制格式存储。
- 定点数类型(Fixed-Point Types):如DECIMAL和NUMERIC,用于存储具有固定小数位数的数字。这些类型使用固定精度进行存储,可以避免浮点数的舍入误差。
- 字符串类型(String Types):如CHAR、VARCHAR、BLOB、TEXT等。这些类型用于存储文本数据、二进制数据以及大型对象(LOB)等信息。
- 日期和时间类型(Date and Time Types):如DATE、TIME、DATETIME和TIMESTAMP等。这些类型用于存储日期和时间信息,可以进行日期和时间的计算和比较。
- 枚举类型(Enumeration Types):枚举类型用于定义一组可选值。在 MySQL 中,枚举类型可以使用ENUM关键字定义。
除了以上提到的类型之外,MySQL还支持其他类型,如SET类型(用于表示一组标志)、BIT类型(用于存储位集合)、JSON类型(用于存储JSON数据)等。
使用不同的数据类型可以最大程度地优化数据库表结构,提高数据存储的效率和准确性。在创建表时需要谨慎选择数据类型,避免占用过多的存储空间或产生意外的舍入误差等问题。
-
有些需要特别说明的类型,如下:
- decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
- char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ’
- varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’
- 字符串text表示存储大文本,当字符大于4000时推荐使用
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
- 更全的数据类型参考:http://blog.csdn.net/anxpp/article/details/51284106
约束
- 主键primary key:物理上存储的顺序
- 非空not null:此字段不允许填写空值
- 唯一unique:此字段的值不允许重复
- 默认default:当不填写此值时会使用默认值,如果填写时以填写为准
- 外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
- 说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
二、命令行脚本
数据库操作
查看所有数据库
show databases;
使用数据库
use 数据库名;
查看当前使用的数据库
select database();
创建数据库
create database 数据库名 charset=utf8;
例:
create database python charset=utf8;
删除数据库
drop database 数据库名;
例:
drop database python;
数据表操作
查看当前数据库中所有表
show tables;
查看表结构
desc 表名;
创建表
auto_increment表示自动增长
CREATE TABLE table_name(
column1 datatype contrai,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns)
);
举例:创建一个班级表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(10)
);
说明:
- “id” 列的数据类型是 “int”,代表整数。使用 "unsigned"关键字表示该列只能存储非负整数值。“auto_increment” 关键字表示在每次插入新行时,该列的值会自动递增。“primarykey” 关键字指定 “id” 列为主键,用于唯一标识每一行数据,确保其唯一性。"not null"关键字表示该列不允许为空,即在插入数据时 “id” 列必须有值。
- “name” 列的数据类型是 “varchar”,代表可变长度的字符串。括号中的数字 “10” 表示该列可以存储最大长度为 10的字符串。没有指定其他约束条件,所以该列可以为空。
例:创建学生表
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('男','女','人妖','保密'),
cls_id int unsigned default 0
)
说明:
- “id” 列的数据类型是 “int”,代表整数。使用 “unsigned” 关键字表示该列只能存储非负整数值。"primary key"关键字指定 “id” 列为主键,用于唯一标识每一行数据,确保其唯一性。"auto_increment"关键字表示在每次插入新行时,该列的值会自动递增。“not null” 关键字表示该列不允许为空,即在插入数据时 “id” 列必须有值。
- “name” 列的数据类型是 “varchar”,代表可变长度的字符串。括号中的数字 “20” 表示该列可以存储最大长度为 20 的字符串。“default ‘’” 表示该列的默认值为空字符串。
- “age” 列的数据类型是 “tinyint”,代表小整数。使用 “unsigned” 关键字表示该列只能存储非负整数值。“default 0” 表示该列的默认值为 0。
- “height” 列的数据类型是 “decimal”,代表定点数。括号中的数字 “5,2” 表示该列可以存储总共 5 位数,其中小数部分占据 2 位数。这意味着 “height” 列可以存储像 12345.67 这样的小数值。用"unsigned" 关键字表示该列只能存储非负整数值。“default 0” 表示该列的默认值为 0。
- “gender” 列的数据类型是 “enum”,代表枚举类型。括号中的参数 ‘男’,‘女’,‘人妖’,‘保密’ 列出了该列允许的枚举值。这意味着 “gender” 列只能存储这四个值中的一个。
- “cls_id” 列的数据类型是 “int”,代表整数。使用 “unsigned” 关键字表示该列只能存储非负整数值。“default 0” 表示该列的默认值为 0。
修改表-添加字段
alter table 表名 add 列名 类型;
例:
alter table students add birthday datetime;
修改表-修改字段:重命名版
alter table 表名 change 原名 新名 类型及约束;
例:
alter table students change birthday birth datetime not null;
修改表-修改字段:不重命名版
alter table 表名 modify 列名 类型及约束;
例:
alter table students modify birth date not null;
修改表-删除字段
alter table 表名 drop 列名;
例:
alter table students drop birthday;
删除表
drop table 表名;
例:
drop table students;
查看表的创建语句
show create table 表名;
例:
show create table classes;
表数据增删改查(curd)
curd的解释: 代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)
查询
- 查询所有列
select * from 表名;
例:
select * from classes;
- 查询指定列(可以使用as为列或表指定别名)
select 列1,列2,... from 表名;
例:
select id,name from classes;
增加
格式:INSERT [INTO] tb_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…
- 全列插入:值的顺序与表中字段的顺序对应
insert into 表名 values(...)
例:
insert into students values(0,'郭靖',1,'蒙古','2016-1-2');
- 部分列插入:值的顺序与给出的列顺序对应
insert into 表名(列1,...) values(值1,...)
例:
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
- 上面的语句一次可以向表中插入一行数据,还可以一次性插入多行数据,这样可以减少与数据库的通信
- 全列多行插入:值的顺序与给出的列顺序对应
insert into 表名 values(...),(...)...;
例:
insert into classes values(0,'python1'),(0,'python2');
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例:
insert into students(name) values('杨康'),('杨过'),('小龙女');
修改
格式: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]…[where 条件判断]
update 表名 set 列1=值1,列2=值2... where 条件
例:
update students set gender=0,hometown='北京' where id=5;
删除
格式:DELETE FROM tbname [where 条件判断]
delete from 表名 where 条件
例:
delete from students where id=5;
- 逻辑删除,本质就是修改操作
update students set isdelete=1 where id=1;
数据库的备份和恢复
- 备份
退出MySQL客户端,在命令行或者终端输入以下命令:
mysqldump –uroot –p 数据库名 > python.sql;
# 按提示输入密码
- 恢复
需要先创建一个新的空数据库,然后同样在命令行或者终端输入以下命令:
mysql -uroot –p 新数据库名 < python.sql
# 根据提示输入mysql密码
三、MySQL查询
MySQL查询
- 查询所有字段
select * from 表名;
例:
select * from students; - 查询指定字段
select 列1,列2,… from 表名;
例:
select name from students; - 使用 as 给字段起别名
select id as 序号, name as 名字, gender as 性别 from students; - 可以通过 as 给表起别名
– 如果是单表查询 可以省略表明
select id, name, gender from students;
– 表名.字段名
select students.id,students.name,students.gender from students;
– 可以通过 as 给表起别名
select s.id,s.name,s.gender from students as s; - 消除重复行
在select后面列前使用distinct可以消除重复的行
select distinct 列1,… from 表名;
例:
select distinct gender from students;
条件(where)
使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
- 语法如下:
select * from 表名 where 条件;
例:
select * from students where id=1; - where后面支持多种运算符,进行条件的处理
比较运算符
逻辑运算符
模糊查询
范围查询
空判断
比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
例:查询编号大于3的学生
select * from students where id > 3;
例:查询编号不大于4的学生
select * from students where id <= 4;
逻辑运算符
- and
- or
- not
例:查询编号大于3的女同学
select * from students where id > 3 and gender=0;
例:查询编号小于4或没被删除的学生
select * from students where id < 4 or is_delete=0;
模糊查询
- like:
- %表示任意多个任意字符
- _表示一个任意字符
例:查询姓黄的学生
select * from students where name like '黄%';
例:查询姓黄并且“名”是一个字的学生
select * from students where name like '黄_';
例:查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖';
范围查询
- in表示在一个非连续的范围内
- between … and …表示在一个连续的范围内
例:查询编号是1或3或8的学生
select * from students where id in(1,3,8);
例:查询编号为3至8的学生
select * from students where id between 3 and 8;
例:查询编号是3至8的男生
select * from students where (id between 3 and 8) and gender=1;
空判断
- 注意:null与’ '是不同的
- 判空is null
- 判非空is not null
例:查询没有填写身高的学生
select * from students where height is null;
例:查询填写了身高的学生
select * from students where height is not null;
例:查询填写了身高的男生
select * from students where height is not null and gender=1;
优先级
- 优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
- and比or先运算,如果同时出现并希望先算or,需要结合()使用
排序(order)
为了方便查看数据,可以对数据进行排序
语法:
- select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]
说明:
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列(asc)
- asc从小到大排列,即升序
- desc从大到小排序,即降序
例:查询未删除男生信息,按学号降序
select * from students where gender=1 and is_delete=0 order by id desc;
例:查询未删除学生信息,按名称升序
select * from students where is_delete=0 order by name;
例:显示所有的学生信息,先按照年龄从大–>小排序,当年龄相同时 按照身高从高–>矮排序
select * from students order by age desc,height desc;
聚合函数
为了快速得到统计数据,经常会用到如下5个聚合函数
总数
- count(*)表示计算总行数,括号中写星与列名,结果是相同的
例:查询学生总数
select count(*) from students;
最大值
- max(列)表示求此列的最大值
例:查询女生的编号最大值
select max(id) from students where gender=2;
最小值
- min(列)表示求此列的最小值
例:查询未删除的学生最小编号
select min(id) from students where is_delete=0;
求和
- sum(列)表示求此列的和
例:查询男生的总年龄
select sum(age) from students where gender=1;
平均年龄
select sum(age)/count(*) from students where gender=1;
平均值
- avg(列)表示求此列的平均值
例5:查询未删除女生的编号平均值
select avg(id) from students where is_delete=0 and gender=2;
分组(group)
group by
- group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
- group by可用于单个字段分组,也可用于多个字段分组
select gender from students group by gender;
当group by单独使用时,只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大
group by + group_concat()
group_concat(字段名)可以作为一个输出字段来使用,
表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合
select gender,group_concat(name) from students group by gender;
select gender,group_concat(id) from students group by gender;
group by + 集合函数
通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个值的集合做一些操作
select gender,group_concat(age) from students group by gender;
select gender,avg(age) from students group by gender;
select gender,count(*) from students group by gender;
group by + having
- having 条件表达式:用来分组查询后指定一些条件来输出查询结果
- having作用和where一样,但having只能用于group by
group by + with rollup
with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和
select gender,count(*) from students group by gender with rollup;
select gender,group_concat(age) from students group by gender with rollup;
分页(limit)
获取部分行
当数据量过大时,在一页中查看数据是一件非常麻烦的事情
语法
- select * from 表名 limit start,count
说明
- 从start开始,获取count条(行)数据
例:查询前3行男生信息
select * from students where gender=1 limit 0,3;
示例:分页
- 已知:每页显示m条数据,当前显示第n页
- 求总页数:此段逻辑后面会在python中实现
- 查询总条数p1
- 使用p1除以m得到p2
- 如果整除则p2为总数页
- 如果不整除则p2+1为总页数
- 求第n页的数据
select * from students where is_delete=0 limit (n-1)*m,m
连接查询
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
mysql支持三种类型的连接查询,分别为:
-
内连接查询:查询的结果为两个表匹配到的数据
-
右连接查询:查询的结果为两个表匹配到的数据、右表特有的数据,对于左表中不存在的数据使用null填充
-
左连接查询:查询的结果为两个表匹配到的数据、左表特有的数据,对于右表中不存在的数据使用null填充
语法:
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
例:使用内连接查询班级表与学生表
select * from students inner join classes on students.cls_id = classes.id;
例:使用左连接查询班级表与学生表
- 此处使用了as为表起别名,目的是编写简单
select * from students as s left join classes as c on s.cls_id = c.id;
例:使用右连接查询班级表与学生表
select * from students as s right join classes as c on s.cls_id = c.id;
例:查询学生姓名及班级名称
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
子查询
子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询
主要查询的对象,第一条 select 语句
主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 返回的结果是一列(一列多行)
- 行子查询: 返回的结果是一行(一行多列)
标量子查询
- 查询班级学生平均年龄
- 查询大于平均年龄的学生
查询班级学生的平均身高
select * from students where age > (select avg(age) from students);
列级子查询
- 查询还有学生在班的所有班级名字
- 1.找出学生表中所有的班级 id
2.找出班级表中对应的名字
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);
子查询中特定关键字使用
- in 范围
- 格式: 主查询 where 条件 in (列子查询)
总结
- 完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
- 执行顺序为:
- from 表名
- where …
- group by …
- select distinct *
- having …
- order by …
- limit start,count
- 实际使用中,只是语句中某些部分的组合,而不是全部