Mysql (一) 单表查询

https://www.cnblogs.com/mofujin/p/11355517.html

表

CREATE TABLE `tb_content` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) NOT NULL COMMENT '内容类目ID',
  `title` varchar(200) DEFAULT NULL COMMENT '内容标题',
  `sub_title` varchar(100) DEFAULT NULL COMMENT '子标题',
  `title_desc` varchar(500) DEFAULT NULL COMMENT '标题描述',
  `url` varchar(500) DEFAULT NULL COMMENT '链接',
  `pic` varchar(300) DEFAULT NULL COMMENT '图片绝对路径',
  `pic2` varchar(300) DEFAULT NULL COMMENT '图片2',
  `content` text COMMENT '内容',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `updated` (`updated`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

查询

1 单表全表查询

SELECT * FROM `tb_content`

2 单表条件查询

SELECT * FROM `tb_content` WHERE  tb_content.id=28

3 单表条件查询表名称缩写只查表的部分列

SELECT tb1.id,tb1.category_id,tb1.title FROM `tb_content` tb1 WHERE  tb1.category_id=90

4 单表 category_id 升序

SELECT tb1.id,tb1.category_id,tb1.title FROM `tb_content` tb1  ORDER BY category_id ASC;

5 单表 category_id 降序

SELECT tb1.id,tb1.category_id,tb1.title FROM `tb_content` tb1  ORDER BY category_id DESC;

6 单表查询最大值

SELECT  max(tb1.category_id) FROM `tb_content` tb1;

7 单表查询最大值和列

SELECT  max(tb1.category_id),tb1.category_id FROM `tb_content` tb1;

8 单表最小值

SELECT  min(tb1.category_id),tb1.category_id FROM `tb_content` tb1;

9 单表查询平均值

SELECT  AVG(tb1.category_id) FROM `tb_content` tb1;

10 单表求和

SELECT  SUM(tb1.category_id) FROM `tb_content` tb1;

修改表

11 单表查询组对应的个数

SELECT  count( sub_title) as 人数 FROM `tb_content` tb1 GROUP BY sub_title;

12 单表查询条件 >,<,=,<=,>= 行总数

//select id,name from emp where id >= 3 and id <= 6;

//select * from emp where id >= 3 or id <= 6;

SELECT  count(*) as 人数 FROM `tb_content` tb1 WHERE tb1.category_id>75;

13单表查询模糊查询

SELECT  * FROM `tb_content` tb1 WHERE sub_title LIKE '%d4%';