MySQL操作

一、操作数据库

MySQL中可以创建多个数据库用于存储不同场景的表结构，学习MySQL之前，我们要先理清如下的关系：

数据库 --> 数据表 --> 字段

抛开数据库存储上限去考虑，每个数据库中可以包含无数个数据表，而每个数据表又可以包含无数个字段，因此我们的学习也应该从创建数据库开始。

• 创建数据库

create database 数据库名 charset=utf8; 例： create database mydata charset=utf8; 

• 查看所有数据库 : show databases;

show databases;  

• 使用数据库

use 数据库名; 

• 查看当前使用的数据库

select database(); 

• 删除数据库

drop database 数据库名; 例： drop database mydata; 

二、数据表常见操作

创建好了数据库，我们就可以在某个数据库中进行数据表的创建与操作了，数据表是MySQL语法中的核心部分，数据工作者的多数时间都在进行数据表中各个字段的“增删改查”。

1. 基本操作

• 查看当前数据库中所有表

show tables; 

• 查看表结构

desc 表名; 

2. 创建表

给定字段名和数据类型时，我们就可以进行数据表的创建了，当然创建表的时候也有很多特殊的可选项，具体如下：

• 数据表创建示例（简版）：

  • column：字段名
  • datatype：数据类型

• MySQL常用数据类型介绍：点击此处

CREATE TABLE table_name(     column1 datatype,     column2 datatype,     column3 datatype,     .....     columnN datatype ); 

• 例：创建分数表

create table Score(     name varchar(20),     age tinyint,     score int ); 

• 例：创建学生表

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 ); 

上述代码块创建的时候使用了许多特殊语句，解释如下：

• unsigned：限制整数类型为无符号数据（没有负数）。
• primary key：主键约束，在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。
• auto_increment：主键自动增长，从1开始，每多插入一条数据，主键值+1。
• default：为该字段设定默认值。
• decimal：保留准确精确度的列，例如decimal(5,2)标识一共能存储5位数，小数点后可以有2位，因此可保存数据的范围是-999.99-999.99。
• enum：只能从其中选择一个值插入到数据库。

3. 修改表

数据库中创建好的内容不是一成不变的，我们可以随时对表结构进行增删改的操作。

• 添加字段

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; 

4. 删除表

建议慎用所有的删除语句。

drop table 表名; 例： drop table students; 

5. 查看表的创建语句

show create table 表名; 例： show create table classes; 

三、添加数据

表创建好之后，我们可以使用SQL语句添加数据，但并不是所有场景都会用到该语句，工作中数据添加的方式多是：端口实时同步，表格导入等。

• 完整格式

insert into 表名 ('字段名1', '字段名2') values(值1, 值2); 

• 省略格式
  • 使用省略格式时，需要在values中填写所有字段对应的值，否则将会报错。

insert into 表名 values(值1, 值2); 

• 完整格式示例

insert into students(id, name, age, height, gender, cls_id) values (10001, 'xiaoming', 22, 180, '男', 1); 

• 省略格式示例

insert into students values (10002, 'xiaolv', 23, 165, '女', 1); 

• 一次添加多列数据

insert into students(id, name, age, height, gender, cls_id) values (10003, 'xiaohu', 22, 175, '男', 2), (10004, 'xiaoli', 24, 160, '女', 1), (10005, 'xiaoxi', 23, 167, '女', 2); 

四、查询数据

表查询操作是数据库中最重要的操作（没有之一），可以说对于数据库，每天的工作就是组合各种各样的查询语句进行不同场景的数据查询。

1. 基础查询

• 查询所有列

select * from 表名; 例： select * from students; 

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

select 列1,列2,... from 表名; 例： select id, name from students; 例（使用别名）： select id as id2, name from students; 

2. 条件查询

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

• 语法如下：

select * from 表名 where 条件; 例： select * from students where id=10001; 

• where后面支持多种运算符，进行条件的处理
  • 比较运算符
  • 逻辑运算符
  • 模糊查询
  • 范围查询
  • 空判断

比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

• 例1：查询编号大于10003的学生

select * from students where id > 10003; 

• 例2：查询编号不大于10004的学生

select * from students where id <= 10004; 

• 例3：查询姓名不是“xiaoming”的学生

select * from students where name != 'xiaoming'; 

逻辑运算符

• and
• or
• not

• 例1：查询编号大于10003的女同学

select * from students where id > 3 and gender='女'; 

• 例2：查询编号小于10002或年龄大于24学生

select * from students where id < 10002 or age > 24; 

模糊查询

• MySQL中使用like语句进行模糊查询，模糊查询常用的符号如下：
• %表示任意多个任意字符
• _表示一个任意字符

• 查询名字以’xiaol’开头并且末尾只有一个字母的学生（xiao ming=xiaomin+g）

select * from students where name like 'xiaol_'; 

• 例：查询叫’hu’的学生

select * from students where name like '%hu'; 

范围查询

• in表示在一个非连续的范围内进行检索，查询数据是否在给定范围内。

• 例1：查询编号是10002或10004的学生：

select * from students where id in(10002,10004); 

• between … and …表示在一个连续的范围内进行检索

• 例2：查询编号为10002至10004的学生

select * from students where id between 10002 and 10004; 

空判断

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

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

# 插入带有空值的数据 insert into students(id, name, age) values (10006, 'xiaomei', 30);  # 查询没有填写身高的学生 select * from students where height is null; 

• 判非空is not null

• 例2：查询填写了性别的学生

select * from students where gender is not null; 

条件优先级

• 查询条件的优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

3. 排序

为了方便查看数据，可以对数据进行排序

语法：

select * from 表名 order by 列1 asc|desc ,列2 asc|desc,...; 

说明

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列（asc）
• asc从小到大排列，即升序
• desc从大到小排序，即降序

示例

• 例1：查询1班女生信息，按学号降序

select * from students where gender='女' and cls_id=1 order by id desc; 

• 例2：查询1班学生信息，按身高升序

select * from students where cls_id=1 order by height; 

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

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

4. 聚合查询

操作数据的时候，我们经常需要进行统计计算，SQL中提供了多种聚合函数可以让我们快速计算。

计数

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

• 例1：查询学生总数

select count(*) from students; 

最大值/最小值

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

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

select max(id) from students where gender='女'; 

求和

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

• 例3：查询男生的总年龄

select sum(age) from students where gender='男'; 

平均值

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

• 例4：查询女生年龄的平均值

select avg(age) from students where gender='女'; 

聚合函数组合

• 聚合函数之间也可以组合进行使用，比如我们可以使用count和sum来求得平均值。
• 例：查询女生年龄的平均值

select sum(age)/count(*) as avg_age from students where gender='女'; 

5.分组查询

• 分组查询在实际工作中经常会用到，当我们需要统计某一字段下不同类别的统计数据时就需要用到分组查询了，比如：统计各个性别的学生数量，各个班级的男女生数量等。

group by

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

• 直接使用group by的方法如下

select gender from students group by gender; 

上段代码根据gender字段来分组，gender字段的全部值有3个’男’,‘女’,NULL,所以出现了三个数据，但是单独使用groupby是没有过多意义的（可以作为去重来使用），通常分组函数要配合聚合函数/concat函数一同使用。

group by + group_concat()

1. group_concat(字段名)可以作为一个输出字段来使用，
2. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

• 查询各个性别的学生名字集合

select gender,group_concat(name) from students group by gender; 

group by + 集合函数

1. 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

• 分别统计各个性别的平均身高

select gender,avg(height) from students group by gender; 

group by + having

1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

• 查找班级人数超过2的班级

select cls_id, count(*) from students group by cls_id having count(*)>2; 

6.分页查询

当数据量过大时，在一页中查看数据是一件非常麻烦的事情，我们可以使用limit进行限制进行分页查询。

语法

• start表示查询开始位置（第几行开始），end表示查询结束位置（第几行结束），可以省略填写start默认从0开始到end结束。

select * from 表名 limit start,end 

• 例1：查询前2行的女生信息

select * from students where gender='女' limit 2; 

• 例2：查询前2行的女生信息（2）

select * from students where gender='女' limit 0,2; 

7.连接查询

在很多工作中我们需要面对成百上千个不同的数据表，当进行查询的时候，我们需要的结果也会来自于不同的表，这种情况下查询时我们需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

• 内连接查询

  查询的结果为匹配字段相同时两个表匹配到的数据

• 左连接查询

  查询的结果为右表匹配字段中的值在左表的匹配字段中存在时匹配到的数据，左表特有的数据，对于左表中无法被右表匹配的数据使用null填充

• 右连接查询

  查询的结果为左表匹配字段中的值在右表的匹配字段中存在时匹配到的数据，右表特有的数据，对于右表中无法被左表匹配的数据使用null填充

• 创建示例数据表

-- 分数表 create table scores (   id int, 	subject varchar(20), 	score int );  -- 学生表 create table students2 (   id int, 	name varchar(20) ); 

• 添加数据

insert into students2 values (10001, '张三'), (10002, '李四'), (10003, '王五'), (10004, '赵六');   insert into scores values (10001, '数学', 80), (10001, '语文', 90), (10003, '数学', 70), (10004, '数学', 100), (10005, '语文', 70); 

语法

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

• 例1：使用内连接查询

select students2.id, name, subject, score  from students2 inner join scores on students2.id = scores.id; 

• 例2：使用左连接查询

select students2.id, name, subject, score  from students2 left join scores on students2.id = scores.id; 

• 例3：使用右连接查询

select scores.id, name, subject, score  from students2 right join scores on students2.id = scores.id; 

8. 子查询

子查询概念

我们在进行select查询的时候，查询的结果本质上也是一张数据表，我们可以使用自查询的方式继续对该表进行查询。

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

主查询

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

主查询和子查询的关系

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

• 例1：标量子查询

1. 查询班级学生所有科目平均分
2. 查询大于等于平均分的记录

select * from scores where score >= (select avg(score) from scores); 

• 例2：列级子查询

1. 具有考试成绩的学生id

2. 找出学生表中对应的名字

select name from students2 where id in (select id from scores); 

in () 括号内的内容表示查询范围，数据在括号中存在则满足。

• 例3：表级别子查询

1. 查询成绩表里具有数学成绩的学生
2. 找出符合条件的学生姓名

select * from students2 inner join (select * from scores  where subject='数学') a on students2.id = a.id; 

上述代码中，我们对一个查询到的结果表进行了join。在使用该方法操作时，我们使用()将自查询扩起来，并在末尾对查询结果临时命名为a，当命名为a后，我们可以认为有数据表a可以供我们使用，a中的内容就是我们查询到的结果，接下来按照常规数据表连接查询方式进行操作即可。