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

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

目录

一、库语句

1.查询现有数据库

2.创建数据库

3.选中数据库

​编辑

4.删除数据库

二、初阶表操作

1.查看数据库现有表

2.查看表结构

3.创建表

4.删除表

5.全列查询

6.删除表2

7.修改操作

三、插入操作

1.全列插入

2.指定列插入

3.一次插入多组数据

4.插入操作的细节

四、数据库的约束

1.not null

2.unique

3.default

4.primary key

5.foreign key

6.check

五、一阶查询

1.指定列查询

2.表达式查询

3.去重查询

4.排序查询

5.条件查询(*)

6.分页查询

六、二阶查询

1.新增插入查询

2.聚合查询

3.分组查询 group by

 having:

七、三阶查询/多表查询

1.多表查询及步骤

2.多表查询join on

3.外连接

4.自连接

5.子查询

6.合并查询


一、库语句

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)聚合函数

函数 说明
count 返回查询到的数据的数量
sum 返回查询到的数据总和,不是数字没有意义
avg

返回查询道德数据的平均值,不是数字没有意义

max 返回查询到的数据的最大值,不是数字没有意义
min 返回查询到的数据的最小值,不是数字没有意义

(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只能针对一张表。

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

两个不同的表:

合并查询:

转载请说明出处内容投诉
CSS教程_站长资源网 » MySQL中常用的操作语句已汇总

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买