1 简述

• 本文以MYSQL数据库为例，讲述下数据库的基础操作之增删改查

2 MYSQL数据库安装

•   sudo apt-get install mysql-server mysql-client
  

• 数据库编程还需要安装开发包

•  sudo apt-get install libmysqlclient-dev
  

3 MYSQL数据库登录

• 安装成功后使用以下命令登录

•   mysql -h [主机名或ip] -u [用户] -p
  

• eg

•   mysql -u root -p
  

• 登录成功后，就进入了以下界面

•   root@ubuntu:/home/lng# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 17Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 
  

• 退出
• exit 或者 quit

4 库的操作

• 库就是仓库，主要存放数据表。
• 库的操作比较简单，主要是以下四种：

4.1 显示库信息

• show databases
• 该命令主要查看数据库中所有的库信息
• eg

•   mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)
  

4.2 创建库

• create database 库名
• eg

•   mysql> create database csdn;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| csdn               || information_schema || mysql              || performance_schema || sys                |	+--------------------+5 rows in set (0.00 sec)mysql> 
  

• 创建成功后，我们再查看，就会看到多了一个csdn的库

4.3 删除库

• drop database 库名
• eg

•   mysql> drop database csdn;Query OK, 0 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)mysql> 
  

• 删除库后，再查看，刚才创建的库就被删除了
• 删库时一定要谨慎操作，库一旦被删除，库下面所有的数据表也会被删除掉。

4.4 选择库

• use 库名
• 如果要对库下面的表进行操作，就要先选择库

•   mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 
  

5 表的操作

5.1 数据类型

• 介绍数据表的操作之前先介绍下mysql数据库的数据类型

• 数值型

  类型	字节	说明
  TINYINT	1	整数型
  SAMLLINT	2	整数型
  INT	4	整数型
  BIGINT	8	整数型
  FLOAT	4	浮点型
  DOUBLE	8	浮点型

• 字符串型

  类型	字节	说明
  CHAR	0 ~ 255	定长字符串
  VARCHAR	0 ~ 255	不定长字符串

• 时间日期型

  类型	字节	范围	格式	说明
  DATA	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期型
  TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
  YEAR	1	1901/2155	YYYY	年份值
  DATETIME	8	1000-01-01 00:00:00/9999-12-21 23:59:59	YYYY-MM-DDHH:MM:SS	日期和时间值

5.2 约束

• 约束是对插入数据库中的数据进行限定，这么做的目的是为了保证数据的有效性和完整性。

  约束关键字	含义
  NOT NULL	约束字段值不能为空
  DEFAULT	设置字段默认值
  UNIQUE KEY	设置字段的值是唯一的
  PRIMARY KEY	设置字段为表的主键
  AUTO_INCREMENT	设置字段值为自动增加
  FOREIGN KEY	设置字段为表的外键

5.3 创建表

•   create table 表名(字段名1 数据类型 约束条件，字段名2 数据类型 约束条件，...)
  

• eg

•   mysql> create database CSDN;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| CSDN               || information_schema || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)mysql> use CSDN;Database changedmysql> create table student(id INT PRIMARY KEY,name VARCHAR(30),age INT,score FLOAT);Query OK, 0 rows affected (0.02 sec)
  

• 创建表前，先要选择库，如果没有，可以先创建一个库。表创建好后，可以使用desc命令查看表结构。

5.4 查看表结构

•   describe/desc 表名	
  

• eg

•   mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | float       | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)mysql> 
  

• 创建好表后，可以查看表的结构。可以看到表的字段名，对应字段的数据类型以及约束等信息

5.5 删除表

•   	drop table 表名
  

5.6 修改表

5.6.1 修改表名

•   alter table 旧表名 rename 新表名
  

5.6.2 增加字段

• 在表的最后一个位置增加字段

  •   alter table 表名 add 字段名 字段数据类型
    

  • eg

  •   mysql> alter table students add class INT;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | float       | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)mysql> 
    

  • 在最后一个位置插入一个class字段
• 在表的第一个位置增加字段

  •   alter table 表名 add 字段名 字段数据类型 first
    

  • eg

  •   mysql> alter table students add code INT first;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| code  | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | float       | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql> 
    

  • 将code字段添加到表的第一个位置
• 在表的指定位置增加字段

  •   alter table 表名 add 字段名 字段数据类型 after 字段名
    

  • eg

  •   mysql> alter table students add sex VARCHAR(10) after name;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| code  | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || sex   | varchar(10) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | float       | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+7 rows in set (0.01 sec)mysql> 
    

  • 我们可以在name字段后面加一个sex字段

5.6.3 删除字段

•   alter table 表名 drop 字段名
  

5.6.4 修改字段

• 修改字段的数据类型

  •   alter table 表名 modify 字段名 字段新的数据类型 
    

  • eg

  •   mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| code  | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || sex   | varchar(10) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | float       | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+7 rows in set (0.01 sec)mysql> alter table students modify score DOUBLE;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| code  | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || sex   | varchar(10) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | double      | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> 
    

  • 可以将score字段的数据类型由float改为double
• 修改字段名称

  •   alter table 表名 change 旧字段名 新字段名 旧字段类型
    

  • eg

  •   mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| code  | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || sex   | varchar(10) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | double      | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> alter table students change code lcode INT;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc students;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| lcode | int         | YES  |     | NULL    |       || id    | int         | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || sex   | varchar(10) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || score | double      | YES  |     | NULL    |       || class | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> 
    

  • 将code字段名称修改为了lcode
• 修改字段名称和类型

  •   alter table 表名 change 旧字段名 新字段名 新字段类型
    

• 修改字段顺序
  • 将字段放到第一个位置

  •   alter table 表名 modify 字段名 字段类型 first
    

  • 将字段放到指定位置

  •   alter table 表名 modify 字段名1 字段类型 after 字段名2
    

• 约束设置
  • 待完成

6 数据的操作

• 前面介绍了表的一些操作，接下来就主要介绍下数据的增删改查

6.1 插入数据

• 单条数据插入

  •   insert into 表名(字段1, 字段2, 字段3, ...) values(值1, 值2, 值3, ...)
    

  • eg

  •   mysql> insert into students(id,name,age,score) values(1001,'TOM',22,90.5);Query OK, 1 row affected (0.00 sec)mysql> select * from students;+------+------+------+-------+| id   | name | age  | score |+------+------+------+-------+| 1001 | TOM  |   22 |  90.5 |+------+------+------+-------+1 row in set (0.00 sec)mysql> 
    

• 多条数据插入

  •   insert into 表名(字段1, 字段2, 字段3, ...) values(值11, 值21, 值31, ...),(值12, 值22, 值32, ...),...
    

  • eg

  •   mysql> insert into students(id,name,age,score) values(1002,'JACK',18,80.0),(1003,'LUCY',20,98.5);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from students;+------+------+------+-------+| id   | name | age  | score |+------+------+------+-------+| 1001 | TOM  |   22 |  90.5 || 1002 | JACK |   18 |    80 || 1003 | LUCY |   20 |  98.5 |+------+------+------+-------+3 rows in set (0.00 sec)mysql> 
    

6.2 更新数据

• 更新特定数据

  •   updata 表名 set 字段1=值1, 字段2=值2, ..., where 条件
    

  • eg

  •   mysql> select * from students;+------+--------+------+-------+| id   | name   | age  | score |+------+--------+------+-------+| 1001 | TOM    |   22 |  90.5 || 1002 | JACK   |   18 |    80 || 1003 | LUCY   |   16 |   100 || 1004 | 曹操   |   18 |    80 |+------+--------+------+-------+4 rows in set (0.00 sec)mysql> update students set age=20,score=99.0 where id=1003;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from students;+------+--------+------+-------+| id   | name   | age  | score |+------+--------+------+-------+| 1001 | TOM    |   22 |  90.5 || 1002 | JACK   |   18 |    80 || 1003 | LUCY   |   20 |    99 || 1004 | 曹操   |   18 |    80 |+------+--------+------+-------+4 rows in set (0.00 sec)mysql> 
    

• 更新所有数据

  •   update 表名 set 字段1=值1, 字段2=值2, ...
    

  • eg

  •   mysql> select * from students;+------+--------+------+-------+| id   | name   | age  | score |+------+--------+------+-------+| 1001 | TOM    |   22 |  90.5 || 1002 | JACK   |   18 |    80 || 1003 | LUCY   |   20 |    99 || 1004 | 曹操   |   18 |    80 |+------+--------+------+-------+4 rows in set (0.00 sec)mysql> update students set age=20,score=80;Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> select * from students;+------+--------+------+-------+| id   | name   | age  | score |+------+--------+------+-------+| 1001 | TOM    |   20 |    80 || 1002 | JACK   |   20 |    80 || 1003 | LUCY   |   20 |    80 || 1004 | 曹操   |   20 |    80 |+------+--------+------+-------+4 rows in set (0.00 sec)mysql> 
    

6.3 删除数据

• 删除指定数据

  •   delete from 表名 where 条件	
    

  • eg

  •   mysql> select * from students;+------+--------+------+-------+| id   | name   | age  | score |+------+--------+------+-------+| 1001 | TOM    |   20 |    80 || 1002 | JACK   |   20 |    80 || 1003 | LUCY   |   20 |    80 || 1004 | 曹操   |   20 |    80 |+------+--------+------+-------+4 rows in set (0.00 sec)mysql> delete from students where name='曹操';Query OK, 1 row affected (0.00 sec)mysql> select * from students;+------+------+------+-------+| id   | name | age  | score |+------+------+------+-------+| 1001 | TOM  |   20 |    80 || 1002 | JACK |   20 |    80 || 1003 | LUCY |   20 |    80 |+------+------+------+-------+3 rows in set (0.00 sec)mysql> 
    

• 删除所有数据

  •   delete from 表名
    

6.4 查询数据

• 我先贴一下原始数据，下面的所有数据查询结果都可以跟原始数据比较下

•   	+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1003 | LUCY | girl |   20 |  10.5 |     2 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1005 | BOB  | boy  |   19 |  86.9 |     2 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 |+------+------+------+------+-------+-------+
  

6.4.1 简单数据查询

• 查询所有字段

  •   select * from 表名
    

  • eg

  •   mysql> select * from students;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1003 | LUCY | girl |   20 |  10.5 |     2 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1005 | BOB  | boy  |   19 |  86.9 |     2 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 |+------+------+------+------+-------+-------+8 rows in set (0.00 sec)mysql> 
    

• 查询指定字段

  •   select 字段1,字段2,... from 表名
    

  • 比如我们只查询id，姓名和班级

  •   mysql> select id,name,class from students;+------+------+-------+| id   | name | class |+------+------+-------+| 1001 | TOM  |     1 || 1002 | JACK |     1 || 1003 | LUCY |     2 || 1004 | ANDY |     1 || 1005 | BOB  |     2 || 1006 | AMY  |     1 || 1007 | EMMY |     2 || 1008 | DAWN |     2 |+------+------+-------+8 rows in set (0.00 sec)mysql> 
    

• 避免重复数据查询

  •   select distinct 字段1,字段2,... from 表名
    

  • 比如数据表中学生数据可能有上千条，但班级可能就几十个，我们想查询都有哪些班级。就可以去掉重复信息查询。

  •   mysql> select distinct class from students;+-------+| class |+-------+|     1 ||     2 |+-------+2 rows in set (0.00 sec)mysql> 
    

• 实现数字四则运算数据查询

  •   select 字段1 运算符 运算符操作数, 字段2 运算符 运算符操作数, ... from 表名
    

  • 运算符包括：+ - * / %

  •   mysql> select id + 1, name from students;+--------+------+| id + 1 | name |+--------+------+|   1002 | TOM  ||   1003 | JACK ||   1004 | LUCY ||   1005 | ANDY ||   1006 | BOB  ||   1007 | AMY  ||   1008 | EMMY ||   1009 | DAWN |+--------+------+8 rows in set (0.00 sec)mysql> 
    

• 给字段起别名

  •   select 字段1 as 字符串, 字段2 as 字符串, ... from 表名
    

  • eg

  •   mysql> select name as '姓名', sex as '性别', age as '年龄', score as '分数', class as '班级' from students;+--------+--------+--------+--------+--------+| 姓名   | 性别   | 年龄   | 分数   | 班级   |+--------+--------+--------+--------+--------+| TOM    | boy    |     18 |     80 |      1 || JACK   | boy    |     20 |   85.5 |      1 || LUCY   | girl   |     20 |   10.5 |      2 || ANDY   | girl   |     20 |   82.2 |      1 || BOB    | boy    |     19 |   86.9 |      2 || AMY    | girl   |     18 |   70.5 |      1 || EMMY   | girl   |     19 |   99.5 |      2 || DAWN   | boy    |     21 |   95.5 |      2 |+--------+--------+--------+--------+--------+8 rows in set (0.00 sec)mysql> 
    

6.4.2 条件数据查询

• •   	select 字段 from 表名 where 条件
    

  • 条件可使用关系运算符和逻辑运算符
  • 关系运算符：> >= < <= = !=
  • 逻辑运算符：and or not
• 单条件数据查询

  •   select 字段 from 表名 where 条件
    

  • 比如我们只查询1班的学生信息

  •   mysql> select * from students where class=1;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1006 | AMY  | girl |   18 |  70.5 |     1 |+------+------+------+------+-------+-------+4 rows in set (0.00 sec)mysql> 
    

• 多条件数据查询

  •   select 字段 from 表名 where 条件1 and 条件2
    

  • 比如我们查询1班女生的学生信息

  •   mysql> select * from students where class=1 and sex='girl';+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1004 | ANDY | girl |   20 |  82.2 |     1 || 1006 | AMY  | girl |   18 |  70.5 |     1 |+------+------+------+------+-------+-------+2 rows in set (0.00 sec)mysql> 
    

• 符合范围的数据查询

  •   select 字段 from 表名 where 字段名 between 值1 and 值2
    

  • 比如我们要查询分数在80到90区间段的学生信息

  •   mysql> select * from students where score between 80 and 90;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1005 | BOB  | boy  |   19 |  86.9 |     2 |+------+------+------+------+-------+-------+4 rows in set (0.00 sec)mysql> 
    

• 不符合范围的数据查询

  •   select 字段 from 表名 where 字段名 not between 值1 and 值2
    

  • 我们要查询分数不在80到90区间段的学生信息

  •   mysql> select * from students where score not between 80 and 90;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1003 | LUCY | girl |   20 |  10.5 |     2 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 |+------+------+------+------+-------+-------+4 rows in set (0.00 sec)mysql> 
    

• 空值查询

  •   select 字段 from 表名 where 字段名 is null
    

  • 我们查询下字段为空的数据。因为数据里没有空值，所以查询结果为空。

  •   mysql> select * from students where id is null;Empty set (0.00 sec)
    

• 非空值查询

  •   select 字段 from 表名 where 字段名 is not null
    

  •   mysql> select * from students where id is not null;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1003 | LUCY | girl |   20 |  10.5 |     2 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1005 | BOB  | boy  |   19 |  86.9 |     2 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 |+------+------+------+------+-------+-------+8 rows in set (0.00 sec)mysql> 
    

• 带in关键字的集合查询

  •   select 字段 from 表名 where 字段名 in(值1, 值2, ...)
    

  • 我们只查询TOM和JACK的信息

  •   mysql> select * from students where name in('TOM','JACK');+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1001 | TOM  | boy  |   18 |    80 |     1 || 1002 | JACK | boy  |   20 |  85.5 |     1 |+------+------+------+------+-------+-------+2 rows in set (0.00 sec)mysql> 
    

• 不在集合中的数据查询

  •   select 字段 from 表名 where 字段名 not in(值1, 值2, ...)
    

  • 查询除了TOM和JACK的其他学生信息

  •   mysql> select * from students where name not in('TOM','JACK');+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1003 | LUCY | girl |   20 |  10.5 |     2 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1005 | BOB  | boy  |   19 |  86.9 |     2 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 |+------+------+------+------+-------+-------+6 rows in set (0.00 sec)mysql> 
    

• 带like关键字的模糊查询

  •   select 字段 from 表名 where 字段名 like 值
    

  • “%” 通配任意多字符，"_"通配一个字符
  • 我们查询下姓名以字母A开头的学生信息和姓名以字母A开头且只有3个字母组成的学生信息

  •   mysql> select * from students where name like 'A%';+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1004 | ANDY | girl |   20 |  82.2 |     1 || 1006 | AMY  | girl |   18 |  70.5 |     1 |+------+------+------+------+-------+-------+2 rows in set (0.00 sec)mysql> select * from students where name like 'A__';+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1006 | AMY  | girl |   18 |  70.5 |     1 |+------+------+------+------+-------+-------+1 row in set (0.00 sec)mysql> 
    

6.4.3 排序查询

•   select 字段名 from 表名 order by 字段名 asc/desc
  

• asc : 升序， desc : 降序
• 以成绩降序查询学生信息

•  mysql> select * from students order by score desc;+------+------+------+------+-------+-------+| id   | name | sex  | age  | score | class |+------+------+------+------+-------+-------+| 1007 | EMMY | girl |   19 |  99.5 |     2 || 1008 | DAWN | boy  |   21 |  95.5 |     2 || 1005 | BOB  | boy  |   19 |  86.9 |     2 || 1002 | JACK | boy  |   20 |  85.5 |     1 || 1004 | ANDY | girl |   20 |  82.2 |     1 || 1001 | TOM  | boy  |   18 |    80 |     1 || 1006 | AMY  | girl |   18 |  70.5 |     1 || 1003 | LUCY | girl |   20 |  10.5 |     2 |+------+------+------+------+-------+-------+8 rows in set (0.00 sec)mysql> 
  

6.4.4 分组查询

•   select 字段名 from 表名 group by 字段名
  

• 分组后再进行条件过滤

•   select 字段名 from 表名 group by 字段名 having 条件
  

•   	mysql> select class from students group by class;+-------+| class |+-------+|     1 ||     2 |+-------+2 rows in set (0.00 sec)mysql> 
  

• 是不是感觉分组查询没啥用？别着急，分组是为了统计，所以一般是和统计函数一起使用的
• 统计函数
  • count:统计表中记录的条数
  • avg:统计计算字段的平均值
  • sum:统计字段的总和
  • max:查询字段的最大值
  • min:查询字段的最小值
• 比如我们统计1班和2班分别有多少人

•  mysql> select class,count(class) from students group by class;+-------+--------------+| class | count(class) |+-------+--------------+|     1 |            4 ||     2 |            4 |+-------+--------------+2 rows in set (0.00 sec)mysql> 
  

• 统计1班和2班的平均分

•   mysql> select class,avg(score) from students group by class;+-------+------------+| class | avg(score) |+-------+------------+|     1 |      79.55 ||     2 |       73.1 |+-------+------------+2 rows in set (0.00 sec)mysql> 
  

• 只统计1班平均分

•   mysql> select class,avg(score) from students group by class having class=1;+-------+------------+| class | avg(score) |+-------+------------+|     1 |      79.55 |+-------+------------+1 row in set (0.00 sec)mysql>