MySQL基础_第2节_完整性约束 & 数据的增删改查

最新推荐文章于 2022-07-25 17:13:05 发布
最新推荐文章于 2022-07-25 17:13:05 发布
阅读量335 收藏 1
点赞数
分类专栏： MySQL基础 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52137329/article/details/111350978
版权

MySQL基础_第2节_完整性约束 & 数据的增删改查

数据表相关操作

如何创建数据表？

  • CREATE TABLE [IF NOT EXISTS] tbl_name(
    字段名称 字段类型 [完整性约束条件]
    …
    )ENGINE=引擎名称 CHARSET=‘编码方式’;
CREATE DATABASE IF NOT EXISTS maizi DEFAULT CHARACTER SET UTF8;	# 名称maizi可以用反引号括起来，用以与特殊命令区分

USE maizi;

-- 创建学员表 user
-- 编号 id
-- 用户名 username
-- 年龄 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married

CREATE TABLE IF NOT EXISTS user(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男', '女', '保密'),	# 注意：当需要输入中文的时候，需要临时转换客户端的编码方式
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0表示未婚，非0表示未婚'
)ENGINE=INNODB CHARSET=UTF8;

# 检查是否创建成功
SHOW TABLES

完整性约束条件，用来限制字段

  • PRIMARY KEY \ KEY 主键：

    • 数据的唯一标识符（人的身份证号，绝对不会重复且一一对应）

    • 一般会加到一个无意义的字段上，例如编号字段。不能重复，不能为空。

    • 单字段主键

      -- 测试单字段主键
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY, COMMENT '也可以只输入KEY，不能重复，不为空'
username VARCHAR(20)
);

INSERT user1 VALUES(1, 'king');
INSERT user1 VALUES(13, 'queen');

SELECT * FROM user1 WHERE id=1 COMMENT '方便查询，唯一'

      在这里插入图片描述

    • 多字段主键：只有多部份主键都重复，才算两条重复，报错并无法录入。

      -- 测试多字段主键
CREATE TABLE IF NOT EXISTS user2(
id INT,
username VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id, card) COMMENT '也可以只输入KEY，用两个字段来确定唯一一条记录'
);

INSERT user2 VALUES(1, 'king', '111');
INSERT user2 VALUES(1, 'queen', '112'); COMMENT '这条记录与上面记录不同，不会报错'
INSERT user2 VALUES(1, 'queen', '112'); COMMENT '会报错'

  • AUTO_INCREMENT自增长

    • 默认从1开始，每次增加1。
    • 每个表中只有一个自增长，一定是主键，只对整数有效。
    • 默认或者NULL值，都会自增长1
      -- 测试AUTO_INCREMENT
CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT, COMMENT '自增长需要配合主键使用，但是他们的顺序可以颠倒'
username VARCHAR(20)
);
INSERT user5 VALUES(1, 'KING');
INSERT user5(username) VALUES('queen'); COMMENT '只插入username id自动分配 +1'
INSERT user5 VALUES(111, 'KING1');
INSERT user5(username) VALUES('queen1'); COMMENT 'id未指定值，自动加1为112'
INSERT user5 VALUES(NULL, 'AAAA'); COMMENT 'id指定NULL值，自动加1为113'
INSERT user5 VALUES(DEFAULT, 'AAAA'); COMMENT 'id指定DEFAULT，自动加1为113'
      在这里插入图片描述
      -- 测试AUTO_INCREMENT，修改默认增长值为100
CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
) AUTO_INCREMENT=100;

-- 修改自增长的值为500
ALTER TABLE user6 AUTO_INCRETMENT=500;

-- 查询自增长的值
SHOW CREATE TABLE user6;

  • FOREIGN KEY外键：先不讲

  • NOT NULL非空

    • 不能有空值，否则会报错
    • 一般配合默认值DEFAULT来使用
      -- 测试NOT NULL
CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED,
);
INSERT user7(username, password) VLAUES('KING', 'KING') COMMENT '不用输入id，因为是自增长，age可以为空';
INSERT user7(username, password, age) VLAUES('KING1', 'KING1', 12)
      在这里插入图片描述
      在这里插入图片描述

  • DEFAULT默认值

    -- 测试DEFAULT
CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男', '女', '保密') NOT NULL DEFULAT '男'
);
INSERT user8(username, password) VLAUES('KING', 'KING') COMMENT 'id自增正，age默认18，addr默认北京，sex默认男';
INSERT user8 VLAUES(2, 'QUEEN','QUEEN', 29, '上海', '保密')
INSERT user8 VLAUES(3, 'QUEEN','QUEEN', DEFAULT, DEFAULT, '保密')

    在这里插入图片描述
    在这里插入图片描述

  • UNIQUE KEY \ UNIQUE 唯一，唯一性索引

    • NULL不算重复
    • 可以有多个唯一值
      -- 测试UNIQUE KEY
CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);
INSERT user9(username) VALUES('A');
INSERT user9(username) VALUES('A1');
INSERT user9(username) VALUES('A1') COMMENT '违反唯一性条件约束，报错';
INSERT user9(username, card) VALUES('B', 111);
INSERT user9(username, card) VALUES('B1', NULL);
INSERT user9(username, card) VALUES('B2', NULL) COMMENT 'NULL不算重复';
      在这里插入图片描述

  • 总结加上完整性约束条件，创建表的语法：

    CREATE TABLE [IF NOT EXISTS] tb1_name(
字段名称 字段类型 [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值][[PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]
)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;

如何查看数据库中的数据表以及表结构？

查看数据库中的数据表

  • 查看数据库下的数据表：SHOW TABLES
  • 查看指定表的信息
    • SHOW CREATE TABLE tbl_name
  • 查看指定表的表结构
    • DESC tbl_name
    • DESCRIBE tbl_name
    • SHOW COLUMNS FROM tbl_name
  • 查看指定表的具体数据
    • SELECT * FROM tbl_name

如何修改表结构？

-- 创建用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
passward CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '38277194@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男', '女', '保密') DEFAULT '保密'
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT ‘default.jpg’
);

在这里插入图片描述

  • 修改表名

    • ALTER TABLE tbl_name RENAME [TO|AS] new_name
    • RENAME TABLE tbl_name TO new_name
      -- 将user10重命名成user11
-- 方法一
ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user11;
ALTER TABLE user10 RENAME user11;
-- 方法二
RENAME TABLE user11 TO user10 COMMENT '这里的 TO 不可以省略'

  • 添加字段：ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

  • 删除字段：ALTER TABLE tbl_name DROP 字段名称

    -- 添加字段
ALTER TABLE user10 ADD card CHAR(18) COMMENT '默认添加到已有字段的末尾';
ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE COMMENT '添加带有完整性约束条件的字段';
ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST COMMENT '把字段添加到第一个位置';
ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username COMMENT '把字段添加到username之后';
-- 选中一次表，添加多个字段
ALTER TABLE user10
ADD test4 INT NOT NULL DEFAULT 123 AFTER passward
ADD test5 FLOAT(6,2) FIRST,
ADD test6 SET('A', 'B', 'C');

-- 删除字段
ALTER TABLE user10 DROP test6;
-- 选中一次表，删除多个字段
ALTER TABLE user10
DROP test2,
DROP test3,
DROP test4,
DROP test5;

-- 添加test字段，删除addr字段
ALTER TABLE user10
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;

  • 修改字段：ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

  • 修改字段名称：ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

    -- MODIFY 修改字段类型、位置、完整性约束条件
-- 将 email 的数据类型修改为 VARCHAR(200)
ALTER TABLE user10 MODIFY email VARCHAR(200);
-- 修改 email 的完整性约束条件
ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '38277194@qq.com'；
-- 修改字段位置
ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
-- 将test字段修改为CHAR(32) NOT NULL DEFUALT ‘123’ 并且移动到第一个位置
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

-- CHANGE 修改字段名称、字段类型、位置、完整性约束条件
-- 将test字段改为test1
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;
ALTER TABLE user10 CHANGE test test INT;

  • 添加默认值：ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

  • 删除默认值：ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

    -- 添加默认值
ALTER TABLE user10 ALTER age SET DEFAULT 18;
-- 删除默认值
ALTER TABLE user10 ALTER age DROP DEFAULT;

  • 添加主键：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称,…)

  • 删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY

    • 如果一个表的主键已经被定义为 AUTO_INCREMENT，则他的主键属性不可以被删除。
    -- 添加主键
-- 将已有字段赋值为主键
ALTER TABLE test10 ADD PRIMARY KEY(id);
ALTER TABLE test10 ADD PRIMARY KEY(id, card);
ALTER TABLE test10 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

-- 删除主键
ALTER TABLE test10 DROP PRIMARY KEY COMMENT '不删除数据，只是取消指定主键';
-- 如果要删除的主键，本身是 AUTO_INCREMENT，则需要先删除自增长，再删除主键
ALTER TABLE test10 MODIFY id INT UNSIGNED;
ALTER TABLE test10 DROP PRIMARY KEY;

  • 添加唯一：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] 索引名称

    • 如果没有命名索引名称的话，则会建立一个跟字段名称一样的索引名称

  • 删除唯一：ALTER TABLE tbl_name DROP {INDEX|KEY} 索引名称

    -- 添加唯一索引
ALTER TABLE user10 ADD UNIQUE(username);
ALTER TABLE user10 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);
ALTER TABLE user10 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test, test1);

-- 删除唯一索引
ALTER TABLE user10 DROP INDEX username;
ALTER TABLE user10 DROP KEY uni_card;
ALTER TABLE user10 DROP KEY mulUni_test_test1;

在这里插入图片描述

  • 修改表的存储引擎：ALTER TABLE tbl_name ENGINE=存储引擎名称
    -- 修改表的储存引擎为MyISAM
ALTER TABLE user10 ENGINE=MyISAM;
  • 设置自增长的值：ALTER TABLE tbl_name AUTO_INCREMNET=值
    -- 修改自增长值，必须保证已有自增长字段
ALTER TABLE user10 AUTO_INCREMENT=100;
  • 删除数据表：DROP TABLE [IF EXISTS] tbl_name[,tbl_name…]
    • 如果删除一列数据表，其中有表不存在，则使用 IF EXISTS 命令只成功删除存在的表
-- 删除数据表
DROP TABLE IF EXISTS user10;
DROP TABLE IF EXISTS user10, user9, user8;
DROP TABLE IF EXISTS user123, user9, user8; COMMENT '不存在user123这张表，所以会显示一个warning。但同时user8、user9会被成功删除。'
  • 在登陆 MySQL 时，直接打开数据库 maizi ：
    • mysql -uroot -p -d maizi
    • 使用 SELECT DATABASE(); 查看当前数据库

数据的操作（DML）：增删改

插入数据

  • 不指定具体的字段名：INSERT [INTO] tbl_name VALUES|VALUE(值…)

  • 列出指定字段
    INSERT [INTO] tbl_name(字段名称1,…) VALUES|VALUE(值1,…)

  • 同时插入多条记录
    INSERT [INTO] tbl_name[(字段名称…)] VALUES(值…),(值…)…

  • 通过SET形式插入记录
    INSERT [INTO] tbl_name SET 字段名称=值,…
    将查询结果插入到表中
    INSERT [INTO] tbl_name[(字段名称,…)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

    -- 测试插入记录INSERT
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);

-- 不指定具体的字段名
INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20) COMMENT '根据建表顺序，一次插入'; 
INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);

-- 列出指定字段
INSERT user(username,password) VALUES('A','AAA');
INSERT user(password,username) VALUES('BBB','B');
INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT) COMMENT '保证指定字段和插入字段顺序一一对应'; 

-- 一次插入多条记录
INSERT user VALUES(6,'D','DDD','D@QQ.COM',35),
(8,'E','EEE','E@QQ.COM',9),
(18,'F','FFF','F@QQ.COM',32);

-- 通过INSERT SET形式插入记录
INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com', age=48;
INSERT user SET username='maizi',password='maizixueyuan' ;

-- 将查询结果插入到表中
CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE
);

INSERT testUser SELECT id,username FROM user COMMENT '将 user 表中 id 和 username 的信息都存到testUser中';
INSERT testUser SELECT * FROM user COMMENT '* 的意思是import 全部数据，这里会报错，因为 testUser 没有那么多 column';
INSERT testUser(username) SELECT username FROM user COMMENT '只把用户名插入值，id 默认是自增的，所以会自动写出来';

更新数据

  • UPDATE tbl_name SET 字段名称=值,… [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
    • 如果不加 where 条件，则表中所有的记录都会被更新
    -- 将用户表中所有的用户年龄更新为5
UPDATE user SET age=5;
-- 将用户表中所有的用户年龄更新为20，邮箱更新为test@qq.com
UPDATE user SET age=20,email='test@qq.com';
-- 将第一个记录的password，email，age改变
UPDATE user SET password='king123',email='123@qq.com',age=99 WHERE id=1 COMMENT 'id=1为第一条';
-- 把id>3的用户年龄减少5
UPDATE user SET age=age-5 WHERE id>=3;
-- 把 A 这个人的年龄恢复默认值 18
UPDATE user SET age=DEFAULT WHERE username='A';

删除数据

  • 删除记录：DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
    • 如果什么条件都不加的话，就会全部清空数据，需要谨慎。
    • 通过delete删除记录时，不会充值AUTO_INCREMENT的值。
  • 彻底清空数据表：TRUNCATE [TABLE] tbl_name
-- 删除testUser表中的记录
DELETE FROM user;
ALTER TABLE user AUTO_INCREMENT=1 COMMENT 'delete不会重置AUTO_INCREMENT，因此想要新插入的数据从1开始，则需要手动重置AUTO_INCREMENT的值，或者直接使用TRUNCATE';
-- 删除user表中id为1的用户
DELETE FROM user WHERE id=1;

-- 彻底清空user表
TRUNCATE TABLE user;

数据的操作（DQL）：查

查询记录

  • 查询记录
    SELECT select_expr [, select_expr ...] 
   [
       FROM table_references 
       [WHERE 条件]
       [GROUP BY {col_name | position}  [ASC | DESC], ... 分组]
       [HAVING 条件 对分组结果进行二次筛选]
       [ORDER BY {col_name | position} [ASC | DESC], ...排序]
       [LIMIT 限制显示条数]
   ]

查询表达式

  • 每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔
  • *表示所有列,tbl_name.*可以表示命名表的所有列
  • 查询表达式可以使用[AS]alias_name为其赋予别名
  • 可以用 SELECT 表名.字段 FROM 表名的形式，来明确字段的来源
    -- 查询 cms_admin 中所有的数据信息
SELCT * FROM cms_admin;
SELECT cms_admin.* FROM cms_admin COMMENT '在多表查询中更有用';

-- 查询管理员编号和名称
SELECT id,username FROM cms_admin COMMENT '结果按照写的字段顺序来显示';

-- 表来自于哪个数据库下db_name.tbl_name
SELECT id,username,role FROM cms.cms_admin;

-- 字段来自于哪张表tbl_name.expr_name
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

-- 给表名起别名，精简表名的长度
SELECT id,username FROM cms_admin AS a;
SELECT id,username FROM cms_admin a COMMENT '可以省略AS，但是尽量不要省略';
SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱',role AS '角色' FROM cms_admin COMMENT 'column name 会改变为编号、用户名、邮箱、角色';

-- 同时给表和字段起别名
SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a COMMENT 'column name 会改变为i、u、e、r'; 

-- 也可以这样子查询，1、2、3、4、5不在cms_user表当中
SELECT 1,2,3,4,5,id,username FROM cms_user;
    在这里插入图片描述

WHERE 条件

在这里插入图片描述

  • 比较：=，<，<=，>，>=，!=，<>，!>，!<， <=>
  • 是否为控制：IS NULL、IS NOT NULL
-- 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1 COMMENT '如果没有的话，返回一个Empty set';

-- 查询编号不为1的用户
SELECT  * FROM cms_user WHERE id!=1;
SELECT  * FROM cms_user WHERE id<>1 COMMENT '也表示不等于';

-- 查询表中记录age值为NULL
-- 方法一： <=>
SELECT * FROM cms_user WHERE age=NULL COMMENT '不可以检测是否等于NULL值';
SELECT * FROM cms_user WHERE age<=>NULL COMMENT '与 = 符号作用一样，只是额外的可以检测等于NULL的运算';
-- 方法二：IS NULL 或者IS NOT NULL
SELECT * FROM cms_user WHERE age IS NULL;
  • 指定范围：BETWEEN AND，NOT BETWEEN AND
-- 查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10 COMMENT '3和10之间，包括3和10';

-- 查询编号不在3~10之间的用户
SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10 COMMENT '不在3和10之间，例如编号为1、2、11、12';
  • 指定集合：IN，NOT IN
    • 集合内字符串忽略大小写
-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100) COMMENT '如果只有1、3、5、7、9、11，则只显示这些行，不显示编号13、100，这些不存在的行。';

-- 查询用户名为king,queen,张三，章子怡的记录
SELECT * FROM cms_user WHERE username IN('king','qUeen','张三','章子怡') COMMENT '查询的时候，集合内字符串忽略大小写';
  • 匹配字符：LIKE，NOT LIKE
    • 集合内字符串忽略大小写
    • %：代表0个一个或者多个任意字符
    • _：代表1个任意字符
-- 模糊查询
-- 查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

-- 相当于没有写查询条件
SELECT * FROM cms_user WHERE username LIKE '%';

-- 查询用户名为3位的用户：用三个下划线代表占位
SELECT * FROM cms_user WHERE username LIKE '___';

-- 用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%' COMMENT '集合内字符串忽略大小写';

-- 没有使用 _ 或者 % 的话，LIKE语句和 = 效果一样
SELECT * FROM cms_user WHERE username LIKE 'king';

-- LIKE 语句取反
SELECT * FROM cms_user WHERE username NOT LIKE '_I%';
  • 多个查询结果：AND、OR
-- 查询编号大于等于3、年龄不为NULL、并且proId为的3的用户 
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;

-- 查询编号在5~10的用户并且用户名为4位的用户
SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

-- 查询用户名以张开始，或者用户所在身份为2,4的记录
SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);

GROUP BY查询结果分组

  • 查询结果分组：GROUP BY
    -- 按照用户所属身份分组proId，返回一个只显示每组中第一条记录的表格。
SELECT * FROM cms_user GROUP BY proId;

-- 向用户表中添加性别字段，且将数据赋值不同的性别
ALTER TABLE cms_user ADD sex ENUM('男','女','保密');
UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);
UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex='保密' WHERE id IN(12,11);

--按照用户性别分组
SELECT * FROM cms_user GROUP BY sex;
    在这里插入图片描述
    --按照字段位置分组
SELECT * FROM cms_user GROUP BY 7 COMMENT '第7列，也就是proId列';
    在这里插入图片描述
    --按照多个字段（sex、proId）分组
SELECT * FROM cms_user GROUP BY sex,proId;
    在这里插入图片描述
    • 先写条件，再分组。
      -- 查询编号大于等于5的用户，按照sex分组。先写条件，再分组。
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;
  • 配合GROUP_CONCAT()得到分组详情
-- 查询id,sex,用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

在这里插入图片描述

--查询proId，性别详情，注册时间详情，用户名详情 安装proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
FROM cms_user GROUP BY proId;

在这里插入图片描述

  • 配合聚合函数
    在这里插入图片描述
    • COUNT()
      • COUNT(*) 和 COUNT(字段) 基本上一样，都是计入全部的数据数。
      • 除非 COUNT(字段) 中，该字段存在 NULL 值。COUNT(字段) 不计入NULL值。
      --查询编号，sex，用户名详情，和组中总人数，按照sex分组。
SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
      在这里插入图片描述
      -- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
SELECT COUNT(id) AS totalUsers FROM cms_user;
--COUNT(字段)不统计NULL值
SELECT COUNT(age) AS totalUsers FROM cms_user;
    • MAX()
    • MIN()
    • AVG()
    • SUM()
      --查询编号、性别、用户名详情、组中总人数、组中最大年龄、最小年龄，平均年龄、以及年龄总和，按照性别分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;
      在这里插入图片描述
  • 配合WITH ROLLUP记录上面所有记录的总和，结果为下图最后一行sex为NULL的部分。
    • totalUsers 的最后一行，计算的是以上数据的个数
    • max_age 的最后一行，显示的是以上数据中的max
    • min_age 的最后一行，显示的是以上数据中的min
    • sum_age 的最后一行，计算的是以上数据的和
-- WITH ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

在这里插入图片描述

HAVING子句

  • 通过 HAVING 语句对已使用 GROUP BY 分组后的结果，进行二次筛选。必须和 GROUP BY 连用。
    -- 查询组中人数大于2的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2;
    • 结果只有两组，因为保密组的人数为2，小于2。
      在这里插入图片描述
  • 可以有多个筛选条件
    -- 查询组中人数大于2并且最大年龄大于60的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;
    • 结果只剩一组，因为性别为男的组 max_age 小于60。
      在这里插入图片描述
  • 可以用 WHERE 语句和 HAVING 语句，进行两次筛选。
    -- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;
IrisEpode
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 余额表设计_余额表的设计
weixin_39600510的博客
02-04 2226
原由在开发ERP应用中，我们经常需要知道某个实体的当前数量，例如知道商品当前的库存，或者科目的金额，或者某个客户剩余的信用额度，所以这种需求是比较普遍的。通常会设计两张表，一张是流水账表，有的称明细表，或者日志表，用于记录所有发生的事务记录。一张是余额表，用于记录各个实体当前最新的余额。流水号NO.商品编号ProductId增加Add减少Reduce1P132P113P25商品编号ProductI...
怎样查询mysql数据库余额_mysql怎样递归查询所有数据
weixin_39781323的博客
01-19 261
mysql怎样递归查询所有数据发布时间：2020-11-13 10:12:10来源：亿速云阅读：90作者：小新小编给大家分享一下mysql怎样递归查询所有数据，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！mysql递归查询所有数据的方法：首先创建表，并初始数据；然后利用【find_in_set()】函数和【group_c...
MySQL语法--03--查询select
时光里的博客
10-03 804
查询 select 判断条件，子查询，统计查询，聚合，分组，表的关联 判断条件 distinct 去重 SELECT DISTINCT job FROM emp; like 通配符：%（n个字符） _下划线（1个字符） 查询emp表中,名字 t 开头 n个字符 SELECT * FROM emp WHERE ename LIKE 't%'; 查询emp表中,名字 含有 n 的 SELECT * FROM emp WHERE ename LIKE '%n%'; 查询emp表中,名字 t 开头 3
交易记录余额计算mysql_用mysql计算余额
weixin_29323273的博客
01-19 863
简短的回答,是的更长的答案,你可以使用一个变量来计算它,因为它向下迭代行,即SELECT`table`.`ID`,`table`.`In`,`table`.`Out`,@Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`FROM `table`, (SELECT @Balance := 0) AS variableInit...
【SQL server】创建表，增删改，约束（数据完整性）
萝卜丝儿皮尔
03-06 750
-- 创建表 create table class (classno nvarchar(8) not null , --不允许为空值 classname nvarchar(20) not null) go -- 增加列ope,clo alter table course add ope datetime null, clo nvarchar(8) null go --删除列ope,clo a...
MySQL 详细单表增删改查crud语句
09-08
第二范式（2NF）要求表中所有非主键字段完全依赖于主键；第三范式（3NF）要求非主键字段不依赖于其他非主键字段。遵循这些范式有助于减少数据冗余和提高数据一致性。 此外，数据库设计中常见的五种约束包括： 1. *...
MySQL数据库基础实例教程(第2版)教学大纲.pdf
06-24
学生应掌握在命令行和图形界面对数据进行增删改查的操作，并会使用SHOW和DESCRIBE等查询语句。 5. **数据查询**：包括单表查询，涉及SELECT语句的基本用法，选择特定列，聚合函数的运用。学生应熟练进行单表查询，...
MySQL-增删改查(四)
12-14
在MySQL数据库管理中，"增删改查"（CRUD，Create, Read, Update, Delete）是最基础且重要的操作。本文将重点讨论“增”这一部分，即如何向数据库表中添加数据。我们以创建一个名为`message`的表为例，来详细讲解两种...
mysql.rar_MYSQL
09-25
一旦连接建立，就可以执行SQL语句进行数据的增删改查操作。 3. **安全性**： 在处理数据库连接时，安全措施至关重要。使用预编译的SQL语句可防止SQL注入攻击，同时应定期更改数据库密码，并限制对敏感数据的访问...
mysql基础知识总结（第二版）
最新发布
12-06
DML语句主要涉及数据的增删改查。INSERT用于向表中插入新记录，UPDATE用于更新现有记录，而DELETE可用来删除单条或批量记录。TRUNCATE比DELETE更快，用于清空整个表。SELECT是查询数据的核心，配合WHERE子句过滤条件...
并发实际场景（保持余额操作的正确：数据库余额字段版）
qq_23725949的博客
12-14 3322
场景： 一个人在一家银行办了一个账户，银行给了 一张卡（存取款）、一本存折（存取款）、一个网银（查询余额） 卡和存储不断存款和取款，网银不断查询余额。如何保持余额的正确。   数据库余额表：原本想用版本号来实现的，后面弃用version字段。 DROP TABLE IF EXISTS `t_test`; CREATE TABLE `t_test` (   `id` int(11) NOT...
mysql 查询每个人、每个月的消费金额及每年的消费总额
热门推荐
rb_Russell的博客
03-01 1万+
mysql 查询每个人、每个月的消费金额及每年的消费总额 最近按需求实现会员年报表， 假设现有会员表member、订单表i_order， member表字段(id, member_name, xxxx）, i_order表字段(id,member_id,handle_time,money,order_status,xxxx); 思路如下： 分别查询12个月及全年各会员消费总额，得到13个表，再...
access自动自动累计余额_在Access2007数据库中如何使用查询来计算累计余额
weixin_34540432的博客
12-23 895
数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。在这一篇教程里面，小编主要和大家简单的介绍一下：在Access2007数据库中如何使用查询来计算累计余额。财务人员在使用手工记账的时候，在“借”以及“贷”这两者后面会有一个余额，这一个余额是累计余额。换一句话来说，也就是每一笔账以后都在记余额。假如说大家用计算机记账，能够直接通过查...
mysql余额统计_实现按部门月卡余额总额分组统计的SQL查_mysql
weixin_39945789的博客
02-07 503
陈优章的专栏(原创，到现在为至最为复杂的SQL查询代码)实现按部门月卡余额总额分组统计的SQL查询代码(在Ms SQL Server中调试通过)SELECT dp.dpname1 AS 部门, cust_dp_SumOddfre.sum_oddfare AS 当月卡总余额FROM (SELECT T_Department.DpCode1, SUM(custid_SumOddfare_group.s...
业务表解析-余额系统
大白菜
07-25 1041
业务表解析-余额系统
利用MySQL基于交易记录表对目标账户和其他账户的收支情况（一个交易对方一行数据）进行统计
dongfang979的专栏
01-19 2328
非IT对口工作，但是因为工作需要时长需处理分析一些表格数据，以前一些简单的数据利用EXCEl轻松解决，但是随着工作的深入，接触了量级更大更多的数据，尝试利用以前初学的数据库知识处理数据，主要使用MySQL和Navicat for MySQL。 近日，为了把一个交易记录表转换成需要特定格式的统计表时陷入僵局，网上搜索方法没有看到想要的和好用的，在尝试后自己解决了，为此第一次发文纪念一下，请高手指正...
【数据库练习】1、mysql查询基础练习
lxm1278的博客
07-13 378
create table emp ( empno int(11) NOT NULL,-- 员工号 ename varchar(30) DEFAULT NULL, -- 名字 job varchar(30) DEFAULT NULL,-- 职位 mgr int(11) DEFAULT NULL, -- 上司 hiredate date DEFAULT NULL, -- 出生日期 sal decimal(6,...
MySQL子查询
taifyang的博客
05-21 214
利用子查询进行过滤：列出订购物品TNT2的所有客户 检索包含物品TNT2的所有订单的编号 select order_num from orderitems where prod_id = 'TNT2'; 检索前一步列出的订单编号的所有客户的ID select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2'); 列出订购物品TNT2的所有客户
mysql按月统计充值提现_MySQL数据篇（五）–SQL对数据进行按月统计，或对数据进行按星期统计...
weixin_32429413的博客
02-03 531
MySQL数据篇(五)–SQL对数据进行按月统计，或对数据进行按星期统计对于所有的需求，当你不知道怎么处理的时候，你就先用最简单的方法，或者说的明白一点，用最原始的方法，先实现业务需求再说。一、对提现队列数据表“ims_checkout_task”进行汇总统计，按月汇总统计每个月的提现总额，提现总次数。1、SQL操作如下：SELECT id,SUM(case when FROM_UNIXTIME(...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值