数据库——SQL

最新推荐文章于 2022-11-08 09:02:46 发布

「已注销」

最新推荐文章于 2022-11-08 09:02:46 发布

阅读量166

点赞数

分类专栏：数据库基础

原文链接：https://www.runoob.com/mysql/mysql-group-by-statement.html

版权

数据库基础专栏收录该内容

7 篇文章 0 订阅

订阅专栏

常用的数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途

INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

时间，日期类型

类型	大小 (字节)	范围	格式	用途

DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串

CHAR ：它是定长格式的，但是长度范围是 0~255. 当你想要储存一个长度不足 255 的字符时，Mysql 会用空格来填充剩下的字符。因此在读取数据时，char 类型的数据要进行处理，把后面的空格去除。
VARCHAR：varchar 类型在 5.0.3 以下的版本中的最大长度限制为 255，而在 5.0.3 及以上的版本中，varchar 数据类型的长度支持到了 65535，也就是说可以存放 65532 个字节的数据（起始位和结束位占去了3个字节）
因此：经常变化的字段用 varchar；知道固定长度的用 char

创建与修改数据库的注意事项

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。若想将其值重置，则可用： truncate table + (表名）
PRIMARY KEY关键字用于定义列为主键。可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

常用语句

添加表中的值：insert into tablename values();
添加表的列：alter table tablename add col char(20);
删除表：drop table tablename where id = 3;
删除表中的列：alter table tablename drop column col ;
更新表：update tablename set field1 = newvalue1, where id = 2;
将一表内容插入到创建的新表：create table newtable as select * from oldtable;
向表中添加新项：alter table tablename add col char(20);
查找表中col1,col2项的所有值（相同值只出现一次）：select distinct col1, col2 from tablename;
limit限制返回行数，可有两参数（起始行和返回的总行数）：select * from tablename limit 2, 3;
在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。 select col1 * col2 as alias from tablename;
concat() 用于连接两个字段，许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 trim() 可以去除首尾空格。 select concat( trim(col1), '(', trim(col2), ')' ) as val from tablename;

使用更新和删除时要注意是否要使用where，否则会将表中所有数据破坏。

LIKE字句

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';

常见匹配：

%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

UNION操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。而 UNION ALL 可选取重复的值

示例：

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

ORDER BY子句

SELECT * from runoob_tbl ORDER BY submission_date ASC;

ASC为升序，DESC为降序。默认为升序
可以设置多个字段作为排序依据

GROUP BY子句

该子句把具有相同数据值的行放在同一组。我们可以对同一分组数据使用计算总数进行处理，例如求每一分组的行数。指定的分组字段会自动按该字段进行排序。

select age, COUNT(*) as num from stu group by age;  --按年龄划分为同一组，计算每组的行数

同样，我们可以用 where 过滤行， having 过滤分组。行过滤应写在分组过滤前面。

select age , COUNT(*) as num from stu where age <= 55 group by age having num >= 2;
   -- 年龄小于等于55且出现次数大于一次

可以用 order by 将分组排序。order by写在 group by 子句之后

select age, COUNT(*) as num from stu  group by age order by num;

常用函数

计算

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 会忽略 NULL 行。
使用 DISTINCT 可以汇总不同的值

select AVG(distinct col1) AS avg_col from tablename;

文本处理

LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

子查询

子查询可以返回一个字段的数据。可以将子查询的结果作为where 语句的过滤条件。

select *
from tablename
where  col1 in(select col2
               from tablename2);

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

-- 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

连接

连接用于连接多个表，使用 JOIN关键字且条件语句用ON而不是where
连接可以替换子查询且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

现在我们筛选出表A和表B之间key列相等的各个value值。

普通查询

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

内连接

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

自连接

自连接是内连接的的一种，不过连接的表是自身。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行，完全连接就是保留两边没有关联的便。
a表 b表

id name id age parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4

（1）左连接： select a.*,b.* from a left join b on a.id=b.parent_id

1 张3 1 23 1
2 李四 2 34 2
3 王武 null

（2）右连接：select a.*,b.* from a right join b on a.id=b.parent_id

1 张3 1 23 1
2 李四 2 34 2
null 3 34 4

（3）完全连接：select a.*,b.* from a full join b on a.id=b.parent_id

1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null

变量

定义方式

mysql中，定义变量有两种方式：

使用set 或 select 直接赋值，变量名以@ 开头。

set @val = 1;

select y*y into ret;

declare 关键字声明。只能在存储过程中使用。只要用在存储过程中或给存储传参数

DECLARE val1 INT DEFAULT 0;

两者的区别是：

在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。

变量类型

局部变量

局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。

例如在存储过程中：

drop procedure if exists add;

create procedure add(in a int, in b int)
begin

    declare c int default 0;

    set c = a + b;

    select c as c;

end;

用户变量

用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。

对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":="方式，因为在select语句中，"="号被看作是比较操作符。

示例：

drop procedure if exists math;

create procedure math

(

    in a int,

    in b int

)

begin

    set @var1 = 1;

    set @var2 = 2;

    select @sum:=(a + b) as sum, @dif:=(a - b) as dif;

end;

会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

设置会话变量有如下三种方式：

set session var_name = value;

set @@session.var_name = value;

set var_name = value;

查看一个会话变量也有如下三种方式：

select @@var_name;

select @@session.var_name;

show session variables like "%var%";

全局变量

全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有SUPER权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件。

要设置一个全局变量，有如下两种方式：

set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION

set @@global.var_name = value; //同上

要想查看一个全局变量，有如下两种方式：

select @@global.var_name;

show global variables like "%var%";

存储过程

存储过程就是一条或者多条为完成特定功能SQL语句的集合，可以理解为程序里的函数。

存储过程的好处：

代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能。

创建

创建存储过程的语句为 CREATE PROCEDURE

DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id'  -- 提示信息，可省略
SQL SECURITY DEFINER  -- DEFINER指明只有定义此SQL的人才能执行，MySQL默认也是这个，可省略
BEGIN
   SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 将结果存入 stuName，stuAge
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号

声明语句结束符，可以自定义：DELIMITER $$ 或 DELIMITER //
存储过程开始和结束符号：BEGIN .... END
默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。
IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数。

调用

call getStuById(1, @name, @age);

SELECT @name AS stuName,@age AS stuAge;

这样，我们可以通过存储过程封装我们写过的SQL，在下次调用时，直接提供参数并查询结果输出到哪些变量即可。

需要注意的是，存储过程一次只能查询一个记录。

删除

DROP PROCEDURE IF EXISTS study.getStuById;

show procedure status like 'g%' -- 查看有哪些以g开头的存储过程

触发器

概述

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行

可在写入数据前，强制检验或者转换数据(保证护数据安全)
触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end
自定义的结束符合

delimiter ;

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态
after：表中数据发生改变后的状态

如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

触发器是针对数据发送改变才会被触发，对应的操作只有

INSERT
DELETE
UPDATE

每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

语法：old/new.字段名

需要注意的是，old 和 new 不是所有触发器都有

INSERT型触发器没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

此外，MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错。

如下所示，创建一个更新orders字段的触发器，然后用update语句对orders表进行更新。会提示不能进行更新

delimiter //
create trigger up before update on orders for each row
begin
    update orders set goods_id = 10 where id = new.id;
end;
//
delimiter ;

示例

（1）首先创建两个表：商品表和订单表：

CREATE TABLE `pays`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) NULL DEFAULT NULL,
  `goods_num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


CREATE TABLE `goods`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `goods_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

（2）若订单表有数据插入，对应的商品表的库存应减少。因此对订单创建触发器

delimiter //
create trigger after_insert_order after insert on pays for each row
begin 
   update goods set goods_num = goods_num - new.goods_num where id = new.goods_id;
end
//
delimiter;

（3）倘若在插入订单数据前，该商品的库存不够，则同样我们也需要创建一个触发器，执行中断操作

delimiter //
create trigger before_insert_order before insert on pays for each row
begin 
  select goods_num from goods where id = new.goods_id into @tmp;
	
	if @tmp < new.goods_num then 
	    insert into xxx values(xxx);
	end if;
end //
delimiter;

该文转载自：

「已注销」

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库——SQL

1.什么是数据库数据库是按照数据结构来组织，存储和管理数据的仓库，我们用关系型数据库（RDBMS）来存储和管理大数据量。关系数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。数据库：一些关联表的集合；数据表：表是数据的矩阵，一个数据库中的表像是一个表格。冗余：存储两倍的数据，降低了性...
复制链接

扫一扫

专栏目录