SQL(结构化查询语言)

程序员文章站 2022-06-29 07:53:07

DDL：数据定义语言添加一行：alter table apple add red varchar[50]; 修改表名: rename table apple To banana 修改列名:alter table apple change red green int ; 修改一个表的字段类型：alt ......

单表：

【基础用法】

GROUP BY ... HAVING ...

全外连接，MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

SQL是一种操纵数据库的语言，主要包括增删改查

SELECT columns_name       --查找一列或多列，多列之间用逗号隔开
FROM Table                --目标表
WHERE condition           --过滤条件
GROUP BY columns_name     --按列值分组，可以1个或多个列
HAVING condition          --分组后的筛选条件，HAVING与WHERE区别在于前者表达式中可包含函数
ORDER BY columns_name     --按列排序
LIMIT start, row_count    --对结果进行限定，start表示从哪行开始，row_count表示结果行数

单表：

【基础用法】

有一张学生表student，包括学生id，姓名，年龄字段，下面用SQL来查找数据：

场景一：

单查

#查找Sid,姓名，年龄列
SELECT t.Sid,t.Sname,t.Sage FROM Student t

结果：

SQL(结构化查询语言)

场景二：

where判断

#查找成绩大于等于70分的学生id,姓名，成绩列
SELECT t.Sid,t.Sname,t.Sscore FROM Student t WHERE t.Sscore>'70'

结果：

SQL(结构化查询语言)

场景三：

计算，COUNT,AVG,MAX,MIN

#计算班级人数，平均成绩，最高成绩，最低成绩
SELECT 
  COUNT(*) zrs,
  AVG(t.Sscore) pjcj,
  MAX(t.Sscore) zgf,
  MIN(t.Sscore) zdf 
FROM
  Student t

ps:关于起别名：在查询语句中可以再被查询的字段后空格写上你想要的结果表中的字段名，也可以在想要查询的字段后加一个as，然后再写你想要的字段名，两种方法均可。

结果：

SQL(结构化查询语言)

场景四：

GROUP BY

#计算班级人数，平均成绩，最高成绩，最低成绩,并根据班级分组
SELECT 
  t.class,
  COUNT(*) AS zrs,
  AVG(t.Sscore) AS pjcj,
  MAX(t.Sscore) AS zgf,
  MIN(t.Sscore) AS zdf 
FROM
  Student t 
GROUP BY t.class

结果:

SQL(结构化查询语言)

ps:计算班级平均成绩用到了GROUP BY语句，同样的class被分为一组，当你使用分组语句时，SELECT语句后面只能出现分组字段和聚合函数，比如这里SELECT语句后面要是有Sid就会报错。还有这里用到了as 重命名功能，一般使用函数后会重新命名，不然得到的列默认为函数表达式。

场景五：

GROUP BY ... HAVING ...

#Having语句筛选班级平均分大于79分的班级
SELECT 
  t.class,
  COUNT(*) AS zrs,
  AVG(t.Sscore) AS pjcj,
  MAX(t.Sscore) AS zgf,
  MIN(t.Sscore) AS zdf 
FROM
  Student t 
GROUP BY t.class  HAVING pjcj>79

两个班级为了看出区别我们来根据平均分79筛选一下一班和二班

结果：

SQL(结构化查询语言)

场景六：

LIKE

1).

#like进行模糊匹配
SELECT t.Sname FROM Student t WHERE t.Sname LIKE '%赵%'

结果：

SQL(结构化查询语言)

可以看出'%%'这种写法匹配的是只要含有这个字的数据全部显示出来，并不关心匹配内容的位置在哪。

2).

SELECT t.Sname FROM Student t WHERE t.Sname LIKE '赵%'

结果：

SQL(结构化查询语言)

可以看出，这种'X%' 找出的是以什么什么开头的数据，找出的就是姓赵的数据

3).

SELECT t.Sname FROM Student t WHERE t.Sname LIKE '陈_'

结果:

SQL(结构化查询语言)

这条sql描述的就是以陈开头，并且名字是两个字的数据。那么举一反三，再加一个_那么就展示名字为三个字的并且以陈开头的数据。

like总结：like进行模糊匹配时，%可以匹配任意多个任意字符，_匹配任意一个字符。

多表：

【表连接】

表连接是指两张表可以通过相同的关键字段进行连接，包括内连接和外连接，而外连接又包括左外连接、右外连接和全外连接。

1）内连接指结果表只包含同时存在两张表中的连接字段；

2）左外连接是指既包含内连接的连接字段还包含左表未连接的字段；

3）右外连接是指既包含内连接的连接字段还包含右表未连接的字段；

4）全外连接是包含两张表的所有连接字段。

场景一：

内连接：

#内连接,*是通配符,取出表所有的变量
SELECT t.*,t1.* FROM student t INNER JOIN stu_grade t1 ON t.sid = t1.sid

结果：

SQL(结构化查询语言)

场景二：

左外连接：

#左外连接
SELECT t.*,t1.* FROM student t LEFT JOIN stu_grade t1 ON t.sid = t1.sid

结果：

SQL(结构化查询语言)

场景三：

右外连接：

#右外连接
SELECT * FROM student t RIGHT JOIN stu_grade t1 ON t.sid = t1.sid

结果：

SQL(结构化查询语言)

场景四：

全外连接，MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

#全外连接
SELECT t.*,t1.* FROM student t FULL OUTER JOIN stu_grade t1 ON t.sid = t1.sid

更多其他的SQL语法

【子查询】

子查询是指把一个查询的结果放到另一个查询里面使用，功能十分丰富，既可以放在FROM语句中作为临时表供另一个查询使用，也可以放在WHERE子句后面进行过滤。放在WHERE子句后面的子查询的返回结果可以是单行也可以是多行，单行子查询一般与>,<,=,<=,>=等比较符一起使用，而多行子查询可与IN（等于子查询返回结果的任意一个），any（比较符满足子查询中的任意一个情况）, all（比较符满足子查询中的所有情况）等配套使用，下面举例说明。

场景一：

单行子查询：

#student表中查询score大于平均分（73.33）的学生id，分数
#WHERE单行子查询
SELECT t.sid,t.Sscore FROM student t WHERE t.Sscore > (SELECT AVG(Sscore) FROM student)

结果：

SQL(结构化查询语言)

场景二：

多行子查询：

#student表中查询属于班级平均分最高班级的学生信息
SELECT 
  * 
FROM
  student t1 
WHERE t1.class IN 
  (SELECT 
    s.class 
  FROM
    (SELECT 
      AVG(t.Sscore) score,
      t.class 
    FROM
      student t 
    GROUP BY t.class 
    ORDER BY score DESC 
    LIMIT 1) s)
SELECT AVG(t.Sscore),t.class FROM student t GROUP BY t.class

结果：

SQL(结构化查询语言)

上面这个嵌套子查询稍微有一点点复杂，我一共用了两层子查询，首选最里层的子查询计算了每个班级的平均分。然后用ORDER BY 子句按班级平均分倒序排列（最高分排第一个），然后LIMIT 1限制输出1行数据，得到了最高分的班级行，接着把这层子查询结果放到外面一层子查询的FROM语句后面，第二层子查询得到class，最后外查询的WHERE 子句判断class是不是等于第二层子查询中的某一个。

场景三：

All的使用

#student表中最高分
SELECT t.Sid,t.Sscore FROM student t WHERE t.Sscore >= ALL (SELECT DISTINCT t1.Sscore FROM student t1)

结果：

SQL(结构化查询语言)

场景四：

any的使用

#student表中非最低分的学生
SELECT t.Sid,t.Sname,t.Sscore FROM student t WHERE t.Sscore > ANY (SELECT DISTINCT t1.Sscore FROM student t1)

结果：

SQL(结构化查询语言)

只有最低分的学生成绩不大于任何一个人的分数，被排除在外。

【case when】

case when 语句是SQL中的一个非常重要的功能，可以完成很多复杂的计算，相当于一个表达式，可以放在任何可放表达式的地方。语法 case when 条件 then 结果 when 条件 then 结果 else end。else可不加，是缺省条件下的值，如果不加，有缺省情况则为NULL。CASE WHEN还可以和GROUP BY 语句搭配使用，用在sum,count,max等聚合函数内部。

场景一：

#student表中生成一个新字段成绩等级，小于60分不及格，大于等于60并小于80及格，大于等于80并小于90良好，大于等于90优秀
SELECT 
  a.*,
  (
    CASE
      WHEN Sscore < 60 
      THEN '不及格' 
      WHEN Sscore >= 60 
      AND Sscore < 80 
      THEN '及格' 
      WHEN Sscore >= 80 
      AND Sscore < 90 
      THEN '良好' 
      WHEN Sscore >= 90 
      THEN '优秀' 
    END
  ) AS grade_level 
FROM
  student a

结果：

SQL(结构化查询语言)

场景二：

#计算每个班级中，及格和不及格人数
SELECT class
       ,COUNT(DISTINCT CASE WHEN Sscore < 60 THEN Sid END) AS failedNum
       ,COUNT(DISTINCT CASE WHEN Sscore >= 60 THEN Sid END) AS passNum
FROM student
GROUP BY class

结果：

SQL(结构化查询语言)

【组合查询】

SQL中可用union或union all将多个查询结果拼接起来，两者的区别是union会对结果重新排序，而union all不会，所以一般为了节省内存在不需要排序的情况下用union all更好。

场景一：

#组合查询
SELECT a.*
FROM student AS a
WHERE class = 1 AND Sscore >= 60
UNION ALL 
SELECT a.*
FROM student AS a
WHERE class = 2 AND Sscore >= 60

结果：

SQL(结构化查询语言)

【常用函数】

字符串：

--字符串函数
substring(string,start,length)  --截取字符串string,从start开始的length个字符,类似excel的mid
left(string,length)  --截取字符串string,从最左边开始的length个字符,类似excel的left
right(string,length)  --截取字符串string,从最右边开始的length个字符,类似excel的right
instr (string1 , string2 [,start_location ]) --MYSQL中查找string1在string2中出现的位置
insert(string1,start，length,string2)  --MYSQL中删除指定位置的的指定个数字符，并在指定位置处插入新字符

举例：

SELECT 
SUBSTRING('中国北京市海淀区',3,3)  AS city
,LEFT('中国北京市海淀区',2)  AS country
,RIGHT('中国北京市海淀区',3)  AS local_area
,INSTR ('中国北京市海淀区' , '北京市')  AS index_city
,INSERT('中国北京市海淀区',3,6,'天津市')  AS replace_city

结果：

SQL(结构化查询语言)

日期函数：

--日期函数,不同数据库会有一些区别，此处以mysql为例
current_date()     --当前日期
date_add(date,interval expr unit)   --date加减expr的unit(年月日周)的date
datediff(expr1,expr2)   --expr1减去expr2的天数
timediff(expr1,expr2)   --expr1减去expr2的的时间
date_format(date,format)  --格式化日期
str_to_date(str,format)  --将字符转换成日期

举例：

SELECT 
CURRENT_DATE() AS NOW, 
DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS yesterday, 
DATEDIFF(CURRENT_DATE(),DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) AS date_cha,
DATE_FORMAT(CURRENT_DATE(),'%Y/%m') AS ym,
STR_TO_DATE('2020-12-04','%Y-%m-%d') AS strdate1,
STR_TO_DATE('2020.12.04','%Y.%m.%d') AS strdate2

结果：

SQL(结构化查询语言)

其他一些常用的函数：

--其他一些常用函数
round(columns, decimals)  --对某个数据列进行指定小数位四舍五入
len(str)                  --返回某个字段长短
lowner(str)               --将字符全部小写
upper(str)                --将字符全部大写
first()                   --返回第一行记录
last()                    --返回最后一个记录的值 
cast(expr as stype)       --类型转化,比如cast(str as bigint)
from_unixtime(timestamp)  --将时间戳转换为时间

【WITH 子句】

with子句是一种临时表，这个临时表简直太强大了，全靠它才能轻轻轻松松写上几百上千行SQL代码，如果没有它，面对日常取数需求几十个字段而且来自不同表格不同维度的嵌套子查询简直会疯。语句格式为： with table_1 as (select……)， table_2 as (select……)，table_3 as (select……)，…… table_n as (select……)，后面子表还可以从前面子表中查找数据，比如table2中的FROM 可以接table_1

with字句举例：

--用with子句得到成年且成绩及格的学生名单
with adult as (
SELECT Sid
FROM student
WHERE age >=18
),
pass_stu as (
SELECT Sid
FROM student
WHERE Sscore>=60
)
SELECT a.Sid, age, Sscore
FROM student AS a
JOIN adult AS b on (a.Sid= b.Sid)
JOIN pass_stu AS c on (a.Sid= c.Sid)

本文地址：https://blog.csdn.net/JavaJieRui/article/details/110382531

相关标签：数据库sql sql 数据库 mysql

上一篇： Matlab解释器模式

下一篇：设计模式-装饰器模式

SQL(结构化查询语言)

单表：

【基础用法】

场景一：

单查

场景二：

where判断

场景三：

计算，COUNT,AVG,MAX,MIN

场景四：

GROUP BY

场景五：

GROUP BY ... HAVING ...

场景六：

LIKE

多表：

【表连接】

场景一：

内连接：

场景二：

左外连接：

场景三：

右外连接：

场景四：

全外连接，MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

更多 其他的SQL语法

【子查询】

场景一：

单行子查询：

场景二：

多行子查询：

场景三：

All的使用

场景四：

any的使用

【case when】

场景一：

场景二：

【组合查询】

场景一：

【常用函数】

字符串：

日期函数：

其他一些常用的函数：

【WITH 子句】

JDO 2.0查询语言的特点

常用SQL语句(嵌套子查询/随机等等)详细整理

实现按关健字模糊查询,并按匹配度排序的SQL语句

SQL查询语句优化的实用方法总结

SQL 分布式查询、插入递增列示例

在sql查询中使用表变量

SQL 复合查询条件(AND,OR,NOT)对NULL值的处理方法

SQL Server高级内容之子查询和表链接概述及使用

Sql学习第三天——SQL 关于CTE(公用表达式)的递归查询使用

sql脚本查询数据库表,数据,结构,约束等操作的方法

更多其他的SQL语法