您现在的位置是：首页 >技术教程 >SQL(--修改中--)网站首页技术教程

SQL(--修改中--)

戏拈秃笔 2024-10-21 00:01:03
简介SQL(--修改中--)

目录

一、基本介绍 

二、常用函数 

集合函数

 字符串函数

MYSQL的日期和时间函数

SQL重要的内建日期函数 

MYSQL重要的内建日期函数 

条件判断函数

三、操作

单表查询 

多表查询 

使用正则表达式查询

添加： 

修改： 

删除： 

四、MYSQL存储函数/自定义函数

五、视图 

六、事务 

 

一、基本介绍 

  • sql是一种语言。
  • mysql是一种关系型数据库管理系统RDBMS(Relational Database Management System）
  • MySQL 使用标准的 SQL 数据语言形式

代码规范：

  • SQL语句不区分大小写，但建议关键字大写、数据列和表名小写 

基础概念：

  • 关系型数据库，是指建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
  • 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

语法： 

1、注释符号：

  • -- 两个横杠加一个空格，注释单行
  • /*注释多行*/
条件判断符说明
=相等
<>, !=不相等
<小于
<=小于或等于
>大于
>=大于或等于
BETWEEN位于两值之间

二、常用函数 

常用函数：

  • 数学函数
  • 集合函数
  • 字符串函数
  • 日期和时间函数
  • 条件判断函数
  • 系统信息函数
  • 加密函数
  • 其他函数

集合函数

集合函数解释
AVG()返回某列的平均值 
COUNT(*)返回表中的总行数
COUNT(字段名)返回指定列的总行数
MAX()

返回某列的最大值

（数值、字符、字符串）

MIN()返回某列的最小值
SUN()返回某列值的和
  • 集合函数无法嵌套，括号里不能是语句
  • 集合函数可以把空值转成 null 输出
  • HAVING后面可以接集合函数，WHERE后面不可以
  • AVG（条件）相当于SUM（IF（条件，1，0））/COUNT(全体)

 字符串函数

字符串函数解释
CHAR_LENGTH(str)返回字符串的长度
CONCAT(s1,s2...)

拼接字符串

（有任一参数为NULL，返回NULL)

CONCAT(x,s1,s2...)

把分隔符x放在要拼接的字符串之间

（有分隔符时，可忽视参数中出现的NULL)

ELT(n,s1,s2,s3...)

返回第n个字符串

若无符合，则返回NULL

FIELD(s,s1,s2...sn)

返回s在列表s1,s2...sn中第一次出现的位置

（s1的位置为1）

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置
INSERT(s1,x,len,s2)

截取len长度的s2的子串替换s1下标从x开始的部分，

最终返回替换后的s1

LOWER(str)或LCASE(str)把字符串里的字符全部转成小写
LENGTH(str)返回字符串包含的字符个数
LEFT(s,n)返回字符串s最左边的n个字符
LPAD(s1,len,s2)

填充字符串。

要返回的s1长度必须是len,

不够长就用s2从s1左边开始补，

超长就截断s1右边多余的部分

LTRIM(s)删除字符串s左边的空格字符后返回s

LOCATE(s1,s2)

或POSITION(s1 IN s2)

或INSTR(s2,s1)

返回s1在s2中开始的位置
MAKE_SET(x,s1,s2...sn)把x换成二进制数，返回列表s1,s2...sn中与二进制数中1出现的位置相对应的字符串
RIGHT(s,n)返回字符串s最右边的n个字符
RPAD(s1,len,s2)

填充字符串。

要返回的s1长度必须是len,

不够长就用s2从s1右边开始补，

超长就截断s1右边多余的部分

RTRIM(s)删除字符串s右边的空格字符后返回s
REPEAT(s,n)返回一个由字符串s重复n次组成的字符串
REPLACE(s,s1,s2)用s2替代s中所有的s1
REVERSE(s)返回反转后的s
SPACE(n)返回一个由n个空格组成的字符串
STRCMP(s1,s2)

比较s1和s2的大小

相同：返回0

小于：返回-1

其他：返回1

SUBSTRING(s,n,len)    

或MID(s,n,len)

截取返回s中下标从n开始的len长度的子串（从1开始计算）

（n>0时，从左边开始；n<0，右边）

SUBSTRING_INDEX(s,delim,count)count为正数，代表从s左边取标识符delim出现第count次之前的子串；负数则从右边。
TRIM(s)删除字符串s左右两端的空格字符后返回s
TRIM(s1 FROM s)删除字符串s左右两端所有的子串s1
UPPER(str)或UCASE(str)把字符串里的字符全部转成大写

MYSQL的日期和时间函数

日期和时间函数解释
CURDATE()或CURRENT_DATE()

将当前日期按照YYYY-MM-DD

或YYYYMMDD格式的值返回

CURDATE()+0

将当前日期转换成数值型YYYYMMDD

CURTIME()或CURRENT_TIME()

将当前时间以HH:MM:SS

或HHMMSS的格式返回

CURTIME()+0将当前日期转换成数值型HHMMSS

CURRENT_TIMESTAMP()

或LOCALTIME()

或NOW()

或SYSDATE()

返回当前日期和时间值

格式：YYYY-MM-DD HH:MM:SS

或YYYYMMDDHHMMSS

UNIX_TIMESTAMP(NOW())

或UNIX_TIMESTAMP()

返回一个UNIX时间戳

1970-01-01 00:00:00 GMT之后的秒数

UNIX_TIMESTAMP(date)返回形式为date类型
FROM_UNIXTIME(date)

把date（秒数）转换成普通格式时间

YYYY-MM-DD HH:MM:SS

UTC_DATE()

返回当前UTC(世界标准时间)日期值

格式：YYYY-MM-DD或YYYYMMDD

UTC_DATE()+0将当前日期转换成数值型YYYYMMDD
UTC_TIME()

返回当前UTC(世界标准时间)时间值

格式：HH:MM:DD

UTC_TIME()+0将当前时间转换成数值型HHMMDD

MONTH(date)

返回date对应的月份
MONTHNAME(date)返回date对应月份的英文全名
DAY(date)返回date对应的工作日
DAYNAME(d)返回d对应工作日的英文全名
DAYOFWEEK(d)

返回d对应的星期

2表示周一

WEEKDAY(d)

返回d对应的星期

0表示周一

WEEK(d)

计算d是一年中的第几周

第二个参数默认为0

（表示指定一周的第一天是周日）

DAYOFYEAR(d)返回日期d是一年中的第几天（1~366）
DAYMONTH(d)返回日期在一个月中的位置（1~31）
YEAR(date)返回日期date对应的年份（1970~2069）
QUARTER(date)

返回日期date对应的一年中

的季度值 （1~4）

MINUTE(time)返回time对应的分钟数（0~59）
SECOND(time)返回指定时间的秒值（0~59）
EXTRACT(type FROM date)

从date中提取type类型的日期部分

如从2018-07-02中提取年份2018

TIME_TO_SEC(time)返回已转化为秒的time参数
SEC_TO_TIME(seconds)

把秒数seconds转化成时间格式

HH:MM:SS

DATE_FORMAT(date,format)根据format指定的格式显示date值
TIME_FORMAT(time,format)根据format指定的格式显示time值
GET_FORMAT(val_type,format_type)返回日期时间字符串的显示格式

SQL重要的内建日期函数 

日期函数解释
GETDATE()返回当前日期和时间
DATEPART()返回日期/时间的单独部分
DATEADD()在日期中添加或减去指定的时间间隔
DATEDIFF()返回两个日期之间的时间
CONVERT()用不同的格式显示日期/时间

MYSQL重要的内建日期函数 

函数解释
NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间按的单独部分
DATE_ADD()给日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

条件判断函数

条件判断函数解释
IF(expr,v1,v2)

如果表达式expr为TRUE

则返回值v1，否则返回值为v2

IFNULL(v1,v2)

如果v1不为NULL，则返回v1

否则返回v2

CASE

        expr

        WHEN v1 THEN r1

        ...

        [ELSE rn+1]

END

如果expr值等于某个vn，则返回

对应位置THEN后面的结果，

否则返回ELSE后面的rn+1

三、操作

单表查询 

  • SELECT * FROM 表名；——查询所有字段
  • SELECT  列名 FROM 表名；——查询指定字段
  • SELECT 列名1，列名2，…… FROM 表名；——查询指定的多个字段 
  • SELECT 列名1，列名2，……

FROM 表名

WHERE 查询条件； 
-- 查询指定记录

    • ……

      WHERE 字段值 IS NOT NULL；——字段值不为空

    • ……

      WHERE 字段值 IS NULL；——字段值为空

    • ……

      WHERE 条件1 AND 条件2；——查询同时满足条件1和条件2的结果 

    • ……

      WHERE 条件1 OR 条件2；——查询满足条件1或条件2的结果（AND先于OR)

  • SELECT id,name

FROM fruits

WHERE id IN (1,2)；
-- 查询id等于1或2的水果……（NOT IN：不满足条件）

  • SELECT id,name

FROM fruits

WHERE id BETWEEN 1 AND 4；
-- 查询id在范围 [1，4] 内的水果id和name

  • SELECT id,name

FROM fruits

WHERE name LIKE 'b%'；
-- 查询name以b开头的水果

    • %b：以b结尾

    • %b%：包含字母b

    • a%b：以a开头，以b结尾

    • _ _ _ _b：以b结尾，且前面只有4个字符（‘_’间没有空格）

  • SELECT DISTINCT 字段名 FROM 表名；——去重查询 

  • SELECT id

FROM fruits

ORDER BY id；
-- 查询id，并排序（默认升序）

    • 多列进行排序，用逗号隔开，优先级从左往右降低

    • ORDER BY id DESC；——降序（DESC只对一列有效，按需在列名后添加）

  • ……

GROUP BY 字段 

HAVING 条件表达式；
-- 分组查询（G和H经常同用）

    • GROUP BY 可根据多字段的值进行分组，从左往右，层次递进，不断细分

    • 优先级：from>on>where>group by>having>order by>limit

      FROM:先知道数据从哪张表取

      WHERE:取哪些数据

      GROUP BY:对数据进行分组

      HAVING:对分组后的数据进行过滤

      ORDER BY:对过滤后的数据进行排序

      LIMIT：取结果的哪几行数据

  • GROUP_CONCAT(
    [DISTINCT] 要连接的字段 
    [Order BY ASC/DESC 排序字段] 
    [Separator '分隔符']
)
-- 把查询到的多个数据拼接在一起输出，用指定分隔符输出（默认为逗号，默认为升序）

  • SELECT id  COUNT(*) Total
FROM fruits
GROUP BY id WITH ROLLUP；
-- 添加一列。在每行后面添加该组的总数

  • LIMIT [位置偏移量]，行数

    • LIMIT 4,3;——从第5个记录开始，返回3行记录

    • LIMIT 3;——默认从第1个记录开始，返回3行记录

多表查询 

1、内连接查询：

        使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。

        在内连接查询中，只有满足条件的记录才能出现在结果关系中。

  • 自连接: SELECT FROM WHERE 或 ANSI
    • 指表与其自身进行连接
    • -- 法一：使用关键字"SELECT FROM WHERE"
SELECT s.name
FROM s,t
WHERE s.classno=t.classno AND t.name="t1";

-- 法二：采用ANSI连接语法形式
SELECT s.name
FROM s INNER JOIN t
ON s.classno=t.classno AND t.name="t1";
    • 使用INNER JOIN语法能够确保不会忘记连接条件，而WHERE子句在某些时刻会影响查询的性能
  • 等值连接: =
    • 在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件
    • -- 法一：使用关键字"SELECT FROM WHERE"
SELECT s.name,t.name
FROM  s,t
WHERE s.classno=t.classno;

-- 法二：采用ANSI连接语法形式
SELECT s.name,t.cname
FROM s INNER JOIN t ON s.classno=t.classno;
  • 不等连接: >, >=, <, <=, != 等
    • 指在关键字ON后的匹配条件中除了等于关系运算符来实现不等条件外，还可以使用包含>, >=, <, <=, != 等关系运算符。
    • -- 法一：使用关键字"SELECT FROM WHERE"
SELECT s.name,t.name
FROM  s,t
WHERE s.classno!=t.classno AND s.id>t.id;

-- 法二：采用ANSI连接语法形式
SELECT s.name,t.name
FROM s INNER JOIN t ON s.classno!=t.classno AND s.id>t.id;

2、外连接查询：

        外连接查询会返回所操作的表中至少一个表的所有数据记录。

        与内连接相比，多了某些不满足比较条件的数据

  • 左外连接:LEFT JOIN 
    • 返回包括左表中的所有记录和右表中连接字段相等的记录
    • 左连接全名又称为左外连接， left join = left outer join
    • SELECT field
    FROM tablename1 LEFT [OUTER] JOIN tablename2
        ON CONDITION
  • 右外连接:RIGHT JOIN
    • 返回包括右表中的所有记录和左表中连接字段相等的记录
    • SELECT 字段名
    FROM tablename1 RIGHT [OUTER] JOIN tablename2
        ON CONDITION
  • 全外连接:FULL JOIN 
    • SELECT 字段名
    FROM tablename1 FULL [OUTER] JOIN tablename2
        ON CONDITION

3、复合条件连接查询:INNER JOIN

        通过添加过滤条件限制查询的结果，使查询的结果更加准确

  • SELECT s.name
    FROM s INNER JOIN t
        ON s.id=t.id AND s.id=1001;
         

4、合并查询数据记录

  • 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。
  • 合并时，两个表对应的列数和数据类型必须相同。
  • 各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
  • UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的。
  • 使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。(尽量不用ALL)
  • SELECT column,...
    FROM table1
UNION [ALL]
SELECT column,...
    FROM table2

5、子查询

        指一个查询语句嵌套在另一个查询语句内部的查询。

  • ALL
SELECT num1 
    FROM s
        WHERE s>ALL
            (
                SELECT num2 FROM t;
            )
-- 返回s表中比t表的num2列所有值都大的值
  • EXISTS
    • NOT EXISTS与EXISTS使用方法相同，返回结果相反
    • SELECT *
    FROM s
        WHERE nums>4 AND EXISTS(SELECT name FROM t WHERE id=9);
-- t表中是否存在id=9的数据，如果存在，就查询s表中的nums>4的记录

  • IN

    • NOT IN 与IN使用方法相同，返回结果相反

    • SELECT c_id 
    FROM orders 
        WHERE o_num IN(SELECT o_num FROM orderitems WHERE f_id='c0');
-- 在orderitems表中，查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id
  • 比较运算符
    • >, >=, <, <=, !=等比较运算符

使用正则表达式查询

        语法：

                SELECT 列名 FROM 表名 WHERE 字段值 REGEXP '正则表达式' ；

添加： 

-- 基本语法结构 
UPDATE table_name
SET column_name1 = value1,column_name2 = value2,...,column_namen = valuen
WHERE (condition);

修改： 

删除： 

-- 基本语法结构 
DELETE FROM table_name
[WHERE <condition>];
TRUNCATE TABLE table_name;-- 删除整个表

四、MYSQL存储函数/自定义函数

  •  存储过程和函数是事先经过编译并存储在数据库中的一SQL语句的集合

  • 存储过程和函数的区别：

    • 函数必须有返回值
    • 存储过程没有返回值

创建存储过程的基本语法结构：

 一：基本形式

CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END;

二：修改结束符

DELIMITER$ -- 将MYSQL的结束符设置为$，默认是;
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$ -- 结束存储过程
DELIMITER ;-- 恢复默认结束符

CALL 存储过程名称(实际参数); -- 调用存储过程

创建存储函数

CREATE FUNCTION 存储函数名称([[IN|OUT|INOUT]存储函数参数列表]) -- IN:输入参数 OUT:输出参数 INOUT:既是输入也可是输出参数。默认为IN型
RETURNS type -- 函数返回数据的类型
RETURN value -- value是sql语句

DELIMITER $
CREATE FUNCTION 存储函数名称([[IN|OUT|INOUT]存储函数参数列表]) -- IN:输入参数 OUT:输出参数 INOUT:既是输入也可是输出参数。默认为IN型
RETURNS type -- 函数返回数据的类型

BEGIN
[DECLARE 变量名称 变量类型 [DEFAULT 变量值];] 
[SET 变量名称 = 变量值;]
RETURN value -- value是sql语句
END
DELIMITER ;

变量

-- 定义变量
DECLARE 变量名称 变量类型 [DEFAULT 变量值] -- 如果没有DEFAULT语句，默认值为null

--变量赋值
SET 变量名称 = 变量值;

-- 多个变量赋值
SELECT 字段名称[,...] INTO 变量名称[,...] 查询条件表达式

五、视图 

视图： 

六、事务 

事务：

风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。

站长推荐