mysql-单表操作
mySql单表操作主要有一下内容:
1.查询:查询又分为几种,范围查询,模糊查询、空值查询、多条件查询
查询的语句格式为:SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 条件表达式
1.1-in:范围查询:SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1,元素 2,元素 3)
1.1-BETWEEN AND:范围查询:SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2
1.2:模糊查询:SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’
1.2.1:%匹配多个字符
1.2.2:_匹配单个字符
1.3.空值查询:SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL
1.4.多条件查询-and:SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]
1.5.多条件查询-or:SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]
2.去重:SELECT DISTINCT 字段名 FROM 表名;
3.排序:SELECT 字段 1,字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC]
4.分组:GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
4.1 分组元素统计:SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;--把每个年级的学生名字全部放在一个字符串里面,以逗号隔开
4.2 分组数量统计:SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
4.3 对分组进行过滤:SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
5.分页:SELECT 字段 1,字段 2...FROM 表名 LIMIT 初始位置,记录数;
以上就是单表基本操作-------------------------------------------------------------------------------------------------------
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community
*********************************************************************
*/
/*!创建学生表*/;
create table `t_student` (
`id` double ,
`stuName` varchar (60),
`age` double ,
`sex` varchar (30),
`gradeName` varchar (60)
);
//插入数据
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张三','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张三丰','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','李四','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','王五','22','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','珍妮','21','女','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李娜','26','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','王峰','20','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','梦娜','21','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','小黑','22','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','追风','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小小张三','21',NULL,'二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小张三','23','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','张三锋小','24',NULL,'二年级');
//单表操作
SELECT id,stuName,age,sex,gradeName FROM t_student ;
SELECT stuName,id,age,sex,gradeName FROM t_student ;
SELECT * FROM t_student;
SELECT stuName,gradeName FROM t_student;
SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;
SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三%';
SELECT * FROM t_student WHERE stuName LIKE '张三__';
SELECT * FROM t_student WHERE stuName LIKE '%张三%';
SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
SELECT DISTINCT gradeName FROM t_student;
SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC;
SELECT * FROM t_student GROUP BY gradeName;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;
;
最新文章
- mysql语句查询练习
- kettle系列-4.kettle定制化开发工具类
- php基础排序算法 冒泡排序 选择排序 插入排序 归并排序 快速排序
- ion-scroll 滚动框
- Android IOS WebRTC 音视频开发总结(八十二)-- VP8对VP9,质量还是码率?
- 自动备份SQL数据库到云存储Storage
- python中时间日期格式化符号
- HTTP状态码对照表 HTTP response codes
- StyleCop学习笔记——初识StyleCop
- swift 函数返回值
- POJ 2391 容牛问题
- navicat:cannot create oci environment
- jQuery性能优化篇
- phpexcel导入excel处理大数据
- Cartfile学习参考博客
- 1705: 小明在工作(zzuli)
- python全栈开发 * 12 知识点汇总 * 180530
- 如何做适合seo的404页面
- JavaScript Boolean Object 逻辑
- java1.8操作日期
热门文章
- c# dll自动注册
- VS2017 不能创建 vsto Excel 工作簿程序的问题
- POJ - 2533 Longest Ordered Subsequence与HDU - 1257 最少拦截系统 DP+贪心(最长上升子序列及最少序列个数)(LIS)
- POJ - 2031 Building a Space Station 三维球点生成树Kruskal
- IOSerialize,xml和json,soap序列化器,二进制序列化器,XML序列化器,文件 检查、新增、复制、移动、删除
- 2014-11-1 NOIP模拟赛2
- Django + Vue cli 3.0 访问静态资源问题
- JDK自带反编译工具javap
- NET Core学习方式(视频)
- 049 Group Anagrams 字谜分组