MySQL中常用的操作语句已汇总

一、库语句

1.查询现有数据库

（1）语法：show databases;

（2）示例

表示当前有五个数据库

2.创建数据库

（1）语法：create database 库名

（2）创建失败示例：忘记加关键字database

（3）创建成功示例

（4）细节

数据库的名字也不能使用mysql中的关键字

除非：加上`的符号，例如：`关键字`

（5）指定字符集（*）

语法：charset 字符集名

在创建数据库时，在名字后面加上 charset utf8

如：create database 数据库名 charset utf8

一般mysql默认字符集是GBK，这样是无法使用汉字字符的，需要指定utf8才可。我们是推荐都在创建数据库的后面加上，否则后续只能重新建数据库

3.选中数据库

（1）语法：use 数据库名

（2）示例

（3）作用：只有选中数据库，才能进行后续的表操作

4.删除数据库

（1）语法：drop database 数据库名字

（2）示例：

（3）后果：删除数据库后，对应所有的表也会消失，所以这个是一个很危险的操作

二、初阶表操作

在选中数据库之后才能进行的表操作，同一个数据库中，表名不能重复；不同库则可以

1.查看数据库现有表

（1）语法：show tables;

（2）示例：

 

2.查看表结构

（1）语法：desc 表名

（2）示例：可以查询表的结构，有多少列，分别对应什么名字和类型

3.创建表

（1）语法：create table 表名(变量名字 类型,变量名字 类型,……);

（2）示例：

（3）注意：名字在前，类型在后；类型就决定了后续插入数据的类型

4.删除表

（1）语法：drop table 表名

（2）示例：

（3）注意：删表是比删库还危险的操作，慎重！

5.全列查询

（1）语法：select * from 表名

（2）作用：查询这个表的所有列和已有的数据

（3）示例：该表中只有一个数据

（4）注意：这种操作只适合在前期学习的时候使用，当数据量庞大的时候，不推荐

6.删除表2

（1）语法：delete from 表名 where 条件;

删除的维度都是一行，也需要添加合适的条件；不添加任何条件，就是删除这个表的所有

（2）与drop的区别

drop是删除整个表，表和内容都没了。

delete删除是把表的内容都删完了，但是表格还在。

（3）示例

7.修改操作

（1）语法：update 表名 set 列名 = 值 where 条件;

如果不加限制条件，会将一列所有的元素全部修改。这种会影响硬盘上面存储的数据

（2）示例：修改单个列

（3）示例：修改多个列

注意事项：

修改时，不能超出类型本身的限制，否则就是修改失败。

为了防止被修改的数据太多，我们建议一条一条的修改，也就是在后面加上limit 1的限制

三、插入操作

进行插入操作时，最好是清楚表的结构，否则容易出现错误

1.全列插入

（1）语法：insert into 表名 valuse(值,值,值,值…);

（2）作用：这样的操作是对所有的列都插入操作，值必须刚好对应上所有的列

（3）示例：关键字into是可以省略的

2.指定列插入

（1）语法：insert into 表名 (列名,列名……) values(值,值……);

（2）注意：每个值要对应的相应的列；两个列名的顺序可以换，但是相应的值顺序也要换

（3）示例：这里只插入了name、age，未插入的列默认为null值

3.一次插入多组数据

（1）一次性全列插入多组数据

做法：只需要多加几个括号即可

（2）一次性指定列插入多组数据

4.插入操作的细节

（1）基本数据类型

（1）表示整数：int

（2）表示小数：double

（3）表示字符串：varchar    使用时在后面指定长度

（4）表示时间日期：datetime

（5）decimal(a,b)：表示小数，a表示这个数的长度，b表示小数的位数

（2）插入字符串

字符串例如：汉字，字符、字符串；需要在两头加上单引号或者双引号(英文)

（3）插入时间

插入时间例如：2018-3-28 也需要加上单引号或者双引号，如："2018-3-28"

四、数据库的约束

这是在建表的时候加上的约束，约束也是对表起的效果

1.not null

（1）作用：用来限制某一列不能为空

（2）正常无约束时

（3）加上约束后

（4）插入数据失败

（5）成功插入数据

2.unique

（1）作用：限制这一列的值不能有重复的

（2）没有约束时

（3）加约束

（4）插入数据失败

3.default

（1）作用：设置默认值。当没有对这列数据赋值时，默认是default。没有加该约束时，默认是NULL

（2）正常无约束时

（3）未加约束时的默认展示

（4）加了约束后

（5）添加数据后

4.primary key

（1）作用：主键，用来标记某一列，作为身份的唯一标识。类似身份证的作用

外键的两个要求：

（1）不能为null

（2）不能重复

（2） 未设置主键时

（3）加了主键后

（4）当被主键约束且为空时

（5）当重复时

（6）not null和unique配合也可以承当主键

（7）主键的使用推荐

1.一个表只能有一个主键

2.一个主键不一定只针对一个列（很少用）

3.主键一般设置在整数类型

（8）自增主键

写法：在设置主键时，跟在主键后面。关键字：auto_increment

添加后：

作用：

当插入数据的这一列不赋值时，系统自动赋值为：当前最大值+1

插入数据：

各种情况：

3-99的值如果需要用，需要手动指定

5.foreign key

（1）作用：外键约束，用于两张表的约束。设置外键的列在父表中也是唯一的（被主键约束或者被unique约束）

（2）语法格式：子表的列名不一定要和父表名字一样

列名 类型,foreign key (子表的列名) references 主表(父表的列)

（3）未设置外键时

他们的列名相同，按理来说student的classId必须遵循class表中的classId，也就是如果class表中没有的id，student中是不能有的。但是这两个表当前并没有任何关系，也就是没有被约束，所以可以随便填

（4）重新设置外键

外键设置的表称为子表，表示受限于父表

此时，class称为父表，student称为子表。student中的classId值在class表中要必须存在

（5）当值不存在时

正常添加：

（6）不能随便删和修改除父表的值

如何删除父表呢？：加一个标记字段

1.在父表中增加一个字段（可以约定这个值为1时，表示有效数据；当为0是，表示无效数据）

2.在想要删除的时候，不能直接delete，而是可以把这个字段改成0

3.后续在查询的时候，只能返回有效数据，因此相当于删除了

4.这种做法在计算机很多方面都有使用，称为逻辑删除

6.check

用来规定填入的某个数据只能是某个值，如：男或女

语法格式：sex varchar(2),check (sex = '男' or sex = '女')

这种用法很少，且新版本才支持，所以用的很少很少

五、一阶查询

前面提到过，全列查询是一个危险操作，所以下面学习其他方式的查询操作  

1.指定列查询

（1）语法：select 列名,列名 from 表名

（2）示例：

2.表达式查询

（1）语法：select 表达式(由列组成) from 表名;

（2）示例：

（3）表达式可以起别名，语法：as 别名

不仅表达式可以起别名，后续的很多情况也可以起别名

3.去重查询

（1）语法：select distinct 列名 from 表名；

（2）作用：查询的所有列中，会将重复的两行合并成一行

（3）示例：第一个是未查重的，第二个是查重操作

合并失败：所有的列并不重复，只有部分重复

 

4.排序查询

（1）语法：select * from 表名  order by 列名/表达式  默认排升序

（2）排降序语法：select * from 表名  order by 列名/表达式 desc

（3）未排序：

按照math排升序

按照math排降序

（4）细节

1.NULL在order by 的时候，视为最小值

2.如果存在多个NULL，他们的顺序是不确定的

3.select 列名 from oeder by 列名，order by后面的列名不一定要在select后面的列名中出现

4.在SQL中，对操作数进行算数运算时，只要有一个NULL，最终结果就是NULL

（5）可以排序多个列：如果第一个列名相同，则会继续比较第二个

 

5.条件查询（*）

（1）语法：select */列名/表达式/去重……  from  表名  where 条件（条件就是一些一些符号表达式，可以有多个）

（2）表达式（用来表示条件）这里不推荐使用别名

（3）示例展示

1）>,>=,<,<=

 

2）=、<=>、!=、<>

用来判断null，一般使用<=>和<>

3）between and  前闭后闭

4）in  匹配离散集合

5）like

两个通配符

（1）%：表示可以匹配任意多个（包括0）任意字符

（2）_：表示任意一个字符

查询孙某

like模糊匹配的缺点：开销很大，性能非常低

6）is NULL、is not null

用来查询非空/空的行

7）and、or、not   一般and的优先级高于or

逻辑符号，相当于java中的&&、||和！

（4）作用：使用好可以配合后面的操作，更细腻的查询精准的信息

6.分页查询

（1）语法：

（1）limit+数字：限制每次查询最多返回的记录个数。如：limit 3只能查询到前3条记录

（2）offset：偏移量。如：limit 3 offset 3：偏移了前3条记录，从第4条开始向后查询3条。不写offset时默认是0

（3）limit 6，3；这里的6代表offset，3代表查询的最大条数

（2）作用：配合排序操作，可以查询第几条消息

（3）示例

查询总成绩前三的同学：

六、二阶查询

这里的查询操作，可以把多个行进行合并

1.新增插入查询

（1）语法：insert into 表1 select * from 表2;

（2）作用：在查询表1的时候，可以同时把查询到的数据插入到表2中

（3）全列查询插入

（4）指定列查询插入

2.聚合查询

通过聚合函数将多个行合并

（1）语法：select 聚合函数(列名)  from 表名;

（2）聚合函数

（3）count

返回查询到的数据的数量（行数）。

步骤：先查询结果，再进行聚合

求总行数：NULL也会计算在内

求个别列：NULL不会计算

（4）sum

求总和，不是数字没有意义

sum不能进行*查询，只能指定列

（5）avg

求平均值，不是数字没有意义

（6）max

求最大值，不是数字没有意义

（7）min

求最小值，不是数字没有意义

3.分组查询 group by

（1）定义：

对指定列进行分组操作：指定某个列，针对这个类，把值相同的行，分到一组中，可以针对每个组，分别进行聚合查询。

（2）注意事项：

1.select指定的列必须是分组依据字段，否则就会像下面的一样，有的数据不会显示。

2.非group by的列，不应该直接写在select查询的列中，但是搭配聚合函数是可以的。

会有这些问题：

（3）group by结合聚合查询进行：

查询每个岗位有多少人：

步骤

第一：先执行select role,id from emp（先将指定表中的这些列查询出来）

第二：再根据group by role，按照role这个列的值和上面查询的结果进行分组

第三：根据每个组，进行count聚合操作

id和role都进行了聚合，就不会发生数据丢失

但是这样子不可以，必须配合聚合操作

（4）求每个岗位的平均的薪水（分组依据：岗位）

错误：非group byd的列role应该使用聚合函数

修改 ：待考证

having:

给聚合查询指定条件。在group by分组之后，不能使用where语句，而是需要having

where可以在分组前使用（聚合之前的条件），也就是在平均工资里面，没有计算张三

查询每个岗位的平均工资，但是刨除平均工资超过2w的数据（条件是在聚合之后执行的，只能用having）

上面两种条件结合：

七、三阶查询/多表查询

联合查询又称多表查询，一次性可以查询多个表。

基本语法1：select * from 表名,表名 where 条件

基本语法2：select * from 表名 join 表名 on 条件

1.多表查询及步骤

（1）同时查询两个表就行将两个表中的行分别进行全排列，也就称为笛卡儿积

实践多表查询：

通过下面的查询：这样不设置条件直接查询，会得出很多无效的数据

加上条件：

（2）总结步骤

下面有四张表：

1：班级表

2：学生表

3：课程表

4：分数表

汇总：

总结步骤：

（1）先确定要查询的信息，来自哪些表

（2）针对这两个表进行笛卡儿积

（3）加上连接条件，去掉无效条件

（4）再根据题目要求，补充其他条件

（3）按照步骤进行查询

查询许仙同学的成绩：

1）确定信息来自哪些表

2）对这两个表进行笛卡儿积

3）加上连接条件，去掉无效条件

4）接着补充条件

优化：

2.多表查询join on

语法：表1 join 表2  on  条件

（1）使用join on查询许仙同学的成绩

（2）查询所有同学的总成绩，及同学的个人信息

第一步：表来自student、score

第二步：加入连接条件

第三步：优化

（3）查询所有同学的成绩，及同学的个人信息

第一步：列出相关的表：学生表、课程（所有的成绩，需要知道对应的科目）、分数表

第二步：三个表笛卡儿积

第三步：设置连接条件

第四步：精简

一般多个表之间笛卡儿积的话，每两个表之间都会有联系的，否则是不好笛卡儿积的，数据量太庞大。

以上的写法属于内连接

3.外连接

内连接和外连接都是基于笛卡儿积进行计算的，但是对于空值/不存在的值处理方式是不一样的。内连接只能得到两张表中都存在的数据，而外连接不一样

外连接：只能使用join on 的方式写，可以在join前头加上left/right的关键字，称为左连接/右连接。加上后表明left/right表的数据会全部输入

student  join score，此时，在join的左边student就是左侧表，右侧就是右侧板。

加了left左边的表会保证每个数据都会存在，不存在部分会有null补充

4.自连接

自连接就是同一张表对自己进行笛卡儿积

（1）查询所有同学的“计算机原理”成绩比“Java”成绩高的成绩信息

所有成绩：

对应的课程：

题目要求就是找到课程号3的成绩大于课程和1的，也就是3>1。两个成绩都在一张表上，比较的话就是行与行之间的比较，不好实现。这个时候借助笛卡儿积就可以就两个表放在一起，这就是自连接。

直接笛卡儿积：

做法：自连接时，给两张表都起别名。下面圈起来的就是满足条件的

加上优化条件：对应同一个人

加上：左边是计算机原理课程，右边是Java课程

加上：分数对应大小关系，3>1

自连接的优缺：

优：可以把行之间的关系转换成列之间的关系

缺：产生的很多结果是没有必要的

5.子查询

这种查询方式将简单问题复杂化，例如直接将一个函数的返回值作为另一个函数的参数，直接写在了一行上面（复杂化）

概念： 把多个sql嵌套成了一个sql，在实际中不推荐使用。返回一行记录的子查询

（1）查询与“不想毕业”同学的同伴同学

步骤：要想知道他的同班同学，需要知道是什么班；通过名字可以查询到他们是什么班级

正常查询：

先查询“不想毕业”同学的班级号：

通过班级号可以查询到他们班：

使用子查询：

（2）多行子查询

使用in()的条件查询，在in中可以有多个参数

查询“语文”或“英语”课程的成绩信息

需要知道课对应程号，通过课程号可以查到这科的成绩

正常的查询：

多行子查询：

6.合并查询

可以同时查询多个表，最后把结果合并到一张表上并输出。使用关键字：union

正常查询：查询id小于3，或者名字为“英语”的课程。

使用or

使用合并查询：

这样使用union不是多此一举吗?不是的，union可以查询不同的表，而or只能针对一张表。

要求：每个表查询的结果集合列中的类型和个数都要匹配，才能合并，并且可以自动去重

两个不同的表：

合并查询：