ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL中常用的操作语句已汇总 [打印本页]

作者: 梦应逍遥 时间: 2024-7-23 02:41
标题: 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）查询与“不想毕业”同学的同伴同学
步骤：要想知道他的同班同学，需要知道是什么班；通过名字可以查询到他们是什么班级
  正常查询：