MySQL 之 SQL语句详解

kk起个名吧
720 阅读12分钟

一、 SQL语句分类

  • DDL：Data Defination Language 数据定义语言，用于创建数据库对象，如库、表、索引等。
    • CREATE，DROP，ALTER
  • DML：Data Manipulation Language 数据操纵语言，用于对表中的数据进行管理。
    • INSERT，DELETE，UPDATE
    • 软件开发：CRUD
  • DQL：Data Query Language 数据查询语言，用于从数据表中查找符合条件的数据记录。
    • SELECT
  • DCL：Data Control Language 数据控制语言，用于设置或者更改数据库用户或角色权限。
    • GRANT，REVOKE
  • TCL：Transaction Control Language 事务控制语言，用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。
    • COMMIT，ROLLBACK，SAVEPOINT

二、 DDL语言

详见：MySQL 之 基本操作 —— 三、管理数据库

三、 DML语言

DML：INSERT（插入），DELETE（删除），UPDATE（更新）

1. INSERT语句

功能：一次插入一行或多行数据

格式：

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

//解释
insert  表名[（字段）]  值（值1，值2....）,(值1，值2)......；
使用 insert 语句时 如果不在表后加上字段就要一一对应填写上信息（注意 字符串用引号引起来）

也可以指定添加的字段

示例：

insert student(name,age) values('xwx','25');
insert1.png 
insert student values('2','lkk','25','F');
//不指明添加字段，值需要全部写上
insert2.png

2. UPDATE语句

功能：更新数据

格式：

update 表名  set  字段='修改的值'[,字段2='修改的值2']  where 指定条件;

`注意：`一定要有限制条件，否则将修改所有行的指定字段

示例：

update student set age='24' where id=3;
//把id为3的数据的 年龄改为24
update.png

3. DELETE语句

删除表中数据，但不会自动缩减数据文件的大小

格式：

delete  from  表名 where  指定条件;

`注意：`一定要有限制条件，否则将清空表中的所有数据

示例：

delete from student where id=3;
//删除student表中的第3条数据
delete.png

四、DQL语言

1. DQL语言（单表查询）

添加脚本：source /root/hellodb_innodb.sql （source 脚本绝对路径 #可以执行sql脚本）

1.1 SELECT —— 显示表格中一个或数个字段的所有数据记录

语法：

select 字段 from  表名;   
//字段可以用* 表示所有字段，也可以挑选出自己想要的字段

示例：

select  age from students; 
select  age,name  from students;

1.2 as —— 字段使用别名

语法：

select 字段1 as 别名1,字段2 as 别名2  from  表名;  
select 字段1 别名1,字段2 别名2  from  表名;      //as可省略

示例：

select name 姓名,gender 性别 from student;
as别名.png

1.3 where —— 过滤查询

语法：

select 字段 from 表名 where 条件;

说明：

  • 过滤条件：布尔型表达式
  • 算术操作符：+, -, *, /, %
  • 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
  • 范例查询: BETWEEN min_num AND max_num
  • 不连续的查询: IN (element1, element2, ...)
  • 空查询: IS NULL, IS NOT NULL
  • 字段显示可以使用别名

示例：

select * from students where name="xi ren";         //姓名为xi ren
select * from students where age=20;                //年龄等于20岁
select * from students where age >20;               //大于20岁
select * from students where age >=20 and age<=30;  //20岁到30岁

`范围区间`
select * from students where age between 20 and 30; //20岁到30岁

`不连续查询`
select * from students where age  in (20,22,30);    //年龄为20、22、30

`空查询` 
select * from students where classid is null;       //classid为空
select * from students where classid is not null;   //classid不为空
where1.png where2.png where3.png where4.png where5.png

1.4 and | or —— 且 | 或

语法：

select  字段  from 表名where 条件1  {[AND|OR] 条件2 }+ ;

示例：

select * from students where (age >20 and age <30) or age >50;  //年龄20到30之间或大于50
select * from students where age > 50  or  age <20;             //年龄大于50或者小于20
select * from students where age > 50  and  classid=2;          //年龄大于50且班级id为2
select * from students where age < 20  and  gender="F";         //年龄小于20且性别为女
and or 1.png and or 2.png

1.5 distinct —— 去除重复行

语法：

select distinct 字段 from 表名;

示例：

select distinct age from students;   //去掉重复的年龄
distinct.png

1.6 like —— 模糊查询 与 通配符

通配符：

% ：百分号表示零个、一个或多个字符   *
_ ：下划线表示单个字符             ？     

'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

语法：

select 字段 from 表名 where 字段 like “匹配表达式”;

示例：

select * from students where name='Xu Zhu';    //精确查找
select * from students where name like "xu %"; //在students表中找到姓名以xu开头的
select * from students where name like "%yu%"; //在students表中找到姓名包含yu字符的

create table test like students;   //复制表结构
like.png

补充： sql 注入攻击

1.7 数学函数

函数名函数值
abs(x)返回 x 的绝对值
rand()返回 0 到 1 的随机数
mod(x,y)返回 x 除以 y 以后的余数
power(x,y)返回 x 的 y 次方
round(x )返回离 x 最近的整数
round(x,y)保留 x 的 y 位小数四舍五入后的值
sqrt(x)返回 x 的平方根
truncate(x,y)返回数字 x 截断为 y 位小数的值
ceil(x)返回大于或等于 x 的最小整数
floor(x)返回小于或等于 x 的最大整数
greatest(x1,x2...)返回集合中最大的值，也可以返回多个字段的最大的值
least(x1,x2...)返回集合中最小的值，也可以返回多个字段的最小的值

1.8 聚合函数 ★

函数名函数意义
avg()返回指定列的平均值
count()返回指定列中非 NULL 值的个数
min()返回指定列的最小值
max()返回指定列的最大值
sum(x)返回指定列的所有值之和

语法：

select  函数(*|单个字段)  from  表名;

示例：

  • avg
select avg(age) from students;                  //求所有人年龄的平均值
select avg(age) from students where classid=1;  //求1班年龄平均值
avg.png
  • count
select count(classid) from students;          //统计非空classid 字段 一共有多少行记录
select count(distinct classid) from students; //一共有几个班级 去重
select count(*) from students;                //统计一共有多少条数据

//count(*)    包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL 的行
//count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行
count.png
  • min
select min(age) from students;     //年龄最小值
select min(classid) from students; //班级号最小值
min.png
  • max
select max(age) from students;     //年龄最大值
select max(classid) from students; //班级号最大值
max.png
  • sum
select sum(age) from students;                 //求年龄总和
select sum(age) from students where classid=1; //求1班的年龄总和
sum.png

1.9 字符串函数

函数名函数意义
concat(x,y)将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)获取从字符串 x 中的第 y 个位置开始的字符串，
substr(x,y,z)获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x)返回字符串 x 的长度
replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 y
upper(x)将字符串 x 的所有字母变成大写字母
lower(x)将字符串 x 的所有字母变成小写字母
left(x,y)返回字符串 x 的前 y 个字符
right(x,y)返回字符串 x 的后 y 个字符
repeat(x,y)将字符串 x 重复 y 次
space(x)返回 x 个空格
strcmp(x,y)比较 x 和 y，返回的值可以为-1,0,1
reverse(x)将字符串 x 反转

示例：

  • concat
select concat(name,classid) from students where stuid=1;     //将姓名字段和班级字段合在一起
select concat(name," ",classid) from students where stuid=1; //加空格 美化格式
concat.png
  • substr
select substr(name,1,3) from students;   //从1开始往后取3个字符
select substr(name,3) from students;     //跳过前3个字符
select substr(name,1,3) from students where stuid=25; //取学号为25的学生姓名的前三个字符
substr.png
  • 其他
select length(name) from students where stuid=1;     //返回数据的长度
select replace(name,"y",11) from students where stuid=1; //将name 字段中的 y  换成11
select left(name,3) from students where stuid=1;     //显示name字段左边三个字符  即最开始的三个
select right(name,3) from students where stuid=1;    //显示name字段右边三个字符 即最后三个
select repeat(name,2) from students where stuid=1;   //将name字段 重复显示2次
select lower(name) from students;                    //返回结果全是小写字母
select reverse(name) from students where stuid=1;    //反向显示字符串

1.10 gruop by —— 分组

语法：

select  字段1,聚合函数(字段2)  from  表名  group by 字段1;

示例：

select gender from students group by gender;            //对性别进行分组
select gender,count(*) from students group by gender;   //对性别进行分组，并显示每一组的人数

select gender,avg(age) from students group by gender;   //求男女的平均年龄
select classid,avg(age) from students group by classid; //求班级的年龄平均值

凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面
group by.png group by2.png

1.11 order by —— 排序

语法：

select  字段  from  表名  where  条件  order by 字段 [asc,desc];
//ASC  升序排序，是默认的排序方式
//DESC 降序排序

示例：

select * from students order by age desc;                  //按年龄降序排序
select * from students where classid=1 order by age desc;  //
order by.png

1.12 limit —— 限制

对查询的结果进行输出行数数量限制

语法：

select  字段  from  表名  [条件]  limit 数量;

示例：

select * from students limit 5;                   //取前5条数据
select * from students limit 3,5;                 //跳过前3条，往后取5条数据
select * from students order by age desc limit 5; //按年龄倒序排序，取前5条数据
limit.png

1.13 having

HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足

select classid,count(classid) from students group by classid having classid > 3; //统计班级号大于3的各班人数（不计空）
等于
select classid,count(classid) from students where classid > 3 group by classid;
having.png

1.14 view —— 视图 临时表

视图：数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了映射。保存有实表的查询结果，相当于别名。

利用视图，可以隐藏表的真实结构，在程序中利用视图进行查询，可以避免表结构的变化，而修改程序，降低程序和数据库之间的耦合度。

语法：

create  view  视图名  as  查询结果；//创建视图
drop  view  视图名;                //删除视图

示例：

create view v2 as select age,count(*) from students group by age having age <20;
//将查询结果（年龄小于20岁的各年龄段人数）生成一张虚拟表，命名为v2
view.png

1.15 正则表达式

不推荐使用。弊端：无法使用索引。

2. DQL语言 （多表查询）

2.1 子查询

在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询。

子查询 subquery 即SQL语句调用另一个SELECT子句，可以是对同一张表，也可以是对不同表，主要有以下常见的用法：

示例：用于比较表达式中的子查询

  • 先求出平均年龄，然后再找出比平均年龄大的
select avg(age) from students;   //先求平均年龄
select * from students where age > (select avg(age) from students);  //再找出比平均年龄大的
zichaxun2.png
  • 利用子查询更新数据
select avg(age) from teachers; //先求教师表的平均年龄
update students set age=(select avg(age) from teachers) where name="ma chao";  //再把ma chao的年龄改成教师表的平均年龄

`注意：`如若修改数据，不允许数据来源于本表
zichaxun1.png

示例：用于IN中的子查询

  • 找出students表中 stuid 和teachers表中 tid 相同的数据
select tid from teachers;  //先列出teachers表中tid数据
select name,stuid from students where stuid in (select tid from teachers); //再找出students表中stuid 和teachers表中tid相同的数据
zichaxun3.png

2.2 联合查询 （纵向合并）

联合查询 Union 实现的条件：多个表的字段数量相同，字段名和数据类型可以不同，但一般数据类型是相同的。

示例：

  • 合并两张表 纵向合并
select name,age from students union select name,age from teachers;
union.png
  • 默认union 是会自动去重的；union all不会去重
select * from teachers union select *from teachers;     //去重
select * from teachers union all select *from teachers; //不去重
union2.png

2.3 交叉连接 （横向合并）

cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加。 交叉连接生成的记录可能会非常多，一般不用。

2.4 内连接

inner join 内连接取多个表的交集

语法：

A表内容  inner join  B表名称  on  条件;

示例：

//学生表中的teacherid 和教师表中的tid 相同的行
select * from students inner join teachers on students.teacherid=teachers.tid;

//挑选name字段
select students.name,teachers.name from students inner join teachers on students.teacherid=teachers.tid;

select students.name 学生姓名,teachers.name 老师姓名 from students inner join teachers on students.teacherid=teachers.tid;

//字段取别名
select s.name 学生姓名,t.name 老师姓名 from students s inner join teachers t on s.teacherid=t.tid;
neilianjie.png

五、 DCL语言

详见：MySQL 之 基本操作 —— 六、 用户管理

六、 TCL语言

详见：MySQL 之 架构和性能优化 —— 五、 并发控制

总结

单表查询 
select 显示数据select 字段 from  表名;
as 别名 （as可省略）select 字段1 别名1,字段2 别名2  from  表名;
where 过滤查询select 字段 from 表名 where 条件;
and且  or或select 字段 from 表名 where 条件1  {[AND/OR] 条件2 }+ ;
distinct 去重select  distinct 字段 from 表名;
like 模糊查询select 字段 from 表名 where 字段 like “匹配表达式”;
函数select  函数(*/单个字段)  from  表名;
group by 分组select  字段1,聚合函数(字段2)  from  表名  group by 字段1;
order by 排序select 字段 from 表名 where 条件 order by 字段 [asc,desc];
limit 限制select  字段  from  表名  [条件]  limit 数量;
view 创建视图create  view  视图名  as  查询结果；
avatar
文章
阅读
粉丝