MySQL 常用操作命令

Author： AIHGF
发布时间：April 11, 2019
4596views
No comments
16091 words
Categories：操作系统

主要学习了以下两篇博文：

[2] - MySql常用查询命令操作大全 - 2017.11.15

MySQL 数据库登录：

mysql -h 主机IP地址 -u 用户名 -p 密码
#或
mysql --host=ip地址 --user=用户名 --password=密码

MySQL 常用命令：

select version(); 查看MySQL的版本号
select current_date(); 查看MySQL的当前日期
select version(),current_date(); 同时查看MySQL的版本号和当前日期
show databases; 显示当前存在的数据库
USE aitable; 选择使用数据库(USE和QUIT命令不需要分号结束)
#Database changed
select database(); 显示当前选择的数据库
show tables; 显示当前数据库中存在的表
select * from db; 显示表(db)的内容 
describe mytable; 显示表的结构

1. 数据库的基本操作

MySQL 不区分大小写，推荐采用大写，不过这里以小写例示.

#1.创建数据库
create databases 数据库名称 ;
#如：
create databases aiuai ;
CREATE DATABASE aiuai;

#2.查看全部数据库
show databases;

#3.查看某个已经创建好的数据库
show create databases 数据库名称;
#如：
show create databases aiuai;

#4.修改数据库编码格式
alter database 数据库名称 default character set 编码方式 collate 编码方式_bin;
#如：
#将数据库aiuai 的编码修改为 gbk
alter database aiuai default character set gbk collate gbk_bin;

#5.选定数据库
use 数据库名称;
#如：
use aiuai

#6.删除数据库
drop database 数据库名称;
#如：
drop database aiuai;

2. 数据表的基本操作

2.1. 创建数据表

create table 表名 
{ 
    字段名 1,数据类型[完整性约束条件], 
    字段名 2,数据类型[完整性约束条件], 
    ... 
    字段名 n,数据类型[完整性约束条件], 
}

如：

#创建aitable
CREATE TABLE 创建aitable
(
    name VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    birthaddr VARCHAR(20)
);

该表的含义是：建立一个员工生日表aitable，表的内容包含员工姓名、性别、出生日期、出生城市.

由于 name、birthaddr 的列值是变化的，因此选择 VARCHAR，其长度不一定是20. 可以选择从1到255的任何长度，如果以后需要改变它的字长，可以使用 ALTER TABLE语句.

性别 sex 只需一个字符就可以表示："m"或"f"，因此选用CHAR(1);

birth 列则使用 DATE 数据类型.

2.2. 查看数据表

show create table 表名;
#或
describe 表名;
#简写：
desc 表名; 
#或
show columns from 表名;

如：

show create table aitable;
desc aitable;

2.3. 修改数据表

[1] - 修改表名

alter table 旧表名 rename [to] 新表名;

如：

alter tabel aitable rename aiutable

[2] - 修改字段名

alter table 表名 change 旧字段名 新字段名 新数据类型;

如：将数据表aitable 中的 name 字段改为 username，数据类型保持不变.

alter table aitable change name username varchar(20);

[3] - 修改字段的数据类型

alter table 表名 modify 字段名 数据类型;

如：将数据表aitable中的 id 字段的数据类型由 int(11) 修改为 int(20).

alter table aitable modify id int(20);
alter table aitable modify id int(10) unsigned auto_increment primary

[4] - 修改字段默认值

alter table 表名 alter 字段名 default 默认值

如：

alter table aitable alter id default 0

[5] - 添加字段

alter table 表名 add 新字段名 数据类型 [约束条件] [FIRST|AFTER已存在的字段名]

如：将数据表 aitable 中添加一个没有约束条件的 int 类型的字段 age.

alter table aitable add age int(10);

[6] - 删除字段

alter table 表名 drop 字段名;

如：

alter table aitable drop column sex;

[7] - 修改字段的排列位置

alter table 表名 modify 字段名1 数据类型 first|after 字段名2

如：

1.将数据表 aitable 的 username 字段修改为表的第一个字段:

alter table aitable modify username varchar(20) first;

2.将数据表aitable的 id 字段插入到 aitable 字段后面

alter table aitable modify id int(20) after aitable;

[8] - 删除数据表

drop table 表名;

2.4. 添加数据

2.4.1. 为所有字段添加数据

[1] - 方式一：insert 语句中指定所有字段名

insert into 表名(字段名1,字段名2, ...) value(值1,值2, ...)

如：向 aitable 中添加一条记录，记录中 id 字段的值为 1，name 字段的值为 "aihgf"，grade 字段的值为 98.5.

首先创建 aitable 表：

create table aitable
(  
    id int(4), 
    name varchar(20) not null, 
    grade float
);

然后添加一条记录数据：

insert into aitable (id,name,grade) values(1,'aihgf',98.5);

[2] - 方式二：insert语句中不指定字段名

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

注：由于 insert 语句中没有指定字段名，故添加值的顺序，必须和字段在表中已经定义的顺序相同.

如：向已添加纪录的 aitable 表中添加一条新记录，记录中 id 字段的值为3，name 字段的值为 "aihgf2"，grade 字段的值为 61.5.

insert into aitable values(3,'aihgf2',61.5);

2.4.2. 为表中的指定字段添加数据

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

如：向 aitable 表中添加一条新记录，记录中 id字段的值为 4，name 字段的值为"aihgf3"，grade 字段不指定值.

insert into aitable(id,name) values(4,'aihgf3');

2.4.3. 为表中指定的字段或者全部字段添加数据

insert into 表名 set 字段名1 = 值1 [,字段名2 = 值2, ...];

如：向 aitable 表中添加一条新记录，该条记录中 id 字段的值为5，name字段的值为 "aihgf4"，grade 字段的值为 99.

insert into aitable set id = 5, name = 'aihgf4', grade = 99;

2.4.4. 为表中同时添加多条记录

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

如：

假设已经创建表 aitable：

create table student( 
    id int(4),  
    name varchar(20) not null, 
    grade float
);

向表 aitable 中添加三条新记录：

insert into aitable values (6,'aihgf6',99), (7,'aihgf7',100), (8,'aihgf8',40.5) ;

2.5. 更新数据

update 表名 set 字段名1 = 值1 [,字段名2 = 值2, ...] [where 条件表达式];

如：

update aitable set birth = "1999-09-01" where name = "aihgf";

2.5.1. 更新部分数据

如：

[1] - 更新 aitable 表中 id 字段值为1的记录，将记录中的 name 字段的值更改为 aihgf101，grade字段的值更改为50.

#更新数据前，首先使用查询语句查看 id 字段值为 1 的记录
select * from aitable where id = 1;

#更新数据
select * from aitable set name = 'aihgf101', grade = 50 where id = 1;

[2] - 更新 aitable 表中 id 字段值小于 4 的记录，将这些记录的 grade 字段值都更新为 100.

#更新数据前，首先使用查询语句查看 id 字段值小于 4 的记录
select * from aitable where id < 4;

#更新数据
update aitable set grade = 100 where id < 4;

2.5.2. 更新全部数据

如：

更新 aitable 表中全部记录，将 grade 字段值都更新为80.

update student set grade = 80;

2.6. 删除数据

2.6.1. delete 删除表中记录

delete from 表名 [where 条件表达式];

2.6.2. delete 删除部分数据

如：

在 aitable 表中，删除 id 字段值为 11 的记录.

select * from aitable where id = 11 ;

2.6.3. delete 删除全部数据

在 delete 语句中，若没用使用 where 子句，则会将表中的所有记录都删除.

如：

删除 aitable 表中所有记录.

delete from aitable;

2.6.4. truncate 语句删除

truncate [table] 表名;

如：

删除 aitable 中所有记录.

truncate table aitable;

2.6.5. delete 和 truncate 的异同

相同点：都能删除表中的所有数据的功能.

不同点：

[1] - delete语句是DML语句；

[2] - delete 语句后面可以跟where子句，通过指定where子句中的条件表达式只删除满足条件的记录；

[3] - delete语句删除表中所有记录后，再次向表中添加记录时，自动增加字段的值为删除时该字段的最大增加1；

[4] - 使用delete语句时，每删除一条记录都会在日志中记录.

[5] - truncate语句通常被认为是DDL语句；

[6] - truncate语句只能用于删除表中的所有记录；

[7] - truncate语句删除表中的数据后，再次向表中添加记录时，自动增加字段的默认初始值重新由1开始；

[8] - 使用truncate语句时，不会在日志中记录删除的内容，因此truncate语句的执行效率比delete语句高.

2.7. 简单查询数据

2.7.1. 简单查询

select [distinct] *| {字段名1,字段名2,字段名3, ...} from 表名 [where 条件表达式1 [group by 字段名 [having 条件表达式2]] [order by 字段名 [asc|desc]] [limit [offset] 记录数];

查询所有字段有两种方法

[1] - 方法一：在select语句中列出所有字段名来查询表中的数据

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

[2] - 方法二：使用星号(“*”)通配符来代替所有的字段名

select * from 表名;

查询指定字段

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

2.7.2. 带关系运算符的条件查询

在select语句中，最常见的是使用where子句指定查询条件对数据进行过滤.

select 字段名1,字段名2, ... from 表名 where 条件表达式;

常见的关系运算符如下表所示：

关系运算符	说明	关系运算符	说明
=	等于	<=	小于等于
< >	不等于	>	大于
!=	不等于	>=	大于等于
<	小于

如：

查询 aitable 表中 grade 大于80的学生姓名.

select name, grade from aitable where grade > 80;

2.7.3. 带 in 关键字的条件查询

in 关键字用于判断某个字段的值是否在指定集合中，若字段的值在集合中，则满足条件，该字段所在的记录将被查询出来

select * | 字段名1, 字段名2, ... from 表名 where 字段名 [not] in (元素1, 元素2, ...);

说明：元素1, 元素2, …表示集合中的元素，即指定的条件范围. not 是可选参数，使用 not 表示查询不在 in 关键字指定集合范围中的记录.

如：

查询 aitable 表中id值为1、2、3的记录.

select id, grade, name, gender from aitable where id in (1,2,3);

2.7.4. 带between and 关键字的条件查询

between and用于判断某个字段的值是否在指定的范围之内.

select *|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] between 值1 and 值2;

说明： 值1 表示范围条件的起始值，值2 表示范围条件的结束值. not 是可选参数，使用 not 表示查询指定范围之外的记录，通常情况下，值1 小于 值2，否则查询不到任何结果.

如：

查询 aitable 表中 id 值在2~5之间的学生姓名.

select id, name from aitable where id between 2 and 5;

2.7.5. 空值查询

在数据表中，某些列的值可能为空值（null），空值不同于0，也不同于空字符串.

在MySQL语句中，使用 is null 关键字来判断字段的值是否为空值.

select *| 字段名1, 字段名2, ... from 表名 where 字段名 is [not] null;

如：

查询 aitable 表中 gender 为空值的记录.

select id, name, grade, gender from aitable where gender is null;

2.7.6. 带distinct关键字的条件查询

很多表中某些字段的数据存在重复的值. 有时，需要过滤掉查询记录中重复的值，可以使用distinct关键字来实现这种功能.

select distinct 字段名 from 表名;

说明：字段名 表示要过滤重复记录的字段.

如：

查询 aitable 表中 gender 字段的值，查询记录不能重复.

select distinct gender from aitable;

distinct关键字还可以作用于多个字段：

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

说明：只有distinct关键字指定的多个字段值都相同，才会被认作是重复记录.

如：

查询 aitable 表中的 gender 和 name 字段，使用 distinct 关键字作用于这两个字段.

select distinct gender, name, from aitable;

2.7.7. 带like关键字的条件查询

like关键字可以判断连个字符串是否相匹配

select *|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] like '匹配字符串';

说明：匹配字符串 指定用来匹配的字符串，其值可以是一个普通字符串，也可以是包含百分号（%）和下划线（_）的通配字符串. 百分号和下划线统称为通配符.

[1] - 百分号（%）通配符

百分号通配符能匹配任意长度的字符串，包括空字符串.

例如，字符串 “c%” 匹配以字符 c 开始，任意长度的字符串，如 “ct”、“cut”、“current”等等.

百分号通配符可以出现在通配字符串的任意位置;

在通配字符串中可以出现多个百分号通配符.

如：查找 aitable 表中 name 字段值以字段 “s” 开头的学生 id.

select id, name, from aitable where name like "s%";

如：查询 aitable 表中 name 字段值包含字符 “y” 的学生 id.

select id, name from aitable where name like '%y%';

[2] - 下划线（_）通配符

下划线通配符与百分号通配符有些不同，下划线通配符只匹配单个字符.

若要匹配多个字符，需要使用多个下划线通配符.

若使用多个下划线匹配多个连续的字符，下划线之间不能有空格.

如：查询 aitable 表中 name 字段值以字符串 “ai” 开始，以字符串 “uai” 结束，并且两个字符串之间只有一个字符的记录.

select * from aitable where name like "ai_uai";

如：查询 aitable 表中 name 字段值包含 7 个字符，并且以字符串 “ing” 结束的记录.

select * from aitable where name like '____ing';

[3] - 百分号和下划线通配符进行的查询操作

若要匹配字符串中的百分号和下划线，则使用右斜线（“”）对百分号和下划线进行转义.

如：查询 aitable 表中 name 字段值包括 “%” 的记录.

假设 aitable 表中以存在如下记录：

insert into aitable(name, grade, gender) values('sun%er', 95, '男');

2.7.8. 带and关键字的多条件查询

select *|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 and 条件表达式2 [... and 条件表达式n];

如：查询 aitable 表中 id 字段值小于5，并且 gender 字段值为“女”的学生姓名.

select id, name, gender from aitable where id < 5 and gender = '女';

2.7.9. 带or关键字的多条件查询

在使用or关键字时，只要记录满足任意一个条件就会被查询出来

select *|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 or 条件表达式2 [... or 条件表达式n];

如：查询 aitable 表中 id 字段值小于3或者 gender 字段值为“女”的学生姓名.

select id, name,gender from aitable where id < 3 or gender = '女';

2.7.10. or和and关键字同时使用的条件查询

and 的优先级高于 or.

如：查询 aitable 表中 gender 字段值为“女”或者gender字段值为“男”，并且 grade 字段值为100的学生姓名.

select name, grade, gender from aitable where gender = '女' or gender = '男' and grade = 100;

2.8. 高级查询数据

2.8.1. 聚合函数

实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值、最小值、平均值等.

如下表中的函数对一组值进行统计，并返回唯一值，这些函数被称为聚合函数.

函数名称	作用	函数名称	作用
count()	返回某列的行数	max()	返回某列的最大值
sum()	返回某列值的和	min()	返回某列的最小值
avg()	返回某列的平均值

[1] - count()函数用来统计记录的条数

select count (*) from 表名;

如：查询 aitable 表中一共有多少条记录

select count(*) from aitable;

[2] - avg()函数用于求出某个字段所有值的平均值

select avg(字段名) from 表名;

如：求出 aitable 表中 grade字段的平均值

select avg(grade) from aitable;

[3] - max()函数是求最大值的函数，用于求出某个字段的最大值

select max(字段名) from 表名;

如：求出 aitable 表中 grade字段的最大值

select max(grade) from aitable;

[4] - min()函数是求最小值的函数，用于求出某个字段的最小值

select min(字段名) from 表名;

如：求出 aitable 表中 grade字段的最小值

select min(grade) from aitable;

2.8.2. 对查询结果排序

使用 order by 对查询结果进行排序

select 字段名1, 字段名2, ... from 表名 order by 字段名1[asc|desc], 字段名2 [asc|desc] ...;

说明：参数 asc 表示按照升序进行排序，desc 表示按照降序进行排序. 默认情况下，按照 asc 方式进行排序.

注意：在按照指定字段进行排序时，若某条记录的字段值为null，则这条记录会在第一条显示，因为 null 值可以被认为是最小值.

如：查出 aitable 表中的所有记录，并使用参数 asc 按照 grade 字段升序方式进行排列

select * from aitable order by grade asc;

2.8.3. 分组查询

使用 group by 按某个字段或者多个字段中的值进行分组，字段中值相同的为一组

select 字段名1, 字段名2, ... from 表名 group by 字段名1, 字段名2, ... [having 条件表达式];

说明：having 关键字指定条件表达式对分组后的内容进行过滤，需要注意的是，group by 一般和聚合函数一起使用.

由于分组查询比较复杂，下面分几种情况对分组查询进行详解:

[1] - 单独使用 group by 分组

单独使用 group by 关键字，查询的是每个分组中的一条记录.

如：查询aitable表中的记录，按照 gender 字段值进行分组.

select * from aitable group by gender;

[2] - group by 和聚合函数一起使用

group by 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等.

如：将 aitable 表按照 gender 字段值进行分组查询，计算出每个分组中各有多少名学生.

select count(*), gender from aitable group by gender;

结果说明：group by 对 aitable 表按照 gender 字段中的不同值进行了分组，并通过 count() 函数统计出每个组个数.

[3] - group by 和 having关键字一起使用

如：将 aitable 表按照 gender 字段进行分组查询，查询出 grade 字段值之和小于300的分组.

select sum(grade), gender from aitable group by gender having sum(grade) < 300;

[4] - having 关键字和where关键字的异同点

相同点：都用于设置条件表达式对查询结果进行过滤

不同点：

[a] - having关键字后可以跟聚合函数，通常情况下，having关键字都和group by 一起使用，用于对分组后的结果进行过滤.

[b] - where关键字不能后跟聚合函数.

2.8.4. 使用 limit 限制查询结果的数量

limit关键字可以指定查询结果从哪一条记录开始以及一共查询多少条信息

select 字段名1, 字段名2, ... from 表名 limit [offset,]记录数;

说明：limit 后面可以跟两个参数，第一个参数“offset”表示偏移量，若偏移量为0，则从查询结果的第一条记录开始；偏移量为1，则从查询结果中的第二条记录开始，以此类推. offset为可选值，若不指定，其默认值为0. 第二个参数“记录数”表示返回查询记录的条数.

如：查询 aitable 表中的前4条记录

select * from aitable limit 4;

如：查询 aitable 表中 grade 字段值从第5位到第8位的学生（从高到底）

select * from aitable order by grade desc limit 4, 4;

2.9. 为表和字段取别名

2.9.1. 为表取别名

select * from 表名 [as] 别名;

如：为 aitable 表起一个别名 a, 并查询 aitable 表中 gender 字段值为“女”的记录.

select * from aitable as a where a.gender = '女';

2.9.2. 为字段取别名

select 字段名 [as] 别名 [, 字段名 [as] 别名, ...] from 表名;

如：查询 aitable 表中的所有记录的 name 和 gender 字段值，并为这两个字段起别名 ai_name 和 ai_gender.

select name as ai_name, gender ai_gender from aitable;

3. 数据备份与恢复

3.1. 导出表

如：

mysqldump --opt aiuai > aiuai.sql;

说明：将数据库 aiuai 中的表全部备份到 aiuai.sql文件，aiuai.sql是一个文本文件.

如：

mysqldump --opt aiuai aitable1 aitable2 > aiuai.aitable1.aitable2.sql;

说明：将数据库 aiuai 中的 aitable1 表和 aitable2 表备份到 aiuai.aitable1.aitable2.sql 文件，aiuai.aitable1.aitable2.sql 是一个文本文件.

3.2. 导入表

如：

create database aiuai;
use aiuai;
source aiuai.sql;
#或
source aiuai.aitable1.aitable2.sql;

3.3. 导出数据库

如：

mysqldump --databases aiuai1 aiuai2 > aiuai1.aiuai2.sql

说明：将数据库 aiuai1 和 aiuai2 备份到 aiuai1.aiuai2.sql文件，aiuai1.aiuai2.sql 是一个文本文件.

如：

mysqldump -h host -u user -p pass --databases dbname > file.dump;

把 host 上的以名字 user，口令 pass 的数据库 dbname 导入到文件 file.dump 中.

3.4. 导入数据库

mysql -u 用户名 -p 密码 数据库名 < 数据库名.sql;

Last modification：April 11th, 2019 at 11:01 pm

MySQL 常用操作命令

AIHGF • 2019 年 04 月 11 日

主要学习了以下两篇博文：

[1] - MySQL的常用操作命令详解

[2] - MySql常用查询命令操作大全 - 2017.11.15

MySQL 数据库登录：

mysql -h 主机IP地址 -u 用户名 -p 密码
#或
mysql --host=ip地址 --user=用户名 --password=密码

MySQL 常用命令：

select version(); 查看MySQL的版本号
select current_date(); 查看MySQL的当前日期
select version(),current_date(); 同时查看MySQL的版本号和当前日期
show databases; 显示当前存在的数据库
USE aitable; 选择使用数据库(USE和QUIT命令不需要分号结束)
#Database changed
select database(); 显示当前选择的数据库
show tables; 显示当前数据库中存在的表
select * from db; 显示表(db)的内容 
describe mytable; 显示表的结构

1. 数据库的基本操作

MySQL 不区分大小写，推荐采用大写，不过这里以小写例示.

#1.创建数据库
create databases 数据库名称 ;
#如：
create databases aiuai ;
CREATE DATABASE aiuai;

#2.查看全部数据库
show databases;

#3.查看某个已经创建好的数据库
show create databases 数据库名称;
#如：
show create databases aiuai;

#4.修改数据库编码格式
alter database 数据库名称 default character set 编码方式 collate 编码方式_bin;
#如：
#将数据库aiuai 的编码修改为 gbk
alter database aiuai default character set gbk collate gbk_bin;

#5.选定数据库
use 数据库名称;
#如：
use aiuai

#6.删除数据库
drop database 数据库名称;
#如：
drop database aiuai;

2. 数据表的基本操作

2.1. 创建数据表

create table 表名 
{ 
    字段名 1,数据类型[完整性约束条件], 
    字段名 2,数据类型[完整性约束条件], 
    ... 
    字段名 n,数据类型[完整性约束条件], 
}

如：

#创建aitable
CREATE TABLE 创建aitable
(
    name VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    birthaddr VARCHAR(20)
);

该表的含义是：建立一个员工生日表aitable，表的内容包含员工姓名、性别、出生日期、出生城市.

性别 sex 只需一个字符就可以表示："m"或"f"，因此选用CHAR(1);

birth 列则使用 DATE 数据类型.

2.2. 查看数据表

show create table 表名;
#或
describe 表名;
#简写：
desc 表名; 
#或
show columns from 表名;

如：

show create table aitable;
desc aitable;

2.3. 修改数据表

[1] - 修改表名

alter table 旧表名 rename [to] 新表名;

如：

alter tabel aitable rename aiutable

[2] - 修改字段名

alter table 表名 change 旧字段名 新字段名 新数据类型;

如：将数据表aitable 中的 name 字段改为 username，数据类型保持不变.

alter table aitable change name username varchar(20);

[3] - 修改字段的数据类型

alter table 表名 modify 字段名 数据类型;

如：将数据表aitable中的 id 字段的数据类型由 int(11) 修改为 int(20).

alter table aitable modify id int(20);
alter table aitable modify id int(10) unsigned auto_increment primary

[4] - 修改字段默认值

alter table 表名 alter 字段名 default 默认值

如：

alter table aitable alter id default 0

[5] - 添加字段

alter table 表名 add 新字段名 数据类型 [约束条件] [FIRST|AFTER已存在的字段名]

如：将数据表 aitable 中添加一个没有约束条件的 int 类型的字段 age.

alter table aitable add age int(10);

[6] - 删除字段

alter table 表名 drop 字段名;

如：

alter table aitable drop column sex;

[7] - 修改字段的排列位置

alter table 表名 modify 字段名1 数据类型 first|after 字段名2

如：

1.将数据表 aitable 的 username 字段修改为表的第一个字段:

alter table aitable modify username varchar(20) first;

2.将数据表aitable的 id 字段插入到 aitable 字段后面

alter table aitable modify id int(20) after aitable;

[8] - 删除数据表

drop table 表名;

2.4. 添加数据

2.4.1. 为所有字段添加数据

[1] - 方式一：insert 语句中指定所有字段名

insert into 表名(字段名1,字段名2, ...) value(值1,值2, ...)

如：向 aitable 中添加一条记录，记录中 id 字段的值为 1，name 字段的值为 "aihgf"，grade 字段的值为 98.5.

首先创建 aitable 表：

create table aitable
(  
    id int(4), 
    name varchar(20) not null, 
    grade float
);

然后添加一条记录数据：

insert into aitable (id,name,grade) values(1,'aihgf',98.5);

[2] - 方式二：insert语句中不指定字段名

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

注：由于 insert 语句中没有指定字段名，故添加值的顺序，必须和字段在表中已经定义的顺序相同.

如：向已添加纪录的 aitable 表中添加一条新记录，记录中 id 字段的值为3，name 字段的值为 "aihgf2"，grade 字段的值为 61.5.

insert into aitable values(3,'aihgf2',61.5);

2.4.2. 为表中的指定字段添加数据

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

如：向 aitable 表中添加一条新记录，记录中 id字段的值为 4，name 字段的值为"aihgf3"，grade 字段不指定值.

insert into aitable(id,name) values(4,'aihgf3');

2.4.3. 为表中指定的字段或者全部字段添加数据

insert into 表名 set 字段名1 = 值1 [,字段名2 = 值2, ...];

如：向 aitable 表中添加一条新记录，该条记录中 id 字段的值为5，name字段的值为 "aihgf4"，grade 字段的值为 99.

insert into aitable set id = 5, name = 'aihgf4', grade = 99;

2.4.4. 为表中同时添加多条记录

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

如：

假设已经创建表 aitable：

create table student( 
    id int(4),  
    name varchar(20) not null, 
    grade float
);

向表 aitable 中添加三条新记录：

insert into aitable values (6,'aihgf6',99), (7,'aihgf7',100), (8,'aihgf8',40.5) ;

2.5. 更新数据

update 表名 set 字段名1 = 值1 [,字段名2 = 值2, ...] [where 条件表达式];

如：

update aitable set birth = "1999-09-01" where name = "aihgf";

2.5.1. 更新部分数据

如：

[1] - 更新 aitable 表中 id 字段值为1的记录，将记录中的 name 字段的值更改为 aihgf101，grade字段的值更改为50.

#更新数据前，首先使用查询语句查看 id 字段值为 1 的记录
select * from aitable where id = 1;

#更新数据
select * from aitable set name = 'aihgf101', grade = 50 where id = 1;

[2] - 更新 aitable 表中 id 字段值小于 4 的记录，将这些记录的 grade 字段值都更新为 100.

#更新数据前，首先使用查询语句查看 id 字段值小于 4 的记录
select * from aitable where id < 4;

#更新数据
update aitable set grade = 100 where id < 4;

2.5.2. 更新全部数据

如：

更新 aitable 表中全部记录，将 grade 字段值都更新为80.

update student set grade = 80;

2.6. 删除数据

2.6.1. delete 删除表中记录

delete from 表名 [where 条件表达式];

2.6.2. delete 删除部分数据

如：

在 aitable 表中，删除 id 字段值为 11 的记录.

select * from aitable where id = 11 ;

2.6.3. delete 删除全部数据

在 delete 语句中，若没用使用 where 子句，则会将表中的所有记录都删除.

如：

删除 aitable 表中所有记录.

delete from aitable;

2.6.4. truncate 语句删除

truncate [table] 表名;

如：

删除 aitable 中所有记录.

truncate table aitable;

2.6.5. delete 和 truncate 的异同

相同点：都能删除表中的所有数据的功能.

不同点：

[1] - delete语句是DML语句；

[2] - delete 语句后面可以跟where子句，通过指定where子句中的条件表达式只删除满足条件的记录；

[3] - delete语句删除表中所有记录后，再次向表中添加记录时，自动增加字段的值为删除时该字段的最大增加1；

[4] - 使用delete语句时，每删除一条记录都会在日志中记录.

[5] - truncate语句通常被认为是DDL语句；

[6] - truncate语句只能用于删除表中的所有记录；

[7] - truncate语句删除表中的数据后，再次向表中添加记录时，自动增加字段的默认初始值重新由1开始；

[8] - 使用truncate语句时，不会在日志中记录删除的内容，因此truncate语句的执行效率比delete语句高.

2.7. 简单查询数据

2.7.1. 简单查询

select [distinct] *| {字段名1,字段名2,字段名3, ...} from 表名 [where 条件表达式1 [group by 字段名 [having 条件表达式2]] [order by 字段名 [asc|desc]] [limit [offset] 记录数];

查询所有字段有两种方法

[1] - 方法一：在select语句中列出所有字段名来查询表中的数据

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

[2] - 方法二：使用星号(“*”)通配符来代替所有的字段名

select * from 表名;

查询指定字段

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

2.7.2. 带关系运算符的条件查询

在select语句中，最常见的是使用where子句指定查询条件对数据进行过滤.

select 字段名1,字段名2, ... from 表名 where 条件表达式;

常见的关系运算符如下表所示：

关系运算符	说明	关系运算符	说明
=	等于	<=	小于等于
< >	不等于	>	大于
!=	不等于	>=	大于等于
<	小于

如：

查询 aitable 表中 grade 大于80的学生姓名.

select name, grade from aitable where grade > 80;

2.7.3. 带 in 关键字的条件查询

in 关键字用于判断某个字段的值是否在指定集合中，若字段的值在集合中，则满足条件，该字段所在的记录将被查询出来

select * | 字段名1, 字段名2, ... from 表名 where 字段名 [not] in (元素1, 元素2, ...);

说明：元素1, 元素2, …表示集合中的元素，即指定的条件范围. not 是可选参数，使用 not 表示查询不在 in 关键字指定集合范围中的记录.

如：

查询 aitable 表中id值为1、2、3的记录.

select id, grade, name, gender from aitable where id in (1,2,3);

2.7.4. 带between and 关键字的条件查询

between and用于判断某个字段的值是否在指定的范围之内.

select *|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] between 值1 and 值2;

如：

查询 aitable 表中 id 值在2~5之间的学生姓名.

select id, name from aitable where id between 2 and 5;

2.7.5. 空值查询

在数据表中，某些列的值可能为空值（null），空值不同于0，也不同于空字符串.

在MySQL语句中，使用 is null 关键字来判断字段的值是否为空值.

select *| 字段名1, 字段名2, ... from 表名 where 字段名 is [not] null;

如：

查询 aitable 表中 gender 为空值的记录.

select id, name, grade, gender from aitable where gender is null;

2.7.6. 带distinct关键字的条件查询

很多表中某些字段的数据存在重复的值. 有时，需要过滤掉查询记录中重复的值，可以使用distinct关键字来实现这种功能.

select distinct 字段名 from 表名;

说明：字段名 表示要过滤重复记录的字段.

如：

查询 aitable 表中 gender 字段的值，查询记录不能重复.

select distinct gender from aitable;

distinct关键字还可以作用于多个字段：

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

说明：只有distinct关键字指定的多个字段值都相同，才会被认作是重复记录.

如：

查询 aitable 表中的 gender 和 name 字段，使用 distinct 关键字作用于这两个字段.

select distinct gender, name, from aitable;

2.7.7. 带like关键字的条件查询

like关键字可以判断连个字符串是否相匹配

select *|{字段名1, 字段名2, ...} from 表名 where 字段名 [not] like '匹配字符串';

[1] - 百分号（%）通配符

百分号通配符能匹配任意长度的字符串，包括空字符串.

例如，字符串 “c%” 匹配以字符 c 开始，任意长度的字符串，如 “ct”、“cut”、“current”等等.

百分号通配符可以出现在通配字符串的任意位置;

在通配字符串中可以出现多个百分号通配符.

如：查找 aitable 表中 name 字段值以字段 “s” 开头的学生 id.

select id, name, from aitable where name like "s%";

如：查询 aitable 表中 name 字段值包含字符 “y” 的学生 id.

select id, name from aitable where name like '%y%';

[2] - 下划线（_）通配符

下划线通配符与百分号通配符有些不同，下划线通配符只匹配单个字符.

若要匹配多个字符，需要使用多个下划线通配符.

若使用多个下划线匹配多个连续的字符，下划线之间不能有空格.

如：查询 aitable 表中 name 字段值以字符串 “ai” 开始，以字符串 “uai” 结束，并且两个字符串之间只有一个字符的记录.

select * from aitable where name like "ai_uai";

如：查询 aitable 表中 name 字段值包含 7 个字符，并且以字符串 “ing” 结束的记录.

select * from aitable where name like '____ing';

[3] - 百分号和下划线通配符进行的查询操作

若要匹配字符串中的百分号和下划线，则使用右斜线（“”）对百分号和下划线进行转义.

如：查询 aitable 表中 name 字段值包括 “%” 的记录.

假设 aitable 表中以存在如下记录：

insert into aitable(name, grade, gender) values('sun%er', 95, '男');

2.7.8. 带and关键字的多条件查询

select *|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 and 条件表达式2 [... and 条件表达式n];

如：查询 aitable 表中 id 字段值小于5，并且 gender 字段值为“女”的学生姓名.

select id, name, gender from aitable where id < 5 and gender = '女';

2.7.9. 带or关键字的多条件查询

在使用or关键字时，只要记录满足任意一个条件就会被查询出来

select *|{字段名1, 字段名2, ...} from 表名 where 条件表达式1 or 条件表达式2 [... or 条件表达式n];

如：查询 aitable 表中 id 字段值小于3或者 gender 字段值为“女”的学生姓名.

select id, name,gender from aitable where id < 3 or gender = '女';

2.7.10. or和and关键字同时使用的条件查询

and 的优先级高于 or.

如：查询 aitable 表中 gender 字段值为“女”或者gender字段值为“男”，并且 grade 字段值为100的学生姓名.

select name, grade, gender from aitable where gender = '女' or gender = '男' and grade = 100;

2.8. 高级查询数据

2.8.1. 聚合函数

实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值、最小值、平均值等.

如下表中的函数对一组值进行统计，并返回唯一值，这些函数被称为聚合函数.

函数名称	作用	函数名称	作用
count()	返回某列的行数	max()	返回某列的最大值
sum()	返回某列值的和	min()	返回某列的最小值
avg()	返回某列的平均值

[1] - count()函数用来统计记录的条数

select count (*) from 表名;

如：查询 aitable 表中一共有多少条记录

select count(*) from aitable;

[2] - avg()函数用于求出某个字段所有值的平均值

select avg(字段名) from 表名;

如：求出 aitable 表中 grade字段的平均值

select avg(grade) from aitable;

[3] - max()函数是求最大值的函数，用于求出某个字段的最大值

select max(字段名) from 表名;

如：求出 aitable 表中 grade字段的最大值

select max(grade) from aitable;

[4] - min()函数是求最小值的函数，用于求出某个字段的最小值

select min(字段名) from 表名;

如：求出 aitable 表中 grade字段的最小值

select min(grade) from aitable;

2.8.2. 对查询结果排序

使用 order by 对查询结果进行排序

select 字段名1, 字段名2, ... from 表名 order by 字段名1[asc|desc], 字段名2 [asc|desc] ...;

说明：参数 asc 表示按照升序进行排序，desc 表示按照降序进行排序. 默认情况下，按照 asc 方式进行排序.

注意：在按照指定字段进行排序时，若某条记录的字段值为null，则这条记录会在第一条显示，因为 null 值可以被认为是最小值.

如：查出 aitable 表中的所有记录，并使用参数 asc 按照 grade 字段升序方式进行排列

select * from aitable order by grade asc;

2.8.3. 分组查询

使用 group by 按某个字段或者多个字段中的值进行分组，字段中值相同的为一组

select 字段名1, 字段名2, ... from 表名 group by 字段名1, 字段名2, ... [having 条件表达式];

说明：having 关键字指定条件表达式对分组后的内容进行过滤，需要注意的是，group by 一般和聚合函数一起使用.

由于分组查询比较复杂，下面分几种情况对分组查询进行详解:

[1] - 单独使用 group by 分组

单独使用 group by 关键字，查询的是每个分组中的一条记录.

如：查询aitable表中的记录，按照 gender 字段值进行分组.

select * from aitable group by gender;

[2] - group by 和聚合函数一起使用

group by 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等.

如：将 aitable 表按照 gender 字段值进行分组查询，计算出每个分组中各有多少名学生.

select count(*), gender from aitable group by gender;

结果说明：group by 对 aitable 表按照 gender 字段中的不同值进行了分组，并通过 count() 函数统计出每个组个数.

[3] - group by 和 having关键字一起使用

如：将 aitable 表按照 gender 字段进行分组查询，查询出 grade 字段值之和小于300的分组.

select sum(grade), gender from aitable group by gender having sum(grade) < 300;

[4] - having 关键字和where关键字的异同点

相同点：都用于设置条件表达式对查询结果进行过滤

不同点：

[a] - having关键字后可以跟聚合函数，通常情况下，having关键字都和group by 一起使用，用于对分组后的结果进行过滤.

[b] - where关键字不能后跟聚合函数.

2.8.4. 使用 limit 限制查询结果的数量

limit关键字可以指定查询结果从哪一条记录开始以及一共查询多少条信息

select 字段名1, 字段名2, ... from 表名 limit [offset,]记录数;

如：查询 aitable 表中的前4条记录

select * from aitable limit 4;

如：查询 aitable 表中 grade 字段值从第5位到第8位的学生（从高到底）

select * from aitable order by grade desc limit 4, 4;

2.9. 为表和字段取别名

2.9.1. 为表取别名

select * from 表名 [as] 别名;

如：为 aitable 表起一个别名 a, 并查询 aitable 表中 gender 字段值为“女”的记录.

select * from aitable as a where a.gender = '女';

2.9.2. 为字段取别名

select 字段名 [as] 别名 [, 字段名 [as] 别名, ...] from 表名;

如：查询 aitable 表中的所有记录的 name 和 gender 字段值，并为这两个字段起别名 ai_name 和 ai_gender.

select name as ai_name, gender ai_gender from aitable;

3. 数据备份与恢复

3.1. 导出表

如：

mysqldump --opt aiuai > aiuai.sql;

说明：将数据库 aiuai 中的表全部备份到 aiuai.sql文件，aiuai.sql是一个文本文件.

如：

mysqldump --opt aiuai aitable1 aitable2 > aiuai.aitable1.aitable2.sql;

说明：将数据库 aiuai 中的 aitable1 表和 aitable2 表备份到 aiuai.aitable1.aitable2.sql 文件，aiuai.aitable1.aitable2.sql 是一个文本文件.

3.2. 导入表

如：

create database aiuai;
use aiuai;
source aiuai.sql;
#或
source aiuai.aitable1.aitable2.sql;

3.3. 导出数据库

如：

mysqldump --databases aiuai1 aiuai2 > aiuai1.aiuai2.sql

说明：将数据库 aiuai1 和 aiuai2 备份到 aiuai1.aiuai2.sql文件，aiuai1.aiuai2.sql 是一个文本文件.

如：

mysqldump -h host -u user -p pass --databases dbname > file.dump;

把 host 上的以名字 user，口令 pass 的数据库 dbname 导入到文件 file.dump 中.

3.4. 导入数据库

mysql -u 用户名 -p 密码 数据库名 < 数据库名.sql;

1. 数据库的基本操作

2. 数据表的基本操作

2.1. 创建数据表

2.2. 查看数据表

2.3. 修改数据表

2.4. 添加数据

2.4.1. 为所有字段添加数据

2.4.2. 为表中的指定字段添加数据

2.4.3. 为表中指定的字段或者全部字段添加数据

2.4.4. 为表中同时添加多条记录

2.5. 更新数据

2.5.1. 更新部分数据

2.5.2. 更新全部数据

2.6. 删除数据

2.6.1. delete 删除表中记录

2.6.2. delete 删除部分数据

2.6.3. delete 删除全部数据

2.6.4. truncate 语句删除

2.6.5. delete 和 truncate 的异同

2.7. 简单查询数据

2.7.1. 简单查询

2.7.2. 带关系运算符的条件查询

2.7.3. 带 in 关键字的条件查询

2.7.4. 带between and 关键字的条件查询

2.7.5. 空值查询

2.7.6. 带distinct关键字的条件查询

2.7.7. 带like关键字的条件查询

2.7.8. 带and关键字的多条件查询

2.7.9. 带or关键字的多条件查询

2.7.10. or和and关键字同时使用的条件查询

2.8. 高级查询数据

2.8.1. 聚合函数

2.8.2. 对查询结果排序

2.8.3. 分组查询

2.8.4. 使用 limit 限制查询结果的数量

2.9. 为表和字段取别名

2.9.1. 为表取别名

2.9.2. 为字段取别名

3. 数据备份与恢复

3.1. 导出表

3.2. 导入表

3.3. 导出数据库

3.4. 导入数据库

※相关文章推荐※

※最新文章推荐※