MySQL：基础操纵(增删查改)

目次
一、库的操纵
创建数据库
检察数据库
显示创建语句
修改数据库
删除数据库
备份和恢复
二、表的操纵
创建表
检察表布局
修改表
删除表
三、表的增删查改
新增数据
插入否则更新
插入查询的效果
查找数据
为查询效果指定别名
效果去重
where 条件
效果排序
筛选分页效果
聚合函数查询
分组查询
HAVING 条件
修改数据
替换数据
修改数据
删除数据
截断表

---

SQL语句中不区分大小写，下面都以小写为主
下面的SQL语句第一行是最简便的，第二行是具体的，包含可选的内容，[ ] 中代表的是可选项
一、库的操纵

创建数据库

创建一个新的数据库

1. create database 库名;
2. create database [if not exists] 库名 [charset=字符集] [collate=校验规则];

复制代码

charset：用于指定数据库所采用的编码格式，字符集
collate：用于指定数据库所采用的校验规则
假如创建数据库时未指明数据库的编码格式或校验规则，则默认利用MySQL设置文件中对应的编码格式和校验规则
默认的编码格式是utf8，默认的校验规则是utf8_general_ci
不指明数据库的编码格式和校验规则：

 通过charset和collate分别指明数据库的编码格式和校验规则：

---

检察数据库

可以检察系统中全部的数据库

1. show databases;

复制代码

---

显示创建语句

可以检察对应数据库的创建语句 

1. show create database 数据库名

复制代码

注意：
①数据库的名字加上反引号，是为了防止利用的数据库名与关键字冲突
②/*!40100 DEFAULT CHARACTER SET utf8 */不是解释，它表示当前MySQL版本假如大于4.10，则执行背面的SQL语句

---

修改数据库

可以修改数据库的字符集或校验规则

1. alter database 库名 [charset=字符集] [collate=校验规则];

复制代码

需要注意的是MySQL数据库不支持修改库名
db1的字符集、校验规则原本是默认的，如今将数据库的字符集改为gbk，将数据库的校验规则改为gbk_bin：

---

删除数据库

删除一个现有的数据库

1. drop database 库名;
2. drop database [if exists] 库名;

复制代码

删除数据库后该数据库对应的文件夹就被删除了
并且删除数据库后，该数据库下的全部表也都会被级联删除，因此不要随意删除数据库

---

备份和恢复

数据库的备份 

1. mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ...  > 数据库备份存储的文件路径

复制代码

先创建一个数据库
接着在该数据库中创建两个表student、teacher，并插入一些数据

这时在命令行中执行如下命令即可将该数据库举行备份，并指定将备份后产生的文件存放在当前目次下：

back.sql文件即可看到，文件中的内容现实就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句
数据库恢复
利用如下命令即可对指定数据库举行恢复：

1. source 数据库备份的文件路径

复制代码

删除数据库后，执行的代码：

此时就会恢复刚刚删除的数据库，且内容也与删除前完全一样

---

表的备份

1. mysqldump -P 端口号 -u 用户名 -p 密码 数据库名 表名1 表名2 ... > 表备份存储的文件路径

复制代码

表的恢复

1. source 表备份的文件路径

复制代码

表的备份与恢复类比上面的数据库的备份与恢复，利用方式是雷同的

---

二、表的操纵

DDL(数据界说语言)：好比建表、删表、该表、新增列、删除列等
DML(数据操纵语言)：好比插入记录、删除记录、修改记录等
下面都是表的DDL操纵
创建表

1. create table 表名(
2.         列名1 类型1 [comment '注释信息'],
3.         列名2 类型2 [comment '注释信息'],
4.         列名3 类型3 [comment '注释信息']
5. );[charset=字符集] [collate=校验规则] [engine=存储引擎];

复制代码

comment表示对指定列添加解释信息
建表时没有指定利用哪种存储引擎，那么就会默认利用InnoDB存储引擎 

---

检察表布局

desc 表名可以检察表的布局

①Field：表示该字段的名字
②Type：表示该字段的类型
③Null：表示该字段是否允许为空
④Key：表示索引类型，好比主键索引为PRI
⑤Default：表示该字段的默认值
⑥Extra：表示该字段的额外信息阐明
show create table [表名]\G，可以检察创建表时的干系细节

---

修改表

1. 新增列
2. alter table 表名 add 新增列名 新增列的属性;
4. 修改列的属性
5. alter table 表名 modify 列名 修改后的列属性;
7. 删除列
8. alter table 表名 drop 列名;
10. 修改表名
11. alter table 表名 rename 新表名;
13. 修改列名和属性
14. alter table 表名 change 列名 新列名 新列属性;

复制代码

新增列假如想新增到哪一列之后，可以在背面加上 after 列名：
 alter table student add name varchar(30) comment '姓名' after sex;
上述语句表示新增一列 name 列，并将 name 列放在 sex 列的背面
新增列后大概还需要对原来插入的记录举行修改，因为假设原有2条数据，此时新增一列，那么原有的2条数据中新增的那一列数据就默认为空
想将插入的列插入到第一列，就在SQL语句的最后加上first即可

---

删除表

drop删除表布局会直接删除整个表，内存中就没有这个表了
delete删除只删除数据，不删除布局，delete完表还在，只是为空了

1. 删除表结构
2. drop table [if exists] 表名;
4. 删除表数据
5. delete from 表名;
7. 删除部分数据
8. delete from 表名 where ...;

复制代码

---

三、表的增删查改

表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）
CRUD的操纵对象是对表当中的数据，是典型的DML（Data Manipulation Language）数据操纵语言
新增数据

1. insert into 表名 [列名] values [数据];

复制代码

新增数据时，假如在values前不加列名，就表示按照表中默认的列次序举行全列插入
假设表的布局如下：

单行数据 + 全列插入
不加列名，全列插入

多行数据 + 指定列插入
用insert语句也可以一次向表中插入多条记录，插入的多条记录之间利用逗号隔开，并且插入记录时可以只指定某些列举行插入

---

插入否则更新

向表中插入记录时，假如待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败
假如表中没有冲突数据，则直接插入数据
假如表中有冲突数据，则将表中的数据举行更新

1. insert into 表名 [列名] values [数据]
2. on duplicate key update [column1=value1, ...];

复制代码

column1=value1表示有冲突时，需要更新的列值
下图的含义就是：假如出现主键或唯一键冲突，则将表中冲突记录的学号和姓名举行更新

执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：
0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值雷同
1 row affected：表中没有冲突数据，数据直接被插入
2 rows affected：表中有冲突数据，并且数据已经被更新

---

插入查询的效果

表示需要插入 select 出来的效果 

1. insert [into] 表名 [列名] select ... [where...] [order by...] [limit...];

复制代码

删除表中重复的记录，重复的数据只能有一份
假设当前表数据如下：

要求删除测试表中重复的数据，思路如下：
①创建一张暂时表，表的布局和数据与原表雷同
②去重的方式查询并插入
③将原表改名，再将该表重定名为原表的名称，从而完成去重操纵
步骤如下：
①创建暂时表 no_duplicate 的时候可以借助like举行创建

②通过插入查询语句将去重查询后的效果插入到暂时表中，暂时表乐成去重

③将原表改名，再将暂时表改名为原表名称，完成去重操纵

---

查找数据

1. select {*/列名/表达式} from 表名 [where] [order by] [limit];

复制代码

此中select背面是*就表示全列查询
假如是列名就对指定的列举行查询，多个列用逗号隔开
假如是表达式，表达式中可以包含多个表中已有的字段，比方列有数学、英语效果，表达式就可以数学+英语效果，从而盘算出更多有意义的数据
背面的where、order by、limit都是可选项
{ }中的 / 代表可以选择此中某一条语句
全列查询
在查询数据时直接用*，表示举行全列查询，这时将会显示被筛选出来的记录的全部列信息：

指定列查询
查询数据时也可以只对指定的列举行查询，这时将需要查询的列在列表列出即可：

表示查找指定的 name 和 math 列
查询字段为表达式
我们也可以将表达式罗列到列表中：

此时就会多一个列，列名为1+1，每行数据都是1+1的值
这种方式可以帮助我们解决很多变乱，比方假如有一个效果表，我们想知道语文、数学和英语的总分表，此时就可以将 select 背面的表达式写成 chinese + math + english：

---

为查询效果指定别名

1. select 列名 [as] 别名 [, ...] from 表名;

复制代码

as可加可不加，也可以指定多个列的别名，在背面加逗号继续起别名即可
以是上面查询字段为表达式是，就可以将语数英的效果综合改名为总分，此时表中的该列就名为总分：

---

效果去重

假如想要对查询效果举行去重操纵，可以在SQL中的select背面带上distinct

1. select distinct 列名 from 表名;

复制代码

这种操纵就用于查询的数据有重复值，我们想要将这些数据去重时的操纵 

---

where 条件

假如在查询数据时没有指定where子句，那么会直接将表中全部的记录作为数据源来依次执行select语句
假如在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句
where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符and或or举行关联
下面是where子句中常用的逻辑运算符和比较运算符：
逻辑运算符：

​
比较运算符：

​
下面举几个利用 where 字句的样例：
①查询语文效果在80到100分的同砚及其语文效果，下面这两种方式都可以

1. select name, chinese from exam_result where chinese between 80 and 100;
3. select name, chinese from exam_result where chinese >= 80 and chinese <= 100;

复制代码

②查询英语效果是59或99分的同砚及其英语效果

1. select name, english from exam_result where english=59 or english=99;
3. select name, english from exam_result where english in (59,99);

复制代码

③查询姓王的同砚/查询王某同砚

1. 查询姓王的同学
2. select name from exam_result where name like '王%';
3. 查询王某同学
4. select name from exam_result where name like '王_';

复制代码

④NULL的查询
查询QQ号已知的同砚/查询QQ号未知的同砚

1. 查询QQ号已知的同学
2. select name, qq from students where qq is not null;
3. 查询QQ号未知的同学
4. select name, qq from students where qq<=>null;

复制代码

 在与 null 值作比较的时候应该利用 <=> 运算符，利用 = 运算符无法得到正确的查询效果
⑤查询语文和数学总分大于180分的同砚

1. select name, chinese+math 总分 from exam_result where chinese+math>180;

复制代码

需要注意的是，在where子句中不能利用select中指定的别名：
查询数据时是先根据where子句筛选出符合条件的记录
然后再将符合条件的记录作为数据源来依次执行select语句
以是在上面查询总分时，where背面不能写成 总分>180

---

效果排序

  

1. select {*/列名} from 表名 where order by 列名 [asc/desc];

复制代码

asc和desc分别代表的是排升序和排降序，不特别指明就默认为 asc 排升序
注意：null值视为比任何值都小，因此排升序时出如今最上面
order by子句中可以指明按照多个字段举行排序，每个字段都可以指明按照升序或降序举行排序，各个字段之间利用逗号隔开，排序优先级与书写次序雷同
①查询同砚的各门效果，依次按数学降序、英语升序显示

1. select name, math, english from exam_result order by math desc, english asc;

复制代码

上述SQL中，当两条记录的数学效果雷同时就会按照英语效果举行排序，假如这两条记录的英语效果也雷同就会继续按照语文效果举行排序，以此类推
②查询同砚及其总分，按总分降序显示

1. select name, chinese+math 总分 from exam_result order by chinese+math desc;
3. select name, chinese+math 总分 from exam_result order by 总分 desc;

复制代码

查询数据时是先根据where子句筛选出符合条件的记录
然后再将符合条件的记录作为数据源来依次执行select语句
最后再通过order by子句对select语句的执行效果举行排序
order by子句的执行是在select语句之后的，以是在order by子句中可以利用别名
以是上述SQL可以利用 总分 desc 如许的方式
③查询姓孙的同砚或姓曹的同砚及其数学效果，按数学效果降序显示
像这种长的题目，先完成查询，再举行排序

1. 查询
2. select name, math from exam_result where name like '孙%' or name like '曹%'；
3. 排序
4. order by math desc;
5. 查询和排序合并起来
6. select name, math from exam_result where name like '孙%' or name like '曹%'
7. order by math desc;

复制代码

---

筛选分页效果

从第0条记录开始，向后筛选出n条记录：

1. select {*/列名} from 表名 [where...] [order by] limit n;

复制代码

从第s条记录开始，向后筛选出n条记录：

1. select {*/列名} from 表名 [where...] [order by] limit n offset s;

复制代码

SQL中各语句的执行次序为：where、select、order by、limit
limit子句在筛选记录时，记录的下标从0开始
假如从表中筛选出的记录不敷n个，则筛选出几个就显示几个
按id举行分页，每页3条记录，分别显示第1、2、3页

1. 第一页筛选3条记录(从0开始)
2. select * from exam_result limit 3 offset 0;
3. 第二页筛选3条记录(从3开始)
4. select * from exam_result limit 3 offset 3;
5. 第三页筛选3条记录(从6开始，不够就输出现有的数量)
6. select * from exam_result limit 3 offset 6;

复制代码

从表中筛选出的记录不敷n个，则筛选出几个就显示几个 

---

聚合函数查询

聚合函数可以在select语句中利用，此时select每处理惩罚一条记录时都会将对应的参数传递给这些聚合函数

利用*做统计
可以获取表中的记录个数

1. select count(*) from students;

复制代码

---

 利用表达式做统计
在select语句中利用count函数，并将表达式作为参数传递给count函数，这时也可以统计出表中的记录条数

1. select count(1) from students;

复制代码

这种写法相当于下图，自行新增了一列列名为特定表达式的列，我们就是在用count函数统计该列中有多少个数据，等价于统计表中有多少条记录

---

统计班级网络的QQ号有多少个

1. select count(qq) from students;

复制代码

假如count函数的参数是一个确定的列名，那么count函数将会忽略该列中的NULL值

---

统计本次考试数学效果的分数个数（去重）

1. select count(distinct math) from students;

复制代码

在count中加上 distinct 修饰可以或许完成去重的操纵

---

统计平均总分

1. select avg(chinese+math) 平均总分 from exam_results;

复制代码

---

返回70分以上的英语最低分
团结where和聚合函数查询

1. select min(english) from exam_result where english>70;

复制代码

---

分组查询

1. select 列名 from 表名 [where...] group by 列名 [order by...] [limit...];

复制代码

查询SQL中各语句的执行次序为：where、group by、select、order by、limit
group by背面的列名，表示按照指定列举行分组查询
显示每个部门的平均工资和最高工资
题目要求的是显示每个部门，以是就是按照部门分组查询

1. select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno

复制代码

---

显示每个部门的每种岗位的平均工资和最低工资
题目要求的是显示每个部门，以是就是按照部门和岗位分组查询

1. select deptno, avg(sal) 平均工资, min(sal) 最低工资 from emp group by deptno， job;

复制代码

---

HAVING 条件

1. select ... from 表名 [where...] [group by...] [having...] [order by...] [limit...];

复制代码

SQL中各语句的执行次序为：where、group by、select、having、order by、limit
having子句中可以指明一个或多个筛选条件
having子句和where子句的区别
①where子句放在表名背面，而having子句必须搭配group by子句利用，放在group by子句的背面
②where子句是对整表的数据举行筛选，having子句是对分组后的数据举行筛选
③where子句中不能利用聚合函数和别名，而having子句中可以利用聚合函数和别名

---

统计每个部门的平均工资

1. select deptno, avg(sal) 平均工资 from emp group by deptno;

复制代码

---

显示平均工资低于2000的部门和它的平均工资

1. select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;

复制代码

---

修改数据

替换数据

假如表中没有冲突数据，则直接插入数据
假如表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据
  

1. replace into 表名 [列名] values [数据];

复制代码

比方下图所示的语句，表达的含义就是假如出现冲突直接将冲突数据删除，再替换，假如没冲突，直接插入

执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：
1 row affected：表中没有冲突数据，数据直接被插入
2 rows affected：表中有冲突数据，冲突数据被删除后重新插入

---

修改数据

1. update 表名 set 列名=数值 [...] [where...] [order by...] [limit...];

复制代码

在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的
下面举例阐明：
①将孙悟空同砚的数学效果修改为80分

1. update exam_result set math=80 where name='孙悟空';

复制代码

②将总效果倒数前三的3位同砚的数学效果加上30分

1. update exam_result set math=math+30 order by chinese+math asc limit 3;

复制代码

③将全部同砚的语文效果修改为原来的2倍

1. update exam_result set chinese=chinese*2;

复制代码

---

删除数据

1. delete from 表名 [where...] [order by...] [limit...];

复制代码

在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的
①删除孙悟空同砚的考试效果

1. delete from exam_result where name='孙悟空';

复制代码

②删除整张表数据

1. delete from exam_result;

复制代码

假设表中有自增长的主键id，原本有三条数据，id分别是1,2,3，删除整张表数据后，再插入数据，不指明 id 是多少时，默认从4开始，这是有一个 AUTO_INCREMENT=n 的字段，表示下一次插入数据时自增长字段的值应该是 n 
通过 show create table 表名 可以看到这个字段

---

截断表

1. truncate [table] 表名;

复制代码

①truncate只能对整表操纵，不能像delete一样针对部门数据操纵
②truncate现实上不对数据操纵，以是比delete更快
③truncate在删除数据时不颠末真正的事务，以是无法回滚
④truncate会重置AUTO_INCREMENT=n字段
利用截断表的操纵时，删除完数据后，重新插入数据对应的自增长id值是重新从1开始增长

---

数据库的增删改查(CURD)操纵到此竣事

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