MySQL｜基础操作+8大查询方式汇总

MySQL操作

一、操作数据库

   MySQL中可以创建多个数据库用于存储不同场景的表结构，学习MySQL之前，我们要先理清如下的关系：
  数据库 --> 数据表 --> 字段
  抛开数据库存储上限去考虑，每个数据库中可以包罗无数个数据表，而每个数据表又可以包罗无数个字段，因此我们的学习也应该从创建数据库开始。
  

• 创建数据库
  

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

复制代码

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

1. show databases;

复制代码

• 利用数据库
  

1. use 数据库名;

复制代码

• 查看当前利用的数据库
  

1. select database();

复制代码

• 删除数据库
  

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

复制代码

二、数据表常见操作

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

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

1. show tables;

复制代码

• 查看表结构
  

1. desc 表名;

复制代码

2. 创建表

   给定字段名和数据范例时，我们就可以举行数据表的创建了，当然创建表的时间也有许多特殊的可选项，详细如下：
  

• 数据表创建示例（简版）：
  
  
  
  • column：字段名
    
  • datatype：数据范例
    
  
  
• MySQL常用数据范例介绍：点击此处
  

1. CREATE TABLE table_name(
2.     column1 datatype,
3.     column2 datatype,
4.     column3 datatype,
5.     .....
6.     columnN datatype
7. );

复制代码

• 例：创建分数表
  

1. create table Score(
2.     name varchar(20),
3.     age tinyint,
4.     score int
5. );

复制代码

• 例：创建学生表
  

1. create table students(
2.     id int unsigned primary key auto_increment not null,
3.     name varchar(20) default '',
4.     age tinyint unsigned default 0,
5.     height decimal(5,2),
6.     gender enum('男','女','保密'),
7.     cls_id int unsigned default 0
8. );

复制代码

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

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

  3. 修改表

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

• 添加字段
  

1. alter table 表名 add 列名 类型;
2. 例：
3. alter table students add birthday datetime;

复制代码

• 修改表-修改字段名
  

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

复制代码

• 修改表-修改字段范例
  

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

复制代码

• 修改表-删除字段
  

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

复制代码

4. 删除表

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

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

复制代码

5. 查看表的创建语句

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

复制代码

三、添加数据

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

• 完整格式
  

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

复制代码

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

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

复制代码

• 完整格式示例
  

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

复制代码

• 省略格式示例
  

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

复制代码

• 一次添加多列数据
  

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

复制代码

四、查询数据

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

• 查询所有列
  

1. select * from 表名;
2. 例：
3. select * from students;

复制代码

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

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

复制代码

2. 条件查询

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

• 语法如下：
  

1. select * from 表名 where 条件;
2. 例：
3. select * from students where id=10001;

复制代码

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

比较运算符

• 即是: =
  
• 大于: >
  
• 大于即是: >=
  
• 小于: <
  
• 小于即是: <=
  
• 不即是: != 或 <>
  

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

1. select * from students where id > 10003;

复制代码

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

1. select * from students where id <= 10004;

复制代码

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

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

复制代码

逻辑运算符

• and
  
• or
  
• not
  

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

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

复制代码

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

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

复制代码

模糊查询

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

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

1. select * from students where name like 'xiaol_';

复制代码

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

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

复制代码

范围查询

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

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

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

复制代码

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

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

1. select * from students where id between 10002 and 10004;

复制代码

空判断

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

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

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

复制代码

• 判非空is not null
  

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

1. select * from students where gender is not null;

复制代码

条件优先级

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

3. 排序

   为了方便查看数据，可以对数据举行排序
  语法：

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

复制代码

说明

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

示例

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

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

复制代码

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

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

复制代码

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

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

复制代码

4. 聚合查询

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

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

• 例1：查询学生总数
  

1. select count(*) from students;

复制代码

最大值/最小值

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

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

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

复制代码

求和

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

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

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

复制代码

平均值

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

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

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

复制代码

聚合函数组合

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

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

复制代码

5.分组查询

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

group by

• group by的寄义:将查询效果按照1个或多个字段举行分组，字段值相同的为一组
  
• group by可用于单个字段分组，也可用于多个字段分组
  

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

1. select gender from students group by gender;

复制代码

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

• group_concat(字段名)可以作为一个输出字段来利用，
  
• 表示分组之后，根据分组效果，利用group_concat()来放置每一组的某字段的值的聚集
  

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

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

复制代码

group by + 聚集函数

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

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

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

复制代码

group by + having

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

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

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

复制代码

6.分页查询

   当数据量过大时，在一页中查看数据是一件非常贫苦的事变，我们可以利用limit举行限制举行分页查询。
  语法

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

1. select * from 表名 limit start,end

复制代码

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

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

复制代码

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

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

复制代码

7.连接查询

   在许多工作中我们需要面对成百上千个不同的数据表，当举行查询的时间，我们需要的效果也会来自于不同的表，这种情况下查询时我们需要将多张表连接成一个大的数据集，再选择符合的列返回
  mysql支持三种范例的连接查询，分别为：

• 内连接查询
  查询的效果为匹配字段相同时两个表匹配到的数据
  

• 左连接查询
  查询的效果为右表匹配字段中的值在左表的匹配字段中存在时匹配到的数据，左表特有的数据，对于左表中无法被右表匹配的数据利用null添补
  

• 右连接查询
  查询的效果为左表匹配字段中的值在右表的匹配字段中存在时匹配到的数据，右表特有的数据，对于右表中无法被左表匹配的数据利用null添补
  

• 创建示例数据表
  

1. -- 分数表
2. create table scores (
3.   id int,
4.         subject varchar(20),
5.         score int
6. );
8. -- 学生表
9. create table students2 (
10.   id int,
11.         name varchar(20)
12. );

复制代码

• 添加数据
  

1. insert into students2 values
2. (10001, '张三'),
3. (10002, '李四'),
4. (10003, '王五'),
5. (10004, '赵六');
8. insert into scores values
9. (10001, '数学', 80),
10. (10001, '语文', 90),
11. (10003, '数学', 70),
12. (10004, '数学', 100),
13. (10005, '语文', 70);

复制代码

语法

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

复制代码

• 例1：利用内连接查询
  

1. select students2.id, name, subject, score
2. from
3. students2
4. inner join
5. scores
6. on students2.id = scores.id;

复制代码

• 例2：利用左连接查询
  

1. select students2.id, name, subject, score
2. from
3. students2
4. left join
5. scores
6. on students2.id = scores.id;

复制代码

• 例3：利用右连接查询
  

1. select scores.id, name, subject, score
2. from
3. students2
4. right join
5. scores
6. on students2.id = scores.id;

复制代码

8. 子查询

子查询概念
   我们在举行select查询的时间，查询的效果本质上也是一张数据表，我们可以利用自查询的方式继续对该表举行查询。
    在一个 select 语句中，嵌入了别的一个 select 语句，那么被嵌入的 select 语句称之为子查询语句。
  主查询
   重要查询的对象,第一条 select 语句
  主查询和子查询的关系

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

• 例1：标量子查询
  

• 查询班级学生所有科目平均分
  
• 查询大于即是平均分的记录
  

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

复制代码

• 例2：列级子查询
  

• 具有考试成绩的学生id
  
• 找出学生表中对应的名字
  

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

复制代码

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

• 例3：表级别子查询
  

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

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

复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。