目录

一、库的操作

创建数据库

查看数据库

显示创建语句

修改数据库

删除数据库

备份和恢复

二、表的操作

创建表

查看表结构

修改表

删除表

三、表的增删查改

新增数据

插入否则更新

插入查询的结果

查找数据

为查询结果指定别名

结果去重

where 条件

结果排序

筛选分页结果

聚合函数查询

分组查询

HAVING 条件

修改数据

替换数据

修改数据

删除数据

截断表

SQL语句中不区分大小写，下面都以小写为主

下面的SQL语句第一行是最简便的，第二行是具体的，包含可选的内容，[ ] 中代表的是可选项

创建数据库

创建一个新的数据库

charset：用于指定数据库所采用的编码格式，字符集
collate：用于指定数据库所采用的校验规则

如果创建数据库时未指明数据库的编码格式或校验规则，则默认使用MySQL配置文件中对应的编码格式和校验规则

默认的编码格式是utf8，默认的校验规则是utf8_general_ci

不指明数据库的编码格式和校验规则：

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

查看数据库

可以查看系统中所有的数据库

显示创建语句

可以查看对应数据库的创建语句 

注意：

①数据库的名字加上反引号，是为了防止使用的数据库名与关键字冲突
不是注释，它表示当前MySQL版本如果大于4.10，则执行后面的SQL语句

修改数据库

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

需要注意的是MySQL数据库不支持修改库名

db1的字符集、校验规则原本是默认的，现在将数据库的字符集改为gbk，将数据库的校验规则改为gbk_bin：

删除数据库

删除一个现有的数据库

删除数据库后该数据库对应的文件夹就被删除了

并且删除数据库后，该数据库下的所有表也都会被级联删除，因此不要随意删除数据库

备份和恢复

数据库的备份 

先创建一个数据库

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

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

back.sql文件即可看到，文件中的内容实际就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句

数据库恢复

使用如下命令即可对指定数据库进行恢复：

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

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

表的备份

表的恢复

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

DDL(数据定义语言)：比如建表、删表、该表、新增列、删除列等
DML(数据操作语言)：比如插入记录、删除记录、修改记录等

下面都是表的DDL操作

创建表

comment表示对指定列添加注释信息

建表时没有指定使用哪种存储引擎，那么就会默认使用InnoDB存储引擎 

查看表结构

desc 表名可以查看表的结构

①Field：表示该字段的名字
②Type：表示该字段的类型
③Null：表示该字段是否允许为空
④Key：表示索引类型，比如主键索引为PRI
⑤Default：表示该字段的默认值
⑥Extra：表示该字段的额外信息说明

show create table [表名]G，可以查看创建表时的相关细节

修改表

新增列如果想新增到哪一列之后，可以在后面加上 after 列名：

 alter table student add name varchar(30) comment '姓名' after sex;

上述语句表示新增一列 name 列，并将 name 列放在 sex 列的后面

新增列后可能还需要对原来插入的记录进行修改，因为假设原有2条数据，此时新增一列，那么原有的2条数据中新增的那一列数据就默认为空

想将插入的列插入到第一列，就在SQL语句的最后加上first即可

删除表

drop删除表结构会直接删除整个表，内存中就没有这个表了

delete删除只删除数据，不删除结构，delete完表还在，只是为空了

表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）

CRUD的操作对象是对表当中的数据，是典型的DML（Data Manipulation Language）数据操作语言

新增数据时，如果在values前不加列名，就表示按照表中默认的列顺序进行全列插入

假设表的结构如下：

单行数据 + 全列插入

不加列名，全列插入

多行数据 + 指定列插入

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

插入否则更新

向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败

如果表中没有冲突数据，则直接插入数据
如果表中有冲突数据，则将表中的数据进行更新

column1=value1表示有冲突时，需要更新的列值

下图的含义就是：如果出现主键或唯一键冲突，则将表中冲突记录的学号和姓名进行更新

执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：

0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同
1 row affected：表中没有冲突数据，数据直接被插入
2 rows affected：表中有冲突数据，并且数据已经被更新

插入查询的结果

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

删除表中重复的记录，重复的数据只能有一份

假设当前表数据如下：

要求删除测试表中重复的数据，思路如下：

①创建一张临时表，表的结构和数据与原表相同
②去重的方式查询并插入
③将原表改名，再将该表重命名为原表的名称，从而完成去重操作

步骤如下：

①创建临时表 no_duplicate 的时候可以借助like进行创建

②通过插入查询语句将去重查询后的结果插入到临时表中，临时表成功去重

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

其中select后面是*就表示全列查询
如果是列名就对指定的列进行查询，多个列用逗号隔开
如果是表达式，表达式中可以包含多个表中已有的字段，例如列有数学、英语成绩，表达式就可以数学+英语成绩，从而计算出更多有意义的数据

后面的where、order by、limit都是可选项
{ }中的 / 代表可以选择其中某一条语句

全列查询

在查询数据时直接用，表示进行全列查询，这时将会显示被筛选出来的记录的所有列信息：

指定列查询

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

表示查找指定的 name 和 math 列

查询字段为表达式

我们也可以将表达式罗列到列表中：

此时就会多一个列，列名为1+1，每行数据都是1+1的值

这种方式可以帮助我们解决很多事情，例如如果有一个成绩表，我们想知道语文、数学和英语的总分表，此时就可以将 select 后面的表达式写成 chinese + math + english：

为查询结果指定别名

as可加可不加，也可以指定多个列的别名，在后面加逗号继续起别名即可

所以上面查询字段为表达式是，就可以将语数英的成绩综合改名为总分，此时表中的该列就名为总分：

结果去重

如果想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct

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

where 条件

如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句

如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句

where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符and或or进行关联

下面是where子句中常用的逻辑运算符和比较运算符：

逻辑运算符：

​

比较运算符：

​

下面举几个使用 where 字句的样例：

①查询语文成绩在80到100分的同学及其语文成绩，下面这两种方式都可以

②查询英语成绩是59或99分的同学及其英语成绩

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

④NULL的查询

查询QQ号已知的同学/查询QQ号未知的同学

 在与 null 值作比较的时候应该使用 <=> 运算符，使用 = 运算符无法得到正确的查询结果

⑤查询语文和数学总分大于180分的同学

需要注意的是，在where子句中不能使用select中指定的别名：

查询数据时是先根据where子句筛选出符合条件的记录
然后再将符合条件的记录作为数据源来依次执行select语句

所以在上面查询总分时，where后面不能写成 总分>180

结果排序

asc和desc分别代表的是排升序和排降序，不特殊指明就默认为 asc 排升序

注意：null值视为比任何值都小，因此排升序时出现在最上面

order by子句中可以指明按照多个字段进行排序，每个字段都可以指明按照升序或降序进行排序，各个字段之间使用逗号隔开，排序优先级与书写顺序相同

①查询同学的各门成绩，依次按数学降序、英语升序显示

上述SQL中，当两条记录的数学成绩相同时就会按照英语成绩进行排序，如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序，以此类推

②查询同学及其总分，按总分降序显示

查询数据时是先根据where子句筛选出符合条件的记录
然后再将符合条件的记录作为数据源来依次执行select语句
最后再通过order by子句对select语句的执行结果进行排序

order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名

所以上述SQL可以使用 总分 desc 这样的方式

③查询姓孙的同学或姓曹的同学及其数学成绩，按数学成绩降序显示

像这种长的题目，先完成查询，再进行排序

筛选分页结果

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

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

SQL中各语句的执行顺序为：where、select、order by、limit

limit子句在筛选记录时，记录的下标从0开始
如果从表中筛选出的记录不足n个，则筛选出几个就显示几个

按id进行分页，每页3条记录，分别显示第1、2、3页

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

聚合函数查询

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

使用做统计

可以获取表中的记录个数

 使用表达式做统计

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

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

统计班级收集的QQ号有多少个

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

统计本次考试数学成绩的分数个数（去重）

在count中加上 distinct 修饰能够完成去重的操作

统计平均总分

返回70分以上的英语最低分

结合where和聚合函数查询

分组查询

查询SQL中各语句的执行顺序为：where、group by、select、order by、limit

group by后面的列名，表示按照指定列进行分组查询

显示每个部门的平均工资和最高工资

题目要求的是显示每个部门，所以就是按照部门分组查询

显示每个部门的每种岗位的平均工资和最低工资

题目要求的是显示每个部门，所以就是按照部门和岗位分组查询

HAVING 条件

SQL中各语句的执行顺序为：where、group by、select、having、order by、limit

having子句中可以指明一个或多个筛选条件

having子句和where子句的区别

①where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面

②where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选

③where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名

统计每个部门的平均工资

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

替换数据

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

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

执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：

1 row affected：表中没有冲突数据，数据直接被插入
2 rows affected：表中有冲突数据，冲突数据被删除后重新插入

修改数据

在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的

下面举例说明：

①将孙悟空同学的数学成绩修改为80分

②将总成绩倒数前三的3位同学的数学成绩加上30分

③将所有同学的语文成绩修改为原来的2倍

在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的

①删除孙悟空同学的考试成绩

②删除整张表数据

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

通过 show create table 表名 可以看到这个字段

截断表

①truncate只能对整表操作，不能像delete一样针对部分数据操作

②truncate实际上不对数据操作，所以比delete更快

③truncate在删除数据时不经过真正的事务，所以无法回滚

④truncate会重置字段

使用截断表的操作时，删除完数据后，重新插入数据对应的自增长id值是重新从1开始增长

数据库的增删改查(CURD)操作到此结束