MySQL数据库操作以及sql语句总结

一、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;
1

使用数据库

use 数据库名;
1

查看当前使用的数据库

select database();
1

创建数据库

create database 数据库名 charset=utf8;
例：
create database python charset=utf8;
1
2
3

删除数据库

drop database 数据库名;
例：
drop database python;
1
2
3

数据表操作

查看当前数据库中所有表

show tables;
1

查看表结构

desc 表名;
1

创建表
auto_increment表示自动增长

CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY(one or more columns)
);
1
2
3
4
5
6
7
8

举例：创建一个班级表

create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);
1
2
3
4

说明：

• “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
)
1
2
3
4
5
6
7
8

说明：

• “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;
1
2
3

修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;
1
2
3

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;
例：
alter table students modify birth date not null;
1
2
3

修改表-删除字段

alter table 表名 drop 列名;
例：
alter table students drop birthday;
1
2
3

删除表

drop table 表名;
例：
drop table students;
1
2
3

查看表的创建语句

show create table 表名;
例：
show create table classes;
1
2
3

表数据增删改查（curd）

curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）

查询

• 查询所有列

	select * from 表名;
	例：
	select * from classes;
1
2
3

• 查询指定列（可以使用as为列或表指定别名）

	select 列1,列2,... from 表名;
	例：
	select id,name from classes;
1
2
3

增加
格式:INSERT [INTO] tb_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…

• 全列插入：值的顺序与表中字段的顺序对应

	insert into 表名 values(...)
	例：
	insert into students values(0,'郭靖',1,'蒙古','2016-1-2');
1
2
3

• 部分列插入：值的顺序与给出的列顺序对应

	insert into 表名(列1,...) values(值1,...)
	例：
	insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
1
2
3

• 上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信
• 全列多行插入：值的顺序与给出的列顺序对应

	insert into 表名 values(...),(...)...;
	例：
	insert into classes values(0,'python1'),(0,'python2');
	
	insert into 表名(列1,...) values(值1,...),(值1,...)...;
	例：
	insert into students(name) values('杨康'),('杨过'),('小龙女');
1
2
3
4
5
6
7

修改
格式: 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;
1
2
3

删除
格式：DELETE FROM tbname [where 条件判断]

	delete from 表名 where 条件
	例：
	delete from students where id=5;
1
2
3

• 逻辑删除，本质就是修改操作

	update students set isdelete=1 where id=1;
1

数据库的备份和恢复

• 备份
  退出MySQL客户端，在命令行或者终端输入以下命令：

	mysqldump –uroot –p 数据库名 > python.sql;
	# 按提示输入密码
1
2

• 恢复
  需要先创建一个新的空数据库，然后同样在命令行或者终端输入以下命令：

	mysql -uroot –p 新数据库名 < python.sql
	# 根据提示输入mysql密码
1
2

三、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;
1

例：查询编号不大于4的学生

select * from students where id <= 4;
1

逻辑运算符

• and
• or
• not

例：查询编号大于3的女同学

select * from students where id > 3 and gender=0;
1

例：查询编号小于4或没被删除的学生

select * from students where id < 4 or is_delete=0;
1

模糊查询

• like：
• %表示任意多个任意字符
• _表示一个任意字符

例：查询姓黄的学生

select * from students where name like '黄%';
1

例：查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';
1

例：查询姓黄或叫靖的学生

select * from students where name like '黄%' or name like '%靖';
1

范围查询

• in表示在一个非连续的范围内
• between … and …表示在一个连续的范围内

例：查询编号是1或3或8的学生

select * from students where id in(1,3,8);
1

例：查询编号为3至8的学生

select * from students where id between 3 and 8;
1

例：查询编号是3至8的男生

select * from students where (id between 3 and 8) and gender=1;
1

空判断

• 注意：null与’ '是不同的
• 判空is null
• 判非空is not null

例：查询没有填写身高的学生

select * from students where height is null;
1

例：查询填写了身高的学生

select * from students where height is not null;
1

例：查询填写了身高的男生

select * from students where height is not null and gender=1;
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;
1

例：查询未删除学生信息，按名称升序

select * from students where is_delete=0 order by name;
1

例：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序

select * from students  order by age desc,height desc;
1

聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

总数

• count(*)表示计算总行数，括号中写星与列名，结果是相同的

例：查询学生总数

select count(*) from students;
1

最大值

• max(列)表示求此列的最大值

例：查询女生的编号最大值

select max(id) from students where gender=2;
1

最小值

• min(列)表示求此列的最小值

例：查询未删除的学生最小编号

select min(id) from students where is_delete=0;
1

求和

• sum(列)表示求此列的和

例：查询男生的总年龄

select sum(age) from students where gender=1;
1

平均年龄

select sum(age)/count(*) from students where gender=1;
1

平均值

• avg(列)表示求此列的平均值

例5：查询未删除女生的编号平均值

select avg(id) from students where is_delete=0 and gender=2;
1

分组(group)

group by

1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
2. group by可用于单个字段分组，也可用于多个字段分组

select gender from students group by gender;
1

当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;
1
2

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;
1
2
3

group by + having

1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
2. 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;
1
2

分页(limit)

获取部分行

当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法

• select * from 表名 limit start,count

说明

• 从start开始，获取count条（行）数据

例：查询前3行男生信息

select * from students where gender=1 limit 0,3;
1

示例：分页

• 已知：每页显示m条数据，当前显示第n页
• 求总页数：此段逻辑后面会在python中实现
  • 查询总条数p1
  • 使用p1除以m得到p2
  • 如果整除则p2为总数页
  • 如果不整除则p2+1为总页数
• 求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m
1

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
mysql支持三种类型的连接查询，分别为：

• 内连接查询：查询的结果为两个表匹配到的数据
  

• 右连接查询：查询的结果为两个表匹配到的数据、右表特有的数据，对于左表中不存在的数据使用null填充
  

• 左连接查询：查询的结果为两个表匹配到的数据、左表特有的数据，对于右表中不存在的数据使用null填充
  

语法：

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

例：使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id = classes.id;
1

例：使用左连接查询班级表与学生表

• 此处使用了as为表起别名，目的是编写简单

select * from students as s left join classes as c on s.cls_id = c.id;
1

例：使用右连接查询班级表与学生表

select * from students as s right join classes as c on s.cls_id = c.id;
1

例：查询学生姓名及班级名称

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
1

子查询

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询

主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)

标量子查询

• 查询班级学生平均年龄
• 查询大于平均年龄的学生

查询班级学生的平均身高

select * from students where age > (select avg(age) from students);
1

列级子查询

• 查询还有学生在班的所有班级名字
• 1.找出学生表中所有的班级 id
  2.找出班级表中对应的名字

select name from classes where id in (select cls_id from students);
1

行级子查询

• 需求: 查找班级年龄最大,身高最高的学生
• 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素

select * from students where (height,age) = (select max(height),max(age) from students);
1

子查询中特定关键字使用

• in 范围
  • 格式: 主查询 where 条件 in (列子查询)

总结

• 完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
1
2
3
4
5
6

• 执行顺序为：
  • from 表名
  • where …
  • group by …
  • select distinct *
  • having …
  • order by …
  • limit start,count
• 实际使用中，只是语句中某些部分的组合，而不是全部