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;
- COUNT()
- 配合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,小于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。
- 结果只剩一组,因为性别为男的组 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;